单表查询

数据查询

数据查询是数据库的核心操作,SQL提供了select语句进行数据查询,该语句具有灵活的使用方式和丰富的功能
其一般格式为:

select [all | distinct] <目标列的表达式>, <目标列的表达式>.....
from <表名或视图名>, [<表名或视图名> ... | (<select语句>)[as]<别名>
where <条件表达式>
group by <列名1> [having <条件表达式>]
order by <列名2> [ASC | DESC];

整个 select 语句的含义是,根据 where 子句的条件表达式从 from 子句指定的基本表,视图或派生表中找出满足条件的元组,再按 select 子句中的目标表达式选出元组中的属性值形成结果表

如果有 group by 子句,则将结果按 <列名1> 的值进行分组,该属性列值相等的元组为一个组,通常会在每组中作用聚集函数,如果 group by 子句带有having 短语,则只有满足指定条件的组才予以输出

如果有 order by 子句,则结果表还要按 <列名2> 的值升序或者降序排序

select语句既可以完成简单的单表查询,也可以完成复杂的连接查询和嵌套查询

一、单表查询

单表查询是指仅涉及一个表的查询

1. 选择表中的若干列

1.1 查询指定列

• 例1 : 查询全体学生的学号和姓名

select Sno,Sname
from Student;

• 例2 : 查询全体学生的姓名,学号,所在系

select Sname,Sno,Sdept
from Student;

1.2 查询全部列

• 例1 : 查询全体学生的详细记录

select *
from Student;

/*等价于*/

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

1.3 查询经过计算的值

select 子句中的<目标列表达式>不仅可以是表中的属性列,也可以是表达式

• l例1 : 查询全体学生的姓名及其出生年份

select Sname,2019-age   /*2019年减去年龄,得到的是出色年份*/
from Student;

-- 执行结果
Sname       2019-age
---------------------
李勇          1994
刘成          1995
.......

用户可以通过指定别名来改变查询结果的列标题,这对于含算术表达式,常量,函数名的目标列表达式尤为有用

• 例2 : 查询全体学生的姓名及其出生年份

select Sname,2019-age BIRTHDAY  /*2019年减去年龄,得到的是出色年份*/
from Student;

-- 执行结果
Sname       BIRTHDAY  
---------------------
李勇          1994
刘成          1995
.......

select 子句中的 <目标列表达式> 不仅可以是算术表达式,还可以是字符串常量,函数等

• 例3 : 擦好像全体学生的姓名,出生月份和所在的院系,要求用小写字母表示系名

select Sname, 'Year of Birth:', 2019-age, lower(Sdept)
from Student;

-- 执行结果
Sname   'Year of Birth:'     2019-age        lower
----------------------------------------------------------
李勇    'Year of Birth:'      1994           cs
刘成    'Year of Birth:'      1995           ma
.......

如果是要求大写字母,则将 lower(Sdept) 改为 upper(Sdept) 即可

2. 选择表中的若干元组

2.1 消除取值重复的行

两个本来不完全相同的元组在投影到指定的某些列上后,可能会变成相同的行,可以用distinct消除它们

• 例1 : 查询选修了课程的学生学号

select Sno
from SC

/*等价于*/

select all Sno
from SC

-- 执行结果

Sno
--------------------
201615121
201615121
201615122
201615122
201615122
201615123

使用distinct去除重复元素

select distinct Sno
from SC

-- 执行结果

Sno
-------
201615121
201615122
201615123

2.2 查询满足条件的元组

查询满足指定条件的元组可以通过 where 子句实现, where 子句常用的查询条件有:

查询条件	谓词
比较	=, >, <, <=, >=, !=, <>, !<, !>, not +上述比较运算符
确定范围	between and, not between and
确定集合	in, not in
字符匹配	like, not like
空值	is null , is not null
多重条件 (逻辑运算)	and , or , not

1. 比较大小

用于比较的运算符:
= (等于) , > (大于) , < (小于) , <= (小于等于) , >= (大于等于) , !=(不等于) , <> (不等于) , !< (不小于) , !> (不大于)

• 例1 : 查询计算机系全体学生名单

select Sname
from Student
where Sdept='CS'

• 例2 : 查询所有年龄在20岁以下的学生姓名及其年龄

select Sname , Sage
from Student
where Sage<20;

• 例3 : 查询考试成绩不及格的学生的学号

select distinct Sno   /*使用distinct短语,当一个学生有多门不及格,他的学号也只显示一次*/
from SC
where Grade<60;

2. 确定范围

谓词 between…and… 和 not between…and… 可以用来查找属性值在 (或不在) 指定范围内的元组,其中 between 后是范围的下限 (即低值) and 后是范围的上限 (即高值)

• 例1 : 查询年龄在20~23岁 (包括20和23岁) 之间的学生的姓名,系别和年龄,

select Sname,Sdept,Sage
from Student
where Sage between 20 and 23;

• 例2 : 查询年龄不在20~23岁 (包括20和23岁) 之间的学生的姓名,系别和年龄,

select Sname,Sdept,Sage
from Student
where Sage not between 20 and 23;

3. 确定集合

谓词 in 可以用来查找属性值属于指定集合的元组

• 例1: 查询计算机系(CS), 数学系(MA), 和信息系(IS) 学生的姓名和性别

select Sname,Ssex
from Student
where Sdept in ('CS','MA','IS');

与 in 相对应的谓词是 not in ,用于查找属性值不属于指定集合的元组

• 例2 : 查询既不是计算机系(CS), 数学系(MA), 也不是信息系(IS) 学生的姓名和性别

select Sname,Ssex
from Student
where Sdept not in ('CS','MA','IS');

4. 字符匹配

谓词 like 可以用来进行字符串的匹配,其一般语法格式如下:

[not] like '<匹配串>' [escape '<换码字符>']

其含义是查找指定的属性列值与<匹配串>向匹配的元组, <匹配串> 可以是一个完整的字符串,也可以含有通配符 % 和 _

% (百分号) 代表任意长度 (长度可以为0) 的字符串,例如 a%b 可以表示以 a 开头,以 b 结尾的任意长度的字符串,如 acb, adeeib,ab 等都满足该匹配串

-_ (下横线) 代表任意单个字符
例如 a_b 表示以a开头,以b结尾的长度为3的任意字符串,如 acb, afb, 等都满足该匹配串

• 例1 : 查询学号为201215121的学生的详细情况

select *
from Student
where Sno like '201215121';

/*等价于*/

select * 
from Student
where Sno='201215121';

如果 like 后面匹配串中不含通配符,
则可以用 =(等于) 运算符取代 like 谓词,用 != 或 <> (不等于) 运算符取代 not like 谓词

• 例2 : 查询所有姓刘的学生的姓名,学号和性别

select Sname,Sno,Ssex
from Student
where Sname like '刘%';

• 例3 : 查询姓 “欧阳” 且全面为三个汉字的学生的姓名

select Sname
from Student
where Sname like '欧阳_';

注意 : 数据库字符集为ASCLL时一个汉字需要两个 _ ; 当字符集为GBK时只需要一个 _

• 例3 : 查询名字中第二个字为 “阳” 的学生的姓名和学号

select Sname,Sno
from Student
where Sname like '_阳%';

• 例4 : 查询所有不姓刘的学生的姓名,学号和性别

select Sname,Sno,Ssex
from Student
where Sname not like '刘%';

如果用户要查询的字符串本身就含有通配符 % 或 _ ,这时就要用 escape ‘<换码字符>’ 短语对通配符进行转义了

• 例5 : 查询DB_Design 课程的课程号和学分

select Cno,Ccredit
from Course
where Cname like 'DB\_Design' escape'\';

/* escape  "\" 表示  "\" 为换码字符*/
/* 这样匹配串中紧跟在 "\" 后面的字符 "_" 不再具有通配符的含义,转义为普通的 "_" 字符 */

• 例6 : 查询以"DB_"开头,且倒数第三个字符为 i 的课程的详细情况

select *
from Course
where Cname like 'DB\_%i_ _'escape'\';
/* 这里的匹配串为"DB\_%i_ _" */
/*第一个_前面有换码字符\,所以它被转义为普通的_字符*/
/*i后面的两个_的前面均没有换码字符\,所以它们仍作为通配符*/

5. 涉及空值的查询

• 例1 : 有些同学选修课程后没有参加考试,所以有选课记录,但没有考试成绩,查询缺少成绩的学生的学号和相应的课程号

select Sno,Cno
from SC
where Grade is null;
/*分数Grade时空值*/

• 例2 : 查询所有有成绩的学生学号和课程号

select Sno,Cno
from SC
where Grade is not null;

注意这里 is 不能用 等号(=) 代替

6. 多重条件查询

逻辑运算符 and 和 or 可用来连接多个查询条件, and 的优先级高于 or ,但用户可以用括号改变优先级

• 例1 : 查询计算机系年龄在20岁以下的学生姓名

select Sname
from Student
where Sdept='CS' and Sage<20;

in 谓词实际上是多个 or 运算符的缩写

• 例2 : 查询 计算机系(CS), 数学系(MA) 和 信息系(IS) 学生的姓名和性别

select Sname,Ssex
from Student 
where Sdept='CS' or Sdept='MA' or Sdept='IS'

3. order by子句

用户可以用 order by 子句对查询结果按照一个或多个属性列的升序(ASC) 或者降序 (DESC) 排列,默认值为升序

• 例1 : 查询选修了3号课程的学生的学号及其成绩,查询结果按分数的降序排列

select Sno,Grade
from SC
where Cno='3'
order by Grade DESC;

对于空值,排序时显示的次序由具体系统实现来决定
例如按升序排,含空值的元组最后显示; 按降序排,空值的元组则最先显示
各个系统的实现可以不同,只要保持一致就行

• 例2 : 查询全体学生情况,查询结果按所在系的系号升序排列,同一系中的学生按年龄降序排列

select*
from Student
order by Sdept,Sage DESC;

4. 聚集函数

聚集函数	含义
count(*)	统计元组个数
count([distinct 或者all <列名>])	统计一列中值的个数
sum([distinct 或者all <列名>])	统计一列值总和 (此列必须是数值型)
avg([distinct 或者all <列名>])	统计一列值的平均值 (此列必须是数值型)
max([distinct 或者all <列名>])	求一列中的最大值
min([distinct 或者all <列名>])	求一列中的最小值

如果指定 distinct 短语,则表示在计算时要取消指定列中的重复值,如果不指定distinct 短语或指定 all 短语 (all为默认值), 则表示不取消重复值

• 例1 : 查询学生总人数

select count(*)
from SC;

• 例2 : 查询选修了课程的学生人数

select count(distinct SNo)
from SC;

学生每选修一门课,在 SC 中都有一条相应的记录,一个学生要选修多门课程,为避免重复计算学生人数,必须在 count 函数中用 distinct 短语

• 例3 : 计算选修1号课程的学生的评价成绩

select avg(Grade)
from SC
where Cno='1';

• 例4 : 查询选修1号课程的学生的最高分数

select MAX(Grade)
from SC
where Cno='1';

• 例5 : 查询学生201215012选修课程的总学分数

select sum(Ccredit)
from SC,Course
where Sno='201215012' and SC.Cno=Course.Cno

当聚集函数遇到空值时,除count(*)外,都跳过空值而只处理非空值,

count(*) 是对元组进行计数,某个元组的一个或部分列取空值不影响count的统计结果

注意 : where子句中是不能用聚集函数作为条件表达式的,聚集函数只能用于select 子句和 group by 中的 having 子句

5. group by子句

group by子句将查询结果按某一列或多列的值分组,值相等的为一组

将查询结果分组的目的是为了细化聚集函数的作用对象,如果未对查询结果分组,聚集函数将作用于整个查询结果,分组后聚集函数将作用于每一个组,即每一组都有一个函数值

• 例1 : 求各个课程号及相对应的选课人数

select Cno,count(Sno)
from SC
group by Cno;
/*该语句对查询结果按Cno的值分组,所有具有相同Cno值的元组为一组,
然后对每一组作用聚集函数count进行计算,以求得该组的学生人数 */

-- 查询结果
Cno        count(Sno)
---------------------
1               22
2               35
3               21
......

如果分组后还要求按一定的条件对这些组进行筛选,最终只输出满足指定条件的组,则可以使用 having 短语指定筛选条件

• 例2 : 查询选修了3门以上课程的学生学号

select Sno
from SC
group by Sno
having count(*)>3;

/*
这里先用 group by 子句按 Sno 进行分组,再用聚集函数 count 对每一组计数;
having 短语给出了选择组的条件,只有满足条件 (即元组个数>3,表示此学生选修的课超过了3门) 的组才会被选出来
*/

where 子句与 having 短语的区别在于作用对象不同,

where 子句作用与基本表或视图,从中选择满足条件的元组;

having 短语作用于组,从中选择满足条件的组

• 例3 : 查询平均成绩大于等于90分的学生学号和平均成绩

/*错误示范:*/

select Sno,avg(Grade)
from SC
where avg(Grade)>=90
group by Sno;

/*因为 where 子句中是不能用聚集函数作为条件表达式的*/


/*正确的查询语句*/

select Sno,AVG(Grade)
from SC
group by Sno
having AVG(Grade)>=90;