MySQL查询语句

Posted on 2022-04-10 Edited on 2024-07-22 In 数据库 , MySQL Views: Word count in article: 65k Reading time ≈ 59 mins.

1、SQL分类

SQL语言在功能上主要分为如下3大类：
- DDL（Data Definition Languages、数据定义语言），这些语句定义了不同的数据库、表、视图、索引等数据库对象，还可以用来创建、删除、修改数据库和数据表的结构。
  - 主要的语句关键字包括CREATE、DROP、ALTER、RENAME、TRUNCATE等。
- DML（Data Manipulation Language、数据操作语言），用于添加、删除、更新和查询数据库记录，并检查数据完整性。
  - 主要的语句关键字包括INSERT、DELETE、UPDATE、SELECT等。
  - SELECT是SQL语言的基础，最为重要。
- DCL（Data Control Language、数据控制语言），用于定义数据库、表、字段、用户的访问权限和安全级别。
  - 主要的语句关键字包括GRANT、REVOKE、COMMIT、ROLLBACK、SAVEPOINT等。
    
    因为查询语句使用的非常的频繁，所以很多人把查询语句单拎出来一类：DQL（数据查询语言）。还有单独将 COMMIT 、 ROLLBACK 取出来称为TCL（Transaction Control Language，事务控制语言）。

2、SQL语言的规则与规范

2.1 基本规则

SQL可以写在一行或者多行。为了提高可读性，各子句分行写，必要时使用缩进。
每条命令以;或\g或\G结束。
关键字不能被缩写也不能分行。
关于标点符号。
- 必须保证所有的()、单引号、双引号是成对结束的。
- 必须使用英文状态下的半角输入方式。
- 字符串型和日期时间类型的数据可以使用单引号（’ ‘）表示。
- 列的别名，尽量使用双引号（” “），而且不建议省略as。

2.2 SQL大小写规范（建议遵守）

MySQL在Windows环境下是大小写不敏感的。
- 被""包裹的内容是区分大小写的，但是由于MySQL没有严格遵守ANSI规范，所以实际上也表现为不区分大小写。
MySQL在Linux环境下是大小写敏感的。
- 数据库名、表名、表的别名、变量名是严格区分大小写的。
- 关键字、函数名、列名(或字段名)、列的别名(字段的别名) 是忽略大小写的。
推荐采用统一的书写规范：
- 数据库名、表名、表别名、字段名、字段别名等都小写。
- SQL关键字、函数名、绑定变量等都大写。

2.3 注释

可以使用如下格式的注释结构：

1
2
3

单行注释：#注释文字(MySQL特有的方式)
单行注释：-- 注释文字(--后面必须包含一个空格。)
多行注释：/* 注释文字 */

2.4 命名规则

数据库、表名不得超过30个字符，变量名限制为29个。
必须只能包含A–Z,a–z,0–9,_共63个字符。
数据库名、表名、字段名等对象名中间不要包含空格。
同一个MySQL软件中，数据库不能同名；同一个库中，表不能重名；同一个表中，字段不能重名。
必须保证你的字段没有和保留字、数据库系统或常用方法冲突。如果坚持使用，请在SQL语句中使用`（着重号）引起来。
保持字段名和类型的一致性，在命名字段并为其指定数据类型的时候一定要保证一致性。假如数据类型在一个表里是整数，那在另一个表里可就别变成字符型了。

3、基本的SELECT语句

3.1 基本语法

SELECT…**

1 2	SELECT 1; #没有任何子句 SELECT 9/2; #没有任何子句

SELECT … FROM

# 语法
SELECT 标识选择哪些列
FROM 标识从哪个表中选择

# 选择全部列
SELECT *
FROM departments;

# 选择特定的列
SELECT department_id, location_id
FROM departments;

一般情况下，除非需要使用表中所有的字段数据，最好不要使用通配符’*’。使用通配符虽然可以节省输入查询语句的时间，但是获取不需要的列数据通常会降低查询和所使用的应用程序的效率。通配符的优势是，当不知道所需要的列的名称时，可以通过它获取它们。

在生产环境下，不推荐你直接使用SELECT *进行查询。

MySQL中的SQL语句是不区分大小写的，因此SELECT和select的作用是相同的，但是，许多开发人员习惯将关键字大写、数据列和表名小写，读者也应该养成一个良好的编程习惯，这样写出来的代码更容易阅读和维护。

3.2 列的别名

重命名一个列。
便于计算。
紧跟列名，也可以在列名和别名之间加入关键字AS，别名使用双引号，以便在别名中包含空格或特殊的字符并区分大小写。
AS可以省略。
建议别名简短，见名知意。
列的别名可以使用一对""引起来。列的别名建议不要使用''，虽然MySQL也支持，但是因为它没有严格遵守ANSI规范造成的。
1
2
3
4
5
SELECT last_name AS name, commission_pct comm
FROM employees;

SELECT last_name "Name", salary*12 "Annual Salary"
FROM employees;

3.3 去除重复行

默认情况下，查询会返回全部行，包括重复行。

在SELECT语句中使用关键字DISTINCT去除重复行。

SELECT DISTINCT department_id
FROM employees;

# DISTINCT需要放到所有列名的前面
# DISTINCT是对后面所有列名的组合进行去重
SELECT DISTINCT department_id,salary
FROM employees;

3.4 空值参与运算

所有运算符或列值遇到null值，运算的结果都为null。
在MySQL里面，空值不等于空字符串。一个空字符串的长度是0，而一个空值的长度是空。而且，在MySQL里面，空值是占用空间的。
1
2
3
SELECT employee_id,salary,commission_pct,
12 * salary * (1 + commission_pct) "annual_sal"
FROM employees;

3.5 着重号

我们需要保证表中的字段、表名等没有和保留字、数据库系统或常用方法冲突。如果真的相同，请在SQL语句中使用一对``（着重号）引起来。

mysql> SELECT * FROM ORDER;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER' at line 1

mysql> SELECT * FROM `order`;
+----------+------------+
| order_id | order_name |
+----------+------------+
|        1 | shkstart   |
|        2 | tomcat     |
|        3 | dubbo      |
+----------+------------+
3 rows in set (0.00 sec)

3.6 查询常数

SELECT查询还可以对常数进行查询。就是在SELECT查询结果中增加一列固定的常数列，这列的取值是我们指定的，而不是从数据表中动态取出的。
SQL中的SELECT语法的确提供了这个功能，一般来说我们只从一个表中查询数据，通常不需要增加一个固定的常数列，但如果我们想整合不同的数据源，用常数列作为这个表的标记，就需要查询常数。

3.7 显示表结构

使用DESCRIBE或DESC命令，表示表结构。

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    |       |
+----------------+-------------+------+-----+---------+-------+
11 rows in set (0.00 sec)

Field：表示字段名称。
Type：表示字段类型，这里barcode、goodsname是文本型的，price是整数类型的。
Null：表示该列是否可以存储NULL值。
Key：表示该列是否已编制索引。PRI表示该列是表主键的一部分；UNI表示该列是UNIQUE索引的一部分；MUL表示在列中某个给定值允许出现多次。
Default：表示该列是否有默认值，如果有，那么值是多少。
Extra：表示可以获取的与给定列有关的附加信息，例如AUTO_INCREMENT等。

3.8 过滤数据

使用WHERE 子句，将不满足条件的行过滤掉。

WHERE子句紧随FROM子句。

mysql> SELECT employee_id, last_name, job_id, department_id
    -> FROM employees
    -> WHERE department_id = 90 ;
+-------------+-----------+---------+---------------+
| employee_id | last_name | job_id  | department_id |
+-------------+-----------+---------+---------------+
|         100 | King      | AD_PRES |            90 |
|         101 | Kochhar   | AD_VP   |            90 |
|         102 | De Haan   | AD_VP   |            90 |
+-------------+-----------+---------+---------------+
3 rows in set (0.01 sec)

4、运算符

4.1 算术运算符

算术运算符主要用于数学运算，其可以连接运算符前后的两个数值或表达式，对数值或表达式进行加（+）、减（-）、乘（*）、除（/）和取模（%）运算。

加法与减法运算符：

mysql> SELECT 100, 100 + 0, 100 - 0, 100 + 50, 100 + 50 -30, 100 + 35.5, 100 - 35.5, 100 + '1', 100 + 'a' FROM dual;
+-----+---------+---------+----------+--------------+------------+------------+-----------+-----------+
| 100 | 100 + 0 | 100 - 0 | 100 + 50 | 100 + 50 -30 | 100 + 35.5 | 100 - 35.5 | 100 + '1' | 100 + 'a' |
+-----+---------+---------+----------+--------------+------------+------------+-----------+-----------+
| 100 |     100 |     100 |      150 |          120 |      135.5 |       64.5 |       101 |       100 |
+-----+---------+---------+----------+--------------+------------+------------+-----------+-----------+
1 row in set, 1 warning (0.00 sec)

一个整数类型的值对整数进行加法和减法操作，结果还是一个整数；

一个整数类型的值对浮点数进行加法和减法操作，结果是一个浮点数；

加法和减法的优先级相同，进行先加后减操作与进行先减后加操作的结果是一样的；

在Java中，+的左右两边如果有字符串，那么表示字符串的拼接。但是在MySQL中+只表示数值相加。如果遇到非数值类型，先尝试转成数值，如果转失败，就按0计算。（补充：MySQL中字符串拼接要使用字符串函数CONCAT()实现）

乘法与除法运算符：

mysql> SELECT 100, 100 * 1, 100 * 1.0, 100 / 1.0, 100 / 2, 100 + 2 * 5 / 2, 100 / 3, 100 DIV 0, 100 DIV 3 FROM dual;
+-----+---------+-----------+-----------+---------+-----------------+---------+-----------+-----------+
| 100 | 100 * 1 | 100 * 1.0 | 100 / 1.0 | 100 / 2 | 100 + 2 * 5 / 2 | 100 / 3 | 100 DIV 0 | 100 DIV 3 |
+-----+---------+-----------+-----------+---------+-----------------+---------+-----------+-----------+
| 100 |     100 |     100.0 |  100.0000 | 50.0000 |        105.0000 | 33.3333 |      NULL |        33 |
+-----+---------+-----------+-----------+---------+-----------------+---------+-----------+-----------+
1 row in set, 1 warning (0.00 sec)

一个数乘以整数1和除以整数1后仍得原数；

一个数乘以浮点数1和除以浮点数1后变成浮点数，数值与原数相等；

一个数除以整数后，不管是否能除尽，结果都为一个浮点数；

一个数除以另一个数，除不尽时，结果为一个浮点数，并保留到小数点后4位；

乘法和除法的优先级相同，进行先乘后除操作与先除后乘操作，得出的结果相同；

在数学运算中，0不能用作除数，在MySQL中，一个数除以0为NULL。

求模（求余）运算符：

mysql> SELECT 12 % 3, 12 % 5, 12 MOD -5, -12 % 5, -12 % -5 FROM dual;
+--------+--------+-----------+---------+----------+
| 12 % 3 | 12 % 5 | 12 MOD -5 | -12 % 5 | -12 % -5 |
+--------+--------+-----------+---------+----------+
|      0 |      2 |         2 |      -2 |       -2 |
+--------+--------+-----------+---------+----------+
1 row in set (0.00 sec)

4.2 比较运算符

比较运算符用来对表达式左边的操作数和右边的操作数进行比较，比较的结果为真则返回1，比较的结果为假则返回0，其他情况则返回NULL。

比较运算符经常被用来作为SELECT查询语句的条件来使用，返回符合条件的结果记录。

等号运算符：

等号运算符（=）判断等号两边的值、字符串或表达式是否相等，如果相等则返回1，不相等则返回0。
在使用等号运算符时，遵循如下规则：
- 如果等号两边的值、字符串或表达式都为字符串，则MySQL会按照字符串进行比较，其比较的是每个字符串中字符的ANSI编码是否相等。
- 如果等号两边的值都是整数，则MySQL会按照整数来比较两个值的大小。
- 如果等号两边的值一个是整数，另一个是字符串，则MySQL会将字符串转化为数字进行比较。如果字符串不能隐式转换为数字，就会把它当成0。
- 如果等号两边的值、字符串或表达式中有一个为NULL，则比较结果为NULL。
对比：SQL中赋值符号使用:=。

mysql> 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 |
+-------+---------+-------+-----------+-------------------+-----------+-------------+
1 row in set (0.00 sec)

# 不会查询出salary为NULL的数据，因为NULL=NULL的结果为NULL，而where只会查询出比较结果为1的数据
mysql> SELECT employee_id,salary FROM employees WHERE salary = NULL;
Empty set (0.00 sec)

安全等于运算符：

安全等于运算符（<=>）与等于运算符（=）的作用是相似的，唯一的区别是<=>可以用来对NULL进行判断。在两个操作数均为NULL时，其返回值为1，而不为NULL；当一个操作数为NULL时，其返回值为0，而不为NULL。

mysql> SELECT 1 <=> '1', 1 <=> 0, 'a' <=> 'a', (5 + 3) <=> (2 + 6), '' <=> NULL,NULL <=> NULL FROM dual;
+-----------+---------+-------------+---------------------+-------------+---------------+
| 1 <=> '1' | 1 <=> 0 | 'a' <=> 'a' | (5 + 3) <=> (2 + 6) | '' <=> NULL | NULL <=> NULL |
+-----------+---------+-------------+---------------------+-------------+---------------+
|         1 |       0 |           1 |                   1 |           0 |             1 |
+-----------+---------+-------------+---------------------+-------------+---------------+
1 row in set (0.00 sec)

可以看到，使用安全等于运算符时，两边的操作数的值都为NULL时，返回的结果为1而不是NULL，其他返回结果与等于运算符相同。

不等于运算符：

不等于运算符（<>和!=）用于判断两边的数字、字符串或者表达式的值是否不相等，如果不相等则返回1，相等则返回0。不等于运算符不能判断NULL值。如果两边的值有任意一个为NULL，或两边都为NULL，则结果为NULL。

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 |
    +--------+--------+------------+----------------+-------------+--------------+
    1 row in set (0.00 sec)

此外，还有非符号类型的运算符：

空运算符：

空运算符（IS NULL或者ISNULL）判断一个值是否为NULL，如果为NULL则返回1，否则返回0。

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

# 查询commission_pct等于NULL。比较如下的四种写法
SELECT employee_id,commission_pct FROM employees WHERE commission_pct IS NULL;
SELECT employee_id,commission_pct FROM employees WHERE commission_pct <=> NULL;
SELECT employee_id,commission_pct FROM employees WHERE ISNULL(commission_pct);
SELECT employee_id,commission_pct FROM employees WHERE commission_pct = NULL;

非空运算符：

非空运算符（IS NOT NULL）判断一个值是否不为NULL，如果不为NULL则返回1，否则返回0。

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

# 查询commission_pct不等于NULL
SELECT employee_id,commission_pct FROM employees WHERE commission_pct IS NOT NULL;
SELECT employee_id,commission_pct FROM employees WHERE NOT commission_pct <=> NULL;
SELECT employee_id,commission_pct FROM employees WHERE NOT ISNULL(commission_pct);

最小值运算符：

语法格式为：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。

最大值运算符：

语法格式为：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。

BETWEEN AND运算符：

BETWEEN运算符使用的格式通常为SELECT D FROM TABLE WHERE C BETWEEN A AND B，此时，当C大于或等于A，并且C小于或等于B时，结果为1，否则结果为0。

mysql> SELECT 1 BETWEEN 0 AND 1, 10 BETWEEN 11 AND 12, 'b' BETWEEN 'a' AND 'c';
+-------------------+----------------------+-------------------------+
| 1 BETWEEN 0 AND 1 | 10 BETWEEN 11 AND 12 | 'b' BETWEEN 'a' AND 'c' |
+-------------------+----------------------+-------------------------+
|                 1 |                    0 |                       1 |
+-------------------+----------------------+-------------------------+
1 row in set (0.00 sec)

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 |
+----------------------+------------+-------------------+--------------------+
1 row in set (0.00 sec)

NOT IN运算符：

NOT IN运算符用于判断给定的值是否不是IN列表中的一个值，如果不是IN列表中的一个值，则返回1，否则返回0。

mysql> SELECT 'a' NOT IN ('a','b','c'), 1 NOT IN (2,3);
+--------------------------+----------------+
| 'a' NOT IN ('a','b','c') | 1 NOT IN (2,3) |
+--------------------------+----------------+
|                        0 |              1 |
+--------------------------+----------------+
1 row in set (0.01 sec)

LIKE运算符：

LIKE运算符主要用来匹配字符串，通常用于模糊匹配，如果满足条件则返回1，否则返回0。如果给定的值或者匹配条件为NULL，则返回结果为NULL。
LIKE运算符通常使用如下通配符：
- “%”：匹配0个或多个字符。
- “_”：只能匹配一个字符。

mysql> SELECT NULL LIKE 'abc', 'abc' LIKE NULL;
+-----------------+-----------------+
| NULL LIKE 'abc' | 'abc' LIKE NULL |
+-----------------+-----------------+
|            NULL |            NULL |
+-----------------+-----------------+
1 row in set (0.00 sec)

mysql> SELECT first_name FROM employees WHERE first_name LIKE 'S%';
+------------+
| first_name |
+------------+
| Steven     |
| Shelli     |
| Sigal      |
| Shanta     |
| Steven     |
| Stephen    |
| Sarath     |
| Sundar     |
| Sundita    |
| Sarah      |
| Samuel     |
| Susan      |
| Shelley    |
+------------+
13 rows in set (0.00 sec)

mysql> SELECT last_name FROM employees WHERE last_name LIKE '_o%';
+------------+
| last_name  |
+------------+
| Kochhar    |
| Lorentz    |
| Popp       |
| Tobias     |
| Colmenares |
| Vollman    |
| Mourgos    |
| Rogers     |
| Doran      |
| Fox        |
| Johnson    |
| Jones      |
+------------+
12 rows in set (0.00 sec)

ESCAPE：

回避特殊符号的：使用转义符。例如：将[%]转为[$%]、[]转为[$]，然后再加上[ESCAPE ‘$’]即可。

mysql> SELECT job_id FROM jobs WHERE job_id LIKE 'IT$_%' escape '$';
+---------+
| job_id  |
+---------+
| IT_PROG |
+---------+
1 row in set (0.00 sec)

如果使用\表示转义，要省略ESCAPE。如果不是\，则要加上ESCAPE。

mysql> SELECT job_id FROM jobs WHERE job_id LIKE 'IT\_%';
+---------+
| job_id  |
+---------+
| IT_PROG |
+---------+
1 row in set (0.00 sec)

REGEXP运算符：

REGEXP运算符用来匹配字符串，语法格式为： expr REGEXP匹配条件。如果expr满足匹配条件，返回1；如果不满足，则返回0。若expr或匹配条件任意一个为NULL，则结果为NULL。

REGEXP运算符在进行匹配时，常用的有下面几种通配符：

‘^’匹配以该字符后面的字符开头的字符串。

‘$’匹配以该字符前面的字符结尾的字符串。

‘.’匹配任何一个单字符。

“[…]”匹配在方括号内的任何字符。例如，“[abc]”匹配“a”或“b”或“c”。为了命名字符的范围，使用一个‘-’。“[a-z]”匹配任何字母，而“[0-9]”匹配任何数字。

‘*’匹配零个或多个在它前面的字符。例如，“x*”匹配任何数量的‘x’字符，“[0-9]*”匹配任何数量的数字，而“*”匹配任何数量的任何字符。

mysql> SELECT 'shkstart' REGEXP '^s', 'shkstart' REGEXP 't$', 'shkstart' REGEXP 'hk';
+------------------------+------------------------+------------------------+
| 'shkstart' REGEXP '^s' | 'shkstart' REGEXP 't$' | 'shkstart' REGEXP 'hk' |
+------------------------+------------------------+------------------------+
|                      1 |                      1 |                      1 |
+------------------------+------------------------+------------------------+
1 row in set (0.01 sec)

4.3 逻辑运算符

逻辑运算符主要用来判断表达式的真假，在MySQL中，逻辑运算符的返回结果为1、0或者NULL。

MySQL中支持4种逻辑运算符如下：

逻辑非运算符：

逻辑非（NOT或!）运算符表示当给定的值为0时返回1；当给定的值为非0值时返回0；当给定的值为NULL时，返回NULL。

mysql> SELECT NOT 1, NOT 0, NOT(1+1), NOT !1, NOT NULL;
+-------+-------+----------+--------+----------+
| NOT 1 | NOT 0 | NOT(1+1) | NOT !1 | NOT NULL |
+-------+-------+----------+--------+----------+
|     0 |     1 |        0 |      1 |     NULL |
+-------+-------+----------+--------+----------+
1 row in set, 1 warning (0.00 sec)

mysql> SELECT last_name, job_id FROM employees WHERE job_id NOT IN ('IT_PROG', 'ST_CLERK', 'SA_REP');
+------------+------------+
| last_name  | job_id     |
+------------+------------+
| King       | AD_PRES    |
| Kochhar    | AD_VP      |
| De Haan    | AD_VP      |

逻辑与运算符：

逻辑与（AND或&&）运算符是当给定的所有值均为非0值，并且都不为NULL时，返回 1；当给定的一个值或者多个值为0时则返回0；否则返回NULL。

mysql> SELECT 1 AND -1, 0 AND 1, 0 AND NULL, 1 AND NULL;
+----------+---------+------------+------------+
| 1 AND -1 | 0 AND 1 | 0 AND NULL | 1 AND NULL |
+----------+---------+------------+------------+
|        1 |       0 |          0 |       NULL |
+----------+---------+------------+------------+
1 row in set (0.00 sec)

mysql> SELECT employee_id, last_name, job_id, salary FROM employees WHERE salary >=10000 AND job_id LIKE '%MAN%';
+-------------+-----------+--------+----------+
| employee_id | last_name | job_id | salary   |
+-------------+-----------+--------+----------+
|         114 | Raphaely  | PU_MAN | 11000.00 |
|         145 | Russell   | SA_MAN | 14000.00 |
|         146 | Partners  | SA_MAN | 13500.00 |
|         147 | Errazuriz | SA_MAN | 12000.00 |
|         148 | Cambrault | SA_MAN | 11000.00 |
|         149 | Zlotkey   | SA_MAN | 10500.00 |
|         201 | Hartstein | MK_MAN | 13000.00 |
+-------------+-----------+--------+----------+
7 rows in set (0.00 sec)

逻辑或运算符：

逻辑或（OR或||）运算符是当给定的值都不为NULL，并且任何一个值为非0值时，则返回1，否则返回0；当一个值为NULL，并且另一个值为非0值时，返回1，否则返回NULL；当两个值都为 NULL时，返回NULL。

mysql> SELECT 1 OR -1, 1 OR 0, 1 OR NULL, 0 || NULL, NULL || NULL;
+---------+--------+-----------+-----------+--------------+
| 1 OR -1 | 1 OR 0 | 1 OR NULL | 0 || NULL | NULL || NULL |
+---------+--------+-----------+-----------+--------------+
|       1 |      1 |         1 |      NULL |         NULL |
+---------+--------+-----------+-----------+--------------+
1 row in set, 2 warnings (0.00 sec)

# 查询基本薪资不在9000-12000之间的员工编号和基本薪资
SELECT employee_id,salary FROM employees WHERE NOT (salary >= 9000 AND salary <= 12000);
SELECT employee_id,salary FROM employees WHERE salary < 9000 OR salary > 12000;
SELECT employee_id,salary FROM employees WHERE salary NOT BETWEEN 9000 AND 12000;

OR可以和AND一起使用，但是在使用时要注意两者的优先级，由于AND的优先级高于OR，因此先对AND两边的操作数进行操作，再与OR中的操作数结合。

逻辑异或运算符：

逻辑异或（XOR）运算符是当给定的值中任意一个值为NULL时，则返回NULL；如果两个非NULL的值都是0或者都不等于0时，则返回0；如果一个值为0，另一个值不为0时，则返回1。

mysql> SELECT 1 XOR -1, 1 XOR 0, 0 XOR 0, 1 XOR NULL, 1 XOR 1 XOR 1, 0 XOR 0 XOR 0;
+----------+---------+---------+------------+---------------+---------------+
| 1 XOR -1 | 1 XOR 0 | 0 XOR 0 | 1 XOR NULL | 1 XOR 1 XOR 1 | 0 XOR 0 XOR 0 |
+----------+---------+---------+------------+---------------+---------------+
|        0 |       1 |       0 |       NULL |             1 |             0 |
+----------+---------+---------+------------+---------------+---------------+
1 row in set (0.00 sec)

mysql> select last_name,department_id,salary from employees where department_id in (10,20) XOR salary > 8000;
+------------+---------------+----------+
| last_name  | department_id | salary   |
+------------+---------------+----------+
| King       |            90 | 24000.00 |
| Kochhar    |            90 | 17000.00 |
| De Haan    |            90 | 17000.00 |
| Hunold     |            60 |  9000.00 |

4.4 位运算符

位运算符是在二进制数上进行计算的运算符。位运算符会先将操作数变成二进制数，然后进行位运算，最后将计算结果从二进制变回十进制数。

MySQL支持的位运算符如下：

按位与运算符：

按位与（&）运算符将给定值对应的二进制数逐位进行逻辑与运算。当给定值对应的二进制位的数值都为1时，则该位返回1，否则返回0。

# 1的二进制数为0001，10的二进制数为1010，所以1 & 10的结果为0000，对应的十进制数为0。20的二进制数为10100，30的二进制数为11110，所以20 & 30的结果为10100，对应的十进制数为20。
mysql> SELECT 1 & 10, 20 & 30;
+--------+---------+
| 1 & 10 | 20 & 30 |
+--------+---------+
|      0 |      20 |
+--------+---------+
1 row in set (0.00 sec)

按位或运算符：

按位或（|）运算符将给定的值对应的二进制数逐位进行逻辑或运算。当给定值对应的二进制位的数值有一个或两个为1时，则该位返回1，否则返回0。

# 1的二进制数为0001，10的二进制数为1010，所以1 | 10的结果为1011，对应的十进制数为11。20的二进制数为10100，30的二进制数为11110，所以20 | 30的结果为11110，对应的十进制数为30。
mysql> SELECT 1 | 10, 20 | 30;
+--------+---------+
| 1 | 10 | 20 | 30 |
+--------+---------+
|     11 |      30 |
+--------+---------+
1 row in set (0.00 sec)

按位异或运算符：

按位异或（^）运算符将给定的值对应的二进制数逐位进行逻辑异或运算。当给定值对应的二进制位的数值不同时，则该位返回1，否则返回0。

# 1的二进制数为0001，10的二进制数为1010，所以1 ^ 10的结果为1011，对应的十进制数为11。20的二进制数为10100，30的二进制数为11110，所以20 ^ 30的结果为01010，对应的十进制数为10。
mysql> SELECT 1 ^ 10, 20 ^ 30;
+--------+---------+
| 1 ^ 10 | 20 ^ 30 |
+--------+---------+
|     11 |      10 |
+--------+---------+
1 row in set (0.00 sec)

mysql> SELECT 12 & 5, 12 | 5,12 ^ 5 FROM DUAL;
+--------+--------+--------+
| 12 & 5 | 12 | 5 | 12 ^ 5 |
+--------+--------+--------+
|      4 |     13 |      9 |
+--------+--------+--------+
1 row in set (0.00 sec)

按位取反运算符：

按位取反（~）运算符将给定的值的二进制数逐位进行取反操作，即将1变为0，将0变为1。

# 由于按位取反（~）运算符的优先级高于按位与（&）运算符的优先级，所以10 & ~1，首先，对数字1进行按位取反操作，结果除了最低位为0，其他位都为1，然后与10进行按位与操作，结果为10。
mysql> SELECT 10 & ~1;
+---------+
| 10 & ~1 |
+---------+
|      10 |
+---------+
1 row in set (0.00 sec)

按位右移运算符：

按位右移（>>）运算符将给定的值的二进制数的所有位右移指定的位数。右移指定的位数后，右边低位的数值被移出并丢弃，左边高位空出的位置用0补齐。

# 1的二进制数为0000 0001，右移2位为0000 0000，对应的十进制数为0。4的二进制数为0000 0100，右移2位为0000 0001，对应的十进制数为1。
mysql> SELECT 1 >> 2, 4 >> 2;
+--------+--------+
| 1 >> 2 | 4 >> 2 |
+--------+--------+
|      0 |      1 |
+--------+--------+
1 row in set (0.00 sec)

按位左移运算符：

按位左移（<<）运算符将给定的值的二进制数的所有位左移指定的位数。左移指定的位数后，左边高位的数值被移出并丢弃，右边低位空出的位置用0补齐。

# 1的二进制数为0000 0001，左移两位为0000 0100，对应的十进制数为4。4的二进制数为0000 0100，左移两位为0001 0000，对应的十进制数为16。
mysql> SELECT 1 << 2, 4 << 2;
+--------+--------+
| 1 << 2 | 4 << 2 |
+--------+--------+
|      4 |     16 |
+--------+--------+
1 row in set (0.00 sec)

4.5 运算符的优先级

数字编号越大，优先级越高，优先级高的运算符先进行计算。可以看到，赋值运算符的优先级最低，使用”()”括起来的表达式的优先级最高。

5、排序与分页

5.1 排序数据

5.1.1 排序规则

使用ORDER BY子句排序（不写默认是升序）。
- ASC（ascend）:升序。
- DESC（descend）:降序。
ORDER BY子句在SELECT语句的结尾。
列的别名只能在ORDER BY中使用，不能在WHERE中使用。

5.1.2 单列排序

mysql> SELECT last_name, job_id, department_id, hire_date FROM employees ORDER BY hire_date;
+-------------+------------+---------------+------------+
| last_name   | job_id     | department_id | hire_date  |
+-------------+------------+---------------+------------+
| King        | AD_PRES    |            90 | 1987-06-17 |
| Whalen      | AD_ASST    |            10 | 1987-09-17 |
| Kochhar     | AD_VP      |            90 | 1989-09-21 |
| Hunold      | IT_PROG    |            60 | 1990-01-03 |

mysql> SELECT last_name, job_id, department_id, hire_date FROM employees ORDER BY hire_date DESC;
+-------------+------------+---------------+------------+
| last_name   | job_id     | department_id | hire_date  |
+-------------+------------+---------------+------------+
| Banda       | SA_REP     |            80 | 2000-04-21 |
| Kumar       | SA_REP     |            80 | 2000-04-21 |
| Ande        | SA_REP     |            80 | 2000-03-24 |
| Markle      | ST_CLERK   |            50 | 2000-03-08 |

mysql> SELECT employee_id, last_name, salary*12 annsal FROM employees ORDER BY annsal;
+-------------+-------------+-----------+
| employee_id | last_name   | annsal    |
+-------------+-------------+-----------+
|         132 | Olson       |  25200.00 |
|         128 | Markle      |  26400.00 |
|         136 | Philtanker  |  26400.00 |
|         127 | Landry      |  28800.00 |

5.1.3 多列排序

可以使用不在SELECT列表中的列排序。

在对多列进行排序的时候，首先排序的第一列必须有相同的列值，才会对第二列进行排序。如果第一列数据中所有值都是唯一的，将不再对第二列进行排序。

mysql> SELECT last_name, department_id, salary FROM employees ORDER BY department_id, salary DESC;
+-------------+---------------+----------+
| last_name   | department_id | salary   |
+-------------+---------------+----------+
| Grant       |          NULL |  7000.00 |
| Whalen      |            10 |  4400.00 |
| Hartstein   |            20 | 13000.00 |
| Fay         |            20 |  6000.00 |

5.2 分页

所谓分页显示，就是将数据库中的结果集，一段一段显示出来需要的条件。
MySQL中使用LIMIT实现分页。

格式：

1
2

# 第一个“位置偏移量”参数指示MySQL从哪一行开始显示，是一个可选参数，如果不指定“位置偏移量”，将会从表中的第一条记录开始（第一条记录的位置偏移量是0，第二条记录的位置偏移量是1，以此类推）；第二个参数“行数”指示返回的记录条数。
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）*每页条数，每页条数。
1
SELECT * FROM table LIMIT (PageNo - 1)*PageSize,PageSize;
- 注意：LIMIT子句必须放在整个SELECT语句的最后！
- 使用LIMIT的好处：
  - 约束返回结果的数量可以减少数据表的网络传输量，也可以提升查询效率。如果我们知道返回结果只有1条，就可以使用LIMIT 1，告诉SELECT语句只需要返回一条记录即可。这样的好处就是SELECT不需要扫描完整的表，只需要检索到一条符合条件的记录即可返回。
在不同的DBMS中使用的关键字可能不同。在MySQL、PostgreSQL、MariaDB和SQLite中使用LIMIT关键字，而且需要放到SELECT语句的最后面。
- 如果是SQL Server和Access，需要使用TOP关键字，比如：
  1
  SELECT TOP 5 name, hp_max FROM heros ORDER BY hp_max DESC
- 如果是DB2，使用FETCH FIRST 5 ROWS ONLY这样的关键字：
  1
  SELECT name, hp_max FROM heros ORDER BY hp_max DESC FETCH FIRST 5 ROWS ONLY
- 如果是Oracle，你需要基于ROWNUM来统计行数：
  1
  SELECT rownum,last_name,salary FROM employees WHERE rownum < 5 ORDER BY salary DESC;
- 需要说明的是，这条语句是先取出来前5条数据行，然后再按照hp_max从高到低的顺序进行排序。但这样产生的结果和上述方法的并不一样。我会在后面讲到子查询，你可以使用如下方式得到与上述方法一致的结果：
  1
  2
  3
  4
  5
  6
  SELECT rownum, last_name,salary
  FROM (
  SELECT last_name,salary
  FROM employees
  ORDER BY salary DESC)
  WHERE rownum < 10;

6、多表查询

多表查询，也称为关联查询，指两个或更多个表一起完成查询操作。
前提条件：这些一起查询的表之间是有关系的（一对一、一对多），它们之间一定是有关联字段，这个关联字段可能建立了外键，也可能没有建立外键。比如：员工表和部门表，这两个表依靠“部门编号”进行关联。

6.1 笛卡尔积（或交叉连接）

笛卡尔乘积是一个数学运算。假设我有两个集合 X 和 Y，那么 X 和 Y 的笛卡尔积就是 X 和 Y 的所有可能组合，也就是第一个对象来自于 X，第二个对象来自于 Y 的所有可能。组合的个数即为两个集合中元素个数的乘积数。

SQL92中，笛卡尔积也称为交叉连接，英文是CROSS JOIN。在SQL99中也是使用CROSS JOIN表示交叉连接。它的作用就是可以把任意表进行连接，即使这两张表不相关。在MySQL中如下情况会出现笛卡尔积：

# 查询员工姓名和所在部门名称
SELECT last_name,department_name FROM employees,departments;
SELECT last_name,department_name FROM employees CROSS JOIN departments;
SELECT last_name,department_name FROM employees INNER JOIN departments;
SELECT last_name,department_name FROM employees JOIN departments;

笛卡尔积的错误会在下面条件下产生：
- 省略多个表的连接条件（或关联条件）。
- 连接条件（或关联条件）无效。
- 所有表中的所有行互相连接。

为了避免笛卡尔积，可以在WHERE加入有效的连接条件。

SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column1 = table2.column2; # 连接条件

# 案例：查询员工的姓名及其部门名称
SELECT last_name, department_name
FROM employees, departments
WHERE employees.department_id = departments.department_id;

# 如果查询语句中出现了多个表中都存在的字段，则必须指明此字段所在的表。
SELECT employees.employee_id,departments.department_name,employees.department_id
FROM employees,departments
WHERE employees.`department_id` = departments.department_id;
# 建议：从sql优化的角度，建议多表查询时，每个字段前都指明其所在的表。

# 可以给表起别名，在SELECT和WHERE中使用表的别名。
SELECT emp.employee_id,dept.department_name,emp.department_id
FROM employees emp,departments dept
WHERE emp.`department_id` = dept.department_id;

# 如果给表起了别名，一旦在SELECT或WHERE中使用表名的话，则必须使用表的别名，而不能再使用表的原名。
# 如下的操作是错误的：
SELECT emp.employee_id,departments.department_name,emp.department_id
FROM employees emp,departments dept
WHERE emp.`department_id` = departments.department_id;

# 如果有n个表实现多表的查询，则需要至少n-1个连接条件
# 练习：查询员工的employee_id,last_name,department_name,city
SELECT e.employee_id,e.last_name,d.department_name,l.city,e.department_id,l.location_id
FROM employees e,departments d,locations l
WHERE e.`department_id` = d.`department_id`
AND d.`location_id` = l.`location_id`;

6.2 多表查询分类

6.2.1 等值连接 vs 非等值连接

等值连接

mysql> SELECT employees.employee_id, employees.last_name, employees.department_id, departments.department_id, departments.location_id FROM employees, departments WHERE employees.department_id = departments.department_id;
+-------------+-------------+---------------+---------------+-------------+
| employee_id | last_name   | department_id | department_id | location_id |
+-------------+-------------+---------------+---------------+-------------+
|         103 | Hunold      |            60 |            60 |        1400 |
|         104 | Ernst       |            60 |            60 |        1400 |
|         105 | Austin      |            60 |            60 |        1400 |
|         106 | Pataballa   |            60 |            60 |        1400 |
|         107 | Lorentz     |            60 |            60 |        1400 |
|         120 | Weiss       |            50 |            50 |        1500 |
|         121 | Fripp       |            50 |            50 |        1500 |
|         122 | Kaufling    |            50 |            50 |        1500 |
|         123 | Vollman     |            50 |            50 |        1500 |

非等值连接

# 查询出公司员工的 last_name,department_name, city
mysql> SELECT e.last_name, e.salary, j.grade_level FROM employees e, job_grades j WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal;
# WHERE e.`salary` >= j.`lowest_sal` AND e.`salary` <= j.`highest_sal`;
+-------------+----------+-------------+
| last_name   | salary   | grade_level |
+-------------+----------+-------------+
| King        | 24000.00 | E           |
| Kochhar     | 17000.00 | E           |
| De Haan     | 17000.00 | E           |
| Hunold      |  9000.00 | C           |
| Ernst       |  6000.00 | C           |
| Austin      |  4800.00 | B           |
| Pataballa   |  4800.00 | B           |

6.2.2 自连接 vs 非自连接

自连接

当table1和table2本质上是同一张表，只是用取别名的方式虚拟成两张表以代表不同的意义。然后两个表再进行内连接，外连接等查询。

# 查询employees表，返回“Xxx works for Xxx”
mysql> SELECT CONCAT(worker.last_name ,' works for ' , manager.last_name) FROM employees worker, employees manager WHERE worker.manager_id = manager.employee_id;
+-------------------------------------------------------------+
| CONCAT(worker.last_name ,' works for ' , manager.last_name) |
+-------------------------------------------------------------+
| Kochhar works for King                                      |
| De Haan works for King                                      |
| Hunold works for De Haan                                    |
| Ernst works for Hunold                                      |
| Austin works for Hunold                                     |
| Pataballa works for Hunold                                  |

# 查询员工id,员工姓名及其管理者的id和姓名
mysql> SELECT emp.employee_id,emp.last_name,mgr.employee_id,mgr.last_name FROM employees emp ,employees mgr WHERE emp.`manager_id` = mgr.`employee_id`;
+-------------+-------------+-------------+-----------+
| employee_id | last_name   | employee_id | last_name |
+-------------+-------------+-------------+-----------+
|         101 | Kochhar     |         100 | King      |
|         102 | De Haan     |         100 | King      |
|         103 | Hunold      |         102 | De Haan   |
|         104 | Ernst       |         103 | Hunold    |
|         105 | Austin      |         103 | Hunold    |

非自连接

不是连接同一张表则为非自连接。

6.2.3 内连接 vs 外连接

内连接

合并具有同一列的两个以上的表的行，结果集中不包含一个表与另一个表不匹配的行。

# SQL92写法创建内连接
mysql> SELECT employee_id,department_name FROM employees e,departments d WHERE e.`department_id` = d.department_id;
+-------------+------------------+
| employee_id | department_name  |
+-------------+------------------+
|         200 | Administration   |
|         201 | Marketing        |
|         202 | Marketing        |
|         114 | Purchasing       |

外连接

两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行，这种连接称为左（或右）外连接。没有匹配的行时，结果表中相应的列为空(NULL)。外连接的分类如下：

左外连接：两个表在连接过程中除了返回满足连接条件的行以外还返回左表中不满足条件的行，这种连接称为左外连接。连接条件中左边的表也称为主表，右边的表称为从表。
右外连接：两个表在连接过程中除了返回满足连接条件的行以外还返回右表中不满足条件的行，这种连接称为右外连接。连接条件中右边的表也称为主表，左边的表称为从表。
全外连接(满外连接)： 全外连接就是左外连接和右外连接的结合。

# SQL92：使用(+)创建连接
# 在 SQL92 中采用（+）代表从表所在的位置。即左或右外连接中，(+) 表示哪个是从表。
# Oracle 对 SQL92 支持较好，而 MySQL 则不支持 SQL92 的外连接。
# 而且在 SQL92 中，只有左外连接和右外连接，没有满（或全）外连接。

# 左外连接
SELECT last_name,department_name
FROM employees ,departments
WHERE employees.department_id = departments.department_id(+);

# 右外连接
SELECT last_name,department_name
FROM employees ,departments
WHERE employees.department_id(+) = departments.department_id;

6.3 SQL99语法实现多表查询

使用JOIN…ON子句创建连接的语法结构：
1
2
3
4
SELECT table1.column, table2.column,table3.column
FROM table1
JOIN table2 ON table1 和 table2 的连接条件
JOIN table3 ON table2 和 table3 的连接条件
- SQL99采用的这种嵌套结构非常清爽、层次性更强、可读性更强，即使再多的表进行连接也都清晰可见。如果你采用 SQL92，可读性就会大打折扣。
语法说明如下：
- 可以使用ON子句指定额外的连接条件。
- 这个连接条件是与其它条件分开的。
- ON子句使语句具有更高的易读性。
- 关键字JOIN、INNER JOIN、CROSS JOIN的含义是一样的，都表示内连接。

6.3.1 内连接(INNER JOIN)的实现

语法：

SELECT 字段列表
FROM A表 INNER JOIN B表
ON 关联条件
WHERE 等其他子句;

例子：

# 内连接(INNER JOIN)实现
mysql> SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id FROM employees e JOIN departments d ON (e.department_id = d.department_id);
+-------------+-------------+---------------+---------------+-------------+
| employee_id | last_name   | department_id | department_id | location_id |
+-------------+-------------+---------------+---------------+-------------+
|         103 | Hunold      |            60 |            60 |        1400 |
|         104 | Ernst       |            60 |            60 |        1400 |
|         105 | Austin      |            60 |            60 |        1400 |
|         106 | Pataballa   |            60 |            60 |        1400 |
|         107 | Lorentz     |            60 |            60 |        1400 |
|         120 | Weiss       |            50 |            50 |        1500 |

mysql> SELECT employee_id, city, department_name FROM employees e JOIN departments d ON d.department_id = e.department_id JOIN locations l ON d.location_id = l.location_id;
+-------------+---------------------+------------------+
| employee_id | city                | department_name  |
+-------------+---------------------+------------------+
|         200 | Seattle             | Administration   |
|         201 | Toronto             | Marketing        |
|         202 | Toronto             | Marketing        |
|         114 | Seattle             | Purchasing       |
|         115 | Seattle             | Purchasing       |
|         116 | Seattle             | Purchasing       |

6.3.2 外连接(OUTER JOIN)的实现

6.3.2.1 左外连接(LEFT OUTER JOIN)

语法：

# 实现查询结果是A
SELECT 字段列表
FROM A表 LEFT JOIN B表
ON 关联条件
WHERE 等其他子句;

例子：

mysql> SELECT e.last_name, e.department_id, d.department_name FROM employees e LEFT OUTER JOIN departments d ON (e.department_id = d.department_id);
+-------------+---------------+------------------+
| last_name   | department_id | department_name  |
+-------------+---------------+------------------+
| King        |            90 | Executive        |
| Kochhar     |            90 | Executive        |
| De Haan     |            90 | Executive        |
| Hunold      |            60 | IT               |
| Ernst       |            60 | IT               |
| Austin      |            60 | IT               |
| Grant       |          NULL | NULL             |

6.3.2.2 右外连接(RIGHT OUTER JOIN)

语法：

# 实现查询结果是B
SELECT 字段列表
FROM A表 RIGHT JOIN B表
ON 关联条件
WHERE 等其他子句;

例子：

mysql> SELECT e.last_name, e.department_id, d.department_name FROM employees e RIGHT OUTER JOIN departments d ON (e.department_id = d.department_id);
+-------------+---------------+----------------------+
| last_name   | department_id | department_name      |
+-------------+---------------+----------------------+
| Whalen      |            10 | Administration       |
| Hartstein   |            20 | Marketing            |
| Fay         |            20 | Marketing            |
| Raphaely    |            30 | Purchasing           |
| Khoo        |            30 | Purchasing           |
| NULL        |          NULL | Treasury             |
| NULL        |          NULL | Corporate Tax        |
| NULL        |          NULL | Control And Credit   |
| NULL        |          NULL | Shareholder Services |
| NULL        |          NULL | Benefits             |
| NULL        |          NULL | Manufacturing        |

需要注意的是，LEFT JOIN和RIGHT JOIN只存在于SQL99及以后的标准中，在SQL92中不存在，只能用 (+) 表示。

6.3.2.3 满外连接(FULL OUTER JOIN)

满外连接的结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据。
SQL99是支持满外连接的。使用FULL JOIN或FULL OUTER JOIN来实现。需要注意的是，MySQL不支持FULL JOIN，但是可以用LEFT JOIN UNION RIGHT join代替。

6.4 UNION的使用

利用UNION关键字，可以给出多条SELECT语句，并将它们的结果组合成单个结果集。合并时，两个表对应的列数和数据类型必须相同，并且相互对应。各个SELECT语句之间使用UNION或UNION ALL关键字分隔。
语法格式：
1
2
3
SELECT column,... FROM table1
UNION [ALL]
SELECT column,... FROM table2
- UNION操作符：返回两个查询的结果集的并集，去除重复记录。
- UNION ALL操作符：返回两个查询的结果集的并集。对于两个结果集的重复部分，不去重。

注意：执行UNION ALL语句时所需要的资源比UNION语句少。如果明确知道合并数据后的结果数据不存在重复数据，或者不需要去除重复的数据，则尽量使用UNION ALL语句，以提高数据查询的效率。

# 查询部门编号>90或邮箱包含a的员工信息

# 方式一
mysql> SELECT * FROM employees WHERE email LIKE '%a%' OR department_id>90;
+-------------+-------------+------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------+
| employee_id | first_name  | last_name  | email    | phone_number       | hire_date  | job_id     | salary   | commission_pct | manager_id | department_id |
+-------------+-------------+------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------+
|         101 | Neena       | Kochhar    | NKOCHHAR | 515.123.4568       | 1989-09-21 | AD_VP      | 17000.00 |           NULL |        100 |            90 |
|         102 | Lex         | De Haan    | LDEHAAN  | 515.123.4569       | 1993-01-13 | AD_VP      | 17000.00 |           NULL |        100 |            90 |
|         103 | Alexander   | Hunold     | AHUNOLD  | 590.423.4567       | 1990-01-03 | IT_PROG    |  9000.00 |           NULL |        102 |            60 |
|         105 | David       | Austin     | DAUSTIN  | 590.423.4569       | 1997-06-25 | IT_PROG    |  4800.00 |           NULL |        103 |            60 |
# 方式二
mysql> SELECT * FROM employees WHERE email LIKE '%a%' UNION SELECT * FROM employees WHERE department_id>90;
+-------------+-------------+------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------+
| employee_id | first_name  | last_name  | email    | phone_number       | hire_date  | job_id     | salary   | commission_pct | manager_id | department_id |
+-------------+-------------+------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------+
|         101 | Neena       | Kochhar    | NKOCHHAR | 515.123.4568       | 1989-09-21 | AD_VP      | 17000.00 |           NULL |        100 |            90 |
|         102 | Lex         | De Haan    | LDEHAAN  | 515.123.4569       | 1993-01-13 | AD_VP      | 17000.00 |           NULL |        100 |            90 |
|         103 | Alexander   | Hunold     | AHUNOLD  | 590.423.4567       | 1990-01-03 | IT_PROG    |  9000.00 |           NULL |        102 |            60 |
|         105 | David       | Austin     | DAUSTIN  | 590.423.4569       | 1997-06-25 | IT_PROG    |  4800.00 |           NULL |        103 |            60 |

6.5 7种SQL JOINS的实现

# 中图：内连接 A∩B
SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`;

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

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

# 左中图：A - A∩B
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL

# 右中图：B-A∩B
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL

# 左下图：满外连接
# 左中图 + 右上图 A∪B
SELECT employee_id,last_name,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,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;

# 右下图
# 左中图 + 右中图 A ∪B- A∩B 或者 (A - A∩B) ∪ （B - A∩B）
SELECT employee_id,last_name,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,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL

6.6 SQL99语法新特性

6.6.1 自然连接

SQL99在SQL92的基础上提供了一些特殊语法，比如NATURAL JOIN用来表示自然连接。我们可以把自然连接理解为SQL92中的等值连接。它会帮你自动查询两张连接表中所有相同的字段，然后进行等值连接。
- 在SQL92标准中：
  1
  2
  3
  4
  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中你可以写成：
  1
  2
  SELECT employee_id,last_name,department_name
  FROM employees e NATURAL JOIN departments d;

6.6.2 USING连接

当我们进行连接的时候，SQL99还支持使用USING指定数据表里的同名字段进行等值连接。但是只能配合JOIN一起使用。比如：
1
2
3
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查询结果是相同的：
1
2
3
SELECT employee_id,last_name,department_name
FROM employees e ,departments d
WHERE e.department_id = d.department_id;

6.7 小结

表连接的约束条件可以有三种方式：WHERE, ON, USING：

WHERE：适用于所有关联查询。
ON：只能和JOIN一起使用，只能写关联条件。虽然关联条件可以并到WHERE中和其他条件一起写，但分开写可读性更好。
USING：只能和JOIN一起使用，而且要求两个关联字段在关联表中名称一致，而且只能表示关联字段值相等。

# 关联条件
# 把关联条件写在where后面
SELECT last_name,department_name
FROM employees,departments
WHERE employees.department_id = departments.department_id;
# 把关联条件写在on后面，只能和JOIN一起使用
SELECT last_name,department_name
FROM employees INNER JOIN departments
ON employees.department_id = departments.department_id;
SELECT last_name,department_name
FROM employees CROSS JOIN departments
ON employees.department_id = departments.department_id;
SELECT last_name,department_name
FROM employees JOIN departments
ON employees.department_id = departments.department_id;
# 把关联字段写在using()中，只能和JOIN一起使用
# 而且两个表中的关联字段必须名称相同，而且只能表示=
# 查询员工姓名与基本工资
SELECT last_name,job_title
FROM employees INNER JOIN jobs USING(job_id);
# n张表关联，需要n-1个关联条件
# 查询员工姓名，基本工资，部门名称
SELECT last_name,job_title,department_name FROM employees,departments,jobs
WHERE employees.department_id = departments.department_id
AND employees.job_id = jobs.job_id;
SELECT last_name,job_title,department_name
FROM employees INNER JOIN departments INNER JOIN jobs
ON employees.department_id = departments.department_id
AND employees.job_id = jobs.job_id;

我们要控制连接表的数量。多表连接就相当于嵌套for循环一样，非常消耗资源，会让SQL查询性能下降得很严重，因此不要连接不必要的表。在许多DBMS中，也都会有最大连接表的限制。

【强制】超过三个表禁止 join。需要 join 的字段，数据类型保持绝对一致；多表关联查询时，保证被关联的字段需要有索引。说明：即使双表 join 也要注意表索引、SQL 性能。来源：阿里巴巴《Java开发手册》

7、函数

7.1 内置函数及分类

MySQL提供了丰富的内置函数，这些函数使得数据的维护与管理更加方便，能够更好地提供数据的分析与统计功能，在一定程度上提高了开发人员进行数据分析与统计的效率。
MySQL提供的内置函数从实现的功能角度可以分为数值函数、字符串函数、日期和时间函数、流程控制函数、加密与解密函数、获取MySQL信息函数、聚合函数等。这里，我将这些丰富的内置函数再分为两类：单行函数、聚合函数（或分组函数、多行函数）。

7.2 单行函数

单行函数：
- 操作数据对象。
- 接受参数返回一个结果。
- 只对一行进行变换。
- 每行返回一个结果。
- 可以嵌套。
- 参数可以是一列或一个值。

7.2.1 数值函数

7.2.1.1 基本函数

1	mysql> SELECT ABS(-123),ABS(32),SIGN(-23),SIGN(43),PI(),CEIL(32.32),CEILING(-43.23),FLOOR(32.32), FLOOR(-43.23),MOD(12,5) FROM DUAL;

1	mysql> SELECT RAND(),RAND(),RAND(10),RAND(10),RAND(-1),RAND(-1) FROM DUAL;

1
2
3

SELECT
ROUND(12.33),ROUND(12.343,2),ROUND(12.324,-1),TRUNCATE(12.66,1),TRUNCATE(12.66,-1)
FROM DUAL;

7.2.1.2 角度与弧度互换函数

1	mysql> SELECT RADIANS(30),RADIANS(60),RADIANS(90),DEGREES(2*PI()),DEGREES(RADIANS(90)) FROM DUAL;

7.2.1.3 三角函数

ATAN2(M,N)函数返回两个参数的反正切值。与ATAN(X)函数相比，ATAN2(M,N)需要两个参数，例如有两个点point(x1,y1)和point(x2,y2)，使用ATAN(X)函数计算反正切值为ATAN((y2-y1)/(x2-x1))，使用ATAN2(M,N)计算反正切值则为ATAN2(y2-y1,x2-x1)。由使用方式可以看出，当x2-x1等于0时，ATAN(X)函数会报错，而 ATAN2(M,N)函数则仍然可以计算。
1
mysql> SELECT SIN(RADIANS(30)),DEGREES(ASIN(1)),TAN(RADIANS(45)),DEGREES(ATAN(1)),DEGREES(ATAN2(1,1) ) FROM DUAL;

7.2.1.4 指数与对数

1	mysql> SELECT POW(2,5),POWER(2,4),EXP(2),LN(10),LOG10(10),LOG2(4) FROM DUAL;

7.2.1.5 进制间的转换

1	mysql> SELECT BIN(10),HEX(10),OCT(10),CONV(10,2,8) FROM DUAL;

7.2.2 字符串函数

MySQL中，字符串的位置是从1开始的。

1	mysql> SELECT FIELD('mm','hello','msm','amma'),FIND_IN_SET('mm','hello,mm,amma') FROM DUAL;

1	mysql> SELECT ASCII('Abcdfsf'),CHAR_LENGTH('hello'),CHAR_LENGTH('我们'), LENGTH('hello'),LENGTH('我们') FROM DUAL;

1	mysql> SELECT CONCAT(emp.last_name,' worked for ',mgr.last_name) "details" FROM employees emp JOIN employees mgr WHERE emp.`manager_id` = mgr.employee_id;

1	mysql> SELECT CONCAT_WS('-','hello','world','hello','beijing') FROM DUAL;

1	mysql> SELECT INSERT('helloworld',2,3,'aaaaa'),REPLACE('hello','lol','mmm') FROM DUAL;

1	mysql> SELECT UPPER('HelLo'),LOWER('HelLo') FROM DUAL;

1	mysql> SELECT LEFT('hello',2),RIGHT('hello',3),RIGHT('hello',13) FROM DUAL;

1
2
3

# LPAD:实现右对齐效果
# RPAD:实现左对齐效果
mysql> SELECT employee_id,last_name,LPAD(salary,10,' ') FROM employees;

1	mysql> SELECT CONCAT('---',LTRIM(' h el lo '),'***'), TRIM('oo' FROM 'ooheollo') FROM DUAL;

1	mysql> SELECT REPEAT('hello',4),LENGTH(SPACE(5)),STRCMP('abc','abe') FROM DUAL;

1	mysql> SELECT SUBSTR('hello',2,2),LOCATE('lll','hello') FROM DUAL;

1	mysql> SELECT ELT(2,'a','b','c','d'),FIELD('mm','gg','jj','mm','dd','mm'), FIND_IN_SET('mm','gg,mm,jj,dd,mm,gg') FROM DUAL;

1	mysql> SELECT employee_id,NULLIF(LENGTH(first_name),LENGTH(last_name)) "compare" FROM employees;

7.2.3 日期和时间函数

7.2.3.1 获取日期、时间

1	mysql> SELECT CURDATE(),CURRENT_DATE(),CURTIME(),NOW(),SYSDATE(), UTC_DATE(),UTC_TIME() FROM DUAL;

1	mysql> SELECT CURDATE(),CURDATE() + 0,CURTIME() + 0,NOW() + 0 FROM DUAL;

7.2.3.2 日期与时间戳的转换

1	mysql> SELECT UNIX_TIMESTAMP(),UNIX_TIMESTAMP('2021-10-01 12:12:32'), FROM_UNIXTIME(1635173853),FROM_UNIXTIME(1633061552) FROM DUAL;

7.2.3.3 获取月份、星期、星期数、天数等函数

1	mysql> SELECT YEAR(CURDATE()),MONTH(CURDATE()),DAY(CURDATE()), HOUR(CURTIME()),MINUTE(NOW()),SECOND(SYSDATE()) FROM DUAL;

1	mysql> SELECT MONTHNAME('2021-10-26'),DAYNAME('2021-10-26'),WEEKDAY('2021-10-26'), QUARTER(CURDATE()),WEEK(CURDATE()),DAYOFYEAR(NOW()), DAYOFMONTH(NOW()),DAYOFWEEK(NOW()) FROM DUAL;

7.2.3.4 日期的操作函数

EXTRACT(type FROM date)函数中type的取值与含义：

1	mysql> SELECT EXTRACT(SECOND FROM NOW()),EXTRACT(DAY FROM NOW()), EXTRACT(HOUR_MINUTE FROM NOW()),EXTRACT(QUARTER FROM '2021-05-12') FROM DUAL;

7.2.3.5 时间和秒钟转换的函数

1	mysql> SELECT TIME_TO_SEC(CURTIME()), SEC_TO_TIME(83355) FROM DUAL;

7.2.3.6 计算日期和时间的函数

第一组

上述函数中type的取值：

1	mysql> SELECT NOW(),DATE_ADD(NOW(),INTERVAL 1 YEAR), DATE_ADD(NOW(),INTERVAL -1 YEAR), DATE_SUB(NOW(),INTERVAL 1 YEAR) FROM DUAL;

mysql> SELECT DATE_ADD(NOW(), INTERVAL 1 DAY) AS col1,DATE_ADD('2021-10-21 23:32:12',INTERVAL 1 SECOND) AS col2,
ADDDATE('2021-10-21 23:32:12',INTERVAL 1 SECOND) AS col3,
DATE_ADD('2021-10-21 23:32:12',INTERVAL '1_1' MINUTE_SECOND) AS col4,
DATE_ADD(NOW(), INTERVAL -1 YEAR) AS col5, #可以是负数
DATE_ADD(NOW(), INTERVAL '1_1' YEAR_MONTH) AS col6 #需要单引号
FROM DUAL;

第二组

mysql> SELECT ADDTIME(NOW(),20),SUBTIME(NOW(),30),SUBTIME(NOW(),'1:1:3'),DATEDIFF(NOW(),'2021-10-01'), TIMEDIFF(NOW(),'2021-10-25 22:10:10'),FROM_DAYS(366),TO_DAYS('0000-12-25'), LAST_DAY(NOW()),MAKEDATE(YEAR(NOW()),32),MAKETIME(10,21,23),PERIOD_ADD(20200101010101,10) FROM DUAL;

7.2.3.7 日期的格式化与解析

上述非GET_FORMAT函数中fmt参数常用的格式符：
GET_FORMAT函数中date_type和format_type参数取值如下：

1 2	# 格式化 mysql> SELECT DATE_FORMAT(CURDATE(),'%Y-%M-%D'), DATE_FORMAT(NOW(),'%Y-%m-%d'),TIME_FORMAT(CURTIME(),'%h:%i:%S'), DATE_FORMAT(NOW(),'%Y-%M-%D %h:%i:%S %W %w %T %r') FROM DUAL;

# 解析：格式化的逆过程
mysql> SELECT STR_TO_DATE('2021-October-25th 11:37:30 Monday 1','%Y-%M-%D %h:%i:%S %W %w') FROM DUAL;

mysql> SELECT GET_FORMAT(DATE,'USA') FROM DUAL;

mysql> SELECT DATE_FORMAT(CURDATE(),GET_FORMAT(DATE,'USA')) FROM DUAL;

7.2.4 流程控制函数

流程处理函数可以根据不同的条件，执行不同的处理流程，可以在SQL语句中实现不同的条件选择。 MySQL中的流程处理函数主要包括IF()、IFNULL()和CASE()函数。

# IF(VALUE,VALUE1,VALUE2)
mysql> SELECT last_name,salary,IF(salary >= 6000,'高工资','低工资') "details" FROM employees;
+-------------+----------+-----------+
| last_name   | salary   | details   |
+-------------+----------+-----------+
| King        | 24000.00 | 高工资    |
| Kochhar     | 17000.00 | 高工资    |
| De Haan     | 17000.00 | 高工资    |

mysql> SELECT last_name,commission_pct,IF(commission_pct IS NOT NULL,commission_pct,0) "details", salary * 12 * (1 + IF(commission_pct IS NOT NULL,commission_pct,0)) "annual_sal" FROM employees;
+-------------+----------------+---------+------------+
| last_name   | commission_pct | details | annual_sal |
+-------------+----------------+---------+------------+
| King        |           NULL |    0.00 |  288000.00 |
| Kochhar     |           NULL |    0.00 |  204000.00 |
| De Haan     |           NULL |    0.00 |  204000.00 |
| Hunold      |           NULL |    0.00 |  108000.00 |

# IFNULL(VALUE1,VALUE2):看做是IF(VALUE,VALUE1,VALUE2)的特殊情况
mysql> SELECT last_name,commission_pct,IFNULL(commission_pct,0) "details" FROM employees;
+-------------+----------------+---------+
| last_name   | commission_pct | details |
+-------------+----------------+---------+
| King        |           NULL |    0.00 |
| Kochhar     |           NULL |    0.00 |
| De Haan     |           NULL |    0.00 |
| Hunold      |           NULL |    0.00 |

# CASE WHEN ... THEN ...WHEN ... THEN ... ELSE ... END
# 类似于java的if ... else if ... else if ... else
mysql> SELECT last_name,salary,CASE WHEN salary >= 15000 THEN '白骨精' WHEN salary >= 10000 THEN '潜力股' WHEN salary >= 8000 THEN '小屌丝' ELSE '草根' END "details",department_id FROM employees;
+-------------+----------+-----------+---------------+
| last_name   | salary   | details   | department_id |
+-------------+----------+-----------+---------------+
| King        | 24000.00 | 白骨精    |            90 |
| Kochhar     | 17000.00 | 白骨精    |            90 |
| De Haan     | 17000.00 | 白骨精    |            90 |
| Hunold      |  9000.00 | 小屌丝    |            60 |
| Ernst       |  6000.00 | 草根      |            60 |
| Austin      |  4800.00 | 草根      |            60 |

mysql> SELECT last_name,salary,CASE WHEN salary >= 15000 THEN '白骨精' WHEN salary >= 10000 THEN '潜力股' WHEN salary >= 8000 THEN '小屌丝' END "details" FROM employees;
+-------------+----------+-----------+
| last_name   | salary   | details   |
+-------------+----------+-----------+
| King        | 24000.00 | 白骨精    |
| Kochhar     | 17000.00 | 白骨精    |
| De Haan     | 17000.00 | 白骨精    |
| Hunold      |  9000.00 | 小屌丝    |
| Ernst       |  6000.00 | NULL      |
| Austin      |  4800.00 | NULL      |

# CASE ... WHEN ... THEN ... WHEN ... THEN ... ELSE ... END
# 类似于java的switch ... case...
# 查询部门号为 10,20, 30 的员工信息, 若部门号为 10, 则打印其工资的 1.1 倍, 20 号部门, 则打印其工资的 1.2 倍, 30 号部门,打印其工资的 1.3 倍数,其他部门,打印其工资的 1.4 倍数
mysql> SELECT employee_id,last_name,department_id,salary,CASE department_id WHEN 10 THEN salary * 1.1 WHEN 20 THEN salary * 1.2 WHEN 30 THEN salary * 1.3 ELSE salary * 1.4 END "details" FROM employees;
+-------------+-------------+---------------+----------+----------+
| employee_id | last_name   | department_id | salary   | details  |
+-------------+-------------+---------------+----------+----------+
|         100 | King        |            90 | 24000.00 | 33600.00 |
|         101 | Kochhar     |            90 | 17000.00 | 23800.00 |
|         102 | De Haan     |            90 | 17000.00 | 23800.00 |

# 查询部门号为 10,20, 30 的员工信息, 若部门号为 10, 则打印其工资的 1.1 倍, 20 号部门, 则打印其工资的 1.2 倍, 30 号部门打印其工资的 1.3 倍数
mysql> SELECT employee_id,last_name,department_id,salary,CASE department_id WHEN 10 THEN salary * 1.1 WHEN 20 THEN salary * 1.2 WHEN 30 THEN salary * 1.3 END "details" FROM employees WHERE department_id IN (10,20,30);
+-------------+------------+---------------+----------+----------+
| employee_id | last_name  | department_id | salary   | details  |
+-------------+------------+---------------+----------+----------+
|         200 | Whalen     |            10 |  4400.00 |  4840.00 |
|         201 | Hartstein  |            20 | 13000.00 | 15600.00 |
|         202 | Fay        |            20 |  6000.00 |  7200.00 |

7.2.5 加密与解密函数

加密与解密函数主要用于对数据库中的数据进行加密和解密处理，以防止数据被他人窃取。这些函数在保证数据库安全时非常有用。
1
2
3
# PASSWORD()在mysql8.0中弃用。
# ENCODE()\DECODE() 在mysql8.0中弃用。
mysql> SELECT MD5('mysql'),SHA('mysql'),MD5(MD5('mysql')) FROM DUAL;

7.2.6 MySQL信息函数

MySQL中内置了一些可以查询MySQL信息的函数，这些函数主要用于帮助数据库开发或运维人员更好地对数据库进行维护工作。

1	mysql> SELECT VERSION(),CONNECTION_ID(),DATABASE(),SCHEMA(), USER(),CURRENT_USER(),CHARSET('尚硅谷'),COLLATION('尚硅谷') FROM DUAL;

7.2.7 其他函数

MySQL中有些函数无法对其进行具体的分类，但是这些函数在MySQL的开发和运维过程中也是不容忽视的。

# 如果n的值小于或者等于0，则只保留整数部分
mysql> SELECT FORMAT(123.125,2),FORMAT(123.125,0),FORMAT(123.125,-2) FROM DUAL;
+-------------------+-------------------+--------------------+
| FORMAT(123.125,2) | FORMAT(123.125,0) | FORMAT(123.125,-2) |
+-------------------+-------------------+--------------------+
| 123.13            | 123               | 123                |
+-------------------+-------------------+--------------------+
1 row in set (0.00 sec)

mysql> SELECT CONV(16, 10, 2), CONV(8888,10,16), CONV(NULL, 10, 2) FROM DUAL;
+-----------------+------------------+-------------------+
| CONV(16, 10, 2) | CONV(8888,10,16) | CONV(NULL, 10, 2) |
+-----------------+------------------+-------------------+
| 10000           | 22B8             | NULL              |
+-----------------+------------------+-------------------+
1 row in set (0.00 sec)

# 以“192.168.1.100”为例，计算方式为192乘以256的3次方，加上168乘以256的2次方，加上1乘以256，再加上100。
mysql> SELECT INET_ATON('192.168.1.100'),INET_NTOA(3232235876) FROM DUAL;
+----------------------------+-----------------------+
| INET_ATON('192.168.1.100') | INET_NTOA(3232235876) |
+----------------------------+-----------------------+
|                 3232235876 | 192.168.1.100         |
+----------------------------+-----------------------+
1 row in set (0.00 sec)

# BENCHMARK()用于测试表达式的执行效率
mysql> SELECT BENCHMARK(100000,MD5('mysql')) FROM DUAL;
+--------------------------------+
| BENCHMARK(100000,MD5('mysql')) |
+--------------------------------+
|                              0 |
+--------------------------------+
1 row in set (0.02 sec)

# CONVERT():可以实现字符集的转换
mysql> SELECT CHARSET('atguigu'),CHARSET(CONVERT('atguigu' USING 'gbk')) FROM DUAL;
+--------------------+-----------------------------------------+
| CHARSET('atguigu') | CHARSET(CONVERT('atguigu' USING 'gbk')) |
+--------------------+-----------------------------------------+
| utf8mb4            | gbk                                     |
+--------------------+-----------------------------------------+
1 row in set (0.00 sec)

7.3 聚合函数

聚合函数作用于一组数据，并对一组数据返回一个值。
聚合函数不能嵌套调用。比如不能出现类似“AVG(SUM(字段名称))”形式的调用。

7.3.1 AVG和SUM函数

可以对数值型数据使用AVG和SUM函数。

# 只适用于数值类型的字段（或变量）
mysql> SELECT AVG(salary),SUM(salary),AVG(salary) * 107 FROM employees;
+-------------+-------------+-------------------+
| AVG(salary) | SUM(salary) | AVG(salary) * 107 |
+-------------+-------------+-------------------+
| 6461.682243 |   691400.00 |     691400.000000 |
+-------------+-------------+-------------------+
1 row in set (0.01 sec)

# 如下的操作没有意义
mysql> SELECT SUM(last_name),AVG(last_name),SUM(hire_date) FROM employees;
+----------------+----------------+----------------+
| SUM(last_name) | AVG(last_name) | SUM(hire_date) |
+----------------+----------------+----------------+
|              0 |              0 |     2136929701 |
+----------------+----------------+----------------+
1 row in set, 214 warnings (0.00 sec)

7.3.2 MIN和MAX函数

可以对任意数据类型的数据使用MIN和MAX函数。

# 适用于数值类型、字符串类型、日期时间类型的字段（或变量）
mysql> SELECT MAX(salary),MIN(salary) FROM employees;
+-------------+-------------+
| MAX(salary) | MIN(salary) |
+-------------+-------------+
|    24000.00 |     2100.00 |
+-------------+-------------+
1 row in set (0.00 sec)

mysql> SELECT MAX(last_name),MIN(last_name),MAX(hire_date),MIN(hire_date) FROM employees;
+----------------+----------------+----------------+----------------+
| MAX(last_name) | MIN(last_name) | MAX(hire_date) | MIN(hire_date) |
+----------------+----------------+----------------+----------------+
| Zlotkey        | Abel           | 2000-04-21     | 1987-06-17     |
+----------------+----------------+----------------+----------------+
1 row in set (0.00 sec)

7.3.3 COUNT函数

COUNT(*)返回表中记录总数，适用于任意数据类型。

# 计算表中有多少条记录，有以下实现方法：
# 方式1：COUNT(*)
# 方式2：COUNT(1)
# 方式3：COUNT(具体字段) : 不一定对！

# 计算指定字段在查询结构中出现的个数时不包含NULL值的
mysql> SELECT COUNT(employee_id),COUNT(salary),COUNT(2 * salary),COUNT(1),COUNT(2),COUNT(*) FROM employees;

1
2

# 公式：AVG = SUM / COUNT永远成立
mysql> SELECT AVG(salary),SUM(salary)/COUNT(salary), AVG(commission_pct),SUM(commission_pct)/COUNT(commission_pct), SUM(commission_pct) / 107 FROM employees;

# 需求：查询公司中平均奖金率
# 错误的！需要把没有奖金的员工也算上。
SELECT AVG(commission_pct)
FROM employees;

# 正确的：
SELECT SUM(commission_pct) / COUNT(IFNULL(commission_pct,0)),
AVG(IFNULL(commission_pct,0))
FROM employees;

如何需要统计表中的记录数，使用COUNT(*)、COUNT(1)、COUNT(具体字段) 哪个效率更高呢？
- 如果使用的是MyISAM存储引擎，则三者效率相同，都是O(1)。
- 如果使用的是InnoDB 存储引擎，则三者效率：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值的行。

7.4 GROUP BY

可以使用GROUP BY子句将表中的数据分成若干组。

# 查询各个部门的平均工资，最高工资
mysql> SELECT department_id,AVG(salary),SUM(salary) FROM employees GROUP BY department_id;
+---------------+--------------+-------------+
| department_id | AVG(salary)  | SUM(salary) |
+---------------+--------------+-------------+
|          NULL |  7000.000000 |     7000.00 |
|            10 |  4400.000000 |     4400.00 |
|            20 |  9500.000000 |    19000.00 |
|            30 |  4150.000000 |    24900.00 |
|            40 |  6500.000000 |     6500.00 |
|            50 |  3475.555556 |   156400.00 |

# 查询各个job_id的平均工资
mysql> SELECT job_id,AVG(salary) FROM employees GROUP BY job_id;
+------------+--------------+
| job_id     | AVG(salary)  |
+------------+--------------+
| AC_ACCOUNT |  8300.000000 |
| AC_MGR     | 12000.000000 |
| AD_ASST    |  4400.000000 |
| AD_PRES    | 24000.000000 |

# 查询各个department_id,job_id的平均工资
# 方式1：
mysql> SELECT department_id,job_id,AVG(salary) FROM employees GROUP BY department_id,job_id;
+---------------+------------+--------------+
| department_id | job_id     | AVG(salary)  |
+---------------+------------+--------------+
|            90 | AD_PRES    | 24000.000000 |
|            90 | AD_VP      | 17000.000000 |
|            60 | IT_PROG    |  5760.000000 |
|           100 | FI_MGR     | 12000.000000 |
|           100 | FI_ACCOUNT |  7920.000000 |

# 方式2：
mysql> SELECT job_id,department_id,AVG(salary) FROM employees GROUP BY job_id,department_id;
+------------+---------------+--------------+
| job_id     | department_id | AVG(salary)  |
+------------+---------------+--------------+
| AD_PRES    |            90 | 24000.000000 |
| AD_VP      |            90 | 17000.000000 |
| IT_PROG    |            60 |  5760.000000 |
| FI_MGR     |           100 | 12000.000000 |
| FI_ACCOUNT |           100 |  7920.000000 |

SELECT中出现的非组函数的字段必须声明在GROUP BY中。反之，GROUP BY中声明的字段可以不出现在SELECT中。

# 错误的！
mysql> SELECT department_id,job_id,AVG(salary) FROM employees GROUP BY department_id;
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'atguigudb.employees.job_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

# GROUP BY 声明在FROM后面、WHERE后面，ORDER BY 前面、LIMIT前面
# 查询各个部门的平均工资，按照平均工资升序排列
mysql> SELECT department_id,AVG(salary) avg_sal FROM employees GROUP BY department_id ORDER BY avg_sal ASC;
+---------------+--------------+
| department_id | avg_sal      |
+---------------+--------------+
|            50 |  3475.555556 |
|            30 |  4150.000000 |
|            10 |  4400.000000 |
|            60 |  5760.000000 |
|            40 |  6500.000000 |

在GROUP BY中可以使用WITH ROLLUP，使用WITH ROLLUP关键字之后，在所有查询出的分组记录之后增加一条记录，即用来得到group by的汇总信息。

mysql> SELECT department_id,AVG(salary) FROM employees GROUP BY department_id WITH ROLLUP;
+---------------+--------------+
| department_id | AVG(salary)  |
+---------------+--------------+
|          NULL |  7000.000000 |
|            10 |  4400.000000 |
|            20 |  9500.000000 |
|            30 |  4150.000000 |
|            40 |  6500.000000 |
|            50 |  3475.555556 |
|            60 |  5760.000000 |
|            70 | 10000.000000 |
|            80 |  8955.882353 |
|            90 | 19333.333333 |
|           100 |  8600.000000 |
|           110 | 10150.000000 |
|          NULL |  6461.682243 |
+---------------+--------------+

mysql> select avg(salary) from employees;
+-------------+
| avg(salary) |
+-------------+
| 6461.682243 |
+-------------+
1 row in set (0.00 sec)

# 当使用ROLLUP时，不能同时使用ORDER BY子句进行结果排序，即ROLLUP和ORDER BY是互相排斥的。
# 以下语句在mysql5版本报错，而mysql8版本不报错
SELECT department_id,AVG(salary) avg_sal
FROM employees
GROUP BY department_id WITH ROLLUP
ORDER BY avg_sal ASC;

7.5 HAVING

过滤分组：HAVING子句。

# 查询各个部门中最高工资比10000高的部门信息
# 错误的写法：
mysql> SELECT department_id,MAX(salary) FROM employees WHERE MAX(salary) > 10000 GROUP BY department_id;
ERROR 1111 (HY000): Invalid use of group function

# 要求1：如果过滤条件中使用了聚合函数，则必须使用HAVING来替换WHERE，否则报错。
# 要求2：HAVING 必须声明在 GROUP BY 的后面。
# 要求3：开发中，我们使用HAVING的前提是SQL中使用了GROUP BY。
mysql> SELECT department_id,MAX(salary) FROM employees GROUP BY department_id HAVING MAX(salary) > 10000;
+---------------+-------------+
| department_id | MAX(salary) |
+---------------+-------------+
|            20 |    13000.00 |
|            30 |    11000.00 |
|            80 |    14000.00 |
|            90 |    24000.00 |
|           100 |    12000.00 |
|           110 |    12000.00 |
+---------------+-------------+
6 rows in set (0.01 sec)

# 当过滤条件中有聚合函数时，则此过滤条件必须声明在HAVING中。当过滤条件中没有聚合函数时，则此过滤条件声明在WHERE中或HAVING中都可以。但是，建议大家声明在WHERE中。

# 查询部门id为10,20,30,40这4个部门中最高工资比10000高的部门信息
# 方式1：推荐，执行效率高于方式2.
mysql> SELECT department_id,MAX(salary) FROM employees WHERE department_id IN (10,20,30,40) GROUP BY department_id HAVING MAX(salary) > 10000;
+---------------+-------------+
| department_id | MAX(salary) |
+---------------+-------------+
|            20 |    13000.00 |
|            30 |    11000.00 |
+---------------+-------------+
2 rows in set (0.00 sec)

# 方式2：
mysql> SELECT department_id,MAX(salary) FROM employees GROUP BY department_id HAVING MAX(salary) > 10000 AND department_id IN (10,20,30,40);
+---------------+-------------+
| department_id | MAX(salary) |
+---------------+-------------+
|            20 |    13000.00 |
|            30 |    11000.00 |
+---------------+-------------+
2 rows in set (0.00 sec)

WHERE与HAVING的对比：
- 区别1：WHERE可以直接使用表中的字段作为筛选条件，但不能使用分组中的计算函数作为筛选条件； HAVING必须要与GROUP BY配合使用，可以把分组计算的函数和分组字段作为筛选条件。
  - 这决定了，在需要对数据进行分组统计的时候，HAVING可以完成WHERE不能完成的任务。这是因为，在查询语法结构中，WHERE在GROUP BY之前，所以无法对分组结果进行筛选。HAVING在 GROUP BY之后，可以使用分组字段和分组中的计算函数，对分组的结果集进行筛选，这个功能是 WHERE无法完成的。另外，WHERE排除的记录不再包括在分组中。所以，从适用范围上来讲，HAVING的适用范围更广。
- 区别2：如果需要通过连接从关联表中获取需要的数据，WHERE是先筛选后连接，而HAVING是先连接后筛选。
  - 这一点，就决定了在关联查询中，WHERE比HAVING更高效。因为WHERE可以先筛选，用一个筛选后的较小数据集和关联表进行连接，这样占用的资源比较少，执行效率也比较高。HAVING则需要先把结果集准备好，也就是用未被筛选的数据集进行关联，然后对这个大的数据集进行筛选，这样占用的资源就比较多，执行效率也较低。所以，如果过滤条件中没有聚合函数：这种情况下，WHERE的执行效率要高于HAVING。
- 开发中的选择：
  - WHERE和HAVING也不是互相排斥的，我们可以在一个查询里面同时使用WHERE和HAVING。包含分组统计函数的条件用 HAVING，普通条件用 WHERE。这样，我们就既利用了WHERE条件的高效快速，又发挥了HAVING可以使用包含分组统计函数的查询条件的优点。当数据量特别大的时候，运行效率会有很大的差别。

8、SELECT的执行过程

查询的结果如下：

# 方式1：
# sql92语法：
SELECT ...,....,...
FROM ...,...,....
WHERE 多表的连接条件
AND 不包含组函数的过滤条件
GROUP BY ...,...
HAVING 包含组函数的过滤条件
ORDER BY ... ASC/DESC
LIMIT ...,...

# 方式2：
# sql99语法：
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：分页

SQL语句的执行过程：

1	FROM ...,...-> ON -> (LEFT/RIGNT JOIN) -> 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的执行过程中，对于我们来说是不可见的。

SQL的执行原理：
- SELECT是先执行FROM这一步的。在这个阶段，如果是多张表联查，还会经历下面的几个步骤：
  - 首先先通过CROSS JOIN求笛卡尔积，相当于得到虚拟表vt（virtual table）1-1；
  - 通过ON进行筛选，在虚拟表vt1-1的基础上进行筛选，得到虚拟表vt1-2；
  - 添加外部行。如果我们使用的是左连接、右链接或者全连接，就会涉及到外部行，也就是在虚拟表vt1-2的基础上增加外部行，得到虚拟表vt1-3。
- 当然如果我们操作的是两张以上的表，还会重复上面的步骤，直到所有表都被处理完为止。这个过程得到是我们的原始数据。
- 当我们拿到了查询数据表的原始数据，也就是最终的虚拟表vt1，就可以在此基础上再进行WHERE阶段。在这个阶段中，会根据vt1表的结果进行筛选过滤，得到虚拟表vt2。
- 然后进入第三步和第四步，也就是GROUP和HAVING阶段。在这个阶段中，实际上是在虚拟表vt2的基础上进行分组和分组过滤，得到中间的虚拟表vt3和vt4。
- 当我们完成了条件筛选部分之后，就可以筛选表中提取的字段，也就是进入到SELECT和DISTINCT阶段。
- 首先在SELECT阶段会提取想要的字段，然后在DISTINCT阶段过滤掉重复的行，分别得到中间的虚拟表vt5-1和vt5-2。
- 当我们提取了想要的字段数据之后，就可以按照指定的字段进行排序，也就是ORDER BY阶段，得到虚拟表vt6。
- 最后在vt6的基础上，取出指定行的记录，也就是LIMIT阶段，得到最终的结果，对应的是虚拟表vt7。
- 当然我们在写SELECT语句的时候，不一定存在所有的关键字，相应的阶段就会省略。
- 同时因为SQL是一门类似英语的结构化查询语言，所以我们在写SELECT语句的时候，还要注意相应的关键字顺序，所谓底层运行的原理，就是我们刚才讲到的执行顺序。

9、子查询

子查询指一个查询语句嵌套在另一个查询语句内部的查询，这个特性从MySQL 4.1开始引入。
SQL中子查询的使用大大增强了SELECT查询的能力，因为很多时候查询需要从结果集中获取数据，或者需要从同一个表中先计算得出一个数据结果，然后与这个数据结果（可能是某个标量，也可能是某个集合）进行比较。

由一个具体的需求，引入子查询：

# 需求：谁的工资比Abel的高？
# 方式1：
mysql> SELECT salary FROM employees WHERE last_name = 'Abel';
+----------+
| salary   |
+----------+
| 11000.00 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT last_name,salary FROM employees WHERE salary > 11000;
+-----------+----------+
| last_name | salary   |
+-----------+----------+
| King      | 24000.00 |
| Kochhar   | 17000.00 |
| De Haan   | 17000.00 |
| Greenberg | 12000.00 |

# 方式2：自连接
SELECT e2.last_name,e2.salary
FROM employees e1,employees e2
WHERE e2.`salary` > e1.`salary` # 多表的连接条件
AND e1.last_name = 'Abel';

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

# 称谓的规范：外查询（或主查询）、内查询（或子查询）
/*
- 子查询（内查询）在主查询之前一次执行完成。
- 子查询的结果被主查询（外查询）使用 。
- 注意事项
  - 子查询要包含在括号内
  - 将子查询放在比较条件的右侧(可读性好些)
  - 单行操作符对应单行子查询，多行操作符对应多行子查询
*/

子查询的分类：
- 分类方式1：按内查询的结果返回一条还是多条记录，将子查询分为单行子查询、多行子查询。
  - 单行子查询：
  - 多行子查询：
- 分类方式2：按内查询是否被执行多次，将子查询划分为相关(或关联)子查询和不相关(或非关联)子查询。
  - 子查询从数据表中查询了数据结果，如果这个数据结果只执行一次，然后这个数据结果作为主查询的条件进行执行，那么这样的子查询叫做不相关子查询。
  - 同样，如果子查询需要执行多次，即采用循环的方式，先从外部查询开始，每次都传入子查询进行查询，然后再将结果反馈给外部，这种嵌套的执行方式就称为相关子查询。

9.1 单行子查询

单行比较操作符如下：

# 查询工资大于149号员工工资的员工的信息
SELECT employee_id,last_name,salary
FROM employees
WHERE salary > (
		SELECT salary
		FROM employees
		WHERE employee_id = 149
		);


# 返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id和工资
SELECT last_name,job_id,salary
FROM employees
WHERE job_id = (
		SELECT job_id
		FROM employees
		WHERE employee_id = 141
		)
AND salary > (
		SELECT salary
		FROM employees
		WHERE employee_id = 143
		);
		
# 返回公司工资最少的员工的last_name,job_id和salary
SELECT last_name,job_id,salary
FROM employees
WHERE salary = (
		SELECT MIN(salary)
		FROM employees
		);

# 查询与141号员工的manager_id和department_id相同的其他员工的employee_id，manager_id，department_id。
# 方式1：
SELECT employee_id,manager_id,department_id
FROM employees
WHERE manager_id = (
		    SELECT manager_id
		    FROM employees
		    WHERE employee_id = 141
		   )
AND department_id = (
		    SELECT department_id
		    FROM employees
		    WHERE employee_id = 141
		   )
AND employee_id <> 141;

# 方式2：了解
SELECT employee_id,manager_id,department_id
FROM employees
WHERE (manager_id,department_id) = (
				    SELECT manager_id,department_id
			            FROM employees
				    WHERE employee_id = 141
				   )
AND employee_id <> 141;

HAVING中的子查询

首先执行子查询。

向主查询中的HAVING子句返回结果。

# 查询最低工资大于110号部门最低工资的部门id和其最低工资
SELECT department_id,MIN(salary)
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
HAVING MIN(salary) > (
			SELECT MIN(salary)
			FROM employees
			WHERE department_id = 110
		     );

CASE中的子查询

在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;

子查询中的空值问题

# 当last_name = 'Haas'的人不存在时无结果
SELECT last_name, job_id
FROM   employees
WHERE  job_id =
                (SELECT job_id
                 FROM   employees
                 WHERE  last_name = 'Haas');

非法使用子查询

# 在单行子查询中，如果子查询返回多个查询结果时报错
mysql> SELECT employee_id, last_name
    -> FROM   employees
    -> WHERE  salary =
    ->                 (SELECT   MIN(salary)
    ->                  FROM     employees
    ->                  GROUP BY department_id); 
ERROR 1242 (21000): Subquery returns more than 1 row

9.2 多行子查询

也称为集合比较子查询。
内查询返回多行。
使用多行比较操作符。

多行比较操作符：

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

# ANY / ALL:
# 返回其它job_id中比job_id为‘IT_PROG’部门任一工资低的员工的员工号、姓名、job_id 以及salary
# 等价于小于job_id为‘IT_PROG’部门的最高工资员工的其它部门员工信息
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE job_id <> 'IT_PROG'
AND salary < ANY (
		SELECT salary
		FROM employees
		WHERE job_id = 'IT_PROG'
		);

# 返回其它job_id中比job_id为‘IT_PROG’部门所有工资低的员工的员工号、姓名、job_id 以及salary
# 等价于小于job_id为‘IT_PROG’部门的最低工资员工的其它部门员工信息
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE job_id <> 'IT_PROG'
AND salary < ALL (
		SELECT salary
		FROM employees
		WHERE job_id = 'IT_PROG'
		);
		
# 查询平均工资最低的部门id
# MySQL中聚合函数是不能嵌套使用的。
# 方式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
				) t_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
			)

空值问题

# 如果子查询中出现null的数据行则会导致整个查询结果为空
SELECT last_name
FROM employees
WHERE employee_id NOT IN (
			SELECT manager_id
			FROM employees
    		# where manager_id is not null
			);

9.3 相关子查询

如果子查询的执行依赖于外部查询，通常情况下都是因为子查询中的表用到了外部的表，并进行了条件关联，因此每执行一次外部查询，子查询都要重新计算一次，这样的子查询就称之为关联子查询。

相关子查询按照一行接一行的顺序执行，主查询的每一行都执行一次子查询（子查询中使用主查询中的列）。

# 回顾：查询员工中工资大于公司平均工资的员工的last_name,salary和其department_id
SELECT last_name,salary,department_id
FROM employees
WHERE salary > (
		SELECT AVG(salary)
		FROM employees
		);

# 查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id
# 方式1：使用相关子查询
SELECT last_name,salary,department_id
FROM employees e1
WHERE salary > (
		SELECT AVG(salary)
		FROM employees e2
		WHERE department_id = e1.`department_id`
		);

# 方式2：在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_sal
WHERE e.department_id = t_dept_avg_sal.department_id
AND e.salary > t_dept_avg_sal.avg_sal

# 查询员工的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`
	) ASC;
# 结论：在SELECT中，除了GROUP BY 和 LIMIT之外，其他位置都可以声明子查询！

# 若employees表中employee_id与job_history表中employee_id相同的数目不小于2，输出这些相同id的员工的employee_id,last_name和其job_id。
SELECT employee_id,last_name,job_id
FROM employees e
WHERE 2 <= (
	    SELECT COUNT(*)
	    FROM job_history j
	    WHERE e.`employee_id` = j.`employee_id`
		)

EXISTS与NOT EXISTS关键字

关联子查询通常也会和EXISTS操作符一起来使用，用来检查在子查询中是否存在满足条件的行。
如果在子查询中不存在满足条件的行：
- 条件返回FALSE。
- 继续在子查询中查找如果在子查询中存在满足条件。
如果在子查询中存在满足条件的行：
- 不在子查询中继续查找。
- 条件返回TRUE。

NOT EXISTS关键字表示如果不存在某种条件，则返回TRUE，否则返回FALSE。

# 查询公司管理者的employee_id，last_name，job_id，department_id信息
# 方式1：自连接
SELECT DISTINCT mgr.employee_id,mgr.last_name,mgr.job_id,mgr.department_id
FROM employees emp JOIN employees mgr
ON emp.manager_id = mgr.employee_id;

# 方式2：子查询
SELECT employee_id,last_name,job_id,department_id
FROM employees
WHERE employee_id IN (
			SELECT DISTINCT manager_id
			FROM employees
			);

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

# 查询departments表中，不存在于employees表中的部门的department_id和department_name
# 方式1：使用右外连接
SELECT d.department_id,d.department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL;

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

9.4 相关更新

使用相关子查询依据一个表中的数据更新另一个表的数据。

格式如下：

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);

9.5 相关删除

使用相关子查询依据一个表中的数据删除另一个表的数据。

格式如下：

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);