Oracle学习笔记(一)（重难点：分级查询，分析函数，聚合函数）

1.外连接查询

select e.last_name ,e.department_id,d.department_name
from employees e ,departments d
where  e.department_id(+)=d.department_id;

select * from employees;

2. 自连接 查询出每个员工的上级领导

select worker.last_name || 'works for '|| manager.last_name
from employees worker ,employees manager
where worker.manager_id = manager.employee_id;

3. 使用cross join子句是连接的表产生叉集

 select  last_name,department_name
 from employees
 cross join departments;

4.使用on子句创建连接 可以使用 ON 子句指定额外的连接条件

-- 这个连接条件是与其它条件分开的
select e.employee_id,e.last_name,e.department_id,
       d.department_id,d.location_id
from employees e join departments d
on (e.department_id = d.department_id);

5.使用on子句创建多表连接

select employee_id, city, department_name
from employees e
join departments d
on d.department_id = e.department_id
join locations l
on d.location_id = l.location_id; 

6.左/右外连接

select e.last_name, e.department_id, d.department_name 
from employees e
--left outer join departments d
right outer join departments d
on (e.department_id = d.department_id);

7.树形结构 分级查询遍历树

select * from employees;
-- 从下到上遍历树
select employee_id, last_name, job_id, manager_id
from employees
start with employee_id = 101
connect by prior manager_id = employee_id;
-- 从上到下遍历树 
select  last_name || ' reports to '|| 
prior  last_name "Walk  Top Down" 
from employees
start with last_name = 'King'
connect by prior employee_id = manager_id;

8. 使用LEVEL伪列标记层次 从上而下查询

-- 使用connect by 子句删除树枝
select level,last_name || ' reports to '||
prior last_name "walk Top Down"
from employees
start with last_name = 'King'
connect by prior employee_id = manager_id
and last_name != 'Greenberg';-- 节点包括节点之后的记录全部删除

-- 使用where子句删除节点 例如 where last_name != 'Higgins'
select level,last_name || ' reports to '||
prior last_name "walk Top Down"
from employees
where last_name != 'Greenberg'-- 只删除了节点，节点之后的记录还在
start with last_name = 'King'
connect by prior employee_id = manager_id;

9 分析函数

9.1 over函数 不能单独使用 必须配合其他函数一起使用

详细看https://blog.csdn.net/a1065423444/article/details/75635611?utm_medium=distribute.pc_relevant.none-task-blog-title-2&spm=1001.2101.3001.4242
–9.1 over函数 不能单独使用 必须配合其他函数一起使用
–eg ：连续求工资和
–over()函数写法over(partition by expr2 order by expr3)，
–根据expr2对结果进行分区，在各分区内按照expr3进行排序；

--eg  ：1.连续求工资和
select employee_id, last_name, slary, 
       sum(salary) over(order by last_name) over1,
from employees;

-- eg
select department_id,
       employee_id,
       last_name,
       salary,
       sum(salary) over(order by last_name) over1,
       sum(salary) over() over2,
       sum(salary) over(ORDER BY department_id) over3,
       sum(salary) over(partition by department_id) over4,
       sum(salary) over(partition by department_id order by last_name) over5
from employees;