MySql连接查询与联合查询

Dear 丶 2022-05-25 03:28 303阅读 0赞

#### PS：相关环境 ####

测试表：

DROP TABLE IF EXISTS `boys`;
    CREATE TABLE `boys` ( `id` int(11) NOT NULL AUTO_INCREMENT, `boyName` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
    
    INSERT INTO `boys` VALUES ('1', '张无忌');
    INSERT INTO `boys` VALUES ('2', '令狐冲');
    INSERT INTO `boys` VALUES ('3', '郭靖');
    INSERT INTO `boys` VALUES ('4', '杨过');
    INSERT INTO `boys` VALUES ('5', '宋青书');
    
    DROP TABLE IF EXISTS `girls`;
    CREATE TABLE `girls` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(50) NOT NULL, `boyfriend_id` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;
    
    INSERT INTO `girls` VALUES ('1', '小龙女', '4');
    INSERT INTO `girls` VALUES ('2', '赵敏', '1');
    INSERT INTO `girls` VALUES ('3', '东方不败', '2');
    INSERT INTO `girls` VALUES ('4', '黄蓉', '3');
    INSERT INTO `girls` VALUES ('5', '郭襄', '100');

`girls`表中的数据：  
![这里写图片描述][70]  
`boys`表中的数据：  
![这里写图片描述][70 1]

为了方便大家测试将表结构与表中的数据都在上面贴了出来，直接拷贝到数据库执行即可。所有的测试都通过上面 2 张表完成，大于 2 张表时原理是一样的。

#### 一、等值连接 ####

等值连接：查询的结果是 2 张表或多张表中“同时满足”条件的数据

主要语法：`... INNER JOIN ... ON ...`

示意图：  
![这里写图片描述][70 2]

案例：查询有男朋友的女生的名字与其对应的男朋友的名字

SQL 语句(同 `WHERE`)：

SELECT g. name, b.boyName FROM girls g INNER JOIN boys b ON g.boyfriend_id = b.id;

查询结果：  
![这里写图片描述][70 3]

#### 二、左外连接 ####

左外连接：也称为左连接，查询的结果是 2 张表“同时满足”条件的数据与左侧表中不符合条件的数据的结合

主要语法：`... LEFT OUTER JOIN ... ON ...`

示意图：  
![这里写图片描述][70 4]  
案例 1：查询有男朋友的女生的名字与对应的男朋友的名字，并查询没有男朋友的女生的名字

SQL 语句(`OUTER` 可以省略)：

SELECT g. name, b.boyName FROM girls g LEFT OUTER JOIN boys b ON g.boyfriend_id = b.id;

查询结果：  
![这里写图片描述][70 5]

案例二：查询没有男朋友的女生的名字

SQL 语句：

SELECT g. name FROM girls g LEFT JOIN boys b ON g.boyfriend_id = b.id WHERE b.id IS NULL;

查询结果：  
![这里写图片描述][70 6]

#### 三、右外连接 ####

右外连接：也称为右连接，查询的结果是 2 张表“同时满足”条件的数据与右侧表中不符合条件的数据的结合

主要语法：`... RIGHT OUTER JOIN ... ON ...`

示意图：  
![这里写图片描述][70 7]

案例 1：查询有男朋友的女生的名字与对应的男朋友的名字，并查询没有女朋友的男生的名字

SQL 语句：

SELECT g. name, B.boyName FROM girls g RIGHT JOIN boys b ON g.boyfriend_id = b.id;

查询结果：  
![这里写图片描述][70 8]

案例 2：查询没有女朋友的男生的名字，这个的答案就不贴出来了，有兴趣的可以自己尝试一下

#### 四、全外连接 ####

全外连接：查询的结果是 2 张表“同时满足”条件的数据与左侧表和右侧表中不符合条件的数据的结合

主要语法：`... FULL JOIN ... ON ...`

示意图：  
![这里写图片描述][70 9]

案例 1：查询有男朋友的女生的名字与对应的男朋友的名字，并查询没有男朋友的女生的名字与没有女朋友的男生的名字

SQL 语句：

SELECT g. name, B.boyName FROM girls g FULL JOIN boys b ON g.boyfriend_id = b.id

注：MySql 并不支持全外连接，所有上面的语句在执行的时候会报错，但是在 Oracle 数据库中是支持的。关于这个案例，我们可以使用联合查询的方式来实现，会在下面讲述。

#### 五、联合查询 ####

联合查询：就是把多个查询语句的查询结果结合在一起

主要语法1：`... UNION ...`  
主要语法2：`... UNION ALL ...`

案例 1：查询所有女生的名字与男生的名字

SQL 语句：

SELECT name FROM girls UNION SELECT boyName FROM boys;

查询结果：  
![这里写图片描述][70 10]

案例 2：实现上面的全外连接

SQL 语句：

SELECT g. name, b.boyName FROM girls g LEFT JOIN boys b ON g.boyfriend_id = b.id UNION SELECT g. name, B.boyName FROM girls g RIGHT JOIN boys b ON g.boyfriend_id = b.id;

查询结果：  
![这里写图片描述][70 11]  
在实现全外连接的时候我们使用了左外连接与右外连接相结合，并且在数据结合的时候把重复的数据去除了。

案例 3：查询单身男女的姓名，这个就留给大家自己做吧

`UNION` 与 `UNION ALL`的区别：联合查询支持`UNION` 与 `UNION ALL`两种用法，其中`UNION` 会把多个查询语句的结果**合并后去重**，`UNION ALL`会把**所有的查询结果合并**，包括重复的数据。

[70]: /images/20220525/e344798ee68e4125a96ba2a79dc6ef75.png
[70 1]: /images/20220525/f33e17dc781541058c33611cd083197d.png
[70 2]: /images/20220525/4bc5c5b28c63486a82fed392e0aa2593.png
[70 3]: /images/20220525/8f6634a639d54f659d02a1e544e067e9.png
[70 4]: /images/20220525/463cc2ac0c9c43ae982e1d2e04b22344.png
[70 5]: /images/20220525/48e2ecb84bed496e8820106fe5442fd1.png
[70 6]: /images/20220525/b9fde55e87c74d0b8a896d284c15d0fe.png
[70 7]: /images/20220525/9fa9db64ce8541ac8eb6779f39071d67.png
[70 8]: /images/20220525/4046e666509944f8bb0562eede3eaaf4.png
[70 9]: /images/20220525/c33b24047b114c1dba1e8d875cd1cfca.png
[70 10]: /images/20220525/da8009099d2343b6ac84451d41415629.png
[70 11]: /images/20220525/9dc2dab05fd545f498d27c6e0b80c60a.png