mysql入门(分组查询八)

1:单行处理

 1：sum求和

mysql> select sum(sal) from emp;
+----------+
| sum(sal) |
+----------+
| 29025.00 |
+----------+
1 row in set (0.00 sec)

2：avg:求平均值
mysql> select avg(sal) from emp;
+-------------+
| avg(sal)    |
+-------------+
| 2073.214286 |
+-------------+
1 row in set (0.00 sec)

3：求最大值
mysql> select max(sal) from emp;
+----------+
| max(sal) |
+----------+
|  5000.00 |
+----------+
1 row in set (0.17 sec)

4:求最小值
mysql> select min(sal) from emp;

5；统计查询(过滤掉空的记录)

 select count(ename) from emp;
+--------------+
| count(ename) |
+--------------+
|           14 |
+--------------+
1 row in set (0.00 sec)


mysql> select count(comm) from emp;
+-------------+
| count(comm) |
+-------------+
|           3 |
+-------------+
1 row in set (0.00 sec)

6:在查询结果中去重(distinct)

select job from emp;
+-----------+
| job       |
+-----------+
| CLERK     |
| SALESMAN  |
| SALESMAN  |
| MANAGER   |
| SALESMAN  |
| MANAGER   |
| MANAGERAN |
| ANALIST   |
| PRESIDENT |
| SALESMAN  |
| CLECK     |
| CLECK     |
| ANALYST   |
| CLERY     |
+-----------+
14 rows in set (0.00 sec)


mysql> select distinct job from emp;
+-----------+
| job       |
+-----------+
| CLERK     |
| SALESMAN  |
| MANAGER   |
| MANAGERAN |
| ANALIST   |
| PRESIDENT |
| CLECK     |
| ANALYST   |
| CLERY     |
+-----------+
9 rows in set (0.00 sec)

7:分组查询(group by,having) 案例:查询每个岗位的最高薪水)[先按照每个岗位分组，使用max求每组的最大值)

   select job,avg(sal) from emp  group by job having avg(sal)>1500;
+-----------+-------------+
| job       | avg(sal)    |
+-----------+-------------+
| ANALIST   | 3000.000000 |
| ANALYST   | 3000.000000 |
| MANAGER   | 2912.500000 |
| MANAGERAN | 2450.000000 |
| PRESIDENT | 5000.000000 |
+-----------+-------------+