软件技术基础数据库技术.ppt

第三章 关系数据库语言SQL （续）,概述 SQL的数据定义 SQL的数据操纵 SQL的视图定义 SQL的数据控制 嵌入式SQL,联接查询(1 of 8),联接查询：涉及至少两个表的查询。 SQL中没有专门的JOIN命令，而是通过SELECT语句中的WHERE子句来达到联接运算的目的，因此更加灵活、简便。,联接查询(2 of 8),联接条件的一般格式为： 表名1.列名1比较运算符表名2.列名2 其中：比较运算符主要有：=、=、= 和!=；联接谓词中的列名称为联接字段。联接条件中，联接字段类型必须是可比的，但联接字段不一定是同名的。,例1：(简单的联接查询) 求选修C2课程的学生学号、姓名和成绩。 SELECT Student.Sno, Sname, Grade FROM Student, SC WHERE Student.Sno=SC.Sno AND SC.Cno=C2;,Example-联接查询(3 of 8),例2：(多表联接) 求学生学号、姓名、选修课程名、成绩。,Example-联接查询(4 of 8),Student:Sno Sname Ssex Sdept Sage,Course:Cno Cname Cpno Ccredit,C1 G 1 C2 H C1 2 C3 I C1 3 C4 J C2 2 C5 K C4 2,S1 C1 A S1 C2 A S1 C3 A S1 C5 B S2 C1 B S2 C2 C S2 C4 C S3 C2 B S3 C3 C S3 C4 B S4 C3 B S4 C5 D S5 C2 C S5 C3 B S5 C5 B S6 C4 A S6 C5 A,S1 A 1 CS 20 S2 B 1 CS 21 S3 C 0 MA 19 S4 D 0 CI 19 S5 E 1 MA 20 S6 F 0 CS 22,直观的查找过程,SELECT Student.Sno，Sname，Cname，Grade FROM Student，Course，SC WHERE Student.Sno=SC.Sno AND SC.Cno=Course.Cno;,例2续：(多表联接) 求学生学号、姓名、选修课程名、成绩。,Example-联接查询(5 of 8),Example-联接查询(6 of 8),例3:(单表或自身联接) 求每一门课的间接先行课。,SELECT FIRST.Cno, SECOND.Cpno FROM Course FIRST, Course SECOND WHERE FIRST.Cpno = SECOND.Cno；,SQL语句为:,直观的查找过程,FIRST,SECOND,查询结果为: Cno Cpno C4 C1 C5 C2,Example-联接查询(7 of 8),例4：（外部连接）查询每个学生及其选修课程的情况包括没有选修课程的学生 SELECT Student.Sno，Sname，Ssex，Sage，Sdept， Cno，Grade FROM Student，SC WHERE Student.Sno = SC.Sno(*)；,左外部连接：外连接符(*)出现在连接条件的左边 右外部连接：外连接符(*)出现在连接条件的右边 外部连接中不匹配的分量用NULL表示,S1 A 1 CS 20 S2 B 1 CS 21 S3 C 0 MA 19,Student:Sno Sname Ssex Sdept Sage,S1 C1 A S1 C2 A S2 C1 B,右外连接结果,S1 A 1 CS 20 C1 A S1 A 1 CS 20 C2 A S2 B 1 CS 21 C1 B S3 C 0 MA 19,Student.Sno Sname Ssex Sdept Sage Cno Grade,Example-联接查询(8 of 8),嵌套查询（子查询）,概述 带有IN谓词的子查询 带有比较运算符的子查询 带有ANY或ALL谓词的子查询 带有EXISTS谓词的子查询,嵌套查询（子查询）,嵌套查询（子查询） 嵌套查询由内向外处理 SQL允许多层嵌套 嵌套查询中最常用的谓词是IN 嵌套查询层次分明、容易理解,SELECT Sno，Sname FROM Student WHERE Sno IN ( SELECT Sno FROM SC WHERE Cno IN ( SELECT Cno FROM Course WHERE Cname=J ) );,将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询,子查询,父查询,限制：子查询不能使用ORDER BY子句,嵌套查询的分类,不相关子查询：子查询的查询条件不依赖于父查询 由里向外逐层处理。即每个子查询在上一级查询处理之前求解，子查询的结果用于建立其父查询的查找条件。 相关子查询：子查询的查询条件依赖于父查询 首先取外层查询中表的第一个元组，根据它与内层查询相关的属性值处理内层查询，若WHERE子句返回值为真，则取此元组放入结果表； 然后再取外层表的下一个元组； 重复这一过程，直至外层表全部检查完为止。,例1：查询与A在同一个系学习的学生,SELECT S1.* FROM Student S1 WHERE S1.Sdept IN （ SELECT Sdept FROM Student S2 WHERE S2.Sname=A）;,带IN谓词的子查询(1 of 5),SELECT * FROM Student WHERE Sdept IN （ SELECT Sdept FROM Student WHERE Sname=A）;,分析：可以用联接实现,SELECT S1.* FROM Student S1, Student S2, WHERE S1.Sdept= S2.Sdept AND S2.Sname=A;,但最后结果只包含Student中的字段，应考虑更为有效直观的方法： 确定A同学所在系名 找在该系学习的学生,back,例2：嵌套查询：求选修了课程名为J的学生学号和姓名。,在Course中找课程J的编号 在SC中找选修该课的学生学号 在Student中找选修该课的学生姓名,带IN谓词的子查询(2 of 5),分析：可以用联接来实现，但最后结果只包含Student中的字段，应考虑更有效、直观的方法：,带IN谓词的子查询(3 of 5),S1 C1 A S1 C2 A S1 C3 A S1 C5 B S2 C1 B S2 C2 C S2 C4 C S3 C2 B S3 C3 C S3 C4 B S4 C3 B S4 C5 D S5 C2 C S5 C3 B S5 C5 B S6 C4 A S6 C5 A,Course: Cno Cname Cpno Ccredit,S1 A 1 CS 20 S2 B 1 CS 21 S3 C 0 MA 19 S4 D 0 CI 19 S5 E 1 MA 20 S6 F 0 CS 22,C1 G 1 C2 H C1 2 C3 I C1 3 C4 J C2 3 C5 K C4 2,Student:Sno Sname Ssex Sdept Sage,在Course中找课程J的编号,在SC中找选修该课的学生学号,在Student中找选修该课的学生学号和姓名,SELECT Cno FROM Course WHERE Cname=J；,SELECT Sno FROM SC WHERE Cno IN （C4）；,SELECT Sno，Sname FROM Student WHERE Sno IN（S2,S3,S6）；,带IN谓词的子查询(4 of 5),最后的查询语句:,SELECT Sno，Sname FROM Student WHERE Sno IN ( SELECT Sno FROM SC WHERE Cno IN ( SELECT Cno FROM Course WHERE Cname=J ) );,SELECT Sno，Sname FROM Student WHERE Sno IN ( SELECT Sno FROM SC WHERE Cno IN ( SELECT Cno FROM Course WHERE Cname=J ) );,带IN谓词的子查询(5 of 5),用连接查询实现本例：,SELECT Sno，Sname FROM Student，SC，Course WHERE Student.Sno = SC.Sno AND SC.Cno = Course.Cno AND Course.Cname=J；,例1、2是相关子查询还是不相关子查询？,带有比较运算符的子查询(1 of 2),当能确切知道内层查询返回单值时，可用比较运算符（，=，） 与ANY或ALL谓词配合使用,带有比较运算符的子查询(2 of 2),又看例1：查询与A在同一个系学习的学生,SELECT * FROM Student WHERE Sdept IN （ SELECT Sdept FROM Student WHERE Sname=A）;,SELECT * FROM Student WHERE Sdept = （ SELECT Sdept FROM Student WHERE Sname=A）;,SELECT * FROM Student WHERE ( SELECT Sdept FROM Student WHERE Sname=A) = Sdept；,子查询应跟在比较符之后,带有ANY或ALL谓词的子查询 (1 of 5),谓词语义 ANY：某一个值 ALL：所有值 使用ANY 或ALL谓词时必须同时使用比较运算符,ANY ALL, = ,带有ANY或ALL谓词的子查询(2 of 5),例1： 查询其他系中比IS系某一学生年龄小的学生姓名和年龄,SELECT Sname, Sage FROM Student WHERE Sage IS；,其他方法？,带有ANY或ALL谓词的子查询(3 of 5),例1： 查询其他系中比IS系某一学生年龄小的学生姓名和年龄 用集函数：,SELECT Sname,Sage FROM Student WHERE Sage IS；,效率更高,带有ANY或ALL谓词的子查询(4 of 5),例2：查询其他系中比IS系所有学生年龄都小的学生姓名和年龄,SELECT Sname, Sage FROM Student WHERE Sage IS；,SELECT Sname, Sage FROM Student WHERE Sage IS；,带有ANY或ALL谓词的子查询(5 of 5),ANY，ALL与集函数及IN谓词的对应关系,带有EXISTS谓词的子查询(1 of 15),EXISTS谓词与NOT EXISTS谓词 不同形式的查询间的替换 用EXISTS/NOT EXISTS实现全称量词 用EXISTS/NOT EXISTS实现逻辑蕴函,带有EXISTS谓词的子查询(2 of 15),EXISTS谓词 存在量词 带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false” 若内层查询结果非空，则返回真值 若内层查询结果为空，则返回假值,带有EXISTS谓词的子查询(3 of 15),例1：查询所有选修了1号课程的学生姓名,SELECT Sname FROM Student WHERE EXISTS （SELECT * FROM SC WHERE Sno = Student.Sno AND Cno=1）；,相当于一个变量，根据它的值处理内层查询 Student中有多少个学号，内层查询就进行多少次,属于相关子查询：查询条件依赖于外层查询中某个值,带有EXISTS谓词的子查询(4 of 15),例1：查询所有选修了1号课程的学生姓名,SELECT Sname FROM Student WHERE EXISTS （SELECT * FROM SC WHERE Sno = Student.Sno AND Cno=1）；,由EXISTS引出的子查询，其目标列表达式通常都用* ，因为带EXISTS的子查询只返回真值或假值，给出列名无实际意义,其他方法？,用联结、相关、不相关子查询实现,带有EXISTS谓词的子查询(5 of 15),例2：查询没有选修1号课程的学生姓名,SELECT Sname FROM Student WHERE NOT EXISTS （SELECT * FROM SC WHERE Sno = Student.Sno AND Cno=1）；,此例用连接运算难于实现,SELECT Sname FROM Student, SC WHERE Student.Sno =SC.Sno AND Cno1 ;,带有EXISTS谓词的子查询(6 of 15),不同形式的查询间的替换 一些带EXISTS或NOT EXISTS谓词的子查询不能被其他形式的子查询等价替换 所有带IN谓词、比较运算符、ANY和ALL谓词的子查询都能用带EXISTS谓词的子查询等价替换。,带有EXISTS谓词的子查询(7 of 15),例：再看“查询与A在同一个系学习的学生”,SELECT * FROM Student S1 WHERE EXISTS SELECT * FROM Student S2 WHERE S2.Sdept = S1.Sdept AND S2.Sname = A ；,back,带有EXISTS谓词的子查询(8 of 15),用EXISTS/NOT EXISTS实现全称量词(难点) SQL语言中没有全称量词 （For all） 可以把带有全称量词的谓词转换为等价的带有存在量词的谓词：,带全称量词的谓词,带存在量词的谓词,（x）P（x）,（ x（ P（x）,带有EXISTS谓词的子查询(9 of 15),例： 查询选修了全部课程的学生姓名,带全称量词的谓词,带存在量词的谓词,（x）P（x）,（ x（ P（x）,本例变为：选这样的学生姓名，没有一门课程是他不选修的,选这样的学生姓名，没有一门课程是他不选修的,分析:任给一学号Sno, 若不存在他不选修的课, 则显示他的姓名, ( Cno (学号为Sno的学生没修课程Cno) ), ( Cno (在SC中不存在选课单 (Sno, Cno, ) ) ),例： 查询选修了全部课程的学生姓名,带有EXISTS谓词的子查询(1 0of 15),SELECT Sname FROM Student WHERE NOT EXISTS ( SELECT * FROM Course WHERE NOT EXISTS ( SELECT * FROM SC WHERE Sno=Student.Sno AND Cno=Course.Cno ) );,带有EXISTS谓词的子查询(11 of 15),带有EXISTS谓词的子查询(12 of 15),用EXISTS/NOT EXISTS实现逻辑蕴函(难点) SQL语言中没有蕴函(Implication)逻辑运算 可以利用谓词演算将逻辑蕴函谓词等价转换为： p q pq,带有EXISTS谓词的子查询(13 of 15),例、查询至少选修了学生95002选修的全部课程的学生号码 分析：用逻辑蕴函表达：查询学号为x的学生，对所有的课程y，只要95002学生选修了课程y，则x也选修了y。 形式化表示： 用P表示谓词 “学生95002选修了课程y” 用q表示谓词 “学生x选修了课程y” 则上述查询为: (y) p q,带有EXISTS谓词的子查询(14 of 15),等价变换： (y)p q (y (p q ) (y ( p q) y(pq) 变换后语义：不存在这样的课程y，学生95002选修了y，而学生x没有选。,带有EXISTS谓词的子查询(15 of 15),SELECT DISTINCT Sno FROM SC SCX WHERE NOT EXISTS (SELECT * FROM SC SCY WHERE SCY.Sno = 95002 AND NOT EXISTS (SELECT * FROM SC SCZ WHERE SCZ.Sno=SCX.Sno AND SCZ.Cno=SCY.Cno)；,不存在这样的课程y，学生95002选修了y，而学生x没有选。,P,q,集合查询(1 of 6),包括：并操作UNION、交操作INTERSECT、差操作MINUS 要求：各数据项数目必须相同，对应项的数据类型也必须相同。 标准SQL中无交操作INTERSECT、差操作MINUS，但可以用其它方法来实现。,例（并操作）： 查询计算机科学系（CS)的学生及年龄不大于19岁的学生。,SELECT * FROM Student WHERE Sdept = CS UNION SELECT * FROM Student WHERE Sage=19；,SELECT DISTINCT * FROM Student WHERE (Sdept = CS) OR (Sage=19)；,集合查询(2 of 6),集合查询(3 of 6),例（交操作）： 查询计算机科学系（CS) 且年龄不大于19岁的学生。,SELECT * FROM Student WHERE (Sdept = CS) AND (Sage=19)；,SELECT * FROM Student WHERE (Sdept = CS) AND Sno IN (SELECT Sno FROM Student WHERE (Sage=19)；,例（差操作）： 查询计算机科学系（CS) 的学生与年龄不大于19岁的学生的差集。,SELECT * FROM Student WHERE (Sdept = CS) AND (NOT (Sage=19)；,SELECT * FROM Student WHERE (Sdept = CS) AND Sno NOT IN (SELECT Sno FROM Student WHERE (Sage=19)；,集合查询(4 of 6),集合查询(5 of 6),对集合操作结果的排序 ORDER BY子句只能用于对最终查询结果排序，不能对中间结果排序 任何情况下，ORDER BY子句只能出现在最后 对集合操作结果排序时，ORDER BY子句中用数字指定排序属性,集合查询(6 of 6),SELECT * FROM Student WHERE Sdept= CS ORDER BY Sno UNION SELECT * FROM Student WHERE Sage=19 ORDER BY Sno；,SELECT * FROM Student WHERE Sdept= CS UNION SELECT * FROM Student WHERE Sage=19 ORDER BY 1；,SELECT语句的一般格式,前已介绍，请大家再自学一遍,3.3.2 SQL的数据更新 -INSERT语句(1 of 5),插入 INSERT （1）把一个记录插入到表中 INSERT INTO 表名 （字段名 ，字段名 VALUES （常量 ，常量 ； （2）把子查询的结果插入到表中 INSERT INTO 表名 （字段名 ，字段名 子查询；,INSERT INTO Student (Sno, Sname, Ssex, Sdept, Sage) VALUES (S9, X, 1, CS, 19);,INSERT INTO Student (Sno, Sname, Ssex, Sage, Sdept) VALUES (S9, X, 1, 19 , CS);,INSERT INTO S VALUES (S9, X, 1, CS, 19);,例1：将学号为S9，姓名为X，性别为1，所在系为 CS，年龄为19的学生记录插入到学生表中。,Example(INSERT语句) (2 of 5),Example(INSERT语句) (3 of 5),例2：插入一条选课记录( S2，1 ),INSERT INTO SC (Sno，Cno) VALUES ( S2 ， 1 )；,新插入的记录在Grade列上取空值,INSERT INTO S_ GRADE (Sno, AVG_GRADE) SELECT Sno, AVG(Grade) FROM SC WHERE Sno IN (SELECT Sno FROM Student WHERE SEX= 1) GROUP BY Sno HAVING AVG (GRADE)80；,例3：在关系SC中，把平均成绩大于80分的男学生(Ssex=1）的学号和平均成绩存入到另一个关系S _GRADE (Sno, AVG_GRADE) 中。,Example(INSERT语句) (4 of 5),Example(INSERT语句) (5 of 5),DBMS在执行插入语句时会检查所插元组是 否破坏表上已定义的完整性规则 实体完整性 参照完整性 用户定义的完整性 对于有NOT NULL约束的属性列是否提供了非空值 对于有UNIQUE约束的属性列是否提供了非重复值 对于有值域约束的属性列所提供的属性值是否在值域范围内,3.3.2 SQL的数据更新 -Update语句(1 of 6),修改 Update UPDATE SET =, = WHERE ；,Example(Update语句) (2 of 6),例1：把学生S1的成绩置0。,UPDATE SC SET Grade=0 WHERE Sno=S1;,UPDATE Student SET Sage=Sage+1;,例2：把所有学生的年龄增加1岁。,例3：把计算机科学系全体学生的成绩置零。,Example(Update语句) (3 of 6),嵌套子查询,UPDATE SC SET Grade=0 WHERE CS= （SELECT Sdept FROM Student WHERE Student.Sno = SC.Sno )；,例4：把课程名为M的成绩提高5%。,Example(Update语句) (4 of 6),UPDATE SC SET Grade=Grade * 1.05 WHERE Cno IN （SELECT Cno FROM Course WHERE Cname= M)；,例5：当C4课程的成绩低于该门课程的平均成绩时提高5%。,Example(Update语句) (5of 6),UPDATE SC SET Grade=Grade * 1.05 WHERE Cno= C4 AND Grade（SELECT AVG(Grade) FROM SC WHERE Cno= C4)；,Example(Update语句) (6 of 6),DBMS在执行修改语句时会检查修改操作 是否破坏表上已定义的完整性规则 实体完整性 主码不允许修改 用户定义的完整性 NOT NULL约束 UNIQUE约束 值域约束,3.3.2 SQL的数据更新 -DELETE语句(1of 3),删除 DELETE DELETE FROM 表名 WHERE 条件 ；,无条件时，删除全部记录， 但表的结构还在，相当于一个空表,例1：删除学号为S1的学生。,DELETE FROM Student WHERE Sno=S1;,例2：把数学系全体学生的选课记录删除。,DELETE FROM SC WHERE MA= （SELECT Sdept FROM Student WHERE Student.Sno = SC.Sno)；,Example(DELETE语句) (2 of 3),Example(DELETE语句) (3 of3),DBMS在执行删除语句时会检查所删元组 是否破坏表上已定义的完整性规则 参照完整性 不允许删除 级联删除,更新操作与数据库的一致性,DBMS在执行插入、删除、更新语句时必须保证数据库一致性 必须有事务的概念和原子性 完整性检查和保证,关系数据库语言SQL(续),概述 SQL的数据定义 SQL的数据操纵 SQL的视图定义 SQL的数据控制 嵌入式SQL,3.4 SQL的视图定义,视图是从一个或几个基本表（或视图）导出的表。 用户外模式是由若干基本表和/或若干视图构成的。 视图是一个虚表，只存储视图的定义，数据存在所基于的基本表中。 视图定义后就可象基本表一样来使用。 可创建、删除视图 可用来定义新的视图 可在视图上查询（SELECT） 可更新（INSERT，DELETE，UPDATE）视图，但受限制,1.建立视图 基本格式 CREATE VIEW 视图名 （字段名 ，字段名） AS 子查询 WITH CHECK OPTION 功能：在数据字典中存储视图的定义（但并不执行子查询），此后视图名就可作为一个表来使用。 限制表中特定列和行的使用 隐藏复杂查询的细节 将更新、插入和修改值限制到某个范围,定义视图(1 of 11),例1：建立计算机系的学生视图。 说明: 若SELECT语句中有集函数、或字段表达式、或多表联接有同名字段时，则视图中必须指定字段名； 视图中字段名可以和基本表中的字段名不同； 一般不允许有ORDER BY子句和DISTINCT短语。,定义视图(2 of 11),CREATE VIEW CS_S AS SELECT Sno, Sname, Sage FROM Student WHERE Sdept=CS,定义视图(3 of 11),2.删除视图 DROP VIEW 视图名 例： DROP VIEW CS_S,例2：建立信息系学生的视图。并要求进行修改和插入 操作时仍保证该视图只有信息系的学生。,注意：1、DBMS执行CREATE VIEW的结果只把对视图的定义存入数据字典；并不执行其中的查询语句。 2、只有对视图进行查询时，才把视图的定义从数据字典中取出，转换成查询语句，进行查询。,定义视图(4 of 11),CREATE VIEW IS_S AS SELECT Sno,Sname,Sage FROM Student WHERE Sdept=IS,(Sno,Sname,Sage),WITH CHECK OPTION;,例2(续)：对IS_S视图的更新操作 修改操作：DBMS自动加上Sdept= IS的条件 删除操作：DBMS自动加上Sdept= IS的条件 插入操作：DBMS自动检查Sdept属性值是否为IS 如果不是，则拒绝该插入操作 如果没有提供Sdept属性值，则自动定义Sdept为IS,定义视图(5 of 11),例3：建立信息系选修了C1课程的学生的视图。,定义视图(6 of 11),CREATE VIEW IS_S1 AS SELECT Student.Sno, Sname, Grade FROM Student, SC WHERE Sdept=IS AND Student.Sno=SC.Sno AND SC.Cno=C1;,(Sno,Sname,Grade),CREATE VIEW IS_S2 AS SELECT Sno,Sname,Grade FROM IS_S1 WHERE Grade=A；,例4：建立信息系选修了C1课程且成绩为A的学生的视图。,定义视图(7 of 11),例5：定义一个反映学生出生年份的视图,定义视图(8 of 11),CREATE VIEW BT_S (Sno, Sname, Sbirth) AS SELECT Sno,Sname,2006-Sage FROM Student;,例6：建立学生学号及平均成绩的视图。,定义视图(9 of 11),CREATE VIEW S_G (Sno,Gavg) AS SELECT Sno,AVG(Grade) FROM SC GROUP BY Sno,定义视图(10 of 11),例7：将Student表中所有女生记录定义为一个视图,CREATE VIEW F_S1(stdnum，name，sex，age，dept) AS SELECT * FROM Student WHERE Ssex=女；,缺点：修改基表Student的结构后，Student表与F_S1视图的映象关系被破坏，导致该视图不能正确工作。,以 SELECT * 方式创建的视图可扩充性差，应尽可能避免,定义视图(11 of 11),为基表Student增加属性列不会破坏Student表与F_S2视图的映象关系,CREATE VIEW F_S (stdnum，name，sex，age，dept) AS SELECT Sno，Sname，Ssex，Sage，Sdept FROM Student WHERE Ssex=女；,例7(续),查询视图(1 of 4),执行过程： 首先进