SQL单表查询

1、SQL单表查询语句的基本结构：

select A1,A2,...,An from R where p

A1,A2,...,An：投影字段

R：目标表

P：筛选条件

2、问题背景

下面的讲述基于以下背景：

假定存在以下3张表，且表之间具有如下的参照关系。

3、对列进行筛选

（1）查询所有列

【例1】查询全体学生的所有信息。

select Sno,Sname,Ssex,Sage,Sdept from Student

或

select * from Student

说明：

1、select之后跟的是将要投影的字段；from之后跟的是将要访问的目标表。必须要保证投影字段均存在于目标表中，否则会引发错误。

2、在SQL语句中，*是通配符，代表所有；因此例1中的两种写法是等价的。第1种写法更明确，第2种写法更简洁。

（2）查询指定列

【例2】查询全体学生的学号及姓名。

select Sno,Sname from Student

说明：如果将要投影的列并不是目标表中的所有列，则应该在select之后逐一书写出将要投影的每一列的列名，用逗号分隔。

注意：本课程将表中的字段和列等同。

（3）更名运算

【例3】查询每一位学生的学号和出生年份。

分析：目标表Student中并没有出生年份字段，所以无法直接投影。我们可以观察到目标表中是有年龄字段的，因此可以换一下思路：用当前年份减去年龄，不刚好是学生的出生年份吗？ok，这样我们就可以写出下面的SQL查询语句：

select Sno,2016-Sage from Student

在SQLServer中，大概可以看到以下结果：

知识点1：在投影时，列与列之间、列与常量之间是可以做数学运算的。当然，前提是这些列的值能够支持数学运算。

但是从结果中可以看出由于出生年份是一个新计算出来的列，在原始目标表中并不存在，所以它并无列名。这对你有影响吗？如果没有，那就不用做什么了；如果有，那么你可能会有两种想法：

1、给这一列添加一个说明信息。比如：

select SNO,’Year of Birth’,2015-Sage from Student

它的执行效果如下：
         

即在“出生年份”列之前加了一个常量列“Year of Birth”，来说明后面这一列的含义。

知识点2：在投影时，可以通过书写常量列来强制向查询结果的相应位置插入一列常量。

2、让这个新列像其它列一样，都有一个列名，来看下面：

select  SNO,2016-Sage as birthday  from Student

或

select  SNO,2016-Sage  birthday  from Student

知识点3：SQL语句中，as和空格都可以作为更名运算符（也叫别名运算符）；但更建议使用明确的as运算符。

扩展阅读：SQL内置函数

上面的例子是通过当前年份（2016）来减去年龄，从而得到出生年份的。这里其实我们给自己预留了一个很大的bug！why？因为这条语句如果放到明天、后年...来执行，那么算出来的值都是错的。因此，随着SQL执行时间的变化，我们需要相应地修改当前年份。

考虑到SQLServer自己是有一个系统时间的，所以如果我们能用SQLServers系统时间中的年份来取代2016，那么一切都完美了！不管你什么时候执行这条语句，我都不需要做任何变化。OK，看上去很美！但现在的问题是怎么取得SQLServer自带的系统时间呢？以及如何从系统时间中取得年份呢？让我们来讨论一下这个问题。

其实这个话题涉及的就是SQL的内置函数。具体的知识，大家可以看一下第7节的微课视频《SQL内置函数》。这里我们只会直接给出两个会用到的内置函数：

getDate() 获取SQLServer的系统当前时间；

yaer(日期参数) 取得日期参数中的年份；

OK,好了，有了这两个函数，我们就可以重写上面的SQL：

select Sno,year(getDate())-Sage as birthday from Student

大功告成！这条语句在任何时间执行都可以得到我们想要的结果。

（3）去掉重复值

如果查询结果中出现了重复数据，这里的重复指的是构成行的各个字段值均重复，则可以采用distinct关键字来消除重复数据。

【例4】查询所有选了课程的学生的学号，并消除重复的学号。

select  distinct Sno  from SC

4、对行进行筛选

（1）where子句与条件运算符

select语句后可以通过加上where子句来实现对行的筛选。

where子句中书写的是对列值进行测试的各种条件。因此首先来了解一下大家都已经很熟悉的条件谓词。

（2）基于比较符的查询

【例5】查询计算机系所有学生的学号和姓名。

select  Sno,Sname  from Student where Sdept=‘cs’

提示：在SQL语句中，字符串和日期时间值要用单引号包围，数值类型则不需要。

扩展阅读：全表扫描

例5中的SQL语句在数据库中被执行时，其实是以全表扫描的方式来完成的。什么是全表扫描呢？就是对目标表Student，从第一行记录依次向下搜索；比如首先找到第一行记录，测试该行的Sdept列的列值是否等于“cs”？如果不等，则略过此行，继续向下搜索；如果相等，则说明该行数据正是要找的结果，因此将该行放入查询结果（当然构成该行的列不一定是原表中的所有列，而应该是你在SQL语句中所投影的列。），然后继续向下搜索。如此规则一直搜索到最后一行，则完成对Student全表数据的扫描，这就是所谓的全表扫描。

从这样的描述中大家可以看出其实从一张表中找出我们所需要的数据并不是一件简单的事情，虽然你只是写了一条简单的select语句！尤其是当表中的数据量庞大的时候，这项工作更显不易！它需要耗费更多的资源和时间去完成。

（3）范围查询

如果你需要测试某列值是否在一个范围以内，或是否不在某一个范围以内，那么between...and...就是一个绝佳的方法。

【例6】查询年龄在19~21岁之间的学生的姓名、所在系和性别。

select  Sname,Sdept,Ssex from Student where Sage between 19 and 21

题外话：

例6的SQL语句其实可以写成另一种形式：

select  Sname,Sdept,Ssex from Student where Sage<=21 and Sage>=19

两条语句是完全等价的。所以在编写SQL时，很多时候，结果并不唯一。各自给出答案的区别更多地不仅仅在于正确与否，而在于SQL执行的效率高低。

另外一点就是：not between ... and ...，这表示测试列值是否不在给定的范围以内。关于这点，就不再举例了。

（3）集合查询

如果你期望测试某列值是否在一个给定的集合以内，那么in谓词就非常有用了！

【例7】查询计算机系、数学系和信息系的学生姓名和年龄。

select Sname,Ssex from Student where Sdept in (‘CS’,’MA’,’IS’)

等价于：

select  Sname,Ssex from Student where Sdep=‘CS’ or Sdept=‘MA’ or Sdept=‘IS’

题外话：其实这里举的例子并不能完全反映出in谓词的真实威力！等到我们了解了嵌套子查询以后，你就会发现in谓词和子查询的结合是如此的犀利！：）

（4）字符匹配

有时候，我们想查找一些数据，但是记忆或对这些数据的了解却很模糊，只知道一些零星的碎片信息。比如我要找一位同学，但是我只知道这位同学姓张，名字中有“国”这个字，也就是我们常说的“张什么国”。如果你面对的是这种需求，那么用于字符匹配的模糊查询就可以派上用场了。

【例8】查询所有姓张的，第3个字为“国”的学生学号、姓名和性别。

select Sno,Sname,Ssex from Student where Sname like ‘张_国%’

知识点4：like

[ not ] like ‘<匹配串>’ [ESCAPE ‘<换码字符>’]

<匹配串>可以包含如下通配符：

◇  %：匹配任意长度的字符串(长度可以为0)

◇  _ ：匹配任意单个字符

◇  [  ] ：匹配[]中的任意一个字符

◇  [^]：不匹配[]中的任意一个字符

这里特别说明一下%的作用：%可以匹配任意类型、任意长度的字符串。举例说明：

%a 匹配以a结尾的字符串；a前面可以有任意多个字符，也可以1个也没有。

a% 匹配以a开头的字符串；a后面可以有任意多个字符，也可以1个也没有。

%a% 匹配含有a的字符串；a前面和后面可以有任意多个字符，也可以1个也没有。

这样，大家应该明白%的作用了。接下来，我们继续举例：

【例9】查询所有课程名中含有‘数’的课程的学分。

select Credit from Course where Cname like ‘%数%’

【例10】查询所有课程名中含有“数“或“理”的课程的学分。

select Credit from Course where Cname like ‘%[数理]%’

提醒：此题[ ]中虽然有“数”和“理”两个字符，但只需匹配其中任意一个即可。

【例11】查询学号的最后1位中不包含2、4、6的学生。

select  * from Student where Sno like ‘%[^246]’

【例12】查询课程名中含有“DB_”的课程的课程号和学分。

提醒：这道题要注意一下，因为“_”本身是like运算中的一种特殊字符（见上），因此必须要告诉数据库：这里的“_”是一个普通字符，别当成前面那个可以匹配单个字符的特殊字符给处理了。这就叫做字符的转义。

因此，我们写出了下面的SQL语句：

select Cno,Credit from Course where Cname like ‘%DB\_%’ escape ‘\’

解析：escape ‘\’这句话是声明“\”是一个转义字符。对于转义字符后面紧跟的字符，数据库会直接将其当做普通字符处理。这就是我们想要的。

题外话：like模糊查询在实际工作中非常有用，因为我们总是不能给予用户太高的期望；期望他们能记得新闻、博文那长长的标题，或者用户那复杂而冗长的名字...用户只需要给我一点点信息，我就能够将他想要的数据完整地呈现在他面前。这，就是我们要做的工作！

（5）空值查询

如果你需要测试某列的值是否为空，那么你可以使用如下的谓词：

is null  为空     或者    is not null   不为空

【例13】查询没有考试成绩的学生学号及相应的课程号.

select Sno,Cno from SC where Grade is null

说明：空值指的是某列还没有被插入数据，而并不是被插入了空字符串或0.关于这一点，大家一定要注意。比如：null和''是不一样的。null表示空值，''表示空字符串。空字符串也是有值的：空字符。

（6）逻辑运算

如果where后面有多个条件，那么你可以使用and（逻辑与）、or（逻辑或）来加以连接。

形如：

select  字段列表 from 表 where 条件1 and/or 条件2 and/or …… and/or 条件n

【例14】查询计算机系年龄在20岁以下或23岁以上的学生的信息。

select  * from Student where Sdept=‘CS’ and (Sage<20 or Sage>23)

当where后面有多个条件时，执行的基本顺序是“从左往右依次执行”。你可以通过显示地加括号来改变执行的顺序。不要小看执行顺序，有时候可能会影响到SQL语句的执行效率。

SQL结果排序与限制

我们继续沿用前面的问题领域，在此处来讨论一下如果对查询结果进行排序，以及如何限制结果集的记录行数。

1、结果排序

SQL语法：

order by <列名> [asc|desc]

列名：排序的依据字段。列名可以有多个，即可以依据多列的值来进行排序。

asc：默认选项，表示升序。

desc：降序，必须明确写出。

【例1】查询选修了1号课程的学生的学号和成绩，查询结果按成绩降序排列。

select  Sno,Grade from SC where Cno=1 order by Grade desc

如果想按升序排列呢？

select  Sno,Grade from SC where Cno=1 order by Grade asc

或

select  Sno,Grade from SC where Cno=1 order by Grade

asc是默认选项，可以不写；或者说不写就是asc！

【例2】查询成绩表，结果按课程号升序、成绩降序排列。

select  * from SC order by Cno, Grade desc

这道题是按2个字段来排序：第1个字段是升序，第2个字段是降序。这会发生什么呢？我来模拟一些数据：

不知道大家对上面的数据有何感受？我想我们可以很直观地看出来Cno（第1个字段）确实是升序排列的，但是Grade（第2个字段）看上去却未能得偿所愿：不是降序的！！！这是为什么呢？

这是因为多个按多个字段排序时，它的优先顺序是从左往右的，即首先要满足Cno是升序的，然后再看看能不能满足Grade是降序的。这么说的话，那大家可能就觉得疑惑呢？对吧！那Grade放在这里有何价值呢？反正都满足不了！别急，我再来模拟一组数据：

大家注意观察：Cno依然是升序；Grade整体看上去无序，但如果按课程来看，那么它就确实是降序排列的。也就是说我可以通过这种方式来实现在同一门课程内将成绩数据将序排列。那么我们反问一下：能不能让成绩从整体上看都是降序的呢？答案是不能！因为如果成绩全部都是降序了，那么就不能保证课程号Cno是升序的，而我要优先保证的是第一个排序字段Cno。

好了，原因就在于此。

2、限制结果集行数

SQL语法：

top n [ percent ] [ with ties ] 

◇  n : 非负整数，代表将要取的元组个数

◇  top n : 表示取查询结果的前n行数据

◇  top n percent : 表示取查询结果的前n%行数据

◇  with ties : 表示包含排序后的并列结果

这种技巧用于限制返回的查询结果行数。

【例3】查询成绩表，取出成绩最高的前五名学生的学号。

select  top 5 Sno from SC order by Grade desc

说明：如果同时使用distinct来去掉重复数据，那么top在distinct之后，字段列表之前。

思考1：如果咱们要取成绩最低的10名学生的学号呢？

可以倒序排列。top是一定从结果集的头部开始取的。如果你想取后面的数据，则可以通过改变排序的方式来实现。

思考2：如果有和第5名并列的学生，我想一并取出呢？

那么这就要用到with tiles关键字，形如：

select  top 5 with ties Sno from SC order by Grade desc

提醒：如果在top子句中使用了with ties，则要求必须对查询结果进行排序，否则会出现语法错误。

聚合函数

聚合函数的主要作用是用于数据统计。常用的聚合函数包括：

聚合函数的基本用法：

聚合函数 ([ all | distinct] <列名>)

all：使用聚合函数计算时，不去掉重复数据；

distinct：使用聚合函数计算时，去掉重复数据。

说明：聚合函数默认会忽略空值。

sum 

sum(列名) 对指定列的所有值进行求和运算。

说明：参数只接受一个列名，且应该确保这一列的值是可以进行数学求和运算的。

【例1】统计成绩表中1号课程的总分。

select  sum(Grade)  from SC where Cno=1 

注意：这条语句的执行过程：

（1）首先做全表扫描，根据Cno=1去搜索符合条件的记录；

（2）在第1步执行完毕后，对结果中的Grade列进行求和运算，即执行sum(Grade)。

（3）最后输出sum(Grade)的计算结果。

avg

avg(列名) 对指定列的所有值进行均值计算。

【例2】计算选修了2号课程的学生的平均分。

select  avg(Grade) as ‘平均成绩’ from SC where Cno=2 

说明：SQL的执行顺序与例1相似。

max

max(列名) 计算指定列中的最大值。

【例3】查询成绩表中的最高分。

select max(Grade) as ‘最高分’ from SC 

min

min(列名) 计算指定列中的最小值。

【例4】查询4号课程成绩的最低分。

select min(Grade) as '最低分' from Cno=4

说明：SQL的执行顺序与例1相似。

count

count(参数) 计算（统计）查询结果中的记录行数。

说明：count这个函数和上面的其它4个比较起来，就显得非常特别。上面的4个函数均是对列进行计算，但count却是对行进行计算；换句话说，如果那4个是竖着看的话，那么count就是横着看的！既然是对行进行计算，那么这一行中包含几列？这样的问题显然就不再是count所关心的了！count关心的只是这一行是否存在？至于这一行中有几列?对不起，它不管！

因此，count函数的参数虽然也是某一列的列名，但其实不管你写哪一列的列名，都不会影响最后的结果。所以，更多的时候，我们使用count函数会将参数直接写成通配符*。归根到底，count和列是没有关系的。

【例5】统计学生的总人数和选课的学生总人数。

select count(*) as ‘学生总人数’ from Student

select count(distinct Sno) as '选课的学生总人数' from SC

说明：第2条SQL语句加上了distinct来去掉重复学号。因为一个学生可能会选择多门课程学习，所以一个学生的学号可能会重复多次。如果你统计的目标是选课人次，那么可能distinct关键字就是多余，甚至错误的。但这里每个学生，我只想统计1次，所以加上了distinct。

另外要补充的1点是：刚才说过count与列是无关的，但这里遇到了distinct，情况有些变化！因为使用distinct后要去掉重复数据，那么你总得告诉数据库可能重复的是什么东西？所以distinct之后要加上明确的列名。如果加上*（即count(distinct *)），那么就是错误的！希望大家注意一下。