- 排序
按照工资升序排： 默认升序 asc升序，desc降序
mysql> select ename,sal from emp order by sal;
+--------+---------+
| ename | sal |
+--------+---------+
| SMITH | 800.00 |
| JAMES | 950.00 |
| ADAMS | 1100.00 |
| WARD | 1250.00 |
| MARTIN | 1250.00 |
| MILLER | 1300.00 |
| TURNER | 1500.00 |
| ALLEN | 1600.00 |
| CLARK | 2450.00 |
| BLAKE | 2850.00 |
| JONES | 2975.00 |
| SCOTT | 3000.00 |
| FORD | 3000.00 |
| KING | 5000.00 |
+--------+---------+
14 rows in set (0.01 sec)
mysql> select ename,sal from emp order by sal desc;
+--------+---------+
| ename | sal |
+--------+---------+
| KING | 5000.00 |
| SCOTT | 3000.00 |
| FORD | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| ALLEN | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
| WARD | 1250.00 |
| MARTIN | 1250.00 |
| ADAMS | 1100.00 |
| JAMES | 950.00 |
| SMITH | 800.00 |
+--------+---------+
14 rows in set (0.00 sec)
按照工资降序 排列，当工资相同时，再按照名字的升序排列：
只有在前面字段相等无法排序时时，后面字段才开始排列
mysql> select ename,sal from emp order by sal desc ,ename asc ;
+--------+---------+
| ename | sal |
+--------+---------+
| KING | 5000.00 |
| FORD | 3000.00 |
| SCOTT | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| ALLEN | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
| MARTIN | 1250.00 |
| WARD | 1250.00 |
| ADAMS | 1100.00 |
| JAMES | 950.00 |
| SMITH | 800.00 |
+--------+---------+
14 rows in set (0.00 sec)
mysql> select ename,sal from emp order by 2 desc; 第二列 不建议
+--------+---------+
| ename | sal |
+--------+---------+
| KING | 5000.00 |
| SCOTT | 3000.00 |
| FORD | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| ALLEN | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
| WARD | 1250.00 |
| MARTIN | 1250.00 |
| ADAMS | 1100.00 |
| JAMES | 950.00 |
| SMITH | 800.00 |
+--------+---------+
14 rows in set (0.00 sec)
找出固定岗位的员工，按照工资排序：
mysql> select ename,sal,job from emp where job = 'salesman' order by sal desc;
+--------+---------+----------+
| ename | sal | job |
+--------+---------+----------+
| ALLEN | 1600.00 | SALESMAN |
| TURNER | 1500.00 | SALESMAN |
| WARD | 1250.00 | SALESMAN |
| MARTIN | 1250.00 | SALESMAN |
+--------+---------+----------+
4 rows in set (0.00 sec)
order by 最后执行
mysql> select ename,sal as sallll ,job from emp where job = 'salesman' order by sallll desc;
+--------+---------+----------+
| ename | sallll | job |
+--------+---------+----------+
| ALLEN | 1600.00 | SALESMAN |
| TURNER | 1500.00 | SALESMAN |
| WARD | 1250.00 | SALESMAN |
| MARTIN | 1250.00 | SALESMAN |
+--------+---------+----------+
4 rows in set (0.01 sec)
- 分组函数 多行处理函数：输入多行输出一行
count 计数 sum求和 avg平均数 max min，不能直接出现在where后面
找出员工的工资总和：
mysql> select sum(sal) from emp;
+----------+
| sum(sal) |
+----------+
| 29025.00 |
+----------+
1 row in set (0.01 sec)
找出最高工资：
mysql> select sum(sal) from emp;
+----------+
| sum(sal) |
+----------+
| 29025.00 |
+----------+
1 row in set (0.01 sec)
mysql> select count(ename) from emp;
+--------------+
| count(ename) |
+--------------+
| 14 |
+--------------+
1 row in set (0.01 sec)
分组函数自动忽略null
找出工资高于平均工资的员工：
分组函数不可直接使用在where字句当中，分组函数在groupby之后执行，groupby执行又在where后面执行
select...from...where...group by...having...order by..
5 1 2 3 4 6
mysql> select ename,sal from emp where sal > avg(sal);
ERROR 1111 (HY000): Invalid use of group function
第一步找出平均工资
第二步找出高于平均工资的员工
mysql> select avg(sal) from emp;
+-------------+
| avg(sal) |
+-------------+
| 2073.214286 |
+-------------+
1 row in set (0.00 sec)
mysql> select ename,sal from emp where sal>2073.214286;
+-------+---------+
| ename | sal |
+-------+---------+
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| KING | 5000.00 |
| FORD | 3000.00 |
+-------+---------+
6 rows in set (0.01 sec)
嵌套子语句
mysql> select ename,sal from emp where sal>(select avg(sal) from emp);
+-------+---------+
| ename | sal |
+-------+---------+
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| KING | 5000.00 |
| FORD | 3000.00 |
+-------+---------+
6 rows in set (0.01 sec)
count（）和count（具体的某个字段）?*
count（*）不是统计某个字段中的数字据个数，统计总记录条数（和字段无关）
count（具体的某个字段）表示统计（）字段中不为null的数据总量
mysql> select count(*) from emp;
+----------+
| count(*) |
+----------+
| 14 |
+----------+
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(*),sum(sal),avg(sal),max(sal),min(sal) from emp;
+----------+----------+-------------+----------+----------+
| count(*) | sum(sal) | avg(sal) | max(sal) | min(sal) |
+----------+----------+-------------+----------+----------+
| 14 | 29025.00 | 2073.214286 | 5000.00 | 800.00 |
+----------+----------+-------------+----------+----------+
1 row in set (0.00 sec)
select ename,
- 单行处理函数
计算每个员工年薪：只要有null参与运算，那么结果一定是null
史密斯：？？？？？？？
mysql> select ename,(sal+comm)*12 as yearsal from emp;
+--------+----------+
| ename | yearsal |
+--------+----------+
| SMITH | NULL |
| ALLEN | 22800.00 |
| WARD | 21000.00 |
| JONES | NULL |
| MARTIN | 31800.00 |
| BLAKE | NULL |
| CLARK | NULL |
| SCOTT | NULL |
| KING | NULL |
| TURNER | 18000.00 |
| ADAMS | NULL |
| JAMES | NULL |
| FORD | NULL |
| MILLER | NULL |
+--------+----------+
14 rows in set (0.01 sec)
ifnull()空处理函数 ifnull（可能为null的数据，被当作什么处理）
mysql> select ename,ifnull(comm,0) as comm from emp;
+--------+---------+
| ename | comm |
+--------+---------+
| SMITH | 0.00 |
| ALLEN | 300.00 |
| WARD | 500.00 |
| JONES | 0.00 |
| MARTIN | 1400.00 |
| BLAKE | 0.00 |
| CLARK | 0.00 |
| SCOTT | 0.00 |
| KING | 0.00 |
| TURNER | 0.00 |
| ADAMS | 0.00 |
| JAMES | 0.00 |
| FORD | 0.00 |
| MILLER | 0.00 |
+--------+---------+
14 rows in set (0.00 sec)
mysql> select ename,(sal+ifnull(comm,0))*12 as yearsal from emp;
+--------+----------+
| ename | yearsal |
+--------+----------+
| SMITH | 9600.00 |
| ALLEN | 22800.00 |
| WARD | 21000.00 |
| JONES | 35700.00 |
| MARTIN | 31800.00 |
| BLAKE | 34200.00 |
| CLARK | 29400.00 |
| SCOTT | 36000.00 |
| KING | 60000.00 |
| TURNER | 18000.00 |
| ADAMS | 13200.00 |
| JAMES | 11400.00 |
| FORD | 36000.00 |
| MILLER | 15600.00 |
+--------+----------+
14 rows in set (0.00 sec)
分组函数自动忽略null
mysql> select sum(comm) from emp;
+-----------+
| sum(comm) |
+-----------+
| 2200.00 |
+-----------+
1 row in set (0.00 sec)
- group by 和 having
group by：按照摸个字段或者某些字段进行分组(分组函数一般和groupby联合使用)，当一条sql语句没有groupby时，整张表自成一组，分组函数在groupbu之后执行。 having ： 对分组之后的数据进行再次过滤
找出每个岗位最高薪资：
select max(sal) from emp group by job;
mysql> select max(sal) from emp group by job;
+----------+
| max(sal) |
+----------+
| 3000.00 |
| 1300.00 |
| 2975.00 |
| 5000.00 |
| 1600.00 |
+----------+
5 rows in set (0.01 sec)
22
