SQL：六 函数、谓词、CASE表达式
各种各样的函数
所谓函数， 就是输入某一值得到相应输出结果的功能，输入值称为参数（parameter）， 输出值称为返回值。
函数的种类
-
算术函数（用来进行数值计算的函数）
-
ABS(数值)
- ABS 是计算绝对值的函数
- ABS 函数的参数为 NULL 时，结果也是 NULL。并非只有 ABS 函数如此，其实绝大多数函数对于 NULL 都返 回NULL
-
MOD(被除数，除数)
- MOD 是计算除法余数（求余）的函数，是 modulo 的缩写
- 只能对整数类型的列使用 MOD 函数
-
ROUND(对象数值，保留小数的位数)
- ROUND函数用来进行四舍五入操作
-
-- 计算数值的绝对值 SELECT m, ABS(m) AS abs_col FROM SampleMath; -- 计算除法（n ÷ p）的余数 SELECT n, p, MOD(n, p) AS mod_col FROM SampleMath; -- 对m列的数值进行n列位数的四舍五入处理 SELECT m, n, ROUND(m, n) AS round_col FROM SampleMath;
-
字符串函数（用来进行字符串操作的函数）
-
字符串1 || 字符串2
- 字符串拼接
- SQL Server用“+”运算符（函数）来连接字符串
- MySQL使用CONCAT函数 来完成字符串的拼接
-
LENGTH(字符串)
- 获取字符串长度
- SQL Server使用LEN函数来计算字符串的长度
-
LOWER(字符串)
- LOWER 函数只能针对英文字母使用，它将参数中的字符串全都转换为小写
- UPPER 大写转换函数
-
REPLACE(对象字符串，替换前的字符串，替换后的字符串)
- 用 REPLACE函数，可以将字符串的一部分替换为其他的字符串
-
SUBSTRING（对象字符串 FROM 截取的起始位置 FOR 截取的字符数）
- 使用 SUBSTRING函数可以截取出字符串中的一部分字符串
-
-
-- 拼接两个字符串（str1+str2） SELECT str1, str2, str1 || str2 AS str_concat FROM SampleStr; -- 计算字符串长度 SELECT str1, LENGTH(str1) AS len_str FROM SampleStr; -- 大写转换为小写 SELECT str1, LOWER(str1) AS low_str FROM SampleStr WHERE str1 IN ('ABC', 'aBC', 'abc'); -- 替换字符串的一部分 SELECT str1, str2, str3, REPLACE(str1, str2, str3) AS rep_str FROM SampleStr; -- 截取出字符串中第3位和第4位的字符 SELECT str1, SUBSTRING(str1 FROM 3 FOR 2) AS sub_str FROM SampleStr;
-
日期函数（用来进行日期操作的函数）
-
CURRENT_DATE
- 返回 SQL 执行的日期，也就是该函数执 行时的日期。由于没有参数，因此无需使用括号
-
CURRENT_TIME
- CURRENT_TIME函数能够取得SQL 执行的时间，也就是该函数执行时的时间
-
CURRENT_TIMESTAMP
- 使用该函数可以同时得到当前的日期和时间，当然也可以 从结果中截取日期或者时间
-
EXTRACT(日期元素 FROM 日期)
- 可以截取出日期数据中的一部分，例如YEAR、MONTH、DAY、HOUR、MINUTE、SECOND
- 该函数的返回值并不是日 期类型而是数值类型
-
-
-- 获得当前日期 SELECT CURRENT_DATE; -- 取得当前时间 SELECT CURRENT_TIME; -- 取得当前日期和时间 SELECT CURRENT_TIMESTAMP; -- 截取日期元素 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（转换前的值 AS 想要转换的数据类型）
- 类型转换其实不是为了方便用户使用而开发的功能，而是为了方便 DBMS内部处理而开发的功能
-
COALESCE(数据1，数据 2，数据 3……)
- 将NULL转换为其他值
- 该函数会返回可变参数 A 中左侧开 始第1个不是 NULL的值。参数个数是可变的，因此可以根据需要无限增加。
-
-
-- 将字符串类型转换为数值类型 -- SQL Server PostgreSQL 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; -- 将字符串类型转换为日期类型 SELECT CAST('2009-12-14' AS DATE) AS date_col; -- 将NULL转换为其他值 SELECT COALESCE(NULL, 1) AS col_1, COALESCE(NULL, 'test', NULL) AS col_2, COALESCE(NULL, NULL, '2009-11-01') AS col_3;
-
聚合函数（用来进行数据聚合的函数）
谓词
什么是谓词
- 谓词就是返回值为真值的函数
LIKE谓词
-
字符串的部分一致查询
-
部分一致大体可以分为三种类型
-
前方一致
- 所谓前方一致，就是选取出作为查询条件的字符串与查询对象字符串起始部分相同的记录的查询方法
-
中间一致
- 所谓中间一致，就是选取出查询对象字符串中含有作为查询条件的字 符串的记录的查询方法。无论该字符串出现在对象字 符串的最后还是中间都没有关系
-
后方一致
- 后方一致与前方一致相反，也就是选取出作为查询条件的字符串与查询对象字符串的末尾部分相同的记录的查询方法。
-
-
这样不使用“=”来指定条件字符串，而以字符串中是否包含该条件的规则为基础的查询称为模式匹配，其中的模式也就是前面提到的“规则”。
-
-- 使用LIKE进行前方一致查询 SELECT * FROM SampleLike WHERE strcol LIKE 'ddd%'; -- 使用LIKE进行中间一致查询 SELECT * FROM SampleLike WHERE strcol LIKE '%ddd%'; -- 使用LIKE进行后方一致查询 SELECT * FROM SampleLike WHERE strcol LIKE '%ddd'; -- 使用LIKE和_（下划线）进行后方一致查询 SELECT * FROM SampleLike WHERE strcol LIKE 'abc__'; -- 查询“abc+任意3个字符”的字符串 SELECT * FROM SampleLike WHERE strcol LIKE 'abc___';
BETWEEN谓词
- 使用 BETWEEN 可以进行范围查询
- 该谓词与其他谓词或者函数的不 同之处在于它使用了3个参数
- BETWEEN 的特点就是结果中会包含100 和 1000 这两个临界值
-
-- 选取销售单价为100～1000日元的商品 SELECT product_name, sale_price FROM Product WHERE sale_price BETWEEN 100 AND 1000;
IS NULL、IS NOT NULL
- 判断是否为NULL
- 为了选取出某些值为 NULL 的列的数据，不能使用 =，而只能使用特定的谓词 IS NULL
- 想要选取 NULL 以外的数据时，需要使用 IS NOT NULL
-
-- 选取出进货单价（purchase_price）为 NULL的商品 SELECT product_name, purchase_price FROM Product WHERE purchase_price IS NULL;
IN谓词
-
OR的简便用法
- IN 谓词“IN(值,……)”
-
否定形式 NOT IN
-
使用 IN 和NOT IN 时是无法选取出 NULL 数据
-
-- 通过OR指定多个进货单价进行查询 SELECT product_name, purchase_price FROM Product WHERE purchase_price = 320 OR purchase_price = 500 OR purchase_price = 5000; -- 通过IN来指定多个进货单价进行查询 SELECT product_name, purchase_price FROM Product WHERE purchase_price IN (320, 500, 5000); -- 使用NOT IN进行查询时指定多个排除的进货单价进行查询 SELECT product_name, purchase_price FROM Product WHERE purchase_price NOT IN (320, 500, 5000);
使用子查询作为IN谓词的参数
-
IN和子查询
- IN 谓词（NOT IN 谓词）具有其他谓词所没有的用法，那就是可以 使用子查询作为其参数
-
NOT IN和子查询
- IN 的否定形式 NOT IN 同样可以使用子查询作为参数，其语法也和 IN 完全一样
-- 使用子查询作为IN的参数 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');
EXIST谓词
-
EXIST谓词的使用方法
-
通常指定关联子查询作为EXIST的参数
-
子查询中的SELECT *
- 由于 EXIST 只关心记录是否存在，因此返回哪些列都没有关系
- 可以把在EXIST 的子查询中书写SELECT * 当作SQL 的一 种习惯
-
使用NOT EXIST替换NOT IN
- NOT EXIST 与 EXIST 相反，当“不存在”满足子查询中指定条件的记录时返回真（TRUE）
-
-- 使用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);
CASE表达式
什么是CASE表达式
- 是一种进行运算的功能
- CASE 表达式是在区分情况时使用的，这种情况的区分在编程中通常 称为（条件）分支
CASE表达式的语法
-
简单CASE表达式
-
搜索CASE表达式
- 搜索CASE 表达式包含了简单CASE 表达式的全部功能
- CASE 表达式会从对最初的 WHEN 子句中的“< 求值表达式 >”进行 求值开始执
CASE WHEN <求值表达式> THEN <表达式> WHEN <求值表达式> THEN <表达式> WHEN <求值表达式> THEN <表达式> . . . ELSE <表达式> END -- 通过CASE表达式将A～C的字符串加入到商品种类当中 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表达式的使用方法
- CASE表达式中的ELSE子句可以省略
- CASE表达式中的END不能省略。
CASE表达式的书写位置
- CASE 表达式的便利之处就在于它是一个表达式
- 表达式可以书写在任意位置
-- 通常使用GROUP BY也无法实现行列转换 SELECT product_type, SUM(sale_price) AS sum_price FROM Product GROUP BY product_type; -- 使用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;
从现在开始，种下梦想中的参天大树
