SQL--数据表的种种操作(下)

数据表的种种操作（下）

修改数据表

当创建完数据表之后，在使用的过程中可以根据需要对数据表进行相应的修改，修改数据表主要包括修改表名称、修改字段的数据类型以及更新表属性，当不再需要数据表时，可以将数据表删除或删除数据表中的某一列等。

重命名/删除表

--利用存储过程来修改表名
EXEC sp_rename 'old_table_name', 'new_table_name'
--删除表和表中的数据
DROP TABLE table_name [CASCADE | RESTRICT]
--只删除表中的数据，而保留表本身
DELETE table_name

将“学生信息”表的名称重命名为Student表

要对表进行重命名或者删除操作，用户必须拥有相应的权限或者服务器角色。

CASCADE选项表示对列进行级联删除，也就是说，不仅删除该列并且同时删除所有引用这一列的视图、约束、例程或者触发器。而如果指定另一选项RESTRICT，那么只有在没有视图、约束、例程或者触发器引用这一列时，这一列才能被删除。

修改列属性

使用ALTER TABLE语句不仅可以添加新列和删除列，还可以对列的属性进行修改。比如，重新设置列名，数据类型，长度，是否允许空等，还可以设置和取消一个列的主键约束。

当设置列名，数据类型，长度以及是否允许空时，ALTER TABLE的语法格式如下：

ALTER TABLE     table_name
ALTER COLUMN     column_name     new_type_name    new_constriant

添加/删除表列

在创建完成数据表之后，在使用过程中可以根据需要添加新列，当然也可以删除无用的列。但是，如果列具有以下特征，则该列不能删除。

• 用于CHECK,FOREIGN KEY, UNIQUE或PRIMARY KEY约束
• 用于索引
• 与DEFAULT定义关联或绑定到某一默认对象
• 绑定到规则
• 用作表的全文键
• 已注册支持全文

--添加列
ALTER TABLE    table_name
ADD    column_name    data_type    constraint

--删除列
ALTER TABLE    table_name
DROP    COLUMN    column_name[,...n]   

 

约束

约束是SQL Server提供的自动保持数据库完整性的一种方法，通过对数据库中数据设置某种约束条件来保证数据的完整性。约束可以分为表约束和列约束。通过约束可以保证数据的完整性和一致性。

数据库完整性

数据库的完整性是指数据的正确性和相容性。数据的正确性是指数据的值准确无误，即数据类型必须正确，并且数据的值在规定范围之内。数据的相容性是指数据的存在必须确保同一个表格数据之间及不同表格数据之间的相容关系。

在关系数据库中有3类完整性约束：实体完整性，参照完整性和用户自定义完整性。其中实体完整性和参照完整性是数据库必须满足的完整性约束条件，而用户自定义完整性可以根据实际情况而定。

• 实体完整性

实体完整性体现的是数据库中所有的实体的唯一性。实体完整性可以通过主键、索引或唯一值等方法实现，通常使用主键来实现实体完整性。

• 参照完整性

参照完整性体现了实体及实体间存在的某种联系，也是用户维护相关数据表之间数据一致性的方法。通过参照完整性，可以防止因修改或删除某一个数据表中的数据而导致其他数据表中数据的不可用等错误。可用来实现参照完整性的方法包括外键、检查、触发器和存储过程等。

• 用户自定义完整性

用户自定义完整性是用户在实际应用中根据具体情况而自行定义的约束条件，它体现了某一具体应用所涉及的数据必须满足的语义要求。可用来实现用户自定义完整性的方法包括：规则、触发器、存储过程和数据表创建时可以使用的所有约束。

主键约束

通过使用PRIMARY KEY来设置主键约束。PRIMARY KEY关键字用于指定一列或多列中的数据值具有唯一性，即不存在相同的数据值，并且指定了PRIMARY KEY关键字的数据列不允许为空。

每个表中只能有一个列被定义为PRIMARY KEY约束，所以该列不允许为空，且image和text类型的列不能被定义为PRIMARY KEY约束。

• 在定义表时直接创建约束

列约束的时候

column_name    data_type    PRIMARY KEY

表约束的时候

CONSTRAINT constraint_name
PRIMARY KEY    [CLUSTERED | NONCLUSTERED]
(column_name [,...n])

• 在未设置PRIMARY KEY约束的表中添加

ALTER TABLE table_name
ADD
[CONSTRAINT constraint_name]
PRIMARY KEY [CLUSTERED | NONCLUSTERED]
(column_name[,...n])

如果向一个没有指定主键约束的表中添加主键约束，那么指定为主键约束的列必须已经指定了NOT NULL约束，否则无法将该列指定为主键约束。

• 删除PRIMARY KEY约束

ALTER TABLE table_name
DROP
CONSTRAINT constraint_name

外键约束

参照完整性主要通过外键约束来定义，FOREIGN KEY约束保证了数据库各个表数据的一致性和正确性。

当属性F是基本关系表R的外键时，那么对于R中的每个元组在F上的值必须满足如下条件。

• 取空值
• 等于S中某个元组的属性值

 

• 在定义表时直接创建约束

CONSTRAINT constraint_name
FOREIGN KEY    (column_name [,...n])
REFERENCES     ref_table_name [(ref_column_name[,..n])]
[ON DELETE {CASCADE | NO ACTION}]
[ON UPDATE {CASCADE | NO ACTION}]
[NOT FOR REPLICATION]

ref_table_name用于指定被参照的数据表名称,ref_column用于指定被参照的列名称。

ON DELETE {CASCADE | NO ACTION}用于指定当删除表的数据列时是否允许对相关表进行级联删除。

ON UPDATE {CASCADE | NO ACTION}用于指定当更新表的数据列时是否允许对相关表进行级联更新。

外键不一定要与相应的主键名称相同，但是，在实际应用中，为了便于识别，当外键与相应的主键在不同的数据表中时，通常用相同的名称。另外，外键不一定要与相应的主键在不同的数据表中，也可以是同一个数据表。

• 在未设置FOREIGN KEY约束的表中添加

ALTER TABLE table_name
ADD
CONSTRAINT constraint_name
FOREIGN KEY column_name REFERENCES ref_table_name(ref_column_name)

• 删除FOREIGN KEY 约束

ALTER TABLE table_name
DROP
CONSTRAINT constraint_name

唯一性约束

在SQL Server系统中，不仅可以用PRIMARY KEY指定数据的唯一性，使用UNIQUE约束类型也可以指定数据唯一性，UNIQUE约束类型指定的列数据值中不允许重复。与PRIMARY KEY不同的是，UNIQUE约束类型可以同时指定一列或多列，而且指定的数据列中可以存在空值。

CONSTRAINT constraint_name
UNIQUE [CLUSTERED | NONCLUSTERED]
(column_name [,...n])

UNIQUE约束类型既可以用于列约束，也可以用于表约束。

CHECK约束

CHECK约束用于指定列的逻辑表达式，同样既可以用于列约束也可以用于表约束。

CONSTRAINT constraint_name
CHECK
( boolean_expression)

DEFAULT约束

DEFAULT约束通过定义列的默认值或使用数据库的默认值对象绑定表的列，来指定列的默认值。当给表列定义了DEFAULT约束后，在表中插入一条记录时，SQL SERVER会将该列的对应位置填入默认值。只能进行列级约束。

column_name data_type  DEFAULT default_value

NOT NULL约束

NOT NULL约束用于定义某列的值不能为空。

column_name  data_type  NOT NULL

NOT NULL约束为列级约束。

规则

规则是对存储的数据表的列或用户定义数据类型中的值的约束。规则与其作用的表或用户定义数据类型是相互独立的，也就是说，对表或用户定义数据类型的任何操作与对其设置的规则不存在影响。

规则与CHECK约束类似，但是又存在不同点：

• CHECK约束类型是创建表时定义的，而规则可以在创建表之后单独对列约束。
• CHECK约束类型只能作用于其定义的列，而规则可以同时作用于多个数据列。

创建规则

CREATE RULE rule_name
AS condition_expression

绑定规则

创建规则后，应将规则绑定到数据表列上或用户定义的数据类型中，当向绑定了规则的列或绑定了规则的用户定义数据类型的所有列中插入或更新数据时，新的数据必须符合规则。

EXEC sp_bindrule 'rule_name', 'table_name.column_name'[,'futureonly_flag']

解除规则

如果不再使用规则，可以解除规则，解除规则使用存储过程sp_unbindrule语句。

EXEC sp_unbindrule 'table_name.column_name' [,'futureonly_flag']

查看规则

可以使用存储过程sp_help查看规则，包括规则名称、所有者以及创建时间等基本信息。

EXEC sp_help rule_name

删除规则

当不再需要定义规则时，可以使用DROP RULE语句删除当前数据库中的一个或多个规则。

DROP RULE rule_name [,...n]

在删除规则时，必须确保已经解除规则与数据列或用户定义的数据类型的绑定，否则，在执行删除语句时会出错。

使用默认值

默认值是指在用户插入数据时，如果没有给某列指定相应的数据值，SQL Server系统会自动填充该列的值。默认值一般应用在数据列或用户定义的数据类型中，但是，它不会因数据列或用户定义的数据类型修改、删除等操作而受影响。

创建默认值

CREATE DEFAULT default_name
AS constant_name

绑定默认值

当创建默认值后，应将默认值绑定到数据列或用户定义的数据类型中，这样，创建的默认值才可以应用到数据列或用户定义的数据类型中。

EXEC sp_binddefault 'default_name', 'table_name.column_name'[,'futureonly_flag']

解绑默认值

如果不再需要绑定默认值，则可以使用存储过程sp_unbinddefault语句将绑定解除。

EXEC sp_unbinddefault 'table_name.column_name'

查看默认值

EXEC sp_help 'default_name'

删除默认值

当不再需要定使用定义的默认值时，需要将其删除。

DROP DEFAULT default_name

在删除默认值时，必须确保已经解除默认值与数据列或用户定义的数据类型绑定，否则，在执行删除语句时会出错。