表

CREATE TABLE CARD (
CNO int(4) NOT NULL COMMENT ‘卡号’,
NAME varchar(32) CHARACTER SET utf8mb4 NOT NULL COMMENT ’ 姓名’,
CLASS varchar(32) CHARACTER SET utf8mb4 NOT NULL COMMENT ‘班级’,
PRIMARY KEY (CNO)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE BOOKS (
BNO int(5) NOT NULL COMMENT ‘书号’,
BNAME varchar(32) CHARACTER SET utf8mb4 NOT NULL COMMENT ‘书名’,
AUTHOR varchar(32) CHARACTER SET utf8mb4 NOT NULL COMMENT ‘作者’,
PRICE decimal(10,2) DEFAULT NULL COMMENT ‘单价’,
QUANTITY int(4) NOT NULL COMMENT ‘库存册数’,
PRIMARY KEY (BNO)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE BORROW(
CNO int(4) NOT NULL COMMENT ‘借书卡号’,
BNO int(5) NOT NULL COMMENT ‘书号’,
RDATE datetime COMMENT ‘还书日期’,
PRIMARY KEY(CNO,BNO),
FOREIGN KEY (CNO) REFERENCES CARD(CNO),
FOREIGN KEY (BNO) REFERENCES BOOKS(BNO)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 comment=‘借书记录’;

INSERT INTO CARD(CNO, NAME, CLASS) VALUES (1, ‘张三’, ‘1班’);
INSERT INTO CARD(CNO, NAME, CLASS) VALUES (2, ‘王四’, ‘1班’);
INSERT INTO CARD(CNO, NAME, CLASS) VALUES (3, ‘李五’, ‘2班’);
INSERT INTO CARD(CNO, NAME, CLASS) VALUES (4, ‘王六’, ‘2班’);
INSERT INTO CARD(CNO, NAME, CLASS) VALUES (5, ‘随七’, ‘3班’);

INSERT INTO BORROW(CNO, BNO, RDATE) VALUES (1, 1, ‘2020-04-15 09:24:54’);
INSERT INTO BORROW(CNO, BNO, RDATE) VALUES (1, 2, ‘2020-04-14 09:25:25’);
INSERT INTO BORROW(CNO, BNO, RDATE) VALUES (2, 1, ‘2020-04-16 09:25:47’);
INSERT INTO BORROW(CNO, BNO, RDATE) VALUES (2, 2, ‘2020-04-14 09:25:55’);
INSERT INTO BORROW(CNO, BNO, RDATE) VALUES (3, 1, ‘2020-04-09 09:26:37’);
INSERT INTO BORROW(CNO, BNO, RDATE) VALUES (3, 2, ‘2020-04-03 09:26:41’);
INSERT INTO BORROW(CNO, BNO, RDATE) VALUES (3, 3, ‘2020-03-03 09:26:44’);
INSERT INTO BORROW(CNO, BNO, RDATE) VALUES (3, 4, ‘2020-04-15 09:26:49’);
INSERT INTO BORROW(CNO, BNO, RDATE) VALUES (3, 5, ‘2020-04-23 09:26:53’);
INSERT INTO BORROW(CNO, BNO, RDATE) VALUES (3, 6, ‘2020-04-02 09:26:57’);
INSERT INTO BORROW(CNO, BNO, RDATE) VALUES (4, 7, ‘2020-04-01 09:27:40’);
INSERT INTO BORROW(CNO, BNO, RDATE) VALUES (4, 6, ‘2020-04-01 09:27:43’);
INSERT INTO BORROW(CNO, BNO, RDATE) VALUES (4, 1, ‘2020-04-01 09:27:46’);
INSERT INTO BORROW(CNO, BNO, RDATE) VALUES (4, 2, ‘2020-04-01 09:27:48’);
INSERT INTO BORROW(CNO, BNO, RDATE) VALUES (4, 3, ‘2020-04-01 09:27:51’);
INSERT INTO BORROW(CNO, BNO, RDATE) VALUES (4, 4, ‘2020-04-01 09:27:53’);
INSERT INTO BORROW(CNO, BNO, RDATE) VALUES (4, 5, ‘2020-04-01 09:27:55’);
INSERT INTO BORROW(CNO, BNO, RDATE) VALUES (5, 1, ‘2020-04-06 09:28:32’);

INSERT INTO BOOKS(BNO, BNAME, AUTHOR, PRICE, QUANTITY) VALUES (1, ‘java编程思想’, ‘aaa’, 132.22, 5);
INSERT INTO BOOKS(BNO, BNAME, AUTHOR, PRICE, QUANTITY) VALUES (2, ‘计算方法’, ‘bbb’, 45.00, 2);
INSERT INTO BOOKS(BNO, BNAME, AUTHOR, PRICE, QUANTITY) VALUES (3, ‘组合数学’, ‘ccc’, 65.00, 1);
INSERT INTO BOOKS(BNO, BNAME, AUTHOR, PRICE, QUANTITY) VALUES (4, ‘springboot实战’, ‘ddd’, 73.22, 4);
INSERT INTO BOOKS(BNO, BNAME, AUTHOR, PRICE, QUANTITY) VALUES (5, ‘rabbitMQ实战’, ‘eee’, 50.00, 3);
INSERT INTO BOOKS(BNO, BNAME, AUTHOR, PRICE, QUANTITY) VALUES (6, ‘计算方法习题集’, ‘fff’, 32.00, 4);
INSERT INTO BOOKS(BNO, BNAME, AUTHOR, PRICE, QUANTITY) VALUES (7, ‘水浒传’, ‘ggg’, 222.00, 1);

– 找出借书超过5本的读者,输出借书卡号及所借图书册数
select CNO, count(CNO) from BORROW GROUP BY CNO HAVING count(CNO)>5;

– 查询借阅了"水浒"一书的读者，输出姓名及班级
SELECT * FROM CARD cd WHERE EXISTS( SELECT * FROM BOOKS bs, BORROW bw WHERE bs.BNO =bw.BNO and bs.BNAME like ‘水浒%’ AND bw.CNO = cd.CNO );

– 查询过期未还图书，输出借阅者（卡号）、书号及还书日期
select * from BORROW bw where bw.RDATE< NOW();

– 查询书名包括"网络"关键词的图书，输出书号、书名、作者
select * from BOOKS bs where bs.BNAME like ‘%水浒%’

– 查询现有图书中价格最高的图书，输出书名及作者
select * from BOOKS bs where bs.PRICE =(select MAX(PRICE) from BOOKS )

– 查询当前借了"计算方法"但没有借"计算方法习题集"的读者，输出其借书卡号，并按卡号降序排序输出
select bw.CNO from BOOKS bs, BORROW bw where bs.BNO =bw.BNO and bs.BNAME = '计算方法’and not EXISTS(select * from BOOKS bs1, BORROW bw1 where bs1.BNO =bw1.BNO and bs1.BNAME = ‘计算方法习题集’ and bw.CNO = bw1.CNO) order by bw.CNO desc;

– 将"C01"班同学所借图书的还期都延长一周
update BORROW bw set bw.RDATE= DATE_ADD(bw.RDATE,INTERVAL 7 DAY) where bw.CNO in (select a.CNO from (SELECT bw1.CNO from BORROW bw1 , CARD cd where bw1.CNO = cd.CNO and cd.CLASS =‘1班’)a)SELECT * from BORROW bw1 , CARD cd where bw1.CNO = cd.CNO and cd.CLASS =‘1班’;

– 从BOOKS表中删除当前无人借阅的图书记录
delete from BOOKS where BNO not in (select bw.BNO from BORROW bw) ;

delete bs from BOOKS bs where bs.BNO not in (select bw.BNO from BORROW bw) ;
delete bs from BOOKS bs where not exists (select * from BORROW bw where bs.BNO = bw.BNO);

– 建立一个视图，显示"1班"学生的借书信息（只要求显示姓名和书名）
create VIEW V_VIEW_1BAN_INFO as select cd.NAME, bs.BNAME from BORROW bw, CARD cd , BOOKS bs where bw.CNO =cd.CNO and bs.BNO = bw.BNO and cd.CLASS =‘1班’;select * from V_VIEW_1BAN_INFO;

– 查询当前同时借有"计算方法"和"组合数学"两本书的读者，输出其借书卡号，并按卡号降序排序输出

select bw.CNO from BORROW bw , BOOKS bs where bw.BNO =bs.BNO and bs.BNAME in(‘计算方法’, ‘组合数学’)
GROUP BY CNO HAVING count(CNO)>1 ORDER BY CNO DESC;

– 将NAME最大列宽增加到10个字符（假定原为32个字符）

ALTER TABLE CARD MODIFY NAME varchar(10) CHARACTER SET utf8mb4 NOT NULL COMMENT ’ 姓名’