MYSQL练习

• 练习表

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for Course
-- ----------------------------
DROP TABLE IF EXISTS `Course`;
CREATE TABLE `Course`  (
  `Cno` char(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `Cname` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `Tno` char(3) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  PRIMARY KEY (`Cno`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '课程表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of Course
-- ----------------------------
INSERT INTO `Course` VALUES ('3-105', '计算机导论', '825');
INSERT INTO `Course` VALUES ('3-245', '操作系统', '804');
INSERT INTO `Course` VALUES ('6-166', '数字电路', '856');
INSERT INTO `Course` VALUES ('9-888', '高等数学', '831');

-- ----------------------------
-- Table structure for Score
-- ----------------------------
DROP TABLE IF EXISTS `Score`;
CREATE TABLE `Score`  (
  `Sno` char(3) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `Cno` char(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `Degree` decimal(4, 1) NULL DEFAULT NULL,
  PRIMARY KEY (`Sno`, `Cno`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COMMENT = '分数表' COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of Score
-- ----------------------------
INSERT INTO `Score` VALUES ('101', '3-105', 64.0);
INSERT INTO `Score` VALUES ('101', '6-166', 85.0);
INSERT INTO `Score` VALUES ('103', '3-105', 92.0);
INSERT INTO `Score` VALUES ('103', '3-245', 86.0);
INSERT INTO `Score` VALUES ('105', '3-105', 88.0);
INSERT INTO `Score` VALUES ('105', '3-245', 75.0);
INSERT INTO `Score` VALUES ('107', '3-105', 91.0);
INSERT INTO `Score` VALUES ('107', '6-166', 79.0);
INSERT INTO `Score` VALUES ('108', '3-105', 78.0);
INSERT INTO `Score` VALUES ('108', '6-166', 81.0);
INSERT INTO `Score` VALUES ('109', '3-105', 76.0);
INSERT INTO `Score` VALUES ('109', '3-245', 68.0);

-- ----------------------------
-- Table structure for Student
-- ----------------------------
DROP TABLE IF EXISTS `Student`;
CREATE TABLE `Student`  (
  `Sno` char(3) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `Sname` char(8) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `Ssex` char(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `Sbirthday` datetime(0) NULL DEFAULT NULL,
  `Class` char(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`Sno`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COMMENT = '学生表' COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of Student
-- ----------------------------
INSERT INTO `Student` VALUES ('101', '李军', '男', '1976-02-20 00:00:00', '95033');
INSERT INTO `Student` VALUES ('103', '陆君', '男', '1974-06-03 00:00:00', '95031');
INSERT INTO `Student` VALUES ('105', '匡明', '男', '1975-10-02 00:00:00', '95031');
INSERT INTO `Student` VALUES ('107', '王丽', '女', '1976-01-23 00:00:00', '95033');
INSERT INTO `Student` VALUES ('108', '曾华', '男', '1977-09-01 00:00:00', '95033');
INSERT INTO `Student` VALUES ('109', '王芳', '女', '1975-02-10 00:00:00', '95031');

-- ----------------------------
-- Table structure for Teacher
-- ----------------------------
DROP TABLE IF EXISTS `Teacher`;
CREATE TABLE `Teacher`  (
  `Tno` char(3) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `Tname` char(4) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `Tsex` char(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `Tbirthday` datetime(0) NULL DEFAULT NULL,
  `Prof` char(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `Depart` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  PRIMARY KEY (`Tno`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COMMENT = '教师表' COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of Teacher
-- ----------------------------
INSERT INTO `Teacher` VALUES ('804', '李诚', '男', '1958-12-02 00:00:00', '副教授', '计算机系');
INSERT INTO `Teacher` VALUES ('825', '王萍', '女', '1972-05-05 00:00:00', '助教', '计算机系');
INSERT INTO `Teacher` VALUES ('831', '刘冰', '女', '1977-08-14 00:00:00', '助教', '电子工程系');
INSERT INTO `Teacher` VALUES ('856', '张旭', '男', '1969-03-12 00:00:00', '讲师', '电子工程系');

SET FOREIGN_KEY_CHECKS = 1;

• 练习题目

查询教师所有的单位即不重复的Depart列
-- SELECT DISTINCT Depart FROM Teacher;

查询Score表中成绩为85，86的记录
-- SELECT * FROM Score WHERE Degree in (85,86);

查询“95031”班的学生人数
-- SELECT count(*) from Student where Class = '95031';

查询Score表中的最高分的学生学号和分数
-- SELECT Sno, Degree FROM Score ORDER BY Degree DESC LIMIT 1;
-- SELECT Sno, Degree from Score where Degree = (SELECT MAX(Degree) FROM Score);

查询Score表中的除了最高分的学生其余学生的学号和课程号
-- SELECT Sno,Degree from Score where Degree not in (SELECT MAX(Degree) from Score) ORDER BY Degree desc;

查询每门课的平均成绩
-- SELECT Cno, avg(Degree) from Score GROUP BY Cno;

查询Score表中至少有5名学生选修的并以3开头的课程的平均分数
-- SELECT Cno, count(*), avg(Degree) as avg_degree from Score GROUP BY Cno having count(*) >= 5 and Cno like '3%';

查询分数大于70，小于90的Sno列
-- SELECT Sno, Degree from Score where Degree > 70 and Degree < 90;

查询所有学生的Sno、Cname和Degree列
-- SELECT Score.Sno, Score.Degree, Course.Cname from Score JOIN Course on Score.Cno = Course.Cno;

查询所有学生的Sname、Cname和Degree列
-- SELECT Student.Sname, Course.Cname, Score.Degree from Student JOIN Score on Student.Sno = Score.Sno join Course on Score.Cno = Course.Cno;

查询“95033”班学生的平均分
SELECT avg(Degree) from Score where Score.Sno in (SELECT Sno from Student where Class = '95033');
SELECT avg(Degree) from Score join Student on Student.Sno = Score.Sno and Student.Class = '95033';

查询选修“3-105”课程的,并且成绩高于“109”号同学成绩的所有同学的记录
SELECT * FROM Student join Score on Student.Sno = Score.Sno and Score.Cno = '3-105' and Score.Degree > (SELECT Degree from Score where Score.Sno = '109' and Score.Cno = '3-105');

Practice makes perfect.