数据库原理及应用第四章SQL示例2

查询指定列[例1]查询全体学生的学号与姓名。SELECTSno,SnameFROMStudent；[例2]查询全体学生的姓名、学号、所在系。SELECTSname,Sno,SdeptFROMStudent;查询全部列[例3]查询全体学生的详细记录。SELECTSno,Sname,Ssex,Sage,SdeptFROMStudent;或SELECT*FROMStudent;查询经过计算的值SELECT子句的目标列表达式为表达式算术表达式字符串常量函数列别名等查询经过计算的值[例4]*查全体学生的姓名及其出生年份。selectSname,2009-Sagefromstudent;输出结果：Sname2009-Sage----------------------李勇1976刘晨1977王名1978张立1978查询经过计算的值[例5]*查询全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名。SELECTSname,'YearofBirth:',2009-Sage,LOWER(Sdept)FROMStudent;例题（续）输出结果：Sname'YearofBirth:'2009-SageLOWER(Sdept)----------------------------------------------李勇YearofBirth:1976cs刘晨YearofBirth:1977is王名YearofBirth:1978ma张立YearofBirth:1977is选择表中的若干元组消除取值重复的行查询满足条件的元组1.消除取值重复的行在SELECT子句中使用DISTINCT短语假设SC表中有下列数据SnoCnoGrade---------------------9500119295001285950013889500229095002380ALL与DISTINCT[例6]查询选修了课程的学生学号。(1)SELECTSnoFROMSC;结果：Sno-------9500195001950019500295002例题（续）(2)SELECTDISTINCTSnoFROMSC;结果：Sno-------9500195002例题（续）注意DISTINCT短语的作用范围是所有目标列例：查询选修课程的各种成绩错误的写法SELECTDISTINCTCno,DISTINCTGradeFROMSC;正确的写法SELECTDISTINCTCno,GradeFROMSC;查询满足条件的元组WHERE子句常用的查询条件(1)比较大小在WHERE子句的比较条件中使用比较运算符=，，，=，=，!=或，!，!，逻辑运算符NOT+比较运算符[例7]查询所有年龄在20岁以下的学生姓名及其年龄。SELECTSname,SageFROMStudentWHERESage20;或SELECTSname,SageFROMStudentWHERENOTSage=20;(2)确定范围使用谓词BETWEEN…AND…NOTBETWEEN…AND…[例8]查询年龄在20~23岁（包括20岁和23岁）之间的学生的姓名、系别和年龄。SELECTSname,Sdept,SageFROMStudentWHERESageBETWEEN20AND23;例题（续）[例9]查询年龄不在20~23岁之间（包括20岁和23岁）的学生姓名、系别和年龄。SELECTSname,Sdept,SageFROMStudentWHERESageNOTBETWEEN20AND23;(3)确定集合使用谓词IN值表,NOTIN值表值表：用逗号分隔的一组取值[例10]查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别。SELECTSname,SsexFROMStudentWHERESdeptIN('IS','MA','CS');(3)确定集合[例11]查询既不是信息系、数学系，也不是计算机科学系的学生的姓名和性别。SELECTSname,SsexFROMStudentWHERESdeptNOTIN('IS','MA','CS');(4)字符串匹配[NOT]LIKE‘匹配串’[ESCAPE‘换码字符’]匹配串：指定匹配模板匹配模板：固定字符串或含通配符的字符串当匹配模板为固定字符串时，可以用=运算符取代LIKE谓词用!=或运算符取代NOTLIKE谓词通配符%(百分号)代表任意长度（长度可以为0）的字符串例：a%b表示以a开头，以b结尾的任意长度的字符串。如acb，addgb，ab等都满足该匹配串_(下横线)代表任意单个字符例：a_b表示以a开头，以b结尾的长度为3的任意字符串。如acb，afb等都满足该匹配串ESCAPE短语：*当用户要查询的字符串本身就含有%或_时，要使用ESCAPE'换码字符'短语对通配符进行转义。例题1)匹配模板为固定字符串[例12]查询学号为95001的学生的详细情况。SELECT*FROMStudentWHERESnoLIKE'95001‘;等价于：SELECT*FROMStudentWHERESno='95001‘;例题（续）2)匹配模板为含通配符的字符串[例13]查询所有姓刘学生的姓名、学号和性别。SELECTSname,Sno,SsexFROMStudentWHERESnameLIKE‘刘%’;SELECT*FROM信息表WHERE姓名like‘%红%’;SELECT*from信息表where籍贯like'%山%';SELECT*from信息表where民族notlike'汉族';例题（续）匹配模板为含通配符的字符串（续）[例14]查询姓欧阳且全名为三个汉字的学生的姓名。SELECTSnameFROMStudentWHERESnameLIKE'欧阳_';SELECT*FROM信息表WHERE姓名LIKE'_阳%';例题（续）匹配模板为含通配符的字符串（续）[例15]查询名字中第2个字为阳字的学生的姓名和学号。SELECTSname,SnoFROMStudentWHERESnameLIKE'_阳%‘;例题（续）匹配模板为含通配符的字符串（续）[例16]查询所有不姓刘的学生姓名。SELECTSname,Sno,SsexFROMStudentWHERESnameNOTLIKE'刘%‘;例题（续）3)使用换码字符将通配符转义为普通字符[例17]*查询DB_Design课程的课程号和学分。SELECTCno,CcreditFROMCourseWHERECnameLIKE'DB\_Design'ESCAPE'\';例题（续）使用换码字符将通配符转义为普通字符(续)[例18]*查询以DB_开头，且倒数第3个字符为i的课程的详细情况。SELECT*FROMCourseWHERECnameLIKE'DB\_%i__'ESCAPE'\';(5)涉及空值的查询使用谓词ISNULL或ISNOTNULL“ISNULL”不能用“=NULL”代替[例19]某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号。SELECTSno,CnoFROMSCWHEREGradeISNULL;例题(续)[例20]查所有有成绩的学生学号和课程号。SELECTSno,CnoFROMSCWHEREGradeISNOTNULL;(6)多重条件查询用逻辑运算符AND和OR来联结多个查询条件AND的优先级高于OR可以用括号改变优先级可用来实现多种其他谓词[NOT]IN[NOT]BETWEEN…AND…例题[例21]查询计算机系年龄在20岁以下的学生姓名。SELECTSnameFROMStudentWHERESdept='CS'ANDSage20;例题[例22]查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别。SELECTSname，SsexFROMStudentWHERESdeptIN('IS‘,'MA‘,'CS')也可写为：SELECTSname,SsexFROMStudentWHERESdept='IS'ORSdept='MA'ORSdept='CS';例题[例23]查询年龄在20~23岁（包括20岁和23岁）之间的学生的姓名、系别和年龄。SELECTSname,Sdept,SageFROMStudentWHERESageBETWEEN20AND23;也可写为：SELECTSname,Sdept,SageFROMStudentWHERESage=20ANDSage=23;三、对查询结果排序使用ORDERBY子句可以按一个或多个属性列排序升序：ASC；降序：DESC；缺省值为升序当排序列含空值时ASC：排序列为空值的元组最后显示DESC：排序列为空值的元组最先显示对查询结果排序（续）[例24]查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列。SELECTSno,GradeFROMSCWHERECno='3'ORDERBYGradeDESC;查询结果SnoGrade--------------9501095024950079295003829501082950097595014619500255对查询结果排序（续）[例25]查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。SELECT*FROMStudentORDERBYSdept,SageDESC;四、使用集函数5类主要集函数计数COUNT（[DISTINCT|ALL]*）COUNT（[DISTINCT|ALL]列名）计算总和SUM（[DISTINCT|ALL]列名）计算平均值AVG（[DISTINCT|ALL]列名）使用集函数（续）求最大值MAX（[DISTINCT|ALL]列名）求最小值MIN（[DISTINCT|ALL]列名）–DISTINCT短语：在计算时要取消指定列中的重复值–ALL短语：不取消重复值–ALL为缺省值使用集函数（续）[例26]查询学生总人数。SELECTCOUNT(*)FROMStudent;[例27]查询选修了课程的学生人数。SELECTCOUNT(DISTINCTSno)FROMSC;注：用DISTINCT以避免重复计算学生人数使用集函数（续）[例28]计算1号课程的学生平均成绩。SELECTAVG(Grade)FROMSCWHERECno='1';[例29]查询选修1号课程的学生最高分数。SELECTMAX(Grade)FROMSCWHERECno='1';五、对查询结果分组使用GROUPBY子句分组细化集函数的作用对象未对查询结果分组，集函数将作用于整个查询结果对查询结果分组后，集函数将分别作用于每个组使用GROUPBY子句分组[例30]求各个课程号及相应的选课人数。SELECTCno,COUNT(Sno)FROMSCGROUPBYCno;结果CnoCOUNT(Sno)122234344433548对查询结果分组（续）GROUPBY子句的作用对象是查询的中间结果表分组方法：按指定的一列或多列值分组，值相等的为一组使用GROUPBY子句后，SELECT子句的列名列表中只能出现分组属性和集函数使用HAVING短语筛选最终输出结果[例31]查询选修了3门以上课程的学生学号。SELECTSnoFROMSCGROUPBYSnoHAVINGCOUNT(*)3;思考题:查询选修了3门以上（包括3门）课程及平均分在80分以上的学生学号及平均成绩。SELECTSno,avg(Grade)FROMSCGROUPBYSnoHAVINGCOUNT(*)=3andavg(Grade)80;例题[例32]*查询有3门以上课程是90分以上的学生的学号及（90分以上的）课程数SELECTSno,COUNT(*)FROMSCWHEREGrade=90GROUPBYSnoHAVI