北京工业大学研究生数据库复试笔试课件chap5-关系数据库标准语言SQL-175

1第5章关系数据库标准语言SQL5.1SQL概述特点：集DDL、DML、DCL于一体非过程化集合操作易学易用与三级模式关系25.2查询所用数据库:1)学生-课程数据库Students(Sno,Sname,Ssex,Sage,Sdept)Courses(Cno,Cname,Cpno,Ccredit)SC(Sno,Cno,Grade)32)电影数据库Movies(title,year,length,genre,studioName,producerC#)StarsIn(movieTitle,movieYear,starName)MovieStars(name,address,gender,birthdate)MovieExecs(name,address,cert#,netWorth)Studios(name,address,presC#)45.2.1单表查询*选择若干列--------SQL投影例:查找学生的学号和姓名SELECTSno,SnameFROMStudents例:查找学生的姓名、学号和所在系SELECTSname,Sno,SdeptFROMStudents5例:查找学生的相关信息SELECT*FROMStudents例:查找学生的姓名和出生年份SELECTSname,2010-SageFROMStudents6去掉重复元组例:查找选课学生的学号SELECTSno等价于SELECTALLSnoFROMSCFROMSCSELECTDISTINCTSnoFROMSC7*查询满足条件的元组----------SQL选择WHERE子句运算符/关键字比较=,,,=,=,,!,!确定范围[NOT]BETWEEN...AND...确定集合[NOT]IN字符匹配[NOT]LIKE空值IS[NOT]NULL逻辑ANDORNOT串的拼接||P146+SQLServer2000以上8例:查找计算机系学生的名字SELECTSnameFROMStudentsWHERESdept=‘计算机’9例:查找年龄小于19的学生的姓名和年龄SELECTSname,SageFROMStudentsWHERESage19NOTSage=1910例:查找有过不及格成绩的学生的学号SELECTDISTINCTSnoFROMSCWHEREGrade6011P144例6.1：查找Disney公司，在1990年制作的所有电影的有关信息SELECT*FROMMoviesWHEREstudioName=‘Disney’ANDyear=199012例:查找年龄在20到23之间的学生的姓名,所在系和年龄SELECTSname,Sdept,SageFROMStudentsWHERESageBETWEEN20AND23例:查找年龄不在20到23之间的学生的姓名,所在系和年龄SELECTSname,Sdept,SageFROMStudentsWHERESageNOTBETWEEN20AND2313例:查找计算机系、经管系的学生的姓名和性别SELECTSname,SsexFROMStudentsWHERESdeptIN(‘计算机’,’经管’)不用IN?例：查找非计算机系非经管系的学生的姓名和性别SELECTSname,SsexFROMStudentsWHERESdeptNOTIN(‘计算机’,’经管’)不用IN?14字符匹配[NOT]LIKE匹配串[ESCAPE换码字符]通配符：%任意长度为n的字符串n=0_(下划线)任意字符[]指定范围内的任意单个字符[^]不在指定范围内的任意单个字符15例：LIKE‘%been%’LIKE‘Ma%’LIKE‘[CK]%’第1个是C或KLIKE‘[^A-D]%’第1个不是A-D之间LIKE‘%ea_’LIKE‘_en’16P148换码字符/转义字符LIKE‘X%%X%’ESCAPE‘X’%……%17P148例6.7查找电影名以‘Star’开头后面紧跟4个字母的所有电影名SELECTtitleFROMMoviesWHEREtitleLIKE‘Star____’18例：查找没有成绩的选课记录中的学号和课程号SELECTSno,CnoFROMSCWHEREGradeISNULL例：查找已有成绩的选课记录中的学号和课程号SELECTSno,CnoFROMSCWHEREGradeISNOTNULL19例：查找计算机系年龄小于19的学生的名字SELECTSnameFROMStudentsWHERESdept=‘计算机’ANDSage19例：查找计算机系或者年龄小于19的学生的名字SELECTSnameFROMStudentsWHERESdept=‘计算机’ORSage1920例：查找计算机系,经管系或建筑系的学生的名字和性别SELECTSname,SsexFROMStudentsWHERESdept=‘计算机’ORSdept=‘经管’ORSdept=‘建筑’21P145例6.2查找Disney公司1990年拍摄的影片的片名和长度SELECTtitle,lengthFROMMoviesWHEREstudioName=‘Disney’ANDyear=‘1990’22例6.3同6.2片名的列标题改为name片长的列标题改为durationSELECTtitleASname,lengthASdurationFROMMoviesWHEREstudioName=‘Disney’ANDyear=‘1990’SQLServer2000:列名AS别名列名别名别名=列名23例6.4进一步，片长以小时计，列标题为lengthInHoursSELECTtitleASname,length*0.016667ASlengthInHoursFROMMoviesWHEREstudioName=‘Disney’ANDyear=‘1990’24P145例6.5进一步改，片长以小时计，且在每一个片长后加‘hrs.’-----加一个新的列，每行都是‘hrs.’SELECTtitle,length*0.016667ASlength,‘hrs.’ASinHoursFROMMoviesWHEREstudioName=‘Disney’ANDyear=199025P147例：查找MGM公司拍摄的:1970年以后或片长小于90分钟的所有电影的名字SELECTtitleFROMMoviesWHERE(year1970ORlength90)ANDstudioName=‘MGM’优先级26日期时间的比较:第一个日期早于第二个小于（）同一天内，第一个时间早于第二个小于（）SQLServer2000：DATETIME‘6/15/2000’‘10:20:10am’‘6/15/200010:20:10am’‘6/15/200013:00:10’27P151例6.11查询Disney公司1990年拍摄的影片信息，以片长的升序排列，同样片长的，以片名的字母序排列。SELECT*FROMMoviesWHEREstudioName=‘Disney’ANDyear=1990ORDERBYlength,titleORDERBY3,128例：查找选修3号课程的学生的学号及相应成绩,以成绩降序排列SELECTSno,GradeFROMSCWHERECno=‘3’ORDERBYGradeDESC29例：查找学生信息以所在系的升序,年龄降序排列SELECT*FROMStudentsORDERBYSdept,SageDESCSdept升序305.2.2多表查询*多表查询的方法之一：连接查询-----FROM子句列出多个关系连接条件的一般格式：关系名1.属性名1比较运算符关系名2.属性名2比较运算符=,,,=,=,连接操作选择满足条件的行31P153例6.12查找影片“StarWars”的制片人的姓名Movies(title,year,length,genre,studioName,producerC#)MovieExecs(name,address,cert#,netWorth)SELECTnameFROMMovies,MovieExecsWHEREtitle=‘StarWars’ANDproducerC#=cert#32P153例6.13找所有具有相同地址的影星和高级主管的组合MovieStars(name,address,gender,birthdate)MovieExecs(name,address,cert#,netWorth)SELECTMovieStars.name,MovieExecs.nameFROMMovieStars,MovieExecsWHEREMovieStars.address=MovieExecs.address消除属性的二义性表名做前缀33例：查询每个学生及其选课情况SELECTStudents.*,SC.*等值连接与等值连接的区别？FROMStudents,SCWHEREStudents.Sno=SC.Sno笛卡尔积：SELECTStudents.*,SC.*FROMStudents,SC34例：查询每个学生及其选课情况SELECTStudents.*,SC.Cno,SC.GradeFROMStudents,SCWHEREStudents.Sno=SC.Sno自然连接?没有SC.Sno？35例：查询每门课的课程号及其先修课的先修课的课程号SELECTFIRST.Cno,SECOND.Cpno别名FROMCoursesFIRST,CoursesSECONDWHEREFIRST.Cpno=SECOND.Cno自连接36例：查询选修2号课程且成绩在90分以上的学生的学号与姓名SELECTStudents.Sno,SnameFROMStudents,SCWHEREStudents.Sno=SC.SnoANDCno=‘2’ANDGrade9037例：查询每个学生选修的课程名及其成绩,同时输出学号、姓名SELECTStudents.Sno,Sname,Cname,GradeFROMStudents,SC,CoursesWHEREStudents.Sno=SC.SnoANDSC.Cno=Courses.Cno38例：查询计算机系每个学生选修的课程名及其成绩SELECTStudents.Sno,Sname,Cname,GradeFROMStudents,SC,CoursesWHEREStudents.Sno=SC.SnoANDSC.Cno=Courses.CnoANDSdept=‘计算机’395.2.3使用聚集函数AVG([ALL|DISTINCT]列名)COUNT([ALL|DISTINCT]列名)非空COUNT(*)统计元组个数MAX(列名)MIN(列名)SUM([ALL|DISTINCT]列名)SQLServer2000列名-----表达式DISTINCT对不重复列统计40P168例6.29找出所有高级主管的平均净资产MovieExecs(name,address,cert#,netWorth)SELECTAVG(netWorth)FROMMovieExecs41例:统计高级主管的人数SELECTCOUNT(*)——统计行数FROMMovieExecs假定重名查找不同名字的个数SELECTCOUNT(DISTINCTname)FROMMovieExecs42例：学生人数SELECTCOUNT(*)FROMStudents例:选课学生人数SELECTCOUNT(DISTINCTSno)FROMSC不加DISTINCT为选课的人次43例：1#课程最高分SELECTMAX(Grade)FROMSCWHERECno=‘1’例：1#课程平均分SELECTAVG(Grade)FROMSCWHERECno=‘1’选出成绩，系统自动完成排列，找寻最大44*对查询结果分组使用聚集函数时，可用GROUPBY子句对查询结果进行分组计算，分组的原则：指定列相等的分为一组HAVING子