Mysql学习（四）

Mysql学习（四）

使用聚合函数来进行数据查询

1.COUNT()函数

1.COUNT()函数用来统计记录的条数；

mysql> SELECT COUNT(*) FROM t_grade;

+----------+

| COUNT(*) |

+----------+

|        9 |

+----------+

1 row in set

 

2.与GOUPE BY 关键字一起使用

mysql> SELECT stuName,COUNT(*) FROM t_grade GROUP BY stuName;

+---------+----------+

| stuName | COUNT(*) |

+---------+----------+

| 李四    |        3 |

| 王五    |        3 |

| 张三    |        3 |

+---------+----------+

3 rows in set

 

2.SUN()函数

1.SUN()函数是求和函数

mysql> SELECT stuName,SUM(score) FROM t_grade WHERE stuName="张三";

+---------+------------+

| stuName | SUM(score) |

+---------+------------+

| 张三    |        237 |

+---------+------------+

1 row in set

 

2.与GROUP BY一起使用

mysql> SELECT stuName ,SUM(score) AS '总分' FROM t_grade GROUP BY stuName;

+---------+------+

| stuName | 总分 |

+---------+------+

| 李四    |  231 |

| 王五    |  230 |

| 张三    |  237 |

+---------+------+

3 rows in set

 

3.AVG()函数

1.AVG()函数是求平均值函数

mysql> SELECT stuName ,AVG(score) FROM t_grade WHERE stuName="张三";

+---------+------------+

| stuName | AVG(score) |

+---------+------------+

| 张三    |         79 |

+---------+------------+

1 row in set

 

2.与GROUP BY一起使用

 

mysql> SELECT stuName ,AVG(score) FROM t_grade GROUP BY stuName;

+---------+-------------------+

| stuName | AVG(score)        |

+---------+-------------------+

| 李四    |                77 |

| 王五    | 76.66666666666667 |

| 张三    |                79 |

+---------+-------------------+

3 rows in set

 

4.MAX()函数

1.MAX()函数是求最大值

mysql> SELECT stuName ,course,MAX(score) FROM t_grade WHERE stuName="张三";

+---------+--------+------------+

| stuName | course | MAX(score) |

+---------+--------+------------+

| 张三    | 语文   |         91 |

+---------+--------+------------+

1 row in set

 

2.与GROUP BY 一起使用

mysql> SELECT stuName,MAX(score) FROM t_grade

 GROUP BY stuName;

+---------+------------+

| stuName | MAX(score) |

+---------+------------+

| 李四    |         87 |

| 王五    |         98 |

| 张三    |         91 |

+---------+------------+

3 rows in set

 

5.MIN()函数

1.MAX()函数是求最小值

mysql> SELECT stuName ,course,MIN

(score) FROM t_grade WHERE stuName="张三";

+---------+--------+------------+

| stuName | course | MIN(score) |

+---------+--------+------------+

| 张三    | 语文   |         59 |

+---------+--------+------------+

1 row in set

 

2.与GROUP BY 一起使用

mysql> SELECT stuName,MIN

(score) FROM t_grade GROUP BY stuName;

+---------+------------+

| stuName | MIN(score) |

+---------+------------+

| 李四    |         61 |

| 王五    |         60 |

| 张三    |         59 |

+---------+------------+

3 rows in set