javaweb-Oracle_2

一、Oracle中的伪列
1、Oracle数据库中的伪列并不是表中真实存在的列， 我们通过select * 是查询不到的。 它是由Oracle数据库系统自动维护的一种列， 如果想查看伪列的信息，必须通过手动指定 。

两个常用的伪列 ：
2.1 rowid ： rowid是一个18位字符组成的唯一的标识一条数据， 是通过对数据的存放物理地址计算后得到的。
注： rowid采用的是Base64编码生成的。
当我们知道一条数据的rowid后，可以将其作为查询条件，而且效率最高。

2.2 rownum 【重点】
rownum是对出现在查询结果中的数据生成一个排序的字段号。永远从1开始生成。
注意：
① rownum不能做大于的运算比较。 不能做=比1大的值的运算比较。
② 可以做 =1 >=1的特殊运算比较。

3、 案例
3.1 rowid
select employee_id,first_name,last_name,rowid,length(rowid)
from employees where rowid=‘AAAC9EAAEAAAABXAAA’;

3.2 rownum
-查询员工表前5个员工的信息
select employee_id,first_name,last_name,email,phone_number,
hire_date,salary,job_id,commission_pct,department_id,manager_id
from employees where rownum<=5;

-查询员工表第6到第10个员工的信息
select employee_id,first_name,last_name,email,phone_number,
hire_date,salary,job_id,commission_pct,department_id,manager_id
from employees where rownum >=6 and rownum<=10; ERROR!

4、当rownum与 select 一起使用时注意事项：
*select ,rownum from employees; - 这样书写会报错，找不到 from关键字。
4.1 给表起别名 ：
-语法： select 别名.字段名… from tabName 别名;
-查询员工表的所有信息，同时显示出rownum的编号
select e.,rownum
from employees e;

5、当rownum与order by一起使用时
-查询员工表工资最高的前5个人的信息
select *
from employees where rownum<=5 order by salary desc; ERROR!
PS: 上面这条sql语句查询到的并不是工资最高的前5个人。
原因是： order by 排序语句是在where 筛选数据后并且select 已经生成结果集后才执行的。 所以在where筛选数据时，工资并没有经过排序。
解决思路： 可不可以让order by 先进行排序， 然后在使用where对排序后的结果进行筛选。

二、子查询
1、子查询也称为嵌套查询， 在一个select语句中可以嵌入其他的select语句 。
2、 子查询的分类：
2.1 子查询的结果是一行一列 : 可以将子查询的结果作为where的判断条件。

• 查询最高工资员工的信息
  ① 查询出最高工资
  select max(salary) from employees; - rel1
  ② 再根据编号查询出最高工资人员信息
  select * from employees where salary =rel1;
  ③ 合并
  select * from employees where salary =（select max(salary) from employees）;
• 查询最高工资人员的编号
  ① 查询出最高工资
  select max(salary) from employees; -rel1
  ② 查询出最高工资人的编号
  select employee_id from employees where salary=rel1;
  ③ 合并
  select employee_id from employees where salary=(select max(salary) from employees);

2.2 子查询的结果是多行一列 : 可以作为where的判断条件 。

• 查询与last_name是 King的人在同一个部门的人员信息
  ① 查询King所在的部门
  select department_id from employees where last_name=’King’; - rel1
  ② 查询出于King在一个部门的人员
  select * from employees where department_id in rel1;
  ③ 合并
  select * from employees
  where department_id in (select department_id from employees where last_name=’King’);

2.3 子查询的结果是多行多列 : 可以将查询结果当做一个虚表， 对其再次执行查询。

• 查询工资最高的前5个人的信息
  ① 查询员工信息，按照工资从高到低排序
  select * from employees order by salary desc; -tab1
  ② 查询出工资最高的前5个人的信息
  select * from tab1 where rownum<=5;
  ③ 合并
  select *
  from (select * from employees order by salary desc)
  where rownum<=5;
• 查询出提成最高的前5个人的信息
  ① 查询员工信息，按提成从高到低排序
  select * from employees order by nvl(commisson_pct,0) desc; -tab1
  ② 查询出提成最高的前5个人
  select * from tab1 where rownum<=5;
  ③ 合并
  select * from
  (select * from employees order by nvl(commission_pct,0) desc)

三、数据库的分页查询 【重点】

1. 使用的技术： rownum 和 子查询来配合实现
2. 什么是数据库的分页查询？
   数据库的分页查询就是将数据库表中的信息，分段的查询和展示出来。
   

3、 实现分页查询的核心思路
3.1 我们通过给rownum起别名来欺骗一下数据库， 让rownum成为表中的一个实际字段，进而我们可以像使用表中其他字段一样， 使用其做条件判断 。

• 查询出工资最高的第6到第10个人的信息
  PS: 当rownum与order by一起使用时，要防止rownum排序错乱。 我们要保证先执行order by 的排序， 再生成rownum.
  ① 先按照工资的降序查询
  select * from employees order by salary desc; -tab1
  ② 给rownum起别名
  select e.,rownum rn from tab1 e; -tab2
  ③ 查询出工资最高的第6到第10个人的信息
  select * from tab2 where rn>=6 and rn<=10;
  ④ 合并
  select *
  from (select e.,rownum rn from (select * from employees order by salary desc) e)
  where rn>=6 and rn<=10;

• 查询80号部门的第6到第10个人的信息
  ① 先查询80号部门人员
  ② 给rownum起别名
  select e.,rownum rn
  from employees e where department_id=80; - tab1
  ③ 查询出第6到第10个人的信息
  select * from tab1 where rn>=6 and rn<=10;
  ④ 合并
  select *
  from (select e.,rownum rn
  from employees e where department_id=80)
  where rn>=6 and rn<=10;

4、分页的计算公式
select * from ( select e.* , rownum as r from employees e ) where r between 6 and 10

Mysql分页: Select * from employees limit 11 5

四、表连接查询
1、在实际的开发中， 往往需求中的数据会存在于多张表中。 这种情况下我们需要将多张表的查询结果合并在一起展示出来， 此时就必须使用表连接查询来完成 。

2、表连接查询的分类
2.1 内连接 【重点】
-语法： select 字段… from tab1 t1 inner join tab2 t2 on 连接的条件 ;

• 查询员工编号、 first_name、 工资、部门编号、部门名称
  ① 书写表连接查询的思路
  -首先确定数据来源于那几张表
  -明确连接条件
  -执行查询操作
  select e.employee_id,e.first_name,e.salary,e.department_id,
  d.department_id,d.department_name
  from employees e inner join departments d on e.department_id=d.department_id ;
• 内连接的特点： 会自动忽略连接条件字段值为null的数据 。

2.2 外链接 【重点】
① 左外连接 ： select … from tab1 t1 left [outer] join tab2 t2 on 连接条件;

• 查询员工编号、 first_name、 工资、部门编号、部门名称
  select e.employee_id,e.first_name,e.salary,d.department_id,d.department_name
  from employees e left outer join departments d on e.department_id=d.department_id;
• 左外连接的特点： 左表的记录全部出现在结果中， 如果对应的某行记录在右表中没有对应关系，则右表会自动补空。

② 右外连接 ： select … from tab1 t1 right [outer] join tab2 t2 on 连接条件;

• 查询员工编号、 first_name、 工资、部门编号、部门名称
  select e.employee_id,e.first_name,e.salary,d.department_id,d.department_name
  from employees e right outer join departments d on e.department_id=d.department_id;

-右外连接的特点： 右表的记录全部出现在结果中，如果对应的某行记录在左表中没有对应关系，则左表会自动补空。

③ 全外连接 ： select … from tab1 t1 full [outer] join tab2 t2 on 连接条件;

• 查询员工编号、 first_name、 工资、部门编号、部门名称
  select e.employee_id,e.first_name,e.salary,d.department_id,d.department_name
  from employees e full outer join departments d on e.department_id=d.department_id;
• 全外连接的特点： 两张表的记录全部出现在查询结果中；
  注： 开发中可以直接把 outer省略掉 。

2.3 自连接 【掌握】
自连接是基于表连接语法产生的另一种表现形式， 强调的是连接的表是同一张表。我们在书写自连接的sql语句时，需要把一张表想象成两张表。

• 查询员工的信息，以及其领导的信息
  select e1.,e2.
  from employees e1 left join employees e2 on e1.manager_id=e2.employee_id;

2.4 多表连接 【掌握】

• 查询员工编号、first_name、工资、部门编号、部门名称、部门的所在地
  select e.,d.,l.*
  from employees e left join departments d on e.department_id=d.department_id
  left join locations l on d.location_id=l.location_id;
  PS: 多张表进行表连接时， 还是两张表相互连接， 连接后的大表再继续向下连接。

2.5 笛卡尔连接 【没有实战意义】
select e.,d. from employees e cross join departments d;
等效写法
select e.,d. from employees e，departments d;

五、集合运算符 【了解】

1. 求并集 ：

• union : 取两条sql语句的查询结果展示， 重复的记录不会展示。
  select * from employees where department_id in (60,80)
  union
  select * from employees where department_id in (80,90);
• union all：取两条sql语句的查询结果展示，如果两条sql语句的查询结果又重复的，也全部展示出来。
  select * from employees where department_id in (60,80)
  union
  select * from employees where department_id in (80,90);

2. 求差集
   -minus
   select * from employees where department_id in (60,80)
   minus
   select * from employees where department_id in (80,90);

3. 求交集
   -intersect
   select * from employees where department_id in (60,80)
   intersect
   select * from employees where department_id in (80,90);