第6章 函数、谓词、CASE表达式-SQL基础教程

6-1　各种各样的函数

函数的种类

所谓函数，就是输入某一值得到相应输出结果的功能，输入值称为参数（parameter），输出值称为返回值
函数大致可以分为以下几种：
● 算术函数（+、-、*、/）
● 字符串函数（用来进行字符串操作的函数）
● 日期函数（用来进行日期操作的函数）
● 转换函数（用来转换数据类型和值的函数）
● 聚合函数（COUNT、SUM、AVG、MAX、MIN）

算术函数

-- 　创建SampleMath表
create table sample_math(
	m numeric(10,3),	-- numeric(全体位数,小数位数)
    n int,
    p int
);

insert into sample_math
values
(500,0,null),
(-180,0,null),
(null,null,null),
(null,7,3),
(null,5,2),
(null,4,null),
(8,null,3),
(2.27,1,null),
(5.555,2,null),
(null,1,null),
(8.76,null,null);

select *
from sample_math;

■ABS——绝对值
ABS(数值)

/*
ABS 函数的参数为 NULL 时，结果也是 NULL
绝大多数函数对于 NULL 都返回 NULL,转换函数中的COALESCE函数除外
*/
select m,abs(m)
from sample_math;

■MOD——求余
MOD(被除数，除数)

-- 小数计算中并没有余数的概念，所以只能对整数类型的列使用 MOD 函数
select n,p,mod(n,p)
from sample_math;

■ROUND——四舍五入
ROUND(对象数值，保留小数的位数)

select m,n,round(m,n)
from sample_math;

字符串函数

create table sample_str(
	str1 varchar(40),
    str2 varchar(40),
    str3 varchar(40)
);

insert into sample_str
values
('opx','rt',null),
('abc','def',null),
('山田','太郎','是我'),
('aaa',null,null),
(null,'xyz',null),
('@!#$%',null,null),
('ABC',null,null),
('aBC',null,null),
('abc太郎','abc','ABC'),
('abcdefabc','abc','ABC'),
('micmic','i','I');

select *
from sample_str;

■||——拼接
|| 函数在 SQL Server 和 MySQL 中无法使用：字符串1||字符串2

-- 进行字符串拼接时，如果其中包含 NULL，那么得到的结果也是 NULL
select str1,str2,str3,concat(str1,str2,str3)
from sample_str;

■LENGTH——字符串长度
LENGTH(字符串)

/*
一个汉字占多少长度与编码有关：
UTF－8：一个汉字＝3个字节
GBK：一个汉字＝2个字节
*/
select str1,length(str1)
from sample_str;

select str1,char_length(str1)	-- 计算字符串长度的自有函数CHAR_LENGTH
from sample_str;

■LOWER——小写转换
LOWER(字符串)

select str1,lower(str1) 
from sample_str;	-- UPPER 是大写转换函数

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

select str1,str2,str3,replace(str1,str2,str3)
from sample_str;

■SUBSTRING——字符串的截取
SUBSTRING（对象字符串 FROM 截取的起始位置 FOR 截取的字符数）

select str1,substr(str1 from 3 for 2)
from sample_str;

日期函数

■CURRENT_DATE——当前日期
CURRENT_DATE

select current_date();

■CURRENT_TIME——当前时间
■CURRENT_TIMESTAMP——当前日期和时间

■EXTRACT——截取日期元素
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;

转换函数

■CAST——类型转换
CAST（转换前的值 AS 想要转换的数据类型）

-- 将字符串类型转换为整数类型时，前面的“00”消失了，能够切实感到发生了转换
select cast('007' as signed int);

-- 将字符串转换为日期类型时，从结果上并不能看出数据发生了什么变化
select cast(curdate() as date);

■COALESCE——将NULL转换为其他值
COALESCE(数据1，数据2，数据3……)

-- COALESCE 函数(将NULL转换为其他值)会返回可变参数中左侧开始第1个不是 NULL 的值
select str2,coalesce(str2,0)
from sample_str;

6-2　谓词

什么是谓词

谓词就是返回值为**真值（TRUE/FALSE/UNKNOWN）**的函数：
● LIKE
● BETWEEN
● IS NULL、IS NOT NULL
● IN
● EXISTS

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

create table sample_like(
	str_col varchar(6) primary key 
);

insert into sample_like
values
('abcddd'),
('dddabc'),
('abdddc'),
('abcdd'),
('ddabc'),
('abddc');

select *
from sample_like;

--  %代表“0 字符以上的任意字符串”
select *
from sample_like
where str_col like '%ddd%'; 

-- _代表“任意 1 个字符”
select *
from sample_like
where str_col like 'abc__';

BETWEEN谓词——范围查询

select product_name,sale_price
from product
where sale_price between 100 and 1000;

/*
BETWEEN 的特点就是结果中会包含 100 和 1000 这两个临界值
如果不想让结果中包含临界值，那就必须使用 < 和 >
*/
select product_name,sale_price
from product
where sale_price > 100 and sale_price < 1000;

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

/*
为了选取出某些值为 NULL 的列的数据，不能使用 =，而只能使用特定的谓词 IS NULL
与此相反，想要选取 NULL 以外的数据时，需要使用 IS NOT NULL
*/
select product_name,purchase_price
from product
where purchase_price is null;

IN谓词——OR的简便用法

/*
IN(值,……)谓词——OR的简便用法,否定形式 NOT IN
在使用IN 和 NOT IN 时是无法选取出 NULL 数据的
*/
select product_name,purchase_price
from product
where purchase_price in(320,500,5000);

使用子查询作为IN谓词的参数

IN 谓词（NOT IN 谓词）具有其他谓词所没有的用法，那就是可以使用子查询作为其参数

create table shop_product(
	shop_id char(4),
    shop_name varchar(200) not null,
    product_id char(4),
    quantity int not null,
    primary key(shop_id,product_id)
);

insert into shop_product
values
('000A','东京','0001',30),
('000A','东京','0002',50),
('000A','东京','0003',15),
('000B','名古屋','0002',30),
('000B','名古屋','0003',120),
('000B','名古屋','0004',20),
('000B','名古屋','0006',10),
('000B','名古屋','0007',40),
('000C','大阪','0003',20),
('000C','大阪','0004',50),
('000C','大阪','0006',90),
('000C','大阪','0007',70),
('000D','福冈','0001',100);

select *
from shop_product;

select product_name,sale_price
from product
where product_id in(
	select product_id
	from shop_product
    where shop_id = '000C'
);

EXIST谓词

-- EXIST（存在）谓词的主语是“记录”,通常指定关联子查询作为EXIST的参数
select product_name,sale_price
from product p 
where exists(
	select *
	from shop_product sp
    where shop_id = '000C' and sp.product_id = p.product_id
);

6-3　CASE表达式

什么是CASE表达式

CASE 表达式是在区分情况时使用的，这种情况的区分在编程中通常称为（条件）分支

CASE表达式的语法

CASE WHEN <求值表达式> THEN <表达式>
WHEN <求值表达式> THEN <表达式>
WHEN <求值表达式> THEN <表达式>
. . .
ELSE <表达式>
END

CASE表达式的使用方法

-- 使用搜索CASE表达式的情况
select product_name,
	case when product_type = '衣服' then concat('A:',product_type)
		 when product_type = '办公用品' then concat('B:',product_type)
         when product_type = '厨房用具' then concat('C:',product_type)
         else null
	end as abc_product_type
from product;

-- 使用简单CASE表达式的情况
select product_name,
	case product_type
		 when '衣服' then concat('A:',product_type)
		 when '办公用品' then concat('B:',product_type)
         when '厨房用具' then concat('C:',product_type)
         else null
	end as abc_product_type
from product;

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