JavaSE.20190516.数据库（三）.rowid和rownum.表连接（92）.99连接.视图.索引（了解）.

一.rowid和rownum

           ROWID是ORACLE中的一个重要的概念。用于定位数据库中一条记录的一个相对唯一地址值。通常情况下，该值在该行数据插入到数据库表时即被确定且唯一。ROWID它是一个伪列，它并不实际存在于表中。它是ORACLE在读取表中数据行时，根据每一行数据的物理地址信息编码而成的一个伪列。所以根据一行数据的ROWID能找到一行数据的物理地址信息。从而快速地定位到数据行。数据库的大多数操作都是通过ROWID来完成的，而且使用ROWID来进行单记录定位速度是最快的。我们可以将其用于删除重复数据。
           ROWNUM是一种伪列，它会根据返回记录生成一个序列化的数字。排序后的结果集的顺序号 ，每一个结果集 都有自己顺 序号 ，不能直接查询大于1的数。利用ROWNUM，我们可以生产一些原先难以实现的结果输出。 例如实现分页操作。
     ps: oracle 中 索引从1开始，java 程序 从0开始

     1.rowid

         实现重复记录的删除

         要求：删除重复记录，一条记录只保留一次
         思路 将所有记录按照某种特定规律分组（相同的记录为一组），保留下每组中的一条记录即可，其他记录删除

      2.rownum

         rownum :1）必须排序      2）不能直接取大于1的数

--最底层 rownum 数据库默认顺序号 -->没有用的 
select emp.*, rownum from emp； 
select emp.*, rownum from emp order by sal ;


--自己 排序后结果集的顺序号 
select e.*, rownum from (select * from emp order by sal desc) e;

--取出工资前5名 
select e.*, rownum 
  from (select * from emp order by sal desc) e 
 where rownum <= 5;
--取出 工资 3-5名 
select e.*, rownum 
from (select * from emp order by sal desc) e 
where rownum <= 5 
and rownum >= 3;
--三层模板 (分页) 
select e.* 
from (select e.*, rownum rn 
from (select * from emp order by sal desc) e 
where rownum <= 5) e 
where rn >= 3; 
/* 
select 字段列表 from (select e.*,rownum rn from (select from 表 order by 字段) e where rownum<=最大值) 
where rn>=最小值 
*/ 
select e.* 
from (select e.*, rownum rn 
from (select * from emp order by sal desc) e
 where rownum <= 10) e 
where rn >= 6;

二.表连接（92）

     当我们获取的数据不是来自于同一张表而是来自于多张表时就需要使用到表连接

     select * from emp;

     select * from dept;

     注意：同名列 非* 必须区分

     数据源 、关系列、 过滤条件、字段

    1.笛卡尔积

       --非* 必须区分 使用表名 或别名.区分

select * from emp , dept; 
select ename , dname from emp , dept; 
select ename, dname, e.deptno from emp e, dept d;

     2.等值连接（在笛卡尔积基础上取条件列相同的值）

--员工名称及部门名称
 select ename, dname, e.deptno from emp e, dept d where e.deptno=d.deptno; 
--找出30部门的员工名称及部门名称:先关联后过滤
select ename, dname, e.deptno from emp e, dept d where e.deptno=d.deptno and e.deptno=30; --记录很多时 :先过滤后关联 
-- 数据来源： emp (select * from emp where deptno=30) e ,
 dept(select * from dept where deptno=30) d 
select * from emp where deptno=30; 
select * from dept where deptno=30;
 -- 查询的字段：ename, dname, e.deptno 
-- 条件：e.deptno=d.deptno , deptno=30
 select ename, dname, e.deptno from
 (select * from emp where deptno=30) e ,
(select * from dept where deptno=30) d 
where e.deptno=d.deptno;

     3.非等值连接    >  <  !=  <>between   and

--900 属于哪个等级 
select grade from salgrade where 900 >losal and 900<hisal; 
select grade from salgrade where 900 between losal and hisal; 
--查询员工姓名，工资及等级 
-- 数据源： emp e, salgrade s 
-- 字段： ename, grade, sal 
-- sal between losal and hisal 
select ename, grade, sal from salgrade s, emp e where sal 
between losal and hisal;

      4.自连接：特殊的等值连接（来至于同一张表）

--找出 存在上级的员工姓名 及上级名称 
-- 数据来源： emp e, emp m 
-- 字段： e.ename, m.ename 
-- 条件： e.mgr=m.empno 
select e.ename, m.ename from emp e, emp m where 
e.mgr=m.empno;

      5.外连接

--找出 所有的员工姓名 及上级名称 
--找出 所有部门的员工数 及部门名称
 select dname, nu from dept d, (select count(1) nu, deptno from emp 
group by deptno) e
 where d.deptno(+)=e.deptno;

       看+和, 主表在,的左边就叫左外连接 主表在,的右边叫右连接

三.99连接

     交叉连接 cross join --->笛卡尔积
     自然连接(主外键、同名列) natural join -->等值连接
     join using连接(同名列) -->等值连接
     [inner]join on 连接 -->等值连接 非等值 自连接 (解决一切) 关系列必须区分
     left|right [outer] join on|using -->外连接
     full join on|using -->全连接 满足直接匹配，不满足 相互补充null ，确保 所有表的记录 都至少出现一次

     1.交叉连接

select * from emp cross join dept;

      2.自然连接

select * from emp natural join dept; 
--在指定列过程中同名列归共同所有(*除外) 
select deptno,e.ename,d.dname from emp e natural join dept d;

      3.using连接

-- natrual 等值 
select ename, dname 
from emp 
natural join dept 
where deptno = 30; 
--using 
select ename, dname from emp join dept using (deptno) where deptno = 30; 
--on
 select ename, dname 
from emp 
join dept 
on emp.deptno = dept.deptno
where emp.deptno = 30;

on 非等值连接 、自连接 
--部门编号为30的员工名称 工资等级
 select ename, grade
 from emp e
 join salgrade s
 on e.sal between s.losal and s.hisal 
where deptno=30; 
--部门编号为30的员工名称 上级名称
select e.ename,m.ename mname from emp e join emp m
 on e.mgr =m.empno where e.deptno =30;

--部门编号为30的员工 员工名称 部门名称 工资等级 上级名称 
select e.ename, dname, grade, m.ename mname
 from emp e
 join dept d
 on e.deptno = d.deptno
 join salgrade s 
on e.sal between s.losal and s.hisal
 join emp m on e.mgr = m.empno
 where e.deptno = 30;

       5.外连接

--所有部门的 部门名称，员工数
 --左外 
select dname, n
 from dept d
 left outer join (select deptno, count(1) n from emp group by
 deptno) i 
on d.deptno = i.deptno;
 select dname, n from dept d 
left outer join (select deptno, count(1) n from emp group by 
deptno) i
 using (deptno);
 --右外
 select dname, n
 from (select deptno, count(1) n from emp group by deptno) i
 right outer join dept d
 on d.deptno = i.deptno;

         6.全连接

select *
from (select 1 no, 'a' "name"
 from dual
 union
 select 2 no, 'b' "name" from dual) a 
full join (select 1 no, 'c' "name"
 from dual
 union
 select 3 no, 'd' "name" from dual) b 
on a.no=b.no;

 select *
 from (select 1 no, 'a' "name" 
from dual
 union
 select 2 no, 'b' "name" from dual) a 
full join (select 1 no, 'c' "name"
 from dual
 union
 select 3 no, 'd' "name" from dual) b
 using(no);

四.视图

     视图:建立在表|结果集|视图上的虚拟表，有以下作用
     1、简化:select 查询语句
     2、重用:封装select语句 命名
     3、隐藏:内部细节
     4、区分:相同数据不同查询
           不是所有的用户都有创建视图的权限
     1、前提： create view -->组 connect resource dba
     2、授权: -->sqlplus /nolog
          a)、sys登录 conn sys/123456@orcl as sysdba
          b)、授权: grant dba to scott;
                 回收: revoke dba from scott;
          c)、重新登录
          create or replace view 视图名 as select语句 [with read only];
         要求:所有列必须存在名称。
         对视图的删除不会删除原有表的数据
        drop view 视图名;
        求部门经理人中平均薪水最低的部门名称

--1)、部门经理人 -->mgr
 create or replace view vw_emp_mgr as select distinct mgr from emp 
where mgr is not null with read only;
 --2)、部门经理人的薪水 
create or replace view vw_emp_mgr_sal as

select *
 from emp
 where empno in (select mgr from vw_emp_mgr) with read only; 
--3)、 按部门平均
 create or replace view vw_emp_sal_group as select deptno,avg(sal)
 avgsal from vw_emp_mgr_sal group by deptno with read only;
 --4)、过滤组 
--找出最低的 平均薪水 
1)、 使用第三个视图
 select min(avgsal) from vw_emp_sal_group 
2)、使用第二个视图 
select min(avg(sal)) minsal from vw_emp_mgr_sal group by deptno; 
--获取部门编号及平均薪水
 select * 
from vw_emp_sal_group 
where avgsal =
(select min(avg(sal)) minsal from vw_emp_mgr_sal group by deptno); 
--部门名称
 select dname
 from vw_emp_sal_group vw, dept d
 where vw.deptno = d.deptno
 and avgsal = 
(select min(avg(sal)) minsal from vw_emp_mgr_sal group by deptno);

五.索引（了解）

     索引是数据库对象之一，用于加快数据的检索，类似于书籍的索引。在数据库中索引可以减少数据库程序查询结果时需要读          取的数据量，类似于在书籍中我们利用索引可以不用翻阅整本书即可找到想要的信息。

     索引是建立在表上的可选对象；索引的关键在于通过一组排序后的索引键来取代默认的全表扫描检索方式，从而提高检索效          率

     索引在逻辑上和物理上都与相关的表和数据无关，当创建或者删除一个索引时，不会影响基本的表 ；

     索引一旦建立，在表上进行DML操作时（例如在执行插入、修改或者删除相关操作时），oracle会自动管理索引，索引删              除，不会对表产生影响

     索引对用户是透明的，无论表上是否有索引，sql语句的用法不变

     oracle创建主键时会自动在该列上创建索引 

     索引: 提高查询速度的一种手段 -->目录
            1、唯一性较好字段适合建立索引
            2、大数据量才有效果
            3、主键|唯一: 唯一索引
            create index 索引名 on表名 (字段列表...)
            drop index 删除索引名
            create index idx_emp on emp(sal,ename);
            drop index idx_emp;
            select * from emp order by sal,ename;