现代微型计算机与接口教程第2版杨文显第3章 关系数据库设计理论.docx

现代微型计算机与接口教程第2版杨文显第3章 关系数据库设计理论.docx

《现代微型计算机与接口教程第2版杨文显第3章 关系数据库设计理论.docx》由会员分享，可在线阅读，更多相关《现代微型计算机与接口教程第2版杨文显第3章 关系数据库设计理论.docx（12页珍藏版）》请在冰豆网上搜索。

现代微型计算机与接口教程第2版杨文显第3章关系数据库设计理论

第3章关系数据库标准语言SQL

习题

一、选择题

1、C

2、B

3、D

4、C

5、A

6、A

7、D

8、C

9、C

10、D

二、填空题

1、SQL操作命令CREATE、DROP、ALTER主要完成的是数据的_定义_功能。

2、__SQL___为关系数据库语言国际标准语言。

3、SQL中文含义是_结构化查询语言_，它集查询、操纵、定义和控制等多种功能。

4、视图是从_基本表或视图_导出的表。

它相当于三级结构中的外模式。

5、视图是虚表，它一经定义就可以和基本表一样被查询，但_更新__操作将有一定限制。

6、SQL的数据更新功能主要包括__INSERT__、__UPDATE_和_DELETE_三个语句。

7、在字符匹配查询中，通配符“%”代表_任意多个字符_，“_”代表__任意单个字符_。

8、SQL语句具有__交互式__和__嵌入式__两种使用方式。

9、SQL语言中，实现数据检索的语句是_SELECT_。

10、在SQL中如果希望将查询结果排序，应在Select语句中使用_ORDERBY_子句。

三、简答与SQL操作表达

1、简述SQL的定义功能。

解：

SQL语言使用数据定义语言（DataDefinitionLanguage，DDL）实现其数据定义功能，可对数据库基本表、视图和索引等进行定义、修改和撤消（删除）等操作。

具体对基本表、视图和索引的定义（创建）、修改和撤消（删除）等操作命令的使用此略。

见书P73-79

2、简述SQL语言支持的三级逻辑结构。

解：

SQL语言支持关系数据库三级模式结构，如图3.1所示。

其中外模式对应于视图（View）和部分基本表（BaseTable），模式对应于基本表，内模式对应于存储文件。

图3.1数据库三级模式结构

基本表是本身独立存在的表，在SQL中一个关系就对应一个表。

一些基本表对应一个存储文件，一个表可以有若干索引，索引也存放在存储文件中。

图3.2关系数据库三级模式结构示意图

视图是从基本表或其他视图中导出的表，它本身不独立存储在数据库中，也就是说数据库中只存放视图的定义而不存放视图对应的数据，这些数据仍存放在导出视图的基本表中，因此视图是一个虚表。

存储文件的物理结构及存储方式等组成了关系数据库的内模式。

对于不同数据库管理系统，其存储文件的物理结构及存储方式等往往是不同的，一般也是不公开的。

视图和基本表是SQL语言的主要操作对象，用户可以用SQL语言对视图和基本表进行各种操作。

在用户眼中，视图和基本表都是关系表，而存储文件对用户是透明的。

关系数据库三级模式结构直观示意图如图3.2所示。

3、解释本章所涉及的有关基本概念的定义：

基本表、导出表、视图、索引、聚集、系统特权、对象特权、角色，并说明视图、索引、聚集、角色的作用。

解：

1）

基本表：

基本表是本身独立存在的表，在SQL中一个关系就对应一个表。

导出表：

在若基本表上定义的查询表。

视图：

视图是从一个或几个基本表（或视图）导出的表，它与基本表不同，是一个虚表。

索引：

在现实生活中我们经常借用索引的手段实现快速查找，例如图书目录、词典索引等。

同样道理，数据库中的索引是为了加速对表中元组（或记录）的检索而创建的一种分散存储结构（如B+树数据结构），它实际上是记录的关键字与其相应地址的对应表。

索引是对表或视图而建立的，由索引页面组成。

聚集：

按照索引记录的存放位置可分为聚集索引与非聚集索引两类。

聚集索引是指索引项的顺序与表中记录的物理顺序一致的索引组织；非聚集索引按照索引的字段排列记录，但是排列的结果并不会与表记录顺序一致，而是另外组织存储。

在检索记录时，聚集索引会比非聚集索引速度快，一个表中只能有一个聚集索引，而非聚集索引可以有多个。

系统特权：

即系统权限，是指数据库系统级的权限，而不是依附于某数据库对象的权限。

如创建数据库表的权限：

CREATETABLE。

对象特权：

数据库管理员拥有系统权限，而作为数据库的普通用户，只对自己建的基本表、视图等数据库对象拥有对象权限。

所谓对象权限即是指依附于数据库对象如表、视图等的权限。

如对某表的添加记录、删除记录、修改记录、查询记录等的权限。

角色：

角色是对多种权限的集合的一种命名。

当要为某一用户同时授予或收回多项权限时，则可以把这些权限定义为一个角色，如此可对此角色内的所有权限进行整体操作。

这样就避免了许多重复性的工作，简化了管理数据库用户权限的人的工作。

可以把角色授予用户或其它角色。

2）以下简单说明视图、索引、聚集、角色的作用。

视图：

在关系数据库系统中，视图为用户提供了多种看待数据库数据的方法与途径，是关系数据库系统中的一种重要对象。

其作用有：

（1）视图能够简化用户的操作；

（2）视图使用户能以多种角度看待同一数据；（3）增加了数据的安全性；（4）视图对重构数据库提供了一定程度的逻辑独立性。

索引：

系统将自动使用索引进行查询优化，能明显加快对表数据的查询速度。

聚集：

聚集是一种数据组织方法，聚集索引是一类索引，由于索引信息与表记录的物理组织存放顺序一致，从而能减少定位索引信息的时间，能更快速地索引查找记录数据。

角色：

简化了数据库系统管理对权限的管理工作。

4、在对数据库进行操作的过程中，设置视图机制有什么优点？

它与数据表之间有什么区别？

解：

1）在关系数据库系统中，视图为用户提供了多种看待数据库数据的方法与途径，是关系数据库系统中的一种重要对象。

其优点有：

（1）视图能够简化用户的操作；

（2）视图使用户能以多种角度看待同一数据；（3）增加了数据的安全性；（4）视图对重构数据库提供了一定程度的逻辑独立性。

2）视图在概念上与基本表等同，一经定义，就可以和基本表一样被查询、被删除，我们也可以在一个视图上再定义新的视图，但对视图的更新（插入、删除、修改）操作相对于数据表来说，则有一定的限制。

即有些视图逻辑上是肯定不能修改的。

5、设有四个关系（只示意性给出一条记录）：

SSPJ

SNOSNAMEADDRESSTELSNOPNOJNOQTY

SlSN1上海南京路68564345S1PlJ1200

PJ

PNOPNAMESPECCITYCOLORJNOJNAMELEADERBG

P1PNl8X8无锡红J1JN1王总10

S（SNO，SNAME，ADDRESS，TEL）其中，SNO：

供应商代码SNAME：

姓名ADDRESS：

地址TEL：

电话；

J（JNO，JNAME，LEADER，BG）其中，JNO：

工程代码JNAME：

工程名LEADER：

负责人BG：

预算；

P（PNO，PNAME，SPEC，CITY，COLOR）其中，PNO：

零件代码PNAME：

零件名SPEC：

规格CITY：

产地COLOR：

颜色；

SPJ（SNO，JNO，PNO，QTY）其中，SNO：

供应商代码JNO：

工程代码PNO：

零件代码QTY：

数量；

（1）为每个关系建立相应的表结构，添加若干记录。

（2）完成如下查询：

1）找出所有供应商的姓名和地址、电话。

2）找出所有零件的名称、规格、产地。

3）找出使用供应商代码为S1供应零件的工程号。

selectjno

fromspj

wheresno='s1'

4）找出工程代码为J2的工程使用的所有零件名称、数量。

selectpname,qty

fromspj,p

wherespj.pno=p.pnoandjno='j2'

5）找出产地为上海的所有零件代码和规格。

6）找出使用上海产的零件的工程名称。

selectjname

fromj

wherejnoin（

selectjno

fromspj

wherepnoin

（selectpno

fromp

wherecity='上海'））

7）找出没有使用天津产的零件的工程号。

selectjno

fromj

wherejnonotin

（selectjno

fromspj,p

wherespj.pno=p.pnoandp.city='上海'））

8）求没有使用天津产的红色零件的工程号。

selectjno

fromj

wherejnonotin

（selectjno

fromspj,p

wherespj.pno=p.pnoandp.city='天津'andcolor='红'））

9）取出为工程J1和J2提供零件的供应商代号。

selectdistinctx.sno

fromspjx,spjy

wherex.jno='j1'andy.jno='j2'andx.sno=y.sno

10）找出使用供应商S2供应的全部零件的工程号。

selectjno

fromj

wherenotexists（

select*fromspjxwherex.sno='s2'and

notexists（

select*fromspjywherey.jno=j.jnoandx.pno=y.pno））

（3）完成如下更新操作：

1）把全部红色零件的颜色改成蓝色;

2）由S10供给J4的零件P6改为由S8供应，请作必要的修改。

3）从供应商关系中删除S2的记录，并从供应零件关系中删除相应的记录。

4）请将（S2,J8,P4,200）插入供应零件关系。

5）将工程J2的预算改为40万元。

6）删除工程J8订购的S4的零件。

（4）请将“零件”和“供应零件”关系的连接定义一个视图，完成下列查询：

1）找出工程代码为J2的工程使用的所有零件名称、数量。

2）找出使用上海产的零件的工程号。

解：

（1）

CREATETABLES（SNOVARCHAR（5）NOTNULLPRIMARYKEY,

SNAMEVARCHAR（20）NOTNULL,

ADDRESSNVARCHAR（30）,

TELVARCHAR（20））

INSERTINTOSVALUES（‘S1’,’SN1’,’上海南京路’,’68564345’）

……

CREATETABLEP（PNOVARCHAR（5）NOTNULLPRIMARYKEY,

PNAMEVARCHAR（20）NOTNULL,

SPECVARCHAR（20）,

CITYNVARCHAR（20）;

COLORNVARCHAR

（2））

INSERTINTOPVALUES（‘P1’,’PN1’,’8x8’,’无锡’,’红’）

……

CREATETABLEJ（JNOVARCHAR（5）NOTNULLPRIMARYKEY,

JNAMEVARCHAR（20）NOTNULL,

LEADERVARCHAR（10）,

BGINT）

INSERTINTOJVALUES（‘J1’,’JN1’,’王总’,10）

……

CREATETABLESPJ（SNOVARCHAR（5）NOTNULL,

PNOVARCHAR（5）NOTNULL,

JNOVARCHAR（5）NOTNULL,

QTYINT,

CONSTRAINTPR_SPJPRIMARYKEY（SNO,SPNO,JNO））

INSERTINTOSPJVALUES（‘S1’,’P1’,’J1’,200）

……

（2）

1）找出所有供应商的姓名和地址、电话。

SELECTSNAME,ADDRESS,TELFROMS

2）找出所有零件的名称、规格、产地。

SELECTONAME,SPEC,CITYFROMP

3）找出使用供应商代码为S1供应零件的工程号。

SELECTDISTINCTJNOFROMSPJWHERESNO=’S1’

4）找出工程代码为J2的工程使用的所有零件名称、数量。

SELECTPNAME,QTY

FROMSPJ,P

WHERESPJ.PNO=P.PNOANDJNO=’J2’

5）找出产地为上海的所有零件代码和规格。

SELECTPNO,SPECFROMPWHERECITY=’上海’

6）找出使用上海产的零件的工程名称。

SELECTJNOFROMFROMP,SPJ,J

WHEREP.CITY=’上海’ANDP.PNO=SPJ.PNOANDSPJ.JNO=J.JNO

7）找出没有使用天津产的零件的工程号。

SELECTJNO

FROMJ

WHEREJNONOTIN

（SELECTJNO

FROMSPJ,P

WHERESPJ.PNO=P.PNOANDP.CITY=’天津’）

8）求没有使用天津产的红色零件的工程号。

SELECTJNO

FROMJ

WHEREJNONOTIN

（SELECTJNO

FROMSPJ,P

WHERESPJ.PNO=P.PNOANDP.CITY=’天津’ANDP.COLOR=’红’）

9）取出为工程J1和J2提供零件的供应商代号。

SELECTSNO

FROMSPJ

WHEREJNO=’J1’ORJNO=’J2’

10）找出使用供应商S2供应的全部零件的工程号。

SELECTJNO

FROMJ

WHERENOTEXISTS

（SELECT*

FROMSPJX

WHEREX.SNO=’S1’ANDNOTEXISTS

（SELECT*

FROMSPJY

WHEREY.JNO=J.JNOANDY.PNO=X.PNO））

（3）

1）把全部红色零件的颜色改成蓝色;

UPDATEPSETCOLOR=’蓝’WHERECOLOR=’红’

2）由S10供给J4的零件P6改为由S8供应，请作必要的修改。

UPDATESPJSETSNO=’S8’WHERESNO=’S10’ANDPNO=’P6’ANDJNO=’J4’

若不能直接修改主码，则可如下操作：

先添加新记录，再删除老记录

Insertintospj

select’S8’,jno,pno,qty

fromspj

whereSNO=’S10’ANDPNO=’P6’ANDJNO=’J4’

deletefromspjwhereSNO=’S10’ANDPNO=’P6’ANDJNO=’J4’

3）从供应商关系中删除S2的记录，并从供应零件关系中删除相应的记录。

Deletefromspjwheresno=’s2’

Deletefromswheresno=’s2’

4）请将（S2,J8,P4,200）插入供应零件关系。

Insertintospjvalues（’S2’,’J8’,’P4’,200）

5）将工程J2的预算改为40万元。

UpdateJSETBG=40WHEREJNO=’J2’

6）删除工程J8订购的S4的零件。

题意为：

删除工程J8订购的S4提供零件的供应关系记录。

DELETEFROMSPJWHEREJNO=’J8’ANDSNO=’S4’

（4）

CREATEVIEWP_SPJ_VIEWASSELECTP.*,SPJ.SNO,SPJ.JNO,QTY

FROMP,SPJ

WHEREP.PNO=SPJ.PNO

1）找出工程代码为J2的工程使用的所有零件名称、数量。

SELECTPNAME,QTYFROMP_SPJ_VIEWWHEREJNO=’J2’

2）找出使用上海产的零件的工程号。

SELECTJNO

FROMP_SPJ_VIEW

WHERECITY=’上海’

6、在嵌入式SQL中如何区分SQL语句和主语句的？

举例说明。

解：

在嵌入式SQL中，为了能够区分SQL语句与主语言语句，所有SQL语句都必须加前缀EXECSQL。

SQL语句的结束标志则随主语言的不同而不同，例如在PL/1和C中以分号（；）结束，在COBOL中以END-EXEC结束。

这样，以C或PL/1作为主语言的嵌入式SQL语句的一般形式为：

EXECSQL;

举例：

如下一条交互形式的SQL语句：

DROPTABLES

嵌入到C程序中，应写作：

EXECSQLDROPTABLES

7、在嵌入式SQL中如何解决数据库工作单元与程序工作单元之间沟通的？

解：

嵌入式SQL语句中可以使用主语言的程序变量来输入或输出数据。

把SQL语句中使用的主语言程序变量称为主变量（HostVariable），主变量在数据库工作单元与程序工作单元之间起相互信息或数据沟通作用。

主变量在宿主语言程序与数据库之间的作用可参阅图3.5。

图3.5主变量的通信与传递数据的作用示意图

主变量根据其作用的不同，分为输入主变量、输出主变量和指示主变量。

输入主变量由应用程序对其赋值，SQL语句引用；输出主变量由SQL语句对其赋值或设置状态信息，返回给应用程序；一个主变量可以附带一个任选的指示主变量，指示主变量是一个整型变量，用来“指示”所指主变量的值的情况，指示主变量可以指示输入主变量是否希望设置为空值，可以检测输出主变量是否是空值（指示主变量为负值指示所指主变量为空值）。

一个主变量可能既是输入主变量又是输出主变量。

在SQL语句中使用这些变量时，需在主变量名前加冒号“：

”作为标记，以区别于表中的字段（或属性）名。

SQL语句在应用程序中执行后，系统要反馈给应用程序若干信息，这些信息送到SQL的通信区SQLCA（SQLCommunicationArea）。

SQLCA用语句EXECSQLINCLUDE加以定义。

SQLCA是一个数据结构（即SQLCA结构中含有能反映不同执行后状况的多个状态变量如：

SQLCODE、SQLERRD1、SQLERRMC、SQLWARN、SQLERRM等），SQLCA中有一个存放每次执行SQL语句后返回代码的状态变量SQLCODE。

当SQLCODE为零时，表示SQL语句执行成功，否则返回一个错误代码（负值）或警告信息（正值），一般程序员应该在每个SQL语句之后测试SQLCODE的值。

以便根据当前SQL命令执行情况决定后续的处理。

8、SQL的集合处理方式与宿主语言的单记录处理方式之间如何协调?

解：

我们知道一个SQL语句一般能处理一组记录，而主语言一次只能处理一个记录，为此必须协调两种处理方式，使它们相互协调地处理。

嵌入式SQL中是引入游标（Cursor）机制来解决这个问题的。

游标是系统为用户开设的一个数据内存缓冲区，用来存放SQL语句的执行结果，每个游标区都有一个名字。

用户可以通过游标逐一获取记录，并赋给主变量，再由主语言程序作进一步处理。

与游标有关的SQL语句有下列4个：

（1）游标定义语句DECLARE。

游标是与某个查询结果相联系的符号名，用SQL的DECLARE语句定义，它是说明性语句，定义时游标定义中的SELECT语句并不马上执行。

（情况与视图的定义相似）

（2）游标打开语句OPEN。

此时执行游标定义中的SELECT语句，同时游标缓冲区中含有SELECT语句执行后对应的所有记录，游标也处于活动状态。

游标指针指向游标中记录结果第一行之前。

（3）游标推进语句FETCH。

此时执行游标向前推进一行。

并把游标指针指向的当前记录读出，放到FETCH语句中指定的对应主变量中。

FETCH语句常置于主语言程序的循环结构中，通过循环逐一处理游标中的一个个记录。

（4）游标关闭语句CLOSE。

关闭游标，使它不再和原来的查询结果相联系，同时释放游标占用的资源。

关闭的游标可以再次打开、得到新的游标记录后再使用游标、再关闭。

在游标处于活动状态时，可以修改和删除游标指针指向的当前记录。