欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识！
您现在的位置是： 首页

MySQL讲义第 25 讲——select 查询之连接查询

程序员文章站 2022-05-28 13:22:04
...

MySQL讲义第 25 讲——select 查询之连接查询

连接查询就是从多张表中查询数据，通常进行连接的两张表中的数据具有一对多的联系，依靠子表中的外键和对应的父表中的主键建立关联条件。

一、交叉连接（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基本操作 MySQL讲义 数据库 mysql select 查询

上一篇： PHP使用Pthread实现的多线程操作实例，pthread实例_PHP教程

下一篇： 这里怎么理解呢

推荐阅读