【MySQL】优化进阶

流年笑掷 未来可期❤️

来日正长 后会有期❤️

好好生活 慢慢相遇❤️

布置作业 今晚想我❤️

早上醒来，太累，按了会头皮

头发没了

中午醒来，太累，做了会眼保健操

眉毛没了

晚上醒来，太累，看了下公众号

浏览量没了

今天的话题是

SQL进阶

连着刷了几天数据结构，快吐了，

这次换个新鲜的吧

🌈🌈🌈

先养眼，再看题

❤️

今天还是小姐姐

这次主要说MySQL的一些使用技巧

感觉还是技巧比较实用点

tips：我真的被数据结构刷吐了

[toc]

SQL的书写规范

SQL的一些进阶实用技巧

    - CASE WHEN

    - 自关联

    - 巧用COALESCE函数

    - EXISTS

> 毋庸置疑，类似于Python和Java都要有读写规范的。

1. 表名第一个字符应该是字母

2. 注释，一定要有注释

-- 单行注释

-- name

SELECT name FROM table_a; 

/*

多行注释

查看name和age

*/

SELECT name,age FROM table_a; 

3. 缩进，简单讲就是让代码变好看

如果使用的navicat，可以点击美化代码

如果使用的DataGrip，可以点击格式化代码

-- 美化前

SELECT name,age FROM table_a WHERE name = 'Tom' and age > 10

-- 美化后

SELECT name，

    age

FROM table_a

WHERE name = 'Tom'

    and age > 10

4. 大小写

关键字和数据库自带的语句用大写，

表名列名首字母用小写，如果是多个单词，用下划线分隔开 

CASE WHEN

1. 使用CASE WHEN进行行列转换

> 行专列，列转行

原数据

需求展示

-- 列转行
-- 这里的max是为了将无数据的值设为0，避免出现null
SELECT username ,
    MAX(CASE subject WHEN '数学' THEN score ELSE 0 END ) 数学,
    MAX(CASE subject WHEN '语文' THEN score ELSE 0 END ) 语文,
    MAX(CASE subject WHEN '英语' THEN score ELSE 0 END ) 英语,
    MAX(CASE subject WHEN '生物' THEN score ELSE 0 END ) 生物
FROM stu_score
GROUP BY username;
复制

2. 使用CASE WHEN进行统计

> 做汇总统计，不按原数据分组，而是新创建分组 

原数据

需求展示

SELECT CASE city
      WHEN '郑州' THEN '河南' 
      WHEN '焦作' THEN '河南'
      WHEN '长沙' THEN '湖南' 
      WHEN '衡阳' THEN '湖南'
    ELSE '其他' END AS province,
    SUM(num) AS num
FROM table_a
GROUP BY province;
复制

3. 使用CASE WHEN进行更新

> 如果需要分情况进行更新操作，不必分开写，而是判断即可 

需求：

将薪资大于高于1万的下调10%，

将薪资低于1万的上调10%

如果仅仅单纯的通过判断，进行更新，如下 

--条件1
UPDATE table_a
SET salary = salary * 0.9 
WHERE salary >= 10000;
--条件2
UPDATE table_a
SET salary = salary * 1.1
WHERE salary < 10000;
复制

这时候会出现一个问题，就是薪资1万的李四，符合条件一，薪资就变成了9000，但又符合了条件二，薪资就又变成了9900，喵喵喵？ 

所以这时候建议用CASE WHEN判断即可，不用分开判断 

UPDATE table_a
SET salary = 
    CASE 
        WHEN salary >= 10000 THEN salary * 0.9
        WHEN salary < 10000 THEN salary * 1.2
    ELSE salary END;
复制

简单而言，就是自己join自己

> 或许大家都习惯于左右关联，其实自关联也很奶斯 

1. 删除重复行

原数据

需求展示

思路：通过自关联，查询出max(id)，删除掉小于max(id) 

DELETE FROM table_a P1
WHERE id < ( 
    SELECT MAX(P2.id) 
    FROM table_a P2 
    WHERE P1.id = P2.id 
    AND P1.name = P2.name 
);
复制

2. 排序

> 在工作学习中，可能我们需要分组函数，但是mysql不支持，因此用自关联 

原数据

需求展示

思路：

排序从 1 开始。如果已出现相同位次，则跳过之后的位次

但即使如此，我还是不推荐，不是那么灵活，

另外还可以查看我之前的公众号文章或博客关于分组排序的介绍 

tips：博客链接：www.guodaxiong.com

SELECT 
    P1.name,
    P1.score,
    (SELECT COUNT(P2.score)
     FROM table_a P2
     WHERE P2.score > P1.score) + 1 AS rank_1
FROM table_a P1 
ORDER BY rank_1;
复制

判断是否为NULL，如果有值就输出，如果没有输出NULL 

需求展示

注意

虽说用if方法判断，也可以实现NULL值

但是那个NULL是字符串而不是NULL 

如果是嵌套子查询，使用EXISTS代替IN

需求：找出两表都存在的name

原数据

table_a

table_b

需求展示

思路：找到a表员工，再看是否和b表关联 

-- 用IN
SELECT * 
FROM table_a
WHERE id IN (SELECT id 
               FROM  table_b);


-- 用EXISTS
SELECT *
FROM table_a A 
WHERE EXISTS
(SELECT * 
FROM table_b B
WHERE A.id = B.id);
复制

首先结论：EXISTS比IN快

其次原因如下

1. 可以利用索引，如果id创建了索引，那么就不在查表，而是查索引

2. 即使止损，只要查到有就结束查询，而不像IN一样，要扫描全表

3. IN会将自查询的结果放入到临时表（内存中）再不停的扫描，比较耗时，EXISTS不会。

备注：如果自查询也行 

SELECT A.id, A.name
FROM table_a A 
INNER JOIN table_b B 
ON A.id = B.id;
复制

用到了id的索引，且因为没有自查询，不会生成临时表 

我是狗子

祝你幸福