MySQL-多表查询（下）

红太狼 2023-10-10 21:03 54阅读 0赞

> ♥️**作者：小刘在C站**
> 
> ♥️**个人主页：[小刘主页][Link 1]**
> 
> ♥️**每天分享云计算网络运维课堂笔记，努力不一定有回报，但一定会有收获加油！一起努力，共赴美好人生！**
> 
> ♥️**树高千尺，落叶归根人生不易，人间真情**

前言

我们之前在讲解 SQL 语句的时候，讲解了 [DQL][] 语句，也就是数据查询语句，但是之前讲解的查询都是单表查询，而本章节我们要学习的则是多表查询操作[上章][Link 2]讲到5.2本章继续

**目录**

MySQL

6 子查询

6.1 概述

1). 概念

2). 分类

6.2 标量子查询

案例:

6.3 列子查询

案例:

6.4 行子查询

6.5 表子查询

5.7 多表查询案例

1). 查询员工的姓名、年龄、职位、部门信息 （隐式内连接）

2). 查询年龄小于30岁的员工的姓名、年龄、职位、部门信息（显式内连接）

3). 查询拥有员工的部门ID、部门名称

4). 查询所有年龄大于40岁的员工, 及其归属的部门名称; 如果员工没有分配部门, 也需要展示出

来(外连接)

5). 查询所有员工的工资等级

6). 查询 "研发部" 所有员工的信息及 工资等级

7). 查询 "研发部" 员工的平均工资

8). 查询工资比 "灭绝" 高的员工信息。

9). 查询比平均薪资高的员工信息

10). 查询低于本部门平均工资的员工信息

11). 查询所有的部门信息, 并统计部门的员工人数

12). 查询所有学生的选课情况, 展示出学生名称, 学号, 课程名称

--------------------

### MySQL ###

> MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。
> 
> MySQL是一种关系型数据库管理系统，关系数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。
> 
> MySQL所使用的 SQL 语言是用于访问数据库的最常用标准化语言。MySQL 软件采用了双授权政策，分为社区版和商业版，由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，一般中小型和大型网站的开发都选择 MySQL 作为网站数据库。

### **6** **子查询** ###

#### **6.1** **概述**  ####

#### 1). 概念 ####

SQL 语句中嵌套 SELECT 语句，称为嵌套查询，又称子查询

SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2 );

#### 2). 分类 ####

根据子查询结果不同，分为：

A.  标量子查询（子查询结果为单个值）

B.  列子查询 ( 子查询结果为一列 )

C.  行子查询 ( 子查询结果为一行 )

D.  表子查询 ( 子查询结果为多行多列 )

根据子查询位置，分为：

A. WHERE 之后

B. FROM 之后

C. SELECT 之后

### **6.2** **标量子查询**  ###

子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式，这种子查询称为标量子查询。

常用的操作符： = <> > >= < <=

### 案例:  ###

A.  查询  " 销售部 "  的所有员工信息

完成这个需求时，我们可以将需求分解为两步： 、

① .  查询  " 销售部 "  部门 ID

select id from dept where name = '销售部';

② .  根据  " 销售部 "  部门 ID,  查询员工信息

select * from emp where dept_id = (select id from dept where name = '销售部');

B.  查询在  " 方东白 "  入职之后的员工信息

完成这个需求时，我们可以将需求分解为两步：

① .  查询 方东白 的入职日期

select entrydate from emp where name = '方东白';

② .  查询指定入职日期之后入职的员工信息

select * from emp where entrydate > (select entrydate from emp where name = '方东
    白');

### **6.3** **列子查询**  ###

子查询返回的结果是一列（可以是多行），这种子查询称为列子查询。

常用的操作符： IN  、 NOT IN  、  ANY  、 SOME  、  ALL

![a20a3bee91944d6a95c68b10d2917505.png][]

#### 案例:  ####

A.  查询  " 销售部 "  和  " 市场部 "  的所有员工信息

分解为以下两步 :

① .  查询  " 销售部 "  和  " 市场部 "  的部门ID

select id from dept where name = '销售部' or name = '市场部';

② .  根据部门 ID,  查询员工信息

select * from emp where dept_id in (select id from dept where name = '销售部' or
    name = '市场部');

B.  查询比 财务部 所有人工资都高的员工信息

分解为以下两步

① .  查询研发部所有人工资

select salary from emp where dept_id = (select id from dept where name = '研发部');

② .  比研发部其中任意一人工资高的员工信息

select * from emp where salary > any ( select salary from emp where dept_id =
    (select id from dept where name = '研发部') );

### **6.4** **行子查询**  ###

子查询返回的结果是一行（可以是多列），这种子查询称为行子查询。

常用的操作符： =  、 <>  、 IN  、 NOT IN

案例：

A.  查询与  " 张无忌 "  的薪资及直属领导相同的员工信息  ;

这个需求同样可以拆解为两步进行 :

① .  查询  " 张无忌 "  的薪资及直属领导

select salary, managerid from emp where name = '张无忌';

② .  查询与  " 张无忌 "  的薪资及直属领导相同的员工信息  ;

select * from emp where (salary,managerid) = (select salary, managerid from emp
    where name = '张无忌');

### **6.5** **表子查询** ###

子查询返回的结果是多行多列，这种子查询称为表子查询。

常用的操作符： IN

案例: A.  查询与  " 鹿杖客 " , " 宋远桥 "  的职位和薪资相同的员工信息

分解为两步执行: ① .  查询  " 鹿杖客 " , " 宋远桥 "  的职位和薪资

select job, salary from emp where name = '鹿杖客' or name = '宋远桥'; 1

② .  查询与  " 鹿杖客 " , " 宋远桥 "  的职位和薪资相同的员工信息

select * from emp where (job,salary) in ( select job, salary from emp where name =
    '鹿杖客' or name = '宋远桥' );

B.  查询入职日期是  "2006-01-01"  之后的员工信息  ,  及其部门信息

分解为两步执行 :

① .  入职日期是  "2006-01-01"  之后的员工信息

select * from emp where entrydate > '2006-01-01';

② .  查询这部分员工 ,  对应的部门信息 ;

select e.*, d.* from (select * from emp where entrydate > '2006-01-01') e left
    join dept d on e.dept_id = d.id ;

### **5.7** **多表查询案例** ###

数据环境准备 :

create table salgrade(
    grade int,
    losal int,
    hisal int
    ) comment '薪资等级表';
    insert into salgrade values (1,0,3000);
    insert into salgrade values (2,3001,5000);
    insert into salgrade values (3,5001,8000);
    insert into salgrade values (4,8001,10000);
    insert into salgrade values (5,10001,15000);
    insert into salgrade values (6,15001,20000);
    insert into salgrade values (7,20001,25000);
    insert into salgrade values (8,25001,30000);

在这个案例中，我们主要运用上面所讲解的多表查询的语法，完成以下的 12 个需求即可，而这里主要涉

及到的表就三张： emp 员工表、 dept 部门表、 salgrade 薪资等级表 。

### 1). 查询员工的姓名、年龄、职位、部门信息 （隐式内连接）  ###

表 : emp , dept

连接条件 : emp.dept\_id = dept.id

select e.name , e.age , e.job , d.name from emp e , dept d where e.dept_id = d.id;

### 2). 查询年龄小于30岁的员工的姓名、年龄、职位、部门信息（显式内连接）  ###

表 : emp , dept

连接条件 : emp.dept\_id = dept.id

select e.name , e.age , e.job , d.name from emp e inner join dept d on e.dept_id =
    d.id where e.age < 30;

### 3). 查询拥有员工的部门ID、部门名称  ###

表 : emp , dept

连接条件 : emp.dept\_id = dept.id

select distinct d.id , d.name from emp e , dept d where e.dept_id = d.id;

### 4). 查询所有年龄大于40岁的员工, 及其归属的部门名称; 如果员工没有分配部门, 也需要展示出  ###

### 来(外连接)  ###

表 : emp , dept

连接条件 : emp.dept\_id = dept.id

select e.*, d.name from emp e left join dept d on e.dept_id = d.id where e.age >
    40 ;

### 5). 查询所有员工的工资等级 ###

表 : emp , salgrade

连接条件  : emp.salary >= salgrade.losal and emp.salary <= salgrade.hisal

-- 方式一
    select e.* , s.grade , s.losal, s.hisal from emp e , salgrade s where e.salary >=
    s.losal and e.salary <= s.hisal;
    -- 方式二
    select e.* , s.grade , s.losal, s.hisal from emp e , salgrade s where e.salary
    between s.losal and s.hisal;

### 6). 查询 "研发部" 所有员工的信息及 工资等级  ###

表 : emp , salgrade , dept

连接条件  : emp.salary between salgrade.losal and salgrade.hisal ,

emp.dept\_id = dept.id

查询条件  : dept.name = ' 研发部 '

select e.* , s.grade from emp e , dept d , salgrade s where e.dept_id = d.id and (
    e.salary between s.losal and s.hisal ) and d.name = '研发部';

### 7). 查询 "研发部" 员工的平均工资  ###

表 : emp , dept

连接条件  : emp.dept\_id = dept.id

select avg(e.salary) from emp e, dept d where e.dept_id = d.id and d.name = '研发
    部';

### 8). 查询工资比 "灭绝" 高的员工信息。  ###

① .  查询  " 灭绝 "  的薪资

select salary from emp where name = '灭绝';

② .  查询比她工资高的员工数据

select * from emp where salary > ( select salary from emp where name = '灭绝' );

### 9). 查询比平均薪资高的员工信息  ###

① .  查询员工的平均薪资

select avg(salary) from emp;

② .  查询比平均薪资高的员工信息

select * from emp where salary > ( select avg(salary) from emp ); 1

#### 10). 查询低于本部门平均工资的员工信息  ####

① .  查询指定部门平均薪资

select avg(e1.salary) from emp e1 where e1.dept_id = 1;
    select avg(e1.salary) from emp e1 where e1.dept_id = 2;

② .  查询低于本部门平均工资的员工信息

select * from emp e2 where e2.salary < ( select avg(e1.salary) from emp e1 where
    e1.dept_id = e2.dept_id );

### 11). 查询所有的部门信息, 并统计部门的员工人数 ###

select d.id, d.name , ( select count(*) from emp e where e.dept_id = d.id ) '人数'
    from dept d;

### 12). 查询所有学生的选课情况, 展示出学生名称, 学号, 课程名称  ###

表 : student , course , student\_course

连接条件 : student.id = student\_course.studentid , course.id =

student\_course.courseid

select s.name , s.no , c.name from student s , student_course sc , course c where
    s.id = sc.studentid and sc.courseid = c.id ;

> **备注** **:**  **以上需求的实现方式可能会很多** **, SQL** **写法也有很多，只要能满足我们的需求，查询出符合条件的记录即可。**

> ♥️关注，就是我创作的动力
> 
> ♥️点赞，就是对我最大的认可
> 
> ♥️这里是小刘，励志用心做好每一篇文章，谢谢大家

[Link 1]: https://blog.csdn.net/lzl10211345?type=blog
[DQL]: https://so.csdn.net/so/search?q=DQL&spm=1001.2101.3001.7020
[Link 2]: http://t.csdn.cn/8TJPK
[a20a3bee91944d6a95c68b10d2917505.png]: https://img-blog.csdnimg.cn/a20a3bee91944d6a95c68b10d2917505.png