【MySQL数据库】三、DML数据操作语言、DCL数据控制语言

一、DML数据操作语言

数据操作语言DML，对数据的操作，通过insert，delete，update，select关键字进行操作。

（一）增 insert into

(一) 添加一条数据

没有指定字段，给所有字段添加

insert into 表名 values(“字段”……)；
如：
insert into stu values("001","zhangsan","man",20);

表示给stu表中所有字段添加一条数据。如果给定的字段信息和字段个数不符，就会报错。

（二）指定字段名称添加

将数据放到指定字段，没有给定字段名称的字段默认为空

insert into stu(id,name,sex) values("002","lisi","woman");

（三）小批量添加数据

（）一对括号中写插入的一行数据，用逗号隔开每一行的数据

insert into stu values("003","wangwu","man",22),
					  ("004","zhaoliu","man",21),
					  ("005","cc","woman",21);

插入完成后，stu表中内容为：

（四）大批量插入

load 加载数据。

（二）删 delete from

(一) 删除整个表：

delete from stu;

表示所有行都满足条件，进行删除。

(二) 删除表中的一行，和where过滤条件配合使用

delete from stu where id="005";

删除id为005的一行。

（三）改 update set

如果没有过滤条件，表示修改所有数据，有过滤条件，只修改过滤之后的行。

（一）修改整个表

update stu set age=0;

（二）修改一行

update 表名 set 字段=新数据 where ……；
update stu set age=0 where id="002";

（四）查 select

MySQL是结构化查询数据库，所以在数据库中查询操作是很重要的。

【1】单表查询

1. 普通查询

1. 查询所有数据，通过 * 通匹的函数，即所有字段

select* from stu;

2. 通过字段查询数据：

select id,name,sex,age from stu;

3. 添加过滤条件查询，用where

select* from stu where id="001";

查询id为001的学生信息

2. 去重查询 distinct

distinct关键字加在字段名称之前

select distinct age from stu；

查询stu表中的年龄，去重。

3. 排序查询 order by

排序方式标识加在最后

1. 默认升序asc排序：

select distinct age from stu order by age;

2. 降序排序desc

select distinct age from stu order by age desc;

4. 分组查询 group by

查询一个学生的总成绩，成绩表中有学号，单科成绩，所以需要先根据学号分组，将学生分组累加，即可得到每一个学生的总成绩。如下表：

将三组进行成绩求和即可。

select id,sum(score) from result group by id;

Sum为聚集函数表示求和，下面我们详细的讲解。

【2】 多表查询

多表的连接都按照笛卡尔乘积的方式处理，现在存在两张表：

一张是学生信息表，一张是成绩表。

1. 等值查询

先连接再筛选：用左表中的每一条数据和右表的所有数据进行匹配，过滤出符合条件的，时间复杂度很高。

假如左表1亿条，右表1亿条，那么耗费3年左右。效率很低。

查询年龄小于20岁学生的不及格成绩。

select stu.id,score
from stu,result
where stu.id = result.id and
      age <20 and
	  score < 60;

采用等值查询：

• 先对两个表进行笛卡尔连接，形成一个新表格。
• 根据where条件进行筛选，需要筛选5*6=30次。

2. 连接查询

先对表进行条件筛选，再对筛选的表再进行笛卡尔乘积。即先缩小范围，再进行查询。

（1）外连接查询

保证任一 一方数据存在

1）左外连接查询 left join

左外连接查询：保证左表的过滤的结果必须全部存在，右表显示符合搜索条件的记录，不存在的地方补空，常用。

查询年龄小于20岁学生的不及格成绩。

//左连接
select a.id,score
from 
(select id,age from stu where age < 20) a #将查询结果命名为表a
left join
(select id,score from result where score < 60) b #将查询结果命名为表b
on a.id = b.id;#连接后的筛选条件

• 从学生表中过滤出年龄小于20岁信息，包含id，age重命名为a表。
• 从成绩表中过滤出成绩小于60的信息，包含id,score重命名为b表。
• 进行笛卡尔连接，需要根据on后面的条件筛选3*3=9次。
• 左表过滤的信息必须全部存在，右表中不存在的补空。

2）右外连接查询 right join

保证右表的过滤数据存在，左表只显示符合搜索条件的记录，不足的地方均为NULL。

查询年龄小于20岁学生的不及格成绩。

//右连接
select a.id,score
from 
(select id,age from stu where age < 20) a #将查询结果命名为表a
right join
(select id,score from result where score < 60) b #将查询结果命名为表b
on a.id = b.id;#连接后的筛选条件

先写出右表过滤的结果，再将左表过滤的信息填充，没有的补空。

3）全连接查询 union 【MySQL不支持全连接】

保证左表右表数据都存在

【1. union】

• 通过union连接的表它们分别单独取出的列数必须相同；
• 使用union 时，完全相等的行，将会被合并，所以具有自动去重的作用。

查询年龄小于20岁学生的不及格成绩。

select a.id,score
from 
(select id,age from stu where age < 20) as a #将查询结果命名为表a
union all
(select id,score from result where score < 60) as b #将查询结果命名为表b
on a.id = b.id; #连接后的筛选条件

MySQL不支持全连接，想要实现全连接，可以将左连接和右连接的结果进行聚合连接即可：

(select a.id,score
from 
(select id,age from stu where age < 20) a 
left join
(select id,score from result where score < 60) b 
on a.id = b.id)
union # 聚合
(select a.id,score
from 
(select id,age from stu where age < 20) a 
right join
(select id,score from result where score < 60) b 
on a.id = b.id);

【2. union all】

和union的区别就是不去重，显示重复数据。

（2）内连接查询 inner

组合两个表筛选的记录，返回左表，右表均有的记录，也就是返回两个表的交集（阴影）部分。

查询年龄小于20岁学生的不及格成绩。

//内连接
select a.id,score
from 
(select id,age from stu where age < 20) a #将查询结果命名为表a
inner join 
(select id,score from result where score < 60) b #将查询结果命名为表b
on a.id = b.id;#连接后的筛选条件

只输出左表，右表都有值的行，不会输出NULL值：

3. 聚合(联合）查询

和全外连接查询相似，但是聚合查询是将两张表的查询结果进行连接，全外连接是将表连接进行筛选。

现在有两张表：

有两种联合查询方式：

• union：自带去重，去掉重复的数据
• union all：不去重，显示重复数据

查询全校师生的信息：需要查询学生表和教师表，将学生表信息和教师表聚合查询。

• union自带去重，不会显示重复数据：

select* from stu union select* from teach;

• 不去重，显示重复数据

select* from stu union all select* from teach;

二、聚合函数 && 分组查询 、where && having

Group by分组的的真正作用在于与各种聚合函数配合使用。聚合函数，也叫做组合函数：对值集合进行操作，返回单个值。 主要有：

聚合函数	含义
avg(x)	对字段x这一列求平均值
count(x)	求字段x的行数
max(x)	求字段x列的最大值
min(x)	求字段x列的最小值
sum(x)	求字段x列的总和

【having 子句】

having和where的作用都是过滤信息的，但是它们的使用不同：

• where是在分组前进行条件过滤的，即过滤指定行所对应的条件。
• having字句是在分组group by后进行条件过滤的，即过滤指定组所对应的条件。
• where字句中不能使用聚合函数。
• having字句中可以使用聚合函数。

三、DCL数据控制语言

（一）创建新用户

root管理员，可以创建用户，创建为本地localhost，不能远程登录，远程登录用%标识。

1. root用户下创建本地登录新用户CC；

create user "CC"@"localhost";

2. 退出数据库，用新用户进行登录：

只能查看到固有的配置表，不能看到所有表，

原因： 新用户对于任何库，任何表都没有操作权限，所以看不到表。只有管理员有授权权限，它可以给其他用户赋予授权权限，

（二）给新用户授权grant

root用户下使用语句进行权限的授予：

grant 权限 on 库名 to 用户

给用户授予库的何种权限，可以授予单个查询权限，部分权限，所有权限，权限之间用逗号【，】间隔即可。

对新用户授予的权限有：

• select
• update
• insert
• delete
• create
• drop删除表权限
• ALL所有权限
• *表示所有

with grant option表示被授予者可以将权限再赋予其他用户。

如：root管理员将Review数据库中所有表的查询权限授予给CC用户，如果加上with grant option则CC用户可以将查询权限授予给其他用户。

grant select on Review.* to "CC" ;

root用户下进行授予，授予完成后，CC可以查看到Review数据库，可以使用Review数据库，select查询所有的表数据，但是它不能进行修改等操作，因为只给了它查询权限。

（三）回收新用户的权限revoke

使用语句：

revoke 权限 on 库名 from 用户；

收回用户对库的何种权限。回收单个查询权限，部分权限，所有权限，权限逗号间隔。

如：回收CC对于Review数据的查询，修改权限

revoke select,update on Review.* from "CC";

【反祖问题：】

MySQL不能进行反祖，即越级回收权限，只能谁赋予的谁回收，即root回收u1，u1回收u2，u4此时谁的权限都不能回收。

root将所有权限给u1，u1可以再给其他人授予权限，u1给u2，u2给u3，u3给u4，u4能不能回收u2，或者u2，u3的权限？如果可以的话，那么u4回收u2,u3的权限是u2赋予的，连带着u3也被回收，u4被回收，那么u4自己也没权限了。而且如果允许的话，那么u4回收root的权限，导致所有用户都没有权限了,所以MySQL禁止出现越级回收，防止出现反组问题

四、常见查询语句练习

【1. 模糊查询】 查询“李”姓老师的信息

select* from teacher name like "李%";

【2. 】求平均分大于60分的学生编号和平均成绩：

select id,AVG(score) avg_score
from result
group by id
having AVG(score)>60;

【3. 】 求”p01”课程大于“p02“课程成绩的所有学生信息

成绩表：

从表中可以看出来001，002学号的学生p01课程成绩大于p02。有以下几种查询方法：

1. 等值查询，子查询：

• 进行子查询，即先通过表result得到满足条件的id，再去stu学生表中查询信息。
• 将表result进行自连接，得到满足条件的id
• 再stu表中查询。

select*
from stu
where id in
(select a.id
from result a,result b
where a.id=b.id and
	  a.pid="p01" and
	  b.pid="p02" and
	  a.score>b.score
);

2. 内连接查询，子查询

• 先得到符号条件的id，到stu表中查询。
• 从result中查询p01课程作为左表，查询p02课程作为右表，进行内连接，筛选条件为学号相等，左表的成绩大于右表，得到符号条件的id。
• 从stu表中查询id学号的信息。

select *  #在stu表中查询id
from stu
where id in
(
select a.id #得到id
from 
(select id,score from result where pid="p01")a #左表
inner join #内连接
(select id,score from result where pid="p02")b #右表
on a.id=b.id and a.score>b.score #筛选条件
);

3. 内连接查询：

• 先对result进行内连接，得到p01大于p02课程的学生的id，将结果表命名为t1。
• 用t1和学生表内连接，筛选条件为id相等，得到学生详细信息。

select *
from 
stu s1
inner join
(
select a.id 
from 
(select id,score from result where pid="p01")a
inner join
(select id,score from result where pid="p02")b
on a.id=b.id and a.score>b.score
) t1
on s1.id=t1.id;

加油哦！🍦。