文档章节

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

o
 osc_n6euf5h6
发布于 2019/03/19 17:38
字数 858
阅读 7
收藏 0

精选30+云产品,助力企业轻松上云!>>>

  

 概念

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

  

o
粉丝 0
博文 500
码字总数 0
作品 0
私信 提问
加载中
请先登录后再评论。
【MySQL】中的多表连接是什么,以及如何实现全外连接查询?

简介 在MySQL中,多表连接的语法格式如下: 其中连接类型有三种: 内连接(inner join) 外连接(outer join) 交叉连接(cross join) 多表连接往往要通过表之间的物理外键(或者逻辑外键)...

一只爱挑错的网虫
04/21
0
0
【MySQL】SQL语句基础

一、操作数据库 1.1 创建数据库 1.2 查看数据库 1.3 修改数据库 1.4 删除数据库 1.5 选择数据库 二、操作表 2.1 创建表 2.2 查看表 2.3 修改表 2.4 删除表 三、操作表记录CRUD 3.1 INSERT 3...

osc_4sjc9hg4
2019/09/23
2
0
【MySQL】MySQL基础

一、基本语法 【MySQL目录结构】 ●bin目录,存储可执行文件 ●data目录,存储数据文件 ●docs,文档 ●include目录,存储包含的头文件 ●lib目录,存储库文件 ●share,错误信息和字符集文件...

osc_snrxosv0
2018/02/03
3
0
MySQL教程(十四)—— 连接

1 连接的语法结构 MySQL在SELECT语句、多表更新、多表删除语句中支持JOIN操作。 语法结构: 1 table_reference//第一张表2 {[INNER | CROSS] JOIN | {LEFT | RIGHT} [OUTER] JOIN}//连接选择...

osc_mi06gsf5
2018/02/19
2
0
mysql内连接,外连接,左右连接...等等

图解MySQL 内连接、外连接、左连接、右连接、全连接……太多了 版权声明:本文为博主转载文章,未经原博主允许不得转载。 原路径:https://blog.csdn.net/plg17/article/details/78758593 用...

地表最弱小火龙_
2018/10/30
8
0

没有更多内容

加载失败,请刷新页面

加载更多

Asp.net core之NLog

NuGet添加 NLog.Web.AspNetCore。 <PackageReference Include="Microsoft.AspNetCore.App" /> 添加配置文件 新建一个文件nlog.config(建议全部小写,linux系统中要注意), 并右键点击其属性......

一介草民Coder
5分钟前
0
0
.NET中的struct和class有什么区别? - What's the difference between struct and class in .NET?

问题: .NET中的struct和class有什么区别? 解决方案: 参考一: https://stackoom.com/question/3OT/NET中的struct和class有什么区别 参考二: https://oldbug.net/q/3OT/What-s-the-differ...

富含淀粉
46分钟前
23
0
android:layout_weight是什么意思? - What does android:layout_weight mean?

问题: I don't understand how to use this attribute. 我不明白如何使用这个属性。 Can anyone tell me more about it? 谁能告诉我更多关于它的事情? 解决方案: 参考一: https://stacko...

javail
今天
17
0
CSS背景不透明度[重复] - CSS Background Opacity [duplicate]

问题: This question already has an answer here: 这个问题已经在这里有了答案: How do I give text or an image a transparent background using CSS? 如何使用CSS为文本或图像提供透明背...

fyin1314
今天
31
0
node http 获取gb2312网页如何转为utf8

最初,我想当然认为是下述做法,但被证明是错误的 const http = require('http'), iconv = require('iconv-lite');const url = 'http://xxx';http.get(url, function(res) { var bo......

高延
今天
24
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部