学步园

表结构及内容

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

SQL语句：

#方法一
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