MySQL(五）——常用函数和case when

5.MySQL基础——常用函数

字符函数

①length(str)函数:字符串长度
②concat(str1,str2,...)函数:字符串连接
③upper(str)、lower(str)函数:转大写
④substr(str,start,len)函数:从指定位置截取指定个数的字符串
⑤instr(str,要查找的子串)函数:判断是否存在 返回 索引值 或者 0
⑥trim(str)函数: 去除两端空格
⑦lpad(str,len,填充字符)、rpad(str,len,填充字符)函数:左右两边指定个数填充.
⑧replace(str,子串,另一个字符串)函数     

##################### 字符函数 #############################
select length('hello');# 结果：5  查询传入字符串的的长度.
select length('格力');# 结果：6 如果是中文.又是utf8编码一个汉字占用3个字节

# 连接商品名称和商拼价格中间,号分隔.
select concat('hello','123','world');#hello123world
select concat(pname,',',price)from product;# "联想,6000"

# 把pid内容变大写.pname变小写
select upper(pid),lower(pname) from product;#P001 ,jack jones

# 把t_book 表中的书名截取前6个字符
select substr('hello',0,3);# 开始位置必须从1开始
select substr(bname,1,6) from book;# 包1包6 [1,6]

#判断商品名称是否包含 '花'
select instr(pname,'花') from product; # 存在返回1 ,不存在返回 0

#去掉空格.一般做数据清洗
select trim(pname) from product;

#指定两端填充
select lpad('aaa',4,'#');#: #aaa 左右两边指定字符填充.然后获取指定长度.
select rpad('aaa',4,'#');#: aaa#

# 替换
select replace('hello','ll','xx');#hexxo

数学函数

①round(x,保留位数)函数:四舍五入保留小数位.
②ceil(x)函数:天花板函数
③floor(x)函数:地板函数
④truncate(x,D)函数:按照D的值截取小数部分.
⑤mod(被除数,除数)函数:求余数
⑥pow(x,D)函数:x的D次方

select round(3.1415926,2);# 3.14
select round(3.1415926,3);# 3.142 四舍五入保留小数位.

select ceil(3.14);#4  向上取整

select floor(3.94);#3  向下取整

select truncate(3.1415,3);#3.141
select truncate(3.1415,4);#3.1415  不考虑四舍五入

select mod(10,3);#1
select mod(9,3);#0

select pow(2,2); #4
select pow(2,3); #8

时间与日期

①日期格式
②now()函数:当前日期和时间
③curdate()函数:当前日期
④curtime()函数:当前时间
⑤获取日期和时间中的年、月、日、时、分、秒
⑥weekofyear()函数:一年的第几周
⑦quarter()函数:一年中的季度
⑧str_to_date()函数:字符串转时间类型
⑨date_format()函数:格式化时间字符串
⑩date_add(日期,interval num 时间)函数:添加日期
⑪last_day()函数:月度最后一天
⑫datediff(end_date,start_date)函数:时间差
⑬timestampdiff(unit,start_date,end_date)函数计算两个时间返回的年/月/天数；
unix_timestamp(date):把日期转毫秒值
from_unixtime(int):把毫秒值转日期

#################  时间和日期 ######################
select now();#2024-07-19 15:55:43
select curdate();#2024-07-19
select curtime();#15:55:43
select year('24-11-11');#2024
select year(now());#2024# 从年月日时分秒中获取年.
select substr(now(),1,4);#2024 -- 探索函数实现原理
select substr(now(),6,2);#03
select month(now()) ;# 3

#查询今日是一年的第几周
select weekofyear(now());# 29周
#查询今日是一年的那个季度
select quarter(now());#3
select quarter('2024-06-09');#2

#字符串时间转日期类型
SELECT STR_TO_DATE('04/31/2004', '%m/%d/%Y');#结果：2004-04-30   str_to_date要求格式比较固定
select str_to_date('04-06-2024','%m-%d-%Y');#结果：2004-04-30

# date_format()函数  :格式化时间字符串
select now();#2024-07-19 15:55:43
select date_format(now(),'%Y/%m/%d %H:%i:%s');#2024/07/19 15:57:51
select date_format(now(),'%Y年%m月%d日 %H时%i分%s秒');#2024年07月19日 15时58分13秒

#查询明天的日期
select date_add(now(),interval 1 day );# 2024-07-20 15:58:43
#查询明年的日期
select date_add(now(),interval 1 year );# 2025-07-20 15:58:43
#查询去年的日期
select date_add(now(),interval -1 year );# 2023-07-20 15:58:43

#计算本月最后一天
select last_day('2024-02-01');# 参数为date
select last_day('2024-12-01');

# 计算到今年10月1还有几天
select datediff('2024-05-01',now());
select timestampdiff(day,now(),'2024-10-01');#73天
select timestampdiff(month ,now(),'2024-10-01');# 2
select timestampdiff(year ,now(),'2025-10-01');# 1

# unix_timestamp(date) : 把日期转毫秒值
select unix_timestamp(now());#1721376035
# from_unixtime(int): 把毫秒值转 日期
select from_unixtime(1721376035);#2024-07-19 16:00:35 日期格式是默认格式.
select from_unixtime(1721376035,'%Y年%m月%d日 %H时%i分%s秒');#2024年07月19日 16时00分35秒 日期格式是指定格式

流程操作

①if(expr,v1,v2)函数   : 判断数据给出返回值
②ifnull()函数         : 判断空给出返回值
③nullif(expr1,expr2)  : 相同返回null 不同保留表达式1的原值.
④case…when函数用法     : 多条件判断给出返回值.

##区间范围判断
'''
 Case
     When condition1 Then result1
     When condition2 Then result2
     ...
     Else result_n
 End
 '''
 
select
case
 when 50>=90 then '优秀'
 when 50>= 70 then '良好'
 when 50>= 60 then '及格'
 else '不及格'
end ;

# 使用case when来查询商品表.如果价格在1000以上是奢侈品.500-1000属于高端品.500以下就是平价商品.
select pname,price,
        case
        when price > 1000 then '奢侈品'
        when price >=500 and price <= 1000 then '高端品'
        when price < 500 then '平价商品'
        end as type
from product;

##固定值判断
'''
case 列名
     when 值 then 值
     when 值 then 值
     ....
     else 值 
end
'''

# 商品表.如果商品类别是c001显示电器,是c002显示服装,是c003显示化妆品.
select pname,price,
       case category_id
            when 'c001' then '电器'
            when 'c002' then '服装'
            when 'c003' then '化妆品'
            else '其它'
       end category_name
       from product;