⑥ 【MySQL函数】字符串函数、数值函数、日期函数、流程函数

CONCAT(S1,S2,...Sn)

LOWER(str)

UPPER(str)

LPAD(str,n,pad)

RPAD(str,n,pad)

TRIM(str)

SUBSTRING(str,start,len)

CEIL(X)

FLOOR(X)

MOD(X,Y)

RAND()

ROUND(X,Y)

-- 案例：通过数据库函数生成一个6位数的随机验证码
SELECT LPAD(ROUND(RAND()*1000000,0),6,'0');

CURDATE()

CURTIME()

NOW()

YEAR(date)

MONTH(date)

DAY(date)

DATE_ADD(date,INTERVAL exper type)

DATEDIFF(date1,date2)

IF(value,t,f)

IFNULL(value1,value2)

CASE 
WHEN [val1] THEN [res1]
WHEN [val2] THEN [res2]
...
WHEN [valn] THEN [resn]
ELSE [default]
END

CASE [expr]
WHEN [val1] THEN [res1]
WHEN [val2] THEN [res2]
...
WHEN [valn] THEN [resn]
ELSE [default]
END

-- 演示
-- 创建表，插入数据
create table score(
    id int comment 'ID',
    name varchar(20) comment '姓名',
    math int comment '数学',
    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, 98, 76);
/*
统计班级各个学员的成绩，展示规则如下：
>=85 展示优秀
>=60 展示几个
否则 展示不及格
*/
SELECT 
  id,
  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;
--