创建表并插入基础数据

DROP TABLE IF EXISTS `student2`;
CREATE TABLE `student2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `course` varchar(255) DEFAULT NULL,
  `score` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of student2
-- ----------------------------
INSERT INTO `student2` VALUES ('1', '张三', '语文', '81');
INSERT INTO `student2` VALUES ('2', '张三', '数学', '75');
INSERT INTO `student2` VALUES ('3', '李四', '语文', '76');
INSERT INTO `student2` VALUES ('4', '李四', '数学', '90');
INSERT INTO `student2` VALUES ('5', '王五', '语文', '81');
INSERT INTO `student2` VALUES ('6', '王五', '数学', '100');
INSERT INTO `student2` VALUES ('7', '王五', '英语', '90');

Solution 1 :

select name from student2 GROUP BY name HAVING min(score)>80;

group by 根据name 对数据分组
min(score)>80 分组后，分组中分数最低分数大于80，表明该学生所有成绩都大于80

Solution 2:

select distinct name from student2 where name not in(select distinct name from student2 where score <= 80);

采用逆向思维
select distinct name from student2 where score <= 80 查找出分数低于80的学生
not in 不存在分数低于80的学生的列表里，就是每门课成绩大于80的学生姓名

Solution 3:

select name,count(id) as number from student2 group by name;      p1
 
select name,sum(score>80) as number from student2 group by name;  p2

select p1.name from (select name,count(id) as number from student2 group by name) p1 ,(select name,sum(score>80) as number from student2 group by name) p2 where p1.name = p2.name and p1.number = p2.number;