常用的MySQL指令

【啟動與停止】
啟動
shell> mysql.server start

停止
shell> mysql.server stop

mysql.server這個script在MySQL安裝目錄(在FreeBSD是/usr/local)的share/mysql下

【3.1與Server的連線與中斷】
連線
shell> mysql -h host -u user -p
Enter password: ********

中斷
有三種方式
mysql> QUIT
mysql> quit;
按Control-D


【3.2輸入查詢】
mysql> SELECT VERSION(), CURRENT_DATE;
+--------------+--------------+
| version() | CURRENT_DATE |
+--------------+--------------+
| 3.22.20a-log | 1999-03-19 |
+--------------+--------------+
row in set (0.01 sec)
mysql>
指令後要加一個分號(;)表示結束（有某些例外，如前述的QUIT）
指令關鍵字不分大小寫

另類查詢，可將mysql當計算器
mysql> SELECT SIN(PI()/4), (4+1)*5;
+-------------+---------+
| SIN(PI()/4) | (4+1)*5 |
+-------------+---------+
| 0.707107 | 25 |
+-------------+---------+

也可以在一行中輸入多項指令
mysql> SELECT VERSION(); SELECT NOW();

也可以將一項指令分成多行輸入
mysql> SELECT

-> USER()

-> ,

-> CURRENT_DATE;

如果要取消之前的輸入可鍵入\c
mysql> SELECT

-> USER()

-> \c
mysql>

提示符號的意義：
提示符號 意義
mysql> 可下新指令
-> 等待下一行，多項指令時
,> 等待下一行，收集以'開頭的字串
"> 等待下一行，收集以"開頭的字串

【3.3建立與使用資料庫】
顯示目前有幾個資料庫
mysql> SHOW DATABASES;
+----------+
| Database |
+----------+
| mysql |
| test |
| tmp |
+----------+

使用test資料庫
mysql> USE test
Database changed
注意USE一QUIT一樣不需在後加分號，USE敘述只能寫在一行中

在test中的任何東西，任何人都可以將其刪除，所以請管理員我們設好一個資料庫的權限。假設這個資料庫叫menagerie，管理員必須執行這樣的指令：
mysql> GRANT ALL ON menagerie.* TO your_mysql_name;

【3.3.1建立與選擇資料庫】
建立資料庫
mysql> CREATE DATABASE menagerie;

使用menagerie，使它成為目前的資料庫
mysql> USE menagerie
Database changed
建立資料庫的步驟只要做一次，但是每次開始一個mysql session時都要做如上的使用動作。或是當使用mysql時就選好資料庫：
shell> mysql -h host -u user -p menagerie
Enter password: ********
注意在這裡menagerie不是密碼，如果要將密碼放在-p後面，要像這樣：-pmypassword。但不建議這麼做。

【3.3.2建立表格】
顯示表格
mysql> SHOW TABLES;
Empty set (0.00 sec)
剛建好資料庫時，裡面是空的

建立表格
mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),
-> species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);

顯示表格結構
mysql> DESCRIBE pet;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name | varchar(20) | YES | | NULL | |
| owner | varchar(20) | YES | | NULL | |
| species | varchar(20) | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| birth | date | YES | | NULL | |
| death | date | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+

【3.3.3載入資料到表格中】
使用LOAD DATA與INSERT敘述
假設pet的資料如下：（這裡YYYY-MM-DD的日期格式可能與你所用的不同）
name owner species sex birth death
Fluffy Harold cat f 1993-02-04
Claws Gwen cat m 1994-03-17
Buffy Harold dog f 1989-05-13
Fang Benny dog m 1990-08-27
Bowser Diane dog m 1998-08-31 1995-07-29
Chirpy Gwen bird f 1998-09-11
Whistler Gwen bird 1997-12-09
Slim Benny snake m 1996-04-29
只為是從空白資料庫開始，所以最簡單的方法是將這些做成文字檔，然後用一行敘述將這些載入資料庫中。
可以建立一個pet.txt，其中每行一筆記錄，用tab隔開其中的值。不明的值可用NULL，在文字檔中，以\N表示。以Whistler這筆為例，會像這樣：
Whistler Gwen bird \N 1997-12-09 \N
用這個命令將pet.txt載入pet表格中：
mysql> LOAD DATA LOCAL INFILE "pet.txt" INTO TABLE pet;
可以在LOAD DATA敘述中指定欄的分隔符號與行的結尾符號，預設是tab與linefeed

一次加入一筆資料，使用INSERT
mysql> INSERT INTO pet
-> VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);

【3.3.4從表格取出資訊】
SELECT what_to_select
FROM which_table
WHERE conditions_to_satisfy

【3.3.4.1選擇所有資料】
mysql> SELECT * FROM pet;
這時發現Bowser的birth是1989而非1988，有幾種方式來改正：
．編輯pet.txt，修正這個錯誤，清掉這表格，重新載入：
mysql> SET AUTOCOMMIT=1; # Used for quick re-create of the table
mysql> DELETE FROM pet;
mysql> LOAD DATA LOCAL INFILE "pet.txt" INTO TABLE pet;
．只修正有錯誤的記錄：
mysql> UPDATE pet SET birth = "1989-08-31" WHERE name = "Bowser";

【3.3.4.2選擇特定列】
mysql> SELECT * FROM pet WHERE name = "Bowser";
mysql> SELECT * FROM pet WHERE birth >= "1998-1-1";
mysql> SELECT * FROM pet WHERE species = "dog" AND sex = "f";
mysql> SELECT * FROM pet WHERE species = "snake" OR species = "bird";
mysql> SELECT * FROM pet WHERE (species = "cat" AND sex = "m")
-> OR (species = "dog" AND sex = "f");

【3.3.4.3選擇特定欄】
mysql> SELECT name, birth FROM pet;
mysql> SELECT owner FROM pet;
mysql> SELECT DISTINCT owner FROM pet;
mysql> SELECT name, species, birth FROM pet
-> WHERE species = "dog" OR species = "cat";

【3.3.4.4輸出時依列排序】
mysql> SELECT name, birth FROM pet ORDER BY birth;
mysql> SELECT name, birth FROM pet ORDER BY birth DESC;
mysql> SELECT name, species, birth FROM pet ORDER BY species, birth DESC;

其它SQL指令
DROP

ALTER

INSERT INTO

DELETE

UPDATE

【dump資料表結構與裡面的資料】
mysqldump -u 使用者名稱 -p 密碼 資料庫 [資料表1 [資料表2...]]

備份home資料庫到home20020627.sql檔案
mysqldump -u root -p home > home20020627.sql

備份所有的MySQL資料庫的到backup-file.sql
# mysqldump -p -A > backup-file.sql

【自dump讀回資料】
# mysql -u root -p < backup-file.sql

【更改使用者密碼】
MySQL的預設管理員username是root，預設無密碼
方法一：
shell> mysql -u root mysql
mysql> UPDATE user SET Password=PASSWORD('new_password')
WHERE user='root';
mysql> FLUSH PRIVILEGES;

★方法二：
You can, in MySQL Version 3.22 and above, use the SET PASSWORD statement:
shell> mysql -u root mysql
mysql> SET PASSWORD FOR root=PASSWORD('new_password');

方法三：
Another way to set the password is by using the mysqladmin command:
shell> mysqladmin -u root password new_password

Only users with write/update access to the mysql database can change the password for others users. All normal users (not anonymous ones) can only change their own password with either of the above commands or with SET PASSWORD=PASSWORD('new password').

【新增使用者】
可使用GRANT指令，同時新增使用者與給予權限的工作

GRANT and REVOKE語法
GRANT priv_type [(column_list)] [, priv_type [(column_list)] ...]
ON {tbl_name | * | *.* | db_name.*}
TO user_name [IDENTIFIED BY 'password']
[, user_name [IDENTIFIED BY 'password'] ...]
[REQUIRE
[{SSL| X509}]
[CIPHER cipher [AND]]
[ISSUER issuer [AND]]
[SUBJECT subject]]
[WITH GRANT OPTION]

REVOKE，取消權限
REVOKE priv_type [(column_list)] [, priv_type [(column_list)] ...]
ON {tbl_name | * | *.* | db_name.*}
FROM user_name [, user_name ...]

For the GRANT and REVOKE statements, priv_type may be specied as any of the following:
★ALL PRIVILEGES FILE RELOAD
★ALTER INDEX ★SELECT
★CREATE ★INSERT SHUTDOWN
★DELETE PROCESS ★UPDATE
★DROP REFERENCES USAGE

The WITH GRANT OPTION clause gives the user the ability to give to other users any privileges the user has at the specied privilege level.

新增使用者的例子：
shell> mysql --user=root mysql
mysql> GRANT ALL PRIVILEGES ON *.* TO monty@localhost
IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
mysql> GRANT RELOAD,PROCESS ON *.* TO admin@localhost;
mysql> GRANT USAGE ON *.* TO dummy@localhost;

【刪除使用者】
要用delete指令
mysql> USE mysql
mysql> delete from user where user = 'the_username_to_be_deleted';

【設定使用權限】
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
ON bankaccount.*
TO custom@localhost
IDENTIFIED BY 'stupid';

mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
ON expenses.*
TO custom@whitehouse.gov
IDENTIFIED BY 'stupid';

mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
ON customer.*
TO custom@'%'
IDENTIFIED BY 'stupid';


出處：小紅帽技術論壇