聚合函数
聚合函数是用来做纵向运算的函数:
COUNT():统计指定列不为NULL的记录行数;
SUM():计算指定列的数值和,如果指定列类型不是数值类型,那么计算结果为0;
count()函数中不能加表达式,加了表达式不会执行,表达式只能加到where后;
sun()函数中可以加表达式,但是加了表达式后会得到该列满足该表达式的非空个数统计和,和count()函数在后面加表达式的结果一样。
mysql> select sal from emp;
+---------+
| sal |
+---------+
| 800.00 |
| 1600.00 |
| 1250.00 |
| 2975.00 |
| 1250.00 |
| 2850.00 |
| 2450.00 |
| 3000.00 |
| 5000.00 |
| 1500.00 |
| 1100.00 |
| 950.00 |
| 3000.00 |
| 1300.00 |
+---------+
14 rows in set (0.00 sec)
mysql> select count(sal>2500) from emp;
+-----------------+
| count(sal>2500) |
+-----------------+
| 14 |
+-----------------+
1 row in set (0.00 sec)
mysql> select count(*) from emp where sal>2500;
+----------+
| count(*) |
+----------+
| 5 |
+----------+
1 row in set (0.00 sec)
mysql> select sum(sal) from emp where sal>2500;
+----------+
| sum(sal) |
+----------+
| 16825.00 |
+----------+
1 row in set (0.00 sec)
mysql> select sum(sal>2500) from emp;
+---------------+
| sum(sal>2500) |
+---------------+
| 5 |
+---------------+
1 row in set (0.00 sec)
mysql> select count(*) from emp where sal>2500;
+----------+
| count(*) |
+----------+
| 5 |
+----------+
1 row in set (0.00 sec)
mysql> select sum(sal) from emp where sal>2500;
+----------+
| sum(sal) |
+----------+
| 16825.00 |
+----------+
1 row in set (0.00 sec)
mysql> select sum(sal) from emp;
+----------+
| sum(sal) |
+----------+
| 29025.00 |
+----------+
1 row in set (0.00 sec)
mysql> select comm from emp;
+---------+
| comm |
+---------+
| NULL |
| 300.00 |
| 500.00 |
| NULL |
| 1400.00 |
| NULL |
| NULL |
| NULL |
| NULL |
| 0.00 |
| NULL |
| NULL |
| NULL |
| NULL |
+---------+
14 rows in set (0.00 sec)
mysql> select sum(comm>400) from emp;
+---------------+
| sum(comm>400) |
+---------------+
| 2 |
+---------------+
1 row in set (0.00 sec)
mysql> select sum(comm<400) from emp;
+---------------+
| sum(comm<400) |
+---------------+
| 2 |
+---------------+
1 row in set (0.00 sec)
mysql> select count(comm) from emp;
+-------------+
| count(comm) |
+-------------+
| 4 |
+-------------+
1 row in set (0.00 sec)
mysql> select count(comm) from emp where comm<400;
+-------------+
| count(comm) |
+-------------+
| 2 |
+-------------+
1 row in set (0.00 sec)