实验数据库的建立和维护

1、实验2 :数据库的建立和维护实验本实验需要2学时。一、实验目的要求学生熟练掌握使用SQL、Transact-SQL和SQL Server企业管理器建立数据库、表、修改数据 库、表和删除数据库和表的操作。二、实验内容和步骤1. 创建数据库1）在企业管理器屮创建数据库姓名_数据库名，要求：数据库姓名数据库名初始大小为10MB最大大小为50MB数据库自动增长，增长方式是按5%比例增长; 日志文件初始大小为2MB最大可增长到5MB按1MB增长；数据库的逻辑文件名和物理文件名均采用默 认值；（分别为姓名_数据库名和 姓名姓名_数据库名mdf事务日志的逻辑文件名和物理文件名也采用默认值。（分别为姓名_数据

2、库名_LOG和姓名姓名_数据库名_LOGdf）2）使用T-SQL语句创建数据库 姓名_数据库名_2要求同1 ；Create database 姓名_数据库名_2On（name=STUDENT_Ol_data,姓名 姓名_数据库名_2. mdf，,size=10,maxsize=unlimited,filegrowth二10%）log on（name=studentl_log,姓名 姓名_数据库名.mdfJ,size二2, maxsize=5, filegrowth二1）2. 创建表以上面创建的数据库为例，创建表。创建表实际上就是创建表结构，再向表屮输入数据。既可以使用企业管理器创建表，也可以用

3、Transact-SQL命令创建表。(1) 使用企业管理器创建表(2) 使用Transact-SQL命令创建表【例1】创建图书信息表BooksoCREATE TABLE books(编号 char (15) CONSTRAINT PK_Books PRIMARY KEY,书名 vaichai(42) NULL ,作者 vdrchd：r(8) NULL ,出版社 varcha:r(28)NULL ,定价 real NULL CONSTRAINT CK_Books CHECK (定价 0)上面例子创建了一个包含5个列的books表，记录了图书的编号、书名、作者、出版社、定价等基本信息。在编号列上定

4、义了一个主键约朿，约束命名为 PK_Books；列定价上定义了检查约束，命 名为CK_Books使定价的值不能是0或负数。【例2创建图书借阅信息表Borrowinf。CREATE 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 (getdateO),还期 datetime

5、 NULL上面例子创建了一个包含5个列的borrowinf表，记录了读者借阅图书的情况，包含读者编号、图书编号、借期、还期等基本信息。列读者编号通过REFERENCE键字定义了一个外键，它引用Readers表编号列 的值。在图书编号列上定义外键的时候使用了 “FOREIGN KEY子句，也可以象读者编号列那样不使用这一子句。在借期列上定义了默认值，用到了系统函数getdateO,用于获得当前日期。定义了这一默认值后，当向表中插入记录时，如果指明了借书日期，则使用该日期填充借期列，否则，为借期 列上添上通过系统函数得到的当前日期。在这个例子中ON DELETE?句特别值得注意，在有关章节将要介绍

6、SQL Server 2000的参照完整性实现了级联修改和级联删除的功能，ON DELETE子句就是用于指定当删除表屮被其他表外键引用的行时采取什么动作，ON DELETED句有两个选项。 NO ACTION：被其他表处键引用的行不能被删除，这是 SQL Server 7.0以前版本的做法。 CASCADE被其他表外键引用的行可以被删除，而且其他表屮通过外键引用了该行的行都将被删除，读者编号列上的外键引用了 readers表的编号列，ON DELETE子句的值为CASCADE所以在readers表中删除一个读者记录时，如果这个读者在Borrowinf表中已有借阅记录，则borrowinf表中的

7、这些 记录也都将一同被删除。图书编号列上的外键引用了books表的编号列，ON DELETE?句的值为NO ACTION这说明在books表中删除一个图书记录时，如果在borrow inf表中已有该书的借阅记录，则这一删除操作不能被执行。【例3创建读者信息表readers。CREATE TABLE readers(编号 char (10) NOT NULL PRIMARY KEY,姓名 char (8) NULL ,读者类型int NULL ,限借阅数量int NULL ,借阅期限int NULL )上面例子创建了一个包含5个列的readers表，用来记录读者的信息，包含编号、姓名、读者类型、

8、限借阅数量、借阅期限等基本信息，主键是编号。3修改表表创建后，难免要对其进行修改。可以使用ALTER TABLE语句或企业管理器进行表的修改。使用企业管理器 修改表，可以用鼠标右键单击要修改的表，在弹出的快捷菜单屮选择【设计表】命令，将弹出入图5-10所示的 设计对话框，此时可以与新建表时一样，向表中加入列、从表中删除列或修改列的属性，修改完毕后单击【保存】 按扭即可。使用属性对话框可以修改检查、外键或主键约束及索引等。这里重点介绍使用ALTER TABLE语句来 对表进行修改。1) ALTER TABLE命令格式2) 修改列属性表屮的每一列都有列名、数据类型、数据长度以及是否允许为空值等属性

9、，这些属性可以在表创建后修改。修改列属性使用子句 ALTER COLUMN比如，以下的语句将rooks表的出版社列改为最大长度为30的 腹口曲工型数据，且不允许空值。USE Student_01ALTER TABLE Books ALTER COLUMN 出版社 vmrchat(30) NOT NULL默认状态下，列是被设置为允许空值的，将一个原来允许空值的列改为不允许空值，必须满足以下两个条 件：表中记录在该列上均不为空值。该列上没有创建索引。3) 添加和删除列向表屮增加一列时，应使新增加的列有默认值或允许为空值，如果既没有提供默认值也不允许为空值，那么新增列的操作将出错。向表中添加列需要使

10、用ALTERTABLE的ADD子句。例如，向表books屮添加出版日期列QataTime型，允许 空值，可以使用如下语句：USE Student_01ALTER TABLE books ADD 岀版日期 datetime NULL又如向readers表中添加电子邮件地址列，且要求输入的电子邮件地址必须包含：符号，可以使用以下语句：USE Student_01ALTER TABLE readers ADD 邮件地址 varchat（50） NULLCONSTRAINT CK_EA CHECK（邮件地址 like , %）删除一列需要使用ALTERTABLE的子句，比如删除readers表的邮件地

11、址列，可以使用以下语句：USE Student_01ALTER TABLE readers DROP COLUMN 邮件地址4）修改约束可以使用ALTERTABLE语句向一个已经存在的表添加约束或删除约朿，分别使用ADD CONSTRAINT子句和DROP COHSTRAIN子句。如果创建表borrowinf时没有定义主键约束（该表的主键由读者编号、图书编号、借期3个属性列组成）,则可以用以下语句定义主键：USE Student_01ALTER TABLE Readers ADD CONSTRAINT PK_BH PRIMARY KE读者编号，图书编号，借期丿向已存在的表屮的 某一列或某几列添

12、加主键约束，表中已有的数据在这几列上需要满足以下两个 条件：不能有重复的数据。不能有空值。删除主键约朿PK_BH可以使用以下语句：USE Student_01ALTER TABLE Readers DROP CONSTRAINT PK_BH PRIMARY KEY读者编号，图书编号，借期丿修改约束时几 个子句的说明：（1） ? WITH NOCHEC 子 句用包含WITH NOCHEC子句的ALTER TABLE语句，添加的约朿只对在以后改变或插入的行发生作用，而不检查已存在的行。比如，要对books表的定价列添加约朿，使定价大于0,可以使用如下代码：USE Student_01ALTER T

13、ABLE Books WITH NOCHECKADD CONSTRAINT CK_Books CHECK （定价 0）(2) ? NOCHECK CONSTRAINTS CHECK CONSTRAIN 子句可以在ALTER TABLE语句屮使用NOCHECK CONSTRAINT句，使表的指定列上的检查约束无效，此时就可以添加一些不满足原来约朿要求的数据了。使用CHECK CONSTRAIN子句可以使检查约朿重新有效。这两个子句后面都要用约束名作为参数。例，为readers表添加了邮件地址列，并定义了检查约束使邮件地址必须包含“”符号。邮件地址列的定义如下：ALTER TABLE reader

14、s ADD 邮件地址 varchat (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角色成员、数据库和数据库对象所有者及其授权用户才能向表中添加数据。在添加数

15、据时，对于不同的列数据类型，插入数据的格式不一样，因此，应严格遵守它们各自的要求。添加数据按输入顺序保存,条数不限，只受存储空间 的限制。1) 使用企业管理器添加数据启动企业管理器后，展开【数据库】文件夹，再展开要添加数据的数据库(如Student_01 )，可以看到所有的数据库对象，单击【表】，用鼠标右击右边列表中要操作的表(例如readers ),运行弹出快捷菜单 中的【打开表】命令，在弹出的子菜单中单击【返回所有行】命令，打开该表的数据窗口。如图5-13所7J o图5-13表Readers的数据窗口在数据窗口中，用户可以添加多行新数据, 同时还可以修改表屮数据。使用该窗口的快捷菜单，可以

16、实现表屮数据各行记录间跳转、剪贴、复制和粘贴等。2) 使用Transact-SQL命令添加数据(1) 添加数据到一行屮的所有列语句：INSERT readers VALUES(,，王晓奇，3, 2)为表readers添加一行数据。INSERT语句中无须罗列表屮的列名，但VALUES提供的数据与Readers表的各列在数量、顺序及数据类型各方面要一一对应。(2) 添加数据到一行中的部分列用下面语句为表添加一行中的部分列：INSERT Reade rs (编号，姓名，读者类型)VALUES ,李亚茜，3)在此情况下，INSERT语句屮必须罗列表中的列名，并且在添加数据前应该确认没有被添加的列上应设

17、有默认值或NULL否则这条INSERT语句出错。语句中的列名的顺序只要与VALUES提供的数据一一对应即可。(3) 使用INSERT- SELECT语句添加数据使用INSERT子句可以将从一个或多个表或视图查询出的数据添加到表中，它可以添加多行数据，而VALUES?句只能添加一行数据，但它们都可以给数据行的全部列或部分列添加数据。需要注意的是被插入数据的表与SELECT子句的结果集必须兼容，即列数、列序、数据类型都应兼容。(4) 使用INSERT-DEFAULT VALUE语句添加数据使用INSERTDEFAULT VALUE子句能够向表屮添加一行数据，语句为：INSERT INTO tabl

18、e_name DEFAULT VALUES添加的数据行各列均为默认值，未设默认值的列，列值为NULLo5修改和删除表屮的数据1) 修改表中的数据(1)修改所有数据行的值【例12】利用UPDATE修改借阅情况表borrow!nf的应还日期为自借期开始后的30天之内，语句为：USE Student_01UPDATE boorowinf SET 应还日期二借期+30读者可以通过SELECT命令查看修改前后数据的变化情况。(3) 修改部分数据行的值【例13将读者类型表type屮的所有学生读者的限借阅数量增加5、借阅期限增加30天，所有教师读者的限借阅数量增加10、借阅期限增加60天，可用以下两个语句实现：UPDATE type SET限借阅数量邙艮借阅数量+5,借阅期限二借阅期限+30WHERE类型名称二学生UPDATE typeSET限借阅数量邙艮借阅数