标准SQL语句（3） --- 表的查询（1）

声明：本文使用到的数据表请参看本博客的另一篇文章：标准SQL语句（2）--- 表的增删改 

引言

表的数据查询是数据库中最常用的操作。SQL提供select语句，通过查询操作可得到所需的信息。select语句的一般格式为：

select [all | distinct ] [top n [percent] ] [with ties]

<列名> [as  别名1] [ {, <别名> [ as 别名2 ] } ]

[ into 新表名 ]

from <表名1 或 视图名1> [ [as] 表1 别名 ] [ {, <表名2 或 视图名2> [ [as] 表2 别名  ] }  ]

[ where <检索条件> ]

[ group by <列名1> [having <条件表达式>] ]

[ order by <列名2> [ asc | desc ] ]

select 语句的格式还可以写为：

select  [ all | distinct ] [ top n [ percent ] [ with ties ] ]

列名1 [as 别名1]

{ [, 列名2 [as 别名2] ...  ] }

[ into 表新名 ] from 表名1 [ [as] 表1别名 ]

[ inner | right | full | outer ] [ outer ] join

表名2 [ [as] 表2别名 ]

on 条件

1. 简单查询

例1：查询全体学生的学号、姓名和年龄。

select SNo, SN, Age from S

例2：查询学生的全部信息。

select * from S

2.条件查询

2.1 完全匹配查询

当要在表中找出满足某些条件的行时，则需要使用where子句指定查询条件。where子句中，条件通常通过三个部分来描述：

1) 列名；

2) 比较运算符；

3) 列名、常数。

常用的比较运算符如下表所示：

常用的比较运算符运算符含义=, >, <, >=, <=, !=, <>比较大小and, or, not多重条件between and确定范围in确定集合is null空值查询like字符匹配

假设数据库中建有如下表：

2.1.1 比较大小

例1：查询选修课程号为“C1”的学生的学号和成绩。

select SNo,Score from SC where CNo='C1'

2.1.2 多重条件查询

当where子句需要指定一个以上的查询条件时，则需要使用逻辑运算符AND, OR 和 NOT将其连接成复合的逻辑表达式。其优先级别由高到低为：NOT, AND, OR

用户可以使用括号改变优先级。

例： 查询选修C1 或C2 且分数大于等于85分的学生的学号、课程号和成绩。

select SNo, CNo, Score FROM SC where (CNo = 'C1' OR CNo = 'C2') and (Score >= 85 )

2.1.3 确定范围

例1： 查询工资在1000元~1500元之间的教师的教师号、姓名及职称。

select TNo, TN, Pro from T where Sal between 1000 and 1500

等价于

select TNo, TN, Pro from T where Sal>=1000 and Sal <=1500

例2： 查询工资不在1000元~1500元之间的教师的教师号、姓名及职称。

select TNo, TN, Pro from T where Sal not between 1000 and 1500

2.1.4 确定集合

利用“IN” 操作可以查询属性值属于指定集合的元组。

例1：查询选修C1或C2的学生的学号、课程号和成绩。

select SNo, CNo, Score from SC where CNo in ('C1', 'C2')

例2： 查询没有选修C1，也没有选修C2的学生的学号、课程号和成绩。

select SNo, CNo, Score from SC where CNo not in ('C1', 'C2')

等价于

select SNo, CNo, Score from SC where (CNo<>'C1') and (CNo<>'C2') 

2.1.5 空值查询

某个字段没有值称为具有空值( null ) 。通常没有为一个列输入值时，该列的值就是空值。

空值不同于零和空格，它不占用任何存储空间。

例：查询没有考试成绩的学生的学号和相应的课程号。

select  SNo, CNo from SC where Score IS NULL

注意：这里的空值条件Score IS NULL ，不能写成Score = NULL

2.2 部分匹配查询（模糊查询）

当用户不知道或不确定完全匹配的值时，还可以使用like或not like 进行部分匹配查询。like定义的一般格式为：

<属性名> like <字符串常量>

其中，属性名必须是字符型，字符串常量中的字符可以包含通配符，利用这些通配符，可以进行模糊查询，字符串中的通配符及其功能，如下表所示：

字符串可以含有的通配符通配符功能实例%代表0个或多个字符'ab%', ‘ab’后可接任意个字符串_代表一个字符'a_b', 'a'与'b'之间可有一个字符[ ]表示在某一范围的字符[0-9], 0~9之间的字符[^ ]表示不在某一范围的字符[^0-9], 不在0~9之间的字符
例1：查询所有姓张的教师的教师号和姓名。

select TNo, TN from T where TN like '张%'

例2：查询姓名中第二个汉字是‘力’的教师号和姓名。

select TNo, TN from T where TN like '_力%'

3. 常用库函数及统计汇总查询

SQL提供了许多库函数，增强了基本检索能力。常用的库函数如下表所示：

常用的库函数及其功能函数名称功能avg 按列计算平均值sum按列计算值的总和max求一列中最大的值min求一列中最小的值count按列值统计个数
例1：求学号为S1的学生的总分和平均分。

select sum(Score) as TotalScore, avg(Score) as AvgScore from S where SNo = 'S1'

例2：求选修C1号课程的最高分、最低分及之间相差的分数。

select max(Score) as maxScore, min(Score) as minScore,  max(Score) - min(Score) as DiffScore from SC where CNo='C1'

例3：求计算机系学生的总数。

select count(SNo) from S where Dept='计算机'

例4：求学校共有多少个系。

select count( distinct  Dept) as DeptNum from S

注意：加入关键字distinct后表示消去重复行，可计算字段Dept不同的数目。count函数对空值不计算，但对0进行计算。

例5：统计有成绩同学的人数。

select count(Score) from SC

例6：利用特殊函数count(*)求计算机系学生的总数。

select count(*) from S where Dept='计算机'

count(*)用来统计元组的个数(注意：元组不是行，一个元组可以是一行也可能是多行，请看下面的分组查询)，不消除重复行，不允许使用distinct关键字。

4.分组查询 group by ... [having]

group by 子句可以将查询结果按属性列或属性列组合在行方向进行分组，每组在属性列或属性列组合上具有相同的值。

例1：查询每个教师的教师号及其任课的门数。

select TN, count(*) as CourseNum from TC group by TNo

解释：group by 子句按TNo的值分组，所有具有相同TNo的元组作为一组，对每一组使用函数count进行计算，统计出各位教师任课的门数。

当需要对分组后的结果按照一定的条件进行筛选的话，就需要使用having子句。

例2：查询选修两门以上（含两门）课程的学生的学号和课程门数。

select SNo, count(*) as CourseNum from SC group by SNo having count(*) >= 2

当在一个SQL查询中同时使用where 子句、group by 子句 、having子句、order by子句时，其顺序是

where 、group by 、having 、order by

where与having子句的根本区别在于作用对象不同，where子句作于表或视图，从中选择满足条件的元组。

having子句作用于组，选择满足条件的组，必须用在group by子句之后，但group by子句之后可以没有having子句。

5. 查询的排序order by

当需要对查询结果排序时，应该使用order by 子句，order by 子句必须出现在其他子句之后。排序方式可以指定，desc为降序，asc为升序，缺省时为升序。

例1：查询选修C1的学生学号和成绩，并按照降序排列。

select SNo, Score from SC where CNo='C1' order by Score desc

例2：查询选修C2、C3、C4或C5课程的学号、课程号和成绩，查询结果按照学号升序排列，学号相同的再按照降序排列。

select SNo,CNo,Score from SC  where CNo in ('C2', 'C3', 'C4', 'C5') order by SNo asc, Score desc

例3：求选课在三门以上（含三门）且各门课程均及格的学生的学号及其总成绩，查询结果按照总成绩降序排列。

select SNo,sum(Score) as totalScore from SC where Score>=60 group by SNo having (count(*)>=3) order by sum(score) desc