系统学习数据库day04

Having子句

分组之后有限制条件该怎么办， 使用having子句

• 查询每个部门中最高薪水大于2900的才输出

SELECT
DEPTNO,
MAX(sal)
from
emp
GROUP BY
deptno
HAVING
MAX(SAL) > 2900

select 语句执行的顺序

• from 子句中找到先要查询的表
• where子句进行费分组函数的筛选判断
• group by 子句完成分组
• having完成组函数筛选
• order by 子句进行排序操作
• select 语句显示列或者组函数
  基础的sql

SELECT deptno, job, sal from emp

增加where

SELECT deptno, job, sal from emp WHERE JOB in ('salsman', 'manager', 'clerk')

增加group by

SELECT
	deptno,
	job,
	sal
FROM
	emp
WHERE
	JOB IN ('salsman','manager','clerk')
GROUP BY
	DEPTNO, JOB, sal

增加Having

SELECT
	deptno,
	job,
	sal
FROM
	emp
WHERE
	JOB IN ('salsman','manager','clerk')
GROUP BY
	DEPTNO, JOB, sal
HAVING avg(SAL) > 1000

增加order by

SELECT
	deptno,
	job,
	sal
FROM
	emp
WHERE
	JOB IN ('salsman','manager','clerk')
GROUP BY
	DEPTNO, JOB, sal
HAVING avg(SAL) > 1000
ORDER BY 3 DESC

• 查询部门人数大于2的部门编号，部门名称，部门人数

--  查询部门人数大于2的部门编号，部门名称，部门人数
SELECT
e.DEPTNO, 
d.DNAME,
count(e.EMPNO)
FROM
emp e, dept d
WHERE e.DEPTNO = d.DEPTNO
GROUP BY
e.deptno
HAVING
count(e.EMPNO) > 2

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

select e.deptno 部门编号, d.dname 部门名称,count(e.empno) 部门人数, AVG(e.SAL) 部门平均工资
from emp e,dept d
where e.deptno=d.deptno
group by e.DEPTNO
HAVINg count(e.empno)>2 and AVG(e.SAL) >2000
ORDER BY 3

查询部门平均工资在2500元以上的部门名称及平均工资。

select d.dname 部门名称, AVG(e.SAL) 部门平均工资
from emp e,dept d
where e.deptno=d.deptno
group by e.DEPTNO
HAVINg  AVG(e.SAL) >2500

查询员工岗位中不是以“SA”开头并且平均工资在2500元以上的岗位及平均工资，并按平均工资降序排序。

select e.JOB 岗位 ,AVG(e.SAL) 岗位平均工资
from emp e
where e.JOB not LIKE 'SA%' 
group by e.JOB 
HAVING avg(e.sal)>2500 and
ORDER BY 2 DESC

查询部门人数在2人以上的部门名称、最低工资、最高工资, 平均工资, 并对求得平均工资工资进行四舍五入到整数位

select d.dname 部门名称,MIN(sal), MAX(sal), ROUND(AVG(e.SAL),0) 部门平均工资
from emp e,dept d
where e.deptno=d.deptno
group by e.DEPTNO
HAVINg  COUNT(e.EMPNO) >2

子查询

询里面还有查询
注意： 子查询优先于主查询执行 最好子查询用括号

• 查询比ALLEN工资高的员工信息

-- 查询比ALLEN工资高的员工信息
SELECT
	*
FROM
	emp
WHERE
	sal > (
		SELECT
			sal
		FROM
			emp
		WHERE
			ENAME = 'ALLEN'
	);

单行子查询

• 单行子查询只返回一行， 可以使用到单行运算符 > 、= 、>= 、< 、<= 、 <> 不等于
  显示和雇员7369从事相同工作并且工资大于雇员7876的雇员的姓名和工作

-- 显示和雇员7369从事相同工作并且
-- 工资大于雇员7876的雇员的姓名和工作
SELECT
	ENAME,
	JOB
FROM
	emp
WHERE
	JOB = (
		SELECT
			job
		FROM
			emp
		WHERE
			EMPNO = 7369
	)
AND sal > (SELECT sal from emp WHERE EMPNO=7876)

• 子查询使用组函数
  查询工资最低的员工姓名, 岗位及工资

SELECT ename, job, sal from emp WHERE sal = (SELECT MIN(SAL) from emp)

• 带有having子句的子查询
  查询部门最低工资比20部门最低工资高的部门编号和最低工资

SELECT deptno, MIN(sal) from emp 
GROUP BY DEPTNO 
HAVING MIN(sal) > (SELECT MIN(sal) from emp WHERE DEPTNO=20) ;

• 查询入职日期最早的员工姓名，入职日期

SELECT ename, hiredate from emp WHERE HIREDATE = (SELECT min(HIREDATE) from emp)

• 查询工资比SMITH工资高并且工作地点在CHICAGO的员工姓名，工资，部门名称

SELECT ename, sal, dname from emp e, dept d WHERE e.DEPTNO=d.DEPTNO 
and sal>(SELECT sal from emp WHERE ename='SMITH') and LOC = 'CHICAGO'

• 查询入职日期比20部门入职日期最早的员工还要早的员工姓名，入职日期

SELECT ename, hiredate from emp WHERE HIREDATE < 
(SELECT MIN(HIREDATE) from emp WHERE DEPTNO = 20)

多行子查询

多行子查询返回的记录有多条
可以使用的操作符 in / all // any

• in
  查询是经理的员工姓名, 工资

SELECT ename, SAL from emp where EMPNO in (SELECT DISTINCT mgr from emp)

any

any 表示和子查询的任意一行进行比较有一个满足就行

• 列 = any（） 表示等于子查询结果集合中的任意一个， 即谁都可以
• < any（） 表示小于子查询结果集合中的任意一个， 即小于大的

• any（） 表示大于子查询结果集合中的任意一个， 即大于最小的

= any（） 表示等于子查询结果集合中的任意一个， 即谁都可以
-- 查询是经理的员工姓名, 工资
SELECT ename, SAL from emp where EMPNO = ANY(SELECT DISTINCT mgr from emp)

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

SELECT EMPNO, ENAME, JOB, SAL from emp WHERE DEPTNO <> 10 AND
sal > ANY(SELECT SAL from emp where DEPTNO =10)