Mysql 使用技巧

case when

-- 当表达式1为 TRUE 时，整个 CASE 表达式的值是结果1；当表达式2为 TRUE 时，整个 CASE 表达式的值是结果2
-- 依此类推，当表达式n 为 TRUE 时，整个 CASE 表达式的值就是结果n
-- 如果所有的表达式都不为 TRUE，那整个 CASE 表达式的值就是 ELSE 之后的默认结果
CASE WHEN 表达式1 THEN 结果1 [WHEN 表达式2 THEN 结果2...] [ELSE 默认结果] END
-- 当待比较表达式的值和表达式1的值相同时，整个 CASE 表达式的值就是结果1
-- 当待比较表达式的值和表达式2的值相同时，整个 CASE 表达式的值就是结果2
-- 依此类推，当待比较表达式的值和表达式1、n 的值相同时，整个 CASE 表达式的值就是结果n
-- 如果所有都不相等，那整个 CASE 表达式的值就是 ELSE 之后的默认结果
CASE 待比较表达式 WHEN 表达式1 THEN 结果1 [WHEN 表达式2 THEN 结果2...] [ELSE 默认结果] END

group by

-- 使用 GROUP BY 子句完成分组的过程，然后通过聚集函数统计各分组信息。例如，统计各科目的平均成绩：
mysql> SELECT subject, AVG(score) FROM student_score GROUP BY subject;
+----------+------------+
| subject  | AVG(score) |
+----------+------------+
| 操作系统 | 73.2500    |
| 数据结构 | 73.0000    |
+----------+------------+
2 rows in set (0.02 sec)
-- 分组的存在是为了方便统计各分组的信息，如果查询列表中有一条既不是分组也不是聚集函数的列，那么该值是随机的
-- number 既不是分组也不是聚集函数的列，它的值是分组中随机取出的一条记录的数据
mysql> SELECT number, subject, AVG(score) FROM student_score GROUP BY subject;
+----------+----------+------------+
| number   | subject  | AVG(score) |
+----------+----------+------------+
| 20210101 | 操作系统 | 73.2500    |
| 20210101 | 数据结构 | 73.0000    |
+----------+----------+------------+
2 rows in set (0.02 sec)

注意：如果分组列中含有NULL，那么NULL也会作为一个独立的分组存在。

带有 WHERE 子句的分组

-- 如果想在划分分组之前就将某些记录过滤掉，这时可以使用 WHERE 子句
-- 将低于 60 分的记录去掉之后，再统计平均成绩
mysql> SELECT subject, AVG(score) FROM student_score WHERE score >= 60 GROUP BY subject;
+----------+------------+
| subject  | AVG(score) |
+----------+------------+
| 操作系统 | 82.3333    |
| 数据结构 | 89.0000    |
+----------+------------+
2 rows in set (0.03 sec)

作用于分组的过滤条件

-- 如果想对分组进行过滤，可以使用 HAVING 子句
-- 使用与分组列有关的条件进行分组过滤，也可以直接将该条件放在 WHERE 子句中，效果是一样的
-- 统计数据结构科目的平均成绩
mysql> SELECT subject, AVG(score) FROM student_score GROUP BY subject HAVING subject = '数据结构';
+----------+------------+
| subject  | AVG(score) |
+----------+------------+
| 数据结构 | 73.0000    |
+----------+------------+
1 row in set (0.04 sec)
-- 使用作用于分组的聚集函数有关的条件进行分组过滤，该条件不能出现在 WHERE 子句中，否则会报错
-- 统计最高分大于 98 分的科目的平均成绩
mysql> SELECT subject, AVG(score) FROM student_score GROUP BY subject HAVING MAX(score) > 98;
+----------+------------+
| subject  | AVG(score) |
+----------+------------+
| 数据结构 | 73.0000    |
+----------+------------+
1 row in set (0.05 sec)

嵌套分组

-- 可以将一个分组继续划分成更小的分组，只需要在 GROUP BY 子句中把各个分组列依次写上，以逗号分隔
-- 先按照 department 进行分组，对于同一分组，再按照 major 划分为更小的分组
mysql> SELECT department, major, COUNT(*) FROM student_info GROUP BY department, major;
+------------+------------------+----------+
| department | major            | COUNT(*) |
+------------+------------------+----------+
| 计算机学院 | 计算机科学与工程 |        2 |
| 计算机学院 | 软件工程         |        2 |
| 航天学院   | 飞行器设计       |        1 |
| 航天学院   | 电子信息         |        1 |
+------------+------------------+----------+
4 rows in set (0.03 sec)

插入数据

-- 可以将某个查询的结果集批量插入表中，下面语句先执行查询操作，再执行批量插入操作
INSERT INTO second_table(s, i) SELECT second_column, first_column FROM first_table WHERE first_column < 4;
-- 对于那些是主键或者具有 UNIQUE 约束的列或者列组合来说，它们不允许重复值的出现
-- 如果待插入记录中的值与已有的记录重复，会报错，此时可以使用 INSERT IGNORE 语法来忽略该记录的插入操作
INSERT IGNORE INTO first_table VALUES (1, '娃哈哈'), (7, 'ggg');
-- 对于那些是主键或者具有 UNIQUE 约束的列或者列组合来说，也可以选择更新已有的重复记录的值
-- INSERT IGNORE 会忽略待插入的记录，而 INSERT ... ON DUPLICATE KEY UPDATE 会修改重复记录的值
-- 记录 (1, '娃哈哈') 的 first_column 列值与已有的记录重复，将重复记录上的列值进行修改
INSERT INTO first_table VALUES(1, '哇哈哈') ON DUPLICATE KEY UPDATE second_column = '雪碧';
-- 可以使用 VALUES(列名) 的形式引用待插入记录中对应列的值
INSERT INTO first_table VALUES(2, '红牛'), (3, '橙汁') ON DUPLICATE KEY UPDATE second_column = VALUES(second_column);

REPLACE

-- REPLACE 和 INSERT 功能类似，但 REPLACE 会按照如下方式插入数据：
-- 1. 如果发现表中已经有此行数据（根据主键或者唯一索引判断）则先删除此行数据，然后插入新的数据
-- 2. 否则，直接插入新数据
-- 使用 INSERT 插入记录 (7, 'hhh')，UNIQUE 约束会导致插入失败
mysql> INSERT INTO first_table VALUES (7, 'hhh');
1062 - Duplicate entry '7' for key 'first_column'
-- 使用 REPLACE 插入记录 (7, 'hhh')，会先将已有记录 (7, 'ggg') 删除，然后插入 (7, 'hhh')
mysql> REPLACE INTO first_table VALUES (7, 'hhh');
Query OK, 2 rows affected (0.02 sec)