MySQL_6 自连接和外连接

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用系列 2核4GB
简介: MySQL 第六节 表的自连接和外连接 内容分享。

目录

一、自连接

       1.概述 :

       2.语法 :

       3.演示 :

二、外连接

       1.为什么需要外连接?

       2.外连接的定义 :

       3.外连接的演示 :

               1° 左外连接

               2° 右外连接

               3° 对部门表问题的解决


一、自连接

       1.概述 :

       自连接是指在同一张表上的连接查询(将同一张看做两张表);自连接本质上就是特殊的多表查询。

       2.语法 :

       SELECT column_1 [AS alias_1], column_2 [AS alias_2]...

               FROM table_1 表别名, table_2 表别名...

               WHERE connect_expression...;

       注意事项——

        可以根据需要对查询的字段起别名

        对表进行自连接时,必须对该表起两个别名,否则报错;对表起别名时不需要用到AS。

       3.演示 :

               先来建一张职员表,如下——

CREATETABLE IF NOT EXISTS `staff`(  `sno` MEDIUMINTUNSIGNEDNOTNULL DEFAULT 0,  `sname` VARCHAR(40)NOTNULL DEFAULT '',  `ssex` CHAR(10),  `ssalary` DECIMAL(8,2),  `mgr` MEDIUMINTUNSIGNEDNOTNULL DEFAULT 0) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin ENGINE INNODB;INSERTINTO `staff`(`sno`,`sname`,`ssex`,`ssalary`,`mgr`)VALUES(1000,'Cyan','male',15000,500),(1001,'Ice','male',17000,501),(1002,'Rain','female',20000,502),(1003,'Five','female',9900,503),(1004,'Rose','female',13500,504),(1005,'Wood','male',12700,505),(500,'爷1号','male',99999,0),(501,'爷2号','male',99999,0),(502,'爷3号','male',99999,0),(503,'爷4号','male',99999,0),(504,'爷5号','male',99999,0),(505,'爷6号','male',99999,0);SELECT*FROM `staff`;

image.gif

image.png

image.gif编辑

              现要求——

               查询职员编号,职员姓名,以及职员对应的的上级编号和上级姓名。如下:  

# 相当于把第一张表当作了下属表,第二张表当作了上级表(本质是同一张表)。
SELECT  `demo_1`.`sno` AS'sub_no',  `demo_1`.`sname` AS'sub_name',  `demo_1`.`mgr` AS'sup_no',  `demo_2`.`sname` AS'sup_name'FROM  `staff` `demo_1`,  `staff` `demo_2` 
WHERE  `demo_1`.mgr= `demo_2`.sno;

image.gif

image.png

image.gif编辑


二、外连接

       1.为什么需要外连接?

       多表查询中,通过WHILE子句的条件来对笛卡尔积的结果进行过滤;但是查询结果只会显示WHERE关联条件匹配成功的记录,而不显示匹配失败的记录

               举个栗子,当前有两张表分别是雇员表emp部门表dep,如下图所示 :

image.png

image.png

               现要求——

               查询员工姓名,员工职位,员工部门号以及对应的部门名称,并且如果某个部门下没有员工,也要求显示出部门号和部门名称。

sSELECT ename,ecareer,emp.deptno,dname
FROM emp,dep
WHERE emp.deptno= dep.dno;

image.gif

image.png

image.gif编辑

               显然,普通的多表查询无法满足我们的需求。这是因为雇员表中没有出现40部门的员工,因此不满足WHERE子句的关联条件,而多表查询只会显示匹配WHERE子句条件的记录,因而也就无法显示40部门的部门号和部门名称,但40部门又确实是真实存在的,这便是多表查询的一个弊端。

       2.外连接的定义 :

       外连接最常用的有两种,左外连接和右外连接。其中,左外连接表示连接后左侧的表会完全显示右外连接则表示连接后右侧的表会完全显示

       左外连接使用格式如下——

               SELECT column_1, column_2...column_n

                       FROM table_1 LEFT JOIN table_2

                      ON connect_expression;

       右外连接使用格式如下——

               SELECT column_1, column_2...column_n

                       FROM table_1 RIGHT JOIN table_2

                       ON connect_expression;

       注意事项——

       左外连接后,左表中无法匹配查询条件的记录也会显示,但左表中不存在的字段数据自动为NULL;右外连接后,右表中无法匹配查询条件的记录也会显示,但右表中不存在的字段数据自动为NULL。

       3.外连接的演示 :

               1° 左外连接

               先来建两张表——学生表stus和成绩表scores。

               创建学生表stus的代码如下 :

CREATETABLE IF NOT EXISTS `stus`(  `id` MEDIUMINTUNSIGNEDNOTNULL DEFAULT 0,  `name` VARCHAR(32)NOTNULL DEFAULT '',  `sex` CHAR(16)NOTNULL DEFAULT '') CHARACTER SET utf8mb3 COLLATE utf8mb3_bin ENGINE INNODB;INSERTINTO `stus`(`id`, `name`, `sex`)VALUES(1,'Cyan','male'),(2,'Five','female'),(3,'Ice','male'),(4,'Rain','female'),(5,'Kaiyu','male');SELECT*FROM `stus`;

image.gif

               学生表stus效果如下 :

image.png

image.gif编辑

               创建成绩表scores的代码如下 :

CREATETABLE IF NOT EXISTS `scores`(  `id` MEDIUMINTUNSIGNEDNOTNULL DEFAULT 0,  `score` MEDIUMINTUNSIGNEDNOTNULL DEFAULT 0) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin ENGINE INNODB;INSERTINTO `scores`
VALUES(1,141),(2,135),(5,142),(7,138),(10,150);SELECT*FROM `scores`;

image.gif

              成绩表scores效果如下 :        

image.png

image.gif编辑

              现要求——

               查询学生的编号,姓名和成绩;并要求显示所有的学生,若该学生无成绩,则成绩一栏显示为空。

SELECT stus.id, `name`, score
FROM stus LEFT JOIN scores
ON stus.id= scores.id;

image.gif

image.png

image.gif编辑

               2° 右外连接

               仍然操作学生表stus和成绩表scores,

               现要求——

               要求查询学生的编号,姓名,性别及成绩;并要求显示所有的成绩,若学生表中无对应的学生,则学生的相关信息显示为空。

SELECT stus.id, `name`, sex, score
FROM stus RIGHT JOIN scores
ON stus.id= scores.id;

image.gif

image.png

image.gif编辑

               3° 对部门表问题的解决

               针对于“为什么需要外连接”中引出的关于显示所有部门的问题,现在可以通过左外连接和右外连接进行解决。

               需求如下——

               查询员工姓名,员工职位,员工部门号以及对应的部门名称,并且如果某个部门下没有员工,也要求显示出部门号和部门名称

               代码如下 :

# 方式一 : 左外连接
SELECT ename,ecareer,dep.dno,dname
FROM dep LEFT JOIN emp
ON dep.dno= emp.deptno;# 方式二 : 右外连接
SELECT ename,ecareer,dep.dno,dname
FROM emp RIGHT JOIN dep
ON emp.deptno= dep.dno;

image.gif

image.png


       System.out.println("END------------------------------------------------------------------------------");

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
9天前
|
安全 关系型数据库 MySQL
【赵渝强老师】MySQL的连接方式
本文介绍了MySQL数据库服务器启动后的三种连接方式:本地连接、远程连接和安全连接。详细步骤包括使用root用户登录、修改密码、创建新用户、授权及配置SSL等。并附有视频讲解,帮助读者更好地理解和操作。
|
23天前
|
SQL Java 关系型数据库
java连接mysql查询数据(基础版,无框架)
【10月更文挑战第12天】该示例展示了如何使用Java通过JDBC连接MySQL数据库并查询数据。首先在项目中引入`mysql-connector-java`依赖,然后通过`JdbcUtil`类中的`main`方法实现数据库连接、执行SQL查询及结果处理,最后关闭相关资源。
|
27天前
|
SQL JavaScript 关系型数据库
node博客小项目:接口开发、连接mysql数据库
【10月更文挑战第14天】node博客小项目:接口开发、连接mysql数据库
|
1月前
|
Java 关系型数据库 MySQL
【编程基础知识】Eclipse连接MySQL 8.0时的JDK版本和驱动问题全解析
本文详细解析了在使用Eclipse连接MySQL 8.0时常见的JDK版本不兼容、驱动类错误和时区设置问题,并提供了清晰的解决方案。通过正确配置JDK版本、选择合适的驱动类和设置时区,确保Java应用能够顺利连接MySQL 8.0。
140 1
|
1月前
|
Java 关系型数据库 MySQL
springboot学习五:springboot整合Mybatis 连接 mysql数据库
这篇文章是关于如何使用Spring Boot整合MyBatis来连接MySQL数据库,并进行基本的增删改查操作的教程。
72 0
springboot学习五:springboot整合Mybatis 连接 mysql数据库
|
1月前
|
SQL JavaScript 关系型数据库
Node.js 连接 MySQL
10月更文挑战第9天
19 0
|
2月前
|
SQL 关系型数据库 MySQL
MySQL C连接与使用
【9月更文挑战第21天】在 MySQL 中,可以通过 C 语言连接和操作数据库。首先需安装 MySQL 服务器及 C 开发库,然后在程序中包含必要头文件,初始化连接对象,并使用实际参数建立连接。执行 SQL 语句时,需替换表名等变量,获取并遍历结果集。最后,释放资源并关闭连接。过程中应注意错误处理、内存管理和安全性,以及性能优化。此方式适用于高效数据存储和检索的应用程序。
|
2月前
|
SQL JavaScript 关系型数据库
Node服务连接Mysql数据库
本文介绍了如何在Node服务中连接MySQL数据库,并实现心跳包连接机制。
43 0
Node服务连接Mysql数据库
|
1月前
|
关系型数据库 MySQL Linux
Navicat 连接 Windows、Linux系统下的MySQL 各种错误,修改密码。
使用Navicat连接Windows和Linux系统下的MySQL时可能遇到的四种错误及其解决方法,包括错误代码2003、1045和2013,以及如何修改MySQL密码。
203 0
|
2月前
|
关系型数据库 MySQL 数据库
docker启动mysql多实例连接报错Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’
docker启动mysql多实例连接报错Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’
179 0