MySQL （3）表与表之间的联系多表查询

傷城~ 2022-07-15 04:26 301阅读 0赞

## **表与表之间的关系** ##

（1）一对一：一对一关系就如球队与球队所在地址之间的关系，一支球队仅有一个地址，而一个地址区也仅有一支球队。这种情况需要找出主从关系，即谁是主表，谁是从表。有了球队才有球队地址区，所以球队是主表，而球队地址是从表。  
一对一要求两个表的主键必须完全一致，通过两个表的主键建立关联关系  
![这里写图片描述][SouthEast]

（2）一对多（多对一）：最为常见的就是一对多！一对多和多对一，从球队角度来说一个球队拥有多个球员 即为一对多  
多对一：从球员角度来说多个球员属于一个球队 即为多对一  
数据表间一对多关系如下图：  
![这里写图片描述][SouthEast 1]

（3）多对多：例如学生和选修课，即一个学生可以上多门课，而每门课也可以有多个学生。这种情况通常需要创建中间表来处理多对多关系。例如再创建一张 学生\_选修课 表，给出两个外键，一个相对学生表的外键，另一个相对课程表的外键。  
![这里写图片描述][SouthEast 2]

--------------------

## **多表查询（重要）** ##

多表查询有如下几种：  
\- 合并结果集；UNION 、 UNION ALL  
\- 连接查询  
![这里写图片描述][20161102133459808]  
\- 子查询

**【1】合并结果集**  
1.作用：合并结果集就是把两个select语句的查询结果合并到一起！  
2.合并结果集有两种方式：  
（1）UNION：去除重复记录，例如：SELECT \* FROM t1 UNION SELECT \* FROM t2；  
（2）UNION ALL：不去除重复记录，例如：SELECT \* FROM t1 UNION ALL SELECT \* FROM t2。  
![这里写图片描述][20161102133601562]  
![这里写图片描述][20161102133619981]  
3.要求：被合并的两个结果：列数、列类型必须相同。

**【2】连接查询 （非常重要）**  
连接查询就是求出多个表的乘积，例如t1连接t2，那么查询出的结果就是t1\*t2。  
![这里写图片描述][20161102133746314]  
连接查询会产生笛卡尔积，假设集合A=\{a,b\}，集合B=\{0,1,2\}，则两个集合的笛卡尔积为\{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)\}。可以扩展到多个集合的情况。  
那么多表查询产生这样的结果并不是我们想要的，那么怎么去除重复的，不想要的记录呢，当然是通过条件过滤。通常要查询的多个表之间都存在关联关系，那么就通过关联关系去除笛卡尔积。

你能想像到emp和dept表连接查询的结果么？emp一共14行记录，dept表一共4行记录，那么连接后查询出的结果是56行记录。  
也就你只是想在查询emp表的同时，把每个员工的所在部门信息显示出来，那么就需要使用主外键来去除无用信息了。  
如下图： 这里我们要通过deptno来筛选出我们想要得到信息  
![这里写图片描述][SouthEast 3]

使用主外键关系做为条件来去除无用信息

SELECT * FROM emp,dept WHERE emp.deptno=dept.deptno； -- 在多表查询中，在使用列时必须指定列所从属的表，例如emp.deptno表示emp表的deptno列

上面查询结果会把两张表的 所有列都查询出来，也许你不需要那么多列，这时就可以指定要查询的列了。

SELECT emp.ename,emp.sal,emp.COMM,dept.dname 
    FROM emp,dept 
    WHERE emp.deptno=dept.deptno;

还可以为表指定别名，然后在引用列时使用别名即可。

SELECT e.ename,e.sal,e.COMM,d.dname FROM emp AS e,dept AS d WHERE e.deptno=d.deptno;[其中AS是可以省略的]

--------------------

**【3】内连接查询**   
上面的查询结果就是内连接查询，但它不是SQL标准中的查询方式。  
内连接最大的特点就是只返回两个表中互相匹配的记录，而那些不能匹配的记录就被自动去除了  
标准的内连接查询为：

SELECT * 
    FROM 表名1
    INNER JOIN 表名2 
    ON 连接规则;
    INNER JOIN 表名3
    ON 连接规则；
    -- [不使用WHERE，而是使用ON]

如果将上面查询emp和dept，使用内连接的方式就是：

SELECT * FROM emp e INNER JOIN dept d ON e.deptno=d.deptno;

可以输出同样的结果。

内连接分为 等值连接 ，自然连接 ， 不等值连接 3种

**等值连接**就是连接规则用=号连接起来，如上

**自然连接**  
大家也都知道，连接查询会产生无用笛卡尔积，我们通常使用主外键关系等式来去除它。而自然连接无需你去给出主外键等式，它会自动找到这一等式：  
两张连接的表中名称和类型完全一致的列作为条件，例如R表和S表都存在B列，并且类型一致，所以会被自然连接找到，然后将他们连接在一起  
![这里写图片描述][SouthEast 4]  
![这里写图片描述][SouthEast 5]

SELECT * FROM R NATURAL JOIN S

**不等值连接**  
不等值连接就是连接规则由等号以外的运算符号组成，例如， < , >BETWEEN等  
student表  
![这里写图片描述][SouthEast 6]  
year表  
![这里写图片描述][SouthEast 7]

SELECT s.stuname,s.stuage,y.年龄大小 FROM student AS s ,YEAR AS Y WHERE s.stuage BETWEEN y.起始年纪 AND y.终止年纪;

![这里写图片描述][SouthEast 8]  
将student表和year表连接起来，但是他们没有共同的字段，所以没法用等值连接，通过使用不等值连接，规则是如果年龄在year表的起始年纪和终止年纪之间就可以连接。

--------------------

**【4】外连接查询**  
在多表连接查询时，有时希望所有记录都被包含进去，即使没有能匹配的记录也被查询结果集包含在内，这时，内连接就满足不了需求了，这里就要用到外连接查询。  
首先我们来看看student表  
![这里写图片描述][SouthEast 6]  
score表  
![这里写图片描述][SouthEast 9]

我们来看看内链接查询的结果

SELECT s.stuid,s.stuname,c.score FROM student AS s INNER JOIN score AS c ON s.stuid = c.stuid;

![这里写图片描述][SouthEast 10]  
可以看到因为score表中没有王老五的stuid，所以它不在结果集。

这里我们就可以用到外连接查询  
外连接查询又分左外连接查询，右外链接查询。

**左外连接查询**  
左外连接查询就是以左表为主，左侧表的所有记录都包含到结果集中，而右边表中有匹配的记录包含到结果集。

SELECT s.stuid,s.stuname,c.score FROM student AS s LEFT OUTER JOIN score AS c ON s.stuid = c.stuid;

![这里写图片描述][SouthEast 11]

**右外连接查询**  
右外连接查询就是以右表为主，右侧表的所有记录都包含到结果集中，而左边表中有匹配的记录包含到结果集。

SELECT s.stuid,s.stuname,c.score FROM student AS s RIGHT OUTER JOIN score AS c ON s.stuid = c.stuid;

![这里写图片描述][SouthEast 12]

--------------------

**子查询**  
一个select语句中包含另一个完整的select语句。  
子查询就是嵌套查询，即SELECT中包含SELECT，如果一条语句中存在两个，或两个以上SELECT，那么就是子查询语句了。

子查询出现的位置：  
（1）where后，作为条为被查询的一条件的一部分；  
（2）from后，作表；

当子查询出现在where后作为条件时，还可以使用如下关键字：  
（1）any  
（2）all 所有

子查询结果集的形式：  
（1）单行单列（用于条件）  
（2）单行多列（用于条件）  
（3）多行单列（用于条件）  
（4）多行多列（用于表）

练习：

--------------------

（1）工资高于JONES的员工。  
分析：  
查询条件：工资>JONES工资，其中JONES工资需要一条子查询。

第一步：查询JONES的工资

SELECT sal FROM emp WHERE ename='JONES'

第二步：查询高于甘宁工资的员工

SELECT * FROM emp WHERE sal > (${第一步});

结果

SELECT * FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename='JONES');

--------------------

（2）查询与SCOTT同一个部门的员工。  
第一步：查询出SCOTT的部门编号

SELECT deptno FROM emp WHERE ename = 'SCOTT'

第二部：查询出 该部门编号的员工

SECECT * FROM emp WHERE deptno =(${第一步});

结果

SELECT * FRMO emp WHERE deptno = (SELECT deptno FROM emp WHERE ename = 'SCOTT');

子查询作为条件：子查询形式为单行单列

--------------------

（3）工资高于30号部门所有人的员工信息  
分析：  
SELECT \* FROM emp WHERE sal>(  
SELECT MAX(sal) FROM emp WHERE deptno=30);

查询条件：工资高于30部门所有人工资，其中30部门所有人工资是子查询。高于所有需要使用all关键字。

第一步：查询30部门所有人工资

SELECT sal FROM emp WHERE deptno=30;

第二步：查询高于30部门所有人工资的员

SELECT * FROM emp WHERE sal > ALL (${第一步})

结果

SELECT * FROM emp WHERE sal > ALL(SELECT sal FROM emp WHERE deptno=30)

子查询作为条件：子查询形式为多行单列（当子查询结果集形式为多行单列时可以使用ALL或ANY关键字）

[SouthEast]: /images/20220715/a485837a85b5486d817a4c635ef7cd75.png
[SouthEast 1]: /images/20220715/69b8e336ee864b30844f42fccee9828b.png
[SouthEast 2]: /images/20220715/825373a50ea347cfa219e6d0e3e9fc0d.png
[20161102133459808]: /images/20220715/82af51bc8fe74c99a747c43c9c4b8800.png
[20161102133601562]: /images/20220715/f0d52a28106843aa8c263bb30ce655d9.png
[20161102133619981]: /images/20220715/92f4954bdd424515967303b5cc471349.png
[20161102133746314]: /images/20220715/5cca72c9c2c546df925b26f391191c0d.png
[SouthEast 3]: /images/20220715/d86eecb6ad544e09ad9e63492f715aed.png
[SouthEast 4]: /images/20220715/84a9fc738cdd4d7c86b43f29010863bc.png
[SouthEast 5]: /images/20220715/0a826991709442fea0b48bc09e9e03d3.png
[SouthEast 6]: /images/20220715/76d18410e0d24dbdbaaaebb5fb565c4f.png
[SouthEast 7]: /images/20220715/bab3471831414361b2e0c7b748bd9196.png
[SouthEast 8]: /images/20220715/f993f54bbc9f4ce4bc1064363f1ffb99.png
[SouthEast 9]: /images/20220715/3de8a617aad34d82803ec928b959aca7.png
[SouthEast 10]: /images/20220715/221138c68a8047a39de5bfb59917a9e6.png
[SouthEast 11]: /images/20220715/bda75d3623d0400a8740a06805f4dd45.png
[SouthEast 12]: /images/20220715/6123535d4c4743c9997469e1fd88d6d8.png