case when then else_第6章 函数、谓词、CASE表达式及练习题
6-1 各种各样的函数
一、函数的种类
所谓函数，就是输入某一值得到相应输出结果的功能，输入值称为参数（parameter），输出值称为返回值。
● 算术函数（用来进行数值计算的函数）
●字符串函数（用来进行字符串操作的函数）
●日期函数（用来进行日期操作的函数）
●转换函数（用来转换数据类型和值的函数）
●聚合函数（用来进行数据聚合的函数）
二、算术函数
+（加法）/-（减法）/*（乘法）//（除法）
ABS绝对值函数：ABS(列名);
MOD函数：MOD(被除数，除数）；SQLServer中不支持MOD函数，用%来进行求余，被除数%除数即可。
ROUND函数：ROUND(对象数值，保留小数的位数）；保留小数的位数也可以用某一列的数值来代替。
三、字符串函数
拼接函数：SQLServer中使用的是+，str1+str2;有的用的是||。
字符串长度函数：SQLServer中使用的是len,len(字符串）；有的用的是length。
LOWER小写转换函数，lower（字符串）。
UPPER大写转换函数，upper(字符串）。
REPLACE字符串替换函数：REPLACE(对象字符串，替换前的字符串，替换后的字符串)，
例：str1:abcdefabc str2:abc str3:ABC。replace(str1,str2,str3)=ABCdefABC。
SQLServer中SUBSTRING字符串截取函数： SUBSTRING(对象字符串，截取的起始位置，截取的字符数)。
四、日期时间函数（SQLServer中）
CURRENT_STAMP函数，返回的是当前的年月日及时间。
利用CAST函数可以将其转换为日期类型或者时间类型：
select cast(current_timestamp as date)as cur_date;
select cast(current_timestamp as time)as cur_time;
截取时间日期元素DATEPART：
SELECT CURRENT_TIMESTAMP,
DATEPART(YEAR , CURRENT_TIMESTAMP) AS year,
DATEPART(MONTH , CURRENT_TIMESTAMP) AS month,
DATEPART(DAY , CURRENT_TIMESTAMP) AS day,
DATEPART(HOUR , CURRENT_TIMESTAMP) AS hour,
DATEPART(MINUTE , CURRENT_TIMESTAMP) AS minute,
DATEPART(SECOND , CURRENT_TIMESTAMP) AS second;
四、转换函数
一是数据类型的转换，一是值的转换。
CAST类型转换函数：CAST（转换前的值 AS 想要转换的数据类型) 例：select cast('0001' as integer) as int_col;得到的结果是1。以及上面的日期转换。
COALESCE将NULL转换为其他值：COALESCE(数据1，数据2，数据3……)。 COALESCE 是 SQL 特有的函数。该函数会返回可变参数 A 中左侧开始第 1个不是 NULL 的值。参数个数是可变的，因此可以根据需要无限增加。
6-2 谓词
一、什么是谓词
谓词的返回值全都是真值（TRUE/FALSE/UNKNOWN）。
● LIKE
● BETWEEN
● IS NULL、 IS NOT NULL
● IN
● EXISTS
二、LIKE谓词——字符串的部分一致查询
模糊查询
例：select * from product
where product_id like'%1%';
查询结果：%1%:0001的结果；%1：0001的结果；1%：没有记录。
三、BETWEEN谓词——范围查询
between包含两个临界值，between .....and ....
四、IS NULL、 IS NOT NULL——判断是否为NULL
判断的是否为空，不能用=，用IS NULL（IS NOT NULL）来进行判断。
五、IN谓词——OR的简便用法
in （值，值，。。。。）；not in (值，值，。。。）
在使用IN 和 NOT IN 时是无法选取出NULL 数据的。
六、使用子查询作为IN谓词的参数
①IN和子查询
例：select product_name,sale_price
from product
where product_id In(
select product_id from shopproduct
where shopid='000C');
②NOT IN和子查询
例：select product_name,sale_price
from product
where product_id NOT In(
select product_id from shopproduct
where shopid='000A');
七、EXIST谓词的使用方法
①通常指定关联子查询作为EXIST的参数。
②作为EXIST参数的子查询中经常会使用SELECT *。
例：select product_name,sale_price
from product as p
where exists (select * from shopproduct as sp
where sp.shopid='000A'
and p.product_id=sp.product_id);
③使用NOT EXIST替换NOT IN
6-3 CASE表达式
一、什么是CASE表达式
CASE 表达式是在区分情况时使用的，这种情况的区分在编程中通常称为（条件）分支 。
二、CASE表达式的语法
CASE表达式的语法分为简单CASE表达式和搜索CASE表达式两种。
搜索CASE表达式:
CASE WHEN <求值表达式> THEN <表达式>
WHEN <求值表达式> THEN <表达式>
WHEN <求值表达式> THEN <表达式>
.............
ELSE <表达式>
END
三、CASE表达式的使用方法
例：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_name
from product;
①虽然CASE表达式中的ELSE子句可以省略，但还是希望大家不要省略。省略时相当于else null。
②CASE表达式中的END不能省略。
③CASE表达式的书写位置，任何位置都可以，可以实现行列转换。
例：select sum(case when product_type='衣服' then sale_price else 0 end )as sum_pirce_clothes,
sum(case when product_type='办公用品' then sale_price else 0 end)as sum_price_office,
sum(case when product_type='厨房用具' then sale_price else 0 end)as sum_price_kitchen
from product;
练习题：
6.1
in和not in 不能返回Null值，使用的时候也不能加入NULL。
①返回除500，2800.5000以外的值；②返回的值为空。
6.2
select count(case when sale_price<=1000 then product_name else null end) as low_price,
count(case when sale_price>1001 and sale_price<=3000 then product_name else null end)as mid_price,
count(case when sale_price>3001 then product_name else null end) as high_price
from product;