《数据库原理及应用第四章SQL示例》由会员分享，可在线阅读，更多相关《数据库原理及应用第四章SQL示例（58页珍藏版）》请在金锄头文库上搜索。
1、查询指定列查询指定列例1查询全体学生的学号与姓名。SELECTSno,SnameFROMStudent；例2查询全体学生的姓名、学号、所在系。SELECTSname,Sno,SdeptFROMStudent;查询全部列查询全部列例3查询全体学生的详细记录。SELECTSno,Sname,Ssex,Sage,SdeptFROMStudent;或SELECT*FROMStudent;查询经过计算的值查询经过计算的值 SELECT子句的为表达式n算术表达式n字符串常量n函数n列别名n等查询经过计算的值查询经过计算的值例4*查全体学生的姓名及其出生年份。selectSname,2009-Sagefro
2、mstudent;输出结果：Sname2009-Sage-李勇1976刘晨1977王名1978张立1978查询经过计算的值查询经过计算的值例5*查询全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名。SELECT Sname,Year of Birth: ,2009-Sage,LOWER(Sdept)FROM Student; 例题（续）例题（续）输出结果：SnameYearofBirth:2009-SageLOWER(Sdept)-李勇YearofBirth:1976cs刘晨YearofBirth:1977is王名YearofBirth:1978ma张立YearofBirth:19
3、77is选择表中的若干元组选择表中的若干元组 n消除取值重复的行n查询满足条件的元组1. 消除取值重复的行消除取值重复的行n在SELECT子句中使用DISTINCT短语假设SC表中有下列数据SnoCnoGrade-9500119295001285950013889500229095002380ALL 与与 DISTINCT 例6查询选修了课程的学生学号。(1)SELECTSnoFROMSC;结果：Sno-9500195001950019500295002例题（续）例题（续）(2)SELECTDISTINCTSnoFROMSC;结果：Sno-9500195002例题（续）例题（续）n注意DIST
4、INCT短语的作用范围是所有目标列例：查询选修课程的各种成绩错误的写法SELECTDISTINCTCno,DISTINCTGradeFROMSC;正确的写法SELECTDISTINCTCno,GradeFROMSC;查询满足条件的元组查询满足条件的元组WHERE子句常用的查询条件子句常用的查询条件(1) 比较大小比较大小在WHERE子句的中使用比较运算符n=，=，=，!=或，!，!，n逻辑运算符NOT+比较运算符例7查询所有年龄在20岁以下的学生姓名及其年龄。SELECTSname,SageFROMStudentWHERESage=20;(2) 确定范围确定范围n使用谓词BETWEENANDN
5、OTBETWEENAND例8查询年龄在2023岁（包括20岁和23岁）之间的学生的姓名、系别和年龄。SELECTSname,Sdept,SageFROMStudentWHERESageBETWEEN20AND23;例题（续）例题（续）例9查询年龄不在2023岁之间（包括20岁和23岁）的学生姓名、系别和年龄。SELECTSname,Sdept,SageFROMStudentWHERESageNOTBETWEEN20AND23;(3) 确定集合确定集合使用谓词IN,NOTIN：用逗号分隔的一组取值例10查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别。SELECTSnam
6、e,SsexFROMStudentWHERESdeptIN(IS,MA,CS);(3) 确定集合确定集合例11查询既不是信息系、数学系，也不是计算机科学系的学生的姓名和性别。SELECTSname,SsexFROMStudentWHERESdeptNOTIN(IS,MA,CS);(4) 字符串匹配字符串匹配nNOT LIKE ESCAPE ：指定匹配模板：指定匹配模板 匹配模板：固定字符串或含匹配模板：固定字符串或含通配符通配符的字符串的字符串 当匹配模板为固定字符串时，当匹配模板为固定字符串时， 可以用可以用 = 运算符取代运算符取代 LIKE 谓词谓词 用用 != 或或 运算符取代运算符取
7、代 NOT LIKE 谓词谓词通配符通配符w% (百分号百分号) 代表任意长度（长度可以为代表任意长度（长度可以为0）的字符串）的字符串n例：a%b表示以a开头，以b结尾的任意长度的字符串。如acb，addgb，ab等都满足该匹配串_ (下横线下横线) 代表任意单个字符代表任意单个字符n例：a_b表示以a开头，以b结尾的长度为3的任意字符串。如acb，afb等都满足该匹配串ESCAPE 短语：短语：n*当用户要查询的字符串本身就含有%或_时，要使用ESCAPE短语对通配符进行转义。例题例题1)匹配模板为固定字符串例12查询学号为95001的学生的详细情况。SELECT*FROMStudentW
8、HERESnoLIKE95001;等价于：SELECT*FROMStudentWHERESno=95001;例题（续）例题（续）2)匹配模板为含通配符的字符串例13查询所有姓刘学生的姓名、学号和性别。SELECTSname,Sno,SsexFROMStudentWHERESnameLIKE刘%;SELECT*FROM信息表WHERE姓名like%红%;SELECT*from信息表where籍贯like%山%;SELECT*from信息表where民族notlike汉族;例题（续）例题（续）匹配模板为含通配符的字符串（续）例14查询姓欧阳且全名为三个汉字的学生的姓名。SELECTSnameFRO
9、MStudentWHERESnameLIKE欧阳_;SELECT*FROM信息表WHERE姓名LIKE_阳%;例题（续）例题（续）匹配模板为含通配符的字符串（续）例15查询名字中第2个字为阳字的学生的姓名和学号。SELECTSname,SnoFROMStudentWHERESnameLIKE_阳%;例题（续）例题（续）匹配模板为含通配符的字符串（续）例16查询所有不姓刘的学生姓名。SELECTSname,Sno,SsexFROMStudentWHERESnameNOTLIKE刘%;例题（续）例题（续）3)使用换码字符将通配符转义为普通字符例17*查询DB_Design课程的课程号和学分。SEL
10、ECTCno,CcreditFROMCourseWHERECnameLIKEDB_DesignESCAPE;例题（续）例题（续）使用换码字符将通配符转义为普通字符(续)例18*查询以DB_开头，且倒数第3个字符为i的课程的详细情况。SELECT*FROMCourseWHERECnameLIKEDB_%i_ESCAPE;(5) 涉及空值的查询涉及空值的查询n使用谓词ISNULL或ISNOTNULLn“ISNULL”不能用“=NULL”代替例19某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号。SELECTSno,CnoFROMSCWHEREG
11、radeISNULL;例题例题(续续) 例20查所有有成绩的学生学号和课程号。SELECTSno,CnoFROMSCWHEREGradeISNOTNULL;(6) 多重条件查询多重条件查询用逻辑运算符AND和OR来联结多个查询条件nAND的优先级高于ORn可以用括号改变优先级可用来实现多种其他谓词nNOTINnNOTBETWEENAND例题例题例21查询计算机系年龄在20岁以下的学生姓名。SELECTSnameFROMStudentWHERESdept=CSANDSage=20ANDSage=23;三、对查询结果排序三、对查询结果排序 使用ORDERBY子句n可以按一个或多个属性列排序n升序：
12、ASC；降序：DESC；缺省值为升序缺省值为升序当排序列含空值时nASC：排序列为空值的元组最后显示nDESC：排序列为空值的元组最先显示对查询结果排序（续）对查询结果排序（续） 例24查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列。SELECTSno,GradeFROMSCWHERECno=3ORDERBYGradeDESC;查询结果查询结果SnoGrade-9501095024950079295003829501082950097595014619500255对查询结果排序（续）对查询结果排序（续） 例25查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年
13、龄降序排列。SELECT*FROMStudentORDERBYSdept,SageDESC;四、使用集函数四、使用集函数 5类主要集函数n计数COUNT（DISTINCT|ALL*）COUNT（DISTINCT|ALL）n计算总和SUM（DISTINCT|ALL）n计算平均值AVG（DISTINCT|ALL）使用集函数（续）使用集函数（续） 求最大值MAX（DISTINCT|ALL）求最小值MIN（DISTINCT|ALL）DISTINCT短语：在计算时要取消指定列中的重复值ALL短语：不取消重复值ALL为缺省值使用集函数使用集函数 （续）（续）例26查询学生总人数。SELECTCOUNT(*
14、)FROMStudent;例27查询选修了课程的学生人数。SELECTCOUNT(DISTINCTSno)FROMSC;注：用DISTINCT以避免重复计算学生人数使用集函数使用集函数 （续）（续）例28计算1号课程的学生平均成绩。SELECT AVG(Grade) FROM SC WHERECno=1;例29查询选修1号课程的学生最高分数。SELECT MAX(Grade) FROM SC WHERECno=1;五、对查询结果分组五、对查询结果分组 使用GROUPBY子句分组细化集函数的作用对象n未对查询结果分组，集函数将作用于整个查询结果n对查询结果分组后，集函数将分别作用于每个组使用使用
15、GROUP BY子句分组子句分组例30求各个课程号及相应的选课人数。SELECTCno,COUNT(Sno)FROMSCGROUPBYCno;结果CnoCOUNT(Sno)122234344433548对查询结果分组对查询结果分组 （续）（续）nGROUPBY子句的作用对象是查询的中间结果表n分组方法：按指定的一列或多列值分组，值相等的为一组n使用GROUPBY子句后，SELECT子句的列名列表中只能出现分组属性和集函数使用使用HAVING短语筛选最终输出结果短语筛选最终输出结果例31查询选修了3门以上课程的学生学号。SELECTSnoFROMSCGROUPBYSnoHAVINGCOUNT(*
16、)3;思思考考题题:查查询询选选修修了了3门门以以上上（包包括括3门门）课课程程及及平平均均分分在在80分分以以上上的的学学生生学学号号及及平均成绩。平均成绩。SELECTSno,avg(Grade)FROMSCGROUPBYSnoHAVINGCOUNT(*)=3andavg(Grade)80;例题例题例32*查询有3门以上课程是90分以上的学生的学号及（90分以上的）课程数SELECTSno,COUNT(*)FROMSCWHEREGrade=90GROUPBYSnoHAVINGCOUNT(*)=3;使用使用HAVING短语筛选最终输出结果短语筛选最终输出结果n只有满足HAVING短语指定条件
17、的组才输出nHAVING短语与WHERE子句的区别：作用对象不同nWHERE子句作用于基表或视图，从中选择满足条件的元组。nHAVING短语作用于组，从中选择满足条件的组。插入数据插入数据n语句格式INSERTINTO(，)VALUES(，)n功能将新元组插入指定表中。插入数据插入数据例1将一个新学生记录（学号：95020；姓名：陈冬；性别：男；所在系：IS；年龄：18岁）插入到Student表中。INSERTINTOStudent(Sno,Sname,Ssex,Sdept,Sage)VALUES(95020,陈冬,男,IS,18);注意注意:若未列出属性名若未列出属性名,则数据顺序必须与表中
18、属性顺序一则数据顺序必须与表中属性顺序一致致nINSERTINTOStudentnVALUES(95020,陈冬,男,18,IS)修改数据修改数据n语句格式UPDATESET=，=WHERE；n功能修改指定表中满足WHERE子句条件的元组1. 修改某一个元组的值修改某一个元组的值例1将学生95001的年龄改为22岁。UPDATEStudentSETSage=22WHERESno=95001；2. 修改多个元组的值修改多个元组的值例2将所有学生的年龄增加1岁。UPDATEStudentSETSage=Sage+1；删除数据删除数据DELETEFROMWHERE；功能w删除指定表中满足WHERE子
19、句条件的元组WHERE子句w指定要删除的元组w缺省表示要修改表中的所有元组1. 删除某一个元组的值删除某一个元组的值例1删除学号为95019的学生记录。DELETEFROMStudentWHERESno=95019;2. 删除多个元组的值删除多个元组的值例2删除2号课程的所有选课记录。DELETEFROMSCWHERECno=2；例3删除所有的学生选课记录。DELETEFROMSC；注意注意:做删除操作时需谨慎做删除操作时需谨慎,避免误删避免误删!视视 图图视图的特点视图的特点n虚表虚表，是从一个或几个基本表（或视图）导出的表n只存放视图的定义，不会出现数据冗余n基表中的数据发生变化，从视图中查询出的数据也随之改变建立视图建立视图n语句格式CREATEVIEW(，)ASWITHCHECKOPTION；例例建立信息系学生的视图。CREATEVIEWIS_StudentASSELECTSno,Sname,SageFROMStudentWHERESdept=IS;
