目录
一、常用函数
1.日期函数
1) NOW():获取当前日期和时间
2) DAY():获取日期中的天
3) MONTH():获取日期中的月
4) YEAR():获取日期中的年
5) 日期转换函数DATE_FORMAT()/STR_TO_DATE()
实例:
1) NOW():获取当前日期和时间
select now();
2) DAY():获取日期中的天
select day(now());
3) MONTH():获取日期中的月
select MONTH(now());
4) YEAR():获取日期中的年
select year(now());
5) 日期转换函数DATE_FORMAT()/STR_TO_DATE()
DATE_FORMAT():将日期转换成字符串
select now(),DATE_FORMAT(now(),'%Y年%m月%d日')
STR_TO_DATE():将字符串转换成日期
select STR_TO_DATE('2022-12-12','%Y-%m-%d')
案例
学生表数据如下:
查询各学生的年龄,只按年份来算
select s.*,YEAR(now())-year(s.sage) from t_student s
按照出生日期来算,当前月日 < 出生年月的月日则,年龄减一
select s.*,YEAR(now())-year(s.sage)+if(month(now())<MONTH(s.sage),-1,0) from t_student s
查询本月过生日的学生
select * from t_student where MONTH(now()) = MONTH(sage);
没有过生日的则为空:
查询下月过生日的学生
第一种写法
select * from t_student where MONTH(now())+1 = MONTH(sage);
第二种写法
select * from t_student where date_add(NOW(), interval 1 MONTH) = MONTH(sage)
为空:
2.字符串函数
1) UPPER()/LOWER():大小写互转
2) REPLACE():搜索并替换字符串中的子字符串
3) SUBSTRING():从具有特定长度的位置开始的最一个子字符串
4) TRIM():去除前后空格
5) LENGTH():获取字符串长度
6) CONCAT():合并字符串函数,返回结果为连接参数产生的字符串,参数可以是一个或多个
3.数字函数
1)FLOOR:向下取整
2)CEIL: 向上取整
3)ROUND:四舍五入
1) UPPER()/LOWER():大小写互转
select upper(' beautiful girl ')
select lower(' GOOD MAN')
2) REPLACE():搜索并替换字符串中的子字符串
select 'yang-can-xia',REPLACE('yang-can-xia','-',' ')
3) SUBSTRING():从具有特定长度的位置开始的最一个子字符串
select SUBSTRING('yan jia yang i love you',2,5)//从第几个开始到第几个结束
select SUBSTRING('yan jia yang i love you',5)//从第几个开始,直到结束
4) TRIM():去除前后空格
5) LENGTH():获取字符串长度
select LENGTH(' yan jia yang i love you'),LENGTH(trim('yan jia yang i love you'))
去掉了空格长度减少
6) CONCAT():合并字符串函数,返回结果为连接参数产生的字符串,参数可以是一个或多个
3.数字函数
select CONCAT('yan jia yang','i love you')
1)FLOOR:向下取整
2)CEIL: 向上取整
3)ROUND:四舍五入
select FLOOR(-3.14),FLOOR(3.14), CEIL(-3.14), CEIL(3.14),round(-3.14),round(3.14)
二、条件判断函数
1.if
IF(expr,v1,v2)
如果expr是TRUE则返回v1,否则返回v2
案例:
SELECT
s.sid,
s.sname,
sum( IF ( sc.cid = '01', sc.score, 0 ) ) 语文,
sum( IF ( sc.cid = '02', sc.score, 0 ) ) 数学,
sum( IF ( sc.cid = '03', sc.score, 0 ) ) 英语
FROM
t_student s
LEFT JOIN t_score sc ON s.sid = sc.sid
GROUP BY s.sid,s.sname
2.ifnull
IFNULL(v1,v2)
如果v1不为NULL,则返回v1,否则返回v2
3.case ...when....
CASE expr WHEN v1 THEN r1 [WHEN v2 THEN v2] [ELSE rn] END
如果expr等于某个vn,则返回对应位置THEN后面的结果,如果与所有值都不想等,则返回ELSE后面的rn
第一种方法:
SELECT
s.sid,
s.sname,
sum(case when sc.cid = '01' then sc.score end) 语文,
sum(case when sc.cid = '02' then sc.score end) 数学,
sum(case when sc.cid = '03' then sc.score end) 英语
FROM
t_student s
LEFT JOIN t_score sc ON s.sid = sc.sid
GROUP BY s.sid,s.sname
第二种方法:
#switch case
select
s.*,case t1.cid
when '01' then '语文'
when '02' then '数学'
else '未知课程' end
from
(select * from t_score where cid='01') t1,
(select * from t_score where cid='02') t2,
t_student s
where
t1.sid = t2.sid
and t1.sid = s.sid
and t1.score>t2.score