1.1. 關(guān)于 SQL

20 世紀 80 年代初，美國國家標準局（ANSI）開始著手制作 SQL 標準，并于 1986 年完成，被叫做 SQL-86。

SQL 標準幾經(jīng)修改，日趨完善，大多數(shù)關(guān)系型數(shù)據(jù)庫系統(tǒng)都支持而 (My)SQL 是在標準 SQL 的基礎(chǔ)上進行了擴展。

1.2. SQL 分類

• DDL（Data Definition Language）：數(shù)據(jù)定義語句

• 主要用于操作數(shù)據(jù)庫對象；

• 常用關(guān)鍵字 create、drop、alter等；

• DML（Data Manipulation Language）：數(shù)據(jù)操作語句

• 主要用于操作數(shù)據(jù)庫記錄;

• 常用關(guān)鍵字 insert、delete、update、select等；

• DCL（Data Control Language）：數(shù)據(jù)控制語句

• 主要用于控制數(shù)據(jù)的訪問權(quán)限；

• 常用關(guān)鍵字 grant、revoke等。

1.3. DDL

1.3.1. 創(chuàng)建數(shù)據(jù)庫

CREATE DATABASE db_name;

1.3.2. 刪除數(shù)據(jù)庫

DROP DATABASE db_name;

1.3.3. 創(chuàng)建表

CREATE TABLE table_name(
    field1_name field1_type [field1_constraints],
    field2_name field2_type [field2_constraints],
    field3_name field3_type [field3_constraints],
    ...
    [PRIMARY KEY ( field1_name ),]
    [INDEX `idx_field1_name`(`field1_name`) USING BTREE COMMENT '',]
    ...
) [table_constraints];

說明：

• field1_name 字段名，建議以下劃線分隔單詞，如 update_at。

• field1_type 字段類型

• 嚴格模式下，如果插入 6 位字符串，則提示：406 - Data too long for column xxxx。

• 非嚴格模式下，如果插入 6 位字符串，則會進行截取后成功插入數(shù)據(jù)庫，并且顯示一個 warning。

• 如果所插入數(shù)值的位數(shù)小于指定的寬度時會用空格填充；若同時使用 zerofill 約束，則會以 0 來填充。

• 注意：顯示寬度只用于顯示，并不能限制取值范圍和占用空間，每種數(shù)據(jù)類型的實際取值范圍決定數(shù)據(jù)能否最終插入數(shù)據(jù)庫。

• 如果插入的值超過字段的實際取值范圍，則提示：1264 - Out of range value for column xxxx

• int、char、varchar、float、decimal、datatime等。

• 數(shù)值型的字段后跟括號，用于設(shè)置字段的顯示寬度，如 int(11)。

• 字符型的字段后跟括號，用于設(shè)置可存放值的范圍，如：varcahr(5)

• field1_constraints 字段約束條件

• NOT NULL 不允許為空，默認允許為空。

• AUTO_INCREMENT 設(shè)置為自增列，在 MySQL 中無論 InnoDB 引擎還是 MyISAM 引擎的表，只能有一個自增列，并且自增列一定是索引列，無論是二級索引還是主鍵索引。

• DEFAULT 默認值，默認 NULL。

• COMMENT '注釋信息' 字段注釋

• unsigned 數(shù)值型字段無符號，默認有符合。

• zerofill 當所插入字段值的長度沒有達到在字段類型中設(shè)置的長度時，用 0 來補全。

• CHARACTER SET utf8mb4 設(shè)置字段的字符集。

• COLLATE utf8mb4_bin

• table_constraints 表約束條件

• MyISAM 支持全文本搜索

• InnoDB 支持事務(wù)處理

• MEMORY 類似MyISAM，但數(shù)據(jù)存儲在內(nèi)存中，速度很快（特別適合臨時表）。

• ENGINE=InnoDB 設(shè)置表的存儲引擎，默認 InnoDB，MySQL5.x 以下默認 MyISAM。

• DEFAULT CHARSET=utf8 設(shè)置默認字符集

• AUTO_INCREMENT=10 設(shè)置自增列的開始位置，查看表中自增字段的下一個值，使用 SHOW TABLE STATUS LIKE 'table_name'; 獲取字段 Auto_increment 的值。

• COMMENT '注釋信息' 表的注釋信息

查看表的定義

DESC table_name;

-- 或

SHOW CREATE TABLE table_name \G;

1.3.4. 刪除表

DROP TABLE table_name；

-- 刪除多張表，并且刪除之前先判斷是否存在
DROP TABLE IF EXISTS table1_name,table2_name,.....;

1.3.5. 修改表

注意

1. 關(guān)鍵字 CHANGE 和 FIRST | AFTER COLUMN 屬于 MySQL 在標準 SQL 上的擴展，并不一定適用于其它數(shù)據(jù)庫。

2. field_definition 至少包含字段名和字段類型。

• 修改表類型

  ALTER TABLE table_name MODIFY [COLUMN] field_definition [FIRST | AFTER field_name];
  

• 增加表字段

  ALTER TABLE table_name ADD [COLUMNM] field_definition [FIRST | AFTER field_name];
  

• 刪除表字段

  ALTER TABLE table_name DROP [COLUMN] field_name;
  

• 修改字段名

  ALTER TABLE table_name CHANGE [COLUMN] old_field_name field_definition [FIRST | AFTER field_name];
  

• 修改字段排列順序

  使用前面的可選參數(shù) [FIRST | AFTER field_name]，其中：

• ADD 新增的字段默認在表的最后位置；

• CHANGE 或 MODIFY 默認不改變字段的位置。

• 更改表名

  ALTER TABLE table_name RENAME [TO] new_table_name;
  

1.3.6. 修改表-外鍵

-- 添加
ALTER TABLE table1 ADD CONSTRAINT fk_table1_table2 FOREIGN KEY (table1) REFERENCES table2 (id);

-- 刪除
ALTER TABLE table1 DROP FOREIGN KEY fk_table1_table2;

1.3.7. 修改表-索引

這里只介紹了通過修改表結(jié)構(gòu)的方式創(chuàng)建索引，此外還有其它兩種方式（CREATE INDEX 或 在 CREATE TABLE 時指定 `），詳見后續(xù)文章索引部分。

• 普通索引

  -- 添加
  ALTER TABLE table1 ADD INDEX idx_name(field1);
  
  -- 刪除
  ALTER TABLE table1 DROP INDEX idx_name;
  

• 主鍵索引

  -- 添加
  -- 先修改要設(shè)置為主鍵的字段：無符號、非空、自增
  ALTER TABLE table1 
  MODIFY COLUMN `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  ADD PRIMARY KEY (`id`);
  
  -- 刪除
  ALTER TABLE table1 DROP PRIMARY KEY;
  

• 唯一索引

  -- 添加
  ALTER table mytable ADD UNIQUE [idx_name] (field1(length))
  
  -- 刪除
  ALTER TABLE table1 DROP UNIQUE field1;
  

• 查詢表的索引

  SHOW INDEX FROM table_name;
  

1.4. DML

1.4.1. 新增記錄（增）

-- 插入一條
INSERT INFO table_name(field1,field2,...,fieldn) VALUES(val1,val2,...,valn);

-- 插入多條
INSERT INTO table_name(field1,field2,...,fieldn)
VALUES
(record1_val1,record1_val2,...,record1_valn),
(record2_val1,record2_val2,...,record2_valn),
...
(recordn_val1,recordn_val2,...,recordn_valn);

• field1,field2,...,fieldn 可以不用寫，但是后面 VALUES 里值的順序需要與表中的字段順序保持一致；

• 如果待插入的數(shù)據(jù)，不包含以下類型的字段，可以不用寫

• 可空：自動設(shè)置為 NULL；

• 有默認值：自動設(shè)置為默認值；

• 自增：自動設(shè)置為下一個自增值。

1.4.2. 刪除記錄（刪）

DELETE FROM table_name [WHERE condition];

-- 一次刪除多張表
DELETE t1,t2,... FROM table1 t1,table2 t2,... [WHERE condition];

1.4.3. 更新記錄（改）

UPDATE table_name SET field1=val1,field2=val2,...,fieldn=valn [WHERE condition];

-- 一次更新多個表
UPDATE table1 a,table2 b, tablen n SET a.field1=val1,...,n.fieldn=valn [WHERE condition];

1.4.4. 查詢記錄（查）

• 基本

  SELECT * FROM table_name 
  [WHERE condition] 
  [ORDER BY field1 [DESC|ASC], field2 [DESC|ASC], ...]
  [LIMIT offset_start,row_count];
  

  說明：

• ASC 升序（默認）

• DESC 降序

• [WHERE condition] 過濾條件

• [ORDER BY field1 [DESC|ASC], field2 [DESC|ASC], ...] 排序

• [LIMIT offset_start,row_count] 限制

• 去重查詢

  SELECT distinct field1 FROM table_name [WHERE condition];
  

• 聚合（匯總統(tǒng)計）

  SELECT [field1,field2,...] func_name FROM table_name
  [WHERE condition]
  [GROUP BY field1,field2,...]
  [WITH ROLLUIP]
  [HAVING condition]
  

  說明：

• func_name 聚合函數(shù)，常用：sum 求和、count(*) 記錄數(shù)、max 最大值、min 最小值。

• [WHERE condition] 聚合前按條件對結(jié)果進行過濾，應(yīng)盡量使用此過濾以減少待聚合的結(jié)果集，提高聚合效率。

• [GROUP BY field1,field2,...] 分類聚合字段

• [WITH ROLLUIP] 是否對分類聚合后的結(jié)果進行再匯總。

• [HAVING condition] 對聚合后的結(jié)果再進行條件過濾。

• 內(nèi)連接

  僅獲取兩張表中互相匹配的記錄

  SELECT field1,field2 FROM table1 t1,table2 t2 
  WHERE t1.outid=t2.outid
  

  其中 field1,field2,... 分別存儲在兩張表中。

• 外連接

• 左連接

  包含所有左邊表中的記錄，甚至是右邊表中沒有和它匹配的記錄。

  SELECT field1,field2,... FROM table1 t1
  LEFT JOIN table2 t2 ON t2.outid=t1.outid
  

• 右連接

  包含所有右邊表中的記錄，甚至是右邊表中沒有和它匹配的記錄。

  SELECT field1,field2,... FROM table1 t1
  RIGHT JOIN table2 t2 ON t2.outid=t1.outid
  

• 子查詢

  當進行查詢時，需要的條件是另一個 select 語句的結(jié)果時將用到子查詢功能。

  SELECT field1,field2,... FROM table1 WHERE outid IN(
      SELECT outid FROM table2
  );
  

  除了 IN，用于子查詢的關(guān)鍵字還有：NOT IN、=、!=、EXISTS、NOT EXISTS等。

  MySQL4.1 以前的版本不支持子查詢，需要用表連接來實現(xiàn)，如：

  SELECT t1.* FROM table1 t1,table2 t2 
  WHERE t1.outid=t2.outid
  

• 聯(lián)合（合并結(jié)果集）

  SELECT * FROM table1
  UNION | UNION ALL
  SELECT * FROM table2
  ...
  UNION | UNION ALL
  SELECT * FROM tablen
  

  說明：

• UNION ALL 把結(jié)果集直接合并在一起。

• UNION 是將 UNION ALL 的結(jié)果集執(zhí)行一次 DISTINCT，去除重復記錄后的結(jié)果。

1.5. DCL

1.5.1. 授予權(quán)限

GRANT select,insert ON db1.* TO 'user1'@'localhost' IDENTIFIED BY '123';

1.5.2. 收回權(quán)限

REVOKE insert ON db1.* FROM 'user1'@'localhost';

1.6. 附錄

1.6.1. 連接數(shù)據(jù)庫

mysql [-h127.0.0.1] [-P3306] -uroot -p[password]

參數(shù)說明：

• -h 可選，數(shù)據(jù)庫地址（IP或域名），默認 127.0.0.1。

• -P 可選，數(shù)據(jù)庫端口號，默認 3306.

• -u 數(shù)據(jù)庫登錄用戶

• -p 數(shù)據(jù)庫登錄用戶對應(yīng)的密碼

示例：

$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.24-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

MySQL [(none)]> exit
Bye

1.6.2. 如何使用系統(tǒng)幫助

在命令行模式下，使用 ? contents 命令查看對應(yīng)分類的幫助信息。

如：

-- 查看 show 相關(guān)的所有命令等
mysql> ? show

1.6.3. 什么是元數(shù)據(jù)信息

元數(shù)據(jù)指數(shù)據(jù)的數(shù)據(jù)，比如表名、列名、列類型、索引名等表的各種屬性名稱。MySQL 5.0 之后提供來一個新的數(shù)據(jù)庫 information_schema，用來記錄 MySQL 中的元數(shù)據(jù)信息。

• 這是一個比較特殊的數(shù)據(jù)庫，它在物理上并不存在相關(guān)的目錄和文件；

• 對應(yīng)數(shù)據(jù)庫里使用 show tables 顯示的各種“表”也并不是實際存在的物理表，而全部是視圖。

常用的視圖如下：

• SCHEMATA 提供當前 MySQL 實例中所有的數(shù)據(jù)庫信息，對應(yīng) show databases; 的結(jié)果。

• TABLES 提供關(guān)于數(shù)據(jù)庫中的表信息（包括視圖），詳細表述里某個表屬于那個 schema、表類型、表引擎、創(chuàng)建時間等，對應(yīng) show tables from schemaname; 的結(jié)果。

• COLUMNS 提供所有表中的列信息，對應(yīng) show columns from schemaname.tablename; 的結(jié)果。

• STATISTICS 提供關(guān)于表索引的信息，對應(yīng) show index from schemaname.tablename; 的結(jié)果。