国脉信息学院数据库综合实验.docx
《国脉信息学院数据库综合实验.docx》由会员分享，可在线阅读，更多相关《国脉信息学院数据库综合实验.docx（26页珍藏版）》请在冰豆网上搜索。
国脉信息学院数据库综合实验
综合实验
一、创建图书管理库（BookSys）
该数据库的主数据文件逻辑名称为：
“图书管理库”，物理文件名为：
图书管理库．Mdf，物理文件路径为D：
\，初始大小为3MB，最大容量为100MB，增长速度为10%；数据库的日志文件逻辑名称为“图书管理库_log”，物理文件名为：
图书管理库_log.ldf，物理文件路径为D：
\，初始大小为1MB，最大容量为2MB，增长速度为10%）
CREATEDATABASE图书管理
ONPRIMARY
（NAME='图书管理',
FILENAME='D:
\图书管理.mdf',
SIZE=3MB,
MAXSIZE=100MB,
FILEGROWTH=10%
）
LOGON
（NAME='图书管理_log',
filename='D:
\图书管理_log.ldf',
size=1MB,
MAXSIZE=2MB,
FILEGROWTH=10%
）
二、在图书管理库（BookSys）中建立如下的表：
1、图书信息表（tsxx）
字段名称
数据类型
可否为空
说明
图书编号
char（8）
否
主键
书名
char（20）
否
价格
money
可
出版社
char（20）
可
出版日期
datetime
可
作者
char（8）
可
并插入如下数据：
createtable图书信息表
（图书编号CHAR（8）NOTNULLPRIMARYKEY,
书名CHAR（20）NOTNULL,
价格MONEY,
出版社CHAR（20）,
出版日期DATETIME,
作者CHAR（8）
）
insertinto图书信息表
values（1,'高等数学',20,'高等教育出版社','2001-1-5','李青'）
insertinto图书信息表
values（2,'C语言程序设计',30,'高等教育出版社','2004-1-5','唐浩强'）
insertinto图书信息表
values（3,'SQLserver原理',28,'中国水利出版社','2008-1-5','李青'）
insertinto图书信息表
values（4,'数据结构',37,'中国水利出版社','2011-1-5','李青'）
insertinto图书信息表
values（5,'操作系统',29,'中国水利出版社','2009-1-5','刘红'）
insertinto图书信息表
values（6,'离散数学',18,'清华大学出版社','2006-1-5','唐浩强'）
insertinto图书信息表
values（7,'微机原理',45,'清华大学出版社','2007-1-5','林林'）
2、读者信息（dzxx）
字段名称
数据类型
可否为空
说明
读者编号
char（8）
否
主键
姓名
char（20）
否
身份证号
char（18）
可
级别
char（8）
可
并插入如下数据：
createtable读者信息
（
读者编号CHAR（8）NOTNULLPRIMARYKEY,
姓名CHAR（20）NOTNULL,
身份证号CHAR（18）,
级别CHAR（8））
insertinto读者信息
values（1,'张三','350322198408030014',1）
insertinto读者信息
values（2,'李子','350322198408030014',1）
insertinto读者信息
values（3,'王五','350322198408030014',2）
insertinto读者信息
values（4,'赵三','350322198408030014',3）
insertinto读者信息
values（5,'林刘','350322198408030014',2）
insertinto读者信息
values（6,'陈东','350322198408030014',3）
insertinto读者信息
values（7,'郑一','350322198408030014',1）
3、借阅信息（jyxx）
字段名称
数据类型
可否为空
说明
读者编号
char（8）
否
主键
外键
图书编号
char（8）
否
外键
借阅日期
datetime
可
还书日期
datetime
可
是否续借
char（4）
可
并插入如下数据：
createtable借阅信息
（读者编号CHAR（8）NOTNULLPRIMARYKEY,
图书编号CHAR（8）NOTNULL,
借阅日期DATETIME,
还书日期DATETIME,
是否续借CHAR（4）,
FOREIGNKEY（图书编号）REFERENCES图书信息表（图书编号）,
FOREIGNKEY（读者编号）REFERENCES读者信息（读者编号））
insertinto借阅信息
values（1,3,'2010-12-2','2011-12-1','可以'）
insertinto借阅信息
values（1,4,'2010-12-2','2011-12-1','可以'）
insertinto借阅信息
values（2,3,'2011-2-2','2011-10-1','可以'）
insertinto借阅信息
values（5,7,'2011-2-2','2011-9-1','可以'）
insertinto借阅信息
values（7,3,'2011-1-2','2011-2-1','可以'）
insertinto借阅信息
values（4,2,'2010-12-20','2011-12-1','可以'）
values（4,1,'2011-1-12','2011-2-1','可以'）
三、完成如下操作：
1、修改列“出版社”的定义，长度修改成30
altertable图书信息表
altercolumn出版社char（30）
2、修改读者信息表中编号为1的读者的级别为2级
UPDATE读者信息
SET级别=2
WHERE读者编号='1'
四、完成如下查询语句：
1、查询图书馆中所有的图书、出版社、读者信息\
SELECT读者信息.*,出版社,书名
FROM读者信息,图书信息表
2、查询前3项读者借阅图书的信息
SELECT读者编号,图书编号,借阅日期
FROM借阅信息
SELECT借阅信息.*
FROM借阅信息
WHERE读者编号in（'1','2','3'）
3、查询前3%项读者借阅图书的信息
4、查询所有借书的读者编号，要求取消重复行
SELECTDISTINCT读者编号
FROM借阅信息
5、查询图书价格打8折后的图书名称、原价和折后价格，分别以“图书名称”、“原价”、“折后价格”为列名显示
6、查询价格大于等于20元的图书信息
SELECT图书信息表.*
FROM图书信息表
WHERE价格>=20
7、查询价格在20~40元之间的图书信息
SELECT图书信息表.*
FROM图书信息表
WHERE价格BETWEEN20AND40
8、查询由“中国水利出版社”、“高等教育出版社”、“清华大学出版社”出版的所有图书
SELECT书名
FROM图书信息表
WHERE出版社IN（'中国水利出版社','高等教育出版社','清华大学出版社'）
9、查询姓“张”的读者的信息
SELECT读者信息.*
FROM读者信息
WHERE姓名liKE'张%'
10、计算图书馆图书的总价格、平均价格
SELECTSUM（价格）,AVG（价格）
FROM图书信息表
11、计算机出自“中国水利出版社”的图书数量
SELECTCOUNT（图书编号）
FROM图书信息表
WHERE出版社='中国水利出版社'
12、按读者级别由高到低输出读者信息
SELECT读者信息.*
FROM读者信息
ORDERBY级别DESC
13、查询图书价格大于图书平均价格的所有图书信息
select图书编号,书名,出版社,价格
from图书信息表
where价格>
（
selectavg（价格）as平均价
from图书信息表
）
14、查询“李青”曾出版过书的出版社还出版了哪些书
select出版社作者
from图书信息表
where作者='李青'
select图书编号,书名,作者,出版社,价格
from图书信息表
where出版社in（'中国水利出版社','高等教育出版社'）
15、查询价格大于“中国水利出版社”出版的任意书的价格的图书信息
select图书编号,书名,出版社,价格
from图书信息表
where价格>
（
selectmax（价格）
from图书信息表
where出版社='中国水利出版社'
）
五、其他操作
1、建立视图显示读者借书的信息（包括读者姓名、借书名、借书日期）
createviewv_读者信息
as
select读者信息.姓名,读者信息.读者编号,图书信息表.书名
from读者信息,借阅信息,图书信息表
where读者信息.读者编号=借阅信息.读者编号and借阅信息.图书编号=图书信息表.书名
go
2、建立存储过程，根据用户输入的读者编号来查看此读者的借阅信息
createprocproc_tszh@tszhchar（12）
as
select姓名,书名
from读者信息,借阅信息,图书信息表
where读者信息.读者编号=借阅信息.读者编号and借阅信息.图书编号=图书信息表.图书编号and读者信息.读者编号=@tszh
go
execproc_tszh'200946'
go
3、建立关于借阅表的触发器，当由用户借书时，向借阅表插入借书信息，并打印“借阅成功”
4、建立INSTEADOF触发器，当向借阅表插入借阅记录时，先检查读者信息是否存在该读者，如果存在则执行插入操作，如果不存在这提示“没有此读者！
”
5、在读者信息表和借阅信息表之间具有逻辑上的主外键关系，要求当删除或更新读者记录的时候，要激发触发器tri_Delete，在借阅信息表中也删除或更新相对应的记录行。
6、创建存储过程，使用游标确定一本书的价格是否排在前3名。
结果为1表示是，结果为0表示否。
升级会员 