第3章2上课用查询语句170317Word格式文档下载.docx
- 文档编号:3105948
- 上传时间:2023-05-01
- 格式:DOCX
- 页数:42
- 大小:264.16KB
第3章2上课用查询语句170317Word格式文档下载.docx
《第3章2上课用查询语句170317Word格式文档下载.docx》由会员分享,可在线阅读,更多相关《第3章2上课用查询语句170317Word格式文档下载.docx(42页珍藏版)》请在冰点文库上搜索。
201415121
李勇
男
20
CS
201415122
刘晨
女
19
201415123
王敏
18
MA
201415125
张立
IS
2.课程Course表
3.学生选课SC表
课程号
Cno
成绩
Grade
1
92
2
85
3
88
90
80
3.4.1单表查询
查询仅涉及一个表:
一、选择表中的若干列
二、选择表中的若干元组
三、ORDERBY子句
四、聚集函数
五、GROUPBY子句
◆一、选择表中的若干列
☺1.查询指定列
[例1]查询全体学生的学号与姓名。
SELECTSno,SnameFROMStudent
[例2]查询全体学生的学号、姓名、所在系。
SELECTSname,Sno,SdeptFROMStudent
☺2.查询全部列
[例3]查询全体学生的详细记录。
SELECTSno,Sname,Ssex,Sage,SdeptFROMStudent或
SELECT*FROMStudent
☺3.查询经过计算的值
SELECT子句的<
可以为:
算术表达式字符串常量函数列别名
[例4]查全体学生的姓名及其出生年份。
SELECTSname,2016-SageFROMStudent
/*查询结果的第2列是一个算术表达式*/
SELECTSnameas姓名,2016-Sageas出生年份FROMStudent
输出结果:
姓名出生年份
李勇1996
刘晨1995
王敏1998
张立1997
[例5]查询全体学生的姓名、出生年份和所有系,要求用小写字母表示所有系名。
SELECTSname,'
YearofBirth:
'
2016-Sage,LOWER(Sdept)
FROMStudent
Sname无列名无列名无列名
李勇YearofBirth:
1996cs
刘晨YearofBirth:
1995is
王敏YearofBirth:
1998ma
张立YearofBirth:
1997is
使用列别名改变查询结果的列标题:
SELECTSnameNAME,'
BIRTH,
2016-SageBIRTHDAY,LOWER(Sdept)DEPARTMENT
FROMStudent
NAMEBIRTHBIRTHDAYDEPARTMENT
1996cs
1995is
王敏YearofBirth:
1998ma
1997is
◆二、选择表中的若干元组
◆1.消除取值重复的行:
不同的元组投影到某些指定列上后,可能会变成相同的行,用DISTINCT消除它们;
如果没有指定DISTINCT关键词,则缺省为ALL。
distinctadj.明显的;
独特的;
清楚的;
有区别的
[例6]查询选修了课程的学生学号。
SELECTSnoFROMSC
等价于:
SELECTALLSnoFROMSC
执行上面的SELECT语句后,结果为:
Sno
指定DISTINCT关键词,去掉表中重复的行
SELECTDISTINCTSnoFROMSC(不同的)
执行结果:
Sno
201415121
201415122
☺2.查询满足条件的元组(通过WHERE子句实现,常用查询条件如下)
表3.4常用的查询条件
查询条件
谓词
比较
=,>
,<
,>
=,<
=,!
>
,!
<
;
NOT+上述比较运算符
确定范围
BETWEENAND,NOTBETWEENAND
确定集合
IN,NOTIN
字符匹配
LIKE,NOTLIKE
空值
ISNULL,ISNOTNULL
多重条件(逻辑运算)
AND,OR,NOT
☺
(1)比较大小
[例7]查询计算机科学系全体学生的名单。
SELECTSnameFROMStudentWHERESdept='
CS'
数据库执行过程:
全表扫描,逐一取出元组,检查Sdept列值是否等于‘CS’,相等取出,否则跳过。
[例8]查询所有年龄在20岁以下的学生姓名及其年龄。
SELECTSname,SageFROMStudentWHERESage<
[例9]查询考试成绩有不及格的学生的学号。
SELECTDISTINCTSnoFROMSCWHEREGrade<
60
当一个学生有多门课程不及格,学号也只列一次。
☺
(2)确定范围
谓词:
BETWEEN…AND…
NOTBETWEEN…AND…
[例10]查询年龄在20~23岁(包括20岁和23岁)之间的学生的姓名、系别和年龄
SELECTSname,Sdept,SageFROMStudent
WHERESageBETWEEN20AND23
[例11]查询年龄不在20~23岁之间的学生姓名、系别和年龄
SELECTSname,Sdept,SageFROMStudent
WHERESageNOTBETWEEN20AND23
☺(3)确定集合
谓词:
IN<
值表>
NOTIN<
[例12]查询信息系(IS)、数学系(MA)和计算机科学系(CS)学生的姓名和性别。
SELECTSname,SsexFROMStudent
WHERESdeptIN('
IS'
'
MA'
)
[例13]查询既不是信息系、数学系,也不是计算机科学系的学生的姓名和性别。
SELECTSname,SsexFROMStudent
WHERESdeptNOTIN('
☺(4)字符匹配:
查找指定的属性列值与<
匹配串>
相匹配的元组,
可以是一个完整的字符串,也可以含通配符‘%’、‘_’。
‘%’代表任意长度(可以为0)的字符串;
‘_’(下划线)代表任意单个字符。
[NOT]LIKE‘<
’[ESCAPE‘<
换码字符>
’]
✧
)匹配串为固定字符串
[例14]查询学号为201415121的学生的详细情况。
SELECT*FROMStudentWHERESnoLIKE'
201415121'
SELECT*FROMStudentWHERESno='
✧2)匹配串为含通配符的字符串
[例15]查询所有姓刘学生的姓名、学号和性别。
SELECTSname,Sno,SsexFROMStudent
WHERESnameLIKE'
刘%'
‘%’代表任意长度(可以为0)的字符串。
[例16]查询姓"
欧阳"
且全名为三个汉字的学生的姓名。
SELECTSnameFROMStudent
欧阳__'
‘_’(下划线)代表任意单个字符。
[例17]查询名字中第2个字为"
阳"
字的学生的姓名和学号。
SELECTSname,SnoFROMStudent
WHERESnameLIKE‘__阳%’
[例18]查询所有不姓刘的学生姓名。
WHERESnameNOTLIKE’刘%’
如果要查询的字符串本身含有通配符%和_,则要使用ESCAPE‘\’对其进行转义。
✧3)使用换码字符将通配符转义为普通字符
[例19]查询DB_Design课程的课程号和学分。
SELECTCno,CcreditFROMCourse
WHERECnameLIKE‘DB\_Design’ESCAPE'
\'
[例20]查询以"
DB_"
开头,且倒数第3个字符为i的课程的详细情况。
SELECT*FROMCourse
WHERECnameLIKE’DB\_%i__’ESCAPE'
第一个_前面有换码字符\,所以用ESCAPE'
把它转义为普通的_字符。
之后的两个_的前面均没有换码字符\,仍为通配符。
☺(5)涉及空值的查询
ISNULL或ISNOTNULL。
“IS”不能用“=”代替!
!
[例21]某些学生选修课程后没有参加考试,所以有选课记录,但没有考试成绩。
查询缺少成绩的学生的学号和相应的课程号。
SELECTSno,CnoFROMSC
WHEREGradeISNULL
[例22]查所有有成绩的学生学号和课程号。
SELECTSno,CnoFROMSC
WHEREGradeISNOTNULL
☺(6)多重条件查询
逻辑运算符:
AND和OR来联结多个查询条件
AND的优先级高于OR
可以用括号改变优先级
可用来实现多种其他谓词如:
[NOT]IN
[NOT]BETWEEN…AND…
[例23]查询计算机系年龄在20岁以下的学生姓名。
SELECTSnameFROMStudent
WHERESdept=’CS’ANDSage<
20;
改写[例12]查询信息系(IS)、数学系(MA)和计算机科学系(CS)学生的姓名和性别。
SELECTSname,SsexFROMStudent
WHERESdeptIN('
)
可改写为:
WHERESdept='
ORSdept='
◆三、ORDERBY子句
ORDERBY子句
查询结果可以按一个或多个属性列排序
升序:
ASC;
降序:
DESC;
缺省值为升序
当排序列含空值时,由具体系统决定,可以是:
ASC:
排序列为空值的元组最后显示
DESC:
排序列为空值的元组最先显示
也可以反过来,系统实现可以不同,只要保持一致就行。
[例24]查询选修了3号课程的学生的学号及其成绩,查询结果按分数降序排列。
SELECTSno,GradeFROMSC
WHERECno='
3'
ORDERBYGradeDESC
[例25]查询全体学生情况,查询结果按所在系的系号升序排列,同一系中的学生按年龄降序排列。
SELECT*FROMStudent
ORDERBYSdept,SageDESC
◆
四、聚集函数
聚集函数:
统计元组个数或一列中值的个数
COUNT([DISTINCT|ALL]*)
COUNT([DISTINCT|ALL]<
列名>
计算一列值的总和
SUM([DISTINCT|ALL]<
)
计算一列值的平均值
AVG([DISTINCT|ALL]<
注:
SUM、AVG函数使用时,跟着的列必须是数值型
求一列值中的最大、最小值
MAX([DISTINCT|ALL]<
MIN([DISTINCT|ALL]<
[例26]查询学生总人数。
SELECTCOUNT(*)FROMStudent
[例27]查询选修了课程的学生人数。
SELECTCOUNT(DISTINCTSno)FROMSC
指定DISTINCT短语表示计算时要取消指定列中的重复值,缺省值为ALL。
因一个学生要选修多门课程,为避免重复计算学生人数,在COUNT函数中用DISTINCT。
[例28]计算1号课程的学生平均成绩。
SELECTAVG(Grade)FROMSCWHERECno='
1'
[例29]查询选修1号课程的学生最高分数。
SELECTMAX(Grade)FROMSCWHERECno='
[例30]查询学生201415121选修课程的总学分数。
SELECTSUM(Ccredit)FROMSC,Course
WHERESno='
ANDSC.Cno=Course.Cno
注意:
WHERE子句中是不能用聚集函数作为条件表达式的。
聚集函数只能用于SELECT子句和GROUPBY中的HAVING子句。
◆五、GROUPBY子句
GROUPBY子句将查询结果按某一列或多列的值分组,值相等的为一组;
目的:
细化聚集函数的作用对象,因为如果不分组,聚集函数(count,sum,avg,max,min)将作用于整个查询结果
分组后,聚集函数将作用于每一个组,即每一组都有一个函数值。
GroupbyCnoGroupbySno
[例31]求各个课程号及相应的选课人数。
SELECTCno,COUNT(Sno)FROMSC
GROUPBYCno
/*查询结果按Cno的值分组*/
可能的查询结果:
CnoCOUNT(Sno)
11
22
32
求出的是选修每门课程的学生人数。
如果语句换成如下:
SELECTSno,COUNT(Cno)FROMSC
GROUPBYSno
/*查询结果按Sno的值分组*/
SnoCOUNT(Cno)
2012151213
2012151222
则求出的是每个学生选修的课程数。
[例32]查询选修了3门以上课程的学生学号。
SELECTSnoFROMSCGROUPBYSno
HAVINGCOUNT(*)>
3/*分组后按一定条件筛选*/
本例先用GROUPBY子句按SNO进行分组,再用聚集函数COUNT对每一组计数;
HAVING短语给出了选择组的条件,只有满足条件(即元组个数>
3,表示学生选修的课程数超过3门)的组才会被选出来。
HAVING短语与WHERE子句的区别在于作用对象不同:
WHERE子句作用于基表或视图,从中选择满足条件的元组
HAVING短语作用于组,从中选择满足条件的组。
[例3.48--P99]查询平均成绩大于等于90分的学生学号和平均成绩
下面的语句是不对的:
SELECTSno,AVG(Grade)
FROMSC
WHEREAVG(Grade)>
=90
GROUPBYSno;
因为WHERE子句中是不能用聚集函数作为条件表达式
正确的查询语句应该是:
GROUPBYSno
HAVINGAVG(Grade)>
=90;
试试:
SELECTSno,count(*)FROMSCgroupbySno,GradehavingGrade<
统计某个学生有几门不及格?
selectsno,count(*)fromscwheregrade<
60groupbysno
3.4.2连接查询之前的查询都是针对一个表进行的,涉及二表以上的称为连接查询
连接查询:
一个查询同时涉及两个以上的表。
WHERE子句中用来连接两个表的条件称为连接条件或连接谓词,其一般格式为:
[<
表名1>
.]<
<
比较运算符>
[<
表名2>
BETWEEN[<
AND[<
列名3>
连接字段:
列名称;
字段类型必须是可比的,但名字不必是相同的。
比较运算符主要有:
=>
>
=<
=!
=(<
)连接运算符为=时,称为等值连接。
连接操作的执行过程:
1、嵌套循环法(NESTED-LOOP):
如通过Sno连接Student和SC表
首先在Student中找到第一个元组,然后从头开始扫描SC,逐一查找满足连接条件的元组(如Student.Sno=SC.Sno),找到后就将Student中的第一个元组与该元组拼接起来,形成结果表中一个元组。
SC全部查找完后,再找Student中第二个元组,然后再从头开始扫描SC,逐一查找满足连接条件的元组,找到后就将Student中的第二个元组与该元组拼接起来,形成结果表中一个元组。
重复上述操作,直到Student中的全部元组都处理完毕。
2、排序合并法(SORT-MERGE):
常用于=连接
首先按连接属性对表1和表2排序,对表1的第一个元组,从头开始扫描表2,顺序查找满足连接条件的元组,找到后就将表1中的第一个元组与该元组拼接起来,形成结果表中一个元组,当遇到表2中第一条大于表1连接字段值的元组时,对表2的查询不再继续;
找到表1的第二条元组,然后从刚才的中断点处继续顺序扫描表2,查找满足连接条件的元组,找到后就将表1中的第一个元组与该元组拼接起来,形成结果表中一个元组,直接遇到表2中大于表1连接字段值的元组时,对表2的查询不再继续;
重复上述操作,直到表1或表2中的全部元组都处理完毕为止
3、索引连接(INDEX-JOIN):
对表2按连接字段建立索引
对表1中的每个元组,依次根据其连接字段值查询表2的索引,从中找到满足条件的元组,找到后就将表1中的第一个元组与该元组拼接起来,形成结果表中一个元组
连接查询是关系数据库中最主要的查询,包括:
一、等值与非等值连接查询
二、自身连接
三、外连接
四、复合条件连接
☺当连接运算符为=时称为等值连接,使用其它运算符称为非等值连接。
[例33]查询每个学生及其选修课程的情况
SELECTStudent.*,SC.*FROMStudent,SC
WHEREStudent.Sno=SC.Sno
查询结果:
Student.Sno
SC.Sno
200215121
200215122
☺自然连接:
在目标列中把等值连接中重复的属性去掉
[例34]对[例33]用自然连接完成。
SELECTStudent.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROMStudent,SC
WHEREStudent.Sno=SC.Sno;
属性列Sname,Ssex,Sage,Sdept,Cno和Grade在Student和SC表中是唯一的,引用时可以去掉表名前缀。
一条SQL语句可以同时完成选择和连接查询,这时WHERE子句是由连接谓词和选择谓词组成的复合条件。
[例3.51]查询选修2号课程且成绩在90分以上的所有学生的学号和姓名。
SELECTStudent.Sno,SnameFROMStudent,SC
WHEREStudent.Sno=SC.SnoAND/*连接谓词*/
SC.Cno='
2'
ANDSC.Grade>
90;
/*选择谓词*/
经查询优化的执行过程:
先从SC中挑选出Cno='
2'
并且Grade>
90的元组形成一个中间关系再和Student中满足连接条件的元组进行连接得到最终的结果关系。
◆二、自身连接
自身连接:
一个表与其自己进行连接
需要给表起别名以示区别
由于所有属性名都是同名属性,因此必须使用别名前缀
[例35]查询每一门课的间接先修课(即先修课的先修课)
SELECTFIRST.Cno,SECOND.Cpno
FROMCourseFIRST,CourseSECOND
WHEREFIRST.Cpno=SECOND.Cno;
查询结果如下:
Pcn
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 上课 查询 语句 170317
![提示](https://static.bingdoc.com/images/bang_tan.gif)