实验数据库的建立和维护

实验 2 ：数据库的建立和维护实验本实验需要 2 学时。

一、实验目的要求学生熟练掌握使用SQL 、Transact-SQL 和SQL Server 企业管理器建立数据库、表、修改数据库、表和删除数据库和表的操作。

二、实验内容和步骤1．创建数据库1)在企业管理器中创建数据库姓名_数据库名,要求：●数据库姓名_数据库名初始大小为10MB，最大大小为50MB，数据库自动增长，增长方式是按5%比例增长；●日志文件初始大小为2MB，最大可增长到5MB，按1MB增长；●数据库的逻辑文件名和物理文件名均采用默认值；●（分别为姓名_数据库名和e:\姓名\data\姓名_数据库名.mdf●事务日志的逻辑文件名和物理文件名也采用默认值。

●（分别为姓名_数据库名_LOG和e:\姓名\data\姓名_数据库名_LOG.ldf）2)②使用T-SQL语句创建数据库姓名_数据库名_2，要求同1；Create database 姓名_数据库名_2On(name=STUDENT_01_data,filename='e:\姓名\data\姓名_数据库名_2.mdf',size=10,maxsize=unlimited,filegrowth=10%)log on(name=student1_log,filename='e:\姓名\data\姓名_数据库名.mdf',size=2,maxsize=5,filegrowth=1)2．创建表以上面创建的数据库为例，创建表。

创建表实际上就是创建表结构，再向表中输入数据。

既可以使用企业管理器创建表，也可以用Transact-SQL命令创建表。

（1）. 使用企业管理器创建表（2）. 使用Transact-SQL命令创建表【例1】创建图书信息表Books。

CREATE TABLE books(编号 char(15) CONSTRAINT PK_Books PRIMARY KEY,书名 varchar(42) NULL ,作者varchar(8) NULL ,出版社 varchar(28)NULL ,定价real NULL CONSTRAINT CK_Books CHECK(定价>0))上面例子创建了一个包含5个列的books表，记录了图书的编号、书名、作者、出版社、定价等基本信息。

在编号列上定义了一个主键约束，约束命名为PK_Books；列定价上定义了检查约束，命名为CK_Books，使定价的值不能是0或负数。

【例2】创建图书借阅信息表Borrowinf。

C REATE TABLE borrowinf(读者编号 char(10) NOT NULL REFERENCES Readers(编号) ON DELETE CASCADE ,图书编号char (15) NOT NULLFOREIGN KEY (图书编号) REFERENCES Books(编号) ON DELETE NO ACTION,借期 datetime NULL DEFAULT (getdate()) ,还期 datetime NULL)上面例子创建了一个包含5个列的borrowinf表，记录了读者借阅图书的情况，包含读者编号、图书编号、借期、还期等基本信息。

列读者编号通过REFERENCE关键字定义了一个外键，它引用Readers表编号列的值。

在图书编号列上定义外键的时候使用了“FOREIGN KEY”子句，也可以象读者编号列那样不使用这一子句。

在借期列上定义了默认值，用到了系统函数getdate(),用于获得当前日期。

定义了这一默认值后，当向表中插入记录时，如果指明了借书日期，则使用该日期填充借期列，否则，为借期列上添上通过系统函数得到的当前日期。

在这个例子中ON DELETE子句特别值得注意，在有关章节将要介绍SQL Server 2000的参照完整性实现了级联修改和级联删除的功能，ON DELETE子句就是用于指定当删除表中被其他表外键引用的行时采取什么动作，ON DELETE子句有两个选项。

● NO ACTION：被其他表处键引用的行不能被删除，这是SQL Server 7.0以前版本的做法。

● CASCADE:被其他表外键引用的行可以被删除，而且其他表中通过外键引用了该行的行都将被删除，读者编号列上的外键引用了readers表的编号列，ON DELETE子句的值为 CASCADE，所以在readers表中删除一个读者记录时，如果这个读者在Borrowinf表中已有借阅记录，则borrowinf表中的这些记录也都将一同被删除。

图书编号列上的外键引用了books表的编号列，ON DELETE子句的值为NO ACTION，这说明在books表中删除一个图书记录时，如果在borrowinf表中已有该书的借阅记录，则这一删除操作不能被执行。

【例3】创建读者信息表readers。

CREATE TABLE readers(编号char (10) NOT NULL PRIMARY KEY,姓名char (8) NULL ,读者类型 int NULL ,限借阅数量 int NULL ,借阅期限 int NULL )上面例子创建了一个包含5个列的readers表，用来记录读者的信息，包含编号、姓名、读者类型、限借阅数量、借阅期限等基本信息，主键是编号。

3 修改表表创建后，难免要对其进行修改。

可以使用ALTER TABLE 语句或企业管理器进行表的修改。

使用企业管理器修改表，可以用鼠标右键单击要修改的表，在弹出的快捷菜单中选择【设计表】命令，将弹出入图5-10所示的设计对话框，此时可以与新建表时一样，向表中加入列、从表中删除列或修改列的属性，修改完毕后单击【保存】按扭即可。

使用属性对话框可以修改检查、外键或主键约束及索引等。

这里重点介绍使用ALTER TABLE语句来对表进行修改。

1)ALTER TABLE命令格式2)．修改列属性表中的每一列都有列名、数据类型、数据长度以及是否允许为空值等属性，这些属性可以在表创建后修改。

修改列属性使用子句ALTER COLUMN。

比如，以下的语句将rooks表的出版社列改为最大长度为30的varchar 型数据，且不允许空值。

USE Student_01ALTER TABLE Books ALTER COLUMN 出版社 varchar(30) NOT NULL默认状态下，列是被设置为允许空值的，将一个原来允许空值的列改为不允许空值，必须满足以下两个条件：·表中记录在该列上均不为空值。

·该列上没有创建索引。

3)添加和删除列向表中增加—列时，应使新增加的列有默认值或允许为空值，如果既没有提供默认值也不允许为空值，那么新增列的操作将出错。

向表中添加列需要使用ALTER TABLE的ADD子句。

例如,向表books中添加出版日期列,DataTime型,允许空值,可以使用如下语句:USE Student_01ALTER TABLE books ADD 出版日期 datetime NULL又如向readers表中添加电子邮件地址列，且要求输入的电子邮件地址必须包含：‘@’符号，可以使用以下语句：USE Student_01ALTER TABLE readers ADD 邮件地址 varchar(50) NULLCONSTRAINT CK_EA CHECK(邮件地址like，'%@%')删除—列需要使用ALTER TABLE的子句，比如删除readers表的邮件地址列，可以使用以下语句：USE Student_01ALTER TABLE readers DROP COLUMN邮件地址4)修改约束可以使用ALTER TABLE语句向一个已经存在的表添加约束或删除约束，分别使用 ADD CONSTRAINT子句和DROP COHSTRAINT子句。

如果创建表borrowinf时没有定义主键约束(该表的主键由读者编号、图书编号、借期3个属性列组成)，则可以用以下语句定义主键：USE Student_01ALTER TABLE Readers ADD CONSTRAINT PK_BH PRIMARY KEY(读者编号,图书编号,借期)向已存在的表中的某一列或某几列添加主键约束，表中已有的数据在这几列上需要满足以下两个条件：·不能有重复的数据。

·不能有空值。

删除主键约束PK_BH，可以使用以下语句：USE Student_01ALTER TABLE Readers DROP CONSTRAINT PK_BH PRIMARY KEY(读者编号,图书编号,借期)修改约束时几个子句的说明：(1)WITH NOCHECK子句用包含WITH NOCHECK子句的ALTER TABLE语句，添加的约束只对在以后改变或插入的行发生作用，而不检查已存在的行。

比如，要对books表的定价列添加约束，使定价大于O，可以使用如下代码：USE Student_01ALTER TABLE Books WITH NOCHECKADD CONSTRAINT CK_Books CHECK (定价>0)(2)NOCHECK CONSTRAINT子句和CHECK CONSTRAINT子句可以在ALTER TABLE语句中使用NOCHECK CONSTRAINT子句，使表的指定列上的检查约束无效，此时就可以添加一些不满足原来约束要求的数据了。

使用CHECK CONSTRAINT子句可以使检查约束重新有效。

这两个子句后面都要用约束名作为参数。

例，为readers表添加了邮件地址列，并定义了检查约束使邮件地址必须包含“@”符号。

邮件地址列的定义如下:ALTER TABLE readers ADD邮件地址varchar (50) NULLCONSTRAINT CK_EA CHECK (邮件地址like'%@%')使用以下语句使这一约束无效:USE Student_01ALTER TABLE Readers NOCHECK CONSTRAINT CK_EA为了使约束重新有效，可使用如下语句：USE Student_01ALTER TABLE Readers CHECK CONSTRAINT CK_EA4 向表中添加数据创建表只是建立了表结构，之后，应该向表中添加数据。

只有System Administrator角色成员、数据库和数据库对象所有者及其授权用户才能向表中添加数据。

在添加数据时，对于不同的列数据类型，插入数据的格式不一样，因此，应严格遵守它们各自的要求。

添加数据按输入顺序保存，条数不限，只受存储空间的限制。