Oracle学习总结_day05_集合_连接查询

本文为博主辛苦总结,希望自己以后返回来看的时候理解更深刻,也希望可以起到帮助初学者的作用.

**转载请注明 出自 : luogg的博客园 ** 谢谢配合!

day05_集合_连接查询

集合操作符

UNION (并集 ,去除重复行)

查询工资大于8000或职位是程序员的雇员的姓名、工资、职位id --37
select first_name,salary,job_id from copy_emp where salary>4000 --37
union
select first_name,salary,job_id from copy_emp where job_id='IT_PROG'; --5

--union all(A+B,不会去除重复的行) --42

select first_name,salary,job_id from copy_emp where salary>4000 --37
union all
select first_name,salary,job_id from copy_emp where job_id='IT_PROG'; --5

--intersect 取查询结果交集C --5

--查询工资大于8000且职位是程序员的雇员的姓名、工资、职位id
select first_name,salary,job_id from copy_emp where salary>4000 --37
INTERSECT
select first_name,salary,job_id from copy_emp where job_id='IT_PROG';--5

--minus(A-B) 的用法 : 显示在A 中存在, 而在B 中不存在 --32

select first_name,salary,job_id from copy_emp where salary>4000 --37
MINUS
select first_name,salary,job_id from copy_emp where job_id='IT_PROG';--5

连接查询 :

---

1. 自连接 : 自己表内连接,比如员工表中员工id和上级的员工id.

--自连接的练习使用
--名字，salary,部门编码，部门的名称，工资等级,上级名称
select
worker.first_name,worker.salary,worker.department_id,department_name,grade,leader.first_name
from copy_emp worker,copy_emp leader,salgrade,copy_dept dept
where worker.manager_id=leader.employee_id
and worker.salary between losal and hisal
and worker.department_id = dept.department_id;

2. 等值连接 :

3. 非等值连接 :

--查询员工工资和等级(非等值连接 )
select first_name || ',' || last_name as "姓名",employee_id,salary,grade
from copy_emp ce,salgrade2 sg
where ce.salary BETWEEN losal AND HISAL;

4. 内连接 :

--查看部门内雇员编号、姓名、所在部门编号以及部门的名称
select employee_id,first_name,emp.department_id,department_name
from copy_emp emp INNER JOIN copy_dept dept
ON emp.department_id=dept.department_id;

5. 外连接 :

--左外连接(左边的表做了主表与右侧的表关联,右外连接相反)
--查看所有部门 (包含没有员工的部门 ) 的雇员编号、姓名、所在部门编号以及部门名称
select employee_id,first_name,emp.department_id,department_name
from copy_emp emp
LEFT JOIN copy_dept dept
ON emp.department_id=dept.department_id;

--多条件左外连接 名字，salary,部门编码，部门的名称，工资等级,上级名称
select worker.first_name,worker.salary,worker.department_id,department_name,grade,leader.first_name
from copy_emp worker
LEFT JOIN copy_emp leader
ON worker.manager_id=leader.employee_id
LEFT JOIN copy_dept dept
ON worker.department_id=dept.department_id
LEFT JOIN salgrade
ON worker.salary between losal and hisal;

--用 (+) 代替左外连接或右外连接,位置相反
select first_name,emp.employee_id
from copy_emp emp,copy_dept dept
where emp.department_id=dept.department_id(+)

6. 全连接 :

rownum 使用

--查询工资前三的员工信息
select rownum,a.* from (select rownum,emp.* from copy_emp emp) a
where rownum <=3 order by salary nulls last;
--工资6-10 的员工信息
select rownum,a.* from (select rownum,emp.* from copy_emp emp) a
where rownum <=10
minus
select rownum,a.* from (select rownum,emp.* from copy_emp emp) a
where rownum <=6

课外补充:

--1.查询没有员工的部门信息
select * from copy_dept where department_id not in(select department_id from copy_emp
where department_id is not null group by department_id)

--2.查询没有部门的员工信息
select * from copy_emp where department_id is null;

--思特奇 查询出只有主功夫 没有次功夫的员工信息( 信息包含 员工名字,部门名称,功夫描述,个人资产 )
select * from dept_info;
select * from kf_info;
select * from user_info;
select * from user_kongfu;

select ukf.user_id,uinfo.name,dinfo.dept_name,kinfo.kf_name,uinfo.user_asset,ukf.main_kf_flag
from user_kongfu ukf,user_info uinfo,dept_info dinfo,kf_info kinfo
where uinfo.user_id=ukf.user_id
and dinfo.dept_no=uinfo.dept_id
and kinfo.kf_id = ukf.kf_id
and ukf.user_id not in(select user_id from user_kongfu where main_kf_flag=0 group by user_id)