MySQL 子查询、连接查询、DCL

发表于 分类于
MySQL 子查询、连接查询、DCL

子查询、连接查询、DCL

一、表连接查询

  • 完成多表操作的两种方式：

  1. 通过表连接查询

  2. 通过子查询

1.1 多表查询

  • 数据准备
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
create database db03;

use db03;

-- 创建部门表
create table dept(
	id int primary key auto_increment,
	name varchar(20)
);

insert into dept(name) values ('研发部'),('销售部'),('财务部');

-- 创建员工表
create table emp (
	id int primary key auto_increment,
	name varchar(10),
	addr varchar(30), 
	age int, 
	sex char(1), 
	dept_id int,
	foreign key (dept_id) references dept(id) -- 外键，关联部门表(部门表的主键)
);

INSERT INTO `emp` VALUES (1, '小明', '南昌', 24, '男', 1);
INSERT INTO `emp` VALUES (2, '小红', '九江', 20, '女', 1);
INSERT INTO `emp` VALUES (3, '小兰', '抚州', 19, '女', 2);
INSERT INTO `emp` VALUES (4, '小龙', '宜春', 18, '男', 2);
INSERT INTO `emp` VALUES (5, '小军', '赣州', 23, '男', 3);

1.2 笛卡尔积：

1.2.1 笛卡尔积现象

1.2.1.1 什么是笛卡尔积现象

  • 什么是笛卡尔积：
1
2
-- 需求：查询所有的员工和所有的部门
select * from dept,emp;

结果如下：

如果左表是部门表，右表是员工表，左表中每一行记录与右表中的每一行记录全都匹配一次。

结果的行数=左表中行数x右表中行数

  • 结果分析：

1.2.1.2 如何清除笛卡尔积现象的影响

我们发现不是所有的数据组合都是有用的，只有员工表.dept_id = 部门表.id

的数据才是有用的。所以需要通过条件过滤掉没用的数据。

1
2
3
4
5
6
-- 设置过滤条件
-- 指定过滤条件 主表.主键=从表.外键
select * from dept,emp where dept.id = emp.dept_id;

-- 可以给表起别名
select * from dept d,emp e where d.id = e.dept_id;

  • 以上过滤以后的结果称为隐式内连接

1.3 内连接

用左边表的记录去匹配右边表的记录，如果符合条件的则显示

1.3.1 隐式内连接

  • 隐式内连接：看不到JOIN关键字，条件使用WHERE指定
1
2
3
select 列名 from 左表, 右表 where 主表.主键=从表.外键

select * from dept,emp where dept.id=emp.dept_id;
  • 上面的案例是就隐式内连接

1.3.2 显式内连接

无论是显示内连接还是隐式内连接查询结果是一样的，只是写法不同。

  • 显式内连接：使用INNER JOIN ... ON语句, 可以省略INNER
1
select 列名 from 左表 inner join 右表 on 主表.主键=从表.外键

案例：查询小龙的信息，显示员工id，姓名，地址，年龄和所在的部门名称，我们发现需要联合2张表同时才能查询出需要的数据，使用内连接

  • 1）确定需要查询的表（dept、emp表）

2）确定连接条件（过滤掉不需要的数据，消除笛卡尔积）

3）确定查询条件，我们查询的是小龙的信息，员工表.name=’小龙’

4）确定查询的列名（员工id，姓名，地址，年龄，部门名称）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- 查询小龙的信息,显示员工id,姓名,地址,年龄和所在的部门名称

-- 我们发现需要联合2张表同时才能查询出需要的数据，使用内连接
-- 1. 确定查询哪些表，得到笛卡尔积
select * from dept inner join emp;

-- 2. 设置表连接的条件
select * from dept d inner join emp e on d.id = e.dept_id;

-- 3. 确定查询条件，我们查询的是小龙的信息，员工表.name='小龙'
select * from dept d inner join emp e on d.id = e.dept_id where e.name='小龙';

-- 4. 员工id，姓名，地址，年龄，部门名称
select 
	e.id 员工id,
	e.name 姓名,
	e.addr 地址,
	e.age 年龄,
	d.name 部门名称		-- 部门名称是dept表的
from dept d 
inner join emp e 
on d.id = e.dept_id 
where e.name='小龙';

1.3.3 内连接查询步骤：

  1. 确定查询哪些表

  2. 确定表连接的条件，通常是 主表.主键=从表.外键（消除笛卡尔积）

  3. 确定查询条件

  4. 确定查询的列

1.4 左外连接

1.4.1 左连接的语法：

  • 左外连接：使用LEFT OUTER JOIN ... ON，OUTER可以省略
1
select 列名 from 左表 left join 右表 on 表连接条件

1.4.2 左连接的案例：

需求：在部门表中增加一个行政部，需要查询所有的部门和员工，将部门表设置成左表，员工表设置成右表

1
2
3
4
5
6
7
8
select * from dept;

insert into dept (name) values ('行政部');

-- 使用内连接查询

-- 需要查询所有的部门和员工，无论这个部门下有没有员工
select * from dept inner join emp on dept.id = emp.dept_id;

结果如下：

使用左外连接查询：

1
select * from dept left join emp on dept.id = emp.dept_id;

左连接的概念：查询的数据以左表为准，即使在其他表中没有匹配的记录也会显示出来；

2.5 右外连接

2.5.1 右连接的语法：

  • 右外连接：使用RIGHT OUTER JOIN ... ON，OUTER可以省略

语法：

1
select 列名 from 左表 right join 右表 on 表连接条件

2.5.2 右连接的案例：

需求：在员工表中增加一个员工，但该员工还未分配部门

1
2
3
4
5
6
7
8
9
select * from emp;

-- 不在任何一个部门
INSERT INTO `emp` VALUES (6, '小赵', '吉安', 26, '男', null);

-- 希望员工的信息全部显示出来

-- 使用内连接查询
select * from dept inner join emp on dept.id = emp.dept_id;

内连接查询结果：

使用右外连接查询：

1
2
-- 使用右外连接查询
select * from dept right join emp on dept.id = emp.dept_id;

右连接的概念：查询的数据以右表为准，即使在其他表中没有匹配的记录也会显示出来；

2.6 全连接

到目前为止我们学过了内连接、外连接；

  • 1）内连接：分为显示内连接和隐式内连接，查询的数据必须左右两表都存在才会显示
  • 2）左连接：又叫左外连接，查询的数据以左表为准，即使左表中的数据在其他表中没有匹配也会查询出来，大不了以null补齐
  • 3）右连接：又叫右外连接，和左外连接相反，查询的数据以右表为准，即使右表中的数据在其他表中没有匹配也会查询出来，大不了以null补齐

我们知道左连接是无论如何左表的数据都能够显示全，右连接是右表的数据无论如何都能够显示全面，那么如果我们希望左表和右表的数据都能够显示全面呢（在对方表中没有匹配的数据就以null补齐）？这种连接查询我们称之为全连接（full join），但是很遗憾，MySQL并没有提供全连接，但Oracle支持；

虽然MySQL不支持全连接，但是我们可以利用MySQL提供的其它功能来完成全连接的功能：

left join + right join

1
2
3
select * from dept d left join emp e on d.id=e.dept_id
union 
select * from dept d right join emp e on d.id=e.dept_id;

全连接查询的结果如下：

union关键字可以将两个或多个SQL语句的结果集拼接成一个结果集，前提是这些SQL语句的结果集列数必须相同；

union关键字自带去重功能，即去除重复的数据：

如果需要保留重复的数据则可以使用union all关键字来连接多个SQL语句的结果集：

三、子查询

  • 子查询的概念：
  1. 一个查询语句结果做为另一个查询语句的条件
  2. 查询语句有嵌套，里面的查询称为子查询，外面的查询称为父查询
  3. 子查询要使用括号括起来

准备数据：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
drop table if exists emp;

-- 创建员工表
create table emp (
	id int primary key auto_increment,
	name varchar(10),
	addr varchar(30), 
	age int, 
	sex char(1), 
	dept_id int,
	foreign key (dept_id) references dept(id) -- 外键，关联部门表(部门表的主键)
);

INSERT INTO `emp` VALUES (1, '小明', '南昌', 24, '男', 1);
INSERT INTO `emp` VALUES (2, '小红', '九江', 20, '女', 1);
INSERT INTO `emp` VALUES (3, '小兰', '抚州', 19, '女', 2);
INSERT INTO `emp` VALUES (4, '小龙', '宜春', 18, '男', 2);
INSERT INTO `emp` VALUES (5, '小军', '赣州', 23, '男', 3);
INSERT INTO `emp` VALUES (6, '小聪', '吉安', 23, '男', 3);
INSERT INTO `emp` VALUES (7, '小陈', '上饶', 25, '男', 2);
INSERT INTO `emp` VALUES (8, '小李', '鹰潭', 28, '男', 3);
INSERT INTO `emp` VALUES (9, '小孙', '萍乡', 20, '男', 1);
INSERT INTO `emp` VALUES (10, '小王', '景德镇', 24, '男', 1);
INSERT INTO `emp` VALUES (11, '小赵', '新余', 29, '女', 3);

3.1 查询的结果

  • 1）查询是单行单列
1
select max(age) from emp;

  • 2）查询是多行单列
1
select id from emp where dept_id=1;

  • 3）查询是多行多列的情况
1
select * from emp where dept_id=1;

3.2 结果为单行单列

如果子查询的结果是一个值，父查询使用比较运算符：> 、 <、=、<>

1
select * from 表名 where 字段名 = (子查询);

需求：查询年龄最高的员工是谁？

1
2
3
4
5
6
7
8
-- 1. 查询年龄大的员工的详细信息
select max(age) from emp;

-- 2. 根据最高年龄到员工表查询到对应的员工信息
select * from emp where age = 26;

-- 使用子查询
select * from emp where age = (select max(age) from emp);

需求：查询年龄大于”小明”年龄的员工

1
2
3
4
5
-- 1. 首先查询小明的年龄是多少
select age from emp where name='小明';

-- 2. 查询大于这个年龄的员工
select * from emp where age > (select age from emp where name='小明');

3.3 结果为多行单列

子查询结果是多行单列的时候，子查询的结果相当于一个集合或数组。父查询要使用in/any/all这些关键字

1
select * from 表名 where 字段名 IN/ANY/ALL(子查询)

需求1：查询年龄大于23的员工的部门信息

1
2
3
4
5
6
7
8
9
10
-- 1. 首先查询年龄大于23岁的员工所在的部门id
select dept_id from emp where age > 23;

-- 2. 再查询在这些部门id中部门的名字

-- ERROR 1242 (21000): Subquery returns more than 1 row 子查询返回了多行数据
select * from dept where id = (select dept_id from emp where age > 23);

-- 采用in 取结果集中的数据 in (1,2,3)
select * from dept where id in (select dept_id from emp where age > 23);

结果如下：

需求2：查询年龄大于1号部门所有员工的人

1
2
3
4
5
6
7
8
9
10
11
-- 1. 查询1号部门所有员工的年龄，得到多行单列
select age from emp where dept_id=1;

-- 2. 当结果集返回多行时不能使用比较运算符
-- ERROR 1242 (21000): Subquery returns more than 1 row
select * from emp where age > (select age from emp where dept_id=1);

select * from emp where age > all (select age from emp where dept_id=1);

-- 比1号部门任意一个大就行
select * from emp where age > any (select age from emp where dept_id=1);

3.4 子查询结果为多行多列

查询结果为多行多列的时候，可以当做一张虚拟表

如果子查询的结果是多行多列，父查询可以将这个查询结果做为一个虚拟表，进行第2次查询。不是放在where后面，而是放在from的后面。

1
select 列名 from 表, (子查询的结果) 别名 where 条件

子查询作为表需要取别名，否则这张表没有名称则无法访问表中的字段

需求：查询出年龄大于23岁的员工信息和部门名称

1
2
3
4
5
6
7
8
9
10
-- 1. 在员工表中查询年龄大于23岁的员工
select * from emp where age > 23;

-- 2.查询所有的部门信息，与上面的虚拟表中的信息组合，找出所有部门id等于的dept_id
select * from dept d, emp e where d.id = e.dept_id; -- 隐式内连接

select e.*,d.name 部门名称 from dept d, (select * from emp where age > 23) e where d.id = e.dept_id; -- 隐式内连接

-- 也可以使用表连接
select e.*,d.name 部门名称 from dept d, emp e where d.id = e.dept_id and e.age > 23;

3.5 子查询小结

  1. 单行单列：父查询使用比较运算符(>，<，=)

  2. 多行单列：父查询使用关键字：in/any/all，只要是单列的情况放在where后面。

  3. 多行多列：父查询放在from后面做为虚拟表并起别名再次进行查询

四、多表查询的案例

4.1 准备数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
-- 图书表
DROP TABLE IF EXISTS `book`;

CREATE TABLE `book`  (
  `id` int(11) NOT NULL COMMENT '图书id',
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '书名',
  `price` decimal(10, 2) NULL DEFAULT NULL COMMENT '定价',
  `publish` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '出版社',
  `publish_date` datetime(0) NULL DEFAULT NULL COMMENT '出版日期',
  PRIMARY KEY (`id`) USING BTREE
) ;

INSERT INTO `book` VALUES (1, 'Java入门到精通', 49.80, '机械工业出版社', '2020-10-28 00:00:00');
INSERT INTO `book` VALUES (2, '高性能MySQL', 68.90, '北京大学出版社', '2021-08-05 00:00:00');
INSERT INTO `book` VALUES (3, 'Java并发编程实战', 65.50, '电子工业出版社', '2010-07-06 00:00:00');
INSERT INTO `book` VALUES (4, '深入理解Java虚拟机', 88.90, '清华大学出版社', '2013-03-14 00:00:00');
INSERT INTO `book` VALUES (5, '图解TCP/IP', 76.90, '机械工业出版社', '2014-10-28 00:00:00');

-- 学校表
DROP TABLE IF EXISTS `university`;
CREATE TABLE `university`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学校id',
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '学校名称',
  `location` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '学校地址',
  `short_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '学校简称',
  `info` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '学校简介',
  PRIMARY KEY (`id`) USING BTREE
) ;

INSERT INTO `university` VALUES (1, '中国科学技术大学', '合肥', '中科大', '世界一流大学');
INSERT INTO `university` VALUES (2, '西安交通大学', '西安', '西安交大', '世界知名高水平大学');
INSERT INTO `university` VALUES (3, '江西财经大学', '南昌', '江财', '中国一流大学');
INSERT INTO `university` VALUES (4, '华南农业大学', '广州', '华农', '中国一流大学');
INSERT INTO `university` VALUES (5, '长沙理工大学', '长沙', '长沙理工', '中国一流大学');

-- 学生表
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `id` int(11) NOT NULL COMMENT 'id',
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '姓名',
  `age` int(11) NULL DEFAULT NULL COMMENT '年龄',
  `sex` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '性别',
  `address` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '籍贯',
  `u_id` int(11) NULL DEFAULT NULL COMMENT '学校id',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `school_s_id_fk`(`u_id`) USING BTREE,
  CONSTRAINT `school_s_id_fk` FOREIGN KEY (`u_id`) REFERENCES `university` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ;

INSERT INTO `student` VALUES (1, '小明', 20, '男', '辽宁辽阳', 1);
INSERT INTO `student` VALUES (2, '小红', 22, '女', '山东威海', 3);
INSERT INTO `student` VALUES (3, '小军', 27, '男', '山西吕梁', 3);
INSERT INTO `student` VALUES (4, '小龙', 24, '男', '河北保定', 2);
INSERT INTO `student` VALUES (5, '小丽', 22, '女', '陕西延安', 4);
INSERT INTO `student` VALUES (6, '小辉', 19, '男', '河南洛阳', 5);

-- 借阅表
DROP TABLE IF EXISTS `borrow`;

CREATE TABLE `borrow`  (
  `s_id` int(11) NOT NULL COMMENT '学生id',
  `b_id` int(11) NULL DEFAULT NULL COMMENT '图书id',
  `borrow_date` datetime(0) NULL DEFAULT NULL COMMENT '借阅日期',
  `count` int(11) NULL DEFAULT NULL COMMENT '借阅数量',
  INDEX `stu_s_id_fk`(`s_id`) USING BTREE,
  INDEX `book_b_id_fk`(`b_id`) USING BTREE,
  CONSTRAINT `book_b_id_fk` FOREIGN KEY (`b_id`) REFERENCES `book` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `stu_s_id_fk` FOREIGN KEY (`s_id`) REFERENCES `student` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ;

INSERT INTO `borrow` VALUES (1, 2, '2020-01-08 21:22:39', 1);
INSERT INTO `borrow` VALUES (3, 1, '2020-04-15 21:30:30', 2);
INSERT INTO `borrow` VALUES (3, 3, '2020-06-18 09:22:39', 2);
INSERT INTO `borrow` VALUES (2, 4, '2020-09-28 12:22:39', 3);
INSERT INTO `borrow` VALUES (4, 5, '2020-04-14 13:22:39', 2);
INSERT INTO `borrow` VALUES (5, 3, '2020-08-19 13:22:39', 1);
INSERT INTO `borrow` VALUES (6, 5, '2020-12-24 13:22:39', 2);
  • 分析4张表的关系：

学生表对学校表：一对多

学生表对图书表：多对多

4.2 练习1

  • 需求：查询所有学生信息。显示学生编号，学生姓名，籍贯，学校名称，学校简介

具体操作：

  1. 确定要查询哪些表：学生表和学校表

  2. 确定表连接条件：student.s_id=university.id

  3. 确定查询的字段：员工编号(student)，员工姓名(student)，籍贯(student)，学校名称(university)，学校简介(university)

1
2
3
4
5
6
7
8
9
10
11
-- 练习1：查询所有学生信息。显示员工编号，员工姓名，籍贯，学校名称，学校简称

-- 1. 确定查询哪些表：学生表，学校表
select * from student s inner join university u;

-- 2.确定表连接的条件
select * from student s inner join university u on s.u_id = u.id;

-- 3. 确定查询哪些列：员工编号，员工姓名，工资，职务名称，职务描述
select s.id 学生编号,s.name 学生姓名,s.address 籍贯,u.name 学校名称,u.info 学校简介 from student s 
inner join university u on s.u_id = u.id;

查询结果：

4.3 练习2

  • 需求：查询学校是’中国一流大学’的学生姓名、年龄、籍贯、学校名称

具体操作：

  1. 确定要查询哪些表：学生表和学校表
  2. 确定表连接条件：s.s_id=u.id
  3. 确定查询添加：学校是’中国一流大学’
  4. 确定查询字段：学生姓名、年龄、籍贯、学校名称
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 查询学校是'中国一流大学'的学生姓名、年龄、籍贯、学校名称

-- 1.确定查询哪些表：学生表和学校表
select * from student s inner join university u;

-- 2. 确定表连接条件：s.s_id=u.id
select * from student s inner join university u on s.u_id = u.id;

-- 2. 确定表连接条件：s.s_id=u.id
select * from student s inner join university u on s.u_id = u.id where u.name='中国一流大学';

-- 3. 查询哪些列：学生姓名、年龄、籍贯、学校名称
select s.name 姓名,s.age 年龄,s.address 籍贯,u.name 学校名称 from student s 
inner join university u on s.u_id = u.id 
where u.info='中国一流大学';

查询结果：

4.4 练习3

  • 需求：查询所有学生借的书的名称、单价、以及学生id、姓名、籍贯

学生、图书、借阅表

具体操作：

  1. 确定要查询哪些表：学生表、学生借阅表、图书表
  2. 确定表连接条件：
    1. 学生关联借阅表：student.id=borrow.s_id
    2. 再关联图书表：borrow.b_id=book.id
  3. 确定查询的字段：学生id、姓名、籍贯、书名、单价
1
2
3
4
5
6
7
8
9
10
11
-- 方式1：先连接4张表，再通过on指定所有的条件

-- 1. 查询4张表并确定连接条件
select * from student s 
inner join borrow bo on s.id=bo.s_id
inner join book b on b.id=bo.b_id;

-- 2. 确定查询的字段
select s.id 学生id,s.name 姓名,s.address 籍贯,b.name 书名,b.price 单价 from student s 
inner join borrow bo on s.id=bo.s_id
inner join book b on b.id=bo.b_id;

查询结果

4.5 练习4

  • 需求：查询借书数量最高的学生详情

具体操作：

  1. 确定要查询哪些表：学生表、借阅表
  2. 确定关联条件：student.id=borrow.s_id
  3. 根据学生id进行分组，求出每个学生的总借阅数量，查询学生信息、学生借阅总数
  4. 根据条件总借阅数量倒叙排序
  5. 只要第一条数据
1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 1. 确定要查询哪些表并且确定连接条件
select * from student s inner join borrow bo on s.id=bo.s_id; 

-- 2. 根据学生id进行分组，求出每个学生的总借阅数量
select s.*,sum(bo.count) 借阅总数 from 
student s inner join borrow bo on s.id=bo.s_id 
group by s.id; 

-- 3. 根据条件总借阅数量倒叙排序并且只要第一条数据
select s.*,sum(bo.count) 借阅总数 from 
student s inner join borrow bo on s.id=bo.s_id 
group by s.id 
order by sum(bo.count) desc 
limit 1;

4.6 练习5

  • 需求：查询学校是”中国一流大学”的学生借书情况，只查询Java相关书籍的记录，查询学生id、姓名、籍贯、书籍名称、出版社、借阅数量、借阅时间、学校名称、学校地址

大学、学生、图书、借阅

具体操作：

  1. 确定要查询哪些表：学生表、学生借阅表、图书表、学校表
  2. 确定表连接条件：
    1. 学生关联借阅表：student.id=borrow.s_id
    2. 再关联图书表：borrow.b_id=book.id
    3. 在关联学校表：university.id=student.u_id
  3. 确定查询条件：university.info='中国一流大学' 并且book.name like '%Java%'
  4. 确定查询的字段：学生id、姓名、籍贯、书籍名称、出版社、借阅数量、借阅时间、学校名称、学校地址
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- 1. 确定要查询哪些表并且确定连接条件
select * from student s 
inner join borrow bo on s.id=bo.s_id
inner join book b on b.id=bo.b_id
inner join university u on u.id=s.u_id;

-- 2.确定查询条件
select * from student s 
inner join borrow bo on s.id=bo.s_id
inner join book b on b.id=bo.b_id
inner join university u on u.id=s.u_id
where u.info='中国一流大学' and b.name like '%Java%';

-- 3.确定查询的字段
select 
	s.id 学生id,s.name 姓名,s.address 籍贯,b.name 书籍名称,b.publish 出版社,bo.count 借阅数量,
	bo.borrow_date 借阅时间,u.name 学校名称,u.location 学校地址 
from student s 
inner join borrow bo on s.id=bo.s_id
inner join book b on b.id=bo.b_id
inner join university u on u.id=s.u_id
where u.info='中国一流大学' and b.name like '%Java%';

查询结果

3.7 练习6

需求：统计每个大学共借书多少本

具体操作：

  1. 确定要查询哪些表：学校表、学生表、借阅表
  2. 确定表连接条件：
    1. 学校表关联学生表：university.id=student.u_id
    2. 学生表关联借阅表：student.id=borrow.s_id
  3. 根据学校名称进行分组，统计借阅表中的借阅数量
1
2
3
4
select u.name 大学名称,sum(bo.count) 借阅数量 from university u 
inner join student s on u.id=s.u_id
inner join borrow bo on bo.s_id=s.id
group by u.name;

查询结果：

4.8 练习7

需求：查询借阅数量大于等于2的大学借阅数量

具体操作：

  1. 确定要查询哪些表：university、student、borrow
  2. 确定表连接条件：
    1. 学校表关联学生表：university.id=student.u_id
    2. 学生表关联借阅表：student.id=bo.s_id
  3. 根据学校名称进行分组，统计借阅表中的借阅数量
  4. 统计之后在统计结果中进行筛选，只要借阅数量大于等于2的大学借阅数量
1
2
3
4
select u.name 大学名称,sum(bo.count) 借阅数量 from university u 
inner join student s on u.id=s.u_id
inner join borrow bo on bo.s_id=s.id
group by u.name having sum(bo.count)>=2;

查询结果

五、DCL (Data Control Language)

DDL：数据库定义语言，建库，建表

DML：数据库操纵语言，增删改

DQL：数据库查询语言，select….

DCL：数据库控制语言，用户控制….

我们现在默认使用的都是root用户，超级管理员，拥有全部的权限。但是，一个公司里面的数据库服务器上面可能同时运行着很多个项目的数据库。所以，我们应该可以根据不同的项目建立不同的用户，分配不同的权限来管理和维护数据库。

5.1 创建用户

5.1.1 语法：

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

tips：用户名、主机名和密码都应该加上单引号

5.1.2 关键字说明：

关键字 说明
‘用户名’ 将创建的用户名
‘主机名’ 指定该用户在哪个主机上可以登陆，如果是本地用户可用localhost，如果想让该用户可以从任意远程主机登陆，可以使用通配符%
‘密码’ 该用户的登陆密码，密码可以为空，如果为空则该用户可以不需要密码登陆服务器

5.1.3 具体操作

案例：创建zhangsan用户，只能在localhost这个服务器登录mysql服务器，密码为123456

1
2
3
create user 'zhangsan'@'localhost' identified by '123456';

flush privileges;

案例：创建lisi用户可以在任何电脑上登录mysql服务器，密码为admin

1
create user 'lisi'@'%' identified by 'admin';

5.2 给用户授权

用户创建之后，没什么权限，需要给用户授权

5.2.1 语法：

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

5.2.2 关键字说明：

关键字 说明
GRANT…ON…TO 授权关键字
权限 授予用户的权限，如CREATE、ALTER、SELECT、INSERT、UPDATE、DELETE等，如果要授予所有的权限则使用ALL。
数据库名.表名 该用户可以操作哪个数据库的哪些表。如果要授予该用户对所有数据库和表的相应操作权限则可用*表示，如*.*
‘用户名‘@’主机名’ 给哪个用户授权，要加上单引号。与创建用户时的用户名和主机名要相同。

5.2.3 具体操作：

案例：给zhangsan用户分配对test这个数据库操作的权限：创建表，修改表，插入记录，更新记录，查询

1
2
3
4
grant 权限1,权限2 on 数据库.表 to '用户名'@'主机名'

grant create,alter,insert,update,select on db03.* to 'zhangsan'@'localhost';
flush privileges;

登录zhangsan用户，执行delete语句：

权限不足

案例：给lisi用户分配所有权限，对所有数据库的所有表

1
grant all on *.* to 'lisi'@'%';

5.3 撤销授权

5.3.1 语法：

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

tips：如果一个用户没有select权限，那么update、delete权限也将执行不了；

5.3.2 关键字说明

关键字 说明
REVOKE…ON…FROM 撤销授权的关键字
权限 用户的权限，如CREATE、ALTER、SELECT、INSERT、UPDATE、SELECT等，所有的权限则使用ALL
数据库名.表名 对哪些数据库的哪些表，如果要取消该用户对所有数据库和表的操作权限则可用*表示，如*.*
‘用户名‘@’主机名’ 给哪个用户撤销，要加上单引号。与创建用户时的用户名和主机名要相同。

5.3.3 具体操作：

案例：撤销zhangsan用户对db03数据库的修改权限

1
2
3
4
5
revoke update on db03.* from 'zhangsan'@'localhost';

revoke select on db03.* from 'zhangsan'@'localhost';

flush privileges;
  • 注：用户名和主机名要与创建时相同，各自要加上单引号

回收之后，需要重启客户端才能刷新权限；

5.4 查看用户权限

1
show grants for 'zhangsan'@'localhost';

5.5 删除用户

5.5.1 语法

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

5.5.2 具体操作：

案例：删除zhangsan用户

1
drop user 'zhangsan'@'localhost';

再次使用lisi账号登录：

1
mysql -ulisi -padmin

5.6 修改管理员密码

5.6.1 语法

  • 在mysql/bin文件夹下可执行文件，不用登录，但要指定管理员的密码
1
mysqladmin -uroot -p password 新密码

回车后要输出原密码，才能更改成功。如果原密码不正确，则修改失败

5.7 暴力破解MySQL密码

修改MySQL核心配置文件：

MySQL的核心配置文件在C:\ProgramData\MySQL\MySQL Server 5.7\my.ini

该配置里面配置很多MySQL系统方面的配置，我们以后会详细讲到

在[mysqld]组下面添加如下配置：

1
skip-grant-tables

以管理员身份运行cmd窗口：

重启MySQL服务：

1
2
3
net stop mysql57

net start mysql57

使用MySQL客户端登录MySQL（此时不需要输入密码）：

修改root用户密码：

1
2
3
4
5
6
7
8
-- 切换到mysql数据库
use mysql;

-- 修改root用户密码
update user set authentication_string=password('123456') where user='root';

-- 刷新权限
flush privileges;

去掉mysql配置文件中的skip-grant-tables配置；

重启MySQL服务：

1
2
3
net stop mysql57

net start mysql57

使用新密码登录：