数据库高级查询与性能优化2,CASE逻辑与集合运算

数据库高级查询与性能优化

CASE逻辑

CASE用于在查询中显示不同类型的数据.从语法上看,其实类似于过程式程序设计语言中的if...else...逻辑,只不过在CASE语句中不能有太复杂的逻辑过程,最复杂只能到条件表达式的程度.其查询结果来说,CASE语句做的实际上是对查询列结果的一种替换.

编写查询语句时,CASE语句块看起来非常臃肿,通常会占用三四行位置(因风格而异),但其只是查询中一列的内容,是SELECT语句的一个对象而已.在查询时,CASE语句前后如要查询其他属性,需要添加逗号分隔,使用括号可以更清晰.

简单值匹配

简单的CASE函数只能做值的匹配,即做到对所查询目标结果值的替换显示,不能执行更加复杂的条件判断.使用时要注意,如果查询结果没有匹配值,则会返回NULL,所以最好在所有条件后使用ELSE兜底.快外,在CASE结束后不能忘记使用END关键字结束.

简单CASE使用时需要在CASE后立即指明比对的列,然后在WHEN中说明匹配的值,THEN中说明替换的值:

CASE 目标表达式
    WHEN 目标表达式的值1 THEN 替换值1
    WHEN 目标表达式的值2 THEN 替换值2
    ...
    WHEN 目标表达式的值n THEN 替换值n
    ELSE 替换值n+1
END 

例如需要查询商品表中商品的品质,将编码替换为中文显示:

SELECT 商品编号, 
CASE 商品品质
    WHEN 1 THEN '一等品'
    WHEN 2 THEN '二等品'
    ELSE '次品'
END
FROM 商品表

条件型

这种CASE语句可以执行更加复杂的逻辑,除了值匹配以外还可以做更多相关的运算比较.使用方法和简单的CASE基本相同,只不过在CASE后不必指明目标表达式,在WHEN中需要说明完整的条件表达式:

CASE 
    WHEN 条件表达式1 THEN 显示值1
    WHEN 条件表达式2 THEN 显示值2
    ...
    WHEN 条件表达式n THEN 显示值n
    ELSE 显示值n+1
END

例如在销售明细表中查看订单的规模,不小于10000元的为大订单:

SELECT 订单号,
CASE
    WHEN 价格*销售数量 > 10000 THEN '大订单'
    ELSE '小订单'
END FROM 销售明细表
JOIN 商品表 ON 商品表.商品编号 = 销售明细表.商品编号

集合运算

如果说JOIN联接操作是纵向上的表合并,那么集合运算就是将表在水平上进行操作.与JOIN不同的是,JOIN是在查询前将表联接,纵向合并成一张大表,而集合运算往往是对查询的结果集进行操作.换句话说,联接操作是给表添加更多的列,集合操作是给表增删一些行.

并集

并集将两个SELECT语句的查询结果连接起来.查询的结果不一定来自同一张表,但合并是必须保证对应列的数据类型一样,而且字段的名称将被统一为第一个SELECT语句中的命名.如果需要排序,需要在最后一个SELECT语句中说明ORDER BY.

并集操作使用到关键字UNION,并集操作默认会去重:

SELECT 语句块1
UNION
SELECT 语句块2
UNION ...
SELECT 语句块n

例如需要查询所有销售明细:

SELECT * FROM 销售明细表
UNION
SELECT * FROM 历史销售明细表

交集

交集不如并集在DBMS中受到广泛的支持,例如MySQL就不支持,本文以下的代码可以在SQL Server中运行.交集与并集使用方式差不多,使用INTERSECT关键字,用于求得同时在两张表中出现的结果.

例如之前提到的查询客户编号为10086和10010的两个用户都购买了的商品有哪些:

SELECT 商品表.商品名称, 商品表.商品编号
FROM 商品表 JOIN 销售明细表 ON 商品表.商品编号 = 销售明细表.商品编号
WHERE 销售明细表.客户编号 = 10086
INTERSECT
SELECT 商品表.商品名称, 商品表.商品编号
FROM 商品表 JOIN 销售明细表 ON 商品表.商品编号 = 销售明细表.商品编号
WHERE 销售明细表.客户编号 = 10010

差集

差集用于求第一张表中有而第二章表中没有得数据,使用EXCEPT实现:

例如查询客户编号为10086客户购买了的而10010的客户没有购买的商品有哪些:

SELECT 商品表.商品名称, 商品表.商品编号
FROM 商品表 JOIN 销售明细表 ON 商品表.商品编号 = 销售明细表.商品编号
WHERE 销售明细表.客户编号 = 10086
EXCEPT
SELECT 商品表.商品名称, 商品表.商品编号
FROM 商品表 JOIN 销售明细表 ON 商品表.商品编号 = 销售明细表.商品编号
WHERE 销售明细表.客户编号 = 10010

集合性能问题

在UNION中,查重这一操作会遍历结果集,对性能产生较大的影响.在不影响最终的查询结果的情况下,使用UNION ALL避免DBMS主动查重影响性能.

在分布式数据库架构中,节点间通信是性能瓶颈之一,使用不必要的联接查询和并查询会引起节点间大量的数据通信,给数据库性能表现造成较大的压力.

无论是否使用子查询,集合查询还是联接查询,查询时都应最小化地指定列名,避免使用*符号查找不必要的数据,优化查询性能.另外,在查询时,需要结合数据库中索引,视图等结构,充分利用性能优化的工具.在模糊匹配查询时,尽量避免将通配符前置,DBMS会不使用索引而是会遍历整张表.同样的,在WHERE条件中,尽量不使用非条件(!=与<>),这样会导致索引失效.

参考

[1]何玉洁, 刘乃嘉. 全国计算机等级考试三级教程-数据库技术[M]. 高等教育出版社. 2020.
[2]Ben Forta. SQL必知必会[M]. 人民邮电出版社. 2020.
[3]史嘉权. 数据库系统概论[M]. 清华大学出版社. 2006.
[4]褚华, 霍邱艳. 软件设计师教程[M]. 清华大学出版社. 2018.
[5]王珊, 陈红. 数据库系统原理教程[M]. 清华大学出版社. 1998.
[6]汤小丹, 梁红兵, 哲凤屏, 汤子瀛. 计算机操作系统[M]. 西安电子科技大学出版社. 2014.