MySQL----多表查询

已于 2022-04-13 18:20:25 修改
阅读量2.9w 收藏 949
点赞数 118
分类专栏： MySQL学习笔记 文章标签： mysql sql 数据库 多表查询 DB
于 2022-04-05 17:26:48 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/m0_53022813/article/details/123972438
版权

MySQL----多表查询

多表关系

在进行数据库表结构的设计时，会根据业务的需求和业务模块之间的关系，分析设计表结构，由于业务之间相互关联，所以各个表结构之间也存在各种联系

表与表之间的联系：

1.一对多(多对一)
2.多对多
3.一对一

一对多(多对一)

例如，一个员工对应一个部门，一个部门可以对应多个员工
在这里插入图片描述

一般在多的一方创建外键，指向一的那一方
员工与部门，在员工表上设置外键，指向部门表

多对多

例如，一个学生可以选修多门课程，一个课程可以被多名学生选修
一般会建立第三张表，至少包含两个外键，分别指向两张表的主键
在这里插入图片描述

一对一

例如，用户和自己的学历信息的关系，一个人只对应一条学历信息
可以在任意一方加入外键，关联另一方的主键，并且设置外键为唯一(unique)
在这里插入图片描述
注：可以放在一张表中，但是对其进行拆分，一张表放基础信息，另一张表放详情，可以提升操作效率

多表查询

概述：
从多张表中查询数据
笛卡尔积：
笛卡尔积为两个集合(两张表)中的每条数据进行两两组合的结果
在多表查询时会产生笛卡尔积，要通过添加条件消除笛卡尔积
在这里插入图片描述
dept表：
请添加图片描述
emp表：
请添加图片描述
查询产生笛卡尔积的结果：

select * from emp, dept ;

请添加图片描述
消除笛卡尔积(添加条件)：

select * from emp, dept where emp.dept_id=dept.id;

请添加图片描述

多表查询的分类

1.连接查询：

内连接：
	相当于查询AB的交集部分
外连接：
		左外连接：
			查询A的所有数据，同时拼接上B对应的数据
		右外连接：
			查询B的所有数据，同时拼接上A中对应的数据
自连接：
	表与自身连接查询
	自连接必须给表取别名

在这里插入图片描述

2.子查询

数据准备

部门表：
请添加图片描述

create table dept (
    id int auto_increment primary key comment 'id',
    name varchar(50) not null comment '部门名称'
) comment '部门表';

insert into dept (id, name)
values (1, '研发部'),
       (2, '市场部'),
       (3, '财务部'),
       (4, '销售部'),
       (5, '总经办'),
       (6, '人事部');

员工表：
请添加图片描述

create table emp(
    id int auto_increment primary key ,
    name varchar(50) not null ,
    age int,
    job varchar(20) comment '职位',
    salary int ,
    entrydate date comment '入职时间',
    managerid int comment '直属领导id',
    dept_id int comment '所在部门id'
) comment '员工表';

insert into emp
values ( 1, '金庸', 66, '总裁', 20000, '2000-01-01', null, 5 ),
       ( 2, '张无忌', 20, '项目经理', 12500, '2005-12-05', 1, 1 ),
       ( 3, '杨晓', 33, '开发', 8400, '2000-11-03', 2, 1 ),
       ( 4, '韦一笑', 48, '开发', 11000, '2002-02-05', 2, 1 ),
       ( 5, '陈玉存', 43, '开发', 10500, '2004-09-07', 3, 1 ),
       ( 6, '小昭', 19, '程序员鼓励师', 6600, '2004-10-12', 2, 1 ),
       ( 7, '灭绝', 60, '财务总监', 8500, '2002-09-12', 1, 3 ),
       ( 8, '周芷若', 19, '会计', 48000, '2006-06-02', 7, 3 ),
       ( 9, '丁敏君', 23, '出纳', 5250, '2009-05-13', 7, 3 ),
       ( 10, '赵敏', 20, '市场部总监', 12500, '2004-10-12', 1, 2 ),
       ( 11, '鹿杖客', 56, '职员', 3750, '2006-10-03', 10, 2 ),
       ( 12, '何碧文', 19, '职员', 3750, '2007-05-09', 10, 2 ),
       ( 13, '东方白', 19, '职员', 5500, '2009-02-12', 10, 2 ),
       ( 14, '张三丰', 88, '销售总监', 14000, '2004-10-12', 1, 4 ),
       ( 15, '鱼梁洲', 38, '销售', 4600, '2004-10-12', 14, 4 ),
       ( 16, '宋远桥', 40, '销售', 4600, '2004-10-12', 14, 4 ),
       ( 17, '陈友谅', 42, null, 2000, '2011-10-12', 1, null );

内连接

语法：

# 隐式内连接
select 字段列表 from 表1,表2 where 条件;
# 显示内连接
select 字段列表 from 表1 [inner] join 表2 on 连接条件;

内连接查询的是两张表交集的部分

# 查询每一个员工的姓名及关联的部门的名称
select emp.name, dept.name from emp, dept where emp.dept_id=dept.id;
select emp.name, dept.name from emp inner join dept on emp.dept_id = dept.id;

外连接

语法：

# 左外连接
select 字段列表 from 表1 left [outer] join 表2 on 条件;
# 右外连接
select 字段列表 from 表1 right [outer] join 表2 on 条件;

左外连接相当于查询表1的所有数据包含表1和表2交集的部分数据
右外连接相当于查询表2的所有数据包含表1和表2交集部分的数据

# 查询emp表的所有数据，和应于的部门信息(左)
select emp.*, dept.* from emp left outer join dept on emp.dept_id = dept.id;
# 查询dept表的所有数据，和对于的员工信息(右)
select dept.*, emp.* from emp right outer join dept on emp.dept_id = dept.id;

左外连接和右外连接可以进行相互转化

自连接

语法：

select 字段列表 from 表a 别名a join 表a 别名b on 条件;

自链接查询可以是内连接查询也可以是外连接查询

# 查询员工及其所属领导的名字
# 自连接可以看成两张一样的表进行连接查询
select a.name, b.name from emp a join emp b on a.managerid=b.id;

联合查询

union、union all
对于联合查询就是把多次查询的结果合并起来，形成一个新的查询结果集
语法：

select 字段列表 from 表a
union [all]
select 字段列表 from 表b

# 将薪资低于5000的员工和年龄大于50的员工查询出来
select * from emp where salary>5000
union all
select * from emp where age>50;

# 没有all重复满足条件的只出现一次
# 将薪资低于5000的员工和年龄大于50的员工查询出来
select * from emp where salary>5000
union
select * from emp where age>50;

对于联合查询的多张表的列数必须保持一致，字段类型也要保持一致
union all会将全部的数据直接合并在一起，union会对合并之后的数据去重

子查询

概念：SQL语句中嵌套select语句为嵌套查询，又称子查询
select * from 表1 where 字段=(select 字段 from 表2);
子查询外的语句可以是insert、update、delete、select中的一个
根据子查询的结构不同，分为：

标量子查询：子查询的结果为单个值
列子查询：子查询的结果为一列
行子查询：子查询的结果为一行
表子查询：子查询的结果为多行多列

根据子查询的位置，分为：

where之后
from之后
select之后

标量子查询

子查询返回的结果是单个值(数字、字符串、日期等)，最简单的形式，这种子查询称为标量子查询
常用符号：=、<>、>、>=、<、<=

# 根据销售部门的id查询员工信息
# 先分开查询
# 查询销售部门的id
select id from dept where name='销售部'; #id为4
# 查询销售部门中员工的信息
select * from emp where dept_id=4;
# 合并为一个查询
select * from emp where dept_id=(select dept.id from dept where dept.name='销售部' );

列子查询

子查询的结果为一列(可以是多行)的，这种子查询为列子查询
常用操作符：
在这里插入图片描述

# 列子查询
# 查询销售部和市场部的所有员工信息
# 查询销售部和市场部的id
select id from dept where name='销售部' or name='市场部'; #id为2 4
# 查询两个部门的所有员工
select * from emp where dept_id in (2,4);
# 合并
select * from emp where dept_id in (select id from dept where name='销售部' or name='市场部');

行子查询

子查询返回的结果是一行(可以是多列)，这种子查询为行子查询
常用操作符：=、<>、in、not in

# 查询与张无忌的薪资及直属领导相同的员工信息
# 查询张无忌的薪资和直属领导
select salary, managerid from emp where name='张无忌';
# 查询与张无忌的薪资及直属领导相同的员工信息
select * from emp where (salary,managerid)=(select salary, managerid from emp where name='张无忌');

表子查询

子查询的结果是多行多列这种查询为表子查询
常用操作符：in

# 查询与鹿杖客和宋远桥的职位和薪资相同的员工信息
select * from emp where (job, salary) in ( select job, salary from emp where name in ('鹿杖客', '宋远桥'));

表子查询的子表作为临时表

# 查询入职日期是’2006-01-01‘之后的员工信息和部门信息
# 先查询出入职在’2006-01-01‘之后员工的所有信息
# 与部门表左连接
select e.*, dept.* from (select * from emp where entrydate>'2006-01-01') e left outer join dept on e.dept_id=dept.id;

多表查询案例

在这里插入图片描述
数据准备：

create table salgrade (
    grade int,
    losal int comment '本薪资等级的最低界限',
    hisal int comment '最高界限'
) 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,025001,30000);

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

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

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

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

3.查询拥有员工的部门id，部门名称

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

4.查询所有年龄大于40的员工，及其归属部门名称，如果员工没有分配部门也要显示

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

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

select e.*,s.grade
from emp e, salgrade s
where e.salary between s.losal and s.hisal;

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

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

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

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

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

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

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

select *
from emp
where salary> (
    select avg(e.salary)
    from emp e
    );

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

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

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

select d.*, (
    select count(*)
    from emp
    where emp.dept_id=d.id
    )
from dept d;

在这里插入图片描述

小炜1128
关注
  • 118
    点赞
  • 踩
  • 949
    收藏
    觉得还不错? 一键收藏
  • 6
    评论
专栏目录
MySQL多表查询详解
03-08
文档讲述了MySQL中的多表查询和一些其他的知识点，这是以后经常用到的
MySQL 多表查询实现分析
09-11
在一个数据库中，可能存在多个表，这些表都是相互关联的。
MySQL多表查询 （超详细）
weixin_55772633的博客
08-05 4459
关系：一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率。实现：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的（UNIQUE）（开发中一般取消外键的设定，即在多的一方建立字段指向一的一方的主键即可）（开发中一般取消外键的设定，即在任意一方加入字段关联另外一方的主键即可）（开发中一般取消外键的设定，即在中间表建立两个字段指向两方的主键即可）关系：一个部门对应多个员工，一个员工对应一个部门。实现：在多的一方建立外键，指向一的一方的主键。
超详细多表查询详解-多表关系-多表查询-子查询
最新发布
无限进步
03-03 959
内连接的语法分为两种: 隐式内连接、显式内连接
MySQL：多表查询（全面详解）
weixin_52533007的博客
06-16 6581
本博主将用CSDN记录软件开发求学之路上亲身所得与所学的心得与知识，有兴趣的小伙伴可以关注博主！也许一个人独行，可以走的很快，但是一群人结伴而行，才能走的更远！
MySQL中的多表查询详解
qq_57492774的博客
07-09 2944
在实际的项目开发中，在进行数据表结构设计的时候，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间很多都是相关联的，所以各个表结构之间也存在着一定的关系。id INT PRIMARY KEY COMMENT '部门编号',name VARCHAR(20) UNIQUE COMMENT '部门名称'
MySQL-视图操作
qq_44065088的博客
06-25 475
视图操作 小学的时候，每年都会举办一次抽考活动，意思是从每一个班级里面筛选出几个优秀的同学去参加考试。这时候很多班级筛选出来的这些同学就可以临时组成一个班级，如果我们把每一个班级都当做是一张真实的表，这个临时的班级在数据库里就可以当做一个视图，也就是说，这个临时的班级其实不是真实存在的，当考试过后，这些学生还是各回各家各找各妈。。。。 视图是从一个或多个表中导出来的表，是一种虚拟存在的表。视图就像一个窗口，通过这个窗口可以看到系统专门提供的数据，这样用户可以不看整个数据库表中的数据，而只关心对自己有.
MySQL知识点
mf_sprite的博客
07-26 106
JavaSE部分还有一大堆笔记还在慢慢整理，这周又把MySQL学完了，无情！！！ 下面是自己整理的一些笔记，有兴趣的可以在B站看看老杜的视频。 MySQL SQL语言的分类 DQL（数据查询语言）: 凡是select语句都是DQL。 DML（数据操作语言）：insert (插入)，delete(删除)， update(更新)，对表当中的数据进行增删改。 DDL（数据定义语言）：create新建 drop删除 alter修改结构 rename重回名 truncate 删除，对表结构的增删改。 DC
access update语句执行_Mysql联表update数据
weixin_39847887的博客
11-21 429
1.MySQL UPDATE JOIN语法在MySQL中，可以在 UPDATE语句 中使用JOIN子句执行跨表更新。MySQL UPDATE JOIN的语法如下：拉勾IT课小编为大家分解UPDATE T1, T2,[INNER JOIN | LEFT JOIN] T1 ON T1.C1 = T2. C1SET T1.C2 = T2.C2,T2.C3 = exprWHERE condition更详细...
分组查询操作（MySQL）
taoxing
01-18 4486
目录前言分组查询语法简单分组查询增加了分组前筛选的分组查询增加了分组后筛选的分组查询group by子句类型按函数筛选多个字段分组添加排序总结 前言 介绍MySQL的单行函数操作我们选择使用可视化软件Navicat，对应的数据库使用了某公司员工数据库，该数据库包含员工信息表、部门信息表、公司地址信息表、工作职业信息表。 分组查询语法 select 分组函数，分组的列表（要求出现在group by的后面） from 表 【where 筛选条件】 group by 分组的列表 【order by 子句】
MySQL多表查询（联合查询、连接查询、子查询）
热门推荐
m0_49864110的博客
07-27 1万+
EXCEPT 将两张表的某些字段的公共记录减去，然后返回一张表的剩余记录（差集）（MySQL不存在，使用NOT IN字段实现）3、作为联合查询的多表之间的列数、以及列数的类型必须相同（例如：表1查询哪些列，表2就查询哪些列）1、通过联合查询，可以得到两张表中记录的集合或者公共记录的集合，或者其中某张表中的记录的集合。将两张表的全部记录交叉组合，在添加了其他表字段的同时，所得的记录数是两张表行数的乘积。#查询张三入职之后的所有员工信息（先查询张三的入职信息，再查询此时间之后的员工信息。
MySQL 多表关联一对多查询实现取最新一条数据的方法示例
09-08
主要介绍了MySQL 多表关联一对多查询实现取最新一条数据的方法,结合实例形式详细分析了MySQL 多表关联一对多查询实现取最新一条数据相关原理、实现方法与操作注意事项,需要的朋友可以参考下
MySQL多表查询实例详解【链接查询、子查询等】
09-08
主要介绍了MySQL多表查询,结合实例形式详细分析了mysql多表查询中的链接查询、子查询等相关操作技巧与使用注意事项,需要的朋友可以参考下
mysql多表联合查询返回一张表的内容实现代码
09-10
在使用mysql多表联合查询时怎样可以做到只返回返回一张表的内容，本文将详细介绍，需要了解的朋友可以参考下
数据库MySQL-多表查询
12-14
1.2 多表查询 1.2.1 内连接 规则：返回两个表的公共记录 语法： -- 语法一 select * from 表1 inner join 表2 on 表1.公共字段=表2.公共字段 -- 语法二 select * from 表1，表2 where 表1.公共字段=表2.公共字段 ...
mysql-batch:小批量运行大型MySQL UPDATE和DELETE查询，以防止表行级锁定
05-27
该工具将小批量运行UPDATE和DELETE查询，以防止表级和行级锁定（使用InnoDB）。 如果必须更新或删除大量行，则也可以限制一次选择的行数。安装 pip3 install mysql_batchUPDATE示例您可以使用可用的模式运行此示例...
mysql-5.5.41-win32
06-14
MySQL是一种关联数据库管理系统，关联数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内。这样就增加了速度并提高了灵活性。MySQL的SQL“结构化查询语言”。SQL是用于访问数据库的最常用标准化语言。...
mysql-server-5.5.40-source.rar
08-04
MySQL的SQL“结构化查询语言”。SQL是用于访问数据库的最常用标准化语言。MySQL软件采用了GPL（GNU通用公共许可证）。由于其体积小、速度快、总体拥有成本低，而且开放源码。 这是最后一个5.5版本 mysql 5.5.40的...
mysql多表查询
06-01
MySQL多表查询是指在一个SELECT语句中同时查询多个表的数据。常见的多表查询方式有以下几种： 1. 使用JOIN关键字连接多个表： ``` SELECT column1, column2, ... FROM table1 INNER JOIN table2 ON condition INNER JOIN table3 ON condition ... ``` 其中，JOIN关键字可以用INNER JOIN、LEFT JOIN、RIGHT JOIN等不同的方式进行连接。condition是连接条件，用于指定两个表之间的关联关系。 2. 使用子查询查询多个表： ``` SELECT column1, column2, ... FROM table1 WHERE column1 IN (SELECT column1 FROM table2 WHERE condition) ``` 其中，子查询用于查询table2表中符合条件的数据，然后将结果作为条件传递给主查询语句。 3. 使用UNION关键字合并多个查询结果： ``` SELECT column1, column2, ... FROM table1 WHERE condition UNION SELECT column1, column2, ... FROM table2 WHERE condition ``` 其中，UNION关键字用于合并两个查询结果，要求两个查询结果的列数和数据类型必须相同。 以上是常见的MySQL多表查询方式，具体使用哪一种方式，需要根据实际情况进行选择。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 6
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值