1 关系型数据库基本概念
一个数据库中可以有多个表，每个表都有一个名字，用来标识自己。表名具有唯一性。表具有一些特
性，这些特性定义了数据在表中如何存储，类似Java和Python中 “类”的设计。
1.1 表、记录、字段
E-R（entity-relationship，实体-联系）模型中有三个主要概念是： 实体集 、 属性 、 联系集 。
一个实体集（class）对应于数据库中的一个表（table），一个实体（instance）则对应于数据库表中的一行（row），也称为一条记录（record）。一个属性（attribute）对应于数据库表中的一列（column），也称为一个字段（field）。
数据库中的一个表对应Java中的一个类
表中的一条数据对应类中的一个对象（或实体）
表中的一个列对应类中的一个字段、属性(field)
1.2 表的关联关系
表与表之间的数据记录有关系。现实世界中的各种实体以及实体之间的各种联系均用关系模型来表示。共有三种：一对一关联、一对多关联、多对多关联
1.2.1 一对一关联
两种建表原则：
外键唯一：主表的主键和从表的外键（唯一），形成主外键关系，外键唯一。
外键是主键：主表的主键和从表的主键，形成主外键关系。
方式1：外键表主键和主键表主键建立约束
方式2：外键表的唯一索引和主键表的主键建立约束
1.2.2 一对多关联
一对多建表原则：在从表(多方)创建一个字段，字段作为外键指向主表(一方)的主键。
例如：部门和员工一对多关系
1.2.3 多对多关联
要表示多对多关系，必须创建第三个表，该表通常称为联接表 ，它将多对多关系划分为两个一对多关系。将这两个表的主键都插入到第三个表中。
例如：员工和职务的多对多关系
2 SQL语言
SQL：Structure Query Language（结构化查询语言），SQL被美国国家标准局（ANSI）确定为关系型数据库语言的美国标准，后来被国际化标准组织（ISO）采纳为关系数据库语言的国际标准。SQL 是一种标准化的语言，它允许你在数据库上执行操作，如创建项目，查询内容，更新内容，并删除条目等操作。
2.1 SQL语句分类
（1）DDL（Data Definition Language）：数据定义语言，用来定义数据库对象：库、表、列等。
- 主要的语句关键字包括 CREATE 、 DROP 、 ALTER 等。
（2）DML（Data Manipulation Language）：数据操作语言，用来定义数据库记录（数据）增删改。
- 主要的语句关键字包括 INSERT 、 DELETE 、 UPDATE 、 SELECT 等，SELECT是SQL语言的基础，最为重要。
（3）DCL（Data Control Language）：数据控制语言，用来定义访问权限和安全级别。
- 主要的语句关键字包括 GRANT 、 REVOKE 、 COMMIT 、 ROLLBACK 、 SAVEPOINT 等。
（4）DQL（Data Query Language）：数据查询语言，用来查询记录（数据）查询。
（5）TCL （Transaction Control Language，事务控制语言），单独将 COMMIT 、 ROLLBACK 取出来称为TCL
2.2 SQL语言的规则与规范
SQL 可以写在一行或者多行。为了提高可读性，各子句分行写，必要时使用缩进 每条命令以 ; 或 \g 或 \G 结束。
关键字不能被缩写也不能分行。
关于标点符号：
- 必须保证所有的()、单引号、双引号是成对结束的。
- 必须使用英文状态下的半角输入方式。
- 字符串型和日期时间类型的数据可以使用单引号（’ '）。
- 表示列的别名，尽量使用双引号（" "），而且不建议省略as。
关于大小写：
- MySQL 在 Windows 环境下是大小写不敏感的。
- MySQL 在 Linux 环境下是大小写敏感的。
- 数据库名、表名、表的别名、变量名是严格区分大小写的。
- 关键字、函数名、列名(或字段名)、列的别名(字段的别名) 是忽略大小写的。
推荐采用统一的书写规范：
- 数据库名、表名、表别名、字段名、字段别名等都小写。
- SQL 关键字、函数名、绑定变量等都大写。
关于注释：
- 单行注释：#注释文字(MySQL特有的方式)
- 单行注释：-- 注释文字(–后面必须包含一个空格)
- 多行注释：/ 注释文字 /
2.3 命名规则
数据命名规范，可以参考阿里开发规约(嵩山版)，基本规则如下：
- 数据库、表名不得超过30个字符，变量名限制为29个。
- 必须只能包含 A–Z, a–z, 0–9, _共63个字符。
- 数据库名、表名、字段名等对象名中间不要包含空格。
- 同一个MySQL软件中，数据库不能同名；同一个库中，表不能重名；同一个表中，字段不能重名必须保证你的字段没有和保留字、数据库系统或常用方法冲突。如果坚持使用，请在SQL语句中使用`（着重号）引起来。
- 保持字段名和类型的一致性，在命名字段并为其指定数据类型的时候一定要保证一致性。假如数据类型在一个表里是整数，那在另一个表里可就别变成字符型了。
3 基本的select语句
3.1 select … from
（1）语法
SELECT 列名
FROM 表名
（2）不带from子句
select 1;
select 9/2;
select 1 from dual;
select null = null; # null，所以规定使用is null判断Null
SELECT * FROM employees WHERE manager_id IS NULL;
（3）选择所有列
select * from 表名;
一般情况下，除非需要使用表中所有的字段数据，最好不要使用通配符‘*’。使用通配符虽然可以节省输入查询语句的时间，但是获取不需要的列数据通常会降低查询和所使用的应用程序的效率。通配符的优势是，当不知道所需要的列的名称时，可以通过它获取它们。在生产环境下，不推荐你直接使用 SELECT * 进行查询。
（4）选择特定的列
SELECT department_id, department_name FROM departments;
MySQL中的SQL语句是不区分大小写的，因此SELECT和select的作用是相同的，但是，许多开发人员习惯将关键字大写、数据列和表名小写，所以我们也应该养成一个良好的编程习惯，这样写出来的代码更容易阅读和维护。
3.2 列的别名
- 重命名一个列
- 便于计算
- 紧跟列名，也可以在列名和别名之间加入关键字AS，别名使用双引号，以便在别名中包含空格或特殊的字符并区分大小写
- AS 可以省略
- 建议别名简短，见名知意
SELECT last_name AS name FROM employees;
SELECT last_name name FROM employees;
SELECT last_name "name" FROM employees;
3.3 去除重复行
默认情况下，查询会返回全部行，包括重复行。
在SELECT语句中使用关键字DISTINCT去除重复行
SELECT DISTINCT department_id FROM employees;
多列去重
SELECT DISTINCT department_id,salary FROM employees;
注意：DISTINCT 其实是对后面所有列名的组合进行去重
3.4 空值参与运算
所有运算符或列值遇到null值，运算的结果都为null
select null + 1; # null
select null >= 1; # null;
select null = null; # null
SELECT commission_pct FROM employees WHERE commission_pct = NULL; # 错误写法
SELECT commission_pct FROM employees WHERE commission_pct is NULL;
注意：在 MySQL 里面， 空值不等于空字符串。一个空字符串的长度是 0，而一个空值的长度是空。而且，在 MySQL 里面，空值是占用空间的。
3.5 着重号（`）
错误示范：
SELECT * FROM ORDER
> 1064 - 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
正确示范：
SELECT * FROM `ORDER`;
结论：我们需要保证表中的字段、表名等没有和保留字、数据库系统或常用方法冲突。如果真的相同，请在SQL语句中使用一对" `` "（着重号）引起来。
3.6 查询常数
SELECT 查询还可以对常数进行查询，就是在 SELECT 查询结果中增加一列固定的常数列，这列的取值是我们指定的，而不是从数据表中动态取出的。
如果我们想整合不同的数据源，用常数列作为这个表的标记，就需要查询常数。比如说，我们想对 employees 数据表中的员工姓名进行查询，同时增加一列字段 corporation ，这个字段固定值为“kong”，可以这样写：
SELECT 'kong' as corporation, last_name FROM employees;
3.7 过滤条件
语法
SELECT 字段1,字段2
FROM 表名
WHERE 过滤条件
示例：
SELECT employee_id, last_name, job_id, department_id
FROM employees
WHERE department_id = 90;
结果：
3.8 显示表结构
语法：
DESCRIBE employees;
或 DESC employees;
结果：
其中，各个字段的含义分别解释如下：
- Field：表示字段名称。
- Type：表示字段类型。
- Null：表示该列是否可以存储NULL值。
- Key：表示该列是否已编制索引。PRI表示该列是表主键的一部分；UNI表示该列是UNIQUE索引的一部分；MUL表示在列中某个给定值允许出现多次。
- Default：表示该列是否有默认值，如果有，那么值是多少。
- Extra：表示可以获取的与给定列有关的附加信息，例如AUTO_INCREMENT等。
4 运算符
4.1 算术运算符
算术运算符主要用于数学运算，其可以连接运算符前后的两个数值或表达式，对数值或表达式进行加（+）、减（-）、乘（*）、除（/）和取模（%）运算。
4.2 比较运算符
比较运算符用来对表达式左边的操作数和右边的操作数进行比较，比较的结果为真则返回1，比较的结果为假则返回0，其他情况则返回NULL。
比较运算符经常被用来作为SELECT查询语句的条件来使用，返回符合条件的结果记录。
4.3 逻辑运算符
逻辑运算符主要用来判断表达式的真假，在MySQL中，逻辑运算符的返回结果为1、0或者NULL。MySQL中支持4种逻辑运算符如下：
4.4 位运算
位运算符是在二进制数上进行计算的运算符。位运算符会先将操作数变成二进制数，然后进行位运算，最后将计算结果从二进制变回十进制数。MySQL支持的位运算符如下：
5 排序与分页
5.1 排序数据
使用 ORDER BY 子句排序，ORDER BY 子句在SELECT语句的结尾。
- ASC（ascend）: 升序
- DESC（descend）：降序
示例：
SELECT salary
FROM employees
ORDER BY salary Asc
结果：
多列排序：
SELECT last_name, department_id, salary
FROM employees
ORDER BY department_id, salary DESC;
结果：
在 department_id 相同的情况下再根据 salary 进行降序排序
5.2 分页
语法：
MySQL中使用 LIMIT 实现分页
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;
分页显式公式：（当前页数-1）*每页条数，每页条数
SELECT * FROM table LIMIT (PageNo - 1)*PageSize,PageSize;
6 多表查询
多表查询，也称为关联查询，指两个或更多个表一起完成查询操作。
前提条件：这些一起查询的表之间是有关系的（一对一、一对多），它们之间一定是有关联字段，这个关联字段可能建立了外键，也可能没有建立外键。比如：员工表和部门表，这两个表依靠“部门编号”进行关联。
6.1 笛卡尔积（交叉连接）
笛卡尔乘积是一个数学运算。假设我有两个集合 X 和 Y，那么 X 和 Y 的笛卡尔积就是 X 和 Y 的所有可能组合，也就是第一个对象来自于 X，第二个对象来自于 Y 的所有可能。组合的个数即为两个集合中元素个数的乘积数。
示例：
# 查询员工姓名和所在部门名称
SELECT last_name,department_name FROM employees,departments;
为了避免笛卡尔积， 可以在 WHERE 加入有效的连接条件。
# 查询员工的姓名及其部门名称
SELECT last_name, department_name
FROM employees, departments
WHERE employees.department_id = departments.department_id;
6.2 多表关联的分类
6.2.1 等值连接和非等值连接
1.等值连接
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;
按照阿里规约规定，多表关联应该强制使用别名
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;
2.非等值连接
使用 BETWEEN 和 AND
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;
6.2.2 自连接和非自连接
1. 自连接
从一张表中查询
示例：
SELECT worker.last_name worker,manager.last_name manager
FROM employees worker, employees manager
WHERE worker.manager_id = manager.employee_id ;
结果：
2. 非自连接
不同张表查询
示例：
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;
6.2.3 内连接和外连接
除了查询满足条件的记录以外，外连接还可以查询某一方不满足条件的记录。
- 内连接: 合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行
- 外连接: 两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行 ，这种连接称为左（或右） 外连接。没有匹配的行时, 结果表中相应的列为空(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语法实现多表查询
语法：
SELECT table1.column, table2.column,table3.column
FROM table1
JOIN table2 ON table1 和 table2 的连接条件
JOIN table3 ON table2 和 table3 的连接条件
- 可以使用 ON 子句指定额外的连接条件。
- 这个连接条件是与其它条件分开的。
- ON 子句使语句具有更高的易读性。
- 关键字 JOIN、INNER JOIN、CROSS JOIN 的含义是一样的，都表示内连接
6.3.1 内连接(INNER JOIN)
语法：
SELECT 字段列表
FROM A表 INNER JOIN B表
ON 关联条件
WHERE 等其他子句;
示例1:
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);
示例2：
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;
6.3.2 左外连接(LEFT OUTER JOIN)
语法：
SELECT 字段列表
FROM A表 LEFT JOIN B表
ON 关联条件
WHERE 等其他子句;
示例：
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) ;
左外连接会返回左表中的所有行，以及与右表中匹配的行。 即使在某些情况下员工没有所在的部门也能获取到信息。即查询左边表所有数据（包括满足条件和不满足条件的数据）
6.3.3 右外连接(RIGHT OUTER JOIN)
语法：
SELECT 字段列表
FROM A表 RIGHT JOIN B表
ON 关联条件
WHERE 等其他子句;
示例：
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) ;
右外连接会返回右表中的所有行，以及与左表中匹配的行。 即使在部门没有员工也能获取到信息。即查询右边表所有数据（包括满足条件和不满足条件的数据）
6.3.4 满外连接(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 关键字分隔。
语法：
SELECT column,... FROM table1
UNION [ALL]
SELECT column,... FROM table2
1. UNION操作符
UNION 操作符返回两个查询的结果集的并集，去除重复记录。
2. UNION ALL操作符
UNION ALL操作符返回两个查询的结果集的并集。对于两个结果集的重复部分，不去重。
注意：执行UNION ALL语句时所需要的资源比UNION语句少。如果明确知道合并数据后的结果数据不存在重复数据，或者不需要去除重复的数据，则尽量使用UNION ALL语句，以提高数据查询的效率。
示例：查询部门编号>90或邮箱包含a的员工信息
# 方式1
SELECT * FROM employees WHERE email LIKE '%a%' OR department_id>90;
# 方式2
SELECT * FROM employees WHERE email LIKE '%a%'
UNION
SELECT * FROM employees WHERE department_id>90;
6.5 SQL JOINS的7种实现
# 图1：左外连接
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;
# 图2：右外连接
SEL CT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;
# 图3：内连接 A∩B
SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`;
# 图4：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
# 图5：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
# 图6：满外连接
# 图2 + 图4 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`;
# 图7
# 图4 + 图4 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
7 单行函数
7.1 函数的理解
函数在计算机语言的使用中贯穿始终，函数的作用是什么呢？它可以把我们经常使用的代码封装起来，需要的时候直接调用即可。这样既提高了代码效率 ，又提高了可维护性 。在 SQL 中我们也可以使用函数对检索出来的数据进行函数操作。使用这些函数，可以极大地 提高用户对数据库的管理效率 。
从函数定义的角度出发，我们可以将函数分成 内置函数 和 自定义函数 。在 SQL 语言中，同样也包括了内置函数和自定义函数。内置函数是系统内置的通用函数，而自定义函数是我们根据自己的需要编写的。
7.1.1 不同DBMS函数的差异
我们在使用 SQL 语言的时候，不是直接和这门语言打交道，而是通过它使用不同的数据库软件，即DBMS。DBMS 之间的差异性很大，远大于同一个语言不同版本之间的差异。实际上，只有很少的函数是被 DBMS 同时支持的。比如，大多数 DBMS 使用（||）或者（+）来做拼接符，而在 MySQL 中的字符串拼接函数为concat()。大部分 DBMS 会有自己特定的函数，这就意味着采用 SQL 函数的代码可移植性是很差的，因此在使用函数的时候需要特别注意。
7.1.2 MySQL的内置函数及分类
MySQL提供了丰富的内置函数，这些函数使得数据的维护与管理更加方便，能够更好地提供数据的分析与统计功能，在一定程度上提高了开发人员进行数据分析与统计的效率。
MySQL提供的内置函数从实现的功能角度可以分为数值函数、字符串函数、日期和时间函数、流程控制函数、加密与解密函数、获取MySQL信息函数、聚合函数等。可以将这些丰富的内置函数再分为两类： 单行函数 、 聚合函数（或分组函数） 。
7.2 字符串函数
| 函数 | 用法 |
|---|---|
| ASCII(S) | 返回字符串S中的第一个字符的ASCII码值 |
| CHAR_LENGTH(s) | 返回字符串s的字符数。作用与CHARACTER_LENGTH(s)相同 |
| LENGTH(s) | 返回字符串s的字节数，和字符集有关 |
| CONCAT(s1,s2,…,sn) | 连接s1,s2,…,sn为一个字符串 |
| CONCAT_WS(x,s1,s2,…,sn) | 同CONCAT(s1,s2,…)函数，但是每个字符串之间要加上x |
| INSERT(str, idx, len,replacestr) | 将字符串str从第idx位置开始，len个字符长的子串替换为字符串replacestr |
| REPLACE(str, a, b) | 用字符串b替换字符串str中所有出现的字符串a |
| UPPER(s) 或 UCASE(s) | 将字符串s的所有字母转成大写字母 |
| LOWER(s) 或LCASE(s) | 将字符串s的所有字母转成小写字母 |
| LEFT(str,n） | 返回字符串str最左边的n个字符 |
| RIGHT(str,n) | 返回字符串str最右边的n个字符 |
| LPAD(str, len, pad) | 用字符串pad对str最左边进行填充，直到str的长度为len个字符 |
| RPAD(str ,len, pad) | 用字符串pad对str最右边进行填充，直到str的长度为len个字符 |
| LTRIM(s) | 去掉字符串s左侧的空格 |
| RTRIM(s) | 去掉字符串s右侧的空格 |
| TRIM(s) | 去掉字符串s开始与结尾的空格 |
| TRIM(s1 FROM s) | 去掉字符串s开始与结尾的s1 |
| TRIM(LEADING s1 FROM s) | 去掉字符串s开始处的s1 |
| TRIM(TRAILING s1 FROM s) | 去掉字符串s结尾处的s1 |
| REPEAT(str, n) | 返回str重复n次的结果 |
| SPACE(n) | 返回n个空格 |
| STRCMP(s1,s2) | 比较字符串s1,s2的ASCII码值的大小 |
| SUBSTR(s,index,len) | 返回从字符串s的index位置其len个字符，作用与SUBSTRING(s,n,len)、 MID(s,n,len)相同 |
| LOCATE(substr,str) | 返回字符串substr在字符串str中首次出现的位置，作用与POSITION(substr IN str)、INSTR(str,substr)相同。未找到，返回0 |
| ELT(m,s1,s2,…,sn) | 返回指定位置的字符串，如果m=1，则返回s1，如果m=2，则返回s2，如 果m=n，则返回sn |
| FIELD(s,s1,s2,…,sn) | 返回字符串s在字符串列表中第一次出现的位置 |
| FIND_IN_SET(s1,s2) | 返回字符串s1在字符串s2中出现的位置。其中，字符串s2是一个以逗号分隔的字符串 |
| REVERSE(s) | 返回s反转后的字符串 |
| NULLIF(value1,value2) | 比较两个字符串，如果value1与value2相等，则返回NULL，否则返回value1 |
7.3 日期和时间函数
7.3.1 获取日期，时间
| 函数 | 用法 |
|---|---|
| CURDATE() ，CURRENT_DATE() | 返回当前日期，只包含年、 月、日 |
| CURTIME() ， CURRENT_TIME() | 返回当前时间，只包含时、 分、秒 |
| NOW() / SYSDATE() / CURRENT_TIMESTAMP() / LOCALTIME() /LOCALTIMESTAMP() | 返回当前系统日期和时间 |
| UTC_DATE() | 返回UTC（世界标准时间） 日期 |
| UTC_TIME() | 返回UTC（世界标准时间） 时间 |
7.3.2 日期与时间戳的转换
| 函数 | 用法 |
|---|---|
| UNIX_TIMESTAMP() | 以UNIX时间戳的形式返回当前时间 |
| UNIX_TIMESTAMP(date) | 将时间date以UNIX时间戳的形式返回 |
| FROM UNIXTIME(timestamp) | 将UNIX时间戳的时间转换为普通格式的时间 |
7.3.3 获取月份、星期、星期数、天数等函数
| 函数 | 用法 |
|---|---|
| YEAR(date) / MONTH(date) / DAY(date) | 返回具体的日期值 |
| HOUR(time) / MINUTE(time) /SECOND(time) | 返回具体的时间值 |
| MONTHNAME(date) | 返回月份：January，… |
| DAYNAME(date) | 返回星期几：MONDAY，TUESDAY…SUNDAY |
| WEEKDAY(date) | 返回周几，注意，周1是0，周2是1，…，周日是6 |
| QUARTER(date) | 返回日期对应的季度，范围为1～4 |
| WEEK(date) ， WEEKOFYEAR(date) | 返回一年中的第几周 |
| DAYOFYEAR(date) | 返回日期是一年中的第几天 |
| DAYOFMONTH(date) | 返回日期位于所在月份的第几天 |
| DAYOFWEEK(date) | 返回周几，注意：周日是1，周一是2，…，周六是7 |
7.3.4 日期的操作函数
| 函数 | 用法 |
|---|---|
| EXTRACT(type FROM date) | 返回指定日期中特定的部分，type指定返回的值 |
EXTRACT(type FROM date)函数中type的取值与含义 ：
7.3.5 时间和秒钟转换的函数
| 函数 | 用法 |
|---|---|
| TIME_TO_SEC(time) | 将 time 转化为秒并返回结果值。转化的公式为： 小时3600+分钟60+秒 |
| SEC_TO_TIME(seconds) | 将 seconds 描述转化为包含小时、分钟和秒的时间 |
7.3.6 计算日期和时间的函数
| 函数 | 用法 |
|---|---|
| DATE_ADD(datetime, INTERVAL expr type)，ADDDATE(date,INTERVAL expr type) | 返回与给定日期时间相差INTERVAL时间段的日期时间 |
| DATE_SUB(date,INTERVAL expr type)，SUBDATE(date,INTERVAL expr type) | 返回与date相差INTERVAL时间间隔的日期 |
上述函数中type的取值：
| 函数 | 用法 |
|---|---|
| ADDTIME(time1,time2) | 返回time1加上time2的时间。当time2为一个数字时，代表的是秒 ，可以为负数 |
| SUBTIME(time1,time2) | 返回time1减去time2后的时间。当time2为一个数字时，代表的是秒 ，可以为负数 |
| DATEDIFF(date1,date2) | 返回date1 - date2的日期间隔天数 |
| TIMEDIFF(time1, time2) | 返回time1 - time2的时间间隔 |
| FROM_DAYS(N) | 返回从0000年1月1日起，N天以后的日期 |
| TO_DAYS(date) | 返回日期date距离0000年1月1日的天数 |
| LAST_DAY(date) | 返回date所在月份的最后一天的日期 |
| MAKEDATE(year,n) | 针对给定年份与所在年份中的天数返回一个日期 |
| MAKETIME(hour,minute,second) | 将给定的小时、分钟和秒组合成时间并返回 |
| PERIOD_ADD(time,n) | 返回time加上n后的时间 |
7.3.7 日期的格式化与解析
| 函数 | 用法 |
|---|---|
| DATE_FORMAT(date,fmt) | 按照字符串fmt格式化日期date值 |
| TIME_FORMAT(time,fmt) | 按照字符串fmt格式化时间time值 |
| GET_FORMAT(date_type,format_type) | 返回日期字符串的显示格式 |
| STR_TO_DATE(str, fmt) | 按照字符串fmt对str进行解析，解析为一个日期 |
上述非GET_FORMAT 函数中fmt参数常用的格式符：
| 格式符 | 说明 | 格式符 | 说明 |
|---|---|---|---|
| %Y | 4位数字表示年份 | %y | 表示两位数字表示年份 |
| %M | 月名表示月份（January,…） | %m | 两位数字表示月份（01,02,03…） |
| %b | 缩写的月名（Jan.，Feb.，…） | %c | 数字表示月份（1,2,3,…） |
| %D | 英文后缀表示月中的天数（1st,2nd,3rd,…） | %d | 两位数字表示月中的天数(01,02…) |
| %H | 两位数字表示小数，24小时制（01,02…） | %h和%I | 两位数字表示小时，12小时制（01,02…） |
| %W | 一周中的星期名称（Sunday…） | %w | 以数字表示周中的天数 (0=Sunday,1=Monday…) |
7.4 数值函数
7.4.1 基本函数
7.4.2 三角函数
7.4.3 角度与弧度互换函数
7.4.4 三角函数
7.4.5 指数和对数
7.4.6 进制间的转换
7.5 流程控制函数
流程处理函数可以根据不同的条件，执行不同的处理流程，可以在SQL语句中实现不同的条件选择。MySQL中的流程处理函数主要包括IF()、IFNULL()和CASE()函数。
7.6 加密与解密函数
加密与解密函数主要用于对数据库中的数据进行加密和解密处理，以防止数据被他人窃取。这些函数在保证数据库安全时非常有用。
7.7 MySQL信息函数
MySQL中内置了一些可以查询MySQL信息的函数，这些函数主要用于帮助数据库开发或运维人员更好地对数据库进行维护工作。
8 聚合函数
8.1 介绍
聚合函数作用于一组数据，并对一组数据返回一个值。
常见的数据库聚合函数包括：
- COUNT()：计算满足条件的行数。
- SUM()：计算指定列的总和。
- AVG()：计算指定列的平均值。
- MAX()：求指定列的最大值。
- MIN()：求指定列的最小值。
问题：用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 值的行。
8.2 GROUP BY
GROUP BY子句是用于在数据库查询中按照一个或多个列对结果进行分组的语句。它将查询结果根据指定的列值进行分类，并对每个组应用聚合函数进行计算。
语法：
SELECT column1, column2, ..., 聚合函数(column)
FROM table
WHERE condition
GROUP BY column1, column2, ...
使用GROUP BY子句时，查询结果将按照GROUP BY子句中指定的列进行分组，并且每个组都会生成一个结果行。聚合函数将针对每个组计算聚合结果，并将其作为结果行的一部分返回。
8.2.1 基本使用
示例：
有一个名为orders的表，其中包含以下列：order_id、customer_id、order_date和order_total。我们希望按照customer_id对订单进行分组，并计算每个客户的订单总额。
表中数据如下：
通过GROUP BY子句查询：
SELECT customer_id, SUM(order_total) AS total_order_amount
FROM orders
GROUP BY customer_id;
查询结果：
这个查询返回每个客户的customer_id和他们的订单总额。结果集中的每一行代表一个客户的汇总信息。
8.2.2 多个列进行分组
示例：
SELECT customer_id, YEAR(order_date) AS order_year, SUM(order_total) AS total_order_amount
FROM orders
GROUP BY customer_id, order_year;
查询结果：
这个查询返回每个客户每年的订单总额，结果集中的每一行表示一个客户在特定年份的订单总额。
8.2.3 GROUP BY中使用WITH ROLLUP
使用 WITH ROLLUP 关键字之后，在所有查询出的分组记录之后增加一条记录，该记录计算查询出的所有记录的总和，即统计记录数量。
示例：
SELECT customer_id, SUM(order_total) AS total_order_amount
FROM orders
GROUP BY customer_id WITH ROLLUP;
结果：
WITH ROLLUP 子句在结果中添加一个汇总行，该行将包含所有客户的聚合结果。
8.3 HAVING
HAVING子句是SQL中用于在查询结果中对分组后的数据进行筛选的语句。它类似于WHERE子句，但WHERE用于筛选行，而HAVING用于筛选分组。
HAVING子句通常与GROUP BY子句一起使用，用于对分组后的结果应用筛选条件。它允许你根据聚合函数的结果或分组列的值来过滤结果。
基本语法：
SELECT column1, column2, ..., 聚合函数(column)
FROM table
WHERE condition
GROUP BY column1, column2, ...
HAVING condition;
8.3.1 基本使用
示例：
还是上面介绍GROUP BY时的orders表，其中包含以下列：order_id、customer_id、order_date和order_total。我们想要找出订单总额超过100的客户，并计算他们的订单总额。
表中数据如下：
查询示例：
SELECT customer_id, SUM(order_total) AS total_order_amount
FROM orders
GROUP BY customer_id
HAVING total_order_amount > 100;
查询结果：
这个查询将按照customer_id对订单进行分组，并计算每个客户的订单总额。然后，HAVING子句将筛选出订单总额超过100的客户。只有满足条件的客户将包含在查询结果中。
8.3.2 WHERE和HAVING的对比
区别1：WHERE 可以直接使用表中的字段作为筛选条件，但不能使用分组中的计算函数作为筛选条件；HAVING 必须要与 GROUP BY 配合使用，可以把分组计算的函数和分组字段作为筛选条件。
这决定了在需要对数据进行分组统计的时候，HAVING 可以完成 WHERE 不能完成的任务。这是因为在查询语法结构中，WHERE 在 GROUP BY 之前，所以无法对分组结果进行筛选。HAVING 在GROUP BY 之后，可以使用分组字段和分组中的计算函数，对分组的结果集进行筛选，这个功能是WHERE 无法完成的。另外，WHERE排除的记录不再包括在分组中。
区别2：如果需要通过连接从关联表中获取需要的数据，WHERE 是先筛选后连接，而 HAVING 是先连接后筛选。 这一点，就决定了在关联查询中，WHERE 比 HAVING 更高效。
因为 WHERE 可以先筛选，用一个筛选后的较小数据集和关联表进行连接，这样占用的资源比较少，执行效率也比较高。HAVING则需要先把结果集准备好，也就是用未被筛选的数据集进行关联，然后对这个大的数据集进行筛选，这样占用的资源就比较多，执行效率也较低。
小结如下：
WHERE 和 HAVING 也不是互相排斥的，我们可以在一个查询里面同时使用 WHERE 和 HAVING。包含分组统计函数的条件用 HAVING，普通条件用 WHERE。这样，我们就既利用了 WHERE 条件的高效快速，又发挥了HAVING 可以使用包含分组统计函数的查询条件的优点。当数据量特别大的时候，运行效率会有很大的差别。
8.4 SELECT的执行过程
8.4.1 查询的结构
方式1：
SELECT ...,....,...
FROM ...,...,....
WHERE 多表的连接条件
AND 不包含组函数的过滤条件
GROUP BY ...,...
HAVING 包含组函数的过滤条件
ORDER BY ... ASC/DESC
LIMIT ...,...
方式2：
SELECT ...,....,...
FROM ... JOIN ...
ON 多表的连接条件
JOIN ...
ON ...
WHERE 不包含组函数的过滤条件
AND/OR 不包含组函数的过滤条件
GROUP BY ...,...
HAVING 包含组函数的过滤条件
ORDER BY ... ASC/DESC
LIMIT ...,...
8.4.2 SELECT执行顺序
SELECT 查询时的两个顺序：
- 关键字的顺序是不能颠倒的
SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT...
- SELECT 语句的执行顺序（在 MySQL 和 Oracle 中，SELECT 执行顺序基本相同）
FROM -> WHERE -> GROUP BY -> HAVING -> SELECT 的字段 -> DISTINCT -> ORDER BY -> LIMIT
示例：
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 的执行过程中，对于我们来说是不可见的。
SELECT 是先执行 FROM 这一步的。在这个阶段，如果是多张表联查，还会经历下面的几个步骤：
- 首先先通过 CROSS JOIN 求笛卡尔积，相当于得到虚拟表 vt（virtual table）1-1；
- 通过 ON 进行筛选，在虚拟表 vt1-1 的基础上进行筛选，得到虚拟表 vt1-2；
- 添加外部行。如果我们使用的是左连接、右链接或者全连接，就会涉及到外部行，也就是在虚拟表vt1-2 的基础上增加外部行，得到虚拟表 vt1-3。
9 子查询
子查询指一个查询语句嵌套在另一个查询语句内部的查询，这个特性从MySQL 4.1开始引入。SQL 中子查询的使用大大增强了 SELECT 查询的能力，因为很多时候查询需要从结果集中获取数据，或者需要从同一个表中先计算得出一个数据结果，然后与这个数据结果（可能是某个标量，也可能是某个集合）进行比较。
以下是子查询的一些常见用法：
- 在WHERE子句中使用子查询： 子查询可以用于在WHERE语句中提供一个条件，根据内部查询的结果来过滤外部查询的结果。例如，你可以使用子查询找出某个表中满足特定条件的行。
- 在SELECT子句中使用子查询： 子查询可以用于SELECT语句中的列，以将外部查询的每一行与子查询的结果进行比较。这通常用于计算聚合值或从另一个表中检索数据。
- 在FROM子句中使用子查询： 子查询可以嵌套在FROM语句中，作为一个临时表，供外部查询使用。这种情况下，子查询可以执行一些复杂的操作，然后外部查询基于其结果执行进一步操作。
- 在HAVING子句中使用子查询： 类似于在WHERE子句中的用法，子查询可以用于HAVING语句中，以根据汇总后的结果过滤数据。
9.1 基本使用
语法：
SELECT column1, column2, ...
FROM table1
WHERE column_name operator (SELECT column_name
FROM table_name
WHERE condition);
子查询（内查询）在主查询之前一次执行完成，子查询的结果被主查询（外查询）使用 。
注意事项
- 子查询要包含在括号内
- 将子查询放在比较条件的右侧
- 单行操作符对应单行子查询，多行操作符对应多行子查询
示例：
假设有一个数据库中有两个表：Customers 和 Orders。我们想要找出每个客户的订单数量。
SELECT CustomerName, (
SELECT COUNT(*)
FROM Orders
WHERE Orders.CustomerID = Customers.CustomerID
) AS OrderCount
FROM Customers;
在这个例子中，子查询嵌套在SELECT语句中，用于计算每个客户的订单数量。在外部查询中，我们选择了客户名称和子查询返回的订单数量。
9.2 子查询的分类
我们按内查询的结果返回一条还是多条记录，将子查询分为单行子查询 、多行子查询 。
9.2.1 单行子查询
单行子查询返回单个值（一个标量值），通常用于比较或作为表达式的一部分。这种子查询可以嵌套在主查询的SELECT、WHERE、HAVING、或FROM子句中。
示例：
SELECT ProductName
FROM Products
WHERE Price = (SELECT MAX(Price) FROM Products);
在这个例子中，子查询(SELECT MAX(Price) FROM Products)返回一个单一的最高价格值，然后主查询找出与该价格相匹配的产品。
HAVING中使用子查询:
查询最低工资大于50号部门最低工资（2100.00）的部门id和其最低工资
SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) >
(SELECT MIN(salary)
FROM employees
WHERE department_id = 50);
结果：
9.2.2 多行子查询
多行子查询返回多行结果集，通常用于在主查询中的IN、ANY、ALL等条件中。这种子查询也可以在主查询的FROM子句中用作临时表。
示例：
SELECT CustomerName
FROM Customers
WHERE CustomerID IN (SELECT CustomerID FROM Orders WHERE OrderDate = '2023-08-24');
在这个例子中，子查询 (SELECT CustomerID FROM Orders WHERE OrderDate = '2023-08-24') 返回订购了 ‘2023-08-24’ 的订单的顾客ID列表，然后主查询从 Customers 表中选择了相应的顾客名字。
思考问题
自连接与子查询哪个好？
自连接方式好！
可以使用子查询，也可以使用自连接。一般情况建议使用自连接，因为在许多 DBMS 的处理过程中，对于自连接的处理速度要比子查询快得多。
可以这样理解：子查询实际上是通过未知表进行查询后的条件判断，而自连接是通过已知的自身数据表进行条件判断，因此在大部分 DBMS 中都对自连接处理进行了优化。
扫一扫
4万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
