SQL——2.数据定义

数据定义

2.0概述

SQL提供了专门的语言用来定义数据库、表、索引等数据库对象，称作数据定义语言 ( Data Definition Language，DDL )。
首先来介绍一些基本概念。

2.0.1 SQL的数据定义语句

• 一般只对表的数据类型进行修改，即修改某一列的数据类型；
• 在以上四个操作对象之上还可以建立包含多个架构的数据库：CREATDATABASE。

2.0.2 SQL中的数据类型

• CHAR和VARCHAR的相比，CHAR由于长度相等更适合查询操作，但VARCHAR更省空间（一般用CHAR）；
• CHAR的每一位包含一个字节，不足以表示汉字，一个汉字占两个字节，要提前分配好长度；
• NCHAR每一位包含两个字节，可以表示一个汉字；
• 这里的INT对应C语言中的长整型，一般使用SMALLINT就足够。

2.0.3常用的完整性约束

(1) 主码约束：

PRIMARY KEY

(2) 唯一性约束（不能取相同值但允许多个空值）：

UNIQUE

(3) 非空值约束（只作为列级约束）：

NOT NULL

(4) 参照完整性/外码约束（在参照关系中）：

FOREIGN KEY (<列名>) REFERENCES <表名>(<列名>)

(5) 用户定义的完整性：

CHECK (<布尔表达式>)

2.1 模式的定义

2.1.1创建模式

定义模式相当于定义了一个命名空间，在其中可以进一步定义该模式包含的数据库对象，如基本表、视图、索引等；

CREATE SCHEMA[<模式名>]AUTHORIZATION<用户名>
[<表定义子句>|<视图定义子句>|<授权定义子句>]

• <模式名>：所要定义的模式的名字，可以用""强制将非标识符作为标识符来使用。
• <用户名>：拥有此模式的用户；
• 没有指定<模式名>，则<模式名>默认为<用户名>;
• 使用双引号可以将非标识符强制作标识符使用。
  [例]

CREATE SCHEMA TEST AUTHORIZATION ZHANG 
         CREATE TABLE TAB1(COL1 SMALLINT, 
                                                   COL2 INT,
                                                   COL3 CHAR(20),
                                                   COL4 NUMERIC(10,3),
                                                   COL5 DECIMAL(5,2)
                                                  );

为用户ZHANG创建了一个模式TEST，其中定义了一个表TAB1

2.1.2删除模式

DROP SCHEMA<模式名> CASCADE|RESTRICT

• CASCADE(级联)：删除模式的同时把该模式中所有的数据库对象全部删除；（不推荐）
• RESTRICT(限制)：如果该模式中定义了下属的数据库对象（如表、视图等），则拒绝该删除语句的执行；当该模式中没有任何下属的对象时才能执行；
• SQL Server中没有级联删除，必须先删除表再删除模式。

2.2基本表的定义

2.2.1创建基本表

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

• <表名>：所要定义的基本表的名字；
• <列名>：组成该表的各个属性（列）的名字；
• <列级完整性约束条件>：涉及当前属性列的完整性约束条件；
• <表级完整性约束条件>：涉及一个或多个属性列的完整性约束条件。

[例1] 建立一个“学生”表Student，它由学号Sno、姓名Sname、性别Ssex、年龄Sage、所在系Sdept五个属性构成。其中学号不能为空，值是唯一的，并且姓名取值也唯一。

	    CREATE TABLE  Student
        ( Sno       CHAR(5)  NOT NULL  UNIQUE ,
          Sname  CHAR(20)  UNIQUE ,
          Ssex      CHAR(2) ,
          Sage      INT ,
          Sdept    CHAR(15),
	　     PRIMARY  KEY  Sno ) ; 

①当主码为单一属性时，PRIMARY KEY 约束与 NOT NULL + UNIQUE 约束表达能力等价，可任用其一；
②当主码为单一属性时，也可以将PRIMARY KEY约束直接置于主码后作为列级完整性约束。

[例2] 建立一个“学生选课”表SC，它由学号Sno、课程号Cno，修课成绩Grade组成，其中(Sno, Cno)为主码。

CREATE  TABLE   SC (
        Sno   CHAR(5) , 
        Cno   CHAR(3) , 
        Grade   INT ,
	　 PRIMARY KEY  (Sno, Cno) ,
	　 FOREIGN  KEY  (Sno)  REFERENCES  Student(Sno) ,
	　 FOREIGN  KEY  (Cno)  REFERENCES  Course(Cno) ) ;

①当主码为多个属性构成的属性组时，主码只能用PRIMARY KEY (<主码属性组>)作为表级完整性约束，不必在主属性后加上 NOT NULL约束，更不能加UNIQUE约束，也不能加 PRIMARY KEY 作为列级完整性约束；
②当定义的关系中有外码时，可以用FOREIGN KEY (<属性组>) REFERENCES <被参照表名>(<属性组>) 作为表级完整性约束指定。

2.2.2删除基本表

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

缺省时为RESTRICT
• RESTRICT(限制)：删除表有限制条件
欲删除的表不能被其他表的约束（如外码）所引用
如果存在依赖该表的对象（视图、索引等），则此表不能被删除
• CASCADE(级联)：删除表没有限制条件
在删除基本表的同时，相关的依赖对象一起删除

2.2.3修改基本表

ALTER TABLE <表名>
	　　[ ADD <新列名> <数据类型> [ 完整性约束 ] ]
	    [ ADD <完整性约束>]
	　　[ DROP <列名>|<完整性约束>]
	　　[ ALTER COLUMN <列名>  <数据类型> ] ;

• <表名>：要修改的基本表；
• ADD子句：增加新列或新的完整性约束条件；
• DROP子句：删除指定的列或完整性约束条件；
• ALTER子句：用于修改列的数据类型。

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

ALTER  TABLE  Student  ADD  entrance  DATE ;

注：如基本表中原来有记录，它们在新增加的列上的属性值取空值。
删除entrance列：

ALTER  TABLE  Student  DROP  entrance ;

[例2] 将年龄的数据类型改为半字长整数。

 ALTER  TABLE  Student  ALTER COLUMN Sage  SMALLINT ;`

注：修改原有的列定义有可能会破坏已有数据，如缩短字符属性长度，将长整数改为短整数等。

[例3]删除/增加学生姓名必须取唯一值的约束。

ALTER  TABLE   Student  DROP  UNIQUE(Sname) ;
ALTER  TABLE   Student  ADD  UNIQUE(Sname) ;

2.3索引的定义

索引可以理解为数据库的“目录”，通常对索引表的查找项(Search-key)进行顺序或Hash排列，就可以很快地定位该项的某一指定值，从而通过指针项找到基本表中取该值的记录。

2.3.1索引的分类

1. 普通索引(Normal Index)：
   索引表的Search-key项中的每一索引值对应全部取该值的基本表中的记录。普通索引通过索引表的指针项指向一个单链表来实现，该链表的每个结点的数据项指向一条物理记录。

2. 单一索引(Unique Index)：
   每一个索引值只对应唯一的数据记录。当建立单一索引后，索引项不可以再插入已有值，但可以插入多个空值，这等同于在建表时对索引列增加一个UNIQUE约束；同样，如果待索引项存在相同值则不能建立单一索引。

3. 聚簇索引(Cluster Index)：
   索引项顺序与表中数据记录的物理顺序一致。即基本表是按照索引表的Search-key项的排列次序组织存储的，因此，一个基本表只能建立一个聚簇索引。

   注：聚簇索引适应于很少对基本表进行增删操作或对 Search-key项进行修改操作的情况。

2.3.2创建索引

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

• <表名>指定要建索引的基本表名字；
• 索引可以建立在该表的一列或多列上，各列名之间用逗号分隔；
• <次序>指定索引值的排列次序，升序：ASC，降序：DESC。缺省值：ASC；
• UNIQUE表示要建立的索引是单一索引；
• CLUSTER表示要建立的索引是聚簇索引。

[例1] 为学生-课程数据库中的Student，Course，SC三个表建立索引。其中Student表按学号升序建单一索引，按年龄降序建立普通索引，Course表按课程号升序建单一索引，SC表按学号升序和课程号降序建单一索引。

CREATE  UNIQUE  INDEX  Stusno  ON  Student(Sno ASC);
CREATE  INDEX  Stusage ON Student(Sage DESC);
CREATE  UNIQUE  INDEX  Coucno  ON  Course(Cno);
CREATE  UNIQUE  INDEX  SCno  ON  SC(Sno, Cno DESC);

[例2] 在Student表的Sname列上建立一个聚簇索引，而且Student表中的记录将按照Sname值的升序存放。

CREATE  CLUSTER  INDEX  Stusname  ON Student(Sname);

2.3.3删除索引

DROP INDEX  <索引名> ;

• 删除索引时，系统会从数据字典中删去有关该索引的描述及索引表。
• SQL标准中没有定义对索引的修改功能，而采用删除后重新定义索引的方式实现。

[例] 删除Student表的Stusname索引。

DROP  INDEX  Stusname ;

关于数据定义的一些解释：
1. 数据库相当于一个容器，它包含了基本表、索引、视图、存储过程、触发器、约束、角色等数据库对象，这些对象都是通过SQL中的数据定义语言来建立的；
2. 数据库具有很高的安全性，不是什么人都可以创建基本表、索引等数据库对象。只有DBA创建某一数据库用户，并将创建基本表等数据库对象的权力赋予该用户时，他才可以创建权力范围内的数据库对象；
3. 创建基本表的只能是DBA或者得到DBA授权创建表的用户；
4. 创建基本表时应指定表的主码、外码(如果有的话)及其他约束，当更新表时才能由DBMS系统自动核查数据的正确性。
5. 创建索引只能由DBA或表的属主(owner，即表的建立者) 建立；
6. 有些DBMS自动建立PRIMARY KEY和UNIQUE列上的索引；
7. 索引的维护由DBMS自动完成，不需要任何人的干预，当索引列上的数据变动时，系统自动修改索引表；
8. 索引的使用由 DBMS自动完成，系统会根据查询条件选择是否使用索引以及使用哪些索引。