封面来源：本文封面来源于 MySQL 官网，如有侵权，请联系删除。

本文参考：MySQL数据库教程天花板，mysql安装到mysql高级，强！硬！

1. SQL 的概述

1.1 SQL 的背景知识

1974 年，IBM 研究员发布了一篇揭开数据库技术的论文《SEQUEL：一门结构化的英语查询语言》，直到今天这门结构化的查询语言并没有太大的变化，相比于其他语言， SQL 的半衰期可以说是非常长了。

SQL（Structured Query Language，结构化查询语言）是使用关系模型的数据库应用语言，与数据直 接打交道。后来美国国家标准局（ANSI）开始着手制定 SQL 标准， 先后有 SQL-86 、SQL-89、SQL-92、SQL-99 等标准。 SQL 有两个重要的标准，分别是 SQL-92 和 SQL-99，它们分别代表了 92 年和 99 年颁布的 SQL 标准，我们今天使用的 SQL 语言依然遵循这些标准。

不同数据库厂商生成的 RDBMS（Relational Database Management System，关系数据库管理系统）应用软件都会遵循这些标准，但它们之间也有自己的特点，比如像 MySQL 与 Oracle。

1.2 SQL 的分类

SQL 语言在功能上主要分为如下 3 大类：

DDL（Data Definition Languages，数据定义语言），这些语句定义了不同的数据库、表、视图、索 引等数据库对象，还可以用来创建、删除、修改数据库和数据表的结构。 主要的语句关键字包括 CREATE、DROP、ALTER 等。

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，事务控制语言）。

1.3 SQL 语言的规则与规范

1、SQL 语句可以写在一行或者多行。为了提高可读性，各子句分行写，必要时使用缩进；

2、每条命令以 ; 或 \g 或 \G 结束；

3、关键字不能被缩写也不能分行；

4、关于标点符号：

  • 必须保证所有的 ()、单引号、双引号是成对结束的
  • 必须使用英文状态下的半角输入方式
  • 字符串型和日期时间类型的数据可以使用单引号 '' 表示
  • 列的别名，尽量使用双引号 ""，而且不建议省略 as

1.4 SQL 的大小写规范

1、MySQL 在 Windows 环境下是大小写不敏感的；

2、MySQL 在 Linux 环境下是大小写敏感的

  • 数据库名、表名、表的别名、变量名是严格区分大小写的

  • 关键字、函数名、列名（或字段名）、列的别名（字段的别名）是忽略大小写的

3、 推荐采用统一的书写规范：

  • 数据库名、表名、表别名、字段名、字段别名等都小写
  • SQL 关键字、函数名、绑定变量等都大写

1.5 SQL 的注释

单行注释：

1
2
# 注释文字（MySQL 特有）
-- 注释文字（--后面必须有一个空格）

多行注释：

1
2
3
4
/*
  注释文字
  注释文字
*/

1.6 命名规则

1、数据库、表名不得超过 30 个字符，变量名限制为 29 个

2、只能包含 A–Z、a–z、0–9、_ 共 63 个字符

3、数据库名、表名、字段名等对象名中间不要包含空格

4、同一个 MySQL 软件中，数据库不能同名；同一个库中，表不能重名；同一个表中，字段不能重名

5、必须保证你的字段没有和保留字、数据库系统或常用方法冲突。如果坚持使用，需要在 SQL 语句中使用 ` （反引号）引起来

6、保持字段名和类型的一致性，在命名字段并为其指定数据类型的时候一定要保证一致性。例如某个字段的数据类型在一个表里是整数，但在另一个表里变成了字符型，这样会增加理解成本

1.7 数据导入指令

在命令行客户端登录 MySQL（在图形化界面内输入指令是无效的），使用 source 指令导入。比如：

1
source d:\mysqldb.sql

查看某个表的表结构：

1
desc employees; # 查看 employees 表的结构

2. 基本的 SELECT

2.1 简单的使用

没有子句的 SELECT

1
2
3
4
5
SELECT 1; # 没有任何子句
SELECT 9/2; # 没有任何子句
# 等效于
SELECT 1 FROM DUAL; # DUAL 是伪表
SELECT 9/2 FROM DUAL;

SELECT … FROM

查询部门表中的所有信息：

1
SELECT * FROM departments;

除非需要使用表中所有的字段数据，否则不建议使用 * 通配符，使用 * 通配符获取不需要的列数据通常会降低查询和所使用的应用程序的效率。因此在生产环境下，不建议直接使用 SELECT * 来查询数据。

查询部门表中的部门 ID 和位置 ID：

1
SELECT department_id, location_id FROM departments;

MySQL 中的 SQL 语句是不区分带小写的，因此 SELECT 和 select 的含义与作用是一样的，只不过大多数开发人员习惯将关键词大写、将列名和表名小写，这也是一个良好的编程习惯。

2.2 列的别名

在查询某些列的数据时， 可以对这些列进行重命名，也就是取一个别名，以方便后续计算。

别名需要紧跟列名，以空格隔开。也可以在列名和别名之间加入关键字 AS，并对别名使用双引号，以便在别名中包含空格或其他特殊字符并区分大小写。

比如：

1
2
SELECT last_name AS name, commission_pct comm FROM employees;
SELECT last_name "Name", salary*12 "Annual Salary" FROM employees;

使用建议： 不省略 AS 关键字，别名使用双引号引上。

2.3 去除重复行

使用 DISTINCT 去除重复行：

1
SELECT DISTINCT department_id, salary FROM employees;

注意： 上述 SQL 语句的查询结果是 department_id 和 salary 列的组合去重。

如果只想查看员工分布在哪些部门中，那么需要：

1
SELECT DISTINCT department_id FROM employees;

2.4 空值参与运算

运算符或列值遇到 NULL 值时，其运算结果都是 NULL。比如：

1
2
3
SELECT employee_id, salary, commission_pct,
	12 * salary * (1 + commission_pct) "annual_sal"
FROM employees;

注意： 在 MySQL 里，空值不等于空字符串。一个空字符串的长度是 0，而一个空值的长度是空。不仅如此，MySQL 里的空值也会占用空间。

2.5 反引号的使用

有一订单表名为 ORDER，需要查询该表中的所有数据：

1
SELECT * FROM ORDER;

但运行这条 SQL 语句时会报错，因为 ORDER 是 MySQL 中的一个关键词，因此需要使用反引号将其引上：

1
SELECT * FROM `ORDER`;

2.6 对常数进行查询

SELECT 还支持对常数进行查询。比如：

1
SELECT '默烦' as corporation, last_name FROM employees;

执行上述语句后将会在查询结果中增加一列固定的常数列，这列的取值是由我们指定的，而不是从数据库表中动态取出的。

那对常数进行查询有什么意义呢？

当想要整合不同的数据源时，可以用常数列作为这个表的标记，这时就需要查询常数。

2.7 显示表结构

可以在命令行里执行以下语句显示表结构：

1
2
3
DESCRIBE employees;
# 或
DESC employees;

employees表结构

各个字段的含义如下：

字段名 含义
Field 字段名称
Type 字段类型
Null 是否能存储 NULL 值
Key 是否已编制索引。PRI 表示该列是表主键的一部分；UNI 表示该列是 UNIQUE 索引的一 部分；MUL 表示在列中某个给定值允许出现多次
Default 默认值
Extra 可以获取的与给定列有关的附加信息，如 AUTO_INCREMENT

2.8 简单的条件查询

返回在 90 号部门工作的所有员工信息：

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

在 FROM 子句后紧随 WHERE 子句，使用 WHERE 子句获取符合条件的行。

3. 运算符

3.1 算术运算符

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

运算符 名称 作用
+ 加法 计算两个值或表达式的和
- 减法 计算两个值或表达式的差
* 乘法 计算两个值或表达式的乘积
/ 或 DIV 除法 计算两个值或表达式的商
% 或 MOD 求模（求余） 计算两个值或表达式的余数

加法与减法运算符

1
2
SELECT 100, 100 + 0, 100 - 0, 100 + 50, 100 + 50 -30, 100 + 35.5, 100 - 35.5 
FROM dual;

加法与减法运算符的使用

根据运算结果，可以得出：

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

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

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

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

乘法与除法运算符

1
2
SELECT 100, 100 * 1, 100 * 1.0, 100 / 1.0, 100 / 2,100 + 2 * 5 / 2,100 /3, 100 
DIV 0 FROM dual;

乘法与除法运算符的使用

根据运算结果，可以得出：

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

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

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

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

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

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

求模运算符

1
SELECT 12 % 3, 12 MOD 5 FROM dual;

取模运算符的使用

求模，即：求余。12 除以 3 的余数是 0，因此 12 模 3 得到的结果也是 0；12 除以 5 的余数是 2，因此 12 模 5 的结果也是 2。

3.2 比较运算符

比较运算符用来对表达式左、右两边的操作数进行比较，比较结果为真时返回 1，为假时返回 0，其它情况返回 NULL。比较运算符常被用来作为 SELECT 查询语句的查询条件。

运算符 名称 作用
= 等于 判断两个值、字符串或表达式是否相等
<=> 安全等于 安全地判断两个值、字符串或表达式是否相等
<> 或 != 不等于 判断两个值、字符串或表达式是否不相等
< 小于 判断前面的值、字符串或表达式是否小于后面的值、字符串或表达式
<= 小于等于 判断前面的值、字符串或表达式是否小于等于后面的值、字符串或表达式
> 大于 判断前面的值、字符串或表达式是否大于后面的值、字符串或表达式
>= 大于等于 判断前面的值、字符串或表达式是否大于等于后面的值、字符串或表达式

等号运算符

1
2
# Java 中的等于是 `==`，SQL 中的赋值符号使用 `:=`
SELECT 1 = 1, 1 = '1', 1 = 0, 'a' = 'a', (5 + 3) = (2 + 6), '' = NULL, NULL = NULL;

等于运算符的使用-1

1
SELECT 1 = 2, 0 = 'abc', 1 = 'abc' FROM dual;

等于运算符的使用-2
在使用等号运算符时，遵循如下规则：

1、如果等号两边的值、字符串或表达式都为字符串，则 MySQL 会按照字符串进行比较，其比较的
是每个字符串中字符的 ANSI 编码是否相等；

2、如果等号两边的值都是整数，则 MySQL 会按照整数来比较两个值的大小；

3、如果等号两边的值一个是整数，另一个是字符串，则 MySQL 会将字符串转化为数字进行比较；

4、如果等号两边的值、字符串或表达式中有一个为 NULL，则比较结果为 NULL。

安全等于运算符

安全等于运算符 <=> 与等于运算符 = 的作用是相似的，只是 <=> 可以用来对 NULL进行判断。在两个操作数均为 NULL 时，其返回值为 1，而不是 NULL；其他返回结果与等于运算符相同。

1
SELECT 1 <=> '1', 1 <=> 0, 'a' <=> 'a', (5 + 3) <=> (2 + 6), '' <=> NULL,NULL <=> NULL FROM dual;

安全等于运算符的使用

不等于运算符

不等于运算符 <> 或 != 不能判断 NULL 值。如果两边的值有任意一个为 NULL，或两边都为 NULL，结果都为 NULL。

不等于运算符的使用

3.3 非符号类型运算符

运算符 名称 作用
IS NULL 为空 判断值、字符串或表达式是否为空
IS NOTNULL 不为空 判断值、字符串或表达式是否不为空
LEAST 最小值 在多个值中返回最小值
GREATEST 最大值 在多个值中返回最大值
BETWEEN AND 两值之间 判断一个值是否在两个值之间
ISNULL 为空 判断值、字符串或表达式是否为空
IN 属于 判断一个值是否为列表中的任意一个值
NOT IN 不属于 判断一个值是否不为列表中的任意一个值
LIKE 模糊匹配 判断一个值是否符合模糊匹配规则
REGEXP 正则表达式 判断一个值是否符合正则表达式的规则
RLIKE 正则表达式 判断一个值是否符合正则表达式的规则

为空运算符

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

1
SELECT NULL IS NULL, ISNULL(NULL), ISNULL('a'), 1 IS NULL;

为空运算符的使用

1
2
3
4
5
# 查询 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。

1
SELECT NULL IS NOT NULL, 'a' IS NOT NULL, 1 IS NOT NULL;

非空运算符的使用

在 Navicat 15 中执行上述语句会报错，显示 Unknown column ' 1' in 'field list'，但在命令行中执行不会出现错误。

最小值运算符

语法格式为：LEAST(值1，值2，...，值n)。其中，“值n”表示参数列表中第 n 个值。在有两个或多个参数的情况下，返回最小值。

1
SELECT LEAST (1,0,2), LEAST('b','a','c'), LEAST(1,NULL,2);

最小值运算符的使用

当参数是整数或者浮点数时，LEAST 将返回其中最小的值；当参数为字符串时，返回字母表中顺序最靠前的字符；当比较值列表中有 NULL 时，不能判断大小，返回值为 NULL。

最大值运算符

语法格式为：GREATEST(值1，值2，...，值n)。其中，“值n”表示参数列表中第 n 个值。在有两个或多个参数的情况下，返回最大值。

1
SELECT GREATEST(1,0,2), GREATEST('b','a','c'), GREATEST(1,NULL,2);

最大值运算符的使用

当参数中是整数或者浮点数时，GREATEST 将返回其中最大的值；当参数为字符串时，返回字母表中顺序最靠后的字符；当比较值列表中有 NULL 时，不能判断大小，返回值为 NULL。

BETWEEN AND运算符

语法格式为：

1
SELECT D FROM TABLE WHERE C BETWEEN A AND B;

当 C 大于或等于 A、并且小于或等于 B 时，结果返回1，否则返回 0。

BETWEEN...AND运算符的使用

IN 运算符

IN 运算符用于判断给定的值是否是给定列表中的一个值，如果是则返回 1，否则返回 0。如果给
定的值为 NULL，或者给定列表中存在 NULL，则结果为 NULL。

1
SELECT 'a' IN ('a','b','c'), 1 IN (2,3), NULL IN ('a','b'), 'a' IN ('a', NULL), NULL IN ('a', NULL);

IN运算符的使用

1
2
3
SELECT employee_id, last_name, salary, manager_id
FROM   employees
WHERE  manager_id IN (100, 101, 201);

NOT IN 运算符

NOT IN 运算符与 IN 运算符的效果相反：

1
SELECT 'a' NOT IN ('a','b','c'), 1 NOT IN (2,3);

NOT...IN运算符的使用

LIKE 运算符

LIKE 运算符主要用来匹配字符串，通常用于模糊匹配，如果满足条件则返回 1，否则返回 0。如果给定的值或者匹配条件为 NULL，返回结果为 NULL。

常用的通配符：

%：匹配 0 个或多个字符；

_：仅匹配一个字符。

1
SELECT NULL LIKE 'abc', 'abc' LIKE NULL, 'abc' LIKE "abc", "abc" LIKE "ab_", "abc" LIKE "a%";

LIKE运算符的使用

1
2
3
4
5
# 查询以 S 开头的名
SELECT  first_name FROM employees WHERE first_name LIKE 'S%';

# 查询第二个字母是 o 的姓
SELECT last_name FROM employees WHERE last_name LIKE '_o%';

% 和 _ 作为常用的通配符，如果就是要查询存在这两个符号的内容呢？可以使用 \ 作为转义符。

比如查询 job_id 中以 IT_ 开头的内容：

1
SELECT job_id FROM jobs WHERE job_id LIKE 'IT\_%';

还可以使用 ESCAPE 自定义转义符，比如：

1
2
# 查询 job_id 以 IT_ 开头的信息
SELECT job_id FROM jobs WHERE job_id LIKE 'IT$_%' ESCAPE '$';

REGEXP 运算符

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

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

1、^ 匹配以该字符后面的字符开头的字符串；

2、$ 匹配以该字符前面的字符结尾的字符串；

3、. 匹配任何一个单字符；

4、[...] 匹配在方括号内的任何字符。例如，[abc] 可以匹配 a 或 b 或 c。为了命名字符的范围，可以使用一个-。比如 [a-z] 匹配任何字母，而 [0-9] 匹配任何数字；

5、* 匹配零个或多个在它前面的字符。例如，x* 匹配任意个 x 字符，[0-9]* 匹配任意个数字，而 * 匹配任意个任何字符；

1
SELECT 'mofan' REGEXP '^m', 'mofan' REGEXP 'n$', 'mofan' REGEXP 'of';

REGEXP运算符的使用-1

1
SELECT 'mofanmofan' REGEXP 'an.of', 'mofan' REGEXP '[mz]';

REGEXP运算符的使用-2

3.4 逻辑运算符

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

运算符 作用
NOT 或 ! 逻辑非
AND 或 && 逻辑与
OR 或 || 逻辑或
XOR 逻辑异或

逻辑非运算符

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

1
SELECT NOT 1, NOT 0, NOT(1+1), NOT !1, NOT NULL;

逻辑非运算符的使用

1
2
3
SELECT last_name, job_id 
FROM employees 
WHERE job_id NOT IN ('IT_PROG', 'ST_CLERK', 'SA_REP');

逻辑与运算符

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

1
SELECT 1 AND -1, 0 AND 1, 0 AND NULL, 1 AND NULL;

逻辑与运算符的使用

1
2
3
4
SELECT employee_id, last_name, job_id, salary 
FROM employees
WHERE salary >=10000
AND job_id LIKE '%MAN%';

逻辑或运算符

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

1
SELECT 1 OR -1, 1 OR 0, 1 OR NULL, 0 || NULL, NULL || NULL;

逻辑或运算符的使用

1
2
3
4
5
6
7
8
9
# 查询基本薪资不在 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;
1
2
3
4
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary >= 10000
OR job_id LIKE '%MAN%';

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

逻辑异或运算符

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

区别于数学中的异或，数学中的异或只对 0 和 1 进行运算，因此常有“相同取 1，不同取 0”的口诀，而在 MySQL 中 1 XOR -1 的值是 0，而不是 1。数学中的异或更像下面讲到的按位异或运算符。

1
SELECT 1 XOR -1, 1 XOR 0, 0 XOR 0, 1 XOR NULL, 1 XOR 1 XOR 1, 0 XOR 0 XOR 0;

逻辑异或运算符的使用

1
2
3
select last_name,department_id,salary 
from employees
where department_id in (10,20) XOR salary > 8000;

3.5 位运算符

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

运算符 作用
& 按位与
| 按位或
^ 按位异或
~ 按位取反
>> 按位右移
<< 按位左移

按位与运算符

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

1
SELECT 1 & 10, 20 & 30;

按位与运算符的使用

解析：1 的二进制数为 0001，10 的二进制数为 1010，所以 1 & 10 的结果为 0000，对应的十进制数为 0。20 的二进制数为 10100，30 的二进制数为 11110，所以 20 & 30 的结果为10100，对应的十进制数为 20。

按位或运算符

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

1
SELECT 1 | 10, 20 | 30;

按位或运算符的使用

解析：1 的二进制数为 0001，10 的二进制数为 1010，所以 1 | 10 的结果为 1011，对应的十进制数为 11。20 的二进制数为 10100，30 的二进制数为 11110，所以 20 | 30 的结果为 11110，对应的十进制数为 30。

按位异或运算符

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

1
SELECT 1 ^ 10, 20 ^ 30;

按位异或运算符的使用

解析：1 的二进制数为 0001，10 的二进制数为 1010，所以 1 ^ 10 的结果为 1011，对应的十进制数为 11。20 的二进制数为 10100，30 的二进制数为 11110，所以 20 ^ 30 的结果为 01010，对应的十进制数为 10。

按位取反运算符

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

1
SELECT 10 & ~1;

按位取反运算符的使用

解析：由于按位取反 ~ 运算符的优先级高于按位与 & 运算符的优先级，所以 10 & ~1 会首先对数字 1 进行按位取反操作，结果除了最低位为 0，其他位都为 1，然后与 10 进行按位与操作，结果为 10。

按位右移运算符

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

1
SELECT 1 >> 2, 4 >> 2;

按位右移运算符的使用

解析：1 的二进制数为 0000 0001，右移 2 位为 0000 0000，对应的十进制数为 0。4 的二进制数为 0000 0100，右移 2 位为 0000 0001，对应的十进制数为 1。

按位左移运算符

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

1
SELECT 1 << 2, 4 << 2;

按位左移运算符的使用

解析：1 的二进制数为 0000 0001，左移两位为 0000 0100，对应的十进制数为 4。4 的二进制数为 0000 0100，左移两位为 0001 0000，对应的十进制数为 16。

3.6 运算符的优先级

优先级 运算符
1 := =（赋值）
2 `
3 && AND
4 NOT
5 BETWEEN CASE WHEN THEN ELSE
6 =（比较运算符） <=> >= > <= < <> != IS LIKE REGEXP IN
7 `
8 &
9 << >>
10 - +
11 * / DIV % MOD
12 ^
13 -（负号） ~
14 !
15 0

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

3.7 使用正则表达式查询

正则表达式通常被用来检索或替换那些符合某个模式的文本内容，根据指定的匹配模式匹配文本中符合要求的特殊字符串。例如，从一个文本文件中提取电话号码、查找一篇文章中重复的单词或者替换用户输入的某些敏感词语等。正则表达式强大而且灵活，可以应用于非常复杂的查询。

MySQL 中使用 REGEXP 关键字指定正则表达式的字符匹配模式。下表列出了常用的字符匹配：

MySQL中常用的正则表达式匹配符

查询以特定字符或字符串开头的记录

^ 匹配以特定字符或者字符串开头的文本。比如，查询 fruits 表中 f_name 字段以字母 b 开头的记录：

1
SELECT * FROM fruits WHERE f_name REGEXP '^b';

查询以特定字符或字符串结尾的记录

$ 匹配以特定字符或者字符串结尾的文本。比如，查询 fruits 表中 f_name 字段以字母 y 结尾的记录：

1
SELECT * FROM fruits WHERE f_name REGEXP 'y$';

替代字符串中的任意一个字符

. 匹配任意一个字符。比如，查询 fruits 表中 f_name 字段值包含字母 a 与 g 且两个字母之间只有一个字母的记录：

1
SELECT * FROM fruits WHERE f_name REGEXP 'a.g';

多个字符的匹配

* 匹配前面的字符任意多次，也包括 0 次，而 + 可以匹配前面的字符至少一次。

查询 fruits 表中 f_name 字段值以字母 b 开头且 b 后面 可能 出现字母 a 的记录：

1
SELECT * FROM fruits WHERE f_name REGEXP '^ba*';

查询 fruits 表中 f_name 字段值以字母 b 开头且 b 后面 至少出现一次 字母 a 的记录：

1
SELECT * FROM fruits WHERE f_name REGEXP '^ba+';

匹配指定字符串

正则表达式可以匹配指定字符串，只要这个字符串在查询文本中即可，如要匹配多个字符串，字符串之间使用 | 隔开即可。

查询 fruits 表中 f_name 字段值包含字符串 on 的记录：

1
2
3
SELECT * FROM fruits WHERE f_name REGEXP 'on';
# 等效于
SELECT * FROM fruits WHERE f_name like '%on%';

查询 fruits 表中 f_name 字段值包含字符串 on 或 ap 的记录：

1
SELECT * FROM fruits WHERE f_name REGEXP 'on|ap';

匹配指定字符中的任意一个

[] 指定一个字符集合， 只匹配其中任何一个字符， 即为所查找的文本。比如，查找 fruits 表中 f_name 字段中包含字母 o 或者 t 的记录：

1
SELECT * FROM fruits WHERE f_name REGEXP '[ot]';

在比如，查询 fruits 表中 s_id 字段中包含 4、5 或 6 的记录：

1
SELECT * FROM fruits WHERE s_id REGEXP '[456]';

匹配指定字符以外的字符

[^ ] 匹配不在指定集合中的任何字符。比如，查询 fruits 表中 f_id 字段中包含字母 a 到 e 和数字 1 到 2 以外字符的记录：

1
SELECT * FROM fruits WHERE f_id REGEXP '[^a-e1-2]';

匹配出现指定次数的字符串

字符串{n,} 表示至少匹配 n 次前面的字符；字符串{n,m} 表示匹配前面的字符串不少于 n 次，不多于 m 次。例如，a{2,} 表示字母 a 连续出现至少 2 次，a{2,4} 表示字母 a 连续出现最少 2 次，但不超过4次。

查询 fruits 表中 f_name 字段值出现字母 x 至少 2 次的记录：

1
SELECT * FROM fruits WHERE f_name REGEXP 'x{2,}';

查询 fruits 表中 f_name 字段值出现字符串 ba 最少 1 次、最多 3 次的记录：

1
SELECT * FROM fruits WHERE f_name REGEXP 'ba{1,3}';

4. 排序与分页

4.1 数据的排序

排序规则

使用 ORDER BY 子句排序，ORDER BY 子句要在 SELECT 语句的结尾。有以下两种排序规则：

1、ASC（ascend）：升序
2、DESC（descend）：降序

默认排序规则是升序。

单列排序

比如按入职时间对员工进行升序排序：

1
2
3
SELECT last_name, job_id, department_id, hire_date
FROM employees
ORDER BY hire_date;

按入职时间升序排序

再比如按入职时间降序排序：

1
2
3
SELECT last_name, job_id, department_id, hire_date
FROM employees
ORDER BY hire_date DESC;

按入职时间降序排序

多列排序

1
2
3
SELECT last_name, department_id, salary
FROM employees
ORDER BY department_id, salary DESC;

多列排序

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

在进行多列进行排序时，如果排序的第一列有相同的列值，才会对第二列进行排序。如果第一列数据中的所有值都是唯一的，那么不会对第二列进行排序。

4.2 分页

当查询返回的数据过多时，或者表中有 4 条数据，但只想显示第 2、3 条数据时，就可以使用分页查询。

MySQL 中使用 LIMIT 关键词实现分页，基本语法如下：

1
LIMIT [位置偏移量,] 行数

其中位置偏移量是可省略的，省略时按照 0 进行处理。

比如：

1
2
3
4
5
6
7
8
9
10
-- 前 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 条记录，和 LIMIT4,3 返回的结果相同。

分页显示公式：(当前页数 - 1) * 每页条数, 每页条数，即：

1
2
SELECT * FROM table 
LIMIT (PageNo - 1) * PageSize, PageSize;

注意： LIMIT 子句必须放在整个 SELECT 语句的最后。

使用分页可以约束返回结果的数量，以减少数据表的网络传输量，并提升查询效率。如果我们知道返回结果只有 1 条，可以直接使用 LIMIT 1，告诉 SELECT 语句只返回一条记录即可。这样 SELECT 就不用扫描完整的表，只需要检索到一条符合条件的记录即可返回。

4.3 不同数据库下的分页

在不同的 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;

4.4 一起使用时的坑

参考链接：

如果 LIMIT row_count 和 ORDER BY 组合使用，MySQL 在找到排序结果的第一行时就会停止排序，而不是对整个结果进行排序。

如果多条记录的 ORDER BY 列中有相同的值，服务器可以自由地按任何顺序返回这些记录，并可能根据整体执行计划的不同而采取不同的方式。

官方的案例说明

目标排序列有相同的值，只使用 ORDER BY 查询时：

mysql> SELECT * FROM ratings ORDER BY category;
+----+----------+--------+
| id | category | rating |
+----+----------+--------+
|  1 |        1 |    4.5 |
|  5 |        1 |    3.2 |
|  3 |        2 |    3.7 |
|  4 |        2 |    3.5 |
|  6 |        2 |    3.5 |
|  2 |        3 |    5.0 |
|  7 |        3 |    2.7 |
+----+----------+--------+

此时前五条数据的 id 是 [1, 5, 3, 4, 6]。

如果追加 LIMIT 5 条件，在排序的基础上只查询前五条数据：

mysql> SELECT * FROM ratings ORDER BY category LIMIT 5;
+----+----------+--------+
| id | category | rating |
+----+----------+--------+
|  1 |        1 |    4.5 |
|  5 |        1 |    3.2 |
|  4 |        2 |    3.5 |
|  3 |        2 |    3.7 |
|  6 |        2 |    3.5 |
+----+----------+--------+

此时前五条数据的 id 发生了变化，变成了 [1, 5, 4, 3, 6]。

两次查询结果不一样： 对具有相同值的列进行 ORDER BY 时，得到的结果拥有不确定性。

如果对顺序十分敏感，可以增加具有唯一性的列进行排序。比如 id 一般来说都是唯一的，因此可以再 ORDER BY 一个 id，比如：

mysql> SELECT * FROM ratings ORDER BY category, id;
+----+----------+--------+
| id | category | rating |
+----+----------+--------+
|  1 |        1 |    4.5 |
|  5 |        1 |    3.2 |
|  3 |        2 |    3.7 |
|  4 |        2 |    3.5 |
|  6 |        2 |    3.5 |
|  2 |        3 |    5.0 |
|  7 |        3 |    2.7 |
+----+----------+--------+

mysql> SELECT * FROM ratings ORDER BY category, id LIMIT 5;
+----+----------+--------+
| id | category | rating |
+----+----------+--------+
|  1 |        1 |    4.5 |
|  5 |        1 |    3.2 |
|  3 |        2 |    3.7 |
|  4 |        2 |    3.5 |
|  6 |        2 |    3.5 |
+----+----------+--------+

对于 LIMIT 中的一些细节，可以参考：我试图扯掉这条 SQL 的底裤。只能扯一点点，不能扯多了~

5. 多表查询

多表查询，也称为关联查询，指两个或多个表一起完成查询操作。

这些一起查询的表之间是有关系的（比如：一对一、一对多），因此它们之间一定是有关联字段的，这些关联字段可能建立了外键，也可能没有建立外键。

5.1 笛卡尔积（或交叉连接）的理解

现在需要查询员工的姓名及其部门名称，根据现有知识得：

1
SELECT last_name, department_name FROM employees, departments;

这样会查询到多少数据呢？

1
2
3
4
5
SELECT COUNT(employee_id) FROM employees;
# 输出 107 行

SELECT COUNT(department_id)FROM departments;
# 输出 27 行

那么最终输出的数据条数有 107 * 27 = 2889。

我们把上述多表查询中出现的问题成为：笛卡尔积的错误。

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

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

在 MySQL 中，像下面这样查询员工姓名和所在部门名称就会出现笛卡尔积：

1
2
3
4
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;

笛卡尔积产生的条件

1、省略多个表的连接条件（或关联条件）

2、连接条件（或关联条件）无效

3、所有表中的所有行互相连接

如何避免产生笛卡尔积

在 WHERE 子句后加入有效的连接条件即可有效地避免笛卡尔积的产生。比如：

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

当某个字段的名称在多个表中出现时，需要在列名前加上表名后缀。 比如：

1
2
3
SELECT last_name, department_name
FROM employees, departments
WHERE employees.department_id = departments.department_id;

5.2 多表查询的分类讲解

等值连接 VS 非等值链接

【等值连接】查询员工的姓名与所在部门信息：

1
2
3
4
5
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;

当多个表中有相同的列名时，必须 在列名前加上表名对列名进行区分。否则会出现以下错误：

Column 'xxx' in field list is ambiguous

阿里巴巴开发规范中也提及到：

【强制】对于数据库中表记录的查询和变更，只要涉及多个表，都需要在列名前加表的别名（或
表名）进行限定。

为了对查询语句进行简化，可以对表名使用别名。如：

1
2
3
4
SELECT e.employee_id, e.last_name, 
	e.department_id, d.department_id, d.location_id
FROM employees e , departments d
WHERE e.department_id = d.department_id;

注意： 如果在 SQL 语句里使用了表的别名，那么在查询字段中、过滤条件中就只能使用别名，不能使用原有的表名。

【非等值链接】查询员工的工资等级：

1
2
3
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;

自连接 VS 非自连接

【自连接】以 xxx works for xxx 输出员工姓名及其经理姓名。

分析：在员工表 employees 中存在 manager_id 字段表示某一员工的经理的 employee_id，因此需要让 employees 表自己连接自己，并用取别名的方式虚拟成两张表以代表不同的意义。

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

内连接 VS 外连接

除了查询满足条件的记录以外，外连接还可以查询某一方不满足条件的记录。

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

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

如果是左外连接，则连接条件中左边的表也称为主表，右边的表称为从表。

如果是右外连接，则连接条件中右边的表也称为主表，左边的表称为从表。

SQL92：使用 + 创建连接

在 SQL92 中采用 + 代表从表所在的位置。即左或右外连接中，+ 所标识的表就是从表。

Oracle 对 SQL92 支持较好，而 MySQL 不支持 SQL92 的外连接。

在 SQL92 中，只有左外连接和右外连接，没有满（或全）外连接。

1
2
3
4
5
6
7
8
9
# 左外连接
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;

5.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 的连接条件

它的嵌套逻辑类似使用的 FOR 循环：

1
2
3
4
5
6
for t1 in table1:
    for t2 in table2:
       if condition1:
           for t3 in table3:
              if condition2:
                  output t1 + t2 + t3

SQL99 采用的这种嵌套结构清爽、层次性强、可读性强，即使再多的表进行连接也都清晰可见。如果采用 SQL92，可读性将会大打折扣。

语法说明：

1、可以使用 ON 子句指定额外的连接条件

2、连接条件是与其它条件分开的

3、ON 子句使语句具有更高的易读性

4、关键字 JOIN、INNER JOIN、CROSS JOIN 的含义是一样的，都表示内连接

内连接（INNER JOIN）的实现

基本语法：

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

查询员工及其所在部门信息：

1
2
3
4
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);

查询员工部门所在的城市：

1
2
3
4
5
6
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;

左外连接（LEFT OUTER JOIN）的实现

LEFT OUTER JOIN 可以简写成 LEFT JOIN。

基本语法：

1
2
3
4
SELECT 字段列表
FROM A表 LEFT JOIN B表
ON 关联条件
WHERE 等其他子句;

使用左外连接查询所有员工及其所在部门：

1
2
3
4
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);

SQL99语法左外连接查询结果示例

右外连接（RIGHT OUTER JOIN）的实现

RIGHT OUTER JOIN 也可以简写成 RIGHT JOIN。

基本语法：

1
2
3
4
SELECT 字段列表
FROM A表 RIGHT JOIN B表
ON 关联条件
WHERE 等其他子句;

使用右外连接查询所有员工及其所在部门：

1
2
3
4
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);

SQL99语法右外连接查询结果示例

使用 LEFT JOIN 也可以实现 RIGHT JOIN 的功能，反过来也是一样，只需要交换 FROM 和 JOIN 的表即可：

1
2
3
4
SELECT e.last_name, e.department_id, d.department_name
FROM departments d
LEFT OUTER JOIN employees e
ON (e.department_id = d.department_id);

注意： LEFT JOIN 和 RIGHT JOIN 只存在于 SQL99 及以后的标准中，在 SQL92 中不存在，SQL92 中只能用 + 表示外连接。

满外连接（FULL OUTER JOIN）的实现

满外连接的结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据

SQL99 支持满外连接，使用 FULL JOIN 或 FULL OUTER JOIN 即可实现。

MySQL 并不支持 FULL JOIN，但可以用 LEFT JOIN UNION RIGHT JOIN 来代替。

5.4 UNION 的使用

合并查询结果

利用 UNION 关键字，可以给出多条 SELECT 语句，并将它们的结果组合成单个结果集。合并时两个表对应的列数和数据类型必须相同，并且相互对应。各个 SELECT 语句之间使用 UNION 或 UNION ALL 关键字分隔。

语法格式：

1
2
3
SELECT column, ... FROM table1
UNION [ALL]
SELECT column, ... FROM table2

UNION 操作符

UNION操作符图解

UNION 操作符返回两个查询的结果集的并集，去除重复记录。

UNION ALL 操作符

UNION-ALL操作符图解

UNION ALL 操作符返回两个查询的结果集的并集。对于两个结果集的重复部分不会去重。

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

具体实例

查询部门编号大于 90 或邮箱包含 a 字符的员工信息：

1
2
3
4
5
6
# 方式 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;

查询中国用户中男性的信息以及美国用户中年男性的用户信息：

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

5.5 7 种 SQL JOINS 的实现

SQL-Join

中图，内连接：

1
2
3
SELECT employee_id, last_name, department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`;

左上图，左外连接：

1
2
3
SELECT employee_id, last_name, department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`;

右上图，右外连接：

1
2
3
SELECT employee_id, last_name, department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;

左中图，A - A ∩ B：

1
2
3
4
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：

1
2
3
4
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;

左下图，满外连接：

1
2
3
4
5
6
7
8
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)：

1
2
3
4
5
6
7
8
9
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;

5.6 SQL99 语法新特性

自然连接

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;

USING 连接

当我们进行连接的时候，SQL99 还支持使用 USING 指定数据表里的 同名字段 进行等值连接，但 只能配合 JOIN 一起使用。 比如：

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

它等效于：

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

与自然连接 NATURAL JOIN 不同的是，USING 指定了具体的相同的字段名称，需要在 USING 的括号 () 中填入要指定的同名字段。

相比于 JOIN ON 的等值链接，使用 JOIN USING 更加简洁。

5.7 多表查询小结

表连接的约束条件有三种方式，分别是 WHERE、ON 和 USING。其中：

WHERE：适用于所有关联查询

ON：只能和 JOIN 一起使用，只能写关联条件。虽然关联条件可以并到 WHERE 中和其他条件一起写，但分开写可读性更好。

USING：只能和 JOIN一起使用，而且要求 两个 关联字段在关联表中名称一致，而且只能表示关联 字段值相等。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
# WHERE 使用示例
SELECT last_name, department_name 
FROM employees, departments 
WHERE employees.department_id = departments.department_id;

# ON 使用示例
SELECT last_name, department_name 
FROM employees INNER JOIN departments 
ON employees.department_id = departments.department_id;

# USING 使用示例
SELECT last_name, department_name
FROM employees e JOIN departments d
USING (department_id);

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

阿里巴巴开发手册有言：超过三个表禁止 JOIN。需要 JOIN 的字段，数据类型保持绝对一致；多表关联查询时， 保证被关联的字段有索引。

6. 单行函数

6.1 函数的理解

什么是函数

我们可以将经常使用的代码封装起来，在需要的时候进行调用即可，这个封装就是一个函数。在 SQL 中我们也可以使用函数对检索出来的数据进行函数操作。使用这些函数，可以极大地提高用户对数据库的管理效率。

从函数定义的角度出发，可以将函数分为 内置函数 和 自定义函数，在 SQL 中也是这样的。内置函数是系统内置的通用函数，自定义函数则是用户根据自己的需求编写的函数。

不同 DBMS 中的函数差异

在使用 SQL 语言时，不是直接和这门语言打交道，而是使用不同的数据库软件，即 DBMS。DBMS 之间的差异性很大，远大于同一个语言不同版本之间的差异。实际上，只有很少的函数是被 DBMS 同时支持的。

比如，大多数 DBMS 使用 || 或者 + 来做拼接符，而在 MySQL 中的字符串拼接函数为 CONCAT()。大部分 DBMS 会有自己特定的函数，这就意味着采用 SQL 函数的代码可移植性是很差的，因此在使用函数的时候需要特别注意。

拓展：MySQL 与 Oracle 下的字符串拼接

在 Oracle 下可以使用 || 进行字符串拼接，但在 MySQL 中 || 表示逻辑或。

比如 Oracle 中可以这样拼接字符串：

1
SELECT 'Hello ' || 'World';

在 MySQL 中更推荐使用 CONCAT() 函数完成字符串拼接。比如：

1
SELECT CONCAT('Hello', ' ', 'World');

虽然 Oracle 也支持 CONCAT() 函数，但是它每次只能拼接两个值，对于多个值的拼接则需要嵌套，比如：

1
2
3
SELECT CONCAT('Hello', CONCAT(' ', 'World'));
-- 如果拼接的字符串中有中文，可能会出现乱码，可以使用 TO_CHAR() 函数
SELECT CONCAT(TO_CHAR('默')， TO_CHAR('烦'));

TO_CHAR() 用于将其他类型数据类型转换成字符类型的函数。对于 TO_CHAR('默') 来说，默 本身就是字符类型，所以这一步没有实际变化。其实使用 CONCAT() 函数或 || 进行字符串拼接时，Oracle 会将字符串按照当前数据库的字符集进行处理，从而避免中文乱码的问题，如果还有中文乱码，就需要检查数据库的字符集设置或者调整应用程序的字符编码设置。

在 Oracle 中使用 || 拼接字符串时就不受每次只能拼接两个字符串的限制：

1
2
3
SELECT (tb_person.first_name || tb_person.last_name) full_name  FROM t1;
-- 除此之外，这还常用于模糊查询中
SELECT * FROM t1 WHERE name LIKE '%' || '默烦' || '%';

再补充一点：在 MySQL 中使用 CONCAT() 函数拼接多个字符串时，如果其中一个是 NULL，那么最终的值也是 NULL，解决这个问题可以使用 CONCAT_WS() 函数进行字符串拼接。

MySQL 的内置函数与分类

MySQL 提供了丰富的内置函数，其提供的内置函数从实现的功能角度可以分为数值函数、字符串函数、日期和时间函数、流程控制函数、加密与解密函数、获取MySQL信息函数、聚合函数等。

这些丰富的内置函数可以再分为两类：单行函数、聚合函数（或分组函数）。

其中单行函数：

1、操作数据对象

2、接受参数并返回一个结果

3、只对一行进行变换

4、每行返回一个结果

5、可以嵌套

6、参数可以是一列或一个值

6.2 数值函数

基本函数

函数 用法
ABS(x) x 的绝对值
SIGN(x) x 的符号。正数返回 1，负数返回 -1，0 返回 0
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 的平方根，负数时，返回 NULL 
1
2
3
4
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

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

数值基本函数的使用-2

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;

数值基本函数的使用-3

角度与弧度互换函数

函数 用法
RADIANS(x) 将角度转化为弧度，x 为角度值
DEGREES(x) 将弧度转化为角度，x 为弧度值 
1
SELECT RADIANS(30), RADIANS(60), RADIANS(90), DEGREES(2 * PI()), DEGREES(RADIANS(90)) FROM DUAL;

角度与弧度互换函数的使用

三角函数

函数 用法
SIN(x) x 的正弦值，x 为弧度值
ASIN(x) x 的反正弦值，即获取正弦为 x 的值。若 x 的值不在 -1 到 1 之间，返回 NULL
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 为弧度值

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) 函数则仍然可以计算。

ATAN2(m, n) 函数的使用示例如下：

1
2
3
SELECT 
	SIN(RADIANS(30)), DEGREES(ASIN(1)), TAN(RADIANS(45)), DEGREES(ATAN(1)), DEGREES(ATAN2(1, 1))
FROM DUAL;

三角函数的使用

指数与对数

函数 用法
POW(x, y)、POWER(x, y) x 的 y 次方
EXP(x) 自然对数 e 的 x 次方
LN(x)、LOG(x) 以自然对数 e 为底的 x 的对数，x <= 0 时，返回的结果为 NULL
LOG10(x) 以 10 为底的 x 的对数，x <= 0 时，返回的结果为 NULL
LOG2(x) 以 2 为底的 x 的对数，x <= 0 时，返回 NULL 
1
SELECT POW(2, 5), POWER(2, 4), EXP(2), LN(10), LOG10(10), LOG2(4) FROM DUAL;

指数与对数函数的使用

进制间的转换

函数 用法
BIN(x) x 的二进制编码，x 是十进制数
HEX(x) x 的十六进制编码，x 是十进制数
OCT(x) x 的八进制编码，x 是十进制数
CONV(x, f1, f2) f1 进制数变成 f2 进制数 
1
SELECT BIN(10), HEX(10), OCT(10), CONV(10, 2, 8) FROM DUAL;

进制函数的使用

6.3 字符串函数

函数 用法
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 开始的。

1
2
3
4
5
SELECT FIELD('mm', 'hello', 'msm', 'amma'),FIND_IN_SET('mm', 'hello,mm,amma') FROM DUAL; 
-- 0 2

SELECT NULLIF('mysql', 'mysql'), NULLIF('mysql', ''); 
-- NULL mysql

有时或许需要在模糊搜索时忽略查询条件的大小写，比如在忽略大小写的情况下查询 user_info 表中 name 包含 Mofan 的信息，查询语句可以这么写：

1
2
3
SELECT * FROM user_info WHERE UPPER(name) LIKE '%MOFAN%';
-- 或者
SELECT * FROM user_info WHERE LOWER(name) LIKE '%mofan%';

6.4 日期和时间函数

获取日期、时间

函数 用法
CURDATE()、CURRENT_DATE() 返回当前日期，只包含年、月、日
CURTIME()、CURRENT_TIME() 返回当前时间，只包含时、分、秒
NOW()、SYSDATE()、CURRENT_TIMESTAMP()、LOCALTIME()、LOCALTIMESTAMP() 返回当前系统日期和时间
UTC_DATE() 返回UTC（世界标准时间）日期
UTC_TIME() 返回UTC（世界标准时间）时间 
1
2
SELECT CURDATE(), CURTIME(), NOW(), SYSDATE() + 0, UTC_DATE(), UTC_DATE() + 0, UTC_TIME(), UTC_TIME() + 0
FROM DUAL;

获取时间和日期函数的使用

日期与时间戳的转换

函数 用法
UNIX_TIMESTAMP() 以 UNIX 时间戳的形式返回当前时间
UNIX_TIMESTAMP(date) 将时间 date 以 UNIX 时间戳的形式返回
FROM_UNIXTIME(timestamp) 将 UNIX 时间戳的时间转换为普通格式的时间 
1
2
3
4
5
6
SELECT 
	UNIX_TIMESTAMP(now()), 
	UNIX_TIMESTAMP(CURDATE()), 
	UNIX_TIMESTAMP(CURTIME()), 
	UNIX_TIMESTAMP('2011-11-11 11:11:11'), 
	FROM_UNIXTIME(1320981071);

日期与时间戳转换函数的使用

获取月份、星期、星期数、天数

函数 用法
YEAR(date)、MONTH(date)、DAY(date) 返回具体的日期值
HOUR(time)、MINUTE(time)、SECOND(time) 返回具体的时间值
MONTHNAME(date) 返回月份，如 January
DAYNAME(date) 返回星期几，如 MONDAY
WEEKDAY(date) 返回周几，如周一表示 0
QUARTER(date) 返回日期对应的季度，范围 1 到 4
WEEK(date)、WEEKOFYEAR(date) 返回一年中的第几周
DAYOFYEAR(date) 返回日期是一年中的第几天
DAYOFMONTH(date) 返回日期位于所在月份的第几天
DAYOFWEEK(date) 返回周几，如周日表示 1

日期的操作函数

函数 用法
EXTRACT(type FROM date) 返回指定日期中特定的部分，type 指定返回的值

type 的取值与含义：

type 取值 含义
MICROSECOND 毫秒数
SECOND 秒数
MINUTE 分钟数
HOUR 小时数
DAY 天数
WEEK 日期在一年中的第几个星期
MONTH 日期在一年中的第几个月
QUARTER 日期在一年中的第几个季度
YEAR 日期的年份

除上述的基本取值外，它们之间还可以两两组合成以下值：

1
2
3
4
5
6
7
8
9
10
11
SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR
YEAR_MONTH
1
2
3
4
SELECT 
	EXTRACT(MINUTE FROM NOW()), EXTRACT( WEEK FROM NOW()),
	EXTRACT(QUARTER FROM NOW()), EXTRACT( MINUTE_SECOND FROM NOW())
FROM DUAL;

时间和秒数的转换

函数 用法
TIME_TO_SEC(time) 将 time 转化为秒并返回结果值。转化的公式为：小时 * 3600 + 分钟 * 60 + 秒。
SEC_TO_TIME(seconds) 将 seconds 描述转化为包含小时、分钟和秒的时间 
1
SELECT TIME_TO_SEC("11:11:11"), SEC_TO_TIME(40271);

计算日期和时间的函数

函数 用法
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 时间间隔的日期

type 的取值：

type 取值 含义
HOUR 小时
MINUTE 分钟
SECOND 秒
YEAR 年
MONTH 月
DAY 日
YEAR_MONTH 年和月
DAY_HOUR 日和小时
DAY_MINUTE 日和分钟
DAY_SECOND 日和秒
HOUR_MINUTE 小时和分钟
HOUR_SECOND 小时和秒
MINUTE_SECOND 分钟和秒 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT 
	DATE_ADD(NOW(), INTERVAL 1 DAY) AS col1,
	DATE_ADD('2011-11-11 11:11:11', INTERVAL 1 SECOND) AS col2,
	ADDDATE('2011-11-11 11:11:11', INTERVAL 1 SECOND) AS col3,
	DATE_ADD('2011-11-11 11:11:11', 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;

SELECT 
	DATE_SUB('2011-11-11', INTERVAL 31 DAY) AS col1,
	SUBDATE('2011-11-11', INTERVAL 31 DAY) AS col2,
	DATE_SUB('2011-11-11 11:11:11', INTERVAL '1 1' DAY_HOUR) AS col3
FROM DUAL;
函数 用法
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（月数） 后的时间，time 格式 yyyyMM 
1
2
3
4
5
SELECT
	ADDTIME(NOW(), 20), SUBTIME(NOW(), 30), SUBTIME(NOW(), '1:1:3'), DATEDIFF(NOW(), '2011-11-11'),
	TIMEDIFF(NOW(), '2011-11-11 11:11:11'), FROM_DAYS(366), TO_DAYS('0000-12-25'),
	LAST_DAY(NOW()), MAKEDATE(YEAR(NOW()), 12), MAKETIME(10, 21, 23), PERIOD_ADD(201111, 10)
FROM DUAL;

日期的格式化和解析

函数 用法
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 进行解析，解析为一个日期

上表中 fmt 参数常用的格式符：

格式符 说明 格式符 说明
%Y 4 位数字，表示年份 %y 两位数字，表示年份
%M 月名表示月份，如 January %m 两位数字表示月份，如 01、02 等
%b 缩写的月名，如 Jan %c 数字，表示月份，如 1、2 等
%D 英文后缀表示月中的天数，如 1st %d 两位数字，表示月中的天数，如 01
%e 数字形式，表示月中的天数，如 1
%H 两位数字表示 24 小时制，如 01 %h、%I 两位数字表示 12 小时制，如 01
%k 数字形式的 24 小时制，如 1 %l 数字形式的 12 小时制，如 1
%i 两位数字，表示分钟，如 00、01 %S、%s 两位数字表示秒，如 00、01
%W 一周中的星期名称，如 Sunday %a 一周中的星期缩写，如 Sun、Mon
%w 以数字表示周中的天数，如 0 表示周日
%j 以三位数字表示年中的天数，如 001 %U 以数字表示年中的第几周，如 1
%u 以数字表示年中的第几周，如 1
%T 24 小时制 %r 12 小时制
%p AM 或 PM %% 表示 %

GET_FORMAT 函数中 date_type 和 format_type 参数取值如下：

GET_FORMAT函数中的参数信息

1
2
3
4
5
6
7
8
9
10
11
SELECT DATE_FORMAT(NOW(), '%H:%i:%s');

SELECT STR_TO_DATE('11/11/2011','%m/%d/%Y') FROM DUAL;

SELECT STR_TO_DATE('20111111111111','%Y%m%d%H%i%s') FROM DUAL;

SELECT STR_TO_DATE('2011-11-11 11:11:11','%Y-%m-%d %H:%i:%s') FROM DUAL;

SELECT GET_FORMAT(DATE, 'USA');

SELECT STR_TO_DATE('2011-11-11 00:00:00','%Y-%m-%d');

6.5 流程控制函数

流程处理函数可以根据不同的条件，执行不同的处理流程，可以在 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 结果n] END 类似 if…else if…else…
CASE expr WHEN 常量值1 THEN 值1 WHEN 常量值1 THEN 值1 … [ELSE 结果n] END 类似 switch…case… 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT IF(1 > 0, '正确', '错误'); -- 正确
SELECT IFNULL(null, 'Hello Word'); -- Hello Word
-- 1 > 0
SELECT CASE 
	WHEN 1 > 0 THEN '1 > 0' 
	WHEN 2 > 0 THEN '2 > 0'
	ELSE '3 > 0' 
END;
-- yes
SELECT CASE WHEN 1 > 0 THEN 'yes' WHEN 1 <= 0 THEN 'no' ELSE 'unknown' END;
-- unknown
SELECT CASE WHEN 1 < 0 THEN 'yes' WHEN 1 = 0 THEN 'no' ELSE 'unknown' END;
-- 1
SELECT CASE 1 WHEN 0 THEN 0 WHEN 1 THEN 1 ELSE -1 END;
-- -1
SELECT CASE -1 WHEN 0 THEN 0 WHEN 1 THEN 1 ELSE -1 END;

实际使用：

1
2
3
4
5
6
7
8
9
SELECT employee_id,12 * salary * (1 + IFNULL(commission_pct,0)) FROM employees;

SELECT last_name, job_id, salary, 
	CASE job_id 
	WHEN 'IT_PROG' THEN 1.10 * salary 
	WHEN 'ST_CLERK' THEN 1.15 * salary
	WHEN 'SA_REP' THEN 1.20 * salary
	ELSE salary END "REVISED_SALARY"
FROM employees;

6.6 加密与解密函数

加密与解密函数主要用于对数据库中的数据进行加密和解密处理，以防止数据被他人窃取。这些函数在保证数据库安全时非常有用。

函数 用法
PASSWORD(str) 返回字符串 str 的加密版本，41 位长的字符串。加密结果不可逆，常用于用户的密码加密
MD5(str) 返回字符串 str 的 md5 加密后的值，也是一种加密方式。若参数为 NULL，则会返回 NULL
SHA(str) 从原明文密码 str 计算并返回加密后的密码字符串，当参数为 NULL 时，返回 NULL。比 MD5 更安全。
ENCODE(value, password_seed) 返回使用 password_seed 作为加密密码加密 value
DECODE(value, password_seed) 返回使用 password_seed 作为加密密码解密 value 
1
2
3
4
5
6
7
-- MySQL8 中已经移除 PASSWORD 函数，可以使用 SHA1、SHA2 等函数代替
SELECT PASSWORD('mysql'), PASSWORD(NULL);
SELECT MD5('mofan');
SELECT SHA('mofan');
-- MySQL8 中已经移除 ENCODE 和 DECODE 函数，可以使用以下函数代替
SELECT AES_ENCRYPT('mysql', 'mysql');
SELECT AES_DECRYPT(AES_ENCRYPT('mysql','mysql'),'mysql');

6.7 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 的比较规则 
1
2
3
4
5
SELECT VERSION();
SELECT DATABASE();
SELECT USER(), CURRENT_USER(), SYSTEM_USER(),SESSION_USER();
SELECT CHARSET('mofan'); -- utf8mb4
SELECT COLLATION('mofan'); -- utf8mb4_0900_ai_ci

6.8 其他函数

还有一些无法进行具体分类的函数，但在日常开发和运维过程中也是不容忽视的。

函数 用法
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
2
3
4
5
6
7
8
9
10
11
12
-- 123.12 124 123
SELECT FORMAT(123.123, 2), FORMAT(123.523, 0), FORMAT(123.123, -2);
-- 10000 22B8 NULL
SELECT CONV(16, 10, 2), CONV(8888,10,16), CONV(NULL, 10, 2);
-- 2130706433
SELECT INET_ATON('127.0.0.1');
-- 127.0.0.1
SELECT INET_NTOA(2130706433);

SELECT BENCHMARK(1000000, MD5('mysql'));
-- utf8mb4 utf8
SELECT CHARSET('mysql'), CHARSET(CONVERT('mysql' USING 'utf8'));

对 FORMAT(value, n) 函数来说，如果 n 的值小于或等于 0，则只保留整数部分。

INET_ATON(ipvalue) 函数的计算方法：

1
2130706433 = 127 * 256^3 + 0 * 256^2 + 0 * 256^1 + 1 * 256^0

7. 聚合函数

7.1 什么是聚合函数

聚合函数作用于一组数据，并对这组数据进行计算后返回一个值。比如：AVG()、SUM()、MAX()、MIN()、COUNT() 等函数都是聚合函数。

聚合函数的语法：

1
2
3
4
5
SELECT		[column1, ] group_function(column2), ....
FROM		table
[WHERE		condition]
[GROUP BY	column3]
[ORDER BY	column4];

聚合函数不能嵌套调用， 因此不能出现类似 AVG(SUM(字段名称)) 的调用。

7.2 简单的聚合函数

AVG() 和 SUM()

可以对 数值型数据 使用AVG 和 SUM 函数。前者表示求平均值，后者表示求和。

1
2
3
SELECT AVG(salary), SUM(salary)
FROM	employees
WHERE job_id LIKE '%REP%';

MIN() 和 MAX()

可以对 任意数据类型 的数据使用 MIN 和 MAX 函数。前者表示求最小值，后者表示求最大值。

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

COUNT()

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

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

COUNT(expr) 返回 expr 不为空 的记录总数。

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

有关 COUNT() 的经典问题

1、使用 COUNT(*)、COUNT(1)、COUNT(columnName) 中的哪一种更好？

答：对于 MyISAM 引擎的表来说是没有区别的，这种引擎内部有一个计数器在维护着行数。对于 Innodb 引擎的表来说，用 COUNT(*) 和 COUNT(1) 会直接读行数，复杂度是O(n)，因为它真的会去数一遍，但这两种都好于具体的 COUNT(columnName)。

2、能不能使用 COUNT(columnName) 替换 COUNT(*)？

答：不能使用 COUNT(columnName) 替代 COUNT(*)，COUNT(*) 是 SQL92 定义的标准统计行数的语法，跟数据库无关，跟 NULL 或非 NULL 无关，它会统计值为 NULL 的行，而 COUNT(columnName) 不会统计列值为 NULL 的行。

7.3 GROUP BY

顾名思义，就是分组。使用 GROUP BY 可以将拥有相同值的某些列进行合并以达到分组的效果。

基本使用

基本语法：

1
2
3
4
5
SELECT column1, group_function(column)
FROM table
[WHERE  condition]
[GROUP BY   group_by_expression]
[ORDER BY   column1];

注意： 在 SELECT 列表中所有未包含在聚合函数中的列都应该包含在 GROUP BY 子句中。

比如，根据部门 ID 求出该部门的平均工资：

1
2
3
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id;

但是反过来就不一定了，包含在 GROUP BY 子句中的列 不必 包含在 SELECT 列表中。

同样是求出部门的平均工资：

1
2
3
SELECT AVG(salary)
FROM employees
GROUP BY department_id;

使用多个列进行分组

除了对某一列进行分组，还可以对多个列进行分组，在这种情况下，分组的列值组合唯一。

比如：

1
2
3
SELECT department_id dept_id, job_id, SUM(salary)
FROM employees
GROUP BY department_id, job_id;

GROUP BY 中使用 WITH ROOLUP

使用 WITH ROLLUP 关键字之后，在所有查询出的分组记录之后会增加 一条记录（是一条记录，不是一列），该记录计算查询出的所有记录的总和，即统计记录数量。

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

GROUP-BY中使用WITH-ROLLUP

针对新增的这一行数据，可以使用 COALESCE 让其可读性提高：

1
2
3
4
SELECT COALESCE(department_id, '平均总工资'), AVG(salary)
FROM employees
WHERE department_id > 80
GROUP BY department_id WITH ROLLUP;

COALESCE的使用

注意： 当使用 ROLL UP时，不能再使用 ORDER BY子句对结果进行排序，它们是互斥的。

补充：WITH 子句（MySQL 8.0 新增）

使用 WITH 子句可以为子查询的数据集建立内存临时表（CTEs），数据在内存中解析，执行效率更高，并且能提高 SQL 的可读性，临时表用完即销毁。

基本语法：

1
2
3
4
WITH 
	sub_table1 AS (SELECT A ...),
	sub_table2 AS (SELECT B ...)
SELECT ...

使用 WITH 子句可以让子查询重用相同的 WITH 查询块，WITH 子句只能被 SELECT 查询块引用。WITH 子句在引用的 SELECT 语句前定义，同级的 WITH 关键字只能使用一次，WITH 子句中可以建立多个内存临时表，它们用逗号分割。

如果定义的内存临时表未被使用，则会报错。这个使用不一定非要在主查询中使用，也可以在其他当前 WITH 子句的其他临时表中使用。

一个 WITH 子句内部不能嵌套 WITH 子句。

WITH 子句定义的内存临时表一旦被 SELECT 语句使用，这些临时表就会被销毁。

1
2
3
4
5
6
7
WITH temp_table as (
    SELECT CONCAT_WS(' ', first_name, last_name) `name`, salary
    FROM employees
    WHERE salary > 15000
)
SELECT `name`, salary
FROM temp_table;

如果再查询 temp_table 则会提示 table or view does not exist。

7.4 HAVING

针对使用 GROUP BY 进行分组后的数据，还可以使用 HAVING 进行过滤。

要点：

1、数据已被分组；

2、使用了聚合函数；

3、满足 HAVING 子句中条件的分组都将被显示；

4、HAVING 不能单独使用，必须要跟 GROUP BY 一起使用。

比如：查询部门最高工资比 10000 高的部门。

1
2
3
4
SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary) > 10000;

注意： 不能在 WHERE 子句中使用聚合函数。比如下面这样的 SQL 语句就是错误的：

1
2
3
4
SELECT department_id, AVG(salary)
FROM employees
WHERE AVG(salary) > 8000
GROUP BY department_id;

WHERE 与 HAVING 的对比

区别一： WHERE 可以直接使用表中的字段作为筛选条件，但不能使用分组中的计算函数作为筛选条件；HAVING 必须要与 GROUP BY 配合使用，可以把分组计算的函数和分组字段作为筛选条件。

这决定了，在需要对数据进行分组统计时，HAVING 可以完成 WHERE 不能完成的任务。这是因为在查询语法结构中，WHERE 在 GROUP BY 之前，所以无法对分组结果进行筛选。HAVING 在 GROUP BY 之后，可以使用分组字段和分组中的计算函数，对分组的结果集进行筛选，这个功能是 WHERE 无法完成的。另外，WHERE 排除的记录不再包括在分组中。

区别二： 如果需要通过连接从关联表中获取需要的数据，WHERE 是先筛选后连接，而 HAVING 是先连接后筛选。 在关联查询中，WHERE 比 HAVING 更高效。

因为 WHERE 可以在筛选后用一个较小数据集和关联表进行连接，占用资源少，执行效率高。HAVING 则需要先把结果集准备好，即用未被筛选的数据集进行关联，并对这个大的数据集进行筛选，相应地占用的资源更多，执行效率也更低。

小结：

优点 缺点
WHERE 先筛选再关联，执行效率高 不能使用分组中的计算函数进行筛选
HAVING 可以使用分组中的计算函数 先关联再筛选，执行效率较低

怎么选择：

WHERE 和 HAVING 也不是互相排斥的，我们可以在一个查询里面同时使用 WHERE 和 HAVING。包含分组统计函数的条件用 HAVING，普通条件用 WHERE。当数据量特别大的时候，运行效率会有很大的差别。

7.5 SELECT 的执行过程

查询的结构

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
#方式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、编写 SQL 语句时使用的关键词顺序不能颠倒。

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

2、SELECT 语句的执行顺序（MySQL 与 Oracle 基本相同）：

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

比如下面这条 SQL 语句的执行顺序是这样的：

1
2
3
4
5
6
7
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 的执行原理

首先执行 FROM。在这个阶段，如果是多张表联查，还会经历下面的几个步骤：

1、首先通过 CROSS JOIN 求笛卡尔积，相当于得到虚拟表 vt（virtual table）1-1；

2、通过 ON 进行筛选，在虚拟表 vt1-1 的基础上进行筛选，得到虚拟表 vt1-2；

3、添加外部行。如果我们使用的是左连接、右链接或者全连接，就会涉及到外部行，也就是在虚拟表 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 语句未包含某些关键字时，对应的阶段会被省略。

8. 子查询

8.1 子查询概括

子查询从 MySQL 4.1 引入，指的是一个查询语句嵌套在另一个查询语句内部的查询。在很多时候查询需要从结果集中获取数据，或者需要从同一个表中先计算出一个结果，然后使用这个结果作为另一个查询语句的条件，那么这是就可以用到子查询。

实际问题

假设需要查询出工资比 Abel 高的员工信息，那么首先肯定得先查询出 Abel 的工资才行。现有如下三种解决方案：

1、分步查询：

1
2
3
4
-- 先查询出 Abel 的工资
SELECT salary FROM employees WHERE last_name = 'Abel';
-- 使用上一步的查询结果作为查询条件，获取符合条件的员工信息
SELECT last_name, salary FROM employees WHERE salary > 11000;

2、自连接：

1
2
3
4
SELECT e2.last_name, e2.salary
FROM employees e1, employees e2
WHERE e1.last_name = 'Abel'
AND e1.`salary` < e2.`salary`;

3、子查询：

1
2
3
4
5
6
7
SELECT last_name,salary
FROM employees
WHERE salary > (
    SELECT salary
    FROM employees
    WHERE last_name = 'Abel'
);

子查询的基本使用

基本语法：

1
2
3
4
5
SELECT select_list
FROM tableName
WHERE expr opereator (
    SELECT select_list FROM tableName
);

内查询在外查询之前一次执行完成，内查询的查询结果被外查询使用。

注意：

  • 内查询要被括号包裹；
  • 将内查询放在比较条件的右侧；
  • 单行操作符对应单行子查询，多行操作符对应多行子查询。

子查询的分类

分类方式一：根据内查询的返回结果数量可以将子查询分为单行子查询和多行子查询。

分类方式二：根据内查询是否被执行多次可以将子查询分为相关（或关联）子查询和不相关（非关联）子查询。

不相关子查询：只执行一次内查询就从数据表中查询出结果数据，并将结果数据作为外查询的条件执行外查询。

相关子查询：如果内查询需要执行多次，即采用循环的方式，先从外查询开始，每次都传入子查询进行查询，然后再将结果返回给外部。

8.2 单行子查询

单行比较操作符

操作符
=
>
>=
<
<=
<>

查询示例

查询工资大于 149 号员工工资的员工信息：

1
2
3
4
5
6
7
SELECT last_name
FROM employees
WHERE salary > (
    SELECT salary           
    FROM employees    
    WHERE employee_id = 149
);

返回 job_id 与 141 号员工相同，但工资比 143 号员工多的员工姓名、job_id 和工资：

1
2
3
4
5
6
7
8
9
10
11
12
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：

1
2
3
4
5
6
SELECT last_name, job_id, salary
FROM employees
WHERE salary = ( 
    SELECT MIN(salary)
    FROM employees
);

查询与 141 号或 174 号员工的 manager_id 和 department_id 相同的其他员工的 employee_id、manager_id、department_id：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- 不成对比较
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);

HAVING 中的子查询

查询最低工资大于 50 号部门最低工资的部门 id 和其最低工资：

1
2
3
4
5
6
7
8
SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) > ( 
    SELECT MIN(salary) 
    FROM employees 
    WHERE department_id = 50
);

CASE 中的子查询

查询员工的 employee_id、last_name 和 location。若员工 department_id 与 location_id 为 1800 的 department_id 相同，则 location 为 Canada，反之为 USA。

1
2
3
4
5
6
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;

子查询中的空值

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

其中，内查询不会得到任何查询结果，那么整个查询语句也不会返回任何行。

非法使用子查询

如果像下面这样在多行子查询中使用单行比较符，那么就会报错：

1
2
3
4
5
6
7
SELECT employee_id, last_name
FROM employees
WHERE salary = (
    SELECT MIN(salary) 
    FROM employees 
    GROUP BY department_id
);

报错内容如下：

Subquery returns more than 1 row

8.3 多行子查询

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

多行比较操作符

操作符 含义
IN 等于列表中的 任意一个
ANY 需要和单行比较操作符一起使用，和内查询返回的 某一个 值比较
ALL 需要和单行比较操作符一起使用，和内查询返回的 所有 值比较
SOME ANY 的别名，但常使用 ANY

使用示例

查询比任一 job_id 为 IT_PROG 的员工工资低的员工号、姓名、job_id 以及 salary：

1
2
3
4
5
6
7
8
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary < ANY (
    SELECT salary
    FROM employees
    WHERE job_id = 'IT_PROG'
)
AND job_id <> 'IT_PROG';

查询比所有 job_id 为 IT_PROG 的员工工资低的员工号、姓名、job_id 以及 salary：

1
2
3
4
5
6
7
8
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary < ALL (
    SELECT salary
    FROM employees
    WHERE job_id = 'IT_PROG'
)
AND job_id <> 'IT_PROG';

查询平均工资最低的部门 id：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- 方式一
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
);
-- 方式二
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) <= ALL (
    SELECT AVG(salary) avg_sal
    FROM employees
    GROUP BY department_id
)

空值处理

1
2
3
SELECT last_name
FROM employees
WHERE employee_id NOT IN (SELECT manager_id FROM employees);

与单行子查询一样，内查询未得到任何查询结果时，整个查询语句也不会返回任何行。

8.4 相关子查询

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

相关子查询按照一行接一行的顺序执行，主查询的每一行都执行一次子查询。

相关子查询执行流程

基本语法：

相关子查询的基本语法

使用示例

查询员工中工资大于本部门平均工资的员工的 last_name、salary 和 department_id：

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 相关子查询
SELECT last_name, salary, department_id
FROM employees e
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
    WHERE department_id = e.department_id
);
-- 在 FROM 中使用子查询
SELECT last_name, salary, e1.department_id
FROM employees e1, (SELECT department_id, AVG(salary) dept_avg_sal FROM employees GROUP BY department_id) e2
WHERE e1.`department_id` = e2.department_id
AND e2.dept_avg_sal < e1.`salary`;

备注：FROM 型的子查询中，子查询是作为 FROM 的一部分，子查询需要使用 () 引起来，并且要给整个子查询取别名，把它的查询结果当成一张临时的虚拟表来使用。

查询员工的 id 和 salary，并按照 department_name 排序：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 使用子查询
SELECT employee_id,salary
FROM employees e
ORDER BY (
    SELECT department_name 
    FROM departments d 
    WHERE e.`department_id` = d.`department_id`
);
-- 使用外连接（这也是我们首先能想到的）
SELECT employee_id, salary
FROM employees e
LEFT JOIN departments d
ON e.department_id = d.department_id
ORDER BY d.department_name;

若 employees 表中 employee_id 与 job_history 表中 employee_id 相同的数目不小于 2，那么输出这些相同 id 的员工的 employee_id、last_name 和 job_id：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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
);
-- 同样可以使用外连接
SELECT e.employee_id, last_name, e.job_id
FROM employees e
LEFT JOIN job_history j
ON e.employee_id = j.employee_id
GROUP BY e.employee_id
HAVING COUNT(j.employee_id) >= 2;

到此不难看出在 SELECT 查询语句中，除了 GROUP BY 和 LIMIT 之外，其他位置都可以声明子查询。

EXISTS 和 NOT EXISTS 关键字

关联子查询通常也会和 EXISTS 关键词一起使用，用来检查在子查询中是否存在满足条件的行。

如果在子查询中不存在满足条件的行，条件返回 FALSE，并继续在子查询中寻找；如果在子查询中存在满足条件的行，条件返回 TRUE，并终止在子查询中的寻找。

比如：查询公司管理者的 employee_id、last_name、job_id 和 department_id 信息。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- 使用子查询
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 employee_id, last_name, job_id, department_id
FROM employees
WHERE employee_id IN (
    SELECT DISTINCT manager_id
    FROM employees
);
-- 使用自连接                     
SELECT DISTINCT e1.employee_id, e1.last_name, e1.job_id, e1.department_id
FROM employees e1 JOIN employees e2
WHERE e1.employee_id = e2.manager_id;

再比如：查询 departments 表中，不存在于 employees 表中的部门的 department_id 和 department_name。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 子查询
SELECT department_id, department_name
FROM departments d
WHERE NOT EXISTS (
    SELECT 'X'
    FROM employees
    WHERE department_id = d.department_id
);
-- 左外连接
SELECT d.department_id, department_name
FROM departments d
LEFT JOIN employees e
ON d.department_id = e.department_id
WHERE e.department_id IS NULL;

相关更新

在更新表中数据时，也可以使用相关子查询依据一个表中的数据去更新另一个表中的数据。

基本语法：

1
2
3
4
5
6
UPDATE table1 alias1
SET column = (
    SELECT expression 
    FROM table2 alias2 
    WHERE alias1.column = alias2.column
);

比如：在 employees 表中增加一个 department_name 字段，其值为员工对应的部门名称。

1
2
3
4
5
6
7
8
9
10
-- 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
);

相关删除

在删除表中数据时，也可以使用相关子查询依据一个表中的数据去删除另一个表中的数据。

基本语法：

1
2
3
4
5
6
DELETE FROM table1 alias1
WHERE column operator (
    SELECT expression 
    FROM table2 alias2 
    WHERE alias1.column = alias2.column
);

比如：删除表 employees 中与 emp_history 表皆有的数据。

1
2
3
4
5
6
DELETE FROM employees e
WHERE employee_id in (
    SELECT employee_id 
    FROM emp_history 
    WHERE employee_id = e.employee_id
);

8.5 最后的问题

在本节开篇就抛出了一个实际问题，即查询出工资比 Abel 高的员工信息，而后使用自连接和子查询的方式求出结果。不仅如此，在本篇很多的示例中用到子查询的地方也都可以使用连接的方式求得结果，那么自连接和子查询哪一种更好呢？

答案是 自连接更好。

在许多 DBMS 的处理过程中，对于自连接的处理速度要比子查询快得多。

子查询实际上是通过未知表进行查询后的条件判断，而自连接是通过已知的自身数据表进行条件判断，因此在大部分 DBMS 中都对自连接处理进行了优化。

9. 【补充】隐式转换

以 MySQL 8.0 为例：

当操作符与不同类型的操作数一起使用时，就会发生隐式转换。

比如 SELECT '5a' + 1; 的结果是 6，其原因就是 5a 经过隐式转换变成了 5。

字符串到数字的隐式转换规则是：

  1. 从字符串的左侧开始向右转换，遇到非数字就停止；
  2. 如果第一个字符就是非数字，最后的结果是 0。

MySQL 官方文档关于隐式转换的几条规则

  • 两个参数中至少有一个是 NULL 时，比较的结果总是 NULL；使用 <=> 对两个为 NULL 的参数进行比较时，结果返回 1；

  • 对都是字符串或整数的参数进行比较时，不进行隐式转换，按照原本类型进行比较；

  • 十六进制的值和非数字做比较时，十六进制的值会被当成二进制字符串，比如 SELECT 0x61; 的结果是 a；

  • 有一个参数类型是 TIMESTAMP 或 DATETIME，另外一个参数是常量时，常量会被转换为时间戳；

  • DECIMAL 和整数进行比较时，会将整数转换为 DECIMAL；DECIMAL 和浮点数 DOUBLE 进行比较时，会将 DECIMAL 转换为浮点数，也就是说，不同数值类型之间的比较总是会向精度要求更高的那一个进行类型转换。MySQL 中浮点数的精度只有 53 bit， 超过 53 bit 后，如果后面 1 位是 1 则进位，是 0 则舍弃，所以超大浮点数在比较时只是取的近似值。

  • 其他情况下，两个参数会被转换为浮点数再进行比较；

应该尽量避免隐式转换

日常开发过程中，应该尽量避免隐式转换。

如果在一个字段上添加了索引，但在经过隐式转换后，这个索引会失效，走全表扫描，导致查询性能下降。

除此之外，还可能出现不期望的结果，甚至导致数据库崩溃。