全面掌握MS ACCESS SQL（08）

时　间:2017-12-27 12:03:20
作　者:Big Young   ID：252  城市：襄阳
摘　要:  用SQL修改库表结构即ALTER TABLE语句的用法。
正　文:

第三节 用SQL修改库表结构

    表创建以后，发现不能满足需要，或者在使用过程中，随着社会现实情况的发展已不适应现实需要了，这时就需要修改，ACCESS SQL提供了完整的对表结构进行调整与修改的语句：Alter TABLE。需要说明的是，如果表中已有数据应在修改前进行备份因为对表结构的修改可能会带来数据方面的损失。例如，更改某个字段的数据类型可能会导致数据丢失或产生舍入误差，这取决于您正在使用的数据类型。同时还会破坏您可能已更改的字段引用的应用程序的其他部分。在修改现有表的结构之前，应始终保持特别高度的警惕与小心。下面我们来详细研究一下SQL语句Alter TABLE的具体用法。

    一、Alter TABLE语句的语法结构

    1、Alter TABLE语句的语法：

Alter TABLE table {ADD {COLUMN field type[(size)] [NOT NULL] [CONSTRAINT index] | Alter COLUMN field type[(size)] | CONSTRAINT multifieldindex} | Drop {COLUMN field | CONSTRAINT indexname} }

    2、Alter TABLE语句组成部分的说明：

    使用 Alter TABLE 语句可以通过多种方法修改现有表。我们可以：

 = 1 \* GB3 ①使用ADD COLUMN向表中添加新字段。可以指定字段名称、数据类型和可选大小（对于文本和二进制字段）。例如，以下语句将具有25个字符的名为Notes的文本字段添加到Employees表中：

SQL示例：Alter TABLE Employees ADD COLUMN Notes TEXT(25)

也可以定义该字段的索引。有关单字段索引的详细信息，请参阅 CONSTRAINT 子句。如果指定一个字段为 NOT NULL，那么新记录的该字段必需有有效数据。

 = 2 \* GB3 ②使用Alter COLUMN可更改现有字段的数据类型。可以指定文本和二进制字段的字段名称、新的数据类型以及可选大小。例如，以下语句将Employees表中名为ZipCode字段的数据类型（原先定义为整型）更改为具有10个字符的文本字段：

SQL示例：Alter TABLE Employees Alter COLUMN ZipCode TEXT(10)

 = 3 \* GB3 ③使用ADD CONSTRAINT可添加多字段索引。有关多字段索引的详细信息，请参阅CONSTRAINT子句。

 = 4 \* GB3 ④使用Drop COLUMN可删除字段。只需指定字段的名称。

 = 5 \* GB3 ⑤使用Drop CONSTRAINT可删除多字段索引。只需在CONSTRAINT保留字后面指定索引名称。

这里需要指出的如下几点：

 = 1 \* GB2 ⑴不能同时添加或删除多个字段或索引。

 = 2 \* GB2 ⑵可以使用Create INDEX语句向表中添加单字段或多字段的索引，并且可以使用Alter TABLE或者Drop语句删除使用Alter TABLE或Create INDEX创建的索引。

 = 3 \* GB2 ⑶在一个字段或一个名为CONSTRAINT的子句中可以使用NOT NULL，该子句既可应用于单字段索引，也可应用于名为CONSTRAINT的多字段索引。但是，NOT NULL限制一次只能应用于一个字段。多次应用此限制将导致运行时错误。

二、用SQL命令Alter TABLE修改表结构

    前面说过，使用Alter TABLE语句，我们可以添加、删除或更改列（或字段），也可以添加或删除约束。我们还可以声明默认值的字段；但是，我们一次只可以改变一个字段。

1、用SQL语句向表中添加字段

假设我们有一个发票数据库表，并且我们想要将一个字段添加到该表中。要向表中添加字段，我们就要使用事带有字段添加子句ADD COLUMN的Alter TABLE语句来完成此任务，同时我们还必须在语句中提供字段名、数据类型及字段的大小等相关属性。下面我们来用实例具体实现：

我们先创建一个空白的数据库，在库中创建一个空的发票数据表，空表我们可以用前面我们提到的一个SQL语句创建：

Create TABLE 发票;

运行后中库中就创建了一个名为“发票”的空表，即表中没有任何字段，这样的表在添加字段前不能实际应用，下面我们来修改其结构向其中添加一个列“票据编号”，SQL语句如下：

Alter TABLE 发票

ADD COLUMN 票据编号 TEXT(4);

    下面继续修改发票表的结构，再一次向其中添加多个字段，SQL语句如下：

Alter TABLE 发票

ADD COLUMN 客户单位 CHAR(20) NOT NULL,

           产品名称 CHAR(10),

           数量     INTEGER,

           单价     CURRENCY,

           开票人   CHAR(4);

    2、用SQL语句修改表中已有的字段

若要更改表中现有字段的数据类型或字段的大小，请使用带Alter COLUMN子句与字段名称、字段的数据类型及字段大小等相关属性的Alter TABLE语句。下面我们先来修改某一个字段的相关属性，如我们发现“发票”表中的“票据编号”是字符型，现在想把它改成自动编号类型的字段，可以用以下SQL语句：

Alter TABLE 发票

Alter COLUMN 票据编号 COUNTER;

再来用一下这个语句同时修改多个字段，实例如下：

    Alter TABLE 发票

Alter COLUMN 客户单位 TEXT(25),

                 产品名称 TEXT(15),

                 开票人   TEXT(10);

这个实例一运行，大家就会发现系统提示语法错误，不能正常运行，为什么呢？大家回想一下，我们前面是不是说过，一次只能修改一个字段。在ACCESS SQL中想修改多个字段的属性，就只能使用多个Alter TABLE语句来完成了。

    3、用SQL语句删除表中的字段

随着情况的变化，某些字段表达的信息数据不需要了，就要删除它。或者如果您想要更改字段的名称，这时就必须删除该字段，然后重新创建它。若要删除一个字段，请使用带字段名称的Drop COLUMN子句来完成此任务。比如我们要删除“发票”表中的“开票人”字段，SQL语句示例如下：

Alter TABLE 发票

Drop COLUMN 开票人;

请注意，使用此方法将被消除字段中的现有数据。若要保留现有数据，应该在图形用户介面的表设计模式中更改该字段的名称，或编写代码来将数据保存在一个临时表中，并在重命名后将其追加到表中。

4、用SQL语句修改默认值

默认值是在任何时间将新记录添加到表中时系统自动为其赋的初始值。若要设置字段的默认值，请在声明字段类型时或使用Alter COLUMN子句后带DEFAULT关键字来实现。例如：

Alter TABLE 发票

Alter COLUMN 客户单位 TEXT(40) DEFAULT 不详;

请注意，默认值未括在单引号或双引号中。如果是，引号也将插入到该记录。此外可以在Create TABLE语句中使用DEFAULT关键字。例如：

Create TABLE 客户

(

   客户编号 INTEGER CONSTRAINT PK_KH

            PRIMARY KEY, 

   姓名     TEXT(8) NOT NULL,

   单位     TEXT(50) NOT NULL,

   电话     TEXT(11),

   信箱     TEXT(50),

   地址     TEXT(60) DEFAULT 不详

);

    5、通过SQL语句修改约束

建立主键和引用完整性，并限制可在字段中插入的值，可以使用约束。一般情况下，可以使用约束来保护的完整性和数据库中数据的一致性。

有两种类型的约束：单字段或字段级别的约束条件和多字段或表级约束。创建表或Alter TABLE语句中，可以使用这两种类型的约束。

在声明字段和数据类型后，使用字段本身，声明单字段约束，也称为列级约束。在下面的示例中，将在“客户”表的“客户编号”字段上创建单字段主键。若要添加约束条件，使用CONSTRAINT关键字并带该字段的名称。

    Alter TABLE 客户

Alter COLUMN 客户编号 INTEGER

          CONSTRAINT PK_BH PRIMARY KEY;

    请注意，这里给出了约束的名称。其实在实际使用中，如果以后不再直接访问这些约束，我们完全可以忽略CONSTRAINT子句指定的名称，直接使用PRIMARY KEY，这种用快捷方式：

    Alter TABLE 客户

Alter COLUMN 客户编号 INTEGER PRIMARY KEY;

但是，使用快捷方式会导致Access随机生成约束名称，这样会使在代码中引用很困难，因而，始终命名约束是一个好办法。

若要删除一个约束，可使用带Drop CONSTRAINT子句的Alter TABLE语句，并提供约束的名称，例如：

Alter TABLE 客户

Drop CONSTRAINT PK_BH;

此外可以使用约束来限制字段的允许值。可以将值限定为不为空或唯一，也可以定义check约束，这是一种可以应用于字段的业务规则。假设我们想限制（或约束）的名字和姓氏字段是唯一的值，这意味着，在同一个表中应该永远不会有名字和姓氏完全相同的两条记录存在。因为这是一个多字段约束，应在表级别，而不是字段级别来声明它。使用ADD CONSTRAINT子句并带多字段的列表一定义。如：

Alter TABLE 客户

ADD CONSTRAINT 客户编号 UNIQUE

   (姓氏, 名字);

检查约束是一种强大的SQL功能，我们可以在一个或多个表中通过可以引用单个字段或多个字段来创建一个表达式，以此向表中添加数据有效性规则。假设我们想要确保发票记录中输入的金额始终是大于￥0.00。若要执行此操作，我们可以通过带检查关键字和验证表达式的ADD CONSTRAINT子句的Alter TABLE语句来声明使用check约束。如：

Alter TABLE 发票

ADD CONSTRAINT CK_JE

    CHECK (金额 > 0);

用来定义检查约束的表达式还可以引用同一个表中的多个字段或字段在其他表中，并且可以使用Select语句，数学运算符，如MS Access SQL现有效的任何操作，聚合函数。定义检查约束的表达式长度不能超过64个字符。