MySQL基础教程15——多表查询
MySQL基础教程15——多表查询
笛卡尔积
mysql> select * from user; +----+------+--------+---------+ | id | name | gender | user_id | +----+------+--------+---------+ | 1 | ton | 男 | 1 | | 2 | bom | 女 | 2 | | 3 | lin | 男 | 3 | +----+------+--------+--- mysql> select * from foot; +----+------+ | id | s | +----+------+ | 1 | 苹果 | | 2 | 香蕉 | | 3 | 橘子 | +----+------+ 3 rows in set (0.03 sec) // 笛卡尔积 mysql> select * from user,foot; +----+------+--------+---------+----+------+ | id | name | gender | foot_id | id | s | +----+------+--------+---------+----+------+ | 1 | ton | 男 | 1 | 1 | 苹果 | | 2 | bom | 女 | 2 | 1 | 苹果 | | 3 | lin | 男 | 3 | 1 | 苹果 | | 1 | ton | 男 | 1 | 2 | 香蕉 | | 2 | bom | 女 | 2 | 2 | 香蕉 | | 3 | lin | 男 | 3 | 2 | 香蕉 | | 1 | ton | 男 | 1 | 3 | 橘子 | | 2 | bom | 女 | 2 | 3 | 橘子 | | 3 | lin | 男 | 3 | 3 | 橘子 | +----+------+--------+---------+----+------+ 9 rows in set (0.03 sec)
以这两张表为例子笛卡尔积现象就是表A的每一个记录与表B的每一个记录匹配组成一条新纪录，例如表A有三条记录表B有三条记录结合后就为3*3(9)条记录。
内连接
笛卡尔积显示出来的数据会有我们不需要的，因此我们可以用条件筛选掉不需要的条件。
隐式内连接
select 字段列表 from 表1,表2 where 条件;
mysql> select * from user as u,foot as f where u.foot_id = f.id; +----+------+--------+---------+----+------+ | id | name | gender | foot_id | id | s | +----+------+--------+---------+----+------+ | 1 | ton | 男 | 1 | 1 | 苹果 | | 2 | bom | 女 | 2 | 2 | 香蕉 | | 3 | lin | 男 | 3 | 3 | 橘子 | +----+------+--------+---------+----+------+ 3 rows in set (0.03 sec)
显式内连接
select 字段列表 from 表1 [inner] join 表2 on 连接条件;
mysql> select * from user as u inner join foot as f on u.foot_id = f.id; +----+------+--------+---------+----+------+ | id | name | gender | foot_id | id | s | +----+------+--------+---------+----+------+ | 1 | ton | 男 | 1 | 1 | 苹果 | | 2 | bom | 女 | 2 | 2 | 香蕉 | | 3 | lin | 男 | 3 | 3 | 橘子 | +----+------+--------+---------+----+------+ 3 rows in set (0.03 sec)
相对而言，隐式连接好理解好书写，语法简单，担心的点较少。但是显式连接可以减少字段的扫描，有更快的执行速度。这种速度优势在3张或更多表连接时比较明显。
外连接
往user中添加一条新记录，字段foot_id为null。
mysql> select * from user; +----+------+--------+---------+ | id | name | gender | foot_id | +----+------+--------+---------+ | 1 | ton | 男 | 1 | | 2 | bom | 女 | 2 | | 3 | lin | 男 | 3 | | 4 | ken | 女 | NULL | +----+------+--------+---------+ 4 rows in set (0.04 sec) mysql> select * from user,foot where user.foot_id = foot.id; +----+------+--------+---------+----+------+ | id | name | gender | foot_id | id | s | +----+------+--------+---------+----+------+ | 1 | ton | 男 | 1 | 1 | 苹果 | | 2 | bom | 女 | 2 | 2 | 香蕉 | | 3 | lin | 男 | 3 | 3 | 橘子 | +----+------+--------+---------+----+------+ 3 rows in set (0.04 sec)
此时用内连接会发现条件字段为空的就查询不出来，因此我们可以用外连接连解决。
左外连接(左表全部和左右表交集)
select 字段列表 from 表1 left [outer] join 表2 on 条件;
mysql> select u.*,f.* from user as u left outer join foot as f on u.foot_id = f.id; +----+------+--------+---------+------+------+ | id | name | gender | foot_id | id | s | +----+------+--------+---------+------+------+ | 1 | ton | 男 | 1 | 1 | 苹果 | | 2 | bom | 女 | 2 | 2 | 香蕉 | | 3 | lin | 男 | 3 | 3 | 橘子 | | 4 | ken | 女 | NULL | NULL | NULL | +----+------+--------+---------+------+------+ 4 rows in set (0.03 sec)
右边外连接(右表全部和左右表交集)
select 字段列表 from 表1 right [outer] join 表2 on 条件;
mysql> select f.*,u.* from user as u right outer join foot as f on u.foot_id = f.id; +----+------+----+------+--------+---------+ | id | s | id | name | gender | foot_id | +----+------+----+------+--------+---------+ | 1 | 苹果 | 1 | ton | 男 | 1 | | 2 | 香蕉 | 2 | bom | 女 | 2 | | 3 | 橘子 | 3 | lin | 男 | 3 | +----+------+----+------+--------+---------+ 3 rows in set (0.03 sec)
这里没有显示user表中id为4的信息是因为在foot表中字段id没有对应user表中字段foot_id的null值。
本质上来说左外连接和右外连接是一样的只要把表的位置换一下实现的效果就能一样，例如：
//左外连接 select * from user as u left outer join foot as f on u.foot_id = f.id; //右外连接 select * from foot as f right outer join user as u on u.foot_id = f.id;
这两句SQL语句执行的效果是一致的。
自连接
自连接可以和内外连接一起使用。
mysql> select * from yg; +----+------+------+ | id | name | t_id | +----+------+------+ | 1 | 张三 | NULL | | 2 | 李四 | 1 | | 3 | 王五 | 1 | | 4 | 熊大 | 2 | +----+------+------+ 4 rows in set (0.05 sec)
t_id字段对应的id表示上级关系，现在需要查询每个人对应的上级就需要用到自连接。
mysql> select a.name,b.name from yg as a left outer join yg as b on a.t_id = b.id; +------+------+ | name | name | +------+------+ | 李四 | 张三 | | 王五 | 张三 | | 熊大 | 李四 | | 张三 | NULL | +------+------+ 4 rows in set (0.03 sec)
自连接本质上就是一张表取两个不同的别名当两张表使用。
联合查询
当我们对多表进行查询时，想把数据汇集在一张表上可以使用联合查询。
select 字段列表 from 表1 .... union[all] select 字段列表 from 表2 ...;
假设我们需要查询user表中id为2的人的信息和yg表中id为3的人的信息并且汇总在一张表上。
mysql> select u.id,u.name,u.gender from user as u where id = 2 -> union all -> select * from yg where id = 3; +----+------+--------+ | id | name | gender | +----+------+--------+ | 2 | bom | 女 | | 3 | 王五 | 1 | +----+------+--------+ 2 rows in set (0.03 sec)
注意： 多个select后的字段列表中字段数量应该一直。
在些情况下两查询的结果会存在重合，若我们不想有重复数据可以去掉union后的all。
子查询
标量子查询
select * from 表1 where a = (select a from 表2 where 条件);
如果我们要查询user表中id为1的人对应foot表中的水果。
mysql> select user.foot_id from user where id = 1; +---------+ | foot_id | +---------+ | 1 | +---------+ 1 row in set (0.03 sec) mysql> select foot.s from foot where id = 1; +------+ | s | +------+ | 苹果 | +------+ 1 row in set (0.02 sec) // 标量子查询 mysql> select foot.s from foot where id = (select user.foot_id from user where id = 1); +------+ | s | +------+ | 苹果 | +------+ 1 row in set (0.04 sec)
列子查询
子查询中数据为列。
select * from 表1 where a in (select a from 表2 where 条件)
select * from 表1 where a > all/some/any(select b from 表2 where 条件);
常用in/not in/any/some/all,其中any和some时一样的效果。
查询user表中在foot表有对应信息的信息。
mysql> select * from user where id in (select foot.id from foot); +----+------+--------+---------+ | id | name | gender | foot_id | +----+------+--------+---------+ | 1 | ton | 男 | 1 | | 2 | bom | 女 | 2 | | 3 | lin | 男 | 3 | +----+------+--------+---------+ 3 rows in set (0.02 sec)
行子查询
子查询中数据为行。
select * from 表名 where (a,b) = (select a,b from where 条件);
mysql> select * from user; +----+------+--------+---------+ | id | name | gender | foot_id | +----+------+--------+---------+ | 1 | 张三 | 男 | 1 | | 2 | 李四 | 女 | 2 | | 3 | 王五 | 男 | 3 | | 4 | 熊大 | 女 | NULL | +----+------+--------+---------+ 4 rows in set (0.03 sec) mysql> select * from yg; +----+------+------+ | id | name | t_id | +----+------+------+ | 1 | 张三 | NULL | | 2 | 李四 | 1 | | 3 | 王五 | 1 | +----+------+------+ 3 rows in set (0.03 sec) //行子查询 mysql> select * from user where (id,name) in (select yg.id,yg.name from yg); +----+------+--------+---------+ | id | name | gender | foot_id | +----+------+--------+---------+ | 1 | 张三 | 男 | 1 | | 2 | 李四 | 女 | 2 | | 3 | 王五 | 男 | 3 | +----+------+--------+---------+ 3 rows in set (0.03 sec)
表子查询
select * from 表名 where (a,b) in (select a,b from 表名 where name = 'bom' or name = 'tom');
mysql> select * from user where (user.id,user.name) in (select yg.id,yg.name from yg where name = '张三' or name = '王五'); +----+------+--------+---------+ | id | name | gender | foot_id | +----+------+--------+---------+ | 1 | 张三 | 男 | 1 | | 3 | 王五 | 男 | 3 | +----+------+--------+---------+ 2 rows in set (0.04 sec)
也可以把查询后的子表当主句查询表，例如：
select e.* , d.* from (select * from 表名 where date > '2006-5-23') e left join dept d on e.dept_id = d.id;
