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

  

 概念

  • 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)

  

posted @ 2019-03-19 17:38  LiliP  阅读(4622)  评论(0编辑  收藏  举报