第三章 SQL(3)数据查询-连接查询.ppt

第三章SQL(3)数据查询-连接查询2023年4月19日 2主要内容主要内容3.1 SQL概述概述3.2 学生学生-课程数据库课程数据库3.3 数据定义数据定义3.4 数据查询数据查询3.5 数据更新数据更新3.6 视图视图2023年4月19日 32023年4月19日 42023年4月19日 52023年4月19日 6广义笛卡尔积广义笛卡尔积SELECT *FROM student,sc笛卡尔积中的笛卡尔积中的数据都有现实数据都有现实意义吗？意义吗？2023年4月19日 7由笛卡尔积到由笛卡尔积到连接查询连接查询SELECT *FROM student,scWHERE student.sno=sc.sno注意：连接查询相当于笛卡尔积上做选择注意：连接查询相当于笛卡尔积上做选择思考：元组数量思考：元组数量由什么决定？由什么决定？2023年4月19日 8一、等值与非等值连接查询一、等值与非等值连接查询 例例 查询学生成绩信息，包括学号，姓名，课程号，成绩。查询学生成绩信息，包括学号，姓名，课程号，成绩。SELECT student.sno,sname,cno,grade FROM student,scWHERE student.sno=sc.sno注意：选择查询结果希望出现的列，由于注意：选择查询结果希望出现的列，由于sno在两个表中都在两个表中都有，所以要指明来自哪个表，对于内连接任意一个均可。有，所以要指明来自哪个表，对于内连接任意一个均可。2023年4月19日 9n关于连接查询条件：关于连接查询条件：连接查询中用来连接两个表的条件称为连接条件或连接谓词，连接查询中用来连接两个表的条件称为连接条件或连接谓词，其一般格式为：其一般格式为：.比较运算符主要有：比较运算符主要有：=、=、=、!=(或或)有效的连接查询条件：有效的连接查询条件：.和和 .是兼容类型是兼容类型有意义的连接查询条件：有意义的连接查询条件：.有意义有意义2023年4月19日 10SELECT student.sno,sname,cno,grade FROM student,scWHERE student.sno=sc.snoSQL_92的写法：的写法：SELECT student.sno,sname,cno,grade FROM student inner join sc on student.sno=sc.sno连接查询的多种语法：连接查询的多种语法：2023年4月19日 11SELECT sno,o,cname,grade FROM course,scWHERE o=oSELECT sno,o,cname,grade FROM course inner join sc on o=o课堂练习课堂练习 ：n查询学生成绩信息，包括学生的学号、课程号，课程查询学生成绩信息，包括学生的学号、课程号，课程名，考试成绩。名，考试成绩。要求：分别用两种语法表达。要求：分别用两种语法表达。2023年4月19日 12注意：注意：索引对于连接查询的作用索引对于连接查询的作用自然连接自然连接2023年4月19日 13二、复合条件连接查询二、复合条件连接查询 例例 查询查询 CSCS系的学生的成绩信息，包括学号，姓名，课程系的学生的成绩信息，包括学号，姓名，课程号，成绩。号，成绩。SELECT student.sno,sname,cno,grade FROM student,scWHERE student.sno=sc.sno and sdept=CSSQL_92的写法：的写法：SELECT student.sno,sname,cno,grade FROM student inner join sc on student.sno=sc.snoWHERE sdept=CS2023年4月19日 14连接查询中给表名指定别名连接查询中给表名指定别名n查询查询 CSCS系的学生的成绩信息，包括学号，姓名，课程号，系的学生的成绩信息，包括学号，姓名，课程号，成绩。成绩。SQL_92的写法：的写法：SELECT student.sno,sname,cno,grade FROM student inner join sc on student.sno=sc.snoWHERE sdept=CS指定别名后：指定别名后：SELECT S.sno,sname,cno,grade FROM student S inner join sc P on S.sno=P.snoWHERE sdept=CS2023年4月19日 15多表查询多表查询SELECT SELECT student.snostudent.sno,sname,sname,o,cname,grade,cname,grade FROM student FROM student inner joininner join sc on student.sno=sc.sno sc on student.sno=sc.sno inner joininner join course on o=o course on o=o或者或者SELECT SELECT student.snostudent.sno,sname,sname,o,cname,grade,cname,grade FROM student,FROM student,sc,coursesc,courseWHERE student.sno=sc.sno and o=oWHERE student.sno=sc.sno and o=o查询选修了课程的学生的学号、姓名、课程号、课程名及查询选修了课程的学生的学号、姓名、课程号、课程名及考试成绩。考试成绩。2023年4月19日 16n查询查询 CS系的选修了课程的学生的学号、姓名、课程号、课系的选修了课程的学生的学号、姓名、课程号、课程名及考试成绩。程名及考试成绩。SELECT student.sno,sname,o,cname,grade SELECT student.sno,sname,o,cname,grade FROM student inner join sc on student.sno=sc.sno FROM student inner join sc on student.sno=sc.sno inner join course on o=o inner join course on o=oWHERE sdept=CSWHERE sdept=CS2023年4月19日 17三、自身连接查询三、自身连接查询查询出生日期比学号为查询出生日期比学号为s001的学生大的学生学号和姓的学生大的学生学号和姓名名:学号学号姓名姓名出生日期出生日期 S001S001王芳王芳1980-6-31980-6-3S002S002唐广书唐广书1981-9-221981-9-22S003S003李霞李霞1978-8-11978-8-1S004S004王忠强王忠强1979-9-51979-9-5S005S005李明李明1982-12-311982-12-31学生基本信息表学生基本信息表2023年4月19日 18SELECT M.学号，学号，M.姓名姓名FROM 学生基本信息表学生基本信息表 M,学生基本信息表学生基本信息表 PWHERE M.出生日期出生日期P.出生日期出生日期 and p.学号学号=s001MP学号学号姓名姓名出生日期出生日期 S001S001王芳王芳1980-6-31980-6-3S002S002唐广书唐广书1981-9-221981-9-22S003S003李霞李霞1978-8-11978-8-1S004S004王忠强王忠强1979-9-51979-9-5S005S005李明李明1982-12-311982-12-31学号学号姓名姓名出生日期出生日期 S001S001王芳王芳1980-6-31980-6-3S002S002唐广书唐广书1981-9-221981-9-22S003S003李霞李霞1978-8-11978-8-1S004S004王忠强王忠强1979-9-51979-9-5S005S005李明李明1982-12-311982-12-312023年4月19日 19Select M.学号，学号，M.姓名姓名From 学生基本信息表学生基本信息表 M,学生基本信息表学生基本信息表 PWhere M.出生日期出生日期P.出生日期出生日期 and p.学号学号=s001M筛选后的筛选后的P学号学号姓名姓名出生日期出生日期 S001S001王芳王芳1980-6-31980-6-3S002S002唐广书唐广书1981-9-221981-9-22S003S003李霞李霞1978-8-11978-8-1S004S004王忠强王忠强1979-9-51979-9-5S005S005李明李明1982-12-311982-12-31学号学号姓名姓名出生日期出生日期 S001S001王芳王芳1980-6-31980-6-32023年4月19日 20 SELECT SELECT S1S1.Sno.Sno，S1S1.Sname.Sname，S1S1.Sdept.Sdept FROM Student FROM Student S1S1，Student Student S2S2 WHERE WHERE S1S1.Sdept=.Sdept=S2S2.Sdept AND .Sdept AND S2S2.Sname=.Sname=刘晨刘晨；学号学号姓名姓名性别性别年龄年龄所在系所在系200215121200215121李勇李勇男男2121cscs200215122200215122刘晨刘晨女女2020cscs200215123200215123王敏王敏女女2121is is练习练习1：查询与：查询与“刘晨刘晨”在同一个系学习的学生。在同一个系学习的学生。用自身连接实现本查询：用自身连接实现本查询：学号学号姓名姓名性别性别年龄年龄所在系所在系200215121200215121李勇李勇男男2121cscs200215122200215122刘晨刘晨女女2020cscs200215123200215123王敏王敏女女2121is iss1s1s2s22023年4月19日 21习题习题2：查询课程信息，包括课程号，课程名，先修课：查询课程信息，包括课程号，课程名，先修课程号，先修课程名称。程号，先修课程名称。Select o,ame,s.cpno,ameFrom course s inner join course p on s.cpno=o演示演示2023年4月19日 22四、外连接查询四、外连接查询学号学号姓名姓名性别性别年龄年龄00010001李晓红李晓红女女212100020002王玲王玲女女202000030003张建国张建国男男2121学生信息表学生信息表课程号课程号课程名课程名学分学分001001操作系统操作系统3 3002002多媒体多媒体3 3003003数据库数据库3 3004004网络网络2 2课程信息表课程信息表学号学号课程号课程号成绩成绩000100010010018585000100010020029090000200020030038989000200020010018080学生选课学生选课2023年4月19日 23查询查询选修了课程的学生选修了课程的学生的学号、姓名、课程号及考试成绩：的学号、姓名、课程号及考试成绩：SELECT M.学号，姓名，课程号，成绩学号，姓名，课程号，成绩FROM 学生信息表学生信息表 M inner join 学生选课表学生选课表 P ON M.学号学号=P.学号学号ORDER BY M.学号学号,课程号课程号 学号学号姓名姓名课程号课程号成绩成绩00010001李晓红李晓红001001858500010001李晓红李晓红002002909000020002王玲王玲001001808000020002王玲王玲0030038989思考：为什么思考：为什么没有学生没有学生0003的相关信息的相关信息2023年4月19日 24查询学生选课信息（学生的学号、姓名、课程号及考试成绩）：查询学生选课信息（学生的学号、姓名、课程号及考试成绩）：SELECT M.学号，姓名，课程号，成绩学号，姓名，课程号，成绩FROM 学生信息表学生信息表 M left outer join 学生选课表学生选课表 P ON M.学号学号=P.学号学号ORDER BY M.学号学号,课程号课程号 学号学号姓名姓名课程号课程号成绩成绩00010001李晓红李晓红001001858500010001李晓红李晓红002002909000020002王玲王玲001001808000020002王玲王玲003003898900030003张建国张建国NULLNULLNULLNULL2023年4月19日 25学号学号姓名姓名性别性别年龄年龄00010001李晓红李晓红女女212100020002王玲王玲女女202000030003张建国张建国男男2121学生信息表学生信息表学号学号课程号课程号成绩成绩000100010010018585000100010020029090000200020030038989000200020010018080学生选课学生选课00010001李晓红李晓红0010018585学号学号姓名姓名课程号课程号成绩成绩00010001李晓红李晓红00200290902023年4月19日 26学号学号姓名姓名性别性别年龄年龄00010001李晓红李晓红女女212100020002王玲王玲女女202000030003张建国张建国男男2121学生信息表学生信息表学号学号课程号课程号成绩成绩000100010010018585000100010020029090000200020030038989000200020010018080学生选课学生选课学号学号姓名姓名课程号课程号成绩成绩00010001李晓红李晓红001001858500010001李晓红李晓红002002909000020002王玲王玲003003898900020002王玲王玲00100180802023年4月19日 27学号学号姓名姓名性别性别年龄年龄00010001李晓红李晓红女女212100020002王玲王玲女女202000030003张建国张建国男男2121学生信息表学生信息表学号学号课程号课程号成绩成绩000100010010018585000100010020029090000200020030038989000200020010018080学生选课学生选课学号学号姓名姓名课程号课程号成绩成绩00010001李晓红李晓红001001858500010001李晓红李晓红002002909000020002王玲王玲001001808000020002王玲王玲003003898900030003张建国张建国NULLNULLNULLNULL2023年4月19日 28查询学生选课信息（学生的学号、姓名、课程号及考试成绩）：查询学生选课信息（学生的学号、姓名、课程号及考试成绩）：SELECT P.学号，姓名，课程号，成绩学号，姓名，课程号，成绩FROM 学生信息表学生信息表 M left outer join 学生选课表学生选课表 P ON M.学号学号=P.学号学号ORDER BY M.学号学号,课程号课程号 学号学号姓名姓名课程号课程号成绩成绩00010001李晓红李晓红001001858500010001李晓红李晓红002002909000020002王玲王玲001001808000020002王玲王玲0030038989NULLNULL张建国张建国NULLNULLNULLNULL思考：如果思考：如果select语句中是语句中是P.学号而不是学号而不是M.学号会怎样？学号会怎样？2023年4月19日 29查询课程被选修情况（课程号、课程名、学生的学号、成绩）：查询课程被选修情况（课程号、课程名、学生的学号、成绩）：SELECT P.课程号，课程名，课程号，课程名，学号，成绩学号，成绩FROM 学生选课表学生选课表 M right outer join 课程信息表课程信息表 P ON M.课程号课程号=P.课程号课程号ORDER BY P.课程号课程号,学号学号 课程号课程号课程名课程名学号学号成绩成绩001001操作系统操作系统000100018585001001操作系统操作系统000200028080002002多媒体多媒体000100019090003003数据库数据库000200028989004004网络网络NULLNULLNULLNULL2023年4月19日 30外连接查询外连接查询_FULL OUTER JOIN 学校数据学校数据库库中有学生和宿舍两个关系：中有学生和宿舍两个关系：学生（学号，姓名，学生（学号，姓名，床位号床位号）宿舍（床位号，楼号，房宿舍（床位号，楼号，房间间号）号）假设有的学生不住宿，床位也可能空闲。如果要列出假设有的学生不住宿，床位也可能空闲。如果要列出所有学生住宿和宿舍分配的情况，包括没有住宿的学生和所有学生住宿和宿舍分配的情况，包括没有住宿的学生和空闲的床位，则应执行空闲的床位，则应执行FULL OUTER JOIN Select 学生学生.学号学号,姓名姓名,楼号楼号,房房间间号号,床位号床位号From 学生学生 FULL OUTER JOIN 宿舍宿舍 on 学生学生.床位号床位号=宿舍宿舍.床位号床位号2023年4月19日 31SELECT s.sno,sname,cno,grade FROM student s left outer join sc p on s.sno=p.sno课堂练习课堂练习 ：n查询学生选课信息（学生的学号、姓名、课程号及考试成查询学生选课信息（学生的学号、姓名、课程号及考试成绩）绩）2023年4月19日 32连接查询总结连接类型连接类型Inner joinInner joinInner joinInner joinouter joinouter joinLeft outer joinLeft outer joinRight outer joinRight outer joinFull outer joinFull outer join2023年4月19日 33连接与集合查询连接与集合查询 查询平均成绩在查询平均成绩在8585分及其以上的学生的学号，姓名和平均成分及其以上的学生的学号，姓名和平均成绩，按照平均成绩的降序排列。绩，按照平均成绩的降序排列。SELECT s.sno,sname,AVG(grade)平均成绩平均成绩 FROM student S inner join sc P on s.sno=p.sno GROUP BY s.sno,sname HAVING AVG(grade)=85 ORDER BY 平均成绩平均成绩 DESC；演示演示2023年4月19日 34内容回顾内容回顾以上详细讲解了连接查询：以上详细讲解了连接查询：一、等值与非等值连接查询一、等值与非等值连接查询二、复合条件连接查询二、复合条件连接查询三、自身连接查询三、自身连接查询四、外连接四、外连接2023年4月19日 35作业：作业：实验：实验：教材以及讲义中的例题教材以及讲义中的例题实验实验12 12 简单数据查询简单数据查询实现查询实现查询:2.(7)(10):2.(7)(10)实验实验13 13 复杂数据查询复杂数据查询实现查询实现查询:2.:2.聚集查询聚集查询 2023年4月19日 36此此课件下件下载可自行可自行编辑修改，修改，仅供参考！供参考！感感谢您的支持，我您的支持，我们努力做得更好！努力做得更好！谢谢!