查询的其他知识

SET 操作符：

以UNION操作符的使用举例：

1.如果有别名，在上面的表加别名来显示，在下面的表加别名上面的表无法显示别名

2.排序的三种方式：别名排序,相对位置排序,列名排序

使用相对位置排序：
select ‘study at’ as “My Dream”,2 from dual
union
select ‘I want to’,1 from dual
union
select ‘atguigu.com’,3 from dual
order by 2 asc

去除顺序的标号：
column a_d noprint;
select ‘study at’ as “My Dream”,2 a_d from dual
union
select ‘I want to’,1 from dual
union
select ‘atguigu.com’,3 from dual
order by 2 asc

查询10,50,20号部门的job_id,department_id，并且department_id按10,50,20顺序排列
select job_id,department_id,1 from employees where department_id = 10
union
select job_id,department_id,2 from employees where department_id = 50
union
select job_id,department_id,3 from employees where department_id = 20
order by 3

3.匹配各SELECT 语句：
select employee_id emp,department_id,to_char(null) from employees01
union
select to_number(null),department_id,department_name from departments

使用 EXISTS 和 NOT EXISTS 操作符
1）EXISTS 操作符
问题：查询公司管理者的employee_id,last_name,job_id,department_id信息
第一种解法：
select employee_id,last_name,job_id,department_id from employees e1
where e1.employee_id in(select manager_id
from employees e2
where e1.employee_id = e2.manager_id
)
第二种解法：
select distinct e1.employee_id,e1.last_name,e1.job_id,e1.department_id
from employees e1,employees e2
where e1.employee_id = e2.manager_id
第三种解法：
EXISTS 操作符检查在子查询中是否存在满足条件的行
如果在子查询中存在满足条件的行:不在子查询中继续查找，条件返回 TRUE
如果在子查询中不存在满足条件的行:条件返回 FALSE，继续在子查询中查找
select employee_id,last_name,job_id,department_id from employees e1
where exists(select ‘S’ from employees e2 where e1.employee_id = e2.manager_id)
2）NOT EXISTS操作符：
问题：查询departments表中，不存在于employees表中的部门的department_id和department_name
select department_id,department_name from departments d
where not exists(select ‘A’ from employees e where d.department_id = e.department_id)

3）相关更新
1）
create table emp011 as select * from employees
2）
alter table emp011 add(department_name varchar(20))
3）
update emp011 set department_name = (select department_name
from departments where department_id = emp011.department_id)

4）相关删除
1）
create table emp022 as select * from employees where department_id in(80,90)
2）
create table emp033 as select * from employees where department_id = 90
3）
delete from emp022 where department_id in(
select department_id from emp033 where department_id = emp022.department_id)

（5）使用 WITH 子句
使用 WITH 子句, 可以避免在 SELECT 语句中重复书写相同的语句块
WITH 子句将该子句中的语句块执行一次并存储到用户的临时表空间中

问题一：查询公司中工资比Abel高的员工的信息
select employee_id,salary from employees
where salary > (select salary from employees where last_name = ‘Abel’)
使用 with 后操作结果：
with Abel_sal as(select salary from employees where last_name = ‘Abel’)
select employee_id,salary from employees where salary > (select salary from Abel_sal)

问题二：查询公司中各部门的总工资大于公司中各部门的平均总工资的部门信息
with dept_sumsal as(
select department_name,sum(salary) sum_sal1 from departments d,employees e
where d.department_id = e.department_id
group by department_name
),
dept_avgsal as(select sum(sum_sal1)/count(*) avg_sum_sal2 from dept_sumsal)
select * from dept_sumsal where sum_sal1 > (select avg_sum_sal2 from dept_avgsal)
order by department_name

（6）Top-N分析：
rownum “伪列”：数据表本身并没有这样的列, 是 oracle 数据库为每个数据表 "加上的"列，
可以标识行号.默认情况下 rownum 按主索引来排序. 若没有主索引则自然排序.
对 ROWNUM 只能使用 < 或 <=, 而用 =, >, >= 都将不能返回任何数据。

查询salary排在前10的employee_id,last_name,salary：

select rownum,employee_id,last_name,salary
from(
select employee_id,last_name,salary from employees
order by salary desc
)
where rownum <= 10

查询员工表中，工资排名在41-50之间的员工信息。
select rn,employee_id,last_name,salary
from (
select rownum rn,employee_id,last_name,salary
from(
select employee_id,last_name,salary from employees
order by salary desc
)
)
where rn > 40 and rn <= 50

习题：
1.查询员工表中 salary 10 - 20 的员工信息.
select *
from (select rownum rn,d.*
from (
select last_name,salary
from employees e
order by salary desc
) d
)
where rn>10 and rn<21
2.对 oralce 数据库中记录进行分页: 每页显示 10 条记录, 查询第 5 页的数据
select employee_id, last_name, salary
from (
select rownum rn, employee_id, last_name, salary
from employees
) e
where e.rn <= 50 and e.rn > 40