连表查询-Day18(2019/5/16)-DATABASE

连表查询篇

一、 rowid 和 rownum

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

ps: oracle 中 索引从 1 开始,java 程序 从 0 开始

1、 rowid

实现重复记录的删除
准备
drop table tb_student;
create table tb_student(
id number(4) ,
name varchar2(20),
course varchar2(20),
score number(5,2)
);
insert into tb_student values(1,'张三','语文',81);
insert into tb_student values(2,'张三','数学',75);
insert into tb_student values(3,'李四','语文',81);
insert into tb_student values(3,'李四','语文',81);
insert into tb_student values(4,'李四','数学',90);
insert into tb_student values(5,'王五','语文',81);
insert into tb_student values(6,'王五','数学',100);
insert into tb_student values(3,'李四','语文',81);
insert into tb_student values(4,'李四','数学',90);
insert into tb_student values(5,'王五','语文',81);
insert into tb_student values(6,'王五','数学',100);
insert into tb_student values(3,'李四','语文',81);
insert into tb_student values(6,'王五','数学',100);
insert into tb_student values(3,'李四','语文',81);
insert into tb_student values(4,'李四','数学',90);
insert into tb_student values(5,'王五','语文',81);
insert into tb_student values(6,'王五','数学',100);
insert into tb_student values(3,'李四','语文',81);
insert into tb_student values(4,'李四','数学',90);
insert into tb_student values(5,'王五','语文',81);
insert into tb_student values(6,'王五','数学',100);
insert into tb_student values(3,'李四','语文',81);
insert into tb_student values(4,'李四','数学',90);
insert into tb_student values(5,'王五','语文',81);
insert into tb_student values(6,'王五','数学',100);
insert into tb_student values(7,'王五','英语',90);
commit;

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

1、找出重复数据 :哪个学生 哪门课重复了

select name,course,count(1) from tb_student group by name,course;
select name,course,count(1) from tb_student group by name,course
having count(1)>1;

2、删除重复数据 :删除重复记录
-- 每条记录的唯一标识

select s.* , rowid from tb_student s;
--找出 保留的rowid
select min(rowid) from tb_student group by name,course;
--删除
delete from tb_student where rowid not in (select min(rowid) from
tb_student group by name,course);

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 连接

select deptno,e.ename,d.dname from emp e join dept d
using(deptno);

4、 on 连接

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

©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 213,752评论 6 493
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 91,100评论 3 387
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 159,244评论 0 349
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 57,099评论 1 286
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 66,210评论 6 385
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 50,307评论 1 292
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 39,346评论 3 412
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 38,133评论 0 269
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 44,546评论 1 306
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 36,849评论 2 328
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 39,019评论 1 341
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 34,702评论 4 337
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 40,331评论 3 319
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 31,030评论 0 21
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,260评论 1 267
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 46,871评论 2 365
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 43,898评论 2 351

推荐阅读更多精彩内容