数据库—DDL(模式，表和索引）

本文链接：https://blog.youkuaiyun.com/yulingzheng/article/details/104828610

数据库—SQL之DDL语言

先来看SQL的一些符号约定

表示X是需要进一步定义或说明语言成分
[X]表示X可以缺省或出现一次
{X}表示X可以出现一次
X |Y表示或者X出现，或者Y出现，但二者不能同时出现

模式的定义

key1   ： CREATE SCHEMA <模式名> [<模式元素>…]

创建一个以<模式名>命名的模式，并可以在创建模式的同时为该模式创建或不创建模式元素
<模式元素>可以是表定义、视图定义、断言定义、授权定义等
这种格式没有授权其他用户访问创建的模式，以后可以用授权语句授权

 key2   ：CREATE SCHEMA [<模式名>] AUTHORIZATION <用户名> [<模式元素>…]

与第一种的区别在于它将创建的模式授权予<用户名>指定的用户
当<模式名>缺省时，用<用户名>作为模式名

eg1：创建关系模式

CREATE SCHEMA Supply_schema AUTHORIZATION WangQiang;
//创建一个名为Supply_schema的模式，并为WangQiang所拥有
 
 CREATE SCHEMA Supply_schema;  
//创建一个名为Supply_schema的模式，但未向任何用户授权
 
CREATE SCHEMA AUTHORIZATION WangQiang;  
//为WangQiang创建一个模式，并用WangQiang命名

eg2：创建关系模式的同时创建该模式中的对象

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
    //Suppliers的属性列分别为Sno，Sname，Status，Address和Phone

模式的删除

 DROP SCHEMA <模式名> CASCADE∣RESTRICT

其中CASCADE和RESTRICT两者必须选择其一

CASCADE，则删除<模式名>指定模式得同时并删除该模式中的所有数据库对象（基本表、视图、断言等）
RESTRICT，则仅当<模式名>指定的模式不包含任何数据库对象时才删除指定的模式，否则拒绝删除

eg :模式的删除

 DROP SCHEMA Supply_schema RESTRICT
 //仅当模式Supply_schema中不包含任何数据库对象时
 //才删除模式Supply_schema，否则什么也不做
 DROP SCHEMA Supply_schema CASCADE
 //将直接删除模式Supply_schema，并同时删除该模式中所有的数据库对象

表的定义

 CREATE TABLE <表名> 
(<列名><数据类型> [DEFAULT <缺省值>] [列级约束定义],
<列名><数据类型> [DEFAULT <缺省值>] [列级约束定义],
 …, 
[<表级约束定义>, …, <表级约束定义>])；

可选短语“DEFAULT <缺省值>”定义列上的缺省值，<缺省值>是<类型>中的一个特定值或空值（NULL）
列级约束定义和该列相关的完整性约束
- NOT NULL ：不允许该列取空值
- PRIMARY KEY ：指明该列是主码
- UNIQUE ：该列上的值必须唯一
- CHECK<条件>：指明该列的值必须满足的条件，其中条件是一个涉及该列的布尔表达式
表级约束定义和一个列或者多个列同时相关的完整性约束
- PRIMARY KEY (A1, …, Ak)：说明属性列A1, …, Ak构成该关系的主码
- UNIQUE (A1, …,Ak)：说明属性列A1, …, Ak上的值必须惟一，这相当于说明A1, …, Ak构成该关系的候选码
- CHECK (<条件>)：说明该表上的一个完整性约束条件
- FOREIGN KEY (A1, …, Ak) REFERENCES <外表名>(<外表主码>) [<参照触发动作>]：定义外码

eg1：

CREATE TABLE Teachers//创建表Teachers
(Tno CHAR (7) PRIMARY KEY,//定义Tno为主码
Tname CHAR (10) NOT NULL,//Tname不能取空值     
Sex  CHAR (2) CHECK (Sex= ‘男’ OR Sex= ‘女’),//性别必须为男或者女     
Birthday DATE,        
Title CHAR (6),
Dno CHAR (4),
FOREIGN KEY (Dno) REFERENCES Departments (Dno));
//约束关系：表Teachers的Dno必须在表Departments中的Dno里找到

eg2：

CREATE TABLE SC//创建表SC
(Sno  CHAR (9),
Cno  CHAR (5),
Grade  SMALLINT CHECK (Grade>=0 AND  Grade<=100),//成绩必须在0到100之间
PRIMARY KEY (Sno,Cno),//定义Sno和Cno的联合为主码
FOREIGN KEY (Sno) REFERENCES Students (Sno),
//约束关系：表SC中的Sno必须在表Students中的Sno里找到
FOREIGN KEY (Cno) REFERENCES Courses (Cno));
//约束关系：表SC中的Cno必须在表Courses中的Cno里找到

修改基本表

新增列：
ALTER TABLE <表名> [ADD [COLUMN] <列名><数据类型>[列级约束定义]]
为已有列设置缺省值：
ALTER TABLE <表名> [ALTER [COLUMN] <列名> {SET DEFAULT <缺省值> | DROP DEFAULT}]
删除列：
ALTER TABLE <表名> [DROP [ COLUMN ] <列名> {CASCADE | RESTRICT}]
添加表约束定义：
[ADD <表约束定义>]
删除表约束定义：
ALTER TABLE <表名> [DROP CONSTRAINT <约束名>{CASCADE | RESTRICT}]

eg:

 ALTER TABLE Courses ADD Pno CHAR (5); 
 //在Courses中增加一个新列Pno，表示课程的先行课的课程号

 ALTER TABLE Students ALTER Sex SET DEFAULT ‘女’;
 //在Students的Sex列设置缺省值“女”可以减少大约一半学生性别的输入

 ALTER TABLE Students ALTER Sex DROP DEFAULT;
 //删除Sex上的缺省值可以用

 ALTER TABLE Courses DROP Pno;
 /// 删除Courses中的Pno列可以用

删除基本表

 DROP TABLE <表名> {CASCADE∣RESTRICT}

CASCADE表示及联删除，依赖于表的数据对象（最常见的是视图）也将一同被删除
RESTRICT表示受限删除，如果基于该表定义有视图，或者有其他表引用该表（如CHECK、FOREIGN KEY等约束），或者该表有触发器、存储过程或函数等，则不能删除

 DROP TABLE SC RESTRICT;
 //仅当没有依赖于SC的任何数据库对象删除才能成功
 DROP TABLE SC CASCADE;
 //删除SC表，以及依赖于此表的数据对象

索引的创建

索引—旦建立，就由系统来选择和维护，无需用户干预，但当删除一些不必要的索引时，可用下列语句来实现

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

<索引名>为建立的索引命名
<表名>是要建立索引的基本表的名字
索引可以建在该表的一列或多列上，各列名间用逗号分隔；每个<列名>后可以用<次序>指定索引值的排列次序，次序可以是ASC（升序）和DESC（降序），缺省值为ASC
UNIQUE表示该索引为惟一性索引UNIQUE缺省时，创建的索引为非唯一性索引
CLUSTER表示建立的索引是聚簇索引，缺省时为非聚簇索引
创建索引不仅创建索引结构，而且将索引的定义存储在数据字典中

删除索引

 DROP INDEX <索引名>

删除索引时，系统将删除索引结构，并同时从数据字典中删去有关该索引的定义

eg：删除索引

DROP INDEX Student_Dept;//删除索引Student_Dept