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 |
+-----------+-------------+