单表查询
数据查询
数据查询是数据库的核心操作,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 : 查询计算机系全体学生名单
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;
- 确定范围
谓词 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;
- 确定集合
谓词 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');
- 字符匹配
谓词 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后面的两个_的前面均没有换码字符\,所以它们仍作为通配符*/
- 涉及空值的查询
- 例1 : 有些同学选修课程后没有参加考试,所以有选课记录,但没有考试成绩,查询缺少成绩的学生的学号和相应的课程号
select Sno,Cno
from SC
where Grade is null;
/*分数Grade时空值*/
- 例2 : 查询所有有成绩的学生学号和课程号
select Sno,Cno
from SC
where Grade is not null;
注意这里 is 不能用 等号(=) 代替
- 多重条件查询
逻辑运算符 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;