SQL基础教程读书笔记 MICK，第6章 函数、谓词、CASE表达式，第7章 集合运算
示例程序下载 http://www.ituring.com.cn/book/1880
说明：如下笔记中的测试基于postgresql14
命令行连接本地PSQL： psql -U <username> -d <dbname> -h 127.0.0.1 -W
目录
IS NULL、IS NOT NULL——判断是否为NULL
6 函数、谓词、CASE表达式
6-1 各种各样的函数
函数大致可以分为以下几种
- 算术函数（用来进行数值计算的函数）
- 字符串函数（用来进行字符串操作的函数）
- 日期函数（用来进行日期操作的函数）
- 转换函数（用来转换数据类型和值的函数）
- 聚合函数（用来进行数据聚合的函数）
算术函数
+（加法）
-（减法）
*（乘法）
/（除法）
语法6-1 ABS函数 -> 绝对值
ABS(数值)
语法6-2 MOD函数 -> 求余
MOD(被除数，除数)
语法6-3 ROUND函数 -> 四舍五入
ROUND(对象数值，保留小数的位数)
字符串函数
语法6-4 ||函数-拼接
字符串1||字符串2
说明：
SQL Server使用“+”运算符（函数）来连接字符串
MySQL使用CONCAT函数来完成字符串的拼接
语法6-5 LENGTH函数 -> 字符串长度
LENGTH(字符串)
语法6-6 LOWER函数 -> 小写转换
LOWER(字符串)
语法6-7 REPLACE函数 -> 字符串的替换
REPLACE(对象字符串，替换前的字符串，替换后的字符串)
语法6-8 SUBSTRING函数（PostgreSQL/MySQL专用语法）-> 字符串的截取
SUBSTRING(对象字符串 FROM 截取的起始位置 FOR 截取的字符数)
SUBSTRING(对象字符串, 截取的起始位置, 截取的字符数)
说明：第1个字符位置就是1
语法6-9 UPPER函数 -> 大写转换
UPPER(字符串)
日期函数
语法6-10 CURRENT_DATE函数 -> 当前日期
CURRENT_DATE
代码清单6-13 获得当前日期
shop=# SELECT CURRENT_DATE;
current_date
--------------
2022-07-30
(1 row)
shop=#
语法6-11 CURRENT_TIME函数 -> 当前时间
CURRENT_TIME
代码清单6-14 取得当前时间
shop=# SELECT CURRENT_TIME;
current_time
--------------------
23:09:52.434122+08
(1 row)
shop=#
语法6-12 CURRENT_TIMESTAMP函数 -> 当前日期和时间
CURRENT_TIMESTAMP
代码清单6-15 取得当前日期和时间
shop=# SELECT CURRENT_TIMESTAMP;
current_timestamp
-------------------------------
2022-07-30 23:12:27.701212+08
(1 row)
shop=#
语法6-13 EXTRACT函数 -> 截取日期元素
EXTRACT(日期元素 FROM 日期)
代码清单6-16 截取日期元素
shop=# SELECT CURRENT_TIMESTAMP,
shop-# EXTRACT(YEAR FROM CURRENT_TIMESTAMP) AS year,
shop-# EXTRACT(MONTH FROM CURRENT_TIMESTAMP) AS month,
shop-# EXTRACT(DAY FROM CURRENT_TIMESTAMP) AS day,
shop-# EXTRACT(HOUR FROM CURRENT_TIMESTAMP) AS hour,
shop-# EXTRACT(MINUTE FROM CURRENT_TIMESTAMP) AS minute,
shop-# EXTRACT(SECOND FROM CURRENT_TIMESTAMP) AS second;
current_timestamp | year | month | day | hour | minute | second
-------------------------------+------+-------+-----+------+--------+-----------
2022-07-30 23:13:51.576628+08 | 2022 | 7 | 30 | 23 | 13 | 51.576628
(1 row)
shop=#
转换函数
语法6-14 CAST函数 -> 类型转换
CAST（转换前的值 AS 想要转换的数据类型）
代码清单6-17 将字符串类型转换为数值类型
SQL Server，PostgreSQL
shop=# SELECT CAST('0001' AS INTEGER) AS int_col;
int_col
---------
1
(1 row)
shop=#MySQL
SELECT CAST('0001' AS SIGNED INTEGER) AS int_col;
代码清单6-18 将字符串类型转换为日期类型
SQL Server， PostgreSQL， MySQL
shop=# SELECT CAST('2009-12-14' AS DATE) AS date_col;
date_col
------------
2009-12-14
(1 row)
shop=#
语法6-15 COALESCE函数 -> 将NULL转换为其他值
COALESCE(数据1，数据2，数据3……)
COALESCE 是 SQL 特有的函数。该函数会返回可变参数 A 中左侧开 始第1个不是 NULL 的值。参数个数是可变的，因此可以根据需要无限增加。
代码清单6-19 将NULL转换为其他值
SQL Server,PostgreSQL,MySQL
shop=# SELECT COALESCE(NULL, 1) AS col_1,
shop-# COALESCE(NULL, 'test', NULL) AS col_2,
shop-# COALESCE(NULL, NULL, '2009-11-01') AS col_3;
col_1 | col_2 | col_3
-------+-------+------------
1 | test | 2009-11-01
(1 row)
shop=#
6-2 谓词
谓词是函数中的一种，其返回值是真值。
LIKE
BETWEEN
IS NULL、IS NOT NULL
IN
EXISTS
LIKE谓词——字符串的部分一致查询
% 代表“0 字符以上的任意字符串”的特殊符号
_（下划线）来代 表“任意 1 个字符”
BETWEEN谓词——范围查询
代码清单6-27 选取销售单价为100～1000日元的商品
SELECT product_name, sale_price FROM Product WHERE sale_price BETWEEN 100 AND 1000;IS NULL、IS NOT NULL——判断是否为NULL
代码清单6-30 选取进货单价（purchase_price）不为NULL的商品
SELECT product_name, purchase_price FROM Product WHERE purchase_price IS NOT NULL;IN谓词——OR的简便用法
代码清单6-32 通过IN来指定多个进货单价进行查询
shop=# SELECT product_name, purchase_price FROM Product WHERE purchase_price IN (320, 500, 5000);
product_name | purchase_price
--------------+----------------
T恤 | 500
打孔器 | 320
高压锅 | 5000
(3 rows)
shop=#
代码清单6-33 使用NOT IN进行查询时指定多个排除的进货单价进行查询
shop=# SELECT product_name, purchase_price FROM Product WHERE purchase_price NOT IN (320, 500, 5000);
product_name | purchase_price
--------------+----------------
运动T恤 | 2800
菜刀 | 2800
擦菜板 | 790
(3 rows)
shop=#
注意：在使用IN 和 NOT IN 时是无法选取出 NULL 数据的。
使用子查询作为IN谓词的参数
IN 谓词（NOT IN 谓词）具有其他谓词所没有的用法 --> 可以使用子查询作为IN的参数
由于子查询就是 SQL内部生成的表 ==> 能够将表作为 IN 的参数 / 能够将视图作为 IN 的参数
之前我们使用的全都是显示商品库存清单的 Product表，但现实中这些商品可能只在个别的商店中进行销售。如下创建的表 ShopProduct 会显示出哪些商店销售哪些商品，
-- DDL：创建表
CREATE TABLE ShopProduct
(shop_id CHAR(4) NOT NULL,
shop_name VARCHAR(200) NOT NULL,
product_id CHAR(4) NOT NULL,
quantity INTEGER NOT NULL,
PRIMARY KEY (shop_id, product_id));
-- DML：插入数据
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000A', '东京', '0001', 30);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000A', '东京', '0002', 50);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000A', '东京', '0003', 15);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0002', 30);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0003', 120);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0004', 20);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0006', 10);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0007', 40);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000C', '大阪', '0003', 20);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000C', '大阪', '0004', 50);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000C', '大阪', '0006', 90);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000C', '大阪', '0007', 70);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000D', '福冈', '0001', 100);
COMMIT;该 CREATE TABLE 语句的特点是指定了 2 列作为主键（primary key）。
ShopProduct 内容如下，
shop=# SELECT * FROM ShopProduct;
shop_id | shop_name | product_id | quantity
---------+-----------+------------+----------
000A | 东京 | 0001 | 30
000A | 东京 | 0002 | 50
000A | 东京 | 0003 | 15
000B | 名古屋 | 0002 | 30
000B | 名古屋 | 0003 | 120
000B | 名古屋 | 0004 | 20
000B | 名古屋 | 0006 | 10
000B | 名古屋 | 0007 | 40
000C | 大阪 | 0003 | 20
000C | 大阪 | 0004 | 50
000C | 大阪 | 0006 | 90
000C | 大阪 | 0007 | 70
000D | 福冈 | 0001 | 100
(13 rows)
shop=#
代码清单6-36 使用子查询作为IN的参数
-- 取得“在大阪店销售的商品的销售单价”
SELECT product_name, sale_price
FROM Product
WHERE product_id IN (SELECT product_id
FROM ShopProduct
WHERE shop_id = '000C');执行结果：
product_name | sale_price
--------------+------------
运动T恤 | 4000
菜刀 | 3000
叉子 | 500
擦菜板 | 880
(4 rows)
shop=#
EXIST谓词
EXIST谓词的使用方法
代码清单6-38 使用EXIST选取出“大阪店在售商品的销售单价”
shop=# SELECT product_name, sale_price FROM Product AS P
shop-# WHERE EXISTS (SELECT * FROM ShopProduct AS SP WHERE SP.shop_id = '000C' AND SP.product_id = P.product_id);
product_name | sale_price
--------------+------------
运动T恤 | 4000
菜刀 | 3000
叉子 | 500
擦菜板 | 880
(4 rows)
shop=#
@EXIST的参数
EXIST 左侧没有任何参数。EXIST 只需要在右侧书写 1 个参数，该参数通常都会是一个子查询。
代码清单6-38 使用EXIST选取出“大阪店在售商品的销售单价”
shop=# SELECT product_name, sale_price FROM Product AS P
shop-# WHERE EXISTS (SELECT * FROM ShopProduct AS SP WHERE SP.shop_id = '000C' AND SP.product_id = P.product_id);
product_name | sale_price
--------------+------------
运动T恤 | 4000
菜刀 | 3000
叉子 | 500
擦菜板 | 880
(4 rows)
shop=#
通过条件"SP.product_id = P.product_id" 将 Product 表和 ShopProduct表进行了联接，因此作为参数的是关联子查询。EXIST 通常都会使用关联子查询作为参数。
@子查询中的SELECT *
EXIST 只关心记录是否存在，因此返回哪些列都没有关系。EXIST 只会判断是否存在满足子查询中 WHERE 子句指定的条
件，只有存在这样的记录时才返回真（TRUE）。
代码清单6-39 这样的写法也能得到与代码清单6-38相同的结果
SELECT product_name, sale_price
FROM Product AS P
WHERE EXISTS (SELECT 1 -- 这里可以书写适当的常数
FROM ShopProduct AS SP
WHERE SP.shop_id = '000C'
AND SP.product_id = P.product_id);
可以把在 EXIST 的子查询中书写 SELECT * 当作 SQL 的一种习惯。
6-3 CASE表达式
CASE表达式的语法
语法6-16 搜索CASE表达式
CASE WHEN <求值表达式> THEN <表达式>
WHEN <求值表达式> THEN <表达式>
WHEN <求值表达式> THEN <表达式>
. . .
ELSE <表达式>
END
语法6-A 简单CASE表达式
CASE <表达式>
WHEN <表达式> THEN <表达式>
WHEN <表达式> THEN <表达式>
WHEN <表达式> THEN <表达式>
. . .
ELSE <表达式>
END
CAS表达式的使用方法
代码清单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;执行结果
product_name | abc_product_type
--------------+------------------
T恤 | A ：衣服
打孔器 | B ：办公用品
运动T恤 | A ：衣服
菜刀 | C ：厨房用具
高压锅 | C ：厨房用具
叉子 | C ：厨房用具
擦菜板 | C ：厨房用具
圆珠笔 | B ：办公用品
(8 rows)
shop=#也可以使用简单CASE表达式
-- 使用简单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;
代码清单6-42 通常使用GROUP BY也无法实现行列转换
SELECT product_type, SUM(sale_price) AS sum_price
FROM Product GROUP BY product_type;执行结果：
product_type | sum_price
--------------+-----------
衣服 | 5000
办公用品 | 600
厨房用具 | 11180
(3 rows)
shop=#代码清单6-43 使用CASE表达式进行行列转换
-- 对按照商品种类计算出的销售单价合计值进行行列转换
shop=# SELECT SUM(CASE WHEN product_type = '衣服' THEN sale_price ELSE 0 END) AS sum_price_clothes,
shop-# SUM(CASE WHEN product_type = '厨房用具' THEN sale_price ELSE 0 END) AS sum_price_kitchen,
shop-# SUM(CASE WHEN product_type = '办公用品' THEN sale_price ELSE 0 END) AS sum_price_office
shop-# FROM Product;
sum_price_clothes | sum_price_kitchen | sum_price_office
-------------------+-------------------+------------------
5000 | 11180 | 600
(1 row)
shop=# 7 集合运算
7-1 表的加减法
表的加法 UNION
创建表Product2并添加数据
CREATE TABLE Product2
(product_id CHAR(4) NOT NULL,
product_name VARCHAR(100) NOT NULL,
product_type VARCHAR(32) NOT NULL,
sale_price INTEGER ,
purchase_price INTEGER ,
regist_date DATE ,
PRIMARY KEY (product_id));
BEGIN TRANSACTION;
INSERT INTO Product2 VALUES ('0001', 'T恤衫' ,'衣服', 1000, 500, '2008-09-20');
INSERT INTO Product2 VALUES ('0002', '打孔器', '办公用品', 500, 320, '2009-09-11');
INSERT INTO Product2 VALUES ('0003', '运动T恤', '衣服', 4000, 2800, NULL);
INSERT INTO Product2 VALUES ('0009', '手套', '衣服', 800, 500, NULL);
INSERT INTO Product2 VALUES ('0010', '水壶', '厨房用具', 2000, 1700, '2009-09-20');
COMMIT;Product和Product2内容如下
shop=# SELECT * FROM Product;
product_id | product_name | product_type | sale_price | purchase_price | regist_date
------------+--------------+--------------+------------+----------------+-------------
0001 | T恤 | 衣服 | 1000 | 500 | 2009-09-20
0002 | 打孔器 | 办公用品 | 500 | 320 | 2009-09-11
0003 | 运动T恤 | 衣服 | 4000 | 2800 |
0004 | 菜刀 | 厨房用具 | 3000 | 2800 | 2009-09-20
0005 | 高压锅 | 厨房用具 | 6800 | 5000 | 2009-01-15
0006 | 叉子 | 厨房用具 | 500 | | 2009-09-20
0007 | 擦菜板 | 厨房用具 | 880 | 790 | 2008-04-28
0008 | 圆珠笔 | 办公用品 | 100 | | 2009-11-11
(8 rows)
shop=# SELECT * FROM Product2;
product_id | product_name | product_type | sale_price | purchase_price | regist_date
------------+--------------+--------------+------------+----------------+-------------
0001 | T恤衫 | 衣服 | 1000 | 500 | 2008-09-20
0002 | 打孔器 | 办公用品 | 500 | 320 | 2009-09-11
0003 | 运动T恤 | 衣服 | 4000 | 2800 |
0009 | 手套 | 衣服 | 800 | 500 |
0010 | 水壶 | 厨房用具 | 2000 | 1700 | 2009-09-20
(5 rows)
shop=#代码清单7-3 使用UNION对表进行加法运算
SELECT product_id, product_name FROM Product
UNION SELECT product_id, product_name FROM Product2;执行结果：
product_id | product_name
------------+--------------
0006 | 叉子
0002 | 打孔器
0007 | 擦菜板
0003 | 运动T恤
0004 | 菜刀
0005 | 高压锅
0008 | 圆珠笔
0010 | 水壶
0009 | 手套
0001 | T恤衫
(10 rows)
shop=#UNION 等集合运算符通常都会除去重复的记录。
包含重复行的集合运算 ALL选项
UNION -> UNION ALL
代码清单7-5 保留重复行
SELECT product_id, product_name FROM Product
UNION ALL SELECT product_id, product_name FROM Product2;集合运算的注意事项
注意事项①——作为运算对象的记录的列数必须相同
注意事项②——作为运算对象的记录中列的类型必须一致
注意事项③——可以使用任何SELECT语句，但ORDER BY子句只能在最后使用一次
代码清单7-4 ORDER BY子句只在最后使用一次
shop=# SELECT product_id, product_name FROM Product WHERE product_type = '厨房用具'
shop-# UNION SELECT product_id, product_name FROM Product2 WHERE product_type = '厨房用具'
shop-# ORDER BY product_id;
product_id | product_name
------------+--------------
0004 | 菜刀
0005 | 高压锅
0006 | 叉子
0007 | 擦菜板
0010 | 水壶
(5 rows)
shop=#
选取表中公共部分(交集) INTERSECT
代码清单7-6 使用INTERSECT选取出表中公共部分
shop=# SELECT product_id, product_name FROM Product
shop-# INTERSECT SELECT product_id, product_name FROM Product2
shop-# ORDER BY product_id;
product_id | product_name
------------+--------------
0001 | T恤衫
0002 | 打孔器
0003 | 运动T恤
(3 rows)
shop=#
记录的减法 EXCEPT
代码清单7-7 使用EXCEPT对记录进行减法运算
shop=# SELECT product_id, product_name FROM Product
shop-# EXCEPT SELECT product_id, product_name FROM Product2
shop-# ORDER BY product_id;
product_id | product_name
------------+--------------
0004 | 菜刀
0005 | 高压锅
0006 | 叉子
0007 | 擦菜板
0008 | 圆珠笔
(5 rows)
shop=#7-2 联结（以列为单位对表进行联结）
UNION是以行（纵向）为单位进行操作，而联结则是以列（横向）为单位进行的。
内联结 INNER JOIN
代码清单7-9 将两张表进行内联结
shop=# SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name, P.sale_price
shop-# FROM ShopProduct AS SP INNER JOIN Product AS P
shop-# ON SP.product_id = P.product_id;
shop_id | shop_name | product_id | product_name | sale_price
---------+-----------+------------+--------------+------------
000A | 东京 | 0001 | T恤衫 | 1000
000A | 东京 | 0002 | 打孔器 | 500
000A | 东京 | 0003 | 运动T恤 | 4000
000B | 名古屋 | 0002 | 打孔器 | 500
000B | 名古屋 | 0003 | 运动T恤 | 4000
000B | 名古屋 | 0004 | 菜刀 | 3000
000B | 名古屋 | 0006 | 叉子 | 500
000B | 名古屋 | 0007 | 擦菜板 | 880
000C | 大阪 | 0003 | 运动T恤 | 4000
000C | 大阪 | 0004 | 菜刀 | 3000
000C | 大阪 | 0006 | 叉子 | 500
000C | 大阪 | 0007 | 擦菜板 | 880
000D | 福冈 | 0001 | T恤衫 | 1000
(13 rows)
shop=#
外联结——OUTER JOIN
指定主表的关键字是 LEFT 和 RIGHT。
代码清单7-11 将两张表进行外联结
SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name, P.sale_price
FROM ShopProduct AS SP RIGHT OUTER JOIN Product AS P
ON SP.product_id = P.product_id;附：
法则 6-1 通常指定关联子查询作为EXIST的参数。
法则 6-2 作为EXIST参数的子查询中经常会使用SELECT *。
法则 6-3 虽然CASE表达式中的ELSE子句可以省略，但还是希望大家不要省略。
法则 7-1 集合运算符会除去重复的记录。
法则 7-2 在集合运算符中使用ALL选项，可以保留重复行。
法则 7-3 进行联结时需要在FROM子句中使用多张表。
法则 7-4 进行内联结时必须使用ON子句，并且要书写在FROM和WHERE之间。
法则 7-5 使用联结时SELECT子句中的列需要按照“<表的别名>.<列名>”的格式进行书写。
法则 7-6 外联结中使用LEFT、RIGHT来指定主表。使用二者所得到的结果完全相同。
817
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
