共计 2268 个字符,预计需要花费 6 分钟才能阅读完成。
因为 BC 一条龙工作搭建须要,最近做了很多 BI 取数的工作,须要用到一些比拟高级的 SQL 技巧,总结了一下工作中用到的一些比拟骚的进阶技巧,特此记录一下,以不便本人当前搭建网站一条龙查阅,次要目录如下:
SQL 的书写标准
SQL 的一些进阶应用技巧
SQL 的优化办法
SQL 的书写标准
在介绍一些技巧之前,有必要强调一下标准,这一点我发现工作中常常被人疏忽,其实遵循好的标准可读性会好很多,应该遵循哪些标准呢
1、表明要有意义,且规范 SQL 中规定表名的第一个字符应该是字母。
2、正文,有单行正文和多行正文,如下
多行正文很多人不晓得,这种写法不仅能够用来增加真正的正文,也能够用来正文代码,十分不便
3、缩进
就像写 Java,Python 等编程语言一样,SQL 也应该有缩进,良好的缩进对晋升代码的可读性帮忙很大,以下别离是好的缩进与坏的缩进示例
4、空格
代码中应该适当留有一些空格,如果一点不留,代码都凑到一起,逻辑单元不明确,浏览的人也会产生额定的压力,以下别离是是好的与坏的示例
5、大小写
关键字应用大小写,表明列名应用小写,如下
花了这么多工夫强调标准,有必要吗,有!好的标准让代码的可读性更好,更有利于团队单干,之后的 SQL 示例都会遵循这些标准。
SQL 的一些进阶应用技巧
1、巧用 CASE WHEN 进行统计
来看看如何巧用 CASE WHEN 进行定制化统计, 假如咱们有如下的需要,心愿依据右边各个市的人口统计每个省的人口
应用 CASE WHEN 如下
2、巧用 CASE WHEN 进行更新
当初某公司员工工资信息表如下:
当初公司出台了一个奇葩的规定
对以后工资为 1 万以上的员工,降薪 10%。
对以后工资低于 1 万的员工,加薪 20%。
一些人不假思索可能写出了以下的 SQL:
这么做其实是有问题的,什么问题,对小明来说,他的工资是 10500,执行第一个 SQL 后,工资变为 10500 0.9 = 9450, 紧接着又执行条件 2,工资变为了 9450 1.2 = 11340,反而涨薪了!
如果用 CASE WHEN 能够解决此类问题,如下:
3、巧用 HAVING 子句
个别 HAVING 是与 GROUP BY 联合应用的,但其实它是能够独立应用的,假如有如下表,第一列 seq 叫间断编号,但其实有些编号是缺失的,怎么晓得编号是否缺失呢,
用 HAVING 示意如下:
SELECT ‘ 存在缺失的编号 ’ AS gap
FROM SeqTbl
HAVING COUNT(*) <> MAX(seq);
1.
2.
3.
4、自连贯
针对雷同的表进行的连贯被称为“自连贯”(self join),这个技巧经常被人们漠视,其实是有挺多妙用的
1、删除反复行
上图中有三个句子,须要把这些反复的行给删掉,用如下自连贯能够解决:
2、排序
在 db 中,咱们常常须要按分数,人数,销售额等进行排名,有 Oracle, DB2 中能够应用 RANK 函数进行排名,不过在 MySQL 中 RANK 函数未实现,这种状况咱们能够应用自连贯来实现, 如对以下 Products 表按价格高下进行排名
应用自连贯能够这么写:
后果如下:
5、巧用 COALESCE 函数
此函数作用返回参数中的第一个非空表达式,假如有如下商品,咱们从新格式化一样,如果 city 为 null,代表商品不在此城市发行,但咱们在展现后果的时候不想展现 null,而想展现 ‘N/A’, 能够这么做:
SELECT
COALESCE(city, 'N/A')
FROM
customers;
1.
2.
3.
4.
SQL 性能优化技巧
1、参数是子查问时,应用 EXISTS 代替 IN
如果 IN 的参数是(1,2,3)这样的值列表时,没啥问题,但如果参数是子查问时,就须要留神了。比方,当初有如下两个表:
当初咱们要查出同时存在于两个表的员工,即田中和铃木,则以下用 IN 和 EXISTS 返回的后果是一样,然而用 EXISTS 的 SQL 会更快:
为啥应用 EXISTS 的 SQL 运行更快呢,有两个起因
能够 ` 用到索引,如果连贯列 (id) 上建设了索引,那么查问 Class_B 时不必查理论的表,只需查索引就能够了。
如果应用 EXISTS,那么只有查到一行数据满足条件就会终止查问,不必像应用 IN 时一样扫描全表。在这一点上 NOT EXISTS 也一样
另外如果 IN 前面如果跟着的是子查问,因为 SQL 会先执行 IN 前面的子查问,会将子查问的后果保留在一张长期的工作表里(内联视图),而后扫描整个视图,显然扫描整个视图这个工作很多时候是十分耗时的,而用 EXISTS 不会生成长期表。
当然了,如果 IN 的参数是子查问时,也能够用连贯来代替,如下:
— 应用连贯代替 IN SELECT A.id, A.name
FROM Class_A A INNER JOIN Class_B B ON A.id = B.id;
1.
2.
用到了「id」列上的索引,而且因为没有子查问,也不会生成长期表
2、防止排序
SQL 是申明式语言,即对用户来说,只关怀它能做什么,不必关怀它怎么做。这样可能会产生潜在的性能问题:排序,会产生排序的代表性运算有上面这些
GROUP BY 子句
ORDER BY 子句
聚合函数 (SUM、COUNT、AVG、MAX、MIN)
DISTINCT
汇合运算符 (UNION、INTERSECT、EXCEPT)
窗口函数 (RANK、ROW_NUMBER 等)
如果在内存中排序还好,但如果内存不够导致须要在硬盘上排序上的话,性能就会急剧下降,所以咱们须要缩小不必要的排序。怎么做能够缩小排序呢。
1、应用汇合运算符的 ALL 可选项
SQL 中有 UNION,INTERSECT,EXCEPT 三个汇合运算符,默认状况下,这些运算符会为了防止反复数据而进行排序,比照一下应用 UNION 运算符加和不加 ALL 的状况。