MySQL学习：Day 10（多表查询）

_SURE_

已于 2023-10-24 16:35:14 修改

阅读量291

点赞数

分类专栏： MySQL 文章标签：学习 1024程序员节

于 2023-10-22 19:06:52 首次发布

本文链接：https://blog.csdn.net/2301_76481680/article/details/133964713

版权

MySQL 专栏收录该内容

10 篇文章 1 订阅

订阅专栏

本文详细介绍了SQL中的多表关系、多表查询、连接查询（内连接、外连接、自连接）、联合查询（union,unionall）以及子查询的多种类型和用法，包括笛卡尔积和实际案例，旨在帮助读者理解和掌握数据库查询的基本技巧。

摘要由CSDN通过智能技术生成

六、联合查询——nuion ， union all

一、多表关系

（1）表结构之间的联系，一般分为三种

一对多（多对一）
多对多
一对一

（2）一对多

（3）多对多

通过中间表来维护两张表之间的关系

案例：可视化界面展示多对多之间的关系

（4）一对一（可以合并成一个表）

二、多表查询概述

（1）概述

多表查询：指从多张表中查询数据。

（2）扩展：笛卡尔积

概念：

练习：

# --笛卡尔积
select * from emp2,dept2;
# 输出的结果一共30条,需要emp2中的每一条数据都和dept2中的数据组合,所以结果有6*5=30种,这种结果称作两表的"笛卡尔积"

（3）语法

select * from 表1,表2 where 两表之间的联系;

代码展示：

# --多表查询
select * from emp2,dept2 where emp2.dept_id=dept2.id;
# 加上限制条件,即可从笛卡尔积中挑选出来有用的数据

（4）多表查询分类

三、连接查询——内连接

（1）概念和语法

内连接查询：查询两张表之间的交集部分

（2）代码演示

# ---------------内连接查询-------------------
# --1.查询每个员工的姓名,及关联的部门的名称(隐式内连接实现)
# 表结构:emp,dept
# 连接条件:dept_id=dept.id
select emp2.name,dept2.name from emp2,dept2 where emp2.dept_id=dept2.id;
select e.name,d.name from emp2 e,dept2 d where e.dept_id = d.id;
# emp2 e即给emp2表起了个别名e,此时就不能再通过表名来限制字段了

# --2.查询每个员工的姓名,及关联的部门的名称(显示内连接实现) ---- inner join ... on...
# 表结构:emp,dept
# 连接条件:dept_id=dept.id
select e.name,d.name from emp2 e inner join dept2 d on e.dept_id=d.id;
# inner可以省略

四、外连接

（1）语法

外连接分为：左外连接和右外连接（左右外连接只有关键字不同，其他的都一样）

左外连接（对应的关键字left）

相当于查询表1（左表）的所有数据包含表1和表2 交集部分的数据。

右外连接（对应的关键字right）

相当于查询表2（左表）的所有数据包含表1和表2 交集部分的数据。

（2）代码演示

# ----------------------------外连接---------------------------------
# 左右外连接可以相互转化
# --左外连接:查询emp2表的所有数据和对应的部门信息
select emp2.*,dept2.name from emp2 left outer join dept2 on emp2.dept_id=dept2.id;

# --右外连接:查询dept2表的所有数据和对应员工的信息
select emp2.*,dept2.name from emp2 right outer join dept2 on emp2.dept_id=dept2.id;

五、自连接

（1）语法结构

自连接：一张表自己连接自己。

（2）代码展示

# ----------------------------自连接---------------------------------
# --1.查询员工及其所属的领导的名字
select e.name '员工',m.name '领导' from emp2 e join emp2 m on e.managerid=m.id;
# 此时没有领导的则不会输出

# --2.查询所有员工emp及其领导的名字emp,如果员工没有领导,也需要查询出来
select e.name '员工',m.name '领导' from emp2 e left join emp2 m on e.managerid=m.id;
# 右外连接,emp2表中的name全部展示,没有领导的展示为null

六、联合查询——nuion ， union all

（1）定义

（2）代码展示

# ----------------------------联合连接---------------------------------
# --union , union all
# 1.将薪资低于10000的员工和年龄大于30岁的员工全部查询出来
select * from emp2 e where e.salary>10000   # 前四个是薪资大于10000的
union all
select * from emp2 m where m.age>30;        # 后四个是年龄大于30的

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

七、子查询

（1）概念

注意：子查询外部的语句可以是INSERT/UPDATE/DELEYTE/SELECT的任何一个。

（2）分类

（3）根据子查询结果分类

标量子查询

概念：子查询的结果是单个的值（数字、字符串、日期等），最简单的形式。

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

代码演示：

# ----------------------------子查询---------------------------------
# --标量子查询
# 1.查询“销售部”的所有员工信息
select * from emp2 where emp2.dept_id=(select id from dept where name='销售部');
# select id from dept where name='销售部'------这部分查询叫做子查询
# 先进性子查询得出销售部的id为4，再让emp2表中的员工作书部门ID即dept_id和销售部的相等即可。

# 2.查询在韦一笑入职之后入职的员工信息
select emp2.name,emp2.entrydate from emp2 where emp2.entrydate>(select entrydate from emp2 where emp2.name='韦一笑');
# 子查询先查询并返回韦一笑的入职时间，然后在emp2表中查询入职时间在韦一笑之后的人

列子查询

概念：子查询返回结果是一列（可以是多行）

常用操作符：in ，not in，any，some，all

代码演示：

# --列子查询
# 1.查询销售部和市场部的所有员工
select * from emp2 where emp2.dept_id in (select id from dept2 where dept2.name='销售部' or dept2.name='市场部');

# 2.查询比销售部所有人工资都高的员工信息
select * from emp2 where emp2.salary > all (select emp2.salary from emp2 where emp2.dept_id=(select id from dept2 where dept2.name='销售部'));

# 3.查询比销售部其中任意一人工资都高的员工信息
select * from emp2 where emp2.salary > any (select emp2.salary from emp2 where emp2.dept_id=(select id from dept2 where dept2.name='销售部'));
# any是满足其一即可,all是所有都要满足

行子查询

概念：子查询结果是一行（可以是多列）

常用的操作符：=，<，>，in，not in

代码演示：
# --行子查询
# 1.查询与张无忌的薪资及直属领导都相同的员工信息
select emp2.name,emp2.salary,emp2.managerid from emp2 where (emp2.salary,emp2.managerid) in (select emp2.salary,emp2.managerid from emp2 where emp2.name='张无忌');

表子查询

概念：子查询结果返回的是多行多列

常用操作符：in

代码演示：

# --表子查询
# 1.查询和张无忌和韦一笑的职位和薪资相同的员工信息
select * from emp2 where (emp2.salary,emp2.job) in (select emp2.salary,emp2.job from emp2 where emp2.name='张无忌' or name='韦一笑');

# 2.查询入职日期是2006-01-01之后的员工信息及部门信息
select e.*,d.name from (select emp2.name,emp2.dept_id from emp2 where emp2.entrydate>'2006-01-01') e left join dept2 d on d.id=e.dept_id;

（4）根据子查询位置分类

WHERE之后
FROM之后
SELECT之后

八、案例

# -----------------------------------子查询练习--------------------------------------
# 数据准备
create table salgrade(
    grade int,
    losal int,
    hisal int
)comment '薪资等级表';
insert into salgrade values (1,0,3000),(2,3001,5000),
                            (3,5001,8000),(4,8001,10000),
                            (5,10001,15000),(6,15001,20000),
                            (7,20001,25000),(8,25001,30000);
# 练习
# 1.查询员工姓名,年龄,职位,部门信息(隐式内连接)
select e.name,e.age,e.job,e.dept_id,d.name from emp2 e,dept2 d where(e.dept_id=d.id);

# 2.查询年龄小于30岁的员工的姓名,年龄,职位,部门信息(显示内连接)
select e.name,e.age,e.job,e.dept_id,d.name from emp2 e join dept2 d on e.dept_id=d.id;

# 3.查询拥有员工的部门ID,部门名称
select distinct d.id,d.name from emp2 e join dept2 d on e.dept_id=d.id ;

# 4.查询所有年龄大于40岁的员工,及其所属的部门名称
select e.name,e.age,e.dept_id,d.name from emp2 e join dept2 d on e.age>40 and e.dept_id=d.id;

# 5.查询所有员工的工资等级
select e.*,s.* from emp2 e join salgrade s on e.salary>=s.losal and e.salary<=s.hisal;

# 6.查询研发部的所有员工的信息及工资等级
# 研发部的所有员工（研发部的dept_id=1）
select * from emp2 e where e.dept_id='1';
# 所有员工的工资等级
select e.*,s.* from emp2 e join salgrade s on e.salary>=s.losal and e.salary<=s.hisal;
# 两者结合
select e.*,s.* from (select * from emp2 e where e.dept_id='1') e join salgrade s on e.salary>=s.losal and e.salary<=s.hisal;

# 7.查询研发部员工的平均工资
select avg(e.salary) '研发部员工平均工资' from emp2 e where e.dept_id='1';

# 8.查询工资比韦一笑高的员工信息
select * from emp2 e where e.salary>(select e.salary from emp2 e where e.name='韦一笑');

# 9.查询比平均工资高的员工信息
select * from emp2 e where e.salary > (select avg(emp2.salary) '所有员工平均工资' from emp2);

# 10.查询低于本部门平均工资的员工信息
select * from emp2 e where e.salary < (select avg(emp2.salary) '所有员工平均工资' from emp2);

# 11.查询所有员工的部门信息，并统计部门的员工人数
select d.id,d.name ,(select count(*) from emp2 e where e.dept_id = d.id) '部门人数' from dept2 d;

# 12.查询所有学生的选课情况，展示出学生的姓名，学号，课程名称
# 表：student,course,student_course
select s.name,s.no,c.name from student s,course c,student_course sc where s.id=sc.studentid and sc.courseid=c.id;