cast函数_SQL基础：第6章函数、谓词、CASE表达式

最新推荐文章于 2021-02-02 00:06:41 发布

weixin_39835925

最新推荐文章于 2021-02-02 00:06:41 发布

阅读量200

点赞数

文章标签： cast函数 coalesce函数 coalesce函数用法 sql exists sql用法 mysql除法函数 select函数返回值

回到目录

SQL函数的种类

SQL中，函数种类大致分为：

算术函数（计算数值）
聚合函数（聚合数据）
字符串函数（操作字符串）
日期函数（操作日期）
转换函数（转换数据类型）

算术函数

下面介绍一些常见的算术函数。

ABS(数值)

用于计算绝对值（absolute value）。函数的参数既可以是整数也可以是浮点数。参数为null时，返回的结果也为null。

MOD(被除数, 除数)

用于取余。参数只能为整数，因为小数除法并没有余数的概念。参数只要含有null，则结果为null。

ROUND(数值, 保留小数的位数)

对数值进行四舍五入。同样，参数只要含有null，则结果为null。

字符串函数

用于字符串拼接，如字符串1 || 字符串2，结果是一个将字符串2拼接在字符串1后面的新字符串。不仅可以拼接两个字符串，三个及以上也可以用||进行拼接。同样，进行拼接时，若参数含有null，则拼接结果为null。

此外，对于MySQL，字符串拼接使用的是CONCAT(字符串1, 字符串2)函数。

LENGTH(字符串)

用于计算字符串含有的字符个数。参数为null时，结果也为null。

LOWER(字符串)

将字符串中的英文转换为小写。显然，该函数只对大写英文字母起作用，对非大写英文字母的字符没有任何影响。与之相反的是UPPER()函数。

UPPER(字符串)

将字符串中的英文转换为大写。

REPLACE(对象字符串, 被替换的字符串, 用于替换的字符串)

用于字符串的替换，即将字符串的一部分替换为其他字符串。例如，REPLACE(str1, str2, str3);就是将字符串str1中的str2替换为str3。

SUBSTRING(字符串 FROM 截取的起始位置 FOR 截取的长度)

用于截取字符串中的子字符串。该函数需要三个参数：对象字符串，子字符串的起始位置，子字符串的长度。参数之间用FROM和FOR隔开。特别需要注意的是，子字符串的起始位置从1开始计，而不是0。

日期函数

CURRENT_DATE

用于返回执行语句的日期，以年-月-日的形式。如2020-03-17。使用该函数不需要传入参数，因此也不需要小括号。

CURRENT_TIME

用于返回执行语句的时间，以时:分:秒的形式。如22:57:15.878426+08。同样，使用该函数不需要参数不需要括号。

CURRENT_TIMESTAMP

用于返回执行语句的日期和时间，以年-月-日时:分:秒的形式。如2020-03-17 23:02:36.071277+08，即具备了上面两个函数的功能。

EXTRACT(日期或时间单位 FROM 日期或时间)

用于截取日期或时间数据中的一部分，例如从年-月-日时:分:秒中截取年或者小时或者秒等。

用法：SELECT EXTRACT(YEAR FROM CURRENT_TIMESTAMP);。特别要注意返回值是数值类型而不是日期类型，如上述语句返回值为2020，为数值类型。

转换函数

在SQL中，转换函数不仅可以转换数据类型，还可以转换某些值。

CAST(待转换的值 AS 目标数据类型)

之所以要进行数据类型的转换，是为了防止数据类型不一致的错误，例如向表中插入了数据类型不匹配的数据。例如，将字符串类型转换为数值类型：CAST('0001' AS INTEGER)；将字符串类型转换为日期类型：CAST('2009-12-14' AS DATE)。

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

该函数会返回参数中从左数第一个不是null的值，函数中参数的个数不限。

该转换函数可以怎么用呢？比如我们要选取一列数据，类型为字符串，但这一列中可能含有null，我们想把这些null转换为字符串"NULL"，该函数就派上用场了：SELECT COALESCE(col, "NULL") FROM Test;。

谓词

谓词（predicate）其实也是函数的一种，且该函数返回值均为真值（TRUE/FALSE）。接下来介绍一些常用的谓词。

LIKE

该谓词用于匹配字符串，匹配的方式有前缀匹配、子字符串匹配、后缀匹配三种。

前缀匹配

即目标字符串的前缀与模板字符串相同。以如下语句为例：

SELECT * FROM Sample WHERE str LIKE 'ddd%';

该语句对每一行记录做判断，如果该行str列中的字符串前缀为ddd，则将这一行的所有列选取出来。比如像dddabc这样的字符串就会被like匹配成功。

%是指0个或多个字符的任意字符串。

子字符串匹配

即只要目标字符串含有模板字符串即可，相当于目标字符串中的子字符串与模板字符串相同，且这个子字符串出现的位置不重要。以如下语句为例：

SELECT * FROM Sample WHERE str LIKE '%ddd%';

类似的，通过上述语句，像abcddd、dddabc、abcdddabc这样的字符串都会被like匹配成功。其中一前一后两个%可以理解为只要字符串中含有模板字符串即可。

后缀匹配

即目标字符串的后缀与模板字符串相同。以如下语句为例：

SELECT * FROM Sample WHERE str LIKE '%ddd';

有了上面两个例子，这行语句就很好理解了。即若字符串以ddd来结尾，都会被匹配成功，比如abcddd。

上面提到了像ddd%中的%代表在ddd后面的位置有0个或多个字符的任意字符串，如果此处我们需要表示字符个数精确的任意字符串呢？例如ddd后面的位置有3个字符的字符串该如何表示呢？

用_（下划线）代替%，下划线表示的是1个字符的任意字符串。即将ddd%改为ddd+三个下划线，即可实现上述要求。

BETWEEN

该谓词用于范围查询，且该范围包含临界值，相当于一个闭区间。

-- 例如
SELECT product_name, sale_price
FROM Product
WHERE sale_price BETWEEN 100 AND 1000;
-- 相当于WHERE sale_price >= 100 AND sale_price <= 1000;

综上，若不想让范围包括临界值，则不能使用between，要使用>（大于）和<（小于）。

IS NULL、IS NOT NULL

前面的章节多次强调过，不能对null使用比较运算符，那么如何选取出null的数据呢？使用is null或is not null。

-- 例如
SELECT product_name, sale_price
FROM Product
WHERE sale_price IS NULL;
-- 从Product表中将所有售价为null的行的名字列和售价列选取出来。

is not null也是同样的用法，只不过选取的是null以外的数据。

IN

IN谓词用于枚举多个值，类似于多个OR。

-- 例如
SELECT product_name, sale_price
FROM Product
WHERE sale_price = 100
   OR sale_price = 200
   OR sale_price = 300;
-- 将售价为100、200、300的记录的名字列和售价列选取出来，用两个or枚举了三个值
-- 这种情况可用谓词in
SELECT product_name, sale_price
FROM Product
WHERE sale_price IN (100, 200, 300);

此外，in谓词的参数还可以使用子查询。上一章提到了，子查询像一次性视图，即将内层本用于定义视图的select语句直接用于外层的from子句中。为什么可以使用子查询，理解起来不难，由于in谓词的作用就是枚举括号中的多个参数值，那么如果子查询返回的是多个值，也同样可以作为参数被in枚举。

-- 例如
SELECT product_name, sale_price
FROM Product
WHERE product_id IN (SELECT product_id
                       FROM Shop
                      WHERE shop_id = 'A');
/*
   如果想在Product表中选取在A商店出售的商品的名称和售价，但Product表中没有商店的信息。
   但Shop表中有商店的信息，还有Product表中也有的商品编号的信息。
   以上是Shop表中仅有的信息。
   所以需要使用子查询在Shop表中选取A商店的商品的编号，作为in枚举的参数。
   这样就能从Product表中返回在A商店出售的商品的名称和售价了。
*/

同is null和is not null，in的否定形式为not in，使用方法相同。

注意，in和not in都无法选取出null。必须使用is null和is not null。

EXISTS

EXISTS的作用是：判断是否存在满足某种条件的记录，如果存在返回TRUE，否则返回FALSE。

现在我们知道该谓词的返回值为真值（TRUE/FALSE），而逻辑表达式的结果也是真值，因此该谓词可以作为逻辑表达式的结果。我们还知道，where子句后面应该跟逻辑表达式，也就是说exists可以用在where后。我们来试试：

-- 例如
SELECT product_name, sale_price
FROM Product AS P
WHERE EXISTS (SELECT * 
              FROM Shop AS S
              WHERE S.shop_id = 'A'
              AND S.product_id = P.product_id);

要理解上述语句，我们不妨忽略exists参数中的所有内容，先来看看该select语句的目的是什么。

首先从Product表中读取记录。如果exists参数中存在记录，返回true，那么Product表中的这条记录的名称和售价将被选取。这就是该select语句的目的。接下来看exists中的参数。

从Shop表中读取记录，如果Shop表中的这条记录商店id为A，并且这条记录与Product表中正在读取的记录商品编号相同，则将Shop的这条记录所有列选取出来，然后读取Shop的下一条记录。只要Shop的记录被选取出来，就意味着exists参数中的记录存在，返回true。这样Product中当前在读取的记录将被选取。

简而言之，每在Product表中读取一条记录，就在Shop表中读取所有记录，只要有和Product表中记录的商品编号相同，且在A商店销售的，exists将返回true，where后面语句的结果就为true，Product中的这条记录将被选取。

还需要知道，exists后面的参数通常是子查询，并且是关联子查询。exists只关心参数中是否存在记录，也就是该子查询能否选取出记录，至于选取哪些列不重要，只要存在记录，exists就会返回true。

not exists的作用则与exists相反，但用法相同。

CASE表达式

这是一种条件分支语句，类似于java语言中的switch分支语句。语法如下所示。

CASE WHEN 逻辑表达式 THEN 结果
     WHEN 逻辑表达式 THEN 结果
     .
     .
     .
     ELSE 结果
END

进入case的执行后，会先对第一个when中的逻辑表达式求值，若值为真，则返回其后then的结果，然后跳到end，case表达式结束；若第一个when中的逻辑表达式值为假，则从上至下顺序判断每个when中逻辑表达式的值，直至一个when中的逻辑值为真，返回其后then的结果，结束case；若所有when都不为真，则返回else后面的结果，case结束。

由此可见，case表达式最终一定会返回一个值。请看下面一个例子。

SELECT product_name, 
       CASE WHEN product_type = '衣服' THEN 'clothes'
            WHEN product_type = '办公用品' THEN 'office'
            WHEN product_type = '厨房用具' THEN 'kitchen'
            ELSE 'null'
       END
FROM Product;

不难理解，每读取一条记录，选取两列内容，一列为该记录的商品名称，另一列的内容视商品类型而定。

此外，ELSE子句是可以省略的，若省略，则默认为ELSE NULL。但END是不能省略的。

下面再举一个行列转换的例子，有助于加深对case表达式运用的理解。

select product_type, sum(sale_price)
from Product
group by product_type;

假设上述语句得到的结果为如下的表格：

现在计划进行一个行列的转换，使得到的结果为如下的表格：

请看case表达式的用法：

SELECT SUM(CASE WHEN product_type = '衣服'
                THEN sale_price
                ELSE 0
           END) AS "衣服",
       SUM(CASE WHEN product_type = '办公用品'
                THEN sale_price
                ELSE 0
           END) AS "办公用品",
       SUM(CASE WHEN product_type = '厨房用具'
                THEN sale_price
                ELSE 0
           END) AS "厨房用具"
FROM Product;