1 / 31

复杂查询和视图

复杂查询和视图. 配合实验 18. 复杂查询实例 （实验 18 范例 5 、 6 ）. (5) 找出至少有 2 门课程不及格的学生的学号 SELECT Sno FROM SC ; WHERE Grade<60; GROUP BY Sno ; HAVING COUNT(Cno)>=2. (6) 找出除了“ C 语言”外，平均成绩大于 86 分 的学生姓名和平均成绩 SELECT Sname,AVG(Grade) AS 平均成绩 ; FROM S; JOIN SC on S.Sno=SC.Sno; JOIN C ON SC.Cno=C.Cno;

shandi
Download Presentation

复杂查询和视图

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 复杂查询和视图 配合实验18

  2. 复杂查询实例（实验18范例5、6） (5)找出至少有2门课程不及格的学生的学号 SELECT Sno FROM SC ;WHERE Grade<60;GROUP BY Sno ;HAVING COUNT(Cno)>=2 (6)找出除了“C语言”外，平均成绩大于86分的学生姓名和平均成绩 • SELECT Sname,AVG(Grade) AS 平均成绩;FROM S; • JOIN SC on S.Sno=SC.Sno;JOIN C ON SC.Cno=C.Cno; • WHERE Cname!='C语言' ; • GROUP BY Sname HAVING AVG(Grade)>86

  3. 嵌套查询 • SELECT … FROM … WHERE语句称为查询块 • 嵌套查询：将查询块嵌套在另一个查询块的WHERE 子句或HAVING 短语中 • 意义： • 可以用多个简单查询进行复杂的查询

  4. 嵌套查询实例 • 范例（1）找出选修了1号课程的学生姓名及性别 • 方法一:使用连接查询 SELECT Sname,Ssex FROM S JOIN SC ONS.Sno=SC.Sno; WHERE Cno="1" • 方法二:使用嵌套查询 SELECT Sname, Ssex FROM S WHERE Sno ; IN ( SELECT Sno FROM SC WHERE Cno=‘1’ ) 说明： • 嵌套查询的执行过程是由里向外进行，即每个子查询在上一级查询处理之前求解，子查询的结果用于建立父查询的查找条件。 • 子查询中不能使用ORDER BY子句。 外层查询 (父查询) 注意 连接查询并不一定能用嵌套查询来实现。其次，嵌套查询也并不一定能用连接查询来实现。具体的分析请阅读教材P275 内层查询 (子查询)

  5. 嵌套查询实例 　　请查看实验指导书P163《实验十七（9）》，范例中采用如下命令，但存在不能表达查询要求的问题 SELECT Sno From SC WHERE Cno NOT IN('1','2','5') 　　若使用嵌套查询方法，则命令如下 SELECT Sno FROM S WHERE Sno ;NOT IN(SELECT Sno FROM SC WHERE Cno IN('1','2','5')) • 找出与“陆逸”在同一个系学习的学生的姓名、性别及年龄及系科。 SELECT Sname,Ssex,Sage,sdept FROM S ;WHERE Sdept =‘XXX’ 若已知陆逸同学是XXX系 • =(Select Sdept From S Where Sname=‘陆逸’) • 范例（2）找出没有选修1号课程的学生姓名、性别 • SELECT Sname,Ssex FROM S WHERE Sno ; • NOT IN(SELECT Sno FROM SC WHERE Cno='1') • 范例（3）找出1号、2号或5号三门课程中一门都没有选修的学生学号

  6. 嵌套查询实例 • 范例（4）找出至今没有被人选修过的课程名。 SELECT Cname FROM C WHERE Cno ;NOT IN (SELECT Cno FROM SC)

  7. 视图 在SQL数据库中，视图是从一个或几个基表（或视图）导出的表，它本身不独立存储在数据库中，即数据库中只存放视图的定义而不存放视图对应的数据，这些数据仍然存放在对应的基表中。因此视图是一个虚表，但它在概念上与基表等同，用户可在视图上再定义视图。

  8. 定义视图 • 命令格式 ： CREATE VIEW <视图名>[(<列名1>[, <列名2> … ] )]AS <子查询> 注意: （1）子查询中不应含有ORDER BY子句和DISTINCT短语； （2）标准的SQL语言和 VFP环境下的命令格 式有所不同，请阅读实验指导书Page173的说明。 • 范例(7)建立视图S_DBS，该视图包含选修了“数据库”课程的学生的所有信息。 CREATE SQL VIEW S_DBS AS ;SELECT S.Sno,Sname,Ssex,Sage,Grade FROM S;JOIN SC ON S.Sno=SC.Sno JOIN C ON SC.Cno=C.Cno;WHERE Cname='数据库'

  9. 如何观察视图 • 使用Modify Database 命令可观察视图的存在； • 使用SELECT命令可通过视图进行查询 • 例如，观察刚才建立的S_DBS视图 SELECT * FROM S_DBS

  10. 创建和应用视图实例 范例（8）：建立一个视图S_AVG，它包含每一个学生的学号及所有已考课程的平均成绩。 CREATE SQL VIEW S_AVG AS;SELECT Sno,AVG(Grade) AS AVG_G;FROM SC WHERE Grade IS NOT NULL;GROUP BY Sno 范例（9）通过S_DBS视图查询“数据库”课程成绩在80分以上的学生学号、姓名和性别。 SELECT Sno,Sname,Ssex FROM S_DBS;WHERE Grade>80 范例（10）：通过S_AVG视图查询所有已考课程在80分以上的学生学号。 SELECT Sno FROM S_AVG WHERE AVG_G>80

  11. 删除视图 • 格式 ： DROP VIEW <视图名> • 例：删除视图S_DBS80 DROP VIEW S_DBS80

  12. 查询视图和查询基表的对比 • 建立信息系（IS）全体学生所有资料的视图IS_S。 CREATE SQL VIEW IS_S AS SELECT * FROM S;WHERE Sdept=‘IS’ • 例 ：用上例所建立的视图查询信息系选修了1号课程的学生学号及姓名 SELECT SC.Sno, Sname FROM IS_S JOIN SC; ON IS_S.Sno=SC.Sno where Cno='1' • 转用基表查询 SELECT S.Sno, Sname FROM S JOIN SC; ON S.Sno=SC.Sno WHERE Sdept='IS' AND Cno='1'

  13. 通过视图删除基表中的记录 • 例 ：删除信息系中年龄为20岁的学生记录DELETE FROM IS_S ; WHERE Sage=20 • 转用基表查询 DELETE FROM S; WHERE Sage=20 AND Sdept=‘IS’

  14. 数据更新 修改数据 • 一般格式： UPDATE <表名> SET <列名1>=<表达式1>[, <列名2>=<表达式2> … ] [WHERE <条件>] 用于按条件修改

  15. 修改数据– 实例 • 全部修改：将所有学生的年龄增加1岁 UPDATE S SET Sage=Sage+1 • 条件修改：将学号为99001的学生的年龄改为19岁 UPDATE S SET Sage=19 ; WHERE Sno=‘99001’ • 用子查询来表达修改条件：将“C语言”课程的所有成绩都置为零 UPDATE SC SET Grade=0 ; WHERE Cno IN ( SELECT Cno FROM C; WHERE Cname=‘C语言’)

  16. 教学目的初步了解数据库技术 初步了解关系型数据模型 初步掌握用SQL语言创建库和表 初步掌握用SQL语言对数据库进行查询的方法 数据库有什么用 • 中外合资上海乾通汽车附件有限公司委托开发 《营销管理系统》 • 上海市公安局出入境管理处委托开发 《在沪常住外国人管理信息系统》 • 浙江省平湖市乡镇企业管理局委托开发 《财务统计信息管理系统》 • 台湾华翰电脑公司委托开发 《会计系统》、《企业进销存管理系统》 • 浙江省温州柳市信用社委托开发 《企业信息管理系统》 你达到这个要求了吗？

  17. 实验十八分析 • (1)找出至今没有通过英语四级考试的学生的身份证号码、姓名和所在学校 SELECT Sid,Sname,Suniname FROM STUD WHERE Sid; IN(SELECT Sid FROM EXAM GROUP BY Sid; HAVING MAX(Elisten+Egrammar+Ewrite) < 60 ; OR MAX(Elisten)<20 OR MAX(Egrammar)<15 ; OR MAX(Ewrite)<15) • (2)找出参加过两次英语四级考试的女生身份证号、学号、姓名和所在学校（使用嵌套查询） SELECT Sid,Sno,Sname,Suniname FROM STUD; WHERE Ssex=‘女’; AND Sid IN(SELECT Sid FROM EXAM GROUP BY Sid ; HAVING COUNT(*)=2)

  18. 实验十八分析 (3)找出只参加过一次英语四级考试就通过的学生的身 份证号 SELECT Sid FROM EXAM GROUP BY Sid;HAVING NOT(MAX(Elisten+Egrammar+Ewrite)<60 ;OR MAX(Elisten)<20 OR MAX(Egrammar)<15 ;OR MAX(Ewrite)<15) AND COUNT(*)=1 (4)在STDU表上定义一个由全体“上海大学”学生组成的 视图S_STUD,并观察此视图。 CREATE SQL VIEW S_STUD AS ;SELECT * FROM STUD WHERE Suniname=‘上海大学’ SELECT * FROM S_STUD (5)利用视图S_STUD，找出所有“上海大学”女生的所 有信息 SELECT * FROM S_STUD WHERE Ssex='女'

  19. 实验十八分析 （6）在EXAM表上定义一个参加了“2001年7月份英语四级考试”全体考生的身份证号组成的视图S_EXAM，并观察该视图。 CREATE SQL VIEW S_EXAM AS ; SELECT Sid FROM EXAM WHERE Eyear='2001' AND; Emonth='7' SELECT * FROM S_EXAM （7）利用STDU基表和S_EXAM视图，找出参加了2001年7月份英语四级考试的上海师大女生的身份证号、学 号和姓名。 SELECT Sid,Sno,Sname FROM STUD WHERE Ssex='女'; AND Suniname='上海师大'; AND Sid IN(SELECT Sid FROM S_EXAM)

  20. 实验十八分析 （8）在EXAM表上定义一个英语四级考试已经通过的学生的身份证号组成的视图PASS_EXAM，并观察该视图。 CREATE SQL VIEW PASS_EXAM AS; SELECT Sid FROM EXAM GROUP BY Sid; HAVING NOT(MAX(Elisten+Egrammar+Ewrite)<60 ; OR MAX(Elisten)<20 OR MAX(Egrammar)<15 ; OR MAX(Ewrite)<15) SELECT * FROM S_EXAM （9）利用STDU基表和PASS_EXAM视图，找出至今没有通过英语四级考试的学生的身份证号、姓名和所在学校。 SELECT Sid,Sname,Suniname FROM STUD WHERE Sid; NOT IN(SELECT * FROM PASS_EXAM) （10）利用STDU基表和PASS_EXAM视图，找出只参加一次考试就通过的学生身份证号。 SELECT Sid FROM EXAM WHERE Sid ; IN(SELECT Sid FROM PASS_EXAM); GROUP BY Sid HAVING COUNT(Sid)=1

  21. 6.4.5 数据更新 3. 删除数据 • 一般格式： DELETE FROM <表名> [WHERE <条件>] • 特别注意：在SQL中，不存在所谓的“逻辑删除”和“物理删除”，DELETE语句所做的就是真正的删除 用于按条件删除

  22. 6.4.5 数据更新 3. 删除数据–例 • 全部删除：删除基表SC的所有记录 DELETE FROM SC • 条件删除：将99001号学生的2号课程选课记录删除 DELETE FROM SC WHERE Sno=‘99001’ AND Cno=‘2’ • 用子查询来表达删除条件： DELETE FROM SC WHERE Cno IN ( SELECT Cno FROM C WHERE Cname=‘C语言’)

  23. 6.4.5 数据更新 4.更新数据与数据库的一致性 • 删除某元组，应将其他关系中的相应元组也删除如：将学号99001的学生删除后，相应的选课记录也须删除 • 否则会出现不一致性，数据的参照完整性受到破坏 • 数据库系统一般都自动实体完整性与参照完整性 • 违反实体完整性的操作，系统一般都采用拒绝执行 • 违反参照完整性的操作，提供了有不同的实现策略

  24. 6.4.7 数据控制 1.授权 GRANT SELECT ON TABLE S TO LI 2.收回权限 • REVOKE SELECT ON TABLE S FROM LI • REVOKE INSERT ON TABLE SC FROM ZHU CASCADE

  25. 常见错误 • 多表连接时，对不唯一的属性忘记了表名前缀 SELECT Sno, Sname, Cno, Grade • WHERE条件中出现集函数（统计函数） WHERE MIN (Grade)>80 • 非GROUP BY中的属性出现在SELECT后面，且未用统计函数 SELECT Sno, Cno, AVG(Grade) • 有HAVING短语，但没有GROUP BY子句 • HAVING中出现没有统计函数的属性 • HAVING Cno<> ‘1’ AND AVG(Grade)>80

  26. 视图的作用 视图对重构数据库提供了一定程度的逻辑独立性 • 对不同的用户定义不同的视图 • 提供了对机密数据的安全保护功能 • 根据不同的需求同一数据 制作不同的视图 有查询的功能，又有基表的功能

  27. 6.4.7 数据控制 • 数据控制是系统通过对数据库用户的使用权限加以限制而保证数据安全的重要措施。 • 控制功能主要有： • 数据库的安全性与完整性控制 • 并发控制 • 数据库恢复 • 安全性控制方法： • 将用户对某类数据操作权限”存入数据库系统 • 当有非法用户或非法访问时，系统拒绝执行用户操作

  28. 数据更新 数据更新是指数据的插入、删除、修改数据的操作 1.插入数据 (1) 插入一个元组 • 格式 ： INSERT INTO <表名> [(<属性列1>[, <属性列2> … ] ) ] VALUES (<常量1>[, <常量2> … ])

  29. 6.4.5 数据更新 1.插入数据 (1) 插入一个元组 — 例 • 将学生记录 (99001，张敏，女，20，MA)插入到S表中 INSERT INTO S VALUES (‘99001’， ‘张敏’， ‘女’，20， ‘MA’) 插入所有属性值，表后的属性名省略 • 将一个选课记录 (99001，2)插入到SC表中 INSERT INTO SC(Sno, Cno) VALUES (‘99001’，‘2’) VALUES中的值必须与属性名一一对应

  30. 6.4.5 数据更新 数据更新是指数据的插入、删除、修改数据的操作 1.插入数据 (2) 插入子查询结果 • 格式 ： INSERT INTO <表名> [(<属性列1>[, <属性列2> … ] ) ] 子查询 • 功能： 可以将一个基表中的数据成批插入到另一个基表中

  31. 6.4.5 数据更新 1.插入数据 (2) 插入子查询结果 • 计算学生已考课程的平均成绩，并将结果存入数据库 • 首先创建存放结果的基表： CREATE TABLE SAvgGrade( Sno CHAR(6), AvgGrade SMALLINT ) • 将查询结果插入新表中： INSERT INTO SAvgGrade (Sno, AvgGrade) SELECTSno, AVG(Grade) FROM SC WHERE Grade IS NOT NULL GROUP BY Sno

More Related