mysql 笔试题_mysql最爱考到的sql笔试题

数据库比表：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;

数据：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`

);