一,函数的定义
1.类似Java定义方法
2.可以带参数
3.必须有返回值(一行一列) ,重点:java方法可以没有返回值,数据库函数必须要有返回值
select max(score) from t_score;
4.函数可以嵌套到sql语句中
注:存储过程则不可以
5.函数可嵌套调用 round(avg)
二、日期函数
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
语句:select now(),DATE_FORMAT(now(),'%Y年%m月%d日')
运行结果:
DATE_FORMAT():将日期转换成字符串
STR_TO_DATE():将字符串转换成日期
语句:select STR_TO_DATE('2022-06-08','%Y-%m-%d')
运行结果
案列
1、查询各学生的年龄,只按年份来算
语句:select s.*,year(now())-year(s.sage) from t_student s;
运行结果:
2、 按照出生日期来算,当前月日 < 出生年月的月日则,年龄减一
语句 select s.*,year(now())-year(s.sage),if(month(now()) < month(s.sage),-1,0) from t_student s;
执行结果:
3、查询本月过生日的学生
语句: select * from t_student where month(now()) = month(sage)
运行结果:(没有7月生日的学生)
4、 查询上月过生日的学生
语句: a: select * from t_student where DATE_ADD(now(),interval 1 MONTH) = month(sage)
语句:b: select * from t_student where month(date_add(now(),interval -1 month ))=month(sage);
运行结果一致:
语句:c: select date_add(NOW(), interval 1 MONTH)
执行结果:
三、字符串函数
1、UPPER()/LOWER():大小写互转
语句:select UPPER('my name is kangkang)
运行结果
语句: select LOWER('I AM KANGKANG')
运行结果:
2、REPLACE():搜索并替换字符串中的子字符串
语句:select uuid(),REPLACE(uuid(),'-','');
运行结果:
3、SUBSTRING():从具有特定长度的位置开始的最一个子字符串
语句: select SUBSTRING('zhang san is a person',1,5) --小标从1开始
执行结果:
语句:select SUBSTRING('zhang san is a person',5)
执行结果:
4、TRIM():去除前后空格
LENGTH():获取字符串长度
语句:select LENGTH(' zhang san is a person ' ),LENGTH(TRIM(' zhang san is a person '))
执行结果:
5、CONCAT():合并字符串函数,返回结果为连接参数产生的字符串,参数可以是一个或多个
语句:select CONCAT('aa','abcd')
运行结果:
四、数字函数
FLOOR:向下取整
CEIL: 向上取整
ROUND:四舍五入
语句:select FLOOR(-3.14), CEIL(-3.14),ROUND(-3.14)
执行结果:
五、条件判断函数
每一个学生的语数外分数 行内转换
①、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
运行结果:
②、IFNULL(v1,v2)
如果v1不为NULL,则返回v1,否则返回v2
③、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
运行结果:
六、合并
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
运行结果:
七、聚合函数
聚合函数(SUM/COUNT/AVG/MAX/MIN)
SUM():求和。常与GROUP BY一起使用,也可单独使用
AVG():求平均值。常与GROUP BY一起使用,也可单独使用
MAX():求最大值。常与GROUP BY一起使用,也可单独使用
MIN():求最小值。常与GROUP BY一起使用,也可单独使用
COUNT():统计记录的条数。常与GROUP BY一起使用,也可单独使用