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

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

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