跟燕十八学习PHP-第二十五天-mysql"group by和having的综合练习"

/** 
燕十八 公益PHP培训 
课堂地址:YY频道88354001 
学习社区:www.zixue.it 
**/


mysql> create table result (
    -> name varchar(20),
    -> subject varchar(20),
    -> score tinyint
    -> )enigne myisam charset utf8;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds 


to your MySQL server version for the right syntax to use near 'enigne myisam charset utf8' 


at line 5
mysql> create table result (
    -> name varchar(20),
    -> subject varchar(20),
    -> score tinyint
    -> )engine myisam charset utf8;
Query OK, 0 rows affected (0.11 sec)


mysql> desc result;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name    | varchar(20) | YES  |     | NULL    |       |
| subject | varchar(20) | YES  |     | NULL    |       |
| score   | tinyint(4)  | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
3 rows in set (0.06 sec)


mysql> insert into result
    -> values
    -> ('张三','数学',90),
    -> ('张三','语文',50),
    -> ('张三','地理',40),
    -> ('李四','语文',55),
    -> ('李四','政治',45),
    -> ('王五','政治',30);
Query OK, 6 rows affected (0.03 sec)
Records: 6  Duplicates: 0  Warnings: 0


mysql> select * from result;
+------+---------+-------+
| name | subject | score |
+------+---------+-------+
| 张三     | 数学       |    90 |
| 张三     | 语文        |    50 |
| 张三     | 地理        |    40 |
| 李四     | 语文        |    55 |
| 李四     | 政治        |    45 |
| 王五     | 政治        |    30 |
+------+---------+-------+
6 rows in set (0.01 sec)


mysql> show create table result;
+--------+---------------------------------------------------------------------------------


----------------------------------------------------------------------------------------+
| Table  | Create Table                                                                      


                                                                                      |
+--------+---------------------------------------------------------------------------------


----------------------------------------------------------------------------------------+
| result | CREATE TABLE `result` (
  `name` varchar(20) DEFAULT NULL,
  `subject` varchar(20) DEFAULT NULL,
  `score` tinyint(4) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+--------+---------------------------------------------------------------------------------


----------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


mysql> #第一种错误
mysql> select name,avg(scocre) from result
    -> where score<60 group by name having count(*)>=2;
ERROR 1054 (42S22): Unknown column 'scocre' in 'field list'
mysql> select name,avg(score) from result
    -> where score<60 group by name having count(*)>=2;
+------+------------+
| name | avg(score) |
+------+------------+
| 张三     |    45.0000 |
| 李四     |    50.0000 |
+------+------------+
2 rows in set (0.00 sec)


mysql> select name,avg(score) from result
    -> where score<60 group by name;
+------+------------+
| name | avg(score) |
+------+------------+
| 张三     |    45.0000 |
| 李四     |    50.0000 |
| 王五     |    30.0000 |
+------+------------+
3 rows in set (0.00 sec)


mysql> select * from result;
+------+---------+-------+
| name | subject | score |
+------+---------+-------+
| 张三     | 数学       |    90 |
| 张三     | 语文        |    50 |
| 张三     | 地理        |    40 |
| 李四     | 语文        |    55 |
| 李四     | 政治        |    45 |
| 王五     | 政治        |    30 |
+------+---------+-------+
6 rows in set (0.00 sec)


mysql> select name,avg(score) from result
    -> group by name having count(score<60) >= 2;
+------+------------+
| name | avg(score) |
+------+------------+
| 张三     |    60.0000 |
| 李四     |    50.0000 |
+------+------------+
2 rows in set (0.00 sec)


mysql> #第二种错误
mysql> select name,avg(score),count(score<60) as gks 
    -> from result
    -> group by name having gks>=2;
+------+------------+-----+
| name | avg(score) | gks |
+------+------------+-----+
| 张三     |    60.0000 |   3 |
| 李四     |    50.0000 |   2 |
+------+------------+-----+
2 rows in set (0.00 sec)


mysql> #暴露他的错误
mysql> insert into  result 
    -> values 
    -> ('赵六','语文',100),
    -> ('赵六','数学',99),
    -> ('赵六','品德',98);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0


mysql> select * from result;
+------+---------+-------+
| name | subject | score |
+------+---------+-------+
| 张三     | 数学       |    90 |
| 张三     | 语文        |    50 |
| 张三     | 地理        |    40 |
| 李四     | 语文        |    55 |
| 李四     | 政治        |    45 |
| 王五     | 政治        |    30 |
| 赵六     | 语文        |   100 |
| 赵六     | 数学       |    99 |
| 赵六     | 品德       |    98 |
+------+---------+-------+
9 rows in set (0.00 sec)


mysql> select name,avg(score),count(score<60) as gks
    -> from result
    -> group by name having gks>=2;
+------+------------+-----+
| name | avg(score) | gks |
+------+------------+-----+
| 张三     |    60.0000 |   3 |
| 李四     |    50.0000 |   2 |
| 赵六     |    99.0000 |   3 |
+------+------------+-----+
3 rows in set (0.00 sec)


mysql> select count(1) from result;
+----------+
| count(1) |
+----------+
|        9 |
+----------+
1 row in set (0.03 sec)


mysql> select count(0) from result;
+----------+
| count(0) |
+----------+
|        9 |
+----------+
1 row in set (0.00 sec)


mysql> #正解
mysql> #第1步:我们就查询所有的平均分
mysql> select name,avg(score) from result group by name;
+------+------------+
| name | avg(score) |
+------+------------+
| 张三     |    60.0000 |
| 李四     |    50.0000 |
| 王五     |    30.0000 |
| 赵六     |    99.0000 |
+------+------------+
4 rows in set (0.03 sec)


mysql> #下一步,再想办法计算出每个人挂科的情况
mysql> select name,subject,score,score<60 as g from result;
+------+---------+-------+------+
| name | subject | score | g    |
+------+---------+-------+------+
| 张三     | 数学       |    90 |    0 |
| 张三     | 语文        |    50 |    1 |
| 张三     | 地理        |    40 |    1 |
| 李四     | 语文        |    55 |    1 |
| 李四     | 政治        |    45 |    1 |
| 王五     | 政治        |    30 |    1 |
| 赵六     | 语文        |   100 |    0 |
| 赵六     | 数学       |    99 |    0 |
| 赵六     | 品德       |    98 |    0 |
+------+---------+-------+------+
9 rows in set (0.00 sec)


mysql> #如上,挂科数目就是g的sum结果
mysql> #综合上面两个语句
mysql> select name,avg(score),sum(score<60)) as gks
    -> from result 
    -> group by name;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds 


to your MySQL server version for the right syntax to use near ') as gks
from result 
group by name' at line 1
mysql> select name,avg(score),sum(score<60) as gks
    -> from result
    -> group by name;
+------+------------+------+
| name | avg(score) | gks  |
+------+------------+------+
| 张三     |    60.0000 |    2 |
| 李四     |    50.0000 |    2 |
| 王五     |    30.0000 |    1 |
| 赵六     |    99.0000 |    0 |
+------+------------+------+
4 rows in set (0.00 sec)


mysql> #每个人的平均分及挂科数已算出,再having筛选一下即可.
mysql> select name,avg(score),sum(score<60) as gks
    -> from result
    -> group by name having gks>=2;
+------+------------+------+
| name | avg(score) | gks  |
+------+------------+------+
| 张三     |    60.0000 |    2 |
| 李四     |    50.0000 |    2 |
+------+------------+------+
2 rows in set (0.00 sec)


mysql> #这一个思路是逆向思维,先查出所有人的平均,再筛选.
mysql> #如果正常的考虑,我们可能会这样做:
mysql> #先找出谁的挂科数>=2,找到这些人,再求这些人的平均分
mysql> #先找挂科数>=2的那些人
mysql> select name from result where score<60;
+------+
| name |
+------+
| 张三     |
| 张三     |
| 李四     |
| 李四     |
| 王五     |
+------+
5 rows in set (0.01 sec)


mysql> select name,score from result where score<60;
+------+-------+
| name | score |
+------+-------+
| 张三     |    50 |
| 张三     |    40 |
| 李四     |    55 |
| 李四     |    45 |
| 王五     |    30 |
+------+-------+
5 rows in set (0.00 sec)


mysql> select name,count(1),score from result where score<60;
+------+----------+-------+
| name | count(1) | score |
+------+----------+-------+
| 张三     |        5 |    50 |
+------+----------+-------+
1 row in set (0.00 sec)


mysql> select name,count(1),score from result where score<60
    -> group by name;
+------+----------+-------+
| name | count(1) | score |
+------+----------+-------+
| 张三     |        2 |    50 |
| 李四     |        2 |    55 |
| 王五     |        1 |    30 |
+------+----------+-------+
3 rows in set (0.00 sec)


mysql> select name,count(1) from result where score<60
    -> group by name;
+------+----------+
| name | count(1) |
+------+----------+
| 张三     |        2 |
| 李四     |        2 |
| 王五     |        1 |
+------+----------+
3 rows in set (0.00 sec)


mysql> select name from result 
    -> where score<60 and count(1)>=2
    -> group by name;
ERROR 1111 (HY000): Invalid use of group function
mysql> select name,count(1) as gks from result 
    -> where score<60
    -> group by name
    -> having gks>=2;
+------+-----+
| name | gks |
+------+-----+
| 张三     |   2 |
| 李四     |   2 |
+------+-----+
2 rows in set (0.00 sec)


mysql> select name from  (select name,count(1) as gks from result
    -> where score<60
    -> group by name
    -> having gks>=2);
ERROR 1248 (42000): Every derived table must have its own alias
mysql> select name from  (select name,count(1) as gks from result
    -> where score<60
    -> group by name
    -> having gks>=2) as tmp;
+------+
| name |
+------+
| 张三     |
| 李四     |
+------+
2 rows in set (0.00 sec)


mysql> #最终3层嵌套的子查询
mysql> select name,avg(score)
    ->  from result
    -> where name in (select name from  (select name,count(1) as gks from result
    -> where score<60
    -> group by name
    -> having gks>=2) as tmp)
    -> group by name;
+------+------------+
| name | avg(score) |
+------+------------+
| 张三     |    60.0000 |
| 李四     |    50.0000 |
+------+------------+
2 rows in set (0.00 sec)


mysql> #这个例子也做出来了,但是用了子查询,且嵌套,非常麻烦.
mysql> #如果group having用的熟悉,一条sql就完成了. 不必嵌套子查询
mysql> exit






燕十八老师太幽默了, 昨天的视频如下:

http://www.tudou.com/programs/view/_5FNLYNPxzs/