数据库实验
姓名： 学号： 班级： 老师：
1. 实验第一部分
（1） 创建数据库并建立6张表 CreateTable book_category (
);
CreateTable member_level (
);
CreateTable loss_reporting (
);
CreateTable borrow (
);
CreateTable books (
book_id varchar(5)Primarykey, book_name varchar(5), author varchar(20), publishing varchar(20), category_id varchar(5), price money, date_in datetime, quantity_in int, reader_id varchar(5), book_id varchar(5), date_borrow datetime, date_return datetime, loss char(2), Primarykey(reader_id,book_id) reader_id varchar(5)Primarykey, loss_date datetime levelvarchar(6)Primarykey, days smallint, numbers smallint, fee smallint category_id varchar(5)Primarykey, category varchar(10)
)
quantity_out int, quantity_loss smallint, Foreignkey(category_id)References book_category(category_id)
CreateTablereader
(
) reader_id varchar(5)Primarykey, reader_name varchar(20), sex char(2), birthday datetime, phone int, Mobile varchar(11), card_name varchar(8), card_id varchar(18), levelvarchar(6), daydatetime, Foreignkey(level)References member_level(level)
（2） 向这6张表输入数据
Reader表（外键先留空）：
INSERT INTO
reader(reader_id,reader_name,sex,birthday,phone,Mobile,card_name,card_id,day)
VALUES('r001','李铭','男','19880307','62127790','136xxxxxxxx','身份证', '23xxxxxxxxxxxx','20100801');
INSERT INTO
reader(reader_id,reader_name,sex,birthday,phone,Mobile,card_name,card_id,day)
VALUES('r002','刘晓鸣','男','19900809','84778123','136xxxxxxxx','身份证',
'21xxxxxxxxxxxx','20100801');
INSERT INTO
reader(reader_id,reader_name,sex,birthday,phone,Mobile,card_name,card_id,day)
VALUES('r003','李明','男','20010221','84900581','139xxxxxxxx','身份证', '23xxxxxxxxxxxx','20100801');
INSERT INTO
reader(reader_id,reader_name,sex,birthday,phone,Mobile,card_name,card_id,day)
VALUES('r004','张鹰','女','19701112','51681212','138xxxxxxxx','身份证', '23xxxxxxxxxxxx','20100620');
INSERT INTO
reader(reader_id,reader_name,sex,birthday,phone,Mobile,card_name,card_id,day)
VALUES('r005','刘竟静','女','19991007','51681213','137xxxxxxxx','身份证',
'23xxxxxxxxxxxx','20090405');
INSERT INTO
reader(reader_id,reader_name,sex,birthday,phone,Mobile,card_name,card_id,day)
VALUES('r006','刘成刚','男','19900518','82161100','136xxxxxxxx','身份证',
'23xxxxxxxxxxxx','20100801');
INSERT INTO
reader(reader_id,reader_name,sex,birthday,phone,Mobile,card_name,card_id,day)
VALUES('r007','王铭','男','20010924','82190703','139xxxxxxxx','身份证', '23xxxxxxxxxxxx','20100515');
INSERT INTO
reader(reader_id,reader_name,sex,birthday,phone,Mobile,card_name,card_id,day)
VALUES('r008','宣明尼','女','19980825','62220506','158xxxxxxxx','身份证',
'23xxxxxxxxxxxx','20081220');
INSERT INTO
reader(reader_id,reader_name,sex,birthday,phone,Mobile,card_name,card_id,day)
VALUES('r009','柳红利','女','19970709','62220712','158xxxxxxxx','身份证',
'23xxxxxxxxxxxx','20100801');
Books表（外键先留空）：
INSERTINTO
books(book_id,book_name,author,publishing,price,date_in,quantity_in,quantity_out,quantity_loss)
VALUES('b001','图像处理','王一','北京大学出版社
','21','20100307','10','3','0');
INSERTINTO
books(book_id,book_name,author,publishing,price,date_in,quantity_in,quantity_out,quantity_loss)
VALUES('b002','苏州园林艺术','李白','清华大学出版社
','40','20100517','8','2','0');
INSERTINTO
books(book_id,book_name,author,publishing,price,date_in,quantity_in,q
uantity_out,quantity_loss)
VALUES('b003','神奇的宇宙','刘力','清华大学出版社
','18','20091209','5','0','0');
INSERTINTO
books(book_id,book_name,author,publishing,price,date_in,quantity_in,quantity_out,quantity_loss)
VALUES('b004','通讯原理','张扬','邮电出版社
','38','20100223','10','1','0');
INSERTINTO
books(book_id,book_name,author,publishing,price,date_in,quantity_in,quantity_out,quantity_loss)
VALUES('b005','肿瘤防治','李小明','人民卫生出版社
','16','20090405','5','0','0');
INSERTINTO
books(book_id,book_name,author,publishing,price,date_in,quantity_in,quantity_out,quantity_loss)
VALUES('b006','海参养殖技术','王平','中国农业出版社
','11','20100801','2','2','0');
INSERTINTO
books(book_id,book_name,author,publishing,price,date_in,quantity_in,quantity_out,quantity_loss)
VALUES('b007','操作系统','陈东','武汉大学出版社
','32','20100613','8','0','0');
Borrow表（外键先留空）：
INSERTINTO borrow
VALUES('r001','b001','20100802','20100902','否');
INSERTINTO borrow
VALUES('r001','b002','20100802','20100902','否');
INSERTINTO borrow
VALUES('r002','b006','20100709','20100809','否');
INSERTINTO borrow
VALUES('r004','b001','20100802','20101102','否');
INSERTINTO borrow
VALUES('r004','b006','20100810','20100910','否');
INSERTINTO borrow
VALUES('r004','b002','20100810','20100910','否');
INSERTINTO borrow
VALUES('r006','b001','20100810','20100910','否');
INSERTINTO borrow
VALUES('r006','b004','20100624','20100824','否');
Book_category表（将Books表的外键补全）：
INSERTINTO book_category
VALUES('ca01','计算机');
INSERTINTO book_category
VALUES('ca02','农林');
INSERTINTO book_category
VALUES('ca03','医学');
INSERTINTO book_category
VALUES('ca04','科普');
INSERTINTO book_category
VALUES('ca05','通信');
INSERTINTO book_category
VALUES('ca06','建筑');
UPDATE books
SET category_id=(select category_id from book_category where category_id='ca01')
WHERE book_id='b001';
UPDATE books
SET category_id=(select category_id from book_category where category_id='ca06')
WHERE book_id='b002';
UPDATE books
SET category_id=(select category_id from book_category where category_id='ca04')
WHERE book_id='b003';
UPDATE books
SET category_id=(select category_id from book_category where category_id='ca05')
WHERE book_id='b004';
UPDATE books
SET category_id=(select category_id from book_category where category_id='ca03')
WHERE book_id='b005';
UPDATE books
SET category_id=(select category_id from book_category where category_id='ca02')
WHERE book_id='b006';
UPDATE books
SET category_id=(select category_id from book_category where category_id='ca01')
WHERE book_id='b007';
Member_level表（将readers表的外键补全）：
Insert member_level
VALUES('普通','30','2','10');
Insert member_level
VALUES('银卡','60','3','50');
Insert member_level
VALUES('金卡','90','5','100');
UPDATE reader
SETlevel=(selectlevelfrom member_level wherelevel='普通') where reader_id='r001';
UPDATE reader
SETlevel=(selectlevelfrom member_level wherelevel='普通') where reader_id='r002';
UPDATE reader
SETlevel=(selectlevelfrom member_level wherelevel='普通') where reader_id='r003';
UPDATE reader
SETlevel=(selectlevelfrom member_level wherelevel='金卡') where reader_id='r004';
UPDATE reader
SETlevel=(selectlevelfrom member_level wherelevel='普通') where reader_id='r005';
UPDATE reader
SETlevel=(selectlevelfrom member_level wherelevel='银卡') where reader_id='r006';
UPDATE reader
SETlevel=(selectlevelfrom member_level wherelevel='普通') where reader_id='r007';
UPDATE reader
SETlevel=(selectlevelfrom member_level wherelevel='普通') where reader_id='r008';
UPDATE reader
SETlevel=(selectlevelfrom member_level wherelevel='普通') where reader_id='r009';
Loss_reporting表
Insert loss_reporting
VALUES('r006','20100901');
2. 实验第二部分
1. 查找姓王的普通会员姓名
Select reader.reader_name
From reader
Where reader.reader_name like'王%'and
reader.level='普通';
2. 显示每个级别会员的会员人数
Selectlevel,Count(1)人数
From reader
Groupbylevel;
3. 查找1980-19xx年出生的会员的姓名
Select reader.reader_name
From reader
Wheredatediff(year,birthday,'1991')>0
anddatediff(year,birthday,'1979')<0;
4. 显示借了书的读者编号
SelectDistinct(reader_id)
From borrow;
5. 查找借出的“b001”书的最早归还日期
SelectMin(date_return)最早归还日期
From borrow
Where book_id='b001';
3. 实验第三部分
1. 将计算机类图书按单价升序排列
SelectDistinct(books.book_name),books.price
From books,book_category
Where books.category_id=(select category_id from book_category where category='计算机')
orderby price asc;
2. 查找借了“图像处理”的会员姓名和借阅日期
SelectDistinct(reader.reader_name),borrow.date_borrow From books,borrow,reader
Where borrow.book_id=(select book_id from books where book_name='图像处理')andreader.reader_id=borrow.reader_id;
3. 查找借了会员“李铭”所借的全部图书的会员姓名 SelectDistinct(reader.reader_name)
from reader,borrow
where borrow.book_id in(
selectDistinct(borrow.book_id)
from borrow,reader
where borrow.reader_id=
(select reader_id from reader where reader_name='李铭') ) and reader.reader_id=borrow.reader_id;
4. 查找没有借“通讯原理”的会员姓名
SelectDistinct(reader.reader_name)
from reader
where reader_id notin(
selectDistinct(borrow.reader_id)
from borrow,books
where borrow.book_id=(select book_id from books where book_name='通讯原理'));
5. 查找借了2本以上图书的会员的姓名
Select reader_name
From reader
Where reader_id in(
Select borrow.reader_id
From borrow
Groupby reader_id
Havingcount(1)>2
);
6. 查找借了编号为“b001”和“b002”的图书的会员编号 SelectDistinct(reader_id)
From borrow
where book_id='b001'or book_id='b002';
4. 实验第四部分
1. 用insert命令向会员级别表中添加记录“钻石卡，180 ，20，200）
Insertinto member_level
Values('钻石卡','180','20','200');
2. 把计算机类图书的书名和入库数量存入另一个已知基本表computer-books(bname,quantity)中
CreateTable computer_books
( bname varchar(50),
quantity int
);
Insertinto computer_books(bname,quantity)
SelectDistinct(books.book_name),books.quantity_in
From books,book_category
where books.category_id=(select category_id from book_category where category='计算机');
3. 将普通会员的会费提高10元
Update member_level
Set fee=fee+10
wherelevel='普通';
4. 用delete命令从会员级别表中删除钻石卡记录
Delete
From member_level
wherelevel='钻石卡';
5. 建立一个金卡会员的视图，显示会员姓名和年龄 CreateView金卡会员视图表(member_name,member_age)
As
Select reader_name,year(getdate())-year(birthday)
From reader
wherelevel='金卡';
6. 建立一个分组视图，显示不同类别图书的出借数量 CreateView图书出借情况(category,number)
As
selectdistinct(book_category.category),count(1)数量
from book_category,books,borrow
where books.book_id=borrow.book_id and
book_category.category_id=books.category_id
groupby category;
5. 实验第五部分
1. 使用check子句定义如下约束条件
（1）性别只能是“男”或“女”
（2）读者编号是以r打头的4位字符,后3位只能是数字
CreateTable reader
(
)
reader_id varchar(5)Primarykey, reader_name varchar(20), sex char(2)check(sex in('男','女')), birthday datetime, phone int, Mobile varchar(11), card_name varchar(8), card_id varchar(18), levelvarchar(6), daydatetime, Foreignkey(level)References member_level(level), check(reader_id like'r[0-9][0-9][0-9]')
2. 编写触发器，实现以下约束
（1） 已挂失的借书证不能再借书；
（2） 借书的册数不能超过该会员级别允许的最大借书册
数。
第一个触发：
CreateTrigger notb
on borrow
forinsert
Asbegin
declare @id varchar(5) Select @id=reader_id from borrow ifexists(select reader_id from loss_reporting where reader_id=@id) Delete borrow where reader_id=@id
print'已挂失的借书证不能再借书!'
end;
第二个触发：
CreateTrigger maxium
on borrow
forinsert
Asbegin
declare @num int
Select @num=count(1)from borrow groupby reader_id
declare @id varchar(5)
Select @id=reader_id from borrow
declare @level varchar(6)
Select @level=levelfrom reader where reader_id=@id
declare @max int
Select @max=(select numbers from member_level wherelevel=@level) if(@num>@max)
delete borrow where reader_id=@id
print'借书的册数不能超过该会员级别允许的最大借书册数。'
end;