一、常用函数

①日期函数

案例一:获取当前日期和时间

select now();
1

案例二:获取日期中的天

select day(now());
1

案例三:获取日期中的月

 select MONTH(now()); 
1

案例四:获取日期中的年

 select YEAR(now());
1

案例五:日期转换函数DATE_FORMAT()/STR_TO_DATE()
DATE_FORMAT()：将日期转换成字符串

 select now(),DATE_FORMAT(now(),'%Y年%m月%d日');
1

STR_TO_DATE()：将字符串转换成日期

 select STR_TO_DATE('2022-06-01','%Y-%m-%d');
1

表t_student和表t_score
表t_score数据

表t_student数据

案例六: 查询各学生的年龄，只按年份来算

select s.*,YEAR(NOW())-YEAR(s.sage) from t_student s;
1

按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一

	select s.*,YEAR(NOW())-YEAR(s.sage)+if(MONTH(now())<MONTH(s.sage),-1,0) from t_student s;
1

查询本月过生日的学生

  select * from t_student where month(now()) = month(sage)
1

查询上月过生日的学生

第一种
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
2
3
4
5
6
7

②字符串函数

1）UPPER()/LOWER()：大小写互转

 select UPPER(' zhang san li si ')
1

 select LOWER(' ZHANG SAN LI SI ')
1

2) REPLACE()：搜索并替换字符串中的子字符串

 select UUID(),REPLACE(uuid(),'-','');  
1

3) SUBSTRING()：从具有特定长度的位置开始的最一个子字符串

select SUBSTRING(' zhang san li si ',1,5)
select SUBSTRING(' zhang san li si ',5)
1
2

4) TRIM()：去除前后空格
5) LENGTH()：获取字符串长度

select LENGTH(' zhang san li si '),LENGTH(TRIM( ' zhang san li si '))
1

③数字函数

1）FLOOR：向下取整
2）CEIL： 向上取整
3）ROUND：四舍五入

 select FLOOR(-3.14),FLOOR(3.14),CEIL(-3.14),CEIL(3.14);
1

二、条件判断函数

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
1
2
3
4
5
6
7
8
9
10
11
12

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
1
2
3
4
5
6
7
8
9
10
11
12

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 
1
2
3
4
5
6
7
8
9
10
11
12
13
14

三、合并(union)

1. UNION：将所有的查询结果合并到一起，然后去除掉相同的记录
2. 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
1
2
3
4
5
6
7
8
9
10
11