SQL 学习 (持续更新）

0 函数篇

• 在 MySQL 中取整函数 CEIL(x) 和 CEILING(x) 的意义相同，返回不小于 x 的最小整数值，返回值转化为一个 BIGINT。

1 关系模型

1.1 主键

小结

主键是关系表中记录的唯一标识。主键的选取非常重要：主键不要带有业务含义，而应该使用 BIGINT 自增或者 GUID 类型。主键也不应该允许NULL。

可以使用多个列作为联合主键，但联合主键并不常用。

1.2 外键

在students表中，通过class_id的字段，可以把数据与另一张表关联起来，这种列称为外键。

外键并不是通过列名实现的，而是通过定义外键约束实现的：

-- 设置外键约束ALTER TABLE studentsADD CONSTRAINT fk_class_idFOREIGN KEY (class_id)REFERENCES classes (id);-- 删除外键约束ALTER TABLE studentsDROP FOREIGN KEY fk_class_id;

多对多关系

多对多关系实际上是通过两个一对多关系实现的，即通过一个中间表，关联两个一对多关系，就形成了多对多关系。

小结

关系数据库通过外键可以实现一对多、多对多和一对一的关系。外键既可以通过数据库来约束，也可以不设置约束，仅依靠应用程序的逻辑来保证。

1.3 索引

索引是关系数据库中对某一列或多个列的值进行预排序的数据结构。通过使用索引，可以让数据库系统不必扫描整个表，而是直接定位到符合条件的记录，这样就大大加快了查询速度。

-- 创建索引ALTER TABLE studentsADD INDEX idx_score (score);-- 使用ADD INDEX idx_score (score)就创建了一个名称为idx_score，使用列score的索引。索引名称是任意的，索引如果有多列，可以在括号里依次写上

索引的效率

索引的效率取决于索引列的值是否散列，即该列的值如果越互不相同，那么索引效率越高。

索引的优点是提高了查询效率，缺点是在插入、更新和删除记录时，需要同时修改索引，因此，索引越多，插入、更新和删除记录的速度就越慢。

对于主键，关系数据库会自动对其创建主键索引。使用主键索引的效率是最高的，因为主键会保证绝对唯一。

唯一索引

-- 创建唯一索引ALTER TABLE studentsADD UNIQUE INDEX uni_name (name);-- 添加唯一约束不创建唯一索引ALTER TABLE studentsADD CONSTRAINT uni_name UNIQUE (name);

小结

通过对数据库表创建索引，可以提高查询速度。

通过创建唯一索引，可以保证某一列的值具有唯一性。

数据库索引对于用户和应用程序来说都是透明的。

2 查询数据

2.1 基本查询

小结

使用 SELECT 查询的基本语句SELECT * FROM <表名>可以查询一个表的所有行和所有列的数据。

SELECT 查询的结果是一个二维表。

2.2 条件查询

小结

通过WHERE条件查询，可以筛选出符合指定条件的记录，而不是整个表的所有记录。

返回的二维表结构和原表是相同的，即结果集的所有列与原表的所有列都一一对应。

2.3 投影查询

用SELECT 列1, 列2, 列3 FROM ...，让结果集仅包含指定列。这种操作称为投影查询。

小结

使用SELECT *表示查询表的所有列，使用SELECT 列1, 列2, 列3则可以仅返回指定列，这种操作称为投影。

SELECT语句可以对结果集的列进行重命名。

2.4 排序 ORDER BY

如果score列有相同的数据，要进一步排序，可以继续添加列名。

SELECT id, name, gender, score FROM students ORDER BY score, gender;

默认的排序规则是ASC：“升序”，即从小到大。ASC可以省略，即ORDER BY score ASC和ORDER BY score效果一样。“倒序”DESC

如果有WHERE子句，那么ORDER BY子句要放到WHERE子句后面。

2.5 分页查询

分页实际上就是从结果集中“截取”出第 M~N 条记录。这个查询可以通过LIMIT <N-M> OFFSET <M>子句实现。

-- 结果集分页，每页3条记录。要获取第1页的记录，可以使用LIMIT 3 OFFSET 0SELECT id, name, gender, scoreFROM studentsORDER BY score DESCLIMIT 3 OFFSET 0;

分页查询的关键在于，首先要确定每页需要显示的结果数量pageSize（这里是 3），然后根据当前页的索引pageIndex（从 1 开始），确定LIMIT和OFFSET应该设定的值：

• LIMIT总是设定为pageSize；

• OFFSET计算公式为pageSize * (pageIndex - 1)。

这样就能正确查询出第 N 页的记录集。

注：OFFSET超过了查询的最大数量并不会报错，而是得到一个空的结果集。

小结

使用LIMIT <M> OFFSET <N>可以对结果集进行分页，每次查询返回结果集的一部分；

分页查询需要先确定每页的数量和当前页数，然后确定LIMIT和OFFSET的值。

使用LIMIT <M> OFFSET <N>分页时，随着N越来越大，查询效率也会越来越低。

OFFSET是可选的，如果只写LIMIT 15，那么相当于LIMIT 15 OFFSET 0。

在 MySQL 中，LIMIT 15 OFFSET 30还可以简写成LIMIT 30, 15。

2.6 聚合查询

MAX()和MIN()函数并不限于数值类型。如果是字符类型，MAX()和MIN()会返回排序最后和排序最前的字符。

如果聚合查询的WHERE条件没有匹配到任何行，COUNT()会返回 0，而SUM()、AVG()、MAX()和MIN()会返回NULL

分组 GROUP BY

2.7 多表查询

小结

使用多表查询可以获取 M x N 行记录；

多表查询的结果集可能非常巨大，要小心使用。

2.8 连接查询

注意 INNER JOIN 查询的写法是：

1. 先确定主表，仍然使用FROM <表1>的语法；

2. 再确定需要连接的表，使用INNER JOIN <表2>的语法；

3. 然后确定连接条件，使用ON <条件...>，这里的条件是s.class_id = c.id，表示students表的class_id列与classes表的id列相同的行需要连接；

4. 可选：加上WHERE子句、ORDER BY等子句。

小结

JOIN 查询需要先确定主表，然后把另一个表的数据“附加”到结果集上；

INNER JOIN 是最常用的一种 JOIN 查询，它的语法是SELECT ... FROM <表1> INNER JOIN <表2> ON <条件...>；

JOIN 查询仍然可以使用WHERE条件和ORDER BY排序。

3 修改数据

3.1 INSERT

-- instert语法INSERT INTO <表名> (字段1, 字段2, ...) VALUES (值1, 值2, ...);-- exampleINSERT INTO students (class_id, name, gender, score) VALUES  (1, '大宝', 'M', 87),  (2, '二宝', 'M', 81);

小结

使用INSERT，我们就可以一次向一个表中插入一条或多条记录。

3.2 UPDATE

我们想更新students表id=1的记录的name和score这两个字段，先写出UPDATE students SET name='大牛', score=66，然后在WHERE子句中写出需要更新的行的筛选条件id=1

UPDATE students SET name='大牛', score=66 WHERE id=1;

小结

使用UPDATE，我们就可以一次更新表中的一条或多条记录。

3.3 DELETE

小结

使用DELETE，我们就可以一次删除表中的一条或多条记录。

4 MYSQL

4.1 管理 mysql

4.2 实用 SQL 语句

5 事务

在执行 SQL 语句的时候，某些业务要求，一系列操作必须全部执行，而不能仅执行一部分。

-- 从id=1的账户给id=2的账户转账100元-- 第一步：将id=1的A账户余额减去100UPDATE accounts SET balance = balance - 100 WHERE id = 1;-- 第二步：将id=2的B账户余额加上100UPDATE accounts SET balance = balance + 100 WHERE id = 2;

数据库事务具有 ACID 这 4 个特性：

• A：Atomic，原子性，将所有 SQL 作为原子工作单元执行，要么全部执行，要么全部不执行；

• C：Consistent，一致性，事务完成后，所有数据的状态都是一致的，即 A 账户只要减去了 100，B 账户则必定加上了 100；

• I：Isolation，隔离性，如果有多个事务并发执行，每个事务作出的修改必须与其他事务隔离；

• D：Duration，持久性，即事务完成后，对数据库数据的修改被持久化存储。

对于单条 SQL 语句，数据库系统自动将其作为一个事务执行，这种事务被称为隐式事务。

要手动把多条 SQL 语句作为一个事务执行，使用BEGIN开启一个事务，使用COMMIT提交一个事务，这种事务被称为显式事务，例如，把上述的转账操作作为一个显式事务

BEGIN;UPDATE accounts SET balance = balance - 100 WHERE id = 1;UPDATE accounts SET balance = balance + 100 WHERE id = 2;COMMIT;

有些时候，我们希望主动让事务失败，这时，可以用ROLLBACK回滚事务，整个事务会失败

BEGIN;UPDATE accounts SET balance = balance - 100 WHERE id = 1;UPDATE accounts SET balance = balance + 100 WHERE id = 2;ROLLBACK;

SQL 标准定义了 4 种隔离级别，分别对应可能出现的数据不一致的情况

数据库事务具有 ACID 特性，用来保证多条 SQL 的全部执行。

5.1 Read Unconnitted

Read Uncommitted 是隔离级别最低的一种事务级别。在这种隔离级别下，一个事务会读到另一个事务更新后但未提交的数据，如果另一个事务回滚，那么当前事务读到的数据就是脏数据，这就是脏读（Dirty Read）。

当事务 A 执行完第 3 步时，它更新了id=1的记录，但并未提交，而事务 B 在第 4 步读取到的数据就是未提交的数据。

随后，事务 A 在第 5 步进行了回滚，事务 B 再次读取id=1的记录，发现和上一次读取到的数据不一致，这就是脏读。

5.2 Read Committed

在 Read Committed 隔离级别下，一个事务可能会遇到不可重复读（Non Repeatable Read）的问题。

当事务 B 第一次执行第 3 步的查询时，得到的结果是Alice，随后，由于事务 A 在第 4 步更新了这条记录并提交，所以，事务 B 在第 6 步再次执行同样的查询时，得到的结果就变成了Bob，因此，在 Read Committed 隔离级别下，事务不可重复读同一条记录，因为很可能读到的结果不一致。

5.3 Repeatable Read

在 Repeatable Read 隔离级别下，一个事务可能会遇到幻读（Phantom Read）的问题。

幻读是指，在一个事务中，第一次查询某条记录，发现没有，但是，当试图更新这条不存在的记录时，竟然能成功，并且，再次读取同一条记录，它就神奇地出现了。

事务 B 在第 3 步第一次读取id=99的记录时，读到的记录为空，说明不存在id=99的记录。随后，事务 A 在第 4 步插入了一条id=99的记录并提交。事务 B 在第 6 步再次读取id=99的记录时，读到的记录仍然为空，但是，事务 B 在第 7 步试图更新这条不存在的记录时，竟然成功了，并且，事务 B 在第 8 步再次读取id=99的记录时，记录出现了。

可见，幻读就是没有读到的记录，以为不存在，但其实是可以更新成功的，并且，更新成功后，再次读取，就出现了。

5.4 Serializable

Serializable 是最严格的隔离级别。在 Serializable 隔离级别下，所有事务按照次序依次执行，因此，脏读、不可重复读、幻读都不会出现。

虽然 Serializable 隔离级别下的事务具有最高的安全性，但是，由于事务是串行执行，所以效率会大大下降，应用程序的性能会急剧降低。如果没有特别重要的情景，一般都不会使用 Serializable 隔离级别。

默认隔离级别

如果没有指定隔离级别，数据库就会使用默认的隔离级别。在 MySQL 中，如果使用 InnoDB，默认的隔离级别是 Repeatable Read。