Principle and Application of Database

Presentation on theme: "Principle and Application of Database"— Presentation transcript:

1 Principle and Application of Database
数据库原理及应用 Principle and Application of Database 第三章 关系数据库标准语言(习题课)

2 基本知识点 需要了解的：SQL语言发展的过程，从而进一步了解关系数据库技术和RDBMS产品的发展过程。
需要牢固掌握的：掌握SQL语言的特点、SQL语言与非关系模型(层次模型、网状模型)数据语言的不同，从而体会SQL语言之所以能够成为用户和业界所接受，并成为国际标准的原因；体会面向过程的语言和SQL语言的区别和优点；体会关系数据库系统为数据库应用系统的开发提供良好环境、减轻用户负担、提高用户生产率的原因。 需要举一反三的：熟练而正确地使用SQL语言完成对数据库的查询、插入、删除、更新操作，特别是各种各样的查询，掌握SQLSQL语言强大的查询功能，并能与关系代数、关系演算等语言进行比较，了解它们各自的特点。 难点：用SQL语言正确完成复杂查询。

3 习题解答和解析 1.试述SQL语言的特点。 答：⑴综合统一：SQL语言集数据定义语言DDL、数据操纵语言DML、数据控制语言DCL的功能于一体。 ⑵高度非过程化：用SQL语言进行数据操作，只要提出“做什么”，无须指明“怎么做”，因此无需了解存取路径。 ⑶面向集合的操作方式：SQL语言采用的集合操作方式，不仅操作对象、查询结果可以是元组的集合，而且一次插入、删除、更新操作的对象也可以是元组的集合。 ⑷以同一种语法结构提供两种使用方法：SQL语言既是自含式语言，能独立地用于联机交互；又是嵌入式语言，能嵌入到高级语言中进行混合编程。 ⑸语言简捷，易学易用。

4 答：建立表S(SNO,SNAME,STATUS,CITY)
2.试述SQL的定义功能。 答：SQL语言的定义功能包括定义表、定义视图和定义索引。 SQL语言使用CREATE TABLE语句建立基本表，ALTER TABLE语句修改基本表的定义，DROP TABLE语句删除基本表；使用CREATE INDEX语句建立索引，DROP INDEX语句删除索引；使用CREATE VIEW语句建立视图，DROP VIEW语句删除视图。 3.用SQL语句建立第二章习题5中的4个表。 答：建立表S(SNO,SNAME,STATUS,CITY) CREATE TABLE S(SNO CHAR(3),SNAME CHAR(10), STATUS CHAR(2),CITY CHAR(10)); 建立表P(PNO,PNAME,COLOR,WETGHT) CREATE TABLE P(PNO CHAR(3),PNAME CHAR(10), COLOR CHAR(4),WEIGHT INT);

5 4.针对上题建立的4个表试用SQL语言完成第二章习题5中的查询
建立表J(JNO,JNAME,CITY) CREATE TABLE J(JNO CHAR(3),JNAME CHAR(10),CITY CHAR(10)); 建立表SPJ(SNO,PNO,JNO,QTY) CREATE TABLE SPJ(SNO CHAR(3), PNO CHAR(3), JNO CHAR(3),QTY INT); 4.针对上题建立的4个表试用SQL语言完成第二章习题5中的查询 答：大家可以对比SQL语言、关系代数、ALPHA、QBE语言，体会各种语言的优点。 ⑴求供应工程J1零件的供应商号SNO。 SELECT SNO FROM SPJ WHERE JNO=‘J1’; ⑵求供应工程J1零件P1的供应商号SNO。 SELECT SNO FROM SPJ WHERE JNO=‘J1’ AND PNO=‘P1’;

6 ⑶求供应工程J1红色零件的供应商号SNO。
SELECT SNO FROM SPJ WHERE JNO=‘J1’ AND PNO IN (SELECT PNO FROM P WHERE COLOR=‘红’); 或SELECT SNO FROM SPJ,P WHERE JNO=‘J1’ AND SPJ.PNO=P.PNO AND COLOR=‘红’); ⑷求没有使用天津供应商生产的红色零件的工程号JNO。 解析：从J表入手，以包含那些尚未使用任何零件的工程号。 SELECT JNO FROM J WHERE NOT EXISTS (SELECT * FROM SPJ WHERE SPJ.JNO=J.JNO AND SNO IN (SELECT SNO FROM S WHERE CITY=‘天津’) AND PNO IN (SELECT PNO FROM P WHERE COLOR=‘红’));

7 或SELECT JNO FROM J WHERE NOT EXISTS
(SELECT * FROM SPJ,S,P WHERE SPJ.JNO=J.JNO AND SPJ.SNO=S.SNO AND SPJ.PNO=P.PNO AND S.CITY=‘天津’ AND P.COLOR=‘红’); ⑸求至少用了S1供应商所供应的全部零件的工程号JNO。 SELECT DISTINCT JNO FROM SPJ SPJZ WHERE NOT EXISTS (SELECT * FROM SPJ SPJX WHERE SNO=‘S1’ AND NOT EXISTS (SELECT * FROM SPJ SPJY WHERE SPJY.PNO=SPJX.PNO AND SPJY.JNO=SPJZ.JNO)); 解析：上述查询可以抽象为：要求这样的工程x，使(y)p→q为真。即对于所有的零件y，满足逻辑蕴涵p→q：p为谓词“供应商S1供应了零件y”；q为谓词“工程x选用了零件y”。即，只要“供应商S1供应了零件y”为真，则“工程x选用了零件y”就为真。逻辑蕴涵可以转换为等价形式： (y)p→q≡﹁(y(﹁(p→q))≡﹁(y(﹁(﹁p∨q)))≡﹁y(p∧﹁q) 表达语义是：不存在这样的零件y，供应商S1供应了，而工程x没有选用。

8 5.针对习题3中的4个表试用SQL语言完成以下各项操作： 答：⑴找出所有供应商的姓名和所在城市。 ⑵找出所有零件的名称、颜色、重量。
SELECT SNAME,CITY FROM S; ⑵找出所有零件的名称、颜色、重量。 SELECT PNAME,COLOR,WEIGHT FROM P; ⑶找出使用供应商S1所供应零件的工程号码。 SELECT JNO FROM SPJ WHERE SNO=‘S1’; ⑷找出工程项目J2使用的各种零件的名称及其数量。 SELECT P.PNAME,SPJ.QTY FROM P,SPJ WHERE P.PNO=SPJ.PNO AND SPJ.JNO=‘J2’; ⑸找出上海厂商供应的所有零件号码。 SELECT DISTINCT PNO FROM SPJ WHERE SNO IN (SELECT SNO FROM S WHERE CITY=‘上海’);

9 ⑹找出使用上海产的零件的工程名称。 ⑺找出没有使用天津产的零件的工程号码。 SELECT JNAME FROM J,SPJ,S
WHERE J.JNO=SPJ.JNO AND SPJ.SNO=S.SNO AND S.CITY=‘上海’; 或：SELECT JNAME FROM J WHERE JNO IN (SELECT JNO FROM SPJ,S WHERE SPJ.SNO=S.SNO AND S.CITY=‘上海’); ⑺找出没有使用天津产的零件的工程号码。 SELECT JNO FROM J WHERE NOT EXISTS (SELECT * FROM SPJ WHERE SPJ.JNO=J.JNO AND SNO IN (SELECT SNO FROM S WHERE CITY=‘天津’)); 或：SELECT JNO FROM J WHERE NOT EXISTS (SELECT * FROM SPJ,S WHERE SPJ.JNO=J.JNO AND SPJ.SNO=S.SNO AND S.CITY=天津’);

10 ⑼由S5供给J4的零件P6改为由S3供应，请做必要的修改。
⑻把全部红色零件的颜色改为蓝色。 UPDATE P SET COLOR=‘蓝’ WHERE COLOR=‘红’; ⑼由S5供给J4的零件P6改为由S3供应，请做必要的修改。 UPDATE SPJ SET SNO=‘S3’ WHERE SNO=‘S5’ AND JNO=‘J4’ AND PNO=‘P6’; ⑽从供应商表中删除S2的记录，并从供应情况表中删除相应记录 DELETE FROM SPJ WHERE SNO=‘S2’; DELETE FROM S WHERE SNO=‘S2’; 解析：注意删除顺序，应先从SPJ表中删除供应商S2的供应零件的记录，然后从S表中删除S2。 ⑾请将(S2,J6,P4,200)插入供应情况表。 INSERT INTO SPJ(SNO,JNO,PNO,QTY) VALUES (‘S2’,’J6’,’P4’,200); 或：INSERT INTO SPJ VALUES (‘S2’,’J6’,’P4’,200);

11 6.什么是基本表？什么是视图？两者的区别和联系是什么？
答：基本表是本身独立存在的表，在SQL中一个关系就对应一个表。视图是从一个或几个基本表导出的表。视图本身不独立存储在数据库中，是一个虚表。即数据库中只存放视图的定义而不存放视图对应的数据，这些数据仍存放在导出视图的基本表中。视图在概念上与基本表等同，用户可以如同基本表那样使用视图，可以在视图上再定义视图。 7.试述视图的优点。 答：⑴视图能够简化用户的操作。 ⑵视图使用户能从多种角度看待同一数据。 ⑶视图对重构数据库提供了一定程度的逻辑独立性。 ⑷视图能够对机密数据提供安全性。 8.所有的视图是否都可以更新？为什么？

12 9.哪类视图是可更新的？哪类视图是不可更新的？
答：不是。视图是不实际存储数据的虚表，因此对视图的更新，最终要转换为对基本表的更新。因为有些视图的更新不能惟一有意义地转换成对相应基本表的更新，所以并不是所有的视图都是可更新的。如对视图S_G(学生的学号及其平均成绩)而言，要修改平均成绩，必须修改各科成绩，但无法知道哪些课程成绩的变化导致了平均成绩的变化。 CREATE VIEW S_G(Sno,Gavg) AS SELECT Sno,AVG(Grade) FROM SC GROUP BY Sno; 9.哪类视图是可更新的？哪类视图是不可更新的？ 答：基本表的行列子集视图一般是可更新的；若视图的属性来自集函数、表达式，则该视图肯定是不可更新的。 10.试述某个你熟悉的实际系统中对视图更新的规定。 答：略。

13 11.请为三建工程项目建立一个供应情况的视图，包括供应商代码(SNO)、零件代码(PNO)、供应数量(QTY)。针对该视图完成下列查询：
⑴找出三建工程项目使用的各种零件代码及其数量。 ⑵找出供应商S1的供应情况。 答：建立视图： CREATE VIEW V_SPJ AS SELECT SNO,PNO,QTY FROM SPJ WHERE JNO=(SELECT JNO FROM J WHERE JNAME=‘三建’); ⑴SELECT PNO,QTY FROM V_SPJ; ⑵SELECT PNO,QTY FROM V_SPJ WHERE SNO=‘S1’; 12.针对习题3建立的表，用SQL语言完成以下各项操作： ⑴把对表S的INSERT权限授予用户张勇，并允许他再将此权限授予其他用户。

14 答：GRANT INSERT ON TABLE S TO 张勇 WITH GRANT OPTION;
⑵把查询SPJ表和修改QTY属性的权限授给用户李天明。 GRANT SELECT,UPDATE(QTY) ON TABLE SPJ TO 李天明; 13.在嵌入式SQL中是如何区分SQL语句和主语言语句的？ 答：在SQL语句前加上前缀EXEC SQL，SQL语句的结束标志则随主语言的不同而不同，如在C中，以分号结束，而在COBOL中，则以END-EXEC结束。 14.在嵌入式SQL中是如何解决数据库工作单元与源程序工作单元之间通信的？ 答：数据库工作单元与源程序工作单元之间的通信主要包括： ⑴SQL通信区SQLCA：用来向主语言传递SQL语句的执行状态信息，使主语言能够根据此信息控制程序流程。 ⑵主变量：用来实现主语言向SQL语句提供参数；将SQL语句

15 查询数据库的结果交主语言进一步处理。 ⑶游标：解决集合性操作语言与过程性操作语言的不匹配，通过游标逐一获取记录，并赋给主变量，交由主语言进一步处理。 15.在嵌入式SQL中是如何协调SQL语言的集合处理方式和主语言的单记录处理方式的？ 答：用游标来协调这两种不同的处理方式。游标区是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果，每个游标区都有一个名字。用户可以通过游标逐一获取记录，并赋给主变量，交由主语言进一步处理。

16 作业 预习4.1～4.2。