实验数据库的建立和维护

实验 2 ：数据库的建立和维护实验

本实验需要 2 学时..

一、实验目的

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

二、实验内容和步骤

1．创建数据库

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













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

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

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

分别为姓名_数据库名和e:姓名data姓名_数据库名.mdf

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

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

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

Create database

姓名_数据库名_2

On

name=STUDENT_01_data;

filename='e:姓名data姓名_数据库名_';

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

编号 char15 CONSTRAINT PK_Books PRIMARY KEY;

书名 varchar42 NULL ;

作者varchar8 NULL ;

出版社 varchar28NULL ;

定价real NULL CONSTRAINT CK_Books CHECK定价>0

上面例子创建了一个包含5个列的books表;记录了图书的编号、书名、作者、出版社、定价等基本信息..在编号列上定义了一个主键约束;约束命名为PK_Books；列定价上定义了检查约束;命名为CK_Books;使定价的值不能是0或负数..

例2 创建图书借阅信息表Borrowinf..

CREATE TABLE borrowinf

读者编号 char10 NOT NULL REFERENCES Readers编号 ON DELETE CASCADE ;

图书编号char 15 NOT NULL

FOREIGN 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_01

ALTER TABLE Books ALTER COLUMN 出版社 varchar30 NOT NULL

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

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

· 该列上没有创建索引..

3) 添加和删除列

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

向表中添加列需要使用ALTER TABLE的ADD子句..例如;向表books中添加出版日期列;DataTime型;允许空值;可以使用如下语句:

USE Student_01

ALTER TABLE books ADD 出版日期 datetime NULL

又如向readers表中添加电子邮件地址列;且要求输入的电子邮件地址必须包含：‘@’符号;可以使用以下语句：

USE Student_01

ALTER TABLE readers ADD 邮件地址 varchar50 NULL

CONSTRAINT CK_EA CHECK邮件地址like;'%@%'

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

USE Student_01

ALTER TABLE readers DROP COLUMN邮件地址

4) 修改约束

可以使用ALTER TABLE语句向一个已经存在的表添加约束或删除约束;分别使用 ADD CONSTRAINT子句和DROP

COHSTRAINT子句..

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

USE Student_01

ALTER TABLE Readers ADD CONSTRAINT PK_BH PRIMARY KEY读者编号;图书编号;借期

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

· 不能有重复的数据..

· 不能有空值..

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

USE Student_01

ALTER TABLE Readers DROP CONSTRAINT PK_BH PRIMARY KEY读者编号;图书编号;借期

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

(1)

WITH NOCHECK子句

用包含WITH NOCHECK子句的ALTER TABLE语句;添加的约束只对在以后改变或插入的行发生作用;而不检查已存在的行..

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

USE Student_01

ALTER TABLE Books WITH NOCHECK

ADD CONSTRAINT CK_Books CHECK 定价>0

(2)

NOCHECK CONSTRAINT子句和CHECK CONSTRAINT子句

可以在ALTER TABLE语句中使用NOCHECK CONSTRAINT子句;使表的指定列上的检查约束无效;此时就可以添加一些不满足原来约束要求的数据了..使用CHECK CONSTRAINT子句可以使检查约束重新有效..这两个子句后面都要用约束名作为参数..

例;为readers表添加了邮件地址列;并定义了检查约束使邮件地址必须包含“@”符号..邮件地址列的定义如下:

ALTER TABLE readers ADD邮件地址varchar 50 NULL

CONSTRAINT CK_EA CHECK 邮件地址like'%@%'

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

USE Student_01

ALTER TABLE Readers NOCHECK CONSTRAINT CK_EA

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

USE Student_01

ALTER TABLE Readers CHECK CONSTRAINT CK_EA

4 向表中添加数据

创建表只是建立了表结构;之后;应该向表中添加数据..只有System Administrator角色成员、数据库和数据库对象所有者及其授权用户才能向表中添加数据..在添加数据时;对于不同的列数据类型;插入数据的格式不一样;因此;应严格遵守它们各自的要求..添加数据按输入顺序保存;条数不限;只受存储空间的限制..

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

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

图5-13 表Readers的数据窗口

在数据窗口中;用户可以添加多行新数据;同时还可以修改表中数据..使用该窗口的快捷菜单;可以实现表中数据各行记录间跳转、剪贴、复制和粘贴等..

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

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

语句：INSERT readers VALUES‘’;‘王晓奇’;3;2

为表readers添加一行数据..INSERT语句中无须罗列表中的列名;但VALUES提供的数据与Readers表的各列在数量、顺序及数据类型各方面要一一对应..

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

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

INSERT Readers编号;姓名;读者类型

VALUES‘’;‘李亚茜’;3

在此情况下;INSERT语句中必须罗列表中的列名;并且在添加数据前应该确认没有被添加的列上应设有默认值或NULL;否则这条INSERT语句出错..语句中的列名的顺序只要与VALUES提供的数据一一对应即可..

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

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

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

4使用INSERT…DEFAULT VALUES语句添加数据

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

INSERT INTO table_name DEFAULT VALUES

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

5 修改和删除表中的数据

1) 修改表中的数据

1 修改所有数据行的值

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

USE Student_01

UPDATE boorowinf SET 应还日期=借期+30

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

(3) 修改部分数据行的值

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

UPDATE type SET 限借阅数量=限借阅数量+5; 借阅期限=借阅期限+30

WHERE 类型名称=‘学生’

UPDATE type

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

WHERE 类型名称=‘教师’

2) 删除表中的数据

DELETE borrowinf

WHERE 读者编号=‘’

例15删除readers表中的所有记录..

DELETE readers

6 删除表

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

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

KEY约束..当没有其他表引用它时;这个表才能被删除;否则;删除操作就会失败..比如;borrowinf表通过外键约束引用了readers表;如果尝试删除readers表;会出现警告对话框;删除操作被取消..

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

1.使用企业管理器

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

2.使用Transact-SQL语句

使用DROP TABLE语句可以删除表;其语法为：

DROP TABLE table_name

需要注意的是DROP TABLE语句不能用来删除系统表..

例17删除读者类型表type..

USE Student_01

DROP TABLE type

刷新后：