MySQL 的 SQL 笔记
主键
选取主键的一个基本原则是：不使用任何业务相关的字段（id）作为主键。修改了主键，会造成一系列的影响
类型：
- 自增整数类型（id）：
BIGINT NOT NULL AUTO_INCREMENT。 - 全局唯一 GUID 类型
注意：如果使用 INT 自增类型，那么当一张表的记录数超过 2147483647（约21亿）时，会达到上限而出错。使用 BIGINT自增类型则可以最多约 922 亿亿条记录。
索引
主键默认含有索引。
即该列的值如果越互不相同，那么索引效率越高。
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_email (email)
只建唯一约束，不建索引
ALTER TABLE students
ADD CONSTRAINT uni_email UNIQUE (email)
查询数据
条件查询
SELECT * FROM <表名> WHERE <条件表达式> (AND / OR)
| 条件 | 表达式举例1 | 表达式举例2 | 说明 |
|---|---|---|---|
| 使用 <> 判断不相等 | score <> 80 | name <> 'abc' | 常用 <> 代替 Not |
| 使用 LIKE 判断相似 | name LIKE 'ab%' | name LIKE '%bc%' | %表示任意字符，例如'ab%'将匹配'ab'，'abc'，'abcd' |
投影查询
SELECT id, score points, name FROM students; // points 为别名
排序
SELECT id, name, gender, score FROM students ORDER BY score DESC, gender; // 表示先按 score 列倒序，如果有相同分数的，再按 gender 列排序。
- ORDER BY 默认正序，从小到大，DESC descend 倒序
分页查询
SELECT id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 0; // 第 1 页，每页 3 条
LIMIT 3 OFFSET 3; // 第 2 页，每页 3 条
LIMIT <M> OFFSET <N>显示从 N+1 行开始，后 M 条记录。
聚合查询
SELECT COUNT(*) boys FROM students WHERE gender = 'M';
- COUNT(*)、COUNT(id)、可用 WHERE 条件
- 其他函数：SUM、AVG、MAX、MIN
- 如果是字符类型，MAX() 和 MIN() 会返回排序最后和排序最前的字符
- 结合 GROUP BY 分组：
SELECT class_id, COUNT(*) num FROM students GROUP BY class_id; - 多个列分组：
SELECT class_id, gender, COUNT(*) num FROM students GROUP BY class_id, gender;
多表查询
SELECT * FROM students, classes; // 同时查询 students 表和 classes 表的“乘积”
连接查询
- 先确定一个主表作为结果集，然后，把其他表的行有选择性地「连接」在主表结果集上。
SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
FROM students s
INNER JOIN classes c
ON s.class_id = c.id;
- 内连接：只查询符合两个表的结果，取交集
- 右外连接 RIGHT OUTER JOIN：查询符合右边表所有结果，空余用 NULL 填充
- 左外连接 LEFT OUTER JOIN：查询符合主表所有结果，空余用 NULL 填充
- FULL OUTER JOIN，并集
修改数据
INSERT
INSERT INTO <表名> (字段1, 字段2, ...) VALUES (值1, 值2, ...);
INSERT INTO students (class_id, name, gender, score) VALUES
(1, '大宝', 'M', 87),
(2, '二宝', 'M', 81);
UPDATE
UPDATE <表名> SET 字段1=值1, 字段2=值2, ... WHERE ...;
- WHERE 跟 SELECT 的 WHERE 一样使用
UPDATE students SET score=60;修改表的所有数据，先用 SELECT 语句测试
DELETE
DELETE FROM <表名> WHERE ...;
DELETE FROM students;删除表所有数据
库/表/列 SQL
也可以只安装 MySQL Client，然后连接到远程 MySQL Server。假设远程 MySQL Server 的 IP 地址是 10.0.1.99，那么就使用 -h 指定 IP 或域名：
mysql -h 10.0.1.99 -u root -p
库
SHOW DATABASES;
CREATE DATABASE test;
use test;
DROP DATABASE test;
表
SHOW TABLES;
CREATE TABLE `students` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT "ID",
`class_id` bigint(20) NOT NULL DEFAULT 0 COMMENT "班级 ID",
`name` varchar(100) NOT NULL DEFAULT '' COMMENT "姓名",
`gender` varchar(1) NOT NULL DEFAULT '男' COMMENT "性别",
`score` int(11) NOT NULL DEFAULT 100 COMMENT "分数",
`created_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`updated_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT "学生表";
DESC students; // describe，查看表结构
SHOW CREATE TABLE students; // 查看创建表的语句
DROP TABLE students; // 删除表
- bigint(20) 后面的 20 指的是字段最长长度，只是起提示作用。bigint 8 字节，最长不超过 20 位（922亿亿）
- NOT NULL：插入数据时，必须有值
- CHARSET( character set ) utf8 与 utf8mb4 的区别，char utf8 默认为 3 个字节，utf8mb4 默认为 4 个字节
- COLLATE=utf8mb4_0900_ai_ci
- ENGINE=InnoDB DEFAULT：默认引擎 InnoDB
列
ALTER TABLE students ADD COLUMN birth VARCHAR(10) NOT NULL; // 给 students 表新增一列 birth
ALTER TABLE students CHANGE COLUMN birth birthday VARCHAR(20) NOT NULL; // 把列名改为 birthday，类型改为 VARCHAR(20)
ALTER TABLE students MODIFY COLUMN birth VARCHAR(20) DEFAULT '' NOT NULL; // 设置默认值为 ''
ALTER TABLE studens DROP COLUMN birthday; // 删除 birthday 列
- https://www.w3schools.com/sql/sql_alter.asp
EXIT // 退出
实用 SQL
插入或替换
REPLACE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);
插入或更新
INSERT INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99) ON DUPLICATE KEY UPDATE name='小明', gender='F', score=99;
插入或忽略：存在就忽略
INSERT IGNORE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);
快照：复制当前表的数据到一个新表
-- 对 class_id=1 的记录进行快照，并存储为新表 students_of_class1:
CREATE TABLE students_of_class1 SELECT * FROM students WHERE class_id=1;
写入查询结果集
INSERT INTO statistics (class_id, average) SELECT class_id, AVG(score) FROM students GROUP BY class_id;
处理数据表被锁
show open tables where in_use>0; // 查看表是否被锁
show processlist // 查看所有进程
kill id // 杀进程
用户
SELECT DISTINCT concat('User:''',USER,'''@''',HOST,''';') AS QUERY FROM mysql. USER; // 查看数据库的所有用户
show grants for 'label'@'%' // 查看用户所有表的权限
CREATE USER 'label'@'%' IDENTIFIED BY 'label123'; // 创建用户
CREATE USER 'label'@'localhost' IDENTIFIED BY 'label123'; // 创建用户
事务
这种把多条语句作为一个整体进行操作的功能，被称为数据库 「事务」。
对于单条SQL语句，数据库系统自动将其作为一个事务执行，这种事务被称为隐式事务。
使用BEGIN开启一个事务，使用COMMIT提交一个事务，这种事务被称为 显式事务
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT; // 如果 COMMIT 语句执行失败了，整个事务也会失败。
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
ROLLBACK; // 回滚前面执行的 sql
事务有四大特性：
- 原子性（Atomicity）：不可分割，要么成功，要么失败；
- 一致性（Consistency）：事务完成后，所有数据的状态是一致的；
- 隔离性（Isolation）：并发执行的事务，对数据的操作要具有隔离性；
- 持久性（Durability）：事务完成后，数据就持久化到数据库中
事务有隔离性有级别，共 4 种，隔离级别由低到高
| Isolation Level | 脏读（Dirty Read） | 不可重复读（Non Repeatable Read） | 幻读（Phantom Read） |
|---|---|---|---|
| Read Uncommitted | Yes | Yes | Yes |
| Read Committed | - | Yes | Yes |
| Repeatable Read | - | - | Yes |
| Serializable | - | - | - |
默认测试数据库
mysql> select * from students;
+----+-------+
| id | name |
+----+-------+
| 1 | Alice |
+----+-------+
1 row in set (0.00 sec)
INSERT INTO students(id, name) VALUES (1, 'Alice');
select @@session.transaction_isolation # 查看隔离级别
SET @@session.transaction_isolation = 'READ-UNCOMMITTED'; # 切换隔离级别读为提交
Read Uncommitted（读未提交）
一个事务可以读到另一个事务更新，但未提交的数据。如果另一个事务回滚，当前读取的值就是脏数据，称为脏读。
| 时刻 | 事务 A | 事务 B |
|---|---|---|
| 1 | SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; | SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; |
| 2 | BEGIN; | BEGIN; |
| 3 | UPDATE students SET name = 'Bob' WHERE id = 1; | |
| 4 | SELECT * FROM students WHERE id = 1; // 'Bob' | |
| 5 | ROLLBACK; | |
| 6 | SELECT * FROM students WHERE id = 1; // 'Alice' | |
| 7 | COMMIT; |
Read Commited（读已提交）
存在不可重复读的问题，事务重复读时，可能数据不一致。
| 时刻 | 事务A | 事务B |
|---|---|---|
| 1 | SET TRANSACTION ISOLATION LEVEL READ COMMITTED; | SET TRANSACTION ISOLATION LEVEL READ COMMITTED; |
| 2 | BEGIN; | BEGIN; |
| 3 | SELECT * FROM students WHERE id = 1; // 'Alice' | |
| 4 | UPDATE students SET name = 'Bob' WHERE id = 1; | |
| 5 | COMMIT; | |
| 6 | SELECT * FROM students WHERE id = 1; // 'Bob' | |
| 7 | COMMIT; |
- 读已提交隔离级别使用读未提交的测试用例的结果：两次读取都是 Alice
Repeatable Commited（可重复读）
存在幻读的问题，幻读就是没有读取到的记录，以为不存在，但插入时将失败，再次读取时又能读取到数据。
| 时刻 | 事务A | 事务B |
|---|---|---|
| 1 | SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; | SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; |
| 2 | BEGIN; | BEGIN; |
| 3 | SELECT * FROM students WHERE id = 99; // Empty | |
| 4 | INSERT INTO students (id, name) VALUES (99, 'Bob'); | |
| 5 | COMMIT; | |
| 6 | SELECT * FROM students WHERE id = 99; // Empty | |
| 7 | INSERT INTO students(id, name) values(99, Alice) // 失败 | |
| 8 | SELECT * FROM students WHERE id = 99; // Alice | |
| 9 | COMMIT; |
其实幻读影响不大，没有脏读和不可重复读的问题，Mysql 默认隔离级别就是 Repeatable Commited。
- 可重复读隔离级别使用读未提交的测试用例的结果：两次读取都是 Alice
- 可重复读隔离级别使用读已提交的测试用例的结果：两次读取都是 Alice
Serializable（串行化）
串行操作，没有并发。
| 时刻 | 事务A | 事务B |
|---|---|---|
| 1 | SET TRANSACTION ISOLATION LEVEL Serializable; | SET TRANSACTION ISOLATION LEVEL Serializable; |
上面三种情况均会报错。
设置隔离级别
数据库语句顺序
- 写的顺序：select ... from... where.... group by... having... order by.. limit [offset,] (rows)
- 执行顺序：from... where...group by... having.... select ... order by... limit
mysql> select host,user from user;
SELECT host,user,password FROM user;
