五、mysql聚合函数及排序综合练习

数据库表创建以及数据准备

可以先复制下面的数据表的数据进入查询并且生成对应的表格

CREATE TABLE DEPT (
DEPTNO INT(2) NOT NULL ,
	DNAME VARCHAR(14) ,
	LOC VARCHAR(13),
	PRIMARY KEY (DEPTNO)
	);
CREATE TABLE EMP(
EMPNO INT(4)  NOT NULL ,
	ENAME VARCHAR(10),
	JOB VARCHAR(9),
	MGR INT(4),
	HIREDATE DATE  DEFAULT NULL,
	SAL DOUBLE(7,2),
	COMM DOUBLE(7,2),
	PRIMARY KEY (EMPNO),
	DEPTNO INT(2) 
	);

CREATE TABLE SALGRADE
      ( GRADE INT,
	LOSAL INT,
	HISAL INT );




INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
10, 'ACCOUNTING', 'NEW YORK'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
20, 'RESEARCH', 'DALLAS'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
30, 'SALES', 'CHICAGO'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
40, 'OPERATIONS', 'BOSTON'); 

 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7369, 'SMITH', 'CLERK', 7902,  '1980-12-17'
, 800, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7499, 'ALLEN', 'SALESMAN', 7698,  '1981-02-20'
, 1600, 300, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7521, 'WARD', 'SALESMAN', 7698,  '1981-02-22'
, 1250, 500, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7566, 'JONES', 'MANAGER', 7839,  '1981-04-02'
, 2975, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7654, 'MARTIN', 'SALESMAN', 7698,  '1981-09-28'
, 1250, 1400, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7698, 'BLAKE', 'MANAGER', 7839,  '1981-05-01'
, 2850, NULL, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7782, 'CLARK', 'MANAGER', 7839,  '1981-06-09'
, 2450, NULL, 10); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7788, 'SCOTT', 'ANALYST', 7566,  '1987-04-19'
, 3000, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7839, 'KING', 'PRESIDENT', NULL,  '1981-11-17'
, 5000, NULL, 10); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7844, 'TURNER', 'SALESMAN', 7698,  '1981-09-08'
, 1500, NULL, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7876, 'ADAMS', 'CLERK', 7788,  '1987-05-23'
, 1100, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7900, 'JAMES', 'CLERK', 7698,  '1981-12-03'
, 950, NULL, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7902, 'FORD', 'ANALYST', 7566,  '1981-12-03'
, 3000, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7934, 'MILLER', 'CLERK', 7782,  '1982-01-23'
, 1300, NULL, 10); 

 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
1, 700, 1200); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
2, 1201, 1400); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
3, 1401, 2000); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
4, 2001, 3000); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
5, 3001, 9999); 

表介绍

dept (部门表)  
   DEPTNO (部门编号)
   NAME(部门名称)
   LOC(部门的地址)
emp(员工表)
   EMPNO(员工编号)  
   ENAME(员工的名称)
   JOB(工作岗位) 
   MGR（直接上级领导编号）
   HIREDATE（入职日期）
   SAL（薪资，工资）
   COMM(补助)
   DEPTNO（部门编号）
salgrade（薪资等级）
   GRADE(薪资编号)
   LOSAL(最低薪资)
   HISAL(最高薪资)

SQL语句查询练习

排序查询

order  by 排序的关键字  升序 asc 降序 desc
根据员工的薪水排序（升序）
如果没有其他条件，只有一个排序的情况下，直接去掉where，并且排序一般都是放在最后执行；
#根据员工的薪水排序（升序）
select*from emp ORDER BY sal asc
#根据员工的薪水排序（降序）
select*from emp ORDER BY sal desc
#员工入职日期降序查询
select*from emp ORDER BY hiredate desc
#查询职位为MANAGER 的员工信息,并且按照薪资从高到低排序
#  order by 一定放在最后
select *from emp where job='MANAGER'  order by sal desc 

聚合函数

SQL中存在一些聚合函数，这些特殊函数是不能直接在where后面当做条件使用的，
一般使用方式为 放在返回值项（select  *） 或者放在having关键字后

聚合函数包括：
#求最大值max
select MAX(sal)  from emp ;
#求最小值 min
select min(sal) from emp;
#求和 sum
select sum(sal) from emp;
#求平均 avg
select  avg(sal) from emp;
#求总数 count
select count(*) from emp;

去重

如果在返回值项目存在重复数据，那么可以使用distinct 关键字去除重复

#去重复(忽略人的情况下，查询公司一共有几个部门)
一般情况下，我们直接查询部门即可。这个只是做测试
# 查询不重复的字段值
select distinct deptno from emp;

分组查询 group by & having

将查询出的数据进行分组处理
关键字是 group by

#找出不同工作类别中的最高薪资  分组之后返会的查询字段一定是分组的列名或者聚合函数，
# 再加其他字段没有意义
select  job, max(sal) from emp  GROUP BY job
#找出不同工作类别中的最高薪资，显示的时候要求按照薪资从高到低显示
select  job, max(sal) as m from emp  GROUP BY job order by m  desc
#求每个部门的平均薪资
select deptno,avg(sal)   from emp GROUP BY deptno ;
#求每个岗位的最高薪资 ，除MANAGER之外    
select job,max(sal) as m  from emp GROUP BY job having job!='MANAGER' 
ORDER BY m desc
注意：如果查询条件中，存在普通条件和分组或者排序，那么普通条件查询优先并且用where 执行，可以接and or等。但是不允许放在分组或者排序后用and 或者 or 等

找出每个工作岗位的平均薪水 ，要求显示平均薪水大于2000的
注意：where 条件中不能直接执行聚合函数  需要借助 having执行（放在分组后执行聚合函数）
#找出每个工作岗位的平均薪水 ，要求显示平均薪水大于2000的
select  avg(sal) a,job from emp GROUP BY job having a>2000

顺序:
group by   having   order by