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

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
Redis 开源版,标准版 2GB
推荐场景:
搭建游戏排行榜
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
简介: 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
相关文章
|
27天前
|
缓存 NoSQL 关系型数据库
Redis和Mysql如何保证数据⼀致?
在项目中,为了解决Redis与Mysql的数据一致性问题,我们采用了多种策略:对于低一致性要求的数据,不做特别处理;时效性数据通过设置缓存过期时间来减少不一致风险;高一致性但时效性要求不高的数据,利用MQ异步同步确保最终一致性;而对一致性和时效性都有高要求的数据,则采用分布式事务(如Seata TCC模式)来保障。
58 14
|
1月前
|
SQL NoSQL 关系型数据库
2024Mysql And Redis基础与进阶操作系列(13)作者——LJS[你个小黑子这都还学不会嘛?你是真爱粉嘛?真是的 ~;以后请别侮辱我家鸽鸽]
MYSQL日志之详解如何配置查看二进制、查询及慢查询日志;备份与恢复等具体详解步骤;举例说明、注意点及常见报错问题所对应的解决方法
2024Mysql And Redis基础与进阶操作系列(13)作者——LJS[你个小黑子这都还学不会嘛?你是真爱粉嘛?真是的 ~;以后请别侮辱我家鸽鸽]
|
1月前
|
存储 SQL NoSQL
|
1月前
|
存储 SQL NoSQL
2024Mysql And Redis基础与进阶操作系列(10)作者——LJS[你个IKUN还学不会嘛?你是真爱粉嘛?真是的 ~;以后别侮辱我家鸽鸽]
Mysql And Redis基础与进阶操作系列之存储函数和MySQL 触发器等具体举例以及详解步骤;注意点及常见报错问题所对应的解决方法]
|
1月前
|
NoSQL 关系型数据库 MySQL
2024Mysql And Redis基础与进阶操作系列(8)作者——LJS[含MySQL 创建、修改、跟新、重命名、删除视图等具体详步骤;注意点及常见报错问题所对应的解决方法]
MySQL 创建、修改、跟新、重命名、删除视图等具体详步骤;举例说明注意点及常见报错问题所对应的解决方法
|
NoSQL 关系型数据库 PHP
|
2月前
|
消息中间件 缓存 NoSQL
Redis 是一个高性能的键值对存储系统,常用于缓存、消息队列和会话管理等场景。
【10月更文挑战第4天】Redis 是一个高性能的键值对存储系统,常用于缓存、消息队列和会话管理等场景。随着数据增长,有时需要将 Redis 数据导出以进行分析、备份或迁移。本文详细介绍几种导出方法:1)使用 Redis 命令与重定向;2)利用 Redis 的 RDB 和 AOF 持久化功能;3)借助第三方工具如 `redis-dump`。每种方法均附有示例代码,帮助你轻松完成数据导出任务。无论数据量大小,总有一款适合你。
78 6
|
26天前
|
缓存 NoSQL 关系型数据库
大厂面试高频:如何解决Redis缓存雪崩、缓存穿透、缓存并发等5大难题
本文详解缓存雪崩、缓存穿透、缓存并发及缓存预热等问题,提供高可用解决方案,帮助你在大厂面试和实际工作中应对这些常见并发场景。关注【mikechen的互联网架构】,10年+BAT架构经验倾囊相授。
大厂面试高频:如何解决Redis缓存雪崩、缓存穿透、缓存并发等5大难题
|
28天前
|
存储 缓存 NoSQL
【赵渝强老师】基于Redis的旁路缓存架构
本文介绍了引入缓存后的系统架构,通过缓存可以提升访问性能、降低网络拥堵、减轻服务负载和增强可扩展性。文中提供了相关图片和视频讲解,并讨论了数据库读写分离、分库分表等方法来减轻数据库压力。同时,文章也指出了缓存可能带来的复杂度增加、成本提高和数据一致性问题。
【赵渝强老师】基于Redis的旁路缓存架构
|
1月前
|
缓存 NoSQL Redis
Redis 缓存使用的实践
《Redis缓存最佳实践指南》涵盖缓存更新策略、缓存击穿防护、大key处理和性能优化。包括Cache Aside Pattern、Write Through、分布式锁、大key拆分和批量操作等技术,帮助你在项目中高效使用Redis缓存。
193 22

相关产品

  • 云数据库 Tair(兼容 Redis)
  • 云数据库 RDS MySQL 版