今天我们继续学习单表的SELECT常用操作。
首先,SELECT还可以在原表列的基础上添加新列,比如:
SELECT Age+10 FROM Student
注意这个新列没有名字,不直观不好看:
所以,我们还可以使用AS(可省略)给它指定一个名称:
SELECT Age+10 AS BigAge FROM Student
SELECT Age+10 BigAge FROM Student --省略掉AS
AS也可以在已有列上使用,改变列名的显示(改变过后的列名又被称之为“别名”):
SELECT Id AS Number FROM Student --Number就是Id的别名
SELECT Id Number FROM Student --AS一样是可以省略的
注意:SELECT只是改变的呈现结果,永远不会更改数据本身。
最后,通配符*还可以和指定列一起共同使用:
SELECT Age+10 AS BigAge, * FROM Student
我们还可以在列名前面添加关键字:
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值相比,会被认为是相同的。
我们还可以使用TOP返回“最上面”的若干行:
SELECT TOP 3 Score FROM Student -- 查询最上面的3行
DISTINCT和TOP还可以混合使用:
SELECT DISTINCT TOP 3 Score FROM Student
我们上面使用了TOP,但究竟哪几行会在“最上面”呢?如果我们不指定排序规则的话,SQL Server就不会进行排序,而是直接从数据库中“无序地”取出行数据。
注意:不要以为数据是一行一行地按插入顺序依次放置在数据库中的,然后取的时候也这么一行一行的取。不一定!具体怎么放,根据有无索引等(后面会讲)由数据库自行决定……
如果你希望按某种特定顺序,有序的显示出查询结果,你需要使用:
ORDER BY(排序)子句
ORDER BY 后面直接跟列名,比如:
SELECT * FROM Student ORDER BY Score
就是按Score列的值进行排序,默认是按升序(从小到大,ASC)排列。如果要降序(从大到小)排列,需要再添加一个关键字:DESC
SELECT * FROM Student ORDER BY Score DESC -- 按成绩从大到小排列
除了数值,其他各种数据类型,比如日期、文本,都可以进行排序。
ORDER后可以跟多个列,比如:
SELECT Id, Age, Score FROM Student ORDER BY Age ASC, Score DESC
这就是要求:
- 首先按年龄(Age)从小到大(ASC)排序,
- 然后相同年龄的行,再按照成绩(Score)从大到小(DESC)排序,
所以,其结果就是:
Age的排序是:16<18<19;Score=19时的排序是:89>81
注意:使用ORDER进行排序时,NULL被认为是“无限小”
此外,ORDER和DISTINCT可以配合使用,但是,(SELECT 引导的)选择列,必须出现在(ORDER BY 引导)排序列中,否则会报错。比如:
-- [Name]没有出现在ORDER BY之后
SELECT DISTINCT [Name] FROM Student ORDER BY Age ASC, Score DESC
报错信息如下:
分组(GROUP BY) 和聚合函数
SELECT还可以进行分组统计,比如,我要查询Student表中不同年龄(Age)的学生,各有多少人,怎么办?这就需要使用:
SELECT Age, COUNT(*) AS [Count] -- COUNT()是统计个数的“聚合”函数,给别名Count
FROM Student
GROUP BY Age -- 按Age分组
运行上述SQL的结果就是:
也就是说:16岁的学生,1个;18岁的学生,1个;19岁的学生,3个。
注意聚合函数COUNT的圆括号中使用的是(*),这是指的所有列。我们还可以指定列名,比如COUNT(Score)。和使用*不同,指定列名的时候,如果某行这一列的值为NULL,不会被纳入统计:
SELECT Age,COUNT(Score) AS [Count] FROM Student
GROUP BY Age
其结果为:
你看,16岁的学生,个数只有0个?什么鬼?
我们其实应该这样理解这个结果:16岁的学生,有成绩(Score不为NULL)的只有0个。见下图:
此外,还可以在COUNT函数中加DISTINCT,以统计就有不同成绩的个数,代码如下:
SELECT Age,COUNT(DISTINCT Score) AS [Count] FROM Student
GROUP BY Age
想一想,这次运行的结果应该是多少?
GROUP BY使用的聚合函数,除了COUNT(),还有:
- MAX():取每组当中最大的值。
- MIN():取每组当中最小的值。
- SUM():取每组的和
- AVG():取每组的平均值。
注意NULL值:
- 在SUM()、MAX()和MIN()中:如果组内只有NULL值,结果为NULL值,否则直接忽略NULL值
- COUNT()和AVG()中:NULL值(行)直接被忽略
以上聚合函数,圆括号中都要指明列名,比如:
SELECT Age, SUM(Score) FROM Student GROUP BY Age
GROUP BY也可以跟多个列名,多个列名之间用逗号(,)隔开,比如:
SELECT Age,Score, COUNT(Score) AS [Count] FROM Student
GROUP BY Age, Score
意思是按Age和Score进行分组,Age和Score都相同的为一组。
先打预防针,^_^,同学们使用GROUP BY的时候容易犯的两个错误:
- 和ORDER BY混淆
- 在SELECT后面接的列名未出现在GROUP BY之后,比如:
SELECT Age, -- Age是可以的
Score, -- Score不能在这里,因为GROUP BY后面没有跟Score
COUNT(Score) -- 但在聚合函数中可以使用任何列
FROM Student
GROUP BY Age
我们还可以使用HAVING子句对分组后的结果进行过滤:
SELECT Age, AVG(Score) AS [Average] FROM Student
GROUP BY Age
HAVING AVG(Score) > 85
--HAVING [Average] > 85 -- 使用会报错
比较没有HAVING过滤,和有HAVING和的区别:
可以同时使用HAVING和WHERE,但要注意HAVING和WHERE的区别:
- HAVING是在GROUP的结果中过滤,
- 而WHERE是在GROUP之前就过滤
仔细观察上面的SQL代码,我们是不能在HAVING中使用列的别名的,这是为什么呢?这就需要我们更深入的了解SQL查询的内幕了……
SELECT执行顺序
通过执行计划,我们可以看出单表查询的顺序是:
- FROM
- WHERE
- GROUP BY
- HAVING
- SELECT
- ORDER BY
即:FROM和WHERE子句子句最先执行,在他们执行的时候,产生别名的SELECT子句还根本就没有执行,所以FROM和WHERE根本就没有别名可用。
SELECT表复制
我们还可以利用 SELECT INTO 获得数据并建新表。比如,可以将Student表里面Id>5的Id和Name取出来,新建一张表ShortStudent,SQL语句如下所示:
SELECT Id, [Name]
INTO ShortStudent --比普通SELECT语句多了一个INTO
FROM Student WHERE Id > 5
注意,该语法会复制:属性类型、IDENTITY标记、NOT NULL约束等,但不能复制主外键关系。
如果要在已有表上插入其他表复制过来的数据,我们可以使用 INSERT SELECT 。其SQL语句如下所示:
INSERT ShortStudent
SELECT [Name] From Student -- 不能添加Id,因为Identity
作业:
在Problem中插入不同作者(Author)不同悬赏(Reward)的若干条数据,以便能完成以下操作:
- 查找出Author为“飞哥”的、Reward最多的3条求助
- 所有求助,先按作者“分组”,然后在“分组”中按悬赏从大到小排序
- 查找并统计出每个作者的:求助数量、悬赏总金额和平均值
- 找出平均悬赏值少于10的作者并按平均值从小到大排序
- 以Problem中的数据为基础,使用SELECT INTO,新建一个Author和Reward都没有NULL值的新表:NewProblem (把原Problem里Author或Reward为NULL值的数据删掉)
- 使用INSERT SELECT, 将Problem中Reward为NULL的行再次插入到NewProblem中
单词:
感谢童鞋们的阅读!^_^
我就是:黑律师/包工头/创业狗/老码农……现在还是教书匠的大飞哥。
再次重申这个系列的目标是:
1)通俗易懂。2)实战为主。3)面向就业。
系列内容的完善需要你的反馈!
欢迎点赞和评论,以及加入我们的QQ交流群:326801052。