大多数人,都低估了编程学习的难度,而高估了自己的学习能力和毅力。
当前系列: SQL 修改讲义
我们继续学习单表的SELECT常用操作。


DISTINCT

我们还可以在列名前面添加关键字:DISTINCT:以返回“唯一”行,即相同的行会被合并成一行显示。

或者你可以理解为:相同的行只显示一行。

比较有无DISTINCT的查询结果:

SELECT Age FROM Student;
SELECT DISTINCT Age FROM Student;

如果SELECT的是多列,那就需要所有列的值都完全相同,才会被认为是“相同的行”,才会被“合并”。比如:

SELECT Age,Score FROM Student;
SELECT DISTINCT Age,Score FROM Student;

上图中(左)第2行和第5行,Age和Score都完全相同,所以会被合并成1行;第4行虽然Age=19,但Score为NULL,和第2行第5行的Score=89不同,所以不能被合并。

注意:NULL值和NULL值相比,会被认为是相同的。


ORDER BY

可以将数据按某种特定顺序,有序的显示出来。

ORDER BY 后面直接跟列名,比如:

SELECT * FROM Student ORDER BY Score;
就是按Score列的值进行排序,默认是按升序(从小到大,ASC)排列。如果要降序(从大到小)排列,需要再添加一个关键字:DESC
SELECT * FROM Student ORDER BY Score DESC; -- 按成绩从大到小排列

除了数值,其他各种数据类型,比如日期、文本,都可以进行排序。(但是,TEXT和image等类型除外

ORDER后可以跟多个列,比如:

SELECT Id, Age, Score FROM Student ORDER BY Age ASC, Score DESC
这就是要求:
  1. 首先按年龄(Age)从小到大(ASC)排序,
  2. 然后相同年龄的行,再按照成绩(Score)从大到小(DESC)排序,

所以,其结果就是:

  • Age的排序是:16<18<19;
  • Score=19时的排序是:89>81

注意:使用ORDER进行排序时,NULL被认为是“无限小”


GROUP BY

SELECT还可以进行分组统计,比如,我要查询Student表中不同年龄(Age)的学生,各有多少人,怎么办?这就需要使用:

SELECT 
Age, 
COUNT(*) -- COUNT()是统计个数的“聚合”函数
FROM Student 
GROUP BY Age -- 按Age分组

运行上述SQL的结果就是:

也就是说:

  • 16岁的学生,1个;
  • 18岁的学生,1个;
  • 19岁的学生,3个。
GROUP BY也可以跟多个列名,多个列名之间用逗号(,)隔开,比如:
SELECT Age,Score, COUNT(Score) FROM Student GROUP BY Age, Score

意思是按Age和Score进行分组,Age和Score都相同的为一组。

先打预防针,^_^,同学们使用GROUP BY的时候容易犯的两个错误:

  1. 和ORDER BY混淆
  2. 在SELECT后面接的列名未出现在GROUP BY之后,比如:
    SELECT Age, -- Age是可以的 
    Score, -- Score不能在这里,因为GROUP BY后面没有跟Score 
    COUNT(*) -- 但在聚合函数中可以使用任何列 
    FROM Student 
    GROUP BY Age

注意:COUNT()被称之为:

聚合函数

GROUP BY使用的聚合函数,除了COUNT(),还有:

  • MAX():取每组当中最大的值。
  • MIN():取每组当中最小的值。
  • SUM():取每组的和
  • AVG():取每组的平均值。

以上聚合函数,圆括号中都要指明列名,比如:

SELECT Age, SUM(Score) FROM Student GROUP BY Age;

#常见面试题#:COUNT(*) / COUNT(Score) / COUNT(1)的区别?

前提知识点:NULL值在聚合函数中自动忽略,不被纳入统计(演示)

  • COUNT(*):只要有一列不是NULL值就算一个
  • COUNT(Score):如果值不是NULL就算一个
  • COUNT(1):哪怕所有列值都是NULL也要算一个

聚合函数中的NULL值会被略过(不纳入计算)。

另:聚合函数还可以独立(没有GROUP BY)使用

SELECT MAX(Score) FROM Student 

HAVING

我们还可以使用HAVING子句对分组后的结果进行过滤:

SELECT Age, AVG(Score) FROM Student 
GROUP BY Age 
HAVING AVG(Score) > 85 

演示:比较没有HAVING过滤,和有HAVING的区别。

可以同时使用HAVING和WHERE,但要注意HAVING和WHERE的区别:

  • WHERE必须在GROUP之前,HAVING必须在GROUP之后
  • 而WHERE是在GROUP之前就过滤,HAVING在GROUP的结果中过滤,


总结:SELECT执行

综合上面全部知识点,生成以下SQL语句:

SELECT DISTINCT Age, MAX(Score)
FROM Student
WHERE Enroll < '2022-12-1'
GROUP BY Age
HAVING MAX(Score)>60
ORDER BY Age DESC;

@想一想@:数据库执行的步骤是怎样的?

查看执行计划,可以看出单表查询的顺序是:

  1. FROM
  2. WHERE
  3. ORDER BY
  4. GROUP BY
  5. HAVING
  6. SELECT

#试一试#:

  • 注释掉ORDER BY子句,对比执行计划,所以你明白了什么?
  • 为MAX(Score)取一个别名,如下所示:
    SELECT DISTINCT Age, MAX(Score) AS MaxScore
    FROM Student
    GROUP BY Age
    HAVING MaxScore>60;
    报错:Invalid column name 'MaxScore'.
    @想一想@:为什么?HAVING(以及WHERE等)先于SELECT执行,在他们执行的时候,产生别名的SELECT子句还根本就没有执行


分页

演示:文章分页

@想一想@:分页的本质是什么?

以每页10条数据为例,第3页就是“跳过”20页之后再取10页。

注意:不能直接用Id,BETWEEN 20 AND 30,因为Id不一定是连续的!

曾经很复杂,现在很简单。在:

SELECT * FROM Student 
ORDER BY Score

之后添加:


T-SQL mysql
跳过1行取2行:
OFFSET 1 ROWS
FETCH NEXT 2 ROWS ONLY;
limit 1,2
只取前2行(简写)
SELECT TOP 2 * FROM Student
limit 2
跳过第1行之后所有行
OFFSET 1 ROWS;


CASE表达式

准备表和数据:

CREATE TABLE Scores
(
	SName VARCHAR(10),
	Major VARCHAR(10),
	Score FLOAT
);
INSERT Scores VALUES('atai','C#',  98);
INSERT Scores VALUES('atai','SQL',  89);
INSERT Scores VALUES('atai','JavaScript',  76);
INSERT Scores VALUES('xj','C#',  87);
INSERT Scores VALUES('xj','SQL',  95);
INSERT Scores VALUES('xj','JavaScript', 58);

优秀/及格/不及格

进行查询,添加一列,按以下规则显示:

  • 如果score>=80,该列结果为'Excellent'
  • 如果score>=60,该列结果为'Pass'
  • 否则,该列结果为'Failed'

这就要使用到:CASE..WHEN...THEN...ELSE…:

SELECT SName, Major, Score,
	CASE -- CASE的启动
		WHEN Score>=80 THEN 'Excellent'
		WHEN Score>=60 THEN 'Pass'-- 注意排序!
		ELSE 'Failed '-- 之前的条件均不能满足
	END AS Grade -- CASE的结束
FROM Scores;

其结果为:


这段SQL语句关键的就是︰

  • CASE和END:CASE为始,END为终,界定这段逻辑的范围
  • WHEN...THEN:WHEN后面跟的是一个条件表达式,THEN后面跟的就是之前WHEN条件满足时的取值
  • ELSE:当之前所有WHEN条件都不满足时的取值

使用该语法时还要注意:正确控制逻辑判断顺序,如果满足了第一个WHEN的条件,就不会继续下—条的比较。所以……

CASE语句的运算结果类似于一个“列值”,所以并不仅限于SELECT子句,而是几乎可以用于任何操作,比如UPDATE。

我们可以在Student上先添加一个列:

ALTER TABLE Scores
ADD Grade VARCHAR(25);
然后使用UPDATE语句进行填充:
UPDATE Scores
SET Grade = CASE
                WHEN Score>=80 THEN 'Excellent'
                WHEN Score>=68 THEN 'Pass'
                ELSE 'Failed '
            END;
如果只是进行简单的“等于”比较,就可以使用简写形式。比如,将IsFemale中的1转换成女,0转换成男,就可以:
CASE IsFemale
WHEN 1 THEN '女’ELSE '男'
END

最后,我们来学习一个经典的

#常见笔试题目#

行列转换

需求就是将下图左表转换成右表格式。

首先,要在SName后面添加三列:

SELECT SName,
	CASE Major WHEN 'C#' THEN Score ELSE 0 END AS 'C#',
	CASE Major WHEN 'SQL' THEN Score ELSE 0 END AS 'SQL',
	CASE Major WHEN 'Javascript' THEN Score ELSE 0 END AS 'Javascript' 
FROM Scores;

然后,使用GROUP和MAX进行“压缩”。最终代码如下:
SELECT SName,
	MAX(CASE Major WHEN 'C#' THEN Score ELSE 0 END) AS 'C#',
	MAX(CASE Major WHEN 'SQL' THEN Score ELSE 0 END) AS 'SQL',
	MAX(CASE Major WHEN 'Javascript' THEN Score ELSE 0 END) AS 'Javascript' 
FROM Scores
GROUP BY SName;


作业

  1. 为User表添加邀请人(InvitedBy)列,用至少两种方式,显示出所有不重复的邀请人
  2. 在Problem中插入作者(Author),以便能完成以下操作:
    1. 所有求助,先按作者“分组”,然后在“分组”中按悬赏(Reward)从大到小排序
    2. 查找并统计出每个作者的:求助数量、悬赏总金额和平均值(精确到小数点后两位
    3. 找出平均悬赏值少于10的作者并按平均值从小到大排序
    4. 查找出Author为“飞哥”的、Reward最多的3条求助
  3. 假设每页10条数据,分别按发布时间和悬赏数量排序,获得第3页的求助
  4. 现有消息统计表MessageStatus(Name,Kind,Amount),记录某某人(Name)某类(Kind)消息共有多少(Amount)条,使用SELECT语句将其按“行列转换”后显示。

学习笔记
源栈学历
大多数人,都低估了编程学习的难度,而高估了自己的学习能力和毅力。

作业

  1. SELECT
    1. 为User表添加邀请人(InvitedBy)列,用至少两种方式,显示出所有不重复的邀请人
    2. 在Problem中插入作者(Author),以便能完成以下操作:
      1. 所有求助,先按作者“分组”,然后在“分组”中按悬赏(Reward)从大到小排序
      2. 查找并统计出每个作者的:求助数量、悬赏总金额和平均值(精确到小数点后两位
      3. 找出平均悬赏值少于10的作者并按平均值从小到大排序
      4. 查找出Author为“飞哥”的、Reward最多的3条求助
    3. 假设每页10条数据,分别按发布时间和悬赏数量排序,获得第3页的求助
    4. 现有消息统计表MessageStatus(Name,Kind,Amount),记录某某人(Name)某类(Kind)消息共有多少(Amount)条,使用SELECT语句将其按“行列转换”后显示。
觉得很 ,不要忘记分享哟!

任何问题,都可以直接加 QQ群:273534701

在当前系列 SQL 中继续学习:

多快好省!前端后端,线上线下,名师精讲

  • 先学习,后付费;
  • 不满意,不要钱。
  • 编程培训班,我就选源栈

更多了解 加:

QQ群:273534701

答疑解惑,远程debug……

B站 源栈-小九 的直播间

写代码要保持微笑 (๑•̀ㅂ•́)و✧

公众号:源栈一起帮

二维码