•学生-课程数据库
学生表:Student(Sno,Sname,Ssex,Sage,Sdept)
课程表:Course(Cno,Cname,Cpno,Ccredit)
学生选课表:SC(Sno,Cno,Grade)
单表查询:
查询学生表中的全部详细记录:
select * from student;
查询经过计算的值:
select
[ all | distinct ]
from
< table | view >
[ where <conditions> ]
[ group by <condition1> [having conditions-1] ]
[ order by <column2> [asc |desc] ]
默认的升序也就是asc
distinct取消重复的行
基于数值的过滤条件
例:查询所有年龄在20岁以下的学生姓名及其年龄。
select Sname ,Sage from student
where age<20;
基于范围的过滤条件
例:查询年龄在20~23岁(包括20岁和23岁)之间的学生的姓名、系别和年龄。
例:查询年龄在20~23岁(包括20岁和23岁)之间的学生的姓名、系别和年龄。
select Sname, Sdept, Sage from student
where Sage between 20 and 23;
基于给定集合的过滤条件
例:查询信息系(IS)、数学系(MA)和计算机科学系(CS)学生的姓名和性别。
select Sname,Ssex from student
例:查询信息系(IS)、数学系(MA)和计算机科学系(CS)学生的姓名和性别。
where Sdept in ( 'IS', 'MA','CS' );
当匹配模板为固定字符串时
用 = 运算符取代 LIKE 谓词
用 != 或 <>运算符取代NOT LIKE 谓词
当模糊查询时
% (百分号) 代表任意长度(长度可以为0)的字符串
_ (下横线) 代表任意单个字符
当用户要查询的字符串本身就含有 % 或 _ 时,要使用ESCAPE'<换码字符>' 短语对通配符进行转义。
•基于文本的过滤条件
例:查询学号为95001的学生的详细情况
例:查询学号为95001的学生的详细情况
select * from student
whrere Sno like '95001';/或者 where Sno='95001';
•基于文本的过滤条件
例:查询名字中第2个字为“阳”字的学生的姓名和学号
select Sname,Sno from student
where Sname like '_阳%';
查询不姓刘的学生的姓名和学号
select Sname,Sno from student
where Sname not like '刘%';
查询 db_ 开头 且倒数第三个字符是 i 的课程的详细情况
select * from course
where Cname like 'db\_%i_ _' escape ' \ ' ;
针对空值的过滤条件
查询所有有成绩的学生学号和课程号
select Sno, Cno from SC
where grade is not null;
•多重条件查询
用逻辑运算符AND和 OR来联结多个查询条件
用逻辑运算符AND和 OR来联结多个查询条件
¤AND的优先级高于OR
可以用括号改变优先级
例:查询信息系(IS)、数学系(MA)和计算机科学系(CS)学生的姓名和性别。
select Sname,Ssex from student
where Sdept='IS' or Sdept='MA' or Sdept='CS';
使用ORDER BY子句
可以按一个或多个属性列排序
升序:ASC;
升序:ASC;
降序:DESC;
缺省值为升序
当排序列含空值时(Null为无穷小)
ASC:排序列为空值的元组最先显示
DESC:排序列为空值的元组最后显示
例:查询全体学生情况,查询结果按所在系的系号升序排列,同一系中的学生按年龄降序排列。
select * from student
order by Sdept asc, Sage desc;
或者
select * from student
order by Sdept , Sage desc;
COUNT([DISTINCT|ALL]*)统计元组个数
COUNT([DISTINCT|ALL]<column> )计算一列中值的个数
SUM( [DISTINCT|ALL]<column>)
计算值的总和并返回总数
计算值的总和并返回总数
AVG( [DISTINCT|ALL]<column> )
返回指定列中的平均值
返回指定列中的平均值
MIN( [DISTINCT|ALL]<column> )
返回自变量中指定列的最小值
返回自变量中指定列的最小值
MAX( [DISTINCT|ALL]<column> )
返回自变量中指定列的最大值
返回自变量中指定列的最大值
DISTINCT短语:在计算时要取消指定列中的重复值
ALL短语:不取消重复值
ALL为缺省值
ALL短语:不取消重复值
ALL为缺省值
NULL
除了计算
count
(*)外,
不参与其他计算
聚集函数
例:查询学生总人数。
例:查询学生总人数。
select count(*) from student;
例:查询选修了课程的学生人数。
select count(distinct Sno) from SC;
聚集函数
例:计算1号课程的学生平均成绩。
select avg(grade) from SC
例:计算1号课程的学生平均成绩。
select avg(grade) from SC
where Cno='1';
使用GROUPBY子句分组
细化聚集函数的作用对象
未对查询结果分组,聚集函数将作用于整个查询结果 用where
对查询结果分组后,聚集函数将分别作用于每个组 用having count
只有满足HAVING短语指定条件的组才输出
HAVING
短语与
WHERE
子句的区别:作用对象不同
WHERE
子句作用于基表或视图,从中选择满足条件的元组。
HAVING
短语作用于组,从中选择满足条件的组。
查询结果分组
练习:查询有3门以上课程是90分以上的学生的学号及(90分以上的)课程数
select Sno,count(*) from SC
where Grade>=90
group by sno
having count(*)>=3;
连接查询
自身连接
查询每一门课的间接先修课(即先修课的先修课)
select First.cno,Second.cpno
from Course First,Course Second
where First.cpno=Second.cno;
左连接:以左边的表为标准对右边的表进行连接
右连接:以右边的表为标准对左边的表进行连接。
外连接与普通连接的区别
普通连接操作只输出满足连接条件的元组
外连接操作以指定表为连接主体,将主体表中不满足连接条件的元组一并输出
查询每个学生及其选修课程的情况,即使学生一门课也没有选也要输出学生信息。
select student.Sno,Sname,Ssex,Sage,Sdept,Cno,grade
from student left join SC on student.Sno=SC.Sno
例:查询选修2号课程且其成绩在90分以上的所有学生的学号、姓名
select Sno,Sname
from Student,SC
where student.Sno=SC.Sno and SC.grade>90 and SC.Cno='2';
一个SELECT-FROM-WHERE语句称为一个查询块
将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询,称为嵌套查询
子查询的限制:不能使用ORDER BY子句
带有IN谓词的子查询
例:查询选修课程名为“信息系统”的学生学号和姓名
select Sno,Sname from student
where sno in(
select sno from SC where Cno in(
select Cno from Course where Cname='信息系统';
)
)
带有ANY或ALL谓词的子查询
例:查询其他系中比信息系中某一个学生年龄小的学生姓名和年龄
例:查询其他系中比信息系中某一个学生年龄小的学生姓名和年龄
select Sname,Sage from student
where age< any( select age from student where Sdept='IS' ) and Sdept!='IS';
或者
select Sname,Sage from student
where age< ( select max(age) from student where Sdept='IS' ) and Sdept!='IS';
any为小于其中的某一个就可以,而all小于其中的所有==小于最小的
嵌套查询分类
不相关子查询
子查询的查询条件不依赖于父查询
处理方式:由里向外逐层处理
相关子查询
子查询的查询条件依赖于父查询
处理方式:
•首先取外层查询中表的第一个元组,根据它与内层查询相关的属性值处 理内层查询,若WHERE子句返回值为真,则取此元组放入结果表;
•然后再取外层表的下一个元组;
•重复这一过程,直至外层表全部检查完为止
由EXISTS引出的子查询,其目标列表达式通常都用* ,因为带EXISTS的子查询只返回真值或假值,给出列名无实际意义
带有EXISTS谓词的子查询
例:查询所有选修了1号课程的学生姓名。
例:查询所有选修了1号课程的学生姓名。
select Sname from student
where exists( select * from SC where Sno=student.Sno and Cno='1';)
带有EXISTS谓词的子查询
例:查询选修了全部课程的学生姓名。
select Sname from student
where not exists( select *from Course
where not exists(select *from SC where Sno=student.Sno
and Cno=course.Cno
)
);
集合查询
并:
例:查询计算机科学系的学生或年龄不大于19岁的学生。
select *from student where Sdept='CS'
unoin
select *from student where Sage<19;
交集
查询选修课程1的学生集合与选修课程2的学生集合的交集
select Sno from SC where Cno='1'
intersect
select Sno from SC where Cno='2';
例:查询计算机科学系的学生与年龄不大于19岁的学生的差集。
select Sno from student where Sdept='CS'
minus
select Sno from student where Sage>19;