——第5版

MYSQL版

第一章 了解SQL

• 主键：表中每一行都应该有一列（或几列）可以唯一标识自己。

• 主键可以是一列构成，也可以是几列共同构成。
• 唯一 非空 不允许修改

第二章 检索数据

• 关于通配符：一般而言，除非你确实需要表中的每一列，否则最好别使用 * 通配符。
• 检索不同的值：DISTINCT

• 不能部分使用 DISTINCT , DISTINCT 关键字作用于所有的列，不仅仅是跟在其后的那一列。
• DISTINCT 需要放到所有列名的前面，如果写成 SELECT salary, DISTINCT department_id
  FROM employees 会报错。
• DISTINCT 其实是对后面所有列名的组合进行去重。

• 限制结果：LIMIT

• 注意：LIMIT 2 OFFSET 3 等价于 LIMIT 3, 2

• 空值参与运算

• 所有运算符或列值遇到 null 值，运算的结果都为null
• 这里一定要注意，在 MySQL 里面， 空值不等于空字符串。一个空字符串的长度是 0，而一个空值的 长度是空。而且，在 MySQL 里面，空值是占用空间的。

• 空运算符 和 非空运算符

SELECT * FROM `om_fnd_company_user` where phone IS NULL

SELECT * FROM `om_fnd_company_user` where phone IS NOT NULL

第三章 排序

• 排序：如果想在多个列上进行降序排序，必须对 **每一列 **指定 DESC 关键字。

第四章 过滤数据

• 范围：BETWEEN 5 AND 10 表示闭区间

SELECT id, name, age FROM student WHERE id BETWEEN 5 AND 10;

• 空值

SELECT id, name, age FROM student WHERE age IS NULL;

• 非空值

SELECT id, name, age FROM student WHERE age IS NOT NULL;

第五章 高级数据过滤

• 注意：任何时候具有 AND 和 OR 操作符的 WHERE 子句，都应该使用圆括号将加以区分。
  
• NOT：在 WHERE 子句中，用来否定其后条件的顺序。
  

SELECT id, name, age FROM student WHERE NOT id = 8; 

• WHERE NOT

SELECT name FROM stu WHERE NOT age < 50;

• 分组函数 AVG(), MAX(), MIN(), SUM() 会忽略 NULL 值。
  
• COUNT(*) 与 COUNT(字段)

• COUNT(*) 会统计所有列
• COUNT(字段) 会统计非 NULL

第六章 多表查询

要避免笛卡尔积

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

内连接（交集）

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

基本语法：

SELECT table1.column, table2.column,table3.column
FROM table1
JOIN table2 ON table1 和 table2 的连接条件
JOIN table3 ON table2 和 table3 的连接条件（有疑惑）

举例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;

外连接

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

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

SELECT e.last_name, e.department_id, d.department_name
FROM employees e
LEFT OUTER JOIN departments d   -- OUTER可以省略
ON (e.department_id = d.department_id) ;

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

SELECT e.last_name, e.department_id, d.department_name
FROM employees e
RIGHT OUTER JOIN departments d -- OUTER可以省略
ON (e.department_id = d.department_id) ;

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

多表连接

UNION

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

SELECT * FROM user WHERE did = 2
UNION
SELECT * FROM user WHERE did = 1 

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

与 OR 有啥区别：UNION 效率高，对于 OR 表连接来说，每连接一次新表则匹配的次数满足笛卡尔积。但是 UNION 可以减少匹配的次数。

UNION ALL

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

union和union all的差异主要是前者需要将结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的CPU运算，加大资源消耗及延迟。

当然，union all的前提条件是两个结果集没有重复数据。所以一般是我们明确知道不会出现重复数据的时候才建议使用 union all 提高速度。

第七章 单行函数

日期和时间函数

获取日期、时间

图文并茂的举个栗子：

日期与时间戳的转换

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

日期的操作函数

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

举个栗子：

时间和秒钟的转换函数

计算日期和时间的函数

上述函数中type的取值：

日期的格式与解析

上述 非GET_FORMAT 函数中fmt参数常用的格式符：

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

流程控制函数

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

图文并茂的举个栗子：

//todo

第八章 聚合函数

AVG()

SUM()

MAX()

MIN()

COUNT()

** 问题**：用 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 值的行。

GROUP BY

分组

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 ;

除聚合函数语句外，SELECT 语句中的每一列都必须在 GROUP BY 字句中给出。

HAVING

过滤分组

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

注意： 不能在 WHERE 子句中使用聚合函数。SQL中where子句中不能出现聚合函数的原因_这孩子谁懂哈的博客-CSDN博客_where 关键字无法与聚合函数一起使用 ？？？

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 可以使用包含分组统计函数的查询条件的优点。当数据量特别大的时候，运行效率会有很大的差别。

插入（特殊）

插入检索出来的数据

INSERT INTO stu(name, age) SELECT name, age FROM user;

复制表

CREATE TABLE user AS SELECT * FROM student;

删除表

1.delete:删除表的数据，支持条件过滤，支持回滚。这个删除操作会记录在日志中，所以比较慢。

delete from table_stu;

2.truncate:删除表的所有数据，不支持条件过滤，也不支持回滚，不记录日志，效率比delete高。

truncate table table_stu;

3.drop：删除表的数据的同时，还删除表结构。将表所占的空间都释放掉。他的删除效率最高。

drop table table_stu;

SELECT 的执行过程

查询的结构

#方式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：分页

SELETC 执行顺序

关键字顺序

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

语句执行顺序

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 的执行过程中，对于我们来说是不可见的。

SQL 执行原理

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

1. 首先先通过 CROSS JOIN 求笛卡尔积，相当于得到虚拟表 vt（virtual table）1-1；
2. 通过 ON 进行筛选，在虚拟表 vt1-1 的基础上进行筛选，得到虚拟表 vt1-2；
3. 添加外部行。如果我们使用的是左连接、右链接或者全连接，就会涉及到外部行，也就是在虚拟表
   vt1-2 的基础上增加外部行，得到虚拟表 vt1-3。

当然如果我们操作的是两张以上的表，还会重复上面的步骤，直到所有表都被处理完为止。这个过程得到是我们的原始数据。

4. 当我们拿到了查询数据表的原始数据，也就是最终的虚拟表 vt1 ，就可以在此基础上再进行 WHERE 阶 段 。在这个阶段中，会根据 vt1 表的结果进行筛选过滤，得到虚拟表 vt2 。
5. 然后进入第三步和第四步，也就是 GROUP 和 HAVING 阶段 。在这个阶段中，实际上是在虚拟表 vt2 的 基础上进行分组和分组过滤，得到中间的虚拟表 vt3 和 vt4 。
6. 当我们完成了条件筛选部分之后，就可以筛选表中提取的字段，也就是进入到 SELECT 和 DISTINCT 阶段 。 首先在 SELECT 阶段会提取想要的字段，然后在 DISTINCT 阶段过滤掉重复的行，分别得到中间的虚拟表 vt5-1 和 vt5-2 。
7. 当我们提取了想要的字段数据之后，就可以按照指定的字段进行排序，也就是 ORDER BY 阶段 ，得到虚拟表 vt6 。 最后在 vt6 的基础上，取出指定行的记录，也就是 LIMIT 阶段 ，得到最终的结果，对应的是虚拟表 vt7 。

当然我们在写 SELECT 语句的时候，不一定存在所有的关键字，相应的阶段就会省略。

同时因为 SQL 是一门类似英语的结构化查询语言，所以我们在写 SELECT 语句的时候，还要注意相应的 关键字顺序，所谓底层运行的原理，就是我们刚才讲到的执行顺序。

SQL语句在MySQL中的执行过程

第九章 子查询

自连接 效率 高于 子查询

第十章 修改表结构

追加一个列

ALTER TABLE sys_log ADD `before_params` varchar(2000) 
  DEFAULT NULL COMMENT '请求前的参数' AFTER `class_method`;  

修改一个列

ALTER TABLE sys_log MODIFY `before_params` varchar(2000) 
  DEFAULT NULL COMMENT '请求前的参数' AFTER `class_method`;  

重命名一个列名

ALTER TABLE uesr CHANGE department_name dept_name varchar(15);

删除一个列

ALTER TABLE sys_log DROP 字段名

使用经验

创建时间获取当前时间

create_time datetime DEFAULT CURRENT_TIMESTAMP() COMMENT '创建时间'
create_time datetime DEFAULT CURRENT_DATE() COMMENT '创建日期'

更新时间获取当前时间

update_time datetime DEFAULT CURRENT_TIMESTAMP() 
  ON UPDATE CURRENT_TIMESTAMP() COMMENT '更新时间' 

SELECT 出现在其他位置

• 出现在 FROM 后

SELECT e.ename 
FROM EMP e
JOIN (SELECT MAX(sal) AS msal, deptno FROM emp GROUP BY deptno) s
ON e.deptno = s.deptno AND e.sal = s.msal;

• 出现在 SELECT 后 (可以将子查询的结果当成是一个临时表)

• 显示用户姓名和其部门名称

SELECT e.ename, (SELECT dname FROM dept WHERE deptno = e.deptno) FROM  emp e;

Other

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 等。

数据导入

在命令行客户端登录mysql，使用source指令导入

mysql> source d:\mysqldb.sql

修改数据库字符集

ALTER DATABASE db3 DEFAULT CHARACTER SET utf8;

DDL DML DQL DCL

SQL是用来存取关系数据库的语言，具有定义、操纵、控制和查询关系型数据库的四方面功能。所以针对四方面功能，我们将SQL进行了分类。

1. DDL(Data Definition Language)数据定义语言用来定义数据库对象：数据库，表，列等。关键字：create drop alter truncate(清空数据记录) show等
2. DML(Data Manipulation Language)数据操作语言★★★
   在数据库表中更新，增加和删除记录。如 update(更新)， insert(插入)， delete(删除) 不包含查询
3. DQL(Data Query Language) 数据查询语言★★★★★
   数据表记录的查询。关键字select。
4. DCL(Data Control Language)数据控制语言(了解)是用来设置或更改数据库用户或角色权限的语句，如grant(设置权限)，revoke(撤销权限)，begin transaction等。这个比较少用到。