声明:本文使用到的数据表请参看本博客的另一篇文章:标准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