Mysql表格操作——Group By

Group by（查询时或者插入数据时以什么属性作为参照进行分类） 

先来看库中所存数据

mysql> select * from student;
+-----------+-------+------+------+-------+
| Sno       | Sname | Ssex | Sage | Sdept |
+-----------+-------+------+------+-------+
| 201215121 | 李勇  | 男   |   20 | CS    |
| 201215122 | 刘晨  | 女   |   19 | CS    |
| 201215123 | 王敏  | 女   |   18 | MA    |
| 201215124 | 张立  | 男   |   19 | IS    |
+-----------+-------+------+------+-------+

第一种建表方法：

mysql> insert into Dept_age(Sdept,Avg_age)
    -> Select Sdept,AVG(Sage)
    -> From Student
    -> Group by Sdept;
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

查看表格内容：

mysql> select * from dept_age;
+-------+---------+
| Sdept | Avg_age |
+-------+---------+
| CS    |      20 |
| MA    |      18 |
| IS    |      19 |
+-------+---------+

可以发现这一次的平均年龄是按照院系名进行求解的。

假如根据去掉Group by Sdept，按照如下方式建表，

mysql> insert into dept_age(Sdept,avg_age)
    -> select sdept,avg(sage)
    -> from student
    -> ;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

所求的内容就变成了

mysql> select * from dept_age;
+-------+---------+
| Sdept | Avg_age |
+-------+---------+
| CS    |      19 |
+-------+---------+
1 row in set (0.00 sec)

SQL自动将所有的内容进行了求和后取平均值，未考虑院系的问题。