多表链接

二、多表链接

1. 联结

1.1 创建联结

`teachers`.`id` = `courses`.`teacher_id` 

2. 内连接 INNER JOIN

SELECT `table1`.`column1`, `table2`.`column2`...
FROM `table1`
INNER JOIN `table2`
ON `table1`.`common_field` = `table2`.`common_field`;

请编写 SQL 语句，将课程表 courses 和教师表 teachers 进行内连接，查询 “Eastern Heretic” 老师所教的所有课程的课程名和课程编号 , 且结果列名分别以课程编号 id 、课程名称 course_name 和教师姓名 teacher_name 显示。

SELECT c.id,c.name as course_name ,t.name as teacher_name 
FROM courses c
INNER JOIN teachers t
ON c.teacher_id=t.id
WHERE t.name='Eastern Heretic';

3. 外连接 OUTER JOIN

3.1 左外连接 LEFT JOIN
现在学校想要统计所有的在校教师姓名及其所教课程的名称

SELECT c.name AS course_name, t.name AS teacher_name
FROM teachers t
    LEFT JOIN courses c ON c.teacher_id = t.id;

3.2 右外连接 RIGHT JOIN
现在学校想要统计所有的在校教师姓名、教师邮箱及其所教课程的名称

SELECT c.name AS course_name, t.name AS teacher_name，t.email AS teacher_email
FROM courses c
    RIGHT JOIN teachers t ON c.teacher_id = t.id;

3.3 全外连接 FULL (OUTER) JOIN
假如现在正是新学期排课阶段，现在学校想要统计所有的在校教师年龄及其所教课程的名称

SELECT c.name AS course_name, t.age AS teacher_age
FROM courses c
	LEFT JOIN teachers t ON c.teacher_id = t.id
UNION
SELECT c.name AS course_name, t.age AS teacher_age
FROM courses c
	RIGHT JOIN teachers t ON c.teacher_id = t.id;

请编写 SQL 语句，将教师表 teachers 和课程表 courses 进行左连接，查询来自中国（讲师国籍 country ='CN' ）的教师名称以及所教课程名称，结果列名请分别以课程名称 course_name ，教师名称 teacher_name 显示。

SELECT c.name AS course_name ,t.name AS teacher_name 
FROM teachers t
LEFT JOIN courses c
ON t.id=c.teacher_id
WHERE t.country ='CN'

请编写 SQL 语句，将课程表 courses 和教师表 teachers 进行全外连接，查询所有课程名称以及与其相互对应的教师名称和教师国籍，结果列名请分别以课程名称 course_name 、教师名称 teacher_name 、教师国籍 teacher_country 显示。

SELECT c.name AS course_name ,t.name AS teacher_name ,t.country AS teacher_country
FROM courses c
LEFT JOIN teachers t ON t.id=c.teacher_id
UNION
SELECT c.name AS course_name ,t.name AS teacher_name ,t.country AS teacher_country
FROM courses c
RIGHT JOIN teachers t ON t.id=c.teacher_id;

3.4 交叉连接

SELECT * FROM Teacher , Course
WHERE Teacher.tid=Course.tid;

速度慢用的少