实验2 数据表的创建与管理

实验目的

1.掌握SQL Server 2005数据表的类型、数据类型。

2.掌握SQL Server 2005创建数据表的不同方法。

3.使用图形界面和使用SQL语句。

4.掌握数据表结构的查看、修改及删除的操作。

5.掌握数据的完整性,6个完整性约束的应用。

实验内容及步骤

一、表的创建

1.利用SQL Server管理平台创建表

在SQL Server管理平台中，展开指定的服务器和数据库，打开想要创建新表的数据库，右击表对象，并从弹出的快捷菜单中选择“新建表”选项，如图3-1所示。在图3-1的对话框中，可以对表的结构进行更改，设置主键及字段属性，使用SQL Server管理平台可以非常直观地修改数据库结构和添加数据。在表中任意行上右击，则弹出一个快捷菜单，如图3-2所示。

图3-1 新建表对话框 

图3-2 设置字段属性对话框 

2. 利用create命令创建表 

使用create命令创建表非常灵活，它允许对表设置几种不同的选项，包括表名、存放位置和列的属性等。其完整语法形式如下：

         CREATE TABLE

  　   [database_name.[owner].|owner.]table_name

  　（{|column_name AS computed_column_expression|

         }[，…n]）

　　　　[ON{ filegroup|DEFAULT}] 

         [TEXTIMAGE_ON { filegroup|DEFAULT}] 

　　　　::={column_name data_type}

  　   [COLLATE ]

  　   [[DEFAULT constant_expression]

          |[IDENTITY[（seed,increment ）[NOT FOR REPLICATION]]]]

  　　   [ROWGUIDCOL]

  　   [][...n] 

例1创建了一个工人信息表，它包括工人编号、姓名、性别、出生日期、职位、工资和备注信息。

SQL语句的程序清单如下：

  CREATE TABLE worker 

 （number  char(8)  not null,

   name  char（8）  NOT NULL,

   sex  char（2）  NULL,

   birthday  datetime  null,

   job_title  varchar（10）  null,

   salary  money  null,

  memo  ntext  null

 ） 

3完整性约束 

约束是SQL Server提供的自动保持数据库完整性的一种方法，它通过字段中数据、记录中数据和表之间的数据来保证数据的完整性。在SQL SERVER中，对于基本表的约束分为列约束和表约束。

列约束是对某一个特定列的约束，包含在列定义中，直接跟在该列的其他定义之后，用空格分隔，不必指定列名；表约束与列定义相互，不包括在列定义中，通常用于对多个列一起进行约束，与列定义用’,’分隔，定义表约束时必须指出要约束的那些列的名称。

完整性约束的基本语法格式为：

   [CONSTRAINT constraint_name（约束名）] <约束类型>

   约束不指定名称时，系统会给定一个名称。

在SQL Server 2005中有6种约束：主键约束（primary key constraint）、惟一性约束（unique constraint）、检查约束（check constraint）、默认约束（default constraint）、外部键约束（foreign key constraint）和空值（NULL）约束。 

1)主键（PRIMARY KEY）约束 

PRIMARY KEY约束用于定义基本表的主键，它是惟一确定表中每一条记录的标识符，其值不能为NULL，也不能重复，以此来保证实体的完整性。PRIMARY KEY与UNIQUE约束类似，通过建立唯一索引来保证基本表在主键列取值的唯一性，但它们之间存在着很大的区别：

①在一个基本表中只能定义一个PRIMARY KEY约束，但可定义多个UNIQUE约束；

②对于指定为PRIMARY KEY的一个列或多个列的组合，其中任何一个列都不能出现空值，而对于UNIQUE所约束的唯一键，则允许为空。

注意：不能为同一个列或一组列既定义UNIQUE约束，又定义PRIMARY KEY约束。

PRIMARY KEY既可用于列约束，也可用于表约束。

例2 建立一个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)) 

2)惟一性约束 

惟一性约束用于指定一个或者多个列的组合值具有惟一性，以防止在列中输入重复的值。定义了UNIQUE约束的那些列称为唯一键，系统自动为唯一键建立唯一索引，从而保证了唯一键的唯一性。 当使用惟一性约束时，需要考虑以下几个因素：

使用惟一性约束的字段允许为空值；

•一个表中可以允许有多个惟一性约束；

•可以把惟一性约束定义在多个字段上；

•惟一性约束用于强制在指定字段上创建一个惟一性索引；

•默认情况下，创建的索引类型为非聚集索引。 

例3创建一个学生信息表，其中name字段具有惟一性。

程序清单如下：

Create  table  student（

id  char（8）,

name  char（10）,

sex  char（2）,

  constraint  pk_id  primary  key（id）,

  constraint  uk_identity  unique（name）

） 

3)检查约束  

   检查约束对输入列或者整个表中的值设置检查条件，以输入值，保证数据库数据的完整性。 

当使用检查约束时，应该考虑和注意以下几点：

•一个列级检查约束只能与的字段有关；一个表级检查约束只能与的表中字段有关；

•一个表中可以定义多个检查约束；

•每个CREATE TABLE语句中每个字段只能定义一个检查约束；

•在多个字段上定义检查约束，则必须将检查约束定义为表级约束；

•当执行INSERT语句或者UPDATE语句时，检查约束将验证数据；

•检查约束中不能包含子查询。 

例4建立一个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))

4)默认（DEFAULT）约束  

     默认约束指定在插入操作中如果没有提供输入值时，则系统自动指定值。默认约束可以包括常量、函数、不带变元的内建函数或者空值。使用默认约束时，应该注意以下几点：

（1）每个字段只能定义一个默认约束；

（2）如果定义的默认值长于其对应字段的允许长度，那么输入到表中的默认值将被截断；

（3）不能加入到带有IDENTITY属性或者数据类型为timestamp的字段上；

（4）如果字段定义为用户定义的数据类型，而且有一个默认绑定到这个数据类型上，则不允许该字段有默认约束。 

例5为 dept字段创建默认约束。

程序清单如下：

CREATE TABLE SC

(SNO CHAR(5) NOT NULL,

CNO CHAR(5) NOT NULL,

SCORE NUMERIC(3),

Dept char(10) constraint  con_dept  default  ‘计算机’ 

)

5) 外部键约束   

   外键 (FOREIGN KEY) 是用于建立和加强两个表数据之间的链接的一列或多列。外部键约束用于强制参照完整性。当使用外部键约束时，应该考虑以下几个因素：

•外部键约束提供了字段参照完整性；

•外部键从句中的字段数目和每个字段指定的数据类型都必须和REFERENCES从句中的字段相匹配；

•外部键约束不能自动创建索引，需要用户手动创建；

•用户想要修改外部键约束的数据，必须有对外部键约束所参考表的SELECT权限或者REFERENCES权限；

•参考同一表中的字段时，必须只使用REFERENCES子句，不能使用外部键子句；

•主键和外部键的数据类型必须严格匹配 

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

程序清单如下：

 CREATE 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)) 

6) 空值（NULL）约束    

空值（NULL）约束用来控制是否允许该字段的值为NULL。NULL值不是0也不是空白，更不是填入字符串的“NULL”字符串，而是表示“不知道”、“ 不确定”或“没有数据”的意思。

当某一字段的值一定要输入才有意义的时候，则可以设置为NOT NULL。如主键列就不允许出现空值，否则就失去了唯一标识一条记录的作用。空值（NULL）约束只能用于定义列约束。 

创建空值（NULL）约束常用的操作方法有如下两种：

     （1）在SQL Server管理平台中添加空值（NULL）约束；

     （2）使用Transact-SQL语句设置空值（NULL）约束。  

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

程序清单如下：

 CREATE TABLE S

 (SNO CHAR(10) CONSTRAINT S_CONS NOT NULL,

 SN VARCHAR(20),

 AGE INT,

 SEX CHAR(2) DEFAULT ’男’,

 DEPT VARCHAR(20))  

二、表的修改

1.利用Transact-SQL语句修改表

向表中添加新的字段：在学生表中添加一个“班级”字段，数据类型为字符型。

ALTER TABLE student add班级char 

◆删除表中的旧列：将学生表中的“Sdept”字段删除。

ALTER TABLE student DROP column Sdept

◆   更改表以添加具有约束的列：

给学生表的增加“grade”字段并加上CHECK约束，让其不可以大于100。

ALTER TABLE student ADD grade int 

Constraint chname check(grade<100)

Exec sp_help chname

给学生表中添加“birthday”字段，并且这个日期不能在录入当天的日期之后。

Alter table student Add birthday DATETIME NULL

CONSTRAINT ch_birthday CHECK (birthday◆ 添加具有默认值的可为空的列：

在学生表中加入“matriculationday”字段，并且这一字段的默认值为录入当天的日期。

Alter table  student

Add matriculationday smalldatetime NULL

Constraint adddateflt

Default getdate()

例9创建一个雇员信息表，然后在表中增加一个salary字段，删除表中的age字段，并且修改memo字段的数据类型。

SQL语句的程序清单如下：

create table employees（

id  char（8）  primary key,

name  char（20）  not null,

department  char（20）  null,

memo  char（30）  null,

age  int  null,

）

alter  table  employees add  salary  int  null,

alter  table  employees  drop  column  age,

alter  table  employees alter  column  memo  varchar（200）  null 

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

SQL语句的程序清单如下：

ALTER TABLE S 

ADD

CLASS_NO CHAR(6),

ADDRESS CHAR(40)

例11在SC表中增加完整性约束定义，使SCORE在0-100之间。

SQL语句的程序清单如下：

ALTER TABLE SC 

ADD 

CONSTRAINT SCORE_CHK CHECK(SCORE BETWEEN 0 AND 100) 

2 查看表 

当在数据库中创建了表后，有时需要查看表的有关信息。比如表的属性、定义、数据、字段属性和索引等。尤其重要的是查看表内存放的数据，另外有时需要查看表与其他数据库对象之间的依赖关系。

1)查看表的定义 ，如图3-3，3-4,3-5,3-6所示。

图3-3 选择表格属性对话框

图3-4  表格属性对话框

图3-5  选择打开表对话框

图3-6  显示表格数据对话框

2) 利用系统存储过程查看表的信息

系统存储过程Sp_help可以提供指定数据库对象的信息，也可以提供系统或者用户定义的数据类型的信息，其语法形式如下：p_help [[@objname=]name]

例12（1）显示当前数据库中所有对象的信息；（2）显示表Person.Contact 的信息。在SQL Server管理平台的查询窗口中，它们对应的语句和运行结果如图3-7和图3-8所示 

图3-7　所有数据库对象显示窗口

图3-8　当前数据库对象显示窗口

三、表的删除

删除表对象，我们可以在SQL Server管理平台中选择要删除的表直接删除，也可以通过Transact-SQL语句DROP 删除表的定义及表中的所有数据、索引、触发器、约束和权限规范。DROP TABLE语句的语法形式如下：DROP TABLE table_name

例13　删除company数据库中的表employee。

程序如下：

drop  table  company.dbo.employee 

练习题

练习1在Student数据库中利用查询分析器创建课程信息表Course、表结构如下：

练习2 表的管理

⑴使用SQL Server管理平台创建教工表teacher，要求如下：

 ⑵ 在查询分析器中用命令：DROP TABLE teacher删除该表。 

⑶ 根据条件使用Transact-SQL语言输入以下语句：

CREATE TABLE teacher 

(

 教工号 INT PRIMARY KEY IDENTITY(1,1),

 姓名  VARCHAR(18) NULL,

 家庭住址 CHAR(30) NULL,

 电话 INT NULL,

 职称 CHAR(14) DEFAULT '讲师',

 所在专业 VARCHAR(16) CONSTRAINT chname1

 CHECK(所在专业 IN ('计算机体系结构','计算机网络'，‘计算机软件’)) 

)

3．创建人事关系RSGX数据库，并定义职工和部门两个关系模式：

职工（职工号，姓名，年龄，职务，工资，部门号），其中职工号为主码；

部门（部门号，名称，经理名，电话），其中部门号为主码；

要求在模式中完成以下完整性约束条件的定义：

(1) 定义每个模式的主码；

(2) 定义参照完整性；

(3) 定义职工年龄不得超过60岁。