SQL基础教程 Chapter 6——函数、谓词、CASE表达式

---

本章重点

• 不仅 SQL，对所有的编程语言来说，函数都起着至关重要的作用。函数就
  像是编程语言的“道具箱”，每种编程语言都准备了非常多的函数。使用函数，
  我们可以实现计算、字符串操作、日期计算等各种各样的运算。
• 本章将会和大家一起学习具有代表性的函数以及特殊版本的函数（谓词和
  CASE 表达式）的使用方法

---

6.1 各种各样的函数

• 根据用途，函数可以大致分为算术函数、字符串函数、日期函数、转换函
  数和聚合函数
• 函数的种类很多，无需全都记住，只需要记住具有代表性的函数就可以了，
  其他的可以在使用时再进行查询

---

• 函数的大致分类

  • 算术函数
  • 字符串函数
  • 日期函数
  • 转换函数
  • 聚合函数

• NUMERIC 函数时通过NUMBERIC 的形式来指定数值的大小

CREATE TABLE SampleMath
(m NUMERIC (10,3), #(全体位数，小数位数)
n INTEGER,
p INTEGER);

算术函数

• ABS——绝对值

• ABS函数的参数为NULL时，返回NULL

• MOD 函数，求余函数

MOD(被除数，除数)
SELECT n，p，MOD(n,p) as mod_col
from samplemath,

• ROUND函数，四舍五入

ROUND(对象数值，保留小数的位数)
SELECT m,n,round(m,n) as round_col
	from samplemath;

字符串函数

• 字符串函数，创建samplestr表

# ||函数(不用于MySQL)
字符串1||字符串2
SELECT str1,str2,str1 || str2 as str_concat
	from samplestr;
# MySQL 
SELECT str1, str2, str3,
CONCAT(str1, str2, str3) AS str_concat
FROM SampleStr;

• LENGTH——字符串长度

LENGTH(字符串)
SELECT str1,
LENGTH(str1) AS len_str
FROM SampleStr;

• LOWER——小写转换 同（UPPER）函数

# LOWER 函数
# 只能针对英文字母使用，并不影响原本就是小写字母的场合
SELECT str1,
LOWER(str1) AS low_str
FROM SampleStr
WHERE str1 IN ('ABC', 'aBC', 'abc', ' 山田 ');
# 注意 IN 这个用法

• REPLACE——字符串的替换

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

• SUBSTRING——字符串的截取

# 使用SUBSTRING 函数可以截取字符串中的一部分字符串
SELECT str1,
SUBSTRING(str1 FROM 3 FOR 2) AS sub_str
FROM SampleStr;

日期函数

• CURRENT_DATE函数，获得当前日期

SELECT CURRENT_DATE;

• CURRENT_TIME函数，获得当前的时间

SELECT CURRENT_TIME;

• CURRENT_TIMESTAMP——当前日期和时间

SELECT CURRENT_TIMESTAMP;

• EXTRACT——截取日期元素

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;

转换函数

• CAST——类型转换

# CAST(转换前的类型 AS 想要转换的数据类型)
SELECT CAST('0001' AS SIGNED INTEGER) AS int_col;
SELECT CAST('2009-12-14' AS DATE) AS date_col;

• COALESCE——将NULL转换为其他值

COALESCE( 数据 1 ，数据 2 ，数据 3 …… )
# 该函数会返回可变参数中左侧开始第一个不是NULL的值

6.2 谓词

• 谓词就是返回值为真值的函数
• 掌握 LIKE 的三种使用方法（前方一致、中间一致、后方一致）
• 需要注意 BETWEEN 包含三个参数
• 想要取得 NULL 数据时必须使用 IS NULL
• 可以将子查询作为 IN 和 EXISTS 的参数

---

• 谓词后返回值是逻辑值，总共三种，TRUE,FALSE,UNKNOW

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

# 先创建samplelike表
-- DDL：创建表
CREATE TABLE samplelike
(strcol varchar(6) not null，
PRIMARY KEY（strcol));

# 前方一致：选取出 "dddabc"
# 中间一致：选取出"abcddd""dddabc""abdddc"
# 后方一致：选取出"abcddd"

# 不用等号来指定条件字符串，而以字符串中是否包含该条件的规则基础的查询称为模式匹配

SELECT *
FROM samplelike
WHERE strcol LIKE 'ddd%';
# 前端一致，注意使用的的单引号

SELECT *
FROM samplelike
WHERE strcol LIKE '%ddd%';
# 中间一致

SELECT *
FROM SampleLike
WHERE strcol LIKE '%ddd';
# 后端一致

# 我们也可以用_(下划线)来代替"任意1个字符"
SELECT *
FROM SampleLike
WHERE strcol LIKE 'abc__';

• BETWEEN 谓词——范围查询

# 选取销售单价为 100～1000 日元的商品
SELECT product_name, sale_price
FROM Product
WHERE sale_price BETWEEN 100 AND 1000;
# 包含100和1000的两个临界值
# 如果不想包含，只能使用<、>号

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

SELECT product_name, purchase_price
FROM Product
WHERE purchase_price IS NULL;

• IN 谓词——OR 的简便用法

# 通过 IN 来指定多个进货单价进行查询
SELECT product_name, purchase_price
FROM Product
WHERE purchase_price IN (320, 500, 5000);
# NOT IN 也是可以使用
# 切记，IN 和 NOT IN 是无法选取出NULL数据的

# 使用子查询作为IN谓词的参数
# 能够将视图作为IN的参数
# 1． 从 ShopProduct 表中选取出在大阪店（ shop _ id = ' 000C ' ）中销售的商品（ product _ id ）
#2． 从 Product 表中选取出上一步得到的商品（ product _ id ）的销售单价（ sale _ price ）

SELECT product_name,sale_price
from product
where product_id in (
SELECT product_id
from shopproduct
where shop_id = '000C');
# 作为子查询来说，先选择第二层，找出对应的因素，在编写外层，以内层为参数
--  子查询展开后的结果
SELECT product_name, sale_price
FROM Product
WHERE product_id IN ('0003', '0004', '0006', '0007');

• NOT IN 和子查询

# 使用子查询作为 NOT IN 的参数
SELECT product_name, sale_price
FROM Product
WHERE product_id NOT IN (SELECT product_id
FROM ShopProduct
WHERE shop_id = '000A');

• EXIST 谓词
• EXIST 的使用方法与之前的都不相同
• 语法理解起来比较困难
• 即使不用EXIST，基本上也都可以使用IN 等谓词来代替

 # 使用 EXIST 选取出“大阪店在售商品的销售单价
 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 的参数

(SELECT *
FROM ShopProduct AS SP
WHERE SP.shop_id = '000C'
AND SP.product_id = P.product_id)

# 上面这样的子查询就是唯一的参数。确切地说，由于通过条件“ SP.
# product _ id = P.product _ id ”将 Product 表和 ShopProduct
# 表进行了联接，因此作为参数的是关联子查询。 EXIST 通常都会使用关
# 联子查询作为参数

# EXIST 只会判断是否存在满足子查询中 WHERE 子句指定的条
# 件“商店编号（ shop _ id ）为 '000C' ，商品（ Product ）表和商店
# 商品（ ShopProduct ）表中商品编号（ product _ id ）相同”的记录，
# 只有存在这样的记录时才返回真（ TRUE ）

6.3 CASE 表达式

• CASE 表达式分为简单 CASE 表达式和搜索 CASE 表达式两种。搜索
  CASE 表达式包含简单 CASE 表达式的全部功能
• 虽然 CASE 表达式中的 ELSE 子句可以省略，但为了让SQL语句更加容易
  理解，还是希望大家不要省略
• CASE 表达式中的 END 不能省略
• 使用 CASE 表达式能够将 SELECT 语句的结果进行组合
• 虽然有些DBMS提供了各自特有的 CASE 表达式的简化函数，例如Oracle
  中的 DECODE 和MySQL中的 IF ，等等，但由于它们并非通用的函数，功
  能上也有些限制，因此有些场合无法使用。

---

• 什么是CASE 表达式
  • 是一种进行运算的功能
  • CASE 表达式是在区分情况时使用的，通常称为条件分支

# 语法：
# 简单CASE 表达式
# 搜索CASE 表达式
CASE WHEN < 求值表达式 > THEN < 表达式 >
WHEN < 求值表达式 > THEN < 表达式 >
WHEN < 求值表达式 > THEN < 表达式 >
.
.
.
ELSE < 表达式 >
END

# CASE 表达式会从对最初的 WHEN 子句中的“< 求值表达式 >”进行
# 求值开始执行。所谓求值，就是要调查该表达式的真值是什么。如果结果
# 为真（ TRUE ），那么就返回 THEN 子句中的表达式， CASE 表达式的执行
# 到此为止。如果结果不为真，那么就跳转到下一条 WHEN 子句的求值之中。
# 如果直到最后的 WHEN 子句为止返回结果都不为真，那么就会返回 ELSE
# 中的表达式，执行终止。

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;

# 使用 CASE 表达式进行行列转换
--  对按照商品种类计算出的销售单价合计值进行行列转换
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;

--  使用搜索 CASE 表达式的情况（重写代码清单 6-41 ）
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;
--  使用简单 CASE 表达式的情况
SELECT product_name,
CASE product_type
WHEN ' 衣服 '  THEN 'A ： '  | | product_type
WHEN ' 办公用品 ' THEN 'B ： '  | | product_type
WHEN ' 厨房用具 ' THEN 'C ： '  | | product_type
ELSE NULL
END AS abc_product_type
FROM Product;

-- MySQL 中使用 IF 代替 CASE 表达式
SELECT product_name,
IF( IF( IF(product_type = ' 衣服 ',
CONCAT('A ： ', product_type), NULL)
IS NULL AND product_type = ' 办公用品 ',
CONCAT('B ： ', product_type),
IF(product_type = ' 衣服 ',
CONCAT('A ： ', product_type), NULL))
IS NULL AND product_type = ' 厨房用具 ',
CONCAT('C ： ', product_type),
IF( IF(product_type = ' 衣服 ',
CONCAT('A ： ', product_type), NULL)
IS NULL AND product_type = ' 办公用品 ',
CONCAT('B ： ', product_type),
IF(product_type = ' 衣服 ',
CONCAT('A ： ', product_type),
NULL))) AS abc_product_type
FROM Product;

APPENDIX

法则6.1 通常指定关联子查询作为 EXIST 的参数

法则6.2 作为 EXIST 参数的子查询中经常会使用 SELECT *

法则6.3 虽然 CASE 表达式中的 ELSE 子句可以省略，但还是希望大家不要省略

法则6.4 CASE 表达式中的 END 不能省略