书签 分享 收藏 举报 版权申诉 / 19
上传文档赚钱

类型常用sql书写规范标准sql编写规范.docx

  • 上传人(卖家):tanweifu
  • 文档编号:5351344
  • 上传时间:2023-03-27
  • 格式:DOCX
  • 页数:19
  • 大小:292.34KB
  • 【下载声明】
    1. 本站全部试题类文档,若标题没写含答案,则无答案;标题注明含答案的文档,主观题也可能无答案。请谨慎下单,一旦售出,不予退换。
    2. 本站全部PPT文档均不含视频和音频,PPT中出现的音频或视频标识(或文字)仅表示流程,实际无音频或视频文件。请谨慎下单,一旦售出,不予退换。
    3. 本页资料《常用sql书写规范标准sql编写规范.docx》由用户(tanweifu)主动上传,其收益全归该用户。163文库仅提供信息存储空间,仅对该用户上传内容的表现方式做保护处理,对上传内容本身不做任何修改或编辑。 若此文所含内容侵犯了您的版权或隐私,请立即通知163文库(点击联系客服),我们立即给予删除!
    4. 请根据预览情况,自愿下载本文。本站不保证下载资源的准确性、安全性和完整性, 同时也不承担用户因使用这些下载资源对自己和他人造成任何形式的伤害或损失。
    5. 本站所有资源如无特殊说明,都需要本地电脑安装OFFICE2007及以上版本和PDF阅读器,压缩文件请下载最新的WinRAR软件解压。
    配套讲稿:

    如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。

    特殊限制:

    部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。

    关 键  词:
    常用 sql 书写 规范 标准 编写
    资源描述:

    1、SQL调优 | SQL 书写规范及优化技巧(上)编者荐语:文章主要讲解了SQL的书写规范,使用技巧和性能优化方法,小伙伴们可以查漏补缺。以下文章来源于码海 ,作者码海码海.一起进阶,一起牛逼!由于工作需要,最近做了很多 BI 取数的工作,需要用到一些比较高级的 SQL 技巧,总结了一下工作中用到的一些比较骚的进阶,特此记录一下,以方便自己查阅,主要目录如下:SQL 的书写规范在介绍一些技巧之前,有必要强调一下规范,这一点我发现工作中经常被人忽略,其实遵循好的规范可读性会好很多,应该遵循哪些规范呢1、 表名要有意义,且标准 SQL 中规定表名的第一个字符应该是字母。2、注释,有单行注释和多行注释

    2、,如下- 单行注释- 从SomeTable中查询col_1 SELECT col_1 FROM SomeTable;/*多行注释从 SomeTable 中查询 col_1 */SELECT col_1 FROM SomeTable;多行注释很多人不知道,这种写法不仅可以用来添加真正的注释,也可以用来注释代码,非常方便3、缩进就像写 Java,Python 等编程语言一样 ,SQL 也应该有缩进,良好的缩进对提升代码的可读性帮助很大,以下分别是好的缩进与坏的缩进示例- 好的缩进SELECT col_1, col_2, col_3, COUNT(*) FROM tbl_A WHERE col_1

    3、= a AND col_2 = ( SELECTMAX(col_2) FROM tbl_B WHERE col_3 = 100 ) GROUPBY col_1, col_2, col_3- 坏的示例SELECT col1_1, col_2, col_3, COUNT(*)FROM tbl_AWHERE col1_1 = aAND col1_2 = (SELECTMAX(col_2)FROM tbl_BWHERE col_3 = 100) GROUPBY col_1, col_2, col_34、空格代码中应该适当留有一些空格,如果一点不留,代码都凑到一起, 逻辑单元不明确,阅读的人也会产生额外

    4、的压力,以下分别是是好的与坏的示例- 好的示例SELECT col_1 FROM tbl_A A, tbl_B B WHERE ( A.col_1 = 100OR A.col_2 IN ( a, b ) ) AND A.col_3 = B.col_3;- 坏的示例SELECT col_1 FROM tbl_A A,tbl_B B WHERE (A.col_1=100OR A.col_2 IN (a,b) AND A.col_3=B.col_3;4、大小写关键字使用大小写,表名列名使用小写,如下SELECT col_1, col_2, col_3, COUNT(*) FROM tbl_A WHE

    5、RE col_1 = a AND col_2 = ( SELECTMAX(col_2) FROM tbl_B WHERE col_3 = 100 ) GROUPBY col_1, col_2, col_3花了这么多时间强调规范,有必要吗,有!好的规范让代码的可读性更好,更有利于团队合作,之后的 SQL 示例都会遵循这些规范。SQL 的一些进阶使用技巧一、巧用 CASE WHEN 进行统计来看看如何巧用 CASE WHEN 进行定制化统计,假设我们有如下的需求,希望根据左边各个市的人口统计每个省的人口使用 CASE WHEN 如下SELECTCASE pref_name WHEN长沙THEN湖南

    6、 WHEN衡阳THEN湖南 WHEN海口THEN海南 WHEN三亚THEN海南 ELSE其他ENDAS district, SUM(population) FROM PopTblGROUPBY district;二、巧用 CASE WHEN 进行更新现在某公司员人工资信息表如下:现在公司出台了一个奇葩的规定1. 对当前工资为 1 万以上的员工,降薪 10%。2. 对当前工资低于 1 万的员工,加薪 20%。一些人不假思索可能写出了以下的 SQL:-条件1UPDATE SalariesSET salary = salary * 0.9WHERE salary = 10000;-条件2UPDATE

    7、 SalariesSET salary = salary * 1.2WHERE salary = 10000THEN salary * 0.9WHEN salary 280000THEN salary * 1.2ELSE salary END;三、巧用 HAVING 子句一般 HAVING 是与 GROUP BY 结合使用的,但其实它是可以独立使用的, 假设有如下表,第一列 seq 叫连续编号,但其实有些编号是缺失的,怎么知道编号是否缺失呢,用 HAVING 表示如下:SELECT存在缺失的编号AS gap FROM SeqTblHAVINGCOUNT(*) MAX(seq);四、自连接针对相

    8、同的表进行的连接被称为“自连接”(self join),这个技巧常常被人们忽视,其实是有挺多妙用的1、删除重复行上图中有三个橘子,需要把这些重复的行给删掉,用如下自连接可以解决:DELETEFROM Products P1 WHEREid P1.price) + 1AS rank_1 FROM Products P1 ORDERBY rank_1;结果如下:name price rank - - - 橘子 100 1 西瓜 80 2 苹果 50 3 葡萄 50 3 香蕉 50 3 柠檬 30 6五、巧用 COALESCE 函数此函数作用返回参数中的第一个非空表达式,假设有如下商品,我们重新格式

    9、化一样,如果 city 为 null,代表商品不在此城市发行,但我们在展示结果的时候不想展示 null,而想展示 N/A, 可以这么做:SELECT COALESCE(city, N/A) FROM customers;SQL 性能优化技巧一、参数是子查询时,使用 EXISTS 代替 IN如果 IN 的参数是(1,2,3)这样的值列表时,没啥问题,但如果参数是子查询时,就需要注意了。比如,现在有如下两个表:现在我们要查出同时存在于两个表的员工,即田中和铃木,则以下用 IN 和 EXISTS 返回的结果是一样,但是用 EXISTS 的 SQL 会更快:- 慢SELECT * FROM Class

    10、_AWHEREidIN (SELECTid FROM CLASS_B);- 快SELECT * FROM Class_A A WHEREEXISTS(SELECT * FROM Class_B B WHERE A.id = B.id);为啥使用 EXISTS 的 SQL 运行更快呢,有两个原因1. 可以用到索引,如果连接列 (id) 上建立了索引,那么查询 Class_B 时不用查实际的表,只需查索引就可以了。2. 如果使用 EXISTS,那么只要查到一行数据满足条件就会终止查询, 不用像使用 IN 时一样扫描全表。在这一点上 NOT EXISTS 也一样另外如果 IN 后面如果跟着的是子查询

    11、,由于 SQL 会先执行 IN 后面的子查询,会将子查询的结果保存在一张临时的工作表里(内联视图),然后扫描整个视图,显然扫描整个视图这个工作很多时候是非常耗时的,而用 EXISTS 不会生成临时表。当然了,如果 IN 的参数是子查询时,也可以用连接来代替,如下:- 使用连接代替 IN SELECT A.id, A.nameFROM Class_A A INNER JOIN Class_B B ON A.id = B.id;用到了 id列上的索引,而且由于没有子查询,也不会生成临时表二、避免排序SQL 是声明式语言,即对用户来说,只关心它能做什么,不用关心它怎么做。这样可能会产生潜在的性能问题

    12、:排序,会产生排序的代表性运算有下面这些l GROUP BY 子句l ORDER BY 子句l 聚合函数(SUM、COUNT、AVG、MAX、MIN)l DISTINCTl 集合运算符(UNION、INTERSECT、EXCEPT)l 窗口函数(RANK、ROW_NUMBER 等)如果在内存中排序还好,但如果内存不够导致需要在硬盘上排序上的话,性能就会急剧下降,所以我们需要减少不必要的排序。怎样做可以减少排序呢。1、 使用集合运算符的 ALL 可选项SQL 中有 UNION,INTERSECT,EXCEPT 三个集合运算符,默认情况下,这些运算符会为了避免重复数据而进行排序,对比一下使用 UN

    13、ION 运算符加和不加 ALL 的情况:注意:加 ALL 是优化性能非常有效的手段,不过前提是不在乎结果是否有重复数据。2、使用 EXISTS 代表 DISTINCT为了排除重复数据, DISTINCT 也会对结果进行排序,如果需要对两张表的连接结果进行去重,可以考虑用 EXISTS 代替 DISTINCT,这样可以避免排序。如何找出有销售记录的商品,使用如下 DISTINCT 可以:SELECTDISTINCT I.item_noFROM Items I INNERJOIN SalesHistory SHON I. item_no = SH. item_no;不过更好的方式是使用 EXIST

    14、S:SELECT item_no FROM Items IWHEREEXISTS (SELECT * FROM SalesHistory SH WHERE I.item_no = SH.item_no);既用到了索引,又避免了排序对性能的损耗。二、在极值函数中使用索引(MAX/MIN)使用 MAX/ MIN 都会对进行排序,如果参数字段上没加索引会导致全表扫描,如果建有索引,则只需要扫描索引即可,对比如下- 这样写需要扫描全表 SELECTMAX(item) FROM Items;- 这样写能用到索引 SELECTMAX(item_no) FROM Items;注意:极值函数参数推荐为索引列中

    15、并不是不需要排序,而是优化了排序前的查找速度(毕竟索引本身就是有序排列的)。三、能写在 WHERE 子句里的条件不要写在 HAVING 子句里下列 SQL 语句返回的结果是一样的:- 聚合后使用 HAVING 子句过滤SELECT sale_date, SUM(quantity) FROM SalesHistory GROUPBY sale_dateHAVING sale_date = 2007-10-01;- 聚合前使用 WHERE 子句过滤SELECT sale_date, SUM(quantity) FROM SalesHistory WHERE sale_date = 2007-10-

    16、01 GROUPBY sale_date;使用第二条语句效率更高,原因主要有两点1. 使用 GROUP BY 子句进行聚合时会进行排序,如果事先通过 WHERE 子句能筛选出一部分行,能减轻排序的负担2. 在 WHERE 子句中可以使用索引,而 HAVING 子句是针对聚合后生成的视频进行筛选的,但很多时候聚合后生成的视图并没有保留原表的索引结构四、在 GROUP BY 子句和 ORDER BY 子句中使用索引GROUP BY 子句和 ORDER BY 子句一般都会进行排序,以对行进行排列和替换,不过如果指定带有索引的列作为这两者的参数列,由于用到了索引,可以实现高速查询,由于索引是有序的,排

    17、序本身都会被省略掉五、使用索引时,条件表达式的左侧应该是原始字段假设我们在 col 列上建立了索引,则下面这些 SQL 语句无法用到索引SELECT * FROM SomeTable WHEREcol * 1.1 100;SELECT * FROM SomeTable WHERESUBSTR(col, 1, 1) = a;以上第一个 SQL 在索引列上进行了运算, 第二个 SQL 对索引列使用了函数,均无法用到索引,正确方式是把列单独放在左侧,如下:SELECT * FROM SomeTable WHERE col_1 100 / 1.1;当然如果需要对此列使用函数,则无法避免在左侧运算,可以

    18、考虑使用函数索引,不过一般不推荐随意这么做。六、尽量避免使用否定形式如下的几种否定形式不能用到索引:l l !=l NOT IN所以以下 了SQL 语句会导致全表扫描SELECT * FROM SomeTable WHERE col_1 100;可以改成以下形式SELECT * FROM SomeTable WHERE col_1 100or col_1 = 10;虽然上面这样的写法能达到目的,但会生成 TMP 这张临时表,所以应该使用下面这样的写法:SELECT sale_date, MAX(quantity) FROM SalesHistory GROUPBY sale_dateHAVIN

    19、GMAX(quantity) = 10;HAVING 子句和聚合操作是同时执行的,所以比起生成中间表后再执行 HAVING 子句,效率会更高,代码也更简洁10、需要对多个字段使用 IN 谓词时,将它们汇总到一处一个表的多个字段可能都使用了 IN 谓词,如下:SELECTid, state, city FROM Addresses1 A1 WHERE state IN (SELECT state FROM Addresses2 A2 WHERE A1.id = A2.id) AND city IN (SELECT city FROM Addresses2 A2 WHERE A1.id = A2.

    20、id);这段代码用到了两个子查询,也就产生了两个中间表,可以像下面这样写SELECT * FROM Addresses1 A1 WHEREid | state | city IN (SELECTid | state| city FROM Addresses2 A2);这样子查询不用考虑关联性,没有中间表产生,而且只执行一次即可。总结本文一开始花了挺大的篇幅来讲解 SQL 的规范,请大家务必重视这部分内部,良好的规范有利于团队协作,对于代码的阅读也比较友好。之后介绍了一些 SQL 的比较高级的用法,巧用这些技巧确实能达到事半功倍的效果,由于本文篇幅有限只是介绍了一部分,下篇我们会再介绍一些其他的技巧,敬请期待哦

    展开阅读全文
    提示  163文库所有资源均是用户自行上传分享,仅供网友学习交流,未经上传用户书面授权,请勿作他用。
    关于本文
    本文标题:常用sql书写规范标准sql编写规范.docx
    链接地址:https://www.163wenku.com/p-5351344.html

    Copyright@ 2017-2037 Www.163WenKu.Com  网站版权所有  |  资源地图   
    IPC备案号:蜀ICP备2021032737号  | 川公网安备 51099002000191号


    侵权投诉QQ:3464097650  资料上传QQ:3464097650
       


    【声明】本站为“文档C2C交易模式”,即用户上传的文档直接卖给(下载)用户,本站只是网络空间服务平台,本站所有原创文档下载所得归上传人所有,如您发现上传作品侵犯了您的版权,请立刻联系我们并提供证据,我们将在3个工作日内予以改正。

    163文库