五、多表连接查询——内连接

柔情只为你懂 2023-06-22 13:58 22阅读 0赞

## 五、多表连接查询——内连接 ##

### 1.什么是内连接 ###

根据相同的某列把多个表合并。

## ##

### 2.等值连接【最常用】 ###

**使用等于号(=)运算符比较被连接列的列值。**

**(1)等值连接语法**

<1>格式一：

select 表名.列名
    from 表名1 [inner] join 表名2
    on 表名1.列名=表名2.列名 [...n]

<2>格式二：

select 表名.列名
    from 表名1,表名2 [,...]
    where 表名1.列名=表名2.列名 [and...]

**(2)将学生表(student)和成绩表(student\_score)通过学号(student\_id)连接起来。【选择全部数据】**

<1>使用inner join的方法

select * from student inner join student_score on student.student_id=student_score.student_id;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw_size_16_color_FFFFFF_t_70][]

<2>使用where

select * from student,student_score where student.student_id=student_score.student_id;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw_size_16_color_FFFFFF_t_70 1][]

**（2）将学生表(student)和成绩表(student\_score)通过学号(student\_id)连接起来。【选择学号、姓名、成绩三列】**

select student.student_id,student.name,student_score.score from student join student_score on student.student_id=student_score.student_id;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw_size_16_color_FFFFFF_t_70 2][]

### **3.不等值连接** ###

在连接条件使用除了等于号(=)之外的其它比较运算符比较被连接的列的列值。

比如：>、>=、<、<=、!=、<>、!>、!<。

**(1)示例：将学生表(student)和成绩表(student\_score)通过学号(student\_id)使用>连接起来。【选择全部数据】**

select * from student join student_score on student.student_id>student_score.student_id;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw_size_16_color_FFFFFF_t_70 3][]

### 4.自然连接 ###

自动去除重复的属性列，仅保留所有不重复的属性列。

自然连接中无需指定连接条件，自动匹配列值相同的字段。

**(1)示例：将学生表(student)和成绩表(student\_score)自然连接。【选择全部数据】**

select * from student natural join student_score;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw_size_16_color_FFFFFF_t_70 4][]

(2)**示例：将学生表(student)和成绩表(student\_score)自然连接。【选择部分数据】**

select student.student_id,student.name,student_score.student_id,student_score.score 
    from student natural join student_score;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw_size_16_color_FFFFFF_t_70 5][]

**为什么会有重复值？**

虽然说select先写，但是select的操作在这个sql语句中最后执行，首先执行自然连接，然后执行选择数据。在自然连接时得到了一张表，但是这张表的结果被select的操作给覆盖掉了。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20191216204150868.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/2019121620442849.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20191216204654613.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20191216210015616.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20191216210559252.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw_size_16_color_FFFFFF_t_70 5]: https://img-blog.csdnimg.cn/20191216210853346.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjgzMDY5Nw==,size_16,color_FFFFFF,t_70