CARDS 借书卡: CNO 卡号,CNAME 姓名,CLASS 班级
BOOKS 图书: BNO 书号,BNAME 书名,AUTHOR 作者,PRICE 单价,QUANTITY 库存册数
BORROW 借书记录:CNO 借书卡号,BNO 书号,RDATE 还书日期
备注:限定每人每种书只能借一本;库存册数随借书、还书而改变。
要求用SQL语句实现下列题目:
1.写出建立BORROW表的SQL语句,要求定义主码完整性约束和引用完整性约束。
CREATE TABLE CARDS(
CNO INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
CNAME VARCHAR(15) NOT null,
CLASS VARCHAR(15) NOT NULL
);
CREATE TABLE BOOKS (
BNO INT(20) NOT NULL PRIMARY KEY AUTO_INCREMENT,
BNAME VARCHAR(20) NOT NULL,
AUTHOR VARCHAR(20) NOT NULL,
PRICE decimal(8,1) NOT NULL,
QUANTITY INT default 0
);
CREATE TABLE BORROW(
CNO int ,
BNO int ,
RDATE datetime,
FOREIGN KEY (CNO) REFERENCES CARDS(CNO),
FOREIGN KEY (BNO) REFERENCES BOOKS(BNO),
PRIMARY KEY (CNO,BNO)
);
insert into CARDS(CNAME,CLASS) values('张三','计科一班');
insert into CARDS(CNAME,CLASS) values('李四','计科一班');
insert into CARDS(CNAME,CLASS) values('王五','计科二班');
insert into CARDS(CNAME,CLASS) values('六四','计科二班');
insert into CARDS(CNAME,CLASS) values('七七','软工一班');
insert into CARDS(CNAME,CLASS) values('粑粑','软工二班');
insert into BOOKS(BNAME,AUTHOR,PRICE,QUANTITY) values ('水浒','施耐庵',188,3);
insert into BOOKS(BNAME,AUTHOR,PRICE,QUANTITY) values ('计算机网络','谢希仁',49,3);
insert into BOOKS(BNAME,AUTHOR,PRICE,QUANTITY) values ('计算方法','严蔚敏',58,3);
insert into BOOKS(BNAME,AUTHOR,PRICE,QUANTITY) values ('计算方法习题集','殷人昆',188,3);
insert into BOOKS(BNAME,AUTHOR,PRICE,QUANTITY) values ('数据库技术及应用','王珊',38,3);
insert into BOOKS(BNAME,AUTHOR,PRICE,QUANTITY) values ('组合数学','周伟',28,3);
insert into BOOKS(BNAME,AUTHOR,PRICE,QUANTITY) values ('Redis初探','周旭龙',25,3);
insert into BORROW(CNO,BNO) values(1,1);
insert into BORROW(CNO,BNO) values(2,1);
insert into BORROW(CNO,BNO) values(3,1);
insert into BORROW(CNO,BNO) values(4,3);
insert into BORROW(CNO,BNO) values(4,6);
insert into BORROW(CNO,BNO) values(5,6);
insert into BORROW(CNO,BNO) values(6,7);
1.找出借书超过5本的读者,输出借书卡号及所借图书册数。
select cno ,count(*) from borrow group by cno having count(*)>1
2.查询借阅了”水浒”一书的读者,输出姓名及班级。
SELECT * FROM CARDS A ,BORROW B,BOOKS C
WHERE A.CNO=B.CNO AND B.BNO=C.BNO
AND C.BNAME=”水浒”
3.查询过期未还图书,输出借阅者(卡号)、书号及还书日期。
CURRENT_TIMESTAMP 2019-09-06 22:34:10
CURRENT_DATE 2019-09-06
select * from borrow b where b.rdate < current_date
4.查询书名包括”网络”关键词的图书,输出书号、书名、作者。
select bno,bname,author from BOOKS where bname like “网络%”
5.查询现有图书中价格最高的图书,输出书名及作者。
select bname,author
from books
where price in (select max(price) from books)
select max(b.PRICE) ,b.* from BOOKS b 这句有问题 只有一个
7. 查询当前借了”计算方法”但没有借”计算方法习题集”的读者,输出其借书卡号,并按卡号降序排序输出。
select r.CNO
from BORROW r,BOOKS b
where r.BNO=b.BNO and b.BNAME=’计算方法’ and not exists
(
select from BORROW r1,BOOKS b1
where r1.BNO=b1.BNO and r.CNO=r1.CNO and b1.BNAME=’计算方法习题集’
)
order by r.CNO desc
*8. 将”C01”班同学所借图书的还期都延长一周。
—解法一
update BORROW set RDATE=DATEADD(Day,7,RDATE)
where CNO in ( select CNO from CARDS where CLASS=’计科一班’ )
—解法二
update b set b.RDATE=DATEADD(Day,7,RDATE)
from BORROW b,CARDS c
where b.CNO=c.CNO and c.CLASS=’计科一班’
9. 从BOOKS表中删除当前无人借阅的图书记录。
delete from BOOKS 2 where BNO not in ( select distinct BNO from BORROW )
10.建立一个视图,显示”力01”班学生的借书信息(只要求显示姓名和书名)。
11.查询当前同时借有”计算方法”和”组合数学”两本书的读者,输出其借书卡号,并按卡号升序排序输出。
12.假定在建BOOKS表时没有定义主码,写出为BOOKS表追加定义主码的语句。
13.对CARD表做如下修改:
a. 将NAME最大列宽增加到10个字符(假定原为6个字符)。
b. 为该表增加1列NAME(系名),可变长,最大20个字符。
14.创建一个触发器,要求库存册数在输入时不能为负数,如果为负数则取消操作。