数据库原理综合实验

数据库原理综合实验
综合实验
一、创建图书管理库（BookSys）
该数据库的主数据文件逻辑名称为：“图书管理库”，物理文件名为：图书管理库．Mdf，物理文件路径为D：\，初始大小为3MB，最大容量为100MB，增长速度为10%；数据库的日志文件逻辑名称为“图书管理库_log”，物理文件名为：图书管理库_log.ldf，物理文件路径为D：\，初始大小为1MB，最大容量为2MB，增长速度为10%）CREATE DATABASE图书管理库
ON PRIMARY
(
NAME='图书管理库',
FILENAME='D:\图书管理库.mdf',
SIZE= 3MB,
MAXSIZE= 100MB,
FILEGROWTH= 10%
)
LOG ON
(
NAME='图书管理库_log',
FILENAME='D:\图书管理库_log.ldf',
SIZE= 1MB,
MAXSIZE= 2MB,
FILEGROWTH= 10%
)
二、在图书管理库（BookSys）中建立如下的表：
1、图书信息表（tsxx）
CREATE TABLE图书信息
(图书编号char(8)PRIMARY KEY NOT NULL,
图书名称char(20)UNIQUE NOT NULL,
价格money NOT NULL,
出版社char (20),
出版时间datetime null,
作者char(8),
)
并插入如下数据：
insert into图书信息
values(1,'高等数学',20,'高等教育出版社','2001-1-5','李青')
insert into图书信息
values(2,'C语言程序设计',30,'高等教育出版社','2004-1-5','唐浩强') insert into图书信息
values(3,'SQL server原理',28,'中国水利出版社','2008-1-5','李青') insert into图书信息
values(4,'数据结构',37,'中国水利出版社','2011-1-5','李青')
insert into图书信息
values(5,'操作系统',29,'中国水利出版社','2009-1-5','刘红')
insert into图书信息
values(6,'离散数学',18,'清华大学出版社','2006-1-5','唐浩强')
insert into图书信息
values(7,'微机原理',45,'清华大学出版社','2007-1-5','林林')
CREATE TABLE读者信息
(读者编号char(8)PRIMARY KEY NOT NULL, 姓名char(20)UNIQUE NOT NULL,
身份证号char(18),
级别char (8),
)
并插入如下数据：
insert into读者信息
values(1,'张三','350322************',1) insert into读者信息
values(2,'李子','350322************',1) insert into读者信息
values(3,'王五','350322************',2) insert into读者信息
values(4,'赵三','350322************',3) insert into读者信息
values(5,'林刘','350322************',2) insert into读者信息
values(6,'陈东','350322************',3) insert into读者信息
values(7,'郑一','350322************',1)
3、借阅信息（jyxx）
CREATE TABLE借阅信息
(读者编号char(8)NOT NULL,
图书编号char(8)NOT NULL,
借阅日期datetime,
还书日期datetime,
是否续借char(4),
PRIMARY KEY(读者编号,图书编号),
FOREIGN KEY (读者编号)REFERENCES读者信息(读者编号), FOREIGN KEY (图书编号)REFERENCES图书信息(图书编号), ) 并插入如下数据：
insert into借阅信息
values(1,3,'2010-12-2','2011-12-1','可以') insert into借阅信息values(1,4,'2010-12-2','2011-12-1','可以') insert into借阅信息values(2,3,'2011-2-2','2011-10-1','可以') insert into借阅信息values(5,7,'2011-2-2','2011-9-1','可以') insert into借阅信息
values(7,3,'2011-1-2','2011-2-1','可以') insert into借阅信息
values(4,2,'2010-12-20','2011-12-1','可以') values(4,1,'2011-1-12','2011-2-1','可以')
三、完成如下操作：
1、修改列“出版社”的定义，长度修改成30 ALTER TABLE图书信息
ALTER COLUMN出版社CHAR(30)
2、修改读者信息表中编号为1的读者的级别为2级UPDATE读者信息
SET级别= 2
WHERE读者编号= 1
四、完成如下查询语句：
1、查询图书馆中所有的图书、出版社、读者信息SELECT图书名称,出版社
FROM图书信息
SELECT读者信息.*
FROM读者信息
2、查询前3项读者借阅图书的信息SELECT TOP 3 *
FROM借阅信息
3、查询前3%项读者借阅图书的信息SELECT TOP 3PERCENT*
FROM借阅信息
4、查询所有借书的读者编号，要求取消重复行SELECT DISTINCT读者编号
FROM借阅信息
5、查询图书价格打8折后的图书名称、原价和折后价格，分别以“图书名称”、“原价”、
“折后价格”为列名显示
SELECT图书名称,价格原价,价格*0.8 折后价格
FROM图书信息
6、查询价格大于等于20元的图书信息
SELECT*
FROM图书信息
WHERE价格>20
7、查询价格在20~40元之间的图书信息
SELECT*
FROM图书信息
WHERE价格BETWEEN 20 AND 40
8、查询由“中国水利出版社”、“高等教育出版社”、“清华大学出版社”出版的所有图书
SELECT*
FROM图书信息
WHERE出版社IN('中国水利出版社','高等教育出版社','清华大学出版社')
9、查询姓“张”的读者的信息
SELECT*
FROM读者信息
WHERE姓名like'张%'
10、计算图书馆图书的总价格、平均价格
SELECT SUM(价格)总价格,AVG(价格)平均价格
FROM图书信息
11、计算机出自“中国水利出版社”的图书数量SELECT COUNT(图书编号)图书数量
FROM图书信息
WHERE出版社='中国水利出版社'
12、按读者级别由高到低输出读者信息
SELECT*
FROM读者信息
ORDER BY级别DESC
13、查询图书价格大于图书平均价格的所有图书信息SELECT*
FROM图书信息
WHERE价格>(SELECT AVG(价格)FROM图书信息)
14、查询“李青”曾出版过书的出版社还出版了哪些书
SELECT图书名称,出版社
FROM图书信息
WHERE作者='李青'
15、查询价格大于“中国水利出版社”出版的任意书的价格的图书信息
SELECT*
FROM图书信息
WHERE价格>(SELECT MAX(价格)FROM图书信息WHERE出版社='中国水利出版社')
五、其他操作
1、建立视图显示读者借书的信息（包括读者姓名、借书名、借书日期）
CREATE VIEW读者借书
AS
SELECT姓名,图书名称借书名,借阅日期借书日期
FROM图书信息,借阅信息,读者信息
WHERE读者信息.读者编号=借阅信息.读者编号AND借阅信息.图书编号=图书信息.图书编号
2、建立存储过程，根据用户输入的读者编号来查看此读者的借阅信息
CREATE PROC编号借阅信息@读者编号char(8)
AS
SELECT读者借书.*
FROM读者借书,读者信息
WHERE读者编号=@读者编号AND读者信息.姓名=读者借书.姓名
EXEC编号借阅信息 1
3、建立关于借阅表的触发器，当由用户借书时，向借阅表插入借书信息，并打印“借阅成
功”
CREATE TRIGGER用户借书ON借阅信息
AFTER INSERT
AS
PRINT'借阅成功'
insert into借阅信息
values(4,3,'2010-12-2','2011-12-1','可以')
4、建立INSTEAD OF触发器，当向借阅表插入借阅记录时，先检查读者信息是否存在该
读者，如果存在则执行插入操作，如果不存在这提示“没有此读者！”
CREATE TRIGGER插入借阅ON借阅信息
INSTEAD OF INSERT
AS
BEGIN
DECLARE@读者编号char(8),@图书编号char(8),@借阅日期datetime,@还书日期datetime,@是否续借char(4)
SELECT@读者编号=读者编号,@图书编号=图书编号,@借阅日期=借阅日期,@还书日期=还书日期,@是否续借=是否续借
FROM inserted
IF NOT EXISTS(SELECT*FROM读者信息WHERE@读者编号=读者编号)
PRINT'没有此读者！'
ELSE
INSERT INTO借阅信息VALUES (@读者编号,@图书编号,@借阅日期,@还书日期,@是否续借)
END
insert into借阅信息
values(10,3,'2010-12-2','2011-12-1','可以')
insert into借阅信息
values(7,4,'2010-12-2','2011-12-1','可以')
5、在读者信息表和借阅信息表之间具有逻辑上的主外键关系，要求当删除或更新读者记录
的时候，要激发触发器tri_Delete，在借阅信息表中也删除或更新相对应的记录行。

CREATE TRIGGER tri_DELETE2ON读者信息
INSTEAD OF DELETE,UPDATE
AS
BEGIN
DECLARE@读者编号_up char(8)
DECLARE@姓名_up char(20)
DECLARE@身份证_up char(18)
DECLARE@级别_up char(8)
DECLARE@读者编号_de char(8)
DECLARE@图书编号char(8)
DECLARE@借阅日期datetime
DECLARE@还书日期datetime
DECLARE@是否续借char(4)
SELECT@读者编号_up=读者编号,@姓名_up=姓名,@身份证_up=身份证号,@级别_up=级别
FROM inserted
SELECT@读者编号_de=读者编号
FROM DELETEd
SELECT@图书编号=图书编号,@借阅日期=借阅日期,@还书日期=还书日期,@是否续借=是否续借FROM借阅信息
IF(@读者编号_de is not null)
BEGIN
DELETE FROM借阅信息WHERE读者编号=@读者编号_de
DELETE FROM读者信息WHERE读者编号=@读者编号_de
END
IF(@读者编号_up is not null)
BEGIN
INSERT INTO读者信息
VALUES(@读者编号_up,@姓名_up,@身份证_up,@级别_up)
INSERT INTO借阅信息
VALUES(@读者编号_up,@图书编号,@借阅日期,@还书日期,@是否续借)
END
END
DELETE FROM读者信息WHERE读者编号=2
UPDATE读者信息SET读者编号= 8 WHERE读者编号= 5
6、创建存储过程，使用游标确定一本书的价格是否排在前3名。

结果为1表示是，结果为
0表示否。

CREATE PROC TOP3_JIAGE@图书名称char(20),@BOOL int OUTPUT
AS
BEGIN
DECLARE cur CURSOR FOR
SELECT TOP 3 图书名称FROM图书信息ORDER BY价格DESC DECLARE@NAME char(20)
OPEN cur
SET@BOOL= 0
FETCH FROM cur INTO@NAME
WHILE@@FETCH_STATUS=0
BEGIN
IF(@NAME=@图书名称)
SET@BOOL= 1
FETCH FROM cur INTO@NAME
END
PRINT@BOOL
CLOSE cur
DEALLOCATE cur
END
DECLARE@是否int
EXEC TOP3_JIAGE'微机原理',@是否PRINT@是否。