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

布满荆棘的人生 2024-05-03 14:36 4阅读 0赞

\#新建一个名为“exam2”的数据库，用老师提供的SQL文件导入库中的数据。  
\#将此文件更名为“学号–姓名.txt”，然后在里面相应的位置完成题目要求，做完后将此文件上传给老师。

/*
     Navicat Premium Data Transfer
    
     Source Server         : MySQL
     Source Server Type    : MySQL
     Source Server Version : 80018
     Source Host           : localhost:3306
     Source Schema         : exam2
    
     Target Server Type    : MySQL
     Target Server Version : 80018
     File Encoding         : 65001
    
     Date: 18/12/2023 17:09:52
    */
    
    SET NAMES utf8mb4;
    SET FOREIGN_KEY_CHECKS = 0;
    
    -- ----------------------------
    -- Table structure for course
    -- ----------------------------
    DROP TABLE IF EXISTS `course`;
    CREATE TABLE `course`  (
      `cno` char(10) CHARACTER SET utf8mb4  NOT NULL,
      `cn` varchar(45) CHARACTER SET utf8mb4  NULL DEFAULT NULL,
      `chour` int(11) NULL DEFAULT NULL,
      `tn` varchar(45) CHARACTER SET utf8mb4  NULL DEFAULT NULL,
      PRIMARY KEY (`cno`) USING BTREE
    ) ENGINE = InnoDB CHARACTER SET = utf8mb4  ROW_FORMAT = Dynamic;
    
    -- ----------------------------
    -- Records of course
    -- ----------------------------
    INSERT INTO `course` VALUES ('1001', '计算机应用', 36, '张平');
    INSERT INTO `course` VALUES ('1002', '英语泛读', 54, '王国强');
    INSERT INTO `course` VALUES ('1003', '经济学原理', 54, '周爱明');
    INSERT INTO `course` VALUES ('1004', '企业经营管理', 36, '苏三');
    
    -- ----------------------------
    -- Table structure for sc
    -- ----------------------------
    DROP TABLE IF EXISTS `sc`;
    CREATE TABLE `sc`  (
      `sno` char(10) CHARACTER SET utf8mb4  NOT NULL,
      `cno` char(10) CHARACTER SET utf8mb4  NOT NULL,
      `score` int(11) NULL DEFAULT NULL,
      PRIMARY KEY (`sno`, `cno`) USING BTREE
    ) ENGINE = InnoDB CHARACTER SET = utf8mb4 ROW_FORMAT = Dynamic;
    
    -- ----------------------------
    -- Records of sc
    -- ----------------------------
    INSERT INTO `sc` VALUES ('2007100', '1002', 22);
    INSERT INTO `sc` VALUES ('2007100', '1003', 85);
    INSERT INTO `sc` VALUES ('2007101', '1001', 96);
    INSERT INTO `sc` VALUES ('2007102', '1004', 88);
    INSERT INTO `sc` VALUES ('2007103', '1001', 50);
    
    -- ----------------------------
    -- Table structure for student
    -- ----------------------------
    DROP TABLE IF EXISTS `student`;
    CREATE TABLE `student`  (
      `sno` char(10) CHARACTER SET utf8mb4  NOT NULL,
      `sn` varchar(45) CHARACTER SET utf8mb4  NOT NULL,
      `sex` char(2) CHARACTER SET utf8mb4  NULL DEFAULT NULL,
      `class` char(10) CHARACTER SET utf8mb4  NULL DEFAULT NULL,
      `age` int(11) NULL DEFAULT NULL,
      `resume` varchar(200) CHARACTER SET utf8mb4  NULL DEFAULT NULL,
      PRIMARY KEY (`sno`) USING BTREE
    ) ENGINE = InnoDB CHARACTER SET = utf8mb4 ROW_FORMAT = Dynamic;
    
    -- ----------------------------
    -- Records of student
    -- ----------------------------
    INSERT INTO `student` VALUES ('2007100', '王丽娟', '女', '金融1', 19, NULL);
    INSERT INTO `student` VALUES ('2007101', '黎明', '男', '金融2', 20, NULL);
    INSERT INTO `student` VALUES ('2007102', '和平', '男', '金融1', 20, NULL);
    INSERT INTO `student` VALUES ('2007103', '李华', '女', '计算机1', 19, NULL);
    INSERT INTO `student` VALUES ('2007104', '张名', '男', '计算机1', 18, NULL);
    
    SET FOREIGN_KEY_CHECKS = 1;

\#（1）向“学生信息”表中添加一条记录，sno为2007105，sn为张三，sex为男，age为20，其余字段值不填。

INSERT INTO student(`sno`, `sn`, `sex`, `age`) VALUES('2007105', '张三', '男', 20)

\#（2）修改“student”表中记录，将“张名”改为“张明”

UPDATE student SET sn = '张明' WHERE sn = '张名'

\#（3）查询所有未选修“英语泛读”课程的所有学生姓名

# 方式1 
    SELECT sn FROM student WHERE sno NOT IN (
    	SELECT sno FROM sc, course WHERE sc.cno = course.cno AND cn = '英语泛读'
    )
    
    # 方式2
    SELECT sn FROM student WHERE sno NOT IN(
    	SELECT sno FROM sc WHERE cno IN (SELECT cno FROM course WHERE cn = '英语泛读')
    )

\#（4）查询至少选修了两门课程的学生的姓名

# 方式1
    SELECT sn FROM student WHERE sno IN(
    	SELECT sno FROM sc, course WHERE sc.cno = course.cno GROUP BY sno HAVING COUNT(sno) > 1
    )
    
    # 方式2
    SELECT sn FROM student, sc WHERE student.sno = sc.sno GROUP BY sc.sno HAVING COUNT(sc.sno) >= 2

\#（5）查询同时选修了“1001”和“1002”课程的学生姓名

# 同时在一个表中查询两个以上的值：子查询、自身连接
    # 方式1-自身连接
    SELECT sn FROM student, sc a, sc b WHERE student.sno = a.sno AND student.sno = b.sno AND a.cno = '1002' AND b.cno = '1003'
    
    # 方式2-子查询
    SELECT sn FROM student WHERE sno IN (SELECT sno FROM sc WHERE cno = '1002') 
    AND sno IN (SELECT sno FROM sc WHERE cno = '1003')

\#（6）查询出男女生的人数

# 涉及到统计的使用：分组(GROUP BY)
    SELECT sex, COUNT(*) FROM student GROUP BY sex

\#（7）查询和“经济学原理”学时相同的课程名称

SELECT cn FROM course WHERE chour in (SELECT chour FROM course WHERE cn = '经济学原理') AND cn <> '经济学原理'

\#（8）查询比“计算机应用”平均分低的学生的学号

SELECT sno FROM sc, course WHERE sc.cno = course.cno AND course.cn = '计算机应用' 
    AND sc.score < (SELECT AVG(score) FROM sc, course WHERE sc.cno = course.cno AND course.cn = '计算机应用')

\#（9）查询“王丽娟”选修的课程名称和分数

SELECT cn, score FROM student, sc, course WHERE student.sno = sc.sno AND sc.cno = course.cno AND student.sn = '王丽娟'

\#（10）删除sc表中考试低于30分的记录

DELETE FROM sc WHERE sc.score < 30

\#创建一个视图，内容是各科考试不及格的学生学号、姓名、班级、课程和分数。

CREATE VIEW V1
    AS
    	SELECT student.sno, sn, class, cn, score FROM student, sc, course 
    WHERE student.sno = sc.sno AND sc.cno = course.cno AND score < 60
    
    # 使用视图
    SELECT * FROM V1

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