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
相关文章
|
7天前
|
关系型数据库 MySQL Linux
在 CentOS 7 中通过编译源码方式安装 MySQL 数据库的详细步骤,并与使用 RPM 包安装进行了对比
本文介绍了在 CentOS 7 中通过编译源码方式安装 MySQL 数据库的详细步骤,并与使用 RPM 包安装进行了对比。通过具体案例,读者可以了解如何准备环境、下载源码、编译安装、配置服务及登录 MySQL。编译源码安装虽然复杂,但提供了更高的定制性和灵活性,适用于需要高度定制的场景。
22 3
|
8天前
|
关系型数据库 MySQL Linux
在 CentOS 7 中通过编译源码安装 MySQL 数据库的详细步骤,并与使用 RPM 包安装进行了对比。
本文介绍了在 CentOS 7 中通过编译源码安装 MySQL 数据库的详细步骤,并与使用 RPM 包安装进行了对比。内容涵盖准备工作、下载源码、编译安装、配置服务、登录设置及实践心得,帮助读者根据需求选择最适合的安装方法。
16 2
|
8天前
|
存储 SQL NoSQL
|
8天前
|
NoSQL 关系型数据库 MySQL
2024Mysql And Redis基础与进阶操作系列(8)作者——LJS[含MySQL 创建、修改、跟新、重命名、删除视图等具体详步骤;注意点及常见报错问题所对应的解决方法]
MySQL 创建、修改、跟新、重命名、删除视图等具体详步骤;举例说明注意点及常见报错问题所对应的解决方法
|
27天前
|
存储 SQL 关系型数据库
Mysql学习笔记(二):数据库命令行代码总结
这篇文章是关于MySQL数据库命令行操作的总结,包括登录、退出、查看时间与版本、数据库和数据表的基本操作(如创建、删除、查看)、数据的增删改查等。它还涉及了如何通过SQL语句进行条件查询、模糊查询、范围查询和限制查询,以及如何进行表结构的修改。这些内容对于初学者来说非常实用,是学习MySQL数据库管理的基础。
104 6
|
24天前
|
存储 关系型数据库 MySQL
Mysql(4)—数据库索引
数据库索引是用于提高数据检索效率的数据结构,类似于书籍中的索引。它允许用户快速找到数据,而无需扫描整个表。MySQL中的索引可以显著提升查询速度,使数据库操作更加高效。索引的发展经历了从无索引、简单索引到B-树、哈希索引、位图索引、全文索引等多个阶段。
57 3
Mysql(4)—数据库索引
|
27天前
|
SQL Ubuntu 关系型数据库
Mysql学习笔记(一):数据库详细介绍以及Navicat简单使用
本文为MySQL学习笔记,介绍了数据库的基本概念,包括行、列、主键等,并解释了C/S和B/S架构以及SQL语言的分类。接着,指导如何在Windows和Ubuntu系统上安装MySQL,并提供了启动、停止和重启服务的命令。文章还涵盖了Navicat的使用,包括安装、登录和新建表格等步骤。最后,介绍了MySQL中的数据类型和字段约束,如主键、外键、非空和唯一等。
62 3
Mysql学习笔记(一):数据库详细介绍以及Navicat简单使用
|
10天前
|
关系型数据库 MySQL Linux
在 CentOS 7 中通过编译源码方式安装 MySQL 数据库的详细步骤,包括准备工作、下载源码、编译安装、配置 MySQL 服务、登录设置等。
本文介绍了在 CentOS 7 中通过编译源码方式安装 MySQL 数据库的详细步骤,包括准备工作、下载源码、编译安装、配置 MySQL 服务、登录设置等。同时,文章还对比了编译源码安装与使用 RPM 包安装的优缺点,帮助读者根据需求选择最合适的方法。通过具体案例,展示了编译源码安装的灵活性和定制性。
46 2
|
13天前
|
存储 关系型数据库 MySQL
MySQL vs. PostgreSQL:选择适合你的开源数据库
在众多开源数据库中,MySQL和PostgreSQL无疑是最受欢迎的两个。它们都有着强大的功能、广泛的社区支持和丰富的生态系统。然而,它们在设计理念、性能特点、功能特性等方面存在着显著的差异。本文将从这三个方面对MySQL和PostgreSQL进行比较,以帮助您选择更适合您需求的开源数据库。
54 4
|
18天前
|
存储 关系型数据库 MySQL
如何在MySQL中创建数据库?
【10月更文挑战第16天】如何在MySQL中创建数据库?

相关产品

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