mysql函数基础知识

函数

-1.概念

函数指一段可以直接被另一段程序调用的程序或代码。

-2.函数的种类

• 字符串函数

  mysql内置了很多函数，但是最常见的函数如下;

  函数	功能
  CONCAT(S1,S2,…SN)	字符串拼接，将S1,S2,…SN拼接成一个字符串
  LOWER(str)	将字符串str全部转为小写
  UPPER(str)	将字符串str全部转为大写
  LPAD(str,n,pad)	左填充，用字符串pad对str的左边进行填充，达到n个字符串长度
  RPAD(str,n,pad)	右填充，用字符串pad对str的右边进行填充，达到n个字符串长度
  TRIM(str)	去掉字符串头部和尾部的空格
  SUBSTRING(str,start,len)	返回从字符串str,从start位置起的len个长度的字符串

  #字符串函数：
  -- concat：拼接字符
  select concat('hello','mysql'); #hellomysql
  -- lower：全部转为小写
  select lower('HELLO');#结果为hello
  -- upper：全部转为大写
  select upper('hello'); #结果为HELLO
  
  -- lpad：向左填充
  select rpad('01',5,'hello');#将hello对01向左填充，中间表示总个数，结果为:hel01
  
  -- rpad：向右填充
  select rpad('01',5,'hello');#结果为10hel
  
  -- trim：去除收尾两个地方的空格
  select trim(' Hello mysql '); #结果为：Hello mysql
  
  -- substring：截取字符串
  select substring('hello mysql',1,5);#注意索引是从1开始，结果为hello
  
  -- 练习：由于业务的需求变更，企业员工工号，统一为5位数，目前不足5位数的全部咋前面补0，比如：1号员工的工号应该是00001.
  update emp set workno=lpad(workno,5,'0');
  

• 数值函数

  常见的数值函数如下：

  函数	功能
  CEIL(x)	向上取整
  FLOOR(x)	向下取整
  MOD(x,y)	返回x/y的模
  RAND()	返回0~1的随机数
  ROUND(x,y)	求参数x的四舍五入的值，保留y位小数

  #数值函数
  -- ceil:向上取整
  select ceil(1.5);#结果为2
  select ceil(1.1);#结果为2
  
  -- floor:向下取整
  select floor(1.9);#结果为1
  
  -- mod #取余操作
  select mod(5,4);#结果为1 ---5%4==1
  
  -- rand：得出一个0到之间的随机数
  select rand();
  
  -- round：四舍五入
  select round(2.34,1);#结果为2.3,means:对2.34进行四舍五入保留小数1位，2.3
  select round(2.35,1);#结果为2.4
  
  #根据需求，通过数据库的函数生成一个6位数的随机验证码
  -- 通过向下取整的方式
  select floor(rand()*1000000);
  -- 通过四舍五入的方式
  select  round( rand()*1000000,0);
  -- 改进
  select lpad(round(rand()*1000000,0),6,'0');#不足六位的话就进行补0操作
  
  

• 日期函数

  函数	功能
  CURDATE()	返回当前日期
  CURTIME()	返回当前时间
  NOW()	返回当前日期和时间
  YEAR(date)	获取指定date的年份
  MONTH(date)	获取指定date的月份
  DAY(date)	获取指定date的日期
  DATE_ADD(date,INTERVAL expr type)	返回一个日期/时间值加上一个时间间隔expr后的时间值
  DATEDIFF(date1,date2)	返回起始时间date1和结束时间date2之间的天数

  #日期函数
  
  -- curdate()
  select curdate();#获取当前日期
  
  -- curtime()
  select curtime();#获取当前时分秒
  
  -- now()
  select now(); #获取当前，年月日，时分秒
  
  -- YEAR,MONTH,DAY
  select YEAR(now());#当前年份
  select MONTH(now());#当前月份
  select DAY(now());#当前日期（一个月中第几天）
  
  -- date_add() 向后推几年
  select date_add(now(),INTERVAL 70 DAY);#当前时间往后推70天
  select date_add(now(),INTERVAL 70 MONTH);#当前时间往后推70个月
  select date_add(now(),INTERVAL 70 YEAR );#当前时间往后推70年
  
  -- datediff；两个指定时间相差的天数求两个时间的差
  select datediff('2021-12-21','2021-10-01');
  
  #查询所有员工的入职天数，并根据入职天数倒序排序
  select name, datediff(curdate(),entrydate) as 'entrydates' from emp order by entrydates desc;
  
  
  

• 流程函数

流程函数也是一类很常用的函数，可以在SQL语句中实现条件筛选，从而提高语句的查询效率

函数	功能
IF(values,t,f)	如果value为true,则返回t,否则返回f
IFNULL(value1,value2)	如果value1不为空，返回value1,否则返回value2
CASE WHEN [val1 ] THEN [res1]…ELSE[default] END	如果val1为true,返回res1,…否则返回Default默认值
CASE[expr] WHEN [val1] THEN [res1]…ELES [default] END	如果expr的值等于val1,返回res1,…否则返回default默认值

#流程控制函数
-- if
 select if (true,'ok','Error');#结果
 select if (false,'ok','Error');#结果为Error
 select if(2>3,'正确','错误');#结果为错误

-- ifnull:如果不为空就返回原值，否则返回默认值
select ifnull('ok','default'); #结果为ok
select ifnull('','default');#结果什么都没有
select ifnull(null,'default')#结果为default

-- case when then else end
-- 需求：查询emp表的员工姓名和工作地址（厦门/福州 ---> 一线城市，其他---->二线城市）
select
    name,
   (case  workaddress when '厦门' then '二线城市' when '福州' then '二线城市' else '三线城市' end) as '工作地址'
from emp;


-- 案例：统计各个学员的成绩，展示规则如下
-- >=85,展示优秀
-- >=60 展示及格
-- 否则，展示不及格

create table score(
    id int comment 'ID',
    name varchar(20) comment '姓名',
    math int comment '数学0',
    english int comment '英语',
    chinese int comment '语文'
)comment  '学员成绩表';

insert into score(id, name, math, english, chinese)
values (1,'Tom',67,88,95),(2,'rose',23,66,90),(3,'jack',56,78,90);

select * from score;

-- 实现
select
    id,
    name,
   (case when math >=85 then '优秀' when math>=60 then '及格' else '不及格'end) '数学',
   (case when english >=85 then '优秀' when english>=60 then '及格' else '不及格'end) '英语',
   (case when chinese >=85 then '优秀' when chinese>=60 then '及格' else '不及格'end) '语文'
from score;

-3.总结：

1. 字符串函数

   CONCAT,LOWER,UPPER,LPAD,RPAD,TRIM,SUBSRTING
   

2. 数值函数

   CEIL,FLOOR,RAND,ROUND
   

3. 日期函数

   CURDATE,CURTIME,NOW,YEAR,MONTH,DAY,DATE_ADD,DATEDIFF
   

4. 流程函数

   IF,IFNULL,CASE[...]WHEN...THEN....ELSE...END