MySQL函数

函数

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

函数的分类

字符串函数

MySQL内置的常用字符串函数：

函数	功能
CONCAT（s1,s2…）	字符串拼接，将多个字符串拼接成一个
LOWER(str)	将字符串转换成小写
UPPER(str)	将字符串转换成大写
LPAD(str,n,pad)	左填充，在字符串str左边拼接字符串pad，使str达到n个长度
RPAD(str,n,pad)	右填充，在字符串str右边拼接字符串pad，使str达到n个长度
TRIM(str)	去掉字符串头部和尾部的空格
SUNSTRING(str,start,len)	在字符串str中从start位置开始，截取len长度的子字符串
REPLACE(column, source, replace)	替换字符串

示例：

-- 字符串拼接
SELECT CONCAT('HELLO','  MySQL');

-- 字符串转小写
SELECT LOWER('HELLO  MySQL');

-- 字符串转大写
SELECT UPPER('hello  mysql');

字符串左填充有多种情况

1. 当 str = n 时，返回str

-- 字符串左填：str长度等于n
SELECT LPAD('abcdef',6,'123456789');

2. 当str > n时，返回str中前n个字符

-- 字符串左填充：str长度大于n
SELECT LPAD('abcdef',3,'123456789');

3. 当str < n时，str缺多少个字符，就从pad中截取多少个字符，填充到str左边

-- 字符串左填充：str长度小于n
SELECT LPAD('abcdef',10,'123456789');

字符串右填充同理，这里就不演示了

字符串截取

-- 在字符串str中从第三个位置开始，截取长度为5的子字符串
SELECT SUBSTRING('123456789',3,5);

TRIM()函数

-- 删除字符串开头结尾的空格，中间不变
SELECT '  12  3 456 789    ';

SELECT TRIM('  12  3 456 789    ');

数值函数

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

-- CEIL(X) 向上取整
SELECT CEIL(3.00546);  -- 4
-- FLOOR(X) 向下取整 
SELECT FLOOR(2.999999); -- 2
-- MOD(x,y) 返回x/y的模      
SELECT MOD(100,10);   -- 0    
-- RAND() 返回0~1内的随机数   
SELECT RAND();       
-- ROUND(x,y)求参数x的四舍五入的值，保留y位小数 
SELECT ROUND(3.54348,3); -- 3.543

日期函数

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

-- 查询当前时间两天后对应的时间
select date_add(now(),interval 2 DAY);
-- 查询当前时间两年后对应的时间
select date_add(now(),interval 2 YEAR);
-- 查询公司员工的入职天数，并以入职天数倒序排列
SELECT name,DATEDIFF(now(),join_date) as entryday FROM emp ORDER BY entryday DESC;

流程函数

函数	作用
IF(value,t,f)	如果value为true，则返回t，否则返回f
IFNULL(value1,value2)	如果value1不为NULL，则返回value1，否则返回value2
CASE WHEN [v1] THEN [res1] … ELSE [default] END	如果v1为true，则返回res1 , … 否则返回default默认值
CASE [expr] WHEN [va1] THEN [res1] … ELSE [defalut] END	如果expr的值等于va1, 返回res1, … 否则返回default默认值

-- 查询公司员工的姓名和工作地址（北京、上海显示为一线城市，其余二线城市）
select `name` as '姓名',(case workaddress  when '北京'  then '一线城市' when '上海' then '一线城市' else '二线城市' end)  as '工作地址'  from emp;
或：
select  `name` as '姓名',(case  when workaddress = '北京'  then '一线城市' when workaddress = '上海' then '一线城市' else '二线城市' end)  as '工作地址'  from emp;
-- 查询公司员工的薪资档次
--由于档次并不是一个具体的值，而是一个范围，所以只能使用CASE WHEN 而不能使用CASE [expr] WHEN
SELECT id                  '员工编号',
       name                '姓名',
       (case
            when salary >= 8800 then '较高'
            when salary BETWEEN 5000 AND 8800 then '中等'
            else  '较低' end) '档次'
from emp
order by 档次 DESC;