【MySQL】多表查询

5. 多表查询

 5.1 多表关系

项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系，基本上分为三种：

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

5.1.1 一对多 

  • 案例: 部门 与 员工的关系
  • 关系: 一个部门对应多个员工，一个员工对应一个部门
  • 实现: 在多的一方建立外键，指向一的一方的主键 

 5.1.2 多对多

  •  案例: 学生 与 课程的关系
  • 关系: 一个学生可以选修多门课程，一门课程也可以供多个学生选择
  • 实现: 建立第三张中间表，中间表至少包含两个外键，分别关联两方主键
create table student(id int auto_increment primary key comment '主键ID',name varchar(10) comment '姓名',no varchar(10) comment '学号'
) comment '学生表';insert into student values (null, '黛绮丝', '2000100101'),(null, '谢逊', '2000100102'),(null, '殷天正', '2000100103'),(null, '韦一笑', '2000100104');create table course(id int auto_increment primary key comment '主键ID',name varchar(10) comment '课程名称'
) comment '课程表';insert into course values (null, 'Java'),(null, 'PHP'),(null , 'MySQL'),(null, 'Hadoop');create table student_course(id int auto_increment comment '主键' primary key,studentid int not null comment '学生ID',courseid int not null comment '课程ID',constraint fk_courseid foreign key (courseid) references course (id),constraint fk_studentid foreign key (studentid) references student (id)
)comment '学生课程中间表';insert into student_course values (null,1,1),(null,1,2),(null,1,3),(null,2,2),(null,2,3),(null,3,4);

5.1.3 一对一

  • 案例: 用户 与 用户详情的关系
  • 关系: 一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率
  • 实现: 在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的(UNIQUE)

create table tb_user(id int auto_increment primary key comment '主键ID',name varchar(10) comment '姓名',age int comment '年龄',gender char(1) comment '1: 男 , 2: 女',phone char(11) comment '手机号'
) comment '用户基本信息表';create table tb_user_edu(id int auto_increment primary key comment '主键ID',degree varchar(20) comment '学历',major varchar(50) comment '专业',primaryschool varchar(50) comment '小学',middleschool varchar(50) comment '中学',university varchar(50) comment '大学',userid int unique comment '用户ID',constraint fk_userid foreign key (userid) references tb_user(id)
) comment '用户教育信息表';insert into tb_user(id, name, age, gender, phone) values (null,'渤',45,'1','18800001111'),(null,'冰冰',35,'2','18800002222'),(null,'码云',55,'1','18800008888'),(null,'李彦宏',50,'1','18800009999');insert into tb_user_edu(id, degree, major, primaryschool, middleschool, university, userid) values (null,'本科','舞蹈','静安区第一小学','静安区第一中学','北京舞蹈学院',1),(null,'硕士','表演','朝阳区第一小学','朝阳区第一中学','北京电影学院',2),(null,'本科','英语','杭州市第一小学','杭州市第一中学','杭州师范大学',3),(null,'本科','应用数学','阳泉第一小学','阳泉区第一中学','清华大学',4);

 5.2 多表查询概述

5.2.1 概述

多表查询就是指从多张表中查询数据。

原来查询单表数据，执行的SQL形式为：select * from emp;

那么我们要执行多表查询，就只需要使用逗号分隔多张表即可，如： select * from emp , dept; 具体的执行结果如下:

此时,我们看到查询结果中包含了大量的结果集，总共102条记录，而这其实就是 员工表emp所有的记录(17) 与 部门表dept所有记录(6) 的所有组合情况，这种现象称之为笛卡尔积。

笛卡尔积: 笛卡尔乘积是指在数学中，两个集合 A集合 和 B集合的所有组合情况。

而在多表查询中，我们需要消除无效的笛卡尔积，只保留两张表关联部分的数据。

在SQL语句中，我们可以给多表查询加上连接查询的条件，来去除无效的笛卡尔积。

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

 5.2.2 分类

1). 连接查询

A. 内连接：相当于查询A、B交集部分数据
B. 外连接：

  • 左外连接：查询左表所有数据，以及两张表交集部分数据
  • 右外连接：查询右表所有数据，以及两张表交集部分数据

C. 自连接：当前表与自身的连接查询，自连接必须使用表别名

2). 子查询

 5.3 内连接

内连接查询的是两张表交集部分的数据。(也就是绿色部分的数据)

内连接的语法分为两种: 隐式内连接、显式内连接。

1). 隐式内连接

SELECT 字段列表 FROM 表1 , 表2 WHERE 条件 ... ;

2). 显式内连接

SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 连接条件 ... ;

案例:

  • 查询每一个员工的姓名 , 及关联的部门的名称
  • 表结构: emp , dept
  • 连接条件: emp.dept_id = dept.id
#隐式内连接
select e.name,d.name from emp e , dept d where e.dept_id = d.id;#显式内连接
select e.name, d.name from emp e join dept d on e.dept_id = d.id;

 5.4 外连接

外连接分为两种，分别是：左外连接 和 右外连接。

1). 左外连接

SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 条件 ... ;

左外连接相当于查询表1(左表)的所有数据，当然也包含表1和表2交集部分的数据。

2). 右外连接

SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 条件 ... ;

右外连接相当于查询表2(右表)的所有数据，当然也包含表1和表2交集部分的数据。

案例:

  • 查询emp表的所有数据, 和对应的部门信息（左外连接）
  • 由于需求中提到，要查询emp的所有数据，所以是不能内连接查询的，需要考虑使用外连接查询。
  • 表结构: emp, dept
  • 连接条件: emp.dept_id = dept.id
select e.*, d.name from emp e left [ outer ] join dept d on e.dept_id = d.id;
  • 查询dept表的所有数据, 和对应的员工信息（右外连接）
  • 由于需求中提到，要查询dept表的所有数据，所以是不能内连接查询的，需要考虑使用外连接查询。
  • 表结构: emp, dept
  • 连接条件: emp.dept_id = dept.id
select d.*, e.* from emp e right [ outer ] join dept d on e.dept_id = d.id;

注意事项：
左外连接和右外连接是可以相互替换的，只需要调整在连接查询时SQL中，表结构的先后顺序就可以了。而我们在日常开发使用时，更偏向于左外连接。 

 5.5 自连接

5.5.1 自连接查询

自连接查询，顾名思义，就是自己连接自己，也就是把一张表连接查询多次。

SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ... ;

而对于自连接查询，可以是内连接查询，也可以是外连接查询。

案例：

  • 查询员工 及其 所属领导的名字
  • 表结构: emp
select a.name , b.name from emp a , emp b where a.managerid = b.id;
  • 查询所有员工 emp 及其领导的名字 emp , 如果员工没有领导, 也需要查询出来
  • 表结构: emp a , emp b
select a.name '员工', b.name '领导' from emp a left join emp b on a.managerid = b.id;

注意事项:
在自连接查询中，必须要为表起别名，要不然我们不清楚所指定的条件、返回的字段，到底是哪一张表的字段。

 5.5.2 联合查询

对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集。

SELECT 字段列表 FROM 表A ...
UNION [ ALL ]
SELECT 字段列表 FROM 表B ....;
  • 对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。
  • union all 会将全部的数据直接合并在一起，union 会对合并之后的数据去重。

 5.6 子查询

5.6.1 概述

1). 概念

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

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

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

2). 分类

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

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

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

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

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

根据子查询位置，分为：

A. WHERE之后

B. FROM之后

C. SELECT之后

5.6.2 标量子查询

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

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

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

5.6.3 列子查询

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

案例:

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

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

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

select * from emp where salary > all ( select salary from emp where dept_id = 
(select id from dept where name = '财务部') );

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

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

5.6.4 行子查询

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

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

案例:

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

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

5.6.5 表子查询

子查询返回的结果是多行多列，这种子查询称为表子查询。
常用的操作符：IN

案例:

A. 查询入职日期是 "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 ;

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：http://www.xdnf.cn/news/17557.html

如若内容造成侵权/违法违规/事实不符，请联系一条长河网进行投诉反馈，一经查实，立即删除！

相关文章

2024-11-16 串的存储结构

2024-11-16 串的存储结构

一、顺序存储。 1.首先定一个静态数组&#xff0c;然后定义i记录串的实际长度。&#xff08;缺点&#xff1a;长度不可变&#xff09; 2.使用malloc申请动态空间&#xff0c;定义指针指向串的地址。&#xff08;需手动ferr&#xff09; 方案一&#xff1a; 数组末尾记录长度 …
阅读更多...
nodejs21: 快速构建自定义设计样式Tailwind CSS

nodejs21: 快速构建自定义设计样式Tailwind CSS

Tailwind CSS 是一个功能强大的低级 CSS 框架&#xff0c;只需书写 HTML 代码&#xff0c;无需书写 CSS&#xff0c;即可快速构建美观的网站。 1. 安装 Tailwind CSS React 项目中安装 Tailwind CSS&#xff1a; 1.1 安装 Tailwind CSS 和相关依赖 安装 Tailwind CSS: npm…
阅读更多...
Windows 安装Docker For Desktop概要

Windows 安装Docker For Desktop概要

Windows 安装docker 下载部分的工作需要使用科学技术。如果没有可以联系博主发送已下载好的文件。 本文档不涉及技术的讲解&#xff0c;仅有安装的步骤。 准备工作 包含下载与环境准备&#xff0c;下载的文件仅下载&#xff0c;在后续步骤进行安装。 微软关于wsl的文档&…
阅读更多...
对称加密算法DES的实现

对称加密算法DES的实现

一、实验目的 1、了解对称密码体制基本原理 2、掌握编程语言实现对称加密、解密 二、实验原理 DES 使用一个 56 位的密钥以及附加的 8 位奇偶校验位&#xff0c;产生最大 64 位的分组大小。这是一个迭代的分组密码&#xff0c;使用称为 Feistel 的技术&#xff0c;其中将加密…
阅读更多...
三十八、Python（pytest框架-上）

三十八、Python（pytest框架-上）

一、介绍 框架&#xff08;framework&#xff09;&#xff1a;框架是为解决一类事情的功能集合。 pytest框架&#xff1a;pytest框架是单元测试框架&#xff0c;这是第三方框架想要使用必须要安装&#xff0c;可以使用pytest来作为自动化测试执行框架&#xff0c;用来管理测试…
阅读更多...
《Django 5 By Example》阅读笔记：p165-p210

《Django 5 By Example》阅读笔记：p165-p210

《Django 5 By Example》学习第6天&#xff0c;p165-p210总结&#xff0c;总计46页。 一、技术总结 1.bookmarks项目 (1)登录认证 作者这里使用的是Django自带的auth。 (2)上传头像 图片处理&#xff0c;使用Pillow。 (3)扩展user 扩展user模型与自带的user使用外键进行…
阅读更多...
shell基础（3）

shell基础（3）

声明&#xff01; 学习视频来自B站up主 **泷羽sec** 有兴趣的师傅可以关注一下&#xff0c;如涉及侵权马上删除文章&#xff0c;笔记只是方便各位师傅的学习和探讨&#xff0c;文章所提到的网站以及内容&#xff0c;只做学习交流&#xff0c;其他均与本人以及泷羽sec团…
阅读更多...
JVM面试题总结

JVM面试题总结

1.介绍一下JVM的内存结构 JDK1.8及以后&#xff0c;JVM主要分为元空间、堆、虚拟机栈、本地方法栈、程序计数器五个部分&#xff0c;另外还有一个直接内存部分&#xff0c;是直接属于操作系统的。 其中元空间、堆是线程共享的&#xff0c;虚拟机栈、本地方法栈、程序计数器是线…
阅读更多...
小新Pro 14 AHP9 2024款(83D3)原装oem预装系统Win11恢复安装包下载

小新Pro 14 AHP9 2024款(83D3)原装oem预装系统Win11恢复安装包下载

适用品牌机型 &#xff1a;LENOVO联想【83D3】 链接&#xff1a;https://pan.baidu.com/s/10RAxNdvYPWJ21b_4--Y7Xw?pwdo5ju 提取码&#xff1a;o5ju 联想原装出厂Windows11系统自带所有驱动、出厂主题壁纸、系统属性联机支持标志、系统属性专属LOGO标志、Office365办公软…
阅读更多...
【论文笔记】Towards Privacy-Aware Sign Language Translation at Scale

【论文笔记】Towards Privacy-Aware Sign Language Translation at Scale

&#x1f34e;个人主页&#xff1a;小嗷犬的个人主页 &#x1f34a;个人网站&#xff1a;小嗷犬的技术小站 &#x1f96d;个人信条&#xff1a;为天地立心&#xff0c;为生民立命&#xff0c;为往圣继绝学&#xff0c;为万世开太平。 基本信息 标题: Towards Privacy-Aware Si…
阅读更多...
Spring：bean的配置

Spring：bean的配置

对于bean的配置中&#xff0c;主要会讲解bean基础配置,bean的别名配置,bean的作用范围配置(重点),这三部分内容&#xff1a; bean基础配置 id与class配置 bean的name属性 bean的别名配置 bean作用范围scope配置 scope使用后续思考 介绍完scope属性以后&#xff0c;我们…
阅读更多...
贴代码框架PasteForm特性介绍之markdown和richtext

贴代码框架PasteForm特性介绍之markdown和richtext

简介 PasteForm是贴代码推出的 “新一代CRUD” &#xff0c;基于ABPvNext&#xff0c;目的是通过对Dto的特性的标注&#xff0c;从而实现管理端的统一UI&#xff0c;借助于配套的PasteBuilder代码生成器&#xff0c;你可以快速的为自己的项目构建后台管理端&#xff01;目前管…
阅读更多...
【RK3588 Linux 5.x 内核编程】-内核中的链表(Linked List)及使用

【RK3588 Linux 5.x 内核编程】-内核中的链表(Linked List)及使用

内核中的链表(Linked List)及使用 文章目录 内核中的链表(Linked List)及使用1、Linked List介绍2、Linux内核中的链表3、链表的操作3.1链表初始化3.2 创建节点3.3 添加节点3.4 删除节点3.5 替换节点3.6 移动节点3.7 链表旋转3.8 链表检测3.9 链表分割与合并3.10 链表遍历4、驱…
阅读更多...
永夜星河主题特效2（星河背景 + 闪烁文字+点击星星 + 文字弹出特效）

永夜星河主题特效2（星河背景 + 闪烁文字+点击星星 + 文字弹出特效）

目录 图片展示 星河背景 闪烁文字点击星星 文字弹出特效 特效介绍&#xff1a; 使用方式&#xff1a; 图片展示 星河背景 闪烁文字点击星星 文字弹出特效 <!DOCTYPE html> <html lang"en"><head><meta charset"UTF-8">&l…
阅读更多...
通过JS实现下载图片到本地教程分享

通过JS实现下载图片到本地教程分享

今天分享的这个方法我之前自己试了一下&#xff0c;感觉还行&#xff0c;原理就是通过<a>标签的新增属性实现的&#xff0c;然后可以强制触发下载功能&#xff0c;废话不多说&#xff0c;直接上教程。 首先在HTML写下面的代码: <a href"img.jpg" download…
阅读更多...
Harmony错题本--@Preview标注上依然无法预览

Harmony错题本--@Preview标注上依然无法预览

初学HarmonyOs开发&#xff0c;写了一个超级简单的组件&#xff0c;但是代码上没有什么问题&#xff0c;DevEco Studio却无法完成预览 代码如下&#xff1a; // 单纯的右键-> ArkTsFile的话&#xff0c;可以创建一个组件。 // 原因是&#xff0c;之前我们学过通过右键->…
阅读更多...
【linux学习指南】VSCode部署Ubantu云服务器，与Xshell进行本地通信文件编写

【linux学习指南】VSCode部署Ubantu云服务器，与Xshell进行本地通信文件编写

文章目录 &#x1f4dd;前言&#x1f320; 步骤&#x1f309;测试同步 &#x1f6a9;总结 &#x1f4dd;前言 本文目的是讲使用Vscode连接Ubantu,与本地Xshell建立通信同步文件编写。 查看本机系统相关信息&#xff1a; cat /etc/lsb*DISTRIB_IDUbuntu: 表示这是 Ubuntu 发行…
阅读更多...
ES-针对某个字段去重后-获取某个字段值的所有值

ES-针对某个字段去重后-获取某个字段值的所有值

针对上面表的数据&#xff0c;现在想根据age分组&#xff0c;并获取每个分组后的name有哪些(去重后)。 select age, GROUP_CONCAT(DISTINCT(name)) from testtable group by age ; 结果&#xff1a; 如果想要增加排序&#xff1a; SELECT age, GROUP_CONCAT(DISTINCT name)…
阅读更多...
基于java+SpringBoot+Vue的在线考试系统设计与实现

基于java+SpringBoot+Vue的在线考试系统设计与实现

项目运行 环境配置&#xff1a; Jdk1.8 Tomcat7.0 Mysql HBuilderX&#xff08;Webstorm也行&#xff09; Eclispe&#xff08;IntelliJ IDEA,Eclispe,MyEclispe,Sts都支持&#xff09;。 项目技术&#xff1a; Springboot mybatis Maven mysql5.7或8.0等等组成&#x…
阅读更多...
一文详细深入总结服务器选型

一文详细深入总结服务器选型

1. 题记&#xff1a; 服务器选型工作是项目规划检讨的一项非常重要的工作&#xff0c;本文详细深入总结服务器选型。 2. 服务器基础知识概览 2.1 服务器的定义与功能 2.1 .1 定义 服务器是一种高性能计算机&#xff0c;其设计目的是在网络中提供服务。它可以处理来自多个客…
阅读更多...
最新文章

Copyright @ 2022~2023