MySQL 基礎

初識 SQL

1. SQL語言的規則和規范

1) 基本規則

SQL 可以寫在一行或者多行。為了提高可讀性，各子句分行寫，必要時使用縮進
每條命令以 ;
關鍵字不能被縮寫也不能分行
關于標點符號
- 必須保證所有的()、單引號、雙引號是成對結束的
- 必須使用英文狀態下的半角輸入方式
- 字符串型和日期時間類型的數據可以使用單引號（' '）表示

2) 大小寫規范

MySQL 在 Windows 環境下是大小寫不敏感的
MySQL 在 Linux 環境下是大小寫敏感的
- 數據庫名、表名、表的別名、變量名是嚴格區分大小寫的
- 關鍵字、函數名、列名(或字段名)、列的別名(字段的別名) 是忽略大小寫的
推薦采用統一的書寫規范：
- 數據庫名、表名、表別名、字段名、字段別名等都小寫
- SQL 關鍵字、函數名、綁定變量等都大寫

3) 注釋

單行注釋：# 注釋文字(MySQL特有的方式)
單行注釋：-- 注釋文字(--后面必須包含一個空格。)
多行注釋：/* 注釋文字 */

4) 命名規則

數據庫、表名不得超過30個字符，變量名限制為29個
必須只能包含 A–Z, a–z, 0–9, _共63個字符
數據庫名、表名、字段名等對象名中間不要包含空格，同一個MySQL軟件中，數據庫不能同名；同一個庫中，表不能重名；
同一個表中，字段不能重名，必須保證你的字段沒有和保留字、數據庫系統或常用方法沖突。如果堅持使用，請在SQL語句中使用`（著重號）引起來
保持字段名和類型的一致性，在命名字段并為其指定數據類型的時候一定要保證一致性。假如數據類型在一個表里是整數，那在另一個表里可就別變成字符型了

2. 基本的SELECT語句

1) SELECT ... FROM

# 選擇全部列
SELECT *
FROM departments;

# 選擇指定列：
SELECT department_id, location_id
FROM departments;

2) 列的別名

使用空格緊跟其后，也可以在列名和別名之間加入關鍵字 AS，別名使用雙引號，以便在別名中包含空格或特殊的字符并區分大小寫。
AS 可以省略
別名盡量簡短，見名知意

SELECT last_name AS name, commission_pct comm
FROM employees;

3) 去重（DISTINCT）

SELECT DISTINCT department_id FROM employees;

4) 空值參與運算

# 空值：NULL ( 不等同于 0 , ' ' , 'NULL' )
# 實際問題的解決方案：引入IFNULL
# 在 MySQL 里，空值不等于空字符串。一個空字符串的長度是 0，而一個空值的長度是空。并且，在 MySQL 里，空值是占用空間的。

SELECT employee_id, salary "月工資", salary * (1 + IFNULL(commission_pct, 0)) * 12 "年工資" FROM employees;

5) 著重號（ ` ）

# 必須保證你的字段不與保留字、數據庫系統或常見方法沖突。
# 如果堅持使用，在SQL語句中使用 ` 引起來

SELECT * FROM `order`;

6) 查詢常數

SELECT '小張科技' as "公司名", employee_id, last_name FROM employees;

3. 顯示表結構

DESC employees;

mysql> desc employees;
+----------------+-------------+------+-----+---------+-------+
| Field          | Type        | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| employee_id    | int         | NO   | PRI | 0       |       |
| first_name     | varchar(20) | YES  |     | NULL    |       |
| last_name      | varchar(25) | NO   |     | NULL    |       |
| email          | varchar(25) | NO   | UNI | NULL    |       |
| phone_number   | varchar(20) | YES  |     | NULL    |       |
| hire_date      | date        | NO   |     | NULL    |       |
| job_id         | varchar(10) | NO   | MUL | NULL    |       |
| salary         | double(8,2) | YES  |     | NULL    |       |
| commission_pct | double(2,2) | YES  |     | NULL    |       |
| manager_id     | int         | YES  | MUL | NULL    |       |
| department_id  | int         | YES  | MUL | NULL    |       |
+----------------+-------------+------+-----+---------+-------+

其中，各 Tab 的含義分別解釋如下：

Field：表示字段名稱
Type：表示字段類型
Null：表示該列是否可以存儲NULL值
Key：表示該列是否已編制索引
- PRI：表示該列是表主鍵的一部分
- UNI：表示該列是UNIQUE索引的一部分
- MUL：表示在列中某個給定值允許出現多次
Default：表示該列是否有默認值，如果有，那么值是多少
Extra：表示可以獲取的與給定列有關的附加信息，例如AUTO_INCREMENT等。

4. 過濾數據

SELECT employee_id, last_name, job_id, department_id
FROM employees
WHERE department_id = 90;

運算符

1. 算術運算符

SELECT 100 + 0, 100 + 50 * 30, 100 - 35.5 FROM DUAL;

一個整數類型的值對整數進行加法和減法操作，結果還是一個整數；

一個整數類型的值對浮點數進行加法和減法操作，結果是一個浮點數；

在Java中， + 的左右兩邊如果有字符串，那么表示字符串的拼接。但是在MySQL中 + 只表示數值相加。如果遇到非數值類型，先嘗試轉成數值，如果轉失敗，就按 0計算。（注：MySQL 中字符串拼接要使用字符串函數CONCAT()實現）

在數學運算中，0 不能用作除數，在 MySQL中，一個數除以 0 為NULL。

2. 比較運算符

1) 等號運算符（=）

比較運算符用來對表達式左邊的操作數和右邊的操作數進行比較，比較的結果為真則返回1，比較的結果為假則返回0，其他情況則返回NULL。

如果等號兩邊的值、字符串或表達式中有一個為NULL，則比較結果為NULL。

SELECT 1 = 1, 1 = '1', 1 = 0, 'a' = 'a', (5 + 3) = (2 + 6), '' = NULL , NULL = NULL;
+-------+---------+-------+-----------+-------------------+-----------+-------------+
| 1 = 1 | 1 = '1' | 1 = 0 | 'a' = 'a' | (5 + 3) = (2 + 6) | '' = NULL | NULL = NULL |
+-------+---------+-------+-----------+-------------------+-----------+-------------+
|     1 |       1 |     0 |         1 |                 1 |      NULL |        NULL |
+-------+---------+-------+-----------+-------------------+-----------+-------------+

如果等號兩邊的值、字符串或表達式都為字符串，則MySQL會按照字符串進行比較，其比較的是每個字符串中字符的ANSI編碼是否相等。

如果等號兩邊的值都是整數，則MySQL會按照整數來比較兩個值的大小。

如果等號兩邊的值一個是整數，另一個是字符串，則MySQL會將字符串轉化為數字進行比較。

如果等號兩邊的值、字符串或表達式中有一個為NULL，則比較結果為NULL。

mysql> SELECT 1 = 2, 0 = 'abc', 1 = 'abc' FROM DUAL;
+-------+-----------+-----------+
| 1 = 2 | 0 = 'abc' | 1 = 'abc' |
+-------+-----------+-----------+
|   0   |     1     |     0     |
+-------+-----------+-----------+

2) 不等于運算符（!=）

不等于運算符（<>和!=）用于判斷兩邊的數字、字符串或者表達式的值是否不相等，如果不相等則返回1，相等則返回0。不等于運算符不能判斷NULL值。如果兩邊的值有任意一個為NULL，或兩邊都為NULL，則結果為NULL。 SQL語句示例如下：

mysql> SELECT 1 <> 1, 1 != 2, 'a' != 'b', (3+4) <> (2+6), 'a' != NULL, NULL <> NULL;
+--------+--------+------------+----------------+-------------+--------------+
| 1 <> 1 | 1 != 2 | 'a' != 'b' | (3+4) <> (2+6) | 'a' != NULL | NULL <> NULL |
+--------+--------+------------+----------------+-------------+--------------+
|    0   |    1   |      1     |        1       |     NULL    |      NULL    |
+--------+--------+------------+----------------+-------------+--------------+

3) （非）空運算符（IS NULL / IS NOT NULL）

空運算符 (IS NULL 或者 ISNULL) 判斷一個值是否為NULL，如果為NULL則返回1，否則返回0。

mysql> SELECT NULL IS NULL, ISNULL(NULL), ISNULL('a'), 1 IS NOT NULL;
+--------------+--------------+-------------+-----------+
| NULL IS NULL | ISNULL(NULL) | ISNULL('a') | 1 IS NULL |
+--------------+--------------+-------------+-----------+
|      1       |       1      |      0      |     1     |
+--------------+--------------+-------------+-----------+
1 row in set (0.00 sec)

4) 最大/最小值運算符

A. 最小值運算符

語法格式為：LEAST(值1，值2，...，值n)。其中，“值n”表示參數列表中有n個值。在有兩個或多個參數的情況下，返回最小值。

mysql> SELECT LEAST (1,0,2), LEAST('b','a','c'), LEAST(1,NULL,2);
+---------------+--------------------+-----------------+
| LEAST (1,0,2) | LEAST('b','a','c') | LEAST(1,NULL,2) |
+---------------+--------------------+-----------------+
|       0       |          a         |        NULL     |
+---------------+--------------------+-----------------+
1 row in set (0.00 sec)

由結果可以看到，當參數是整數或者浮點數時，LEAST將返回其中最小的值；當參數為字符串時，返回字母表中順序最靠前的字符；當比較值列表中有NULL時，不能判斷大小，返回值為NULL。

B. 最大值運算符

語法格式為：GREATEST(值1，值2，...，值n)。其中，n表示參數列表中有n個值。當有兩個或多個參數時，返回值為最大值。假如任意一個自變量為NULL，則GREATEST()的返回值為NULL。

mysql> SELECT GREATEST(1,0,2), GREATEST('b','a','c'), GREATEST(1,NULL,2);
+-----------------+-----------------------+--------------------+
| GREATEST(1,0,2) | GREATEST('b','a','c') | GREATEST(1,NULL,2) |
+-----------------+-----------------------+--------------------+
|         2       |             c         |         NULL       |
+-----------------+-----------------------+--------------------+
1 row in set (0.00 sec)

由結果可以看到，當參數中是整數或者浮點數時，GREATEST將返回其中最大的值；當參數為字符串時，返回字母表中順序最靠后的字符；當比較值列表中有NULL時，不能判斷大小，返回值為NULL。

5) BETWEEN AND 運算符

BETWEEN運算符使用的格式通常為SELECT D FROM TABLE WHERE C BETWEEN A AND B，此時，當C大于或等于A，并且C小于或等于B時，結果為1，否則結果為0。

6) IN / NOT IN 運算符

IN運算符用于判斷給定的值是否是IN列表中的一個值，如果是則返回1，否則返回0。如果給定的值為NULL，或者IN列表中存在NULL，則結果為NULL。

mysql> SELECT 'a' IN ('a','b','c'), 1 IN (2,3), NULL IN ('a','b'), 'a' IN ('a', NULL);
+----------------------+------------+-------------------+--------------------+
| 'a' IN ('a','b','c') | 1 IN (2,3) | NULL IN ('a','b') | 'a' IN ('a', NULL) |
+----------------------+------------+-------------------+--------------------+
|            1         |      0     |         NULL      |          1         |
+----------------------+------------+-------------------+--------------------+

NOT IN 運算符用于判斷給定的值是否不是IN列表中的一個值，如果不是IN列表中的一個值，則返回1，否則返回0。

7) LIKE 運算符

LIKE運算符主要用來匹配字符串，通常用于模糊匹配，如果滿足條件則返回1，否則返回 0。如果給定的值或者匹配條件為NULL，則返回結果為NULL。

“%”：匹配0個或多個字符。
“_”：只能匹配一個字符。

8) ESCAPE

回避特殊符號的：使用轉義符。例如：將[%]轉為[$%]、[]轉為[$]，然后再加上[ESCAPE‘$’]即可。

SELECT job_id
FROM jobs
WHERE job_id LIKE ‘IT\_%‘;

如果使用表示轉義，要省略ESCAPE。如果不是\，則要加上ESCAPE。

SELECT job_id
FROM jobs
WHERE job_id LIKE ‘IT$_%‘ escape ‘$‘;

9) REGEXP 運算符

REGEXP運算符用來匹配字符串，語法格式為： expr REGEXP 匹配條件。

（1）‘^’匹配以該字符后面的字符開頭的字符串。

（2）‘$’匹配以該字符前面的字符結尾的字符串。

（3）‘.’匹配任何一個單字符。

（4）“[...]”匹配在方括號內的任何字符。例如，“[abc]”匹配“a”或“b”或“c”。為了命名字符的范圍，使用一個‘-’。“[a-z]”匹配任何字母，而“[0-9]”匹配任何數字。

（5）‘’匹配零個或多個在它前面的字符。例如，“x”匹配任何數量的‘x’字符，“[0-9]”匹配任何數量的數字，而“”匹配任何數量的任何字符。

3. 位運算

位運算符是在二進制數上進行計算的運算符。位運算符會先將操作數變成二進制數，然后進行位運算，最后將計算結果從二進制變回十進制數。

MySQL支持的位運算符如下：

4. 運算符的優先級

數字編號越大，優先級越高，優先級高的運算符先進行計算。

擴展：使用正則表達式查詢

排序與分頁

1. 排序規則

使用 ORDER BY 子句排序
- ASC（ascend）: 升序
- DESC（descend）:降序
ORDER BY 子句在 SELECT 語句的結尾。

1) 單列排序

SELECT last_name, job_id, department_id, hire_date
FROM employees
ORDER BY hire_date;

2) 多列排序

可以使用不在 SELECT 列表中的列排序。
在對多列進行排序的時候，首先排序的第一列必須有相同的列值，才會對第二列進行排序。如果第一列數據中所有值都是唯一的，將不再對第二列進行排序。

2. 分頁

LIMIT [位置偏移量] 行數

# 前10條記錄：
SELECT * FROM 表名 LIMIT 0,10;
# 或
SELECT * FROM 表名 LIMIT 10;
# 第11至20條記錄：
SELECT * FROM 表名 LIMIT 10,10;
# 第21至30條記錄：
SELECT * FROM 表名 LIMIT 20,10;

MySQL 8.0中可以使用“LIMIT 3 OFFSET 4”，意思是獲取從第5條記錄開始后面的3條記錄，和“LIMIT 4,3;”返回的結果相同。

# 分頁顯式公式：（當前頁數-1）* 每頁條數，每頁條數
# LIMIT 子句必須放在整個SELECT語句的最后！！
SELECT * FROM tablename
LIMIT(PageNo - 1) * PageSize, PageSize;

使用LIMIT的好處：

約束返回結果的數量可以減少數據表的網絡傳輸量，也可以提升查詢效率。如果我們知道返回結果只有 1 條，就可以使用 LIMIT 1 ，告訴 SELECT 語句只需要返回一條記錄即可。這樣的好處就是 SELECT 不需要掃描完整的表，只需要檢索到一條符合條件的記錄即可返回。

多表查詢

1. 多表查詢分類

1) 自連接

題目：查詢employees表，返回 <員工 works for 老板>

SELECT CONCAT(worker.last_name , ' works for ', manager.last_name)
FROM employees worker, employees manager
WHERE worker.manager_id = manager.employee_id;

2) 內連接與外連接

內連接: 合并具有同一列的兩個以上的表的行, 結果集中不包含一個表與另一個表不匹配的行

SQL92語法

SELECT emp.employee_id, dep.department_name
FROM employee emp, department dep
WHERE emp.`department_id` = dep.`department_id`;

SQL99語法

SELECT emp.employee_id, dep.department_name
FROM employee emp JOIN department dep
ON emp.`department_id` = dep.`department_id`;

外連接: 兩個表在連接過程中除了返回滿足連接條件的行以外還返回左（或右）表中不滿足條件的行，這種連接稱為左（或右）外連接。沒有匹配的行時, 結果表中相應的列為空(NULL)。

如果是左外連接，則連接條件中左邊的表也稱為主表，右邊的表稱為從表。

LEFT OUTER JOIN

SELECT last_name, department_name
FROM employees emp LEFT OUTER JOIN department dep
ON emp.`department_id` = dep.`department_id`;

如果是右外連接，則連接條件中右邊的表也稱為主表，左邊的表稱為從表。

RIGHT OUTER JOIN

SELECT last_name, department_name
FROM employees emp RIGHT OUTER JOIN department dep
ON emp.`department_id` = dep.`department_id`

2. UNION 的使用

合并查詢結果

利用UNION關鍵字，可以給出多條SELECT語句，并將它們的結果組合成單個結果集。合并時，兩個表對應的列數和數據類型必須相同，并且相互對應。各個 SELECT 語句之間使用 UNION 或 UNION ALL 關鍵字分隔。

SELECT column,... FROM table1
UNION [ALL]
SELECT column,... FROM table2

UNION操作符

UNION 操作符返回兩個查詢的結果集的并集，去除重復記錄。

UNION ALL操作符

UNION ALL 操作符返回兩個查詢的結果集的并集。對于兩個結果集的重復部分，不去重。

注意：執行 UNION ALL語句時所需要的資源比UNION語句少。如果明確知道合并數據后的結果數據不存在重復數據，或者不需要去除重復的數據，則盡量使用UNION ALL語句，以提高數據查詢的效率。

舉例：查詢部門編號>90或郵箱包含a的員工信息

#方式1
SELECT * FROM employees WHERE email LIKE '%a%' OR department_id>90;

#方式2
SELECT * FROM employees WHERE email LIKE '%a%'
UNION
SELECT * FROM employees WHERE department_id>90;

舉例：查詢中國用戶中男性的信息以及美國用戶中年男性的用戶信息

SELECT id,cname FROM t_chinamale WHERE csex='男'
UNION ALL
SELECT id,tname FROM t_usmale WHERE tGender='male';

3.七種 SQL JOINS 的實現

# 中圖：內連接
SELECT employee_id,department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`;

# 左上圖：左外連接
SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`;

# 右上圖：右外連接
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;

# 左中圖：
SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL;

# 右中圖：
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL;

# 左下圖：滿外連接
# 方式1：左上圖 UNION ALL 右中圖
SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
UNION ALL
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL;

# 方式2：左中圖 UNION ALL 右上圖
SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;

# 右下圖：左中圖  UNION ALL 右中圖
SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL;

4. SQL99 語法的新特性

1) 自然連接

SQL99 在 SQL92 的基礎上提供了一些特殊語法，比如 NATURAL JOIN 用來表示自然連接。我們可以把自然連接理解為 SQL92 中的等值連接。它會幫你自動查詢兩張連接表中所有相同的字段，然后進行等值連接。

在SQL92標準中：

SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`
AND e.`manager_id` = d.`manager_id`;

在 SQL99 中你可以寫成：

SELECT employee_id,last_name,department_name
FROM employees e NATURAL JOIN departments d;

2) USING連接

當我們進行連接的時候，SQL99還支持使用 USING 指定數據表里的同名字段進行等值連接。但是只能配合JOIN一起使用。比如：

SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
USING (department_id);

你能看出與自然連接 NATURAL JOIN 不同的是，USING 指定了具體的相同的字段名稱，你需要在 USING 的括號（）中填入要指定的同名字段。同時使用 JOIN...USING 可以簡化 JOIN ON 的等值連接。它與下面的 SQL 查詢結果是相同的：

SELECT employee_id,last_name,department_name
FROM employees e ,departments d
WHERE e.department_id = d.department_id;

5. 小結

表連接的約束條件可以有三種方式：WHERE, ON, USING

WHERE：適用于所有關聯查詢
ON ：只能和JOIN一起使用，只能寫關聯條件。雖然關聯條件可以并到WHERE中和其他條件一起寫，但分開寫可讀性更好。
USING：只能和JOIN一起使用，而且要求兩個關聯字段在關聯表中名稱一致，而且只能表示關聯字段值相等

我們要控制連接表的數量。

多表連接就相當于嵌套 for 循環一樣，非常消耗資源，會讓 SQL 查詢性能下降得很嚴重，因此不要連接不必要的表。

在許多 DBMS 中，也都會有最大連接表的限制。

單行函數

1. 數值函數

1) 基本函數

函數	用法
ABS(x)	返回x的絕對值
SIGN(X)	單元格
PI()	返回圓周率的值
CEIL(x)，CEILING(x)	返回大于或等于某個值的最小整數
FLOOR(x)	返回小于或等于某個值的最大整數
LEAST(e1,e2,e3…)	返回列表中的最小值
GREATEST(e1,e2,e3…)	返回列表中的最大值
MOD(x,y)	返回X除以Y后的余數
RAND()	返回0~1的隨機值
RAND(x)	返回0~1的隨機值，其中x的值用作種子值，相同的X值會產生相同的隨機數
ROUND(x)	返回一個對x的值進行四舍五入后，最接近于X的整數
ROUND(x,y)	返回一個對x的值進行四舍五入后最接近X的值，并保留到小數點后面Y位
TRUNCATE(x,y)	返回數字x截斷為y位小數的結果
SQRT(x)	返回x的平方根。當X的值為負數時，返回NULL

2) 角度與弧度互換函數

函數	用法
RADIANS(x)	將角度轉化為弧度，其中，參數x為角度值
DEGREES(x)	將弧度轉化為角度，其中，參數x為弧度值

3) 三角函數

函數	用法
SIN(x)	將角度轉化為弧度，其中，參數x為角度值
ASIN(x)	將弧度轉化為角度，其中，參數x為弧度值
COS(x)	返回x的余弦值，其中，參數x為弧度值
ACOS(x)	返回x的反余弦值，即獲取余弦為x的值。如果x的值不在-1到1之間，則返回NULL
TAN(x)	返回x的正切值，其中，參數x為弧度值
ATAN(x)	返回x的反正切值，即返回正切值為x的值
ATAN2(m,n)	返回兩個參數的反正切值
COT(x)	返回x的余切值，其中，X為弧度值

4) 指數與對數函數

函數	用法
POW(x,y)，POWER(X,Y)	返回x的y次方
EXP(X)	返回e的X次方，其中e是一個常數，2.718281828459045
LN(X)，LOG(X)	返回以e為底的X的對數，當X <= 0 時，返回的結果為NULL
LOG10(X)	返回以10為底的X的對數，當X <= 0 時，返回的結果為NULL
LOG2(X)	返回以2為底的X的對數，當X <= 0 時，返回NULL

5) 進制間的轉換

函數	用法
BIN(x)	返回x的二進制編碼
HEX(x)	返回x的十六進制編碼
OCT(x)	返回x的八進制編碼
CONV(x,f1,f2)	返回f1進制數變成f2進制數

2. 字符串函數

函數	用法
ASCII(S)	返回字符串S中的第一個字符的ASCII碼值
CHAR_LENGTH(s)	返回字符串s的字符數。作用與CHARACTER_LENGTH(s)相同
LENGTH(s)	返回字符串s的字節數，和字符集有關
CONCAT(s1,s2,......,sn)	連接s1,s2,......,sn為一個字符串
CONCAT_WS(x, s1,s2,......,sn)	同CONCAT(s1,s2,...)函數，但是每個字符串之間要加上x
INSERT(str, idx, len, replacestr)	將字符串str從第idx位置開始，len個字符長的子串替換為字符串replacestr
REPLACE(str, a, b)	用字符串b替換字符串str中所有出現的字符串a
UPPER(s) 或 UCASE(s)	將字符串s的所有字母轉成大寫字母
LOWER(s) 或LCASE(s)	將字符串s的所有字母轉成小寫字母
LEFT(str,n)	返回字符串str最左邊的n個字符
RIGHT(str,n)	返回字符串str最右邊的n個字符
LPAD(str, len, pad)	用字符串pad對str最左邊進行填充，直到str的長度為len個字符
RPAD(str ,len, pad)	用字符串pad對str最右邊進行填充，直到str的長度為len個字符
LTRIM(s)	去掉字符串s左側的空格
RTRIM(s)	去掉字符串s右側的空格
TRIM(s)	去掉字符串s開始與結尾的空格
TRIM(s1 FROM s)	去掉字符串s開始與結尾的s1
TRIM(LEADING s1 FROM s)	去掉字符串s開始處的s1
TRIM(TRAILING s1 FROM s)	去掉字符串s結尾處的s1
REPEAT(str, n)	返回str重復n次的結果
SPACE(n)	返回n個空格
STRCMP(s1,s2)	比較字符串s1,s2的ASCII碼值的大小
SUBSTR(s,index,len)	返回從字符串s的index位置其len個字符，作用與SUBSTRING(s,n,len)、 MID(s,n,len)相同
LOCATE(substr,str)	返回字符串substr在字符串str中首次出現的位置，作用于POSITION(substr IN str)、INSTR(str,substr)相同。未找到，返回0
ELT(m,s1,s2,…,sn)	返回指定位置的字符串，如果m=1，則返回s1，如果m=2，則返回s2，如果m=n，則返回sn
FIELD(s,s1,s2,…,sn)	返回字符串s在字符串列表中第一次出現的位置
FIND_IN_SET(s1,s2)	返回字符串s1在字符串s2中出現的位置。其中，字符串s2是一個以逗號分隔的字符串
REVERSE(s)	返回s反轉后的字符串
NULLIF(value1,value2)	比較兩個字符串，如果value1與value2相等，則返回NULL，否則返回 value1

注意：MySQL中，字符串的位置是從1開始的。

3. 日期和時間函數

1) 獲取日期、時間

函數	用法
CURDATE() ，CURRENT_DATE()	返回當前日期，只包含年、月、日
CURTIME() ， CURRENT_TIME()	返回當前時間，只包含時、分、秒
NOW() / SYSDATE() / CURRENT_TIMESTAMP() / LOCALTIME() / LOCALTIMESTAMP()	返回當前系統日期和時間
UTC_DATE()	返回UTC（世界標準時間）日期
UTC_TIME()	返回UTC（世界標準時間）時間

2) 日期與時間戳的轉換

函數	用法
UNIX_TIMESTAMP()	以UNIX時間戳的形式返回當前時間。SELECT UNIX_TIMESTAMP() - >1634348884
UNIX_TIMESTAMP(date)	將時間date以UNIX時間戳的形式返回。
FROM_UNIXTIME(timestamp)	將UNIX時間戳的時間轉換為普通格式的時間

3) 獲取月份、星期、星期數、天數等函數

函數	用法
YEAR(date) / MONTH(date) / DAY(date)	返回具體的日期值
HOUR(time) / MINUTE(time) / SECOND(time)	返回具體的時間值
FROM_UNIXTIME(timestamp)	將UNIX時間戳的時間轉換為普通格式的時間
MONTHNAME(date)	返回月份：January，...
DAYNAME(date)	返回星期幾：MONDAY，TUESDAY.....SUNDAY
WEEKDAY(date)	返回周幾，注意，周1是0，周2是1，。。。周日是6
QUARTER(date)	返回日期對應的季度，范圍為1～4
WEEK(date) ， WEEKOFYEAR(date)	返回一年中的第幾周
DAYOFYEAR(date)	返回日期是一年中的第幾天
DAYOFMONTH(date)	返回日期位于所在月份的第幾天
DAYOFWEEK(date)	返回周幾，注意：周日是1，周一是2，。。。周六是 7

5) 時間和秒鐘轉換的函數

函數	用法
TIME_TO_SEC(time)	將 time 轉化為秒并返回結果值。轉化的公式為：小時 * 3600+分鐘 * 60 + 秒
SEC_TO_TIME(seconds)	將 seconds 描述轉化為包含小時、分鐘和秒的時間

6) 計算日期和時間的函數

函數	用法
DATE_ADD(datetime, INTERVAL expr type)， ADDDATE(date,INTERVAL expr type)	返回與給定日期時間相差INTERVAL時間段的日期時間
DATE_SUB(date,INTERVAL expr type)， SUBDATE(date,INTERVAL expr type)	返回與date相差INTERVAL時間間隔的日期

函數	用法
ADDTIME(time1,time2)	返回time1加上time2的時間。當time2為一個數字時，代表的是秒，可以為負數
SUBTIME(time1,time2)	返回time1減去time2后的時間。當time2為一個數字時，代表的是秒，可以為負數
DATEDIFF(date1,date2)	返回date1 - date2的日期間隔天數
TIMEDIFF(time1, time2)	返回time1 - time2的時間間隔
FROM_DAYS(N)	返回從0000年1月1日起，N天以后的日期
TO_DAYS(date)	返回日期date距離0000年1月1日的天數
LAST_DAY(date)	返回date所在月份的最后一天的日期
MAKEDATE(year,n)	針對給定年份與所在年份中的天數返回一個日期
MAKETIME(hour,minute,second)	將給定的小時、分鐘和秒組合成時間并返回
PERIOD_ADD(time,n)	返回time加上n后的時間

7) 日期的格式化與解析

函數	用法
DATE_FORMAT(date,fmt)	按照字符串fmt格式化日期date值
TIME_FORMAT(time,fmt)	按照字符串fmt格式化時間time值
GET_FORMAT(date_type,format_type)	返回日期字符串的顯示格式
STR_TO_DATE(str, fmt)	按照字符串fmt對str進行解析，解析為一個日期

上述非GET_FORMAT 函數中fmt參數常用的格式符：

格式符	說明	格式符	說明
%Y	4位數字表示年份	%y	表示兩位數字表示年份
%M	月名表示月份（January,....）	%m	兩位數字表示月份（01,02,03。。。）
%b	縮寫的月名（Jan.，Feb.，....）	%c	數字表示月份（1,2,3,...）
%D	英文后綴表示月中的天數（1st,2nd,3rd,...）	%d	兩位數字表示月中的天數(01,02...)
%e	數字形式表示月中的天數（1,2,3,4,5.....）
%H	兩位數字表示小數，24小時制（01,02..）	%h 和%I	兩位數字表示小時，12小時制（01,02..）
%k	數字形式的小時，24小時制(1,2,3)	%l	數字形式表示小時，12小時制（1,2,3,4....）
%i	兩位數字表示分鐘（00,01,02）	%S 和%s	兩位數字表示秒(00,01,02...)
%W	一周中的星期名稱（Sunday...）	%a	一周中的星期縮寫（Sun.， Mon.,Tues.，..）
%w	以數字表示周中的天數 (0=Sunday,1=Monday....)
%j	以3位數字表示年中的天數(001,002...)	%U	以數字表示年中的第幾周，（1,2,3。。）其中Sunday為周中第一天
%u	以數字表示年中的第幾周，（1,2,3。。）其中Monday為周中第一天
%T	24小時制	%r	12小時制
%p	AM或PM	%%	表示%

4. 流程控制函數

流程處理函數可以根據不同的條件，執行不同的處理流程，可以在SQL語句中實現不同的條件選擇。 MySQL中的流程處理函數主要包括IF()、IFNULL()和CASE()函數。

函數	用法
IF(value,value1,value2)	如果value的值為TRUE，返回value1，否則返回value2
IFNULL(value1, value2)	如果value1不為NULL，返回value1，否則返回value2
CASE WHEN 條件1 THEN 結果1 WHEN 條件2 THEN 結果2 .... [ELSE resultn] END	相當于Java的if...else if...else...
CASE expr WHEN 常量值1 THEN 值1 WHEN 常量值1 THEN 值1 .... [ELSE 值n] END	相當于Java的switch...case...

5. 加密與解密函數

加密與解密函數主要用于對數據庫中的數據進行加密和解密處理，以防止數據被他人竊取。這些函數在保證數據庫安全時非常有用。

函數	用法
PASSWORD(str)	返回字符串str的加密版本，41位長的字符串。加密結果不可逆，常用于用戶的密碼加密
MD5(str)	返回字符串str的md5加密后的值，也是一種加密方式。若參數為 NULL，則會返回NULL
SHA(str)	從原明文密碼str計算并返回加密后的密碼字符串，當參數為 NULL時，返回NULL。 SHA加密算法比MD5更加安全。
ENCODE(value,password_seed)	返回使用password_seed作為加密密碼加密value
DECODE(value,password_seed)	返回使用password_seed作為加密密碼解密value

6. MySQL信息函數

MySQL中內置了一些可以查詢MySQL信息的函數，這些函數主要用于幫助數據庫開發或運維人員更好地對數據庫進行維護工作。

函數	用法
VERSION()	返回當前MySQL的版本號
CONNECTION_ID()	返回當前MySQL服務器的連接數
DATABASE()，SCHEMA()	返回MySQL命令行當前所在的數據庫
USER()，CURRENT_USER()、SYSTEM_USER()， SESSION_USER()	返回當前連接MySQL的用戶名，返回結果格式為 “主機名@用戶名”
CHARSET(value)	返回字符串value自變量的字符集
COLLATION(value)	返回字符串value的比較規則

MySQL中有些函數無法對其進行具體的分類，但是這些函數在MySQL的開發和運維過程中也是不容忽視的。

函數	用法
FORMAT(value,n)	返回對數字value進行格式化后的結果數據。n表示四舍五入后保留到小數點后n位
CONV(value,from,to)	將value的值進行不同進制之間的轉換
INET_ATON(ipvalue)	將以點分隔的IP地址轉化為一個數字
INET_NTOA(value)	將數字形式的IP地址轉化為以點分隔的IP地址
BENCHMARK(n,expr)	將表達式expr重復執行n次。用于測試MySQL處理expr表達式所耗費的時間
CONVERT(value USING char_code)	將value所使用的字符編碼修改為char_code

聚合函數

1. 聚合函數介紹

什么是聚合函數

聚合函數作用于一組數據，并對一組數據返回一個值。

聚合函數類型
- AVG()
- SUM()
- MAX()
- MIN()
- COUNT()

1) AVG和SUM函數

SELECT AVG(salary), MAX(salary),MIN(salary), SUM(salary)
FROM employees
WHERE job_id LIKE '%REP%';

2) MIN和MAX函數

可以對任意數據類型的數據使用 MIN 和 MAX 函數。

SELECT MIN(hire_date), MAX(hire_date)
FROM employees;

3) COUNT函數

COUNT(*)返回表中記錄總數，適用于任意數據類型。

SELECT COUNT(*)
FROM employees
WHERE department_id = 50;

COUNT(expr) 返回expr不為空的記錄總數。

SELECT COUNT(commission_pct)
FROM employees
WHERE department_id = 50;

問題：用count(*)，count(1)，count(列名)誰好呢?

其實，對于MyISAM引擎的表是沒有區別的。這種引擎內部有一計數器在維護著行數。 Innodb引擎的表用count(*),count(1)直接讀行數，復雜度是O(n)，因為innodb真的要去數一遍。但好于具體的count(列名)。

問題：能不能使用count(列名)替換count(*)?

不要使用 count(列名)來替代 count() ， count() 是 SQL92 定義的標準統計行數的語法，跟數據庫無關，跟 NULL 和非 NULL 無關。說明：count(*)會統計值為 NULL 的行，而 count(列名)不會統計此列為 NULL 值的行。

2. GROUP BY

1) 基本使用

可以使用GROUP BY子句將表中的數據分成若干組

SELECT column, group_function(column)
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[ORDER BY column];

結論1：SELECT中出現的非組函數的字段必須聲明在GROUP BY中。

反之，GROUP BY中聲明的字段可以不出現在SELECT中。

結論2：GROUP BY聲明在FROM后面、WHERE后面、ORDER BY前面、LIMIT前面。

2) 使用WITH ROLLUP

SELECT department_id,AVG(salary)
FROM employees
WHERE department_id > 80
GROUP BY department_id WITH ROLLUP;

注意：當使用ROLLUP時，不能同時使用ORDER BY子句進行結果排序，即ROLLUP和ORDER BY是互相排斥的。

3. HAVING

1) 基本使用

過濾分組：HAVING子句

行已經被分組。
使用了聚合函數。
滿足HAVING 子句中條件的分組將被顯示。
HAVING 不能單獨使用，必須要跟 GROUP BY 一起使用。

SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary)>10000 ;

要求

如果過濾條件中使用了聚合函數，則必須使用HAVING來替換WHERE。否則，報錯。
當過濾條件中沒有聚合函數時，則次過濾條件聲明在WHERE中或HAVING中都可以。但是，建議聲明在WHERE中的執行效率高。
HAVING必須聲明在GROUP BY 的后面
開發中，我們使用HAVING的前提是SQL中使用了GROUP BY。

2) WHERE和HAVING的對比

區別1：WHERE 可以直接使用表中的字段作為篩選條件，但不能使用分組中的計算函數作為篩選條件； HAVING 必須要與 GROUP BY 配合使用，可以把分組計算的函數和分組字段作為篩選條件。

這決定了，在需要對數據進行分組統計的時候，HAVING 可以完成 WHERE 不能完成的任務。這是因為，在查詢語法結構中，WHERE 在 GROUP BY 之前，所以無法對分組結果進行篩選。HAVING 在 GROUP BY 之后，可以使用分組字段和分組中的計算函數，對分組的結果集進行篩選，這個功能是 WHERE 無法完成的。另外，WHERE排除的記錄不再包括在分組中。

區別2：如果需要通過連接從關聯表中獲取需要的數據，WHERE 是先篩選后連接，而 HAVING 是先連接后篩選。

這一點，就決定了在關聯查詢中，WHERE 比 HAVING 更高效。因為 WHERE 可以先篩選，用一個篩選后的較小數據集和關聯表進行連接，這樣占用的資源比較少，執行效率也比較高。HAVING 則需要先把結果集準備好，也就是用未被篩選的數據集進行關聯，然后對這個大的數據集進行篩選，這樣占用的資源就比較多，執行效率也較低。

小結如下：

關鍵字	用法	缺點
WHERE	先篩選數據再關聯，執行效率高	不能使用分組中的計算函數進行篩選
HAVING	可以使用分組中的計算函數	在最后的結果集中進行篩選，執行效率較低

開發中的選擇：

WHERE 和 HAVING 也不是互相排斥的，我們可以在一個查詢里面同時使用 WHERE 和 HAVING。包含分組統計函數的條件用 HAVING，普通條件用 WHERE。這樣，我們就既利用了 WHERE 條件的高效快速，又發揮了 HAVING 可以使用包含分組統計函數的查詢條件的優點。當數據量特別大的時候，運行效率會有很大的差別。

4. SELECT的執行過程

1) 查詢的結構

#方式1：
SELECT ...,....,...
FROM ...,...,....
WHERE 多表的連接條件
AND 不包含組函數的過濾條件
GROUP BY ...,...
HAVING 包含組函數的過濾條件
ORDER BY ... ASC/DESC
LIMIT ...,...
#方式2：
SELECT ...,....,...
FROM ... JOIN ...
ON 多表的連接條件
JOIN ...
ON ...
WHERE 不包含組函數的過濾條件
AND/OR 不包含組函數的過濾條件
GROUP BY ...,...
HAVING 包含組函數的過濾條件
ORDER BY ... ASC/DESC
LIMIT ...,...
#其中：
#（1）from：從哪些表中篩選
#（2）on：關聯多表查詢時，去除笛卡爾積
#（3）where：從表中篩選的條件
#（4）group by：分組依據
#（5）having：在統計結果中再次篩選
#（6）order by：排序
#（7）limit：分頁

需要記住 SELECT 查詢時的兩個順序：

1. 關鍵字的順序是不能顛倒的：

SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT...

1. SELECT 語句的執行順序（在 MySQL 和 Oracle 中，SELECT 執行順序基本相同）：

FROM -> WHERE -> GROUP BY -> HAVING -> SELECT 的字段 -> DISTINCT -> ORDER BY -> LIMIT

比如你寫了一個 SQL 語句，那么它的關鍵字順序和執行順序是下面這樣的：

SELECT DISTINCT player_id, player_name, count(*) as num # 順序 5
FROM player JOIN team ON player.team_id = team.team_id # 順序 1
WHERE height > 1.80 # 順序 2
GROUP BY player.team_id # 順序 3
HAVING num > 2 # 順序 4
ORDER BY num DESC # 順序 6
LIMIT 2 # 順序 7

在 SELECT 語句執行這些步驟的時候，每個步驟都會產生一個虛擬表，然后將這個虛擬表傳入下一個步驟中作為輸入。需要注意的是，這些步驟隱含在 SQL 的執行過程中，對于我們來說是不可見的。

2) SQL的執行原理

SELECT 是先執行 FROM 這一步的。在這個階段，如果是多張表聯查，還會經歷下面的幾個步驟：

首先先通過 CROSS JOIN 求笛卡爾積，相當于得到虛擬表 vt（virtual table）1-1；
通過 ON 進行篩選，在虛擬表 vt1-1 的基礎上進行篩選，得到虛擬表 vt1-2；
添加外部行。如果我們使用的是左連接、右鏈接或者全連接，就會涉及到外部行，也就是在虛擬表 vt1-2 的基礎上增加外部行，得到虛擬表 vt1-3。

當然如果我們操作的是兩張以上的表，還會重復上面的步驟，直到所有表都被處理完為止。這個過程得到是我們的原始數據。
然后進入第三步和第四步，也就是 GROUP 和 HAVING 階段。在這個階段中，實際上是在虛擬表 vt2 的基礎上進行分組和分組過濾，得到中間的虛擬表 vt3 和 vt4 。
當我們完成了條件篩選部分之后，就可以篩選表中提取的字段，也就是進入到 SELECT 和 DISTINCT 階段。
首先在 SELECT 階段會提取想要的字段，然后在 DISTINCT 階段過濾掉重復的行，分別得到中間的虛擬表 vt5-1 和 vt5-2 。
當我們提取了想要的字段數據之后，就可以按照指定的字段進行排序，也就是 ORDER BY 階段，得到虛擬表 vt6 。
最后在 vt6 的基礎上，取出指定行的記錄，也就是 LIMIT 階段，得到最終的結果，對應的是虛擬表 vt7 。
當然我們在寫 SELECT 語句的時候，不一定存在所有的關鍵字，相應的階段就會省略。

同時因為 SQL 是一門類似英語的結構化查詢語言，所以我們在寫 SELECT 語句的時候，還要注意相應的關鍵字順序，所謂底層運行的原理，就是我們剛才講到的執行順序。

子查詢

1. 基本使用

子查詢的基本語法結構
子查詢（內查詢）在主查詢之前一次執行完成。
子查詢的結果被主查詢（外查詢）使用。
注意事項
- 子查詢要包含在括號內
- 將子查詢放在比較條件的右側
- 單行操作符對應單行子查詢，多行操作符對應多行子查詢

2. 子查詢的分類

分類方式1：

我們按內查詢的結果返回一條還是多條記錄，將子查詢分為單行子查詢、多行子查詢。
分類方式2：

我們按內查詢是否被執行多次，將子查詢劃分為相關(或關聯)子查詢和不相關(或非關聯)子查詢。

子查詢從數據表中查詢了數據結果，如果這個數據結果只執行一次，然后這個數據結果作為主查詢的條件進行執行，那么這樣的子查詢叫做不相關子查詢。

同樣，如果子查詢需要執行多次，即采用循環的方式，先從外部查詢開始，每次都傳入子查詢進行查詢，然后再將結果反饋給外部，這種嵌套的執行方式就稱為相關子查詢。

3. 單行子查詢

1) 單行比較操作符

操作符	含義
=	equal to
>	greater than
>=	greater than or equal to
<	less than
<=	less than or equal to
<>	not equal to

2) 代碼示例

題目：返回job_id與141號員工相同，salary比143號員工多的員工姓名、job_id和工資

SELECT last_name, job_id, salary
FROM eployees
WHERE job_id = (
	SELECT job_id
	FROM eployees
    WHERE employee_id = 141
)
AND salary > (
	SELECT salary
	FROM eployees
    WHERE employee_id = 143
);

題目：查詢與141號或174號員工的manager_id和department_id相同的其他員工的employee_id， manager_id，department_id

# 實現方式一：不成對比較
SELECT employee_id, manager_id, department_id
FROM employees
WHERE manager_id IN
        (SELECT manager_id
        FROM employees
        WHERE employee_id IN (174,141))
AND department_id IN
        (SELECT department_id
        FROM employees
        WHERE employee_id IN (174,141))
AND employee_id NOT IN(174,141);

# 實現方式二：成對比較
SELECT employee_id, manager_id, department_id
FROM employees
WHERE (manager_id, department_id) IN
        (SELECT manager_id, department_id
        FROM employees
        WHERE employee_id IN (141,174))
AND employee_id NOT IN (141,174);

題目：查詢最低工資大于50號部門最低工資的部門id和其最低工資

SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) >
            (SELECT MIN(salary)
            FROM employees
            WHERE department_id = 50);

3) CASE中的子查詢

題目：顯式員工的employee_id,last_name和location。其中，若員工department_id與location_id為1800 的department_id相同，則location為’Canada’，其余則為’USA’。

SELECT employee_id, last_name,
    (CASE department_id
    WHEN
        (SELECT department_id FROM departments
        WHERE location_id = 1800)
    THEN 'Canada' ELSE 'USA' END) location
FROM employees;

4) 子查詢中的空值問題

SELECT last_name, job_id
FROM employees
WHERE job_id =
(SELECT job_id
FROM employees
WHERE last_name = 'Haas');

子查詢不返回任何行

5) 非法使用子查詢

SELECT employee_id, last_name
FROM employees
WHERE salary =
(SELECT MIN(salary)
FROM employees
GROUP BY department_id);

多行子查詢使用單行比較符

4. 多行子查詢

也稱為集合比較子查詢
內查詢返回多行
使用多行比較操作符

1) 多行比較操作符

操作符	含義
IN	等于列表中的任意一個
ANY	需要和單行比較操作符一起使用，和子查詢返回的某一個值比較
ALL	需要和單行比較操作符一起使用，和子查詢返回的所有值比較
SOME	實際上是ANY的別名，作用相同，一般常使用ANY

2) 代碼示例

題目：返回其它job_id中比job_id為‘IT_PROG’部門任一工資低的員工的員工號、姓名、job_id 以及salary

SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE job_id <> 'IT_PROG' 
AND salary < ANY(
	SELECT salary
    FROM emplyees
    WHERE job_id = 'IT_PROG'
);

題目：查詢平均工資最低的部門id

#方式1：
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) = (
        SELECT MIN(avg_sal)
        FROM (
            SELECT AVG(salary) avg_sal
            FROM employees
            GROUP BY department_id
            ) dept_avg_sal
);

#方式2：
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) <= ALL (
        SELECT AVG(salary) avg_sal
        FROM employees
        GROUP BY department_id
);

3) 空值問題

SELECT last_name
FROM employees
WHERE employee_id NOT IN (
    SELECT manager_id
    FROM employees
    WHERE manager_id IS NOT NULL
);

5. 相關子查詢

如果子查詢的執行依賴于外部查詢，通常情況下都是因為子查詢中的表用到了外部的表，并進行了條件關聯，因此每執行一次外部查詢，子查詢都要重新計算一次，這樣的子查詢就稱之為關聯子查詢。

相關子查詢按照一行接一行的順序執行，主查詢的每一行都執行一次子查詢。

說明：子查詢中使用主查詢中的列

1) 代碼示例

題目：查詢員工中工資大于本部門平均工資的員工的last_name,salary和其department_id

# 方式一：使用相關子查詢
SELECT last_name, salary, department
FROM employees e1
WHERE salary > (
		SELECT AVG(salary)
    	FROM employees e2
    	WHERE department_id = e1.`department_id`
);
# 方式二：在FROM中聲明子查詢
SELECT e.last_name, e.salary, e.department_id
FROM employees e, (
    			SELECT department_id, AVG(salary) avg_sal
    			FROM employees
    			GROUP BY department_id) t_dept_avg_salary
WHERE e.department_id = t_dept_avg_salary.department_id
AND e.salary > t_dept_avg_salary.avg_sal;

在ORDER BY 中使用子查詢：

查詢員工的id,salary,按照department_name 排序

SELECT employee_id, salary
FROM employees e
ORDER BY (
	SELECT department_name
    FROM departments d
    WHERE e.`department_id` = d.`department_id`
);

題目：若employees表中employee_id與job_history表中employee_id相同的數目不小于2，輸出這些相同 id的員工的employee_id,last_name和其job_id

SELECT e.employee_id, last_name,e.job_id
FROM employees e
WHERE 2 <= (SELECT COUNT(*)
        FROM job_history
        WHERE employee_id = e.employee_id
);

2) EXISTS 與 NOT EXISTS 關鍵字

關聯子查詢通常也會和 EXISTS操作符一起來使用，用來檢查在子查詢中是否存在滿足條件的行。
如果在子查詢中不存在滿足條件的行：
- 條件返回 FALSE
- 繼續在子查詢中查找
如果在子查詢中存在滿足條件的行：
- 不在子查詢中繼續查找
- 條件返回 TRUE
NOT EXISTS關鍵字表示如果不存在某種條件，則返回TRUE，否則返回FALSE。

題目：查詢公司管理者的employee_id，last_name，job_id，department_id信息

# 方式一：EXISTS
SELECT employee_id, last_name, job_id, department_id
FROM employees e1
WHERE EXISTS ( SELECT *
        FROM employees e2
        WHERE e2.manager_id =
        e1.employee_id
);

# 方式二：自連接
SELECT DISTINCT e1.employee_id, e1.last_name, e1.job_id, e1.department_id
FROM employees e1 JOIN employees e2
ON e1.employee_id = e2.manager_id;

# 方式三：IN
SELECT employee_id, last_name, job_id, department_id
WHERE employee_id IN (
        SELECT DISTINCT manager_id
        FROM employees
);

題目：查詢departments表中，不存在于employees表中的部門的department_id和department_name

# 方式一：
SELECT d.department_id, d.department_name
FROM departments e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL;

# 方式二：
SELECT department_id, department_name
FROM departments d
WHERE NOT EXISTS (
	SELECT *
    FROM employees e
    WHERE d.`department_id` = e.`department_id`
);

3) 相關更新

UPDATE table1 alias1
SET column = (SELECT expression
FROM table2 alias2
WHERE alias1.column = alias2.column);

使用相關子查詢依據一個表中的數據更新另一個表的數據。

題目：在employees中增加一個department_name字段，數據為員工對應的部門名稱

# 1）
ALTER TABLE employees
ADD(department_name VARCHAR2(14));

# 2）
UPDATE employees e
SET department_name = (SELECT department_name
FROM departments d
WHERE e.department_id = d.department_id);

4) 相關刪除

DELETE FROM table1 alias1
WHERE column operator (SELECT expression
FROM table2 alias2
WHERE alias1.column = alias2.column);

使用相關子查詢依據一個表中的數據刪除另一個表的數據。

題目：刪除表employees中，其與emp_history表皆有的數據

DELETE FROM employees e
WHERE employee_id in(
    SELECT employee_id
    FROM emp_history
    WHERE employee_id = e.employee_id
);

6. 思考題

問題：誰的工資比Abel的高？解答：

#方式1：自連接
SELECT e2.last_name,e2.salary
FROM employees e1,employees e2
WHERE e1.last_name = 'Abel'
AND e1.`salary` < e2.`salary`;

#方式2：子查詢
SELECT last_name,salary
FROM employees
WHERE salary > (
    SELECT salary
    FROM employees
    WHERE last_name = 'Abel'
);

問題：以上兩種方式有好壞之分嗎？

解答：自連接方式好！

題目中可以使用子查詢，也可以使用自連接。一般情況建議你使用自連接，因為在許多 DBMS 的處理過程中，對于自連接的處理速度要比子查詢快得多。可以這樣理解：子查詢實際上是通過未知表進行查詢后的條件判斷，而自連接是通過已知的自身數據表進行條件判斷，因此在大部分 DBMS 中都對自連接處理進行了優化。

7. 課后練習

查詢和Zlotkey相同部門的員工姓名和工資

SELECT last_name, salary
FROM employees
WHERE department_id = (
	SELECT department_id
    FROM employees
    WHERE last_name = 'Zlotkey'
);

查詢工資比公司平均工資高的員工的員工號，姓名和工資。

SELECT employee_id, last_name, salary
FROM employees
WHERE salary > (
	SELECT AVG(salary)
    FROM employee
);

選擇工資大于所有JOB_ID = 'SA_MAN' 的員工的工資的員工的last_name, job_id, salary

SELECT last_name, job_id, salary
FROM employees
WHERE salary > ALL (
	SELECT salary
    FROM employees
    WHERE job_id = 'SA_MAN'
);

查詢和姓名中包含字母u的員工在相同部門的員工的員工號和姓名

SELECT employee_id, last_name
FROM eployees
WHERE department_id IN (
    SELECT DISTINCT department_id
    FROM employees
    WHERE last_name LIKE '%u%'
);

查詢在部門的location_id為1700的部門工作的員工的員工號

SELECT employee_id
FROM employees
WHERE department_id IN (
	SELECT department_id
    FROM departments
    WHERE location_id = 1700
);

查詢管理者是King的員工姓名和工資

SELECT last_name, salary
FROM employees
WHERE manage_id IN (
	SELECT employee_id
    FROM employees
    WHERE last_name = 'King'
);

查詢工資最低的員工信息 (last_name, salary)

SELECT last_name, salary
FROM employees
WHERE salary = (
	SELECT MIN(salary)
    FROM employees
);

查詢平均工資最低的部門信息

# 方式一
SELECT *
FROM departments
WHERE department_id = (
	SELECT department_id
    FROM employees
    GROUP BY department_id
    HAVING AVG(salary) = (
    	SELECT MIN(avg_sal)
        FROM (
        	SELECT AVG(salary) avg_sal
            FROM employees
            GROUP BY department_id
        ) t_dept_avg_sal
    )
);

# 方式二
SELECT *
FROM departments
WHERE department_id = (
	SELECT department_id
    FROM employees
    GROUP BY department_id
    HAVING AVG(salary) <= ALL (
        SELECT AVG(salary) avg_sal
        FROM employees
        GROUP BY department_id
    )
);

# 方式三: LIMIT
SELECT *
FROM departments
WHERE department_id IN (
    SELECT department_id
    FROM employees
    GROUP BY department_id
    HAVING AVG(salary) = (
    	SELECT AVG(salary) avg_sal
        FROM employees
        GROUP BY department_id
        ORDER BY avg_sal ASC
        LIMIT 1
    )
);

# 方式四
SELECT d.*
FROM departments d, (
	SELECT department_id, AVG(salary) avg_sal
    FROM employees
    GROUP BY department_id
    ORDER BY avg_sal ASC
    LIMIT 0,1
) t_dept_avg_sal
WHERE d.`department_id` = t_dept_avg_sal.`department_id`;

查詢平均工資最低的部門信息和該部門的平均工資 (相關子查詢)

SELECT d.*, (SELECT AVG(salary) FROM employees WHERE department_id = d.`department_id`) avg_sal
FROM departments d, (
	SELECT department_id, AVG(salary) avg_sal
    FROM employees
    GROUP BY department_id
    ORDER BY avg_sal ASC
    LIMIT 0,1
) t_dept_avg_sal
WHERE d.`department_id` = t_dept_avg_sal.`department_id`;

查詢平均工資最高的job信息

SELECT *
FROM jobs
WHERE job_id = (
	SELECT job_id
    FROM employees
    GROUP BY job_id
    HAVING AVG(salary) = (
    	SELECT MAX(avg_sal)
        FROM (
        	SELECT AVG(salary) avg_sal
            FROM employees
            GROUP BY job_id
        ) t_job_avg_sal
    )
);

查詢平均工資高于公司平均工資的部門有哪些？

SELECT depatment_id
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
HAVING AVG(salary) > (
	SELECT AVG(salary)
    FROM eployees
);

查詢出公司中所有manager的詳細信息

# 方式1：自連接
SELECT DISTINCT *
FROM employees emp, employees manager
WHERE emp.`manager_id` = manager.`employee_id`;

SELECT DISTINCT *
FROM employees emp JOIN employees manager
ON emp.`manager_id` = manager.`employee_id`; 

# 方式2：子查詢
SELECT *
FROM employees
WHERE employee_id IN (
	SELECT manager_id
    FROM employees
);

# 方式3：EXISTS
SELECT *
FROM employees manager
WHERE EXISTS (
	SELECT *
    FROM employees emp
    WHERE manager.`employee_id` = emp.`manager_id`
);

各個部門中，最高工資中最低的那個部門的最低工資是多少？

# 方式一：
SELECT MIN(salary)
FROM employees
WHERE department_id = (
    SELECT department_id
    FROM employees
	GROUP BY department_id
	HAVING MAX(salary) = (
    	SELECT MIN(max_sal)
        FROM (
        	SELECT MAX(salary) max_sal
            FROM employees
            GROUP BY department_id
        ) t_dept_max_sal
    ) 
);

# 方式二：
SELECT MIN(salary)
FROM employees
WHERE department_id = (
    SELECT department_id
    FROM employees
	GROUP BY department_id
	HAVING MAX(salary) <= ALL (
        SELECT MAX(salary)
        FROM employees
        GROUP BY department_id
    ) 
);

# 方式三：
SELECT MIN(salary)
FROM employees
WHERE department_id = (
    SELECT department_id
    FROM employees
	GROUP BY department_id
	HAVING MAX(salary) = (
        SELECT MAX(salary) max_sal
        FROM employees
        GROUP BY department_id
        ORDER BY max_sal ASC
        LIMIT 0,1
    ) 
);

# 方式四：
FROM employees e, (
	SELECT department_id, MAX(salary) max_sal
    FROM employees
    GROUP BY department_id
    ORDER BY max_sal ASC
    LIMIT 0,1
) t_dept_max_sal
WHERE e.`department_id` = t_dept_max_sal.`department_id`;

查詢平均工資最高的部門的manager的詳細信息：last_name, department_id, email, salary

SELECT last_name, department_id, email, salary
FROM employees
WHERE employee_id IN (
	SELECT DISTINCT manager_id
    FROM employees
    WHERE department_id = (
    	SELECT department_id
        FROM employees
        GROUP BY department_id
        HAVING AVG(salary) = (
        	SELECT MAX(avg_sal)
            FROM (
            	SELECT AVG(salary) avg_sal
                FROM employees
                GROUP BY department_id
            ) t_dept_avg_sal
        )
    )
);

SELECT last_name, department_id, email, salary
FROM employees
WHERE employee_id IN (
    SELECT DISTINCT manager_id
    FROM employees e, (
        SELECT department_id, AVG(salary) avg_sal
        FROM employees
        GROUP BY department_id
        ORDER BY avg_sal DESC
        LIMIT 0,1
    ) t_dept_avg_sal
    WHERE e.`department_id` = t_dept_avg_sal.`department_id`
);

查詢部門的部門號，其中不包括job_id是"ST_CLERK"的部門號

SELECT department_id
FROM departments
WHERE department_id NOT IN (
	SELECT DISTINCT department_id
    FROM employees
    WHERE job_id = `ST_CLERK`
);

SELECT department_id
FROM department d
WHERE NOT EXISTS (
	SELECT *
    FROM employees e
    WHERE d.`department_id` = e.`department_id`
    AND e.`job_id` = 'ST_CLERK'
);

選擇所有沒有管理者的員工的last_name

SELECT last_name
FROM employees emp
WHERE NOT EXISTS (
	SELECT *
    FROM employees mgr
    WHERE emp.`manager_id` = mgr.`employee_id`
);

查詢員工號、姓名、雇用時間、工資，其中員工的管理者為 ‘De Haan'

SELECT employee_id, last_name, hire_date, salary
FROM employee
WHERE manager_id IN (
	SELECT manager_id
    FROM employee
    WHERE last_name = 'De Haan'
);

查詢各部門中工資比本部門平均工資高的員工的員工號，姓名和工資（相關子查詢）

SELECT department_id, last_name, salary
FROM employees e1
WHERE salary > (
	SELECT AVG(salary)
    FROM employees e2
    WHERE e2.`department_id` = e1.`department_id`
);

SELECT e.last_name, e.salary, e.department_id
FROM employees e, (
	SELECT department_id, AVG(salary) avg_sal
    FROM employees
    GROUP BY department_id
) t_dept_avg_sal
WHERE e.`department_id` = t_dept_avg_sal.`department_id`
AND e.`salary` > t_dept_avg_sal.`avg_sal`;

查詢每個部門下的部門人數大于5的部門名稱（相關子查詢）

SELECT department_name
FROM departments d
WHERE 5 < (
	SELECT COUNT(*)
    FROM employees e
    WHERE d.`department_id` = e.`department_id`
);

查詢每個國家下的部門個數大于2的國家編號（相關子查詢）

SELECT country_id
FROM locations l
WHERE 2 < (
	SELECT COUNT(*)
    FROM department d
    WHERE l.`location_id` = d.`location_id`
);

創建和管理表

1. 基礎知識

1) 標識符命名規則

數據庫名、表名不得超過30個字符，變量名限制為29個
必須只能包含 A–Z, a–z, 0–9, _共63個字符
數據庫名、表名、字段名等對象名中間不要包含空格
同一個MySQL軟件中，數據庫不能同名；同一個庫中，表不能重名；同一個表中，字段不能重名
必須保證你的字段沒有和保留字、數據庫系統或常用方法沖突。如果堅持使用，請在SQL語句中使用`（著重號）引起來
保持字段名和類型的一致性：在命名字段并為其指定數據類型的時候一定要保證一致性，假如數據類型在一個表里是整數，那在另一個表里可就別變成字符型了

2) MySQL中的數據類型

類型	數據變量
整數類型	TINYINT、SMALLINT、MEDIUMINT、INT(或INTEGER)、BIGINT
浮點類型	FLOAT、DOUBLE
定點數類型	DECIMAL
位類型	BIT
日期時間類型	YEAR、TIME、DATE、DATETIME、TIMESTAMP
文本字符串類型	CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT
枚舉類型	ENUM
集合類型	SET
二進制字符串類型	BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB
JSON類型	JSON對象、JSON數組
空間數據類型	單值：GEOMETRY、POINT、LINESTRING、POLYGON；集合：MULTIPOINT、MULTILINESTRING、MULTIPOLYGON、 GEOMETRYCOLLECTION

其中，常用的幾類類型介紹如下：

數據類型	描述
INT	從-2^31到231-1的整型數據。存儲大小為 4個字節
CHAR(size)	FLOAT、DOUBLE
VARCHAR(size)	DECIMAL
FLOAT(M,D)	BIT
DOUBLE(M,D)	YEAR、TIME、DATE、DATETIME、TIMESTAMP
DECIMAL(M,D)	CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT
DATE	ENUM
BLOB	SET
TEXT	BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB

2. 創建和管理數據庫

1) 創建數據庫

方式1：創建數據庫

CREATE DATABASE 數據庫名;

方式2：創建數據庫并指定字符集

CREATE DATABASE 數據庫名 CHARACTER SET 字符集;

方式3：判斷數據庫是否已經存在，不存在則創建數據庫（推薦）

CREATE DATABASE IF NOT EXISTS 數據庫名;

如果MySQL中已經存在相關的數據庫，則忽略創建語句，不再創建數據庫。

注意：DATABASE 不能改名。一些可視化工具可以改名，它是建新庫，把所有表復制到新庫，再刪舊庫完成的。

2) 使用數據庫

查看當前所有的數據庫

SHOW DATABASES; #有一個S，代表多個數據庫

查看當前正在使用的數據庫

SELECT DATABASE(); #使用的一個 mysql 中的全局函數

查看指定庫下所有的表

SHOW TABLES FROM 數據庫名

查看數據庫的創建信息

SHOW CREATE DATABASE 數據庫名;
或者：
SHOW CREATE DATABASE 數據庫名\G

使用/切換數據庫

USE 數據庫名;

注意：要操作表格和數據之前必須先說明是對哪個數據庫進行操作，否則就要對所有對象加上“數據庫名.”。

3) 修改數據庫

更改數據庫字符集

ALTER DATABASE 數據庫名 CHARACTER SET 字符集; #比如：gbk、utf8等

方式1：刪除指定的數據庫

DROP DATABASE 數據庫名;

方式2：刪除指定的數據庫（推薦）

DROP DATABASE IF EXISTS 數據庫名;

3. 創建表

1) 創建方式1

語法格式：

CREATE TABLE [IF NOT EXISTS] 表名(
字段1, 數據類型 [約束條件] [默認值],
字段2, 數據類型 [約束條件] [默認值],
字段3, 數據類型 [約束條件] [默認值],
……
[表約束條件]
);

加上了IF NOT EXISTS關鍵字，則表示：如果當前數據庫中不存在要創建的數據表，則創建數據表；如果當前數據庫中已經存在要創建的數據表，則忽略建表語句，不再創建數據表。

2) 創建方式2

使用 AS subquery 選項，將創建表和插入數據結合起來

CREATE TABLE 表名
	[(column, column, ...)]
AS subquery;

指定的列和子查詢中的列要一一對應
通過列名和默認值定義列

CREATE TABLE dept80
AS
SELECT employee_id, last_name, salary*12 ANNSAL, hire_date
FROM employees
WHERE department_id = 80;

3) 查看數據表結構

在MySQL中創建好數據表之后，可以查看數據表的結構。MySQL支持使用 DESCRIBE/DESC 語句查看數據表結構，也支持使用 SHOW CREATE TABLE 語句查看數據表結構。

語法格式如下：

SHOW CREATE TABLE 表名\G

使用SHOW CREATE TABLE語句不僅可以查看表創建時的詳細語句，還可以查看存儲引擎和字符編碼。

4. 修改表

修改表指的是修改數據庫中已經存在的數據表的結構。

使用 ALTER TABLE 語句可以實現：

向已有的表中添加列
修改現有表中的列
刪除現有表中的列
重命名現有表中的列

1) 追加一個列

語法格式如下：

ALTER TABLE 表名 ADD 【COLUMN】 字段名 字段類型 【FIRST|AFTER 字段名】;

舉例：

ALTER TABLE dept80
ADD job_id varchar(15);

2) 修改一個列

可以修改列的數據類型，長度、默認值和位置
修改字段數據類型、長度、默認值、位置的語法格式如下：

ALTER TABLE 表名 MODIFY 【COLUMN】 字段名1 字段類型 【DEFAULT 默認值】【FIRST|AFTER 字段名2】;

舉例：

ALTER TABLE dept80
MODIFY salary double(9,2) default 1000;

對默認值的修改只影響今后對表的修改
此外，還可以通過此種方式修改列的約束。

3) 重命名一個列

使用 CHANGE old_column new_column dataType子句重命名列。語法格式如下：

ALTER TABLE 表名 CHANGE 【column】 列名 新列名 新數據類型;

舉例：

ALTER TABLE dept80
CHANGE department_name dept_name varchar(15);

4) 刪除一個列

刪除表中某個字段的語法格式如下：

ALTER TABLE 表名 DROP 【COLUMN】字段名

5) 更改表名

方式一：使用RENAME

RENAME TABLE emp
TO myemp;

方式二：

ALTER table dept
RENAME [TO] detail_dept; -- [TO]可以省略

必須是對象的擁有者

5. 刪除表

在MySQL中，當一張數據表沒有與其他任何數據表形成關聯關系時，可以將當前數據表直接刪除。
數據和結構都被刪除
所有正在運行的相關事務被提交
所有相關索引被刪除

語法格式：

DROP TABLE [IF EXISTS] 數據表1 [, 數據表2, …, 數據表n];

IF EXISTS 的含義為：如果當前數據庫中存在相應的數據表，則刪除數據表；如果當前數據庫中不存在相應的數據表，則忽略刪除語句，不再執行刪除數據表的操作。

舉例：

DROP TABLE dept80;

DROP TABLE 語句不能回滾

6. 清空表

TRUNCATE TABLE語句：
- 刪除表中所有的數據
- 釋放表的存儲空間
舉例：

TRUNCATE TABLE detail_dept;

TRUNCATE語句不能回滾，而使用 DELETE 語句刪除數據，可以回滾

阿里開發規范：【參考】TRUNCATE TABLE 比 DELETE 速度快，且使用的系統和事務日志資源少，但 TRUNCATE 無事務且不觸發 TRIGGER，有可能造成事故，故不建議在開發代碼中使用此語句。說明：TRUNCATE TABLE 在功能上與不帶 WHERE 子句的 DELETE 語句相同。

7. 內容擴展

拓展1：阿里巴巴《Java開發手冊》之MySQL字段命名

【強制】表名、字段名必須使用小寫字母或數字，禁止出現數字開頭，禁止兩個下劃線中間只出現數字。數據庫字段名的修改代價很大，因為無法進行預發布，所以字段名稱需要慎重考慮。
- 正例：aliyun_admin，rdc_config，level3_name
- 反例：AliyunAdmin，rdcConfig，level_3_name
【強制】禁用保留字，如 desc、range、match、delayed 等，請參考 MySQL 官方保留字。
【強制】表必備三字段：id, gmt_create, gmt_modified。
- 說明：其中 id 必為主鍵，類型為BIGINT UNSIGNED、單表時自增、步長為 1。gmt_create, gmt_modified 的類型均為 DATETIME 類型，前者現在時表示主動式創建，后者過去分詞表示被動式更新
【推薦】表的命名最好是遵循 “業務名稱_表的作用”。
- 正例：alipay_task 、 force_project、 trade_config
【推薦】庫名與應用名稱盡量一致。
【參考】合適的字符存儲長度，不但節約數據庫表空間、節約索引存儲，更重要的是提升檢索速度。
- 正例：無符號值可以避免誤存負數，且擴大了表示范圍。

擴展2：操作注意要求

表刪除操作將把表的定義和表中的數據一起刪除，并且MySQL在執行刪除操作時，不會有任何的確認信息提示，因此執行刪除操時應當慎重。在刪除表前，最好對表中的數據進行備份，這樣當操作失誤時可以對數據進行恢復，以免造成無法挽回的后果。
同樣的，在使用 ALTER TABLE 進行表的基本修改操作時，在執行操作過程之前，也應該確保對數據進行完整的備份，因為數據庫的改變是無法撤銷的，如果添加了一個不需要的字段，可以將其刪除；相同的，如果刪除了一個需要的列，該列下面的所有數據都將會丟失。

擴展3：MySQL8新特性—DDL的原子化

在MySQL 8.0版本中，InnoDB表的DDL支持事務完整性，即 DDL操作要么成功要么回滾。DDL操作回滾日志寫入到data dictionary數據字典表mysql.innodb_ddl_log（該表是隱藏的表，通過show tables無法看到）中，用于回滾操作。通過設置參數，可將DDL操作日志打印輸出到MySQL錯誤日志中。

數據處理之增刪改

1. 插入數據

1) 方式1：VALUES的方式添加

使用這種語法一次只能向表中插入一條數據。

情況1：為表的所有字段按默認順序插入數據

INSERT INTO 表名
VALUES (value1,value2,....);

值列表中需要為表的每一個字段指定值，并且值的順序必須和數據表中字段定義時的順序相同。

舉例：

INSERT INTO departments
VALUES (70, 'Pub', 100, 1700);

情況2: 指定字段名插入數據

為表的指定字段插入數據，就是在INSERT語句中只向部分字段中插入值，而其他字段的值為表定義時的默認值。在 INSERT 子句中隨意列出列名，但是一旦列出，VALUES中要插入的value1,....valuen需要與 column1,...columnn列一一對應。如果類型不同，將無法插入，并且MySQL會產生錯誤。

舉例：

INSERT INTO departments(department_id, department_name)
VALUES (80, 'IT');

情況3：同時插入多條記錄

INSERT語句可以同時向數據表中插入多條記錄，插入時指定多個值列表，每個值列表之間用逗號分隔開，基本語法格式如下：

INSERT INTO table_name
VALUES
(value1 [,value2, …, valuen]),
(value1 [,value2, …, valuen]),
……
(value1 [,value2, …, valuen]);

或者

INSERT INTO table_name(column1 [, column2, …, columnn])
VALUES
(value1 [,value2, …, valuen]),
(value1 [,value2, …, valuen]),
……
(value1 [,value2, …, valuen]);

使用INSERT同時插入多條記錄時，MySQL會返回一些在執行單行插入時沒有的額外信息，這些信息的含義如下：

Records：表明插入的記錄條數。
Duplicates：表明插入時被忽略的記錄，原因可能是這些記錄包含了重復的主鍵值。
Warnings：表明有問題的數據值，例如發生數據類型轉換。

一個同時插入多行記錄的INSERT語句等同于多個單行插入的INSERT語句，但是多行的INSERT語句在處理過程中效率更高。因為MySQL執行單條INSERT語句插入多行數據比使用多條INSERT語句快，所以在插入多條記錄時最好選擇使用單條INSERT語句的方式插入。

2) 方式2：將查詢結果插入到表中

INSERT還可以將SELECT語句查詢的結果插入到表中，此時不需要把每一條記錄的值一個一個輸入，只需要使用一條INSERT語句和一條SELECT語句組成的組合語句即可快速地從一個或多個表中向一個表中插入多行

INSET INTO 目標表名
(tar_column1 [, tar_column2, ..., tar_columnn])
SELECT
(src_column1 [, src_column2, …, src_columnn])
FROM 源表名
[WHERE condition]

在 INSERT 語句中加入子查詢。
不必書寫 VALUES 子句。
子查詢中的值列表應與 INSERT 子句中的列名對應。

INSERT INTO emp2
SELECT *
FROM employees
WHERE department_id = 90;

INSERT INTO sales_reps(id, name, salary, commission_pct)
SELECT employee_id, last_name, salary, commission_pct
FROM employees
WHERE job_id LIKE '%REP%';

2. 更新數據

使用 UPDATE 語句更新數據。語法如下：

UPDATE table_name
SET column1=value1, column2=value2, ..., column=valuen
[WHERE condition]

可以一次更新多條數據。
如果需要回滾數據，需要保證在DML前，進行設置：SET AUTOCOMMIT = FALSE;

使用 WHERE 子句指定需要更新的數據。

UPDATE employees
SET department_id = 70
WHERE employee_id = 113;

如果省略 WHERE 子句，則表中的所有數據都將被更新。

3. 刪除數據

DELETE FROM table_name [WHERE <condition>];

table_name指定要執行刪除操作的表；“[WHERE ]”為可選參數，指定刪除條件，如果沒有WHERE子句， DELETE語句將刪除表中的所有記錄。

4. MySQL8新特性：計算列

什么叫計算列呢？簡單來說就是某一列的值是通過別的列計算得來的。例如，a列值為1、b列值為2，c列不需要手動插入，定義a+b的結果為c的值，那么c就是計算列，是通過別的列計算得來的。

在MySQL 8.0中，CREATE TABLE 和 ALTER TABLE 中都支持增加計算列。下面以CREATE TABLE為例進行講解。

舉例：定義數據表tb1，然后定義字段id、字段a、字段b和字段c，其中字段c為計算列，用于計算a+b的值。首先創建測試表tb1，語句如下：

CREATE TABLE tb1(
id INT,
a INT,
b INT,
c INT GENERATED ALWAYS AS (a + b) VIRTUAL
);

數據類型精講

1. MySQL中的數據類型

類型	舉例
整數類型	TINYINT、SMALLINT、MEDIUMINT、INT(或INTEGER)、BIGINT
浮點類型	FLOAT、DOUBLE
定點數類型	DECIMAL
位類型	BIT
日期時間類型	YEAR、TIME、DATE、DATETIME、TIMESTAMP
文本字符串類型	CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT
枚舉類型	ENUM
集合類型	SET
二進制字符串類型	BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB
JSON類型	JSON對象、JSON數組
空間數據類型	單值類型：GEOMETRY、POINT、LINESTRING、POLYGON；集合類型：MULTIPOINT、MULTILINESTRING、MULTIPOLYGON、 GEOMETRYCOLLECTION

常見數據類型的屬性，如下：

MySQL關鍵字	含義
NULL	TINYINT、SMALLINT、MEDIUMINT、INT(或INTEGER)、BIGINT
NOT NULL	FLOAT、DOUBLE
DEFAULT	DECIMAL
PRIMARY KEY	BIT
AUTO_INCREMENT	YEAR、TIME、DATE、DATETIME、TIMESTAMP
UNSIGNED	CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT
CHARACTER SET name	ENUM

2. 整數類型

1) 類型介紹

整數類型一共有 5 種，包括 TINYINT、SMALLINT、MEDIUMINT、INT（INTEGER）和 BIGINT。

它們的區別如下表所示：

整數類型	字節	有符號數取值范圍	無符號數取值范圍
TINYINT	1	-128~127	0~255
SMALLINT	2	-32768~32767	0~65535
MEDIUMINT	3	-8388608~8388607	0~16777215
INT、INTEGER	4	-2147483648~2147483647	0~4294967295
BIGINT	8	-9223372036854775808~9223372036854775807	0~18446744073709551615

2) 可選屬性

整數類型的可選屬性有三個：

M : 表示顯示寬度，M的取值范圍是(0, 255)。例如，int(5)：當數據寬度小于5位的時候在數字前面需要用字符填滿寬度。該項功能需要配合“ ZEROFILL ”使用，表示用“0”填滿寬度，否則指定顯示寬度無效。如果設置了顯示寬度，那么插入的數據寬度超過顯示寬度限制，會不會截斷或插入失敗？

答案：不會對插入的數據有任何影響，還是按照類型的實際寬度進行保存，即顯示寬度與類型可以存儲的值范圍無關。從MySQL 8.0.17開始，整數數據類型不推薦使用顯示寬度屬性。整型數據類型可以在定義表結構時指定所需要的顯示寬度，如果不指定，則系統為每一種類型指定默認的寬度值。

舉例：

CREATE TABLE test_int1 ( x TINYINT, y SMALLINT, z MEDIUMINT, m INT, n BIGINT );

查看表結構（MySQL5.7中顯式如下，MySQL8中不再顯式范圍）

mysql> desc test_int1;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| x | tinyint(4) | YES | | NULL | |
| y | smallint(6) | YES | | NULL | |
| z | mediumint(9) | YES | | NULL | |
| m | int(11) | YES | | NULL | |
| n | bigint(20) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

TINYINT有符號數和無符號數的取值范圍分別為-128~127和0~255，由于負號占了一個數字位，因此 TINYINT默認的顯示寬度為4。同理，其他整數類型的默認顯示寬度與其有符號數的最小值的寬度相同。

UNSIGNED

UNSIGNED : 無符號類型（非負），所有的整數類型都有一個可選的屬性UNSIGNED（無符號屬性），無符號整數類型的最小取值為0。所以，如果需要在MySQL數據庫中保存非負整數值時，可以將整數類型設置為無符號類型。 int類型默認顯示寬度為int(11)，無符號int類型默認顯示寬度為int(10)。

ZEROFILL

ZEROFILL : 0填充,（如果某列是ZEROFILL，那么MySQL會自動為當前列添加UNSIGNED屬性），如果指定了ZEROFILL只是表示不夠M位時，用0在左邊填充，如果超過M位，只要不超過數據存儲范圍即可。

原來，在 int(M) 中，M 的值跟 int(M) 所占多少存儲空間并無任何關系。 int(3)、int(4)、int(8) 在磁盤上都是占用 4 bytes 的存儲空間。也就是說，int(M)，必須和UNSIGNED ZEROFILL一起使用才有意義。如果整數值超過M位，就按照實際位數存儲。只是無須再用字符 0 進行填充。

3) 適用場景

TINYINT ：一般用于枚舉數據，比如系統設定取值范圍很小且固定的場景。

SMALLINT ：可以用于較小范圍的統計數據，比如統計工廠的固定資產庫存數量等。

MEDIUMINT ：用于較大整數的計算，比如車站每日的客流量等。

INT、INTEGER ：取值范圍足夠大，一般情況下不用考慮超限問題，用得最多。比如商品編號。

BIGINT ：只有當你處理特別巨大的整數時才會用到。比如雙十一的交易量、大型門戶網站點擊量、證券公司衍生產品持倉等。

4) 如何選擇？

在評估用哪種整數類型的時候，你需要考慮存儲空間和可靠性的平衡問題：一方面，用占用字節數少的整數類型可以節省存儲空間；另一方面，要是為了節省存儲空間，使用的整數類型取值范圍太小，一旦遇到超出取值范圍的情況，就可能引起系統錯誤，影響可靠性。

舉個例子，商品編號采用的數據類型是 INT。原因就在于，客戶門店中流通的商品種類較多，而且，每天都有舊商品下架，新商品上架，這樣不斷迭代，日積月累。

如果使用 SMALLINT 類型，雖然占用字節數比 INT 類型的整數少，但是卻不能保證數據不會超出范圍 65535。相反，使用 INT，就能確保有足夠大的取值范圍，不用擔心數據超出范圍影響可靠性的問題。

你要注意的是，在實際工作中，系統故障產生的成本遠遠超過增加幾個字段存儲空間所產生的成本。因此，我建議你首先確保數據不會超過取值范圍，在這個前提之下，再去考慮如何節省存儲空間。

3. 浮點類型

1) 類型介紹

浮點數和定點數類型的特點是可以處理小數，你可以把整數看成小數的一個特例。因此，浮點數和定點數的使用場景，比整數大多了。 MySQL支持的浮點數類型，分別是 FLOAT、DOUBLE、REAL。

FLOAT 表示單精度浮點數；
DOUBLE 表示雙精度浮點數；
REAL默認就是 DOUBLE。如果你把 SQL 模式設定為啟用“ REAL_AS_FLOAT ”，那么，MySQL 就認為 REAL 是 FLOAT。如果要啟用“REAL_AS_FLOAT”，可以通過以下 SQL 語句實現：
```
SET sql_mode = “REAL_AS_FLOAT”;
```

問題：為什么浮點數類型的無符號數取值范圍，只相當于有符號數取值范圍的一半，也就是只相當于有符號數取值范圍大于等于零的部分呢？

MySQL 存儲浮點數的格式為：符號(S) 、尾數(M) 和階碼(E) 。因此，無論有沒有符號，MySQL 的浮點數都會存儲表示符號的部分。因此，所謂的無符號數取值范圍，其實就是有符號數取值范圍大于等于零的部分。

2) 數據精度說明

對于浮點類型，在MySQL中單精度值使用 4 個字節，雙精度值使用 8 個字節。

MySQL允許使用非標準語法（其他數據庫未必支持，因此如果涉及到數據遷移，則最好不要這么用）： FLOAT(M,D) 或 DOUBLE(M,D) 。這里，M稱為精度，D稱為標度。(M,D)中 M=整數位+小數位，D=小數位。 D<=M<=255，0<=D<=30。

例如，定義為FLOAT(5,2)的一個列可以顯示為-999.99-999.99。如果超過這個范圍會報錯。
FLOAT和DOUBLE類型在不指定(M,D)時，默認會按照實際的精度（由實際的硬件和操作系統決定）來顯示。
說明：浮點類型，也可以加 UNSIGNED ，但是不會改變數據范圍，例如：FLOAT(3,2) UNSIGNED仍然只能表示0-9.99的范圍。
不管是否顯式設置了精度(M,D)，這里MySQL的處理方案如下：
- 如果存儲時，整數部分超出了范圍，MySQL就會報錯，不允許存這樣的值
- 如果存儲時，小數點部分若超出范圍，就分以下情況：
  - 若四舍五入后，整數部分沒有超出范圍，則只警告，但能成功操作并四舍五入刪除多余的小數位后保存。例如在FLOAT(5,2)列內插入999.009，近似結果是999.01。
  - 若四舍五入后，整數部分超出范圍，則MySQL報錯，并拒絕處理。如FLOAT(5,2)列內插入 999.995和-999.995都會報錯。
從MySQL 8.0.17開始，FLOAT(M,D) 和DOUBLE(M,D)用法在官方文檔中已經明確不推薦使用，將來可能被移除。另外，關于浮點型FLOAT和DOUBLE的UNSIGNED也不推薦使用了，將來也可能被移除。

3) 精度誤差說明

浮點數類型有個缺陷，就是不精準。下面我來重點解釋一下為什么 MySQL 的浮點數不夠精準。比如，我們設計一個表，有f1這個字段，插入值分別為0.47,0.44,0.19，我們期待的運行結果是：0.47 + 0.44 + 0.19 = 1.1。而使用sum之后查詢：

CREATE TABLE test_double2(
f1 DOUBLE
);
INSERT INTO test_double2
VALUES(0.47),(0.44),(0.19);

mysql> SELECT SUM(f1)
-> FROM test_double2;
+--------------------+
| SUM(f1) |
+--------------------+
| 1.0999999999999999 |
+--------------------+
1 row in set (0.00 sec)

查詢結果是 1.0999999999999999。看到了嗎？雖然誤差很小，但確實有誤差。你也可以嘗試把數據類型改成 FLOAT，然后運行求和查詢，得到的是， 1.0999999940395355。顯然，誤差更大了。

那么，為什么會存在這樣的誤差呢？問題還是出在 MySQL 對浮點類型數據的存儲方式上。

MySQL 用 4 個字節存儲 FLOAT 類型數據，用 8 個字節來存儲 DOUBLE 類型數據。無論哪個，都是采用二進制的方式來進行存儲的。比如 9.625，用二進制來表達，就是 1001.101，或者表達成 1.001101×2^3。如果尾數不是 0 或 5（比如 9.624），你就無法用一個二進制數來精確表達。進而，就只好在取值允許的范圍內進行四舍五入。

在編程中，如果用到浮點數，要特別注意誤差問題，因為浮點數是不準確的，所以我們要避免使用“=”來判斷兩個數是否相等。同時，在一些對精確度要求較高的項目中，千萬不要使用浮點數，不然會導致結果錯誤，甚至是造成不可挽回的損失。那么，MySQL 有沒有精準的數據類型呢？當然有，這就是定點數類型： DECIMAL 。

4. 定點數類型

1) 類型介紹

MySQL中的定點數類型只有 DECIMAL 一種類型。

類型	字節	有符號數取值范圍
DECIMAL(M,D),DEC,NUMERIC	M+2字節	有效范圍由M和D決定

使用 DECIMAL(M,D) 的方式表示高精度小數。其中，M被稱為精度，D被稱為標度。0<=M<=65， 0<=D<=30，D

DECIMAL(M,D)的最大取值范圍與DOUBLE類型一樣，但是有效的數據范圍是由M和D決定的。 DECIMAL 的存儲空間并不是固定的，由精度值M決定，總共占用的存儲空間為M+2個字節。也就是說，在一些對精度要求不高的場景下，比起占用同樣字節長度的定點數，浮點數表達的數值范圍可以更大一些。
定點數在MySQL內部是以字符串的形式進行存儲，這就決定了它一定是精準的。
當DECIMAL類型不指定精度和標度時，其默認為DECIMAL(10,0)。當數據的精度超出了定點數類型的精度范圍時，則MySQL同樣會進行四舍五入處理。
浮點數 vs 定點數
- 浮點數相對于定點數的優點是在長度一定的情況下，浮點類型取值范圍大，但是不精準，適用于需要取值范圍大，又可以容忍微小誤差的科學計算場景（比如計算化學、分子建模、流體動力學等）
- 定點數類型取值范圍相對小，但是精準，沒有誤差，適合于對精度要求極高的場景（比如涉及金額計算的場景）

2) 開發中的經驗

“由于 DECIMAL 數據類型的精準性，在我們的項目中，除了極少數（比如商品編號）用到整數類型外，其他的數值都用的是 DECIMAL，原因就是這個項目所處的零售行業，要求精準，一分錢也不能差。 ” ——來自某項目經理

5. 位類型：BIT

BIT類型中存儲的是二進制值，類似010110。

二進制字符串類型	長度	長度范圍	占用空間
BIT(M)	M	1 <= M <= 64	約為(M + 7)/8個字節

BIT類型，如果沒有指定(M)，默認是1位。這個1位，表示只能存1位的二進制值。這里(M)是表示二進制的位數，位數最小值為1，最大值為64。

6. 日期與時間類型

日期與時間是重要的信息，在我們的系統中，幾乎所有的數據表都用得到。原因是客戶需要知道數據的時間標簽，從而進行數據查詢、統計和處理。

MySQL有多種表示日期和時間的數據類型，不同的版本可能有所差異，MySQL8.0版本支持的日期和時間類型主要有：YEAR類型、TIME類型、DATE類型、DATETIME類型和TIMESTAMP類型。

YEAR 類型通常用來表示年
DATE 類型通常用來表示年、月、日
TIME 類型通常用來表示時、分、秒
DATETIME 類型通常用來表示年、月、日、時、分、秒
TIMESTAMP 類型通常用來表示帶時區的年、月、日、時、分、秒

類型	名稱	字節	日期格式	最小值	最大值
YEAR	年	1	YYYY或YY	1901	2155
TIME	時間	3	HH:MM:SS	-838:59:59	838:59:59
DATE	日期	3	YYYY-MM-DD	1000-01-01	9999-12-03
DATETIME	日期時間	8	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00	9999-12-31 23:59:59
TIMESTAMP	日期時間	4	YYYY-MM-DD HH:MM:SS	1970-01-01 00:00:00 UTC	2038-01-19 03:14:07UTC

可以看到，不同數據類型表示的時間內容不同、取值范圍不同，而且占用的字節數也不一樣，你要根據實際需要靈活選取。

為什么時間類型 TIME 的取值范圍不是 -23:59:59～23:59:59 呢？原因是 MySQL 設計的 TIME 類型，不光表示一天之內的時間，而且可以用來表示一個時間間隔，這個時間間隔可以超過 24 小時。

7. 文本字符串類型

MySQL中，文本字符串總體上分為 CHAR 、 VARCHAR 、 TINYTEXT 、 TEXT 、 MEDIUMTEXT 、 LONGTEXT 、 ENUM 、 SET 等類型。

8. ENUM類型

ENUM類型也叫作枚舉類型，ENUM類型的取值范圍需要在定義字段時進行指定。設置字段值時，ENUM 類型只允許從成員中選取單個值，不能一次選取多個值。其所需要的存儲空間由定義ENUM類型時指定的成員個數決定。

文本字符串類型	長度	長度范圍	占用的存儲空間
ENUM	L	1 <= L <= 65535	1或2個字節

當ENUM類型包含1～255個成員時，需要1個字節的存儲空間；
當ENUM類型包含256～65535個成員時，需要2個字節的存儲空間。
ENUM類型的成員個數的上限為65535個。

9. SET類型

當SET類型包含的成員個數不同時，其所占用的存儲空間也是不同的，具體如下：

成員個數范圍（L表示實際成員個數）	占用的存儲空間
1 <= L <= 8	1個字節
9 <= L <= 16	2個字節
17 <= L <= 24	3個字節
25 <= L <= 32	4個字節
33 <= L <= 64	8個字節

SET類型在存儲數據時成員個數越多，其占用的存儲空間越大。注意：SET類型在選取成員時，可以一次選擇多個成員，這一點與ENUM類型不同。

13. 小結及選擇建議

在定義數據類型時，如果確定是整數，就用 INT ；如果是小數，一定用定點數類型 DECIMAL(M,D) ；如果是日期與時間，就用 DATETIME 。這樣做的好處是，首先確保你的系統不會因為數據類型定義出錯。不過，凡事都是有兩面的，可靠性好，并不意味著高效。比如，TEXT 雖然使用方便，但是效率不如 CHAR(M) 和 VARCHAR(M)。

阿里巴巴《Java開發手冊》之MySQL數據庫：

任何字段如果為非負數，必須是 UNSIGNED
【強制】小數類型為 DECIMAL，禁止使用 FLOAT 和 DOUBLE。

說明：在存儲的時候，FLOAT 和 DOUBLE 都存在精度損失的問題，很可能在比較值的時候，得到不正確的結果。如果存儲的數據范圍超過 DECIMAL 的范圍，建議將數據拆成整數和小數并分開存儲。
【強制】如果存儲的字符串長度幾乎相等，使用 CHAR 定長字符串類型。
【強制】VARCHAR 是可變長字符串，不預先分配存儲空間，長度不要超過 5000。如果存儲長度大于此值，定義字段類型為 TEXT，獨立出來一張表，用主鍵來對應，避免影響其它字段索引效率。

約束

1. 約束的分類

根據約束數據列的限制，約束可分為：
- 單列約束：每個約束只約束一列
- 多列約束：每個約束可約束多列數據
根據約束的作用范圍，約束可分為：
- 列級約束：只能作用在一個列上，跟在列的定義后面
- 表級約束：可以作用在多個列上，不與列一起，而是單獨定義
根據約束起的作用，約束可分為：
- NOT NULL 非空約束，規定某個字段不能為空
- UNIQUE 唯一約束，規定某個字段在整個表中是唯一的
- PRIMARY KEY 主鍵(非空且唯一)約束
- FOREIGN KEY 外鍵約束
- CHECK 檢查約束
- DEFAULT 默認值約束

注意： MySQL不支持check約束，但可以使用check約束，而沒有任何效果

如何添加/ 刪除約束？

CREATE TABLE 時添加約束

ALTER TABLE 時增加約束、刪除約束

查看某個表已有的約束

#information_schema數據庫名（系統庫）
#table_constraints表名稱（專門存儲各個表的約束）
SELECT * FROM information_schema.table_constraints
WHERE table_name = '表名稱';

2. 非空約束

1) 作用

限定某個字段/ 某列的值不允許為空

2) 關鍵字

NOT NULL

3) 特點

默認，所有的類型的值都可以是NULL，包括INT、FLOAT等數據類型
非空約束只能出現在表對象的列上，只能某個列單獨限定非空，不能組合非空
一個表可以有很多列都分別限定了非空
空字符串''不等于NULL，0也不等于NULL

4) 添加非空約束

1. 建表時

CREATE TABLE 表名稱(
字段名 數據類型,
字段名 數據類型 NOT NULL,
字段名 數據類型 NOT NULL
);

2. 建表后

alter table 表名稱 modify 字段名 數據類型 not null;

5) 刪除非空約束

alter table 表名稱 modify 字段名 數據類型 NULL;#去掉not null，相當于修改某個非注解字段，該字段允許為空
或
alter table 表名稱 modify 字段名 數據類型;#去掉not null，相當于修改某個非注解字段，該字段允許為空

3. 唯一性約束

1) 作用

用來限制某個字段/某列的值不能重復。

2) 關鍵字

UNIQUE

3) 特點

同一個表可以有多個唯一約束。
唯一約束可以是某一個列的值唯一，也可以多個列組合的值唯一。
唯一性約束允許列值為空。
在創建唯一約束的時候，如果不給唯一約束命名，就默認和列名相同。
MySQL會給唯一約束的列上默認創建一個唯一索引。

4) 添加唯一約束

1. 建表時

create table 表名稱(
字段名 數據類型,
字段名 數據類型 unique,
字段名 數據類型 unique key,
字段名 數據類型
);

create table 表名稱(
字段名 數據類型,
字段名 數據類型,
字段名 數據類型,
[constraint 約束名] unique key(字段名)
);

舉例：

CREATE TABLE USER(
id INT NOT NULL,
NAME VARCHAR(25),
PASSWORD VARCHAR(16),
-- 使用表級約束語法
CONSTRAINT uk_name_pwd UNIQUE(NAME,PASSWORD)
);

表示用戶名和密碼組合不能重復

2. 建表后指定唯一鍵約束

#字段列表中如果是一個字段，表示該列的值唯一。如果是兩個或更多個字段，那么復合唯一，即多個字段的組合是唯
一的
#方式1：
alter table 表名稱 add unique key(字段列表);
#方式2：
alter table 表名稱 modify 字段名 字段類型 unique;

5) 關于復合唯一約束

create table 表名稱(
字段名 數據類型,
字段名 數據類型,
字段名 數據類型,
unique key(字段列表) #字段列表中寫的是多個字段名，多個字段名用逗號分隔，表示那么是復合唯一，即多
個字段的組合是唯一的
);

6) 刪除唯一約束

添加唯一性約束的列上也會自動創建唯一索引。
刪除唯一約束只能通過刪除唯一索引的方式刪除。
刪除時需要指定唯一索引名，唯一索引名就和唯一約束名一樣。
如果創建唯一約束時未指定名稱，如果是單列，就默認和列名相同；
如果是組合列，那么默認和() 中排在第一個的列名相同。也可以自定義唯一性約束名。

SELECT * FROM information_schema.table_constraints WHERE table_name = '表名'; #查看都有哪些約束

ALTER TABLE USER
DROP INDEX uk_name_pwd;

注意：可以通過 show index from 表名稱; #查看表的索引

4. PRIMARY KEY 約束

1) 作用

用來唯一標識表中的一行記錄。

2) 關鍵字

primary key

3) 特點

主鍵約束相當于唯一約束+非空約束的組合，主鍵約束列不允許重復，也不允許出現空值。

一個表最多只能有一個主鍵約束，建立主鍵約束可以在列級別創建，也可以在表級別上創建。
主鍵約束對應著表中的一列或者多列（復合主鍵）
如果是多列組合的復合主鍵約束，那么這些列都不允許為空值，并且組合的值不允許重復。
MySQL的主鍵名總是PRIMARY，就算自己命名了主鍵約束名也沒用。
當創建主鍵約束時，系統默認會在所在的列或列組合上建立對應的主鍵索引（能夠根據主鍵查詢的，就根據主鍵查詢，效率更高。如果刪除主鍵約束了，主鍵約束對應的索引就自動刪除了。
需要注意的一點是，不要修改主鍵字段的值。因為主鍵是數據記錄的唯一標識，如果修改了主鍵的值，就有可能會破壞數據的完整性。

4) 添加主鍵約束

1. 建表時指定主鍵約束

create table 表名稱(
字段名 數據類型 primary key, #列級模式
字段名 數據類型,
字段名 數據類型
);

create table 表名稱(
字段名 數據類型,
字段名 數據類型,
字段名 數據類型,
[constraint 約束名] primary key(字段名) #表級模式
);

2. 建表后增加主鍵約束

ALTER TABLE 表名稱 ADD PRIMARY KEY(字段列表); #字段列表可以是一個字段，也可以是多個字段，如果是多個字段的話，是復合主鍵

5) 關于復合主鍵

create table 表名稱(
字段名 數據類型,
字段名 數據類型,
字段名 數據類型,
primary key(字段名1,字段名2) #表示字段1和字段2的組合是唯一的，也可以有更多個字段
);

6) 刪除主鍵約束

alter table 表名稱 drop primary key

說明：刪除主鍵約束，不需要指定主鍵名，因為一個表只有一個主鍵，刪除主鍵約束后，非空還存在。

5. 自增列：AUTO_INCREMENT

1) 作用

某個字段的值自增

2) 關鍵字

auto_increment

3) 特點

（1）一個表最多只能有一個自增長列

（2）當需要產生唯一標識符或順序值時，可設置自增長

（3）自增長列約束的列必須是鍵列（主鍵列，唯一鍵列）

（4）自增約束的列的數據類型必須是整數類型

（5）如果自增列指定了 0 和 null，會在當前最大值的基礎上自增；如果自增列手動指定了具體值，直接賦值為具體值。

4) 如何指定自增約束

1. 建表時

create table 表名稱(
字段名 數據類型 primary key auto_increment,
字段名 數據類型 unique key not null,
字段名 數據類型 unique key,
字段名 數據類型 not null default 默認值,
);
create table 表名稱(
字段名 數據類型 default 默認值 ,
字段名 數據類型 unique key auto_increment,
字段名 數據類型 not null default 默認值,
primary key(字段名)
);

2. 建表后

alter table 表名稱 modify 字段名 數據類型 auto_increment;

5) 刪除自增約束

#alter table 表名稱 modify 字段名 數據類型 auto_increment;#給這個字段增加自增約束
alter table 表名稱 modify 字段名 數據類型; #去掉auto_increment相當于刪除

6) MySQL 8.0新特性—自增變量的持久化

在MySQL 8.0之前，自增主鍵AUTO_INCREMENT的值如果大于max(primary key)+1，在MySQL重啟后，會重置AUTO_INCREMENT=max(primary key)+1，這種現象在某些情況下會導致業務主鍵沖突或者其他難以發現的問題。下面通過案例來對比不同的版本中自增變量是否持久化。在MySQL 5.7版本中，測試步驟如下：創建的數據表中包含自增主鍵的id字段，語句如下：

CREATE TABLE test1(
id INT PRIMARY KEY AUTO_INCREMENT
);

在MySQL 5.7系統中，對于自增主鍵的分配規則，是由InnoDB數據字典內部一個計數器來決定的，而該計數器只在內存中維護，并不會持久化到磁盤中。當數據庫重啟時，該計數器會被初始化。

在MySQL 8.0將自增主鍵的計數器持久化到重做日志中。每次計數器發生改變，都會將其寫入重做日志中。如果數據庫重啟，InnoDB會根據重做日志中的信息來初始化計數器的內存值。

6. FOREIGN KEY 約束

1) 作用

限定某個表的某個字段的引用完整性。

2) 關鍵字

FOREIGN KEY

3) 主表和從表/父表和子表

主表（父表）：被引用的表，被參考的表

從表（子表）：引用別人的表，參考別人的表

4) 特點

（1）從表的外鍵列，必須引用/參考主表的主鍵或唯一約束的列為什么？因為被依賴/被參考的值必須是唯一的

（2）在創建外鍵約束時，如果不給外鍵約束命名，默認名不是列名，而是自動產生一個外鍵名（例如 student_ibfk_1;），也可以指定外鍵約束名。

（3）創建(CREATE)表時就指定外鍵約束的話，先創建主表，再創建從表

（4）刪表時，先刪從表（或先刪除外鍵約束），再刪除主表

（5）當主表的記錄被從表參照時，主表的記錄將不允許刪除，如果要刪除數據，需要先刪除從表中依賴該記錄的數據，然后才可以刪除主表的數據

（6）在“從表”中指定外鍵約束，并且一個表可以建立多個外鍵約束

（7）從表的外鍵列與主表被參照的列名字可以不相同，但是數據類型必須一樣，邏輯意義一致。如果類型不一樣，創建子表時，就會出現錯誤“ERROR 1005 (HY000): Can't create table'database.tablename'(errno: 150)”。例如：都是表示部門編號，都是int類型。

（8）當創建外鍵約束時，系統默認會在所在的列上建立對應的普通索引。但是索引名是外鍵的約束名。（根據外鍵查詢效率很高）

（9）刪除外鍵約束后，必須手動刪除對應的索引

5) 添加外鍵約束

1. 建表時

create table 主表名稱(
字段1 數據類型 primary key,
字段2 數據類型
);

create table 從表名稱(
字段1 數據類型 primary key,
字段2 數據類型,
[CONSTRAINT <外鍵約束名稱>] FOREIGN KEY（從表的某個字段) references 主表名(被參考字段)
);
#(從表的某個字段)的數據類型必須與主表名(被參考字段)的數據類型一致，邏輯意義也一樣
#(從表的某個字段)的字段名可以與主表名(被參考字段)的字段名一樣，也可以不一樣
-- FOREIGN KEY: 在表級指定子表中的列
-- REFERENCES: 標示在父表中的列

create table dept( #主表
did int primary key, #部門編號
dname varchar(50) #部門名稱
);
create table emp(#從表
eid int primary key, #員工編號
ename varchar(5), #員工姓名
deptid int, #員工所在的部門
foreign key (deptid) references dept(did) #在從表中指定外鍵約束
#emp表的deptid和和dept表的did的數據類型一致，意義都是表示部門的編號
);
說明：
（1）主表dept必須先創建成功，然后才能創建emp表，指定外鍵成功。
（2）刪除表時，先刪除從表emp，再刪除主表dept

2. 建表后

一般情況下，表與表的關聯都是提前設計好了的，因此，會在創建表的時候就把外鍵約束定義好。不過，如果需要修改表的設計（比如添加新的字段，增加新的關聯關系），但沒有預先定義外鍵約束，那么，就要用修改表的方式來補充定義。

格式：

ALTER TABLE 從表名 ADD [CONSTRAINT 約束名] FOREIGN KEY (從表的字段) REFERENCES 主表名(被引用字段) [on update xx][on delete xx];

舉例：

ALTER TABLE emp1
ADD [CONSTRAINT emp_dept_id_fk] FOREIGN KEY(dept_id) REFERENCES dept(dept_id);

6) 約束等級

Cascade方式 ：在父表上update/delete記錄時，同步update/delete掉子表的匹配記錄
Set null方式 ：在父表上update/delete記錄時，將子表上匹配記錄的列設為null，但是要注意子表的外鍵列不能為not null
No action方式 ：如果子表中有匹配的記錄，則不允許對父表對應候選鍵進行update/delete操作
Restrict方式 ：同no action，都是立即檢查外鍵約束
Set default方式 （在可視化工具SQLyog中可能顯示空白）：父表有變更時，子表將外鍵列設置成一個默認的值，但Innodb不能識別x

如果沒有指定等級，就相當于Restrict方式。對于外鍵約束，最好是采用: ON UPDATE CASCADE ON DELETE RESTRICT 的方式。

7) 刪除外鍵約束

流程如下：

(1)第一步先查看約束名和刪除外鍵約束
SELECT * FROM information_schema.table_constraints WHERE table_name = '表名稱';  #查看某個表的約束名
ALTER TABLE 從表名 DROP FOREIGN KEY 外鍵約束名;

（2）第二步查看索引名和刪除索引。（注意，只能手動刪除）
SHOW INDEX FROM 表名稱; #查看某個表的索引名
ALTER TABLE 從表名 DROP INDEX 索引名;

8) 開發場景

問題1：如果兩個表之間有關系（一對一、一對多），比如：員工表和部門表（一對多），它們之間是否一定要建外鍵約束？

答：不是的

問題2：建和不建外鍵約束有什么區別？

答：建外鍵約束，你的操作（創建表、刪除表、添加、修改、刪除）會受到限制，從語法層面受到限制。例如：在員工表中不可能添加一個員工信息，它的部門的值在部門表中找不到。

不建外鍵約束，你的操作（創建表、刪除表、添加、修改、刪除）不受限制，要保證數據的引用完整性，只能依靠程序員的自覺，或者是在Java程序中進行限定。例如：在員工表中，可以添加一個員工的信息，它的部門指定為一個完全不存在的部門。

問題3：那么建和不建外鍵約束和查詢有沒有關系？

答：沒有

在 MySQL 里，外鍵約束是有成本的，需要消耗系統資源。對于大并發的 SQL 操作，有可能會不適合。比如大型網站的中央數據庫，可能會因為外鍵約束的系統開銷而變得非常慢。所以， MySQL 允許你不使用系統自帶的外鍵約束，在應用層面完成檢查數據一致性的邏輯。也就是說，即使你不用外鍵約束，也要想辦法通過應用層面的附加邏輯，來實現外鍵約束的功能，確保數據的一致性。

9) 阿里開發規范

【強制】不得使用外鍵與級聯，一切外鍵概念必須在應用層解決。

說明：（概念解釋）學生表中的 student_id 是主鍵，那么成績表中的 student_id 則為外鍵。如果更新學生表中的 student_id，同時觸發成績表中的 student_id 更新，即為級聯更新。外鍵與級聯更新適用于單機低并發，不適合分布式、高并發集群；級聯更新是強阻塞，存在數據庫更新風暴的風險；外鍵影響數據庫的插入速度。

7. CHECK 約束

1) 作用

檢查某個字段的值是否符號xx要求，一般指的是值的范圍

2) 關鍵字

CHECK

3) 說明

MySQL5.7 可以使用check約束，但check約束對數據驗證沒有任何作用。添加數據時，沒有任何錯誤或警告

但是MySQL 8.0中可以使用check約束了。

create table employee(
eid int primary key,
ename varchar(5),
gender char check ('男' or '女')
);

8. DEFAULT約束

1) 作用

給某個字段/某列指定默認值，一旦設置默認值，在插入數據時，如果此字段沒有顯式賦值，則賦值為默認值。

2) 關鍵字

DEFAULT

3) 添加默認值

1. 建表時

create table 表名稱(
字段名 數據類型 primary key,
字段名 數據類型 unique key not null,
字段名 數據類型 unique key,
字段名 數據類型 not null default 默認值,
);

2. 建表后

alter table 表名稱 modify 字段名 數據類型 default 默認值;
#如果這個字段原來有非空約束，你還保留非空約束，那么在加默認值約束時，還得保留非空約束，否則非空約束就被刪除了
#同理，在給某個字段加非空約束也一樣，如果這個字段原來有默認值約束，你想保留，也要在modify語句中保留默認值約束，否則就刪除了
alter table 表名稱 modify 字段名 數據類型 default 默認值 not null;

刪除默認值

alter table 表名稱 modify 字段名 數據類型; #刪除默認值約束，也不保留非空約束
alter table 表名稱 modify 字段名 數據類型 not null; #刪除默認值約束，保留非空約束

9. 面試

面試1、為什么建表時，加 not null default '' 或 default 0

答：不想讓表中出現null值。

面試2、為什么不想要 null 的值

答:

（1）不好比較。null是一種特殊值，比較時只能用專門的is null 和 is not null來比較。碰到運算符，通常返回null。

（2）效率不高。影響提高索引效果。因此，我們往往在建表時 not null default '' 或 default 0

面試3、帶AUTO_INCREMENT約束的字段值是從1開始的嗎？

在MySQL中，默認AUTO_INCREMENT的初始值是1，每新增一條記錄，字段值自動加1。設置自增屬性（AUTO_INCREMENT）的時候，還可以指定第一條插入記錄的自增字段的值，這樣新插入的記錄的自增字段值從初始值開始遞增，如在表中插入第一條記錄，同時指定id值為5，則以后插入的記錄的id值就會從6開始往上增加。添加主鍵約束時，往往需要設置字段自動增加屬性。

面試4、并不是每個表都可以任意選擇存儲引擎？

外鍵約束（FOREIGN KEY）不能跨引擎使用。

MySQL支持多種存儲引擎，每一個表都可以指定一個不同的存儲引擎，需要注意的是：外鍵約束是用來保證數據的參照完整性的，如果表之間需要關聯外鍵，卻指定了不同的存儲引擎，那么這些表之間是不能創建外鍵約束的。所以說，存儲引擎的選擇也不完全是隨意的。

視圖

1. 常見的數據庫對象

對象	描述
表(TABLE)	表是存儲數據的邏輯單元，以行和列的形式存在，列就是字段，行就是記錄
數據字典	就是系統表，存放數據庫相關信息的表。系統表的數據通常由數據庫系統維護，程序員通常不應該修改，只可查看
約束 (CONSTRAINT)	執行數據校驗的規則，用于保證數據完整性的規則
視圖(VIEW)	一個或者多個數據表里的數據的邏輯顯示，視圖并不存儲數據
索引(INDEX)	用于提高查詢性能，相當于書的目錄
存儲過程 (PROCEDURE)	用于完成一次完整的業務處理，沒有返回值，但可通過傳出參數將多個值傳給調用環境
存儲函數 (FUNCTION)	用于完成一次特定的計算，具有一個返回值
觸發器 (TRIGGER)	相當于一個事件監聽器，當數據庫發生特定事件后，觸發器被觸發，完成相應的處理

2. 視圖概述

視圖是一種虛擬表，本身是不具有數據的，占用很少的內存空間，它是 SQL 中的一個重要概念。
視圖建立在已有表的基礎上, 視圖賴以建立的這些表稱為基表。
視圖的創建和刪除只影響視圖本身，不影響對應的基表。但是當對視圖中的數據進行增加、刪除和修改操作時，數據表中的數據會相應地發生變化，反之亦然。
視圖提供數據內容的語句為 SELECT 語句, 可以將視圖理解為存儲起來的 SELECT 語句
- 在數據庫中，視圖不會保存數據，數據真正保存在數據表中。當對視圖中的數據進行增加、刪除和修改操作時，數據表中的數據會相應地發生變化；反之亦然。
視圖，是向用戶提供基表數據的另一種表現形式。通常情況下，小型項目的數據庫可以不使用視圖，但是在大型項目中，以及數據表比較復雜的情況下，視圖的價值就凸顯出來了，它可以幫助我們把經常查詢的結果集放到虛擬表中，提升使用效率。理解和使用起來都非常方便。

3. 創建視圖

在 CREATE VIEW 語句中嵌入子查詢

CREATE [OR REPLACE]
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW 視圖名稱 [(字段列表)]
AS 查詢語句
[WITH [CASCADED|LOCAL] CHECK OPTION]

精簡版

CREATE VIEW 視圖名稱
AS 查詢語句

1) 創建單表視圖

舉例：

# 方式一：
CREATE VIEW empvu80
AS
SELECT employee_id, last_name, salary
FROM employees
WHERE department_id = 80;

# 方式二：
CREATE VIEW empsalary8000(emp_id, NAME, monthly_sal) # 小括號內字段個數與SELECT中字段個數相同
AS
SELECT employee_id, last_name, salary
FROM employees
WHERE salary > 8000;

查詢視圖：

SELECT *
FROM salvu80;

2) 創建多表聯合視圖

舉例：

CREATE VIEW empview
AS
SELECT employee_id emp_id,last_name NAME,department_name
FROM employees e,departments d
WHERE e.department_id = d.department_id;

CREATE VIEW dept_sum_vu
(name, minsal, maxsal, avgsal)
AS
SELECT d.department_name, MIN(e.salary), MAX(e.salary),AVG(e.salary)
FROM employees e, departments d
WHERE e.department_id = d.department_id
GROUP BY d.department_name;

利用視圖對數據進行格式化

常需要輸出某個格式的內容，比如我們想輸出員工姓名和對應的部門名，對應格式為 emp_name(department_name)，就可以使用視圖來完成數據格式化的操作：

CREATE VIEW emp_depart
AS
SELECT CONCAT(last_name,'(',department_name,')') AS emp_dept
FROM employees e JOIN departments d
WHERE e.department_id = d.department_id;

3) 基于視圖創建視圖

當我們創建好一張視圖之后，還可以在它的基礎上繼續創建視圖。

舉例：聯合“emp_dept”視圖和“emp_year_salary”視圖查詢員工姓名、部門名稱、年薪信息創建 “emp_dept_ysalary”視圖。

CREATE VIEW emp_dept_ysalary
AS
SELECT emp_dept.ename,dname,year_salary
FROM emp_dept INNER JOIN emp_year_salary
ON emp_dept.ename = emp_year_salary.ename;

4. 查看視圖

語法1：查看數據庫的表對象、視圖對象

SHOW TABLES;

語法2：查看視圖的結構

DESC / DESCRIBE 視圖名稱;

語法3：查看視圖的屬性信息

# 查看視圖信息（顯示數據表的存儲引擎、版本、數據行數和數據大小等）
SHOW TABLE STATUS LIKE '視圖名稱'\G

執行結果顯示，注釋Comment為VIEW，說明該表為視圖，其他的信息為NULL，說明這是一個虛表。語法4：查看視圖的詳細定義信息

SHOW CREATE VIEW 視圖名稱;

5. 更新視圖的數據

1) 一般情況

MySQL支持使用INSERT、UPDATE和DELETE語句對視圖中的數據進行插入、更新和刪除操作。當視圖中的數據發生變化時，數據表中的數據也會發生變化，反之亦然。

舉例：UPDATE操作

UPDATE emp_tel SET tel = '13789091234' WHERE ename = '孫洪亮';

舉例：DELETE操作

 DELETE FROM emp_tel WHERE ename = '孫洪亮';

2) 不可更新的視圖

要使視圖可更新，視圖中的行和底層基本表中的行之間必須存在一對一的關系。另外當視圖定義出現如下情況時，視圖不支持更新操作：

在定義視圖的時候指定了“ALGORITHM = TEMPTABLE”，視圖將不支持INSERT和DELETE操作；
視圖中不包含基表中所有被定義為非空又未指定默認值的列，視圖將不支持INSERT操作；
在定義視圖的SELECT語句中使用了 JOIN聯合查詢，視圖將不支持INSERT和DELETE操作；
在定義視圖的SELECT語句后的字段列表中使用了數學表達式或子查詢，視圖將不支持INSERT，也不支持UPDATE使用了數學表達式、子查詢的字段值；
在定義視圖的SELECT語句后的字段列表中使用 DISTINCT 、聚合函數、 GROUP BY 、 HAVING 、 UNION 等，視圖將不支持INSERT、UPDATE、DELETE；
在定義視圖的SELECT語句中包含了子查詢，而子查詢中引用了FROM后面的表，視圖將不支持 INSERT、UPDATE、DELETE；
視圖定義基于一個不可更新視圖；常量視圖。

雖然可以更新視圖數據，但總的來說，視圖作為虛擬表，主要用于方便查詢，不建議更新視圖的數據。對視圖數據的更改，都是通過對實際數據表里數據的操作來完成的。

6. 修改、刪除視圖

1) 修改視圖

方式1：使用CREATE OR REPLACE VIEW 子句修改視圖

CREATE OR REPLACE VIEW empvu80
(id_number, name, sal, department_id)
AS
SELECT employee_id, first_name || ' ' || last_name, salary, department_id
FROM employees
WHERE department_id = 80;

說明：CREATE VIEW 子句中各列的別名應和子查詢中各列相對應。

方式2：ALTER VIEW

修改視圖的語法是：

ALTER VIEW 視圖名稱
AS
查詢語句

2) 刪除視圖

刪除視圖只是刪除視圖的定義，并不會刪除基表的數據。
刪除視圖的語法是：

DROP VIEW IF EXISTS 視圖名稱;

舉例：

DROP VIEW empvu80;

說明：基于視圖a、b創建了新的視圖c，如果將視圖a或者視圖b刪除，會導致視圖c的查詢失敗。這樣的視圖c需要手動刪除或修改，否則影響使用。

7. 總結

1) 優點

1. 操作簡單

將經常使用的查詢操作定義為視圖，可以使開發人員不需要關心視圖對應的數據表的結構、表與表之間的關聯關系，也不需要關心數據表之間的業務邏輯和查詢條件，而只需要簡單地操作視圖即可，極大簡化了開發人員對數據庫的操作。

2. 減少數據冗余

視圖跟實際數據表不一樣，它存儲的是查詢語句。所以，在使用的時候，我們要通過定義視圖的查詢語句來獲取結果集。而視圖本身不存儲數據，不占用數據存儲的資源，減少了數據冗余。

3. 數據安全

MySQL將用戶對數據的訪問限制在某些數據的結果集上，而這些數據的結果集可以使用視圖來實現。用戶不必直接查詢或操作數據表。這也可以理解為視圖具有隔離性。視圖相當于在用戶和實際的數據表之間加了一層虛擬表。

同時，MySQL可以根據權限將用戶對數據的訪問限制在某些視圖上，用戶不需要查詢數據表，可以直接通過視圖獲取數據表中的信息。這在一定程度上保障了數據表中數據的安全性。

4. 適應靈活多變的需求

當業務系統的需求發生變化后，如果需要改動數據表的結構，則工作量相對較大，可以使用視圖來減少改動的工作量。這種方式在實際工作中使用得比較多。

5. 能夠分解復雜的查詢邏輯

數據庫中如果存在復雜的查詢邏輯，則可以將問題進行分解，創建多個視圖獲取數據，再將創建的多個視圖結合起來，完成復雜的查詢邏輯。

2) 不足

如果我們在實際數據表的基礎上創建了視圖，那么，如果實際數據表的結構變更了，我們就需要及時對相關的視圖進行相應的維護。特別是嵌套的視圖（就是在視圖的基礎上創建視圖），維護會變得比較復雜，可讀性不好，容易變成系統的潛在隱患。因為創建視圖的 SQL 查詢可能會對字段重命名，也可能包含復雜的邏輯，這些都會增加維護的成本。

實際項目中，如果視圖過多，會導致數據庫維護成本的問題。

所以，在創建視圖的時候，你要結合實際項目需求，綜合考慮視圖的優點和不足，這樣才能正確使用視圖，使系統整體達到最優。

存儲過程與函數

MySQL從5.0版本開始支持存儲過程和函數。存儲過程和函數能夠將復雜的SQL邏輯封裝在一起，應用程序無須關注存儲過程和函數內部復雜的SQL邏輯，而只需要簡單地調用存儲過程和函數即可。

1. 存儲過程概述

1) 理解

含義：存儲過程的英文是 Stored Procedure 。它的思想很簡單，就是一組經過預先編譯的 SQL 語句的封裝。

執行過程：存儲過程預先存儲在 MySQL 服務器上，需要執行的時候，客戶端只需要向服務器端發出調用存儲過程的命令，服務器端就可以把預先存儲好的這一系列 SQL 語句全部執行。

好處：

1、簡化操作，提高了sql語句的重用性，減少了開發程序員的壓力。
2、減少操作過程中的失誤，提高效率。
3、減少網絡傳輸量（客戶端不需要把所有的 SQL 語句通過網絡發給服務器）。
4、減少了 SQL 語句暴露在網上的風險，也提高了數據查詢的安全性。

和視圖、函數的對比：

它和視圖有著同樣的優點，清晰、安全，還可以減少網絡傳輸量。不過它和視圖不同，視圖是虛擬表，通常不對底層數據表直接操作，而存儲過程是程序化的 SQL，可以直接操作底層數據表，相比于面向集合的操作方式，能夠實現一些更復雜的數據處理。

一旦存儲過程被創建出來，使用它就像使用函數一樣簡單，我們直接通過調用存儲過程名即可。相較于函數，存儲過程是沒有返回值的。

2) 分類

存儲過程的參數類型可以是IN、OUT和INOUT。根據這點分類如下：

1、沒有參數（無參數無返回）

2、僅僅帶 IN 類型（有參數無返回）

3、僅僅帶 OUT 類型（無參數有返回）

4、既帶 IN 又帶 OUT（有參數有返回）

5、帶 INOUT（有參數有返回）

注意：IN、OUT、INOUT 都可以在一個存儲過程中帶多個。

2. 創建存儲過程

1) 語法分析

語法：

CREATE PROCEDURE 存儲過程名(IN|OUT|INOUT 參數名 參數類型,...)
[characteristics ...]
BEGIN
存儲過程體
END

說明：

1、參數前面的符號的意思

IN ：當前參數為輸入參數，也就是表示入參；

存儲過程只是讀取這個參數的值。如果沒有定義參數種類，默認就是 IN ，表示輸入參數。
OUT ：當前參數為輸出參數，也就是表示出參；

執行完成之后，調用這個存儲過程的客戶端或者應用程序就可以讀取這個參數返回的值了。
INOUT ：當前參數既可以為輸入參數，也可以為輸出參數。

2、形參類型可以是 MySQL數據庫中的任意類型。

3、characteristics 表示創建存儲過程時指定的對存儲過程的約束條件，其取值信息如下：

LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'

LANGUAGE SQL ：說明存儲過程執行體是由SQL語句組成的，當前系統支持的語言為SQL。
[NOT] DETERMINISTIC ：指明存儲過程執行的結果是否確定。DETERMINISTIC表示結果是確定的。每次執行存儲過程時，相同的輸入會得到相同的輸出。NOT DETERMINISTIC表示結果是不確定的，相同的輸入可能得到不同的輸出。如果沒有指定任意一個值，默認為NOT DETERMINISTIC。
{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } ：指明子程序使用SQL語句的限制。
- CONTAINS SQL表示當前存儲過程的子程序包含SQL語句，但是并不包含讀寫數據的SQL語句；
- NO SQL表示當前存儲過程的子程序中不包含任何SQL語句；
- READS SQL DATA表示當前存儲過程的子程序中包含讀數據的SQL語句；
- MODIFIES SQL DATA表示當前存儲過程的子程序中包含寫數據的SQL語句。
- 默認情況下，系統會指定為CONTAINS SQL。
SQL SECURITY { DEFINER | INVOKER } ：執行當前存儲過程的權限，即指明哪些用戶能夠執行當前存儲過程。
- DEFINER 表示只有當前存儲過程的創建者或者定義者才能執行當前存儲過程；
- INVOKER 表示擁有當前存儲過程的訪問權限的用戶能夠執行當前存儲過程。
COMMENT 'string' ：注釋信息，可以用來描述存儲過程。

4、存儲過程體中可以有多條 SQL 語句，如果僅僅一條SQL 語句，則可以省略 BEGIN 和 END

1. BEGIN…END：BEGIN…END 中間包含了多個語句，每個語句都以（;）號為結束符。
2. DECLARE：DECLARE 用來聲明變量，使用的位置在于 BEGIN…END 語句中間，而且需要在其他語句使用之前進
行變量的聲明。
3. SET：賦值語句，用于對變量進行賦值。
4. SELECT… INTO：把從數據表中查詢的結果存放到變量中，也就是為變量賦值。

5、需要設置新的結束標記

DELIMITER 新的結束標記

因為MySQL默認的語句結束符號為分號‘;’。為了避免與存儲過程中SQL語句結束符相沖突，需要使用 DELIMITER改變存儲過程的結束符。

比如：“DELIMITER //”語句的作用是將MySQL的結束符設置為//，并以“END //”結束存儲過程。存儲過程定義完畢之后再使用“DELIMITER ;”恢復默認結束符。DELIMITER也可以指定其他符號作為結束符。

當使用DELIMITER命令時，應該避免使用反斜杠（‘\’）字符，因為反斜線是MySQL的轉義字符。

示例：

DELIMITER $
CREATE PROCEDURE 存儲過程名(IN|OUT|INOUT 參數名 參數類型,...)
[characteristics ...]
BEGIN
sql語句1;
sql語句2;
END $

2) 代碼舉例

舉例1：創建存儲過程select_all_data()，查看 emps 表的所有數據

DELIMITER $
CREATE PROCEDURE select_all_data()
BEGIN
SELECT * FROM emps;
END $
DELIMITER ;

舉例2：創建存儲過程avg_employee_salary()，返回所有員工的平均工資

DELIMITER //
CREATE PROCEDURE avg_employee_salary ()
BEGIN
SELECT AVG(salary) AS avg_salary FROM emps;
END //
DELIMITER ;

3. 調用存儲過程

1) 調用格式

存儲過程有多種調用方法。存儲過程必須使用CALL語句調用，并且存儲過程和數據庫相關，如果要執行其他數據庫中的存儲過程，需要指定數據庫名稱，例如CALL dbname.procname。

CALL 存儲過程名(實參列表)

格式：

1、調用in模式的參數：

CALL sp1('值');

2、調用out模式的參數：

SET @name;
CALL sp1(@name);
SELECT @name;

3、調用inout模式的參數：

SET @name=值;
CALL sp1(@name);
SELECT @name;

2) 代碼舉例

舉例1：

DELIMITER //
CREATE PROCEDURE CountProc(IN sid INT,OUT num INT)
BEGIN
SELECT COUNT(*) INTO num FROM fruits
WHERE s_id = sid;
END //
DELIMITER ;

調用存儲過程：

CALL CountProc (101, @num);

查看返回結果：

SELECT @num;

舉例2：創建存儲過程，實現累加運算，計算 1+2+…+n 等于多少。具體的代碼如下：

DELIMITER //
CREATE PROCEDURE `add_num`(IN n INT)
BEGIN
DECLARE i INT;
DECLARE sum INT;
SET i = 1;
SET sum = 0;
WHILE i <= n DO
SET sum = sum + i;
SET i = i +1;
END WHILE;
SELECT sum;
END //
DELIMITER ;

直接使用 CALL add_num(50); 即可。這里我傳入的參數為 50，也就是統計 1+2+…+50 的積累之和。

3) 如何調試

在 MySQL 中，存儲過程不像普通的編程語言（比如 VC++、Java 等）那樣有專門的集成開發環境。因此，你可以通過 SELECT 語句，把程序執行的中間結果查詢出來，來調試一個 SQL 語句的正確性。調試成功之后，把 SELECT 語句后移到下一個 SQL 語句之后，再調試下一個 SQL 語句。這樣逐步推進，就可以完成對存儲過程中所有操作的調試了。當然，你也可以把存儲過程中的 SQL 語句復制出來，逐段單獨調試。

4. 存儲函數的使用

1) 語法分析

學過的函數：LENGTH、SUBSTR、CONCAT等

語法格式：

CREATE FUNCTION 函數名(參數名 參數類型,...)
RETURNS 返回值類型
[characteristics ...]
BEGIN
函數體 #函數體中肯定有 RETURN 語句
END

說明：

1、參數列表：指定參數為IN、OUT或INOUT只對PROCEDURE是合法的，FUNCTION中總是默認為IN參數。

2、RETURNS type 語句表示函數返回數據的類型； RETURNS子句只能對FUNCTION做指定，對函數而言這是強制的。它用來指定函數的返回類型，而且函數體必須包含一個 RETURN value 語句。

3、characteristic 創建函數時指定的對函數的約束。取值與創建存儲過程時相同，這里不再贅述。

4、函數體也可以用BEGIN…END來表示SQL代碼的開始和結束。如果函數體只有一條語句，也可以省略 BEGIN…END。

2) 調用存儲函數

在MySQL中，存儲函數的使用方法與MySQL內部函數的使用方法是一樣的。換言之，用戶自己定義的存儲函數與MySQL內部函數是一個性質的。區別在于，存儲函數是用戶自己定義的，而內部函數是MySQL 的開發者定義的。

SELECT 函數名(實參列表)

3) 代碼舉例

舉例1：

創建存儲函數，名稱為email_by_name()，參數定義為空，該函數查詢Abel的email，并返回，數據類型為字符串型。

DELIMITER //
CREATE FUNCTION email_by_name()
RETURNS VARCHAR(25)
DETERMINISTIC
CONTAINS SQL
BEGIN
RETURN (SELECT email FROM employees WHERE last_name = 'Abel');
END //
DELIMITER ;

調用：

SELECT email_by_name();

舉例2：

創建存儲函數，名稱為email_by_id()，參數傳入emp_id，該函數查詢emp_id的email，并返回，數據類型為字符串型。

DELIMITER //
CREATE FUNCTION email_by_id(emp_id INT)
RETURNS VARCHAR(25)
DETERMINISTIC
CONTAINS SQL
BEGIN
RETURN (SELECT email FROM employees WHERE employee_id = emp_id);
END //
DELIMITER ;

調用：

SET @emp_id = 102;
SELECT email_by_id(@emp_id);

注意：

若在創建存儲函數中報錯“ you might want to use the less safe log_bin_trust_function_creators variable ”，有兩種處理方法：

方式1：

加上必要的函數特性“[NOT] DETERMINISTIC”和“{CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}”
方式2：

SET GLOBAL log_bin_trust_function_creators = 1;

4) 對比存儲函數與存儲過程

	關鍵字	調用語法	返回值	應用場景
存儲過程	PROCEDURE	CALL 存儲過程()	理解為有0個或多個	一般用于更新
存儲函數	FUNCTION	SELECT 函數 ()	只能是一個	一般用于查詢結果為一個值并返回時

此外，存儲函數可以放在查詢語句中使用，存儲過程不行。反之，存儲過程的功能更加強大，包括能夠執行對表的操作（比如創建表，刪除表等）和事務操作，這些功能是存儲函數不具備的。

5. 存儲過程和函數的查看、修改、刪除

1) 查看

創建完之后，怎么知道我們創建的存儲過程、存儲函數是否成功了呢？

MySQL存儲了存儲過程和函數的狀態信息，用戶可以使用SHOW STATUS語句或SHOW CREATE語句來查看，也可直接從系統的information_schema數據庫中查詢。這里介紹3種方法。

使用SHOW CREATE語句查看存儲過程和函數的創建信息

SHOW CREATE {PROCEDURE | FUNCTION} 存儲過程名或函數名

使用SHOW STATUS語句查看存儲過程和函數的狀態信息

SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern']

從information_schema.Routines表中查看存儲過程和函數的信息

MySQL中存儲過程和函數的信息存儲在information_schema數據庫下的Routines表中。可以通過查詢該表的記錄來查詢存儲過程和函數的信息。其基本語法形式如下：

SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME='存儲過程或函數的名' [AND ROUTINE_TYPE = {'PROCEDURE|FUNCTION'}];

說明：如果在MySQL數據庫中存在存儲過程和函數名稱相同的情況，最好指定ROUTINE_TYPE查詢條件來指明查詢的是存儲過程還是函數。

2) 修改

修改存儲過程或函數，不影響存儲過程或函數功能，只是修改相關特性。使用ALTER語句實現。

ALTER {PROCEDURE | FUNCTION} 存儲過程或函數的名 [characteristic ...]

其中，characteristic指定存儲過程或函數的特性，其取值信息與創建存儲過程、函數時的取值信息略有不同。

{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'

CONTAINS SQL ，表示子程序包含SQL語句，但不包含讀或寫數據的語句。
NO SQL ，表示子程序中不包含SQL語句。
READS SQL DATA ，表示子程序中包含讀數據的語句。
MODIFIES SQL DATA ，表示子程序中包含寫數據的語句。
SQL SECURITY { DEFINER | INVOKER } ，指明誰有權限來執行。
- DEFINER ，表示只有定義者自己才能夠執行。
- INVOKER ，表示調用者可以執行。
COMMENT 'string' ，表示注釋信息。

修改存儲過程使用ALTER PROCEDURE語句，修改存儲函數使用ALTER FUNCTION語句。但是，這兩個語句的結構是一樣的，語句中的所有參數也是一樣的。

3) 刪除

刪除存儲過程和函數，可以使用DROP語句，其語法結構如下：

DROP {PROCEDURE | FUNCTION} [IF EXISTS] 存儲過程或函數的名

6. 關于存儲過程使用的爭議

1) 優點

1、存儲過程可以一次編譯多次使用。存儲過程只在創建時進行編譯，之后的使用都不需要重新編譯，這就提升了 SQL 的執行效率。

2、可以減少開發工作量。將代碼封裝成模塊，實際上是編程的核心思想之一，這樣可以把復雜的問題拆解成不同的模塊，然后模塊之間可以重復使用，在減少開發工作量的同時，還能保證代碼的結構清晰。

3、存儲過程的安全性強。我們在設定存儲過程的時候可以設置對用戶的使用權限，這樣就和視圖一樣具有較強的安全性。

4、可以減少網絡傳輸量。因為代碼封裝到存儲過程中，每次使用只需要調用存儲過程即可，這樣就減少了網絡傳輸量。

5、良好的封裝性。在進行相對復雜的數據庫操作時，原本需要使用一條一條的 SQL 語句，可能要連接多次數據庫才能完成的操作，現在變成了一次存儲過程，只需要連接一次即可。

2) 缺點

阿里開發規范【強制】禁止使用存儲過程，存儲過程難以調試和擴展，更沒有移植性。

1、可移植性差。存儲過程不能跨數據庫移植，比如在 MySQL、Oracle 和 SQL Server 里編寫的存儲過程，在換成其他數據庫時都需要重新編寫。

2、調試困難。只有少數 DBMS 支持存儲過程的調試。對于復雜的存儲過程來說，開發和維護都不容易。雖然也有一些第三方工具可以對存儲過程進行調試，但要收費。

3、存儲過程的版本管理很困難。比如數據表索引發生變化了，可能會導致存儲過程失效。我們在開發軟件的時候往往需要進行版本管理，但是存儲過程本身沒有版本控制，版本迭代更新的時候很麻煩。

4、它不適合高并發的場景。高并發的場景需要減少數據庫的壓力，有時數據庫會采用分庫分表的方式，而且對可擴展性要求很高，在這種情況下，存儲過程會變得難以維護，增加數據庫的壓力，顯然就不適用了。

3) 小結

存儲過程既方便，又有局限性。盡管不同的公司對存儲過程的態度不一，但是對于我們開發人員來說，不論怎樣，掌握存儲過程都是必備的技能之一。

變量、流程控制與游標

在MySQL數據庫的存儲過程和函數中，可以使用變量來存儲查詢或計算的中間結果數據，或者輸出最終的結果數據。

1. 變量

在MySQL數據庫的存儲過程和函數中，可以使用變量來存儲查詢或計算的中間結果數據，或者輸出最終的結果數據。

在 MySQL 數據庫中，變量分為系統變量以及用戶自定義變量。

1) 系統變量

系統變量分類

變量由系統定義，不是用戶定義，屬于服務器層面。啟動MySQL服務，生成MySQL服務實例期間， MySQL將為MySQL服務器內存中的系統變量賦值，這些系統變量定義了當前MySQL服務實例的屬性、特征。這些系統變量的值要么是編譯MySQL時參數的默認值，要么是配置文件（例如my.ini等）中的參數值。大家可以通過網址 https://dev.mysql.com/doc/refman/8.0/en/server-systemvariables.html 查看MySQL文檔的系統變量。

系統變量分為全局系統變量（需要添加 global 關鍵字）以及會話系統變量（需要添加 session 關鍵字），有時也把全局系統變量簡稱為全局變量，有時也把會話系統變量稱為local變量。如果不寫，默認會話級別。靜態變量（在 MySQL 服務實例運行期間它們的值不能使用 set 動態修改）屬于特殊的全局系統變量。

每一個MySQL客戶機成功連接MySQL服務器后，都會產生與之對應的會話。會話期間，MySQL服務實例會在MySQL服務器內存中生成與該會話對應的會話系統變量，這些會話系統變量的初始值是全局系統變量值的復制。如下圖：

全局系統變量針對于所有會話（連接）有效，但不能跨重啟
會話系統變量僅針對于當前會話（連接）有效。會話期間，當前會話對某個會話系統變量值的修改，不會影響其他會話同一個會話系統變量的值。
會話1對某個全局系統變量值的修改會導致會話2中同一個全局系統變量值的修改。

在MySQL中有些系統變量只能是全局的，例如 max_connections 用于限制服務器的最大連接數；有些系統變量作用域既可以是全局又可以是會話，例如 character_set_client 用于設置客戶端的字符集；有些系統變量的作用域只能是當前會話，例如 pseudo_thread_id 用于標記當前會話的 MySQL 連接 ID。

查看系統變量

查看所有或部分系統變量

#查看所有全局變量
SHOW GLOBAL VARIABLES;
#查看所有會話變量
SHOW SESSION VARIABLES;
或
SHOW VARIABLES;

#查看滿足條件的部分系統變量。
SHOW GLOBAL VARIABLES LIKE '%標識符%';
#查看滿足條件的部分會話變量
SHOW SESSION VARIABLES LIKE '%標識符%';

查看指定系統變量

作為 MySQL 編碼規范，MySQL 中的系統變量以兩個“@” 開頭，其中“@@global”僅用于標記全局系統變量，“@@session”僅用于標記會話系統變量。“@@”首先標記會話系統變量，如果會話系統變量不存在，則標記全局系統變量。

#查看指定的系統變量的值
SELECT @@global.變量名;
#查看指定的會話變量的值
SELECT @@session.變量名;
#或者
SELECT @@變量名;

修改系統變量的值

有些時候，數據庫管理員需要修改系統變量的默認值，以便修改當前會話或者MySQL服務實例的屬性、特征。具體方法：

方式1：修改MySQL 配置文件，繼而修改MySQL系統變量的值（該方法需要重啟MySQL服務）

方式2：在MySQL服務運行期間，使用“set”命令重新設置系統變量的值

#為某個系統變量賦值
#方式1：
SET @@global.變量名=變量值;
#方式2：
SET GLOBAL 變量名=變量值;
#為某個會話變量賦值
#方式1：
SET @@session.變量名=變量值;
#方式2：
SET SESSION 變量名=變量值;

2) 用戶變量

用戶變量分類

用戶變量是用戶自己定義的，作為 MySQL 編碼規范，MySQL 中的用戶變量以一個“@” 開頭。根據作用范圍不同，又分為會話用戶變量和局部變量。

會話用戶變量：作用域和會話變量一樣，只對當前連接會話有效。
局部變量：只在 BEGIN 和 END 語句塊中有效。局部變量只能在存儲過程和函數中使用。

會話用戶變量

變量的定義

#方式1：“=”或“:=”
SET @用戶變量 = 值;
SET @用戶變量 := 值;
#方式2：“:=” 或 INTO關鍵字
SELECT @用戶變量 := 表達式 [FROM 等子句];
SELECT 表達式 INTO @用戶變量 [FROM 等子句];

查看用戶變量的值 (查看、比較、運算等)

SELECT @用戶變量

局部變量

定義：可以使用 DECLARE 語句定義一個局部變量

作用域：僅僅在定義它的 BEGIN ... END 中有效

位置：只能放在 BEGIN ... END 中，而且只能放在第一句

BEGIN
#聲明局部變量
DECLARE 變量名1 變量數據類型 [DEFAULT 變量默認值];
DECLARE 變量名2,變量名3,... 變量數據類型 [DEFAULT 變量默認值];
#為局部變量賦值
SET 變量名1 = 值;
SELECT 值 INTO 變量名2 [FROM 子句];
#查看局部變量的值
SELECT 變量1,變量2,變量3;
END

定義變量

DECLARE 變量名 類型 [default 值]; # 如果沒有DEFAULT子句，初始值為NULL

變量賦值

方式1：一般用于賦簡單的值

SET 變量名=值;
SET 變量名:=值;

方式2：一般用于賦表中的字段值

SELECT 字段名或表達式 INTO 變量名 FROM 表;

使用變量 (查看、比較、運算等)

SELECT 局部變量名;

舉例1：聲明局部變量，并分別賦值為employees表中employee_id為102的last_name和salary

DELIMITER //
CREATE PROCEDURE set_value()
BEGIN
DECLARE emp_name VARCHAR(25);
DECLARE sal DOUBLE(10,2);
SELECT last_name, salary INTO emp_name,sal
FROM employees
WHERE employee_id = 102;
SELECT emp_name, sal;
END //
DELIMITER ;

舉例2：聲明兩個變量，求和并打印（分別使用會話用戶變量、局部變量的方式實現）

#方式1：使用用戶變量
SET @m=1;
SET @n=1;
SET @sum=@m+@n;
SELECT @sum;

#方式2：使用局部變量
DELIMITER //
CREATE PROCEDURE add_value()
BEGIN
#局部變量
DECLARE m INT DEFAULT 1;
DECLARE n INT DEFAULT 3;
DECLARE SUM INT;
SET SUM = m+n;
SELECT SUM;
END //
DELIMITER ;

對比會話用戶變量與局部變量

	作用域	定義位置	語法
會話用戶變量	當前會話	會話的任何地方	加@符號，不用指定類型
局部變量	定義它的BEGIN END中	BEGIN END的第一句話	一般不用加@,需要指定類型

2. 定義條件與處理程序

定義條件是事先定義程序執行過程中可能遇到的問題，處理程序定義了在遇到問題時應當采取的處理方式，并且保證存儲過程或函數在遇到警告或錯誤時能繼續執行。這樣可以增強存儲程序處理問題的能力，避免程序異常停止運行。

說明：定義條件和處理程序在存儲過程、存儲函數中都是支持的。

1) 案例分析

案例分析：創建一個名稱為“UpdateDataNoCondition”的存儲過程。代碼如下：

DELIMITER //
CREATE PROCEDURE UpdateDataNoCondition()
BEGIN
SET @x = 1;
UPDATE employees SET email = NULL WHERE last_name = 'Abel';
SET @x = 2;
UPDATE employees SET email = 'aabbel' WHERE last_name = 'Abel';
SET @x = 3;
END //
DELIMITER ;

調用存儲過程：

mysql> CALL UpdateDataNoCondition();
ERROR 1048 (23000): Column 'email' cannot be null
mysql> SELECT @x;
+------+
| @x |
+------+
| 1 |
+------+
1 row in set (0.00 sec)

可以看到，此時@x變量的值為1。結合創建存儲過程的SQL語句代碼可以得出：在存儲過程中未定義條件和處理程序，且當存儲過程中執行的SQL語句報錯時，MySQL數據庫會拋出錯誤，并退出當前SQL邏輯，不再向下繼續執行。

2) 定義條件

定義條件就是給MySQL中的錯誤碼命名，這有助于存儲的程序代碼更清晰。它將一個錯誤名字和指定的錯誤條件關聯起來。這個名字可以隨后被用在定義處理程序的 DECLARE HANDLER 語句中。

定義條件使用DECLARE語句，語法格式如下：

DECLARE 錯誤名稱 CONDITION FOR 錯誤碼（或錯誤條件）

錯誤碼的說明：

MySQL_error_code 和 sqlstate_value 都可以表示MySQL的錯誤。
- MySQL_error_code是數值類型錯誤代碼。
- sqlstate_value是長度為5的字符串類型錯誤代碼。

例如，在ERROR 1418 (HY000)中，1418是MySQL_error_code，'HY000'是sqlstate_value。

例如，在ERROR 1142（42000）中，1142是MySQL_error_code，'42000'是sqlstate_value。

舉例1：定義“Field_Not_Be_NULL”錯誤名與MySQL中違反非空約束的錯誤類型是“ERROR 1048 (23000)”對應。

#使用MySQL_error_code
DECLARE Field_Not_Be_NULL CONDITION FOR 1048;
#使用sqlstate_value
DECLARE Field_Not_Be_NULL CONDITION FOR SQLSTATE '23000';

3) 定義處理程序

可以為SQL執行過程中發生的某種類型的錯誤定義特殊的處理程序。定義處理程序時，使用DECLARE語句的語法如下：

DECLARE 處理方式 HANDLER FOR 錯誤類型 處理語句

處理方式：處理方式有3個取值：CONTINUE、EXIT、UNDO。
- CONTINUE ：表示遇到錯誤不處理，繼續執行。
- EXIT ：表示遇到錯誤馬上退出。
- UNDO ：表示遇到錯誤后撤回之前的操作。MySQL中暫時不支持這樣的操作。
錯誤類型（即條件）可以有如下取值：
- SQLSTATE '字符串錯誤碼' ：表示長度為5的sqlstate_value類型的錯誤代碼；
- MySQL_error_code ：匹配數值類型錯誤代碼；
- 錯誤名稱：表示DECLARE ... CONDITION定義的錯誤條件名稱。
- SQLWARNING ：匹配所有以01開頭的SQLSTATE錯誤代碼；
- NOT FOUND ：匹配所有以02開頭的SQLSTATE錯誤代碼；
- SQLEXCEPTION ：匹配所有沒有被SQLWARNING或NOT FOUND捕獲的SQLSTATE錯誤代碼；
處理語句：如果出現上述條件之一，則采用對應的處理方式，并執行指定的處理語句。語句可以是像“ SET 變量 = 值 ”這樣的簡單語句，也可以是使用 BEGIN ... END 編寫的復合語句。

定義處理程序的幾種方式，代碼如下：

#方法1：捕獲sqlstate_value
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET @info = 'NO_SUCH_TABLE';
#方法2：捕獲mysql_error_value
DECLARE CONTINUE HANDLER FOR 1146 SET @info = 'NO_SUCH_TABLE';
#方法3：先定義條件，再調用
DECLARE no_such_table CONDITION FOR 1146;
DECLARE CONTINUE HANDLER FOR NO_SUCH_TABLE SET @info = 'NO_SUCH_TABLE';
#方法4：使用SQLWARNING
DECLARE EXIT HANDLER FOR SQLWARNING SET @info = 'ERROR';
#方法5：使用NOT FOUND
DECLARE EXIT HANDLER FOR NOT FOUND SET @info = 'NO_SUCH_TABLE';
#方法6：使用SQLEXCEPTION
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info = 'ERROR';

4) 案例解決

在存儲過程中，定義處理程序，捕獲sqlstate_value值，當遇到MySQL_error_code值為1048時，執行 CONTINUE操作，并且將@proc_value的值設置為-1。

DELIMITER //
CREATE PROCEDURE UpdateDataNoCondition()
BEGIN
    #定義處理程序
    DECLARE CONTINUE HANDLER FOR 1048 SET @proc_value = -1;
    SET @x = 1;
    UPDATE employees SET email = NULL WHERE last_name = 'Abel';
    SET @x = 2;
    UPDATE employees SET email = 'aabbel' WHERE last_name = 'Abel';
    SET @x = 3;
END //
DELIMITER ;

3. 流程控制

解決復雜問題不可能通過一個 SQL 語句完成，我們需要執行多個 SQL 操作。流程控制語句的作用就是控制存儲過程中 SQL 語句的執行順序，是我們完成復雜操作必不可少的一部分。只要是執行的程序，流程就分為三大類：

順序結構：程序從上往下依次執行
分支結構：程序按條件進行選擇執行，從兩條或多條路徑中選擇一條執行
循環結構：程序滿足一定條件下，重復執行一組語句

針對于MySQL 的流程控制語句主要有 3 類。注意：只能用于存儲程序。

條件判斷語句：IF 語句和 CASE 語句
循環語句：LOOP、WHILE 和 REPEAT 語句
跳轉語句：ITERATE 和 LEAVE 語句

1) 分支結構之 IF

IF 語句的語法結構是：

IF 表達式1 THEN 操作1
[ELSEIF 表達式2 THEN 操作2]……
[ELSE 操作N]
END IF

根據表達式的結果為TRUE或FALSE執行相應的語句。這里“[]”中的內容是可選的。

特點：① 不同的表達式對應不同的操作 ② 使用在begin end中
舉例1：

IF val IS NULL
	THEN SELECT 'val is null';
ELSE SELECT 'val is not null';
END IF;

舉例2：聲明存儲過程“update_salary_by_eid1”，定義IN參數emp_id，輸入員工編號。判斷該員工薪資如果低于8000元并且入職時間超過5年，就漲薪500元；否則就不變。

DELIMITER //
CREATE PROCEDURE update_salary_by_eid1(IN emp_id INT)
BEGIN
    DECLARE emp_salary DOUBLE;
    DECLARE hire_year DOUBLE;
    SELECT salary INTO emp_salary FROM employees WHERE employee_id = emp_id;
    SELECT DATEDIFF(CURDATE(),hire_date)/365 INTO hire_year
    FROM employees WHERE employee_id = emp_id;
    IF emp_salary < 8000 AND hire_year > 5
    THEN UPDATE employees SET salary = salary + 500 WHERE employee_id = emp_id;
    END IF;
END //
DELIMITER ;

2) 分支結構之 CASE

CASE 語句的語法結構1：

#情況一：類似于switch
CASE 表達式
WHEN 值1 THEN 結果1或語句1(如果是語句，需要加分號)
WHEN 值2 THEN 結果2或語句2(如果是語句，需要加分號)
...
ELSE 結果n或語句n(如果是語句，需要加分號)
END [case]（如果是放在begin end中需要加上case，如果放在select后面不需要）

CASE 語句的語法結構2：

#情況二：類似于多重if
CASE
WHEN 條件1 THEN 結果1或語句1(如果是語句，需要加分號)
WHEN 條件2 THEN 結果2或語句2(如果是語句，需要加分號)
...
ELSE 結果n或語句n(如果是語句，需要加分號)
END [case]（如果是放在begin end中需要加上case，如果放在select后面不需要）

舉例1：使用CASE流程控制語句的第1種格式，判斷val值等于1、等于2，或者兩者都不等。

CASE val
    WHEN 1 THEN SELECT 'val is 1';
    WHEN 2 THEN SELECT 'val is 2';
    ELSE SELECT 'val is not 1 or 2';
END CASE;

舉例2：聲明存儲過程“update_salary_by_eid4”，定義IN參數emp_id，輸入員工編號。判斷該員工薪資如果低于9000元，就更新薪資為9000元；薪資大于等于9000元且低于10000的，但是獎金比例為NULL的，就更新獎金比例為0.01；其他的漲薪100元。

DELIMITER //
CREATE PROCEDURE update_salary_by_eid4(IN emp_id INT)
BEGIN
    DECLARE emp_sal DOUBLE;
    DECLARE bonus DECIMAL(3,2);
    SELECT salary INTO emp_sal FROM employees WHERE employee_id = emp_id;
    SELECT commission_pct INTO bonus FROM employees WHERE employee_id = emp_id;
    CASE
    WHEN emp_sal<9000
    	THEN UPDATE employees SET salary=9000 WHERE employee_id = emp_id;
    WHEN emp_sal<10000 AND bonus IS NULL
    	THEN UPDATE employees SET commission_pct=0.01 WHERE employee_id = emp_id;
    ELSE
    	UPDATE employees SET salary=salary+100 WHERE employee_id = emp_id;
    END CASE;
END //
DELIMITER ;

舉例3：聲明存儲過程update_salary_by_eid5，定義IN參數emp_id，輸入員工編號。判斷該員工的入職年限，如果是0年，薪資漲50；如果是1年，薪資漲100；如果是2年，薪資漲200；如果是3年，薪資漲300；如果是4年，薪資漲400；其他的漲薪500。

DELIMITER //
CREATE PROCEDURE update_salary_by_eid5(IN emp_id INT)
BEGIN
    DECLARE emp_sal DOUBLE;
    DECLARE hire_year DOUBLE;
    SELECT salary INTO emp_sal FROM employees WHERE employee_id = emp_id;
    SELECT ROUND(DATEDIFF(CURDATE(),hire_date)/365) INTO hire_year FROM employees
    WHERE employee_id = emp_id;
    CASE hire_year
        WHEN 0 THEN UPDATE employees SET salary=salary+50 WHERE employee_id = emp_id;
        WHEN 1 THEN UPDATE employees SET salary=salary+100 WHERE employee_id = emp_id;
        WHEN 2 THEN UPDATE employees SET salary=salary+200 WHERE employee_id = emp_id;
        WHEN 3 THEN UPDATE employees SET salary=salary+300 WHERE employee_id = emp_id;
        WHEN 4 THEN UPDATE employees SET salary=salary+400 WHERE employee_id = emp_id;
        ELSE UPDATE employees SET salary=salary+500 WHERE employee_id = emp_id;
    END CASE;
END //
DELIMITER ;

3) 循環結構之LOOP

LOOP循環語句用來重復執行某些語句。LOOP內的語句一直重復執行直到循環被退出（使用LEAVE子句），跳出循環過程。

LOOP語句的基本格式如下：

[loop_label:] LOOP
循環執行的語句
END LOOP [loop_label]

其中，loop_label表示LOOP語句的標注名稱，該參數可以省略。

舉例1：使用LOOP語句進行循環操作，id值小于10時將重復執行循環過程。

DECLARE id INT DEFAULT 0;
add_loop:LOOP
    SET id = id +1;
    IF id >= 10 THEN LEAVE add_loop;
    END IF;
END LOOP add_loop;

舉例2：當市場環境變好時，公司為了獎勵大家，決定給大家漲工資。聲明存儲過程 “update_salary_loop()”，聲明OUT參數num，輸出循環次數。存儲過程中實現循環給大家漲薪，薪資漲為原來的1.1倍。直到全公司的平均薪資達到12000結束。并統計循環次數。

DELIMITER //
CREATE PROCEDURE update_salary_loop(OUT num INT)
BEGIN
	DECLARE avg_salary DOUBLE;
	DECLARE loop_count INT DEFAULT 0;
	SELECT AVG(salary) INTO avg_salary FROM employees;
	label_loop:LOOP
        IF avg_salary >= 12000 THEN LEAVE label_loop;
        END IF;
        UPDATE employees SET salary = salary * 1.1;
        SET loop_count = loop_count + 1;
        SELECT AVG(salary) INTO avg_salary FROM employees;
    END LOOP label_loop;
    SET num = loop_count;
END //
DELIMITER ;

4) 循環結構之WHILE

WHILE語句創建一個帶條件判斷的循環過程。WHILE在執行語句執行時，先對指定的表達式進行判斷，如果為真，就執行循環內的語句，否則退出循環。WHILE語句的基本格式如下：

[while_label:] WHILE 循環條件 DO
循環體
END WHILE [while_label];

while_label為WHILE語句的標注名稱；如果循環條件結果為真，WHILE語句內的語句或語句群被執行，直至循環條件為假，退出循環。

舉例1：WHILE語句示例，i值小于10時，將重復執行循環過程，代碼如下：

DELIMITER //
CREATE PROCEDURE test_while()
BEGIN
    DECLARE i INT DEFAULT 0;
    WHILE i < 10 DO
    	SET i = i + 1;
    END WHILE;
    SELECT i;
END //
DELIMITER ;
#調用
CALL test_while();

舉例2：市場環境不好時，公司為了渡過難關，決定暫時降低大家的薪資。聲明存儲過程 “update_salary_while()”，聲明OUT參數num，輸出循環次數。存儲過程中實現循環給大家降薪，薪資降為原來的90%。直到全公司的平均薪資達到5000結束。并統計循環次數。

DELIMITER //
CREATE PROCEDURE update_salary_while(OUT num INT)
BEGIN
    DECLARE avg_sal DOUBLE ;
    DECLARE while_count INT DEFAULT 0;
    SELECT AVG(salary) INTO avg_sal FROM employees;
    WHILE avg_sal > 5000 DO
        UPDATE employees SET salary = salary * 0.9;
        SET while_count = while_count + 1;
        SELECT AVG(salary) INTO avg_sal FROM employees;
    END WHILE;
    SET num = while_count;
END //
DELIMITER ;

5) 循環結構之REPEAT

REPEAT語句創建一個帶條件判斷的循環過程。與WHILE循環不同的是，REPEAT 循環首先會執行一次循環，然后在 UNTIL 中進行表達式的判斷，如果滿足條件就退出，即 END REPEAT；如果條件不滿足，則會就繼續執行循環，直到滿足退出條件為止。

REPEAT語句的基本格式如下：

[repeat_label:] REPEAT
循環體的語句
UNTIL 結束循環的條件表達式
END REPEAT [repeat_label]

repeat_label為REPEAT語句的標注名稱，該參數可以省略；REPEAT語句內的語句或語句群被重復，直至 expr_condition為真。

舉例1：

DELIMITER //
CREATE PROCEDURE test_repeat()
BEGIN
    DECLARE i INT DEFAULT 0;
    REPEAT
    	SET i = i + 1;
    UNTIL i >= 10
    END REPEAT;
    SELECT i;
END //
DELIMITER ;

舉例2：當市場環境變好時，公司為了獎勵大家，決定給大家漲工資。聲明存儲過程 “update_salary_repeat()”，聲明OUT參數num，輸出循環次數。存儲過程中實現循環給大家漲薪，薪資漲為原來的1.15倍。直到全公司的平均薪資達到13000結束。并統計循環次數。

DELIMITER //
CREATE PROCEDURE update_salary_repeat(OUT num INT)
BEGIN
    DECLARE avg_sal DOUBLE ;
    DECLARE repeat_count INT DEFAULT 0;
    SELECT AVG(salary) INTO avg_sal FROM employees;
    REPEAT
    	UPDATE employees SET salary = salary * 1.15;
    	SET repeat_count = repeat_count + 1;
    	SELECT AVG(salary) INTO avg_sal FROM employees;
    UNTIL avg_sal >= 13000
    END REPEAT;
    SET num = repeat_count;
END //
DELIMITER ;

對比三種循環結構：

這三種循環都可以省略名稱，但如果循環中添加了循環控制語句（LEAVE或ITERATE）則必須添加名稱。
LOOP：一般用于實現簡單的"死"循環 WHILE：先判斷后執行
REPEAT：先執行后判斷，無條件至少執行一次

6) 跳轉語句之LEAVE語句

LEAVE語句：可以用在循環語句內，或者以 BEGIN 和 END 包裹起來的程序體內，表示跳出循環或者跳出程序體的操作。如果你有面向過程的編程語言的使用經驗，你可以把 LEAVE 理解為 break。

基本格式如下：

LEAVE 標記名

其中，label參數表示循環的標志。LEAVE和BEGIN ... END或循環一起被使用。

舉例1：創建存儲過程 “leave_begin()”，聲明INT類型的IN參數num。給BEGIN...END加標記名，并在 BEGIN...END中使用IF語句判斷num參數的值。

如果num<=0，則使用LEAVE語句退出BEGIN...END；如果num=1，則查詢“employees”表的平均薪資；如果num=2，則查詢“employees”表的最低薪資；如果num>2，則查詢“employees”表的最高薪資。

IF語句結束后查詢“employees”表的總人數。

DELIMITER //
CREATE PROCEDURE leave_begin(IN num INT)
    begin_label: BEGIN
        IF num<=0
        	THEN LEAVE begin_label;
        ELSEIF num=1
        	THEN SELECT AVG(salary) FROM employees;
        ELSEIF num=2
        	THEN SELECT MIN(salary) FROM employees;
        ELSE
        	SELECT MAX(salary) FROM employees;
        END IF;
        SELECT COUNT(*) FROM employees;
    END //
DELIMITER ;

舉例2：當市場環境不好時，公司為了渡過難關，決定暫時降低大家的薪資。聲明存儲過程“leave_while()”，聲明 OUT參數num，輸出循環次數，存儲過程中使用WHILE循環給大家降低薪資為原來薪資的90%，直到全公司的平均薪資小于等于10000，并統計循環次數。

DELIMITER //
CREATE PROCEDURE leave_while(OUT num INT)
BEGIN
    DECLARE avg_sal DOUBLE;#記錄平均工資
    DECLARE while_count INT DEFAULT 0; #記錄循環次數
    SELECT AVG(salary) INTO avg_sal FROM employees; #① 初始化條件
    while_label:WHILE TRUE DO #② 循環條件
    #③ 循環體
    IF avg_sal <= 10000 THEN
    LEAVE while_label;
    END IF;
    UPDATE employees SET salary = salary * 0.9;
    SET while_count = while_count + 1;
    #④ 迭代條件
    SELECT AVG(salary) INTO avg_sal FROM employees;
    END WHILE;
    #賦值
    SET num = while_count;
END //
DELIMITER ;

7) 跳轉語句之ITERATE語句

ITERATE語句：只能用在循環語句（LOOP、REPEAT和WHILE語句）內，表示重新開始循環，將執行順序轉到語句段開頭處。如果你有面向過程的編程語言的使用經驗，你可以把 ITERATE 理解為 continue，意思為“再次循環”。

語句基本格式如下：

ITERATE label

label參數表示循環的標志。ITERATE語句必須跟在循環標志前面。

舉例：定義局部變量num，初始值為0。循環結構中執行num + 1操作。

如果num < 10，則繼續執行循環；
如果num > 15，則退出循環結構；

DELIMITER //
CREATE PROCEDURE test_iterate()
BEGIN
    DECLARE num INT DEFAULT 0;
    my_loop:LOOP
    	SET num = num + 1;
        IF num < 10
        	THEN ITERATE my_loop;
        ELSEIF num > 15
        	THEN LEAVE my_loop;
        END IF;
        SELECT 'MySQL';
    END LOOP my_loop;
END //
DELIMITER ;

4. 游標

1) 什么是游標（或光標）

雖然我們也可以通過篩選條件 WHERE 和 HAVING，或者是限定返回記錄的關鍵字 LIMIT 返回一條記錄，但是，卻無法在結果集中像指針一樣，向前定位一條記錄、向后定位一條記錄，或者是隨意定位到某一條記錄，并對記錄的數據進行處理。

這個時候，就可以用到游標。游標，提供了一種靈活的操作方式，讓我們能夠對結果集中的每一條記錄進行定位，并對指向的記錄中的數據進行操作的數據結構。游標讓 SQL 這種面向集合的語言有了面向過程開發的能力。

在 SQL 中，游標是一種臨時的數據庫對象，可以指向存儲在數據庫表中的數據行指針。這里游標充當了指針的作用，我們可以通過操作游標來對數據行進行操作。

MySQL中游標可以在存儲過程和函數中使用。

2) 使用游標步驟

游標必須在聲明處理程序之前被聲明，并且變量和條件還必須在聲明游標或處理程序之前被聲明。

如果我們想要使用游標，一般需要經歷四個步驟。不同的 DBMS 中，使用游標的語法可能略有不同。

第一步，聲明游標

在MySQL中，使用DECLARE關鍵字來聲明游標，其語法的基本形式如下：

DECLARE cursor_name CURSOR FOR select_statement;

這個語法適用于 MySQL，SQL Server，DB2 和 MariaDB。如果是用 Oracle 或者 PostgreSQL，需要寫成：

DECLARE cursor_name CURSOR IS select_statement;

要使用 SELECT 語句來獲取數據結果集，而此時還沒有開始遍歷數據，這里 select_statement 代表的是 SELECT 語句，返回一個用于創建游標的結果集。

比如：

DECLARE cur_emp CURSOR FOR
SELECT employee_id,salary FROM employees;

第二步，打開游標

打開游標的語法如下：

OPEN cursor_name

當我們定義好游標之后，如果想要使用游標，必須先打開游標。打開游標的時候 SELECT 語句的查詢結果集就會送到游標工作區，為后面游標的逐條讀取結果集中的記錄做準備。

OPEN cur_emp;

第三步，使用游標（從游標中取得數據）

語法如下：

FETCH cursor_name INTO var_name [, var_name] ...

這句的作用是使用 cursor_name 這個游標來讀取當前行，并且將數據保存到 var_name 這個變量中，游標指針指到下一行。如果游標讀取的數據行有多個列名，則在 INTO 關鍵字后面賦值給多個變量名即可。

注意：var_name必須在聲明游標之前就定義好。

FETCH cur_emp INTO emp_id, emp_sal ;

注意：游標的查詢結果集中的字段數，必須跟 INTO 后面的變量數一致，否則，在存儲過程執行的時候，MySQL 會提示錯誤。

第四步，關閉游標

CLOSE cursor_name

有 OPEN 就會有 CLOSE，也就是打開和關閉游標。當我們使用完游標后需要關閉掉該游標。因為游標會占用系統資源，如果不及時關閉，游標會一直保持到存儲過程結束，影響系統運行的效率。而關閉游標的操作，會釋放游標占用的系統資源。

關閉游標之后，我們就不能再檢索查詢結果中的數據行，如果需要檢索只能再次打開游標。

CLOSE cur_emp;

3) 舉例

創建存儲過程“get_count_by_limit_total_salary()”，聲明IN參數 limit_total_salary，DOUBLE類型；聲明 OUT參數total_count，INT類型。函數的功能可以實現累加薪資最高的幾個員工的薪資值，直到薪資總和達到limit_total_salary參數的值，返回累加的人數給total_count。

DELIMITER //
CREATE PROCEDURE get_count_by_limit_total_salary(IN limit_total_salary DOUBLE, OUT total_count INT)
BEGIN
	DECLARE sum_salary DOUBLE DEFAULT 0; # 記錄累加的總工資
	DECLARE cursor_salary DOUBLE DEFAULT 0; # 記錄某一個工資值
	DECLARE emp_count INT DEFAULT 0; # 記錄循環個數
	# 定義游標
	DECLARE emp_cursor CURSOR FOR SELECT salary FROM employees ORDER BY salary DESC;
	# 打開游標
	OPEN emp_cursor;
	
	REPEAT
		# 使用游標(從游標中獲取數據)
		FETCH emp_cursor INTO cursor_salary;
		SET sum_salary = sum_salary + cursor_salary;
		SET emp_count = emp_count + 1;
		UNTIL sum_salary >= limit_total_salary
	END REPEAT;
	set total_count = emp_count;
	# 關閉游標
	CLOSE emp_cursor;
END //
DELIMITER;

4) 小結

游標是 MySQL 的一個重要的功能，為逐條讀取結果集中的數據，提供了完美的解決方案。跟在應用層面實現相同的功能相比，游標可以在存儲程序中使用，效率高，程序也更加簡潔。

但同時也會帶來一些性能問題，比如在使用游標的過程中，會對數據行進行加鎖，這樣在業務并發量大的時候，不僅會影響業務之間的效率，還會消耗系統資源，造成內存不足，這是因為游標是在內存中進行的處理。

建議：養成用完之后就關閉的習慣，這樣才能提高系統的整體效率。

補充：MySQL 8.0的新特性—全局變量的持久化

在MySQL數據庫中，全局變量可以通過SET GLOBAL語句來設置。例如，設置服務器語句超時的限制，可以通過設置系統變量max_execution_time來實現：

SET GLOBAL MAX_EXECUTION_TIME=2000;

使用SET GLOBAL語句設置的變量值只會臨時生效。數據庫重啟后，服務器又會從MySQL配置文件中讀取變量的默認值。 MySQL 8.0版本新增了 SET PERSIST 命令。例如，設置服務器的最大連接數為1000：

SET PERSIST global max_connections = 1000;

MySQL會將該命令的配置保存到數據目錄下的 mysqld-auto.cnf 文件中，下次啟動時會讀取該文件，用其中的配置來覆蓋默認的配置文件。

觸發器

在實際開發中，我們經常會遇到這樣的情況：有 2 個或者多個相互關聯的表，如商品信息和庫存信息分別存放在 2 個不同的數據表中，我們在添加一條新商品記錄的時候，為了保證數據的完整性，必須同時在庫存表中添加一條庫存記錄。

這樣一來，我們就必須把這兩個關聯的操作步驟寫到程序里面，而且要用事務包裹起來，確保這兩個操作成為一個原子操作，要么全部執行，要么全部不執行。要是遇到特殊情況，可能還需要對數據進行手動維護，這樣就很容易忘記其中的一步，導致數據缺失。

這個時候，咱們可以使用觸發器。你可以創建一個觸發器，讓商品信息數據的插入操作自動觸發庫存數據的插入操作。這樣一來，就不用擔心因為忘記添加庫存數據而導致的數據缺失了。

1. 觸發器概述

觸發器是由事件來觸發某個操作，這些事件包括 INSERT 、 UPDATE 、 DELETE 事件。所謂事件就是指用戶的動作或者觸發某項行為。如果定義了觸發程序，當數據庫執行這些語句時候，就相當于事件發生了，就會自動激發觸發器執行相應的操作。

當對數據表中的數據執行插入、更新和刪除操作，需要自動執行一些數據庫邏輯時，可以使用觸發器來實現。

2. 觸發器的創建

1) 語法

CREATE TRIGGER 觸發器名稱
{BEFORE|AFTER} {INSERT|UPDATE|DELETE} ON 表名
FOR EACH ROW
觸發器執行的語句塊

說明：

表名：表示觸發器監控的對象。
BEFORE|AFTER ：表示觸發的時間。BEFORE 表示在事件之前觸發；AFTER 表示在事件之后觸發。
INSERT|UPDATE|DELETE ：表示觸發的事件。
- INSERT 表示插入記錄時觸發；
- UPDATE 表示更新記錄時觸發；
- DELETE 表示刪除記錄時觸發。
觸發器執行的語句塊：可以是單條SQL語句，也可以是由BEGIN…END結構組成的復合語句塊。

2) 代碼舉例

舉例1：

創建數據表：

CREATE TABLE test_trigger (
id INT PRIMARY KEY AUTO_INCREMENT,
t_note VARCHAR(30)
);

CREATE TABLE test_trigger_log (
id INT PRIMARY KEY AUTO_INCREMENT,
t_log VARCHAR(30)
);

創建觸發器：創建名稱為before_insert的觸發器，向test_trigger數據表插入數據之前，向 test_trigger_log數據表中插入before_insert的日志信息。

DELIMITER //
CREATE TRIGGER before_insert
BEFORE INSERT ON test_trigger
FOR EACH ROW
BEGIN
    INSERT INTO test_trigger_log (t_log)
    VALUES('before_insert');
END //
DELIMITER ;

向test_trigger數據表中插入數據

INSERT INTO test_trigger (t_note) VALUES ('測試 BEFORE INSERT 觸發器');

查看test_trigger_log數據表中的數據

mysql> SELECT * FROM test_trigger_log;
+----+---------------+
| id | t_log |
+----+---------------+
| 1 | before_insert |
+----+---------------+
1 row in set (0.00 sec)

舉例2：

定義觸發器“salary_check_trigger”，基于員工表“employees”的INSERT事件，在INSERT之前檢查將要添加的新員工薪資是否大于他領導的薪資，如果大于領導薪資，則報sqlstate_value為'HY000'的錯誤，從而使得添加失敗。

DELIMITER //
CREATE TRIGGER salary_check_trigger
BEFORE INSERT ON employees FOR EACH ROW
BEGIN
    DECLARE mgrsalary DOUBLE;
    SELECT salary INTO mgrsalary FROM employees WHERE employee_id = NEW.manager_id;
    IF NEW.salary > mgrsalary THEN
    	SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = '薪資高于領導薪資錯誤';
    END IF;
END //
DELIMITER ;

上面觸發器聲明過程中的NEW關鍵字代表INSERT添加語句的新記錄。

3. 查看、刪除觸發器

1) 查看觸發器

查看觸發器是查看數據庫中已經存在的觸發器的定義、狀態和語法信息等。

方式1：查看當前數據庫的所有觸發器的定義

SHOW TRIGGERS\G

方式2：查看當前數據庫中某個觸發器的定義

SHOW CREATE TRIGGER 觸發器名

方式3：從系統庫information_schema的TRIGGERS表中查詢“salary_check_trigger”觸發器的信息。

SELECT * FROM information_schema.TRIGGERS;

2) 刪除觸發器

觸發器也是數據庫對象，刪除觸發器也用DROP語句，語法格式如下：

DROP TRIGGER IF EXISTS 觸發器名稱;

4. 觸發器的優缺點

1) 優點

1、觸發器可以確保數據的完整性。

假設我們用進貨單頭表（demo.importhead）來保存進貨單的總體信息，包括進貨單編號、供貨商編號、倉庫編號、總計進貨數量、總計進貨金額和驗收日期。

listnumber (進貨單編號)	supplierid (進貨商編號)	stockid (參庫編號)	quantity (總計數量)	importvalue (總計金額)	confirmationdate （驗收日期)

用進貨單明細表（demo.importdetails）來保存進貨商品的明細，包括進貨單編號、商品編號、進貨數量、進貨價格和進貨金額。

listnumber (進貨單編號)	itemnumber (商品編號)	quantity (進貨數量)	importprice (進貨價格)	importvalue （進貨金額)

每當我們錄入、刪除和修改一條進貨單明細數據的時候，進貨單明細表里的數據就會發生變動。這個時候，在進貨單頭表中的總計數量和總計金額就必須重新計算，否則，進貨單頭表中的總計數量和總計金額就不等于進貨單明細表中數量合計和金額合計了，這就是數據不一致。

為了解決這個問題，我們就可以使用觸發器，規定每當進貨單明細表有數據插入、修改和刪除的操作時，自動觸發 2 步操作：

1）重新計算進貨單明細表中的數量合計和金額合計；

2）用第一步中計算出來的值更新進貨單頭表中的合計數量與合計金額。

這樣一來，進貨單頭表中的合計數量與合計金額的值，就始終與進貨單明細表中計算出來的合計數量與合計金額的值相同，數據就是一致的，不會互相矛盾。

2、觸發器可以幫助我們記錄操作日志。

利用觸發器，可以具體記錄什么時間發生了什么。比如，記錄修改會員儲值金額的觸發器，就是一個很好的例子。這對我們還原操作執行時的具體場景，更好地定位問題原因很有幫助。

3、觸發器還可以用在操作數據前，對數據進行合法性檢查。

比如，超市進貨的時候，需要庫管錄入進貨價格。但是，人為操作很容易犯錯誤，比如說在錄入數量的時候，把條形碼掃進去了；錄入金額的時候，看串了行，錄入的價格遠超售價，導致賬面上的巨虧…… 這些都可以通過觸發器，在實際插入或者更新操作之前，對相應的數據進行檢查，及時提示錯誤，防止錯誤數據進入系統。

2) 缺點

1、觸發器最大的一個問題就是可讀性差。

因為觸發器存儲在數據庫中，并且由事件驅動，這就意味著觸發器有可能不受應用層的控制。這對系統維護是非常有挑戰的。

2、相關數據的變更，可能會導致觸發器出錯。

特別是數據表結構的變更，都可能會導致觸發器出錯，進而影響數據操作的正常運行。這些都會由于觸發器本身的隱蔽性，影響到應用中錯誤原因排查的效率。

3) 注意點

注意，如果在子表中定義了外鍵約束，并且外鍵指定了ON UPDATE/DELETE CASCADE/SET NULL子句，此時修改父表被引用的鍵值或刪除父表被引用的記錄行時，也會引起子表的修改和刪除操作，此時基于子表的UPDATE和DELETE語句定義的觸發器并不會被激活。

例如：基于子表員工表（t_employee）的DELETE語句定義了觸發器t1，而子表的部門編號（did）字段定義了外鍵約束引用了父表部門表（t_department）的主鍵列部門編號（did），并且該外鍵加了“ON DELETE SET NULL”子句，那么如果此時刪除父表部門表（t_department）在子表員工表（t_employee）有匹配記錄的部門記錄時，會引起子表員工表（t_employee）匹配記錄的部門編號（did）修改為NULL， mysql> update demo.membermaster set memberdeposit=20 where memberid = 2; ERROR 1054 (42S22): Unknown column 'aa' in 'field list' 但是此時不會激活觸發器t1。只有直接對子表員工表（t_employee）執行DELETE語句時才會激活觸發器 t1。

MySQL8其他新特性

1. MySQL8新特性概述

MySQL從5.7版本直接跳躍發布了8.0版本，可見這是一個令人興奮的里程碑版本。MySQL 8版本在功能上做了顯著的改進與增強，開發者對MySQL的源代碼進行了重構，最突出的一點是多MySQL Optimizer優化器進行了改進。不僅在速度上得到了改善，還為用戶帶來了更好的性能和更棒的體驗。

1) MySQL8.0 新增特性

更簡便的NoSQL支持 NoSQL泛指非關系型數據庫和數據存儲。隨著互聯網平臺的規模飛速發展，傳統的關系型數據庫已經越來越不能滿足需求。從5.6版本開始，MySQL就開始支持簡單的NoSQL存儲功能。 MySQL 8對這一功能做了優化，以更靈活的方式實現NoSQL功能，不再依賴模式（schema）。
更好的索引在查詢中，正確地使用索引可以提高查詢的效率。MySQL 8中新增了隱藏索引和降序索引。隱藏索引可以用來測試去掉索引對查詢性能的影響。在查詢中混合存在多列索引時，使用降序索引可以提高查詢的性能。
更完善的JSON支持 MySQL從5.7開始支持原生JSON數據的存儲，MySQL 8對這一功能做了優化，增加了聚合函數 JSON_ARRAYAGG() 和 JSON_OBJECTAGG() ，將參數聚合為JSON數組或對象，新增了行內操作符 ->>，是列路徑運算符 ->的增強，對JSON排序做了提升，并優化了JSON的更新操作。
安全和賬戶管理 MySQL 8中新增了 caching_sha2_password 授權插件、角色、密碼歷史記錄和FIPS 模式支持，這些特性提高了數據庫的安全性和性能，使數據庫管理員能夠更靈活地進行賬戶管理工作。
InnoDB的變化 InnoDB是MySQL默認的存儲引擎，是事務型數據庫的首選引擎，支持事務安全表（ACID），支持行鎖定和外鍵。在MySQL 8 版本中，InnoDB在自增、索引、加密、死鎖、共享鎖等方面做了大量的改進和優化，并且支持原子數據定義語言（DDL），提高了數據安全性，對事務提供更好的支持。
數據字典在之前的MySQL版本中，字典數據都存儲在元數據文件和非事務表中。從MySQL 8開始新增了事務數據字典，在這個字典里存儲著數據庫對象信息，這些數據字典存儲在內部事務表中。
原子數據定義語句 MySQL 8開始支持原子數據定義語句（Automic DDL），即原子DDL 。目前，只有 InnoDB存儲引擎支持原子DDL。原子數據定義語句（DDL）將與DDL操作相關的數據字典更新、存儲引擎操作、二進制日志寫入結合到一個單獨的原子事務中，這使得即使服務器崩潰，事務也會提交或回滾。使用支持原子操作的存儲引擎所創建的表，在執行DROP TABLE、CREATE TABLE、ALTER TABLE、 RENAME TABLE、TRUNCATE TABLE、CREATE TABLESPACE、DROP TABLESPACE等操作時，都支持原子操作，即事務要么完全操作成功，要么失敗后回滾，不再進行部分提交。對于從MySQL 5.7復制到MySQL 8 版本中的語句，可以添加 IF EXISTS 或 IF NOT EXISTS 語句來避免發生錯誤。
資源管理 MySQL 8開始支持創建和管理資源組，允許將服務器內運行的線程分配給特定的分組，以便線程根據組內可用資源執行。組屬性能夠控制組內資源，啟用或限制組內資源消耗。數據庫管理員能夠根據不同的工作負載適當地更改這些屬性。目前，CPU時間是可控資源，由“虛擬CPU”這個概念來表示，此術語包含CPU的核心數，超線程，硬件線程等等。服務器在啟動時確定可用的虛擬CPU數量。擁有對應權限的數據庫管理員可以將這些CPU與資源組關聯，并為資源組分配線程。資源組組件為MySQL中的資源組管理提供了SQL接口。資源組的屬性用于定義資源組。MySQL中存在兩個默認組，系統組和用戶組，默認的組不能被刪除，其屬性也不能被更改。對于用戶自定義的組，資源組創建時可初始化所有的屬性，除去名字和類型，其他屬性都可在創建之后進行更改。在一些平臺下，或進行了某些MySQL的配置時，資源管理的功能將受到限制，甚至不可用。例如，如果安裝了線程池插件，或者使用的是macOS 系統，資源管理將處于不可用狀態。在FreeBSD和Solaris系統中，資源線程優先級將失效。在Linux系統中，只有配置了CAP_SYS_NICE屬性，資源管理優先級才能發揮作用。
字符集支持 MySQL 8中默認的字符集由 latin1 更改為 utf8mb4 ，并首次增加了日語所特定使用的集合，utf8mb4_ja_0900_as_cs。
優化器增強 MySQL優化器開始支持隱藏索引和降序索引。隱藏索引不會被優化器使用，驗證索引的必要性時不需要刪除索引，先將索引隱藏，如果優化器性能無影響就可以真正地刪除索引。降序索引允許優化器對多個列進行排序，并且允許排序順序不一致。
公用表表達式公用表表達式（Common Table Expressions）簡稱為CTE，MySQL現在支持遞歸和非遞歸兩種形式的CTE。CTE通過在SELECT語句或其他特定語句前使用WITH語句對臨時結果集進行命名。

基礎語法如下：
```
WITH cte_name (col_name1,col_name2 ...) AS (Subquery)
SELECT * FROM cte_name;
```

? Subquery代表子查詢，子查詢前使用WITH語句將結果集命名為cte_name，在后續的查詢中即可使用 cte_name進行查詢。

窗口函數 MySQL 8開始支持窗口函數。在之前的版本中已存在的大部分聚合函數在MySQL 8中也可以作為窗口函數來使用。

正則表達式支持 MySQL在8.0.4以后的版本中采用支持Unicode的國際化組件庫實現正則表達式操作，這種方式不僅能提供完全的Unicode支持，而且是多字節安全編碼。MySQL增加了REGEXP_LIKE()、 EGEXP_INSTR()、REGEXP_REPLACE()和 REGEXP_SUBSTR()等函數來提升性能。另外，regexp_stack_limit和 regexp_time_limit 系統變量能夠通過匹配引擎來控制資源消耗。
內部臨時表 TempTable存儲引擎取代MEMORY存儲引擎成為內部臨時表的默認存儲引擎。TempTable存儲引擎為VARCHAR和VARBINARY列提供高效存儲。internal_tmp_mem_storage_engine會話變量定義了內部臨時表的存儲引擎，可選的值有兩個，TempTable和MEMORY，其中TempTable為默認的存儲引擎。 temptable_max_ram系統配置項定義了TempTable存儲引擎可使用的最大內存數量。
日志記錄在MySQL 8中錯誤日志子系統由一系列MySQL組件構成。這些組件的構成由系統變量 log_error_services來配置，能夠實現日志事件的過濾和寫入。 WITH cte_name (col_name1,col_name2 ...) AS (Subquery) SELECT * FROM cte_name;
備份鎖新的備份鎖允許在線備份期間執行數據操作語句，同時阻止可能造成快照不一致的操作。新備份鎖由 LOCK INSTANCE FOR BACKUP 和 UNLOCK INSTANCE 語法提供支持，執行這些操作需要備份管理員特權。
增強的MySQL復制 MySQL 8復制支持對 JSON文檔進行部分更新的二進制日志記錄，該記錄使用緊湊的二進制格式，從而節省記錄完整JSON文檔的空間。當使用基于語句的日志記錄時，這種緊湊的日志記錄會自動完成，并且可以通過將新的binlog_row_value_options系統變量值設置為PARTIAL_JSON來啟用。

2) MySQL8.0 移除的舊特性

在MySQL 5.7版本上開發的應用程序如果使用了MySQL8.0 移除的特性，語句可能會失敗，或者產生不同的執行結果。為了避免這些問題，對于使用了移除特性的應用，應當盡力修正避免使用這些特性，并盡可能使用替代方法。

查詢緩存查詢緩存已被移除，刪除的項有：（1）語句：FLUSH QUERY CACHE和RESET QUERY CACHE。（2）系統變量：query_cache_limit、query_cache_min_res_unit、query_cache_size、 query_cache_type、query_cache_wlock_invalidate。（3）狀態變量：Qcache_free_blocks、 Qcache_free_memory、Qcache_hits、Qcache_inserts、Qcache_lowmem_prunes、Qcache_not_cached、 Qcache_queries_in_cache、Qcache_total_blocks。（4）線程狀態：checking privileges on cached query、checking query cache for query、invalidating query cache entries、sending cached result to client、storing result in query cache、waiting for query cache lock。
加密相關刪除的加密相關的內容有：ENCODE()、DECODE()、ENCRYPT()、DES_ENCRYPT()和 DES_DECRYPT()函數，配置項des-key-file，系統變量have_crypt，FLUSH語句的DES_KEY_FILE選項， HAVE_CRYPT CMake選項。對于移除的ENCRYPT()函數，考慮使用SHA2()替代，對于其他移除的函數，使用AES_ENCRYPT()和AES_DECRYPT()替代。
空間函數相關在MySQL 5.7版本中，多個空間函數已被標記為過時。這些過時函數在MySQL 8中都已被移除，只保留了對應的ST_和MBR函數。
\N和NULL 在SQL語句中，解析器不再將\N視為NULL，所以在SQL語句中應使用NULL代替\N。這項變化不會影響使用LOAD DATA INFILE或者SELECT...INTO OUTFILE操作文件的導入和導出。在這類操作中，NULL 仍等同于\N。
mysql_install_db 在MySQL分布中，已移除了mysql_install_db程序，數據字典初始化需要調用帶著-- initialize或者--initialize-insecure選項的mysqld來代替實現。另外，--bootstrap和INSTALL_SCRIPTDIR CMake也已被刪除。
通用分區處理程序通用分區處理程序已從MySQL服務中被移除。為了實現給定表分區，表所使用的存儲引擎需要自有的分區處理程序。提供本地分區支持的MySQL存儲引擎有兩個，即InnoDB和NDB，而在 MySQL 8中只支持InnoDB。
系統和狀態變量信息在INFORMATION_SCHEMA數據庫中，對系統和狀態變量信息不再進行維護。 GLOBAL_VARIABLES、SESSION_VARIABLES、GLOBAL_STATUS、SESSION_STATUS表都已被刪除。另外，系統變量show_compatibility_56也已被刪除。被刪除的狀態變量有Slave_heartbeat_period、 Slave_last_heartbeat,Slave_received_heartbeats、Slave_retried_transactions、Slave_running。以上被刪除的內容都可使用性能模式中對應的內容進行替代。
mysql_plugin工具 mysql_plugin工具用來配置MySQL服務器插件，現已被刪除，可使用--plugin-load或- -plugin-load-add選項在服務器啟動時加載插件或者在運行時使用INSTALL PLUGIN語句加載插件來替代該工具。

2. 新特性1：窗口函數

1) 使用窗口函數前后對比

假設我現在有這樣一個數據表，它顯示了某購物網站在每個城市每個區的銷售額：

CREATE TABLE sales(
id INT PRIMARY KEY AUTO_INCREMENT,
city VARCHAR(15),
county VARCHAR(15),
sales_value DECIMAL
);
INSERT INTO sales(city,county,sales_value)
VALUES
('北京','海淀',10.00),
('北京','朝陽',20.00),
('上海','黃埔',30.00),
('上海','長寧',10.00);

查詢：

mysql> SELECT * FROM sales;
+----+------+--------+-------------+
| id | city | county | sales_value |
+----+------+--------+-------------+
| 1  | 北京  |  海淀   |      10    |
| 2  | 北京  |  朝陽   |      20    |
| 3  | 上海  |  黃埔   |      30    |
| 4  | 上海  |  長寧   |      10    |
+----+------+--------+-------------+
4 rows in set (0.00 sec)

需求：現在計算這個網站在每個城市的銷售總額、在全國的銷售總額、每個區的銷售額占所在城市銷售額中的比率，以及占總銷售額中的比率。

如果用分組和聚合函數，就需要分好幾步來計算。

第一步，計算總銷售金額，并存入臨時表 a：

CREATE TEMPORARY TABLE a -- 創建臨時表
SELECT SUM(sales_value) AS sales_value -- 計算總計金額
FROM sales;

查看一下臨時表 a ：

mysql> SELECT * FROM a;
+-------------+
| sales_value |
+-------------+
| 70 |
+-------------+
1 row in set (0.00 sec)

第二步，計算每個城市的銷售總額并存入臨時表 b：

CREATE TEMPORARY TABLE b -- 創建臨時表
SELECT city, SUM(sales_value) AS sales_value -- 計算城市銷售合計
FROM sales
GROUP BY city;

查看臨時表 b ：

mysql> SELECT * FROM b;
+------+-------------+
| city | sales_value |
+------+-------------+
| 北京  |     30      |
| 上海  |     40      |
+------+-------------+
2 rows in set (0.00 sec)

第三步，計算各區的銷售占所在城市的總計金額的比例，和占全部銷售總計金額的比例。我們可以通過下面的連接查詢獲得需要的結果：

mysql> SELECT s.city AS 城市,s.county AS 區,s.sales_value AS 區銷售額,
-> b.sales_value AS 市銷售額,s.sales_value/b.sales_value AS 市比率,
-> a.sales_value AS 總銷售額,s.sales_value/a.sales_value AS 總比率
-> FROM sales s
-> JOIN b ON (s.city=b.city) -- 連接市統計結果臨時表
-> JOIN a -- 連接總計金額臨時表
-> ORDER BY s.city,s.county;
+------+------+----------+----------+--------+----------+--------+
| 城市 | 區 | 區銷售額 | 市銷售額 | 市比率 | 總銷售額 | 總比率 |
+------+------+----------+----------+--------+----------+--------+
| 上海 | 長寧 | 10 | 40 | 0.2500 | 70 | 0.1429 |
| 上海 | 黃埔 | 30 | 40 | 0.7500 | 70 | 0.4286 |
| 北京 | 朝陽 | 20 | 30 | 0.6667 | 70 | 0.2857 |
| 北京 | 海淀 | 10 | 30 | 0.3333 | 70 | 0.1429 |
+------+------+----------+----------+--------+----------+--------+
4 rows in set (0.00 sec)

結果顯示：市銷售金額、市銷售占比、總銷售金額、總銷售占比都計算出來了。

同樣的查詢，如果用窗口函數，就簡單多了。我們可以用下面的代碼來實現：

mysql> SELECT city AS 城市,county AS 區,sales_value AS 區銷售額,
-> SUM(sales_value) OVER(PARTITION BY city) AS 市銷售額, -- 計算市銷售額
-> sales_value/SUM(sales_value) OVER(PARTITION BY city) AS 市比率,
-> SUM(sales_value) OVER() AS 總銷售額, -- 計算總銷售額
-> sales_value/SUM(sales_value) OVER() AS 總比率
-> FROM sales
-> ORDER BY city,county;
+------+------+----------+----------+--------+----------+--------+
| 城市 | 區 | 區銷售額 | 市銷售額 | 市比率 | 總銷售額 | 總比率 |
+------+------+----------+----------+--------+----------+--------+
| 上海 | 長寧 | 10 | 40 | 0.2500 | 70 | 0.1429 |
| 上海 | 黃埔 | 30 | 40 | 0.7500 | 70 | 0.4286 |
| 北京 | 朝陽 | 20 | 30 | 0.6667 | 70 | 0.2857 |
| 北京 | 海淀 | 10 | 30 | 0.3333 | 70 | 0.1429 |
+------+------+----------+-----------+--------+----------+--------+
4 rows in set (0.00 sec)

結果顯示，我們得到了與上面那種查詢同樣的結果。

使用窗口函數，只用了一步就完成了查詢。而且，由于沒有用到臨時表，執行的效率也更高了。很顯然，在這種需要用到分組統計的結果對每一條記錄進行計算的場景下，使用窗口函數更好。

2) 窗口函數分類

MySQL從8.0版本開始支持窗口函數。窗口函數的作用類似于在查詢中對數據進行分組，不同的是，分組操作會把分組的結果聚合成一條記錄，而窗口函數是將結果置于每一條數據記錄中。

窗口函數可以分為靜態窗口函數和動態窗口函數。

靜態窗口函數的窗口大小是固定的，不會因為記錄的不同而不同；
動態窗口函數的窗口大小會隨著記錄的不同而變化。

MySQL官方網站窗口函數的網址為https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptio ns.html#function_row-number。

窗口函數總體上可以分為序號函數、分布函數、前后函數、首尾函數和其他函數，如下表：

3) 語法結構

窗口函數的語法結構是：

函數 OVER（[PARTITION BY 字段名 ORDER BY 字段名 ASC|DESC]）

或者是：

函數 OVER 窗口名 … WINDOW 窗口名 AS （[PARTITION BY 字段名 ORDER BY 字段名 ASC|DESC]）

OVER 關鍵字指定函數窗口的范圍。
- 如果省略后面括號中的內容，則窗口會包含滿足WHERE條件的所有記錄，窗口函數會基于所有滿足WHERE條件的記錄進行計算。
- 如果OVER關鍵字后面的括號不為空，則可以使用如下語法設置窗口。
窗口名：為窗口設置一個別名，用來標識窗口。
PARTITION BY子句：指定窗口函數按照哪些字段進行分組。分組后，窗口函數可以在每個分組中分別執行。
ORDER BY子句：指定窗口函數按照哪些字段進行排序。執行排序操作使窗口函數按照排序后的數據記錄的順序進行編號。
FRAME子句：為分區中的某個子集定義規則，可以用來作為滑動窗口使用。

4) 分類講解

創建表：

CREATE TABLE goods(
id INT PRIMARY KEY AUTO_INCREMENT,
category_id INT,
category VARCHAR(15),
NAME VARCHAR(30),
price DECIMAL(10,2),
stock INT,
upper_time DATETIME
);

添加數據：

INSERT INTO goods(category_id,category,NAME,price,stock,upper_time)
VALUES
(1, '女裝/女士精品', 'T恤', 39.90, 1000, '2020-11-10 00:00:00'),
(1, '女裝/女士精品', '連衣裙', 79.90, 2500, '2020-11-10 00:00:00'),
(1, '女裝/女士精品', '衛衣', 89.90, 1500, '2020-11-10 00:00:00'),
(1, '女裝/女士精品', '牛仔褲', 89.90, 3500, '2020-11-10 00:00:00'),
(1, '女裝/女士精品', '百褶裙', 29.90, 500, '2020-11-10 00:00:00'),
(1, '女裝/女士精品', '呢絨外套', 399.90, 1200, '2020-11-10 00:00:00'),
(2, '戶外運動', '自行車', 399.90, 1000, '2020-11-10 00:00:00'),
(2, '戶外運動', '山地自行車', 1399.90, 2500, '2020-11-10 00:00:00'),
(2, '戶外運動', '登山杖', 59.90, 1500, '2020-11-10 00:00:00'),
(2, '戶外運動', '騎行裝備', 399.90, 3500, '2020-11-10 00:00:00'),
(2, '戶外運動', '運動外套', 799.90, 500, '2020-11-10 00:00:00'),
(2, '戶外運動', '滑板', 499.90, 1200, '2020-11-10 00:00:00');

下面針對goods表中的數據來驗證每個窗口函數的功能。

1) 序號函數

1. ROW_NUMBER()函數

ROW_NUMBER()函數能夠對數據中的序號進行順序顯示。

舉例：查詢 goods 數據表中每個商品分類下價格降序排列的各個商品信息。

mysql> SELECT ROW_NUMBER() OVER(PARTITION BY category_id ORDER BY price DESC) AS
row_num, id, category_id, category, NAME, price, stock
FROM goods;
+---------+----+-------------+---------------+------------+---------+-------+
| row_num | id | category_id |    category   |     NAME   |  price  | stock |
+---------+----+-------------+---------------+------------+---------+-------+
|    1    |  6 |     1       |  女裝/女士精品  | 呢絨外套     | 399.90  | 1200  |
|    2    |  3 |     1       |  女裝/女士精品  | 衛衣        | 89.90   | 1500  |
|    3    |  4 |     1       |  女裝/女士精品  | 牛仔褲       | 89.90   | 3500  |
|    4    |  2 |     1       |  女裝/女士精品  | 連衣裙       | 79.90   | 2500  |
|    5    |  1 |     1       |  女裝/女士精品  | T恤         | 39.90   | 1000  |
|    6    |  5 |     1       |  女裝/女士精品  | 百褶裙       | 29.90   | 500   |
|    1    |  8 |     2       |     戶外運動   | 山地自行車    | 1399.90 | 2500  |
|    2    | 11 |     2       |     戶外運動   | 運動外套      | 799.90  | 500  |
|    3    | 12 |     2       |     戶外運動   | 滑板         | 499.90  | 1200  |
|    4    |  7 |     2       |     戶外運動   | 自行車       | 399.90  | 1000  |
|    5    | 10 |     2       |     戶外運動   | 騎行裝備     | 399.90  | 3500  |
|    6    |  9 |     2       |     戶外運動   | 登山杖       | 59.90   | 1500  |
+---------+----+-------------+---------------+------------+---------+-------+
12 rows in set (0.00 sec)

舉例：查詢 goods 數據表中每個商品分類下價格最高的3種商品信息。

mysql> SELECT *
-> FROM (
-> SELECT ROW_NUMBER() OVER(PARTITION BY category_id ORDER BY price DESC) AS
row_num,
-> id, category_id, category, NAME, price, stock
-> FROM goods) t
-> WHERE row_num <= 3;
+---------+----+-------------+---------------+------------+---------+-------+
| row_num | id | category_id |     category  |      NAME  |  price  | stock |
+---------+----+-------------+---------------+------------+---------+-------+
|     1   |  6 |      1      | 女裝/女士精品   | 呢絨外套     | 399.90  | 1200  |
|     2   |  3 |      1      | 女裝/女士精品   | 衛衣        | 89.90   | 1500  |
|     3   |  4 |      1      | 女裝/女士精品   | 牛仔褲      | 89.90    | 3500 |
|     1   |  8 |      2      | 戶外運動       | 山地自行車   | 1399.90  | 2500 |
|     2   | 11 |      2      | 戶外運動       | 運動外套     | 799.90  | 500   |
|     3   | 12 |      2      | 戶外運動       | 滑板        | 499.90   | 1200 |
+---------+----+-------------+---------------+------------+----------+-------+
6 rows in set (0.00 sec)

在名稱為“女裝/女士精品”的商品類別中，有兩款商品的價格為89.90元，分別是衛衣和牛仔褲。兩款商品的序號都應該為2，而不是一個為2，另一個為3。此時，可以使用RANK()函數和DENSE_RANK()函數解決。

2．RANK()函數

使用RANK()函數能夠對序號進行并列排序，并且會跳過重復的序號，比如序號為1、1、3。

舉例：使用RANK()函數獲取 goods 數據表中各類別的價格從高到低排序的各商品信息。

mysql> SELECT RANK() OVER(PARTITION BY category_id ORDER BY price DESC) AS row_num,
-> id, category_id, category, NAME, price, stock
-> FROM goods;
+---------+----+-------------+---------------+------------+---------+-------+
| row_num | id | category_id | category      | NAME       | price   | stock |
+---------+----+-------------+---------------+------------+---------+-------+
|     1   | 6  |     1       | 女裝/女士精品   | 呢絨外套     | 399.90  | 1200  |
|     2   | 3  |     1       | 女裝/女士精品   | 衛衣        | 89.90   | 1500  |
|     2   | 4  |     1       | 女裝/女士精品   | 牛仔褲      | 89.90   | 3500   |
|     4   | 2  |     1       | 女裝/女士精品   | 連衣裙      | 79.90   | 2500   |
|     5   | 1  |     1       | 女裝/女士精品   | T恤        | 39.90   | 1000   |
|     6   | 5  |     1       | 女裝/女士精品   | 百褶裙      | 29.90   | 500    |
|     1   | 8  |     2       | 戶外運動       | 山地自行車   | 1399.90 | 2500   |
|     2   | 11 |     2       | 戶外運動       | 運動外套     | 799.90  | 500   |
|     3   | 12 |     2       | 戶外運動       | 滑板        | 499.90  | 1200   |
|     4   | 7  |     2       | 戶外運動       | 自行車      | 399.90   | 1000  |
|     4   | 10 |     2       | 戶外運動       | 騎行裝備    | 399.90   | 3500  |
|     6   | 9  |     2       | 戶外運動       | 登山杖      | 59.90   | 1500   |
+---------+----+-------------+---------------+------------+---------+-------+
12 rows in set (0.00 sec)

3．DENSE_RANK()函數

DENSE_RANK()函數對序號進行并列排序，并且不會跳過重復的序號，比如序號為1、1、2。舉例：使用DENSE_RANK()函數獲取 goods 數據表中各類別的價格從高到低排序的各商品信息。

mysql> SELECT DENSE_RANK() OVER(PARTITION BY category_id ORDER BY price DESC) AS
row_num,
-> id, category_id, category, NAME, price, stock
-> FROM goods;
+---------+----+-------------+---------------+------------+---------+-------+
| row_num | id | category_id | category      | NAME       | price   | stock |
+---------+----+-------------+---------------+------------+---------+-------+
|    1    | 6  |      1      | 女裝/女士精品   |     呢絨外套 | 399.90 | 1200   |
|    2    | 3  |      1      | 女裝/女士精品   |     衛衣    | 89.90  | 1500   |
|    2    | 4  |      1      | 女裝/女士精品   |     牛仔褲  | 89.90   | 3500  |
|    3    | 2  |      1      | 女裝/女士精品   |     連衣裙  | 79.90   | 2500  |
|    4    | 1  |      1      | 女裝/女士精品   |     T恤    | 39.90   | 1000  |
|    5    | 5  |      1      | 女裝/女士精品   |     百褶裙  | 29.90   | 500   |
|    1    | 8  |      2      | 戶外運動       |    山地自行車| 1399.90 | 2500 |
|    2    | 11 |      2      | 戶外運動       |    運動外套  | 799.90 | 500    |
|    3    | 12 |      2      | 戶外運動       |    滑板     | 499.90 | 1200   |
|    4    | 7  |      2      | 戶外運動       |    自行車    | 399.90 | 1000   |
|    4    | 10 |      2      | 戶外運動       |    騎行裝備  | 399.90 | 3500   |
|    5    | 9  |      2      | 戶外運動       |    登山杖    | 59.90 | 1500   |
+---------+----+-------------+---------------+------------+---------+-------+
12 rows in set (0.00 sec)

2) 分布函數

1．PERCENT_RANK()函數

PERCENT_RANK()函數是等級值百分比函數。按照如下方式進行計算。

(rank - 1) / (rows - 1)

其中，rank的值為使用RANK()函數產生的序號，rows的值為當前窗口的總記錄數。

舉例：計算 goods 數據表中名稱為“女裝/女士精品”的類別下的商品的PERCENT_RANK值。

#寫法一：
SELECT RANK() OVER (PARTITION BY category_id ORDER BY price DESC) AS r,
PERCENT_RANK() OVER (PARTITION BY category_id ORDER BY price DESC) AS pr,
id, category_id, category, NAME, price, stock
FROM goods
WHERE category_id = 1;
#寫法二：
mysql> SELECT RANK() OVER w AS r,
-> PERCENT_RANK() OVER w AS pr,
-> id, category_id, category, NAME, price, stock
-> FROM goods
-> WHERE category_id = 1 WINDOW w AS (PARTITION BY category_id ORDER BY price
DESC);
+---+-----+----+-------------+---------------+----------+--------+-------+
| r | pr  | id | category_id | category      | NAME     | price  | stock |
+---+-----+----+-------------+---------------+----------+--------+-------+
| 1 | 0   | 6  |          1  | 女裝/女士精品   |   呢絨外套 | 399.90 | 1200 |
| 2 | 0.2 | 3  |          1  | 女裝/女士精品   |   衛衣    | 89.90 | 1500 |
| 2 | 0.2 | 4  |          1  | 女裝/女士精品   |   牛仔褲  | 89.90 | 3500 |
| 4 | 0.6 | 2  |          1  | 女裝/女士精品   |   連衣裙  | 79.90 | 2500 |
| 5 | 0.8 | 1  |          1  | 女裝/女士精品   |   T恤    | 39.90 | 1000 |
| 6 | 1   | 5  |          1  | 女裝/女士精品   |   百褶裙  | 29.90 | 500 |
+---+-----+----+-------------+---------------+----------+--------+-------+
6 rows in set (0.00 sec)

2．CUME_DIST()函數

CUME_DIST()函數主要用于查詢小于或等于某個值的比例。

舉例：查詢goods數據表中小于或等于當前價格的比例。

mysql> SELECT CUME_DIST() OVER(PARTITION BY category_id ORDER BY price ASC) AS cd,
-> id, category, NAME, price
-> FROM goods;
+---------------------+----+---------------+------------+---------+
|                cd   | id | category      | NAME       | price   |
+---------------------+----+---------------+------------+---------+
| 0.16666666666666666 | 5  | 女裝/女士精品   | 百褶裙      | 29.90 |
| 0.3333333333333333  | 1  | 女裝/女士精品   | T恤        | 39.90 |
| 0.5                 | 2  | 女裝/女士精品   | 連衣裙      | 79.90 |
| 0.8333333333333334  | 3  | 女裝/女士精品   | 衛衣       | 89.90 |
| 0.8333333333333334  | 4  | 女裝/女士精品   | 牛仔褲     | 89.90 |
| 1                   | 6  | 女裝/女士精品   | 呢絨外套    | 399.90 |
| 0.16666666666666666 | 9  | 戶外運動       | 登山杖      | 59.90 |
| 0.5                 | 7  | 戶外運動       | 自行車      | 399.90 |
| 0.5                 | 10 | 戶外運動       | 騎行裝備     | 399.90 |
| 0.6666666666666666  | 12 | 戶外運動       | 滑板        | 499.90 |
| 0.8333333333333334  | 11 | 戶外運動       | 運動外套     | 799.90 |
| 1                   | 8  | 戶外運動       | 山地自行車   | 1399.90 |
+---------------------+----+---------------+------------+---------+
12 rows in set (0.00 sec)

3) 前后函數

1．LAG(expr,n)函數

LAG(expr,n)函數返回當前行的前n行的expr的值。

舉例：查詢goods數據表中前一個商品價格與當前商品價格的差值。

mysql> SELECT id, category, NAME, price, pre_price, price - pre_price AS diff_price
-> FROM (
-> SELECT id, category, NAME, price,LAG(price,1) OVER w AS pre_price
-> FROM goods
-> WINDOW w AS (PARTITION BY category_id ORDER BY price)) t;
+----+---------------+------------+---------+-----------+------------+
| id | category | NAME | price | pre_price | diff_price |
+----+---------------+------------+---------+-----------+------------+
| 5 | 女裝/女士精品 | 百褶裙 | 29.90 | NULL | NULL |
| 1 | 女裝/女士精品 | T恤 | 39.90 | 29.90 | 10.00 |
| 2 | 女裝/女士精品 | 連衣裙 | 79.90 | 39.90 | 40.00 |
| 3 | 女裝/女士精品 | 衛衣 | 89.90 | 79.90 | 10.00 |
| 4 | 女裝/女士精品 | 牛仔褲 | 89.90 | 89.90 | 0.00 |
| 6 | 女裝/女士精品 | 呢絨外套 | 399.90 | 89.90 | 310.00 |
| 9 | 戶外運動 | 登山杖 | 59.90 | NULL | NULL |
| 7 | 戶外運動 | 自行車 | 399.90 | 59.90 | 340.00 |
| 10 | 戶外運動 | 騎行裝備 | 399.90 | 399.90 | 0.00 |
| 12 | 戶外運動 | 滑板 | 499.90 | 399.90 | 100.00 |
| 11 | 戶外運動 | 運動外套 | 799.90 | 499.90 | 300.00 |
| 8 | 戶外運動 | 山地自行車 | 1399.90 | 799.90 | 600.00 |
+----+---------------+------------+---------+-----------+------------+
12 rows in set (0.00 sec)

2．LEAD(expr,n)函數

LEAD(expr,n)函數返回當前行的后n行的expr的值。

舉例：查詢goods數據表中后一個商品價格與當前商品價格的差值。

mysql> SELECT id, category, NAME, behind_price, price,behind_price - price AS
diff_price
-> FROM(
-> SELECT id, category, NAME, price,LEAD(price, 1) OVER w AS behind_price
-> FROM goods WINDOW w AS (PARTITION BY category_id ORDER BY price)) t;
+----+---------------+------------+--------------+---------+------------+
| id | category      | NAME       | behind_price | price   | diff_price |
+----+---------------+------------+--------------+---------+------------+
| 5  | 女裝/女士精品   | 百褶裙       | 39.90       | 29.90 | 10.00 |
| 1  | 女裝/女士精品   | T恤         | 79.90       | 39.90 | 40.00 |
| 2  | 女裝/女士精品   | 連衣裙      | 89.90        | 79.90 | 10.00 |
| 3  | 女裝/女士精品   | 衛衣        | 89.90       | 89.90 | 0.00 |
| 4  | 女裝/女士精品   | 牛仔褲       | 399.90     | 89.90 | 310.00 |
| 6  | 女裝/女士精品   | 呢絨外套     | NULL       | 399.90 | NULL |
| 9  | 戶外運動       | 登山杖       | 399.90    | 59.90 | 340.00 |
| 7  | 戶外運動       | 自行車       | 399.90    | 399.90 | 0.00 |
| 10 | 戶外運動       | 騎行裝備     | 499.90     | 399.90 | 100.00 |
| 12 | 戶外運動       | 滑板         | 799.90    | 499.90 | 300.00 |
| 11 | 戶外運動       | 運動外套     | 1399.90    | 799.90 | 600.00 |
| 8  | 戶外運動       | 山地自行車   | NULL       | 1399.90 | NULL |
+----+---------------+------------+--------------+---------+------------+
12 rows in set (0.00 sec)

4) 首尾函數

1．FIRST_VALUE(expr)函數

FIRST_VALUE(expr)函數返回第一個expr的值。

舉例：按照價格排序，查詢第1個商品的價格信息。

mysql> SELECT id, category, NAME, price, stock,FIRST_VALUE(price) OVER w AS
first_price
-> FROM goods WINDOW w AS (PARTITION BY category_id ORDER BY price);
+----+---------------+------------+---------+-------+-------------+
| id | category      | NAME | price | stock | first_price |
+----+---------------+------------+---------+-------+-------------+
| 5  | 女裝/女士精品   | 百褶裙 | 29.90 | 500 | 29.90 |
| 1  | 女裝/女士精品   | T恤 | 39.90 | 1000 | 29.90 |
| 2  | 女裝/女士精品   | 連衣裙 | 79.90 | 2500 | 29.90 |
| 3  | 女裝/女士精品   | 衛衣 | 89.90 | 1500 | 29.90 |
| 4  | 女裝/女士精品   | 牛仔褲 | 89.90 | 3500 | 29.90 |
| 6  | 女裝/女士精品   | 呢絨外套 | 399.90 | 1200 | 29.90 |
| 9  | 戶外運動       | 登山杖 | 59.90 | 1500 | 59.90 |
| 7  | 戶外運動       | 自行車 | 399.90 | 1000 | 59.90 |
| 10 | 戶外運動       | 騎行裝備 | 399.90 | 3500 | 59.90 |
| 12 | 戶外運動       | 滑板 | 499.90 | 1200 | 59.90 |
| 11 | 戶外運動       | 運動外套 | 799.90 | 500 | 59.90 |
| 8  | 戶外運動       | 山地自行車 | 1399.90 | 2500 | 59.90 |
+----+---------------+------------+---------+-------+-------------+
12 rows in set (0.00 sec)

LAST_VALUE(expr)函數

LAST_VALUE(expr)函數返回最后一個expr的值。

舉例：按照價格排序，查詢最后一個商品的價格信息。

mysql> SELECT id, category, NAME, price, stock,LAST_VALUE(price) OVER w AS last_price
-> FROM goods WINDOW w AS (PARTITION BY category_id ORDER BY price);
+----+---------------+------------+---------+-------+------------+
| id | category      | NAME | price | stock | last_price |
+----+---------------+------------+---------+-------+------------+
| 5  | 女裝/女士精品   | 百褶裙 | 29.90 | 500 | 29.90 |
| 1  | 女裝/女士精品   | T恤 | 39.90 | 1000 | 39.90 |
| 2  | 女裝/女士精品   | 連衣裙 | 79.90 | 2500 | 79.90 |
| 3  | 女裝/女士精品   | 衛衣 | 89.90 | 1500 | 89.90 |
| 4  | 女裝/女士精品   | 牛仔褲 | 89.90 | 3500 | 89.90 |
| 6  | 女裝/女士精品   | 呢絨外套 | 399.90 | 1200 | 399.90 |
| 9  | 戶外運動       | 登山杖 | 59.90 | 1500 | 59.90 |
| 7  | 戶外運動       | 自行車 | 399.90 | 1000 | 399.90 |
| 10 | 戶外運動       | 騎行裝備 | 399.90 | 3500 | 399.90 |
| 12 | 戶外運動       | 滑板 | 499.90 | 1200 | 499.90 |
| 11 | 戶外運動       | 運動外套 | 799.90 | 500 | 799.90 |
| 8  | 戶外運動       | 山地自行車 | 1399.90 | 2500 | 1399.90 |
+----+---------------+------------+---------+-------+------------+
12 rows in set (0.00 sec)

5) 其他函數

1．NTH_VALUE(expr,n)函數

NTH_VALUE(expr,n)函數返回第n個expr的值。舉例：查詢goods數據表中排名第2和第3的價格信息。

mysql> SELECT id, category, NAME, price,NTH_VALUE(price,2) OVER w AS second_price,
-> NTH_VALUE(price,3) OVER w AS third_price
-> FROM goods WINDOW w AS (PARTITION BY category_id ORDER BY price);
+----+---------------+------------+---------+--------------+-------------+
| id | category      | NAME       | price   | second_price | third_price |
+----+---------------+------------+---------+--------------+-------------+
| 5  | 女裝/女士精品   | 百褶裙 | 29.90 | NULL | NULL |
| 1  | 女裝/女士精品   | T恤 | 39.90 | 39.90 | NULL |
| 2  | 女裝/女士精品   | 連衣裙 | 79.90 | 39.90 | 79.90 |
| 3  | 女裝/女士精品   | 衛衣 | 89.90 | 39.90 | 79.90 |
| 4  | 女裝/女士精品   | 牛仔褲 | 89.90 | 39.90 | 79.90 |
| 6  | 女裝/女士精品   | 呢絨外套 | 399.90 | 39.90 | 79.90 |
| 9  | 戶外運動       | 登山杖 | 59.90 | NULL | NULL |
| 7  | 戶外運動       | 自行車 | 399.90 | 399.90 | 399.90 |
| 10 | 戶外運動       | 騎行裝備 | 399.90 | 399.90 | 399.90 |
| 12 | 戶外運動       | 滑板 | 499.90 | 399.90 | 399.90 |
| 11 | 戶外運動       | 運動外套 | 799.90 | 399.90 | 399.90 |
| 8  | 戶外運動       | 山地自行車 | 1399.90 | 399.90 | 399.90 |
+----+---------------+------------+---------+--------------+-------------+
12 rows in set (0.00 sec)

2．NTILE(n)函數

NTILE(n)函數將分區中的有序數據分為n個桶，記錄桶編號。

舉例：將goods表中的商品按照價格分為3組。

mysql> SELECT NTILE(3) OVER w AS nt,id, category, NAME, price
-> FROM goods WINDOW w AS (PARTITION BY category_id ORDER BY price);
+----+----+---------------+------------+---------+
| nt | id | category      | NAME       | price |
+----+----+---------------+------------+---------+
| 1  | 5  | 女裝/女士精品   | 百褶裙 | 29.90 |
| 1  | 1  | 女裝/女士精品   | T恤 | 39.90 |
| 2  | 2  | 女裝/女士精品   | 連衣裙 | 79.90 |
| 2  | 3  | 女裝/女士精品   | 衛衣 | 89.90 |
| 3  | 4  | 女裝/女士精品   | 牛仔褲 | 89.90 |
| 3  | 6  | 女裝/女士精品   | 呢絨外套 | 399.90 |
| 1  | 9  | 戶外運動       | 登山杖 | 59.90 |
| 1  | 7  | 戶外運動       | 自行車 | 399.90 |
| 2  | 10 | 戶外運動       | 騎行裝備 | 399.90 |
| 2  | 12 | 戶外運動       | 滑板 | 499.90 |
| 3  | 11 | 戶外運動       | 運動外套 | 799.90 |
| 3  | 8  | 戶外運動       | 山地自行車 | 1399.90 |
+----+----+---------------+------------+---------+
12 rows in set (0.00 sec)

5) 小結

窗口函數的特點是可以分組，而且可以在分組內排序。另外，窗口函數不會因為分組而減少原表中的行數，這對我們在原表數據的基礎上進行統計和排序非常有用。

3. 新特性2：公用表表達式

公用表表達式（或通用表表達式）簡稱為CTE（Common Table Expressions）。CTE是一個命名的臨時結果集，作用范圍是當前語句。CTE可以理解成一個可以復用的子查詢，當然跟子查詢還是有點區別的， CTE可以引用其他CTE，但子查詢不能引用其他子查詢。所以，可以考慮代替子查詢。

依據語法結構和執行方式的不同，公用表表達式分為普通公用表表達式和遞歸公用表表達式 2 種。

1) 普通公用表表達式

普通公用表表達式的語法結構是：

WITH CTE名稱
AS （子查詢）
SELECT|DELETE|UPDATE 語句;

普通公用表表達式類似于子查詢，不過，跟子查詢不同的是，它可以被多次引用，而且可以被其他的普通公用表表達式所引用。

舉例：查詢員工所在的部門的詳細信息。

mysql> SELECT * FROM departments
-> WHERE department_id IN (
-> SELECT DISTINCT department_id
-> FROM employees
-> );
+---------------+------------------+------------+-------------+
| department_id | department_name  | manager_id | location_id |
+---------------+------------------+------------+-------------+
|     10        | Administration   | 200        | 1700        |
|     20        | Marketing        | 201        | 1800        |
|     30        | Purchasing       | 114        | 1700        |
|     40        | Human Resources  | 203        | 2400        |
|     50        | Shipping         | 121        | 1500        |
|     60        | IT               | 103        | 1400        |
|     70        | Public Relations | 204        | 2700        |
|     80        | Sales            | 145        | 2500        |
|     90        | Executive        | 100        | 1700        |
|     100       | Finance          | 108        | 1700        |
|     110       | Accounting       | 205        | 1700        |
+---------------+------------------+------------+-------------+
11 rows in set (0.00 sec)

這個查詢也可以用普通公用表表達式的方式完成：

mysql> WITH emp_dept_id
-> AS (SELECT DISTINCT department_id FROM employees)
-> SELECT *
-> FROM departments d JOIN emp_dept_id e
-> ON d.department_id = e.department_id;
+---------------+------------------+------------+-------------+---------------+
| department_id | department_name  | manager_id | location_id | department_id |
+---------------+------------------+------------+-------------+---------------+
|      90       | Executive        | 100        | 1700        | 90            |
|      60       | IT               | 103        | 1400        | 60            |
|      100      | Finance          | 108        | 1700        | 100           |
|      30       | Purchasing       | 114        | 1700        | 30            |
|      50       | Shipping         | 121        | 1500        | 50            |
|      80       | Sales            | 145        | 2500        | 80            |
|      10       | Administration   | 200        | 1700        | 10            |
|      20       | Marketing        | 201        | 1800        | 20            |
|      40       | Human Resources  | 203        | 2400        | 40            |
|      70       | Public Relations | 204        | 2700        | 70            |
|      110      | Accounting       | 205        | 1700        | 110           |
+---------------+------------------+------------+-------------+---------------+
11 rows in set (0.00 sec)

例子說明，公用表表達式可以起到子查詢的作用。以后如果遇到需要使用子查詢的場景，你可以在查詢之前，先定義公用表表達式，然后在查詢中用它來代替子查詢。而且，跟子查詢相比，公用表表達式有一個優點，就是定義過公用表表達式之后的查詢，可以像一個表一樣多次引用公用表表達式，而子查詢則不能。

2) 遞歸公用表表達式

遞歸公用表表達式也是一種公用表表達式，只不過，除了普通公用表表達式的特點以外，它還有自己的特點，就是可以調用自己。它的語法結構是：

WITH RECURSIVE
CTE名稱 AS （子查詢）
SELECT|DELETE|UPDATE 語句;

遞歸公用表表達式由 2 部分組成，分別是種子查詢和遞歸查詢，中間通過關鍵字 UNION [ALL]進行連接。這里的種子查詢，意思就是獲得遞歸的初始值。這個查詢只會運行一次，以創建初始數據集，之后遞歸查詢會一直執行，直到沒有任何新的查詢數據產生，遞歸返回。

案例：針對于我們常用的employees表，包含employee_id，last_name和manager_id三個字段。如果a是b 的管理者，那么，我們可以把b叫做a的下屬，如果同時b又是c的管理者，那么c就是b的下屬，是a的下下屬。

下面我們嘗試用查詢語句列出所有具有下下屬身份的人員信息。

如果用我們之前學過的知識來解決，會比較復雜，至少要進行 4 次查詢才能搞定：

第一步，先找出初代管理者，就是不以任何別人為管理者的人，把結果存入臨時表；
第二步，找出所有以初代管理者為管理者的人，得到一個下屬集，把結果存入臨時表；
第三步，找出所有以下屬為管理者的人，得到一個下下屬集，把結果存入臨時表。
第四步，找出所有以下下屬為管理者的人，得到一個結果集。

如果第四步的結果集為空，則計算結束，第三步的結果集就是我們需要的下下屬集了，否則就必須繼續進行第四步，一直到結果集為空為止。比如上面的這個數據表，就需要到第五步，才能得到空結果集。而且，最后還要進行第六步：把第三步和第四步的結果集合并，這樣才能最終獲得我們需要的結果集。

如果用遞歸公用表表達式，就非常簡單了。我介紹下具體的思路。

用遞歸公用表表達式中的種子查詢，找出初代管理者。字段 n 表示代次，初始值為 1，表示是第一代管理者。
用遞歸公用表表達式中的遞歸查詢，查出以這個遞歸公用表表達式中的人為管理者的人，并且代次的值加 1。直到沒有人以這個遞歸公用表表達式中的人為管理者了，遞歸返回。
在最后的查詢中，選出所有代次大于等于 3 的人，他們肯定是第三代及以上代次的下屬了，也就是下下屬了。這樣就得到了我們需要的結果集。

這里看似也是 3 步，實際上是一個查詢的 3 個部分，只需要執行一次就可以了。而且也不需要用臨時表保存中間結果，比剛剛的方法簡單多了。

代碼實現：

WITH RECURSIVE cte
AS
(
SELECT employee_id,last_name,manager_id,1 AS n FROM employees WHERE employee_id = 100
-- 種子查詢，找到第一代領導
UNION ALL
SELECT a.employee_id,a.last_name,a.manager_id,n+1 FROM employees AS a JOIN cte
ON (a.manager_id = cte.employee_id) -- 遞歸查詢，找出以遞歸公用表表達式的人為領導的人
)
SELECT employee_id,last_name FROM cte WHERE n >= 3;

總之，遞歸公用表表達式對于查詢一個有共同的根節點的樹形結構數據，非常有用。它可以不受層級的限制，輕松查出所有節點的數據。如果用其他的查詢方式，就比較復雜了。

3) 小結

公用表表達式的作用是可以替代子查詢，而且可以被多次引用。遞歸公用表表達式對查詢有一個共同根節點的樹形結構數據非常高效，可以輕松搞定其他查詢方式難以處理的查詢。

posted @ 2022-12-21 16:18 doubleuch 閱讀(92) 評論(0) 收藏舉報

刷新頁面返回頂部

Doubleuch

MySQL 基礎

初識 SQL

1. SQL語言的規則和規范

1) 基本規則

2) 大小寫規范

3) 注釋

4) 命名規則

2. 基本的SELECT語句

1) SELECT ... FROM

2) 列的別名

3) 去重（DISTINCT）

4) 空值參與運算

5) 著重號（ ` ）

6) 查詢常數

3. 顯示表結構

4. 過濾數據

運算符

1. 算術運算符

2. 比較運算符

1) 等號運算符（=）

2) 不等于運算符（!=）

3) （非）空運算符（IS NULL / IS NOT NULL）

4) 最大/最小值運算符

A. 最小值運算符

B. 最大值運算符

5) BETWEEN AND 運算符

6) IN / NOT IN 運算符

7) LIKE 運算符

8) ESCAPE

9) REGEXP 運算符

3. 位運算

4. 運算符的優先級

擴展：使用正則表達式查詢

排序與分頁

1. 排序規則

1) 單列排序

2) 多列排序

2. 分頁

多表查詢

1. 多表查詢分類

1) 自連接

2) 內連接與外連接

2. UNION 的使用

3.七種 SQL JOINS 的實現

4. SQL99 語法的新特性

1) 自然連接

2) USING連接

5. 小結

單行函數

1. 數值函數

1) 基本函數

2) 角度與弧度互換函數

3) 三角函數

4) 指數與對數函數

5) 進制間的轉換

2. 字符串函數

3. 日期和時間函數

1) 獲取日期、時間

2) 日期與時間戳的轉換

3) 獲取月份、星期、星期數、天數等函數

5) 時間和秒鐘轉換的函數

6) 計算日期和時間的函數

7) 日期的格式化與解析

4. 流程控制函數

5. 加密與解密函數

6. MySQL信息函數

聚合函數

1. 聚合函數介紹

1) AVG和SUM函數

2) MIN和MAX函數

3) COUNT函數

2. GROUP BY

1) 基本使用

2) 使用WITH ROLLUP

3. HAVING

1) 基本使用

2) WHERE和HAVING的對比

4. SELECT的執行過程

1) 查詢的結構