国脉信息学院数据库综合实验.docx

1、国脉信息学院数据库综合实验 综合实验一、 创建图书管理库（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,FILEGROWT

2、H =10%)LOG ON( 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) 可并插入如下数据：create table 图书信息表( 图书编号CHAR (8) NOT NULL PRIMARY

3、KEY,书名CHAR (20) NOT NULL,价格MONEY ,出版社CHAR (20) ,出版日期DATETIME ,作者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,李

4、青)insert into 图书信息表values(5,操作系统,29,中国水利出版社,2009-1-5,刘红)insert into 图书信息表values(6,离散数学,18,清华大学出版社,2006-1-5,唐浩强)insert into 图书信息表values(7,微机原理,45,清华大学出版社,2007-1-5,林林)2、 读者信息(dzxx)字段名称 数据类型 可否为空 说明 读者编号 char(8) 否 主键 姓名 char(20)否 身份证号char(18) 可 级别char(8) 可并插入如下数据：create table 读者信息(读者编号CHAR (8) NOT NULL

5、 PRIMARY KEY,姓名CHAR (20) NOT NULL,身份证号CHAR (18) ,级别CHAR (8) )insert into 读者信息values(1,张三,350322198408030014,1)insert into 读者信息values(2,李子,350322198408030014,1)insert into 读者信息values(3,王五,350322198408030014,2)insert into 读者信息values(4,赵三,350322198408030014,3)insert into 读者信息values(5,林刘,350322198408030

6、014,2)insert into 读者信息values(6,陈东,350322198408030014,3)insert into 读者信息values(7,郑一,350322198408030014,1)3、 借阅信息（jyxx）字段名称 数据类型 可否为空 说明 读者编号 char(8) 否主键外键 图书编号 char(8) 否外键 借阅日期datetime 可还书日期datetime 可是否续借 char(4) 可并插入如下数据：create table 借阅信息( 读者编号CHAR (8) NOT NULL PRIMARY KEY,图书编号CHAR (8) NOT NULL,借阅日期

7、DATETIME ,还书日期DATETIME ,是否续借CHAR (4),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-

8、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、 修改列“出版社”的定义，长度修改成30alter table 图书信息表alter column 出版社char(30)2、 修改读者信息表中编号为1的读者的级别为2级UPDATE 读者信息SET 级别=2WHERE 读者编号=1四、 完成如下查询语句：1、 查询图书馆中所有的图书

9、、出版社、读者信息SELECT 读者信息.*,出版社,书名FROM 读者信息,图书信息表2、 查询前3项读者借阅图书的信息SELECT 读者编号,图书编号,借阅日期FROM 借阅信息SELECT 借阅信息.*FROM 借阅信息WHERE 读者编号in (1 , 2 , 3)3、 查询前3%项读者借阅图书的信息4、 查询所有借书的读者编号，要求取消重复行SELECT DISTINCT 读者编号FROM 借阅信息5、 查询图书价格打8折后的图书名称、原价和折后价格，分别以“图书名称”、“原价”、“折后价格”为列名显示6、 查询价格大于等于20元的图书信息SELECT 图书信息表.*FROM 图书信

10、息表WHERE 价格=207、 查询价格在2040元之间的图书信息SELECT 图书信息表.*FROM 图书信息表WHERE 价格BETWEEN 20 AND 408、 查询由“中国水利出版社”、“高等教育出版社”、“清华大学出版社”出版的所有图书SELECT 书名FROM 图书信息表WHERE 出版社IN (中国水利出版社 ,高等教育出版社,清华大学出版社)9、 查询姓“张”的读者的信息SELECT 读者信息.*FROM 读者信息WHERE 姓名liKE 张%10、 计算图书馆图书的总价格、平均价格SELECT SUM(价格),AVG(价格)FROM 图书信息表11、 计算机出自“中国水利出

11、版社”的图书数量SELECT COUNT(图书编号)FROM 图书信息表WHERE 出版社=中国水利出版社12、 按读者级别由高到低输出读者信息SELECT 读者信息.*FROM 读者信息ORDER BY 级别DESC13、 查询图书价格大于图书平均价格的所有图书信息select 图书编号,书名,出版社,价格from 图书信息表where 价格( select avg(价格) as 平均价 from 图书信息表 )14、 查询“李青”曾出版过书的出版社还出版了哪些书select 出版社作者from 图书信息表where 作者= 李青select 图书编号,书名,作者,出版社,价格from 图书

12、信息表where 出版社in(中国水利出版社 , 高等教育出版社)15、 查询价格大于“中国水利出版社”出版的任意书的价格的图书信息select 图书编号,书名,出版社,价格from 图书信息表where 价格( select max(价格) from 图书信息表 where 出版社= 中国水利出版社 )五、 其他操作1、 建立视图显示读者借书的信息（包括读者姓名、借书名、借书日期）create view v_读者信息asselect 读者信息.姓名,读者信息.读者编号,图书信息表.书名from 读者信息,借阅信息,图书信息表where 读者信息.读者编号=借阅信息.读者编号and 借阅信息.

13、图书编号=图书信息表.书名go2、 建立存储过程，根据用户输入的读者编号来查看此读者的借阅信息create proc proc_tszh tszh char(12)as select 姓名,书名from 读者信息,借阅信息,图书信息表where 读者信息.读者编号=借阅信息.读者编号and 借阅信息.图书编号=图书信息表.图书编号and 读者信息.读者编号=tszhgoexec proc_tszh 200946go3、 建立关于借阅表的触发器，当由用户借书时，向借阅表插入借书信息，并打印“借阅成功”4、 建立 INSTEAD OF触发器，当向借阅表插入借阅记录时，先检查读者信息是否存在该读者，如果存在则执行插入操作，如果不存在这提示“没有此读者！”5、 在读者信息表和借阅信息表之间具有逻辑上的主外键关系，要求当删除或更新读者记录的时候，要激发触发器tri_Delete，在借阅信息表中也删除或更新相对应的记录行。6、创建存储过程，使用游标确定一本书的价格是否排在前3名。结果为1表示是，结果为0表示否。