上一章我们学习了基本的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(*)的结果对应的就是特定城市的学生数量。其结果类似以下:

CityCOUNT(*)
Beijing1
Los Angeles2
New York City1
Shanghai2

如果我们将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,代表此学生选择的课程:

StudentIDNameGenderAgeCityCourseID
1DavidM21Shanghai2

如果我们如果想要抓取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;