一. 字符串函数

1. 常用字符串函数

函数	功能
CONCAT(S1, S2, ..., Sn)	字符串拼接
LOWER(str)	转小写
UPPER(str)	转大写
LPAD(str, n, pad)	左填充，用字符串pad对str左边填充，达到n个字符长度
RPAD(str, n, pad)	右填充，用字符串pad对str右边填充，达到n个字符长度
TRIM(str)	去掉头尾的空格
SUBSTRING(str, start, len)	截取str，在str的start位置截取len长度的字符串，索引从1开始

2. 案例

由于业务升级，公司对员工工号有新的要求，工号需要为 5 位，不足5位的前面补0

UPDATE 表名 SET WORKNO = LPAD(WORKNO, 5 , '0');

二. 数值函数

1. 常用的数值函数

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

2. 案例

通过数据库函数，生成6位数随机验证码

SELECT LPAD(FLOOR(RAND() * 1000000), 6, '0');

存在 RAND() 得出 0.00xxxx 的情况，导致不使用 LPAD 时不足 6 位的情况

三. 日期函数

1. 常用的日期函数

函数	功能
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 如果 date1 < date2 那么会导致得数为负

2. 案例

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

SELECT NAME '姓名', DATEDIFF(CURDATE(), ENTRYDATE) '入职天数' FROM 表名 ORDER BY '入职天数' DESC;

四. 流程控制函数

1.常用流程控制函数

函数	功能
IF(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

2. 案例

• 查询emp表的员工姓名和工作地址（北京/上海 --> 一线城市，其他 --> 二线城市）

-- IF()
SELECT
    NAME,
    IF(WORKADDRESS IN ('北京', '上海'), '一线城市', '二线城市') WORKADDRESSTYPE
FROM
    EMP;

-- CASE WHEN THEN ELSE END
SELECT
    NAME,
    (CASE WORKADDRESS 
        WHEN '北京' THEN '一线城市'
        WHEN '上海' THEN '一线城市'
        ELSE '二线城市'
    END) AS WORKADDRESSTYPE
FROM
    EMP;

• 统计班级各个学员的成绩，展示的规则如下：
  • score表，id, name, math, english, chinese
  • >= 85，展示优秀
  • >= 60，暂时及格
  • 否则，暂时不及格

SELECT
    NAME,
    (CASE 
        WHEN MATH >= 85 THEN '优秀'
        WHEN MATH >= 60 THEN '及格'
        ELSE '不及格'
    END) AS '数学成绩',
    (CASE 
        WHEN ENGLISH >= 85 THEN '优秀'
        WHEN ENGLISH >= 60 THEN '及格'
        ELSE '不及格'
    END) AS '英语成绩',
    (CASE 
        WHEN CHINESE >= 85 THEN '优秀'
        WHEN CHINESE >= 60 THEN '及格'
        ELSE '不及格'
    END) AS '语文成绩'
FROM
    SCORE