MySQL常考学生成绩查询练习题

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104

/*
 Navicat Premium Data Transfer

 Source Server         : root
 Source Server Type    : MySQL
 Source Server Version : 50726
 Source Host           : localhost:3306
 Source Schema         : xuexiao

 Target Server Type    : MySQL
 Target Server Version : 50726
 File Encoding         : 65001

 Date: 08/05/2020 16:30:45
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for t_class
-- ----------------------------
DROP TABLE IF EXISTS `t_class`;
CREATE TABLE `t_class`  (
  `classid` int(10) NOT NULL,
  `classname` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`classid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of t_class
-- ----------------------------
INSERT INTO `t_class` VALUES (1701, '一班');
INSERT INTO `t_class` VALUES (1702, '二班');
INSERT INTO `t_class` VALUES (1703, '三班');

-- ----------------------------
-- Table structure for t_course
-- ----------------------------
DROP TABLE IF EXISTS `t_course`;
CREATE TABLE `t_course`  (
  `cid` int(10) NOT NULL,
  `cname` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`cid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of t_course
-- ----------------------------
INSERT INTO `t_course` VALUES (101, 'MySQL');
INSERT INTO `t_course` VALUES (102, '网络');
INSERT INTO `t_course` VALUES (103, '系统');

-- ----------------------------
-- Table structure for t_score
-- ----------------------------
DROP TABLE IF EXISTS `t_score`;
CREATE TABLE `t_score`  (
  `sid` int(10) NULL DEFAULT NULL,
  `cid` int(10) NULL DEFAULT NULL,
  `score` int(3) NULL DEFAULT NULL,
  INDEX `s_id`(`sid`) USING BTREE,
  INDEX `c_id`(`cid`) USING BTREE,
  CONSTRAINT `c_id` FOREIGN KEY (`cid`) REFERENCES `t_course` (`cid`) ON DELETE NO ACTION ON UPDATE CASCADE,
  CONSTRAINT `s_id` FOREIGN KEY (`sid`) REFERENCES `t_student` (`sid`) ON DELETE NO ACTION ON UPDATE CASCADE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of t_score
-- ----------------------------
INSERT INTO `t_score` VALUES (10001, 101, 59);
INSERT INTO `t_score` VALUES (10001, 102, 66);
INSERT INTO `t_score` VALUES (10001, 103, 80);
INSERT INTO `t_score` VALUES (10002, 101, 79);
INSERT INTO `t_score` VALUES (10002, 102, 50);
INSERT INTO `t_score` VALUES (10002, 103, 82);
INSERT INTO `t_score` VALUES (10003, 101, 30);
INSERT INTO `t_score` VALUES (10003, 102, 84);
INSERT INTO `t_score` VALUES (10003, 103, 65);

-- ----------------------------
-- Table structure for t_student
-- ----------------------------
DROP TABLE IF EXISTS `t_student`;
CREATE TABLE `t_student`  (
  `sid` int(10) NOT NULL,
  `sname` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `classid` int(10) NULL DEFAULT NULL,
  PRIMARY KEY (`sid`) USING BTREE,
  INDEX `class_id`(`classid`) USING BTREE,
  CONSTRAINT `class_id` FOREIGN KEY (`classid`) REFERENCES `t_class` (`classid`) ON DELETE NO ACTION ON UPDATE CASCADE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of t_student
-- ----------------------------
INSERT INTO `t_student` VALUES (10001, '张三', 1701);
INSERT INTO `t_student` VALUES (10002, '李四', 1701);
INSERT INTO `t_student` VALUES (10003, '王五', 1702);
INSERT INTO `t_student` VALUES (10004, '赵六', 1702);
INSERT INTO `t_student` VALUES (10005, '孙七', 1703);
INSERT INTO `t_student` VALUES (10006, '王八', 1703);

SET FOREIGN_KEY_CHECKS = 1;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

SELECT
	d.classid,
	a.sid,
	a.sname,
	SUM( c.score ) totalScore,
	COUNT( c.cid ) courseNum 
FROM
	t_student a,
	t_course b,
	t_score c,
	t_class d 
WHERE
	a.sid = c.sid 
	AND b.cid = c.cid 
	AND a.classid = d.classid 
GROUP BY
	a.sid

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

SELECT
	d.classid,
	a.sid,
	a.sname,
	SUM( c.score ) totalScore 
FROM
	t_student a,
	t_course b,
	t_score c,
	t_class d 
WHERE
	a.sid = c.sid 
	AND b.cid = c.cid 
	AND a.classid = d.classid 
GROUP BY
	a.sid 
HAVING
	( SUM( c.score ) > 180 )

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

SELECT
	e.classid,
	COUNT( classid ) stuNum 
FROM
	(
SELECT
	bj.classid,
	xs.sid,
	SUM( cj.score ) totalScore 
FROM
	t_student xs,
	t_course kc,
	t_score cj,
	t_class bj 
WHERE
	xs.sid = cj.sid 
	AND kc.cid = cj.cid 
	AND xs.classid = bj.classid 
GROUP BY
	xs.sid 
HAVING
	( totalScore > 180 ) 
	) e

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

SELECT
	e.classid,
	stu.sname,
	MAX(e.totalScore) maxScore
FROM
	t_student stu
	INNER JOIN (
SELECT
	bj.classid,
	xs.sid,
	SUM( cj.score ) totalScore 
FROM
	t_student xs,
	t_course kc,
	t_score cj,
	t_class bj 
WHERE
	xs.sid = cj.sid 
	AND kc.cid = cj.cid 
	AND xs.classid = bj.classid 
GROUP BY
	xs.sid 
	) e ON stu.sid = e.sid
GROUP BY
	classid