MySQL sql select

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `grade`
-- ----------------------------
DROP TABLE IF EXISTS `grade`;
CREATE TABLE `grade` (
`id`  bigint(20) NOT NULL AUTO_INCREMENT ,
`name`  varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`subject`  varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`score`  decimal(5,2) NULL DEFAULT NULL ,
PRIMARY KEY (`id`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci

;

-- ----------------------------
-- Records of grade
-- ----------------------------
BEGIN;
INSERT INTO `grade` VALUES ('1', '小明', '数学', '95.00'), ('2', '小李', '数学', '96.00'), ('3', '小红', '数学', '88.00'), ('4', '小绿', '数学', '92.00'), ('5', '小莫', '语文', '92.00'), ('6', '小燕', '语文', '82.00'), ('7', '小婷', '语文', '85.00'), ('8', '小珍', '英语', '68.00'), ('9', '小小', '英语', '78.00'), ('10', '小雪', '英语', '88.00'), ('11', '小张', '数学', '96.00');
COMMIT;

## 每一科成绩最高的分数以及这个学生的名字
select b.* from (select subject,max(score) m from grade GROUP BY subject) t,grade b 
where t.subject=b.subject and t.m=b.score;

## 用一句SQL查出所有课程成绩最高和最低的学生及其分数。
select b.* from (select subject,max(score) m from grade GROUP BY subject) t,grade b 
where t.subject=b.subject and t.m=b.score UNION
select b.* from (select subject,min(score) m from grade GROUP BY subject) t,grade b 
where t.subject=b.subject and t.m=b.score;

## 查询每门课程都大于80分的学生姓名
select name from grade group by name  having min(score)>80;