SQL -- Tips

最新推荐文章于 2024-03-11 13:57:57 发布

Mario.Alex

最新推荐文章于 2024-03-11 13:57:57 发布

阅读量364

点赞数

本文链接：https://blog.csdn.net/duxiangwushirenfei/article/details/79855509

版权

MySQL 同时被 3 个专栏收录

6 篇文章 0 订阅

订阅专栏

SQL

1 篇文章 0 订阅

订阅专栏

PostgreSQL

1 篇文章 0 订阅

订阅专栏

前言

先前陆续总结过不少关于MySQL中SQL的使用，偶然一个使用，激发了去深入一下标准SQL的全面语法的构想。以下所使用的DB包括：MySQL 5.7，PostgreSQL 9.6.8此二者均是笔者自己在机器上部署的单服务，以及阿里云RDS服务 MySQL 5.6.16-log。
很多基础语法请参见之前：
MySQL 小结（一）
MySQL 小结（二）
MySQL 小结（三）

标准SQL

SQL（Structured Query Language）是用来操作关系型数据库的结构化查询语言，国际标准化组织(ISO)为 SQL 制定了相应的标准，以此为基准的 SQL 称为标准 SQL，但通常不同的数据库有自身支持的特定SQL。
标准SQL的书写规则较为统一，常见规则如下：

SQL语句要以”;”结尾
SQL语句不区分大小写，习惯性关键字大写
单词需要用半角空格或者换行来分隔

不同DB编写SQL也有不同识别的符号，例如

-- MySQL 加上``以标示表，字段
> SELECT `product_name` FROM `product`;

-- PostgreSQL 不能使用`` 否则无法执行通过
> SELECT product_name FROM product;

注意：
对于数据库，表级别的操作，不同数据库支持的SQL略有不同，诸如查看当前数据库有哪些表
MySQL是：SHOW TABLES;
而PostgreSQL是：\dt
同时对于修改已存在表，增加，修改，删除字段等SQL也不尽相同，有机会在详细介绍。

基础查询

1，DISTINCT
对于DISTINCT 去重关键字的使用，只能是放在SELECT 检索的所有字段最前面，并且有效与后面所有字段。

2，NULL
SQL的逻辑是三值逻辑，True，False，Unknow。举个例子，商品表中有price字段，以条件 price=10进行检索，则会检索出所有价格为10的商品，以 price!=10进行检索会检索出所有 price<>10的记录，但是，所有price为NULL的数据均不会检索出。所以切不可直接对某一condition取 !condition误以为取出剩下的所有数据。判定NULL的方法仅仅能用 IS NULL 和IS NOT NULL两种方式。
并且所有包含NULL的数据进行运算的结果，亦是NULL。

3，SELECT
SELECT 查询未必一定要接FROM子句，此外SELECT后的 FROM 子句支持多表查询。

-- 查看数据库版本
> SELECT VERSION();

-- 多表查询订单和订单明细
> SELECT o.code, d.product_id, d.price, d.quantity FROM order AS o, detail AS d WHERE o.id=d.orderId;

4，字符串
对于CHAR, VARCHAR类型的字符串字段，包括TEXT字段不要轻易地使用比较操作，数子 11>2 为True，但是字符串 ‘11’>’2’是False，因为SQL对字符串比较，是按照字典序。

聚合函数

常用聚合函数

对于order表remark为备注字段，使用COUNT(*)，COUNT(1)，COUNT(remark)得到的结果，一样么？
常见的聚合函数：
COUNT：计算表中的记录数(行数)
SUM：计算表中数值列中数据的合计值
AVG：计算表中数值列中数据的平均值
MAX：求出表中任意列中数据的最大值
MIN：求出表中任意列中数据的最小值
此外不同DB也有自己特有的一些聚合函数，但是最为常用的，通用的是这5个。

1，首先需要知道，上述5个聚合函数是不对NULL字段做统计。也就是说SUM求和，是不会对NULL值进行计算。
2，上述5个聚合函数只能接收一个参数，可以是一个列，或是一个计算表达式，如SUM(price * quantity).
3，可以在参数中添加 DISTINCT 关键字用以先去重再统计。

由此可知，COUNT(1)，COUNT(remark)是不一样的，COUNT(remark)是相当于对remark列中非NULL的记录进行个数统计，而COUNT(1)相当于对整个order表行数的统计。
再者COUNT(1)和COUNT(*)也是不一样的，因为COUNT()实质是执行SELECT检索，检索之后对其中非NULL的行记录统计，因此COUNT(*)相当于取表中全字段后统计个数，效率低，COUNT(1)则效率更高，二者的数据集是相同的。

tips

聚合函数通常和GROUP BY分组关键字连用，对一个分组內的数据统计，使用tips：
1，SELECT 子句中只能是包含：常数，聚合函数，GROUP BY 中指定的分组列。
2，只有SELECT 子句，GROUP BY 的HAVING 子句中能使用聚合函数，换而言之在WHERE 子句中使用 WHERE SUM(quantity) > 20是无法执行通过。
3，HAVING子句中能够使用的元素也是常数，聚合函数，GROUP BY 中指定的分组列。
4，ORDER BY子句中可以使用SELECT子句中定义的列的别名，且可以使用SELECT子句中未出现的列或者聚合函数。

注意：
1，亲测在阿里RDS服务上MySQL 5.6.16-log，可以支持SELECT子句包含并非GROUP BY 中指定的分组列执行通过，本地MySQL5.7则不支持。
2，通常包含分组的SQL书写顺序是：
SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY
其执行顺序是 FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY
3，有些教程上说，在 GROUP BY 子句中不能使用 SELECT 子句中定义的别名。但亲测在上面的三个版本数据库中执行均通过，可以使用别名。

复杂查询

VIEW

视图在先前的MySQL基础中介绍过，但是视图使用时有一些值得注意的地方。
1，首先视图归根结底就是一个SELECT查询语句，实质并非一张固化数据的表，而是执行检索出的临时表，因此视图定义时不能有ORDER BY子句，某些DB可能支持，但是并不通用。
2，不要尝试对视图执行INSERT，UPDAT，DELETE等修改操作，因为视图并不是实实在在的表。
3，当使用SELECT从某一VIEW中检索数据时，首先执行的时VIEW中的检索，而后执行外层的SELECT。当出现多重VIEW的时，会大大降低SQL执行效率。

子查询

子查询可以理解为没有定义成VIEW的SELECT语句，执行时候也是先执行内层的自查询。
需求一：
当前有需求对于订单明细表detail有detail_id，product_name，price，quantity字段，需要筛选出所有 price大于平均售价的明细。
由于AVG(price)聚合函数是不能用做WHERE子句条件的，因此可以使用标量子查询的形式：

> SELECT detail_id, product_name，price，quantity FROM detail WHERE price>(SELECT AVG(price) FROM detail)

需求二：
还是上文的订单明细表，需要筛选出所有不同商品中大于改商品销售均价的明细。
与之前相比，此处的均价并非全局商品均价，而是某一product_name商品的均价格。
可以使用关联子查询

> SELECT d.detail_id, d.product_name，d.price，d.quantity FROM detail as d WHERE d.price>(SELECT AVG(ad.price) FROM detail AS ad WHERE d.product_name=ad.product_name GROUP BY ad.product_name);

上述查询可以看出，在子查询中给出了与主查询的关联条件d.product_name=ad.product_name，主查询能够检索出所有detail中同商品大于该商品均价的行。在MySQL中，detail表100条数据0.03s统计结束，1000条数据35s才统计完，效率极低。换用连接查询

> SELECT d.detail_id, d.product_name，d.price，d.quantity FROM detail as d INNER JOIN (SELECT product_name, AVG(price) AS a_price FROM detail GROUP BY product_name) AS ad ON d.product_name=ad.product_name AND d.price>ad.a_price;

上述连接查询在20w数据的MySQL中约0.4s统计完毕。

注意：
在关联子查询中，子查询内外均对detail表起了别名，值得注意的是，内层查询可以引用外层查询设定的别名，外层查询不可使用内层的别名。类似编程中的局部变量和全局变量一样。

函数

除过之前的聚合函数，SQL中还有一些常见的函数。诸如：
● 算术函数(用来进行数值计算的函数)
● 字符串函数(用来进行字符串操作的函数)
● 日期函数(用来进行日期操作的函数)
● 转换函数(用来转换数据类型和值的函数)

算数函数

除 + - * / 四则运算外，算术运算还可调用：
1，ABS (c)
对给定的c取其绝对值运算。
2，MOD(被除数，除数)
取余数运算，MOD(7,3)得结果1。
3，ROUND(c, [n])
对给定c做小数点后第n位的4舍5入运算，n省略默认取到整数位。
以上三者对NULL列运算返回仍是NULL。

字符串函数

1，||
|| 用来拼接字符串，可以形如以下使用：

> SELECT product_type || product_name AS product_info FROM product;

2，LENGTH
获取字符串长度

> SELECT product_name FROM product WHERE LENGTH(product_name)>2;

此外还有:
LOWER——小写转换
UPPER——大写转换
REPLACE——字符串的替换
SUBSTRING——字符串的截取

日期函数

-- 获取当前日期
> SELECT CURRENT_DATE;
-- 获取当前时间
> SELECT CURRENT_TIME;
-- 获取当前日期和时间
> SELECT CURRENT_TIMESTAMP;
-- EXTRACT——截取日期元素
> SELECT CURRENT_TIMESTAMP, 
->EXTRACT(YEAR FROM CURRENT_TIMESTAMP) AS year, 
->EXTRACT(MONTH FROM CURRENT_TIMESTAMP) AS month, 
->EXTRACT(DAY FROM CURRENT_TIMESTAMP) AS day, 
->EXTRACT(HOUR FROM CURRENT_TIMESTAMP) AS hour, 
->EXTRACT(MINUTE FROM CURRENT_TIMESTAMP) AS minute, 
->EXTRACT(SECOND FROM CURRENT_TIMESTAMP) AS second;

转换函数

转换函数主要功能是对数据类型的转换，以及对NULL值的转换，实用性非常强。
1，CAST类型转换

-- CAST(转换前的值 AS 想要转换的数据类型)

-- MySQL 示例，如果是无符号则不是-1，而是一个很大的正整数
> SELECT CAST('-001' AS SIGNED INTEGER) AS int_col;
> 
-- PostgreSQL 示例，无需指明是否有符号
> SELECT CAST('-001' AS INTEGER) AS int_col;

2，COALESCE NULL值替换
COALESCE接收一组参数，返回该组参数中第一个非NULL值，如果参数全部是NULL则返回NULL。
假设订单明细中有预定量 quantity和结算量auditQuantity，预算订单结算金额是如果有明细有结算量按结算量统计，如果没有按照预定量统计，则如下：

> SELECT orderId, SUM(price*COALESCE(auditQuantity, quantity, 0)) FROM detail GROUP BY orderId;

CASE

CASE 表达式和Python中的if … elif … else的含义非常接近，语法如下：

CASE 
WHEN <求值表达式> THEN <表达式> 
WHEN <求值表达式> THEN <表达式> 
WHEN <求值表达式> THEN <表达式>
.
.
ELSE <表达式> END

对之前的COALESCE进行条件选择CASE形式的改写如下：

> SELECT orderId, SUM(price* 
    CASE
        WHEN auditQuantity IS NOT NULL THEN auditQuantity
        WHEN auditQuantity IS NULL AND quantity IS NOT NULL THEN quantity
        ELSE 0
    END 
) FROM detail GROUP BY orderId;

CASE条件使用时注意:
1，ELSE 子句可以省略，此时默认表达ELSE NULL，建议写上，方便理解维护。
2，END结束关键字不要漏，否则无法执行通过。
在MySQL还支持IF关键字，但是并非通用的。

集合运算

在先前的介绍中，不论关联查询，多表查询，连接查询都是为了在横向上拓展检索出的信息，让我们一次性能够把一条记录中所需的关联信息更完整的从不同表中取出。但问题是，有些情况下需要纵向上的查询。
例如：一张完整订单的结算金额，应该是：
订单所有的明细记录对应总额 + 订单对应所有补货记录对应总额 - 订单对应退货记录总额 = 订单结算金额
对于此种情况是要从三张不同的明细表中取出数据做为行数据后统计。此时之前所有的查询手段都无法解决该问题，当然可以在程序实现时从不同的表中取出数据后在代码中统计，实则可以通过SQL的集合运算进行统计。
标准SQL支持 UNION 并集，INTERSECT交集，EXCEPT差集，此三者和数学上的集合概念一致。
由此，对订单id为10的订单统计detail明细，rfo补货，退货rto，如下：

> SELECT product_id, price, quantity FROM detail WHERE orderId=10
  UNION ALL
  SELECT product_id, price, quantity FROM rfo WHERE orderId=10
  UNION ALL
  SELECT product_id, price, quantity FROM rto  WHERE orderId=10;

这样就完成了一条查询在多张表中取出所有商品的售卖，退补货记录了。

注意：
1，UNION 是并集运算，相同的记录值是不会重复录入的，如果想要保留必须指定关键字ALL，此处如果不保留ALL关键字，则当补货单中有相同product_id商品且补货价格，补货量与订单明细相同时就会被过滤。
2，MySQL不支持交集INTERSECT 和差集EXCEPT运算，PostgreSQL支持。

窗口函数

基础使用

个人理解窗口函数就是数据分片，类似一个个窗口一样的统计数据。MySQL是不支持窗口函数的，PostgreSQL中演示结果如下：
这里写图片描述
语法上书写规则：

< 窗口函数 > OVER ([PARTITION BY < 列清单 >] ORDER BY < 排序用列清单 >)

PARTITION指定分片字段，然后ORDER表示排序纬度，RANK是所调用的窗口函数，用来获取排序序号。
在调用过程中，可以不指定 PARTITION表示全局排序，此时效果如下：
这里写图片描述
看官可以自行理解下 RANK，DENSE_RANK，ROW_NUMBER三个函数的区别，得到的序号是不一样的。

与聚合函数结合

还是看实际的使用结果，以窗口函数和SUM聚合函数配合使用：
这里写图片描述
不仅如此，还可以指定窗口的宽度，如下：

每条数据的平均价是取该行记录的前一行，后一行，以及本行累加后除以3。首行数据是第1行，第2行求平均，尾行数据是自身与前一行求平均。

GROUPING

在使用GROUP BY 配合聚合函数使用时是无法得到总额统计，也无法得到 GROUP BY多个字段时的小计统计，此时可以使用GROUPING运算符。
标准SQL中GROUPING运算符包含三种分别是：

ROLLUP
CUBE
GROUPING SETS

MySQL中仅仅支持ROLLUP，其余2者不支持。以ROLLUP为例，了解下GROUPING的使用。

上图可以看出，最后一行为总共的price总和，而每个product_type的行中有一个proudct_name为NULL是该类的product_type所有price的小计和。
MySQL中对ROLLUP的写法略有不同，示例中SQL在MySQL中修改成如下：

> SELECT product_type, product_name, SUM(sale_price) AS sum_price FROM Product GROUP BY product_type, product_name WITH ROLLUP;

至此，算是把SQL中一些使用的小tips初步梳理一下，当然不同数据库对于一SQL的支持也是各有差别的，需要不断积累。

Mario.Alex

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录