SQL-DLL-数据定义语言

 

3.2.2.1 创建数据表

（1）       创建一个数据表的命令组成部分：创建一个数据表的命令组成部分有字段名，字段数据类型，字段长度，精度和小数位数以及字段的NULL值与DEFAULT值。

（2）       创建数据表的SQL语法格式

在SQL语言中，使用语句Create table创建数据表，其基本语法格式为：

Create table<表名>

(<列名> 【列的数据类型】<约束类型> )

例：用SQL命令建立一学生表S

Create table S

（

SNO CHAR(8),

SN VARCHAR(20),

AGE INT,

SEX CHAR(2) DEFAULT’男’,

DEPT VARCHAR(20)

）

（3）       定义完整型约束 语法格式为 CONSTRAINT <约束名> 【约束类型】

l  NULL/NOT NULL

NULL值不是0也不是空白，更不是填入字符串“NULL”，而是表示“不知道”、“不确定”或“没有数据”的意识。当某一字段的值一定要输入值才有意义的时候，则可以设置为NOT NULL 语法格式如下：[CONSTRAINT <约束名> [NULL|NOT NULL]]

例：建立一个S表，对SNO字段进行NOT NULL约束

Create table S

（

SNO CHAR(8) CONSTRAINT S_CONS NOT NULL,

SN VARCHAR(20),

AGE INT,

EX CHAR(2) DEFAULT’男’,

DEPT VARCHAR(20)

）

当SNO为空时，系统给出错误信息，无NOT NULL约束时，系统缺省为NULL.其中S_CONS为指定的约束名称，当约束名称省略时，系统自动产生一个名字，如下列语句的功能与前一个例子相同。

Create table S

（

SNO CHAR(8) NOT NULL,

SN VARCHAR(20),

AGE INT,

EX CHAR(2) DEFAULT’男’,

DEPT VARCHAR(20)

）

l  UNIQUE约束

UNIQUE约束用于指明基本表在某一列或多个列的组合上取值必须唯一。

UNIQUE既可用于列约束，也可以用于表约束。UNIQUE用于定义列约束时，其语法格式如下：[CONSTRAINT <约束名> UNIQUE]

例 建立一个S表，定义SN为唯一键。

Create table S

（

SNO CHAR(8) NOT NULL,

SN VARCHAR(20) CONSTRAINT SN_UNIQ UNIQUE,

AGE INT,

EX CHAR(2) DEFAULT’男’,

DEPT VARCHAR(20)

）

其中SN_UNIQ为指定的约束名称，约束名称可以省略，如下例：

Create table S

（

SNO CHAR(8) NOT NULL,

SN VARCHAR(20) UNIQUE,

AGE INT,

EX CHAR(2) DEFAULT’男’,

DEPT VARCHAR(20)

）

UNIQUE用于定义表约束时，其语法格式如下：[CONSTRAINT <约束名> UNIQUE([列名],[列名],~~)]

例：建立一个S表，定义SN+SEX为唯一键，此约束为表约束

Create table S

（

SNO CHAR(8) NOT NULL,

SN VARCHAR(20),

AGE INT,

EX CHAR(2) DEFAULT’男’,

DEPT VARCHAR(20),

CONSTRAINT S_UNIQ UNIQUE(SN,SEX)

）

l  PRIMARY KEY约束

PRIMARY KEY约束用于定义基本表的主键，起唯一标示作用，其值不能为NULL，也不能重复，以此来保证实体的完整性。

PRIMARY KEY与UNIQUE约束类似，通过建立唯一索引来保证基本表在主键列取值的唯一性，但他们之间存在很大的区别

（1）       在一个基本表中只能定义一个PRIMARY KEY约束，但可以定义多个UNIQUE约束。

（2）       对于指定为PRIMARY KEY的一个列或多个列的组合，其中任何一个列都不能出现空值，而对于UNIQUE所约束的唯一键，则允许出现空值。（注意：不能为同一个列和一组列既定义UNIQUE约束，又定义PRIMARY KEY约束）

PRIMARY KEY既可以用于列约束，也可以用于表约束。PRIMARY KEY用于定义列约束时语法为：CONSTRAINT <约束名> PRIMARY KEY

例：建立一个S表，定义SNO为S的主键，建立另外一数据表C，定义CNO为C的主键。

Create table S

(

   SNO CHAR(5) NOT NULL CONSTRAINT S_PRIM PRIMARY KEY,

   SN CHAR(8),

   AGE NUMBERIC(2)

)

定义数据表C：

Create table C

(

   CNO CHAR(5) NOT NULL CONSTRAINT C_PRIM PRIMARY KEY,

   CN CHAR(50)

)

PRIMARY KEY 用于定义表约束时，即将某些列的组合定义为主键时，其语法格式为：CONSTRAINT <约束名> PRIMARY KEY([列明],[列明],~~~~~)

例：建立已一个SC表，定义SNO+CNO为SC的主键。

Create table SC

(

   SNO CHAR(5) NOT NULL,

   CNO CHAR(5) NOT NULL,

   SCORE NUMERIC(3),

   CONSTRAINT SC_PRIM PRIMARY KEY(SNO,CNO)

)

l  FOREIGN KEY约束

FOREIGN KEY约束指定某一个列或一组列作为外部键，其中，包含外部键的表称为从表，包含外部键所引用的主键或唯一键的表称主表。系统保证从表在外部键上的取值是主表中某一个主键值或唯一键值，或者去空值，以此保证两个表之间的连接，确保了尸体的参照完整性。

FOREIGN KEY既可用于列约束，也可以用于表结构，其语法格式为：

CONSTRAINT <约束名>  FOREIGN KEY REFERENCES<主表名>（列名）

例：建立一个SC表，定义SNO，CNO为SC的外部键。

Creat table SC

(

   SNO CHAR(5) NOT NULL CONSTRAINT S_FORE FOREIGN KEY REFERENCES S(SNO),

   CNO CHAR(5) NOT NULL CONSTRAINT C_FORE FOREIGN KEY REFERENCES C(CNO),

   SCORE NUMERIC(3),

   CONSTRAINT S_C_PRIM PRIMARY KEY(SNO,CNO)

)

l  CHECK约束

CHECK约束用来检查字段值所允许的范围，如一个字段只能输入整数，而且限定在0~100的整数，以此来保证域的完整性。

CHECK 既可用于列约束，也可以用于表约束，其语法格式为：

CONSTRAINT <约束名> CHECK(约束条件)

例：建立一个SC表，定义SCORE的取值范围为0~100之间

Create table SC

(

  SNO CHAR(5),

  CNO CHAR(5),

  SCORE NUMERIC(5,1) CONSTRAINT SCORE_CHK CHECK(SCORE>0 AND SCORE<100)

)

建立包含定义完整性定义的学生表。

Create table S

(

  SNO CHAR(6) CONSTRAINT S_PRIM PRIMARY KEY,

  SN CHAR(8) CONSTRAINT SN_CONS NOT NULL,

  AGE NUMERIC(2) CONSTRAINT AGE_CONE NOT NULL,

                 CONSTRAINT AGE_CHK CHECK(AGE BETWEEN 15 AND 50),

  SEX CHAR(2) DEFAULT ‘男’,

  DEPT CHAR(50) CONSTRAINT DEPT_CONE NOT NULL

)

3.2.2.2 修改基本表

       SQL语言使用Alter table命令来完成这一功能，有如下三种修改方式。

（1）      ADD方式

ADD方式用于增加新列和完整性约束，定义方式同Create table语句中的定义方式相同，其语法格式为：Alter table <表名> ADD <列名>|<完整性约束>

例：在S表中增加一个班号和住址列

Altera table S

Add

Class_NO  char(6),

Address Char(6)

注意：使用此方法增加的新列自动填充NULL值，所以不能为增加的新列指定NOT NULL约束

例：在SC表中增加完整性约束定义，是SCORE在0~100之间。

Alter table SC

Add

CONSTRAINT SCORE_CHK CHECK(SCORE BETWEEN 0 AND 100)

（2）      ALTER方法

ALTER方式用于修改某些列，其语法格式为：

ALTER TABLE <表名>

ALTER COLUMN <列名><数据类型>[NULL|NOT NULL]

例：把S表中的SN列加宽10个字符。

Alter table S

Alter Column

SN Char(20)

注意：使用此方法有如下一些限制：

l  不能更改列名；

l  不能将含有空值的列的定义修改为NOT NULL约束；

l  若列中已有数据，则不能减少改列的宽度，也不能改变其数据类型；

l  只能修改NULL|NOT NULL约束，其他类型的约束在修改之前必须先将约束删除，然后在重新添加修改过的约束定义

（3）      DROP方法

DROP方式只用于删除完整性约束定义，其语法格式为：

Atler table<列名>

Drop Constraint<约束名>

列：删除S表中的主键

Alter table s

Drop Constraint S_PRIM

3.2.2.3 删除基本表

       当某个基本表无用时，可将其删除。删除后，该表中

       DROP TABLE<表名>

       删除表STUDENT

       DROP TABLE STUDENT

3.2.3 设计、创建和维护索引

1.         索引的作用

（1）       加快查询速度

（2）       保证行的唯一性

2.         索引的分类

（1）      聚集索引与非聚集索引

按照索引记录的存放位置，索引可分为聚集索引与非聚集索引。

l  聚集索引按照索引的字段排列记录，并且按照排好的顺序将记录存储在表中

l  非聚集索引按照说有的字段排列记录，但是排列的结构并不会存储在表中，而是另外存储

上述定义可以看出，在检索记录时，聚集索引会比非聚集索引速度快，因为数据在比表中已经依索引顺序排好了，但是要新增或更新记录时，由于聚集索引需要将排序后的记录存储在表中，所以其速度会比非聚集索引引稍慢。另外，一个表中只能有一个聚集索引，而非聚集索引可以有多个。

（2）      唯一索引

唯一索引表示表中每一个索引值只对应唯一的数据记录，这与表的PRIMARY KEY的特征类似，因此唯一索引常用于PRIMARY KEY的字段上，以区别每一个记录。当表中有被设置为UNIQUE的字段时，SQL SERVER会自动建立一个非聚集索引的唯一索引，而当表中有PRIMARY KEY的字段时，SQL SERVER会在PRIMARY KEY字段建立一个聚集索引。

（3）      复合索引

复合索引是将两个字段或多个字段组合起来建立的索引，单独的字段允许有重要的值。

3.         建立索引

建立索引的语句是 CREATE INDEX,其语法格式为

CREATE [UNIQUE]|[CLUSTERED] INDEX <索引名> ON <表名>（<列名>[次序]，<列名>[次序]····）

其中 UNIQUE表示建立唯一索引

     CLUSTERED表示建立聚集索引

“次序”用来指定索引值的排序顺序，可为ASC（升序）或DESC（降序），缺省值为ASC。

例：为表SC在SNO和CNO上建立唯一索引

CREATE UNIQUE INDEX SCI ON SC(SNO,CNO)

执行此命令后，为SC表建立一个索引名为SCI的唯一索引，此索引为SNO和CNO两列的复合索引，即对SC表中的行先按SNO的递增顺序索引，对于相同的SNO，又按CNO的递增顺序索引。由于有UNIQUE的限制，所以该索引在（SNO，CNO）组合列的排序上具有唯一性，不存在重复值。

例：为教师表T在TN上建立聚集索引。

CREATE CLUTERED INDEX TI ON T(TN)

执行此命令后，为T表建立一个索引名为TI的聚集索引，T表中的记录将按照TN值的升序存放。

注意：

（1）       改变表中的数据（如增加或删除记录）时，索引将自动更新。

（2）       索引建立后，在查询使用该列时，系统将自动使用索引进行查询。

（3）       索引数目无限制，但索引越多，更新数据的速度越慢对于仅用于查询的表可多建索引，对于数据更新频繁的表则应少建索引

4.         删除索引

建立索引是为了提高查询速度，但是随着索引的增多，数据更新时，系统会花费许多时间来维护索引，这时，应删除不必要的索引。

删除素的语句是DROP INDEX,其语法格式为：

DROP INDEX 表名.索引名

例：删除表SC的索引SCI

DROP INDEX SC.SCI