MySQL多表连接查询，子查询【MySQL】

最新推荐文章于 2023-07-27 11:30:00 发布

码农C风

最新推荐文章于 2023-07-27 11:30:00 发布

阅读量1.2k

点赞数 1

分类专栏：数据库养成文章标签： mysql 分类数据库

本文链接：https://blog.csdn.net/a23452/article/details/121367860

版权

数据库养成专栏收录该内容

20 篇文章 3 订阅

订阅专栏

Java养成计划----学习打卡第六十八天

内容导航

子查询

Java（打卡第六十八天)

MySQL多表连接查询（MySQL99)

昨天我们已经大概分享了MySQL的多表的MySQL92语法，该语法的缺点就是将多表连接条件和普通的过滤条件混杂再一起，同时介绍了等值连接，就是连接的条件是等量条件

mysql> SELECT
    -> f.fruits_name,c.language
    -> FROM
    -> fruits f
    -> JOIN
    -> culture c
    -> ON
    -> f.fruits_origin = c.location;
+-------------+----------+
| fruits_name | language |
+-------------+----------+
| 葡萄        | 山东话   |
| 苹果        | 山东话   |
| 香蕉        | 海南话   |
+-------------+----------+
3 rows in set (0.01 sec)

接下来就介绍内连接的非等值连接

内连接之非等值连接

内连接的非等值连接的意思就是两张表连接的条件不再是等量条件，而是其他的条件

也就是关键词ON后面的语句是非等量条件

这里举个例子，比如两张对于cfeng库中的fruits表中的price进行定级，那么就会使用到非等值条件

mysql> SELECT
    ->  f.fruits_name '水果名称',f.fruits_price '水果价格',g.grade '价格等级'
    -> FROM
    -> fruits f
    -> JOIN
    -> fruitsgrade g
    -> ON
    -> f.fruits_price BETWEEN g.lowpri AND g.highpri;
+----------+----------+----------+
| 水果名称 | 水果价格 | 价格等级 |
+----------+----------+----------+
| 葡萄     |      3.7 | 一般     |
| 苹果     |      2.8 | 便宜     |
| 香蕉     |        6 | 昂贵     |
+----------+----------+----------+
3 rows in set (0.00 sec)

可以看出条件不是等量关系，其中JOIN 和INNER JOIN一样

内连接之自连接【关键是看成两张表】

比如这里给出一张员工表，表中包含员工的id，name和领导id，现在要求查表获得每一个员工的上级领导的名字

处理方法就是将一张表当作两张表来看待

所以这道题目的处理方法就是以后看成两张表，一张表是员工表，一张表是领导表，查询的就是一个等值查询，员工表的mgl和领导表的id相等为查询条件

这里是原表

mysql> SELECT
    -> *
    -> FROM
    -> emp;
+----+-------+-------+
| id | name  | splid |
+----+-------+-------+
|  1 | zhang |     4 |
|  2 | wang  |     3 |
|  3 | liu   |     4 |
|  4 | huang |     5 |
|  5 | hu    |     0 |
+----+-------+-------+
5 rows in set (0.00 sec)

那就当作等值查询处理，JOIN时同样还是同一张表，可以取别名来区分

mysql> SELECT
    -> e.name '员工姓名',b.name '领导姓名'
    -> FROM
    -> emp e
    -> JOIN
    -> emp b
    -> ON
    -> e.splid = b.id;     //两个字段相同
+----------+----------+
| 员工姓名 | 领导姓名 |
+----------+----------+
| wang     | liu      |
| liu      | huang    |
| zhang    | huang    |
| huang    | hu       |
+----------+----------+
4 rows in set (0.00 sec)

这里就给员工表取别名e，给领导表取别名b；但是它们都是表emp

上面的例子就是内连接，注意这里的查询记录比表少一条

外连接 LEFT RIGHT 【OUTER可以省略】

内连接两张表是平等关系，外连接两张表是主次关系

LEFT OUTER RIGHT OUTER

任何一个左连接都有右连接的写法，右连接同理

LEFT 将JOIN关键字左边的表看成主表，主要是为了将主表的数据全部查询出来，只是捎带着查询右表
RIGHT将JOIN关键字右边的表看成主表，主要是为了将主表的数据全部查询出来，只是捎带着查询左表

其实简单一点就是为了选择一张主表，主表的特权就是不仅仅显示匹配到的数据，没有匹配到的数据也可以显示【也即是可以显示所有数据】

内连接的特点就是 — 完全能够匹配上这个条件的数据才查询出来

比如上面的水果内连接

+----------+----------+
| location | language |
+----------+----------+
| 四川     | 四川话   |
| 山东     | 山东话   |
| 海南     | 海南话   |
+----------+----------+

这是culture表，一共有3行记录可以匹配

然后使用SQL语句之后

+-------------+----------+
| fruits_name | language |
+-------------+----------+
| 葡萄        | 山东话   |
| 苹果        | 山东话   |
| 香蕉        | 海南话   |
+-------------+----------+
3 rows in set (0.01 sec)

可以看到最后的显示结果并不包含culture表的第一行记录，这就是内连接的只有满足查询条件的查询数据才查询出来导致的

那如果这里想将没有匹配上的数据查询到怎么办？

这个时候就要使用外连接

外连接的语法格式

SELECT …… FROM table1 RIGHT JOIN table2 ……

这是右连接，代表的是没有查询结果会显示右边表中没有匹配到的数据，也就是JOIN后的表

而上面的查询刚好就是JOIN表有数据还有剩余

mysql> SELECT
    -> f.fruits_name,c.language
    -> FROM
    -> fruits f
    -> RIGHT JOIN
    -> culture c
    -> ON
    -> f.fruits_origin = c.location;
+-------------+----------+
| fruits_name | language |
+-------------+----------+
| NULL        | 四川话   |
| 苹果        | 山东话   |
| 葡萄        | 山东话   |
| 香蕉        | 海南话   |
+-------------+----------+
4 rows in set (0.01 sec)

可以看出比之前的查询多了一行数据，就是没有匹配到的culture表的第一行数据

第二种就是左连接，左连接关键词为LEFT，会额外显示JOIN左边表没有匹配到的数据，也急速和JOIN上面的表

SELECT …… FROM table1 LEFT JOIN table2 ……

这里的例子就是将上面的表的两列颠倒顺序，由language查询fruits_name

mysql> SELECT
    -> c.language,f.fruits_name
    -> FROM
    -> culture c
    -> LEFT JOIN
    -> fruits f
    -> ON
    -> f.fruits_origin = c.location;
+----------+-------------+
| language | fruits_name |
+----------+-------------+
| 四川话   | NULL        |
| 山东话   | 苹果        |
| 山东话   | 葡萄        |
| 海南话   | 香蕉        |
+----------+-------------+
4 rows in set (0.00 sec)

外连接的查询结果条数一定是 >= 内连接查询结果条数 如果两张表的映射是双射，那么等号成立

上面的查询领导姓名的时候，缺少一条记录，因为有的数据不匹配，可以使用外连接，比如这里要求查询所有员工的领导名

从要求可以看出强调的是所有员工，所以员工表是主表

mysql> SELECT
    -> e.name '员工姓名',b.name '领导姓名'
    -> FROM
    -> emp e
    -> LEFT JOIN
    -> emp b
    -> ON
    -> e.splid = b.id;
+----------+----------+
| 员工姓名 | 领导姓名 |
+----------+----------+
| zhang    | huang    |
| wang     | liu      |
| liu      | huang    |
| huang    | hu       |
| hu       | NULL     |
+----------+----------+
5 rows in set (0.00 sec)

这样就查询了所有的数据，包括NULL

这里就同时使用了左连接的方式

多张表的连接

上面都是分享的是两张表的连接，那么多张表的连接时如何进行的呢

说白了就是多次JOIN ON

基本格式

SELECT
……
FROM
table_a
JOIN
table_b
ON
a表和b表的连接条件
JOIN
table_c
ON
a表和c表的连接条件
JOIN
table_d
ON
a表和d表的连接条件

注意这里不要把自己绕进去了，这里的连接条件都是为了查询a表的字段，所以就是a表和其余各表的连接条件

这里举个例子，就是fruits表和culture表和fruitsgrade表一起查询，一个时等值连接一个时非等值连接

mysql> SELECT
    -> f.fruits_name '水果名称',c.language '地方方言',g.grade '价格等级'
    -> FROM
    -> fruits f
    -> JOIN
    -> culture c
    -> ON
    -> f.fruits_origin = c.location
    -> JOIN
    -> fruitsgrade g
    -> ON
    -> f.fruits_price BETWEEN g.lowpri AND g.highpri;
+----------+----------+----------+
| 水果名称 | 地方方言 | 价格等级 |
+----------+----------+----------+
| 葡萄     | 山东话   | 一般     |
| 苹果     | 山东话   | 便宜     |
| 香蕉     | 海南话   | 昂贵     |
+----------+----------+----------+
3 rows in set (0.01 sec)

可以理解为先连接culture表，连接之后再连接另外一张表

多表连接中可以内外连接同时使用

即使一个不断加JOIN和ON，同时也可以使用自连接

子查询

子查询就是在SELECT语句中嵌套SELECT语句，就是子查询，因为查询的关键字是SELECT

子查询可以出现的位置

SELECT
……(SELECT)
FROM
…… (SELECT)
WHERE
…… (SELECT)
//就是说子查询可以出现在SELECT，FROM，WHERE后面

WHERE子句中的子查询

之前分享分组函数，多行函数的时候提过WHERE子句中是不能直接使用分组返回的，因为GROUP BY 在WHERE之后执行，在SELECT之前执行，没有分组是之前是不能使用分组函数的

比如查询city表的最低人口的城市的所有数据

mysql> SELECT
    -> *
    -> FROM
    -> city
    -> WHERE
    -> Population = MIN(Population);
ERROR 1111 (HY000): Invalid use of group function

这样写就会报错：分组函数的无效使用

所以正确的思路是先找出最低的人口是多少，然后根据最少的人口来查询城市

1. SELECT MIN(Population) FROM city;
2. SELECT * FROM city WHERE Population = min;

那么合并就可以了，也就是将min换成上面的语句

SELECT * FROM city WHERE Population = (SELECT MIn(Population) FROM city);

可以看看嵌套后能否使用

mysql> SELECT
    -> *
    -> FROM
    -> city
    -> WHERE
    -> Population = (SELECT MIN(Population) FROM city);
+------+-----------+-------------+----------+------------+
| ID   | Name      | CountryCode | District | Population |
+------+-----------+-------------+----------+------------+
| 2912 | Adamstown | PCN         | –       |         42 |
+------+-----------+-------------+----------+------------+
1 row in set (0.01 sec)

嵌套的语句写成一行方便嵌套

子查询的顺序是先进行子查询，才会进行父查询，这里要先执行括号中的查询语句，才会执行外面的语句

FROM子句的子查询

FROM后面的子查询，可以将子查询的结果当成一张临时表来看待了

其实不难理解，from就是获取表的，WHERE就是过滤的，from后面的子查询当然就是一个临时的表

给一个案例

查找city表前100个城市每个国码的平均人口的人口等级？

这里的查询目标也可以分为两步

1.查询前100个城市每个国码的平均人口
SELECT CountryCode,AVG(Population) FROM city WHERE ID <= 100 GROUP BY CountryCode
2.查询每个国码平均人口所对应的人口等级
这里查询出来的平均人口形成一张临时的表temp，将该表和populationgrade连接获取等级
SELECT temp.CountryCode,temp.AVG(Population),g.grade FROM temp JOIN populationgrade g ON temp.temp.AVG(Population) BETWEEN g.lowpop AND g.highpop

这样可以看到将上面的查询语句放到下面的temp位置就可以了，起个别名

mysql> SELECT
    -> c.CountryCode '国码',c.AVG(Population) '平均人口',g.grade '人口等级'
    -> FROM
    -> (SELECT CountryCode,AVG(Population) FROM city WHERE ID <= 100 GROUP BY CountryCode) c
    -> JOIN
    -> populationgrade g
    -> ON
    -> c.AVG(Population) BETWEEN g.lowpop AND g.highpop;
ERROR 1630 (42000): FUNCTION c.AVG does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual

这里报错的原因是因为下面的ON中的AVG函数不存在，这里SQL会把其当作函数处理，所以报错，这里的解决办法是将上面的子查询中的字段起别名

mysql> SELECT
    ->  c.CountryCode '国码',c.pop '平均人口',g.grade '人口等级'
    -> FROM
    -> (SELECT CountryCode,AVG(Population) AS pop  FROM city WHERE ID <= 100 GROUP BY CountryCode) c
    -> JOIN
    -> populationgrade g
    -> ON
    -> c.pop BETWEEN g.lowpop AND g.highpop;
+------+-------------+----------+
| 国码 | 平均人口    | 人口等级 |
+------+-------------+----------+
| AFG  | 583025.0000 | 人口稠密 |
| NLD  | 185001.7500 | 人口正常 |
| ANT  |   2345.0000 | 人口稀少 |
| ALB  | 270000.0000 | 人口正常 |
| DZA  | 288454.3889 | 人口正常 |
| ASM  |   3761.5000 | 人口稀少 |
| AND  |  21189.0000 | 人口正常 |
| AGO  | 512320.0000 | 人口稠密 |
| AIA  |    778.0000 | 人口稀少 |
| ATG  |  24000.0000 | 人口正常 |
| ARE  | 345667.2000 | 人口稠密 |
| ARG  | 513810.6250 | 人口稠密 |
+------+-------------+----------+
12 rows in set (0.00 sec)

SELECT子句子查询

mysql> SELECT
    -> f.fruits_name,(SELECT c.language FROM culture c WHERE c.location = f.fruits_origin)
    -> FROM
    -> fruits f;
+-------------+-----------------------------------------------------------------------+
| fruits_name | (SELECT c.language FROM culture c WHERE c.location = f.fruits_origin) |
+-------------+-----------------------------------------------------------------------+
| 葡萄        | 山东话                                                                |
| 苹果        | 山东话                                                                |
| 香蕉        | 海南话                                                                |
+-------------+-----------------------------------------------------------------------+
3 rows in set (0.00 sec)

这里不建议使用，其实就是表连接，问题就是子查询的结果如果不是一个字段就很混乱

所以还是以前两种子查询为主

今天的内容就这些，明天会多一点干货~🌳