多表联合查询

多表联合查询

表连接

当需要查询多个表中的字段时，就可以使用表连接来实现。表联接分为内连接和外连接。

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表数据如下：

注意： 在上表order_goods表中uid是指user表中的uid字段。上表中oid为1的数据行，uid为10的用户。为user表中uid为10的用户：高小峰。该用户购买了商品为苹果鼠标。购买时间buytime为一个unix时间戳。

内连接

注：下例中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)

基本语法二：

结果与基本语法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)

外连接

外连接又分为左连接和右链接，具体定义如下。

左连接：包含所有的左边表中的记录甚至是右边表中没有和它匹配的记录

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)

右连接：包含所有的右边表中的记录甚至是右边表中没有和它匹配的记录

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等。

示例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，去除重复记录后的结果。

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)