黑马mysql教程笔记（mysql8教程）基础篇——函数（字符串函数、数值函数、日期函数、流程函数）

参考文章1：https://www.bilibili.com/video/BV1Kr4y1i7ru/

参考文章2：https://dhc.pythonanywhere.com/article/public/1/

基础篇

函数

• 字符串函数
• 数值函数
• 日期函数
• 流程函数

字符串函数

常用函数

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

查询结果：