MySQL基础学习3: 多表查询详解及实践

在数据库的查询中，经常涉及到不止一个表的查询，如用户与订单表的查询，商品与订单的查询等。熟练掌握多表查询相关语句能让我们处理更加复杂的数据库操作。多表查询主要有笛卡尔积，内连接和外连接，下面通过用户表和订单表的例子来解释多表查询的相关操作。

创建用户和订单表

建表语句如下：

-- 用户表(user)
create table `user` (                                  
	`id` int auto_increment primary key,                
    `username` varchar(50)  -- 用户姓名                                                
);

-- 订单表(orders)
create table `orders` (                                                  
    `id` int  auto_increment primary key,                                  
    `price` double,                                           
    `user_id` int                                       
);

由于用户表和订单表是一对多的关系，还需要在订单表上添加外键约束：

-- 给订单表添加外键约束
alter table orders add constraint user_fk foreign key (user_id) references user(id);

插入数据：

-- 向user表中添加数据
insert into user values(3,'张三');
insert into user values(4,'李四');
insert into user values(5,'王五');
insert into user values(6,'赵六');

-- 向orders 表中插入数据
insert into orders values(1,1314,3);
insert into orders values(2,1314,3);
insert into orders values(3,15,4);
insert into orders values(4,315,5);
insert into orders values(5,1014,null);

最终得到的表的结果如下：
用户表(user)：

订单表(orders):

多表查询-笛卡尔积

这是多张表无条件的联合查询，相当于是两张表做笛卡尔积，A表的每条记录与B表的每条记录进行连接。如用户表有4条记录，订单表有5条记录，最终连接得到的表为4x5=20条记录。
sql语句如下:

SELECT A.*, B.* FROM
`user` A, orders B

查询结果如下：

多表查询-内连接

内连接主要用于查询两个表的交集（以某一条件），如查询用户的订单,没有订单的用户不显示。
内连接分为隐式内连接和显式内连接，隐式内连接的写法如下：

select user.*,orders.* 
from user, orders 
where user.id=orders.user_id;

查询结果如下：

显式内连接的写法如下：

select user.*,orders.* 
from user inner join orders 
on user.id=orders.user_id;

其中inner可省略。

外连接

外连接可分为左外连接和右外连接，在内连接中是只有两个表中满足相同条件的记录才会显示出来，但有时我们想显示某个表的所有记录以及其相应的详情，根据想显示的那个表的位置可分为左外连接和右外连接。如查询所有用户的订单详情和所有订单的用户详情。

左外连接

在内连接的基础上，还包含左表中所有不符合条件的数据行，并在其中的右表列填写NULL，如查询所有用户的订单详情：

select user.*, orders.*
from user left join orders 
on user.id = orders.user_id

查询结果如下：

可以看到，与内连接相比，结果中还包含了订单为Null的用户王五，这就是左外连接，将user表作为左表，结果中会包含左边中的所有记录。
left join之前的为左表

右外连接

在内连接的基础上，还包含右表中所有不符合条件的数据行，并在其中的左表列填写NULL ，如查询所有订单的用户详情：

select orders.*,user.* 
from user right join orders 
on user.id=orders.user_id;

查询结果如下：

使用关键字right join，其之后的为完全表。从中可以看出，其实左连接和右连接是可以相互转换的，我们完全可以将上述的右连接转换为功能相同的左连接：

select orders.*,user.* 
from orders left join user  
on user.id=orders.user_id;

最后，用一张图总结一下多表查询的关系：

参考：
1.传智播客Java就业班
2.https://www.jianshu.com/p/ecb1833777fd
3.https://blog.youkuaiyun.com/u013215018/article/details/52808979