SQL语法整理[3]——表的管理

 

SQL语法整理[3]——表的管理

USE     school

GO

/*查看student表的属性*/

EXECUTE    SP_HELP   student

GO

 

修改表格名称

/*students为现在表格名称，student为变更后表格名称*/

EXECUTE   SP_RENAME  ‘students’,’student’

GO

 

/*将student表格中的“学生名”列的列名变更为“name”*/

EXECUTE   SP_RENAME   ‘student.学生名’,’name’

GO

 

ALTER TABLE修改表结构

（修改,增加,删除列或约束;

使约束和触发器有效或无效等。）

 

添加列

ALTER   TABLE    student

ADD    city     VARCHAR(10)

 

删除列

ALTER   TABLE    student

DROP   COLUMN   city

 

将学生表年龄字段变更为字符串U

ALTER   TABLE    student

ALTER    COLUMN    age   CHAR(10)

 

将列变为NOT NULL

ALTER    TABLE    student

ALTER    COLUMN    graduation  CHAR(20)   NOT  NULL

 

 

实现数据的完整性

数据的正确,信息的完整性具有3个特点:

数据的值正确

数据的和谐性

数据之间保持不同表之间的和谐关系

 

数据完整性概念

数据完整性是指存放在数据库中,数据要满足业务规则.

 

在数据库中数据的完整性类型共有3种:

a.实体完整性

b.域完整性

c.引用完整性

 

a.实体完整性

也称为行完整性,是指表中的每一行都必须能够惟一标识,且不存在重复的数据行.

实现实体完整性的方法:

     (1)强制表的标识规范;

     (2)列或主键的完整性(通过索引、UNIQUE约束、PRIMARY KEY约束或IDENTITY属性  实现).

 

b.域完整性

也称为列完整性,是指限制用户往列中输入内容，保证给定列的输入数据的有效性。

实现实体完整性的方法:

     (1)限制列值的数据类型;

     (2)限制列值的格式(通过设置CHECK约束与规则来实现)；

     (3)限制列值的取值范围(通过FOREIGN KEY约束、CHECK约束、DEFAULT定义、NOT NULL  定义和规则)

 

（CHECK约束和规则都是实现域完整性的方法。CHECK约束与表存储在一起，当表被删除时，CHECK约束也将同时被删除；规则独立于表存在于数据库中，可以被多个表操作引用，只有当数据库被删除，规则才被删除。）

 

c. 引用完整性

也称为参照完整性、关联完整性，是指当一个表引用了另一个表中的某些数据时，要防止非法的数据更新，如插入、修改或删除，以保证表之间已定义的关系。实现引用完整性主要通过主键与外键的联系来实现。外键的取值参照主键的取值，即外键的取值要不取自主键，要不为空。设置引用完整性可以确保键值在所有的关联表中都一致，这种一致性不允许引用参照表中不存在的值，如果主键值更改了，那么在整个数据库中对该键值的引用也要进行一致的更改。

 

约束

在SQL Server中用于实现数据完整性的途径，主要是约束(Constraint)、标识列(Identity Column) 、默认值(Default) 、规则(Rule) 、触发器(Trigger) 、数据类型(Data Type) 、索引(Index)和存储过程(Stored Procedure)等。

PRIMARY KEY主键约束

FOREIGN KEY外键约束

UNIQUE惟一约束

CHECK检查约束

DEFAULT默认值约束

CASCADE级联引用一致性约束

 

（PRIMARY KEY主键约束：当某一（组）列被设置为主关键字，该一（组）列的取值不能为空或有重复值出现。

UNIQUE惟一约束：当某一（组）列被设置了惟一性约束，该一（组）列的取值不能有重复值出现，可以取空值，但最多只能有一个数据为空。

既然有了主键,为什么还需要UNIQUE约束呢?每个实体,都应该有一个惟一的标识来表示自己,这类属性值可以作为表的主键.另一方面,实体可能还有其他的属性值,也是需要惟一的,比如驾照,军人证,暂住证等,这类属性值就不能作为表的主键存在,但是还需要对存储这类值的列进行限制,则出现了UNIQUE约束.）

 

Check约束

在parents表格中添加salary列，数据类型为money，并为其创建约束

ALTER     TABLE     parents

ADD   salary  MONEY   CHECK  (salary>0)

GO

 

/*先在student表格中添加一列名为city数据类型为char(10)*/

/*为学生表格添加限制生源地约束*/

USE   school

GO

ALTER TABLE student

ADD city CHAR(10)

GO

ALTER TABLE student

ADD CONSTRAINT ck_student_city CHECK(city in('北京','天津','上海','广州'))

GO

 

可以使用SP_HELP以及SP_HELPCONSTRAINT两个命令来查看当前表中所有的约束。

 

SQL Server是不会检验出约束的限制是否正确的。约束的作用是叠加的，如果一个列中有多个约束，则录入的数值就要满足所在列上的所有约束

 

DEFAULT约束

/*为表格student的city列添加一个默认值约束*/

USE      school

GO

ALTER    TABLE      student

ADD  CONSTRAINT   df_student_city

DEFAULT(‘北京’)   FOR  city

GO

 

/*输入带默认值的一条学生记录*/

USE    school

GO

INSERT    INTO   student

(id,name,gender,age,grade,class,teacher_id,parents_id,graduation,city)

VALUES

(2006008,’MR.wang’,’男’,24,1,1,101,2003,’2006年毕业’,default)

GO

 

USE    school

GO

INSERT    INTO   student

(id,name,gender,age,grade,class,teacher_id,parents_id,graduation)

VALUES

(2006008,’MR.wang’,’男’,24,1,1,101,2003,’2006年毕业’)

GO

 

<两种方法都对，都可以插入，其中city列的值为北京。>

 

 

如果该列允许为空，可以将该行赋予为空。由于有时不希望可能有空的列，可为该列定义一个默认值。

在创建表时，可以创建 DEFAULT 定义作为表定义的一部分。如果某个表已经存在，则可以为其添加 DEFAULT 定义。表中的每一列都可以包含一个 DEFAULT 定义。

若要修改 DEFAULT 定义，必须首先删除现有的 DEFAULT 定义，然后用新定义重新创建它。

 

UNIQUE约束

/*在student表格中增加名为uk_student_cardno惟一性约束*/

ALTER   TABLE      student

ADD    CONSTRAINT    uk_student_cardno  UNIQUE(cardno)

GO

 

对于默认值约束和检查约束,如果一开始创建表时,列允许空,并且表中已经存有数据了即空因为允许空,如果不添加数据的话),那么后来添加约束时,对空值不起作用.

 

禁用/启用约束

ALTER TABLE table_name                  

CHECK|NOCHECK CONSTRAINT constraint_name

 

对于新约束，假定为检验表原始数据(原始数据为NULL的不参加检验,不为NULL的原始数据参加检验)，对于重新启用的约束，假定为 不检验原始数据.

{ CHECK | NOCHECK} CONSTRAINT

指定启用或禁用 constraint_name。此选项只能与 FOREIGN KEY 和 CHECK 约束一起使用。如果指定了 NOCHECK，则将禁用约束，从而在将来插入或更新列时，不根据约束条件进行验证。无法禁用 DEFAULT、PRIMARY KEY 和 UNIQUE 约束。

WITH CHECK | WITH NOCHECK

指定表中的数据是否用新添加的或重新启用的 FOREIGN KEY 或 CHECK 约束进行验证。如果未指定，对于新约束，假定为 WITH CHECK，对于重新启用的约束，假定为 WITH NOCHECK。

如果不想根据现有数据验证新的 CHECK 或 FOREIGN KEY 约束，请使用 WITH NOCHECK。除极个别的情况外，建议不要进行这样的操作。在以后所有数据更新中，都将计算该新约束。如果添加约束时用 WITH NOCHECK 禁止了约束冲突，则将来使用不符合该约束的数据来更新行时，可能导致更新失败。

查询优化器不考虑使用 WITH NOCHECK 定义的约束。在使用 ALTER TABLE table CHECK CONSTRAINT ALL 语句重新启用这些约束之前，将忽略这些约束。

对表原始数据不校验添加CHECK约束

默认情况下，新添加的约束是检验表原始数据的.

如果数据库中的表需要创建约束,而原始数据或者说老系统的数据不满足约束的要求但想保留,而未来数据都满足约束的要求，这个时候只能通过不检验原始数据的方式来添加约束.

此时如果还想录入不满足约束的信息,只能通过约束的禁用|启用来实现.

对表中原始数据不检验添加约束的语法如下:

ALTER TABLE table_name                                                    

WITH   CHECK|NOCHECK ADD  CONSTRAINT  constraint_name   constraint_content

一般情况下是由于老的系统的数据需要继续保留,而新的数据却满足关系数据库的法则,在这种情况下只能通过对原始数据不检验添加约束来解决。

 

使用级联CASCADE

如果关联表中在主表中的主键信息进行了修改,关联表中的存储与其相对应的外键信息也应该同步的修改.但是由于主表被关联表所引用,所以无论删除或者修改主表中主键,都会报错,因为违反了信息的完整性.用户可以在先删除或者更改外键信息的前提下，再删除或者修改主键信息,这样就不会报错.但是如果表的主键信息,被多个表所引用,那么其操作将是非常繁重的,这个时候，就不能通过这个方法来实现数据的完整性.为此SQL Server提供了一种叫级联的方式，当主键的信息删除或者修改时,引用的外键信息就会相应地删除或者修改。

 

使用级联的语法如下:

FOREIGN KEY (column[,…n])                        

REFERENCES referenced_table_name[(ref_column[,…n])]

[ON DELETE CASCADE]                               

[ON UPDATE CASCADE]        

 

ON DELETE CASCADE :

删除级联,如果主表的主键被删除,则从属表中引用主键相关键值的信息也删除.

ON UPDATE CASCADE:

更改级联,如果主表的主键被修改，则从属表引用主键相关键值的信息也被更改。

 

在学生表格上创建外键约束

ALTER    TABLE    student

ADD   CONSTRAINT    fk_student_teacher_id_teachers_id

FOREIGN  KEY(teacher_id)     REFERENCES    teachers(id)

ON   DELETE     CASCADE

ON   UPDATE     CASCADE

GO

使用级联的情况下, 删除编号为101的老师, 修改编号为101的老师信息,结果是成功删除或更新.同时学生表中的tercher_id也被删除或更新

 

不使用级联的情况下,报错: 因为与学生表的外键约束冲突.学生表的外键tercher_id引用老师表的主键id.

 

创建/使用规则

规则类似于CHECK约束,是一种独立的对象,是与表同级别的对象.

CHECK约束只能约束表中的列,没有可复用性.而规则却是可复用的。

创建规则的语法如下:

CREATE RULE rule_name

AS condition_expression

绑定规则的语法如下:

SP_BINDRULE  [@rulename=]’rule’,[@objname=]’object_name’

 

创建规则/规则的绑定

规则由于是与表级别相同的对象,如果要将规则应用,必须与要使用这个规则的列进行绑定,这样的列才能被规则所限定.

/*在school数据库中创建年龄规则*/

USE    school

GO

CREATE     RULE      age_rule

AS

@age>0

GO

 

/*将规则与学生表格列绑定*/

USE    school

GO

EXECUTE   SP_BINDRULE     age_rule,’student.age’

GO

 

将规则从列上解除绑定/删除规则

当规则还与多个表的列有绑定的情况下是不能够随便删除的,需要先将规则从表的列上解除才能够删除规则。

/*解除表格student上规则与age列的绑定*/

USE   school

GO

EXECUTE   SP_UNBINDRULE   ‘student.age’

GO

/*删除规则age_rule*/

USE   school

GO

DROP   RULE    age_rule

GO

通过SP_CONSTRAINT或SP_HELP命令查看表上的规则。

 

创建/使用DEFAULT对象

DEFAULT对象与规则类似,都是与表同一个级别的对象，同规则的创建方式、使用方法是一样的。

DEFAULT对象是为特定数据库定义的，通过将其绑定到默认值要应用的每个列上，与规则类似，都可以被不同的列共享。

如果DEFAULT对象绑定到某一列上，该表已经为列指定了不同的默认值。在向列绑定新的默认值时，原来绑定的默认值对象就会自动地从该表中释放出来。

 

创建DEFAULT对象/绑定DEFAULT对象

/*创建一个名称为df_age的默认值*/

USE   school

GO

CREATE    DEFAULT    df_age

AS

23

GO

 

/*在学生表格年龄列绑定df_age*/

USE    school

GO

EXECUTE   SP_BINDEFAULT      df_age,’student.age’

GO

 

DEFAULT从列上解除绑定/删除DEFAULT对象

与解除规则绑定/删除规则的操作一样，如果要删除DEFAULT对象，就先要将绑定在表列上的DEFAULT解除，然后删除DEFAULT对象。

 

/*使用SP_UNBINDEFAULT命令解除绑定*/

USE    school

GO

EXECUTE    SP_UNBINDEFAULT   ‘student.age’

GO

 

/*删除df_age对象*/

USE    school

GO

DROP     DEFAULT     df_age

GO

 

DEFAULT与RULE的共同点如下：

a.独立于表的数据库对象。

b.定义后需要BIND列。

c.要删除必须要解除绑定。

 

 

——大二的期末考试整理 [e-mail]