MySQL学习：Day08（函数）

一、函数

1.定义

2.常见的函数

二、字符串函数

1.常用字符串函数

2.格式

select 字符串函数(参数);

3.代码演示

# ------------------------------字符串函数-------------------------------
# concat字符串拼接
select concat('Hello',' World');
# lower全部转为小写
select lower('HELLO');
# upper全部转为大写
select upper('hello');
#lpad()左填充
select lpad('abcdef',8,'0');
# rpad右填充
select rpad('abcdef',8,'0');
# trim去掉字符串头部和尾部的空格
select trim('  a b cd ef  ');
# substring()返回从字符串从start位置起的len个长度的字符串
select substring('abcdef',2,5);
# (起始位置为1,不是0)

4.练习题

员工表为emp表，如下：

答案：

# 1.由于业需求变更,企业员工的工号,统一为5位数,目前不足5位数的全部在前面补0
update emp set workno =  lpad(workno,5,'0');
# update用于更新数据库中的表

三、数值函数

1.常见的数值函数

2.代码演示

# ------------------------------数值函数------------------------
# 向上取整
select ceil(2.1);   # 无论是否大于5都选择进一
# 向下取整
select floor(6.8);  # 大于5的也舍去
# 返回x/y的模
select mod(5,3);    # 相当于取模
# 返回0~1内的随机数
select rand();
# 求参数x四舍五入的值，保留y位小数
select round(1.2345678,3);

3.练习题

答案

# 2.通过数据库的函数,生成一个6位数的随机验证码
select round(rand()*1000000,0);

函数的嵌套。

因为rand()生成的是一个0~1的小数，所以乘1000000之后是一个6位数，然后再用round(x,y);使其小数点后有0位。lpad是当生成的数是0.0几的时候，只有5位，则需要在前面或者后面补0即可

四、日期函数

1.常用日期函数

2.代码演示

# ------------------------------日期函数------------------------
# 1.返回当前日
select curdate();
# 2.返回当前书间
select curtime();
# 3.返回当前时间和日期
select now();
# 4.获取指定date的年\月\日
select year('2021-10-23');
select month('2021-10-23');
select day('2021-10-23');
# 5.返回上一个日期/时间值加上时间间隔expr后的时间值
select date_add('2021-10-23',interval 20 day);      # 往后推20天,月用month,年用year
# 6.返回起始时间date1和结束时间date2之间的天数
select datediff(now(),'2004-05-19');

3.练习题

答案;

# 3.查询所有员工的入职天数,并根据入职天数倒序排序
select name,datediff(now(), entrydate) as JGtime from emp order by JGtime desc;
# 先获取到每个员工的name,再用datediff()计算入职天数
# as给入职时间取个别名JGtime，然后再用order by JGtime desc;进行倒序排序

五、流程函数

1.常见的流程函数

2.代码展示

# ------------------------------流程函数------------------------
# 1.if(a,b,c)：如果a是true，则返回b；如果为false，则返回c
select if(true, 'OK', 'Error');

# 2.ifnull(a,b)：如果a不为空，则返回a；如果a为空，则返回b
select ifnull('OK', 'Default');
select ifnull('','2');  # 此时返回空的字符串
select ifnull(null,'2');    # 此时返回2

# 3.case when then else end
# 需求：查询emp表的员工姓名和地址，如果是北京/上海，返回一线城市，其他的返回二线城市
select name,
    case workaddress when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end
from emp;

3.练习题

答案：

# 练习题
# 统计各个学员的成绩，>=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);

# 答案展示
select id,name,
    case when math>=80 then '优秀' when math<80&&math>=60 then '及格' else '不及格' end as math1,
    case when english>=80 then '优秀' when english<80&&english>=60 then '及格' else '不及格' end as english1,
    case when chinese>=80 then '优秀' when chinese<80&&chinese>=60 then '及格' else '不及格' end as chinese1
from score;

六、总结