参考文章1：https://www.bilibili.com/video/BV1Kr4y1i7ru/
参考文章2：https://dhc.pythonanywhere.com/article/public/1/
文章目录
- 基础篇
- 函数
- 字符串函数
- 常用函数
- 使用示例
- 实例：更新已有的所有员工号，使其满足5位数长度，不足5位左方补零
- 数值函数
- 常见函数
- 使用示例
- 实例：通过数据库的函数，生成一个六位数的随机验证码
- 日期函数
- 常用函数
- 使用示例
- 实例：查询所有员工的入职天数，并根据入职天数倒序排序
- 流程函数
- 常用函数
- 例子
- 实例：统计班级每个学员的成绩，并根据分数展示优秀、及格或不及格
基础篇
函数
- 字符串函数
- 数值函数
- 日期函数
- 流程函数
字符串函数
常用函数
| 函数 | 功能 |
|---|---|
| CONCAT(s1, s2, …, sn) | 字符串拼接，将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从start位置起的len个长度的字符串 |
| REPLACE(column, source, replace) | 替换字符串 |
使用示例
-- 拼接
SELECT CONCAT('Hello', 'World');
-- HelloWorld-- 小写
SELECT LOWER('Hello');
-- hello-- 大写
SELECT UPPER('Hello');
-- HELLO-- 左填充
SELECT LPAD('01', 5, '-');
-- ---01-- 右填充
SELECT RPAD('01', 5, '-');
-- 01----- 去除空格
SELECT TRIM(' Hello World ');
-- Hello World-- 切片（起始索引为1）
SELECT SUBSTRING('Hello World', 1, 5);
-- Hello
实例：更新已有的所有员工号，使其满足5位数长度，不足5位左方补零
-- 1. 由于业务需求变更，企业员工的工号，统一为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.1);
-- 2-- floor
select floor(1.9);
-- 1-- mod（取余）
select mod(3, 4);
-- 3-- rand
select rand();
-- 0.7344290974870595-- round
select round(2.3456, 2);
-- 2.35
select round(2.3456, 10);
-- 2.3456实例：通过数据库的函数，生成一个六位数的随机验证码
-- 案例：通过数据库的函数，生成一个六位数的随机验证码
select lpad(round(rand()*1000000, 0), 6, '0')
-- 318197
-- 743267
日期函数
常用函数
| 函数 | 功能 |
|---|---|
| CURDATE() | 返回当前日期 |
| CURTIME() | 返回当前时间 |
| NOW() | 返回当前日期和时间 |
| YEAR(date) | 获取指定date的年份 |
| MONTH(date) | 获取指定date的月份 |
| DAY(date) | 获取指定date的日期 |
| DATE_ADD(date, INTERVAL expr type) | 返回一个日期/时间值加上一个时间间隔expr后的时间值，单位为type（year、month、day、hour、minute、second） |
| DATEDIFF(date1, date2) | 返回起始时间date1和结束时间date2之间的天数 |
| TIMESTAMPDIFF(type, timestamp1, timestamp2) | 返回起始时间timestamp1和结束时间timestamp2之间的type时间差 |
使用示例
-- 日期函数-- curdate()
select curdate();
-- 2023-10-12-- curtime()select curtime();
-- 21:48:47-- now()select now();
-- 2023-10-12 21:48:47-- YEAR, MONTH, DAY, HOUR, MINUTE, SECOND
select year(now());
-- 2023
select month(now());
-- 10
select day(now());
-- 12
select hour(now());
-- 21
select minute(now());
-- 48
select second(now());
-- 47-- date_add
select date_add(now(), interval 70 year);
-- 2093-10-12 21:59:52
select date_add(now(), interval 70 second);
-- 2023-10-12 22:01:17-- datediff（前面减后面）select datediff('2021-10-02', '2021-10-01');
-- 1select datediff('2021-10-02', '2022-10-01');
-- -364select datediff(now(), '2021-10-01');
-- 741-- timestampdiff（后面减前面）
SELECT TIMESTAMPDIFF(YEAR, '2000-01-01', '2021-12-31');
-- 21
SELECT TIMESTAMPDIFF(second , '2000-01-01 15:00:00', '2000-01-01 14:00:00');
-- -3600
SELECT TIMESTAMPDIFF(month , '2000-01-01 15:00:00', '2000-03-01 12:00:00');
-- 1
实例：查询所有员工的入职天数，并根据入职天数倒序排序
-- 查询所有员工入职天数，并根据入职天数倒序排序
select name, datediff(current_date(), entrydate) as 'entrydays' from emp order by entrydays desc ;
desc为倒序排序
流程函数
常用函数
| 函数 | 功能 |
|---|---|
| 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默认值 |
例子
-- 流程控制函数-- if
select if(false, 'ok', 'Error');
-- Error-- ifnull
select ifnull('Ok', 'Default');
-- Okselect ifnull('', 'Default');
--select ifnull(null, 'Default');
-- Default
-- case when then else end
selectname as '姓名',(case when age > 30 then '中年' else '青年' end) as '年纪'
from emp;
-- case when then else end-- 需求：查询emp表的员工姓名和工作地址（北京/上海 ----> 一线城市，其他 ----> 二线城市）
selectname as '姓名', (case workaddress when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end) as '工作地址'
from emp;-- 不够简洁，优化一下：
selectname as '姓名',if(workaddress in ('北京', '上海'), '一线城市', '二线城市') as '工作地址'
from emp;
实例：统计班级每个学员的成绩，并根据分数展示优秀、及格或不及格
需求：
-- 统计班级每个学员的成绩，展示规则如下：
-- >= 85，展示优秀
-- >= 60，展示及格
-- 否则，展示不及格
建表语句：
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);
查看表数据：
sql查询语句：
selectid,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;
查询结果：
