搜文章
推荐 原创 视频 Java开发 iOS开发 前端开发 JavaScript开发 Android开发 PHP开发 数据库 开发工具 Python开发 Kotlin开发 Ruby开发 .NET开发 服务器运维 开放平台 架构师 大数据 云计算 人工智能 开发语言 其它开发
Lambda在线 > 删库跑路小分队 > 【MySQL】优化进阶

【MySQL】优化进阶

删库跑路小分队 2020-05-24


This browser does not support music or audio playback. Please play it in WeChat or another browser. 【MySQL】优化进阶


流年笑掷 未来可期❤️

来日正长 后会有期❤️

好好生活 慢慢相遇❤️

布置作业 今晚想我❤️


【MySQL】优化进阶


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

头发没了


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

眉毛没了


浏览量没了


今天的话题

SQL进阶

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

这次换个新鲜的吧


🌈🌈🌈


先养眼,再看题

❤️

今天还是小姐姐


【MySQL】优化进阶


【MySQL】优化进阶


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

感觉还是技巧比较实用点

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


[toc]

SQL的书写规范

SQL的一些进阶实用技巧

    - CASE WHEN

    - 自关联

    - 巧用COALESCE函数

    - EXISTS


【MySQL】优化进阶


【MySQL】优化进阶


【MySQL】优化进阶

1,关于SQL的书写规范


> 毋庸置疑,类似于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. 大小写

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

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


【MySQL】优化进阶


【MySQL】优化进阶


【MySQL】优化进阶

SQL的进阶技巧


CASE WHEN


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

> 行专列,列转行


原数据

username
subject
score

张三

语文

80
张三 数学
90
张三 英语 70
张三 生物
85
李四
语文 80
李四 数学 92
李四 英语 76
李四 生物 88
王五
语文 60
王五 数学 82
王五 英语 96
王五 生物 78


需求展示

username
语文
数学 英语 生物
张三
90 80 70 85
李四
92
80
76
88
王五 82
60
96
78


-- 列转行-- 这里的max是为了将无数据的值设为0,避免出现nullSELECT 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_scoreGROUP BY username;


2. 使用CASE WHEN进行统计

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


原数据

city
num
郑州
100
焦作 200
长沙 300
衡阳
400


需求展示

province
num
河南
300
湖南
700


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



【MySQL】优化进阶


3. 使用CASE WHEN进行更新

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


name
salary
张三
9000
李四 10000
王五
11000


需求:

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

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

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

--条件1UPDATE table_aSET salary = salary * 0.9 WHERE salary >= 10000;--条件2UPDATE table_aSET salary = salary * 1.1WHERE salary < 10000;


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


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


UPDATE table_aSET salary =  CASE  WHEN salary >= 10000 THEN salary * 0.9 WHEN salary < 10000 THEN salary * 1.2 ELSE salary END;


【MySQL】优化进阶


【MySQL】优化进阶
【MySQL】优化进阶

自关联


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

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


1. 删除重复行


原数据

id
name
1
张三
2 李四
3
王五
4
张三
5 李四



需求展示

id
name
3
王五
4
张三
5
李四


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


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


2. 排序

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


原数据

name
score
张三
100
李四
90
王五 90
赵六 80


需求展示

name
score
rank
张三
100
1
李四 90
2
王五
90 2
赵六
80 4


思路:

排序从 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_1FROM table_a P1 ORDER BY rank_1;


【MySQL】优化进阶

【MySQL】优化进阶


【MySQL】优化进阶

巧用COALESCE函数


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


需求展示

id
name
1
null
2
张三
3
李四


注意

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

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


【MySQL】优化进阶


【MySQL】优化进阶


【MySQL】优化进阶

EXISTS


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


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


原数据


table_a

id
name
1
张三
2
李四
3
王五


table_b

id
name
1
张三
2
李四
4
赵六



需求展示

id
name
1
张三
2
李四


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

-- 用INSELECT * FROM table_aWHERE id IN (SELECT id  FROM table_b);
-- 用EXISTSSELECT *FROM table_a A WHERE EXISTS(SELECT * FROM table_b BWHERE A.id = B.id);


首先结论:EXISTS比IN快

其次原因如下

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


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


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


备注:如果自查询也行 

SELECT A.id, A.nameFROM table_a A INNER JOIN table_b B ON A.id = B.id;


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


【MySQL】优化进阶




 阳光明媚,清风徐来


关于SQL的一些其他技巧,也会持续输出

但是数据结构还是要完结的

算法包那块也会后续输出的



扫码关注我

个人博客 : www.guodaxiong.com


如果不曾见过阳光,我本可以忍受黑暗

 Hi GuoDaXiong 



我是狗子

祝你幸福

版权声明:本站内容全部来自于腾讯微信公众号,属第三方自助推荐收录。《【MySQL】优化进阶》的版权归原作者「删库跑路小分队」所有,文章言论观点不代表Lambda在线的观点, Lambda在线不承担任何法律责任。如需删除可联系QQ:516101458

文章来源: 阅读原文

相关阅读

关注删库跑路小分队微信公众号

删库跑路小分队微信公众号:gh_1b2d6d982829

删库跑路小分队

手机扫描上方二维码即可关注删库跑路小分队微信公众号

删库跑路小分队最新文章

精品公众号随机推荐

下一篇 >>

MySQL基础(一)