一、常用函数
①日期函数
案例一:获取当前日期和时间
select now();
案例二:获取日期中的天
select day(now());
案例三:获取日期中的月
select MONTH(now());
案例四:获取日期中的年
select YEAR(now());
案例五:日期转换函数DATE_FORMAT()/STR_TO_DATE()
DATE_FORMAT():将日期转换成字符串
select now(),DATE_FORMAT(now(),'%Y年%m月%d日');
STR_TO_DATE():将字符串转换成日期
select STR_TO_DATE('2022-06-01','%Y-%m-%d');
表t_student和表t_score
表t_score数据
表t_student数据
案例六: 查询各学生的年龄,只按年份来算
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 MONTH(date_add(NOW(), interval -1 MONTH)) = month(sage)
②字符串函数
1)UPPER()/LOWER():大小写互转
select UPPER(' zhang san li si ')
select LOWER(' ZHANG SAN LI SI ')
2) REPLACE():搜索并替换字符串中的子字符串
select UUID(),REPLACE(uuid(),'-','');
3) SUBSTRING():从具有特定长度的位置开始的最一个子字符串
select SUBSTRING(' zhang san li si ',1,5)
select SUBSTRING(' zhang san li si ',5)
4) TRIM():去除前后空格
5) LENGTH():获取字符串长度
select LENGTH(' zhang san li si '),LENGTH(TRIM( ' zhang san li si '))
③数字函数
1)FLOOR:向下取整
2)CEIL: 向上取整
3)ROUND:四舍五入
select FLOOR(-3.14),FLOOR(3.14),CEIL(-3.14),CEIL(3.14);
二、条件判断函数
1)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(v1,v2)
如果v1不为NULL,则返回v1,否则返回v2
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
3)CASE expr WHEN v1 THEN r1 [WHEN v2 THEN v2] [ELSE rn] END。如果expr等于某个vn,则返回对应位置THEN后面的结果,如果与所有值都不想等,则返回ELSE后面的rn
# 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
三、合并(union)
- UNION:将所有的查询结果合并到一起,然后去除掉相同的记录
- UNION ALL:将所有的查询结果合并到一起,不会去除掉相同的记录
前提条件:结果集列数个数相同,列的类型还要相同或是兼容
使用场景:在项目统计报表模块,用来合并数据
select 1,'zs',18
union
select 2,'ls',22
union
select 2,'ls',22
select 1,'zs',18
union all
select 2,'ls',22
union all
select 2,'ls',22