讲义第 25 讲——select 查询之连接查询"/>
MySQL讲义第 25 讲——select 查询之连接查询
MySQL讲义第 25 讲——select 查询之连接查询
文章目录
- MySQL讲义第 25 讲——select 查询之连接查询
- 一、交叉连接（CROSS JOIN）
- 二、内连接（INNER JOIN）
- 1、查询学生的 stu_name，phone 和所在系的 dept_name
- 2、查询所有男生的 stu_id、stu_name、height 和 dept_name
- 3、查询 2019 级学生的 stu_id、stu_name 和 dept_name
- 三、外连接
- 1.左外连接
- 2、右外连接
- 3、完全外连接
连接查询就是从多张表中查询数据，通常进行连接的两张表中的数据具有一对多的联系，依靠子表中的外键和对应的父表中的主键建立关联条件。
一、交叉连接（CROSS JOIN）
交叉连接（CROSS JOIN）又称为关系的笛卡儿积。是用左表中的每一行与右表中的每一行进行连接，所得到的结果是这两个表中各行数据的所有组合。例如：
mysql> select * from dept;
+---------+--------------+
| dept_id | dept_name |
+---------+--------------+
| D01 | 管理系 |
| D02 | 计算机系 |
| D03 | 数学系 |
+---------+--------------+
3 rows in set (0.00 sec)mysql> select * from stu;
+-----------+-----------+--------+--------+-------------+---------+
| stu_id | stu_name | gender | height | phone | dept_id |
+-----------+-----------+--------+--------+-------------+---------+
| 201801101 | 王占峰 | 男 | 177.0 | 15937320987 | D02 |
| 201901002 | 王宏伟 | 男 | 180.0 | 15937320255 | D01 |
| 201901004 | 李刚 | 男 | 178.0 | 15937320321 | D01 |
| 201901005 | 刘鹏 | 男 | 176.0 | 15937320654 | D01 |
+-----------+-----------+--------+--------+-------------+---------+
4 rows in set (0.00 sec)mysql> SELECT-> *-> FROM-> stu cross join dept;
+-----------+-----------+--------+--------+-------------+---------+---------+--------------+
| stu_id | stu_name | gender | height | phone | dept_id | dept_id | dept_name |
+-----------+-----------+--------+--------+-------------+---------+---------+--------------+
| 201801101 | 王占峰 | 男 | 177.0 | 15937320987 | D02 | D01 | 管理系 |
| 201801101 | 王占峰 | 男 | 177.0 | 15937320987 | D02 | D02 | 计算机系 |
| 201801101 | 王占峰 | 男 | 177.0 | 15937320987 | D02 | D03 | 数学系 |
| 201901002 | 王宏伟 | 男 | 180.0 | 15937320255 | D01 | D01 | 管理系 |
| 201901002 | 王宏伟 | 男 | 180.0 | 15937320255 | D01 | D02 | 计算机系 |
| 201901002 | 王宏伟 | 男 | 180.0 | 15937320255 | D01 | D03 | 数学系 |
| 201901004 | 李刚 | 男 | 178.0 | 15937320321 | D01 | D01 | 管理系 |
| 201901004 | 李刚 | 男 | 178.0 | 15937320321 | D01 | D02 | 计算机系 |
| 201901004 | 李刚 | 男 | 178.0 | 15937320321 | D01 | D03 | 数学系 |
| 201901005 | 刘鹏 | 男 | 176.0 | 15937320654 | D01 | D01 | 管理系 |
| 201901005 | 刘鹏 | 男 | 176.0 | 15937320654 | D01 | D02 | 计算机系 |
| 201901005 | 刘鹏 | 男 | 176.0 | 15937320654 | D01 | D03 | 数学系 |
+-----------+-----------+--------+--------+-------------+---------+---------+--------------+
12 rows in set (0.02 sec)-- 也可以把 cross join 换成逗号（,）
mysql> SELECT -> * -> FROM -> stu,dept;
+-----------+-----------+--------+--------+-------------+---------+---------+--------------+
| stu_id | stu_name | gender | height | phone | dept_id | dept_id | dept_name |
+-----------+-----------+--------+--------+-------------+---------+---------+--------------+
| 201801101 | 王占峰 | 男 | 177.0 | 15937320987 | D02 | D01 | 管理系 |
| 201801101 | 王占峰 | 男 | 177.0 | 15937320987 | D02 | D02 | 计算机系 |
| 201801101 | 王占峰 | 男 | 177.0 | 15937320987 | D02 | D03 | 数学系 |
| 201901002 | 王宏伟 | 男 | 180.0 | 15937320255 | D01 | D01 | 管理系 |
| 201901002 | 王宏伟 | 男 | 180.0 | 15937320255 | D01 | D02 | 计算机系 |
| 201901002 | 王宏伟 | 男 | 180.0 | 15937320255 | D01 | D03 | 数学系 |
| 201901004 | 李刚 | 男 | 178.0 | 15937320321 | D01 | D01 | 管理系 |
| 201901004 | 李刚 | 男 | 178.0 | 15937320321 | D01 | D02 | 计算机系 |
| 201901004 | 李刚 | 男 | 178.0 | 15937320321 | D01 | D03 | 数学系 |
| 201901005 | 刘鹏 | 男 | 176.0 | 15937320654 | D01 | D01 | 管理系 |
| 201901005 | 刘鹏 | 男 | 176.0 | 15937320654 | D01 | D02 | 计算机系 |
| 201901005 | 刘鹏 | 男 | 176.0 | 15937320654 | D01 | D03 | 数学系 |
+-----------+-----------+--------+--------+-------------+---------+---------+--------------+
12 rows in set (0.00 sec)
二、内连接（INNER JOIN）
查询结果中只包含两表的公共字段（子表的外键和父表的主键）值相等的行。语法如下：
FROM 子表 INNER JOIN 父表 ON 子表.外键 = 父表.主键
或
FROM 子表 JOIN 父表 ON 子表.外键 = 父表.主键
或
FROM 子表, 父表
WHERE 子表.外键 = 父表.主键
说明：连接查询时如果需要使用的列在多个表中被使用，需要在列名前面加上表名加以限定，即：表名.列名。
1、查询学生的 stu_name，phone 和所在系的 dept_name
--说明：stu s 表示为 stu 表指定别名 s
mysql> SELECT-> stu_name,-> phone,-> dept_name-> FROM-> stu s INNER JOIN dept d ON s.dept_id = d.dept_id;
+-----------+-------------+--------------+
| stu_name | phone | dept_name |
+-----------+-------------+--------------+
| 王占峰 | 15937320987 | 计算机系 |
| 刘国强 | 15937320789 | 计算机系 |
| 巩莉 | 15937320456 | 计算机系 |
| 宋丹风 | 15937320444 | 计算机系 |
| 王艳艳 | NULL | 数学系 |
| 赵牡丹 | 15937320666 | 数学系 |
| 王鹏飞 | 15937320555 | 数学系 |
| 王宏伟 | 15937320255 | 管理系 |
| 张静静 | 15937320123 | 管理系 |
| 李刚 | 15937320321 | 管理系 |
| 刘鹏 | NULL | 管理系 |
+-----------+-------------+--------------+
11 rows in set (0.00 sec)
2、查询所有男生的 stu_id、stu_name、height 和 dept_name
mysql> SELECT-> s.stu_id,-> s.stu_name,-> s.height,-> d.dept_name-> FROM-> stu s JOIN dept d ON s.dept_id = d.dept_id-> WHERE-> gender = '男';
+-----------+-----------+--------+--------------+
| stu_id | stu_name | height | dept_name |
+-----------+-----------+--------+--------------+
| 201801101 | 王占峰 | 177.0 | 计算机系 |
| 201801102 | 刘国强 | 174.0 | 计算机系 |
| 201801203 | 王鹏飞 | 174.0 | 数学系 |
| 201901002 | 王宏伟 | 180.0 | 管理系 |
| 201901004 | 李刚 | 178.0 | 管理系 |
| 201901005 | 刘鹏 | 176.0 | 管理系 |
+-----------+-----------+--------+--------------+
6 rows in set (0.01 sec)
3、查询 2019 级学生的 stu_id、stu_name 和 dept_name
mysql> SELECT-> s.stu_id,-> s.stu_name,-> d.dept_name-> FROM-> stu s, dept d-> WHERE-> s.dept_id = d.dept_id AND-> stu_id LIKE '2019%';
+-----------+-----------+-----------+
| stu_id | stu_name | dept_name |
+-----------+-----------+-----------+
| 201901002 | 王宏伟 | 管理系 |
| 201901003 | 张静静 | 管理系 |
| 201901004 | 李刚 | 管理系 |
| 201901005 | 刘鹏 | 管理系 |
+-----------+-----------+-----------+
4 rows in set (0.68 sec)
三、外连接
外连接包括左外连接（LEFT JOIN）、右外连接（RIGHT JOIN）、完全外连接（FULL JOIN，MySQL不支持）。
（1）左外连接：结果集中包括两表连接后满足指定的连接条件的行，还显示 JOIN 关键字左侧表中所有满足检索条件的行，如何左表的某行在右表中没有匹配行，则在结果中，右表的所有选择列均为NULL。
（2）右外连接：是左外连接的反向连接。
（3）完全外连接：完全外连接查询的结果集包括两表内连接的结果集和左表与右表中不满足条件的行。
1.左外连接
mysql> SELECT-> s.stu_id,-> s.stu_name,-> s.height,-> s.phone,-> s.dept_id stu_dept_id,-> d.dept_id,-> d.dept_name-> FROM-> stu s LEFT JOIN dept d ON s.dept_id = d.dept_id;
+-----------+-----------+--------+-------------+-------------+---------+--------------+
| stu_id | stu_name | height | phone | stu_dept_id | dept_id | dept_name |
+-----------+-----------+--------+-------------+-------------+---------+--------------+
| 201901002 | 王宏伟 | 180.0 | 15937320255 | D01 | D01 | 管理系 |
| 201901003 | 张静静 | 167.0 | 15937320123 | D01 | D01 | 管理系 |
| 201901004 | 李刚 | 178.0 | 15937320321 | D01 | D01 | 管理系 |
| 201901005 | 刘鹏 | 176.0 | NULL | D01 | D01 | 管理系 |
| 201801101 | 王占峰 | 177.0 | 15937320987 | D02 | D02 | 计算机系 |
| 201801102 | 刘国强 | 174.0 | 15937320789 | D02 | D02 | 计算机系 |
| 201801103 | 巩莉 | 170.0 | 15937320456 | D02 | D02 | 计算机系 |
| 201801104 | 宋丹风 | 165.0 | 15937320444 | D02 | D02 | 计算机系 |
| 201801202 | 赵牡丹 | 160.0 | 15937320666 | D03 | D03 | 数学系 |
| 201801203 | 王鹏飞 | 174.0 | 15937320555 | D03 | D03 | 数学系 |
| 201801201 | 王艳艳 | 162.0 | NULL | NULL | NULL | NULL |
+-----------+-----------+--------+-------------+-------------+---------+--------------+
11 rows in set (0.00 sec)
2、右外连接
mysql> SELECT-> s.stu_id,-> s.stu_name,-> s.height,-> s.phone,-> s.dept_id stu_dept_id,-> d.dept_id,-> d.dept_name-> FROM-> stu s RIGHT JOIN dept d ON s.dept_id = d.dept_id;
+-----------+-----------+--------+-------------+-------------+---------+--------------+
| stu_id | stu_name | height | phone | stu_dept_id | dept_id | dept_name |
+-----------+-----------+--------+-------------+-------------+---------+--------------+
| 201801101 | 王占峰 | 177.0 | 15937320987 | D02 | D02 | 计算机系 |
| 201801102 | 刘国强 | 174.0 | 15937320789 | D02 | D02 | 计算机系 |
| 201801103 | 巩莉 | 170.0 | 15937320456 | D02 | D02 | 计算机系 |
| 201801104 | 宋丹风 | 165.0 | 15937320444 | D02 | D02 | 计算机系 |
| 201801202 | 赵牡丹 | 160.0 | 15937320666 | D03 | D03 | 数学系 |
| 201801203 | 王鹏飞 | 174.0 | 15937320555 | D03 | D03 | 数学系 |
| 201901002 | 王宏伟 | 180.0 | 15937320255 | D01 | D01 | 管理系 |
| 201901003 | 张静静 | 167.0 | 15937320123 | D01 | D01 | 管理系 |
| 201901004 | 李刚 | 178.0 | 15937320321 | D01 | D01 | 管理系 |
| 201901005 | 刘鹏 | 176.0 | NULL | D01 | D01 | 管理系 |
| NULL | NULL | NULL | NULL | NULL | D04 | 法律系 |
| NULL | NULL | NULL | NULL | NULL | D05 | 艺术系 |
+-----------+-----------+--------+-------------+-------------+---------+--------------+
12 rows in set (0.00 sec)3、完全外连接
MySQL 不支持完全外连接，可以使用左外连接和右外连接进行查询，然后在执行 Union 运算达到同样的效果，命令如下：
mysql> SELECT-> s.stu_id,-> s.stu_name,-> s.phone,-> s.dept_id,-> d.dept_name-> FROM-> stu s LEFT JOIN dept d ON s.dept_id = d.dept_id-> UNION-> SELECT-> s.stu_id,-> s.stu_name,-> s.phone,-> s.dept_id,-> d.dept_name-> FROM-> stu s RIGHT JOIN dept d ON s.dept_id = d.dept_id;
+-----------+-----------+-------------+---------+--------------+
| stu_id | stu_name | phone | dept_id | dept_name |
+-----------+-----------+-------------+---------+--------------+
| 201901002 | 王宏伟 | 15937320255 | D01 | 管理系 |
| 201901003 | 张静静 | 15937320123 | D01 | 管理系 |
| 201901004 | 李刚 | 15937320321 | D01 | 管理系 |
| 201901005 | 刘鹏 | NULL | D01 | 管理系 |
| 201801101 | 王占峰 | 15937320987 | D02 | 计算机系 |
| 201801102 | 刘国强 | 15937320789 | D02 | 计算机系 |
| 201801103 | 巩莉 | 15937320456 | D02 | 计算机系 |
| 201801104 | 宋丹风 | 15937320444 | D02 | 计算机系 |
| 201801202 | 赵牡丹 | 15937320666 | D03 | 数学系 |
| 201801203 | 王鹏飞 | 15937320555 | D03 | 数学系 |
| 201801201 | 王艳艳 | NULL | NULL | NULL |
| NULL | NULL | NULL | NULL | 法律系 |
| NULL | NULL | NULL | NULL | 艺术系 |
+-----------+-----------+-------------+---------+--------------+
13 rows in set (0.02 sec)
更多推荐
MySQL讲义第 25 讲——select 查询之连接查询
发布评论