学习之路

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
---- 视图

---- 存储过程
-- 特点：封装、复用、预先编译
-- > 命令行创建存储过程需要自定义分隔符
-- > 包含 in、out、inout 三种参数
-- > 给变量赋值都需要 select into 语句
-- > 每次只能给一个变量赋值，不支持集合操作
/*
DELIMITER //

CREATE PROCEDURE mypro(OUT ret INT)
BEGIN
DECLARE y INT;
SELECT SUM(col1) FROM `table` INTO y;
SELECT y*y INTO ret;
END //

DELIMITER ;

CALL mypro(@ret)
SELECT @ret
*/

---- 游标
/*
delimiter //

create procedure myprocedure(out ret int)
begin
declare done boolean default 0;

declare mycursor cursor for
select col1 from mytable;
# 定义了一个 continue handler，当 sqlstate '02000' 这个条件出现时，会执行 set done = 1
declare continue handler for sqlstate '02000' set done = 1;

open mycursor;

repeat
fetch mycursor into ret;
select ret;
until done end repeat;

close mycursor;
end //

delimiter;
*/

---- 触发器
-- > 触发器会在某个表执行以下语句时而自动执行: DELETE、INSERT、UPDATE。
-- > 触发器必须指定在语句执行之前还是之后自动执行，之前执行使用 BEFORE 关键字，之后执行使用 AFTER 关键字。
-- > BEFORE 用于数据验证和净化，AFTER 用于审计跟踪，将修改记录到另外一张表中。
-- > INSERT 触发器包含一个名为 NEW 的虚拟表
-- > DELETE 触发器包含一个名为 OLD 的虚拟表，并且是只读的
-- > UPDATE 触发器包含一个名为 NEW 和一个名为 OLD 的虚拟表，其中 NEW 是可以被修改的，而 OLD 是只读的
-- > MySQL 不允许在触发器中使用 CALL 语句，也就是不能调用存储过程

CREATE TRIGGER mytrigger AFTER INSERT ON mytable
FOR EACH ROW SELECT NEW.col into @result;

SELECT @result;

---- 事务管理
-- 不能回退 SELECT 语句，回退 SELECT 语句也没意义；也不能回退 CREATE 和 DROP 语句。
-- MySQL 的事务提交默认是隐式提交，每执行一条语句就把这条语句当成一个事务然后进行提交。当出现 START TRANSACTION 语句时，会关闭隐式提交；当 COMMIT 或 ROLLBACK 语句执行后，事务会自动关闭，重新恢复隐式提交。
-- 通过设置 autocommit 为 0 可以取消自动提交；autocommit 标记是针对每个连接而不是针对服务器的。
-- 如果没有设置保留点，ROLLBACK 会回退到 START TRANSACTION 语句处；如果设置了保留点，并且在 ROLLBACK 中指定该保留点，则会回退到该保留点。
START TRANSACTION
// ...
SAVEPOINT delete1
// ...
ROLLBACK TO delete1
// ...
COMMIT

常见问题

技巧

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 找到某一列最值对应的字段
-- https://leetcode.cn/problems/the-airport-with-the-most-traffic/
# 窗口函数 rank / dense_rank
# all / any 嵌套

-- 判空
# COALESCE
# IFNULL
# IF(? is null, ..., ...)

-- 是否（多次）含有某个字符
# 用 REPLACE(ch, '') 跟原字符串的长度作比较


行列互换

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
CREATE TABLE t (
    cid varchar(4),
    sname varchar(10),
    course varchar(10),
    score int
);

INSERT INTO
    t
VALUES ('01', 'Andy', 'Chinese', 78),
    ('01', 'Andy', 'English', 92),
    ('01', 'Andy', 'Math', 50),
    ('02', 'Bella', 'English', 33),
    ('02', 'Bella', 'Math', 70),
    ('02', 'Bella', 'Chinese', 96),
    ('03', 'Caro', 'Chinese', 66),
    ('03', 'Caro', 'Math', 54),
    ('03', 'Caro', 'English', 62),
    ('04', 'David', 'Chinese', 90),
    ('04', 'David', 'Math', 80),
    ('04', 'David', 'English', 95);

-- 示例题目：
-- https://leetcode.cn/problems/products-price-for-each-store/

-- pivot ？

SQL 优化

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
SELECT name FROM user WHERE id not IN(1, 3, 4);
-- 负向查询不能使用索引
SELECT name FROM user WHERE id IN (2, 5, 6);

SELECT name FROM user WHERE name LIKE '%zhangsan';
-- 前导模糊查询不能使用索引
# 可以考虑使用 Lucene 等全文索引工具来代替频繁的模糊查询。
SELECT name FROM user WHERE name LIKE 'zhangsan%';

-- 数据区分不明显的不建议创建索引

-- 字段的默认值不要为 NULL

SELECT name FROM user WHERE FROM_UNIXTIME(create_time) < CURDATE();
-- 在字段上进行计算不能命中索引
SELECT name FROM user WHERE create_time < FROM_UNIXTIME(CURDATE());

SELECT username FROM user WHERE pwd = 'axsedf1sd';
-- 最左前缀问题
-- 如果给 user 表中的 username pwd 字段创建了复合索引那么使用以下SQL 都是可以命中索引:
SELECT username
FROM user
WHERE
    username = 'zhangsan'
    AND pwd = 'axsedf1sd';

SELECT username
FROM user
WHERE
    pwd = 'axsedf1sd'
    AND username = 'zhangsan';

SELECT username FROM user WHERE username = 'zhangsan';

-- 如果明确知道只有一条记录返回
-- 可以提高效率，可以让数据库停止游标移动。
SELECT name FROM user WHERE username = 'zhangsan' LIMIT 1;

-- 这样虽然可以查出数据，但是会导致全表扫描。
SELECT name FROM user WHERE telno = 18722222222;
-- 不要让数据库帮我们做强制类型转换
SELECT name FROM user WHERE telno = '18722222222';

-- 如果需要进行 JOIN 的字段两表的字段类型要相同
-- 不然也不会命中索引