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

【MySQL】MySQL内连接,左连接,右连接查询

程序员文章站 2022-06-04 20:03:53
概念 INNER JOIN(内连接):获取两个表中字段匹配关系的记录。也就是只会返回共有的内容。 LEFT JOIN(左连接):获取左表所有记录,即使右表没有对应匹配的记录。 RIGHT JOIN(右连接): 获取右表所有记录,即使左表没有对应匹配的记录。 示例 先在数据库中建立两张表student ......

  

 概念

  • inner join(内连接):获取两个表中字段匹配关系的记录。也就是只会返回共有的内容。
  • left join(左连接):获取左表所有记录,即使右表没有对应匹配的记录。
  • right join(右连接): 获取右表所有记录,即使左表没有对应匹配的记录。

 

 示例

  • 先在数据库中建立两张表student和score,具体内容如下:

  【student】

mysql> select * from student;
--------------
select * from student
--------------

+----+---------------------+------+-------+------------+-----------+
| id | name                | sex  | birth | department | address   |
+----+---------------------+------+-------+------------+-----------+
|  1 | rooneymara          | f    |  1985 | psychology | american  |
|  2 | chrishemsworth      | m    |  1983 | cs         | australia |
|  3 | ellenpage           | f    |  1987 | music      | canada    |
|  4 | tomholland          | m    |  1996 | cs         | england   |
|  5 | scarlettjohansson   | f    |  1984 | music      | american  |
|  6 | benedictcumberbatch | m    |  1976 | psychology | england   |
|  7 | evagreen            | f    |  1980 | math       | france    |
+----+---------------------+------+-------+------------+-----------+
7 rows in set (0.00 sec)

 

  【score】

mysql> select * from score;
--------------
select * from score
--------------

+----+--------+------------+-------+
| id | stu_id | c_name     | grade |
+----+--------+------------+-------+
|  1 |      1 | psychology |    98 |
|  2 |      1 | music      |    80 |
|  3 |      2 | psychology |    65 |
|  4 |      2 | cs         |    88 |
|  5 |      3 | cs         |    95 |
|  6 |      4 | psychology |    70 |
|  7 |      4 | music      |    92 |
|  8 |      5 | music      |    94 |
|  9 |      6 | psychology |    90 |
| 10 |      6 | cs         |    85 |
| 11 |      8 | music      |    91 |
+----+--------+------------+-------+
11 rows in set (0.00 sec)

  

  •  内连接

  查询student表中的所有个人信息及score表中的c_name,grade

mysql> select a.*,c_name,grade from student a join score b on a.id=b.stu_id;
--------------
select a.*,c_name,grade from student a join score b on a.id=b.stu_id
--------------

+----+---------------------+------+-------+------------+-----------+------------+-------+
| id | name                | sex  | birth | department | address   | c_name     | grade |
+----+---------------------+------+-------+------------+-----------+------------+-------+
|  1 | rooneymara          | f    |  1985 | psychology | american  | psychology |    98 |
|  1 | rooneymara          | f    |  1985 | psychology | american  | music      |    80 |
|  2 | chrishemsworth      | m    |  1983 | cs         | australia | psychology |    65 |
|  2 | chrishemsworth      | m    |  1983 | cs         | australia | cs         |    88 |
|  3 | ellenpage           | f    |  1987 | music      | canada    | cs         |    95 |
|  4 | tomholland          | m    |  1996 | cs         | england   | psychology |    70 |
|  4 | tomholland          | m    |  1996 | cs         | england   | music      |    92 |
|  5 | scarlettjohansson   | f    |  1984 | music      | american  | music      |    94 |
|  6 | benedictcumberbatch | m    |  1976 | psychology | england   | psychology |    90 |
|  6 | benedictcumberbatch | m    |  1976 | psychology | england   | cs         |    85 |
+----+---------------------+------+-------+------------+-----------+------------+-------+
10 rows in set (0.00 sec)

 
  以上语句等价于:

mysql> select a.*,c_name,grade from student a,score b where a.id=b.stu_id;
--------------
select a.*,c_name,grade from student a,score b where a.id=b.stu_id
--------------

+----+---------------------+------+-------+------------+-----------+------------+-------+
| id | name                | sex  | birth | department | address   | c_name     | grade |
+----+---------------------+------+-------+------------+-----------+------------+-------+
|  1 | rooneymara          | f    |  1985 | psychology | american  | psychology |    98 |
|  1 | rooneymara          | f    |  1985 | psychology | american  | music      |    80 |
|  2 | chrishemsworth      | m    |  1983 | cs         | australia | psychology |    65 |
|  2 | chrishemsworth      | m    |  1983 | cs         | australia | cs         |    88 |
|  3 | ellenpage           | f    |  1987 | music      | canada    | cs         |    95 |
|  4 | tomholland          | m    |  1996 | cs         | england   | psychology |    70 |
|  4 | tomholland          | m    |  1996 | cs         | england   | music      |    92 |
|  5 | scarlettjohansson   | f    |  1984 | music      | american  | music      |    94 |
|  6 | benedictcumberbatch | m    |  1976 | psychology | england   | psychology |    90 |
|  6 | benedictcumberbatch | m    |  1976 | psychology | england   | cs         |    85 |
+----+---------------------+------+-------+------------+-----------+------------+-------+
10 rows in set (0.00 sec)

  

  •  左连接

  student表中id为7的数据,在score中没有对应的内容。所以最后一条查询结果c_name,grade对应内容为null。

mysql> select a.*,c_name,grade from student a left join score b on a.id=b.stu_id;
--------------
select a.*,c_name,grade from student a left join score b on a.id=b.stu_id
--------------

+----+---------------------+------+-------+------------+-----------+------------+-------+
| id | name                | sex  | birth | department | address   | c_name     | grade |
+----+---------------------+------+-------+------------+-----------+------------+-------+
|  1 | rooneymara          | f    |  1985 | psychology | american  | psychology |    98 |
|  1 | rooneymara          | f    |  1985 | psychology | american  | music      |    80 |
|  2 | chrishemsworth      | m    |  1983 | cs         | australia | psychology |    65 |
|  2 | chrishemsworth      | m    |  1983 | cs         | australia | cs         |    88 |
|  3 | ellenpage           | f    |  1987 | music      | canada    | cs         |    95 |
|  4 | tomholland          | m    |  1996 | cs         | england   | psychology |    70 |
|  4 | tomholland          | m    |  1996 | cs         | england   | music      |    92 |
|  5 | scarlettjohansson   | f    |  1984 | music      | american  | music      |    94 |
|  6 | benedictcumberbatch | m    |  1976 | psychology | england   | psychology |    90 |
|  6 | benedictcumberbatch | m    |  1976 | psychology | england   | cs         |    85 |
|  7 | evagreen            | f    |  1980 | math       | france    | null       |  null |
+----+---------------------+------+-------+------------+-----------+------------+-------+
11 rows in set (0.00 sec)

  

  •  右连接

  score表中id为11的数据,在student中没有对应的内容,所以最后一条查询结果id,name,sex等对应内容为null。

mysql> select a.*,c_name,grade from student a right join score b on a.id=b.stu_id;
--------------
select a.*,c_name,grade from student a right join score b on a.id=b.stu_id
--------------

+------+---------------------+------+-------+------------+-----------+------------+-------+
| id   | name                | sex  | birth | department | address   | c_name     | grade |
+------+---------------------+------+-------+------------+-----------+------------+-------+
|    1 | rooneymara          | f    |  1985 | psychology | american  | psychology |    98 |
|    1 | rooneymara          | f    |  1985 | psychology | american  | music      |    80 |
|    2 | chrishemsworth      | m    |  1983 | cs         | australia | psychology |    65 |
|    2 | chrishemsworth      | m    |  1983 | cs         | australia | cs         |    88 |
|    3 | ellenpage           | f    |  1987 | music      | canada    | cs         |    95 |
|    4 | tomholland          | m    |  1996 | cs         | england   | psychology |    70 |
|    4 | tomholland          | m    |  1996 | cs         | england   | music      |    92 |
|    5 | scarlettjohansson   | f    |  1984 | music      | american  | music      |    94 |
|    6 | benedictcumberbatch | m    |  1976 | psychology | england   | psychology |    90 |
|    6 | benedictcumberbatch | m    |  1976 | psychology | england   | cs         |    85 |
| null | null                | null |  null | null       | null      | music      |    91 |
+------+---------------------+------+-------+------------+-----------+------------+-------+
11 rows in set (0.00 sec)