数据库原理及应用教案第四章

教案授课时间第四周周一12节、周三78节授课教师杨清波授课班级：信息14-12授课内容概要4.1数据查询功能4.2数据更改功能目的要求掌握SQL语言的数据查询功能，掌握SQL语言的数据更改功能。重点SQL语言的数据查询功能难点SQL语言的数据查询功能作业布置课后题：3、6、9、12、21、24本章（节）参考书《数据库系统概论(第4版)》，王珊等，电子工业出版社，2006-05-01《数据库原理与应用教程（第三版）》，何玉洁等，机械工业出版社，2012-09教学方法主要教具多媒体、板书结合备注2授课过程及内容备注1.SQL数据查询语句的基本格式SELECT目标列名序列--需要哪些列FROM数据源--来自于哪些表[WHERE检索条件]--根据什么条件[GROUPBY分组依据列][HAVING组提取条件][ORDERBY排序依据列]2.查询表中用户感兴趣的部分属性列例1．查询全体学生的学号与姓名。SELECTSno,SnameFROMStudent例2．查询全体学生的姓名、学号、所在系SELECTSname,Sno,SdeptFROMStudent例3．查询全体学生的记录SELECT*FROMStudent例4．查询全体学生的姓名及其出生年份。SELECTSname,2010-SageFROMStudent3.选择表中若干元组例6．查询选修了课程的学生的学号，去掉重复的远组SELECTDISTINCTSnoFROMSC例7．查询计算机系全体学生的姓名。SELECTSnameFROMStudentWHERESdept='计算机系'例9．查询考试成绩有不及格的学生的学号SELECTDISTINCTSnoFROMSCWHEREGrade60例10．查询年龄在20～23岁之间的学生的姓名、所在系和年龄。SELECTSname,Sdept,SageFROMStudentWHERESageBETWEEN20AND23例12．查询信息系、数学系和计算机系学生的姓名和性别。SELECTSname,SsexFROMStudentWHERESdeptIN('信息系','数学系','计算机系')例16．查询名字中第2个字为‘小’或‘大’的学生的姓名和学号。SELECTSname,SnoFROMStudentWHERESnameLIKE'_[小大]%'例20．查询没有考试成绩的学生的学号和相应的课程号。SELECTSno,CnoFROMSCWHEREGradeISNULL例21．查询计算机系年龄在20岁以下的学生姓名。SELECTSnameFROMStudentWHERESdept=‘计算机系'ANDSage203授课过程及内容备注4.对查询结果进行排序例23．查询选修了‘c02’号课程的学生的学号及其成绩，查询结果按成绩降序排列。SELECTSno,GradeFROMSCWHERECno='c02'ORDERBYGradeDESC5.使用计算函数汇总数据SQL提供的计算函数有：COUNT（*）：统计表中元组个数；COUNT（[DISTINCT]列名）：统计本列列值个数；SUM（[DISTINCT]列名）：计算列值总和；AVG（[DISTINCT]列名）：计算列值平均值；MAX（[DISTINCT]列名）：求列值最大值；MIN（[DISTINCT]列名）：求列值最小值。上述函数中除COUNT（*）外，其他函数在计算过程中均忽略NULL值。例26．统计选修了课程的学生的人数。SELECTCOUNT(DISTINCTSno)FROMSC例27．计算9512101号学生的考试总成绩之和。SELECTSUM(Grade)FROMSCWHERESno='9512101'6.对查询结果进行分组计算作用：可以控制计算的级别：对全表还是对一组。目的：细化计算函数的作用对象。分组语句的一般形式：[GROUPBY分组条件][HAVING组过滤条件]例31．查询每名学生的选课门数和平均成绩。SELECTSnoas学号,COUNT(*)as选课门数,AVG(Grade)as平均成绩FROMSCGROUPBYSno例32．查询修了3门以上课程的学生的学号SELECTSnoFROMSCGROUPBYSnoHAVINGCOUNT(*)37.若一个查询同时涉及两个或两个以上的表，则称之为连接查询。连接查询是关系数据库中最主要的查询，连接查询包括内连接、外连接和交叉连接等。SQL-92内连接语法如下：SELECT…FROM表名[INNER]JOIN4授课过程及内容备注被连接表ON连接条件执行连接操作的过程：1)首先取表1中的第1个元组，然后从头开始扫描表2，逐一查找满足连接条件的元组，2)找到后就将表1中的第1个元组与该元组拼接起来，形成结果表中的一个元组。3)表2全部查找完毕后，再取表1中的第2个元组，然后再从头开始扫描表2，…4)重复这个过程，直到表1中的全部元组都处理完毕为止。例36.查询计算机系学生的修课情况，要求列出学生的名字、所修课的课程号和成绩。SELECTSname,Cno,GradeFROMStudentJOINSCONStudent.Sno=SC.SnoWHERESdept='计算机系'例38．查询所有修了VB课程的学生的修课情况，要求列出学生姓名和所在的系。SELECTSname,SdeptFROMStudentSJOINSCONS.Sno=SC.SnoJOINCourseCONC.Cno=SC.cnoWHERECname='VB'例40.查询学生的修课情况，包括修了课程的学生和没有修课的学生。SELECTStudent.Sno,Sname,Cno,GradeFROMStudentLEFTOUTERJOINSCONStudent.Sno=SC.Sno8.子查询使用子查询进行基于集合的测试的语句的一般格式为：列名[NOT]IN(子查询)例41.查询与刘晨在同一个系的学生。SELECTSno,Sname,SdeptFROMStudentWHERESdeptIN(SELECTSdeptFROMStudentWHERESname=‘刘晨’)5授课过程及内容备注ANDSname!=‘刘晨’带比较运算符的子查询指父查询与子查询之间用比较运算符连接。例44.查询修了‘c02’课程且成绩高于此课程的平均成绩的学生的学号和成绩。SELECTSno,GradeFROMSCWHERECno='c02‘andGrade(SELECTAVG(Grade)fromSCWHERECno='c02')带EXISTS谓词的子查询不返回查询的数据，只产生逻辑真值（有数据）和假值（没有数据）。例45．查询选修了‘c01’号课程的学生姓名。SELECTSnameFROMStudentWHEREEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno='c01')9.数据更改功能插入单行记录的INSERT语句的格式为：INSERTINTO表名[(列名表)]VALUES（值表）功能：新增一个符合表结构的数据行，将值表数据按表中列定义顺序[或列名表顺序]赋给对应列名。例1．将新生记录（95020，陈冬，男，信息系，18岁）插入到Student表中。INSERTINTOStudentVALUES('9521105','陈冬','男',18,'信息系')数据更新功能用UPDATE语句实现。格式：UPDATE表名SET列名＝表达式[,…n][WHERE更新条件]例2.将‘9512101’学生的年龄改为21岁UPDATEStudentSETSage=21WHERESno='9512101'删除数据用DELETE语句实现。格式：DELETE[FROM]表名[WHERE删除条件6授课过程及内容备注例2．删除所有不及格学生的修课记录。DELETEFROMSCWHEREGrade60