MySQL学习笔记五：函数、谓词和case表达式

算术函数

先创建一个包含numeric数据类型的表格，在此基础上进行算术函数运算：

SET SQL_SAFE_UPDATES=0;-- 关闭安全模式
create table samplemath
(m numeric (10,3),
-- numeric（全体位数，小数位数），全体位数包括小数点左边和右边位数的总和
 n integer,
 p integer);
#数据插入语句略
SELECT * FROM samplemath;

abs(m)求绝对值；mod(n,p)或n%p求n÷p的余数；round(对象,保留位数）对数据对象保留规定小数位：

select m,abs(m) from samplemath;-- 求绝对值，绝大多数函数对于null都返回null
select n,p,mod(n,p) from samplemath;-- n÷p的余数，n%p也行
select m,round(m,1) as round_col from samplemath;

字符串函数

创建另一个全是字符串的表格SampleStr：

select * from SampleStr;

字符拼接concat(str1,str2,…)；字符串长度计算length(str)，注意在UTF-8编码中1个汉字占3个字节，中文编码gbk、gb2312中1个汉字占2个字节；大小写转换用upper和lower函数；字符串替换用replace(对象str,替换前的str,替换后的str)；字符串截取用substring(str,from 起始位置 for 截取的字符数)：

-- 字符拼接：concat函数，比如第concatstr列第1行为'opxrt'：
select str1,str2,concat(str1,str2) as concatstr from SampleStr;

-- 字符串长度计算：length函数，得出'山田'的长度为4，此时是gbk编码：
select str1,length(str1) as lenstr1 from SampleStr;
-- 下列语句可修改编码方式：
alter table SampleStr CONVERT TO CHARACTER SET utf8;
-- 现在1个汉字就占了3个字节：
select str1,length(str1) as lenstr2 from SampleStr where str2='abc';

-- 大小写转换：upper、lower
select str1,upper(str1) as up,lower(str1) as low from SampleStr where 
str1 in ('ABC','abc','aBC','山田');-- in和or一个意思

-- 字符串替换：replace函数
select str1,str2,str3,replace(str1,str2,str3) as repstr from SampleStr;
-- 比如str1中的‘abc太郎’含有str2中的‘abc’，那么‘abc’就换成str3中的‘ABC’

-- 字符串截取：substring或者substr函数
select str1,substring(str1 from 3 for 2) as sub_str from SampleStr;
-- 上述语句截取了第2位和第3位，注意1个汉字也算1个字符

日期函数

-- 当前日期和时间
select current_date;-- 返回年-月-日
select current_time;-- 返回时:分:秒
select current_timestamp;-- 返回年-月-日 时:分:秒
-- 截取日期元素 
select current_timestamp,
extract(year from current_timestamp()) as Y,
extract(month from current_timestamp()) as M,
extract(day from current_timestamp()) as D,
extract(hour from current_timestamp()) as h,
extract(minute from current_timestamp()) as m,
extract(second from current_timestamp()) as s;

转换函数

数据类型转换函数：cast(转换前的值 as 转换后的数据类型)；将null转换为其他值：coalesce(数据1,数据2,…)，返回第一个不为null的数据：

-- 数据类型转换：cast函数
select cast('0007' as signed int) as int_col;-- 字符串转化为带符号整数
select cast('2017-09-09' as date) as date_col;

-- 将null转换为其他值：coalesce函数
select coalesce(null,1) as col1,coalesce(null,'test',null) as col1,
coalesce(null,null,'哈哈') as col1;
-- 上述语句返回可变参数中第一个不为null的值:1,'test','哈哈'
select coalesce(str2,'null') from SampleStr;-- 将null转换为字符串'null'

谓词

谓词就是返回值为真值的函数，作用是判断是否存在满足某种条件的记录。首先创建一个全是abcd字符的表格SampleLike，记录如图：

CREATE TABLE SampleLike
(strcol VARCHAR(6) NOT NULL,
 PRIMARY KEY (strcol));
SELECT * FROM samplelike;

模糊查询：like谓词，有前方一致、中间一致和后方一致三种类型；范围查询用between谓词，即 < column> between A and B，注意是闭区间；in谓词是or的简单用法，但是不能筛选出null记录，得用is null或者is not null语句：

#like谓词，字符串的部分一致查询 
select * from SampleLike where strcol like 'ddd%';-- 前方一致，返回dddabc
select * from SampleLike where strcol like '%ddd%';-- 中间一致，返回3条记录
select * from SampleLike where strcol like '%ddd' ;-- 后方一致，返回abcddd
select * from SampleLike where strcol like 'abc__' ;
-- 后方一致，后面是两个下划线，返回abcdd
select * from SampleLike where strcol like '_bd__' ;-- 中间一致，返回abddc

#between谓词，范围查询 
select product_name,sale_price from product where sale_price between 100 and 1000;
-- 上述语句包含临界值
select product_name,sale_price from product where sale_price between 100.01 and 999.99;
-- 或者用大于、小于符号

#in谓词
select product_name,purchase_price from product where purchase_price in (320,500,2800);
-- 上述语句不返回null数据，得用is null或者is not null来判断：
select product_name,purchase_price from product where purchase_price is not null;

使用子查询作为in谓词的参数。创建另一个表格ShopProduct，包含了各种商品和售卖商品商店的信息：

CREATE TABLE ShopProduct
(shop_id    CHAR(4)       NOT NULL,
 shop_name  VARCHAR(200)  NOT NULL,
 product_id CHAR(4)       NOT NULL,
 quantity   INTEGER       NOT NULL,
 PRIMARY KEY (shop_id, product_id));#两列为主键才能区分每一条记录
SELECT * FROM ShopProduct;

比如想找出大阪商店售卖的商品及其售价，得从product表格中获得售价数据：

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 not in
(select product_id from ShopProduct where shop_id='000A');
-- not加在第一层where后也可

exist谓词：参数是关联子查询（子查询中使用select *语句是惯例），但是一般都用in谓词代替exist谓词，因为in更好理解。比如上述两个事务也可以由以下两个语句实现：

select product_name,sale_price from product as p1 where exists
(select * from ShopProduct as p2 where shop_id='000C' and 
p1.product_id=p2.product_id);
select product_name,sale_price from product as p1 where not exists
(select * from ShopProduct as p2 where shop_id='000A' and 
p1.product_id=p2.product_id);

case表达式

主要是搜索型case表达式，类似于其他编程语句中的if…else…分支语句。结构为case (when…then…) (when…then…) else… end，注意else默认不写时就是else null，end一定不能省略。比如实现字母与商品名称的组合：

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_type from product;

按列输出各类商品售价之和：

select sum(case when product_type='衣服' then sale_price else 0 end) as 'clothes',
sum(case when product_type='办公用品' then sale_price else 0 end) as 'office',
sum(case when product_type='厨房用具' then sale_price else 0 end) as 'kitchen' from product;