第6章 函数、谓词、CASE表达式
6-1 各种各样的函数
学习重点:
- 根据用途,函数可以大致分为算术函数、字符串函数、日期函数、转换函数和聚合函数。
- 函数的种类很多,无需全都记住,只需要记住具有代表性的函数就可以了,其他的可以在使用时再进行查询。
函数的种类
所谓函数,就是输入某一值得到相应输出结果的功能,输入值称为参数(parameter),输出值称为返回值。
函数大致可以分为以下几种:
- 算术函数(用来进行数值计算的函数)
- 字符串函数(用来进行字符串操作的函数)
- 日期函数(用来进行日期操作的函数)
- 转换函数(用来转换数据类型和值的函数)
- 聚合函数(用来进行数据聚合的函数)
算术函数
- +(加法)
- -(减法)
- *(乘法)
- /(除法)
- ABS-绝对值,语法
ABS(数值)
- MOD-求余,语法
MOD(被除数,除数)
- ROUND-四舍五入,语法
ROUND(对象数值,保留小数的位数)
字符串函数
? CONCAT——拼接
CONCAT(字符串1,字符串2)
? LENGTH——字符串长度
LENGTH(字符串)
? LOWER——小写转换,UPPER——大写转换函数
LOWER(字符串)
? REPLACE——字符串的替换
REPLACE(对象字符串,替换前的字符串,替换后的字符串)
? SUBSTRING——字符串的截取
SUBSTRING(对象字符串 FROM 截取的起始位置 FOR 截取的字符数)
截取出字符串中第3位和第4位的字符:
日期函数
? CURRENT_DATE——当前日期
CURRENT_DATE
? CURRENT_TIME——当前时间
CURRENT_TIME
? CURRENT_TIMESTAMP——当前日期和时间
CURRENT_TIMESTAMP
? EXTRACT——截取日期元素
EXTRACT(日期元素 FROM 日期)
转换函数
? CAST——类型转换
CAST(转换前的值 AS 想要转换的数据类型)
将字符串类型转换为数值类型:
将字符串类型转换为日期类型:
? COALESCE——将NULL转换为其他值
COALESCE(数据1,数据2,数据3.....)
COALESCE是SQL特有的函数。该函数会返回可变参数中左侧开始第1个不是NULL的值。参数个数是可变的,因此可以根据需要无限增加。
6-2 谓词
学习重点:
- 谓词就是返回值为真值(TRUE/FALSE/UNKNOWN)的函数。
- 掌握LIKE的三种使用方法(前方一致、中间一致、后方一致)。
- 掌握注意BETWEEN包含三个参数。
- 想要取得NULL数据时必须使用IS NULL。
- 可以将子查询作为IN和EXISTS的参数。
LIKE谓词——字符串的部分一致查询
想从该表中读取出包含字符串“ddd”的记录时,可能会得到前方一致、中间一致和后方一致等不同的结果。
- 前方一致:选取出“dddabc”
- 中间一致:选取出“abcddd",“dddabc”,“abdddc”
- 后方一致:选取出”abcddd"
?前方一致查询
%是代表“0字符以上的任意字符串”的特殊符号,本例中代表”以ddd开头的所有字符串“。
?中间一致查询
?后方一致查询
_(下划线)代表了任意一个字符
查询”abc+任意2个字符”的字符串:
查询“abc+任意3个字符”的字符串:
BETWEEN谓词——范围查询
BETWEEN的特点就是结果中会包含100到1000这两个临界值。如果不想包含临界值,那就必须使用<和>。
IS NULL,IS NOT NULL——判断是否为NULL
选取进货单价为NULL的商品:
选取进货单价不为NULL的商品:
IN谓词——OR的简便用法
通过OR指定多个进货单价进行查询:
通过IN来指定多个进货单价进行查询
使用NOT IN进行查询时指定多个排除的进货单价进行查询:
但是需要注意的是,在使用IN和NOT IN时是无法选取出NULL数据的。实际结果也是如此,上述两组结果中都不包含进货单价为NULL的叉子和圆珠笔。NULL终究还是需要使用IS NULL和IS NOT NULL来进行判断。
使用子查询作为IN谓词的参数
■ IN和子查询
ShopProduct(商店商品)表:
使用子查询作为IN的参数:
子查询展开后的结果:
■ NOT IN 和子查询
EXIST谓词
使用EXIST选取出“大阪店在售商品的销售单价”
☝ 通常指定关联子查询作为EXIST的参数
可能大家会觉得子查询中的SELECT *稍微有些不同,就像我们之前学到的那样,由于EXIST只关心记录是否存在,因此返回哪些列都没有关系。EXIST只会判断是否满足子查询中WHERE子句指定的条件,只有存在的记录时才返回真。
这样的写法也能得到相同的结果:
☝ 作为EXIST参数的子查询经常会使用SELECT *。
⚪ 使用NOT EXIST替换NOT IN
使用NOT EXIST 读取出“东京店在售之外的商品的销售单价”
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 WHEN <求值表达式> THEN <表达式>
WHEN <求职表达式> THEN <表达式>
WHEN <求职表达式> THEN <表达式>
...
ELSE <表达式>
END
通过CASE表达式将A~C的字符串加入到商品种类当中
■ 使用CASE表达式进行行列转换