MySQL之函数

一、函数的定义

1.类似Java定义方法
2.可以带参数
3.必须有返回值(一行一列) ，重点：java方法可以没有返回值，据库函数必须要有返回值
select max(score) from sc;
4.函数可以嵌套到sql语句中（ 注：存储过程则不可以）
5.函数可嵌套调用 round(avg)

二、常用函数

1.日期函数

①、 NOW()：获取当前日期和时间

SELECT now();

②、DAY()：获取日期中的天

SELECT DAY(NOW());

③、MONTH()：获取日期中的月

SELECT MONTH(NOW())

④、YEAR()：获取日期中的年

SELECT YEAR(NOW())

⑤、日期转换函数DATE_FORMAT()/STR_TO_DATE()

SELECT NOW(),DATE_FORMAT(NOW(),'%Y年%m月%d日')

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

SELECT STR_TO_DATE('2022-07-09','%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;

2、字符串函数

①、 UPPER()/LOWER()：大小写互转

大写 SELECT UPPER("my name is dage") 小写SELECT LOWER("MY NAME IS AGE")

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

SELECT UUID(),REPLACE(UUID(),'-','');

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

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

SELECT SUBSTRING('zhang san is a person',5)

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

SELECT LENGTH(' zhang san is a person '),LENGTH(TRIM('zhang san is a person'))

⑤、数字函数
1、FLOOR：向下取整
2、CEIL： 向上取整
3、ROUND：四舍五入

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

2、条件判断函数

①、IF(expr,v1,v2) 如果expr是TRUE则返回v1，否则返回v2

SELECT s.sid,s.sname, SUM(if(sc.cid = '1',sc.score,0)) 语文, SUM(IF(sc.cid = '2',sc.score,0)) 数学, SUM(IF(sc.cid = '3',sc.score,0)) 英语 FROM t_student s LEFT JOIN t_score sc on s.sid = sc.sid GROUP BY s.sid,s.sname

②、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= '1' then sc.score end) 语文, SUM(case when sc.cid= '2' then sc.score end) 数学, SUM(case when sc.cid= '3' 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、聚合函数

1、 SUM()：求和。常与GROUP BY一起使用，也可单独使用
2、AVG()：求平均值。常与GROUP BY一起使用，也可单独使用
3、MAX()：求最大值。常与GROUP BY一起使用，也可单独使用
4、 MIN()：求最小值。常与GROUP BY一起使用，也可单独使用
5、 COUNT()：统计记录的条数。常与GROUP BY一起使用，也可单独使用**

四、合并

1、 UNION：将所有的查询结果合并到一起，然后去除掉相同的记录
2、UNION ALL：将所有的查询结果合并到一起，不会去除掉相同的记录
前提条件：结果集列数个数相同，列的类型还要相同或是兼容
使用场景：在项目统计报表模块，用来合并数据

SELECT 1,'zs',18 UNION SELECT 2,'s',18 UNION SELECT 2,'s',18

SELECT 1,'zs',18 UNION ALL SELECT 2,'s',18 UNION ALL SELECT 2,'s',18