数据库之查询操作

高级查询

一. 分组函数

1.分组函数是对数据行的集合进行操作并按组给出一个结果，这个结果可直接输出，或者用来做判断条件。
2.分组函数是对表中一组记录进行操作，每组只返回一个结果，即首先要对表记录进行分组，然后再进行操作汇总，每组返回一个结果，分组时可能是整个表分为一组，也可能根据条件分成多组。
3.分组函数常用到以下五个函数： MIN MAX SUM AVG COUNT

MIN ，MAX

-- 查询入职日期最早和最晚的日期

    SELECT  MIN(HIREDATE),  MAX(HIREDATE)
    FROM EMP

SUM AVG

-- 查询职位以SALES开头的所有员工平均工资、最低工资、最高工资、工资和。
 SELECT  AVG(SAL) '平均工资',MIN(SAL) '最低工资',  MAX(SAL) '最高工资', SUM(SAL) '工资和'
 FROM EMP   
 WHERE  JOB LIKE 'SALES%'

COUNT(*)

返回表中满足条件的行记录数

 -- 查询部门30有多少个员工
    SELECT COUNT(*)
    FROM EMP
    WHERE DEPTNO=30

-- 查询部门30有多少个员工领取奖金。

SELECT COUNT(COMM)
FROM EMP
WHERE DEPTNO=30

-- 组函数中DISTINCT
DISTINCT会消除重复记录后再使用组函数 

-- 查询有员工的部门数量。 


SELECT COUNT(DISTINCT DEPTNO)
FROM EMP

------ 分组函数中的空值处理
— 分组函数中空值处理
除了COUNT（*）之外，其它所有分组函数都会忽略列中的空值，然后再进行计算。

– 在分组函数中使用IFNULL函数
IFNULL 函数可以使分组函数强制包含含有空值的记录

二. 创建数据组

用GROUP BY子句创建数据组
– 通过 GROUP BY 子句可将表中满足WHERE条件的记录按照指定的列划分成若干个小组
– 其中GROUP BY子句指定要分组的列

-- 查询每个部门的编号，平均工资
SELECT  emp.deptno   ,AVG(sal)
FROM  emp
GROUP BY  emp.deptno

按多个列分组

-- 查询每个部门每个岗位的工资总和
SELECT deptno,job,SUM(sal)
FROM emp 
GROUP BY deptno,job






-- 查询每个部门最高工资大于2900的部门编号，最高工资
SELECT deptno,MAX(sal)
FROM emp
GROUP BY empno
HAVING MAX(sal)>2900




-- 2.查询部门平均工资大于2000，且人数大于2的部门编号，部门名称，部门人数，部门平均工资，并按照部门人数升序排序。

SELECT   emp.deptno   '部门编号',dept.dname'部门名称',COUNT(emp.deptno)'部门人数',AVG(emp.sal)'部门平均工资'
FROM emp,dept
WHERE   emp.deptno = dept.deptno
 
GROUP BY  emp.deptno ,dept.dname
HAVING    AVG(emp.sal)>2000 AND COUNT( emp.deptno)>2
ORDER BY 3


SELECT * FROM emp

三.子查询

1.单行子查询

-- 谁的薪水比 Pam还高呢？
SELECT  ename
FROM emp 
WHERE sal >
	   ( SELECT sal 
	     FROM emp
	     WHERE ename='Pam')

子查询中使用组函数

– 查询工资最低的员工姓名，岗位及工资

SELECT ename,job,sal
FROM emp
WHERE sal= 
			(  SELECT MIN(sal) 
			   FROM emp)
-- 2.HAVING子句中使用子查询

– 查询部门最低工资比20部门最低工资高的部门编号及最低工资

SELECT deptno ,MIN(sal)
FROM emp 
GROUP BY deptno
HAVING  MIN(sal) > ( SELECT MIN(sal)
		    FROM emp
		    WHERE deptno = 20 )

— 子查询可以嵌于以下SQL子句中：
WHERE子句
HAVING子句
FROM子句

2.多行子查询

显示和雇员7369从事相同工作并且工资大于雇员7876的雇员的姓名和工作。

SELECT ename,job
FROM emp
WHERE job =
		( SELECT job
		  FROM emp      
		  WHERE empno =7369)    -- job='clerk'
AND    sal>
		(  SELECT sal
		  FROM emp
		  WHERE empno =7876 )   -- sal = 1100

ANY的使用

ANY：表示和子查询的任意一行结果进行比较，有一个满足条件即可。
< ANY：表示小于子查询结果集中的任意一个，即小于最大值就可以。
> ANY：表示大于子查询结果集中的任意一个，即大于最小值就可以。
= ANY：表示等于子查询结果中的任意一个，即等于谁都可以，相当于IN。

– 查询部门编号不为10，且工资比10部门任意一名员工工资高的员工编号，姓名，职位，工资

SELECT empno,ename,job,sal
FROM emp
WHERE sal >ANY (SELECT sal
	    FROM  emp
	    WHERE  deptno = 10
		   )
AND deptno<>10

ALL的使用

ALL：表示和子查询的所有行结果进行比较，每一行必须都满足条件。
< ALL:表示小于子查询结果集中的所有行，即小于最小值。
> ALL:表示大于子查询结果集中的所有行，即大于最大值。
= ALL :表示等于子查询结果集中的所有行,即等于所有值，通常无意义。

– 查询部门编号不为20，且工资比20部门所有员工工资高的员工编号，姓名，职位，工资。

SELECT empno,ename,job,sal
FROM emp
WHERE sal >ALL (SELECT sal
	    FROM  emp
	    WHERE  deptno = 20
		   )
AND deptno<>20

在 FROM 子句中使用子查询

– 查询比自己部门平均工资高的员工姓名，工资，部门编号，部门平均工资

SELECT a.ename,a.sal,a.deptno,b.salavg
FROM emp a,(SELECT  deptno,AVG(sal) salavg
	    FROM emp
	    GROUP BY deptno		 ) b

WHERE a.deptno = b.deptno
AND  a.sal>b.salavg