《数据库应用与设计》实验报告
实验1~实验9
学院管理学院
专业电子商务
年级班别2013级1班
学号3113004762
学生姓名吴孙德
指导教师胡凤
成绩
目录
实验1 建立数据库 (1)
实验2 建立表和定义完整性约束 (6)
实验3 数据库操作和完整性约束的作用 (10)
实验4 数据的查询 (19)
实验5 视图的应用 (42)
实验6 游标 (51)
实验7 存储过程 (53)
实验8 触发器 (56)
实验9 用户管理和权限管理 (59)
实验1 建立数据库
实验名称：建立数据库
实验内容：在SQL Server环境下建立数据库和维护数据库。
实验目的：理解SQL Server数据库的存储结构，掌握SQL Server数据库的建立方法和维护方法。
实验方法：在SQL Server环境下用CREATE DATABASE命令建立数据库（注意：需要是系统管理员用户，或者被指定dbcreator固定服务器角色的成员，或者被显式地授予CREATE DATABASE权限，有关权限的内容请课文5.2节）
实验要求：
1、根据上体要求创建如下3个数据库：
(1)创建数据库test1（使用一个数据文件和一个日志文件），要求指定一个数据文件、
一个事务日志文件，并把它们安排在不同的硬盘上。
(2)创建数据库test2（使用多个数据文件和多个日志文件），要求使用3个数据文件和
两个事务日志文件，主数据文件是列表中的第一个文件或者用关键字PRIMARY指定（文件扩展名：主数据文件使用.mdf，次数据文件使用.ndf，事务日志文件使用.ldf）。
(3)创建数据库test3（使用文件组），要求使用3个文件组（含主文件组，默认的文件组），每个文件组包含两个数据文件，并且每个文件组要安排在不同的硬盘上。
2、修改建立的test1数据库，为之添加一个5MB大小的新数据文件。
3、修改建立的test2数据库，将其中的主数据文件增加5MB的存储空间。
4、删除建立的数据库test1。
实验操作:
1、根据上体要求创建如下3个数据库：
(1) 创建数据库test1（使用一个数据文件和一个日志文件），要求指定一个数据文件、
一个事务日志文件，并把它们安排在不同的硬盘上。
create database test1on
(name=test,filename='e:\test.mdf',size=10,maxsize=50,filegrowth=5) log on
(name=test_log,filename='f:\test.ldf',size=5mb,maxsize=25mb,filegrowt h=5mb)
(2) 创建数据库test2（使用多个数据文件和多个日志文件），要求使用3个数据文件和
两个事务日志文件，主数据文件是列表中的第一个文件或者用关键字PRIMARY指定（文件扩展名：主数据文件使用.mdf，次数据文件使用.ndf，事务日志文件使用.ldf）。
create database test2on
primary
(name=t1,filename='e:\a1.mdf',size=10,maxsize=50,filegrowth=5)
,(name=t2,filename='e:\a2.ndf',size=10,maxsize=50,filegrowth=5)
,(name=t3,filename='e:\a3.ndf',size=10,maxsize=50,filegrowth=5)
log on
(name=t1_log,filename='e:\a1.ldf',size=5mb,maxsize=25mb,filegrowth=5m
b),
(name=t2_log,filename='e:\a2.ldf',size=5mb,maxsize=25mb,filegrowth=5m b)
(3) 创建数据库test3（使用文件组），要求使用3个文件组（含主文件组，默认的文件组），每个文件组包含两个数据文件，并且每个文件组要安排在不同的硬盘上。
create database test3on
primary
(name=te0,filename='f:\b0.mdf',size=10,maxsize=50,filegrowth=5)
,filegroup fg1
(name=te1,filename='f:\b1.mdf',size=10,maxsize=50,filegrowth=5)
,(name=te2,filename='f:\b2.ndf',size=10,maxsize=50,filegrowth=5)
,filegroup fg2
(name=te3,filename='f:\b3.ndf',size=10,maxsize=50,filegrowth=5)
,(name=te4,filename='f:\b4.ndf',size=10,maxsize=50,filegrowth=5)
,filegroup fg3
(name=te5,filename='f:\b5.ndf',size=10,maxsize=50,filegrowth=5)
,(name=te6,filename='f:\b6.ndf',size=10,maxsize=50,filegrowth=5)
2、修改建立的test1数据库，为之添加一个5MB大小的新数据文件。
alter database test1
add file
(name=test1,filename='f:\test1.ndf',size=5mb,maxsize=50,filegrowth=5)
3、修改建立的test2数据库，将其中的主数据文件增加5MB的存储空间。
alter database test2
modify file
(name=t1,filename='e:\a1.mdf',size=15,maxsize=55,filegrowth=10)
4、删除建立的数据库test1。
drop database test1
实验2建立表和定义完整性约束
实验名称：建立模式、表和定义完整性约束
实验内容：在实验一建立的数据库的基础上，参照下图和下表建立表并定义完整性约束。
表名字段名字段类型关键字约束说明
实验目的：熟练掌握表的建立和数据完整性速描定义方法，实践DBMS提供的数据完整性功能，加深对数据完整性的理解。
实验方法：在实验一建立数据库的基础上用CREATE SCHEMA命令建立模式，用CREATE TABLE 命令建立表并定义数据完整性约束（注意：需要有CREATE SCHEMA和CREATE TABLE的权限，或者其他具有相应权限的身份，有关权限的内容请参见课文5.2节），用ALTER TABLE命令修改表结构。
实验要求：
1、用CREATE SCHEMA命令建立模式（自己命名模式名称）。
create database实验
create schema wusunde
2、在定义的模式下参照图1和表1使用CREATE TABLE命令建立表并定义完整性约束。create table wusunde.仓库
(仓库号char(6)primary key check(仓库号like
'[A-Z][A-Z][0-9][0-9][0-9][0-9]'),
城市char(10)unique not null,
面积int check(面积>=50)
)
create table wusunde.职工
(职工号char(8)primary key check(职工号like
'[A-Z][A-Z][0-9][0-9][0-9][0-9][0-9][0-9]'),
仓库号char(6)constraint ck foreign key references wusunde.仓库(仓库号), 工资money check(工资>=1000 and工资<=10000)
)
create table wusunde.供应商
(供应商号char(4)primary key check(供应商号like'[S][0-9][0-9][0-9]'), 供应商名char(16),
地址char(30)
)
create table wusunde.订购单
(职工号char(8)not null foreign key references wusunde.职工(职工号),
供应商号char(4)null constraint supply foreign key references wusunde.供应商(供应商号)on delete set null,
订购单号char(6)primary key check(订购单号like
'OR[0-9][0-9][0-9][0-9]'),
订购日期datetime default getdate(),
金额money default null
)
create table wusunde.订购单明细
(订购单号char(6)not null foreign key references wusunde.订购单(订购单号),
序号char(2),
产品名称char(20),
单价money default null check(单价>0),
数量int not null check(数量>0),
primary key(订购单号,序号)
)
3、使用ALTER TABLE命令按如下要求修改表结构：
(1) 为订购单增加一个新的字段“完成日期”，类型为日期时间型，默认是空值。alter table wusunde.订购单
add完成日期datetime default null
(2) 为订单明细表的“数量”字段重新定义约束：大于0、小于等于1000的正整数，不允许为空值。
alter table wusunde.订购单明细
add check(数量between 0 and 1000)
alter table wusunde.订购单明细
alter column数量int not null
(3) 将供应商表的供应商名字段的类型修改为varchar(30)。
alter table wusunde.供应商
alter column供应商名varchar(30)
实验3数据操作和完整性约束的作用
实验名称：数据操作笔完整性约束的作用
实验内容：在实验二的基础上完成数据的插入操作，然后进行部分修改和删除操作，在这些操作中体会数据完整性约束的作用。
实验目的：熟练掌握SQL的INSERT，UPDATE和DELETE命令，深刻理解数据完整性约束的作用以及约束时机。
实验方法：在实验二的基础上首先用INSERT命令插入各个表的记录，然后使用UPDATE和DELETE
命令对部分记录进行修改和删除操作。
实验要求：
1、自行设计各个表的记录，其中仓库表至少5条记录，职工表至少10条记录、供应商表
至少5条记录，订购单表至少20条记录，订购单明细表至少50条记录。
2、使用INSERT命令完成记录的插入操作，要求分别设计若干违反实体完整性、参照完整
性和用户定义完整性约束的插入操作，并分析原因。
3、设计若干删除操作，体会执行删除操作时检查参照完整性规则的效果（要求涉及拒绝删除、空值删除和级联删除等不同的处理方式）。
4、设计若干更新操作，体会执行更新操作时检查实体完整性规则、参照完整性规则和用户
定义完整性规则的效果。
5、将x城市仓库的职工的工资提高10%。
6、用UPDATE语句完成订购单表金额字段的计算（提示：某张订购单的金额为该订购单所
有订购项目（订购单明细）单价乘以数量的总和）。
7、设计2~3个删除操作，它需要另外一个表中的数据作为删除条件（例如，按指定的供应
商名称删除订购单记录）。
8、在实验报告中给出具体的记录和设计的操作，并针对各种数据完整性检查，给出具体的
分析和讨论。
实验操作：
1、分别设计仓库表5条记录，职工表10条记录、供应商表5条记录，订购单表20条记录，订购单明细表50条记录。
insert into wusunde.仓库values('QW0001','广州',500)
insert into wusunde.仓库values('QW0002','上海',900)
insert into wusunde.仓库values('QW0003','北京',250)
insert into wusunde.仓库values('QW0004','纽约',1000)
insert into wusunde.仓库values('QW0005','深圳',650)
insert into wusunde.职工values('ZG000001','QW0005',3000)
insert into wusunde.职工values('ZG000002','QW0001',5600)
insert into wusunde.职工values('ZG000003','QW0004',6600)
insert into wusunde.职工values('ZG000004','QW0002',3800)
insert into wusunde.职工values('ZG000005','QW0005',3900)
insert into wusunde.职工values('ZG000006','QW0001',4600)
insert into wusunde.职工values('ZG000007','QW0003',4000)
insert into wusunde.职工values('ZG000008','QW0003',5000)
insert into wusunde.职工values('ZG000009','QW0002',4800)
insert into wusunde.职工values('ZG000010','QW0004',6000)
insert into wusunde.供应商values('S001','华广','深圳')
insert into wusunde.供应商values('S002','广工','广州')
insert into wusunde.供应商values('S003','美的','茂名')
insert into wusunde.供应商values('S004','乐视','深圳')
insert into wusunde.供应商values('S005','康佳','广州')
insert into wusunde.订购单
values('ZG000007','S005','OR0001','2015-1-6',5000,'2015-2-5') insert into wusunde.订购单
values('ZG000002','S001','OR0002','2015-2-3',10000,'2015-3-4') insert into wusunde.订购单
values('ZG000003','S004','OR0003','2015-2-17',3000,'2015-5-16') insert into wusunde.订购单
values('ZG000006','S002','OR0004','2015-2-16',9000,'2015-4-18') insert into wusunde.订购单
values('ZG000010','S005','OR0005','2015-3-20',8000,'2015-6-27') insert into wusunde.订购单
values('ZG000001','S001','OR0006','2015-3-22',7500,'2015-7-29') insert into wusunde.订购单
values('ZG000006','S003','OR0021','2015-2-15',4600,'2015-10-13') insert into wusunde.订购单
values('ZG000004','S002','OR0007','2015-2-23',8500,'2015-8-29') insert into wusunde.订购单
values('ZG000003','S005','OR0008','2015-1-5',3600,'2015-11-11') insert into wusunde.订购单
values('ZG000001','S004','OR0009','2015-1-31',7600,'2015-12-14') insert into wusunde.订购单
values('ZG000007','S001','OR0010','2015-3-25',12000,'2015-7-30') insert into wusunde.订购单
values('ZG000008','S002','OR0011','2015-2-26',4600,'2015-9-27') insert into wusunde.订购单
values('ZG000010','S002','OR0012','2015-1-28',3400,'2015-11-21') insert into wusunde.订购单
values('ZG000009','S004','OR0013','2015-3-19',2300,'2015-10-24') insert into wusunde.订购单
values('ZG000007','S005','OR0014','2015-2-23',9000,'2015-9-21') insert into wusunde.订购单
values('ZG000008','S003','OR0015','2015-2-27',6400,'2015-12-23') insert into wusunde.订购单
values('ZG000002','S001','OR0016','2015-1-30',7900,'2015-6-30') insert into wusunde.订购单
values('ZG000005','S004','OR0017','2015-1-27',20000,'2015-8-18') insert into wusunde.订购单
values('ZG000009','S004','OR0018','2015-3-5',13000,'2015-7-31') insert into wusunde.订购单
values('ZG000004','S002','OR0019','2015-3-14',7000,'2015-6-17') insert into wusunde.订购单
values('ZG000006','S005','OR0020','2015-2-21',4300,'2015-5-25')
insert into wusunde.订购单明细values('OR0020','01','沐浴露',32,240) insert into wusunde.订购单明细values('OR0001','02','洗衣粉',30,125)
insert into wusunde.订购单明细values('OR0014','04','香皂',14,99)
insert into wusunde.订购单明细values('OR0004','05','盐',2,169)
insert into wusunde.订购单明细values('OR0005','06','酱油',14,65)
insert into wusunde.订购单明细values('OR0011','07','化妆品',26,24) insert into wusunde.订购单明细values('OR0019','08','洗面奶',39,68) insert into wusunde.订购单明细values('OR0008','09','面膜',5,147)
insert into wusunde.订购单明细values('OR0003','10','花生油',36,258) insert into wusunde.订购单明细values('OR0017','11','鼠标',69,72)
insert into wusunde.订购单明细values('OR0015','12','鼠标垫',18,64) insert into wusunde.订购单明细values('OR0016','13','移动电源',58,69) insert into wusunde.订购单明细values('OR0012','14','插座',37,169) insert into wusunde.订购单明细values('OR0011','15','牛奶',72,111) insert into wusunde.订购单明细values('OR0020','16','洗洁精',9,114) insert into wusunde.订购单明细values('OR0019','17','洁厕液',6,300) insert into wusunde.订购单明细values('OR0010','18','鸡蛋',14,124) insert into wusunde.订购单明细values('OR0009','19','牛肉',38,300) insert into wusunde.订购单明细values('OR0016','20','内裤',59,654) insert into wusunde.订购单明细values('OR0018','21','鞋子',299,300) insert into wusunde.订购单明细values('OR0015','22','手表',890,34) insert into wusunde.订购单明细values('OR0005','23','钱包',129,124) insert into wusunde.订购单明细values('OR0008','24','行李箱',344,64) insert into wusunde.订购单明细values('OR0014','25','手袋',3,874)
insert into wusunde.订购单明细values('OR0018','26','篮球',360,59) insert into wusunde.订购单明细values('OR0003','27','足球',260,36) insert into wusunde.订购单明细values('OR0005','28','羽毛球拍',78,69) insert into wusunde.订购单明细values('OR0007','29','乒乓球拍',89,145) insert into wusunde.订购单明细values('OR0020','30','羽毛球',5,645) insert into wusunde.订购单明细values('OR0013','31','乒乓球',3,542) insert into wusunde.订购单明细values('OR0016','32','牙膏',23,200) insert into wusunde.订购单明细values('OR0009','33','牙刷',6,456)
insert into wusunde.订购单明细values('OR0019','34','防晒霜',56,65) insert into wusunde.订购单明细values('OR0017','35','水杯',39,1187) insert into wusunde.订购单明细values('OR0010','36','拖把',15,187) insert into wusunde.订购单明细values('OR0004','37','扫把',6,200)
insert into wusunde.订购单明细values('OR0005','38','垃圾桶',16,254) insert into wusunde.订购单明细values('OR0006','39','书',46,688)
insert into wusunde.订购单明细values('OR0016','40','卫生纸',13,500) insert into wusunde.订购单明细values('OR0020','41','相机',3698,32) insert into wusunde.订购单明细values('OR0013','42','手机',2000,200) insert into wusunde.订购单明细values('OR0017','43','音响',500,143) insert into wusunde.订购单明细values('OR0001','44','吸尘器',1688,40) insert into wusunde.订购单明细values('OR0012','45','油烟机',3500,10) insert into wusunde.订购单明细values('OR0010','46','台式电脑',4000,25)
insert into wusunde.订购单明细values('OR0005','48','电视机',1300,100) insert into wusunde.订购单明细values('OR0016','49','洗衣机',3400,15) insert into wusunde.订购单明细values('OR0011','50','笔记本',6000,20)
2、使用INSERT命令完成记录的插入操作，要求分别设计若干违反实体完整性、参照完整
性和用户定义完整性约束的插入操作，并分析原因。
1）违反实体完整性
insert into wusunde.仓库values('QW0001','惠州',600)
原因：违反了PRIMARY KEY 约束'PK__仓库__530C599C7F60ED59'。不能在对象
'wusunde.仓库' 中插入重复键。语句已终止。
2）违反参照完整性
insert into wusunde.职工values('ZG000011','QW0009',3000)
原因：仓库号列参照仓库表的仓库号，此时在仓库表中找不到仓库号为QW0009的仓库。INSERT 语句与FOREIGN KEY 约束"FK__职工__仓库号__0AD2A005"冲突。该冲突发生于数据库"实验"，表"wusunde.仓库", column '仓库号'。
3）违反用户定义完整性
insert into wusunde.仓库values('QW0006','天津',30)
原因：在定义时，面积要大于等于50，这里面积为30，明显违反了用户定义完整性
3、设计若干删除操作，体会执行删除操作时检查参照完整性规则的效果（要求涉及拒绝删除、空值删除和级联删除等不同的处理方式）。
1）拒绝删除
delete wusunde.仓库where城市='北京'
删除操作失败。因为有职工参照了干记录
2）空值删除
delete wusunde.供应商where供应商号='S002'
因为删除参照完整性规则定义为set null
3)级联删除
alter table wusunde.职工
drop constraint ck
alter table wusunde.职工
drop column仓库号
alter table wusunde.职工
add仓库号char(6)constraint ck foreign key references wusunde.仓库
on delete cascade
on update cascade
4、设计若干更新操作，体会执行更新操作时检查实体完整性规则、参照完整性规则和用户定义完整性规则的效果。
1）违反参照完整性
update wusunde.职工set仓库号='QW0006'where职工号='ZG000002'
更新操作失败，因为职工表所参考的仓库表中不存在仓库号为QW0006的仓库。
2）违反实体完整性
update wusunde.仓库set仓库号=null where仓库号='QW0001'
更新操作失败，因为仓库号是关键字，不能为空。
3)更新违反自定义完整性
update wusunde.仓库set面积=30 where仓库号='QW0001'
更新操作失败，因为面积被定义约束大于或等于50，此处面积=30，显然违反了约束。
5、将x城市仓库的职工的工资提高10%。
update wusunde.职工set工资=(工资+(工资*0.1))
6、用UPDATE语句完成订购单表金额字段的计算（提示：某张订购单的金额为该订购单所
有订购项目（订购单明细）单价乘以数量的总和）。
update wusunde.订购单set金额=(select SUM(单价*数量)from wusunde.订购单明细
where订购单明细.订购单号=订购单.订购单号)
7、设计2~3个删除操作，它需要另外一个表中的数据作为删除条件（例如，按指定的供应
商名称删除订购单记录）
1）delete wusunde.职工where仓库号in(select仓库号from wusunde.仓库where城市='北京')
2）delete wusunde.订购单where供应商号in(select供应商号from wusunde.供应商where供应商名='广工')