mysql 连接查询
在多表查询的时候，一般有两种实现方式：子查询和连接查询。因为子查询在使用上比较灵活，也比较简单，所以我们用的最多就是子查询。但是当数据量很大的时候，由于子查询会多次遍历数据表，所以在效率上是不如可以生成临时表的连接查询的。
因此本篇主要介绍的是连接查询。
连接查询
内连接
语法
select *
from 表1 a
[inner] join 表2 b
on a.id=b.id
描述
组合两个表中的记录，返回关联字段相符的记录，即做交集运算。
左外连接
语法
select *
from 表1 a
right join 表2 b
on a.id=b.id
描述
与左外连接相反，右外连接，左表只会显示符合搜索条件的记录，而右表的记录将会全部表示出来。左表记录不足的地方均为NULL，即在做交集运算的基础上再加上右边独有的部分。
右外连接
语法
select *
from 表1 a
[inner] join 表2 b
on a.id=b.id
描述
组合两个表中的记录，返回关联字段相符的记录，即做交集运算。
全外连接
mysql中没有全外连接的关键字full join on，在oracle中是支持的，但是mysql可以使用union来实现。
语法
select *
from 表1 a
left join 表2 b
on a.id=b.id
UNION
select *
from 表1 a
right join 表2 b
on a.id=b.id
描述
就是两张表的数据做并集。
只得到左表独有的数据
语法
select *
from 表1 a
left join 表2 b
on a.id=b.id
where b.id IS NULL
只得到右表独有的数据
语法
select *
from 表1 a
right join 表2 b
on a.id=b.id
where a.id IS NULL
只得到两表各自独有的内容
语法
select *
from 表1 a
left join 表2 b
on a.id=b.id
where b.id IS NULL
UNION
select *
from 表1 a
right join 表2 b
on a.id=b.id
where a.id IS NULL
案例：子查询+连接查询实现
这个案例来源于我们的数据库实验，题目比较基础，就当练手了。
建表
建表的脚本文件我放在了github上面：
下面是表结构：
查询
1.查询选修了课程的学生的学号、姓名、所选课程号、课程名称以及所取得的成绩。
# 子查询：
SELECT Student.sno AS 学号, Student.sname AS 姓名, SC.cno AS 课程号, Course.cname AS 课程名, SC.grade AS 成绩
FROM Student, SC, Course
WHERE SC.sno=Student.sno AND Course.cno=SC.cno
ORDER BY Student.sno;
# 连接查询：
SELECT st.sno AS 学号, st.sname AS 姓名, c.cno AS 课程号, c.cname AS 课程名, sc.grade AS 成绩
FROM Student st
JOIN SC sc ON (sc.sno=st.sno)
JOIN Course c ON (sc.cno=c.cno)
ORDER BY st.sno;
2.查询每一门课程及其先修课程，查询结果要求显示课程号、课程名和先修课程名。
SELECT cno AS 课程号, cname 课程名, cpno AS 先修课程名
FROM Course;
# 连接查询：
SELECT c.cno AS 课程号, c.cname AS 课程名, c.cpno AS 先修课程名
FROM Course c
LEFT JOIN Course cp
ON (c.cpno = cp.cno);
3.查询和“陈奕迅”在一个班级的学生的信息(即列出所有属性)。
# 子查询：
SELECT *
FROM Student
WHERE class=(SELECT class FROM Student WHERE sname='陈奕迅');
# 连接查询：
SELECT st1.*
FROM Student st1
JOIN Student st2
ON (st2.sname='陈奕迅' AND st1.class=st2.class);
4.查询选修了‘操作系统’课的学生的学号、姓名、性别。
# 子查询：
SELECT Student.sno, Student.sname, Student.ssex
FROM SC, Student
WHERE SC.cno=(SELECT cno FROM Course WHERE cname='操作系统') AND Student.sno=SC.sno;
# 连接查询：
SELECT st.sno, st.sname, st.ssex
FROM Student st
JOIN SC sc
ON (st.sno=sc.sno)
JOIN Course c
ON (sc.cno = c.cno AND c.cname='操作系统');
5.查询没有选修课程的学生的学号、姓名、性别、班级。
# 子查询：
SELECT sno, sname, ssex
FROM Student
WHERE sno NOT IN
(SELECT sno FROM SC);
# 左外连接查询：
SELECT st.sno, st.sname, st.ssex
FROM Student st
LEFT JOIN SC sc
ON (st.sno=sc.sno)
WHERE sc.sno IS NULL;
6.查询所学课程超过该门课的平均成绩的学生的学号。
子查询：
SELECT DISTINCT sno
FROM SC a
WHERE grade > (SELECT AVG(grade) FROM SC b WHERE a.cno=b.cno GROUP BY cno);
连接查询：
SELECT DISTINCT sc1.sno
FROM SC sc1
JOIN (
SELECT cno, AVG(grade) average
FROM SC
GROUP BY cno
) sc2
ON (sc1.cno=sc2.cno)
AND sc1.grade > sc2.average;
7.查询学生“赵薇”选修的所有课程的学分之和。
# 子查询：
SELECT SUM(ccredit)
FROM Course
WHERE cno=ANY(SELECT cno FROM SC WHERE sno = (SELECT sno FROM Student WHERE sname='赵薇'));
# 连接查询：
SELECT SUM(c.ccredit)
FROM Course c
JOIN SC sc
ON (c.cno=sc.cno)
JOIN Student st
ON (st.sno=sc.sno AND st.sname='赵薇');
8.查询同时选修了“计算机基础”和“C语言基础”的学生的班级、姓名、性别。
# 子查询：
SELECT class, sname, ssex
FROM Student
WHERE sno = ANY(SELECT sno FROM SC,Course WHERE SC.cno=Course.cno AND cname="计算机基础" AND sno IN (SELECT sno FROM SC, Course WHERE cname="C语言基础" AND Course.cno=SC.cno));
# 连接查询：
SELECT st.class, st.sname, st.ssex
FROM Student st
JOIN SC sc1
ON (st.sno=sc1.sno)
JOIN SC sc2
ON (st.sno=sc2.sno)
JOIN Course c1
ON (sc1.cno=c1.cno AND c1.cname='计算机基础')
JOIN Course c2
ON (sc2.cno=c2.cno AND c2.cname='C语言基础');
9.查询教师的编号、姓名、上课的课程号、课程名称及班级。
SELECT Teacher.tno, Teacher.tname, Course.cno, Course.cname, TC.class FROM Teacher, TC, Course
WHERE Teacher.tno=TC.tno AND TC.cno=Course.cno;
# 连接查询：
SELECT t.tno, t.tname, tc.cno, c.cname, tc.class
FROM Teacher t
JOIN TC tc
ON (t.tno=tc.tno)
JOIN Course c
ON (tc.cno=c.cno);
10.统计每一位教师上课的数量。
SELECT Teacher.tname AS 教师名, COUNT(TC.cno) AS 课程数量
FROM Teacher,TC
WHERE Teacher.tno=TC.tno
GROUP BY TC.tno;
连接查询：
SELECT t.tname AS 教师名, COUNT(tc.cno) AS 课程数量
FROM Teacher t
JOIN TC tc
ON (t.tno=tc.tno)
GROUP BY t.tno;