《SQL 必知必会》- 阅读索引

作者: TWLESVCNZ | 来源:发表于2019-02-24 23:47 被阅读12次

阅读总结

用于了解 SQL 的基础知识还是非常好的。

在使用时可以具体看 DBMS 文档。

以下 SQL 以 cnpmjs.org 数据库为例。

[toc]

第 1 课了解 SQL

1.1 数据库基础

1.1.1 数据库

数据库（database）：保存有组织的数据的容器（通常是一个文件或一组文件）
DBMS，数据库管理系统（即数据库软件）

1.1.2 表

表（table）：某种特定类型数据的结构化清单（一种结构化的文件）
- 统一类型的数据
- 表名在数据库中唯一
模式（schema）：关于数据库和表的布局及特性的信息

1.1.3 列

列（column）：表中的一个字段
数据类型（datatype）：所允许的数据的类型，每个表列都有相应的数据类型，它限制（或允许）该列中存储的数据
- 帮助分类数据
- 优化磁盘使用
- 数据类型兼容问题（不同的 DBMS）

1.1.3 行

行（row）：表中的一个记录（record）

1.1.5 主键

主键（primary key）：一列（或一组列），其值能够唯一标识表中的每一行
- 任何满足条件的列都可以作为主键
  - 唯一标识行
  - 不允许 NULL
  - 不允许修改或更新
  - 删除后其值不能重用
- 不是必须，也可以是多列主键

1.2 什么是 SQL

SQL：结构化查询语言（Structured Query Language）
- 不是某个 DBMS 专用的
- 简单易学，强描述性
- 强大
SQL 的扩展：针对特定 DBMS 的简化特定操作执行的方法
标准 SQL 由 ANSI 标准委员会管理

1.3 动手实践

1.4 小结

什么是 SQL
为什么很有用
基本的数据库术语

第 2 课检索数据

2.1 SELECT 语句

关键词（keyword）：作为 SQL 组成部分的保留字，不能用作表或列的名字
SELECT 语句：从一个或多个表中检索信息
- 想选择什么，以及从什么地方选择

2.2 检索单个列

SELECT name FROM USER

数据未排序（可能是被添加到表中的属性，可能不是）
多条 SQL 必须以 ; 分隔
SQL 语句不区分大小写
所有空格都被忽略

2.3 检索多个列

SELECT id, name FROM user

列名之间 , 分隔

2.4 检索所有列

SELECT * FROM user

使用通配符 *
- 耗费性能
- 可以检索未知列

2.5 检索不同的值

SELECT DISTINCT name FROM module

使用 DISTINCT 关键字
- 必须直接放在列名前
- 作用于所有的列

2.6 限制结果

# MySQL、MariaDB、PostgreSQL、SQLite
SELECT name from user LIMIT 10

# 第 5 行后的 10 行
SELECT name from user LIMIT 10 OFFSET 5

不同的 DBMS 不同
LIMIT、OFFSET

2.7 使用注释

SELECT name FROM user -- 注释
# 注释
/*
 * 注释
 */

-- 行内
# 单行
/* */ 多行

2.8 小结

SELECT 语句检索
注释

第 3 课排序检索数据

3.1 排序数据

SELECT name from user ORDER BY name
SELECT name from user ORDER BY id

关系数据库设计理论认为：如果不明确规定排序顺序，则不应该假定检索出的数据的顺序有任何意义
子句（clause）：SQL 语句由子句构成，一个子句通常由一个关键字加所提供的数据组成
ORDER BY
- ORDER BY 子句要求在 SELECT 语句的最后一句
- 可以通过非检索列排序

3.2 按多个列排序

SELECT id, name, email from user ORDER BY name, id

按优先级以逗号分隔

3.3 按列位置排序

SELECT id, name, email from user ORDER BY 2, 3
# =
SELECT id, name, email from user ORDER BY name, email

用所选列的序号代替重复列名
不建议使用

3.4 指定排序方向

# 先按 name 降序，再按 email 升序
SELECT id, name, email from user ORDER BY name DESC, email

使用 DESC 关键字
- 只应用于直接位于前面的列名

3.5 小结

ORDER BY 子句

第 4 课过滤数据

4.1 使用 WHERE 子句

SELECT * from user WHERE name = 'xiaoming'

WHERE 指定搜索条件（过滤条件）
- WHERE 子句放在表名（FROM 子句）后
使用 SQL 过滤而不是应用过滤

4.2 WHERE 子句操作符

操作符（不同 DBMS 不同）

操作符	说明
=
<>	不等于
!=	不等于
<
<=
>
>=
BETWEEN ... AND ...
IS NULL

4.2.1 检查单个值

SELECT * from user WHERE id < 10

4.2.2 不匹配检查

SELECT * from user WHERE npm_user <> 1

有的 DBMS 不支持 !=

4.2.3 范围值检查

SELECT * from user WHERE id BETWEEN 10 AND 100

4.2.3 空值检查

SELECT * from user WHERE email IS NULL

IS NULL 子句
NULL，无值

4.3 小结

WHERE 子句过滤

第 5 课高级数据过滤

5.1 组合 WHERE 子句

操作符（operator，逻辑操作符）：用来联结或改变 WHERE 子句中的子句的关键字

5.1.1 AND 操作符

SELECT *
FROM user
WHERE npm_user = 1 AND email IS NULL

AND：用在 WHERE 子句中的关键字，用来表示检索满足所有给定条件的行

5.1.2 OR 操作符

OR：用在 WHERE 子句中的关键字，用来表示检索匹配任一给定条件的行

5.1.3 求值顺序

# npm_user = 0 或 1，同时 email 为空值
SELECT *
FROM user
WHERE (npm_user = 1 OR npm_user = 0) AND email IS NULL

SQL 会优先处理 AND 操作符
使用 () 来控制优先级
建议 AND OR 混用时始终使用圆括号

5.2 IN 操作符

SELECT * 
FROM module
WHERE authoer IN ('xiaoming', 'xiaohong')
ORDER BY name

IN：WHERE 子句中用来指定要匹配值的清单的关键字，功能与 OR 相当
- 多选项时更直观
- 与 AND/OR 组合时，求值顺序更易管理
- 比一组 OR 执行更快
- 最大优点是可以包含其他 SELECT 语句，能更动态地建立 WHERE 子句

5.3 NOT 操作符

SELECT *
FROM user
WHERE NOT (npm_user = 1 AND id > 10)

NOT：WHERE 子句中用来否定其后条件的关键字

5.4 小结

AND/OR
求值顺序
NOT

第 6 课用通配符进行过滤

6.1 LIKE 操作符

通配符（wildcard）：用来匹配值的一部分的特殊字符
- 只能用于字符串
搜索模式（search pattern）：由字面值、通配符或两者组合构成的搜索条件
谓词（predicate）：LIKE 作为谓词

6.1.1 百分号（%）通配符

# xiao 开头的 name
SELECT * 
FROM user
WHERE name LIKE 'xiao%'

%：任何字符出现任意次数
- 可以在搜索模式的不同位置：a%e
根据 DBMS 的不同及其配置，可以区分大小写
不匹配 NULL

6.1.2 下划线（_）通配符

# 两个字符的 163 邮箱
SELECT * 
FROM user
WHERE email LIKE '__@163.com'

_：匹配单个字符

6.1.3 方括号（[]）通配符

# 非 a 和 b 开头的 name
SELECT *
FROM user
WHERE name LIKE '[^ab]%'

[]: 指定字符集，必须匹配指定位置的一个字符
- 只有 Access 和 SQL Server 支持集合
^：否定集合

6.2 使用通配符的技巧

不过度使用，通配符是最后的手段
不要用在搜索模式的开始处，否则会很慢
注意通配符的位置，容易出错

6.3 小结

什么是通配符
如何在 WHERE 子句中使用
注意点

第 7 课创建计算字段

7.1 计算字段

字段（field）：与列基本相同，经常互换使用
只有数据库知道 SELECT 语句中哪些是实际的表列，哪些是计算字段，对应用程序是透明的
把数据转换和格式化工作放在数据库中会比在应用程序中更快

7.2 拼接字段

SELECT CONCAT(name, '[', email, ']') AS info
FROM user
WHERE id < 100

CONCAT
TRIM、LTRIM（清除左边的空格）、RTRIM（清除右边的空格）
AS：别名或导出列
- 使用 AS 是最佳实践
- 让应用程序可以按名称引用这个列
- 可以对列中的不合法字符重新命名
- 增加可读性

7.3 执行算术计算

SELECT dist_size / 1024 AS dist_kb
FROM module
LIMIT 100

SELECT 可以用来测试计算（省略 FROM），如 SELECT 3 * 2 返回 6

7.4 小结

计算字段及如何创建
字符串拼接
算数计算
别名

第 8 课使用函数处理数据

8.1 函数

提供数据的转化和处理方法
各 DBMS 差异大，因此使用函数的 SQL 可移植性差

8.2 使用函数

处理文本字符串
数值算数操作
处理日期和时间
返回 DBMS 证使用的特殊信息（如用户登录信息）的系统函数

8.2.1 文本处理函数

SELECT UPPER(name) AS name_upcase, email
From user

常用的文本处理函数

函数	说明
LEFT	返回字符串左边的字符
LENGTH	返回字符串长度
LOWER	返回字符串小写
UPPER	返回字符串大写
LTRIM	去字符串左边空格
RIGHT	返回字符串右边的字符
RTRIM	去字符串右边空格
SOUNDEX	返回字符串的 SOUNDEX 值（语音描述）

8.2.2 日期和时间处理函数

DBMS 都有自己的日期时间形式和处理函数
- 高效排序或过滤
- 节省存储空间
- 可移植性差

8.2.3 数值处理函数

常用数值处理函数

函数	说明
ABS	绝对值
COS	余弦值
EXP	指数值
PI	圆周率
SIN	正弦值
SQRT	平方根
TAN	正切

8.3 小结

有用
各 DBMS 不一致

第 9 课汇总数据

9.1 聚集函数

只需要汇总数据而不需要实际检索，如
- 满足某个条件的行的行数
- 某些行的和
- 某些列的最大值、平均值等
聚集函数（aggregate function）：对某些行运行的函数，计算并返回一个值
在各 DMBS 中实现相当一致

函数	说明
AVG	某列的平均值
COUNT	某列的行数
MAX	某列的最大值
MIN	某列的最小值
SUM	某列之和

9.1.1 AVG() 函数

SELECT AVG(dist_size) 
AS avg_dist_size 
FROM module

SELECT AVG(dist_size)
AS avg_babel_dist_size
FROM module
WHERE name LIKE '@babel/%'

只能用于单列，多个列的平均值必须使用多个 AVG()
对 NULL
- 值为 NULL 的行会被忽略

9.1.1 COUNT() 函数

SELECT COUNT(*) AS user_count FROM user;
SELECT COUNT(email) AS user_with_email_count FROM user;

对 NULL
- COUNT(*) 对表中行的数目进行技术，不管列中是否包含 NULL
- COUNT(column) 对特定列中非 NULL 值的行的数目进行计数

9.1.3 MAX() 函数

SELECT MAX(dist_size) AS max_dist_size FROM module

对非数值
- 返回按该列排序后的最后一行
对 NULL
- 值为 NULL 的行会被忽略

9.1.4 MIN() 函数

与 MAX 相反

9.1.5 SUM() 函数

SELECT SUM(dist_size) AS total_dist_size FROM module
SELECT SUM(dist_size*LENGTH(name)) AS for_test FROM module

对非数值忽略
对 NULL 忽略

9.2 聚集不同的值

SELECT AVG(DISTINCT dist_size) 
AS avg_dist_size 
FROM module

默认为 ALL，对所有行执行计算
指定 DISTINCT 只包含不同的值

9.3 组合聚集函数

SELECT 
MAX(dist_size) AS max_dist_size,
MIN(dist_size) AS min_dist_size,
FROM module

9.4 小结

使用 5 个聚集函数来汇总数据

第 10 课分组数据

10.1 数据分组

使用分组可以将数据分成多个逻辑组，对每个组进行聚集计算

10.2 创建分组

# 每个包及其版本数
SELECT name, COUNT(*) AS version_count 
FROM module 
GROUP BY name

GROUP BY 子句可以包含任意数目的列
列可以是检索列或有效表达式（不能是聚集函数）
NULL 会被作为一个分组返回
放在 WHERE 后，ORDER BY 前

10.3 过滤分组

# 版本数大于 100 的包及其版本数
SELECT name, COUNT(*) AS version_count 
FROM module 
GROUP BY name 
HAVING COUNT(*) >= 100
# 体积大于 1024 的版本数大于 100 的包及其版本
SELECT name, COUNT(*) AS version_count 
FROM module 
WHERE dist_size > 1024
GROUP BY name 
HAVING COUNT(*) >= 100

HAVING 语法同 WHERE，其能代替 WHERE
HAVING 过滤分组，WHERE 过滤行
HAVING 在数据分组后进行过滤，WHERE 在分组前进行过滤

10.4 分组和排序

ORDER BY 和 GROUP BY

ORDER BY	GROUP BY
对产生的输出排序	对行分组，输出不一定是分组的顺序
任意列都可以使用	只能使用选择列或表达式列
不一定需要	在与聚集函数一起使用时必要

10.5 SELECT 子句顺序

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

10.6 小结

使用 GROUP BY 对多组数据汇总计算
HAVING 以及和 WHERE 的区别

第 11 课使用子查询

11.1 子查询

查询（query）：任何 SQL 语句都是查询，但一般指 SELECT 语句
子查询（subquery）：嵌套在其他查询中查询

11.2 利用子查询进行过滤

# 内部作者的包
SELECT DISTINCT name
FROM module
WHERE author IN (
    SELECT name 
    FROM user 
    WHERE npm_user = 0
)

子查询的 SELECT 语句只能查询单个列
子查询存在性能问题，更有效方法见 12 章

11.3 作为计算字段使用子查询

# 每个有包的作者的包的数量
SELECT name, (
    SELECT COUNT(DISTINCT name) 
    FROM module
    WHERE module.author = user.name
) AS module_count
FROM user
HAVING module_count > 0

通过 table.column 来完全限定列名，避免歧义
JOIN 是更好的解决方案

11.4 小结

子查询及如何使用

第 12 课联结表

12.1 联结

JOIN

12.1.1 关系表

将数据分层多个关联的表

12.1.2 为什么使用联结

联结是一种机制，用来在一条 SELECT 语句中关联表

12.2 创建联结

SELECT module.name, version, user.name, email
FROM module, user
WHERE module.author = user.name

12.2.1 WHERE 子句的重要性

没有 WHERE，检索的结果是笛卡尔积（第一张表的行数乘以第二张表的行数），也叫做叉联结

12.2.2 内联结

上面的例子是等值联结，也称为内联结
可以用 INNER JOIN 语法

SELECT module.name, version, user.name, email
FROM module INNER JOIN user
ON module.author = user.name

12.2.3 联结多个表

同两张表
过多的联结表耗费性能

12.3 小结

联结是 SQL 中最重要、最强大的特性
要求对关系数据库设计有基本的了解

第 13 课创建高级联结

13.1 使用表别名

SELECT m.name, version, u.name, email
FROM module AS m INNER JOIN user AS u
ON m.author = u.name

13.2 使用不同类型的联结

内联结（等值联结）
自联结（self join）
自然联结（natural join）
外联结（outer join）

13.2.1 自联结

# 和 babel 同作者的包
SELECT DISTINCT m1.name, m1.author
FROM module AS m1, module AS m2
WHERE m1.author = m2.author AND m2.name = 'babel'

用来替代从相同表中检索数据的使用子查询的语句
通常 DBMS 处理联结远比子查询快

13.2.2 自然联结

联结中至少有一列会出现在多个表中，自然联结使每一列只返回一次
以上的内联结都是自然联结

13.3.3 外联结

# 作者及其包
SELECT DISTINCT u.name, u.email, m.name
FROM user AS u LEFT OUTER JOIN module AS m
ON u.name = m.author

需要包含没有关联行的那些行的联结
LEFT OUTER JOIN、RIGHT OUTER JOIN、FULL OUTER JOIN（mysql 等不支持）

13.3 使用带聚集函数的联结

# 所有没有发过包的用户
SELECT u.name, u.email, COUNT(DISTINCT m.name) AS module_count
FROM user AS u LEFT OUTER JOIN module AS m
ON u.name = m.author
GROUP BY u.name
HAVING module_count < 1

13.4 使用联结和联结条件

注意使用的联结类型，应使用内联结还是外联结
注意各 DBMS 的联结语法
使用正确的联结条件
多表联结合法且有用，注意使用前分开测试

13.5 小结

联结类型
联结中聚集
注意事项

第 14 课组合查询

14.1 组合查询

并（union）：执行多个查询（多条 SELECT 语句）并将结果作为一个查询结果集返回，也称为复合查询
使用场景
- 在一个查询中从不同的表返回结构数据
- 对一个表执行多个查询，按一个查询返回结果
具有多个 WHERE 子句的 SELECT 语句都可以作为一个组合查询

14.2 创建组合查询

14.2.1 使用 UNION

SELECT name FROM user WHERE npm_user = 0
UNION
SELECT name FROM user WHERE email IS NULL
# 等于
SELECT name FROM user WHERE npm_user = 0 OR email IS NULL

多数 DBMS 会将 UNION 优化为一条 SELECT 语句

14.2.2 UNION 规则

必须由两条及以上 SELECT 语句组成，语句之间用 UNION 分隔
每个查询必须包含相同的列、表达式或聚集函数
列数据类型必须兼容

14.2.3 包含或取消重复的行

UNION 会过滤重复的行
UNION ALL 包含重复的行，这是 WHERE 无法完成的

14.2.4 对组合查询结果排序

ORDER BY 放在所有 SELECT 之后

14.3 小结

UNION 组合查询语句

第 15 课插入数据

15.1 数据插入

插入数据的方式
- 完整的行
- 行的一部分
- 某些查询的结果
DBMS 中的 INSERT 权限

15.1.1 插入完整的行

# 插入用户，可省略 allow null 列或有默认值的列
INSERT INTO user
(name, email, npm_user)
VALUES
('1', '2', 0)

15.1.2 插入部分行

如 15.1.1
省略的列必须满足条件之一
- 允许为 NULL
- 表定义中有默认值

15.1.3 插入检索出的数据

# 从 user2 中检索出 n 行数据插入 user
INSERT INTO user(name, email, npm_user)
SELECT name, email, npm_user FROM user2

SELECT 后的列的位置与 INSERT 对应，而无须列名一致

15.2 从一个表复制到另一个表

# 创建新表 user2，并复制 user 数据
SELECT * INTO user2 FROM user
# mysql、sqllite 等
CREATE TABLE user2 AS
SELECT * FROM user

任何 SELECT 选项和子句都可以使用，包括 WHERE、GROUP BY
可用联结
只能插入到一张表中

15.3 小结

使用 INSERT 将行插入到数据库表中

第 16 课更新和删除数据

16.1 更新数据

UPDATE user
SET email = NULL,
SET npm_user = 0
WHERE name = 'a'

UPDATE
- 更新特定行，注意 WHERE
- 更新所有行
DBMS 中的 UPDATE 权限
UPDATE 语句组成
- 要更新的表
- 列名和它们的新值
- 行的过滤条件

16.2 删除数据

DELETE FROM user
WHERE name = 'a'

DELETE
- 删除特定行，注意 WHERE
- 删除所有行
DBMS 中的 DELETE 权限
删除的是行而不是表
使用 TRUNCATE TABLE 来删除所有行更快（因为不记录数据的变动）

16.3 更新和删除的指导原则

除非确实打算更新和删除每一行，否则必须带 WHERE
保证每个表都有主键
先用 SELECT 测试，保证正确
使用强制实施引用完整性的数据库
如果 DBMS 支持，施加约束不允许执行不带 WHERE 的

16.4 小结

UPDATE、DELETE 的使用
保证数据安全应该遵循的一些指导原则

第 17 课创建和操纵表

17.1 创建表

用交互式创建和管理表的工具
用 SQL 语句创建

17.1.1 表创建基础

CREATE TABLE test (
    a CHAR(10) NOT NULL,
    b VARCHAR(1000) NOT NULL,
    c BIGINT(10) NOT NULL
)

17.1.2 使用 NULL 值

# 允许 b 为 NULL
CREATE TABLE test (
    a CHAR(10) NOT NULL,
    b VARCHAR(1000),
    c BIGINT(10) NOT NULL
)

每个表列要么是 NULL 列，要么是 NOT NULL 列
允许 NULL 值的列不能作为主键
有的 DBMS 需要指定关键词 NULL

17.1.3 指定默认值

CREATE TABLE test (
    a CHAR(10) NOT NULL DEFAULT 1,
    b VARCHAR(1000),
    t DATE NOT NULL DEFAULT CURRENT_DATE()
)

17.2 更新表

ALTER TABLE test
ADD d CHAR(20);
ALTER TABLE test
DROP COLUMN a;

对 ALTER TABLE 的限制
- 尽量避免在表中包含数据时更新
- 所有 DBMS 都支持增加列
- 许多 DBMS 不允许删除或更改列
- 多少 DMBS 允许重命名列
- 许多 DBMS 限制对有数据的列更改，对无数据的列几乎没有限制
复杂的表结构更改一般需要手动删除过程
1. 用新的列布局创建一个新表
2. 用 INSERT SELECT 从旧表复制数据倒新表
3. 检验包含所需数据的新表
4. 重命名旧表（可以删除）
5. 用旧表原来的名字重命名新表
6. 重新创建触发器、存储过程、索引和外键

17.3 删除表

DROP TABLE test;

使用关系规则防止意外删除，如果是某个关系的组成部分，DBMS 会阻止删除

17.4 重命名表

不同 DBMS 有差别，MySQL 用 RENAME

17.5 小结

CREATE TABLE
ALTER TABLE
DROP TABLE
RENAME TABLE

第 18 课使用视图

18.1 视图

视图是虚拟的表，只包含使用时动态检索数据的查询
可以用与表基本相同的方式使用
本身不包含数据
每次使用视图时，都必须处理传执行时所需要的所有检查，因此比较耗费性能

18.1.1 为什么使用视图

重用 SQL 语句
简化复制的 SQL 操作。在编写查询后，可以方便地重用它而不必知道基本查询细节
使用表的一部分而不是整个表
保护数据。可以授予用户访问表的特定部分的权限
更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据

18.1.2 视图的规划和限制

视图命名唯一
不限制视图数量
创建视图的权限
视图嵌套
实体不能所有，也不能有关联的触发器或默认值
其它参与具体的 DBMS 文档

18.2 创建视图

CREATE VIEW
DROP VIEW

18.2.1 利用视图简化复杂的联结

CREATE VIEW user_module AS
SELECT user.name , user.email, module.name as module
FROM user INNER JOIN module
on user.name = module.author;

18.2.2 用视图重新格式化检索出的数据

18.2.3 用视图过滤不想要的数据

18.2.4 使用视图与计算字段

18.3 小结

视图为虚拟的表，包含的不是数据而是根据需要检索数据的查询

第 19 课使用存储的过程

19.1 存储过程

存储过程是为以后使用而保存的一条或多条 SQL 语句。可视为批文件（不仅限于此）

19.2 为什么要使用存储过程

好处：简单、安全、高性能
- 把处理封装在一个易用的单元，简化复杂的操作
- 使用同一存储过程，可以保证数据的一致性
- 隔离变动
- 存储过程通常以编译过的形式存储，简化了 DBMS 处理工作，提高了性能
- 可以使用一些职能用在单个请求中的 SQL 元素和特性
缺陷
- DBMS 语法差异，几乎不可移植
- 编写存储过程复杂
编写存储过程权限

19.3 执行存储过程

EXECUTE + 存储过程名 + 参数

19.4 创建存储过程

19.5 小结

见具体 DBMS

第 20 课管理事务处理

20.1 事务处理

事务处理（transaction processing）：通过确保成批的 SQL 操作要么完全执行，要么完全不执行，来维护数据库的完整性
几个术语
- 事务（transaction）：一组 SQL 语句
- 回退（rollback）：撤销指定 SQL 语句的过程
- 提交（commit）：将未存储的 SQL 语句结果写入数据库表
- 保留点（savepoint）：事务处理中设置的临时占位符（placeholder），可以对它发布回退（与回退整个事务处理不同）
可以回退：INSERT、UPDATE、DELETE 语句

20.2 控制事务处理

# MySQL
START TRANSACTION
# ...

管理事务的关键在于将 SQL 语句组成分解为逻辑块，并明确规定数据何时应该回退，何时不应该回退

20.2.1 使用 ROLLBACK

# 使用 ROLLBACK 撤销
DELETE FROM test;
ROLLBACK;

20.2.2 使用 COMMIT

# SQL server
BEGIN TRANSACTION;
DELETE ...
COMMIT TRANSACTION;

一般的 SQL 语句都是直接执行和编写的，即隐式提交（implicit commit），提交操作是自动进行的
在事物处理中，提交通常通过 COMMIT 明确提交

20.2.3 使用保留点

SAVEPOINT delete1;
# DELTE ...
ROLLBACK TO delete1;

保留点越多越好，以进行灵活地回退

20.3 小结

事务是必须完整执行的 SQL 语句块
使用 COMMIT 和 ROLLBACK 对写数据、撤销数据进行明确管理
使用保留点控制回退操作
参考具体 DBMS

第 21 课使用游标

21.1 游标

结果集（result set）：SQL 查询所检索出的结果
游标（cursor）：一个存储在 DBMS 服务器上的数据库查询，不是一条 SQL 语句，而是被该语句检索出来的结果集。应用程序可以根据需要滚动或浏览其中的数据
对于 WEB 应用用处不大，因为应用服务器是数据库客户端而不是最终用户

21.2 使用游标

21.3 小结

第 22 课高级 SQL 特性

22.1 约束

约束（constraint）：管理如何插入或处理数据库数据的规则
DBMS 通过在数据库表上引用完整性（referential integrity）

22.1.1 主键

主键是一种特殊的约束，用来保证一列（或一组列）中的值是唯一的，且永不改动
任意列只要满足以下条件，都可以用于主键
- 任意两行的主键值都不相同
- 列中不允许 NULL 值
- 列从不修改或更新
- 主键值不能重用
定义主键：创建表时或 CONSTRAINT PRIMARY KEY

CREATE TABLE test (
    name CHAR(10) NOT NULL PRIMARY KEY;
);

ALTER TABLE test 
ADD CONSTRAINT PRIMARY KEY (name);

22.1.2 外键

外键：表中的一列，其值必须列在另一表的主键中。是保证引用完整性的极其重要部分
定义外键：REFERENCES、CONSTRAINT FOREIGN KEY
可以防止意外删除，DBMS 不允许删除在另一个表中具有关联行的行

CREATE TABLE module(
    author CHAR(20) NOT NULL REFERENCES user(name)
)

ALTER TABLE module
ADD CONSTRAINT FOREIGN KEY author REFERENCES user(name)

22.1.3 唯一约束

唯一约束：用来保证一列（或一组列）中的数据是唯一的
与主键的区别：
- 表可以包含多个唯一约束
- 唯一约束列可以包含 NULL
- 唯一约束列可以修改或更新
- 唯一约束列的值可以重复使用
- 不能用来定义外键
定义唯一约束：UNIQUE，用法同上

22.1.4 检查约束

检查约束：用来保证一列（或一组列）中的数据满足一组指定的条件
用途
- 检查最小或最大值
- 指定范围
- 只允许特定的值
定义检查约束：CHECK
有的 DBMS 支持用户定义数据类型，即定义检查约束的基本简单数据类型

CREATE TABLE module(
    dist_size INT(10) CHECK (dist_size < 1024)
)

ADD CONSTRAINT CHECK (gender LIKE '[MF]')

22.2 索引

索引：用来排序数据以加快搜索和排序操作的速度
索引改善了检索操作的性能，但降低了数据插入、修改和删除的性能（DBMS 必须动态地更新索引）
索引数据可能要占用大量的存储空间
并非所有的数据都适合做缩影，具有更多可能值的数据能通过索引得到更多好处
可以在索引中定义多个列，在组合多个列排序时有用
索引的效率随着表数据的变化而变化，需要定期检查、调整索引

CREATE INDEX name_index ON user(name)

22.3 触发器

触发器：特殊的存储过程，在特定的数据库互动发生时自动执行
可以与特定表上的 INSERT、UDPATE、DELETE 操作（或组合）相关联
用途
- 保证数据一致，如在 INSERT 或 UPDATE 操作时将字段转化为大写
- 基于某个表的变动在其他表上执行活动
- 进行额外的验证并根据需要回退数据
- 计算计算列的值或更新时间戳
约束比触发器更快
不同的 DBMS 差异大：CREATE TRIGGER ... FOR ... SET ...

22.4 数据库安全

对数据库管理功能（创建表等）的访问
对特定数据库或表的访问
访问的类型（只读、对特定列的访问等）
仅通过视图或存储过程对表进行访问
创建多层次的安全措施，从而允许多种基于登录的访问和控制
限制管理用户账号的能力

《SQL 必知必会》- 阅读索引

阅读总结

第 1 课 了解 SQL

1.1 数据库基础

1.1.1 数据库

1.1.2 表

1.1.3 列

1.1.3 行

1.1.5 主键

1.2 什么是 SQL

1.3 动手实践

1.4 小结

第 2 课 检索数据

2.1 SELECT 语句

2.2 检索单个列

2.3 检索多个列

2.4 检索所有列

2.5 检索不同的值

2.6 限制结果

2.7 使用注释

2.8 小结

第 3 课 排序检索数据

3.1 排序数据

3.2 按多个列排序

3.3 按列位置排序

3.4 指定排序方向

3.5 小结

第 4 课 过滤数据

4.1 使用 WHERE 子句

4.2 WHERE 子句操作符

4.2.1 检查单个值

4.2.2 不匹配检查

4.2.3 范围值检查

4.2.3 空值检查

4.3 小结

第 5 课 高级数据过滤

5.1 组合 WHERE 子句

5.1.1 AND 操作符

5.1.2 OR 操作符

5.1.3 求值顺序

5.2 IN 操作符

5.3 NOT 操作符

5.4 小结

第 6 课 用通配符进行过滤

6.1 LIKE 操作符

6.1.1 百分号（%）通配符

6.1.2 下划线（_）通配符

6.1.3 方括号（[]）通配符

6.2 使用通配符的技巧

6.3 小结

第 7 课 创建计算字段

7.1 计算字段

7.2 拼接字段

7.3 执行算术计算

7.4 小结

第 8 课 使用函数处理数据

8.1 函数

8.2 使用函数

8.2.1 文本处理函数

8.2.2 日期和时间处理函数

8.2.3 数值处理函数

8.3 小结

第 9 课 汇总数据

9.1 聚集函数

9.1.1 AVG() 函数

9.1.1 COUNT() 函数

9.1.3 MAX() 函数

9.1.4 MIN() 函数

9.1.5 SUM() 函数

9.2 聚集不同的值

9.3 组合聚集函数

9.4 小结

第 10 课 分组数据

10.1 数据分组

10.2 创建分组

10.3 过滤分组

10.4 分组和排序

10.5 SELECT 子句顺序

10.6 小结

第 11 课 使用子查询

第 1 课了解 SQL

第 2 课检索数据

第 3 课排序检索数据

第 4 课过滤数据

第 5 课高级数据过滤

第 6 课用通配符进行过滤

第 7 课创建计算字段

第 8 课使用函数处理数据

第 9 课汇总数据

第 10 课分组数据

第 11 课使用子查询

第 12 课联结表

第 13 课创建高级联结

第 14 课组合查询

第 15 课插入数据

第 16 课更新和删除数据

第 17 课创建和操纵表

第 18 课使用视图

第 19 课使用存储的过程

第 20 课管理事务处理