6 SQL 函数、谓词、CASE表达式

6 函数、谓词、CASE表达式

6-1 各种各样的函数

所谓函数，就是输入某一值得到相应输出结果的功能。输入值称为参数（parameter），输出值称为返回值。

函数大致可以分为以下几种：

算术函数：用来进行数值计算的函数

字符串函数：用来进行字符串操作的函数

日期函数：用来进行日期操作的函数

转换函数：用来转换数据类型和值的函数

聚合函数：用来进行数据聚合的函数

算术函数

-- DDL ：创建表

CREATE TABLE sampleMath (

m NUMERIC (10, 3),

n INTEGER,

p INTEGER

);

-- DML : 插入数据

START 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.67, NULL, NULL);

COMMIT;

SELECT * FROM sampleMath;

ABS是计算绝对值的函数。绝对值（absolute value）就是不考虑数值的符合，表示一个数到原点距离的数值。简单来讲，绝对值的计算方法就是：0和正数的绝对值就是其本身，负数的绝对值就是去掉符号后的结果。

通过上述结果我们可以发现，ABS函数的参数为NULL时，结果也是NULL。并非只有ABS函数如此，其实绝大多数函数对于NULL的结果都是NULL。

MOD是计算除法余数（剩余）的函数，是modulo的简称。例如，7/3的余数是1，因此MOD（7，3）的结果也是1.由于小数计算中并没有余数的概念，所以只能对整数类型的列使用MOD函数。

这里有一点需要大家注意。主流DBMS都支持MOD函数，只有SQL Server不支持该函数。

ROUND函数用来进行四舍五入操作。

绝对值、余数、四舍五入、

-- ABS(数值)

SELECT m, ABS(m) AS abs_col FROM sampleMath;

-- MOD(被除数，除数) 主流的DBMS都支持MOD函数，只有SQL Server不支持该函数。

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

-- SQL Server特殊处理方法

SELECT n, p, n % p AS mod_col FROM sampleMath;

-- ROUND(对象数值, 保留小数的位数)

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

字符串函数

-- DDL ：创建表

CREATE TABLE sampleStr (

str1 VARCHAR(40),

str2 VARCHAR(40),

str3 VARCHAR(40)

);

-- DML : 插入数据

START TRANSACTION;

INSERT INTO sampleStr(str1, str2, str3) VALUES ('opt', '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 ('micmic', 'i', 'I');

COMMIT;

SELECT * FROM sampleStr;

拼接（||）、字符串长度、小（大）写转换、字符串的替换、字符串截取

-- oracle db2 postgresql

SELECT str1, str2, str1 || str2 AS str_concat FROM sampleStr;

SELECT str1, str2, str3, str1 || str2 || str3 AS str_concat FROM sampleStr;

-- SQL SERVER

SELECT str1, str2, str3, str1 + str2 + str3 AS str_concat FROM sampleStr;

-- mysql

SELECT str1, str2, str3, CONCAT(str1, str2, str3) AS str_concat FROM sampleStr;

-- LENGTH(字符串) 该函数无法在SQL Server中使用

SELECT str1, LENGTH(str1) AS len_str FROM sampleStr;

-- SQL SERVER

SELECT str1, LEN(str1) AS len_str FROM sampleStr;

-- 对1个字节使用LENGTH函数有可能得到2字节以上的结果

-- LOWER(字符串) UPPER(字符串)

SELECT str1, LOWER(str1) AS low_str FROM sampleStr WHERE str1 IN ('ABC', 'aBC', 'abc', '山田');

-- REPLACE(对象字符串,替换前的字符串,替换后的字符串)

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

-- SUBSTRING(对象字符串 FROM 截取的起始位置 FOR 截取的字符数) 此函数为postgresql和mysql专用语法

-- postgresql和mysql

SELECT str1, SUBSTRING(str1 FROM 3 FOR 2) AS sub_str FROM sampleStr;

-- 特定的sql

-- SUBSTRING(对象字符串，截取的起始位置，截取的字符串) sql SERVER专用

-- SUBSTR(对象字符串，截取的起始位置，截取的字符数) oracle db2专用

SELECT str1 SUBSTRING(str1, 3, 2) AS sub_str FROM sampleStr;

SELECT str1 SUBSTR(str1, 3, 2) AS sub_str FROM sampleStr;

-- UPPER(字符串)

SELECT str1, UPPER(str1) AS up_str FROM sampleStr WHERE str1 IN ('ABC', 'aBC', 'abc', '山田');

-- UPPER(字符串)

SELECT str1, UPPER(str1) AS up_str FROM sampleStr WHERE str1 IN ('ABC', 'aBC', 'abc', '山田');

日期函数、当前日期、当前时间、当前日期和时间、截取日期元素

-- 使用于postgresql和mysql 无法在sql server 中执行，此外Oracle和db2中的语法略有不同

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;

-- 使用于postgresql和mysql 无法在sql server 中执行，此外Oracle和db2中的语法略有不同

SELECT CURRENT_TIME;

-- 使用于postgresql和mysql 无法在sql server 中执行，此外Oracle和db2中的语法略有不同

SELECT CURRENT_TIMESTAMP;

-- EXTRACT(日期元素 FROM 日期) postgresql 和 mysql sql server 也无法使用该函数

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中主要有两层意思。一是数据类型的转换，简称为“类型转换”，在英语中称为cast。另一层意思是值的转换。

转换函数

-- CAST(转换前的值 AS 想要转换的数据类型)

-- sql server postresql

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;

-- SQL SERVER POSTGRESQL mysql

SELECT CAST('2009-12-14' AS DATE) AS date_col;

-- oracle

SELECT CAST('2009-12-14' AS DATE) AS date_col FROM DUAL;

-- db2

SELECT CAST('2009-12-14' AS DATE) AS date_col FROM SYSIBM.SYSDUMMY1;

-- COALESCE(数据1, 数据2, ...)

-- 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;

-- 有疑问

SELECT * FROM samplestr;

SELECT COALESCE(str2, 'NULL') FROM samplestr;

COALESCE是SQL特有的函数。该函数会返回可变参数中左侧开始第一个不是NULL的值。参数个数是可变的，因此可以根据需要无限增加。

6-2 谓词

本节将会和大家一起学习SQL的抽出条件中不可或缺的工具—谓词（predicate）。

谓词，通俗来讲就是函数中的一种，是需要满足特定条件的函数。该条件就是“返回值是真值”。对通常函数来说，返回值有可能是数字、字符串或者日期等等，但是谓词的返回值全部是真值（TRUE/FALSE/UNKNOWN）。这也是谓词和函数的最大区别。

LIKE/BETWEEN/IS NULL、IS NOT NULL/IN/EXISTS

LIKE谓词更加模糊一些，当需要进行字符串的部分一致查询时需要使用该谓词。

部分一致大体可以分为前方一致、中间一致和后方一致三种类型。

像这样不适用“=”来指定条件字符串，而以字符串中是否包含该条件的规则为基础的查询称为“模式匹配”。其中的模式也就是前面提到的“规则”。

其中“%”是代表“0字符以上的任意字符串”的特殊符号。

此外，我们还可以使用 _ (下划线) 来代替“%”，与%不同的是其代表了“任意1个字符”。

LIKE谓词-字符串的部分查询

-- DDL ：创建表

CREATE TABLE sampleLike (

strcol VARCHAR(6) NOT NULL,

PRIMARY KEY (strcol)

);

-- DML ：插入数据

START 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 ('abcddd');

INSERT INTO sampleLike (strcol) VALUES ('abddc');

COMMIT;

SELECT * FROM sampleLike WHERE strcol LIKE 'ddd%';

SELECT * FROM sampleLike WHERE strcol LIKE '%ddd%';

SELECT * FROM sampleLike WHERE strcol LIKE '%ddd';

SELECT * FROM sampleLike WHERE strcol LIKE 'abc__';

SELECT * FROM sampleLike WHERE strcol LIKE 'abc___';

使用BETWEEN可以进行范围查询。

BETWEEN的特点就是结果中会包含100和1000这两个临界值。如果不想让结果中包含临界值，那就必须使用<（小于）和>（大于）。

BETWEEN谓词-范围查询

SELECT shohin_mei, hanbai_tanka FROM shohin WHERE hanbai_tanka BETWEEN 100 AND 1000;

SELECT shohin_mei, hanbai_tanka FROM shohin WHERE hanbai_tanka > 100 AND hanbai_tanka < 1000;

为了选取出某些值为NULL的列的数据，不能使用=，而只能使用特定的谓词IS NULL。

与之相反，想要选取NULL以外的数据时，需要使用IS NOT NULL。

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

SELECT shohin_mei, shiire_tanka FROM shohin WHERE shiire_tanka IS NULL;

SELECT shohin_mei, shiire_tanka FROM shohin WHERE shiire_tanka IS NOT NULL;

需要注意的是，在使用IN和NOT IN时是无法选取出NULL数据的。NULL终究还是需要使用IS NULL和IS NOT NULL来进行判断。

IN谓词-OR的简便用法

SELECT shohin_mei, shiire_tanka FROM shohin WHERE shiire_tanka = 300 OR shiire_tanka = 500 OR shiire_tanka = 5000;

SELECT shohin_mei, shiire_tanka FROM shohin WHERE shiire_tanka IN (300, 500, 5000);

SELECT shohin_mei, shiire_tanka FROM shohin WHERE shiire_tanka NOT IN (300, 500, 5000);

IN谓词（NOT IN谓词）具有其他谓词所没有的使用方法。那就是可以使用子查询作为其参数来使用。子查询就是SQL内部生成的表。因此也可以说“能够将表作为IN的参数”。同理，我们还可以说“能够将视图作为IN的参数”。

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

CREATE TABLE tenpoShohin (

tenpo_id CHAR(4) NOT NULL,

ten_mei VARCHAR(200) NOT NULL,

shohin_id CHAR(4) NOT NULL,

suryo INTEGER NOT NULL,

PRIMARY KEY (tenpo_id, shohin_id)

);

-- 该CREATE TABLE语句的特点是指定了2列作为主键（primary key）。这样做的目的当然还是为了区分表中每一行数据。

START TRANSACTION;

INSERT INTO tenpoShohin (tenpo_id, ten_mei, shohin_id, suryo) VALUES ('000A', '东京', '0001', 30);

INSERT INTO tenpoShohin (tenpo_id, ten_mei, shohin_id, suryo) VALUES ('000A', '东京', '0002', 50);

INSERT INTO tenpoShohin (tenpo_id, ten_mei, shohin_id, suryo) VALUES ('000A', '东京', '0003', 15);

INSERT INTO tenpoShohin (tenpo_id, ten_mei, shohin_id, suryo) VALUES ('000B', '名古屋', '0002', 30);

INSERT INTO tenpoShohin (tenpo_id, ten_mei, shohin_id, suryo) VALUES ('000B', '名古屋', '0003', 120);

INSERT INTO tenpoShohin (tenpo_id, ten_mei, shohin_id, suryo) VALUES ('000B', '名古屋', '0004', 20);

INSERT INTO tenpoShohin (tenpo_id, ten_mei, shohin_id, suryo) VALUES ('000B', '名古屋', '0006', 10);

INSERT INTO tenpoShohin (tenpo_id, ten_mei, shohin_id, suryo) VALUES ('000B', '名古屋', '0007', 40);

INSERT INTO tenpoShohin (tenpo_id, ten_mei, shohin_id, suryo) VALUES ('000C', '大阪', '0003', 20);

INSERT INTO tenpoShohin (tenpo_id, ten_mei, shohin_id, suryo) VALUES ('000C', '大阪', '0004', 50);

INSERT INTO tenpoShohin (tenpo_id, ten_mei, shohin_id, suryo) VALUES ('000C', '大阪', '0006', 90);

INSERT INTO tenpoShohin (tenpo_id, ten_mei, shohin_id, suryo) VALUES ('000C', '大阪', '0007', 70);

INSERT INTO tenpoShohin (tenpo_id, ten_mei, shohin_id, suryo) VALUES ('000D', '福冈', '0001', 100);

COMMIT;

SELECT shohin_id FROM tenpoShohin WHERE tenpo_id = '000C';

SELECT shohin_mei, hanbai_tanka FROM shohin WHERE shohin_id IN (SELECT shohin_id FROM tenpoShohin WHERE tenpo_id = '000C');

SELECT shohin_mei, hanbai_tanka FROM shohin WHERE shohin_id NOT IN (SELECT shohin_id FROM tenpoShohin WHERE tenpo_id = '000A');

实际上，由于各个商店销售的商品都在不断发生变化，所以tenpoShohin表内大阪店销售的商品也会发生变化。如果SELECT语句中没有使用子查询的话，一旦商品发生了改变，那么SELECT语句也不得不进行修改。而且这样的修改工作会变得没完没了。

反之，如果在SELECT语句中使用了子查询，那么即使数据发生了变更，还可以继续使用同样的SELECT语句。这样也就减少了我们的常规作业（单纯的重复操作）。

像这样可以完美应对数据变更的程序称为“易维护程序”，或者“免维护程序”。这也是系统开发中需要重点考虑的部分。希望大家在开始学习编程时，就能够有意识地编写易于维护的代码。

IN的否定形式NOT IN同样可以使用子查询作为参数，其语法也和IN完全一样。

EXIST谓词，难点，了解即可。

一言以弊之，谓词的作用就是“判断是否存在满足某种条件的记录”。如果存在这样的记录就返回真（TRUE），如果不存在就返回假（FALSE）。EXIST（存在）谓词的主语是“记录”。

EXIST谓词