字符串函数

-- concat(str1,str2,...strn)字符串拼接，将str1,str2,...strn拼接成一个字符串

select concat('Hello','MySQL');

-- lower(str)将字符串str全部转为小写

select lower('HElLow');

-- upper(str)将字符串str全部转为大写

select upper('hEllow');

-- lpad(str,n,pad)左填充，用字符串pad对str的左边进行填充，达到n个字符串长度

select lpad('01',10,'-');

-- rpad(str,n,pad)右填充，用字符串pad对str的右边进行填充，达到n个字符串长度

select rpad('2022',11,'0');

-- trim(str)去掉字符串头部和尾部的空格

select trim('  he llo      ');
select concat('55+',trim('  he llo      '),'+55');

-- substring(str,start,len)返回字符串str从start位置起的len个长度的字符串

select substring('12345678',2,5); #start是从1开始，不是从0开始
select substring('1 234 5678',3,5); #start是从1开始，不是从0开始，空格也占位置

案例: 要求企业员工的工号，统一为5位数，不足5位数的全部在前面补0

create database if not exists bilibili; #创建数据库bilibili use bilibili; #选择bilibili数据库，即可进行下面的代码演示 drop database if exists bilibili; #代码演示结束后删除bilibili数据库。这个小文件全部代码演示完再删 show databases; #查看是否删除完成

-- 创建一个emp表格

create table emp(
    id           int                 comment '编号',
    workno       varchar(10)         comment '工号',
    name         varchar(10)         comment '姓名',
    gender       char                comment '性别',
    age          tinyint unsigned    comment '年龄',
    idcard       char(18)            comment '身份证号',
    workaddress  varchar(50)         comment '工作地址',
    entrydate    date                comment '入职时间'
) comment '员工表';

-- 为emp表格的每一个字段添加数据

insert into emp (id,workno,name,gender,age,idcard,workaddress,entrydate)
values (1,'1','刘岩','女',20,'123456789012345670','北京','2000-01-01'),
       (2,'2','张无忌','男',18,'123456789012345671','北京','2005-09-01'),
       (3,'3','韦一笑','男',38,'123456789012345672','上海','2005-08-01'),
       (4,'4','赵敏','女',18,'123456789012345673','北京','2009-12-01'),
       (5,'5','小邵','女',16,'123456789012345674','上海','2007-07-01'),
       (6,'6','杨瑶','男',28,'123456789012345675','北京','2006-01-01'),
       (7,'7','范瑶','男',40,'123456789012345676','北京','2005-05-01'),
       (8,'8','黛绮丝','女',38,'123456789012345677','天津','2015-05-01'),
       (9,'9','范亮亮','女',45,'123456789012345678','北京','2010-04-01'),
       (10,'10','陈友谅','男',53,'123456789012345679','上海','2011-01-01'),
       (11,'11','张士诚','男',55,'12345678901234567X','江苏','2015-05-01'),
       (12,'12','常遇春','男',32,'12345678901234568X','北京','2004-02-01'),
       (13,'13','张三丰','男',88,'123456789012345671','江苏','2020-11-01'),
       (14,'14','灭绝','女',65,'123456789012345672','西安','2019-05-01'),
       (15,'15','胡青牛','男',70,'123456789012345673','西安','2018-04-01'),
       (17,'17','临时员1','男',28,'123456789012345475','江苏','2006-05-01'),
       (18,'18','临时员2','女',26,'123456789012345435','江苏','2006-09-01'),
       (16,'16','周芷若','女',18,null,'北京','2012-06-01');

-- 查看表内容

select * from bilibili.emp;

-- 注意工号1~9需要补4个0才能达到五位数。工号10~18只需要补3个0就可以达到五位数

update emp set workno = lpad(workno,5,'0'); #修改后会同步表中的数据，即真实的修改

-- 查看表内容

select * from bilibili.emp;

字符串函数

-- 常见的数值函数如下

-- ceil(x) 向上取整

select ceil(1.2);

-- floor(x) 向下取整

select floor(1.7);

-- mod(x,y) 返回x/y的模，注意返回的是余数

select mod(5,3);

-- rand() 返回0~1内的随机数

select rand();

-- round(x,y) 求参数x的四舍五入的值，保留y位小数

select round(5.54554,3);
select round(rand()*10,3);
select round(rand()*10000,0); #可以用于4位数随机验证码

案例: 生成一个六位数的随机验证码

思路:生成8位随机数向上取整,有可能是六位或7位，截取前六位

select ceil(rand()*100000000);
select substring(ceil(rand()*100000000),1,6);

-- 或者当位数不够时，我们直接采用右填充，位数不够就在末尾填充0，这种写法最精确

select ceil(rand()*1000000);
select rpad(ceil(rand()*100000000),6,0);

-- 其实这种写法跟上面那种一样精确。写法最简洁

select rand();
select substring(rand(),3,6); #从第三位开始，即从小数点后一位开始，截取6位

日期函数

-- 常见的日期函数如下:

-- curdate() 返回当前日期，年月日

select curdate();

-- curtime() 返回当前时间，时分秒

select curtime();

-- now() 返回当前日期和时间，年月日 时分秒

select now();

-- year(date) 获取指定date的年份

select year(now());

-- month(date) 获取指定date的月份

select month(now());

-- day(date) 获取指定date的日期

select day(now());

-- date_add(date,interval expr type) 返回一个日期/时间值加上一个时间间隔expr后的时间值，interval是间隔的意思

select date_add(now(),interval 70 day); #返回当前时间往后推70天的时间
select date_add(now(),interval 70 month ); #返回当前时间往后推70月的时间

-- datediff(date1,date2) 返回起始时间date1 和 结束时间date2之间的天数

select datediff('2022-12-27','2022-8-1'); #注意date2的时间要比date1早，不然会出现负数。日期求差值

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

use bilibili; #选择bilibili数据库，即可进行下面的代码演示
select * from emp;

select name,datediff(curdate(),entrydate) from emp order by datediff(curdate(),entrydate); #升序

-- 或者使用别名比较容易阅读，as可以省略

select name,datediff(curdate(),entrydate) as 'entrydays' from emp order by entrydays desc; #倒序

流程函数

-- 流程函数是很常用的函数，作用是实现条件筛选，提供语句的效率

-- if(value,t,f) -- 如果value为true，则返回t，否则返回f。value一般是条件表达式，而不能直接写true或false

select if(true,'ok','error');
select if(false,'ok','error');

-- ifnull(value1,value2) -- 如果value不为空，返回value1，否则返回value2

select ifnull('ok','Default');
select ifnull(null,'Default');

-- case when [val1] then [res1] ... else [default] end -- 如果val1为true，返回res1，... ,否则返回default默认值

select case when true then 'ok' else 'error' end;
select case when false then 'ok' else 'error' end;

-- case [expr] when [val1] then [res1] ... else [default] end -- 如果expr的值等于val1，返回res1，... 否则返回default默认值 -- 需求: 查询emp表的员工姓名和工作地址，地址如果是北京上海则展示为一线城市，其他地址均展示为二线城市

select
    name,
    case workaddress when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end
from emp;

案例: 统计班级各个学员的成绩

-- 展示的规则例如下

-- 1. >=85，展示优秀 -- 2. >=60，展示及格 -- 3. 否则，展示不及格

-- 新建学员成绩表

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);
select * from score;

-- 完成案例

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;