12.8.3 多表联合查询
最后更新于：2022-04-02 00:16:37
很多时候在实际的业务中我们不只是查询一张表。
1. 在电子商务系统中，查询哪些用户没有购买过产品。
2. 银行中可能查询违规记录，同时查询出用户的
3. 查询中奖信息和中奖人员的基本信息。
以上只是列的情况我们就需要把两张表在一起进行查询。
而上述业务中需要多表联合在一起查询才能有结果，而多表联合查询的本质是：表连接。
##表连接
当需要查询多个表中的字段时，就可以使用表连接来实现。表联接分为内连接和外连接。
1. 内联结：将两个表中存在联结关系的字段符合联结关系的那些记录形成记录集的联结。
2. 外连接：会选出其他不匹配的记录，分为外左联结和外右联结。
在学习实验前，我为大家准备了两个模拟的数据表：
1. 用户表，存放用户信息
2. 订单表，存放哪个用户购买过哪个商品
**user表创建语句**
> CREATE TABLE IF NOT EXISTS `user` (
`uid` int(11) NOT NULL,
`username` varchar(30) NOT NULL,
`password` char(32) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
> CREATE TABLE IF NOT EXISTS `order_goods` (
`oid` int(11) NOT NULL,
`uid` int(11) NOT NULL,
`name` varchar(50) NOT NULL,
`buytime` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
user表数据如下：
| uid | username | password |
| -- | -- | |
|1|景甜|123456|
|2|王小二|245667|
|3|李文凯|1235531|
|4|井柏然|123455|
|5|范冰冰|5abcwa|
|6|黄晓明|abcdeef|
|7|anglebaby|caption|
|8|TFBOYS|abcdwww|
|9|安小超|12tfddwd|
|10|高小峰|3124qwqw|
|11|李小强|323fxfvdvd|
|12|李小超|311aqqee|
|13|韩小平|121rcfwrfq|
|14|宋小康|123123tcsd|
|15|佟小刚|3cxvdfs|
order_goods数据如下：
|oid|uid|name|buytime|
| -- | -- |-- |-- |
|1|10|苹果鼠标|1212313|
|2|3|iphone 12s|123121241|
|3|12|雪碧|13232333|
|4|15||34242123|
|5|3|iphone 键盘|12123413|
**注意：**
在上表order_goods表中uid是指user表中的uid字段。上表中oid为1的数据行，uid为10的用户。为user表中uid为10的用户：高小峰。该用户购买了商品为苹果鼠标。购买时间buytime为一个unix时间戳。
##内连接
基本语法一：
| 类别 | 详细解示 |
| -- | -- |
| 基本语法 | select 表1.字段 [as 别名],表n.字段 from 表1 [别名],表n where 条件; |
| 示例 |select user.uid ,user.username as username,order_goods.oid,order_goods.uid,order_goods.name as shopname from user,order_goods where user.uid = order_goods.uid; |
| 示例说明 | 查询商品表中哪些用户购买过商品，并将用户信息显示出来 |
> **注：下例中from 表使用到了表别名。**
由于表名太长，每次写的时候容易写错。我们可以在表后直接跟上一个简写英文字符串。在前面拼接字段时，直接使用简写字符串.字段即可。
> mysql> select u.uid ,u.username as username,o.oid,o.uid,o.name as shopname from user u,order_goods o where u.uid = o.uid;
+-----+-----------+-----+-----+---------------+
| uid | username | oid | uid | shopname |
+-----+-----------+-----+-----+---------------+
| 10 | 高小峰 | 1 | 10 | 苹果鼠标 |
| 3 | 李文凯 | 2 | 3 | iphone 12s |
| 12 | 李小超 | 3 | 12 | 雪碧 |
| 15 | 佟小刚 | 4 | 15 | |
| 3 | 李文凯 | 5 | 3 | iphone 键盘 |
+-----+-----------+-----+-----+---------------+
5 rows in set (0.00 sec)
基本语法二：
| 类别 | 详细解示 |
| -- | -- |
| 基本语法 | select 表1.字段 [as 别名],表n.字段 from 表1 INNER JOIN 表n on 条件; |
| 示例 | select user.uid ,user.username as username,order_goods.oid,order_goods.uid,order_goods.name as shopname from user inner join order_goods on user.uid = order_goods.uid;|
| 示例说明 |查询商品表中哪些用户购买过商品，并将用户信息显示出来 |
**结果与基本语法1中一致。**
> mysql> select user.uid ,user.username as username,order_goods.oid,order_goods.uid,order_goods.name as shopname from user inner join order_goods on user.uid = order_goods.uid;
+-----+-----------+-----+-----+---------------+
| uid | username | oid | uid | shopname |
+-----+-----------+-----+-----+---------------+
| 10 | 高小峰 | 1 | 10 | 苹果鼠标 |
| 3 | 李文凯 | 2 | 3 | iphone 12s |
| 12 | 李小超 | 3 | 12 | 雪碧 |
| 15 | 佟小刚 | 4 | 15 | |
| 3 | 李文凯 | 5 | 3 | iphone 键盘 |
+-----+-----------+-----+-----+---------------+
5 rows in set (0.00 sec)
##外连接
| 说明 | 详解 |
| -- | -- |
| 基本语法 | select 表1.字段 [as 别名],表n.字段 from 表1 LEFT JOIN 表n on 条件; |
| 示例 | select * from user left join order_goods on user.uid = order_goods.uid; |
| 示例说明 | 以左边为主，查询哪些用户未购买过商品，并将用户信息显示出来 |
外连接又分为左连接和右链接，具体定义如下。
**左连接：包含所有的左边表中的记录甚至是右边表中没有和它匹配的记录**
mysql> select * from user left join order_goods on user.uid = order_goods.uid;
+-----+-----------+------------+------+------+---------------+-----------+
| uid | username | password | oid | uid | name | buytime |
+-----+-----------+------------+------+------+---------------+-----------+
| 10 | 高小峰 | 3124qwqw | 1 | 10 | 苹果鼠标 | 1212313 |
| 3 | 李文凯 | 1235531 | 2 | 3 | iphone 12s | 123121241 |
| 12 | 李小超 | 311aqqee | 3 | 12 | 雪碧 | 13232333 |
| 15 | 佟小刚 | 3cxvdfs | 4 | 15 | | 34242123 |
| 3 | 李文凯 | 1235531 | 5 | 3 | iphone 键盘 | 12123413 |
| 1 | 景甜 | 123456 | NULL | NULL | NULL | NULL |
| 2 | 王小二 | 245667 | NULL | NULL | NULL | NULL |
| 4 | 井柏然 | 123455 | NULL | NULL | NULL | NULL |
| 5 | 范冰冰 | 5abcwa | NULL | NULL | NULL | NULL |
| 6 | 黄晓明 | abcdeef | NULL | NULL | NULL | NULL |
| 7 | anglebaby | caption | NULL | NULL | NULL | NULL |
| 8 | TFBOYS | abcdwww | NULL | NULL | NULL | NULL |
| 9 | 安小超 | 12tfddwd | NULL | NULL | NULL | NULL |
| 11 | 李小强 | 323fxfvdvd | NULL | NULL | NULL | NULL |
| 13 | 韩小平 | 121rcfwrfq | NULL | NULL | NULL | NULL |
| 14 | 宋小康 | 123123tcsd | NULL | NULL | NULL | NULL |
+-----+-----------+------------+------+------+---------------+-----------+
16 rows in set (0.00 sec)
**右连接：包含所有的右边表中的记录甚至是右边表中没有和它匹配的记录**
| 类别 | 详细解示 |
| -- | -- |
| 基本语法 | select 表1.字段 [as 别名],表n.字段 from 表1 right JOIN 表n on 条件; |
| 示例 |select * from user right join order_goods on user.uid = order_goods.uid; |
| 示例说明 |查询商品表中哪些用户购买过商品，并将用户信息显示出来 |
mysql> select * from user right join order_goods on user.uid = order_goods.uid;
+------+-----------+----------+-----+-----+---------------+-----------+
| uid | username | password | oid | uid | name | buytime |
+------+-----------+----------+-----+-----+---------------+-----------+
| 10 | 高小峰 | 3124qwqw | 1 | 10 | 苹果鼠标 | 1212313 |
| 3 | 李文凯 | 1235531 | 2 | 3 | iphone 12s | 123121241 |
| 12 | 李小超 | 311aqqee | 3 | 12 | 雪碧 | 13232333 |
| 15 | 佟小刚 | 3cxvdfs | 4 | 15 | | 34242123 |
| 3 | 李文凯 | 1235531 | 5 | 3 | iphone 键盘 | 12123413 |
+------+-----------+----------+-----+-----+---------------+-----------+
5 rows in set (0.00 sec)
##子查询
有时候，当我们查询的时候，需要的条件是另外一个select语句的结果，这时就需要使用子查询。用于子查询的关键字包括in、not in、=、!=、exists、not exists等。
| 类别 | 详细解示 |
| -- | -- |
| 基本语法 | select 字段 from 表 where 字段 in(条件) |
| 示例1 | select * from user where uid in (1,3,4);|
| 示例1说明 |按照id 查询指定用户 |
| 示例2 |select * from user where uid in (select uid from order_goods); |
| 示例2说明 |将购买过商品的用户信息显示出来 |
**示例1：**
> mysql> select * from user where uid in (1,3,4);
+-----+-----------+----------+
| uid | username | password |
+-----+-----------+----------+
| 1 | 景甜 | 123456 |
| 3 | 李文凯 | 1235531 |
| 4 | 井柏然 | 123455 |
+-----+-----------+----------+
3 rows in set (0.00 sec)
**示例2：**
> mysql> select * from user where uid in (select uid from order_goods);
+-----+-----------+----------+
| uid | username | password |
+-----+-----------+----------+
| 10 | 高小峰 | 3124qwqw |
| 3 | 李文凯 | 1235531 |
| 12 | 李小超 | 311aqqee |
| 15 | 佟小刚 | 3cxvdfs |
+-----+-----------+----------+
4 rows in set (0.00 sec)
mysql> select * from emp where deptno in (select deptno from dept);
##记录联合
使用 union 和 union all 关键字，将两个表的数据按照一定的查询条件查询出来后，将结果合并到一起显示。两者主要的区别是把结果直接合并在一起，而 union 是将 union all 后的结果进行一次distinct，去除重复记录后的结果。
| 类别 | 详细解示 |
| -- | -- |
| 基本语法 | select语句1 union[all] select语句2 |
| 示例 | select * from user where uid in (1,3,4);|
| 示例说明 |将商品表中的用户信息和用户表中的用户信息的结果组合在一起 |
> mysql> select uid from user union select uid from order_goods;
+-----+
| uid |
+-----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
| 11 |
| 12 |
| 13 |
| 14 |
| 15 |
+-----+
15 rows in set (0.00 sec)
';