SQL高级查询语法【关系数据库SQL教程5】

上一章我们学习了基本的SQL查询语法，这一章就来学习一些更高级的查询语法吧。本章用到的数据还是基于上一章创建好的Students和Courses表，大家可以使用以下的SQL语句创建这两张表格：

CREATE TABLE Students (
    StudentID int PRIMARY KEY,
    Name varchar(255),
    Gender varchar(1),
    Age int,
    City varchar(255)
);
INSERT INTO Students VALUES(1, 'David', 'M', 21, 'Shanghai');
INSERT INTO Students VALUES(2, 'Kevin', 'M', 19, 'Beijing');
INSERT INTO Students VALUES(3, 'Emily', 'F', 22, 'Shanghai');
INSERT INTO Students VALUES(4, 'William', 'M', 20, 'New York City');
INSERT INTO Students VALUES(5, 'Alice', 'F', 19, 'Los Angeles');
INSERT INTO Students VALUES(6, 'Frank', 'F', 22, 'Los Angeles');

CREATE TABLE Courses (
    CourseID int PRIMARY KEY,
    CourseName varchar(255)
);
INSERT INTO Courses VALUES(1, 'CS101');
INSERT INTO Courses VALUES(2, 'CS202');
INSERT INTO Courses VALUES(3, 'EE101');

聚合查询

聚合函数

假设我们要统计学生表的记录数量，除了直接使用SELECT * FROM Students得到结果集后，通过手动查询数量这种麻烦的方法外，我们可以使用SQL提供的聚合函数 COUNT，快速获得结果。以查询有多少学生为例，我们以下SQL语句：

SELECT COUNT(*) FROM Students;

虽然COUNT(*)显示的是一个数字，但是查询结果依然是一个二维表，只不过这个表只有一行和一列，列名为COUNT(*)，如下：

COUNT(*)

6

我们也可以给列名设置一个别名，语法类似以下：SELECT column AS alias FROM tables，比如我们要给COUNT(*)设置一个别名叫做StudentsNum，可以使用以下SQL语句：

SELECT COUNT(*) AS StudentsNum FROM Students;

结果可能如下：

StudentsNum

6

使用COUNT(*) 和 COUNT(StudentID)是一样的效果，因为StudentID是主键，每行记录的主键都不同。另外我们在聚合查询中还是能使用WHERE子句的，比如我们要查找年龄大于20岁的学生数量，可使用以下SQL语句：

SELECT COUNT(*) FROM Students WHERE Age > 20;

除了 COUNT 函数外，SQL还提供以下这些常用的聚合函数：

函数	说明
SUM	计算某一列的总和，该列必须为数值类型
AVG	计算某一列的平均数，该列必须为数值类型
MAX	计算某一列的最大值
MIN	计算某一列的最小值

如果我们要查询学生的平均年龄，就能直接使用AVG聚合函数：

SELECT AVG(Age) FROM Students;

分组

SQL还提供了“分组聚合”功能，帮助我们将具有共性字段的记录整合起来。比如我们想要根据城市City对学生进行分组，并需要查询每个城市有多少学生，可以使用GROUP BY语句：

SELECT City, COUNT(*) FROM Students GROUP BY City;

执行此语句后，GROUP BY子句会按照City将学生进行分组，所有City相同的学生会被放到同一个组中，再进行分别计算，所以COUNT(*)的结果对应的就是特定城市的学生数量。其结果类似以下：

City	COUNT(*)
Beijing	1
Los Angeles	2
New York City	1
Shanghai	2

如果我们将Name也放入查询结果，就不合适了，因为来自相同城市的学生可能有很多位，名字就各不相同了，所以以下语句是不合法的：

SELECT City, COUNT(*), Name FROM Students GROUP BY City;

GROUP BY 也能放入多个字段，比如我们想先将学生根据City分组，然后再根据性别分组，可以使用以下语句：

SELECT City, Gender, COUNT(*) FROM Students GROUP BY City, Gender;

多表查询

SELECT 查询除了能从单表中查数据外，也能从多表中查询数据。语法如下：

SELECT * FROM table1, table2;

比如要查询Students表和Courses表的“乘积”，可以使用以下语句：

SELECT * FROM Students, Courses;

假设Students有5列字段和7行记录，Courses有2列字段和3行记录，其结果就是一个拥有21 (3 * 7) 行记录和 7 (5 + 2) 列字段的二维表，既Students表的每一行和Courses表的每一行两两拼在一起。结果集的列数会是两表列数之和，而行数是两表行数之积。

有时候两张表可能拥有相同名字的字段，那么结果则会让人疑惑，我们可以通过使用 AS 取别名来区别字段。比如使用以下语句给StudentID和CourseID改名为StudentId和CourseId：

SELECT Students.StudentID AS StudentId, Courses.CourseID AS CourseId FROM Students, Courses;

给字段取别名的语法类似 column_name AS new_column_name，上面的语句分别给StudentID和CoursesID取了别名StudentId和CourseId，虽然在这个例子中，我们只是将最后的字母D变为小写。但考虑一下另一种情况：假设Students和Courses的主键字段都叫ID，那么这个别名就很有帮助了，我们可以使用以下语句使查询结果更明了：

SELECT Students.ID AS StudentId, Courses.ID AS CourseId FROM Students, Courses;

除了给输出字段取别名外，给表格取别名也很有用的，语法类似table_name AS alias，我们可以将上面的语句写成下面这样：

SELECT S.ID AS StudentId, C.ID AS CourseId FROM Students AS S, Courses AS C;

在多表查询中，我们同样还是能使用WHERE子句。为了帮助大家理解之后的内容，我们需要给Students添加一列新的字段CourseID，代表此学生选择的课程：

StudentID	Name	Gender	Age	City	CourseID
1	David	M	21	Shanghai	2

如果我们如果想要抓取StudentID和其对应的课程名字，可以使用以下语句：

SELECT S.StudentID, C.CourseName FROM Students AS S, Courses AS C WHERE S.CourseID = C.CourseID;

除了WHERE子句，其他子句类似ORDER BY，GROUP BY也都适用于多表查询。

连接(JOIN)查询

连接查询是另一种类型的多表查询，连接查询会对多个表格进行JOIN运算。也就是说，先确定一个主表作为结果集，然后将其他表的记录有选择性地“嵌入”到主表结果集上。

假设我们想要知道每个学生选择的课程名字，除了上面提到的多表查询加WHERE子句，我们还能使用INNER JOIN子句：

SELECT S.StudentID, C.CourseName 
FROM Students AS S 
INNER JOIN Courses AS C ON S.CourseID = C.CourseID;

此语句就能将每个StudentID和其对应的课程名查询出来，要注意INNSER JOIN语句的内在执行过程如下：

1. 确定主表，使用 FROM table_name
2. 紧接着确认连接的表，使用 INNER JOIN table_name
3. 再确定连接条件，使用 ON condition，上面语句的条件就是 S.CourseID = C.CourseID
4. 最后还能加上 ： WHERE、ORDER BY等子句

除了INNER JOIN外，我们还有LEFT JOIN, RIGHT JOIN, 和FULL JOIN。

补充：别名不是必须的，只是为了增加可读性。

RIGHT JOIN, LEFT JOIN

如果我们将语句改为 RIGHT JOIN：

SELECT S.StudentID, C.CourseName
FROM Students AS S 
RIGHT JOIN Courses AS C ON S.CourseID = C.CourseID;

执行上面的语句之后，如果有一节课没有任何学生加入，我们会有一行多余的记录，记录中仅有CourseName，但是StudentID为NULL。

为什么会这样呢？INNER JOIN 会返回同时存在两张表的数据，如果Students有1, 2, 3, 5课号，Courses也有1, 2, 3, 4课号，那么结果就是其相交集1, 2, 3。而 RIGHT JOIN 返回的则是右表存在的记录，如果左表不存在右表中的某几行，那结果中的那几行就会是NULL。

而 LEFT JOIN 则会返回左表中都存在的数，如果给Students加上CourseID=10，即使Courses表中没有ID为10的课程记录，那么LEFT JOIN的结果还是会多一行记录，其对应的CourseName是NULL。（补充：LEFT JOIN 在有些数据库中叫做 LEFT OUT JOIN，同理 RIGHT JOIN 也可能叫做 RIGHT OUT JOIN。）

FULL JOIN

最后一种 JOIN 是 FULL JOIN，结果集会把两张表的所有记录全部选出来，并自动把两张表中不存在的列补充为NULL。

为了帮助大家理解连接查询的逻辑，大家可以参看以下的图示，左边的圆可以理解为左表，右边的圆可以理解为右表。

以上就是SQL语法的基本教程啦，现在大家已经学会了如何使用 SQL 创建表格和记录，并使用高级的 SQL 语句进行复杂的查询，下一章我们就开始学习使用真正的数据库软件 MySQL。

课后练习

请写出SQL语句，找出加入CourseID为1的学生数量和课程名字，要注意的是，我们只寻找那些年纪大于20岁的男学生数量。

SELECT COUNT(*) AS StudentsNum, CourseName 
FROM Students LEFT JOIN Courses ON Students.CourseID = Courses.CourseID 
WHERE Students.Age > 20 AND Students.Gender = 'M'
GROUP BY Students.CourseID;