使用一个SQL查询出每门课程的成绩都大于80分的学生姓名
表名为student，字段和数据如下

用一条SQL语句查询出student表中每门功课都大于80分的学生姓名。
name    kecheng    fenshu
张三     语文          81
张三     数学          75
李四     语文          76
李四     数学          90
王五     语文          81
王五     数学          100
王五     英语          90

#建表
mysql> create table student(name char(20),kecheng varchar(20),fenshu int);

Query OK, 0 rows affected (0.06 sec)
mysql> alter table student  default  CHARACTER SET utf8//
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
###################################################
# 插入数据
mysql> insert into student values("张三","语文",81 );
Query OK, 1 row affected (0.01 sec)

mysql> insert into student values("张三","数学",75);
Query OK, 1 row affected (0.00 sec)

mysql> insert into student values("李四","语文",76 );
Query OK, 1 row affected (0.00 sec)

mysql> insert into student values("李四","数学",90 );
Query OK, 1 row affected (0.01 sec)

mysql> insert into student values("王五","数学",100 );
Query OK, 1 row affected (0.00 sec)

mysql> insert into student values("王五","英语",90 );
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+--------+---------+--------+
| name   | kecheng | fenshu |
+--------+---------+--------+
| 张三   | 语文    |     81 |
| 张三   | 数学    |     75 |
| 李四   | 语文    |     76 |
| 李四   | 数学    |     90 |
| 王五   | 数学    |    100 |
| 王五   | 英语    |     90 |
+--------+---------+--------+
####################################
##方法一 先分组然后用having过滤
mysql> select name from student as t  group by t.name having min(t.fenshu)>80;
+--------+
| name   |
+--------+
| 王五   |
+--------+
1 row in set (0.00 sec)
########################################
##方法二 
先求出小于80分的
mysql> select e.name  from student as e where fenshu<80;
+--------+
| name   |
+--------+
| 张三   |
| 李四   |
+--------+


然后用 not in 匹配
mysql>  select  d.name from student as d  where d.name  not in (select e.name  from student as e where fenshu<80) ;
+--------+
| name   |
+--------+
| 王五   |
| 王五   |
| 王五   |
+--------+


最后用distinct 去重
mysql>  select  distinct d.name from student as d  where d.name  not in (select e.name  from student as e where fenshu<80) ;
+--------+
| name   |
+--------+
| 王五   |
+--------+
1 row in set (0.01 sec)
########################################################
成功