SQL：数据库、模式、表、索引的操作

1. 数据库、模式、表、视图的关系

支持SQL的关系数据库管理系统同样支持关系数据库三级模式结构。其中外模式包括若干视图和部分基本表，数据库模式包括若干基本表，内模式包括若干存储文件

用户可以用SQL对基本表和视图进行查询或其他操作，基本表和视图一样，都是关系。

基本表是本身独立存在的表，在关系数据库管理系统中一个关系就对应一个基本表。一个或多个基本表对应一个存储文件，一个表可以带若干索引，索引也存放在存储文件中。

存储文件的逻辑结构组成了关系数据库的内模式。存储文件的物理结构对最终用户是隐蔽的。

视图是从一个或几个基本表导出的表。它本身不独立存储在数据库中，即数据库中只存放视图的定义而不存放视图对应的数据。这些数据仍存放在导出视图的基本表中，因此视图是一个虚表。视图在概念上与基本表等同，用户可以在视图上再定义视图。

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

2. 数据库的创建和删除

Data Define Language数据定义语言，用于对数据库和表的管理和操作

2.1 创建数据库

CREATE DATABASE stuDB;
CREATE DATABASE IF NOT EXISTS stuDB;

2.2 删除数据库

DROP DATABASE stuDB;
DROP DATABASE IF EXISTS stuDB;

3. 模式的定义和删除

3.1 定以模式

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

CREATE SCHEMA <模式名> AUTHORIZATION <用户名>;

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

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

#案例：为用户WANG定义一个学生-课程模式S-T
CREATE SCHEMA "S-T" AUTHORIZATION WANG;

#案例：为用户ZHANG创建一个模式TEST，并在其中定义一个表TAB1
CREATE SCHEMA TEST AUTHORIZATION ZHANG
CREATE TABLE TAB1(COL1 SMALLINT,
                  COL2 INT,
                  COL3 NUMAERIC(10,3),
                  COL4 CHAR(20),
                  COL5 DECIMAL(5,2)
                  );

3.2 删除模式

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

DROP SCHEMA <模式名><CASCADE|ERSTRICT>;

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

只有当该模式中没有任何下属的对象时才能执行DROP SCHEMA语句。

DROP SCHEMA ZHANG CASCADE;

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

4.1 常用数据类型

常见类型：

1. int：整形
2. double/float：浮点型，例如double(5,2)表示最多5位，其中必须2位小数，即最大值为999.99
3. decimal：浮点型，在表示钱方面使用该类型，因为不会出现精度缺失问题
4. char：固定长度字符串类型
5. varchar：可变长度字符串类型

6. text：字符串类型，表示存储较长文本
7. date：日期类型，格式为：yyyy-MM-dd
8. time：时间类型，格式为：hh:mm:ss
9. timestamp/datetime：时间戳类型；日期+时间yyyyMMdd hhmmss

10. blob：字节类型，可保存图片

4.2 定义基本表

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

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

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

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

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

#案例：创建一个“学生”表Student
CREATE TABLE Student
	(Sno CHAR(9) PRIMARY KEY,   /*列级完整性约束条件，Sno是主码*/
     Sname CHAR(20) UNIQUE,     /*Sname取唯一值*/
     Ssex CHAR(2),
     Sage SMALLINT,
     Sdept CHAR(20),
    );
    
#案例：建立一个“课程”表Course
CREATE TABLE Course
	(Cno CHAR(4) PRIMARY KEY,     /*列级完整性约束条件，Cno是主码*/
     Cname CHAR(40) NOT NULL,      /*列级完整性约束条件，Cname不能取空值*/
     Cpno CHAR(4),                 /*Cpno的含义是先修课*/
     Ccredit SMALLINT,
     FOREIGN KEY (Cpno) REFERENCES Course(Cno)
     /*表级完整性约束条件，Cpno是外码，被参照表是Course，被参照列是Cno*/
    );

4.3 删除基本表

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

DROP TABLE <表名> [RESTRICT|CASCADE];

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

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

默认情况是RESTRICT。

#案例：删除Student表
DROP TABLE Student CASCADE;

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

有的关系数据库管理系统还会同时删除在此表上建立的视图。如果欲删除的基本表被其他基本表所引用,则这些表也可能被删除

4.4 修改基本表

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子句用于修改原有的列定义，包括修改列名和数据类型

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

#案例：将年龄的数据类型由字符型改为整数
ALTER TABLE Student ALTER COLUMN Sage INT;

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

5. 索引的定义、删除与修改

当表的数据量比较大时，查询操作会比较耗时。建立索引是加快查询速度的有效手段。数据库索引类似于图书后面的索引，能快速定位到需要查询的内容

用户可以根据应用环境的需求在基本表上建立一个或多个索引，以提供多种存取路径，加快查找速度

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

5.1 建立索引

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

CREATE [UNIQUE] [CLUSTER] INDEX <索引名>
ON <表名>(<列名> [<次序>][,<列名>[<次序>]]……)

其中，<表名>是要建索引的基本表的名字。索引可以建立在该表的一列或多列上，各列名之间用逗号分隔。

每个<列名>后面还可以用<次序>指定索引值的排列次序，可选ASC(升序)或DESC（降序)，默认值为ASC。

/*为学生-课程数据库中的 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);

5.2 修改索引

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

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

#案例：将SC表的SCno索引名改为SCSno
ALTER INDEX SCno RENAME TO SCSno;

5.3 删除索引

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

DROP INDEX <索引名>;

#案例：删除Student表的Stusname索引
DROP INDEX Stusname;

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