数据库比表：

CREATE TABLE `demo`.`student`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '自动编号',
  `sno` bigint(20) NOT NULL COMMENT '学号',
  `name` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '学生姓名',
  `sex` int(2) NULL DEFAULT NULL COMMENT '1-男，2-女',
  `cno` bigint(20) NULL DEFAULT NULL COMMENT '课程号',
  `score` float(64, 0) NULL DEFAULT NULL COMMENT '分数',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 36 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Compact;

数据：

INSERT INTO `demo`.`student`(`sno`, `name`, `sex`, `cno`, `score`) VALUES (8, 'zhangsan8', 1, 2, 99);
INSERT INTO `demo`.`student`(`sno`, `name`, `sex`, `cno`, `score`) VALUES (9, 'zhangsan9', 1, 1, 63);
INSERT INTO `demo`.`student`(`sno`, `name`, `sex`, `cno`, `score`) VALUES (10, 'zhangsan10', 1, 6, 47);
INSERT INTO `demo`.`student`(`sno`, `name`, `sex`, `cno`, `score`) VALUES (11, 'zhangsan11', 1, 1, 67);
INSERT INTO `demo`.`student`(`sno`, `name`, `sex`, `cno`, `score`) VALUES (12, 'zhangsan12', 1, 7, 37);
INSERT INTO `demo`.`student`(`sno`, `name`, `sex`, `cno`, `score`) VALUES (13, 'zhangsan13', 1, 1, 85);
INSERT INTO `demo`.`student`(`sno`, `name`, `sex`, `cno`, `score`) VALUES (14, 'zhangsan14', 1, 7, 98);
INSERT INTO `demo`.`student`(`sno`, `name`, `sex`, `cno`, `score`) VALUES (15, 'zhangsan15', 1, 1, 63);
INSERT INTO `demo`.`student`(`sno`, `name`, `sex`, `cno`, `score`) VALUES (16, 'zhangsan16', 1, 6, 39);
INSERT INTO `demo`.`student`(`sno`, `name`, `sex`, `cno`, `score`) VALUES (17, 'zhangsan17', 1, 1, 80);
INSERT INTO `demo`.`student`(`sno`, `name`, `sex`, `cno`, `score`) VALUES (18, 'zhangsan18', 1, 2, 99);
INSERT INTO `demo`.`student`(`sno`, `name`, `sex`, `cno`, `score`) VALUES (19, 'zhangsan19', 1, 1, 73);
INSERT INTO `demo`.`student`(`sno`, `name`, `sex`, `cno`, `score`) VALUES (21, 'zhangsan20', 1, 7, 53);
INSERT INTO `demo`.`student`(`sno`, `name`, `sex`, `cno`, `score`) VALUES (22, 'zhangsan21', 1, 1, 47);
INSERT INTO `demo`.`student`(`sno`, `name`, `sex`, `cno`, `score`) VALUES (23, 'zhangsan22', 1, 6, 85);
INSERT INTO `demo`.`student`(`sno`, `name`, `sex`, `cno`, `score`) VALUES (24, 'zhangsan23', 1, 1, 64);
INSERT INTO `demo`.`student`(`sno`, `name`, `sex`, `cno`, `score`) VALUES (25, 'zhangsan24', 1, 2, 53);
INSERT INTO `demo`.`student`(`sno`, `name`, `sex`, `cno`, `score`) VALUES (26, 'zhangsan25', 1, 2, 44);
INSERT INTO `demo`.`student`(`sno`, `name`, `sex`, `cno`, `score`) VALUES (27, 'zhangsan26', 1, 6, 77);
INSERT INTO `demo`.`student`(`sno`, `name`, `sex`, `cno`, `score`) VALUES (28, 'zhangsan27', 1, 7, 58);
INSERT INTO `demo`.`student`(`sno`, `name`, `sex`, `cno`, `score`) VALUES (29, 'zhangsan28', 1, 7, 97);
INSERT INTO `demo`.`student`(`sno`, `name`, `sex`, `cno`, `score`) VALUES (30, 'zhangsan29', 1, 6, 68);
INSERT INTO `demo`.`student`(`sno`, `name`, `sex`, `cno`, `score`) VALUES (31, 'zhangsan30', 1, 2, 59);
INSERT INTO `demo`.`student`(`sno`, `name`, `sex`, `cno`, `score`) VALUES (32, 'zhangsan31', 1, 1, 38);
INSERT INTO `demo`.`student`(`sno`, `name`, `sex`, `cno`, `score`) VALUES (33, 'zhangsan32', 1, 2, 74);
INSERT INTO `demo`.`student`(`sno`, `name`, `sex`, `cno`, `score`) VALUES (34, 'zhangsan33', 1, 6, 66);
INSERT INTO `demo`.`student`(`sno`, `name`, `sex`, `cno`, `score`) VALUES (35, 'zhangsan34', 1, 7, 89);

查询：

1. 查询每们课程的最高分、最低分、平均分

SELECT
	t.cno,
	MAX( t.score ),
	min(t.score ),
	AVG(t.score )
FROM
	student t 
GROUP BY
	t.cno;

2. 查询每门课的最高分学生信息

SELECT
  temp.cno,
	s.sno,
	s.`name`,
	temp.maxScore
FROM
	student s
	INNER JOIN ( SELECT s.cno, MAX( s.score ) maxScore FROM student s GROUP BY s.cno ) temp on s.cno = temp.cno
	WHERE s.score = temp.maxScore

3. 查查询每个本门的前三名

SELECT
	e1.* 
FROM
	student e1 
WHERE
	( SELECT count( 1 ) FROM student e2 WHERE e2.cno = e1.cno AND e2.score >= e1.score ) <= 3 
ORDER BY
	cno,
	score DESC;

4. 查询每门课程都大于80分的学生信息
 

SELECT * FROM student s GROUP BY s.`name` having min(s.score) > 80

5. 删除自动编编号不同其他信息相同的学生

DELETE 
FROM
	student 
WHERE
	id NOT IN (
SELECT
	min( temp.id ) AS id 
FROM
	( SELECT * FROM student s ) AS temp 
GROUP BY
	temp.cno,
	temp.sno,
	temp.score,
	temp.sno,
	temp.`name` 
);