#新建一个名为“exam1”的数据库,用老师提供的SQL文件导入库中的数据。
#将此文件更名为“学号–姓名.txt”,然后在里面相应的位置完成题目要求,做完后将此文件上传给老师。
/*
Navicat Premium Data Transfer
Source Server : MySQL
Source Server Type : MySQL
Source Server Version : 80018
Source Host : localhost:3306
Source Schema : exam1
Target Server Type : MySQL
Target Server Version : 80018
File Encoding : 65001
Date: 18/12/2023 17:06:18
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for book
-- ----------------------------
DROP TABLE IF EXISTS `book`;
CREATE TABLE `book` (
`bookid` char(10) CHARACTER SET utf8mb4 NOT NULL,
`bname` varchar(45) CHARACTER SET utf8mb4 NOT NULL,
`author` varchar(45) CHARACTER SET utf8mb4 NULL DEFAULT NULL,
`price` int(11) NULL DEFAULT NULL,
`class` char(20) CHARACTER SET utf8mb4 NULL DEFAULT NULL,
`publish` varchar(45) CHARACTER SET utf8mb4 NULL DEFAULT NULL,
`edition` varchar(45) CHARACTER SET utf8mb4 NULL DEFAULT NULL,
`resume` varchar(200) CHARACTER SET utf8mb4 NULL DEFAULT NULL,
PRIMARY KEY (`bookid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of book
-- ----------------------------
INSERT INTO `book` VALUES ('100001', 'C程序设计', '王宇天', 24, '计算机', '中国水利出版社', '2003第1版', '使用通俗语言介绍C程序语言');
INSERT INTO `book` VALUES ('100002', 'VB程序设计语言', '张大海', 19, '计算机', '清华大学出版社', '2004第2版', NULL);
INSERT INTO `book` VALUES ('100003', '大学英语', '宋美美', 34, '英语', '北大出版社', '2010第1版', NULL);
INSERT INTO `book` VALUES ('100004', '机床加工', '张小梅', 27, '建筑', '电子工业出版社', '2009第1版', NULL);
-- ----------------------------
-- Table structure for borrow
-- ----------------------------
DROP TABLE IF EXISTS `borrow`;
CREATE TABLE `borrow` (
`bookid` char(10) CHARACTER SET utf8mb4 NOT NULL,
`readerid` char(10) CHARACTER SET utf8mb4 NOT NULL,
`status` varchar(45) CHARACTER SET utf8mb4 NULL DEFAULT NULL,
`date` date NULL DEFAULT NULL,
PRIMARY KEY (`bookid`, `readerid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of borrow
-- ----------------------------
INSERT INTO `borrow` VALUES ('100001', '1001', '借阅', '2012-05-30');
INSERT INTO `borrow` VALUES ('100002', '1004', '续借', '2013-02-13');
INSERT INTO `borrow` VALUES ('100003', '1004', '借阅', '2013-01-19');
INSERT INTO `borrow` VALUES ('100004', '1002', '续借', '2013-10-22');
INSERT INTO `borrow` VALUES ('23123', '1001', 'fsdf', '2000-01-01');
-- ----------------------------
-- Table structure for reader
-- ----------------------------
DROP TABLE IF EXISTS `reader`;
CREATE TABLE `reader` (
`readerid` char(10) CHARACTER SET utf8mb4 NOT NULL,
`rname` varchar(45) CHARACTER SET utf8mb4 NULL DEFAULT NULL,
`sex` enum('男','女') CHARACTER SET utf8mb4 NULL DEFAULT NULL,
`department` varchar(45) CHARACTER SET utf8mb4 NULL DEFAULT NULL,
PRIMARY KEY (`readerid`) USING BTREE,
CONSTRAINT `reader_chk_1` CHECK ((`sex` = _utf8mb4'男') or (`sex` = _utf8mb4'女')),
CONSTRAINT `reader_chk_2` CHECK ((`sex` = _utf8mb4'男') or (`sex` = _utf8mb4'女'))
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of reader
-- ----------------------------
INSERT INTO `reader` VALUES ('1001', '陈芳', '女', '计算机');
INSERT INTO `reader` VALUES ('1002', '罗军军', '女', '机电');
INSERT INTO `reader` VALUES ('1003', '黎明', '男', '外语');
INSERT INTO `reader` VALUES ('1004', '刘凡易', '男', '信息');
SET FOREIGN_KEY_CHECKS = 1;
#(1)向book表中添加一条记录,书号为“100005”,书名“计算机网络基础”,作者“孙超”
INSERT INTO book(bookid, bname, author) VALUES('100005', '计算机网络基础', '孙超')
#(2)修改reader表中记录,将刘凡易的所在部门改为水建
UPDATE reader SET department = '水建' WHERE rname = '刘凡易'
#(3)查询所有未借阅图书的读者姓名
# 方式1
#SELECT r.rname FROM borrow b, reader r WHERE b.readerid = r.readerid AND b.status <> '借阅' AND b.status <> '续借'
# 方式2
#SELECT r.rname FROM borrow b, reader r WHERE b.readerid = r.readerid AND b.status NOT IN ('借阅', '续借')
# 上述理解错误
SELECT rname FROM reader WHERE readerid NOT IN (select readerid from borrow)
#(4)查询至少借阅了两门图书的读者的姓名
# 方式1
SELECT r.rname FROM borrow b, reader r WHERE b.readerid = r.readerid AND b.status IN ('借阅', '续借') GROUP BY b.readerid HAVING COUNT(*) > 1
# 方式2
SELECT r.rname FROM borrow b LEFT JOIN reader r USING(readerid) WHERE b.status IN ('借阅', '续借') GROUP BY readerid HAVING COUNT(*) > 1
# 方式3 非 b.status IN ('借阅', '续借')
SELECT rname FROM reader WHERE readerid IN(
SELECT readerid FROM borrow GROUP BY readerid HAVING COUNT(*) > 1
)
#(5)查询借阅了“100001”并且未借阅“100002”图书的读者姓名
# 方式-连接查询
SELECT r.rname FROM borrow b, reader r WHERE b.readerid = r.readerid AND b.bookid = '100001'
AND b.bookid <> '100002'
# 方式-子查询
SELECT rname FROM reader WHERE readerid IN(
SELECT readerid FROM borrow WHERE bookid = '100001' AND readerid NOT IN (
SELECT readerid FROM borrow WHERE bookid = '100002' )
)
# 方法-自身查询
SELECT DISTINCT r.rname FROM reader r, borrow a, borrow b WHERE r.readerid = a.readerid AND r.readerid = b.readerid
AND a.bookid = '100001' AND b.bookid <> '100002'
#(6)查询出借阅了图书的各个部门的人数
# 方式1
SELECT r.department, COUNT(DISTINCT r.readerid) AS count FROM reader r, borrow b WHERE r.readerid = b.readerid GROUP BY r.department
# 方式2
SELECT department, COUNT(DISTINCT readerid) AS count FROM borrow JOIN reader USING(readerid) GROUP BY department
#(7)查询和“大学英语”出版社相同的图书名称
# 方式1-子查询
SELECT bname FROM book WHERE publish in (SELECT publish FROM book WHERE bname = '大学英语') AND bname <> '大学英语'
# 方式2-自身查询
SELECT b.bname FROM book a, book b WHERE a.bname = '大学英语' AND a.publish = b.publish AND b.bname <> '大学英语'
#(8)查询比所有图书平均价低的图书的编号
SELECT bookid FROM book WHERE price < (SELECT AVG(price) FROM book)
#(9)查询书名中包含有“计算机”的书名和作者
# 测试
# SELECT * FROM book WHERE bname LIKE '%计_机%'
# SELECT * FROM book WHERE bname LIKE '%__机%'
SELECT * FROM book WHERE bname LIKE '%计算机%'
#(10)查询各个出版社中图书最高价格、最低价格和图书数目
SELECT publish, MAX(price) AS max, MIN(price) AS min, COUNT(*) AS count FROM book GROUP BY publish HAVING publish <> ''
#创建一个视图,内容是输出续借状态下的书籍的价格总和
SELECT SUM(a.price) AS sum FROM book a, borrow b WHERE a.bookid = b.bookid AND b.status = '续借'
# 创建视图
CREATE VIEW V1
AS
SELECT SUM(price) AS sum FROM book JOIN borrow USING(bookid) WHERE status = '续借'
# 使用视图
SELECT * FROM V1
先赞后看,养成习惯!!!^ _ ^ ?? ?? ??
码字不易,大家的支持就是我的坚持下去的动力。点赞后不要忘了关注我哦!