多表关联查询，等值连接，内连接，外连接 （左外连接、右外连接、全外连接） **子查询--重点** 分页查询rownum

---

–day03

–多表关联查询

test
SELECT * from emp_szl;
SELECT * from dept_szl;
–1.笛卡尔积，将两张表完整地、无差别的连接起来
–1⃣️等值连接，通过两张表中的公共字段，来进行匹配筛选
select * from emp_szl,dept_szl
where emp_szl.DEPTNO = dept_szl.DEPTNO;

–获得员工和他所在部门名称
select
–公共字段必须添加表明前缀，唯一字段可以不加前缀
emp_szl.DEPTNO,ename,dname
from emp_szl,dept_szl
where emp_szl.DEPTNO = dept_szl.DEPTNO;

–等值连接问题：只能显示两边能匹配上的数据
–外连接、内连接
–显示所有的员工，和他对应的部门名，如果没有部门，就显示null
–内连接，等同于等值连接
select
emp_szl.DEPTNO,emp_szl.ename,dname
from emp_szl
inner join dept_szl --inner可以省略
on emp_szl.DEPTNO = dept_szl.DEPTNO;

–左外连接、右外连接、全外连接

select
emp_szl.DEPTNO,ENAME,DNAME
from emp_szl
LEFT JOIN dept_szl
on emp_szl.DEPTNO = dept_szl.DEPTNO;
–全外连接，mysql不支持

select
emp_szl.DEPTNO,ENAME,DNAME
from emp_szl
FULL JOIN dept_szl
on emp_szl.DEPTNO = dept_szl.DEPTNO;

—案例：查询ACCOUNTING部门所有的员工名和薪资
select
ename,sal
from
emp_szl e
join dept_szl d
on e.DEPTNO = d.DEPTNO
where dname = ‘ACCOUNTING’;

—案例：查询员工姓名和他们的领导
–将一张表看成两张表–自连接
SELECT
e.EMPNO,e.ENAME,m.ENAME manager , e.mgr
from
emp_szl e
left join emp_szl m
on e.mgr = m.EMPNO;

---

–day03关联查询
–1
SELECT
emp_szl.ENAME,DNAME,LOC
from emp_szl
JOIN dept_szl
on emp_szl.DEPTNO = dept_szl.DEPTNO;

–2

SELECT
e.ENAME,m.ENAME MGR_NAME
from emp_szl e
join emp_szl m
on e.mgr = m.EMPNO;

–3

SELECT
e.EMPNO,e.ENAME,e.DEPTNO,d.dname,d.loc
from emp_szl e
left join dept_szl d
on e.DEPTNO = d.DEPTNO
ORDER BY DEPTNO;

–4

SELECT
e.EMPNO,e.ENAME,d.DEPTNO,d.dname,d.loc
from emp_szl e
right join dept_szl d
on e.DEPTNO = d.DEPTNO

–5
–全外连接
SELECT
e.empno,e.ename,d.DEPTNO,d.DNAME,d.LOC
from emp_szl e
right JOIN dept_szl d
on e.deptno = d.deptno
WHERE e.empno is null;

–6
SELECT DISTINCT
e.JOB
from emp_szl e
JOIN DEPT_SZL d
on e.deptno = d.deptno
where d.DNAME = ‘SALES’;

–7
SELECT
d.DNAME,d.LOC,COUNT(d.DNAME) EMP_COUNT,AVG(e.SAL) SAL_AVG
from dept_szl d
JOIN emp_szl e
on d.DEPTNO = e.deptno
GROUP BY d.dname,d.loc;

–8
SELECT
e1.ename,e2.ename
from emp_szl e1,emp_szl e2
where e1.mgr = e2.EMPNO;
–左边下属 右边经理

SELECT
e1.ename,e2.ename
from emp_szl e1,emp_szl e2
where e1.empno = e2.mgr;
–左边经理 右边下属

-------总结：
–1.驱动表：显示那张表的全部数据
–2.join on ： 永远是共同字段，连接的条件

---

–day03

子查询

select from where group by having
–查询所有薪资比ALLEN高的员工姓名和薪资
select
ENAME,SAL
from
emp_szl
where
sal>(
select
sal
from
emp_szl
where
ename = ‘ALLEN’
);

–列出部门平均薪资比ALLEN薪资高的部门编号
SELECT sal from emp_szl where ename = ‘ALLEN’;
SELECT
DEPTNO,AVG(sal) AVG_SAL
from
emp_szl
GROUP BY deptno
having
AVG(sal)>(SELECT sal from emp_szl where ename = ‘ALLEN’);

–列出所有比自己部门平均薪资高的员工薪水和姓名
SELECT
deptno,ROUND(AVG(sal)) avg_sal
from
emp_szl
GROUP BY
DEPTNO;

SELECT
e.ename,e.sal,d.avg_sal
from
emp_szl e
join(SELECT
deptno,ROUND(AVG(sal)) avg_sal
from
emp_szl
GROUP BY
DEPTNO) d
on e.deptno = d.deptno
where e.sal> d.avg_sal;

–查询所有薪资比ALLEN高的员工姓名和薪资
select sal from emp_szl where ename = ‘ALLEN’;
select ename,sal
from emp_szl
where
sal>(select sal from emp_szl where ename = ‘ALLEN’);

–列出部门平均薪资比ALLEN薪资高的部门编号
select sal from emp_szl where ename = ‘ALLEN’;
SELECT
DEPTNO,AVG(sal)
from emp_szl
GROUP BY DEPTNO
having AVG(sal)>(select sal from emp_szl where ename = ‘ALLEN’);

–列出所有比自己部门平均薪资高的员工薪水和姓名
select
deptno,round(AVG(sal)) AVG_SAL
from emp_szl
GROUP BY DEPTNO;

SELECT 
	e.ename,e.sal,d.AVG_SAL
from 
	emp_szl e
join (select 
		deptno,round(AVG(sal)) AVG_SAL
			from emp_szl 
			GROUP BY DEPTNO) d
on e.deptno = d.deptno
where e.sal>d.AVG_SAL;

–ACCOUNTING 部门中所有的员工姓名
SELECT
DEPTNO
from
dept_szl
where
dname = ‘ACCOUNTING’;

select 
	ename 
from 
	emp_szl
where
	DEPTNO = (SELECT
							DEPTNO
						from 
							dept_szl
						where 
							dname = 'ACCOUNTING');

–查询和SCOTT同职位的员工
SELECT
JOB
from
emp_szl
where
ename = ‘SCOTT’;

select
	*
from 
	emp_szl 
where 
	job = (SELECT 
	JOB
from 
	emp_szl
where 
	ename = 'SCOTT');

–查找薪水比整个机构平均薪水高的员工
SELECT
(AVG(sal)) AVG_SAL
from
emp_szl;

SELECT
ENAME,SAL
FROM
emp_szl
where
sal > (SELECT
(AVG(sal)) AVG_SAL
from
emp_szl);

–查询出部门中有SALESMAN但职位不是SALESMAN的员工的信息
SELECT DISTINCT
DEPTNO
FROM
emp_szl
where
JOB = ‘SALESMAN’;

select
	ENAME,JOB
from
	emp_szl 
where 
	DEPTNO = (SELECT DISTINCT
								DEPTNO
						FROM
								emp_szl
						where 
								JOB = 'SALESMAN') and job != 'SALESMAN';

–列出那些有员工的部门信息
SELECT DISTINCT
DEPTNO
FROM
emp_szl
where
ENAME is not null;

SELECT
	d.DEPTNO,d.dname,d.loc
from 
		dept_Szl d
join (SELECT DISTINCT
								DEPTNO
						FROM
								emp_szl
						where 
								DEPTNO is not null) n
on n.DEPTNO = d.DEPTNO;

–查询出最低薪水高于部门30的最低薪水的部门信息
SELECT
MIN(sal)
from
emp_szl
where
DEPTNO = 30;

SELECT
DEPTNO
from
emp_szl
GROUP BY DEPTNO
HAVING
MIN(sal)>(SELECT
MIN(sal)
from
emp_szl
where
DEPTNO = 30);–得到部门10

SELECT
*
from dept_szl
where
DEPTNO in(SELECT
DEPTNO
from
emp_szl
GROUP BY DEPTNO
HAVING
MIN(sal)>(
SELECT
MIN(sal)
from
emp_szl
where
DEPTNO = 30));

–查询出薪水比本部门平均薪水高的员工信息
SELECT DISTINCT
DEPTNO,round(AVG(sal))AVG_SAL
from
emp_szl
GROUP BY
DEPTNO;

SELECT
ename,sal
from
emp e
join (SELECT DISTINCT
DEPTNO,round(AVG(sal))AVG_SAL
from
emp_szl
GROUP BY
DEPTNO) d
on e.DEPTNO = d.DEPTNO
where
e.sal > d.AVG_SAL;

–用子查询查询员工所在部门名称
–SELECT 里面用 SELECT
SELECT dname from dept where deptno = 10 ;
select
ename , e.deptno,(
select dname
from dept d
where d.deptno = e. deptno)
from
emp e;

–子查询小结：
– 1. 结果是单记录 - 一行一列 ，where 直接比较
– 2.一列，多行记录 - 集合 any in all
– 3.多行多列，作为一张临时表来使用

---

–day03 分页查询 top10 查询
–rownum

 SELECT 
			sal,ename,rownum
 from emp
 
 --ORDER BY sal desc

–查询前四条记录
SELECT SAL,ENAME,ROWNUM
from emp_szl
WHERE ROWNUM<=4;
–rownum 只能从1开始，只能查前n条

SELECT
sal ,ENAME,e.n
FROM
(SELECT
sal,ename,rownum n
from emp) e
WHERE
e.n BETWEEN 5 and 8 ;

–查询工资由高到低 5～8名
SELECT sal,ENAME,e.n
from(
SELECT sal,ENAME,ROWNUM n
from(
SELECT
sal,ename
from emp_szl
ORDER BY sal desc
)
) e
WHERE e.n BETWEEN 5 and 8 ;

–