一、字符函数
①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') ;# 查询传入字符串的的长度.
select length(pname) from product where pid = 'p001';#如果是中文.又是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 tb_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
#把霸改成霸王
select replace(pname,'霸','霸王') from product where pid = 'p007';
二、数学函数
①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);
select truncate(3.1415,4);# 不考虑四舍五入
select mod(10,3);#1
select mod(9,3);#0
select pow(2,2);
select pow(2,3);
#把书的价格调整为整数销售.
select ceil(bprice) from tb_book;
三、时间和日期
①日期格式
②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-03-27 16:09:49
select curdate();#2024-03-27
select curtime();#16:09:49
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());# 13周
#查询今日是一年的那个季度
select quarter(now());#1
select quarter('2024-06-09');#2
#字符串时间转日期类型
SELECT STR_TO_DATE('04/31/2004', '%m/%d/%Y');# str_to_date要求格式比较固定
select str_to_date('04-06-2024','%m-%d-%Y');
# date_format()函数 :格式化时间字符串
select now();#2024-03-27 16:28:12
select date_format(now(),'%Y/%m/%d %H:%i:%s');#2024/03/27 16:34:02
select date_format(now(),'%Y年%m月%d日 %H时%i分%s秒');#2024年03月27日 16时33分15秒
#查询明天的日期
select date_add(now(),interval 1 day );# ;2024-03-28 16:37:35
#查询明年的日期
select date_add(now(),interval 1 year );# ;2025-03-27 16:37:35
#查询去年的日期
select date_add(now(),interval -1 year );# ;2023-03-27 16:37:35
#计算本月最后一天
select last_day('2024-02-01');# 参数为date
select last_day('2024-12-01');
# datediff(end_date,start_date)函数 : 时间差
# 计算到今年5月1还有几天
select datediff('2024-05-01',now());
select timestampdiff(day,now(),'2024-05-01');#34天
select timestampdiff(month ,now(),'2024-06-01');# 1
select timestampdiff(year ,now(),'2025-06-01');# 1
# unix_timestamp(date) : 把日期转毫秒值
select unix_timestamp(now());#1711529268
# from_unixtime(int): 把毫秒值转 日期
select from_unixtime(1711529268);#2024-03-27 16:47:48 日期格式是默认格式.
select from_unixtime(1711529268,'%Y年%m月%d日 %H时%i分%s秒');#2024年03月27日 16时47分48秒 日期格式是指定格式
四、流程操作
①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
#--------------------------------------
# 固定值的判断.
# case 列名
# when 值 then 值
# when 值 then 值
# ....
# else 值 end
④nullif(expr1,expr2) : 相同返回null 不同保留表达式1的原值.
/**
④case…when函数用法 : 多条件判断给出返回值.
#区间范围的判断.
Case
When condition1 Then result1
When condition2 Then result2
...
Else result_n
End
--------------------------------------
固定值的判断.
case 列名
when 值 then 值
when 值 then 值
....
else 值
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;