1、排序(升序、降序)
按照工资升序,找出员工名和薪资?
select
ename,sal
from
emp
order by
sal;
注意:默认为升序。
如何指定升序或者降序呢? asc为升序,desc表示降序。
select ename , sal from emp order by sal;
select ename , sal from emp order by sal asc; /*升序*/
select ename , sal from emp order by sal desc; /*降序*/
mysql> select ename,sal from emp order by sal desc;
/*找出emp表中字段为ename,sal的字段,并且按照薪资值降序*/
+--------+---------+
| 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)
/*按照工资的降序排列,当工资相同的时候在按照名字的升序排列*/
select ename , sal from emp order by sal desc,ename asc;
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)
按照工资的降序排列,当工资相同的时候在按照名字的升序排列
select ename , sal from emp order by sal desc,ename asc;
多个字段一起排序时,越靠前的字段决定因素越大,后面的可能都用不上。
select ename,sal from emp order by 1;
order by后面跟一个数组,表示是根据表的第几列字段来排序
找出工作岗位是SALESMAN的员工,并且要求按照薪资的降序排序列
select ename , job, sal from emp where job = ‘SALESMAN’ order by sal desc;
mysql> select ename , job, sal from emp where job = 'SALESMAN' order by sal desc;
+--------+----------+---------+
| ename | job | sal |
+--------+----------+---------+
| ALLEN | SALESMAN | 1600.00 |
| TURNER | SALESMAN | 1500.00 |
| WARD | SALESMAN | 1250.00 |
| MARTIN | SALESMAN | 1250.00 |
+--------+----------+---------+
4 rows in set (0.00 sec)
查找排序格式 执行顺序
select
XXX 3
from
tablename 1
where 筛选
条件 2
order by
… 4
执行顺序是: 1找到对应表,2运行对应条件,3找出需要的相应字段,4最后执行是否排序。
2、单行处理函数
什么是单行处理函数?
输入一行,输出一行。
计算每个员工的年薪
select ename,sal*12 as yearSal from emp order by yearSal desc;
select ename,(sal+comm)*12 as yearSal from emp order by yearSal desc;
重点:所有数据库都是这样规定的,只要有NULL参与的运算结果一定是NULL。
ifnull(可能为NULL的数据,被当做什么处理。:属于单行处理函数
ifnull(comm,0)
计算每个员工的年薪
select ename,sal*12 as yearSal from emp order by yearSal desc;
select ename,(sal+comm)*12 as yearSal from emp order by yearSal desc;
/*正确*/
select ename,(sal+ifnull(comm,0))*12 as yearSal from emp order by yearSal desc;
mysql> select ename,(sal+ifnull(comm,0))*12 as yearSal from emp order by yearSal desc;
+--------+----------+
| ename | yearSal |
+--------+----------+
| KING | 60000.00 |
| SCOTT | 36000.00 |
| FORD | 36000.00 |
| JONES | 35700.00 |
| BLAKE | 34200.00 |
| MARTIN | 31800.00 |
| CLARK | 29400.00 |
| ALLEN | 22800.00 |
| WARD | 21000.00 |
| TURNER | 18000.00 |
| MILLER | 15600.00 |
| ADAMS | 13200.00 |
| JAMES | 11400.00 |
| SMITH | 9600.00 |
+--------+----------+
14 rows in set (0.00 sec)
3、分组函数
函数名 | 功能 |
---|---|
count | 计数 |
sum | 求和 |
avg | 平均值 |
max | 最大值 |
min | 最小值 |
**注意:**所有的分数函数都是对“某一组”数据进行操作的。
分组函数一共5个,
分组函数还有另一个名字:多行处理函数
多行处理函数的特点是:输入多行,最终输出的结果是一行
例如:
select count(comm) from emp;
mysql> select count(comm) from emp;
+-------------+
| count(comm) |
+-------------+
| 4 |
+-------------+
1 row in set (0.0 sec)
注意:
分组函数自动忽略null
为什么分组函数不可直接使用在where子句当中?(无效使用分组函数)
例如:select ename,sal from emp where sal > avg(sal); //错误
答:因为group by是在where子句执行之后才会执行,所以没分组时不能使用avg分组函数。
count(*)和count(具体某个字段),他们有什么区别?
count(*): 不是统计某个字段中数据的个数,而是统计总记录条数(和某个字段无关)
count(comm): 表示统计comm字段中不为NULL的数据总数量。
练习:
/*统计薪资的总和*/
select sum(sal) from emp;
/*select sum(sal) as sumsal from emp;*/
mysql> select sum(sal) as sumsal from emp;
+----------+
| sumsal |
+----------+
| 29025.00 |
+----------+
1 row in set (0.00 sec)
/*找出总人数*/
select count(*) from emp;
mysql> select count(*) from emp;
+----------+
| count(*) |
+----------+
| 14 |
+----------+
1 row in set (0.00 sec)
/*找出工资高于平均工资的员工*/
select avg(sal) from emp;/
mysql> select avg(sal) from emp;//平均工资
+-------------+
| avg(sal) |
+-------------+
| 2073.214286 |
+-------------+
1 row in set (0.00 sec)
select ename,sal from emp where sal > (select avg (sal) from emp);//子查询
分组函数也可以组合起来用
4、group by 和 having
group by:按照某个字段或者某些字段进行分组。
having:having是对分组之后的数据进行再次过滤
案例:找出每个工作岗位的最高薪资
select job,max(sal) from emp group by job;
mysql> select job,max(sal) from emp group by job;
+-----------+----------+
| job | max(sal) |
+-----------+----------+
| CLERK | 1300.00 |
| SALESMAN | 1600.00 |
| MANAGER | 2975.00 |
| ANALYST | 3000.00 |
| PRESIDENT | 5000.00 |
+-----------+----------+
5 rows in set (0.00 sec)
注意:分组函数一般都会和group by联合使用。
任何一个分组函数,都是在group by语句执行结束之后才会执行。
当语句中没有group by时,整张表的数据会自成一组。(会有一个缺省的group by)
执行顺序:
语句 | 执行优先级 |
---|---|
select … | 5 |
from … | 1 |
where … | 2 |
group by … | 3 |
having … | 4 |
order by … | 6 |
select ename, job,max(sal) from emp group by job;
以上在mysql当中,查询结果是有的,但是结果没有意义,在Oracle数据库当中会报错,语法错误
Oracle的语法规则比MySQL更加严谨。
规则:当一条语句中有 group by 的话,select后面就只能跟分组函数和参与分组的字段。
/*每个工作岗位的平均薪资*/
select job,avg(sal) from emp group by job;
联合分组
多个字段能不能联合起来一块来分组?
案例:找出每个部门不同工作岗位的最高薪资。
select deptno,job,max(sal) from emp group by deptno,job;
mysql> select deptno,job,max(sal) from emp group by deptno,job;
/*先按照部门号进行分组,然后在对各个分组内的岗位进行分组,最后求出最大值*/
+--------+-----------+----------+
| deptno | job | max(sal) |
+--------+-----------+----------+
| 20 | CLERK | 1100.00 |
| 30 | SALESMAN | 1600.00 |
| 20 | MANAGER | 2975.00 |
| 30 | MANAGER | 2850.00 |
| 10 | MANAGER | 2450.00 |
| 20 | ANALYST | 3000.00 |
| 10 | PRESIDENT | 5000.00 |
| 30 | CLERK | 950.00 |
| 10 | CLERK | 1300.00 |
+--------+-----------+----------+
9 rows in set (0.00 sec)
having
案例:找出每个部门的最高薪资,要求显示薪资大于2500的数据。
select deptno ,max(sal) from emp where sal > 2900 group by deptno ; //效率比较高,能用则用
select deptno ,max(sal) from emp group by deptno having max(sal) > 2900 ; //这种方式效率低
案例:找出每个部门的平均薪资,要求显示薪资大于2000的数据。
select deptno,avg(sal) from emp group by deptno having avg(sal) > 2000;
重点:having的使用场景,having本身的效率是没有where的效率高的,但是因为优先级问题以及where后面无法使用分组函数 而导致一些场景下,只能使用having才能处理。
5、总结一个完整的DQL语句应该怎么写?
语句 | 执行内容 | 执行优先级 |
---|---|---|
select … | 查找那些字段 | 5 |
from … | 来自于哪张表 | 1 |
where … | 查找的条件 | 2 |
group by … | 按照什么分组 | 3 |
having … | 分组后还需要过滤什么 | 4 |
order by … | 按照什么顺序来显示 | 6 |