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

3.3  数据定义

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

表3.3

    一个关系数据库管理系统的实例（instance）中可以建立多个数据库，一个数据库中可以建立多个模式，一个模式下通常包括多个表、视图和索引等数据库对象。

3.3.1  模式的定义与删除

01 定义模式

     在SQL中，模式定义语句如下：

create schema <模式名> authorization <用户名>；

     如果没有指定<模式名>，那么<模式名>隐含为<用户名>。

     要创建模式，调用该命令的用户必须拥有数据库管理员权限，或者获得了数据库管理员授予的create schema的权限。

例3.1  为用户WANG定义一个学生-课程模式S-T。

create schema “S-T” authorization WANG；

例3.2  create schema authorization WANG；

该语句没有指定<模式名>，所以<模式名>隐含为用户名WANG。

     目前，在create schema中可以接受create table，create view和grant子句。也就是说用户可以在创建模式的同时在这个模式定义中进一步创建基本表、视图，定义授权。即

    create schema <模式名> authorization <用户名> [<表定义子句>|<视图定义子句>|<授权定义子句>]；

例3.3  为用户ZHANG创建一个模式TEST，并且在其中定义一个表TAB1。

create schema TEST authorization ZHANG
create TABLE TAB1(  COL1 SMALLINT,
            COL2 INT,
            COL3 CHAR(20),
            COL4 NUMERIC(10,3),
            COL5 DECIMAL(5,2)
);

02删除模式

     在SQL中，删除模式语句如下：

drop schema <模式名> <cascade|restrict>；

     其中cascade和restrict两者必选其一。选择了cascade（级联），表示在删除模式的同时把该模式中所有的数据库对象全部删除；选择了restrict（限制），表示如果该模式中已经定义了下属的数据库对象（如表、视图等），则拒绝该删除语句的执行。只有当该模式中没有任何下属的对象时才能执行drop schema语句。

例3.4  drop schema ZHANG cascade；

    该语句删除了模式ZHANG，同时，该模式中已经定义的表TAB1也被删除了。

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

01定义基本表

    SQL语句使用create table语句定义基本表，其基本格式如下：

create table <表名>(
    <列名><数据类型>[列级完整性约束条件]
    [,<列名><数据类型>[列级完整性约束条件]]
    ···
    [,<表级完整性约束条件>]
)；

例3.5  建立一个“学生”表Student。

create table Student
(Sno char(9) primary key,      
          /*列级完整性约束条件，Sno是主码*/
Sname char(20) unique,                       /*Sname取唯一值*/
Ssex char(2),
Sage smallint,
Sdept char(20)
);

例3.6  建立一个“课程”表Course。

create table Course
(Cno char(4) primary key,
Cname char(40) not null,
Cpno char(4),
Ccredit smallint,
foreign key(Cpno) references Course(Cno)
 /*表级完整性约束条件，Cpno是外码，被参照表是Course，被参照列是Cno*/
);

本例说明参照表和被参照表可以是同一个表。

例3.7  建立学生选课表SC。

create table SC
(Sno char(9),
Cno char(4),
Grade smallint,
primary key(Sno,Cno),
foreign key(Sno) references Student(Sno),
    /*表级完整性约束条件，Sno是外码，被参照表是Student*/
foreign key(Cno) references Course(Cno)
    /*表级完整性约束条件，Cno是外码，被参照表是Course*/
);

02 数据类型

表3.4  数据类型

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

03 模式与表

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

方法一，在表名中明显地给出模式名。

create table “S-T”.Student(···)；
        /*Student所属的模式是S-T*/
create table “S-T”.Course(···)；
        /*Course所属的模式是S-T*/
create table “S-T”.SC(···)；
        /*SC所属的模式是S-T*/

方法二，在创建模式语句中创建表。

方法三，设置所属的模式，这样在创建表时表名中不必给出模式名。

     当用户创建基本表（其他数据库对象也一样）时若没有指定模式，系统根据搜索路径（search path）来确定该对象所属的模式。

     搜索路径包含一组模式列表，关系数据库管理系统会使用模式列表中第一个存在的模式作为数据库对象的模式名。若搜索路径中的模式名都不存在，系统将给出错误。

     使用下面的语句可以显示当前的搜索路径：show search_path;

     搜索路径的当前默认值是$user,public。其含义是首先搜索与用户名相同的模式名，如果该模式名不存在，则使用public模式。

     数据库管理员也可以设计搜索路径，例如：set search_path to “S-T”,public;

     然后，定义基本表：create table Student（···）；

     实际结果是建立了S-T.Student基本表。因为关系数据库管理系统发现搜索路径中第一个模式名S-T存在，就把该模式作为基本表Student所属的模式。

04 修改基本表

     SQL语言用alter table语句修改基本表，其一般格式为

alter table <表名>
     [add [column] <新列名><数据类型>[完整性约束]]
      [add <表级完整性约束>]
      [drop [column] <列名> [cascade|restrict]]
   [drop constraint <完整性约束名>[restrict|cascade]]
      [alter column <列名><数据类型>]；

     其中<表名>是要修改的基本表，add子句用于增加新列、新的列级完整性约束条件和新的表级完整性约束条件。drop column子句用于删除表中的列，如果指定了cascade短语，则自动删除引用了该列的其他对象，比如视图；如果指定了restrict短语，则如果该列被其他对象引用，RDBMS将拒绝删除该列。drop constraint子句用于删除指定的完整性约束条件。alter column子句用于修改原有的列定义，包括修改列名和数据类型。

例3.8  向Student表增加“入学时间”列，其数据类型为日期型。

alter table Student add S_entrance date;

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

例3.9  将年龄的数据类型由字符型（假设原来的数据类型是字符型）改为整数。

alter table Student alter column Sage INT;

例3.10  增加课程名称必须取唯一值的约束条件。

alter table Course add unique(Cname);

05 删除基本表

     当某个基本表不在需要时，可以使用drop table语句删除它。其一般格式为：

drop table <表名> [restrict|cascade];

     若选择restrict，则该表的删除是有限制条件的。欲删除的基本表不能被其他表的约束所引用（如check，foreign key等约束），不能有视图，不能有触发器（trigger），不能有存储过程或函数等。如果存在这些依赖该表的对象，则此表不能被删除。

     若选择cascade，则该表的删除没有限制条件。在删除基本表的同时，相关的依赖对象，例如视图，都将被一起删除。

     默认情况是restrict。

例3.11  删除Student表。

drop table Student cascade；

例3.12  若表上建有视图，选择restrict时表不能删除；选择cascade时可以删除表，视图也自动被删除。

create view IS_Student         /*Student表上建立视图*/
as
select Sno，Sname,Sage
from Student
where Sdept = ‘IS’;
drop table Student restrict;        /*删除Student表*/
--ERROR:cannot drop table Student because other objects depend it
      /*系统返回错误信息，存在依赖该表的对象，此表不能被删除*/
drop table Student cascade；        /*删除Student表*/
--NOTICE:drop cascade to view IS_Student
        /*系统返回提示，此表上的视图也被删除*/
select * from IS_Student；
--ERROR:relation “IS_Student” does not exist

表3.5  drop table时，SQL2011与3个关系数据库管理系统的处理策略比较

3.3.3  索引的建立与删除

建立索引的目的：加快查询速度。

     数据库索引有多种类型，常见索引包括顺序文件上的索引、B+树索引、散列（hash）索引、位图索引等。

     顺序文件上的索引是针对按指定属性值升序或降序存储的关系，在该属性上建立一个顺序索引文件，索引文件由属性值和相应的元组指针组成。

     B+树索引是将索引属性组织成B+树形式，B+树的叶结点为属性值和相应的元组指针。B+树索引具有动态平衡的优点。

     散列索引是建立若干个桶，将索引属性按照其散列函数值映射到相应桶中，桶中存放索引属性值和相应的元组指针。散列索引具有查找速度快的特点。

     位图索引是用位向量记录索引属性中可能出现的值，每个位向量对应一个可能值。

    关系数据库管理系统在执行查询时会自动选择合适的索引作为存取路径，用户不必也不能显式地选择索引。索引是关系数据库管理系统的内部实现技术，属于内模式的范畴。

01 建立索引

     在SQL语言中，建立索引使用create index语句，其一般格式为

create [unique][cluster] index <索引名>
      on <表名>(<列名>[<次序>][,<列名>[<次序>]]···)；

     其中，<表名>是要建索引的基本表的名字。索引可以建立在该表的一列或多列上，各列名之间用逗号分隔。每个<列名>后面还可以用<次序>指定索引值的排列次序，可选asc（升序）或desc（降序），默认值为asc。

    unique表明此索引的每一个索引值只对应唯一的数据记录。

     cluster表示要建立的索引是聚簇索引。

例3.13  为学生-课程数据库中的Student、Course和SC三个表建立索引。其中Student表按学号升序建唯一索引，Course表按课程号升序建唯一索引，SC表按学号升序和课程号降序建唯一索引。

create unique index Stusno on Student(Sno);
create unique index Coucno on Course(Cno);
create unique index SCno on SC(Sno asc,Cno desc);

02 修改索引

     对于已经建立索引，如果需要对其重新命名，可以使用alter index语句。其一般格式为

    alter index <旧索引名> rename to <新索引名>；

例3.14  将SC表的SCno索引名改成SCSno。

alter index SCno rename to SCSno；

03 删除索引

     在 SQL中，删除索引使用drop index语句，其一般格式为

drop index Stusname；

     删除索引时，系统会同时从数据字典中删去有关该索引的描述。

3.3.4  数据字典

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