＜＜SQL必知必会＞＞读书笔记(自用)

---

第一章 了解SQL

主键：一列（或几列），其值能唯一标识表中每一行。

1. 表中每一行都应该有一列（或几列）可以唯一标识自己。
2. 主键可以是一列构成，也可以是几列共同构成。
3. 主键需要满足以下条件
   1. 唯一
   2. 非空

第二章 检索数据

2.1 检索不同的值（去重）

DISTINCT

1. 如果使用 DISTINCT 关键字，它必须直接放在列的前面。

SELECT DISTINCT user_name FROM user WHERE age > 10;

2. 不能部分使用 DISTINCT , DISTINCT 关键字作用于所有的列，不仅仅是跟在其后面的那一列。

SELECT DISTINCT user_name, city FROM user WHERE age > 10;

2.2 分页

LIMIT 关键字

2.2.1 MySQL

SELECT user_name, city FROM user WHERE age > 10 LIMIT 0,10;

SELECT user_name, city FROM user WHERE age > 10 LIMIT 10 OFFSET 0;

注意：

1. 上面两种写法效果是一样的：第一页的十行数据。
2. ‘,’ 和 ‘OFFSET’ 前后的参数意义截然相反的奥（详情见下方截图）
3. 第一个被检索的数据是第 0 行。

分割线

2.2.2 Oracle

SELECT user_name, city FROM user WHERE ROWNUM <= 5;

Oracle 三种分页方法_oracle 分页-CSDN博客

2.3 注释

-- 注释# 注释/* 
注释
*/

第三章 排序

ORDER BY
如果想在多个列上进行排序，必须对每一列指定关键字。

第四章 WHERE

4.1 BETWEEN AND 是闭区间

-- 表示 id >= 10 && id <= 20
SELECT id, name FROM user WHERE id BETWEEN 10 AND 20;

4.2 IS NULL

-- city 为空
SELECT id, name FROM user WHERE city IS NULL;

-- city 不为空
SELECT id, name FROM user WHERE city IS NOT NULL;

4.3 NOT

NOT 操作符有且只有一个功能，那就是否定其后所跟的任何条件。

SELECT id, name FROM user WHERE NOT city = 'HK';

与其有同样效果的语句如下：

SELECT id, name FROM user WHERE city != 'HK';
SELECT id, name FROM user WHERE city <> 'HK';

4.4 通配符

1. % 表示任何字符出现任意次数（0个 1个 或多个）
2. _ 表示任何字符出现一次（精准的1个）

第八章 函数

lower() 将字符型转换为小写
upper() 将字符型转换为大写

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

第九章 聚合函数

AVG() ：会忽略 NULL 值
MAX()：会忽略 NULL 值
MIN()：会忽略 NULL 值
SUM()：会忽略 NULL 值
COUNT(): COUNT(列) 会忽略NULL值，COUNT(*)不会忽略NULL值。
不能在 WHERE 子句中使用聚合函数。

第十章 分组函数

注意：

1. 在包含GROUP BY的语句中, 除了聚合函数外，SELECT 语句中的每一列都必须在 GROUP BY 子句中给出。

执行如下语句，报错如下：

SELECT resource_object_id, maintenance_id,  operator_time 
FROM procatch_work_om_log 
GROUP BY resource_object_id

2. 位置：在 WHERE 之后 ，在 ORDER BY 之前。
3. GROUP BY 后可以跟 HAVING 进行过滤数据
   1. 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 可以使用包含分组统计函数的查询条件的优点。当数据量特别大的时候，运行效率会有很大的差别。

4. GROYP BY 后不可以跟别名（执行顺序原因）

第 11,12,13,14 章 子查询&联结表

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

内连接（交集）

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

基本语法：

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 提高速度。

第十五章 插入数据

INSERT INTO user(name, age, city) VALUES ('snow', 120, '香港');

特殊插入(将查询结果插入)

INSERT INTO user(name, age, city) SELECT name, age, city FROM user WHERE ...;

从一个表复制到另一个表

CREATE TABLE user2 AS SELECT * FROM user;

第十六章 更新和删除数据

第十七章 创建表和操作表

删除表

DROP TABLE user;

清空表

TRUNCATE TABLE user;

第十八章 视图

第十九章 存储过程

第二十章 事务

BEGIN;SAVEPOINT P1;COMMIT;ROLLBACK;