MySQL基础-3

id	stuName	course	score
1	张三	语文	91
2	张三	数学	90
3	张三	英语	87
4	李四	语文	79
5	李四	数学	95
6	李四	英语	80
7	王五	语文	77
8	王五	数学	81
9	王五	英语	89

count（）函数

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

mysql> select count(*) as total from t_grade;
+-------+
| total |
+-------+
|     9 |
+-------+
1 row in set (0.00 sec)

count和group up 一起使用

mysql> select count(*) as total from t_grade group by stuName;
+-------+
| total |
+-------+
|     3 |
|     3 |
|     3 |
+-------+
3 rows in set (0.00 sec)

sum（）函数

mysql> select stuName,sum(score) from t_grade where stuName='张三';
+---------+------------+
| stuName | sum(score) |
+---------+------------+
| 张三    |        268 |
+---------+------------+
1 row in set (0.00 sec)

sum和group up 一起使用

mysql> select stuName,sum(score) from t_grade group by stuName;
+---------+------------+
| stuName | sum(score) |
+---------+------------+
| 张三    |        268 |
| 李四    |        254 |
| 王五    |        247 |
+---------+------------+
3 rows in set (0.00 sec)

avg()函数

mysql> select stuName,avg(score) from t_grade where stuName='张三';
+---------+-------------------+
| stuName | avg(score)        |
+---------+-------------------+
| 张三    | 89.33333333333333 |
+---------+-------------------+
1 row in set (0.00 sec)

avg和group up 一起使用

mysql> select stuName,avg(score) from t_grade group by stuName;
+---------+-------------------+
| stuName | avg(score)        |
+---------+-------------------+
| 张三    | 89.33333333333333 |
| 李四    | 84.66666666666667 |
| 王五    | 82.33333333333333 |
+---------+-------------------+
3 rows in set (0.00 sec)

max和min同理

mysql> select stuName,max(score) from t_grade where stuName='张三';
+---------+------------+
| stuName | max(score) |
+---------+------------+
| 张三    | 91         |
+---------+------------+
1 row in set (0.00 sec)

mysql> select stuName,max(score) from t_grade group by stuName;
+---------+------------+
| stuName | max(score) |
+---------+------------+
| 张三    | 91         |
| 李四    | 95         |
| 王五    | 89         |
+---------+------------+
3 rows in set (0.00 sec)

但不显示课程有点坑爹