2024Mysql And Redis基础与进阶操作系列(6)作者——LJS[含MySQL 多表之一对一/多;多对多;多表联合查询等详解步骤及常见报错问题所对应的解决方法]

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
Redis 开源版,标准版 2GB
推荐场景:
搭建游戏排行榜
简介: MySQL 多表之一对一/多;多对多;多表联合之交叉连接;内连接;左、右、外、满、连接;子查询及关键字;自连接查询等详解步骤及常见报错问题所对应的解决方法

欢迎各位彦祖与热巴畅游本人专栏与博客

你的三连是我最大的动力

以下图片仅代表专栏特色 [点击箭头指向的专栏名即可闪现]

专栏跑道一

➡️网络空间安全——全栈前沿技术持续深入学习

image.gif

专栏跑道二

➡️ 24 Network Security -LJS

image.gif

image.gif

image.gif

专栏跑道三


➡️ MYSQL REDIS Advance operation

image.gif

专栏跑道四

➡️HCIP;H3C-SE;CCIP——LJS[华为、华三、思科高级网络]

image.gif

专栏跑道五

➡️RHCE-LJS[Linux高端骚操作实战篇]

image.png

专栏跑道六

➡️数据结构与算法[考研+实际工作应用+C程序设计]

image.gif

专栏跑道七

➡️RHCSA-LJS[Linux初级及进阶骚技能]

image.gif

image.gif

上节回顾



MySQL多表操作

1多表关系

  • 简介
  • :一个商城项目就需要分类表(category)、商品表(products)、订单表(orders)等多张表。且这些表 的数据之间存在一定的关系,如下图:
  • image.gif 编辑编辑
  • MySQL多表之间的关系可以概括为:一对一、一对多/多对一关系,多对多  

1.1 一对一关系

  • 比如
  • 一个学生只有一张身份证;
  • 一张身份证只能对应一学生。 在任一表中添加唯一外键,指向另一方主键确保一对一关系
  • 一般一对一关系很少见,遇到一对一关系的表最好是合并表
  • image.gif

1.2 一对多/多对一关系

  • 比如:
  • 部门和员工关系:一个部门有多个员工,一个员工只能对应一个部门
  • 实现规则:
  • 多的一方建立外键指向一的一方的主键
  • image.gif

1.3 多对多关系

  • 举例:
  • 学生和课程关系:一个学生可以选择很多门课程,一个课程也可以被很多学生选择
  • 规则:
  • 多对多关系实现需要借助第三张中间表。
  • 中间表至少包含两个字段,将多对多关系,拆成一对多的关系,中间表至少要有两个外键,这两个外键分别指向原来的那两张表的主键
  • image.gif 编辑

2. 多表联合查询

  • 简介
  • 多表查询就是同时查询两个或两个以上的表,因为有的时候在查看数据的时候,需要显示的数据来自多张表.
  • 多表查询有以下分类
  • 知识补充——笛卡尔积(了解即可)
  • 笛卡尔积是指两个集合之间所有可能的有序对的集合
  • 在数据库中,如果你对两个表进行交叉连接查询(CROSS JOIN),结果会是这两个表的笛卡尔积,即每一行的组合会和另一个表的每一行组合在一起。这通常会产生大量的数据因为行数是两个表行数的乘积。
  • 交叉连接查询 [产生笛卡尔积]
select * from A,B;
  • image.gif
  • 内连接查询(使用的关键字 inner join -- inner可以省略)
#隐式内连接
select * from A,B where 条件; 
显示内连接
select * from A inner join B on 条件;
select * from A inner join B on 条件;
  • image.gif

#左外连接:left outer join 
select * from A left outer join B on 条件; 
#右外连接:right outer join
select * from A right outer join B on 条件; 
#满外连接: full outer join 
select * from A full outer join B on 条件;
  • image.gif
  • 子查询        select的嵌套表自关联: 将一张表当成多张表来用
  • image.gif 编辑

2.1 数据准备——详解步骤如下

  • 准备查询数据
use mydb3;
  • image.gif
  • 创建部门表
create table if not exists dept3(
    deptno varchar(20) primary key ,  #部门号
 
    name varchar(20) # 部门名字
);
  • image.gif
  • 创建员工表
create table if not exists emp3(
 
    eid varchar(20) primary key , #员工编号
 
    ename varchar(20), # 员工名字
 
    age int, # 员工年龄
 
    dept_id varchar(20)  #员工所属部门
);
  • image.gif
  • 给dept3表添加数据
insert into dept3 values('1001','研发部');
insert into dept3 values('1002','销售部');
insert into dept3 values('1003','财务部');
insert into dept3 values('1004','人事部');
  • image.gif
  • 给emp表添加数据
insert into emp3 values('1','乔峰',20, '1001');
insert into emp3 values('2','段誉',21, '1001');
insert into emp3 values('3','虚竹',23, '1001');
insert into emp3 values('4','阿紫',18, '1001');
insert into emp3 values('5','扫地僧',85, '1002');
insert into emp3 values('6','李秋水',33, '1002');
insert into emp3 values('7','鸠摩智',50, '1002');
 
insert into emp3 values('8','天山童姥',60, '1003');
insert into emp3 values('9','慕容博',58, '1003');
insert into emp3 values('10','丁春秋',71, '1005');
  • image.gif

2.2 交叉连接查询

  • 交叉连接查询
交叉连接查询返回被连接的两个表所有数据行笛卡尔积
笛卡尔积可以理解为一张表的每一行去和另外一张表的任意一行进行匹配
假如A表有m行数据,B表有n行数据,则返回m*n行数据
笛卡尔积会产生很多冗余的数据,后期的其他查询可以在该集合的基础上进行条件筛选
  • 格式
select * from 表1,表2,表3….;
  • image.gif
  • 示例
select * from dept3,emp3;
  • image.gif

2.3 内连接查询

  • 格式
  • 隐式内连接
select * from A,B where 条件;
  • image.gif
  • 显示内连接
select * from A inner join B on 条件;
  • image.gif
  • 示例
  • 查询每个部门的所属员工
select * from dept3,emp3 where dept3.deptno = emp3.dept_id;
select * from dept3 inner join emp3 on dept3.deptno = emp3.dept_id;
  • image.gif
  • 查询研发部和销售部的所属员工
select * from dept3,emp3 where dept3.deptno = emp3.dept_id and name in( '研发
部','销售部');
select * from dept3 join emp3 on dept3.deptno = emp3.dept_id and name in( '研发
部','销售部');
  • image.gif
  • 查询每个部门的员工数,并升序排序
select deptno,count(1) as total_cnt from dept3,emp3 where dept3.deptno = emp3.dept_id group by deptno order by total_cnt;
select deptno,count(1) as total_cnt from dept3 join emp3 on dept3.deptno = emp3.dept_id group by deptno order by total_cnt;
  • image.gif
  • 查询人数大于等于3的部门,并按照人数降序排序
select deptno,count(1) as total_cnt from dept3,emp3 where dept3.deptno = emp3.dept_id group by deptno having total_cnt >= 3 order by total_cnt desc;
 
select deptno,count(1) as total_cnt from dept3 join emp3 on dept3.deptno = emp3.dept_id group by deptno having total_cnt >= 3 order by total_cnt desc;
  • image.gif

2.4 外连接查询

  • 简介
  • 外连接分为左外连接(left outer join)、右外连接(right outer join),满外连接(full outer join)。
  • 外连接(Outer Join)是一种 SQL JOIN 操作,它允许从一个表中选择所有的记录,而无论是否在另一个表中有匹配的记录。
  • 如果记录在另一个表中没有匹配,那么结果集中的值将为 NULL
  • 注意:
  • oracle里面有full join,可是在mysql对full join支持的不好。我们可以使用union来达到目的。
  • 格式
  • 左外连接
left outer join  select * from A left outer join B on 条件;
  • image.gif
  • 右外连接:
right outer join  select * from A right outer join B on 条件;
  • image.gif
  • 满外连接:
full outer join  select * from A full outer join B on 条件;
  • image.gif
  • image.gif 编辑
  • 外连接查询——举例
#查询哪些部门有员工,哪些部门没有员工
use mydb3;
select * from dept3 left outer join emp3 on dept3.deptno = emp3.dept_id; 
#查询哪些员工有对应的部门,哪些没有
select * from dept3 right outer join emp3 on dept3.deptno = emp3.dept_id;
#使用union关键字实现左外连接和右外连接的并集
select * from dept3 left outer join emp3 on dept3.deptno = emp3.dept_id union select * from dept3 right outer join emp3 on dept3.deptno = emp3.dept_id;
  • image.gif

2.5 子查询


  • 简介
  • 子查询就是指的在一个完整的查询语句之中,嵌套若干个不同功能的小查询从而一起完成复杂查询的一种编写形式,通俗一点就是包含select嵌套的查询。
  • 特点
  • 子查询可以返回的数据类型一共分为四种

  • 子查询回显的数据类型
  • 单行单列
  • 返回的是一个具体列的内容,可以理解为一个单值数据;
  • 单行多列
  • 返回一行数据中多个列的内容;
  • 多行单列
  • 返回多行记录之中同一列的内容,相当于给出了一个操作范围;
  • 多行多列
  • 查询返回的结果是一张临时表
  • 举例
  • 查询年龄最大的员工信息,显示信息包含员工号、员工名字,员工年龄
select eid,ename,age from emp3 where age = (select max(age) from emp3);
  • image.gif
  • 查询年研发部和销售部的员工信息,包含员工号、员工名字
select eid,ename,t.name from emp3 where dept_id in (select deptno,name from dept3 where name = '研发部' or name = '销售部') ;
  • image.gif
  • 查询研发部20岁以下的员工信息,包括员工号、员工名字,部门名字
select eid,age,ename,name from (select * from dept where name = '研发部 ')t1,(select * from emp3 where age <20)t2
  • image.gif
  • 子查询关键字
  • 1.ALL关键字格式
select …from …where c > all(查询语句)
#相当于:
select ...from ... where c > result1 and c > result2 and c > result3
  • image.gif
  • 特点
  • ALL关键字特点
ALL: 与子查询返回的所有值比较为true 则返回true
ALL可以与=、>、>=、、、<>结合是来使用,分别表示等于、大于、大于等于、小于、小于等于、不等于其中的所有数据。

ALL表示指定列中的值必须要大于子查询集的每一个值,即必须要大于子查询集的最大值;

如果是小于号即小于子查询集的最小值。同理可以推出其它的比较运算符的情况。

  • 示例
  • 查询年龄大于‘1003’部门所有年龄的员工信息
select * from emp3 where age > all(select age from emp3 where dept_id = '1003’);
  • image.gif
  • 查询不属于任何一个部门的员工信息
select * from emp3 where dept_id != all(select deptno from dept3);
  • image.gif
  • 2.ANY关键字
  • 3.SOME关键字格式——格式
select …from …where c > any(查询语句)
#等价于
select ...from ... where c > result1 or c > result2 or c > result3
  • image.gif
  • 特点
ANY:与子查询返回的任何值比较为true 则返回true
ANY可以与=、>、>=、、、<>结合是来使用,分别表示等于、大于、大于等于、小于、小于等于、不等于其中的其中的任何一个数据。
表示指定列中的值要大于子查询中的任意一个值,即必须要大于子查询集中的最小值。同理可以推出其它的比较运算符的情况。
表示指定列中的值要大于子查询中的任意一个值,即必须要大于子查询集中的最小值。同理可以推出其它的比较运算符的情况。
  • 4.IN关键字
  • 格式
select …from …where c in(查询语句)
#等价于:
select ...from ... where c = result1 or c = result2 or c = result3
  • image.gif
  • 示例
  • 查询研发部和销售部的员工信息,包含员工号、员工名字
select eid,ename,t.name from emp3 where dept_id in (select deptno from dept3 
where name = '研发部' or name = '销售部') ;
  • image.gif
  • 5.EXISTS关键字
  • 格式
select …from …where exists(查询语句)
  • image.gif
  • 特点
  • EXISTS关键字特点
该子查询如果“有数据结果”(至少返回一行数据), 则该EXISTS() 的结果为“true”外层查询执行
该子查询如果“没有数据结果”(没有任何数据返回),则该EXISTS()的结果为“false”,外层查询不执行
EXISTS后面的子查询不返回任何实际数据,只返回真或假,当返回真时 where条件成立
注意,EXISTS关键字,比IN关键字的运算效率高,因此,对于大数据量时推荐使用EXISTS关键字
  • 示例
#查询公司是否有大于60岁的员工,有则输出
select * from emp3 a where exists(select * from emp3 b where a.age > 60);
 
# 查询有所属部门的员工信息
select * from emp3 a where exists(select * from dept3 b where a.dept_id = b.deptno);
  • image.gif

2.6 自连接查询

  • 简介
  • MySQL有时在信息查询时需要进行对表自身进行关联查询,即一张表自己和自己关联一张表当成多张表来用
  • 注意自关联时表必须给表起别名。
  • 格式
select 字段列表 from 表1 a , 表1 b where 条件;
#或者: 
select 字段列表 from 表1 a [left] join 表1 b on 条件;
  • image.gif
  • 示例
  • 创建表,并建立自关联约束
create table t_sanguo(
    
    eid int primary key ,
   
    ename varchar(20),
   
    manager_id int,
 
    foreign key (manager_id) references t_sanguo (eid)   # 添加自关联约束
);
  • image.gif
  • 添加数据
insert into t_sanguo values(1,'刘协',NULL);
insert into t_sanguo values(2,'刘备',1);
insert into t_sanguo values(3,'关羽',2);
insert into t_sanguo values(4,'张飞',2);
insert into t_sanguo values(5,'曹操',1);
insert into t_sanguo values(6,'许褚',5);
insert into t_sanguo values(7,'典韦',5);
insert into t_sanguo values(8,'孙权',1);
insert into t_sanguo values(9,'周瑜',8);
insert into t_sanguo values(10,'鲁肃',8);
  • image.gif
  • 进行关联查询
#查询每个三国人物及他的上级信息,如: 关羽 刘备
 
select * from t_sanguo a, t_sanguo b where a.manager_id = b.eid;



相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
3天前
|
弹性计算 双11 开发者
阿里云ECS“99套餐”再升级!双11一站式满足全年算力需求
11月1日,阿里云弹性计算ECS双11活动全面开启,在延续火爆的云服务器“99套餐”外,CPU、GPU及容器等算力产品均迎来了全年最低价。同时,阿里云全新推出简捷版控制台ECS Lite及专属宝塔面板,大幅降低企业和开发者使用ECS云服务器门槛。
|
21天前
|
存储 弹性计算 人工智能
阿里云弹性计算_通用计算专场精华概览 | 2024云栖大会回顾
阿里云弹性计算产品线、存储产品线产品负责人Alex Chen(陈起鲲)及团队内多位专家,和中国电子技术标准化研究院云计算标准负责人陈行、北京望石智慧科技有限公司首席架构师王晓满两位嘉宾,一同带来了题为《通用计算新品发布与行业实践》的专场Session。本次专场内容包括阿里云弹性计算全新发布的产品家族、阿里云第 9 代 ECS 企业级实例、CIPU 2.0技术解读、E-HPC+超算融合、倚天云原生算力解析等内容,并发布了国内首个云超算国家标准。
阿里云弹性计算_通用计算专场精华概览 | 2024云栖大会回顾
|
3天前
|
人工智能 弹性计算 文字识别
基于阿里云文档智能和RAG快速构建企业"第二大脑"
在数字化转型的背景下,企业面临海量文档管理的挑战。传统的文档管理方式效率低下,难以满足业务需求。阿里云推出的文档智能(Document Mind)与检索增强生成(RAG)技术,通过自动化解析和智能检索,极大地提升了文档管理的效率和信息利用的价值。本文介绍了如何利用阿里云的解决方案,快速构建企业专属的“第二大脑”,助力企业在竞争中占据优势。
|
1天前
|
人工智能 自然语言处理 安全
创新不设限,灵码赋新能:通义灵码新功能深度评测
自从2023年通义灵码发布以来,这款基于阿里云通义大模型的AI编码助手迅速成为开发者心中的“明星产品”。它不仅为个人开发者提供强大支持,还帮助企业团队提升研发效率,推动软件开发行业的创新发展。本文将深入探讨通义灵码最新版本的三大新功能:@workspace、@terminal 和 #team docs,分享这些功能如何在实际工作中提高效率的具体案例。
|
7天前
|
负载均衡 算法 网络安全
阿里云WoSign SSL证书申请指南_沃通SSL技术文档
阿里云平台WoSign品牌SSL证书是由阿里云合作伙伴沃通CA提供,上线阿里云平台以来,成为阿里云平台热销的国产品牌证书产品,用户在阿里云平台https://www.aliyun.com/product/cas 可直接下单购买WoSign SSL证书,快捷部署到阿里云产品中。
1849 6
阿里云WoSign SSL证书申请指南_沃通SSL技术文档
|
10天前
|
Web App开发 算法 安全
什么是阿里云WoSign SSL证书?_沃通SSL技术文档
WoSign品牌SSL证书由阿里云平台SSL证书合作伙伴沃通CA提供,上线阿里云平台以来,成为阿里云平台热销的国产品牌证书产品。
1789 2
|
19天前
|
编解码 Java 程序员
写代码还有专业的编程显示器?
写代码已经十个年头了, 一直都是习惯直接用一台Mac电脑写代码 偶尔接一个显示器, 但是可能因为公司配的显示器不怎么样, 还要接转接头 搞得桌面杂乱无章,分辨率也低,感觉屏幕还是Mac自带的看着舒服
|
26天前
|
存储 人工智能 缓存
AI助理直击要害,从繁复中提炼精华——使用CDN加速访问OSS存储的图片
本案例介绍如何利用AI助理快速实现OSS存储的图片接入CDN,以加速图片访问。通过AI助理提炼关键操作步骤,避免在复杂文档中寻找解决方案。主要步骤包括开通CDN、添加加速域名、配置CNAME等。实测显示,接入CDN后图片加载时间显著缩短,验证了加速效果。此方法大幅提高了操作效率,降低了学习成本。
5386 15
|
13天前
|
人工智能 关系型数据库 Serverless
1024,致开发者们——希望和你一起用技术人独有的方式,庆祝你的主场
阿里云开发者社区推出“1024·云上见”程序员节专题活动,包括云上实操、开发者测评和征文三个分会场,提供14个实操活动、3个解决方案、3 个产品方案的测评及征文比赛,旨在帮助开发者提升技能、分享经验,共筑技术梦想。
1139 152
|
21天前
|
存储 缓存 关系型数据库
MySQL事务日志-Redo Log工作原理分析
事务的隔离性和原子性分别通过锁和事务日志实现,而持久性则依赖于事务日志中的`Redo Log`。在MySQL中,`Redo Log`确保已提交事务的数据能持久保存,即使系统崩溃也能通过重做日志恢复数据。其工作原理是记录数据在内存中的更改,待事务提交时写入磁盘。此外,`Redo Log`采用简单的物理日志格式和高效的顺序IO,确保快速提交。通过不同的落盘策略,可在性能和安全性之间做出权衡。
1585 14

热门文章

最新文章

  • 1
    2024Mysql And Redis基础与进阶操作系列(8)作者——LJS[含MySQL 创建、修改、跟新、重命名、删除视图等具体详步骤;注意点及常见报错问题所对应的解决方法]
    5
  • 2
    2024Mysql And Redis基础与进阶操作系列(11)作者——LJS[你个小黑子这都还学不会嘛?你是真爱粉嘛?真是的 ~;以后请别侮辱我家鸽鸽]
    4
  • 3
    2024Mysql And Redis基础与进阶操作系列(9)作者——LJS[含MySQL存储过程之局部、系统变量、参数传递、流程控制-判断/case具体详步骤;注意点及常见报错问题所对应的解决方法]
    6
  • 4
    2024Mysql And Redis基础与进阶操作系列(10)作者——LJS[你个IKUN还学不会嘛?你是真爱粉嘛?真是的 ~;以后别侮辱我家鸽鸽]
    4
  • 5
    2024Mysql And Redis基础与进阶操作系列(7)作者——LJS[含MySQL 聚合、数学、字符创、日期、控制流函数等使用详解;注意点及常见报错问题所对应的解决方法]
    4
  • 6
    2024Mysql And Redis基础与进阶操作系列(12)作者——LJS[你个小黑子这都还学不会嘛?你是真爱粉嘛?真是的 ~;以后请别侮辱我家鸽鸽]
    5
  • 7
    2024Mysql And Redis基础与进阶操作系列(6)作者——LJS[含MySQL 多表之一对一/多;多对多;多表联合查询等详解步骤及常见报错问题所对应的解决方法]
    28
  • 8
    2024Mysql And Redis基础与进阶操作系列(5)作者——LJS[含MySQL DQL基本查询:select;简单、排序、分组、聚合、分组、分页等详解步骤及常见报错问题所对应的解决方法]
    22
  • 9
    2024Mysql And Redis基础与进阶操作系列(4-2)作者——LJS[含MySQL非空、唯一性、PRIMARY KEY、自增列/自增约束举例说明等详解步骤及常见报错问题对应的解决方法]
    8
  • 10
    2024Mysql And Redis基础与进阶操作系列(4-1)作者——LJS[含MySQL非空、唯一性、PRIMARY KEY、自增列/自增约束举例说明等详解步骤及常见报错问题对应的解决方法]
    8