DCL

DCL英文全称是Data Control Language(数据控制语言),用来管理数据库用户、控制数据库的访问权限。

2.7.1 管理用户

1).查询用户

1

select * from mysql.user;

查询的结果如下：

其中 Host代表当前用户访问的主机, 如果为localhost, 仅代表只能够在当前本机访问，是不可以远程访问的。 User代表的是访问该数据库的用户名。在MySQL中需要通过Host和User来唯一标识一个用户。

2).创建用户

1

CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

3).修改用户密码

1

ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码' ;

4).删除用户

1

DROP USER '用户名'@'主机名' ;

注意事项：
• 在MySQL中需要通过用户名@主机名的方式，来唯一标识一个用户。
• 主机名可以使用 % 通配。
• 这类SQL开发人员操作的比较少，主要是DBA（ Database Administrator 数据库管理员）使用。

案例:

A. 创建用户itcast, 只能够在当前主机localhost访问, 密码123456;

1

create user 'itcast'@'localhost' identified by '123456';

B. 创建用户heima, 可以在任意主机访问该数据库, 密码123456;

1

create user 'heima'@'%' identified by '123456';

C. 修改用户heima的访问密码为1234;

1

alter user 'heima'@'%' identified with mysql_native_password by '1234';

D. 删除 itcast@localhost 用户

1

drop user 'itcast'@'localhost';

2.7.2 权限控制

MySQL中定义了很多种权限,但是常用的就以下几种:

上述只是简单罗列了常见的几种权限描述，其他权限描述及含义，可以直接参考官方文档。

1).查询权限

1

SHOW GRANTS FOR '用户名'@'主机名' ;

2).授予权限

1

GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';

3).撤销权限

1

REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';

注意事项：

• 多个权限之间，使用逗号分隔

• 授权时，数据库名和表名可以使用＊进行通配，代表所有。

案例：

A.查询＇heima＇＠＇％＇用户的权限

1

show grants for 'heima'@'%';

B.授予＇heima＇＠＂％＇用户itcast数据库所有表的所有操作权限

1

grant all on itcast.* to 'heima'@'%';

C.撤销＇heima＇＠％”用户的itcast数据库的所有权限

1

revoke all on itcast.* from 'heima'@'%';

3.函数

函数 是指一段可以直接被另一段程序调用的程序或代码。 也就意味着,这一段程序或代码在MySQL中已经给我们提供了,我们要做的就是在合适的业务场景调用对应的函数完成对应的业务需求即可。 那么,函数到底在哪儿使用呢?

我们先来看两个场景:

1). 在企业的OA或其他的人力系统中,经常会提供的有这样一个功能,每一个员工登录上来之后都能够看到当前员工入职的天数。 而在数据库中,存储的都是入职日期,如 2000-11-12,那如果快速计算出天数呢?

2). 在做报表这类的业务需求中,我们要展示出学员的分数等级分布。而在数据库中,存储的是学生的分数值,如98/75,如何快速判定分数的等级呢?

其实,上述的这一类的需求呢,我们通过MySQL中的函数都可以很方便的实现 。

MySQL中的函数主要分为以下四类: 字符串函数、数值函数、日期函数、流程函数。

3.1 字符串函数

MySQL中内置了很多字符串函数,常用的几个如下:

演示如下:

A. concat : 字符串拼接

1

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

B. lower : 全部转小写

1

select lower('Hello');

C. upper : 全部转大写

1

select upper('Hello');

D. lpad : 左填充

1

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

E. rpad : 右填充

1

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

F. trim : 去除空格

1

select trim(' Hello MySQL ');

G. substring : 截取子字符串

1

select substring('Hello MySQL',1,5);

案例：

由于业务需求变更，企业员工的工号，统一为5位数，目前不足5位数的全部在前面补0。比如：1号员工的工号应该为00001。

1

update emp set workno = lpad(workno, 5, '0');

处理完毕后，具体的数据为：

3.2 数值函数

常见的数值函数如下：

A. ceil:向上取整

1

select ceil(1.1);

B. floor:向下取整

1

select floor(1.9);

C. mod:取模

1

select mod(7,4);

D. rand:获取随机数

1

select rand();

E. round:四舍五入

1

select round(2.344,2);

案例:

通过数据库的函数,生成一个六位数的随机验证码。

思路: 获取随机数可以通过rand()函数,但是获取出来的随机数是在0-1之间的,所以可以在其基础上乘以1000000,然后舍弃小数部分,如果长度不足6位,补0

1

select lpad(round(rand()*1000000 , 0), 6, '0');

3.3 日期函数

常见的日期函数如下:

演示如下:

A. curdate:当前日期

1

select curdate();

B. curtime:当前时间

1

select curtime();

C. now:当前日期和时间

1

select now();

D. YEAR , MONTH , DAY:当前年、月、日

1
2
3
4
5

select YEAR(now());

select MONTH(now());

select DAY(now());

E. date_add：增加指定的时间间隔

1

select date_add(now(), INTERVAL 70 YEAR );

F. datediff:获取两个日期相差的天数

1

select datediff('2021-10-01', '2021-12-01');

案例:

查询所有员工的入职天数,并根据入职天数倒序排序。

思路: 入职天数, 就是通过当前日期 - 入职日期,所以需要使用datediff函数来完成。

1
2
3

select name, datediff(curdate(), entrydate) as 'entrydays' from emp order by

entrydays desc;

3.4 流程函数

流程函数也是很常用的一类函数,可以在SQL语句中实现条件筛选,从而提高语句的效率。

演示如下:

A. if

1

select if(false, 'Ok', 'Error');

B. ifnull

1
2
3
4
5

select ifnull('Ok','Default');

select ifnull('','Default');

select ifnull(null,'Default');

C. case when then else end
需求: 查询emp表的员工姓名和工作地址 (北京/上海 —-> 一线城市 , 其他 —-> 二线城市)

1
2
3
4
5
6

select

	name,
	( case workaddress when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end ) as '工作地址'

from emp;

案例:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

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语句如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

select
	id,
	name,

(case when math >= 85 then '优秀' when math >=60 then '及格' else '不及格' end )

'数学',

(case when english >= 85 then '优秀' when english >=60 then '及格' else '不及格'

end ) '英语',

(case when chinese >= 85 then '优秀' when chinese >=60 then '及格' else '不及格'

end ) '语文'

from score;

MySQL的常见函数我们学习完了,那接下来,我们就来分析一下,在前面讲到的两个函数的案例场景,思考一下需要用到什么样的函数来实现?

1). 数据库中, 存储的是入职日期, 如 2000-01-01, 如何快速计算出入职天数呢?

答案: datediff

2). 数据库中, 存储的是学生的分数值, 如98、75, 如何快速判定分数的等级呢?

答案: case … when …

删除/更新行为

添加了外键之后,再删除父表数据时产生的约束行为,我们就称为删除/更新行为。具体的删除/更新行为有以下几种:

1
2
3

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES

主表名 (主表字段名) ON UPDATE CASCADE ON DELETE CASCADE;

演示如下:

由于NO ACTION 是默认行为，我们前面语法演示的时候，已经测试过了，就不再演示了，这里我们再演示其他的两种行为：CASCADE、SET NULL。

1). CASCADE

1
2
3

alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references

dept(id) on update cascade on delete cascade ;

A.修改父表id为1的记录，将id修改为6

我们发现，原来在子表中dept＿id值为1的记录，现在也变为6了，这就是cascade级联的效果。

在一般的业务系统中，不会修改一张表的主键值。

B.删除父表id为6的记录

我们发现，父表的数据删除成功了，但是子表中关联的记录也被级联删除了。

2). SET NULL

在进行测试之前，我们先需要删除上面建立的外键 fk_emp_dept_id。然后再通过数据脚本，将emp、dept表的数据恢复了。

1
2
3

alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references

dept(id) on update set null on delete set null ;

接下来，我们删除id为1的数据，看看会发生什么样的现象。

我们发现父表的记录是可以正常的删除的，父表的数据删除之后，再打开子表 emp，我们发现子表emp 的dept＿id字段，原来dept＿id为1的数据，现在都被置为NULL了。

这就是SET NULL这种删除／更新行为的效果。

5.5 自连接

5.5.1 自连接查询

自连接查询,顾名思义,就是自己连接自己,也就是把一张表连接查询多次。我们先来学习一下自连接的查询语法:

1

SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ... ;

而对于自连接查询,可以是内连接查询,也可以是外连接查询。

案例:
A. 查询员工 及其 所属领导的名字

表结构: emp

1

select a.name , b.name from emp a , emp b where a.managerid = b.id;

B. 查询所有员工 emp 及其领导的名字 emp , 如果员工没有领导, 也需要查询出来 表结构: emp a , emp b

1

select a.name '员工', b.name '领导' from emp a left join emp b on a.managerid = b.id;

注意事项：
在自连接查询中，必须要为表起别名，要不然我们不清楚所指定的条件、返回的字段，到底是哪一张表的字段。

5.5.2 联合查询

对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集。

1
2
3
4
5

SELECT 字段列表 FROM 表A ...

UNION [ ALL ]

SELECT 字段列表 FROM 表B ....;

• 对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。

• union all 会将全部的数据直接合并在一起，union 会对合并之后的数据去重。

案例：

A.将薪资低于 5000 的员工，和年龄大于 50 岁的员工全部查询出来.

当前对于这个需求，我们可以直接使用多条件查询，使用逻辑运算符or 连接即可。那这里呢，我们也可以通过union／union all来联合查询.

1
2
3
4
5

select * from emp where salary < 5000

union all

select * from emp where age > 50;

union all查询出来的结果，仅仅进行简单的合并，并未去重。

1
2
3
4
5

select * from emp where salary < 5000

union

select * from emp where age > 50;

union 联合查询，会对查询出来的结果进行去重处理。

注意：

如果多条查询语句查询出来的结果，字段数量不一致，在进行union／union all联合查询时，将会报错。如：