having与子查询 查询各门课程超过80分的学生姓名

CREATE TABLE IF NOT EXISTS `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) NOT NULL,
  `subject` varchar(10) NOT NULL,
  `score` int(11) NOT NULL,
  PRIMARY KEY (`id`)
)

#--插入数据
INSERT INTO `student` (`id`, `name`, `subject`, `score`) VALUES
(1, '小明', '英文', 80),
(2, '小明', '数学', 79),
(3, '小明', '语文', 81),
(4, '小刚', '英文', 80),
(5, '小刚', '数学', 80),
(6, '小刚', '语文', 80),
(7, '小红', '英文', 90),
(8, '小红', '数学', 90),
(9, '小红', '语文', 81);

#方法一
select name from student group by name having in(score)>80

#方法二
select distinct name from student where name not in (
    select name from student where score<80
)

#方法三
select name from student where score>80 group by name having COUNT(*)>1