Oracle总结笔记(二)

10 . 伪列：虚列，在原表中不存在的列，可通过select语句查询到。
I. 语法：select 伪列 from 表名
II. 详解：
rowid【了解】：唯一标识一行数据的物理位置。 //Oracle查询优化器，内部查询时使用比较多，应用开发很少用
rownum【重点】：为查询出的每一行符合要求的数据分配逻辑编号。 //从1开始，依次递增1。
III. 用例：

//基于rowid进行一行数据的查询（速度快）
select * from employees where rowid = 'AAAR5kAAFAAAADNABD'  file# block# row# 

//查询所有列和rowid（表别名 e ，不允许使用as）
select e.*, rowid from employees e

//查询员工表中前5名员工信息（编号，名字，薪资）
select employee_id , first_name , salary
from employees
where rownum <= 5;

//查询员工表中第6名之后员工信息（编号，名字，薪资）
select employee_id , first_name , salary , rownum
from employees
where rownum > 5; //error没有符合条件 的结果
注：rownum总是从1开始生成，只在 <= 的环境下使用  在>=的环境下使用无效

//查询员工表中工资排名前5员工的信息（编号，名字，薪资）
select employee_id , first_name , salary
from employees
where rownum <= 5
order by salary desc //error
原因：order by在where之后生效 

未完待续 接续子查询

11 . 子查询：嵌套查询
I. 语法：与基本查询相同。
II. 详解：
将子查询“一行一列”的结果作为条件判断做第二次查询。
将子查询“多行一列”的结果作为枚举查询的判断条件做第二次查询。
将子查询“多行多列”的结果作为一张临时表进行第二次查询。
III. 用例：
//查询工资大于平均工资的员工信息(工号，名字，薪资)
思路：

1). 先查询员工平均薪资
select avg(salary) from employees; //平均薪资：6461.83177570093

2). 查询大于平均薪资的员工信息
select employee_id , first_name , salary
from employees
where salary > 6461.83177570093 

SQL：
select employee_id , first_name , salary
from employees
where salary > (select avg(salary) from employees) //一行一列，才能和salary进行比较

//查询与姓氏为‘King’的员工在同一部门的员工信息(工号，名字，薪资，部门id)
思路：
1). 先查询 'King' 所在的部门编号
select department_id
from employees
where last_name = 'King' //部门编号：80、90

2). 再查询80、90号部门的员工信息
select employee_id , first_name , salary , department_id
from employees
where department_id in (80,90); 
SQL：
select employee_id , first_name , salary , department_id
from employees
where department_id in (select department_id cfrom employees where last_name = 'King'); //N行一列

//查询员工表中工资排名前5员工的信息（编号，名字，薪资）
思路：
1). 先对所有员工的薪资进行排序（排序后的临时表）
select employee_id , first_name , salary
from employees
order by salary desc

2). 再查询临时表中前5行员工信息
select employee_id , first_name , salary
from (临时表) 
where rownum <= 5;
SQL：合并
select employee_id , first_name , salary
from (select employee_id , first_name , salary from employees order by salary desc) //N行N列
where rownum <= 5;

13 . 集合运算符：将多个查询结果合并为一张临时表。
I. 语法：A查询结果 集合运算符 B查询结果。
II. 详解：
union：并集。（联合去重）
union all：并集。（联合）
minus：差集。（减去）
intersect：交集。（较差）
III. 用例：
//查询60、70号部门员工信息
select * from employees where department_id in (60,70);

//查询70、90号部门员工信息
select * from employees where department_id in (90,70);

通过集合运算符将上面两条SQL语句的查询结果进行合并。

//union：并集将A和B的查询结果合并，重复数据只保留一份。
select * from employees where department_id in (60,70)
union
select * from employees where department_id in (90,70); //70号部门的员工信息只保留一份。

//union all：并集将A和B的查询结果合并，重复数据如数显示。
select * from employees where department_id in (60,70)
union all
select * from employees where department_id in (90,70); //70号部门的员工信息有几份保留几份

//minus：差集在A中删除与B中相同的结果。
select * from employees where department_id in (60,70)
minus
select * from employees where department_id in (90,70); //在A中删除与B中出现的重复数据，70号部门

AAAA//intersect：交集只保留A和B中重复的数据。

select * from employees where department_id in (60,70)
intersect
select * from employees where department_id in (90,70); //只保留重复的70号部门的数据

注：使用规则【重点】：查询结果可以来自与不同的表、不同的列。但列的个数必须相同、列的数据类型必须相同，最终结果中的列名和数据类型 依赖与第一个结果集。

14 . 表连接查询：（Excel）

I. 语法：table1 连接 table2 on 连接条件

II. 详解：
外连接：
左外连接：主表left [outer] join 从表
右外连接：从表right [outer] join 主表
全外连接：主表 full [outer] join 主表
内连接：从表 inner join 从表
自连接：主表 连接方式 从表 （主从相同）

III. 用例：
//查询员工工号，名字，薪资，部门id，部门名称
问题：当查询数据来自于多张表时，使用表连接。
思路：将多张表基于关系列，拼接成一张大表，再取其中的某些列。

1). 左外连【重点】：table1 left join table2 on连接条件（table1为主表）

//查询所有员工信息，以及所对应的部门名称（没有部门的员工，也在查询结果中）
select e.employee_id , e.first_name , e.salary , d.department_name
from employees e left join departments d on e.department_id = d.department_id;

2). 右外连【了解】：table1 right join table2 on 连接条件（table2为主表）

//查询所有部门信息，以及此部门中的所有员工信息（没有员工的部门，也在查询结果中）
select e.employee_id , e.first_name , e.salary , d.department_name
from employees e right join departments d on e.department_id = d.department_id;

3). 全外连【了解】：table1 full join table2 on 连接条件（两张表都是主表）

//查询所有员工信息和所有部门信息（没有部门的员工、没有员工的部门，都在查询结果中）
select e.employee_id , e.first_name , e.salary , d.department_name
from employees e FULL join departments d on e.department_id = d.department_id;

4). 内连接【了解】：table1 inner join table2 on 连接条件（两张表都是从表）

//查询所有有部门的员工信息（既不包括没有部门的员工，也不包括没有员工的部门）
select e.employee_id , e.first_name , e.salary , d.department_name
from employees e inner join departments d on e.department_id = d.department_id;

5). 自连接【重点】：table1 连接 table2 on 连接条件（逻辑上的两张表，物理上的同一张表）

//查询员工编号，名字，直接领导(经理)id，直接领导的名字
select e1.employee_id , e1.first_name , e1.manager_id , e2.first_name
from employees e1 left join employees e2 on e1.manager_id = e2.employee_id;

6). 三表连接查询：
--查询所有员工工号、名字、部门名称、部门所在国家ID

select * from employees e 
inner join departments d 
on e.department_id = d.department_id
inner join locations l
on d.location_id = l.location_id

交叉连接【了解】 cross join 乘积
select * from employees cross join departments