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(10) 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(1)，也能存 20 位。bigint 8 字节，最长不超过 20 位（922亿亿）。当 id 为 1 时，也是占 8 个字节。
varchar 为可变长度，varchar(10) 后面的 10 也指的是字段最长长度。
- varchar(10) 最多能保存 10 个字符，varchar(100) 最多能保存 100 个字符。name 定义为 varchar(10)，当数据为「张三」时，占用 6 个字节（每个汉字 3 字节），而不会占用 30 个字节。
- 定义时最好只冗余 10%，不要冗余太多，查询时会创建临时表，将 varchar 转为 char，对性能有损耗
- 当字符长度没有变化时，推荐用 char
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; # 查看隔离级别
SELECT @@TX_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		UPDATE students SET name = ‘Alice’ WHERE id = 99; – 1 row affected
8		SELECT * FROM students WHERE id = 99; – Alice
9		COMMIT;
其实幻读影响不大，没有脏读和不可重复读的问题，Mysql 默认隔离级别就是 Repeatable Commited。

可重复读隔离级别使用读未提交的测试用例的结果：两次读取都是 Alice
可重复读隔离级别使用读已提交的测试用例的结果：两次读取都是 Alice

时刻	事务A	事务B
1	SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
2	BEGIN;
3	SELECT stock FROM products WHERE product_id = 1; // 10
4		SELECT stock FROM products WHERE product_id = 1; // 10
5	UPDATE products SET stock = stock - 5 WHERE product_id = 1;
6	COMMIT;
7		SELECT stock FROM products WHERE product_id = 1; // 10
8		INSERT INTO orders (product_id, quantity) VALUES (1, 5); // 成功
9		UPDATE products SET stock = stock - 5 WHERE product_id = 1;
10
11	SELECT stock FROM products WHERE product_id = 1; // 5

Serializable（串行化）

串行操作，没有并发。

时刻	事务A	事务B
1	SET TRANSACTION ISOLATION LEVEL Serializable;	SET TRANSACTION ISOLATION LEVEL Serializable;

上面三种情况均会报错。

MVVC

数据库语句顺序

写的顺序：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;

主键

索引