运行环境: postgreSQL。

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

各种各样的函数

所谓函数,即输入参数(parameter), 输出相应返回值的功能,大致分为以下几种:

  • 算术函数(用来进行数值计算)
  • 字符串函数(用来进行字符串操作)
  • 日期函数(用来进行日期操作)
  • 转换函数(用来转换数据类型和值)
  • 聚合函数(用来进行数据聚合)

算术函数

创建表 SampleMath , 插入数据:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
-- DDL 创建表
CREATE TABLE SampleMath
	(m	NUMERIC(10, 3),
   n	INTEGER,
   p	INTEGER);

-- 插入数据
BEGIN TRANSACTION;

INSERT INTO SampleMath(m, n, p) VALUES (500, 0, NULL); 
INSERT INTO SampleMath(m, n, p) VALUES (-180, 0, NULL); 
INSERT INTO SampleMath(m, n, p) VALUES (NULL, NULL, NULL); 
INSERT INTO SampleMath(m, n, p) VALUES (NULL, 7, 3);
INSERT INTO SampleMath(m, n, p) VALUES (NULL, 5, 2); 
INSERT INTO SampleMath(m, n, p) VALUES (NULL, 4, NULL); 
INSERT INTO SampleMath(m, n, p) VALUES (8, NULL, 3); 
INSERT INTO SampleMath(m, n, p) VALUES (2.27, 1, NULL); 
INSERT INTO SampleMath(m, n, p) VALUES (5.555,2, NULL); 
INSERT INTO SampleMath(m, n, p) VALUES (NULL, 1, NULL); 
INSERT INTO SampleMath(m, n, p) VALUES (8.76, NULL, NULL);

COMMIT;

-- 确认表内容
SELECT * FROM SampleMath;

ABS — 绝对值

1
SELECT m, ABS(m) AS abs_col FROM SampleMath;

大多数函数对 NULL 都返回 NULL。

MOD — 求余

只能对整数类型的列使用 MOD 函数,因为小数计算没有余数的概念:

1
2
3
4
MOD(被除数, 除数)
-- SQL Server 使用"%" 求余

SELECT n, p, MOD(n, p) AS mod_col FROM SampleMath;

ROUND — 四舍五入

1
2
3
ROUND(对象数值, 保留小数的位数)

SELECT m, n, ROUND(m, n) AS round_col FROM SampleMath;

字符串函数

创建表 SampleStr,并插入数据:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
-- DDL 创建表
CREATE TABLE SampleStr
	(str1	VARCHAR(40),
   str2	VARCHAR(40),
   str3	VARCHAR(40));

-- 插入数据
BEGIN TRANSACTION;

INSERT INTO SampleStr (str1, str2, str3) VALUES ('opx', 'rt', NULL); 
INSERT INTO SampleStr (str1, str2, str3) VALUES ('abc', 'def', NULL); 
INSERT INTO SampleStr (str1, str2, str3) VALUES ('山田', '太郎', '是我');
INSERT INTO SampleStr (str1, str2, str3) VALUES ('aaa', NULL, NULL); 
INSERT INTO SampleStr (str1, str2, str3) VALUES (NULL, 'xyz', NULL); 
INSERT INTO SampleStr (str1, str2, str3) VALUES ('@!#$%', NULL ,NULL); 
INSERT INTO SampleStr (str1, str2, str3) VALUES ('ABC', NULL ,NULL); 
INSERT INTO SampleStr (str1, str2, str3) VALUES ('aBC', NULL ,NULL); 
INSERT INTO SampleStr (str1, str2, str3) VALUES ('abc太郎', 'abc', 'ABC'); 
INSERT INTO SampleStr (str1, str2, str3) VALUES ('abcdefabc', 'abc', 'ABC'); 
INSERT INTO SampleStr (str1, str2, str3) VALUES ('micmic', 'i' ,'I');

COMMIT;

-- 确认表内容
SELECT * FROM SampleStr;

|| — 拼接

当字符串拼接时,其中包含 NULL,那么得到的结果也是 NULL,因为 “||” 是变种的函数。

1
2
3
4
5
-- SQL 使用 "+" 拼接, MySQL 使用 CONCAT 函数完成拼接
SELECT str1, str2, str3,
	str1 || str2 || str3 AS str_concat
	FROM SampleStr
	WHERE str1 = '山田';

LENGTH — 字符串长度

1
2
3
4
LENGTH(字符串)

-- SQL Server 使用 LEN 函数
SELECT str1, LENGTH(str1) AS len_str FROM SampleStr;

LOWER — 小写转换

1
2
3
4
5
LOWER(字符串)

-- LOWER 只针对英文字母
SELECT str1, LOWER(str1) AS low_str FROM SampleStr 
	WHERE str1 IN ('ABC', 'aBC', 'abc', '山田');

相对地,大写转换的函数为 UPPER

1
2
3
4
5
UPPER(字符串)

-- UPPER 只针对英文字母
SELECT str1, UPPER(str1) AS up_str FROM SampleStr 
	WHERE str1 IN ('ABC', 'aBC', 'abc', '山田');

REPLACE — 字符串的替换

1
2
3
4
REPLACE(对象字符串, 替换前的字符串, 替换后的字符串)

SELECT str1, str2, str3, REPLACE(str1, str2, str3) AS rep_str
	FROM SampleStr;

SUBSTRING — 字符串截取

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
-- PostgreSQL 和 MySQL 支持
SUBSTRING(对象字符串 FROM 截取前的起始位置 FOR 截取的字符数)

SELECT str1, SUBSTRING(str1 FROM 3 FOR 2) AS sub_str
	FROM SampleStr;
	
-- SQL Server
SUBSTRING(对象字符串, 截取前的起始位置, 截取的字符数)

-- Oracle 和 DB2
SUBSTR(对象字符串, 截取前的起始位置, 截取的字符数)

日期函数

CURRENT_DATE — 当前日期

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
-- PostgreSQL/MySQL
SELECT CURRENT_DATE;

-- SQL Server
SELECT CAST(CURRENT_TIMESTAMP AS DATE) AS CUR_DATE;

-- Oracle
SELECT CURRENT_DATE FROM dual;

-- DB2
SELECT CURRENT DATE FROM SYSIBM.SYSDUMMY1;

CURRENT_TIME — 当前时间

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
-- PostgreSQL/MySQL
SELECT CURRENT_TIME;

-- SQL Server
SELECT CAST(CURRENT_TIMESTAMP AS TIME) AS CUR_DATE;

-- Oracle
SELECT CURRENT_TIMESTAMP FROM dual;

-- DB2
SELECT CURRENT TIME FROM SYSIBM.SYSDUMMY1;

CURRENT_TIMESTAMP — 当前日期和时间

1
2
3
4
5
6
7
8
-- SQL Server/PostgreSQL/MySQL
SELECT CURRENT_TIMESTAMP;

-- Oracle
SELECT CURRENT_TIMESTAMP FROM dual;

-- DB2
SELECT CURRENT TIMESTAMP FROM SYSIBM.SYSDUMMY1;

EXTRACT — 截取日期元素

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
-- PostgreSQL/MySQL
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;
  
-- SQL Server 使用 DATEPART 函数
SELECT CURRENT_TIMESTAMP,
	DATEPART(YEAR , CURRENT_TIMESTAMP) 		AS year, 
	DATEPART(MONTH , CURRENT_TIMESTAMP) 	AS month, 
	DATEPART(DAY , CURRENT_TIMESTAMP) 		AS day, 
	DATEPART(HOUR , CURRENT_TIMESTAMP) 		AS hour, 
	DATEPART(MINUTE , CURRENT_TIMESTAMP) 	AS minute, 
	DATEPART(SECOND , CURRENT_TIMESTAMP) 	AS second;

-- Oracle 和 DB2, 和 CURRENT_DATE 相同,FROM 临时表,DB2 需要在 CURRENT 和 TIMESTAMP 添加半角空格 

转换函数

转换在 SQL 中主要有两层意思:一是数据类型转换(cast);二是数值转换;

CAST — 类型转换

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
CAST(转换前的值 AS 想要转换的数据类型)

-- SQL Server/PostgreSQL
SELECT CAST('0001' AS INTEGER) AS int_col;

-- MySQL
SELECT CAST('0001' AS SIGNED INTEGER) AS int_col;

-- Oracle
SELECT CAST('0001' AS INTEGER) AS int_col FROM DUAL;

-- DB2
SELECT CAST('0001' AS INTEGER) AS int_col FROM SYSIBM.SYSDUMMY1;

COALESCE — 将 NULL 转换为其他值

该函数会返回可变参数中左侧开始第1个不是 NULL 的值。

当运算或者函数中含有 NULL 时,结果就会为 NULL,这时就可以利用 COALESCE 进行转换;又如获取非空的列等等,类似的用法还有很多。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
COALESCE(数据1, 数据2, 数据3......)

-- SQL Server/PostgreSQL/MySQL
SELECT 	COALESCE(NULL, 1) AS col_1, 
        COALESCE(NULL, 'test', NULL) AS col_2, 
        COALESCE(NULL, NULL, '2009-11-01') AS col_3;

-- Oracle
SELECT 	COALESCE(NULL, 1) AS col_1, 
        COALESCE(NULL, 'test', NULL) AS col_2, 
        COALESCE(NULL, NULL, '2009-11-01') AS col_3 
	FROM DUAL;

-- DB2
SELECT 	COALESCE(NULL, 1) AS col_1, 
        COALESCE(NULL, 'test', NULL) AS col_2, 
        COALESCE(NULL, NULL, '2009-11-01') AS col_3 
	FROM SYSIBM.SYSDUMMY1;

以下例子更好地说明了 COALESCE 的作用:

将表中 NULL 替换为 ”不确定“:

1
2
3
4
5
SELECT COALESCE(SP.shop_id, ' 不确定 ') AS shop_id, 	
       COALESCE(SP.shop_name, ' 不确定 ') AS shop_name, 
       P.product_id, P.product_name, P.sale_price 
	FROM ShopProduct SP RIGHT OUTER JOIN Product P 
	ON SP.product_id = P.product_id ORDER BY shop_id;

谓词(predicate)

什么是谓词

谓词是需要满足特定条件的函数,该条件就是返回值是真值(TRUE/FALSE/UNKNOWN)

LIKE 谓词 — 字符串的部分一致查询

需要进行字符串的部分一致查询时需要使用 LIKE 谓词。

创建表 SampleLike:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
-- DDL 
CREATE TABLE SampleLike
(	strcol VARCHAR(6) NOT NULL,
 	PRIMARY KEY (strcol));
 	
-- 插入数据
BEGIN TRANSACTION;

INSERT INTO SampleLike (strcol) VALUES ('abcddd'); 
INSERT INTO SampleLike (strcol) VALUES ('dddabc'); 
INSERT INTO SampleLike (strcol) VALUES ('abdddc'); 
INSERT INTO SampleLike (strcol) VALUES ('abcdd'); 
INSERT INTO SampleLike (strcol) VALUES ('ddabc'); 
INSERT INTO SampleLike (strcol) VALUES ('abddc');

COMMIT;

以字符串中是否包含该条件的规则为基础的查询成为模式匹配

  • 前方一致查询

    1
    
    SELECT * FROM SampleLike WHERE strcol LIKE 'ddd%';
  • 中间一致查询

    1
    
    SELECT * FROM SampleLike WHERE strcol LIKE '%ddd%';
  • 后方一致查询

    1
    
    SELECT * FROM SampleLike WHERE strcol LIKE '%ddd';

此外,还可以使用_代替%,其表示“任意一个字符”:

1
SELECT * FROM SampleLike WHERE strcol LIKE 'abc__';

BETWEEN 谓词 — 范围查询

示例,从 product 表中读取出销售单价(sale_price)为 100~1000 之间的商品:

1
2
SELECT product_name, sale_price FROM Product
	WHERE sale_price BETWEEN 100 AND 1000;

BETWEEN 的结果包含临界值,如果不想包含临界值,则使用”<“和”>“。

IS NULL、IS NOT NULL — 判断是否为 NULL

示例,从 product 表中选取出进货单价(purchase_price)为 NULL 的商品:

1
2
3
4
5
6
SELECT product_name, purchase_price FROM Product
	WHERE purchase_price IS NULL;
	
-- 相反地,选取进货单价不为 NULL 的商品
SELECT product_name, purchase_price FROM Product
	WHERE purchase_price IS NOT NULL;

IN 谓词 — OR 的简便用法

以下是通过 OR 选取进货单价为 320,500,5000 的商品的 SQL:

1
2
3
4
SELECT product_name, purchase_price	FROM Product
	WHERE purchase_price = 320
		OR	purchase_price = 500
		OR	purchase_price = 5000;

随着选取对象越来越多,SQL 语句会变得越来越长,通过IN(value1, value2, ……)来简化以上语句:

1
2
3
4
5
6
SELECT product_name, purchase_price	FROM Product
	WHERE purchase_price IN (320, 500, 5000);

-- 相反地,使用 NOT IN 选取相反的结果
SELECT product_name, purchase_price	FROM Product
	WHERE purchase_price NOT IN (320, 500, 5000);

注意:IN 和 NOT IN 都无法选择出 NULL 数据, NULL 数据终究需要 IS NULL 和 IS NOT NULL 进行判断。

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

创建表 ShopProduct:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
-- 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));
  
-- 插入数据
BEGIN TRANSACTION;

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;

IN 和子查询

示例,读取”大阪店”在售商品的销售单价:

1
2
3
4
5
6
7
8
9
-- 读取大阪店在售商品
SELECT product_id FROM ShopProduct WHERE shop_id = '000C';

-- 取得“在大阪店销售的商品的销售单价“
SELECT product_name, sale_price	
	FROM Product 
	WHERE product_id IN (SELECT product_id 
                       	FROM ShopProduct 
                       WHERE shop_id = '000C');

NOT IN 和子查询

相应地,NOT IN 也可以结合子查询使用:

1
2
3
4
5
SELECT product_name, sale_price
	FROM Product 
	WHERE product_id NOT IN (SELECT product_id 
                           	FROM ShopProduct 
                           WHERE shop_id = '000A');

EXIST谓词

示例,选取“大阪在售商品的销售单价”:

1
2
3
4
SELECT product_name, sale_price FROM Product AS P
	WHERE EXISTS (SELECT * FROM ShopProduct AS SP 
               		WHERE SP.shop_id = '000C'
               			AND SP.product_id = P.product_id);

EXIST 的参数为上面的关联子查询,通过 EXIST 通常都会使用关联子查询作为参数。

子查询中的 SELECT *

EXIST 只会关心记录是否存在,和返回哪些列没有关系,因此这里的 SELECT * 只是一种习惯,写成 SELECT 1 结果也不会发生改变。

使用 NOT EXIST 替换 NOT IN

示例,读取“东京店(000A)在售以外的商品的销售单价”:

1
2
3
4
SELECT product_name, sale_price FROM Product AS P
	WHERE NOT EXISTS (SELECT * FROM ShopProduct AS SP 
               		WHERE SP.shop_id = '000A'
               			AND SP.product_id = P.product_id);

CASE 表达式

CASE 表达式是在区分(条件)分支情况下使用的,其语法分为简单 CASE 表达式搜索 CASE 表达式,搜索 CASE 表达式包含了简单 CASE 表达式的全部功能,以下是搜索 CASE 表达式的语法:

1
2
3
4
5
6
7
8
CASE WHEN <求值表达式> THEN <表达式>
		 WHEN <求值表达式> THEN <表达式>
		 WHEN <求值表达式> THEN <表达式>
		 	.
		 	.
			.
		 ELSE <表达式>
END

<求值表达式>返回值为真值(TRUE/FALSE/UNKNOWN)的表达式,也可以看作使用=, !=或者LIKE, BETWEEN 等谓词编写的表达式。

CASE 表达式会从最初的 WHEN 子句开始,当<求值表达式>为真时,则执行 THEN 子句中的表达式,CASE 表达式执行结束,如果不为真,则继续执行,如果 WHEN 子句全不为真,则返回 ELSE 中的表达式,执行终止。

CASE 表达式的使用方法

示例,通过 CASE 表达式将产品类型增加 ABC 前缀:

1
2
3
4
5
6
7
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;

ELSE 子句也可以省略不写,这时默认为 ELSE NULL,此外,END 是不能省略的。

CASE 表达式可以书写在任意位置,示例,将下列 SELECT 语句结果中的行和列进行交换:

1
2
3
4
5
6
7
8
SELECT product_type, SUM(sale_price) AS sum_price
	FROM Product GROUP BY product_type;

-- 结果
product_type	sum_price
衣服	5000
办公用品	600
厨房用具	11180

使用 CASE 表达式进行行列转换:

1
2
3
4
5
-- 对按照商品种类计算出的销售单价合计值进行行列转换 
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;