八、MySQL基础系列笔记之SQL DQL7种连接查询
连接是关系数据库模型的主要特点。
连接查询是关系数据库中最主要的查询，包含内连接、外连接等。
准备工作
新建2张表，数据如下
mysql> select * from user;
+----+-----------+------------------+
| id | name | email |
+----+-----------+------------------+
| 1 | 李四 | 123456@qq.com |
| 2 | 王五 | 124568@qq.com |
| 3 | 张三 | zhangsan@163.com |
| 4 | 王美丽 | meili@163.com |
| 5 | 你真帅 | 7788641@qq.com |
| 6 | 刘一 | liuyi@163.com |
| 7 | 陈二 | 782372340@qq.com |
| 8 | 吴九 | 8193730-@qq.com |
+----+-----------+------------------+
8 rows in set (0.00 sec)
mysql> select * from info;
+----+---------+-----------+
| id | user_id | hobby |
+----+---------+-----------+
| 1 | 1 | 篮球 |
| 2 | 4 | 乒乓球 |
| 3 | 2 | 排球 |
| 4 | 6 | 棒球 |
| 5 | 3 | 溜溜球 |
| 6 | 9 | 足球 |
+----+---------+-----------+
6 rows in set (0.00 sec)
笛卡尔积
将出现48条记录，由于太长，不列出。
mysql> select * from user,info;
inner join - 内连接查询
inner join查询交集，也就是两表公有部分结果集。
mysql> select * from user as a inner join info as b on a.id = b.user_id;
+----+-----------+------------------+----+---------+-----------+
| id | name | email | id | user_id | hobby |
+----+-----------+------------------+----+---------+-----------+
| 1 | 李四 | 123456@qq.com | 1 | 1 | 篮球 |
| 4 | 王美丽 | meili@163.com | 2 | 4 | 乒乓球 |
| 2 | 王五 | 124568@qq.com | 3 | 2 | 排球 |
| 6 | 刘一 | liuyi@163.com | 4 | 6 | 棒球 |
| 3 | 张三 | zhangsan@163.com | 5 | 3 | 溜溜球 |
+----+-----------+------------------+----+---------+-----------+
left join - 左连接查询
left join 返回左表中所有的记录和右表中与连接字段相等的记录，右表中没有的记录补null。
mysql> select * from user as a left join info as b on a.id = b.user_id;
+----+-----------+------------------+------+---------+-----------+
| id | name | email | id | user_id | hobby |
+----+-----------+------------------+------+---------+-----------+
| 1 | 李四 | 123456@qq.com | 1 | 1 | 篮球 |
| 4 | 王美丽 | meili@163.com | 2 | 4 | 乒乓球 |
| 2 | 王五 | 124568@qq.com | 3 | 2 | 排球 |
| 6 | 刘一 | liuyi@163.com | 4 | 6 | 棒球 |
| 3 | 张三 | zhangsan@163.com | 5 | 3 | 溜溜球 |
| 5 | 你真帅 | 7788641@qq.com | NULL | NULL | NULL |
| 7 | 陈二 | 782372340@qq.com | NULL | NULL | NULL |
| 8 | 吴九 | 8193730-@qq.com | NULL | NULL | NULL |
+----+-----------+------------------+------+---------+-----------+
8 rows in set (0.00 sec)
right join - 右连接查询
right join 返回右表中所有的记录和左表中与连接字段相等的记录，左表中没有的记录补null。
mysql> select * from user as a right join info as b on a.id = b.user_id;
+------+-----------+------------------+----+---------+-----------+
| id | name | email | id | user_id | hobby |
+------+-----------+------------------+----+---------+-----------+
| 1 | 李四 | 123456@qq.com | 1 | 1 | 篮球 |
| 4 | 王美丽 | meili@163.com | 2 | 4 | 乒乓球 |
| 2 | 王五 | 124568@qq.com | 3 | 2 | 排球 |
| 6 | 刘一 | liuyi@163.com | 4 | 6 | 棒球 |
| 3 | 张三 | zhangsan@163.com | 5 | 3 | 溜溜球 |
| NULL | NULL | NULL | 6 | 9 | 足球 |
+------+-----------+------------------+----+---------+-----------+
6 rows in set (0.00 sec)
left join b.key is null 查询左表度有部分
mysql> select * from user as a left join info as b on a.id = b.user_id where b.user_id is null;
+----+-----------+------------------+------+---------+-------+
| id | name | email | id | user_id | hobby |
+----+-----------+------------------+------+---------+-------+
| 5 | 你真帅 | 7788641@qq.com | NULL | NULL | NULL |
| 7 | 陈二 | 782372340@qq.com | NULL | NULL | NULL |
| 8 | 吴九 | 8193730-@qq.com | NULL | NULL | NULL |
+----+-----------+------------------+------+---------+-------+
3 rows in set (0.00 sec)
right join a.key is null查询右表独有
mysql> select * from user as a right join info as b on a.id = b.user_id where a.id is null;
+------+------+-------+----+---------+--------+
| id | name | email | id | user_id | hobby |
+------+------+-------+----+---------+--------+
| NULL | NULL | NULL | 6 | 9 | 足球 |
+------+------+-------+----+---------+--------+
1 row in set (0.00 sec)
全连接查询
mysql> select * from user as a left join info as b on a.id = b.user_id
-> union
-> select * from user as a right join info as b on a.id = b.user_id;
+------+-----------+------------------+------+---------+-----------+
| id | name | email | id | user_id | hobby |
+------+-----------+------------------+------+---------+-----------+
| 1 | 李四 | 123456@qq.com | 1 | 1 | 篮球 |
| 4 | 王美丽 | meili@163.com | 2 | 4 | 乒乓球 |
| 2 | 王五 | 124568@qq.com | 3 | 2 | 排球 |
| 6 | 刘一 | liuyi@163.com | 4 | 6 | 棒球 |
| 3 | 张三 | zhangsan@163.com | 5 | 3 | 溜溜球 |
| 5 | 你真帅 | 7788641@qq.com | NULL | NULL | NULL |
| 7 | 陈二 | 782372340@qq.com | NULL | NULL | NULL |
| 8 | 吴九 | 8193730-@qq.com | NULL | NULL | NULL |
| NULL | NULL | NULL | 6 | 9 | 足球 |
+------+-----------+------------------+------+---------+-----------+
9 rows in set (0.00 sec)
单a与独b查询
mysql> select * from user as a left join info as b on a.id = b.user_id where b.user_id is null
-> union
-> select * from user as a right join info as b on a.id = b.user_id where a.id is null;
+------+-----------+------------------+------+---------+--------+
| id | name | email | id | user_id | hobby |
+------+-----------+------------------+------+---------+--------+
| 5 | 你真帅 | 7788641@qq.com | NULL | NULL | NULL |
| 7 | 陈二 | 782372340@qq.com | NULL | NULL | NULL |
| 8 | 吴九 | 8193730-@qq.com | NULL | NULL | NULL |
| NULL | NULL | NULL | 6 | 9 | 足球 |
+------+-----------+------------------+------+---------+--------+
4 rows in set (0.00 sec)
2020-10-11
请登录后再评论