---

前言

MySQL 是一種開放源代碼的關系型數據庫管理系統（RDBMS），使用最常用的數據庫管理語言--結構化查詢語言（SQL）進行數據庫管理。MySQL 是開放源代碼的，因此任何人都可以在 General Public License 的許可下下載并根據個性化的需要對其進行修改。MySQL 因為其速度、可靠性和適應性而備受關注。大多數人都認為在不需要事務化處理的情況下，MySQL 是管理內容最好的選擇。

---

前文回顧：《MySQL 入門教程：初學者一學就會，零基礎 MySQL 安裝及增刪改查入門》

一、連接到 MySQL 數據庫

連接到 MySQL 數據庫，基本形式如下：

mysql -h主機地址 -u用戶名 －p用戶密碼

1.1、連接到本機上的 MySQL

首先打開 DOS 窗口，然后進入目錄 mysql\bin，再鍵入命令 mysql -u root -p，回車后提示你輸密碼。注意用戶名前可以有空格也可以沒有空格，但是密碼前必須沒有空格，否則讓你重新輸入密碼。

如果剛安裝好 MySQL，超級用戶 root 是沒有密碼的，故直接回車即可進入到 MySQL 中了，MySQL 的提示符如下：

mysql>

1.2、連接到遠程主機上的 MySQL

假設遠程主機的 IP 為：110.110.110.110，用戶名為 root，密碼為 abcd123，則輸入如下命令：

mysql -h110.110.110.110 -u root -pabcd123

注意：u 與 root 之間可以不用加空格，其它也一樣。

二、退出 MySQL 命令

退出 MySQL，實現命令如下：

exit

三、修改 MySQL 密碼

修改 MySQL 密碼，基本形式如下：

mysqladmin -u用戶名 -p舊密碼 password 新密碼

3.1、先給 root 用戶加個密碼 ab12

首先在 DOS 下進入目錄 mysql\bin，然后輸入如下命令：

mysqladmin -u root password ab12

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

3.2、再將 root 用戶的密碼改為 djg345

mysqladmin -u root -pab12 password djg345

四、增加新用戶

注意：和上面不同，下面的因為是 MySQL 環境中的命令，所以后面都帶一個分號作為命令結束符。

增加新用戶，基本形式如下：

grant select on 數據庫.* to 用戶名@登錄主機 identified by “密碼”;

4.1、增加一個可以在任何主機上登錄用戶 test1，密碼為 abc，并對所有數據庫有查詢、插入、修改、刪除的權限

首先用 root 用戶連入MySQL，然后鍵入以下命令：

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

注意：增加的用戶是十分危險的，你想如某個人知道 test1 的密碼，那么他就可以在 internet 上的任何一臺電腦上登錄你的 MySQL 數據庫并對你的數據可以為所欲為了，解決辦法見 4.2。

4.2、增加一個只可以在 localhost 上登錄用戶 test2，密碼為 abc，并可以對數據庫 mydb 進行查詢、插入、修改、刪除的操作

（localhost 指本地主機，即 MySQL 數據庫所在的那臺主機），這樣用戶即使用知道 test2 的密碼，他也無法從 internet 上直接訪問數據庫，只能通過 MySQL 主機上的 web 頁來訪問了。

grant select,insert,update,delete on mydb.* to [email=test2@localhost]test2@localhost[/email] identified by “abc”;

如果你不想 test2 有密碼，可以再打一個命令將密碼消掉，實現命令如下：

grant select,insert,update,delete on mydb.* to [email=test2@localhost]test2@localhost[/email] identified by “”;

五、創建新的數據庫

創建數據庫之前要先連接 MySQL 服務器，基本形式如下：

create database <數據庫名>;

例如：創建一個名為 xhkdb 的數據庫，實現命令如下：

mysql> create database xhkdb;

例如：創建數據庫并分配用戶，實現命令如下：

CREATE DATABASE 數據庫名;
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER ON 數據庫名.* TO 數據庫名@localhost IDENTIFIED BY '密碼';
SET PASSWORD FOR '數據庫名'@'localhost' = OLD_PASSWORD('密碼');

依次執行 3 個命令完成數據庫創建，在這里中文 “密碼”和“數據庫”是戶自己需要設置的。

六、顯示存在的數據庫與編碼設置

顯示存在的數據庫，實現命令如下（注意：最后有個 s）：

show databases;

為了不再顯示的時候亂碼，要修改數據庫默認編碼，我們以 GBK 編碼頁面為例進行說明：

• 修改 MySQL 的配置文件：my.ini 里面修改 default-character-set=gbk。
• 代碼運行時修改：

①Java 代碼：

jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=gbk;

②PHP 代碼：

header("Content-Type:text/html;charset=gb2312");

③C 語言代碼：

int mysql_set_character_set( MYSQL * mysql, char * csname);

該函數用于為當前連接設置默認的字符集。字符串 csname 指定了1個有效的字符集名稱。連接校對成為字符集的默認校對。該函數的工作方式與 SET NAMES 語句類似，但它還能設置mysql- > charset的值，從而影響了由mysql_real_escape_string() 設置的字符集。

七、刪除數據庫

刪除數據庫，基本形式如下：

drop database <數據庫名>;

例如：刪除名為 xhkdb 的數據庫，實現命令如下：

mysql> drop database xhkdb;

例如：刪除一個已經確定存在的數據庫 drop_database，實現命令如下：

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

例如：刪除一個不確定存在的數據庫，實現命令如下：

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)

八、連接數據庫

連接數據庫，基本形式如下：

use <數據庫名>;

例如：如果 xhkdb 數據庫存在，嘗試存取它，實現代碼如下：

mysql> use xhkdb;

這個時候屏幕會出現如下提示：

Database changed

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

使用 USE 語句為一個特定的當前的數據庫做標記，不會阻礙您訪問其它數據庫中的表。下面的例子可以從 db1 數據庫訪問作者表，并從 db2 數據庫訪問編輯表，命令如下：

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

可能有些同學會有疑問，連接以后怎么退出，其實不用退出來，use 數據庫后，使用 show databases 就能查詢所有數據庫，如果想跳到其他數據庫，用 use 其他數據庫名字 就可以了。

九、查看當前選擇的數據庫

查看當前選擇的數據庫，實現命令如下：

mysql> select database();

MySQL 中 SELECT 命令類似于其他編程語言里的 print 或者 write，你可以用它來顯示一個字符串、數字、數學表達式的結果等等。那么我們如何使用 MySQL 中 SELECT命令的特殊功能？

9.1、查看顯示 MySQL 的版本

我們可以使用 select 命令查看顯示 MySQL 的版本，實現命令如下：

mysql> select version(); 
+-----------------------+ 
| version()             | 
+-----------------------+ 
| 6.0.4-alpha-community | 
+-----------------------+ 
1 row in set (0.02 sec) 

9.2、查看顯示當前時間

我們可以使用 select 命令查看顯示當前時間，實現命令如下：

mysql> select now(); 
+---------------------+ 
| now()               | 
+---------------------+ 
| 2009-09-15 22:35:32 | 
+---------------------+ 
1 row in set (0.04 sec) 

9.3、查看顯示當前年月日

我們可以使用 select 命令查看顯示當前年月日，實現命令如下：

SELECT DAYOFMONTH(CURRENT_DATE); 
+--------------------------+ 
| DAYOFMONTH(CURRENT_DATE) | 
+--------------------------+ 
|                       15 | 
+--------------------------+ 
1 row in set (0.01 sec) 

SELECT MONTH(CURRENT_DATE); 
+---------------------+ 
| MONTH(CURRENT_DATE) | 
+---------------------+ 
|                   1 | 
+---------------------+ 
1 row in set (0.00 sec) 

SELECT YEAR(CURRENT_DATE); 
+--------------------+ 
| YEAR(CURRENT_DATE) | 
+--------------------+ 
|               2020 | 
+--------------------+ 
1 row in set (0.00 sec) 

9.4、查看顯示字符串

我們可以使用 select 命令查看顯示字符串，實現命令如下：

mysql> SELECT "welecome to my blog!"; 
+----------------------+ 
| welecome to my blog! | 
+----------------------+ 
| welecome to my blog! | 
+----------------------+ 
1 row in set (0.00 sec) 

9.5、當計算器用

我們可以使用 select 命令當計算器用，實現命令如下：

select ((4 * 4) / 10 ) + 25; 
+----------------------+ 
| ((4 * 4) / 10 ) + 25 | 
+----------------------+ 
|                26.60 | 
+----------------------+ 
1 row in set (0.00 sec) 

9.6、串接字符串

我們可以使用 select 命令串接字符串，實現命令如下：

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)' 起了個列別名。

十、創建數據表

創建數據表，基本形式如下：

create table <表名> ( <字段名1> <類型1> [,..<字段名n> <類型n>]);

例如：建立一個名為 MyClass 的表，具體如下表所示：

字段名	數字類型	數據寬度	是否可以為空	是否主鍵	自動增加	默認值
id	int	4	否	primary key	auto_increment	0
name	char	20	否
sex	int	4	否
degree	double	16	是

實現命令如下：

mysql> create table MyClass(> id int(4) not null primary key auto_increment,
> name char(20) not null,
> sex int(4) not null default '0',
> degree double(16,2));

十一、查看表的字段信息

查看表的字段信息，基本形式如下：

mysql> describe table_name;

十二、刪除數據表

刪除數據表，基本形式如下：

drop table <表名>;

例如：刪除表名為 MyClass 的表，實現命令如下：

mysql> drop table MyClass;

注意：DROP TABLE 用于取消一個或多個表。您必須有每個表的 DROP 權限。所有的表數據和表定義會被取消，所以使用本語句要小心！

注意：

• 對于一個帶分區的表，DROP TABLE 會永久性地取消表定義，取消各分區，并取消儲存在這些分區中的所有數據。DROP TABLE 還會取消與被取消的表有關聯的分區定義（.par）文件。
• 對與不存在的表，使用 IF EXISTS 用于防止錯誤發生。當使用 IF EXISTS 時，對于每個不存在的表，會生成一個NOTE。
• RESTRICT 和 CASCADE 可以使分區更容易。目前，RESTRICT 和 CASCADE 不起作用。

十三、在表中插入數據

在表中插入數據，基本形式如下：

insert [into] 表名 [(列名1, 列名2, 列名3, ...)] values (值1, 值2, 值3, ...);

說明：其中 [] 內的內容是可選的，當向表中所有列插入數據時，并且插如的數據值和表中列的定義順序完全相同時，可以缺省[]。

例如：往表 MyClass 中所有列插入二條的記錄，這二條記錄表示：編號為 1 的名為 Tom 的成績為 96.45，編號為 2 的名為 Joan 的成績為 82.99，編號為 3 的名為 Wang 的成績為 96.5，實現命令如下：

mysql> insert into MyClass values(1,'Tom',1,96.45),(2,'Joan',1,82.99), (3,'Wang', 0,96.59);

按回車鍵確認后若提示 Query Ok, 1 row affected (0.05 sec) 表示數據插入成功。若插入失敗請檢查是否已選擇需要操作的數據庫。

13.1、設置某些屬性的值為空

在插入的元組中，設置某些屬性的值為空，基本形式如下：

insert into 表名 values(值1，值2，NULL，值3，…);

表明插入的記錄的第三列的取值為 NULL。

13.2、為指定列插入數據

有時我們要為指定列插入數據，或者不按照列的順序進行插入，則表名后必須制定要插入的列名，基本形式如下:

insert into student (Sno,Sname, Ssex) values(‘95007’,’孫麗華’, ‘女’);

這時 student 中沒有插入具體值的屬性的取值為空，默認 NULL。

十四、查詢表中的數據

14.1、查詢所有行數據

查詢所有行數據，基本形式如下：

select <字段1，字段2，...> from < 表名 > where < 表達式 >;

例如：查看表 MyClass 中所有數據，實現命令如下：

 mysql> select * from MyClass;

14.2、查詢前幾行數據（分頁查詢）

例如：查看表 MyClass 中前 2 行數據，實現命令如下：

mysql> select * from MyClass order by id limit 0,2;

說明：表的元組從 0 開始編號，limit 的格式為 limit a,b，表明從第 a 個元組開始顯示，總共顯示 b 個元組。

十五、刪除表中數據

刪除表中數據，基本形式如下：

delete from 表名 where 表達式;

說明：select 一般配合 where 使用，以查詢更精確更復雜的數據。

例如：刪除表 MyClass 中編號為 1 的記錄，實現命令如下：

mysql> delete from MyClass where id=1;

例如：下面以 PHP 代碼為例刪除 “Persons” 表中所有 LastName=‘Griffin’ 的記錄，在刪除之前，表具體如下所示：

FirstName	LastName	Age
Peter	Griffin	35
Glenn	Quagmire	33

實現命令如下：

<?php $con = mysql_connect("localhost","peter","abc123"); if (!$con) {die('Could not connect: ' . mysql_error()); } mysql_select_db("my_db", $con); mysql_query("DELETE FROM Persons WHERE LastName='Griffin'"); mysql_close($con); 
?>

在這次刪除之后，表具體如下表所示：

FirstName	LastName	Age
Glenn	Quagmire	33

十六、修改表中數據

修改表中數據，基本形式如下：

update 表名 set 字段=新值,… where 條件;

例如：

mysql> update MyClass set name='Mary' where id=1;

16.1、單表的 MySQL UPDATE 語句

單表的 MySQL UPDATE 語句，基本形式如下：

UPDATE [LOW_PRIORITY] [IGNORE] tbl_name SET col_name1=expr1 [, col_name2=expr2 ...] [WHERE where_definition] [ORDER BY ...] [LIMIT row_count]

16.2、多表的 MySQL UPDATE 語句

多表的 MySQL UPDATE 語句，基本形式如下：

UPDATE [LOW_PRIORITY] [IGNORE] table_references SET col_name1=expr1 [, col_name2=expr2 ...] [WHERE where_definition]

• UPDATE 語法可以用新值更新原有表行中的各列。
• SET 子句指示要修改哪些列和要給予哪些值。
• WHERE 子句指定應更新哪些行。如果沒有 WHERE 子句，則更新所有的行。
• 如果指定了 ORDER BY 子句，則按照被指定的順序對行進行更新。
• LIMIT 子句用于給定一個限值，限制可以被更新的行的數目。

十七、增加字段

增加字段，基本形式如下：

alter table 表名 add字段 類型 其他;

例如：在表 MyClass 中添加了一個字段 passtest，類型為 int(4)，默認值為 0，實現命令如下：

mysql> alter table MyClass add passtest int(4) default '0';

17.1、增加索引

增加索引，基本形式如下：

mysql> alter table 表名 add index 索引名 (字段名1[，字段名2 …]);

例如：

mysql> alter table employee add index emp_name (name);

17.1.1、加主關鍵字的索引

加主關鍵字的索引，基本形式如下：

mysql> alter table 表名 add primary key (字段名);

例如：

mysql> alter table employee add primary key(id);

17.1.2、加唯一限制條件的索引

加唯一限制條件的索引，基本形式如下：

mysql> alter table 表名 add unique 索引名 (字段名);

例如：

mysql> alter table employee add unique emp_name2(cardnumber);

17.2、刪除某個索引

刪除某個索引，基本形式如下：

mysql> alter table 表名 drop index 索引名;

例如：

mysql>alter table employee drop index emp_name;

17.3、對字段的操作

17.3.1、增加字段

增加字段，基本形式如下：

mysql> ALTER TABLE table_name ADD field_name field_type;

17.3.2、修改原字段名稱及類型

修改原字段名稱及類型，基本形式如下：

mysql> ALTER TABLE table_name CHANGE old_field_name new_field_name field_type;

17.3.3、刪除字段

刪除字段，基本形式如下：

mysql> ALTER TABLE table_name DROP field_name;

十八、修改表名

修改表名，基本形式如下：

rename table 原表名 to 新表名;

例如：將表 MyClass 名字更改為 YouClass，實現命令如下：

mysql> rename table MyClass to YouClass;

RENAME TABLE 在 MySQL 3.23.23 中被加入。當你執行 RENAME 時，你不能有任何鎖定的表或活動的事務。你同樣也必須有對原初表的 ALTER 和 DROP 權限，以及對新表的 CREATE 和 INSERT 權限。如果在多表更名中，MySQL 遭遇到任何錯誤，它將對所有被更名的表進行倒退更名，將每件事物退回到最初狀態。

十九、修改表的定義

MySQL 用 alter 關鍵字修改表名，列名，列類型，添加表列，刪除表列。

1)alter table 舊表名 rename 新表名; --修改表名  
2)alter table舊表名add  column 新列名 列的類型; --添加表列  
3)alter table舊表名drop  column 列名; --刪除表列  
4)alter table舊表名modify 列名 要修改的類型 --修改表列類型  
或alter table test change列名 列名 要修改的類型 
5)alter table test change column 舊列名 新列名 列的類型--修改表列名

二十、備份數據庫

20.1、導出整個數據庫

命令在 DOS的[url=file://\mysql\bin] 目錄下執行，導出文件默認是存在 mysql\bin 目錄下，實現命令如下：

mysqldump -u 用戶名 –p用戶密碼 數據庫名 > 導出的文件名
mysqldump -u user_name -p123456 database_name > outfile_name.sql （句末沒有分號）

20.2、在命令行導出 MySQL 數據到 excel 表

有時候需要將 MySQL 的數據導出成 excel，這很簡單，無需第三方工具，直接 MySQL 命令行就自帶了這樣的功能。命令需在進入 MySQL，在 MySQL 提示符 mysql> 下操作，并選定表所屬的數據庫，基本形式如下：

SELECT * FROM table_name into outfile 'D:\\nowamagic.xls';

說明：雙斜杠是帶轉義識別目錄。

當然也可以加入篩選條件，將特定的列篩選出來，導出成 excel，比如：

SELECT Sname, Sno FROM Student WHERE Ssex = '男' into outfile 'D:\\Student.xls';

注意：如果你的數據是 utf-8 編碼，可能生成的 excel 文件直接打開會出現亂碼，這不要緊，用記事本打開，以 ANSI 編碼格式另存一下就 OK 了。

20.3、導出一個表

導出一個表，基本形式如下：

mysqldump -u 用戶名 -p 數據庫名 表名> 導出的文件名
mysqldump -u user_name -p database_name table_name > outfile_name.sql

20.4、導出一個數據庫結構

導出一個數據庫結構，基本形式如下：

mysqldump -u user_name -p -d –add-drop-table database_name > outfile_name.sql

說明：-d 沒有數據 –add-drop-table 在每個create語句之前增加一個drop table。

20.5、帶語言參數導出

帶語言參數導出，基本形式如下：

mysqldump -uroot -p –default-character-set=latin1 –set-charset=gbk –skip-opt database_name > outfile_name.sql

例如，將 aaa 庫備份到文件 back_aaa 中：

[root@test1 root]# cd　/home/data/mysql
[root@test1 mysql]# mysqldump -u root -p --opt aaa > back_aaa

二十一、建庫和建表的實例

21.1、實例一

drop database if exists school; //如果存在SCHOOL則刪除
create database school; //建立庫SCHOOL
use school; //打開庫SCHOOL
create table teacher //建立表TEACHER
(id int(3) auto_increment not null primary key,name char(10) not null,address varchar(50) default ‘深圳’,year date
); //建表結束//以下為插入字段
insert into teacher values(”,’allen’,'大連一中’,'1976-10-10′);
insert into teacher values(”,’jack’,'大連二中’,'1975-12-23′);

如果你在 MySQL 提示符鍵入上面的命令也可以，但不方便調試。

1. 你可以將以上命令原樣寫入一個文本文件中，假設為 school.sql，然后復制到 c:\ 下，并在 DOS 狀態進入目錄[url=file://\mysql\bin]\mysql\bin[/url]，然后鍵入以下命令：

mysql -uroot -p密碼 < c:\\school.sql

如果成功，空出一行無任何顯示；如有錯誤，會有提示。（以上命令已經調試，你只要將 // 的注釋去掉即可使用）。

2. 或者進入命令行后使用 mysql> source c:\\school.sql; 也可以將 school.sql 文件導入數據庫中。

21.2、實例二

drop database if exists school; //如果存在SCHOOL則刪除
create database school; //建立庫SCHOOL
use school; //打開庫SCHOOL
create table teacher //建立表TEACHER
(id int(3) auto_increment not null primary key,name char(10) not null,address varchar(50) default ''深圳'',year date
); //建表結束//以下為插入字段
insert into teacher values('''',''glchengang'',''深圳一中'',''1976-10-10'');
insert into teacher values('''',''jack'',''深圳一中'',''1975-12-23'');

說明：

1. 將 ID 設為長度為 3 的數字字段：int(3)；并讓它每個記錄自動加一：auto_increment；并不能為空：not null；而且讓它成為主字段 primary key。
2. 將 NAME 設為長度為 10 的字符字段。
3. 將 ADDRESS 設為長度 50 的字符字段，而且缺省值為深圳。
4. 將 YEAR 設為日期字段。

---

總結

本文系統的總結了 MySQL 數據庫的增刪改查高級命令并且對于開發中最常見的錯誤做了總結。同時附了兩個案例供大家參考，其他的關于 MySQL 數據庫的文章大家可以移步我的專欄查看。

---

我是白鹿，一個不懈奮斗的程序猿。望本文能對你有所裨益，歡迎大家的一鍵三連！若有其他問題、建議或者補充可以留言在文章下方，感謝大家的支持！