SQL 基础知识梳理（六）-  函数、谓词、CASE 表达式

目录

• 函数
• 谓词
• CASE 表达式

一、函数

1.函数：输入某一值得到相应输出结果的功能，输入值称为”参数，输出值称为”返回值。

2.函数的种类：

（1）算术函数 - 数值计算

（2）字符串函数 - 字符串操作

（3）日期函数 - 日期操作

（4）转换函数 - 转换数据类型

（5）聚合函数 - 数据聚合

　　3.算术函数（加、减、乘、除）：+、-、*、/

【备注】数据类型 NUMBERIC（全体位数，小数位数）可以指定数值的大小。

CREATE TABLE SampleMath
    (
      m NUMERIC(10, 3) ,
      n INTEGER ,
      p INTEGER
    );

BEGIN TRAN;

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

COMMIT;

初始化数据

（1）ABS - 绝对值：不考虑数值的符号，表示一个数到原点距离的数值。

绝对值的计算方法：0 和正数的绝对值就是其本身，负数的绝对值就是去掉符号后的结果。

--语法： ABS(数值)

图：第 2 行：-180 的绝对值为 180

 　　

（2）MOD - 取余、求余

--语法： MOD(被除数，除数)

【备注】Oracle、DB2、PostgreSQL、MySQL 支持该函数，而 SQL Server 不支持该函数，所以这里用”%代替。

（3）ROUND - 四舍五入

 　　如果指定四舍五入的位数为 1，那么会对小数点第 2 位进行四舍五入；如果指定位数为 2，那么就会对第 3 位进行四舍五入。

 　　4.字符串函数

CREATE TABLE SampleStr
(
    str1 VARCHAR(40),
    str2 VARCHAR(40),
    str3 VARCHAR(40)
)

BEGIN TRAN;
INSERT INTO dbo.SampleStr
        ( str1, str2, str3 )
VALUES  ( 'opx', -- str1 - varchar(40)
          'rt', -- str2 - varchar(40)
          NULL  -- str3 - varchar(40)
          );

INSERT INTO dbo.SampleStr
        ( str1, str2, str3 )
VALUES  ( 'abc', -- str1 - varchar(40)
          'def', -- str2 - varchar(40)
          NULL  -- str3 - varchar(40)
          );

INSERT INTO dbo.SampleStr
        ( str1, str2, str3 )
VALUES  ( 'aaa', -- str1 - varchar(40)
          NULL, -- str2 - varchar(40)
          NULL  -- str3 - varchar(40)
          );

INSERT INTO dbo.SampleStr
        ( str1, str2, str3 )
VALUES  ( 'aaa', -- str1 - varchar(40)
          NULL, -- str2 - varchar(40)
          NULL  -- str3 - varchar(40)
          );

INSERT INTO dbo.SampleStr
        ( str1, str2, str3 )
VALUES  ( NULL, -- str1 - varchar(40)
          'xyz', -- str2 - varchar(40)
          NULL  -- str3 - varchar(40)
          );

INSERT INTO dbo.SampleStr
        ( str1, str2, str3 )
VALUES  ( '@!#$%', -- str1 - varchar(40)
          NULL, -- str2 - varchar(40)
          NULL  -- str3 - varchar(40)
          );

INSERT INTO dbo.SampleStr
        ( str1, str2, str3 )
VALUES  ( 'ABC', -- str1 - varchar(40)
          NULL, -- str2 - varchar(40)
          NULL  -- str3 - varchar(40)
          );

INSERT INTO dbo.SampleStr
        ( str1, str2, str3 )
VALUES  ( 'aBC', -- str1 - varchar(40)
          NULL, -- str2 - varchar(40)
          NULL  -- str3 - varchar(40)
          );

INSERT INTO dbo.SampleStr
        ( str1, str2, str3 )
VALUES  ( 'abc太郎', -- str1 - varchar(40)
          'abc', -- str2 - varchar(40)
          'ABC'  -- str3 - varchar(40)
          );

INSERT INTO dbo.SampleStr
        ( str1, str2, str3 )
VALUES  ( 'abcdefabc', -- str1 - varchar(40)
          'abc', -- str2 - varchar(40)
          'ABC'  -- str3 - varchar(40)
          );

INSERT INTO dbo.SampleStr
        ( str1, str2, str3 )
VALUES  ( 'micmic', -- str1 - varchar(40)
          'i', -- str2 - varchar(40)
          'T'  -- str3 - varchar(40)
          );

COMMIT;

初始化数据

（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 - 字符串的部分一致查询

【备注】= 运算符：字符串完全一致。

CREATE TABLE SampleLike
(
strcool VARCHAR(6) NOT NULL,
PRIMARY KEY(strcool)
)

BEGIN TRAN;
INSERT INTO dbo.SampleLike
        ( strcool )
VALUES  ( 'abcddd'  -- strcool - varchar(6)
          );

INSERT INTO dbo.SampleLike
        ( strcool )
VALUES  ( 'dddabc'  -- strcool - varchar(6)
          );

INSERT INTO dbo.SampleLike
        ( strcool )
VALUES  ( 'abdddc'  -- strcool - varchar(6)
          );

INSERT INTO dbo.SampleLike
        ( strcool )
VALUES  ( 'ddabc'  -- strcool - varchar(6)
          )

INSERT INTO dbo.SampleLike
        ( strcool )
VALUES  ( 'abddc'  -- strcool - varchar(6)
          )

COMMIT;

初始化数据

%：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 谓词具有其它谓词没有的用法，它的参数可以是子查询。 

-- DDL：创建表
CREATE TABLE TenpoShohin
(tenpo_id  CHAR(4)       NOT NULL,
 tenpo_mei  VARCHAR(200) NOT NULL,
 shohin_id CHAR(4)       NOT NULL,
 suryo     INTEGER       NOT NULL,
 PRIMARY KEY (tenpo_id, shohin_id));

-- DML：插入数据

INSERT INTO TenpoShohin (tenpo_id, tenpo_mei, shohin_id, suryo) VALUES ('000A',    '东京',        '0001',    30);
INSERT INTO TenpoShohin (tenpo_id, tenpo_mei, shohin_id, suryo) VALUES ('000A',    '东京',        '0002',    50);
INSERT INTO TenpoShohin (tenpo_id, tenpo_mei, shohin_id, suryo) VALUES ('000A',    '东京',        '0003',    15);
INSERT INTO TenpoShohin (tenpo_id, tenpo_mei, shohin_id, suryo) VALUES ('000B',    '名古屋',    '0002',    30);
INSERT INTO TenpoShohin (tenpo_id, tenpo_mei, shohin_id, suryo) VALUES ('000B',    '名古屋',    '0003',    120);
INSERT INTO TenpoShohin (tenpo_id, tenpo_mei, shohin_id, suryo) VALUES ('000B',    '名古屋',    '0004',    20);
INSERT INTO TenpoShohin (tenpo_id, tenpo_mei, shohin_id, suryo) VALUES ('000B',    '名古屋',    '0006',    10);
INSERT INTO TenpoShohin (tenpo_id, tenpo_mei, shohin_id, suryo) VALUES ('000B',    '名古屋',    '0007',    40);
INSERT INTO TenpoShohin (tenpo_id, tenpo_mei, shohin_id, suryo) VALUES ('000C',    '大阪',        '0003',    20);
INSERT INTO TenpoShohin (tenpo_id, tenpo_mei, shohin_id, suryo) VALUES ('000C',    '大阪',        '0004',    50);
INSERT INTO TenpoShohin (tenpo_id, tenpo_mei, shohin_id, suryo) VALUES ('000C',    '大阪',        '0006',    90);
INSERT INTO TenpoShohin (tenpo_id, tenpo_mei, shohin_id, suryo) VALUES ('000C',    '大阪',        '0007',    70);
INSERT INTO TenpoShohin (tenpo_id, tenpo_mei, shohin_id, suryo) VALUES ('000D',    '福冈',        '0001',    100);

测试数据

图

　　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 执行结果正确。

《SQL 基础知识梳理》系列

《SQL 基础知识梳理（一） - 数据库与 SQL》

《SQL 基础知识梳理（二） - 查询基础》

《SQL 基础知识梳理（三） - 聚合和排序》

《SQL 基础知识梳理（四） - 数据更新》

《SQL 基础知识梳理（五） - 复杂查询》

《SQL 基础知识梳理（六）- 函数、谓词、CASE 表达式》

《SQL 基础知识梳理（七）- 集合运算》

【博主】反骨仔

【原文】http://www.cnblogs.com/liqingwen/p/6572284.html

【参考】《SQL ゼロからはじめるデータベース操作》