怎么在MySQL中设计一个学生成绩表 用mysql设计一个学生表

创建学生表

CREATE TABLE `student`  (

  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '自动编号',

  `sno` bigint(20) NOT NULL COMMENT '学号',

  `name` varchar(15)  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 ;

插入数据

INSERT INTO `test`.`student`(`sno`, `name`, `sex`, `cno`, `score`) VALUES (8, 'zhangsan8', 1, 2, 99);

INSERT INTO `test`.`student`(`sno`, `name`, `sex`, `cno`, `score`) VALUES (9, 'zhangsan9', 1, 1, 63);

INSERT INTO `test`.`student`(`sno`, `name`, `sex`, `cno`, `score`) VALUES (10, 'zhangsan10', 1, 6, 47);

INSERT INTO `test`.`student`(`sno`, `name`, `sex`, `cno`, `score`) VALUES (11, 'zhangsan11', 1, 1, 67);

INSERT INTO `test`.`student`(`sno`, `name`, `sex`, `cno`, `score`) VALUES (12, 'zhangsan12', 1, 7, 37);

INSERT INTO `test`.`student`(`sno`, `name`, `sex`, `cno`, `score`) VALUES (13, 'zhangsan13', 1, 1, 85);

INSERT INTO `test`.`student`(`sno`, `name`, `sex`, `cno`, `score`) VALUES (14, 'zhangsan14', 1, 7, 98);

INSERT INTO `test`.`student`(`sno`, `name`, `sex`, `cno`, `score`) VALUES (15, 'zhangsan15', 1, 1, 63);

INSERT INTO `test`.`student`(`sno`, `name`, `sex`, `cno`, `score`) VALUES (16, 'zhangsan16', 1, 6, 39);

INSERT INTO `test`.`student`(`sno`, `name`, `sex`, `cno`, `score`) VALUES (17, 'zhangsan17', 1, 1, 80);

INSERT INTO `test`.`student`(`sno`, `name`, `sex`, `cno`, `score`) VALUES (18, 'zhangsan18', 1, 2, 99);

INSERT INTO `test`.`student`(`sno`, `name`, `sex`, `cno`, `score`) VALUES (19, 'zhangsan19', 1, 1, 73);

INSERT INTO `test`.`student`(`sno`, `name`, `sex`, `cno`, `score`) VALUES (21, 'zhangsan20', 1, 7, 53);

INSERT INTO `test`.`student`(`sno`, `name`, `sex`, `cno`, `score`) VALUES (22, 'zhangsan21', 1, 1, 47);

INSERT INTO `test`.`student`(`sno`, `name`, `sex`, `cno`, `score`) VALUES (23, 'zhangsan22', 1, 6, 85);

INSERT INTO `test`.`student`(`sno`, `name`, `sex`, `cno`, `score`) VALUES (24, 'zhangsan23', 1, 1, 64);

INSERT INTO `test`.`student`(`sno`, `name`, `sex`, `cno`, `score`) VALUES (25, 'zhangsan24', 1, 2, 53);

INSERT INTO `test`.`student`(`sno`, `name`, `sex`, `cno`, `score`) VALUES (26, 'zhangsan25', 1, 2, 44);

INSERT INTO `test`.`student`(`sno`, `name`, `sex`, `cno`, `score`) VALUES (27, 'zhangsan26', 1, 6, 77);

INSERT INTO `test`.`student`(`sno`, `name`, `sex`, `cno`, `score`) VALUES (28, 'zhangsan27', 1, 7, 58);

INSERT INTO `test`.`student`(`sno`, `name`, `sex`, `cno`, `score`) VALUES (29, 'zhangsan28', 1, 7, 97);

INSERT INTO `test`.`student`(`sno`, `name`, `sex`, `cno`, `score`) VALUES (30, 'zhangsan29', 1, 6, 68);

INSERT INTO `test`.`student`(`sno`, `name`, `sex`, `cno`, `score`) VALUES (31, 'zhangsan30', 1, 2, 59);

INSERT INTO `test`.`student`(`sno`, `name`, `sex`, `cno`, `score`) VALUES (32, 'zhangsan31', 1, 1, 38);

INSERT INTO `test`.`student`(`sno`, `name`, `sex`, `cno`, `score`) VALUES (33, 'zhangsan32', 1, 2, 74);

INSERT INTO `test`.`student`(`sno`, `name`, `sex`, `cno`, `score`) VALUES (34, 'zhangsan33', 1, 6, 66);

INSERT INTO `test`.`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` 
);