汉码未来

1. 插入表测试数据

为了演示方便，分别清空学生信息表 student、教师信息表 teacher、课程表 course、学生选课关联表 student_course 数据，并分别插入测试数据。

1.1 清空表数据

分别清空学生信息表 student、教师信息表 teacher、课程表 course、学生选课关联表 student_course 数据：

 TRUNCATE TABLE student;
 TRUNCATE TABLE teacher;
 TRUNCATE TABLE course;
 TRUNCATE TABLE student_course;
代码块
1
2
3
4

1.2 向学生信息表 student 插入数据

 INSERT INTO student (name,age,id_number) VALUES
 ('赵小明',21,'420117199303036666'),
 ('王小红',22,'420117199303037777'),
 ('张小虎',18,'420117199303038888'),
 ('李小平',19,'420117199303039999'),
 ('刘美丽',21,'420117199303035555'),
 ('周杰',22,'420117199303034444'),
 ('秦小贤',19,'420117199303033333'),
 ('马笑',23,'420117199303032222'),
 ('艾伦',22,'420117199303031111'),
 ('包小天',20,'420117199303030000');
代码块
1
2
3
4
5
6
7
8
9
10
11

执行结果如下图：Tips：如果上述添加表数据出现报错，可能是因为之前修改表字段的时候把表的字段长度或者类型更改了，请检查字段类型和长度是否正确，还可以删除所有表按照之前建表步骤重新建表。

1.3 向教师信息表 teacher 插入数据

INSERT INTO teacher (name,age,id_number,email) VALUES('Tom'，29,'420117202006040000'，'tom@qq.com')，('Jack'，30,'420117202006041111'，'jack@qq.com')，('Mary'，31,'420117202006042222'，'mary@qq.com')，('Timo'，35,'420117202006043333'，'timo@qq.com')，('Faker'，38,'420117202006044444'，'faker@qq.com')，('Bob'，35,'420117202006045555'，'bob@qq.com')，('kelly'，40,'420117202006046666'，'kelly@qq.com')，('Rose'，42,'420117202006047777'，'rose@qq.com')，('Hale'，55,'420117202006048888'，'hale@qq.com')，('John'，49,'420117202006049999'，'john@qq.com')，('Amy'，55,'42011720200604888X'，'amy@qq.com')，('Judy'，49,'42011720200604999X'，'judy@qq.com');
代码块
1
2
3
4
5
6
7
8
9
10
11
12
13

执行结果如下图：

]

1.4 向课程表 course 插入数据

因为表 course 有 teacher_id 字段，所以插入数据之前，需要获取 teacher 表数据：

SELECT * FROM teacher;
代码块
1

teacher 表 id 的值如上图所示，对应 course 表 teacher_id，按照上面 id 可插入相关课程信息：

INSERT INTO course (course_name,teacher_id) VALUES('高等数学',1),('英语',2),('政治',3),('信息论',4),('数据结构和算法',5),('体育',6),('模拟电路',7),('数字电路',8),('通信原理',9),('信号系统',10),('概率论',13),('光学原理',14);
代码块
1
2
3
4
5
6
7
8
9
10
11
12
13

执行结果如下图：

1.5 向学生选课关联表 student_course 插入数据

INSERT INTO student_course (student_id,course_id) VALUES(1,1),(1,2),(2,3),(2,4),(3,5),(3,6),(4,7),(4,8),(5,9),(5,10),(6,1),(6,2),(7,3),(7,4);
代码块
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

执行结果如下图：

2.LEFT JOIN 左连接

以课程表 course 和 teacher 左连接为例：

SELECT c.id AS course_id,c.*,t.* FROM course c LEFT JOIN teacher t ON c.teacher_id=t.id; 
代码块
1

执行结果如下图：

Tips：这对上述 sql 语句说明如下：

• c.id AS course_id 表示将 course表 中 id 字段重命名为 course_id 展示，其目的是为了防止和 teacher表 中 id 字段混淆；
• c.* 表示 course 表所有字段数据；
• t.* 表示 teacher 表字段所有数据；
• ON 后面跟着的条件是连接表的条件；
• course c 表示将 course 简写为 c， teacher t 表示将 teacher 简写为 t；
• LEFT JOIN 为左连接，是以左边的表为’基准’，若右表没有对应的值，用 NULL 来填补。

3.INNER JOIN 内连接

同样以表 course 和 teacher 内连接为例：

SELECT c.id AS course_id,c.*,t.* FROM course c INNER JOIN teacher t ON c.teacher_id=t.id;
代码块
1

执行结果如下图：

Tips：INNER JOIN 为内连接，展示的是左右两表都有对应的数据。

4.RIGHT JOIN 右连接

同样以表 course 和 teacher 右连接为例：

SELECT c.id AS course_id,c.*,t.* FROM course c RIGHT JOIN teacher t ON c.teacher_id=t.id;
代码块
1

执行结果如下图：

Tips：RIGHT JOIN 为右连接，是以右边的表为’基准’，若左表没有对应的值，用 NULL 来填补。

5. 多表混合连接

以本小节所有数据全部连接查询为例：

SELECT * FROM student a LEFT JOIN student_course b ON a.id=b.student_id 
RIGHT JOIN course c ON b.course_id=c.id 
INNER JOIN teacher d ON c.teacher_id=d.id;
代码块
1
2
3
4
5
6
7
8
9
10

执行结果如下图：

Tips：多表混合连接查询时，后面可以把前面执行的结果集整体当成一个表，例如 SELECT * FROM student a LEFT JOIN student_course b ON a.id=b.student_id RIGHT JOIN course c ON b.course_id=c.id 可以理解为 student 和 student_course 左连接查询之后的结果集再对 course 右连接查询。

6. 小结

本小节介绍了如何使用 LEFT JOIN、INNER JOIN、RIGHT JOIN 对表数据进行连接查询，需要注意的是，ON 后面的表示对连表条件，并且还能对表连接查询的结果集用 WHERE 进行筛选，例如：

SELECT * FROM student a LEFT JOIN student_course b ON a.id=b.student_id 
RIGHT JOIN course c ON b.course_id=c.id 
INNER JOIN teacher d ON c.teacher_id=d.idWHERE a.age > 18 AND d.age < 55;
代码块
预览复制
1
2
3
4
5
6
7
8
9
10
11

以上 sql 语句后面使用了 WHERE 条件筛选，表示学生年龄大于 18，教师年龄小于 55。