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

逃离我推掉我的手 2024-05-03 14:36 61阅读 0赞

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

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