SQL基础复习04--数据查询SQL语句（多表查询）
参考教材《数据库系统：原理、设计与编程（MOOC版）》，陆鑫 张凤荔 陈安龙
3.4 数据查询SQL语句
SELECT [ALL/DISTINCT] <⽬标列>[,<⽬标列>...][INTO <新表>]
FROM <表名>[,<表名>...][WHERE <条件表达式>]
[GROUP BY <列名> [HAVING <条件表达式>] ORDER BY <列名> [ASC/DESC]];
SELECT语句由多种字句组成：
1. SELECT⼦句，⽤来指明从数据库表中需要查询的⽬标列。ALL是默认操作，获取所有满⾜条件的数据⾏；DISTINCT⽤来去掉结果集中的重复数据⾏；<⽬标列>为被查询表的指定列名，可以有多个。2. INTO⼦句，⽤来将被查询的结果集数据插⼊新表。
3. FROM⼦句，⽤来指定被查询的数据来⾃哪个表或哪些表。多表⽤逗号分隔。
4. WHERE⼦句，⽤来给出查询的检索条件，多个条件之间可以⽤AND、OR进⾏连接。
5. GROUP BY⼦句，⽤来对查询结果进⾏分组，并进⾏分组统计等处理，在分组中，还可以使⽤HAVING关键词定义分组条件。6. ORDER BY⼦句，⽤来对查询结果集进⾏排序。ASC当然是升序，DESC是降序。默认为ASC。
从SELECT语句的操作结果看，<⽬标列>实现对关系表的投影操作，WHERE <条件表达式>实现对关系表的元组选择操作。
3.4.9 使⽤⼦查询处理多表
可以在SELECT查询语句中使⽤⼦查询⽅式实现多表关联查询。
参考书中并未给出创建表格的语句，也未给出数据库⽂件，但却直接执⾏了多表查询。我根据⼀些查询的结果模糊的推断出了表的结构，并且创建了表。代码放在⽂章最后的附录中。
例3-36
关联教师表和学院表，检索出“计算机学院”的教师名单，采⽤⼦查询⽅法：
SELECT TeacherID, TeacherName, TeacherTitleFROM Teacher
WHERE CollegeID IN (SELECT CollegeID FROM College
WHERE CollegeName='计算机学院')ORDER BY TeacherID;GO
以上语句在处理多表查询时，仅仅在SELECT语句的WHERE⼦句中嵌套了⼀层SELECT⼦查询语句。⼦查询还可以嵌套多层SELECT⼦查询语句。但实际应⽤中，受限于DBMS处理SQL语句的性能，不宜嵌套过多⼦查询。
⼦查询只有在最终输出的信息完全来⾃⼀个表的情况下才有⽤，如果输出的信息来字多个表，应当使⽤连接查询。
3.4.10 使⽤连接查询多表
连接查询的基本思想是将关联表的主键值于外键值进⾏匹配对⽐，从中检索出符合条件的关联表信息。例3-36-2
同例3-36，但采⽤连接查询⽅式处理：
SELECT TeacherID, TeacherName, TeacherTitleFROM Teacher, College
WHERE Teacher.CollegeID=College.CollegeID AND College.CollegeName='计算机学院'ORDER BY TeacherID;
GO
连接查询还有⼀个优势，就是输出的结果可以来字多个表，⽽不是跟⼦查询⼀样只能来字⼀个表。
例3-37
查询各个学院教师的⼈数信息。该操作需要关联教师信息表和学员信息表，查询学院名称、教师⼈数，输出按名称降序排列：
SELECT College.CollegeName AS 学院名称, COUNT(Teacher.TeacherID) AS 教师⼈数FROM Teacher, College
WHERE Teacher.CollegeID=College.CollegeIDGROUP BY College.CollegeName
ORDER BY College.CollegeName DESC;GO
由上⾯代码可以发现，GROUP BY和ORDER BY的参数只能是SELECT后⾯跟着的参数，也就是只能是查询结果中的列。这⾥想⼀下也是可以想通的。同时分组列名和排序列名需要⼀致。
为了在多表连接查询中简化列名的表名限定，可以(在FROM⼦句中)使⽤AS关键词给表名赋予⼀个简单名称。例3-38
查询各个学院的教师的信息。关联Teacher表和College表，查询CollegeName、TeacherID、TeacherName、TeacherGender、TeacherTitle。按学院名称、编号分别排序输出：
SELECT B.CollegeName AS 学院名称, A.TeacherID AS 编号, A.TeacherName AS 姓名, A.TeacherGender AS 性别, A.TeacherTitle AS 职称FROM Teacher AS A, College AS BWHERE A.CollegeID=B.CollegeID
ORDER BY B.CollegeName, A.TeacherID;GO
3.4.11 SQL JOIN...ON连接
实现多表关联查询还可以⽤JOIN...ON关键字的格式。两表关联查询的JOIN...ON连接语句格式：
SELECT <⽬标列>[,<⽬标列>...]
FROM <表名1> JOIN <表名2> ON <连接条件>;
例3-39
使⽤JOIN...ON实现例3-38：
SELECT B.CollegeName AS 学院名称, A.TeacherID AS 编号, A.TeacherName AS 姓名, A.TeacherGender AS 性别, A.TeacherTitle AS 职称FROM Teacher AS A JOIN College AS BON A.CollegeID=B.CollegeID
ORDER BY B.CollegeName, A.TeacherID;GO
结果与例3-38是相同的。
使⽤JOIN...ON关联查询语句，还可以实现两个以上的表关联查询。其中3表关联查询的JOIN...ON连接语句格式如下：
SELECT <⽬标列>[,<⽬标列>...]
FROM <表名1> JOIN <表名2> ON <连接条件1> JOIN <表名3> ON <连接条件2>;
但我在⼀些博客和微信公众号⽂章中曾经看到过有朋友测试发现，MySQL使⽤JOIN进⾏3表及3表以上的连接时，会有⼀些问题，但Oracle数据库就可以。这⾥需要引起注意。（我⾃⼰测试的时候，mysql 8点⼏的版本，4表连接也是没有问题的，可能出问题的是⽼
版本。）
此处的例题⽤到的多个数据库中的数据，根据书上的内容难以推断。希望以后出书的⼈可以把这些省略去但⼜⾮常重要的东西加上，不要省掉。因为缺少数据，此处的例题不再做了，只把书上的例题代码敲上，⽅便查阅。
例3-40
查询课表信息，关联Teacher、Course、Plan、College四个表，查询课程名称、教师姓名、上课地点、上课时间、开课学院等信息，按开课计划编号排序输出：
SELECT C.CourseName AS 课程名称, T.TeacherName AS 教师姓名, P.CourseRoom AS 地点, P.CourseTime AS 时间, S.CollegeName AS 开课学院FROM Course AS C JOIN Plan AS P ON C.CourseID=P.CourseID JOIN Teacher AS T ON P.TeacherID=T.TeacherIDJOIN College AS S ON S.CollegeID=T.CollegeIDORDER BY P.CoursePlanID;GO
上⾯的连接查询使⽤Course的主键与Plan的外键进⾏匹配关联，同时也使⽤Teacher的主键与Plan的外键进⾏匹配关联，还使⽤College的主键与Teacher的外键进⾏匹配关联，这样实现了4表关联数据查询。
1. 内连接
以上的JOIN...ON连接查询中，只有关联表相关字段的列值满⾜等值连接条件时，才从这些关联表中提取数据组合成新的结果集，这样的连接被称为JOIN...ON内连接。例3-41
查询所有课程的学⽣选课情况，包括开设课程名称、选课学⽣⼈数。需要关联Course、Plan、Register。使⽤内连接查询：
SELECT C.CourseName AS 课程名称, T.TeacherName AS 教师, COUNT(R.CoursePlanID) AS 选课⼈数FROM Course AS C JOIN Plan AS PON C.CourseID=P.CourseID
JOIN Teacher AS T ON P.TeacherID=T.TeacherID
JOIN Register AS R ON P.CoursePlanID=R.CoursePlanIDGROUP BY C.CourseName, T.TeacherName;GO
上⾯的内连接查询中，只能找出有学⽣注册的课程名称和选课⼈数，不能找出没有学⽣注册的课程信息。2. 外连接
有时候我们也希望输出那些不满⾜连接条件的元组数据。可以使⽤JOIN...ON外连接⽅式实现。有3中形式：
LEFT JOIN: 左外连接，即使右表中没有匹配，也从左表返回所有的⾏。RIGHT JOIN: 右外连接，即使左表中没有匹配，也从右表返回所有的⾏。FULL JOIN: 全外连接，只要其中⼀个表中存在匹配，就返回⾏。
例3-42
查询所有开设课程的学⽣选课情况，包括开设课程名称、选课学⽣⼈数。需要关联Course、Plan、Register。希望不但能找出有学⽣注册的课程信息，也能找出没有学⽣注册的课程信息。使⽤左外连接查询：
SELECT C.CourseName AS 课程名称, T.TeacherName AS 教师, COUNT(R.CoursePlanID) AS 选课⼈数FROM Course AS C JOIN Plan AS PON C.CourseID=P.CourseID
JOIN Teacher AS T ON P.TeacherID=T.TeacherID
LEFT JOIN Register AS R ON P.CoursePlanID=R.CoursePlanIDGROUP BY C.CourseName, T.TeacherName;GO
⽆法⾃⼰实现例题，对概念的理解⼤打折扣，但没有办法。因为缺少数据，以后的⼏章可能都⽆法实现例题。所以我计划在学完所有SQL语句之后，⾃⼰搞⼀个数据库的项⽬，把学过去的所有知识实现⼀遍，以深⼊理解各类SQL语句的功能与使⽤。附录代码：
CREATE TABLE College
(CollegeID int IDENTITY(1,1) PRIMARY KEY, CollegeName varchar(40) NOT NULL);GO
INSERT INTO College VALUES('软件学院');INSERT INTO College VALUES('计算机学院');SELECT * FROM College;GO
CREATE TABLE Teacher
(TeacherID char(4) NOT NULL,
TeacherName varchar(10) NOT NULL,
TeacherTitle varchar(6),
TeacherGender char(2) CHECK(TeacherGender IN('男', '⼥')), CollegeID int NOT NULL,
CONSTRAINT Teacher_PK PRIMARY KEY(TeacherID),
CONSTRAINT CollegeID_FK FOREIGN KEY(CollegeID) REFERENCES College(CollegeID) ON DELETE CASCADE);GO
ALTER TABLE Teacher
ADD CONSTRAINT CK_Teacher_Teacher_04E4BC85 CHECK(TeacherTitle IN('教授', '副教授', '讲师'));INSERT INTO Teacher VALUES('T000', '张键', '副教授', '男', 2);UPDATE Teacher
SET TeacherID='T001'
WHERE TeacherName='张键';GO
SELECT * FROM Teacher;GO
INSERT INTO Teacher VALUES('T002', '万佐', '教授', '男', 2);INSERT INTO Teacher VALUES('T003', '青迎', '副教授', '⼥', 2);INSERT INTO Teacher VALUES('T004', '马敬', '教授', '男', 2);INSERT INTO Teacher VALUES('T005', '赵微', '讲师', '⼥', 2);INSERT INTO Teacher VALUES('T006', '汪明', '副教授', '男', 1);INSERT INTO Teacher VALUES('T007', '傅超', '副教授', '男', 1);INSERT INTO Teacher VALUES('T008', '李⼒', '教授', '男', 1);INSERT INTO Teacher VALUES('T009', '杨阳', '副教授', '⼥', 1);INSERT INTO Teacher VALUES('T010', '楚青', '副教授', '⼥', 1);GO
