MySQL之函数

目录

一，函数的定义

二，日期函数

三，字符串函数

四，数字函数

五，条件判断函数

六，合并

七，聚合函数

---

 一，函数的定义

 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一起使用，也可单独使用