【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)
上一篇: MySQL添加、删除索引