MySQL数据库基础知识（三）《函数》

学习目标：

• 学习mysql数据库常用内置函数
• 函数演示
• 实例演示
• 数据控制语言-DCL

---

学习内容：

1. 字符串函数

• 函数演示：

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

-- lower
select lower('Hello');

-- upper
select upper('Hello');

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

-- rpad
select rpad('01',5,'-');

-- rtrim
select rtrim(' Hello MySQL ');

-- substring
select substring('Hello mysql',1,5);

运行结果：

• 实例演示

-- 1、由于业务需求变更，企业员工工号统一为5位数，目前不足5位数的在前面补0，例如1号员工工号00001
select lpad(workno,5,'0'),name
from emp;-- 查询但未改变

update emp set workno=lpad(workno,5,'0');-- 更改数据表

运行结果：
更改源数据后，查看数据表如下：

2. 数值函数

• 常见函数
  

• 函数演示

-- ceil
select ceil(5.6);

-- floor
select floor(5.6);

-- mod
select mod(7,2);

-- rand
select rand();

-- round
select round(5.233335,2);

运行结果：

• 实例演示

-- 1、通过数据库的函数，生成一个6位数的随机验证码
select round(rand(),6)*1000000;
-- 此处注意点：如果生成随机数小于0.1，则只会有5位以下数，因此可使用以下函数改进
select lpad(round(rand(),6)*1000000,6,'0');

运行结果：
改进后生成的验证码可解决生成随机数小于0.1时验证码小于6位数的bug

3. 日期函数

• 常见函数
  

• 函数演示

-- curtime
select curtime();

-- curdate
select curdate();

-- now
select now();

-- year（date）获取指定日期的年份
select year('2024-6-18');

-- month获取
select month('2024-6-18');

-- day
select day('2024/6/18');

-- date_add往指定日期推一个时间段后的值
select date_add('2024-6-18',interval 20 day );

-- datediff
select datediff('2002.6.18','2001.11.2');

运行结果：

• 实例演示

-- 1、查询所有员工入职天数，并根据入职天数倒序排列
select *
from emp;

select datediff(curdate(),hiredate) as day_hire,name
from emp
order by day_hire desc ;

运行结果：

4. 流程函数

• 函数演示

-- if
select if(true,'yes','no');
select if(false,'yes','no');

运行结果：

-- ifnull
select ifnull(1,'0');
select ifnull(null,'0');

运行结果：

-- case when [] then [] …… else [] end
-- 查询emp表姓名、员工工作地址（北京/上海---一线城市，其他---二线城市）
select name,
       location,
       case when location in ('北京','上海') then '一线城市' else '二线城市' end
from emp;

运行结果：

• 实例演示
  数据准备

create table score(
    id varchar(10),
    name varchar(10),
    math int,
    chinese int,
    english int
);
insert into score values ('1','Tom',95,80,67),('2','Rose',56,66,90),('3','Jack',23,98,76);

运行结果：

-- 展示学员成绩，展示规则如下
-- >=85优秀， >=60及格， 否则展示不及格
select id,
       name,
       case when math>=85 then '优秀' when math>=60 then '及格' else '不及格' end ,
       case when chinese>=85 then '优秀' when chinese>=60 then '及格' else '不及格' end,
       case when english>=85 then '优秀' when english>=60 then '及格' else '不及格' end
from score;

运行结果：

5. 管理用户

DCL为数据控制语言，用来管理数据库用户、控制数据库的访问权限。

-- 查询用户
use mysql;
select *
from user;

-- 创建用户
create user '用户名'@'主机名' identified by '密码';

-- 修改用户密码
alter user '用户名'@'主机名' identified with mysql_native_password by '新密码';

-- 删除用户
drop user  '用户名'@'主机名';

例如:

-- 创建用户itcase，只能够在当前主机localhost访问，密码123456；
create user 'itcase'@'localhost' identified by '123456';

-- 创建用户heima，可以在任意主机访问该数据库，密码123456
create user 'heima'@'%' identified by '123456';

-- 修改用户heima访问密码为1234
alter user 'heima'@'%' identified with mysql_native_password by '1234';

-- 删除itcase@localhost用户
drop user 'itcase'@'localhost';

6. 权限控制

• 常用权限：
  
  1查询权限

show grants for '用户名 '@'主机名';

2授予权限

grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';

3撤销权限

revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';

例如：

-- 查询用户权限
show grants for 'heima'@'%';

-- 授予权限
grant all on itcase.* to 'heima'@'%';

-- 撤销权限
revoke all on itcase.* from 'heima'@'%';

授予权限后，登录heima用户，查询到的结果：

撤销用户权限后，查询数据表结果

总结

• 函数可以用于日常查询表时，进行一些数据的预处理，方便用户进行理解；并且可以将数据库内的数据按照我们的需求进行整理，进行更高级的操作。
• 权限管理语言可以帮助数据库管理者进行各个用户的创建、管理以及删除，对于数据库的数据安全有重要作用。

学习时间：

• 周一至周六晚上 7 点—晚上9点
• 周日下午 3 点-下午 6 点

---

学习产出：

• 技术笔记 2 遍
• CSDN 技术博客 1 篇