SQL 基础知识梳理（六）- 函数、谓词、CASE 表达式
SQL 基础知识梳理（六）- 函数、谓词、CASE 表达式
目录
- 函数
- 谓词
- CASE 表达式
一、函数
1.函数：输入某一值得到相应输出结果的功能，输入值称为“参数”，输出值称为“返回值”。
2.函数的种类：
（1）算术函数 - 数值计算
（2）字符串函数 - 字符串操作
（3）日期函数 - 日期操作
（4）转换函数 - 转换数据类型
（5）聚合函数 - 数据聚合
3.算术函数（加、减、乘、除）：+、-、*、/
【备注】数据类型 NUMBERIC（全体位数，小数位数）可以指定数值的大小。
1 CREATE TABLE SampleMath 2 ( 3 m NUMERIC(10, 3) , 4 n INTEGER , 5 p INTEGER 6 ); 7 8 BEGIN TRAN; 9 10 INSERT INTO dbo.SampleMath 11 ( m, n, p ) 12 VALUES ( 500, -- m - numeric 13 0, -- n - integer 14 NULL -- p - integer 15 ); 16 INSERT INTO dbo.SampleMath 17 ( m, n, p ) 18 VALUES ( -180, -- m - numeric 19 0, -- n - integer 20 NULL -- p - integer 21 ); 22 INSERT INTO dbo.SampleMath 23 ( m, n, p ) 24 VALUES ( NULL, -- m - numeric 25 NULL, -- n - integer 26 NULL -- p - integer 27 ); 28 INSERT INTO dbo.SampleMath 29 ( m, n, p ) 30 VALUES ( NULL, -- m - numeric 31 7, -- n - integer 32 -- p - integer 33 ); 34 INSERT INTO dbo.SampleMath 35 ( m, n, p ) 36 VALUES ( NULL, -- m - numeric 37 5, -- n - integer 38 -- p - integer 39 ); 40 INSERT INTO dbo.SampleMath 41 ( m, n, p ) 42 VALUES ( NULL, -- m - numeric 43 4, -- n - integer 44 NULL -- p - integer 45 ); 46 INSERT INTO dbo.SampleMath 47 ( m, n, p ) 48 VALUES ( 8, -- m - numeric 49 NULL, -- n - integer 50 -- p - integer 51 ); 52 INSERT INTO dbo.SampleMath 53 ( m, n, p ) 54 VALUES ( 2.27, -- m - numeric 55 1, -- n - integer 56 NULL -- p - integer 57 ); 58 INSERT INTO dbo.SampleMath 59 ( m, n, p ) 60 VALUES ( 5.555, -- m - numeric 61 2, -- n - integer 62 NULL -- p - integer 63 ); 64 INSERT INTO dbo.SampleMath 65 ( m, n, p ) 66 VALUES ( NULL, -- m - numeric 67 1, -- n - integer 68 NULL -- p - integer 69 ); 70 INSERT INTO dbo.SampleMath 71 ( m, n, p ) 72 VALUES ( 8.76, -- m - numeric 73 NULL, -- n - integer 74 NULL -- p - integer 75 ); 76 77 COMMIT; 78 79 初始化数据
（1）ABS - 绝对值：不考虑数值的符号，表示一个数到原点距离的数值。
绝对值的计算方法：0 和正数的绝对值就是其本身，负数的绝对值就是去掉符号后的结果。
--语法： ABS(数值)
图：第 2 行：-180 的绝对值为 180
（2）MOD - 取余、求余
--语法： MOD(被除数，除数)
【备注】Oracle、DB2、PostgreSQL、MySQL 支持该函数，而 SQL Server 不支持该函数，所以这里用“%”代替。
（3）ROUND - 四舍五入
如果指定四舍五入的位数为 1，那么会对小数点第 2 位进行四舍五入；如果指定位数为 2，那么就会对第 3 位进行四舍五入。
4.字符串函数
1 CREATE TABLE SampleStr 2 ( 3 str1 VARCHAR(40), 4 str2 VARCHAR(40), 5 str3 VARCHAR(40) 6 ) 7 8 BEGIN TRAN; 9 INSERT INTO dbo.SampleStr 10 ( str1, str2, str3 ) 11 VALUES ( 'opx', -- str1 - varchar(40) 12 'rt', -- str2 - varchar(40) 13 NULL -- str3 - varchar(40) 14 ); 15 16 INSERT INTO dbo.SampleStr 17 ( str1, str2, str3 ) 18 VALUES ( 'abc', -- str1 - varchar(40) 19 'def', -- str2 - varchar(40) 20 NULL -- str3 - varchar(40) 21 ); 22 23 INSERT INTO dbo.SampleStr 24 ( str1, str2, str3 ) 25 VALUES ( 'aaa', -- str1 - varchar(40) 26 NULL, -- str2 - varchar(40) 27 NULL -- str3 - varchar(40) 28 ); 29 30 INSERT INTO dbo.SampleStr 31 ( str1, str2, str3 ) 32 VALUES ( 'aaa', -- str1 - varchar(40) 33 NULL, -- str2 - varchar(40) 34 NULL -- str3 - varchar(40) 35 ); 36 37 INSERT INTO dbo.SampleStr 38 ( str1, str2, str3 ) 39 VALUES ( NULL, -- str1 - varchar(40) 40 'xyz', -- str2 - varchar(40) 41 NULL -- str3 - varchar(40) 42 ); 43 44 INSERT INTO dbo.SampleStr 45 ( str1, str2, str3 ) 46 VALUES ( '@!#$%', -- str1 - varchar(40) 47 NULL, -- str2 - varchar(40) 48 NULL -- str3 - varchar(40) 49 ); 50 51 INSERT INTO dbo.SampleStr 52 ( str1, str2, str3 ) 53 VALUES ( 'ABC', -- str1 - varchar(40) 54 NULL, -- str2 - varchar(40) 55 NULL -- str3 - varchar(40) 56 ); 57 58 INSERT INTO dbo.SampleStr 59 ( str1, str2, str3 ) 60 VALUES ( 'aBC', -- str1 - varchar(40) 61 NULL, -- str2 - varchar(40) 62 NULL -- str3 - varchar(40) 63 ); 64 65 INSERT INTO dbo.SampleStr 66 ( str1, str2, str3 ) 67 VALUES ( 'abc太郎', -- str1 - varchar(40) 68 'abc', -- str2 - varchar(40) 69 'ABC' -- str3 - varchar(40) 70 ); 71 72 INSERT INTO dbo.SampleStr 73 ( str1, str2, str3 ) 74 VALUES ( 'abcdefabc', -- str1 - varchar(40) 75 'abc', -- str2 - varchar(40) 76 'ABC' -- str3 - varchar(40) 77 ); 78 79 INSERT INTO dbo.SampleStr 80 ( str1, str2, str3 ) 81 VALUES ( 'micmic', -- str1 - varchar(40) 82 'i', -- str2 - varchar(40) 83 'T' -- str3 - varchar(40) 84 ); 85 86 COMMIT; 87 88 初始化数据
（1）拼接：+
（2）LEN - 字符串长度
--语法： LEN(字符串)
（3）LOWER - 小写转换
--语法：LOWER(字符串)
（4）REPLACE - 字符串的替换
--语法：REPLACE(对象字符串, 替换前的字符串, 替换后的字符串)
（5）SUBSTRING - 字符串的截取
--语法：SUBSTRING(对象字符串,截取的起始位置,截取的字符数)
（6）UPPER - 大写转换
--语法：UPPER(字符串)
5.日期函数
（1）获取当前日期和时间：
（2）DATEPART - 截取日期元素
6.转换函数
（1）CAST - 类型转换
（2）COALESCE - 将 NULL 转换为其他值
作用：返回可变参数中左侧开始的第一个不是 NULL 的值（参数是可变的，即可以个数是无限的）。
--语法：COALESCE(数据1, 数据2, 数据3 ...)
图
图
二、谓词
1.谓词：返回值为真值（TRUE/FALSE/UNKNOWN）的函数。
2.LIKE - 字符串的部分一致查询
【备注】= 运算符：字符串完全一致。
1 CREATE TABLE SampleLike 2 ( 3 strcool VARCHAR(6) NOT NULL, 4 PRIMARY KEY(strcool) 5 ) 6 7 BEGIN TRAN; 8 INSERT INTO dbo.SampleLike 9 ( strcool ) 10 VALUES ( 'abcddd' -- strcool - varchar(6) 11 ); 12 13 INSERT INTO dbo.SampleLike 14 ( strcool ) 15 VALUES ( 'dddabc' -- strcool - varchar(6) 16 ); 17 18 INSERT INTO dbo.SampleLike 19 ( strcool ) 20 VALUES ( 'abdddc' -- strcool - varchar(6) 21 ); 22 23 INSERT INTO dbo.SampleLike 24 ( strcool ) 25 VALUES ( 'ddabc' -- strcool - varchar(6) 26 ) 27 28 INSERT INTO dbo.SampleLike 29 ( strcool ) 30 VALUES ( 'abddc' -- strcool - varchar(6) 31 ) 32 33 COMMIT; 34 35 初始化数据
%：0 字符以上的任意字符串。
_：任意 1 个字符。
图：前部分一致
图：中间一致
图：后部分一致
后面 ddd 是 3 个字符，所以“abc__（2个 _）”不满足条件。
3.BETWEEN - 范围查询
BETWEEN 会在结果中包含临界值（100 和 1000）。如果不想包含临界值可以使用 < 和 >。
4.IS NULL、IS NOT NULL - 判断是否为 NULL
为了选取部分值为 NULL 的列的数据，不能使用 =，只能使用 IS NULL。
取反（不为空的数据），请使用 IS NOT NULL。
5.IN - OR 的简便用法
用 IN 替换上述语句：
否定形式 NOT IN：
【备注】IN 和 NOT IN 是无法选取 NULL 数据的。
6.使用子查询作为 IN 谓词的参数
IN 和 NOT IN 谓词具有其它谓词没有的用法，它的参数可以是子查询。
1 -- DDL：创建表 2 CREATE TABLE TenpoShohin 3 (tenpo_id CHAR(4) NOT NULL, 4 tenpo_mei VARCHAR(200) NOT NULL, 5 shohin_id CHAR(4) NOT NULL, 6 suryo INTEGER NOT NULL, 7 PRIMARY KEY (tenpo_id, shohin_id)); 8 9 -- DML：插入数据 10 11 INSERT INTO TenpoShohin (tenpo_id, tenpo_mei, shohin_id, suryo) VALUES ('000A', '东京', '0001', 30); 12 INSERT INTO TenpoShohin (tenpo_id, tenpo_mei, shohin_id, suryo) VALUES ('000A', '东京', '0002', 50); 13 INSERT INTO TenpoShohin (tenpo_id, tenpo_mei, shohin_id, suryo) VALUES ('000A', '东京', '0003', 15); 14 INSERT INTO TenpoShohin (tenpo_id, tenpo_mei, shohin_id, suryo) VALUES ('000B', '名古屋', '0002', 30); 15 INSERT INTO TenpoShohin (tenpo_id, tenpo_mei, shohin_id, suryo) VALUES ('000B', '名古屋', '0003', 120); 16 INSERT INTO TenpoShohin (tenpo_id, tenpo_mei, shohin_id, suryo) VALUES ('000B', '名古屋', '0004', 20); 17 INSERT INTO TenpoShohin (tenpo_id, tenpo_mei, shohin_id, suryo) VALUES ('000B', '名古屋', '0006', 10); 18 INSERT INTO TenpoShohin (tenpo_id, tenpo_mei, shohin_id, suryo) VALUES ('000B', '名古屋', '0007', 40); 19 INSERT INTO TenpoShohin (tenpo_id, tenpo_mei, shohin_id, suryo) VALUES ('000C', '大阪', '0003', 20); 20 INSERT INTO TenpoShohin (tenpo_id, tenpo_mei, shohin_id, suryo) VALUES ('000C', '大阪', '0004', 50); 21 INSERT INTO TenpoShohin (tenpo_id, tenpo_mei, shohin_id, suryo) VALUES ('000C', '大阪', '0006', 90); 22 INSERT INTO TenpoShohin (tenpo_id, tenpo_mei, shohin_id, suryo) VALUES ('000C', '大阪', '0007', 70); 23 INSERT INTO TenpoShohin (tenpo_id, tenpo_mei, shohin_id, suryo) VALUES ('000D', '福冈', '0001', 100); 24 25 测试数据
图
7.EXIST
很多时候基本上可以使用 IN 或 NOT IN 来代替该谓词。
作用：判断是否存在满足某种条件的记录。
NOT EXIST 与 EXIST 相反，不存在：
三、CASE 表达式
1.CASE 表达式：（条件）分歧。
2.语法
--语法 --CASE WHEN <判断表达式> THEN <表达式> -- WHEN <判断表达式> THEN <表达式> -- ... -- ELSE <表达式> --END
判断表达式类似“键 = 值”的形式，返回值为真值（TRUE/FALSE/UNKNOW）的表达式。如果结果为真，就会返回 THEN 子句中的表达式；如果不为真，就跳转到下一条 WHEN 子句的判断中；如果到最后的 WHEN 子句都不为真，就执行最后一条 ELSE 的表达式。
下面是简化版的 CASE 表达式：
3.行转列
备注
这里采用 MS SQL Server 进行验证，不保证所有的 DBMS 执行结果正确。
