第六章：函数、谓词、 CASE表达式

1、函数

ABS(m) ——绝对值
代码清单6-2 计算数值的绝对值
SELECT m,
ABS(m) AS abs_col
FROM SampleMath;

MOD(n, p)——求余数
代码清单6-3 计算除法（n ÷ p）的余数
Oracle DB2 PostgreSQL MySQL
SELECT n, p,
MOD(n, p) AS mod_col
FROM SampleMath;

ROUND(m, n)——m列数据进行n列位数四舍五入
代码清单6-4 对m列的数值进行n列位数的四舍五入处理
SELECT m, n,
ROUND(m, n) AS round_col
FROM SampleMath;

LENGTH(str1)——计算字符
代码清单6-8 计算字符串长度
Oracle DB2 PostgreSQL MySQL
SELECT str1,
LENGTH(str1) AS len_str
FROM SampleStr;

SUBSTRING(str1 FROM 3 FOR 2) ——字符串提取
代码清单6-11 截取出字符串中第3位和第4位的字符
PostgreSQL MySQL
SELECT str1,
SUBSTRING(str1 FROM 3 FOR 2) AS sub_str
FROM SampleStr;

转换函数
代码清单6-17 将字符串类型转换为数值类型
SQL Server PostgreSQL
SELECT CAST(‘0001’ AS INTEGER) AS int_col;

代码清单6-18 将字符串类型转换为日期类型
SQL Server PostgreSQL MySQL
SELECT CAST(‘2009-12-14’ AS DATE) AS date_col;

2、谓词

1）LIKE谓词——字符串的部分一致查询
代码清单6-22 使用LIKE进行前方一致查询

SELECT
FROM SampleLike
WHERE strcol LIKE ‘ddd%’;

代码清单6-23 使用LIKE进行中间一致查询
SELECT
FROM SampleLike
WHERE strcol LIKE ‘%ddd%’;

代码清单6-24 使用LIKE进行后方一致查询
SELECT
FROM SampleLike
WHERE strcol LIKE* ‘%ddd’;

2、BETWEEN谓词——范围查询

3、IS NULL、 IS NOT NULL——判断是否为NULL
4、 IN谓词——OR的简便用法

代码清单6-32 通过IN来指定多个进货单价进行查询
SELECT product_name, purchase_price
FROM Product
WHERE purchase_price IN (320, 500, 5000);

4、 使用子查询作为IN谓词的参数

代码清单6-36 使用子查询作为IN的参数
— 取得“在大阪店销售的商品的销售单价”
SELECT product_name, sale_price
FROM Product
WHERE product_id IN (SELECT product_id
FROM ShopProduct
WHERE shop_id = ‘000C’);

5、EXIST谓词

3、case表达式

代码清单6-41 通过CASE表达式将A ～ C的字符串加入到商品种类当中
SELECT product_name,
CASE WHEN product_type = ‘衣服’
THEN ‘A ：’ | | product_type
WHEN product_type = ‘办公用品’
THEN ‘B ：’ | | product_type
WHEN product_type = ‘厨房用具’
THEN ‘C ：’ | | product_type
ELSE NULL
END AS abc_product_type
FROM Product;

代码清单6-43 使用CASE表达式进行行列转换
— 对按照商品种类计算出的销售单价合计值进行行列转换
SELECT SUM(CASE WHEN product_type = ‘衣服’
THEN sale_price ELSE 0 END) AS sum_price_clothes,
SUM(CASE WHEN product_type = ‘厨房用具’
THEN sale_price ELSE 0 END) AS sum_price_kitchen,
SUM(CASE WHEN product_type = ‘办公用品’
THEN sale_price ELSE 0 END) AS sum_price_office
FROM Product;

代码清单6-A 使用CASE表达式将字符串A ～ C添加到商品种类中
— 使用搜索CASE表达式的情况（重写代码清单6-41）
SELECT product_name,
CASE WHEN product_type = ‘衣服’
THEN ‘A ：’ | |product_type
WHEN product_type = ‘办公用品’
THEN ‘B ：’ | |product_type
WHEN product_type = ‘厨房用具’
THEN ‘C ：’ | |product_type
ELSE NULL
END AS abc_product_type
FROM Product;
— 使用简单CASE表达式的情况
SELECT product_name,
CASE product_type
WHEN ‘衣服’ THEN ‘A ：’ | | product_type
WHEN ‘办公用品’ THEN ‘B ：’ | | product_type
WHEN ‘厨房用具’ THEN ‘C ：’ | | product_type
ELSE NULL
END AS abc_product_type
FROM Product;

SELECT SUM(CASE WHEN sale_price <= 1000
THEN 1 ELSE 0 END) AS low_price,
SUM(CASE WHEN sale_price BETWEEN 1001
AND 3000
THEN 1 ELSE 0 END) AS mid_price,
SUM(CASE WHEN sale_price >= 3001
THEN 1 ELSE 0 END) AS high_price
FROM Product;

第七章 集合运算

7-1 表的加减法

■什么是集合运算

• 集合运算就是对满足同一规则的记录进行的加减等四则运算。
• 使用UNION（并集）、 INTERSECT（交集）、 EXCEPT（差集）等集合运
  算符来进行集合运算。
• 集合运算符可以去除重复行。
• 如果希望集合运算符保留重复行，就需要使用ALL选项。

■注意事项① ——作为运算对象的记录的列数必须相同
■注意事项② ——作为运算对象的记录中列的类型必须一致
注意事项③ —— 可以使用任何SELECT语句，但ORDER BY子句只
能在最后使用一次


union_加法运算
代码清单7-3 使用UNION对表进行加法运算
SELECT product_id, product_name
FROM Product
UNION
SELECT product_id, product_name
FROM Product2;

UNION ALL——保留重复行
代码清单7-5 保留重复行
SELECT product_id, product_name
FROM Product
UNION ALL
SELECT product_id, product_name
FROM Product2;

intersect——取交集
代码清单7-6 使用INTERSECT选取出表中公共部分
Oracle SQL Server DB2 PostgreSQL
SELECT product_id, product_name
FROM Product
INTERSECT
SELECT product_id, product_name
FROM Product2
ORDER BY product_id;

记录的减法——EXCEPT
代码清单7-7 使用EXCEPT对记录进行减法运算
SQL Server DB2 PostgreSQL
SELECT product_id, product_name
FROM Product
EXCEPT
SELECT product_id, product_name
FROM Product2
ORDER BY product_id;

■表的加法——UNION
■集合运算的注意事项
■包含重复行的集合运算——ALL选项
■选取表中公共部分——INTERSECT
■记录的减法——EXCEPT

7-2 联结（以列为单位对表进行联结）

■什么是联结
■内联结——INNER JOIN

代码清单7-9 将两张表进行内联结
SQL Server DB2 PostgreSQL MySQL
SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name,
P.sale_price
FROM ShopProduct AS SP INNER JOIN Product AS P ①
ON SP.product_id = P.product_id;
（on代表连接的关键列）

innerjoin 只能显示同时存在于两个图表之间的数据
外连接只需要数据存在其中一个图表就会同时展示

■外联结——OUTER JOIN
■3张以上的表的联结

代码清单7-14 对3张表进行内联结
SQL Server DB2 PostgreSQL MySQL
SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name, 
P.sale_price, IP.inventory_quantity
FROM ShopProduct AS SP INNER JOIN Product AS P ①
ON SP.product_id = P.product_id
INNER JOIN InventoryProduct AS IP ②
ON SP.product_id = IP.product_id
WHERE IP.inventory_id = ‘P001’;

■交叉联结——CROSS JOIN
■联结的特定语法和过时语法

第八章 窗口函数

语法的基本使用方法——使用RANK函数

代码清单8-4 将SUM函数作为窗口函数使用
Oracle SQL Server DB2 PostgreSQL
SELECT product_id, product_name, sale_price,
SUM (sale_price) OVER (ORDER BY product_id) AS current_sum
FROM Product;

代码清单8-5 将AVG函数作为窗口函数使用
Oracle SQL Server DB2 PostgreSQL
SELECT product_id, product_name, sale_price,
AVG (sale_price) OVER (ORDER BY product_id) AS current_avg
FROM Product;

计算移动平均

8-2 GROUPING运算符

■ROLLUP的使用方法

代码清单8-12 使用ROLLUP同时得出合计和小计
Oracle SQL Server DB2 PostgreSQL
SELECT product_type, SUM(sale_price) AS sum_price
FROM Product
GROUP BY ROLLUP(product_type);