实验数据库的建立和维护.docx

实验数据库的建立和维护.docx

《实验数据库的建立和维护.docx》由会员分享，可在线阅读，更多相关《实验数据库的建立和维护.docx（10页珍藏版）》请在冰点文库上搜索。

实验数据库的建立和维护

实验2：

数据库的建立和维护实验

本实验需要2学时。

一、实验目的

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

二、实验内容和步骤

1．创建数据库

1）在企业管理器中创建数据库姓名_数据库名,要求：

数据库姓名_数据库名初始大小为10MB，最大大小为50MB，数据库自动增长，增长方式是按5%比例增长；

日志文件初始大小为2MB，最大可增长到5MB，按1MB增长；

数据库的逻辑文件名和物理文件名均采用默认值；

（分别为姓名_数据库名和e:

\姓名\data\姓名_数据库名.mdf

事务日志的逻辑文件名和物理文件名也采用默认值。

（分别为姓名_数据库名_LOG和e:

\姓名\data\姓名_数据库名_LOG.ldf）

2）②使用T-SQL语句创建数据库姓名_数据库名_2，要求同1；

Createdatabase姓名_数据库名_2

On

（name=STUDENT_01_data,

filename='e:

\姓名\data\姓名_数据库名_2.mdf',

size=10,

maxsize=unlimited,

filegrowth=10%）

logon

（name=student1_log,

filename='e:

\姓名\data\姓名_数据库名.mdf',

size=2,

maxsize=5,

filegrowth=1）

2．创建表

以上面创建的数据库为例，创建表。

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

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

（1）.使用企业管理器创建表

（2）.使用Transact-SQL命令创建表

【例1】创建图书信息表Books。

CREATETABLEbooks

（

编号char（15）CONSTRAINTPK_BooksPRIMARYKEY,

书名varchar（42）NULL,

作者varchar（8）NULL,

出版社varchar（28）NULL,

定价realNULLCONSTRAINTCK_BooksCHECK（定价>0）

）

上面例子创建了一个包含5个列的books表，记录了图书的编号、书名、作者、出版社、定价等基本信息。

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

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

CREATETABLEborrowinf

（读者编号char（10）NOTNULLREFERENCESReaders（编号）ONDELETECASCADE,

图书编号char（15）NOTNULL

FOREIGNKEY（图书编号）REFERENCESBooks（编号）ONDELETENOACTION,

借期datetimeNULLDEFAULT（getdate（））,

还期datetimeNULL

）

上面例子创建了一个包含5个列的borrowinf表，记录了读者借阅图书的情况，包含读者编号、图书编号、借期、还期等基本信息。

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

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

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

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

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

●NOACTION：

被其他表处键引用的行不能被删除，这是SQLServer7.0以前版本的做法。

●CASCADE:

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

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

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

CREATETABLEreaders

（

编号char（10）NOTNULLPRIMARYKEY,

姓名char（8）NULL,

读者类型intNULL,

限借阅数量intNULL,

借阅期限intNULL）

上面例子创建了一个包含5个列的readers表，用来记录读者的信息，包含编号、姓名、读者类型、限借阅数量、借阅期限等基本信息，主键是编号。

3修改表

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

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

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

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

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

1）ALTERTABLE命令格式

2）．修改列属性

表中的每一列都有列名、数据类型、数据长度以及是否允许为空值等属性，这些属性可以在表创建后修改。

修改列属性使用子句ALTERCOLUMN。

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

USEStudent_01

ALTERTABLEBooksALTERCOLUMN出版社varchar（30）NOTNULL

默认状态下，列是被设置为允许空值的，将一个原来允许空值的列改为不允许空值，必须满足以下两个条件：

·表中记录在该列上均不为空值。

·该列上没有创建索引。

3）添加和删除列

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

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

例如,向表books中添加出版日期列,DataTime型,允许空值,可以使用如下语句:

USEStudent_01

ALTERTABLEbooksADD出版日期datetimeNULL

又如向readers表中添加电子邮件地址列，且要求输入的电子邮件地址必须包含：

‘@’符号，可以使用以下语句：

USEStudent_01

ALTERTABLEreadersADD邮件地址varchar（50）NULL

CONSTRAINTCK_EACHECK（邮件地址like，'%@%'）

删除—列需要使用ALTERTABLE的子句，比如删除readers表的邮件地址列，可以使用以下语句：

USEStudent_01

ALTERTABLEreadersDROPCOLUMN邮件地址

4）修改约束

可以使用ALTERTABLE语句向一个已经存在的表添加约束或删除约束，分别使用ADDCONSTRAINT子句和DROPCOHSTRAINT子句。

如果创建表borrowinf时没有定义主键约束（该表的主键由读者编号、图书编号、借期3个属性列组成），则可以用以下语句定义主键：

USEStudent_01

ALTERTABLEReadersADDCONSTRAINTPK_BHPRIMARYKEY（读者编号,图书编号,借期）

向已存在的表中的某一列或某几列添加主键约束，表中已有的数据在这几列上需要满足以下两个条件：

·不能有重复的数据。

·不能有空值。

删除主键约束PK_BH，可以使用以下语句：

USEStudent_01

ALTERTABLEReadersDROPCONSTRAINTPK_BHPRIMARYKEY（读者编号,图书编号,借期）

修改约束时几个子句的说明：

（1）?

WITHNOCHECK子句

用包含WITHNOCHECK子句的ALTERTABLE语句，添加的约束只对在以后改变或插入的行发生作用，而不检查已存在的行。

比如，要对books表的定价列添加约束，使定价大于O，可以使用如下代码：

USEStudent_01

ALTERTABLEBooksWITHNOCHECK

ADDCONSTRAINTCK_BooksCHECK（定价>0）

（2）?

NOCHECKCONSTRAINT子句和CHECKCONSTRAINT子句

可以在ALTERTABLE语句中使用NOCHECKCONSTRAINT子句，使表的指定列上的检查约束无效，此时就可以添加一些不满足原来约束要求的数据了。

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

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

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

邮件地址列的定义如下:

ALTERTABLEreadersADD邮件地址varchar（50）NULL

CONSTRAINTCK_EACHECK（邮件地址like'%@%'）

使用以下语句使这一约束无效:

USEStudent_01

ALTERTABLEReadersNOCHECKCONSTRAINTCK_EA

为了使约束重新有效，可使用如下语句：

USEStudent_01

ALTERTABLEReadersCHECKCONSTRAINTCK_EA

4向表中添加数据

创建表只是建立了表结构，之后，应该向表中添加数据。

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

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

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

1）使用企业管理器添加数据

启动企业管理器后，展开【数据库】文件夹，再展开要添加数据的数据库（如Student_01）,可以看到所有的数据库对象，单击【表】，用鼠标右击右边列表中要操作的表（例如readers）,运行弹出快捷菜单中的【打开表】命令，在弹出的子菜单中单击【返回所有行】命令，打开该表的数据窗口。

如图5-13所示。

图5-13表Readers的数据窗口

在数据窗口中，用户可以添加多行新数据，同时还可以修改表中数据。

使用该窗口的快捷菜单，可以实现表中数据各行记录间跳转、剪贴、复制和粘贴等。

2）使用Transact-SQL命令添加数据

（1）添加数据到一行中的所有列

语句：

INSERTreadersVALUES（‘2004060001’，‘王晓奇’，3，2）

为表readers添加一行数据。

INSERT语句中无须罗列表中的列名，但VALUES提供的数据与Readers表的各列在数量、顺序及数据类型各方面要一一对应。

（2）添加数据到一行中的部分列

用下面语句为表添加一行中的部分列：

INSERTReaders（编号，姓名，读者类型）

VALUES（‘2004060003’，‘李亚茜’，3）

在此情况下，INSERT语句中必须罗列表中的列名，并且在添加数据前应该确认没有被添加的列上应设有默认值或NULL，否则这条INSERT语句出错。

语句中的列名的顺序只要与VALUES提供的数据一一对应即可。

（3）使用INSERT…SELECT语句添加数据

使用INSERT子句可以将从一个或多个表或视图查询出的数据添加到表中，它可以添加多行数据，而VALUES子句只能添加一行数据，但它们都可以给数据行的全部列或部分列添加数据。

需要注意的是被插入数据的表与SELECT子句的结果集必须兼容，即列数、列序、数据类型都应兼容。

（4）使用INSERT…DEFAULTVALUES语句添加数据

使用INSERT…DEFAULTVALUES子句能够向表中添加一行数据，语句为：

INSERTINTOtable_nameDEFAULTVALUES

添加的数据行各列均为默认值，未设默认值的列，列值为NULL。

5修改和删除表中的数据

1）修改表中的数据

（1）修改所有数据行的值

【例12】利用UPDATE修改借阅情况表borrowinf的应还日期为自借期开始后的30天之内，语句为：

USEStudent_01

UPDATEboorowinfSET应还日期=借期+30

读者可以通过SELECT命令查看修改前后数据的变化情况。

（3）修改部分数据行的值

【例13】将读者类型表type中的所有学生读者的限借阅数量增加5、借阅期限增加30天，所有教师读者的限借阅数量增加10、借阅期限增加60天，可用以下两个语句实现：

UPDATEtypeSET限借阅数量=限借阅数量+5,借阅期限=借阅期限+30

WHERE类型名称=‘学生’

UPDATEtype

SET限借阅数量=限借阅数量+10,借阅期限=借阅期限+60

WHERE类型名称=‘教师’

2）删除表中的数据

【例14】删除borrowinf表中的读者编号为：

2002060328的读者的借书记录。

DELETEborrowinf

WHERE读者编号=‘2002060328’

【例15】删除readers表中的所有记录。

DELETEreaders

6删除表

删除一个表时，它的结构定义、数据、约束、索引都将被永久地删除。

如果一个表被其它表通过FOREIGNKEY约束引用，那么必须先删除定义FOREIGNKEY约束的表，或删除其FOREIGNKEY约束。

当没有其他表引用它时，这个表才能被删除，否则，删除操作就会失败。

比如，borrowinf表通过外键约束引用了readers表，如果尝试删除readers表，会出现警告对话框，删除操作被取消。

删除一个表可以使用企业管理器或Transact-SQL语句。

1.使用企业管理器

使用企业管理器删除一个表非常简单，只需展开【服务器组】→【服务器】→【数据库】→【表】，在右边的窗口中用鼠标右击要删除的表，在弹出的快捷菜单中选择【删除】选项，如果确定要删除该表，则在弹出的“除去对象”对话框中单击【全部除去】按钮，便完成对表的删除。

2.使用Transact-SQL语句

使用DROPTABLE语句可以删除表，其语法为：

DROPTABLEtable_name

需要注意的是DROPTABLE语句不能用来删除系统表。

【例17】删除读者类型表type。

USEStudent_01

DROPTABLEtype

刷新后：