Unix Shell
從unix shell 登入到mysql
[mysql dir]/bin/mysql -h hostname -u root –p
修改使用者密碼
[mysql dir]/bin/mysqladmin -u root -h hostname.blah.org -p password 'new-password'
匯出：
[mysql dir]mysqldump -c -u username -ppassword databasename [tablename] > backup.sql
tar zcvf database.tgz mysql data directory
匯入：
[mysql dir]mysql –h host –u user –ppassword databasename <>
tar zxvf database.tgz
MYSQL指令
建立資料庫
CREATE database [databasename];
刪除資料庫
DROP database [database name];
列出所有資料庫清單
show databases;
開啟一個資料庫
use [database name];
列出開啟中資料庫的所有資料表
Show tables;
刪除資料表
DROP table `table name`;
最佳化資料表
OPTIMIZE table `table name1`,`table name2`;
列出一個資料表的格式
Describe [table name];
列出一個資料表欄位和附屬關係
show columns from [table name];
新增一個資料表
CREATE table `table name` (`column name` varchar(50) ,primary Key(`column name`)) type=MYISAM;
新增一個欄位
ALTER table `table name` ADD column `new column name` varchar (20);
修改欄位的資料型態
ALTER table `table name` MODIFY `column name` VARCHAR(15);
修改欄位名稱
ALTER table `table name` change `old column name` `new column name` varchar (50);
建立Primary key
ALTER table `table name` ADD primary key (`column name`);
建立Index
ALTER table `table name` ADD index (`column name`);
增加Unique的屬性
ALTER table `table name` ADD unique (`column name`);
增加全文檢索的屬性
ALTER table `table name` ADD full text (`column name`);
刪除主KEY
ALTER table `table name` DROP primary key;
刪除index屬性
ALTER table `table name` DROP index `colmn name`;
刪除Unique的屬性
ALTER table `table name` DROP unique `column name`;
刪除全文檢索的屬性
ALTER table `table name` DROP full text `column name`;
刪除一個欄位
ALTER table `table name` DROP column `column name`;
範例：
CREATE TABLE `table name`
(firstname VARCHAR(20), middleinitial VARCHAR(3), lastname VARCHAR(35),suffix VARCHAR(3),
officeid VARCHAR(10),userid VARCHAR(15),username VARCHAR(8),email VARCHAR(35),phone VARCHAR(25), groups VARCHAR(15),datestamp DATE,timestamp time,pgpemail VARCHAR(255));
CREATE table `table name` (personid int(50) not null auto_increment primary key,firstname varchar(35),middlename varchar(50),lastname varchar(50) default 'bato');
回傳一個資料表的所有資料
SELECT * FROM [table name];
回傳符合條件的部份資料
SELECT * FROM [table name] WHERE [field name] = "whatever";
回傳符合除了條件以外的部份資料並依某個欄位的值排序
SELECT * FROM [table name] WHERE name != "whatever" AND phone_number = '123456' order by phone_number;
回傳類似該條件的部份資料
SELECT * FROM [table name] WHERE name like "Bob%" AND phone_number = '123456';
使用正規劃方式回傳符合條件的部份資料
SELECT * FROM [table name] WHERE rec RLIKE "^a$";
回傳唯一的資料
SELECT DISTINCT [column name] FROM [table name];
回傳資料並指定降冪(DESC)升冪(ASE)排序
SELECT [col1],[col2] FROM [table name] ORDER BY [col2] DESC;
回傳一個資料表的資料筆數
SELECT COUNT(*) FROM [table name];
回傳一個欄位的所有資料加總起來的值
SELECT SUM(table name) FROM [database name];
組合搜尋多個資料表
SELECT lookup.illustrationid, lookup.personid,person.birthday from lookupleft JOIN person on lookup.personid=person.personid=statement to JOIN birthday in person table with primary illustration id;
刪除一筆資料
DELETE from [table name] where [field name] = 'whatever';
建立一個本地端新的MYSQL使用者
use mysql
INSERT INTO [table name] (Host,User,Password) VALUES('localhost','user',PASSWORD('password'));
修改使用者密碼
SET PASSWORD FOR 'user'@'hostname' = PASSWORD('passwordhere');
建立一個使用者並指定能對一個資料庫進行SELECT、INSERT、UPDATE、DELETE、CREATE
INSERT INTO [table name] (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES ('%','db','user','Y','Y','Y','Y','Y','N');
修改既有的使用者權限
UPDATE [table name] SET Select_priv = 'Y',Insert_priv = 'Y',Update_priv = 'Y' where user = 'user';
重新載入資料庫權限
FLUSH PRIVILEGES;
載入CSV檔到一個資料表裡
LOAD DATA INFILE '/dir/filename.csv' replace INTO TABLE `table name`
FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (field1,field2,field3);