数据库期末考试重点

数据库的地位: 数据库技术是信息系统的核心和基础, 它的出现极大地促进了计算机应用向各行各业的渗透。数据库的建设规模、数据库信息量的大小和使用频度已成为衡量一个国家信息化程度的重要标志。数据库系统是最

数据库的地位: 数据库技术是信息系统的核心和基础, 它的出现极大地促进了计算机应用向各行各业的渗透。数据库的建设规模、数据库信息量的大小和使用频度已成为衡量一个国家信息化程度的重要标志。数据库系统是最广泛使用的软件系统之一。数据库研究, 开发与应用是计算机学科最活跃的领域之一。数据库系统原理主要讲述结构化数据的管理

实际应用对数据操作的要求：面临故障：各种各样的故障都可能发生，必须确保数据在任何情况下都不被破坏。例如，银行存款数据不能因突然停电而丢失或破坏。数据的安全性：防止用户对数据进行未经授权的访问。例如，可以允许银行客户查看自己的存款余额，但不能允许他/她们修改。数据的完整性：防止不符合语义的数据进入数据库。例如，我们不能将负数作为学生的成绩输入到数据库中。数据的一致性：防止数据库进入不一致状态

数据库管理系统（DBMS ）是一种重要的程序设计系统，它由一个相互关联的数据集合和一组访问这些数据的程序组成。这个数据集合称为数据库，它包含了一个企业、政府部门或一个单位的全部信息。DBMS 的基本目标是提供一个方便、有效地访问这些信息的环境。功能：1. 数据定义：提供数据定义语言（DDL ），用于定义数据库中的数据对象和它们的结构2. 数据操纵：提供数据操纵语言（DML ），用于操纵数据，实现对数据库的基本操作（查询、插入、删除和修改）3. 事务管理和运行管理：统一管理数据、控制对数据的并发访问保证数据的安全性、完整性. 4. 数据存储和查询处理:确定数据的物理组织和存取方式, 提供数据的持久存储和有效访问, 确定查询处理方法，优化查询处理过程5. 数据库的建立和维护:提供实用程序，完成数据库数据批量装载、数据库转储、介质故障恢复、数据库的重组和性能监测等6. 其他功能包括DBMS 与其它软件通信异构数据库之间数据转换和互操作

数据库（简称DB ）是持久储存在计算机中、有组织的、可共享的大量数据的集合

使用数据库的优点: (1) 数据整体结构化:在数据库中，数据的组织面向整个机构、面向所有可能的应用(2) 数据共享:数据库中的数据的面向整个机构组织使得它能够更好地被多个用户、多个应用程序共享。共享的好处是节省存储空间，尽量避免同一数据不必要地重复存放(冗余) (3) 数据独立性：数据独立性是指数据独立于应用程序，包括数据的物理独立性，数据的逻辑独立性4) 数据由DBMS 同一管理和控制，使得系统能够为数据管理提供更多的支持。(5) 标准化：使用数据库进行信息管理有利于制定部门标准、行业标准、工业标准、国家标准和国际标准，促进数据库管理系统和数据库开发工具的研制、开发，推动数据管理应用的健康发展

数据模型的三要素：1. 数据结构：数据结构描述数据库中的对象和对象之间的联系，是对系统静态特性的描述，数据结构刻画了数据模型最重要的方面，数据结构定义基本数据项的类型2. 数据操作：定义数据库中各种数据对象的实例上允许执行的操作和操作规则，是对系统动态特性的描述 。数据库操作主要包括查询和更新（包括插入、删除、修改）3. 数据的完整性约束条件：是一组规则，用以限定符合数据模型的数据库状态和状态的变化，保证数据的正确、有效和相容。

从用户角度，数据库系统的外部结构可以分为：单用户结构，主从式结构，分布式结构，客户/服务器结构，浏览器/应用服务器/数据库服务器结构

数据库系统三级模式结构：外模式（external schema）也称子模式或用户模式。外模式介于模式与应用之间，是特定数据库用户的数据视图，是与某一具体应用相关的数据局部逻辑结构的描述。模式（schema ）也称逻辑模式，是数据库中全体数据的总体逻辑结构描述，是所有用户的公共数据视图，模式综合了所有用户的数据需求，模式是数据库的中心与关键。内模式（internal sch ema ）也称存储模式或物理模式，是数据物理结构和存储方式的描述，定义数据在数据库内部的表示方式。内模式依赖于全局逻辑结构，但它既独立于数据库的用户视图（即外模式），也独立于具体的存储设备

所谓数据独立性是指数据与应用程序相互独立。数据的逻辑独立性：是指应用程序与数据库的逻辑结构之间的相互独立性。数据的物理独立性：是指应用程序与存储在磁盘上的数据库中数据之间的相互独立性

模式改变时，数据库管理员可以修改有关的外模式-模式映像，使外模式保持不变，为数据的逻辑独立性提供了保证；内模式改变时，数据库管理员可以修改模式-内模式映像，使得模式保持不变，为数据的物理独立性提供了保证。

关系模型：优点：关系模型具有坚实的数学基础。简洁、直观的数据表示形式。支持易学易用的非过程化语言 关系模型的三要素和三级模式结构： 数据结构：关系。数据操作：关系代数、关系演算（元组关系演算、域关系演算）。 完整性约束：实体完整性、参照完整性和用户自定义完整性。三级模式结构：关系模式：记录的型为关系模式，关系模式的集合是数据库的概念模式，关系模式用DDL 定义（包括：模式名，属性名，值域名，主码）。子模式：用户用到的那部分数据的描述（包括数据与关系模式中相应数据的联系）及操作权限的定义。存储模式：DBMS 中关系存储作为文件看待，存储一个关系可用散列、索引、堆等来实现

关系的完整性约束：实体完整性约束是对关系主码取值的基本限制。实体完整性规则：关系R 的所有元组在主码上的值必须唯一，并且在主码的任何属性上都不能取空值。向一个表中插入元祖或者修改一个表的主属性就能违反完整性。参照完整性是对外码取值的限制。参照完整性规则：如果属性集FK 是关系R 的外码，它参照关系S 的主码

Ks ，则R 的任何元组在FK 上的值或者等于S 的某个元组在主码Ks 上的值，或者为空值。用户定义的完整性反映特定的数据库所涉及的数据必须满足的语义约束条件。由于不存在一般性规则，这些约束条件必须由用户根据实际问题的语义指定

关系R 的属性集X 是它的超码，如果R 的所有可能实例都不包含两个不同的元组，它们在X 的所有属性上都具有相同的值

关系R 的属性集K 是它的码，如果K 是R 的超码，并且K 的任何真子集都不是R 的超码（即K 是极小超码）通常，当一个关系具有多个码时，应当选择其中的一个作为唯一识别关系元组的码

术语主码用于表示由多个码中选出的作为唯一识别关系元组的码，而所有的码又称候选码

形式地，如果FK 是关系R 的属性集，并且不是R 的码，但是FK 与关系R ’的主码K ’对应，则称FK 是关系R 的外码。其中R 是参照关系，R ’是被参照关系（R 与R ’不一定是不同的关系），并称FK 参照R ’的主码K ’

数据定义语言（DDL ）DDL 提供对数据定义进行修改和删除的功能。DDL 提供数据完整性约束条件的定定义机制。例如，下面是用SQL 语言定义的关系表Students ：

CREATE TABLE Students

( Sno CHAR(9) PRIMARY KEY,

Sname CHAR(8),

Ssex CHAR(2),

Sbirthday DATE,

Sspeciality CHAR(20));

数据操纵语言（DML ）表达对按照某种数据模式组织起来的数据的访问

数据控制语言（DCL ）用于定义用户对数据对象的访问权限和审计，提供授权语句保证数据的安全性，还提供了回收授权和建立审计的语句

视图：视图是一种命名的导出表，是从一个或几个基本表（或视图）导出的表。与基本表不同，视图的数据并不物理地存储在数据库中（物化视图除外）。查询时，凡是能够出现基本表的地方，都允许出现视图。

视图的作用1. 使用视图可以使一些查询表达更加简洁2. 视图提供了一定程度的逻辑独立性3. 视图的安全保护作用4. 视图使得用户能够以不同角度看待相同的数据

SQL 语句和主语言语句之间的信息交换（通信）可以通过：SQLCODE 。主语言变量。游标

游标 为什么需要游标：主语言是面向记录的过程式语言，而SQL 是面向集合的非过程式语言。存在矛盾：一个SQL 语句得到的结果可能是多个记录，而主语言没有办法一次处理多个记录解决该问题的方法是使用游标。

游标定义其实就是一个数据缓冲区，暂时存放SQL 语句的执行结果，以便主语言可以逐一获取记录，进行处理 所有使用游标的SQL 语句都必须先通过说明定义游标。在使用前打开游标。然后，反复推进游标指针并取当前记录进行处理。最后，当所有记录都处理完之后，关闭游标

不使用游标的语句包括：说明性语句。数据定义语句。数据控制语句。查询结果为单个记录的SELECT 语句和非交互形式的更新语句

数据完整性旨在保护数据库中的数据，防止合法用户对数据库进行修改时破坏数据的一致性；

数据安全性旨在保护数据库，防止未经授权的访问和恶意破坏和修改。

为了维护数据库的完整性，完整性控制应当作为DBMS 核心机制，必须提供：

说明和定义完整性约束条件的方法：DBMS 的DDL 允许用户根据实际问题的语义说明和定义各种完整性约束条件。 完整性检查机制：DBMS 在数据更新可能破坏完整性时自动进行完整性检查。检查可以在更新操作执行时立即执行，也可以在事务提交时进行。

违约处理：当数据更新违反完整性约束时，DBMS 应当采取相应的措施，确保数据的完整性。

判断主属性是否为空值是简单的。为了有效地判定主码上的值是否惟一，通常DBMS 自动在主码上建立索引（如B 树索引）。通过索引查找而不必访问任何元组就能确定主码上的值是否惟一。

当更新导致破坏参照完整性时，可能的处理措施包括：1. 拒绝：拒绝违反参照完整性的更新是最简单的处理措施

2. 级联：进行更新，并且对更新导致违反参照完整性的参照关系元组进行相应更新。3. 置空值：进行更新，并且对更新导致违反参照完整性的参照关系元组的外码置空值。这种处理方法仅当外码允许取空值时才能使用。4. 置缺省值：进行更新，并且对更新导致违反参照完整性的参照关系元组的外码置缺省值；其中缺省值必须是被参照关系某元组主码上的值 CASCADE 、SET NULL、SET DEFAULT和NO ACTION

域完整性约束：每个属性都必须在一个值域上取值。

断言（Assertions ）是一种命名约束，它表达了数据库状态必须满足的逻辑条件。数据库完整性约束可以看成一系

列断言的集合，实体完整性和参照完整性约束都是断言的特殊形式。所有的约束都可以用断言形式表达，然而属性约束和关系约束使用相应的方式说明更方便，也更有效。通常用断言表达数据库约束，这些约束不能或很难用其他方法表达。

触发器（trigger ）是特殊类型的存储过程，当某个事件发生时它自动执行。

要设置触发器机制，必须满足两个要求：(1) 指明什么事件发生和满足什么条件执行触发器； (2) 指明触发器执行什么样的动作

、数据库安全保护的任务：防止数据一致性的意外破坏比防止对数据库的恶意访问要容易。下面是一些恶意访问的形式：未经授权读取数据（窃取信息）；未经授权修改数据；未经授权删除数据。

数据库管理员（DBA ）的重要的责任之一是保证数据库的安全性，保护数据库涉及的任务：防止对数据的未经过授权的存取，确保敏感信息没有被不“需要知道”这些信息的人访问得到；防止未经过授权的人员删除和修改数据；

监视对数据的访问和更改等使用情况

数据库设计:根据选择的数据库的支撑环境（包括DBMS 、操作系统和硬件）和用户信息需求、处理需求，设计出数据模式（包括外模式、模式和内模式）及典型应用程序 .

数据库设计的目标:为用户和各种应用系统提供一个信息基础设施和高效的运行环境.(即存取效率高、存储空间的利用率高, 数据库系统运行管理效率高)

为什么说数据库设计在应用系统开发中起着重要的作用？

（1）不合适数据库设计，检索某些类型的信息是困难的或可能得到不正确的检索信息。

（2）不合适的数据库设计导致不正确的信息，不正确的信息会影响一个单位正常的使用这些数据

数据库设计与应用程序设计不同： 数据库设计的目标是获得合理数据模式和典型的应用程序，它是程序设计的基础，数据库设计的好坏直接影响着应用程序的性能；而应用程序设计是针对某一特定的数据库进行应用设计的，其基本任务是明确数据的组织、绘制数据流程图、设计用户界面以及应用程序的测试与审查等。

数据库设计的特点： 反复性， 试探性，多阶段性，多技术性

数据库设计的步骤：可行性分析：是确定数据库系统在单位的计算机系统中的地位以及各个数据库之间的关系，确定数据库支持的业务范围是建立一个综合的数据库还是建立若干个专门的数据库。需求分析阶段：收集分析用户对系统的信息需求和处理需求，得到设计系统所必须需求信息，建立系统说明文档。概念结构设计：根据系统需求，用概念数据模型表示数据及数据之间的联系。逻辑结构设计：在概念结构设计的基础上，按照一定原则将概念模式转换为某个具体DBMS 支持的逻辑结构。物理设计：数据库在物理设备上的存储结构和存取方法. 其目的是为了提高数据库的访问速度并有效地利用存储空间。。数据库的实施和维护：建立数据库模式、加载数据，而试运行将检验数据库系统的设计是否达到设计目标、能否满足实际需要

需求分析阶段的任务：调查应用领域，对应用领域中各种应用的信息要求、处理要求、安全性和完整性要求进行详细分析，形成需求分析说明书。

数据字典是关于数据库中数据性质的描述，即元数据，而不是数据本身。

数据字典应该具备查询方便、没有数据冗余、易于修改和更新等特点。

作用：它为设计人员提供了关于数据详细描述的信息，是下一步概念结构设计的输入，它和数据流图一起完整地描述了系统的需求信息

数据抽象一般有三种抽象方法：（1）将具有某些共同特性和行为的对象抽象为一个概念。 （2）定义某一类型的组成成分。3）定义对象集之间的子集联系

各局部E-R 图之间的冲突主要有三类：属性冲突、命名冲突和结构冲突。

查询处理步骤：查询；语法分析与翻译器；查询的内部表示；优化器（元数据）；查询执行计划；执行引擎；查询结果

查询优化就是从多种可能的查询执行方案中选择一种最有效执行的查询执行计划的过程。必要性：对于相同的查询，不同的查询执行计划的时间开销可能相差几个数量级。这意味好的执行计划可能是可行的，而差的执行计划在实践上是不可行。因此，即使查询只执行一次，查询优化也是必须的

事务是用户定义的一个数据库的操作序列，这些操作要么全做, 要么全不做，是一个不可分割的工作单元。事务是并发控制与调度的基本单位，也是数据库恢复的基本单位。四个特性：原子性一致性隔离性持久性

使用并发执行机制的动机：提高吞吐量和资源的利用率，减少等待时间

. 丢失修改两个或多个事务同时从数据库中读取相同的数据对象并进行修改，后提交的事务的修改覆盖了先提交的事务的修改，导致先提交的事务的修改丢失 。读“脏”数据读“脏”数据是指事务Ti 修改某一数据，并将其写回磁

盘。 不可重复读 ：修改删除插入

串行调度是指这些事务一个接一个地执行，其中每个事务都在上一个事务（如果有的话）完全结束之后才开始执行。 对于一组事务，串行调度总是正确的

基本的封锁类型有两种：共享锁（S 锁）：称读锁。T 可以读但不能写Q ，释放Q 上的S 锁之前，获得S 锁，不能获得X 锁。排他锁（X 锁）：称写锁。T 既可以读又可以写Q ，释放Q 上的X 锁之前，不能获得Q 上的S 锁，也不能获得Q 上的X 锁。

一级封锁协议：事务T 在更新数据对象Q 之前必须先对其加X 锁，直到事务结束才释放。（数据加锁与否都可以读） 。一级封锁协议可防止丢失修改。二级封锁协议在一级封锁协议的基础上，进一步要求事务T 在读取数据对象Q 之前必须先对其加S 锁，但是读完后可以立即释放S 锁 。可以进一步防止读“脏”数据，但不能保证可以重复读。三级封锁协议在一级封锁协议的基础上，进一步要求事务T 在读取数据对象Q 之前必须先对其加S 锁，并且直到事务结束才能释放S 锁。可以进一步保证可重复读，可以很好地避免并发导致的问题

两段锁协议：是最常用的一种封锁协议。理论上已经证明使用该协议可保证并发调度串行化。两阶段锁协议要求每个事务。在对任何数据进行读、写操作之前，首先要申请并获得对该数据对象的相应封锁。在释放一个锁之后，事务不能再申请任何其他锁 。两段封锁协议是可串行化调度的充分条件，其能够保证并发事务执行的正确性，保持数据库的一致性。但是，遵守两段锁协议的并发事务仍有可能发生死锁

一个事务获得它的最后一个锁的位置称为封锁点

系统能够同时支持多种封锁粒度供不同的事务选择的封锁方法称之为多粒度封锁

意向锁：IS 锁：意向共享锁，如果对一个数据对象加IS 锁，表示它的后裔结点拟（意向）加S 锁。IX 锁：意向排它锁，如果对一个数据对象加IX 锁，表示它的后裔结点拟（意向）加X 锁。SIX 锁： 共享意向排它锁，如果对一个数据对象加SIX 锁，表示对它加S 锁，再加IX 锁，即SIX = S IX

封锁可能产生的问题是活锁和死锁现象。活锁是不公平调度所引起的，容易处理。死锁又两种处理方法：预防、检测和解除。

数据库的恢复机制就是负责将数据库恢复到故障发生前的某一个一致状态，并将崩溃后的数据库不能使用的时间减少到最小。

1．事务故障是指某个事务在运行过程中由于种种原因未能运行到正常终止而夭折

2．系统故障是指由于某种原因造成整个系统的正常运行突然停止，致使所有正在运行的事务都以非正常方式终止

3．介质故障是指存储数据库的存储设备故障

恢复的基本思想：它们的基本思想是一样的：在系统正常运行时建立冗余数据，保证有足够的信息可用于故障恢复 。故障发生后采取措施，将数据库内容恢复到某个一致性状态，保证事务原子性和持久性。数据库系统主要通过登记日志和数据转储来建立冗余数据。日志记录了数据库的所有更新的详细信息，所有故障的恢复都需要使用它。数据转储制作数据库的后备副本，这些副本与日志配合使用，用来实现介质故障恢复。

1. 在检查点tc 之前已经提交的事务。这类事务已提交不需要做redo 。他们对数据库的更新已经建立在检查点时输出到数据库中。

2. 在检查点tc 之前开始执行，在检查点之后故障点tf 之前提交的事务。这类已提交的事务登记在检查点记录中，需要做redo 。

3. 在检查点tc 之前开始执行，在故障点tf 时尚未完成的事务。这类未完成的事务登记在检查点记录中需要做undo

4. 在检查点tc 之后开始执行，在故障点tf 之前提交的事务。这类已提交的事务的日志记录都在检查点之后，需要做redo 。

5. 在检查点tc 之后开始执行，在故障点tf 还未完成的事务。这类未完成的事务的日志记录都在检查点之后，需要做undo 。

Departments (Dno, Dname, Dheadno)Teachers (Tno, Tname, Sex, Birthday, Title, Dno)Students (Sno, Sname, Sex, Birthday,

Enroolyear, Speciality, Dno)Courses (Cno, Cname, Period, Credit)Teaches (Tno, Cno)SC (Sno, Cno, Grade)Evalues (Sno, Tno, Cno, Escore) Phones (Phone#, Dno, Office)

Departments (Dno, Dname, Dheadno)Teachers (Tno, Tname, Sex, Birthday, Title, Dno)Students (Sno, Sname, Sex, Birthday, Enrollyear, Speciality, Dno)Courses (Cno, Cname, Period, Credit)Teaches (Tno, Cno)SC (Sno, Cno, Grade)Evalues (Sno, Tno, Cno, Escore)的模式图

用关系代数式表示查询

(1)列出系编号为MA （数学系）的所有学生的详细信息δθσDno=’MA’(Students)

(2)列出所有课程的课程号、课程名和学分。（这是无条件查询。课程号、课程名和学分都是Course 的属性，因此回答该查询只需要将Course 投影到这些属性上）：ΠCno,Cname,Credit (Courses)

(3) 列出年龄不超过45岁的所有副教授的姓名、性别和年龄。ΠTname,Sex,Age(σAge<45∧Title=’副教授’(Teachers))

(4) 列出选修了课程号为CS201的课程的所有学生的学号ΠSno (σCno=’CS201’ (SC))

(1) 列出选修了课程号为CS201的课程的所有学生的学号和姓名ΠSno,Sname (σCno=’CS201’ (SC∞Students))

(2) 列出每个学生选修的每门课程的成绩，要求列出的学号、姓名、课程名和成绩ΠSno,Sname,Cname,Grade (Students∞SC ∞Courses)

(3) 求评估得分高于90分的教师所在院系名称、教师姓名、课程名和评估得分（TCscore ）

ΠDname,Tname,Cname,TCscore(ΠDno,Dname(Departments)∞ΠTno,Tname,Dno(Teachers)∞ΠTno,Cno(σTCscore>90(Teaches)∞ΠCno,Cname(Courses))

(1) 列出所有选修了CS101和CS202课程的学生的学号ΠSno(σCno=’CS101’ (SC))∩ΠSno(σCno=’CS202’ (SC)) 或者ΠSno,Cno (SC) ÷{CS101, CS202}

(2) 列出所有选修了全部课程的学生的学号和姓名ΠSno,Sname ((ΠSno,Cno (SC)÷ΠCno (Course))∞Students) ΠTno, Tname, (2008-year(Birthday)) as Age, Title (Teachers)

用元组关系演算表示查询

(1) 列出系编号为MA （数学系）的所有学生的详细信息{t | Students(t)∧t*Dno =’MA’}

(2) 列出所有课程的课程号、课程名和学分

{t(3) | (Еu)(Courses(u)∧t[Cno]=u[Cno]∧t[Cname]=u[Cname]∧t[Credit]=u[Credit])}

(3) 列出年龄不超过45岁的所有副教授的姓名、性别和年龄

{t(3) | (Еu)(Teachers(u)∧u*Title =’副教授’∧t[Tname]=u[Tname]∧t[Sex]=u[Sex]∧t[Age]=u[Age])}

(4) 列出选修了课程号为CS201的课程的所有学生的学号

{t(1) | (Еu)(SC(u)∧u*Cno =’CS201’∧t[Sno]=u[Sno])}

（1） 列出选修了课程号为CS201的课程的所有学生的学号和姓名

{t(2) | (E u)(E v)(SC(u)∧Students(v)∧u*Cno =’CS201’∧u[Sno]=v[Sno]∧t[Sno]=v[Sno]∧t[Sname]=v[Sname])}

(2) 列出每个学生选修的每门课程的成绩，要求列出的学号、姓名、课程名和成绩

{t(4) | (Εu)(Εv)(Εw)(Students(u) ∧SC(v)∧Courses(w)∧u[Sno]=v[Sno]∧v[Cno]=w[Cno]∧t[Sno]=u[Sno]∧ t*Sname =u*Sname ∧t[Cname]=v[Cname]∧t[Grade]=w[Grade])}

(3) 求评估得分高于90分的教师所在院系名称、教师姓名、课程名和评估得分

{t(4) | (Εt1)(Εt2)(Εt3)(Εt4)(Departments(t1)∧Teachers(t2)∧Teaches(t3)∧Courses(t4)∧t1[Dno]=t2[Dno]∧t2[Tno]=t3[Tno]∧t3[Cno]=t4[Cno]∧t[Dno]=t1[Dno]∧t[Tname]=t2[Tname]∧t[Cname]=t4[Cname]∧

t[TCscore]=t3[TCscore])}

删除这些元组： R =R －E

插入R ←R ∪E

修改。1. 对“大学路”支行的所有账户支付2.5的利息，可以用如下赋值：

Account ←ПAccount-no, Branch, Balance*1.025(σBranch=’大学路’(Account))∪(Account－σBranch=’大学路’(Account))

SQL （Structured Query Language ）结构化查询语言，是关系数据库的标准语言。SQL 是一个通用的、功能极强的关系数据库语言SQL 的特点集多种数据库语言于一体. 高度非过程化 . 面向集合的操作方式 . 一种语法两种使用方式 功能强大，语言简洁

创建选课表SC 用如下语句：

CREATE TABLE SC

(Sno CHAR (9),

Cno CHAR (5),

Grade SMALLINT CHECK (Grade>=0 AND Grade<=100),

PRIMARY KEY (Sno,Cno),

FOREIGN KEY (Sno) REFERENCES Students (Sno),

FOREIGN KEY (Cno) REFERENCES Courses (Cno));

创建学生表

CREATE TABLE Students

(Sno CHAR(9) PRIMARY KEY,

Sname CHAR(10) NOT NULL,

Sex CHAR (2) CHECK (Sex=’男’ OR Sex=’女’),

Birthday D ATE,

Enrollyear CHAR(4),

Speciality CHAR(20),

Dno CHAR (3),

FOREIGN KEY (Dno) REFERENCES Department (Dno));

创建老师CREATE TABLE Teaches

(Tno CHAR (7),

Cno CHAR (5),

TCscore SMALLINT,

PRIMARY KEY (Tno,Cno),

FOREIGN KEY (Tno) REFERENCES Teachers (Tno),

FOREIGN KEY (Cno) REFERENCES Courses (Cno));

例4.2 在Courses 中增加一个新列Pno ，表示课程的先行课的课程号，可以用

ALTER TABLE Courses ADD Pno CHAR (5);

在Students 的Sex 列设置缺省值“女”可以减少大约一半学生性别的输入。可以用如下语句来设置缺省值： ALTER TABLE Students ALTER Sex SET DEFAULT ‘女’;

而删除Sex 上的缺省值可以用

ALTER TABLE Students ALTER Sex DROP DEFAULT;

删除Courses 中的Pno 列可以用

ALTER TABLE Courses DROP Pno;

如果用如下语句删除SC 表

DROP TABLE SC RESTRICT;

则仅当没有依赖于SC 的任何数据库对象删除才能成功。然而，如果用

DROP TABLE SC CASCADE;

则表SC 和依赖它的数据库对象都被彻底删除

例4.4 在Students 的Dno 上创建一个名为Student_Dept的索引

可以用：

CREATE INDEX Student_Dept ON Students (Dno);

而在Teachers 上的Dno 创建一个名为Teacher-Dept 的聚簇索引可以用：

CREATE CLUSTER INDEX Teacher_Dept ON Teachers (Dno);

删除索引Student_Dept。

DROP INDEX Student_Dept

例4.6 为WangQiang 创建一个名为Supply_schema的模式，可以用：

CREATE SCHEMA Supply_schema AUTHORIZATION WangQiang;

所创建的模式在当前目录下，并为WangQiang 所拥有

如果用

CREATE SCHEMA Supply_schema;

则创建一个名为Supply_schema的模式，但未向任何用户授权

如果用

CREATE SCHEMA AUTHORIZATION WangQiang;

则为WangQiang 创建一个模式，并用WangQiang 命名

还可以在创建模式的同时创建该模式中的对象

例如

CREATE SCHEMA Supply_schema

CREATE TABLE Suppliers

(Sno CHAR(5) PRIMERY KEY,

Sname CHAR(20) NOT NULL,

Status SMALLINT,

Address CHAR(30),

Phone CHAR(10));

在创建模式Supply_schema的同时还在该模式中定义了一个基本表Suppliers

例4.7 语句

DROP SCHEMA Supply_schema RESTRICT

仅当模式Supply_schema中不包含任何数据库对象时，才删除模式Supply_schema，否则什么也不做。而 DROP SCHEMA Supply_schema CASCADE

将直接删除模式Supply_schema，并同时删除该模式中所有的数据库对象

例4.13(1) 查询所有以“数据”开头的课程名。

SELECT Cname

FROM Courses

WHERE Cname LIKE ‘数据’;

(2) 查询姓李并且姓名只有两个汉字的学生的学号和姓名。

SELECT Sno, Sname

FROM Students

WHERE Sname LIKE ‘李_ _’

（3）查询以C_打头的课程的详细信息。

SELECT *

FROM Courses

WHERE Cname LIKE ‘C_’ ESCAPE ‘’;

(2) 查询每位学生的每门课程的成绩，并将查询结果按课程号升序、成绩降序排序。

SELECT *

FROM SC

ORDER BY Cno, Grade DESC; 升序ASE

例4.17 查询每个学生的平均成绩，输出学生的学号和平均成绩。

SELECT Sno, AVG (Grade)

FROM SC

GROUP BY Sno; 例4.18 查询每个学生的平均成绩，并输出平均成绩大于85的学生学号和平均成绩。 SELECT Sno, AVG (Grade)

FROM SC

GROUP BY Sno HAVING AVG (Grade)>85;

例4.21 查询每个学生选修的每门课程的成绩，要求列出的学号、姓名、课程名和成绩。 SELECT Student.Sno, Sname, Cname, Grade

FROM Students, SC, Courses

WHERE Students.Sno = SC. Sno AND SC.Cno = Course. Cno;

例4.26 查询平均成绩最高的课程的课程号和平均成绩。

SELECT Cno, AVG(Grade)

FROM SC

GROUP BY Cno

HAVING AVG(Grade) >=

ALL (SELECT AVG(Grade)

FROM SC

GROUP BY Cno);

例4.28 查询选修了全部课程的学生的学号和姓名。：

SELECT Sno, Sname

FROM Students S

WHERE NOT EXISTS

(SELECT *

FROM Courses C

WHERE NOT EXISTS

(SELECT *

FROM SC

WHERE SC.Sno= S.Sno AND SC.Cno= C.Cno))

例4.29 查询至少选修了学号为200515122的学生选修的全部课程的学生的学号和姓名。 SELECT Sno, Sname

FROM Students S

WHERE NOT EXISTS

(SELECT *

FROM SC SC1

WHERE SC1.Sno = ‘200515122’ AND NOT EXISTS

(SELECT *

FROM SC SC2

WHERE SC2.Sno=S.Sno AND SC2.Cno=SC1.Cno))

例4.30 查询只讲授一门课程的教师的姓名和职称

SELECT Tname, Title

FROM Teachers T

WHERE UNIQUE

(SELECT Tno

FROM Teaches TC

WHERE T.Tno=TC.Tno)

例4.31 查询平均成绩高于85分的课程的课程号

SELECT Cno, Avg_grade

FROM (SELECT Cno, AVG (Grade)

FROM SC

GROUP BY Cno) AS Course_avg_grade (Cno, Avg_grade)

WHERE Avg_grade>85;

例4.34 查询既选修了CS301号课程, 又选修了CS306号课程的学生的学号

SELECT DISTINCT Sno

FROM SC

WHERE Cno=‘CS301’ AND Sno IN

(SELECT Sno

FROM SC

WHERE Cno=‘CS306’);

例4.36 将学号为200616010、姓名为司马相如、性别为男、生日为1985-01-28、入校年份为2006年、专业为计算数学、所在院系为MATH 的学生元组插入到Students 表中。

INSERT INTO Students (Sno, Sname, Sex, Birthday, Enrollyear,

Speciality, Dno)

VALUES (‘200616010’, ‘司马相如’, ‘男’, 1985-01-28, ‘2006’,

‘计算数学’, ‘MATH ’);

例4.37下面的语句将删除计算机软件与理论专业的所有学生的选课记录：

DELETE FROM SC

WHERE Sno IN

(SELECT Sno

FROM Students

WHERE Speciality＝‘计算机软件与理论’);

例4.42 建立软件工程专业学生的视图SE_Students，它包含Students 中除Speciality 之外的所有属性和软件工程专业所有学生的信息

CREATE VIEW SE_Students

AS SELECT Sno, Sname, Sex, Birthday, Dno

FROM Students

WHERE Speciality = ‘软件工程’

WITH CHECK OPTION;