数据库原理与应用.docx
- 文档编号:30356157
- 上传时间:2023-08-13
- 格式:DOCX
- 页数:56
- 大小:43.56KB
数据库原理与应用.docx
《数据库原理与应用.docx》由会员分享,可在线阅读,更多相关《数据库原理与应用.docx(56页珍藏版)》请在冰豆网上搜索。
数据库原理与应用
青岛理工大学琴岛学院教师课题纸
课题
数据库原理与应用
总课
时数
64
累计课时
第19-20课时
授课时间
2015年10月5日
第6周星期一34节
教学目的
通过学习熟练掌握表的各种查询
教学重点
连接查询、嵌套查询
教学难点
嵌套查询
课型
理论
教具
多媒体
课后小结
连接查询、简单嵌套查询在查询中所占比例最大,掌握程度较好
板书设计
第三章
3.33.4
教学进程
回顾注释
一、复习提问
二、单表查询
三、连接查询
四、嵌套查询
五、总结
Select
From
Where
Distinct
复习
1、什么是索引?
如何创建索引?
2、什么是聚集索引?
3、查询语句的一般格式?
4、查询语句中的*和all有何区别?
5、Like有何作用?
有哪些通配符?
各有什么作用?
3.3.1ORDERBY子句
ORDERBY子句:
可以按一个或多个属性列排序升序:
ASC;降序:
DESC;缺省值为升序。
当排序列含空值时
ASC:
排序列为空值的元组最先显示
DESC:
排序列为空值的元组最后显示
[例24]查询选修了3号课程的学生的学号及其成绩,查询结果按分数降序排列。
SELECTSno,GradeFROMSCWHERECno='3'
ORDERBYGradeDESC;
[例25]查询全体学生情况,查询结果按所在系的系号升序排列,同一系中的学生按年龄降序排列。
SELECT*
FROMStudent
ORDERBYSdept,SageDESC;
3.3.2GROUPBY子句
GROUPBY子句分组:
细化聚集函数的作用对象
1)未对查询结果分组,聚集函数将作用于整个查询结果
2)对查询结果分组后,聚集函数将分别作用于每个组
3)作用对象是查询的中间结果表
4)按指定的一列或多列值分组,值相等的为一组
[例31]求各个课程号及相应的选课人数。
SELECTCno,COUNT(Sno)选课人数
FROMSC
GROUPBYCno;
[例32]查询选修了3门以上课程的学生学号。
SELECTSno
FROMSC
GROUPBYSno
HAVINGCOUNT(*)>3;
注意事项:
HAVING短语与WHERE子句的区别:
作用对象不同
1)WHERE子句作用于基表或视图,从中选择满足条件的元组
2)HAVING短语作用于组,从中选择满足条件的组。
例题:
selectsno,count(*)asnum,
sum(score),max(score),min(score)
fromscgroupbysnohavingavg(score)>=60
3.3.3聚集函数
COUNT函数--
COUNT函数返回列中的记录个数。
函数格式:
COUNT([ALL|DISTINCT]列名|*)
说明:
(1)COUNT函数默认使用ALL参数,它将重复计算相同的值,但不包含值为NULL的行。
(2)使用DISTINCT时,相同的值只计数一次,且不包含NULL值。
COUNT函数
(3)在COUNT函数中只引用一个列名时,将返回该列中行记录的个数,但不包含值为NULL的行。
(4)在COUNT函数中可以使用*做参数,它表示返回表中的所有行数,包括含有NULL值的行。
集合函数
[例28]计算1号课程的学生平均成绩。
SELECTAVG(Grade)
FROMSC
WHERECno='1';
[例30]查询学生200215012选修课程的总学分数。
SELECTSUM(Ccredit)
FROMSC,Course
WHERSno='200215012'ANDSC.Cno=Course.Cno;
注意:
1)Where子句中不能使用聚集函数
2)聚集函数除count(*)外,都不处理空值
3.4连接查询
Transact-SQL提供了连接操作符JOIN,用于从两张或多张数据表的连接中获取数据。
两张表之间的连接可以有以下几种不同的方式:
1)内连接[INNER]JOIN
2)外连接LEFT[OUTER]JOIN
RIGHT[OUTER]JOIN
FULL[OUTER]JOIN
3)自连接[INNER]JOIN
4)交叉连接CROSSJOIN
一、内连接(等值与非等值连接查询)
JOIN和ON关键字建立连接的命令格式:
SELECTselect_listFROMtable1
[INNER]JOINtable2
ONtable1.column1<比较运算符>table2.Column2
)
等值连接:
连接运算符为=
[例33]查询每个学生及其选修课程的情况
SELECTStudent.*,SC.*
FROMStudent,SC
WHEREStudent.Sno=SC.Sno;
自然连接:
[例34]对[例33]用自然连接完成。
SELECTStudent.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROMStudent,SC
WHEREStudent.Sno=SC.Sno;
二、自身连接
自身连接:
一个表与其自己进行连接
需要给表起别名以示区别
由于所有属性名都是同名属性,因此必须使用别名前缀
[例35]查询每一门课的间接先修课(即先修课的先修课)
SELECTFIRST.Cno,SECOND.Cpno
FROMCourseFIRST,CourseSECOND
WHEREFIRST.Cpno=SECOND.Cno;
练习:
自身连接
查询books表中至少有两本相同书名的所有图书的信息,包括编号、书名和作者。
三、外连接
外连接与内连接的区别,内连接操作只输出满足连接条件的元组,外连接操作以指定表为连接主体,将主体表中不满足连接条件的元组一并输出。
[例36]改写[例33]
SELECTStudent.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROMStudentLEFTJOINSCON(Student.Sno=SC.Sno);
左外连接
列出左边关系(如本例Student)中所有的元组
右外连接
列出右边关系中所有的元组
全外连接
全外连接返回两个表的所有元组。
Use成绩管理
select*fromtableaarightjointablebbona.c=b.c
select*fromtableaafulljointablebbona.c=b.c
小结:
本次课必须掌握两个知识点,自连接的实现方法和聚集函数的应用。
作业:
查询选修了课程名为“信息系统”的学生学号和姓名。
(引领思路,让学生用所学知识举一反三)
答案:
SELECTSno,Sname③最后在Student关系中
FROMStudent取出Sno和Sname
WHERESnoIN
(SELECTSno②然后在SC关系中找出选
FROMSC修了3号课程的学生学号
WHERECnoIN
(SELECTCno①首先在Course关系中找出
FROMCourse“信息系统”的课程号,为3号
WHERECname=‘信息系统’
)
)
青岛理工大学琴岛学院教师课题纸
课题
数据库原理与应用
总课
时数
64
累计课时
第21、22课时
授课时间
2015年10月9日
第6周星期五12节
教学目的
通过学习掌握表的嵌套查询
教学重点
嵌套查询
教学难点
Exists嵌套查询
课型
理论
教具
多媒体
课后小结
深刻理解了嵌套查询的使用方法,能够写出简单的Exists嵌套查询
板书设计
第三章
3.5
教学进程
回顾注释
一、习题讲解
二、嵌套查询
三、集合查询
Groupby
Orderby
内连接
外连接
复习
1、什么是相关子查询?
2、什么是不相关子查询?
3、左外连接和右外连接有何区别?
3.5嵌套查询
当能确切知道内层查询返回单值时,可用比较运算符(>,<,=,>=,<=,!
=或<>)。
一、与ANY或ALL谓词配合使用
例:
假设一个学生(无重名)只可能在一个系学习,并且必须属于一个系,则在[例39]可以用=代替IN:
SELECTSno,Sname,Sdept
FROMStudent
WHERESdept=
(SELECTSdept
FROMStudent
WHERESname=‘刘晨’);
)
[例41]找出每个学生超过他选修课程平均成绩的课程号。
SELECTSno,Cno
FROMSCx
WHEREGrade>=(SELECTAVG(Grade)
FROMSCyWHEREy.Sno=x.Sno)
可能的执行过程:
1.从外层查询中取出SC的一个元组x,将元组x的Sno值(200215121)传送给内层查询。
SELECTAVG(Grade)
FROMSCy
WHEREy.Sno='200215121';
2.执行内层查询,得到值88(近似值),用该值代替内层查询,得到外层查询:
SELECTSno,Cno
FROMSCx
WHEREGrade>=88;
3.执行这个查询,得到
(200215121,1)
(200215121,3)
4.外层查询取出下一个元组重复做上述1至3步骤,直到外层的SC元组全部处理完毕。
结果为:
(200215121,1)
(200215121,3)
(200215122,2)
子查询一定要跟在比较符之后
错误的例子:
SELECTSno,Sname,Sdept
FROMStudent
WHERE(SELECTSdeptFROMStudent
WHERESname=‘刘晨’)
=Sdept
二、带有ANY(SOME)或ALL谓词的子查询
<列值><比较运算符>[ALL|ANY|SOME]<内部查询>
ALL:
列值必须和内部查询结果集的每一个值进行比较,只有每一次的比较结果都为真时,比较结果才为真。
ANY、SOME:
列值和内部查询结果集的值进行比较,只要有一次为真,比较结果就为真。
[例42]查询其他系中比计算机科学某一学生年龄小的学生姓名和年龄
SELECTSname,Sage
FROMStudent
WHERESage WHERESdept='CS') ANDSdept<>‘CS' 执行过程: 1.RDBMS执行此查询时,首先处理子查询,找出CS系中所有学生的年龄,构成一个集合(20,19) 2.处理父查询,找所有不是CS系且年龄小于 20或19的学生 带有ANY(SOME)或ALL谓词的子查询(续) 用聚集函数实现[例42] SELECTSname,Sage FROMStudent WHERESage<(SELECTMAX(Sage) FROMStudent WHERESdept=‘CS') ANDSdept<>'CS’; [例43]查询其他系中比计算机科学系所有学生年龄都小的学生姓名及年龄。 方法一: 用ALL谓词 SELECTSname,Sage FROMStudent WHERESage (SELECTSage FROMStudent WHERESdept='CS') ANDSdept<>'CS’ 带有ANY(SOME)或ALL谓词的子查询(续) 方法二: 用聚集函数 SELECTSname,Sage FROMStudent WHERESage<(SELECTMIN(Sage)FROMStudent WHERESdept='CS') ANDSdept<>'CS' 三、带有EXISTS谓词的子查询 1.EXISTS谓词 存在量词 检查内层查询(子查询)是否有结果返回。 若内层查询结果非空,则外层的WHERE子句返回真值 若内层查询结果为空,则外层的WHERE子句返回假值 由EXISTS引出的子查询,其目标列表达式通常都用* 2.NOTEXISTS谓词 若内层查询结果非空,则外层的WHERE子句返回假值 若内层查询结果为空,则外层的WHERE子句返回真值 例: [例39]查询与“刘晨”在同一个系学习的学生。 可以用带EXISTS谓词的子查询替换: SELECTSno,Sname,Sdept FROMStudentS1 WHEREEXISTS (SELECT* FROMStudentS2 WHERES2.Sdept=S1.SdeptAND S2.Sname=‘刘晨’) [例44]查询所有选修了1号课程的学生姓名。 思路分析: 本查询涉及Student和SC关系 在Student中依次取每个元组的Sno值,用此值去检查SC关系 若SC中存在这样的元组,其Sno值等于此Student.Sno值,并且其Cno='1',则取此Student.Sname送入结果关系 用嵌套查询 SELECTSname FROMStudent WHEREEXISTS (SELECT* FROMSC WHEREStudent.Sno=SnoANDCno='1' ) 用连接运算 SELECTSname FROMStudent,SC WHEREStudent.Sno=SC.SnoANDSC.Cno='1'; [例45]查询没有选修1号课程的学生姓名。 SELECTSname FROMStudent WHERENOTEXISTS (SELECT* FROMSC WHERESno=Student.Sno ANDCno='1'); 注意事项: 不同形式的查询间的替换一些带EXISTS或NOTEXISTS谓词的子查询不能被其他形式的子查,比较运算符、ANY和ALL谓词的子查询都能用带EXISTS谓词的子查询等价替换。 小结: 运用之前的连接方法应用于对于嵌套查询的实现中,多做题目练习。 青岛理工大学琴岛学院教师课题纸 课题 数据库原理与应用 总课 时数 64 累计课时 第23、24课时 授课时间 2015年10月12日 第7周星期一34节 教学目的 视图的各种操作 教学重点 视图操作 教学难点 视图操作使用的命令 课型 理论 教具 多媒体 课后小结 深刻理解视图的作用,并能熟练进行视图操作。 板书设计 第三章 3.6 教学进程 回顾注释 一、视图概念 二、视图创建 三、视图修改 复习 提问: 1.嵌套查询的实现方法有几种? 并举例 2.创建表的语句实现? 3.6视图 3.6.1视图的创建与撤消 3.6.2视图的更新 3.6.3视图的查询 定义: 虚表,是从一个或几个基本表(或视图)导出的表,只存放视图的定义,不存放视图对应的数据,基表中的数据发生变化,从视图中查询出的数据也随之改变。 一、建立视图 语句格式 CREATEVIEW <视图名>[(<列名>[,<列名>]…)] AS<子查询> [WITHCHECKOPTION]; 1)组成视图的属性列名: 全部省略或全部指定 2)子查询不允许含有ORDERBY子句、DISTINCT短语 3)RDBMS执行CREATEVIEW语句时只是把视图定义存入数据字典,并不执行其中的SELECT语句。 4)在对视图查询时,按视图的定义从基本表中将数据查出。 [例1]建立信息系学生的视图。 CREATEVIEWIS_Student AS SELECTSno,Sname,Sage FROMStudent WHERESdept='IS'; [例2]建立信息系学生的视图,并要求进行修改和插入操作时仍需保证该视图只有信息系的学生。 CREATEVIEWIS_Student AS SELECTSno,Sname,Sage FROMStudent WHERESdept='IS' WITHCHECKOPTION; 对IS_Student视图的更新操作: 1)修改操作: 自动加上Sdept='IS'的条件 2)删除操作: 自动加上Sdept='IS'的条件 3)插入操作: 自动检查Sdept属性值是否为'IS' 4)如果不是,则拒绝该插入操作 5)如果没有提供Sdept属性值,则自动定义Sdept为'IS' 基于多个基表的视图 [例3]建立信息系选修了1号课程的学生视图。 CREATEVIEWIS_S1(Sno,Sname,Grade) AS SELECTStudent.Sno,Sname,Grade FROMStudent,SC WHERESdept='IS'AND Student.Sno=SC.SnoAND SC.Cno='1'; [例4]建立信息系选修了1号课程且成绩在90分以上的学生的视图。 CREATEVIEWIS_S2 AS SELECTSno,Sname,Grade FROMIS_S1 WHEREGrade>=90; 带表达式的视图 [例5]定义一个反映学生出生年份的视图。 CREATEVIEWBT_S(Sno,Sname,Sbirth) AS SELECTSno,Sname,2000-Sage FROMStudent; 分组视图 [例6]将学生的学号及他的平均成绩定义为一个视图 CREATVIEWS_G(Sno,Gavg) AS SELECTSno,AVG(Grade) FROMSC GROUPBYSno; 不指定属性列 [例7]将Student表中所有女生记录定义为一个视图 CREATEVIEWF_Student(F_Sno,name,sex,age,dept) AS SELECT* FROMStudent WHERESsex=‘女’ 二、删除视图 语句的格式: DROPVIEW<视图名>[CASCADE]; 1)该语句从数据字典中删除指定的视图定义 2)如果该视图上还导出了其他视图,使用CASCADE级联删除语句,把该视图和由它导出的所有视图一起删除 3)删除基表时,由该基表导出的所有视图定义都必须显式地使用DROPVIEW语句删除 [例8] 删除视图BT_S: DROPVIEWBT_S; 删除视图IS_S1: DROPVIEWIS_S1;拒绝执行级联删除: DROPVIEWIS_S1CASCADE; 3.6.2查询视图 1.用户角度: 查询视图与查询基本表相同 2.RDBMS实现视图查询的方法 3.视图消解法(ViewResolution) 1)进行有效性检查 2)转换成等价的对基本表的查询 3)执行修正后的查询 [例9]在信息系学生的视图中找出年龄小于20岁的学生。 SELECTSno,Sage FROMIS_Student WHERESage<20; [例10]查询选修了1号课程的信息系学生 SELECTIS_Student.Sno,Sname FROMIS_Student,SC WHEREIS_Student.Sno=SC.SnoANDSC.Cno='1'; [例11]在S_G视图中查询平均成绩在90分以上的学号和平均成绩 SELECT*FROMS_G WHEREGavg>=90; S_G视图的子查询定义: CREATEVIEWS_G(Sno,Gavg) AS SELECTSno,AVG(Grade)FROMSC GROUPBYSno; 查询转换 错误: SELECTSno,AVG(Grade)FROMSC WHEREAVG(Grade)>=90 GROUPBYSno; 正确: SELECTSno,AVG(Grade)FROMSC GROUPBYSno HAVINGAVG(Grade)>=90; [例12]将信息系学生视图IS_Student中学号200215122的学生姓名改为“刘辰”。 UPDATEIS_StudentSETSname='刘辰' WHERESno='200215122'; 转换后的语句: UPDATEStudentSETSname='刘辰' WHERESno='200215122'ANDSdept='IS' [例13]向信息系学生视图IS_S中插入一个新的学生记录: 200215129,赵新,20岁 INSERTINTOIS_StudentVALUES(‘95029’,‘赵新’,20); 转换为对基本表的更新: INSERTINTOStudent(Sno,Sname,Sage,Sdept) VALUES(‘200215129','赵新',20,'IS'); [例14]删除信息系学生视图IS_Student中学号为200215129的记录 DELETEFROMIS_Student WHERESno='200215129'; 转换为对基本表的更新: DELETEFROMStudent WHERESno='200215129'ANDSdept='IS'; 允许对行列子集视图进行更新 小结: 视图的作用: 1.视图能够简化用户的操作 2.视图使用户能以多种角度看待同一数据 3.视图对重构数据库提供了一定程度的逻辑独立性 4.视图能够对机密数据提供安全保护 5.适当的
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库 原理 应用