数据库多表查询的各样连接，你能搞定吗？

4lqedz

VOL 268

13

2021-07

今天距2022年171天

这是ITester软件测试小栈第268次推文

点击上方蓝字“ITester软件测试小栈“关注我，每周一、三、五早上 08:30准时推送，每月不定时赠送技术书籍。

微X公众号后台回复“资源”、“测试工具包”领取测试资源，回复“微X交流群”、“内推群”一起进群打怪。

本文5580字，阅读约需14分钟

Hi，大众好。计算机行业有许多伟大的设计理念和思想，例如：

在 Unix 中，一切皆文件。

在面向对象的编程语言中，一切皆对象。

关系数据库一样亦有自己的设计思想：在 SQL 中，一切皆关系。

多表查找经常用到连接，关系型数据库连接查找中有常用到内连接、左连接、右连接、交叉连接几种查找方式，今天就给大众介绍各样连接。

首要准备数据表，如需领取Navicat Premium或本文所需建表语句，可在ITester软件测试小栈后台回复“数据库礼包”。

1.学生表

Student(s_id,s_name,s_birth,s_sex) –学生编号、学生姓名、出生年月、学生性别。

2.课程表

Course(c_id,c_name,t_id) – –课程编号、 课程名叫作、 教师编号。

3.教师表

Teacher(t_id,t_name) –教师编号、教师姓名。

4.成绩表

Score(s_id,c_id,s_score) –学生编号、课程编号、分数。

‍

一

连接查找返回的结果归类

1内连接（inner join）

定义：只返回两张表中所有满足连接要求的行，即运用比较运算符按照每一个表中共有的列的值匹配两个表中的行。

应用场景：满足某一要求的A，B内部的数据。

示例：

执行结果：

说明：inner join 是比较运算符，只返回符合要求的行。

此时相当于：select * from Student,Course where Student.s_id=Course.c_id;

2外连接（outer join）

(1)  左连接 （left join/left outer join ）

定义：左连接返回左表中的所有记录和右表中连接字段相等的记录。(左表便是写在left join关键字左边的表)。

应用场景：A的所有数据，和满足某一要求的B的数据。

示例：

运行结果：

说明：左外连接包括left join左表所有行，倘若左表中某行在右表无匹配，则结果中对应行右表的部分所有为空(NULL).注：此时咱们不可说结果的行数等于左表数据的行数。当然此处查找结果的行数等于左表数据的行数，由于上下两表此时为一对一关系。

(2)  右连接  （right join/right outer join）

定义：返回包含右表中的所有记录和左表中连接字段相等的记录。(右表便是写在right join关键字右边的表)。

应用场景：B的所有数据，和满足某一要求的A的数据。

示例：

运行结果：

说明：右外连接包括right join右表所有行，倘若左表中某行在右表无匹配，则结果中对应左表的部分所有为空(NULL)。注：一样此时咱们不可说结果的行数等于右表的行数。当然此处查找结果的行数等于左表数据的行数，由于上下两表此时为一对一关系。

(3)  全外连接（full join/full outer join） 

定义：返回上下表中所有的记录和上下表中连接字段相等的记录。

应用场景：满足某一要求的公共记录，和独有的记录。

示例：

说明：完全外连接包括full join上下两表中所有的行，倘若右表中某行在左表中无匹配，则结果中对应行右表的部分所有为空(NULL)，倘若左表中某行在右表中无匹配，则结果中对应行左表的部分所有为空(NULL)。

注：全外连接是在等值连接的基本上将左表和右表的未匹配数据都加上。然则MySQL日前不支持此种方式，倘若去掉on要求语句的话，是有查找结果的。

运行结果：

3

交叉连接（cross join）/笛卡尔积定义：交叉连接/笛卡尔积返回左表中的所有行，左表中的每一行与右表中的所有行组合。

举例：有两个集合A和B，A = {0,1} B = {2,3,4}

集合 A×B 和 B×A的结果集就能够分别暗示为以下这种形式：

A×B = {（0，2），（1，2），（0，3），（1，3），（0，4），（1，4

）}；

B×A = {（2，0），（2，1），（3，0），（3，1），（4，0），（4，1

）}；

以上A×B和B×A的结果就能够叫做两个集合相乘的‘笛卡尔积’。

从以上的数据分析咱们能够得出以下两点结论：

两个集合相乘，不满足交换率，既 A×B ≠ B×A;A集合和B集合相乘，包括了集合A中元素和集合B中元素相结合的所有的可能性，即两个集合相乘得到的新集合的元素个数是 A集合的元素个数 × B集合的元素个数。

交叉连接：

SELECT * from 表1 cross JOIN 表2；

交叉连接实质上是将两个表进行笛卡尔积运算，结果表是由于第1个表的每一行与第二个表的每一行拼接后形成的表，叫作为‘笛卡尔积表’，结果表的行数等于两个表的行数之积。

示例：

运行结果：

说明：无 WHERE 子句的交叉联接将产生连接所触及的表的笛卡尔积。第1个表的行数乘以第二个表的行数等于笛卡尔积结果集的体积。倘若咱们这里时给这条SQL加上WHERE子句的时候例如SQL:select * from Student cross join course where Student.s_id=Course.c_id;此时将返回符合要求的结果集，结果和inner join所示执行结果同样。

4自连接在一个连接查找中，触及到的两个表都是同一个表，这种查找就叫作为自连接查找。同一张表在FROM字句中多次显现，为了区别该表的每一次显现，必须为表定义一个别名。自连接是一种特殊的内连接，它指的是相互连接的表在理学上为同一张表，但能够在规律上分为两张表。

示例：

运行结果：

二

按连接要求归类

1等值连接（join）定义：运用”=”关系将表连接起来的查找，其查找结果中列出被连接表中的所有列，包含其中的重复列。示例：

运行结果：

2非等值连接

不消=连接，运算符包含>、>=、<=、<、!>、!<和<>。通常是经过外键创立索引运用等值连接，倘若无索引则必须遍历全部表，非常耗费时间，创立了索引就能够经过特定的值找到特定的记录。

3自然连接（natural  join）自然连接运算需求两个表有一起属性（列），自然连接运算的结果表是在参与操作的两个表的一起属性上进行等值连接后，再去除重复的属性后所得的新表。

三

数据库连接查找题

数据表如下：

学生表Student--stu，学生编号s_id，学生姓名s_name，出生日期s_age，性别s_sex；

成绩表Score--sc，学生编号s_id，课程编号c_id，成绩s_score；

课程表Course--co，课程编号c_id，课程名叫作c_name，教师编号t_id；

教师表Teacher--th，教师编号t_id、教师姓名t_name；

1.查找所有朋友的学生编号、学生姓名、选课总数、所有课程的总成绩；

select Student.s_id,s_name,count(c_id) 选课数量,ifnull(sum(s_score),0) 总成绩

from Student left join Score on Student.s_id=Score.s_id

group by Student.s_id;

查找结果：

2.查找课程编号为01且课程成绩在80分以上的学生的学号和姓名；

select Student.s_id,s_name ,Score.s_score

from Student join Score on Student.s_id=Score.s_id

where c_id="01" and s_score>=80;

查找结果：

3.查找两门及其以上不及格课程的朋友的学号，姓名及其平均成绩；

select Student.s_id,s_name,avg(s_score) 平均成绩

from Student join Score on Student.s_id=Student.s_id

group by Student.s_id

having sum(s_score<60)>1;

查找结果：

4.查找无参与任意课程的学生信息；

select *

from

Student stu

left join Score sc on stu.s_id = sc.s_id

where

sc.s_id is null;

运行结果：

5.查找Jessie老师的授课信息；

select

*

from

Teacher th

inner join Course co on th.t_id = co.t_id

where

th.t_name = Jessie老师;

查找结果：

6.查找Jessie老师每个授课课程的学员人数；

select

co.c_id,

co.c_name,

th.t_name,

count( * )

from

Teacher th

inner join Course co on th.t_id = co.t_id

inner join Score sc on sc.c_id = co.c_id

where

th.t_name = Jessie老师

group by

co.c_id,

co.c_name;

查找结果：

7.无报名Jessie老师课程的学生信息；

select

*

from

Teacher th

inner join Course co on th.t_id = co.t_id

and th.t_name= Jessie老师

inner join Score sc on co.c_id= sc.c_id

right join Student stu on stu.s_id= sc.s_id

where

sc.s_id is null;

查找结果：

8.查找报名Jessie老师课程的学生信息;

select

*

from

Student stu

inner join Score sc on stu.s_id = sc.s_id

inner join Course co on sc.c_id = co.c_id

inner join Teacher th on co.t_id = th.t_id

where

th.t_name= Jessie老师;

查找结果：

9.查找CoCo的数学成绩；

select

*

from

Student stu

inner join Score sc on stu.s_id = sc.s_id

inner join Course co on co.c_id = sc.c_id

where

stu.s_name = CoCo

and co.c_name= 数学;

查找结果：

10.查找出语文最高分；

select

max(sc.s_score),

co.c_name

from

Student stu

inner join Score sc on stu.s_id = sc.s_id

inner join Course co on sc.c_id= co.c_id

where

co.c_name = 语文;

查找结果：

11.查找无参与语文考试的学生信息；

select

*

from

Course co

inner join Score sc on co.c_id = sc.c_id

and co.c_name = 语文

right join Student stu on stu.s_id= sc.s_id

where

sc.s_id is null;

查找结果：

12.查找语数外三门成绩的平均分；

select

co.c_id,

co.c_name,

avg(sc.s_score)

from

Student stu

inner join Score sc on stu.s_id = sc.s_id

inner join Course co on sc.c_id = co.c_id

group by

co.c_id,

co.c_name;

查找结果：

四

连接查找关联问题

1你在测试工作中，常用到那些连接查找？

1）inner join（内连接）：倘若表中有最少一个匹配，则返回行（在语法中能够省略inner关键字）；

2）left join（左连接）：从左表返回所有的行，倘若右表中无匹配，对应的列返回Null；

3）right join（右连接）：从右表返回所有的行 ，倘若左表中无匹配，对应的列返回Null；

4）full join（全连接）：只要其中一个表中存在匹配，则返回行（即结合左连接和右连接的结果）；

2等值连接和自然连接的区别？

1）等值连接中不需求相等属性值的属性名相同，而自然连接需求相等属性值的属性名必要相同，即两关系仅有在同名属性才可进行自然连接。

2）等值连接不将重复属性去掉，而自然连接去掉重复属性，亦能够说，自然连接是去掉重复列的等值连接。

3触及多表连接查找，你的思路是？

1）

分析查找列源自的表，形成from子句；

2）

分析表的相关关系，如两个表之间无直接相关关系，而是经过另一个中间表相关，则亦要在from子句中弥补中间相关表；

3）

在where子句中弥补表之间的相关关系，N个表通常要N-1个相关关系；

4）

分析是不是必须其他限制要求弥补到where子句的表相关关系之后，做为限制要求；

5）

按照需求弥补select子句；

6）分析是不是需求排序，如要且触及其他表就要再次执行2）、3）、4）步； 以上That‘s all

更加多系列文案

敬请期待ITester软件测试小栈（ID：ITestingA），专注于软件测试技术和宝藏干货分享，每周准时更新原创技术文案，每月不定时赠送技术书籍，愿咱们在更高处相逢。爱好记得星标⭐我，每周即时得到最新推送，第三方转载请注明出处。ITester软件测试小栈往期内容宠幸1.Python接口自动化-接口基本(一)

2.Python接口自动化-接口基本(二)

3.Python接口自动化-requests模块之get请求

4.Python接口自动化-requests模块之post请求

5.Python接口自动化之cookie、session应用

6.Python接口自动化之Token详解及应用

7.Python接口自动化之requests请求封装

8.Python接口自动化之pymysql数据库操作

9.Python接口自动化之logging日志

10.Python接口自动化之logging封装及实战

想获取更加多最新干货内容快来星标 置顶 关注我每周一、三、五 08:30见

<<  向右滑动查看下一张照片  >>

 后台 回复"资源"取干货回复"微X群"一块打怪升级

个人微X：Cc2015123

添加请注明来意

真爱四连，BiuBiuBiu~

akbchina.cn

我完全同意你的观点，说得太对了。

nqkk58

我完全同意你的看法，期待我们能深入探讨这个问题。

4lqedz

你的见解独到，让我受益匪浅，非常感谢。