MYSQL之函数

目录

一、函数定义

二、常用函数

三、条件判断函数

四、聚合函数(SUM/COUNT/AVG/MAX/MIN)

五、合并(union)

---

一、函数定义

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

---

二、常用函数

 

1.日期函数

   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()：将日期转换成字符串 
     select  DATE_FORMAT('2002-09-11','%Y-%m-%d')
   STR_TO_DATE()：将字符串转换成日期

   查询各学生的年龄，只按年份来算
     select s.* ,year(now()) - year(s.sage) FROM tb_student s;
    
   按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一
      select s.*,year(now()) - year(s.sage)+if(month(now()) < month(s.sage),-1,0) from tb_student s;
   查询本月过生日的学生
     select *  from tb_student where month(now()) = month(sage);
   查询下月过生日的学生
     1)select *  from tb_student where month(now())+1 = month(sage);
     
     2)date_add(NOW(), interval 1 MONTH)
        select * from tb_student where month(DATE_ADD(now(),interval 1 month)) = month(sage);

 

   2.字符串函数

 1) UPPER()/LOWER()：大小写互转
     select upper (' i love yuwenwen ') ---大写
    
     select lower ('I LOVE BaiJingTing ') ----小写
     
   2) REPLACE()：搜索并替换字符串中的子字符串
     select uuid(), REPLACE(uuid(),'-','') ;
     
   3) SUBSTRING()：从具有特定长度的位置开始的最一个子字符串
     select SUBSTRING('我是图图小淘气',1,5);
     select SUBSTRING('我是图图小淘气',5); 
     
   4) TRIM()：去除前后空格
     select LENGTH(trim(' goodgoodStudy '));
     select LENGTH(' goodgoodStudy ');  ---对比两个字符的长度
   5) LENGTH()：获取字符串长度
     select LENGTH(' goodgoodStudy ');
   6) CONCAT()：合并字符串函数，返回结果为连接参数产生的字符串，参数可以是一个或多个
        CONCAt(first_name,last_name)

 
   3.数字函数
   
   

 1）FLOOR：向下取整
   2）CEIL： 向上取整
     select FLOOR(-3.14) FLOOR(3.14),CEIL(-3.14),CEIL(3.14);
     ---答案: -4;3;-3;4
     
   3）ROUND：四舍五入
     select ROUND(3.141596);

---

 

三、条件判断函数

       
   1）IF(expr,v1,v2)
   如果expr是TRUE则返回v1，否则返回v2
     行列转换：
     需求：每一个学生的语数外分数

方法1

写法1) if：
 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 tb_student s LEFT JOIN tb_score  sc on s.sid= sc.sid 
     GROUP BY  s.sid,s.sname
        

方法2
  

     写法2) case when ...then end :
 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 tb_student s LEFT JOIN tb_score  sc on s.sid= sc.sid 
     GROUP BY  s.sid,s.sname;
     

其他方法
        

写法3)case ...when   ?
        select s.*,case t1.cid
        when '01' then '语文'
        when '02' then '数学'
        else '未知课程' end
        from 
        (select * from tb_score where cid='01') t1,
        (select * from tb_score where cid='02') t2 ,
        tb_student s
        where t1.sid=t2.sid and t1.score > t2.score ;
        

 
    
   2）IFNULL(v1,v2)
   如果v1不为NULL，则返回v1，否则返回v2

   3）CASE expr(变量名) WHEN v1 THEN r1 [WHEN v2 THEN v2] [ELSE rn] END
   如果expr等于某个vn，则返回对应位置THEN后面的结果，如果与所有值都不想等，则返回ELSE后面的rn
     

---

四、聚合函数(SUM/COUNT/AVG/MAX/MIN)

   

1) SUM()：求和。常与GROUP BY一起使用，也可单独使用
     select SUM(b.score) from tb_student a ,tb_score b where a.sid=b.sid  GROUP BY b.sid;
   2) AVG()：求平均值。常与GROUP BY一起使用，也可单独使用
     select AVG(b.score) from tb_student a ,tb_score b where a.sid=b.sid  GROUP BY b.sid;
   3) MAX()：求最大值。常与GROUP BY一起使用，也可单独使用
    select b.cid, MAX(b.score) from tb_student a ,tb_score b where a.sid=b.sid  GROUP BY b.cid;
   4) MIN()：求最小值。常与GROUP BY一起使用，也可单独使用
         select b.cid, MIN(b.score) from tb_student a ,tb_score b where a.sid=b.sid  GROUP BY b.cid;
   5) COUNT()：统计记录的条数。常与GROUP BY一起使用
         select  b.cid,COUNT(a.sid) from tb_student a ,tb_score b where a.sid=b.sid  GROUP BY b.cid;   也可单独使用

---

 

五、合并(union)

        1) UNION：将所有的查询结果合并到一起，然后去除掉相同的记录
        select 1,'zs',23
        union
        select 2,'ls',29
        union
        select 2,'ls',29

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

---

 --------------------------听说点赞的会越长越好看~ _~  -------------------------------------------------------------