数据库实验 王珊.docx
《数据库实验 王珊.docx》由会员分享，可在线阅读，更多相关《数据库实验 王珊.docx（47页珍藏版）》请在冰豆网上搜索。
数据库实验王珊
实验内容和时间安排
1时间安排
序号
实验内容
0
SQL语句练习
1
数据定义和数据更新
2
数据库的建立和维护
3
数据查询
4
SQL的视图、数据控制
5
数据库的备份和恢复
6
系统需求分析和关系数据库设计
2实验内容
实验0SQL语句练习
[实验时数]2学时
该实验以SQLServer2000系统自带的pubs数据库为例，以一个图书出版公司为模型。
（1）该系统中数据库基本表如下：
Authors：
属性名
数据类型
含义说明
可为空
检查
键/索引
au_id
Id
作者编号
否
是1
主键
au_lname
varchar（40）
作者姓
否
au_fname
varchar（20）
作者名
否
phone
char（12）
电话
否
address
varchar（40）
地址
是
city
varchar（20）
所在城市
是
state
char
（2）
所在州
是
zip
char（5）
邮编
是
是2
contract
Bit
是否签约
否
1au_idCHECK约束定义为（au_idLIKE'[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]'）。
2zipCHECK约束定义为（zipLIKE'[0-9][0-9][0-9][0-9][0-9]'）。
============================
discounts
属性名
数据类型
含义说明
可为空
检查
键/索引
discounttype
varchar（40）
折扣类型
否
stor_id
char（4）
商店编号
是
外键stores（stor_id）
lowqty
Smallint
数量下限
是
highqty
Smallint
数量上限
是
discount
Float
折扣
否
============================
Employee
属性名
数据类型
含义说明
可为空
默认值
检查
键/索引
emp_id
Empid
职工编号
否
是1
主键
fname
varchar（20）
职工名
否
minit
char
（1）
是
lname
varchar（30）
职工姓
否
job_id
Smallint
工作编号
否
1
外键jobs（job_id）
job_lvl
Tinyint
否
10
pub_id
char（4）
出版社编号
否
'9952'
外键publishers（pub_id）
Hire_date
Datetime
工作日期
否
GETDATE（）
CHECK约束定义为：
（emp_idLIKE'[A-Z][A-Z][A-Z][1-9][0-9][0-9][0-9][0-9][FM]'）OR
（emp_idLIKE'[A-Z]-[A-Z][1-9][0-9][0-9][0-9][0-9][FM]'）。
============================
Jobs
属性名
数据类型
含义说明
可为空
检查
键/索引
job_id
Smallint
工作编号
否
主键
job_desc
varchar（50）
工作描述
否
min_lvl
Tinyint
否
是1
max_lvl
Tinyint
否
是2
（1）min_lvlCHECK约束定义为（min_lvl>=10）。
（2）max_lvlCHECK约束定义为（max_lvl<=250）。
============================
pub_info
属性名
数据类型
含义说明
可为空
检查
键/索引
pub_id
char（4）
出版社编号
否
主键，外键publishers（pub_id）
logo
Image
标志图
是
pr_info
Text
出版信息
是
============================
Publishers
属性名
数据类型
含义说明
可为空
检查
键/索引
pub_id
char（4）
出版社编号
否
是1
主键
pub_name
varchar（40）
出版社名称
是
city
varchar（20）
所在城市
是
state
char
（2）
所在州
是
country
varchar（30）
所在国家
是
1pub_idCHECK约束定义为
（pub_id='1756'OR（pub_id='1622'OR（pub_id='0877'OR（pub_id='0736'OR（pub_id='1389'））））OR（pub_idLIKE'99[0-9][0-0]'）。
============================
roysched
属性名
数据类型
含义说明
可为空
检查
键/索引
title_id
Tid
书编号
否
外键titles（title_id）
lorange
Int
低
是
hirange
Int
高
是
royalty
Int
版权
是
============================
Sales
属性名
数据类型
含义说明
可为空
键/索引
stor_id
char（4）
商店编号
否
组合主键，聚集索引,外键stores（stor_id）
ord_num
varchar（20）
订单编码
否
组合主键，聚集索引
ord_date
Datetime
订购日期
否
qty
Smallint
数量
否
payterms
varchar（12）
付款方式
否
title_id
Tid
书编号
否
组合主键，聚集索引,外键titles（title_id）
============================
titles
属性名
数据类型
含义说明
可为空
检查
键/索引
title_id
Tid
书编号
否
主键
title
varchar（80）
书名
否
type
char（12）
类型
否
pub_id
char（4）
出版社编号
是
外键publishers（pub_id）
price
Money
价格
是
advance
Money
预付款
是
royalty
Int
版税
是
Ytd_sales
Int
年销售量
是
notes
varchar（200）
简介
是
pubdate
Datetime
出版日期
否
============================
Stores
属性名
数据类型
含义说明
可为空
检查
键/索引
stor_id
char（4）
商店编号
否
主键
stor_name
varchar（40）
商店名称
是
stor_address
varchar（40）
商店地址
是
city
varchar（20）
所在城市
是
state
char
（2）
所在州
是
zip
char（5）
邮编
是
============================
titleauthor
属性名
数据类型
含义说明
可为空
检查
键/索引
au_id
id
作者编号
否
组合主键，聚集索引,外键authors（au_id）
title_id
tid
书编号
否
组合主键，聚集索引,外键titles（title_id）
au_ord
tinyint
是
royaltyper
int
版权百分比
是
（2）练习内容
目的1：
1.加深对表间关系的理解。
2.理解数据库中数据的查询方法和应用。
3.学会各种查询的异同及相互之间的转换方法。
内容1：
1.查询所有作者的作者号、姓名信息
2.查询所有作者的姓名、作者号信息，并在每个作者的作者号前面显示字符串“身份证号：
”，表明显示的信息是身份证信息
3.查询在CA州的作者姓名和城市
4.查询出版日期在2002.1.1-2002.12.31之间的书名和出版日期
5.查询每个出版社出版的书
6.查询某店销售某书的数量
7.查询有销售记录的所有书信息，包括书的编号、书名、类型和价格
8.查询已销售书的信息
9.显示所有的书名（无销售记录的书也包括在内）
10.查询已销售书的信息（书号、书名、作者等）
11.查询所有出版商业（business）书籍的出版社的名称
目的2：
1.理解数据库中数据的其他查询方法和应用；
2.学会各种查询要求的实现。
内容2：
在实验1的基础上，练习查询语句的使用，包括计算列、求和、最大、最小值、各类选择条件、字符匹配、分组和排序，体会各种查询的执行过程，为简单综合应用打下良好的基础。
1.查询书名以T开头或者出版社号为0877，而且价格大于16的书的信息。
2.按照类型的升序和价格的降序（在类型相同时）显示书的信息（书名、作者、出版社、类型、价格）
3.查询销售量大于30的书名及销售数量
4.查询在2002.1.1到2002.10.31间，每本书的销售总额
5.查询所有作者的所在城市和州名，要求没有重复信息
6.计算多少种书已被订价
7.查询每本书的书名、作者及它的售书总量
8.计算所有书的平均价格
9.查询价格最高的书的书名、作者及价格
目的3：
1.加深对数据库相关性质的理解；
2.各种约束性理解；
3.学会数据库中数据的更新的方法。
内容3：
1.参照以上各表给出的主键、外键设置的设置要求，在自己创建的表中进行相应的设置。
2.向authors表中插入一行作者信息（具体值自定）
3.数量超过100的商店增加10%的折扣
4.删除2001.10.3的订单
5.删除1中所建立的索引
6.建立CA州作者所著书的视图（包括作者号、姓名、所在州、书名、价格、出版日期）
7.建立付款方式是现金（cash）的订单视图
8.建立CA州的所有商店的视图
实验一数据定义和数据更新
[实验目的]
1.熟悉SQLServer中建立数据库、数据表以及连接数据库的操作；
2.掌握SQLServer中查询分析器的工作环境，以及在其中使用SQL的基本操作。
[实验时数]2学时
[实验内容]
1.创建表、确定表的主码和约束条件，为主码创建索引。
2.查看和修改表结构。
3.熟悉SQLServer企业管理器和查询分析器工具的使用方法
[实验步骤]
1．基本操作实验
●在企业管理器中查看已经建立的数据库的属性。
●通过企业管理器，在已有的数据库中建立图书、读者和借阅3个表，其结构为：
Ø图书（书号，类别，出版社，作者，书名，定价，作者）；
Ø读者（编号，姓名，单位，性别，电话）；
Ø借阅（书号，读者编号，借阅日期）。
要求为属性选择合适的数据类型，定义每个表的主码，是否允许空值和默认值等列级数据约束。
●在企业管理器中建立图书、读者和借阅3个表的表级约束：
每个表的主码约束；借阅表与图书表间、借阅表与读者表之间的外码约束，要求按语义先确定外码约束表达式，再通过操作予以实现；实现借阅表的书号和读者编号的惟一性约束；实现读者性别只能是“男”或“女”的Check（检查）约束。
2．提高操作实验
●用SQL建立学生—课程库操作，在SQLServer企业管理器中实现。
表结构为：
Ø学生（学号，姓名，年龄，性别，所在系）；
Ø课程（课程号，课程名，先行课）；
Ø选课（学号，课程号，成绩）。
要求：
Ø建表和建立表间联系。
Ø选择合适的数据类型。
Ø定义必要的索引、列级约束和表级约束。
●将设计的数据库应用系统中的数据库、库中的表、索引和约束用Transact-SQL表达，并通过企业管理器或查询分析器实现建库、建表、建立表间联系和建立必要的索引、列级约束和表级约束的操作。
[实验方法]
1．创建数据库
（因为各个用户已经建立以自己学号为名称的数据库，该步可以省略。
以下的数据库一律指该数据库，用户不必再次重复创建。
）
（1）使用企业管理器创建数据库的步骤
1）进入SQLServer2000企业管理器。
2）选中需要在其上创建数据库的服务器，单击前面的“+”号，使其展示为树形目录。
3）选中“数据库”文件夹，单击右键，选择“新建数据库”，如图5-1所示。
随后在数据库属性对话框的常规页面中，输人数据库名，选择SQL服务器，如图5-2所示。
图5-1在数据库的弹出菜单中选择新建数据库图5-2数据库属性对话框
数据库属性对话框中有3个页面：
常规页面、数据文件页面和事务日志页面。
数据文件页面和事务日志页面主要用来定义数据库的数据文件和日志文件的属性。
4）选择数据文件页面，输入图书----读者数据库的数据文件属性，包括文件名、存放位置和文件属性，如图5-3所示；再选择事务日志页面，输人数据库的日志文件属性，包括文件名、存放位置、大小和文件属性，如图5-4所示。
在选择文件位置时，可以单击位于“位置”列的“…”按键，在调出的文件选择器中进行位置选择。
5）单击[确定]按钮，关闭对话框。
在企业管理器窗口中出现“图书----读者”数据库标志，这表明建库工作已经完成。
（2）文件属性参数说明
文件属性栏设在页面的下部，它包括下列选项：
图5-3数据库属性对话框中的数据文件页面图5-4数据库属性对话框中的事务日志页面
1）文件自动增长复选框：
选中后允许文件放满数据时自动增长。
2）文件增长单选框：
设置允许文件自动增长时，每次文件增长的大小。
其中，选“按兆字节”项为设置文件增长为固定的大小，单位是MB；选“按百分比”项为文件按指定比例数增长，单位是％。
3）最大文件大小单选框：
设置当允许文件扩展时，数据文件能够增长的最大值。
选“文件增长不受限制”项可使文件无限增长，直到用完磁盘空间；选“将文件增长限制为”项时，要设置文件最多达到的固定值。
2．查看和修改数据库属性参数
已经建好的数据库，有时还需要对它的属性参数进行查看和修改。
下面我们分两步介绍：
先介绍查看和修改数据库属性的步骤，再介绍数据库有关的参数及其含义。
（1）查看和修改数据库属性的步骤
1）启动企业管理器，使数据库所在的服务器展开为树形目录。
2）选中数据库文件夹，使之展开；用鼠标右键单击指定的数据库标识，在弹出的菜单中选择“属性”项，如图5-5所示。
出现数据库属性对话框，如图5-6所示。
图5-5在企业管理器中选择数据库的“属性”项图5-6数据库属性对话框中的选项页面
从该对话框中可以看出，它由6个选项卡构成，与图5-2不同的是增加了文件组、选项和权限页面。
其中：
文件组页面用于设置数据库的文件组，其概念在前面已经介绍过；权限页面用来设置用户对该数据库的访问权限，有关选项页面内容在下面介绍。
3）在选项卡中查看或修改相应的内容，单击[确定]按钮关闭对话框。
（2）选项页面中的数据库属性参数
“选项”页面如图5-6所示。
“选项”页面分访问、故障还原、设置和兼容性4个栏目。
1）访问栏目。
访问栏用来设置数据库的操作许可。
限制访问复选框：
选择后，限制用户访问数据库。
dbowner，dbcreater或sysadmin成员：
只能由数据库拥有者（创建者）使用数据库。
单用户：
在同一时刻是否只允许一个用户使用该数据库。
只读：
该数据库是否是只读的。
2）故障还原栏目。
设置故障还原模型。
3）设置栏目。
该栏目用来设置数据库在进行数据操作时的行为特征，它包括8个复选
框，其含义比较容易理解。
3．删除数据库
对于不需要的数据库，可以通过下面的方法删除：
1）用鼠标右键单击要删除的数据库，在出现的弹出菜单中选择删除项。
2）在弹出的确认删除对话框中，单击[确认]按钮。
4．新建表
在SQLServer2000的数据库中，文件夹是按数据库对象的类型建立的，文件夹名是该数据库对象名。
当在企业管理器中选择服务器和数据库文件夹，并打开已定义好的数据库后，会发现它自动设置了关系图、表、视图、存储过程、用户、角色、规则、默认等文件夹。
要建立“图书”表，先选中数据库中的表文件夹，单击鼠标右健。
在弹出的菜单中选择新建表，如图5-7所示。
随后的输入表结构对话框如图5-8所示。
图5-7数据库中的表文件夹的弹出菜单图5-8输入表结构对话框
输入表结构对话框是一张表，它的列属性有列名、数据类型、长度和是否允许空4项。
用户把新建表的结构填入对话框的表中，表中的每一行定义新建表（图书）的一列，每一列定义新建表的一个列属性。
当光标移到表中的某一行时，下面的列描述就会对应当前行显示输入项，用户可在其中对关系的属性进行进一步说明。
列描述包括数据的精度、小数位数、默认值、是否标识等项。
输入表结构时应注意以下几点：
1）“列名”列用于输入字段名，例如“编号”、“类别”等，列名类似于变量名，其命名规格与变量一致。
列名中不允许出现空格，一张表也不允许有重复的列名。
2）“数据类型”列中的数据类型是通过选择方法，而不是直接键人数据类型字符输入的。
当鼠标指针移向该列时，就会出现控制键，单击后就出现数据类型弹出框，如图3-8所示，可选择其中之一为指定的数据类型。
3）“长度”列、精度和小数位数项不是所有字段都必选的。
例如int和datetime型的长度是固定的，也不需要数据精度值。
数据精度仅对一些数值型、字符型、货币型等数据有效，小数位仅对一些数值型数据有效。
4）“允许空”列用于设置是否允许字段为空值，默认项用于设置字段的默认值。
5）标识、标识种子和标识递增量用于设置字段具有的新生行递增性、初始值以及步长，以便让SQLServer2000自动填写该列的值。
具有标识性能的字段的数据类型只能为int，Smallint，tinyint,decimal（p，0）或numeric（p，0），而且不允许为空值。
一个表只允许有一列具有标识性能。
6）列名前的一列按钮为字段标注按钮列。
钥匙图标说明这个字段为主码，黑三角图标说明所指示行为当前字段。
7）在对话框中单击鼠标右键，则会出现一个弹出框，如图5-9所示。
其中的几个选项非常有用：
选择“设置主键”项，则定义当前字段为主码，表中第一列处会显示钥匙图案；选择“插入列”项，则在当前字段处插入一个新行；选择“删除列”项，则删除当前字段；选择“属性”项，可调出表属性对话框，如图5-10所示，可以在其中定义索引／键、与其他表间的关联和约束等属性，具体方法在下面介绍。
图5-9建表对话框中的弹出框图5-10表属性对话框
8）字段输入完后，就可以关闭建表对话框了。
最后，会弹出输入表名对话框，如图5-11所示。
在对话框中输入“图书”表名，单击[确定]按钮后，建表工作就完成了。
图5-11输入表名对话框
5．定义表的完整性约束和索引
表的约束包括码（主键）约束、外码约束（关联或关系约束）、唯一性约束、Check（检查）约束4种。
这些约束可以在表属性对话框中定义。
（1）定义索引和键
选择“索引／键”页面，其界面如图3-10所示。
1）查看、修改或删除索引时，先要在“选定的索引”下拉菜单中选择索引名，其索引内容就显示在表中。
需要时，可以直接在表中修改索引内容，如改变索引列名，改变排序方法等。
对于不需要的索引可以单击[删除]按钮，直接删除此索引。
2）新建一个索引时，单击[新建l按钮，并在下面的表中输入索引名、索引列名及排列顺序。
3）设置UNIQUE复选框，确定是否为唯一索引约束。
设置CLUSTERED复选框，确定是否为群集索引（CLUSTERED）。
（2）定义表间关联
选择表页面，其界面如图5-12所示。
1）查看、修改或删除表关联时，先要在“选定的关系”下拉菜单中选择关联名（即关系名），其关联内容就显示在表中。
需要时，可以直接在表中修改关联内容，例如改变主键，改变外码键等。
对于不需要的关联可以单击[删除]按钮，直接删除此关联。
2）新建一个关联时，单击[新建]按钮，选择库中的关联表（参照表）后，在表中输入关联名、主码和外码。
3）设置“创建中检查现存数据”复选框，确定新建关联时是否对数据进行检查，要求符合外码约束；设置“对复制强制关系”复选框，确定在进行数据复制时是否要符合外码约束；设置“对INSERT和UPDATE强制关系”复选框，确认在对数据插入和更新时，是否符合外码约束；设置“级联更新相关的字段”复选框和“级联删除相关的记录”复选框，确认被参照关系的主码值被修改时，是否也将参照表中的对应的外码值修改，而被参照关系的主码值被删除时，是否也将参照表中对应外码的记录删除。
（3）定义CHECK约束
选择CHECK约束页面，其界面如图5-13所示。
图5-12表属性对话框中的表关联页面 图5-13表属性对话框中的约束页面
1）查看、修改或删除CHECK约束时，先要在“选定的约束”下拉菜单中选择约束名，其约束内容就显示在约束表达式框中。
需要时，可以直接在框中修改约束表达式。
对于不需要的CHECK约束可以单击[删除]按钮，直接删除此约束。
2）新建一个CHECK约束时，单击[新建]按钮，并在表中输入约束名和约束表达式。
3）设置“创建中检查现存数据”，确认在创建约束时是否对表中数据进行检查，要求符合约束要求；设置“对复制强制约束”复合框，确认对数据复制时是否要求符合约束条件；设置“对INSERT~UPDATE强制约束”，确认在进行数据插入和数据修改时，是否要求符合约束条件。
图5-14基本表的弹出菜单
6．修改表结构
当需要对建好的表修改结构时，首先要在企业管理器中找到该表，用鼠标右键单击该表名，就会出现弹出菜单，如图5-14所示。
随后，在弹出的菜单中选择“设计表”项，企业管理器会把如图3-9所示的建表对话框调出，用户可对原有内容进行修改。
[实验报告要求]
1．分别用SQL和Transact-SQL表示图书读者数据
升级会员 