第三章——关系数据库标准语言SQL——数据定义

SQL概述

• 结构化查询语言(Structured Query Language,SQL)是关系数据库的标准语言，其功能不仅仅是查询，而是包括数据库模式创建、数据库数据的插入与修改、数据库安全性完整性定义与控制等一系列功能。

SQL的特点

SQL集数据查询(data query)、数据操作(data manipulation)、数据定义(data defination)和数据控制(data control)功能于一体，其主要特点包括以下几部分：

1. 综合统一

• SQL集数据定义语言(DDL)、数据操纵语言(DML)、数据控制语言(DCL)的功能于一体
• 可以独立完成数据库生命周期中的全部活动
• 用户数据库投入运行后，可根据需要随时、逐步修改模式，不影响数据库的运行
• 数据操作符统一

2. 高度非过程化

• 非关系数据模型(层次模型、网状模型)的数据操纵语言是"面向过程"的语言，用"过程化"语言完成某项请求必须指定存取路径。而用SQL进行数据操作，用户只需提出"做什么"，而不必指明"怎么做"，因此无需了解存取路径。存取路径的选择以及SQL的操作过程由系统自动完成。

3. 面向集合的操作方式

• 非关系数据模型采用的是面向记录的操作方式，操作对象是一条记录；而SQL采用集合操作方式，不仅操作对象，查找结果可以是元组的集合，而且一次插入、删除、更新操作的对象也可以是元组的集合。

4. 以同一种语法结构提供多种使用方式

• SQL既是独立的语言，又是嵌入式语言。作为独立的语言，它能够独立地用于联机交互的使用方式，用户可以在终端键盘上直接输入SQL命令对数据库进行操作；作为嵌入式语言，SQL语句能够嵌入到高级语言(例如C、C++、Java)程序中，供程序员设计使用。而在两种不同的使用方式下，SQL的语法结构基本上是一致的。这种以统一的语法结构提供多种不同使用方式的做法，提供了极大的灵活性与方便性

5. 语言简洁，易学易用

• SQL功能极强，由于设计巧妙，语言十分简洁，完成核心功能只用了9个动词。

SQL功能	动词
数据查询	SELECT
数据定义	CREATE，DROP，ALTER
数据操纵	INSERT，UPDATE，DELETE
数据控制	GRANT，REMOVE

SQL的基本概念

支持SQL的关系数据库管理系统同样支持关系数据库三级模式结构

• 其中外模式包括若干试图(view)和部分基本表(base table)，模式包括若干基本表，内模式包括若干存储文件(stored file)
• 用户可以用SQL对基本表和视图进行查询或其他操作，基本表和视图一样，都是关系
• 基本表是本身独立存在的表，在关系数据库管理系统中一个关系就对应一个基本表。一个或多个基本表对应一个存储文件，一个表可以带若干索引，索引也存放在存储文件中。存储文件的逻辑结构组成了关系数据库的内模式。存储文件的物理结构对最终用户是隐蔽的。
• 视图是从一个或几个基本表导出的表。它本身不独立存储在数据库中，即数据库中只存放视图的定义而不存放视图对应的数据。这些数据仍存放在导出视图的基本表中，因此视图是个虚表。视图在概念上与基本表等同，用户可以在视图上再定义视图。

数据定义

• 关系数据库系统支持三级模式结构，其模式、外模式和内模式中的基本对象有模式、表、视图和索引等。因此SQL的数据定义功能包括模式定义、表定义、视图和索引的定义

操作对象	操作方式
	创建	删除	修改
模式	CREATE SCHEMA	DROP SCHEMA
表	CREATE TABLE	DROP TABLE	ALTER TABLE
视图	CREATE VIEW	DROP VIEW
索引	CREATE INDEX	DROP INDEX	ALTER INDEX

• SQL标准不提供修改模式定义和修改视图定义的操作。如果用户要修这些对象，只能先将它们删除然后再重建

模式的定义与删除

定义模式

• 在SQL中，模式定义语句如下：CREATE SCHEMA <模式名> AUTHORIZATION <用户名>;
• 如果没有指定<模式名>，那么<模式名>隐含为<用户名>
• 要创建模式，调用该命令的用户必须拥有数据库管理员权限，或者获得了数据库管理员授予的CREATE SCHEMA的权限

例1：为用户WANG定义一个学生-课程模式S-T
CREATE SCHEMA “S-T” AUTHORIZATION WANG;

• 定义模式实际上定义了一个命名空间，在这个空间中可以进一步定义该模式包含的数据库对象，例如基本表、视图、索引等
• 目前，在CREATE SCHEMA中可以接收CREATE TABLE，CREATE VIEW和GRANT子句。也就是说用户可以在创建模式的同时在这个模式定义中进一步创建基本表、视图，定义授权。
  即CREATE SCHEMA <模式名> AUTHORIZATION <用户名> [<表定义子句>|<视图定义子句>|<授权定义子句>];
  

删除模式

• 在SQL中，删除模式语句如下：DROP SCHEMA <模式名> <CASCADE|RESTRICT>;
• 其中CASCADE和RESTRICT两者必选其一。选择了CASCADE(级联)，表示在删除模式的同时把该模式中所有的数据库对象删除；选择了RESTRICT(限制)，表示如果该模式中已经定义了下属的数据库对象(如表、视图等)，则拒绝该删除语句的执行。只有当该模式中没有任何下属的对象时才能执行DROP SCHEMA语句。

例3：DROP SCHEMA ZHANG CASCADE;
该语句删除了模式ZHANG，同时，该模式中已经定义的表TAB1也被删除了

基本表的定义、删除与修改

定义基本表

• 创建了一个模式就建立了一个数据库的命名空间，一个框架。在这个空间中首先要定义的是该模式包含的数据库基本表。
• SQL语言使用CREATE TABLE语句定义基本表，其基本格式如下：
  
• 建表的同时通常还可以定义与该表有关的完整性约束条件，这些完整性约束条件被存入系统的数据字典中，当用户操作表中的数据时由关系数据库管理系统自动检查该操作是否违背这些完整性约束条件。如果完整性约束条件设计该表的多个属性列，则必须定义在表级上，否则既可以定义在列级也可以定义在表级。

例4：建立一个"学生表"Student
CREATE TABLE Student
(Sno CHAR(9) PRIMARY KEY, /*列级完整性约束条件，Sno是主码*/
Sname CHAR(20) UNIQUE, /*Sname取唯一值*/
Ssex CHAR(2),
Sage SMALLINT,
Sdept CHAR(20)
);

例5：建立一个"课程"表Course
CREATE TABLE Course
(Cno CHAR(4) PRIMARY KEY, /*列级完整性约束条件，Cname不能取空指*/
Cname CHAR(40) NOT NULL,
Cpno CHAR(4),
Ccredit SMALLINT,
FOREIGN KEY(Cpno) REFERENCES Course(Cno) /*表级完整性约束条件，Cpno是外码，被参照表是Course，被参照列是Cno*/
);

例6：建立学生选课SC
CREATE TABLE SC
(Sno CHAR(9),
Cno CHAR(4),
Grade SMALLINT,
PRIMARY KEY(Sno,Cno), /*主码由两个属性构成，必须作为表级完整性进行定义*/
FOREIGN KEY(Sno) REFERENCES Student(Sno),
FOREIGN KEY(Cno) REFERENCES Course(Cno)
);

数据类型

• 一个属性选用哪种数据类型要根据实际情况来决定，一般要从两个方面来考虑，一是取值范围，二是要做哪些运算

模式与表

每一个基本表都属于某一个模式，一个模式包含多个基本表。当定义基本表时一般可以有三种方法定义它所属的模式

• 方法一：在表名中明显地给出模式名
• 方法二：在创建模式语句中同时创建表
• 方法三：设置所属地模式，这样在创建表时表名中不必给出模式名

修改基本表

• SQL语言用ALTER TABLE语句修改基本表，其一般格式为：
  ALTER TABLE<表名>
  [ADD [COLUM] <新列名><数据类型> [完整性约束]]
  [ADD<表级完整性约束>]
  [DROP [COLUMN] <列名> [CASCADE|RESTRICT]]
  [DROP CONSTRAINT<完整性约束名> [RESTRICT|CASCADE]]
  [ALTER COLUMN <列名><数据类型>];
• 其中<表名>是要修改的基本表，ADD子句用于增加新列、新的列级完整性约束条件和新的表级完整性约束条件。DROP COLUMN子句用于删除表中的列，如果指定了CASCADE短语，则自动删除引用了该列的其他对象，比如视图；如果指定了 RESTRICT短语，则如果该列被其他对象引用，RDBMS将拒绝删除该列。DROP CONSTRAINT子句用于删除指定的完整性约束条件。ALTER COLUMN子句用于修改原有的列定义，包括修改列名和数据类型。

例7：向Student表增加"入学时间"列，其数据类型为日期型
ALTER TABLE Student ADD S_entrance DATE;

• 不论基本表中原来是否已有数据，新增加的列一律为空值

例8：将年龄的数据类型由字符型(假设原来的数据类型是字符型)改为整数
ALTER TABLE Student ALTER COLUMN Sage INT;

例9：增加课程名称必须取唯一值的约束条件
ALTER TABLE Course ADD UNIQUE(Cname);

删除基本表

• 当某个基本表不再需要时，可以使用DROP TABLE语句删除它。其一般格式为：DROP TABLE <表名> [RESTRICT|CASCADE];
• 若选择RESTRICT，则该表的删除是有限制条件的。欲删除的基本表不能被其它表的约束所引用(如CHECK，FOREIGN KEY等约束)，不能有视图，不能有触发器(trigger)，不能有存储过程或函数等。如果存在这些依赖该表的对象，则此表不能被删除。
• 若选择CASCADE，则该表的删除没有限制条件。在删除基本表的同时，相关的依赖对象，例如视图，都将被一起删除。

索引的建立与删除

当表的数据量比较大时，查询操作会比较耗时。建立索引是加快查询速度的有效手段。数据库索引有多种类型，常见索引包括顺序文件上的索引、B+树索引、散列(hash)索引、位图索引等。

• 顺序文件上的索引是针对按指定属性值升序或降序存储的关系，在该属性上建立一个顺序索引文件，索引文件由属性值和相应的元组指针组成
• B+树索引是将索引属性组织成B+树形式，B+树的叶结点为属性值和相应的元组指针。B+树索引具有动态平衡的优点。
• 散列索引是建立若干个桶，将索引属性按照其散列函数值映射到相应桶中，桶中存放索引属性值和相应的元组指针。散列索引具有查找速度快的特点。
• 位图索引是用位向量记录索引属性中可能出现的值，每个位向量对应一个可能值

建立索引

• 在SQL语句中，建立索引使用CREATE INDEX语句，其一般形式为：
  CREATE [UNIQUE] [CLUSTER] INDEX <索引名> ON <表名>(<列名> [<次序>] [,<列名>[<次序>]]…);
• 其中，<表名>是要建索引的基本表的名字。索引可以建立在该表的一列或多列上，各列名之间用逗号分隔，每个<列名>后面还可以用<次序>指定索引值的排列次序，可选ASC(升序)或DESC(降序)，默认值为ASC。
• UNIQUE表明此索引的某一个索引值只对应唯一的数据记录
• CLUSTER表示要建立的索引是聚簇索引

例10：为学生-课程数据库中的Student、Course和SC三个表建立索引。其中Student表按学号升序建唯一索引，Course表按课程号升序建唯一索引，SC表按学号升序和课程号降序建唯一索引
CREATE UNIQUE INDEX Stusno ON Student(Sno);
CREATE UNIQUE INDEX Coucno ON Course(Cno);
CREATE UNIQUE INDEX Sco ON SC(Sno ASC,Cno DESC);

修改索引

• 对于已经建立的索引，如果需要对其重新命名，可以使用ALTER INDEX语句。其一般格式为：ALTER INDEX <旧索引名> RENAME TO <索引名>;

删除索引

• 索引一经建立就由系统使用和维护，不需用户干预。建立索引是为了减少查询操作的时间，但如果数据增、删、改频繁，系统会花费许多时间来维护索引，从而降低了查询效率。
• 在SQL中，删除索引使用DROP INDEX语句，其一般格式为：DROP INDEX <索引名>;

数据字典

• 数据字典是关系数据库管理系统内部的一组系统表，它记录了数据库中所有的定义信息，包括关系模式定义、视图定义、索引定义、完整性约束定义、各类用户对数据库的操作权限、 统计信息等。关系数据库管理系统在执行SQL的数据定义语句时，实际上就是在更新数据字典表中的相应信息。在进行查询优化和查询处理时，数据字典中的信息是其重要依据。