oracle课堂笔记--第十三天
自连接：
empid ename mgrid
100 abc
101 def 100
102 xyz 100
emp: mgr:
empid ename mgrid empid mgrname
100 abc 100 abc
101 def 100
102 xyz 100
101 def 100 100 abc
102 xyz 100 100 abc
select emp.ename, mgr.mgrname
from emp, mgr
where emp.mgrid=mgr.empid
emp: mgr:
empid ename mgrid empid ename mgrid
100 abc 100 abc
101 def 100 101 def 100
102 xyz 100 102 xyz 100
select e.last_name, m.last_name
from employees e, employees m
where e.manager_id=m.employee_id;
有经理的员工数：106
SQL> select count(*) from employees where manager_id is not null;
没有经理的员工数：1
SQL> select count(*) from employees where manager_id is null;
练习：
显示所有员工姓名和经理姓名，没有经理的显示“无”。
select e.last_name, nvl(m.last_name, 'N/A')
from employees e, employees m
where e.manager_id=m.employee_id(+);
不等值连接：
conn scott/tiger
select e.ename, sg.grade
from emp e, salgrade sg
where e.sal between sg.losal and sg.hisal;
练习：
找出工资大于所在部门平均工资的员工姓名。
create table avg_sal_dept as select department_id, avg(salary) avg_sal from employees where department_id is not null group by department_id;
select e.last_name, e.salary, asd.avg_sal
from employees e, avg_sal_dept asd
where e.department_id=asd.department_id
and e.salary>asd.avg_sal;
select e.last_name, e.salary, asd.avg_sal
from employees e, (select department_id, avg(salary) avg_sal from employees where department_id is not null group by department_id) asd 给部门平均工资取别名！
where e.department_id=asd.department_id
and e.salary>asd.avg_sal;
子查询
第一写成子查询 第二写成主查询
单行子查询的思路：
SQL> select salary from employees where last_name='Feeney';
SQL> select last_name from employees where salary>3000;
SQL> select last_name from employees where salary>(select salary from employees where last_name='Feeney');
多行子查询的思路：
SQL> select distinct department_id from employees where department_id is not null;
SQL> select department_name from departments where department_id in (10, 20,30);
SQL> select department_name from departments where department_id in (select department_id from employees where department_id is not null);
用多表连接改写：
select distinct d.department_name
from employees e, departments d
where e.department_id=d.department_id
for dept in 1..27
for emp in 1..107
查看emp中是否出现deptid
练习：
工资大于全公司平均工资的员工姓名。
SQL> select last_name from employees where salary>(select avg(salary) from employees);
和Feeney同年入职的员工姓名
select last_name, hire_date
from employees
where extract(year from hire_date)=
(select extract(year from hire_date) from employees where last_name='Feeney')
and last_name != 'Feeney';
select last_name, hire_date
from employees
where hire_date between
(select to_date(to_char(hire_date, 'yyyy')||'0101', 'yyyymmdd') from employees where last_name='Feeney')
And
(select to_date(to_char(hire_date, 'yyyy')||'1231', 'yyyymmdd') from employees where last_name='Feeney')
在Seattle工作的所有员工姓名
在Seattle的部门1 Seattle的location id是多少 2 location id 下的部门信息
在这些部门中的员工 1 那些部门 2 这些部门的员工
select last_name
from employees
where department_id in
(select department_id from departments
where location_id=
(select location_id from locations where city='Seattle'));
查找符合下列条件的员工姓名：和Abel在同一个部门，工资比Olson高
select last_name from employees
where department_id=
(select department_id from employees where last_name='Abel')
and salary >
(select salary from employees where last_name='Olson');
配对子查询：
和Feeney在同一个部门、做同一职位的员工姓名：
select last_name, department_id, job_id
from employees
where department_id=
(select department_id from employees where last_name='Feeney')
and job_id=
(select job_id from employees where last_name='Feeney')
and last_name != 'Feeney';
select last_name, department_id, job_id
from employees
where (department_id, job_id)=
(select department_id, job_id from employees where last_name='Feeney')
and last_name != 'Feeney';
in和not in受null值的影响：
有员工的部门名称
select department_name from departments where department_id in (select department_id from employees);
没有员工的部门名称select department_name from departments where department_id not in (select department_id from employees where department_id is not null);
所有管理，者的姓名：
SQL> select last_name from employees where employee_id in (select manager_id from employees);
所有普通员工的姓名：
SQL> select last_name from employees where employee_id not in (select manager_id from employees where manager_id is not null);
关联子查询：
工资大于所在部门平均工资的员工姓名。
for i in 1..107所有员工
{
select avg(salary) from employees where department_id=i.department_id
if i.salary > i所在部门的平均工资
保留此记录
}
select last_name
from employees outer
where salary >
(select avg(salary) from employees
where department_id = outer.department_id);
select e.last_name, e.salary, asd.avg_sal
from employees e, (select department_id, avg(salary) avg_sal from employees where department_id is not null group by department_id) asd
where e.department_id=asd.department_id
and e.salary>asd.avg_sal;
exists/not exists查询： 是否存在
for i in 1..27所有部门
{
for j in 1..107所有员工
{
if i.department_id = j.department_id
保留此记录
break
}
}
select department_name
from departments outer
where exists
(select 1 from employees where department_id=outer.department_id);
select department_name
from departments outer
where not exists
(select 1 from employees where department_id=outer.department_id);
练习：
在Seattle工作的所有员工姓名（使用子查询和多表连接两种方式）
select last_name
from employees
where department_id in
(select department_id from departments
where location_id=
(select location_id from locations where city='Seattle'));
select e.last_name
from employees e, departments d, locations l
where e.department_id=d.department_id
and d.location_id=l.location_id
and l.city='Seattle';
最大值查询：
SQL> select last_name from employees where salary=(select max(salary) from employees);
top-N查询：
SQL> select last_name, salary from employees where rownum<=3 order by salary desc;
SQL> select * from (select last_name, salary from employees order by salary desc) where rownum<=3;
分页查询：
SQL> select * from
(select * from
(select * from
(select last_name, salary from employees order by salary desc)
where rownum<=6)
order by salary)
where rownum<=3
order by salary desc;
SQL> select last_name, salary
from (select rownum row_num, v1.*
from
(select last_name, salary from employees order by salary desc) v1
) v2
where row_num between 4 and 6;
select last_name, salary
from (select rownum row_num, v1.*
from
(select last_name, salary from employees order by salary desc) v1
where rownum<=6
) v2
where row_num >= 4;
