SQL sever 基础学习6（函数，谓词，case表达式）

一. 函数

1.算术函数

函数	符号
加法	+
减法	-
乘法	*
除法	/
绝对值	ABS()
求余(SQL sever 不支持)	MOD()
四舍五入	ROUND()

2.字符串函数

拼接函数

字符串1||字符串2
--例
select string1, string2, string1||string2 as str_concat
    from sampleStr;
--SQL sever 中拼接函数为'+'
select str1, str2, str3, str1 + str2 + str3 as str_concat
    from sampleStr;

字符串长度函数

--LENGTH()
select str1, length(str1) as len_str
    from sampleStr;
--SQL sever 写法
select str1, len(str1) as len_str
    from sampleStr;

小写转换

--LOW(字符串)
--将大写转换为小写
select str1, lower(str1) as low_str
    from sampleStr
    where str1 in ('ABC', 'aBC', 'abc', '小日本');

字符串转换

--REPLCAE(对象字符串， 替换前字符串， 替换后字符串)
select str1, str2, str3, replace(str1, str2, str3) as rep_str
    from sampleStr;

字符串截取

-- substring(对象字符串 from 截取的起始位置 for 截取的字符数)
select str1, substring(str1 from 3 for 2) as sub_str
    from sampletr;
/*SQL sever 写法：
substring(对象字符串，截取字符串，街区的字符数)*/
--例
select str1, substring(str1,3,2) as sub_str
    from sampleStr;

大写转换

--upper(字符串)
select str1, upper(str1) as up_str
    from sampleStr
    where str in ('ABC', 'aBC', 'abc', '小日本')；

3.日期函数

-- current_date 当前日期
select current_date;
--SQL sever 写法
select cast(current_timestamp as date) as current_date1;

--current_time 当前时间
select current_time;
--SQL sever 写法
select cast(current_timestamp as time) as cur_time;


--current_timestamp 当前日期和时间
select current_timestamp;


-- extract(日期元素 from 日期) 截取日期元素
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;
--SQl sever 写法
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;

4.转换函数

类型转换 CAST

--cast (转换前的值 as 想要转换的数据类型)
--SQL sever 例
select cast('0001' as integer) as int_col;
select cast('2009-12-09' as date) as date_col;

将NULL值转换为其他值 COALESCE

--coalesce (数据1，数据2，数据3)
/*
该函数会返回可变参数中左侧开始第一个不是null的值
*/
select coalesce (null, 1) as col_1,
    coalesce (null,'test',null) as col_2,
    coalesce (null, null, '2009-12-09') as col_3;

二. 谓词

谓词可以说时函数的一种，是需要满足特定条件的函数，该条件就是返回值必须是真值

1.LIKE谓词

--前方一直查询
select * 
    from sampleLike 
    where strcol like '小日子过得%';
--中间一直查询
select * 
    from sampleLike
    where strcol like '%日子过得真%';
--后方一直查询
select *
    from sampleLike
    where strcol like '%过的正不错';

此外可以用'_'代替'%'，'_'表示任意一个字符，而'%'表示任意个字符

select *
    from sampleLike
    where strcol Like '小日子过得不多的___';
select *
    from sampleLike
    where strcol Like '小日子_得不_的日本_';

2.BETWEEN谓词——范围查询

--例
select product_name, sale_price
    from product
    where sale_price between 100 and 1000;
/*
上述范围会包含100与1000，也就是范围>=与<=;
*/

--例
select product_name, sale_price
    from product
    where sale_price > 100 and sale_price <1000;
/*
上述范围不包括100与1000，也即范围为>与<
*/

3.IS NULL与IS NOT NULL——判断是否为NULL

select product_name, purchase_price
    from product
    where purchase_price IS NULL;
select product_name, purchase_price
    from product
    where purchase_price IS NOT NULL;

4.IN谓词——OR的简便方法

--例
select product_name, purchase_price
    from product
    where purchase_price = 320
        or purchase_price =500
        or purchase_price =1000;
--等同写法
select product_name, purchase_price
    from product
    where purchase_price in (320, 500, 1000);
--否定写法
select product_name, purchase_price
    from product
    where purchase_price not in (320, 500, 1000);

5.使用子查询作为IN的参数

select product_name, sale_price
    from product
    where prodict_id in (select prodict_id 
                            from product
                            where shop_id = '000c');

6.EXIST谓词

判断是否存在满足某种条件的记录，如果存在则返回true，否则返回false

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);

--NOT EXISTS
select product_name, sale_price
    from product as p
    where  not exists (select *
                            from shopProduct as sp
                            where sp.shop_id = '000c' and sp.product_id = p.product_id);

三.CASE表达式

case when <求值表达式> then <表达式>
    when <求值表达式> then <表达式>
    when <求值表达式> then <表达式>
    .    
    .    
    .
    else <表达式>
end
--例
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;