读廖雪峰SQL教程笔记

廖雪峰SQL教程

1 SQL: Structed Query Language

• DDL: Data Definition Language -> 允许用户定义数据（创建表、删除表、修改表结构）
• DML: Data Manipulation Language -> 允许用户添加、删除、更新数据
• DQL: Data Query Language -> 允许用户查询数据

2 主键是关系表中记录的唯一标识。

• 主键的选取非常重要：主键不要带有业务含义，而应该使用BIGINT自增或者GUID类型。主键也不允许NULL

3 外键约束

会降低数据库的性能

-- 定义外键约束：
ALTER TABLE students
ADD CONSTRAINT fk_class_id
FOREIGN KEY (class_id)
REFERENCES classes (id);

-- 删除外键约束：
ALTER TABLE students
DROP FOREIGN KEY fk_class_id;

数据库优化策略：

• 去掉数据库中的外键约束，在程序中保证逻辑的正确性
• 在业务允许的前提下，将一个大表拆分成经常读取和不经常读取的两张表
• 使用索引

4 索引

_ 关系数据库中对某一列或多个列的值进行预排序

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

_ 使用索引时数据库不用整表扫描，直接定位到符合条件的记录

_ 索引的效率取决与索引列的值是否散列(该列值越不相同，索引效率越高)

_ 在插入、更新、删除记录时，需要同时修改索引，因此一张表的索引越多，插入、更新、删除记录的速度就越慢

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

_ 有索引时数据库会自动使用索引提高查询效率，没有索引查询也能正常执行，只是速度会变慢

-- 添加索引
ALTER TABLE students
ADD INDEX idx_score (score);

-- 添加联合索引
ALTER TABLE students
ADD INDEX idx_name_score (name, score);

-- 添加唯一索引
ALTER TABLE students
ADD UNIQUE INDEX uni_name (name);

-- 为某列添加唯一约束而不创建唯一索引
ALTER TABLE students
ADD CONSTRAINT uni_name UNIQUE (name);

5 查询(SELECT)

1. SELECT 查询返回结果集中仅包含指定列，这种操作称为投影查询。

2. 分页查询

-- LIMIT = PAGE_SIZE
-- OFFSET = PAGE_INDEX * LIMIT (可省略，省略时相当于OFFSET 0)
-- 使用LIMIT <M> OFFSET <N>分页时，随着N越来越大，查询效率也会越来越低
-- 查询第一页
SELECT id,name,gender,score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 0;

-- 查询第二页
SELECT id,name,gender,score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 3;

-- 查询第三页
SELECT id,name,gender,score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 6;

-- 模板
SELECT col1,col2,col3
FROM table_name
ORDER BY col1 DESC
LIMIT page_size OFFSET (page_index * page_size);

-- 模板简写
SELECT col1,col2,col3
FROM table_name
ORDER BY col1 DESC
LIMIT page_size, (page_index * page_size);

3. 聚合查询

函数	说明
SUM	计算某一列的合计值，该列必须为数值类型
AVG	计算某一列的平均值，该列必须为数值类型
MAX	计算某一列的最大值
MIN	计算某一列的最小值

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

4. 多表查询(笛卡尔查询)

结果集的行数是目标表的行数乘积，要小心使用

5. 连接查询(另一种多表查询)

_ 连接查询对多个表进行JOIN运算

_ 先确定一个主表作为结果集，然后将其他表的行有选择性地 连接 在主表结果集上

• INNER JOIN(内连接)
  
  返回同时存在于两张表的行数据
• OUTER JOIN(外连接)
  
  -- RIGHT OUTER JOIN
  返回右表都存在的行，如果某一行仅在右表中存在，那么结果集就会用NULL填充剩下的字段
  
  -- LEFT OUTER JOIN
  返回左表都存在的行，如果某一行仅在左表中存在，那么结果集就会用NULL填充剩下的字段
  
  -- FULL OUTER JOIN
  返回两张表中的所有记录，并自动把不存在的列填充为NULL
  

6 插入(INSERT)

INSERT字段顺序不必和数据库表字段顺序一致，但值的顺序必须和INSERT字段顺序一致。

-- 插入一行
INSERT INTO table_name (col1,col2,col3...) VALUES (col1_val,col2_val,col3_val...);

-- 插入多行
INSERT INTO table_name (col1,col2,col3...) VALUES 
(col1_val1,col2_val1,col3_val1...),
(col1_val2,col2_val2,col3_val2...),
(col1_val3,col2_val3,col3_val3...);

7 更新(UPDATE)

UPDATE语句可以没有WHERE条件，这时会更新整表！

返回值为被更新的行数

UPDATE table_name SET col1=val1,col2=val2,col3=val3 ... WHERE ...;

8 删除(DELETE)

WHERE子句没匹配到任何记录时，不会报错也不会有任何记录被删除。

DELETE语句可以没有WHERE条件，这时会删除整表！

返回值为被删除的行数

DELETE FROM table_name WHERE ...;

9 MYSQL

MYSQL Client的可执行程序是mysql，MySQL Server的可执行程序是mysqld。
命令行程序mysql实际上是MySQL客户端，真正的MySQL服务器程序是mysqld。

MySQL Client和MySQL Server的关系如下:

-- 列出所有数据库
SHOW DATABASES;

-- 创建新数据库
CREATE DATABASE test;

-- 删除数据库
DROP DATABASE test;

-- 切换数据库
USE test;

-- 列出当前数据库所有表
SHOW TABLES;

-- 查看表结构
DESC students;

-- 查看创建表的SQL语句
SHOW CREATE TABLE students;

-- 删除表
DROP TABLE students;

-- 修改表

--- 给students表添加一列birth
ALTER TABLE students ADD COLUMN birth varchar(10) NOT NULL;

--- 修改birth列为birthday，类型改为VARCHAR(20);
ALTER TABLE students CHANGE COLUMN birth birthday VARCHAR(20) NOT NULL;

--- 删除列
ALTER TABLE students DROP COLUMN birthday;

10 实用SQL语句

-- 插入或替换
-- 如果希望插入新记录时，记录存在则先删除原记录，再插入新记录，则可以使用REPLACE语句。
REPLACE INTO table_name (col1,col2,col3...) VALUES (col1_val,col2_val,col3_val...);

-- 插入或更新
-- 如果希望插入新记录时，记录存在则更新该记录
INSERT INTO table_name (col1,col2,col3...) VALUES (col1_val,col2_val,col3_val...) ON DUPLICATE KEY UPDATE col1 = col1_val, col2 = col2_val,col3 = col3_val ...;

-- 插入或忽略
-- 如果希望插入新记录时，记录存在则忽略
INSERT IGNORE INTO table_name (col1,col2,col3..) VALUES (col1_val,col2_val,col3_val...);

-- 快照
-- 对一个表进行快照
CREATE TABLE table_name_snapshot SELECT * FROM table_name;

-- 对一个表的部分数据进行快照
CREATE TABLE table_name_part_snapshot SELECT * FROM table_name WHERE col1 = col1_val;

-- 写入查询结果集
-- 将查询结果集写入到表中
CREATE TABLE statistics(
    id BIGINT NOT NULL AUTO_INCREMENT,
    class_id BIGINT NOT NULL,
    average DOUBLE NOT NULL,
    PRIMARY KEY (id)
);

INSERT INTO statistics (class_id,average) SELECT class_id, AVG(score) FROM students GROUP BY class_id; 

-- 强制使用指定索引(指定的索引必须存在)
SELECT * FROM students FORCE INDEX (idx_class_id) WHERE class_id = 1 ORDER BY id DESC;

11 事务

多条语句作为一个整体进行操作的功能成为数据库事务

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

1. 事务ACID特性(用于保证多条SQL的全部执行)

• Automicity: 原子性，将所有SQL作为原子工作单元执行，要么全部执行，要么全部不执行
• Consistency: 一致性，事务完成后，所有数据的状态都是一致的
• Isolation: 隔离性，如果有多个事务并发执行，每个事务作出的修改必须与其他事务隔离
• Durability: 持久性，事务完成后，对数据库数据的修改将呗持久化存储

-- 开始事务
BEGIN;
-- SQL STR1
-- SQS STR2

-- 提交事务
COMMIT;

-- 回滚事务
ROLLBACK;

2. 隔离级别

对于两个并发执行的事务，如果涉及到操作同一条记录时，可能会发生问题，并发操作会带来数据的不一致性，包括脏读、不可重复读、幻读等。

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

Isolation Level	脏读(Dirty Read)	不可重复读(Non Repeatable Read)	幻读(Phantom Read)	锁行为
Read Uncommitted	Yes	Yes	Yes	几乎不加锁
Read Committed	-	Yes	Yes	查询不加锁，更新加锁
Repeatable Read	-	-	Yes(使用间隙锁防幻读)	查询使用一致性快照，更新加锁
Serializable	-	-	-	查询也会加共享锁，导致性能差

3. Read Uncommitted

隔离级别最低的事务级别。

这个级别下，一个事务可能会读到另一个事务更新后但未提交的数据。如果另一个事务回滚，那么当前事务读到的数据就是脏数据，即脏读(Dirty Read)。

测试方法：

-- mysql1 代指客户端连接1, mysql2 代指客户端连接2
-- mysql1设置事务隔离等级为Read Uncommitted
mysql1> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- mysql1开启事务A
mysql1> BEGIN;
-- mysql1更新数据
mysql1> UPDATE students SET name = 'Bob' WHERE id = 1;

-- mysql2设置事务隔离等级为Read Uncommitted
mysql2> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- mysql2开启事务B
mysql2> BEGIN;
-- mysql2读取数据
mysql2> SELECT * FROM students WHERE id = 1;

-- mysql1回滚事务A
mysql1> ROLLBACK;

-- mysql2再读取数据
mysql2> SELECT * FROM students WHERE id = 1;
-- mysql2提交事务
mysql2> COMMIT;

4. Read Committed

该隔离级别下，一个事务不会读到另一个事务还没提交的数据，但可能会遇到不可重复读(Non Repeatable Read)的问题。

不可重复读：在一个事务内，多次读同一数据，在这个事务还没结束时，如果另一个事务恰好修改了这个数据，那么在第一个事务中，两次读取的数据就可能不一致。

测试方法：

-- mysql1 代指客户端连接1, mysql2 代指客户端连接2
-- mysql1设置事务隔离等级为Read Committed
mysql1> SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- mysql1开启事务A
mysql1> BEGIN;

-- mysql2设置事务隔离等级为Read Committed
mysql2> SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- mysql2开启事务B
mysql2> BEGIN;
-- mysql2读取数据
mysql2> SELECT * FROM students WHERE id = 1; -- Alice

-- mysql1更新数据
mysql1> UPDATE students SET name = 'Bob' WHERE id = 1;
-- mysql1提交事务A
mysql1> COMMIT;

-- mysql2再读取数据
mysql2> SELECT * FROM students WHERE id = 1; -- Bob
-- mysql2提交事务
mysql2> COMMIT;

5. Repeatable Read

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

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

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

测试方法：

-- mysql1 代指客户端连接1, mysql2 代指客户端连接2
-- mysql1设置事务隔离等级为REPEATABLE READ
mysql1> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- mysql1开启事务A
mysql1> BEGIN;

-- mysql2设置事务隔离等级为REPEATABLE READ
mysql2> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- mysql2开启事务B
mysql2> BEGIN;
-- mysql2读取数据
mysql2> SELECT * FROM students WHERE id = 99; -- empty

-- mysql1新增数据
mysql1> INSERT INTO students (id,name) VALUES (99,'Bob');
-- mysql1提交事务A
mysql1> COMMIT;

-- mysql2再读取数据
mysql2> SELECT * FROM students WHERE id = 99; -- empty
-- mysql2更新数据
mysql2> UPDATE students SET name = 'Alice' WHERE id = 99; -- 1 row affected
-- mysql2再查询数据
mysql2> SELECT * FROM students WHERE id = 99; -- Alice
-- mysql2提交事务
mysql2> COMMIT;

6. Serializable

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

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