MySQL----多表查询

article/2025/9/24 8:39:00

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.salaryfrom emp ewhere 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 ewhere e.dept_id=emp.dept_id);

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

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

在这里插入图片描述


http://chatgpt.dhexx.cn/article/LHASTTCq.shtml

相关文章

SQL语句多表查询

SQL语句多表查询

多表查询 多表查询&#xff0c;也称为关联查询&#xff0c;指两个或更多个表一起完成查询操作。 前提条件&#xff1a;这些一起查询的表之间是有关系的&#xff08;一对一、一对多&#xff09;&#xff0c;它们之间一定是有关联字段&#xff0c;这个关联字段可能建立了外键&a…
阅读更多...
十、多表查询

十、多表查询

十、多表查询 *多表查询的本质就是把多张表合并成一个临时表 1、多表数据合并方案 连接查询合并方案【最常见】 首先确保两张表之间存在【隶属关系】将两张表中的数据行【沿着水平方向进行拼接】最终得到一个拥有【完整隶属关系】的新数据行 联合查询合并方案 不需要两张表之…
阅读更多...
图解数据库多表查询（多表联查）的几种方式

图解数据库多表查询（多表联查）的几种方式

多表查询&#xff0c;也被称为多表连接查询。 为什么会有多表查询这个操作呢&#xff1f; 在实际项目开发过程中&#xff0c;会出现多张表&#xff0c;用于分类存储不同的信息&#xff0c;我们测试需要的数据也会来自不同的表&#xff0c;因此需要使用多表查询。 我们常用的…
阅读更多...
史上最牛mysql-06 (多表连接)

史上最牛mysql-06 (多表连接)

多表连接 个人博客 &#xff1a;www.xiaobeigua.icu 多表查询&#xff0c;也称为关联查询&#xff0c;指两个或更多个表一起完成查询操作。 前提条件&#xff1a;这些一起查询的表之间是有关系的&#xff08;一对一、一对多&#xff09;&#xff0c;它们之间一定是有关联字段&…
阅读更多...
sql多表查询

sql多表查询

1.多表查询概念&#xff1a;从多张表查询数据 2.分类&#xff1a;连接查询和子查询。 有两张表A、B 连接查询&#xff1a; 内连接 相当于查询A、B交集数据 例1&#xff1a;select * from emp,dept where emp.dep_iddept.id; 外连接 左外连接&#xff1a;相当于查询…
阅读更多...
sql语句之多表查询

sql语句之多表查询

文章目录 一、笛卡尔积(交叉连接) ---->cross join1、概念2. 语法3.示例 二、内连接---->inner join1、概念2. 语法3.示例 三、外链接---->outer join1、概念2. 语法3.示例 四、联合连接---->union1、概念2.示例 一、笛卡尔积(交叉连接) ---->cross join 1、概…
阅读更多...
连接查询（多表查询）——MySQL

连接查询（多表查询）——MySQL

连接查询&#xff08;多表查询&#xff09; 又称多表查询&#xff0c;当查询的字段涉及多个表的时候&#xff0c;就要用到连接查询 分类&#xff1a; 为表起别名&#xff1a; 提高语句的简洁度区分多个重名字段注意&#xff1a;如果为表起了别名&#xff0c;则查询的字段就不…
阅读更多...
MySQL-多表查询

MySQL-多表查询

一、表架构 student(sid,sname,sage,ssex) 学生表course(cid,cname,tid) 课程表sc(sid,cid,score) 成绩表teacher(tid,tname) 教师表 二 、建表sql语句 SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS 0;-- ---------------------------- -- Table structure for course -- --…
阅读更多...
Java中的XML解析

Java中的XML解析

前言 这几天在手写Spring IOC的时候遇上了xml文件的解析&#xff0c;通过解析Spring.xml配制文件的方式来创建对象。因为之前从来都没遇见过相关的需求&#xff0c;所以对此做一份记录 XML (eXtensible Markup Language) 意为可扩展标记语言&#xff0c;被多数技术人员用以选择…
阅读更多...
Java - XML文件解析

Java - XML文件解析

Java对XML文件的解析 一、前言二、实例分析 - DOM方式解析XML文件2.1 XMLDomParseUtil - 抽象工具类2.2 待解析xml文件示例2.3 解析xml文件2.3.1 新建一个用于存储xml文件中各节点的属性和属性值的类2.3.2 解析实际节点 2.4 测试 一、前言 1、HTTP网络传输中的数据组织方式有三…
阅读更多...
Java解析XML文件的方式

Java解析XML文件的方式

在项目里&#xff0c;我们往往会把一些配置信息放到xml文件里&#xff0c;或者各部门间会通过xml文件来交换业务数据&#xff0c;所以有时候我们会遇到“解析xml文件”的需求。一般来讲&#xff0c;有基于DOM树和SAX的两种解析xml文件的方式&#xff0c;在这部分里&#xff0c;…
阅读更多...
java解析xml格式的节点属性值

java解析xml格式的节点属性值

xml示例&#xff1a; <ResponseContent><Parameter><Document><RecordSet id"1"><Master name"purchase"><Record><Field name"orderNumber" value"1P05-151200172"/><Field name"…
阅读更多...
Java解析XML字符串

Java解析XML字符串

在网上找了很多Java语言解析XML字符串的资料&#xff0c;很多内容写得很繁复&#xff0c;没有普适性&#xff0c;遂自己动手写了一个用Java解析XML的工具类。话不多说&#xff0c;直接看下面代码&#xff1a; XML解析工具类&#xff1a; import org.dom4j.Document; import o…
阅读更多...
JAVA解析xml报文

JAVA解析xml报文

目录 问题需求 响应报文格式 1&#xff0c;开发导入的依赖 2&#xff0c;开发样例 3&#xff0c;测试结果 问题需求 在工作中此项目请求指定项目之后&#xff0c;项目会返回一个xml报文格式的响应&#xff0c;此时我需要判断返回的报文是否是成功的响应报文&#xff0c;如…
阅读更多...
Java解析xml

Java解析xml

记录工作中遇到的一些问题和解决方式&#xff0c;自己摘抄整理了一下用来巩固&#xff0c;如有不足或有更好的方式 欢迎交流。 java解析xml的话有四种方式&#xff0c;分别为DOM,SAX,DOM4J,JDOM 转载&#xff1a;Java解析XML(4种方法)_蓝星花的博客-CSDN博客_java解析xml 这里的…
阅读更多...
Java解析XML(4种方法)

Java解析XML(4种方法)

xml文件 <?xml version"1.0" encoding"utf-8" ?> <class><student><firstname>cxx1</firstname><lastname>Bob1</lastname><nickname>stars1</nickname><marks>85</marks></stud…
阅读更多...
混合开发架构|Android工程集成React Native、Flutter、ReactJs

混合开发架构|Android工程集成React Native、Flutter、ReactJs

混合开发架构|Android工程集成React Native、Flutter、ReactJs 架构设计说明创建安卓原生工程创建Flutter集成嵌入原生工程 创建React Native解决RN报错问题集成嵌入原生工程RN集成后&#xff0c;启动报错 底部导航栏架构设计原生仿招商银行首页原生Android Socket 即时通讯Rea…
阅读更多...
Android MVVM开发框架

Android MVVM开发框架

MVVM 数据双向绑定&#xff0c;通过数据驱动UI&#xff0c;M提供数据&#xff0c;V视图&#xff0c;VM即数据驱动层 MVP框架图 &#xff08;图片来源于网络&#xff09; 学习MVVM开发框架&#xff0c;要先了解DataBinding&#xff0c;DataBinding 是什么&#xff1f; DataBi…
阅读更多...
Android： 开发框架设计

Android： 开发框架设计

Android 开发框架 Android运用框架的目的不外乎是实现代码的代码解耦、逻辑分类、快速集成 便于维护等目的&#xff1b;Android 开发架构从早期的MVC &#xff0c;到近年到MVP 架构&#xff0c;到最近流行到MVVM 架构。不断到对业务逻辑、代码结构进行分层&#xff0c;便于快速…
阅读更多...
安卓框架

安卓框架

http://www.cnblogs.com/jincheng-yangchaofan/articles/7018780.html 框架&#xff1a;提供一定能力的小段程序 一.榜单介绍 排行榜包括四大类&#xff1a; 单一框架&#xff1a;仅提供路由、网络层、UI层、通信层或其他单一功能的框架 混合开发框架&#xff1a;提供开发hy…
阅读更多...
推荐文章

Copyright @ 2022~2023