数据库MySQL —— 函数

目录

 一、字符串函数

二、数值函数

三、日期函数

四、流程函数

---

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

	数据库表中，存储的是入职日期，那么如何快速计算入职天数呢？
	数据库表中，存储的是学生的分数值，那么如何快速判断分数的等级呢？

 这些都会用到数据库中的 内置函数 。 

 一、字符串函数

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个长度的字符串

• 语法： SELECT 函数( 参数 );

-- concat :HelloMySQL
select concat('Hello' , 'MySQL');
-- lower :hello
select lower('HELLO');
-- upper :HELLO
select upper('hello');
-- lpad :---01
select lpad('01',5,'-');
-- rpad :01---
select rpad('01',5,'-');
-- trim :Hello MySQL
select trim(' Hello MySQL ');
-- substring :Hello
select substring('Hello MySQL',1,5);

二、数值函数

常见的数值函数如下：

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

-- ceil :2
select ceil(1.5);
select ceil(1.1);
-- floor :1
select ceil(1.1);
-- mod :3
select mod(3,4);
-- rand
select rand();
-- round :2.35
select round(2.345,2);

案列：

需求：通过数据库的函数，生成一个六位数的随机验证码。

-- 1.生成随机数变小数点前6位
select rand()*1000000;
-- 2.四舍五入
select round(rand()*1000000,0);
-- 0.051316 3.乘后补零
select lpad(round(rand()*1000000,0),6,'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() :2023-03-21
select curdate();
-- curtime() :19:13:22
select  curtime();
-- now() :2023-03-21 19:13:44
select now();
-- year() :2023
select year(now());
-- month() :3
select month(now());
-- day() :21
select day(now());
-- date_add() :2023-05-30 19:15:32
select date_add(now(),INTERVAL 70 DAY );
-- 2029-01-21 19:15:54
select date_add(now(),INTERVAL 70 MONTH );
-- dateiff() :70
select datediff('2023-05-30','2023-03-21');

案列：

需求：查询所有员工的入职天数，并根据入职天数倒序排序。

-- 1.查询所有员工信息
select * from employee;
-- 2. 入职天数
select name, datediff(curdate(),entrydate)from employee;
-- 3.起别名
select name, datediff(curdate(),entrydate) as 'worktime' from employee ;
-- 4.倒序排序
select name, datediff(curdate(),entrydate) as 'worktime' 
from employee order by worktime desc ;

四、流程函数

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

函数	功能
lF (value , 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] ...ELSE  [default] END	如果expr的值等于val1，返回res1，...否则返回default默认值

-- if :ok
select if(true,'ok','error');
-- ifnull :ok
select ifnull('ok','default');
-- default
select ifnull(null,'default');
-- 第一个值不为null，返回第一个值
-- 第一个值为null，返回第二个值

-- case when then else end
-- 需求：查询员工姓名和工作地址（北京/上海 -> 一线城市 ； 其他 -> 二线城市）
select name,
       (case workaddress when '北京' then '一线城市'
           when '上海' then '一线城市' else'二线城市' end)
           as '工作城市'
from employee;

案列：

需求：统计班级各个学院的成绩，展示规则如下：

• >= 85，展示优秀。
• >= 60，展示及格。
• 否则，展示不及格。

-- 1.查询全部信息
select id,name,math,english,chinese from score;
-- 2.判断
select
       id,
       name,
       (case when math >= 85 then '优秀' 
             when math >=60 then '及格' 
             else '不及格' end) as '数学',
       (case when english >= 85 then '优秀' 
             when math >=60 then '及格' 
             else '不及格' end) as '英语',
       (case when chinese >= 85 then '优秀' 
             when math >=60 then '及格' 
             else '不及格' end) as '语文'
from score;