MySQL数据库原理与应用-阶段测试1

#新建一个名为“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

先赞后看，养成习惯！！！^ _ ^ ?? ?? ??
码字不易，大家的支持就是我的坚持下去的动力。点赞后不要忘了关注我哦！