MySQL常用命令

標籤：opera   視圖   cond   備份   匯入   seh   分組   cal   condition   

MySQL 常用命令大全

1. mysql：串連資料庫

mysql 命令使用者串連資料庫。 mysql 命令格式： mysql -h 主機地址 -u 使用者名稱 －p 使用者密碼

1) 串連到本機上的 MYSQL 首先開啟 DOS 視窗，然後進入目錄 mysql\bin，再鍵入命令 mysql -u root -p，斷行符號後提示你

輸密碼。注意使用者名稱前可以有空格也可以沒有空格，但是密碼前必須沒有空格，否則讓你重新輸入密碼。

如果剛安裝好 MYSQL，超級使用者 root 是沒有密碼的，故直接斷行符號即可進入到 MYSQL 中了，

MYSQL 的提示符是： mysql> 2) 串連到遠程主機上的 MYSQL

假設遠程主機的 IP 為：110.110.110.110，使用者名稱為 root，密碼為 abcd123。則鍵入以下命令： mysql -h110.110.110.110 -u root -p 123;（註：u 與 root 之間可以不用加空格，其它也一樣）

3) 退出 MYSQL 命令 exit （斷行符號）

2. mysqladmin：修改使用者密碼

mysqladmin 命令用於修改使用者密碼。

mysqladmin 命令格式：mysqladmin -u 使用者名稱 -p 舊密碼 password 新密碼

1) 給 root 加個密碼 ab12

首先在 DOS 下進入目錄 mysql\bin，然後鍵入以下命令：

mysqladmin -u root -password ab12

註：因為開始時 root 沒有密碼，所以-p 舊密碼一項就可以省略了。

2) 再將 root 的密碼改為 djg345

mysqladmin -u root -p ab12 password djg345

3. grant on：新增使用者

grant on 命令用於增加新使用者並控制其許可權。

grant on 命令格式：grant select on 資料庫.* to 使用者名稱@登入主機 identified by “密碼”;

1) 增加一個使用者 test1，密碼為 abc，讓他可以在任何主機上登入，並對所有資料庫有查詢、插入、修改、刪除的許可權。首先用 root 使用者連入 MYSQL，然後鍵入以下命令：

grant select,insert,update,delete on *.* to [[email protected]”%][email protected]”%[/email]” Identified by “abc”;

但增加的使用者是十分危險的，你想如某個人知道 test1 的密碼，那麼他就可以在 internet 上

的任何一台電腦上登入你的 mysql 資料庫並對你的資料可以為所欲為了，解決辦法如下。

2) 增加一個使用者 test2 密碼為 abc，讓他只可以在 localhost 上登入，並可以對資料庫 mydb 進行查詢、插入、修改、刪除的操作（localhost 指本地主機，即 MYSQL 資料庫所在的那台主機），這樣使用者即使用知道 test2 的密碼，他也無法從 internet 上直接存取資料庫，只能通過 MYSQL 主機上的 web 頁來訪問了。

grant select,insert,update,delete on mydb.* to [[email protected]][email protected][/email] identified by “abc”; 如果你不想 test2 有密碼，可以再打一個命令將密碼消掉。 grant select,insert,update,delete on mydb.* to [[email protected]][email protected][/email] identified by “”;

4. create：建立資料庫

create 命令用於建立資料庫。

create 命令格式：create database <資料庫名>; 注意：建立資料庫之前要先串連 Mysql 伺服器。

1) 建立一個名為 xhkdb 的資料庫：

mysql> create database xhkdb;

2) 建立資料庫並分配使用者：

a：CREATE DATABASE 資料庫名;

b：GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER ON 資料庫名.* TO

資料庫名@localhost IDENTIFIED BY ‘密碼‘; c:SET PASSWORD FOR ‘資料庫名‘@‘localhost‘ = OLD_PASSWORD(‘密碼‘); 依次執行 3 個命令完成資料庫建立。

注意：中文 “密碼”和“資料庫”是戶自己需要設定的。

5. show databases：顯示資料庫

show databases 命令用於顯示所有資料庫。

show databases 命令格式：show databases; （注意：最後有個 s）例如：mysql> show databases;

6. drop database：刪除資料庫

drop 命令用於刪除資料庫。

drop 命令格式：drop database <資料庫名>; 例如，刪除名為 xhkdb 的資料庫：

mysql> drop database xhkdb;

[例子 1] 刪除一個已經確定存在的資料庫：

mysql> drop database drop_database; Query OK, 0 rows affected (0.00 sec)

[例子 2] 刪除一個不確定存在的資料庫：

mysql> drop database drop_database; ERROR 1008 (HY000): Can‘t drop database ‘drop_database‘; database doesn‘t exist // 發生錯誤，不能刪除‘drop_database‘資料庫，該資料庫不存在。 mysql> drop database if exists drop_database; Query OK, 0 rows affected, 1 warning (0.00 sec) //產生一個警告說明此資料庫不存在 mysql> create database drop_database; // 建立一個資料庫 Query OK, 1 row affected (0.00 sec) mysql> drop database if exists drop_database; // if exists 判斷資料庫是否存在，不存在也不產生錯誤 Query OK, 0 rows affected (0.00 sec)

7. use：使用資料庫

use 命令可以讓我們來使用資料庫。

use 命令格式： use <資料庫名>;

例如，如果 xhkdb 資料庫存在，嘗試存取它：

mysql> use xhkdb;

工具提示：Database changed

1) use 語句可以通告 MySQL 把 db_name 資料庫作為預設（當前）資料庫使用，用於後續語句。該資料庫保持為預設資料庫，直到語段的結尾，或者直到出現下一個不同的 use 語句：

mysql> USE db1;

mysql> SELECT COUNT(*) FROM mytable; # selects from db1.mytable mysql> USE db2;

mysql> SELECT COUNT(*) FROM mytable; # selects from db2.mytable

2) 使用 USE 語句為一個特定的當前的資料庫做標記，不會阻礙您訪問其它資料庫中的表。下面的例子可以從 db1 資料庫訪問 author 表，並從 db2 資料庫訪問 editor 表：

mysql> USE db1; mysql> SELECT author_name,editor_name FROM author,db2.editor -> WHERE author.editor_id = db2.editor.editor_id;

use 語句被設立出來，用於與 Sybase 相相容。 有些網友問到，串連以後怎麼退出。其實，不用退出來，use 資料庫後，使用 show databases

就能查詢所有資料庫，如果想跳到其他資料庫，用 use 其他資料庫名字就可以了。

8. select：當前串連的資料庫

select 命令表示當前選擇（串連）的資料庫。

select 命令格式：mysql> select database();

MySQL 中 SELECT 命令類似於其他程式設計語言裡的 print 或者 write，你可以用它來顯示一個字串、數字、數學運算式的結果等等。如何使用 MySQL 中 SELECT 命令的特殊功能呢？

1) 顯示 MYSQL 的版本

mysql> select version(); +-----------------------+

| version() |

+-----------------------+

| 6.0.4-alpha-community |

+-----------------------+

1 row in set (0.02 sec)

2) 顯示目前時間

mysql> select now();

+---------------------+

| now() |

+---------------------+

| 2009-09-15 22:35:32 |

+---------------------+

1 row in set (0.04 sec)

3) 顯示年月日

SELECT DAYOFMONTH(CURRENT_DATE);

+--------------------------+

| DAYOFMONTH(CURRENT_DATE) |

+--------------------------+

| 15 |

+--------------------------+

1 row in set (0.01 sec)

SELECT MONTH(CURRENT_DATE);

+---------------------+

| MONTH(CURRENT_DATE) |

+---------------------+

| 9 |

+---------------------+

1 row in set (0.00 sec)

SELECT YEAR(CURRENT_DATE);

+--------------------+

| YEAR(CURRENT_DATE) |

+--------------------+

| 2009 |

+--------------------+

1 row in set (0.00 sec)

4) 顯示字串

mysql> SELECT "welecome to my blog!";

+----------------------+

| welecome to my blog! |

+----------------------+

| welecome to my blog! |

+----------------------+

1 row in set (0.00 sec)

5) 當計算機用

select ((4 * 4) / 10 ) + 25; +----------------------+

| ((4 * 4) / 10 ) + 25 |

+----------------------+

| 26.60 |

+----------------------+

1 row in set (0.00 sec)

6) 串接字串

select CONCAT(f_name, " ", l_name) AS Name from employee_data where title = ‘Marketing Executive‘;

+---------------+

| Name |

+---------------+

| Monica Sehgal | | Hal Simlai |

| Joseph Irvine |

+---------------+

3 rows in set (0.00 sec)

注意：這裡用到 CONCAT()函數，用來把字串串接起來。另外，我們還用到以前學到的 AS 給結果列‘CONCAT(f_name, " ", l_name)‘起了個假名。

9. create table：建立資料表

資料表屬於資料庫，在建立資料表之前，應該使用語句“USE <資料庫名>”指定操作是在哪個資料庫中進行，如果沒有選擇資料庫，會拋出“No database selected”的錯誤。建立資料表的語句為 CREATE TABLE，文法規則如下：

CREATE TABLE <表名> ( 欄位名 1，資料類型 [列層級約束條件] [預設值], 欄位名 2，資料類型 [列層級約束條件] [預設值], ?? [表層級約束條件] );

使用 CREATE TABLE 建立表時，必須指定以下資訊：

（1） 要建立的表的名稱，不區分大小寫，不能使用 SQL 語言中的關鍵字，如 DROP、ALTER、

INSERT 等。

（2） 資料表中每一個列（欄位）的名稱和資料類型，如果建立多個列，要用逗號隔開。

建立員工表 tb_emp1，結構如下表所示。

表 tb_emp1 表結構

欄位名稱	資料類型	備忘
id	INT(11)	員工編號
name	VARCHAR(25)	員工名稱
deptId	INT(11)	所在部門編號
salary	FLOAT	工資

首先建立資料庫，SQL 陳述式如下：

CREATE DATABASE test_db;

選擇建立表的資料庫，SQL 陳述式如下：

USE test_db;

建立 tb_emp1 表，SQL 陳述式為：

CREATE TABLE tb_emp1

(

id INT(11), name VARCHAR(25), deptId INT(11), salary FLOAT );

語句執行後，便建立了一個名稱為 tb_emp1 的資料表，使用 SHOW TABLES；語句查看資料

表是否建立成功，SQL 陳述式如下：

mysql> SHOW TABLES;

+-----------------------+

| Tables_in_ test_db |

+----------------------+

| tb_emp1 |

+----------------------+

1 row in set (0.00 sec)

可以看到，test_db 資料庫中已經有了資料表 tb_tmp1，資料表建立成功。

10. desc：擷取表結構

在 MySQL 中，查看錶結構可以使用 DESCRIBE 和 SHOW CREATE TABLE 語句。

DESCRIBE/DESC 語句可以查看錶的欄位資訊，其中包括：欄位名、欄位資料類型、是否為主鍵、是否有預設值等。文法規則如下：

DESCRIBE 表名;

或者簡寫為：

DESC 表名;

SHOW CREATE TABLE 語句可以用來顯示建立表時的 CREATE TABLE 語句，文法格式如下：

SHOW CREATE TABLE <表名\G>;

使用 SHOW CREATE TABLE 語句，不僅可以查看錶建立時候的詳細語句，而且還可以查

看儲存引擎和字元編碼。

如果不加’\G’參數，顯示的結果可能非常混亂，加上參數’\G’之後，可使顯示結果更加直觀，

易於查看。使用 SHOW CREATE TABLE 查看錶 tb_emp1 的詳細資料，SQL 陳述式如下：

mysql> SHOW CREATE TABLE tb_emp1;

11. drop table：刪除資料表

在 MySQL 中，使用 DROP TABLE 可以一次刪除一個或多個沒有被其他表關聯的資料表。語

法格式如下：

DROP TABLE [IF EXISTS]表 1, 表 2, . . . 表 n;

其中“表 n”指要刪除的表的名稱，後面可以同時刪除多個表，只需將要刪除的表名依次寫在

後面，相互之間用逗號隔開即可。如果要刪除的資料表不存在，則 MySQL 會提示一條錯誤資訊，

“ERROR 1051 (42S02): Unknown table ‘表名‘”。參數“IF EXISTS”用於在刪除前判斷刪除的表是否存在，加上該參數後，再刪除表的時候，如果表不存在，SQL 陳述式可以順利執行，但是會發出警告（warning）。

在前面的例子中，已經建立了名為 tb_dept2 的資料表。如果沒有，讀者可輸入語句，建立該

表，SQL 陳述式如例 4.8 所示。下面使用刪除語句將該表刪除。刪除資料表 tb_dept2，SQL 陳述式如下：

DROP TABLE IF EXISTS tb_dept2;

12. insert into：向表中插入資料

INSERT INTO 語句用於向表格中插入新的行。文法如下：

INSERT INTO 表名稱 VALUES (值 1, 值 2,....)

我們也可以指定所要插入資料的列：

INSERT INTO table_name (列 1, 列 2,...) VALUES (值 1, 值 2,....)

【例】建立資料表 tmp3，定義資料類型為 YEAR 的欄位 y，向表中插入值 2010，’2010’，SQL 陳述式如下：首先建立表 tmp3：

CREATE TABLE tmp3( y YEAR );

向表中插入資料：

mysql> INSERT INTO tmp3 values(2010),(‘2010‘);

13. select from：查詢表中資料

MySQL 從資料表中查詢資料的基本語句為 SELECT 語句。SELECT 語句的基本格式是：

SELECT

{* | <欄位列表>}

[

FROM <表 1>,<表 2>...

[WHERE <運算式>

[GROUP BY <group by definition>]

[HAVING <expression> [{<operator> <expression>}...]]

[ORDER BY <order by definition>]

[LIMIT [<offset>,] <row count>]

]

SELECT [欄位 1,欄位 2,…,欄位 n]

FROM [表或視圖]

WHERE [查詢條件];

其中，各條子句的含義如下：

{* | <欄位列表>}包含星號萬用字元選欄位列表，表示查詢的欄位，其中欄位列至少包含一個欄位名稱，如果要查詢多個欄位，多個欄位之間用逗號隔開，最後一個欄位後不要加逗號。

FROM <表 1>,<表 2>...，表 1 和表 2 表示查詢資料的來源，可以是單個或者多個。

WHERE 子句是可選項，如果選擇該項，將限定查詢行必須滿足的查詢條件。

GROUP BY <欄位>，該子句告訴 MySQL 如何顯示查詢出來的資料，並按照指定的欄位分組。

[ORDER BY <欄位 >]，該子句告訴 MySQL 按什麼樣的順序顯示查詢出來的資料，可以進行的排序有：升序(ASC)、降序（DESC）。

[LIMIT [<offset>,] <row count>]，該子句告訴 MySQL 每次顯示查詢出來的資料條數。

14. delete from：刪除記錄

從資料表中刪除資料使用 DELETE 語句，DELETE 語句允許 WHERE 子句指定刪除條件。 DELETE 語句基本文法格式如下：

DELETE FROM table_name [WHERE <condition>];

table_name 指定要執行刪除操作的表；“[WHERE <condition>]”為選擇性參數，指定刪除條件，

如果沒有 WHERE 子句，DELETE 語句將刪除表中的所有記錄。

【例】在 person 表中，刪除 id 等於 11 的記錄，SQL 陳述式如下：

mysql> DELETE FROM person WHERE id = 11; Query OK, 1 row affected (0.02 sec)

15. update set：修改表中的資料

MySQL 中使用 UPDATE 語句更新表中的記錄，可以更新特定的行或者同時更新所有的行。

基本文法結構如下：

UPDATE table_name

SET column_name1 = value1,column_name2=value2,……,column_namen=valuen WHERE (condition);

column_name1,column_name2,……,column_namen 為指定更新的欄位的名稱； value1,

value2,……valuen 為相對應的指定欄位的更新值；condition 指定更新的記錄需要滿足的條件。更新多個列時，每個“列-值”對之間用逗號隔開，最後一列之後不需要逗號。

【例】在 person 表中，更新 id 值為 11 的記錄，將 age 欄位值改為 15，將 name 欄位值改為

LiMing，SQL 陳述式如下：

UPDATE person SET age = 15, name=’LiMing’ WHERE id = 11;

16. alter add：增加欄位

添加欄位的文法格式如下：

ALTER TABLE <表名> ADD <新欄位名> <資料類型> [約束條件] [FIRST | AFTER 已存在欄位名];

新欄位名為需要添加的欄位的名稱；“FIRST”為選擇性參數，其作用是將新添加的欄位設定為表的第一個欄位；“AFTER”為選擇性參數，其作用是將新添加的欄位添加到指定的“已存在欄位名”的後面。

【例】在資料表 tb_dept1 中添加一個沒有完整性條件約束的 INT 類型的欄位 managerId（部門經理編號），SQL 陳述式如下：

ALTER TABLE tb_dept1 ADD managerId INT(10);

17. rename：修改表名

MySQL 是通過 ALTER TABLE 語句來實現表名的修改的，具體的文法規則如下：

ALTER TABLE <舊錶名> RENAME [TO] <新表名>;

其中 TO 為選擇性參數，使用與否均不影響結果。

【例】將資料表 tb_dept3 改名為 tb_deptment3，SQL 陳述式如下：

ALTER TABLE tb_dept3 RENAME tb_deptment3;

18. mysqldump：備份資料庫

mysqldump 備份資料庫語句的基本文法格式如下：

mysqldump –u user –h host –ppassword dbname[tbname, [tbname...]]> filename.sql

user 表示使用者名稱稱；host 表示登入使用者的主機名稱；password 為登入密碼；dbname 為需要備份的資料庫名稱；tbname 為 dbname 資料庫中需要備份的資料表，可以指定多個需要備份的表；右箭頭符號“>”告訴 mysqldump 將備份資料表的定義和資料寫入備份檔案；filename.sql 為備份檔案的名稱。

【例】使用 mysqldump 命令備份資料庫中的所有表，執行過程如下：開啟作業系統命令列輸入視窗，輸入備份命令如下：

C:\ >mysqldump -u root -p booksdb > C:/backup/booksdb_20130301.sql

Enter password: **

輸入密碼之後，MySQL 便對資料庫進行了備份，在 C:\backup 檔案夾下面查看剛才備份過的檔案。

19. mysql 和 source：還原資料庫

對於已經備份的包含 CREATE、INSERT 語句的文字檔，可以使用 mysql 命令匯入到資料庫

中。

備份的 sql 檔案中包含 CREATE、INSERT 語句（有時也會有 DROP 語句）。mysql 命令可以

直接執行檔案中的這些語句。其文法如下：

mysql –u user –p [dbname] < filename.sql

user 是執行 backup.sql 中語句的使用者名稱；-p 表示輸入使用者密碼；dbname 是資料庫名。如果 filename.sql 檔案為 mysqldump 工具建立的包含建立資料庫語句的檔案，執行的時候不需要指定資料庫名。

【例 1】使用 mysql 命令將 C:\backup\booksdb_20130301.sql 檔案中的備份匯入到資料庫中，輸入語句如下：

mysql –u root –p booksDB < C:/backup/booksdb_20130301.sql

執行該語句前，必須先在 MySQL 伺服器中建立 booksDB 資料庫，如果不存在恢複過程將會出錯。命令執行成功之後 booksdb_20130301.sql 檔案中的語句就會在指定的資料庫中恢複以前的表。如果已經登入 MySQL 伺服器，還可以使用 source 命令匯入 sql 檔案。source 語句文法如下：

source filename

【例 2 】使用 root 使用者登入到伺服器，然後使用 source 匯入本地的備份檔案

booksdb_20110101.sql，輸入語句如下：

--選擇要恢複到的資料庫 mysql> use booksDB; Database changed --使用 source 命令匯入備份檔案 mysql> source C:\backup\booksDB_20130301.sql

命令執行後，會列出備份檔案 booksDB_20130301.sql 中每一條語句的執行結果。source 命令執行成功後，booksDB_20130301.sql 中的語句會全部匯入到現有資料庫中。

20. mysqlhotcopy：快速恢複資料庫

mysqlhotcopy 備份後的檔案也可以用來恢複資料庫，在 MySQL 伺服器停止運行時，將備份的資料庫檔案複製到 MySQL 存放資料的位置（MySQL 的 data 檔案夾），重新啟動 MySQL 服務即可。如果以根使用者執行該操作，必須指定資料庫檔案的所有者，輸入語句如下：

chown -R mysql.mysql /var/lib/mysql/dbname

【例】從 mysqlhotcopy 複製的備份恢複資料庫，輸入語句如下：

cp -R /usr/backup/test usr/local/mysql/data

執行完該語句，重啟伺服器，MySQL 將恢複到備份狀態。

MySQL常用命令