Mysql常用面试题

创建数据库：
CREATE TABLE demo.Untitled (
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;

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
   );

摘自：http://www.zuidaima.com/blog/4420967603538944.htm