10 第5章 关系数据库标准语言SQL2

1第5章关系数据库标准语言SQL2主要内容5.1SQL的功能及特点5.2数据定义语句5.3数据查询语句5.4数据更新语句5.6数据控制语句35.3.1查询语句语法SELECT[ALL|DCSTINCT]目标列组FROM表名或视图名[WHERE元组选择条件][GROUPBY分组列[HAVING组选择条件]][ORDERBY排序列[ASC|DESC]]；4SELECT子句：指定要显示的属性列;FROM子句：指定查询对象(基本表或视图);WHERE子句：指定查询条件;GROUPBY子句：对查询结果按指定列的值分组，该属性列值相等的元组为一个组。通常会在每组中使用聚集函数。HAVING短语：筛选出只有满足指定条件的组;ORDERBY子句：对查询结果表按指定列值的升序或降序排序;5.3.1查询语句语法55.3.2简单查询指FROM子句中仅涉及一个表的查询，是一种最简单的查询操作。一、选择表中的若干列二、选择表中的若干元组三、对查询结果排序四、使用聚集函数五、对查询结果分组6示例数据库学生课程库，包含3个基本表，结构为：Student(Sno，Sname，Ssex，Sage，Sdept)Course(Cno，Cname，Cpno，Ccredit)SC(Sno，Cno，Grade)71.查询指定列[例1]查询全体学生的学号与姓名。SELECTSname，Sno，SdeptFROMStudent；[例2]查询全体学生的姓名、学号、所在系。SELECTSno，SnameFROMStudent；82.查询全部列[例3]查询全体学生的详细记录。SELECTSno，Sname，Ssex，Sage，SdeptFROMStudent；或SELECT*FROMStudent；93.查询经过计算的值SELECT子句的目标列为表达式算术表达式字符串常量函数列别名等10[例4]查全体学生的姓名及其出生年份。SELECTSname，2007-SageFROMStudent；输出结果：Sname2007-Sage----------------------王林1989张大民1988顾芳1988姜凡1989葛波1988算术表达式可以给列或表达式起别名：SELECTSname，2007-SageASBirthYearFROMStudent；保留字AS可以省略114.消除取值重复的行在SELECT子句中使用DISTINCT短语假设SC表中有下列数据SnoCnoGrade---------------------2007001192200700128520070013882007002290200700238012ALL与DISTINCT[例5]查询选修了课程的学生学号。SELECTSnoFROMSC;或(默认ALL)SELECTALLSnoFROMSC;结果：Sno-------2007001200700120070012007002200700213例题(2)SELECTDISTINCTSnoFROMSC;该语句则去掉了重复元组结果：Sno-------2007001200700214例题（续）注意DISTINCT短语的作用范围是所有目标列例：查询选修课程的各种成绩错误的写法SELECTDISTINCTCno，DISTINCTGradeFROMSC;正确的写法SELECTDISTINCTCno，GradeFROMSC;155.WHERE子句常用的运算符查询条件谓词比较=,,,=,=,确定范围BETWEENAND,NOTBETWEENAND字符匹配LIKE,NOTLIKE确定集合IN,NOTIN空值ISNULL,ISNOTNULL逻辑运算符AND,OR,NOT16(1)比较大小[例1]查询所有年龄在20岁以下的学生姓名及其年龄。SELECTSname，SageFROMStudentWHERESage20；或SELECTSname，SageFROMStudentWHERENOTSage=20；17(2)确定范围使用谓词BETWEEN…AND…NOTBETWEEN…AND…[例2]查询年龄在20~23岁（包括20岁和23岁）之间的学生的姓名、系别和年龄。SELECTSname，Sdept，SageFROMStudentWHERESageBETWEEN20AND23；18例题（续）[例3]查询年龄不在20~23岁之间的学生姓名、系别和年龄。SELECTSname，Sdept，SageFROMStudentWHERESageNOTBETWEEN20AND23；19使用谓词IN值表,NOTIN值表[例4]查询计算机系（CS）、数学系（MA）学生的姓名和性别。SELECTSname，SsexFROMStudentWHERESdeptIN('CS'，'MA');(3)确定集合20(3)确定集合[例5]查询不是计算机系、数学系的学生的姓名和性别。SELECTSname，SsexFROMStudentWHERESdeptNOTIN('CS'，'MA');21(4)涉及空值的查询使用谓词ISNULL或ISNOTNULL“ISNULL”不能用“=NULL”代替[例13]某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号。SELECTSno，CnoFROMSCWHEREGradeISNULL；22例题(续)[例14]查所有有成绩的学生学号和课程号。SELECTSno，CnoFROMSCWHEREGradeISNOTNULL；23(6)多重条件查询用逻辑运算符AND和OR来联结多个查询条件AND的优先级高于OR可以用括号改变优先级可用来实现多种其他谓词[NOT]IN[NOT]BETWEEN…AND…24例题[例15]查询计算机系年龄在20岁以下的学生姓名。SELECTSnameFROMStudentWHERESdept='CS'ANDSage20；25改写[例4][例4]查询计算机系（CS）、数学系（MA）学生的姓名和性别。SELECTSname，SsexFROMStudentWHERESdeptIN('CS'，'MA')可改写为：SELECTSname，SsexFROMStudentWHERESdept='CS'ORSdept='MA'；26改写[例2][例2]查询年龄在20~23岁（包括20岁和23岁）之间的学生的姓名、系别和年龄。SELECTSname，Sdept，SageFROMStudentWHERESageBETWEEN20AND23；可改写为：SELECTSname，Sdept，SageFROMStudentWHERESage=20ANDSage=23；276.聚集函数5类主要集函数（P137表5-10）计数COUNT（[DISTINCT|ALL]*）COUNT（[DISTINCT|ALL]列名）计算总和SUM（[DISTINCT|ALL]列名）计算平均值AVG（[DISTINCT|ALL]列名）28聚集函数（续）求最大值MAX（[DISTINCT|ALL]列名）求最小值MIN（[DISTINCT|ALL]列名）–DISTINCT短语：在计算时要取消指定列中的重复值–ALL短语：不取消重复值–ALL为缺省值29[例1]查询学生总人数。SELECTCOUNT(*)FROMStudent；[例2]查询选修了课程的学生人数。SELECTCOUNT(DISTINCTSno)FROMSC；注：用DISTINCT以避免重复计算学生人数30[例3]计算1号课程的学生平均成绩。SELECTAVG(Grade)FROMSCWHERECno='1'；[例4]查询选修1号课程的学生最高分数。SELECTMAX(Grade)FROMSCWHERCno='1'；317.GROUPBY子句——分组使用GROUPBY子句分组，细化聚集函数的作用对象未对查询结果分组，聚集函数将作用于整个查询结果;对查询结果分组后，聚集函数将分别作用于每个组;32使用GROUPBY子句分组[例1]求各个课程号及相应的选课人数。SELECTCno，COUNT(Sno)FROMSCGROUPBYCno；CnoCOUNT(Sno)1156411373102421136233使用GROUPBY子句分组（续）注意：GROUPBY子句的作用对象是查询的中间结果表分组方法：按指定的一列或多列值分组，值相等的为一组使用GROUPBY子句后，SELECT子句的列名列表中只能出现分组属性和聚集函数34使用HAVING短语筛选最终输出结果[例2]查询选修了3门以上课程的学生学号。SELECTSnoFROMSCGROUPBYSnoHAVINGCOUNT(*)3；35使用HAVING短语筛选最终输出结果（续）注意：•只有满足HAVING短语指定条件的组才输出•HAVING短语与WHERE子句的区别：作用对象不同WHERE子句作用于基表或视图，从中选择满足条件的元组。HAVING短语作用于组，从中选择满足条件的组。368.ORDERBY子句——排序使用ORDERBY子句可以按一个或多个属性列排序升序：ASC；降序：DESC；缺省值为升序当排序列含空值时ASC：排序列为空值的元组最后显示DESC：排序列为空值的元组最先显示37[例1]查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列。SELECTSno，GradeFROMSCWHERECno='3'ORDERBYGradeDESC；38查询结果SnoGrade--------------2007010200702420070079220070038220070108220070097520070146120070025539[例2]查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。SELECT*FROMStudentORDERBYSdept，SageDESC；ORDERBY5，4DESC；40小结：SELECT语句的一般格式SELECT[ALL|DISTINCT]目标列FROM表名或视图名[WHERE条件表达式][GROUPBY列名[[HAVING条件表达式]][ORDERBY列名[ASC|DESC]]；415.3.3连接查询连接查询中的连接条件通过WHERE子句表达，连接条件和元组选择条件之间用AND（与）操作符衔接；源表一般为多表；用来连接两个表的条件称为连接条件或连接谓词；连接谓词中的列名称为连接字段；连接字段不一定同名，但类型必须是可比的；42[例1]查询每个学生的基本情况以及他所选修的课程情况。Student(Sno，Sname，Ssex，Sage，Sdept)Course(Cno，Cname，Cpno，Ccredit)SC(Sno，Cno，Grade)43SELECTStudent.*，SC.*FROMStudent，SCWHEREStudent.Sno=SC.Sno；44[例2]求学生的学号、姓名、选修的课程名及成绩。Student(Sno，Sname，Ssex，Sage，Sdept)Course(Cno，Cname，Cpno，Ccredit)SC(Sno，Cno，Grade)45SELECTStudent.Sno,Sname,Cname,GradeFROMStudent,Course,SCWHEREStudent.Sno=SC.SnoANDCourse.Cno=SC.Cno;46[例3]求选修了C1课程且成绩为90分以上的学生学号、姓名及成绩。Student(Sno，Sname，Ssex，Sage，Sdept)Course(Cno，Cname，Cpno，Ccredit)SC(Sno，Cno，Grade)47SELECTStudent.Sno,Sname,GradeFROMS