大数据学习从入门到精通（day3 排序和分组）

1.排序

关键词：order by

order by 使用时后面可以跟多个排序列，跟多个排序列时，按照第一排序列（紧跟order by 关键字的列）排序，如果第一个排序字段的值相同时，它会按照第二个排序字段进行排序。

格式：select 列名,列名,..,列名 from 表名 where 条件表达式 order by 排序列的列名 asc|desc;

asc：升序关键词

desc：降序关键词

注意：升序关键词可不写，降序关键字一定要写

--查询员工信息，按照员工的工资升序排序
SELECT * FROM EMP ORDER BY SAL;
--查询员工信息，按照员工编号降序排序
SELECT * FROM EMP ORDER BY EMPNO DESC;
--查询员工信息，按照部门编号升序排序，如果部门编号相同时，按照工资的升序排序
SELECT * FROM EMP ORDER BY DEPTNO,SAL DESC;
--查询员工信息，按照部门编号降序排序，如果部门编号相同，按照员工编号升序排序
SELECT * FROM EMP ORDER BY DEPTNO DESC,EMPNO;
--查询员工的编号、姓名、工作、工资、部门编号，按照部门编号升序排序，
--如果部门相同时，按照工资降序排序
SELECT EMPNO,ENAME,JOB,SAL,DEPTNO FROM EMP ORDER BY DEPTNO,SAL DESC;

2.聚合函数

聚合函数：对一组值执行计算,并返回单个值

count(列名|*|常数):它求记录数（数据条数）

max(列名):取最大值

min(列名):取最小值

avg(列名):取平均值

sum(列名):求和

count(主键列或索引列)

---

count(*)

count(常数)

count(普通列)

它们执行效率从上到下，依次降低

---

distinct:去重关键字,跟在列的最前面
select distinct deptno from emp;

注意：distinct后面跟多个列时，判断重复数据，所有列的值完全相同时，它才会认为是重复数据

---

3.分组

格式：select 列名,列名,...,列名 from 表名 where 条件 group by 分组列 order by 排序列 asc|desc

关键字：group by 后面跟分组列名，可以是一个分组列，也可以是多个列

--查询各个部门的部门编号和部门的平均工资
select deptno,avg(sal) from emp group by deptno;
select empno,min(sal),max(sal),avg(sal),sum(sal),count(*)  from emp group by empno;


--查询各个部门的员工人数
select deptno,count(empno) from emp group by deptno;
select job,deptno,count(*) from emp group by job,deptno;

注意：group by 后面跟多个列时，只有当多个列的值同时相等时，它才会分为同一个组

---

4.having

select col_name,col_name,...,col_name  from 表名 where 条件 group by 分组列 having 条件 order by 排序列

关键词：having 它是对分组后的数据进行筛选，条件表达式中可以使用聚合函数

--查询各个部门的部门编号和部门的平均工资
select deptno,avg(sal) from emp group by deptno;

--求平均工资大于2000的部门编号和平均工资
select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;

小总结：

where和having的异同

where:条件，where后面跟的条件比having后的条件先执行，条件中不允许使用聚合函数。

having：条件中可以使用聚合函数，一般having和group by联用。

---

小练习：

-- 1、请查询表DEPT中所有部门的情况。
SELECT * FROM DEPT;
-- 2、查询表DEPT中的部门号、部门名称两个字段的所有信息。
SELECT DEPTNO,DNAME FROM DEPT;
-- 3、请从表EMP中查询10号部门工作的雇员姓名和工资。
SELECT ENAME,SAL FROM EMP WHERE DEPTNO=10;
-- 4、请从表EMP中查找工种是职员CLERK或经理MANAGER的雇员姓名、工资。
SELECT ENAME,SAL FROM EMP WHERE JOB='CLERK' OR JOB='MANAGER';
-- 5、请在EMP表中查找部门号在10－30之间的雇员的姓名、部门号、工资、工作。
SELECT ENAME,DEPTNO,SAL,JOB FROM EMP WHERE DEPTNO BETWEEN 10 AND 30 ORDER BY DEPTNO;
-- 6、请从表EMP中查找姓名以J开头所有雇员的姓名、工资、职位。
SELECT ENAME,SAL,JOB FROM EMP WHERE ENAME='J%';
-- 7、请从表EMP中查找工资低于2000的雇员的姓名、工作、工资，并按工资降序排列。
SELECT ENAME,JOB,SAL FROM EMP WHERE SAL<2000 ORDER BY SAL;
-- 8、雇员中谁的工资最高。
SELECT MAX(SAL) FROM EMP;
-- 9、选择部门30中的雇员　
SELECT ENAME FROM EMP WHERE DEPTNO=30;
-- 10、列出所有办事员（job为CLERK）的姓名、编号和部门编号
SELECT ENAME,EMPNO,DEPTNO FROM EMP WHERE JOB='CLERK';
-- 11、找出佣金高于薪金(comm)的雇员
SELECT ENAME FROM EMP WHERE COMM>SAL;
-- 12、找出佣金高于薪金60%的雇员
SELECT ENAME FROM EMP WHERE (COMM/SAL)>0.6;
-- 13、找出部门10中所有经理和部门20中的所有办事员的详细资料
SELECT * FROM EMP WHERE DEPTNO=10 AND JOB='MANAGER'OR DEPTNO=20 AND JOB='CLERK';
-- 14、找出收取佣金的雇员的不同工作
SELECT DISTINCT JOB FROM EMP WHERE COMM is NOT NULL;
-- 15、找出不收取佣金或收取的佣金低于100的雇员
SELECT ENAME FROM EMP WHERE COMM<100 OR COMM IS NULL;
-- 16、显示不带有'R'的雇员姓名
SELECT ENAME FROM EMP WHERE ENAME  NOT LIKE'%R%';
-- 17、显示雇员的详细资料，按姓名排序
SELECT * FROM EMP ORDER BY ENAME;
-- 18、显示雇员姓名，根据其服务年限，将最老的雇员排在最前面
SELECT ENAME,HIREDATE FROM EMP ORDER BY HIREDATE;
-- 19、显示所有雇员的姓名、工作和薪金，按工作的降序顺序排序，而工作相同时按薪金升序
SELECT ENAME,JOB,SAL FROM EMP ORDER BY JOB DESC,SAL;
-- 20、显示在一个月为30天的情况下所有雇员的日薪金
SELECT ENAME,SAL/30 FROM EMP;
-- 21、查询至少有4个员工的部门的部门编号
SELECT DEPTNO,COUNT(*) FROM EMP GROUP BY DEPTNO HAVING COUNT(*)>=4;