数据库系统概论 ---- 第三章 -- 关系数据库标准语言SQL(3.1 - 3.3)（下）

3.3.1 模式的定义和删除

1. 定义模式

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

create schema '模式名' authorization '用户名';

MySQL中不存在 authorization 关键词

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

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

在MySQL中模式与数据库被认为一样，创建模式即创建数据库，二者没有区分，create schema与create database都是创建一个数据库。

定义模式实际上定义了一个命名空间，在这个空间中可以进一步定义该模式包含的数据库对象。

在create schema中可以接受 create table，create view，grant子句，用户可以在创建模式的同时在这个模式定义中创建基本表、视图，定义授权。

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

• |为或。
• []内的，表示可选，可以选择设置也可以选择不设置。

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

2. 删除模式

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

drop schema '模式名' cascade | restrict ;

• |为或
  
• 其中cascade和restrict两者必选其一。
  
• 选择了cascade(级联)，表示在删除模式的同时把该模式下的所有数据库对象全部删除；
  
• 选择了restrict(限制)，表示如果该模式中已经定义了下属的数据库对象，则拒绝该删除语句的执行，只有当该模式下没有任何下属对象时才执行删除语句。
  
• MySQL中不支持在删除模式时候指定删除时的处理方式

• 在MySQL中可以使用DROP SCHEMA 数据库名;删除对应的数据库，不支持指定删除时候的处理方式，默认为级联删除。
  

例题：

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

1. 定义基本表

创建了一个模式就建立了一个数据库的命名空间，一个框架。在这个空间中首先要定义的是该模式包含的数据库基本表。

SQL语言使用 CREATE TABLE 语句定义基本表，其基本格式如下:

CREATE TABLE 表名 (
    列名 数据类型 [列级完整性约束条件],
    列名 数据类型 [列级完整性约束条件],
    列名 数据类型 [列级完整性约束条件],
    ...
    列名 数据类型 [列级完整性约束条件]
);

• 新创建的表为空表
• []内的，表示可选，可以选择设置也可以选择不设置。

建表的同时通常还可以定义与该表有关的完整性约束条件，这些完整性约束条件被存入系统的数据字典中，当用户操作表中数据时由关系数据库管理系统自动检查该操作是否违背这些完整性约束条件。

如果完整性约束条件涉及该表的多个属性列，则必须定义在表级上，否则既可以定义在列级也可以定义在表级。

例题[3.5] - 例题[3.7]：

# 使用 mydb 这个数据库
USE mydb;
# 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,    # 列级完整性约束条件，Cno 为主码
    Cname CHAR(40) NOT NULL ,   # 列级完整性约束条件，Cname 不能取空值
    Cpno CHAR(4),
    Ccredit SMALLINT,
    # 表级完整性约束条件，设置Cpno为外键，参照Course表中的Cno
    FOREIGN KEY (Cpno) REFERENCES Course(Cno)
);
# 3.7
# 建立学生选课表 SC
CREATE TABLE SC(
    Sno CHAR(9),
    Cno CHAR(4),
    Grade SMALLINT,
    PRIMARY KEY (Sno, Cno), # 主码由两个属性构成，必须作为表级完整性进行定义
    # 表级完整性约束条件，设置Sno为外键，参照Student表中的Sno
    FOREIGN KEY (Sno) REFERENCES Student(Sno),
    # 表级完整性约束条件，设置Cno为外键，参照Course表中的Cno
    FOREIGN KEY (Cno) REFERENCES Course(Cno)
)

2. 数据类型

关系模型中一个很重要的概念是域。每一个属性来自一个域，它的取值必须是域中的值。

在SQL中域的概念用数据类型来实现。

定义表的各个属性时需要指明其数据类型及长度。

SQL标准支持多种数据类型，表3.4列出了几种常用数据类型。要注意，不同的关系数据库管理系统中支持的数据类型不完全相同。

一个属性选用哪种数据类型要根据实际情况来决定，一般要从两个方面来考虑：

• 一是取值范围
• 二是要做哪些运算

3. 模式与表

每一个基本表都属于某一个模式，一个模式包含多个基本表。

当定义基本表时一般可以有三种方法定义它所属的模式。

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

# 语法
# CREATE TABLE 模式名(MySQL中为数据库名).表名(
#     列名 数据类型 [列级约束条件],
#     ...
#     列名 数据类型 [列级约束条件]
# )
CREATE TABLE mytest.test1(
    one INT
);

• 方法二：在创建模式语句中同时创建表
  MySQL没有此语法
  
• 方法三：设置所属的模式，这样在创建表时表名中不必给出模式名。

# 使用 mydb 这个数据库
# 设置所属的模式
USE mydb;
# 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)
);

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

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

• MySQL中不支持查看当前搜索路径的语法

SHOW search_path

4. 修改基本表

SQL语言使用ALTER TABLE语句修改基本表，其一般格式为：

ALTER TABLE 表名
[ ADD [COLUMN] 新列名 数据类型 [完整性约束] ]
[ ADD 表级完整性约束]
[ DROP [COLUMN] 列名 [CASCADE | RESTRICT] ]
[ DROP CONSTRAINT 完整性约束名 [CASCADE | RESTRICT] ]
[ ALTER COLUMN 列名 数据类型] ]

其中

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

例题：

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

ALTER TABLE Student
ADD COLUMN S_entrance DATE;

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

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

# mysql中不支持该语句
ALTER TABLE Student
ALTER COLUMN Sage int;

# mysql中使用 modify
ALTER TABLE Student
MODIFY Sage INT;

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

ALTER TABLE Course
ADD UNIQUE(Cname);

# 增加课程名称必须取唯一值的约束条件，且约束的名称为 Cname_unique 
ALTER TABLE Course
ADD CONSTRAINT Cname_unique UNIQUE(Cname);

删除Student表中的“入学时间”列

ALTER TABLE Student
DROP COLUMN S_entrance;

删除新增加的约束条件

ALTER TABLE Course
DROP CONSTRAINT Cname_unique;

5. 删除基本表

当某个基本表不再需要时，可以使用DROP TABLE语句删除该基本表，其一般格式为：

DROP TABLE 表名 [CASCADE | RESTRICT];

• 若选择RESTRICT，则该表的删除是有限制条件的。欲删除的基本表不能被其他表的约束所引用(如 CHECK,FOREIGN KEY等约束) ，不能有视图，不能有触发器（trigger),不能有存储过程或函数等。如果存在这些依赖该表的对象，则此表不能被删除。
• 若选择CASCADE，则该表的删除没有限制条件。在删除基本表的同时，相关的依赖对象，例如视图，都将被一起删除。
• 默认情况是RESTRICT。

不指定删除时的处理方式，默认是RESTRICT，因为Student中的Sno被SC表外键引用，所不能删除。

[ 例 3.11 ] 删除 Student 表

# mysql中需要先删除 SC 表，因为 SC 有外键引用 Student 表中的 Sno ，不能删除
DROP TABLE SC;
DROP TABLE Student CASCADE;

基本表定义一旦被删除，不仅表中的数据和此表的定义将被删除，而且此表上建立的索引、触发器等对象一般也都将被删除。

有的关系数据库管理系统还会同时删除在此表上建立的视图(mysql不会删除,但是查看视图会报错)。如果欲删除的基本表被其他基本表所引用，则这些表也可能被删除。

因此执行删除基本表的操作一定要格外小心。

3.3.3 索引的建立与删除

建立索引是加快查询速度的有效手段。

索引虽然能够加速数据库查询，但需要占用一定的存储空间，当基本表更新时，索引要进行相应的维护，这些都会增加数据库的负担，因此要根据实际应用的需要有选择地创建索引。

一般说来，建立与删除索引由数据库管理员或表的属主(owner)，即建立表的人，负责完成。关系数据库管理系统在执行查询时会自动选择合适的索引作为存取路径，用户不必也不能显式地选择索引。

1. 建立索引

在SQL语言中，建立索引使用CREATE INDEX语句，其一般格式为：

CREATE [UNIQUE] [CLUSTER] INDEX 索引名
ON 表名 (
      列名 [次序(排序方式)],
      列名 [次序(排序方式)],
      列名 [次序(排序方式)],
      ...
    );

其中，

• <表名>是要建索引的基本表的名字。
• 索引可以建立在该表的一列或多列上，各列名之间用逗号分隔。
• 每个<列名>后面还可以用<次序>指定索引值的排列次序，可选 ASC（升序）或DESC（降序)，默认值为 ASC。
• UNIQUE表明此索引的每一个索引值只对应唯一的数据记录。
• CLUSTER表示要建立的索引是聚簇索引。(mysql中无此关键词)

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

# Student 表按学号升序建唯一索引，
CREATE UNIQUE INDEX Stusno
ON Student(Sno ASC);
# Course 表按课程号升序建唯一索引，
CREATE UNIQUE INDEX Coucno
ON Course(Cno ASC);
# SC表按学号升序和课程号降序建唯一索引。
CREATE UNIQUE INDEX SCno
ON SC(
     Sno ASC ,
     Cno DESC
    );

2. 修改索引

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

ALTER INDEX 旧索引名 RENAME TO 新索引名;

• mysql中不支持，mysql中没有真正意义上的修改索引只有先删除再创建新的索引才可以达到修改的目的。

# 给Student表的Sno添加唯一索引
ALTER TABLE Student
ADD UNIQUE(Sno);
# 给Student表的Sno添加主键索引
ALTER TABLE Student
ADD PRIMARY KEY (Sno ASC);
# SC表按学号升序和课程号降序建多列索引。
ALTER TABLE SC
ADD INDEX SC_no(
               Sno ASC ,
               Cno DESC
    );

3. 删除索引

mysql中查询索引

SHOW INDEX FROM 表名;

索引一经建立就由系统使用和维护，不需用户干预。建立索引是为了减少查询操作的时间，但如果数据增、删、改频繁，系统会花费许多时间来维护索引，从而降低了查询郊率。这时可以删除一些不必要的索引。

在SQL中，删除索引使用DROP INDEX语句，其一般格式为：

DROP INDEX 索引名;

mysql：

DROP INDEX 索引名 ON 表名;

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

3.3.4 数据字典

数据字典是关系数据库管理系统内部的一组系统表，它记录了数据库中所有的定义信息，包括关系模式定义、视图定义、索引定义、完整性约束定义、各类用户对数据库的操作权限、统计信息等。

关系数据库管理系统在执行SQL的数据定义语句时，实际上就是在更新数据字典表中的相应信息。

在进行查询优化和查询处理时，数据字典中的信息是其重要依据。