挑战SQL经典题（二）

问题描述：
card 借书卡:cno卡号，name姓名，class 班级
books图书:bno书号，banme书名,author作者，price单价，quantity 库存册数
borrow借书记录:cno借书卡卡号，bno书号，rdate还书日期
备注：限定每人每种书只能借一本；库存册数随借书、还书而改变。
建表语句：

DROP TABLE IF EXISTS `books`;
CREATE TABLE `books` (
  `bno` int(11) NOT NULL COMMENT '书号',
  `bname` varchar(16) NOT NULL COMMENT '书名',
  `author` varchar(16) NOT NULL COMMENT '作者',
  `price` double(16,2) NOT NULL COMMENT '单价',
  `quantity` int(11) NOT NULL COMMENT '库存册数',
  PRIMARY KEY (`bno`),
  KEY `bno` (`bno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of books
-- ----------------------------
INSERT INTO `books` VALUES ('801', '仙剑奇侠传', '莫问', '64.00', '10');
INSERT INTO `books` VALUES ('802', '天龙八部', '金庸', '66.00', '20');
INSERT INTO `books` VALUES ('803', '射雕英雄传', '金庸', '34.00', '15');
INSERT INTO `books` VALUES ('804', '陆小凤传奇', '古龙', '22.00', '8');
INSERT INTO `books` VALUES ('805', '西游记', '吴承恩', '15.00', '9');
INSERT INTO `books` VALUES ('806', '三国演义', '罗贯中', '28.00', '11');
INSERT INTO `books` VALUES ('807', '水浒传', '施耐庵', '23.00', '12');
INSERT INTO `books` VALUES ('808', '斗破苍穹', '西红柿', '88.00', '33');

-- ----------------------------
-- Table structure for `borrow`
-- ----------------------------
DROP TABLE IF EXISTS `borrow`;
CREATE TABLE `borrow` (
  `cno` int(11) NOT NULL,
  `bno` int(11) NOT NULL,
  `rdate` varchar(255) NOT NULL,
  PRIMARY KEY (`cno`,`bno`),
  KEY `bno` (`bno`),
  CONSTRAINT `bno` FOREIGN KEY (`bno`) REFERENCES `books` (`bno`),
  CONSTRAINT `cno` FOREIGN KEY (`cno`) REFERENCES `card` (`cno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of borrow
-- ----------------------------
INSERT INTO `borrow` VALUES ('1', '801', '20181001');
INSERT INTO `borrow` VALUES ('1', '802', '20181001');
INSERT INTO `borrow` VALUES ('1', '803', '20181001');
INSERT INTO `borrow` VALUES ('1', '804', '20181001');
INSERT INTO `borrow` VALUES ('1', '805', '20181001');
INSERT INTO `borrow` VALUES ('1', '806', '20181001');
INSERT INTO `borrow` VALUES ('2', '801', '20181111');
INSERT INTO `borrow` VALUES ('2', '802', '20181111');
INSERT INTO `borrow` VALUES ('3', '803', '20180506');
INSERT INTO `borrow` VALUES ('3', '806', '20180506');
INSERT INTO `borrow` VALUES ('4', '807', '20180708');
INSERT INTO `borrow` VALUES ('4', '808', '20180708');
INSERT INTO `borrow` VALUES ('5', '805', '20181010');
INSERT INTO `borrow` VALUES ('6', '802', '20180208');
INSERT INTO `borrow` VALUES ('6', '806', '20181212');
INSERT INTO `borrow` VALUES ('7', '804', '20181018');
INSERT INTO `borrow` VALUES ('7', '806', '20181018');

-- ----------------------------
-- Table structure for `card`
-- ----------------------------
DROP TABLE IF EXISTS `card`;
CREATE TABLE `card` (
  `cno` int(11) NOT NULL COMMENT '卡号',
  `name` varchar(16) NOT NULL COMMENT '姓名',
  `class` int(11) NOT NULL COMMENT '班级',
  PRIMARY KEY (`cno`),
  KEY `cno` (`cno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of card
-- ----------------------------
INSERT INTO `card` VALUES ('1', '李逍遥', '101');
INSERT INTO `card` VALUES ('2', '赵灵儿', '101');
INSERT INTO `card` VALUES ('3', '林月如', '102');
INSERT INTO `card` VALUES ('4', '唐钰', '102');
INSERT INTO `card` VALUES ('5', '阿奴', '103');
INSERT INTO `card` VALUES ('6', '刘晋元', '104');
INSERT INTO `card` VALUES ('7', '酒剑仙', '105');

数据示意图如下：

 

表数据示意图.png

 

问题：

1. 写出建立borrow表的SQL语句，要求定义主码完整性约束和引用完整性约束

DROP TABLE IF EXISTS `borrow`;
CREATE TABLE `borrow` (
  `cno` int(11) NOT NULL,
  `bno` int(11) NOT NULL,
  `rdate` varchar(255) NOT NULL,
  PRIMARY KEY (`cno`,`bno`),
  KEY `bno` (`bno`),
  CONSTRAINT `bno` FOREIGN KEY (`bno`) REFERENCES `books` (`bno`),
  CONSTRAINT `cno` FOREIGN KEY (`cno`) REFERENCES `card` (`cno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

1. 找出借书超过5本的读者,输出借书卡号及所借图书册数;（1号 借了6本）

SELECT cno,COUNT(*) FROM borrow GROUP BY cno HAVING COUNT(*)>5

1. 查询借阅了"水浒传"一书的读者，输出姓名及班级；（4号 102班）

SELECT name,class FROM card ca WHERE EXISTS(
    SELECT * FROM borrow rr,books oo
    WHERE oo.bname='水浒传' AND oo.bno=rr.bno AND rr.cno=ca.cno) 

1. 查询截止今天未归还的图书，输出借阅者（卡号）、书号及还书日期

SELECT * FROM borrow WHERE rdate<'20181025'

1. 查询书名包括"西游"关键词的图书，输出书号、书名、作者（805-西游记-吴承恩）

SELECT bno,bname,author FROM books WHERE bname LIKE '%西游%'

1. 查询现有图书中价格最高的图书，输出书名及作者（斗破苍穹-西红柿）

SELECT bname,author FROM books WHERE price=
(SELECT MAX(price) FROM books)

1. 查询当前借了"天龙八部"但没有借"陆小凤传奇"的读者，输出其借书卡号，并按卡号降序排序输出（2号-赵灵儿、6号-刘晋元）

SELECT a.cno FROM borrow a,books b
WHERE b.BNAME='天龙八部' AND a.bno=b.bno AND NOT EXISTS(
   SELECT * FROM borrow rr,books bb
   WHERE bb.bno=rr.bno AND bb.bname='陆小凤传奇' AND rr.cno=a.CNO) 
ORDER BY a.cno DESC 

1. 将"102"班同学所借图书的归还日期都延长一周（归还日期是String型 执行出错）

UPDATE b SET rdate=DATEADD(Day,7,b.rdate)
FROM card a,borrow b WHERE a.cno=b.cno AND a.class='102' 

1. 从books表中删除当前无人借阅的图书记录

DELETE a FROM books a WHERE NOT EXISTS(
    SELECT * FROM borrow WHERE bno=a.bno) 

1. 如果经常按书名查询图书信息，请建立合适的索引

唯一索引(UNIQUE)
聚集索引(CLUSTERED） 
非聚集索引(NONCLUSTERED）
CREATE CLUSTERED INDEX books_bname ON books(bname)

1. 在borrow表上建立一个触发器，完成如下功能：如果读者借阅的书名是"鹿鼎记"，就将该读者的借阅记录保存在borrow_save表中（注borrow_save表结构同borrow表）

CREATE TRIGGER TR_save ON borrow
FOR INSERT,UPDATE
AS
IF @@ROWCOUNT>0
INSERT borrow_save SELECT i.*
FROM INSERTED i,books oo
WHERE i.bno=oo.bno AND oo.bname='鹿鼎记' 

1. 建立一个视图，显示"102"班学生的借书信息（只要求显示姓名和书名）

CREATE VIEW V_VIEW AS
SELECT a.name,b.bname
FROM borrow ab,card a,books b
WHERE ab.cno=a.cno AND ab.bno=b.bno AND a.class='102'

所建视图图示.png

1. 查询当前同时借有"仙剑奇侠传"和"天龙八部"两本书的读者，输出其借书卡号，并按卡号升序排序输出(1号-李逍遥 和 2号-赵灵儿)

SELECT rr.cno FROM borrow rr,books bb
WHERE bb.bname IN('仙剑奇侠传','天龙八部') AND bb.bno=rr.bno
GROUP BY rr.cno
HAVING COUNT(*)=2
ORDER BY rr.cno DESC 

1. 假定在建books表时没有定义主码，写出为该表追加定义主码的语句

ALTER TABLE books ADD PRIMARY KEY(bno) 

1. 将card表中的name字段的最大列宽增加到10个字符（假定原为6个字符）

ALTER TABLE card ALTER COLUMN NAME varchar(10) 

16.为card表增加1列add_name（系名），可变长，最大20个字符

ALTER TABLE card ADD add_name varchar(20)