Day18 Oracle 数据库 连表查询

一  rowid和rownum

1.rowid

rowid相当于对象的地址,在数据插入到表中时候已经存在,rowid不是地址,根据地址转换的
rowid和rownum都是伪列

--项目数据值保留唯一的,删除重复的
--查询到要保留的数据
select deptno,dname,rowid from dept;
select name,course,score,min(rowid) from tb_student group by name,course,score;
--要删除的数据
select * from tb_student where not rowid in(select min(rowid) from tb_student group by name,course,score);
--删除数据
delete from tb_student where not rowid in(select min(rowid) from tb_student group by name,course,score)

2.rownum

--分页 rownum 结果集的序号  从1开始
--如果按照主键进行排序,先排序后确定rownum
--如果根据非主键进行排序,先确定rownum再排序
select  deptno,dname,rownum from dept order by deptno desc;
select empno,ename,sal,rownum from emp order by sal desc;

 

select rownum,n from (select empno,ename,sal,rownum n from emp order by sal desc);

select deptno,dname,rownum,n from (select deptno,dname,rownum n from dept order by deptno desc) where n>=3 and n<=5 ;

一页显示2 n条数据  第3 i页     这一页的起始数据的rownum=(i-1)*n+1     结束的rownum=i*n
select deptno,dname,rownum,n from (select deptno,dname,rownum n from dept order by deptno desc) where n>=5 and n<=6 ;

二  连表查询

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

1.  92语法

(1)笛卡尔积

总行数等于两个表的行数相乘  z=x*y

select empno,emp.deptno from emp,dept;

(2)等值连接

可以是相同名字的字段,也可以是非相同名字的字段,但是要保证两个字段的类型相同

select * from emp,dept where emp.deptno=dept.deptno;
select * from emp,dept where emp.ename=dept.dname; --没有满足条件的数据

(3)非等值连接

--薪资2500的等级信息
select * from salgrade where 2500 between losal and hisal;
--查询所有员工的信息及工资等级信息
select * from emp e,salgrade s where e.sal between s.losal and s.hisal;

--查询用户的用户信息,部门信息及薪资等级信息
select * from emp e, dept d, salgrade s where e.deptno = d.deptno and sal between s.losal and s.hisal;

(4)自连接:特殊的等值连接(来自于同一张表)

--查询所有有上级的员工的信息及上级信息  假设e1是员工表  e2是上级表

select * from emp e1,emp e2 where e1.mgr=e2.empno;

(5)外连接

--查询所有员工的信息及上级信息  假设e1是员工表  e2是上级表
--需求:想要其中的某张表中所有的数据全部展示,无论是否满足等值连接条件 --外链接
--外链接:确认主表(主表中的内容会全部展示)   +对面的表为主表,+所在的表为从表
--左外连接   主表在,的左边叫做左连接
--右连接     主表在,的右边叫做右连接

select * from emp e2,emp e1 where e1.mgr=e2.empno(+); --右连接
select * from emp e1,emp e2 where e1.mgr=e2.empno(+); --左连接

2.  99语法

(1)交叉连接

cross join

select * from emp cross join dept;

(2)自然连接

--在指定列过程中同名列归共同所有(*除外)

--等值连接
--自然连接  natural join  内部自动查找同名字段|主外键关系 自动实现等值连
--不能指定限定词
select deptno from emp natural join dept;

(3)using 连接

指定字段等值连接

select * from emp  join dept using(deptno);

(4)on 连接

--非等值连接|等值连接  join  ..on..   (Inner join内连接)
select * from emp join dept on emp.deptno = dept.deptno;

 

--查询10和30部门的员工的名称,部门名称,薪资,薪资等级,上级名称
select e.ename, d.dname, e.deptno, e.sal, s.grade, m.ename
  from emp e
  join emp m
    on e.mgr = m.empno
  join salgrade s
    on e.sal between s.losal and s.hisal
  join dept d
    on e.deptno = d.deptno
 where deptno in (10, 30);--注意:此处的deptno如果不表明表的来源就默认离得最近的.

(5)外连接

--左外连接   left join .. on..
select * from emp e1 left join emp e2 on e1.mgr = e2.empno;

--右外连接   right join .. on..
select * from emp e2 right join emp e1 on e1.mgr = e2.empno;

(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; 

三  视图

视图:建立在表|结果集|视图上的虚拟表，有以下作用

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

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

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

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

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

(6)oracle 创建主键时会自动在该列上创建索引

索引: 提高查询速度的一种手段 -->目录

1、唯一性较好字段适合建立索引

2、大数据量才有效果

3、主键|唯一: 唯一索引

--索引

--索引是透明的,是否添加索引对表的使用没有区分

--索引是数据库对象之一,提高查询速度,针对大量数据而言,因为索引毕竟是对象,需要维护

--索引相当于目录

--不是所有的索引都有效,如果对修改,删除等操作如果存在索引效率会贬低,因为索引需要维护,更新

--主键自动添加索引

select * from emp where sal>1500;

 

--创建索引: create index 索引名 on表名 (字段列表...)

create index index_emp_sal on emp(sal);

select * from emp where name like '%sint%';

--删除索引: drop index 索引名

drop index index_emp_sal;