分段表rowid_三分钟带你了解连表查询

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

--rowid和rownum都是伪列

select * from tb_student;

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

--项目数据值保留唯一的,删除重复的

--查询到要保留的数据

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)

--分页 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);

--显示前5个数据 根据员工编号降序排序,找到前5条数据

select empno,ename,rownum from emp order by empno desc;

select deptno,dname,rownum from dept where rownum>=3 and rownum<=5 order by deptno 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 ;

---- 按照工资降序排序， 找第三页的数据 一页显示2条

--查询所有用户的信息及所在部门信息

--查询的数据: 用户信息 部门信息

--来源: 用户表 emp 部门表 dept

--多个表中才能拿到我们想要的数据==连表查询

--92语法 99语法

--笛卡尔积

select empno,emp.deptno from emp,dept;

--使用的字段,是多个表中存在的,指定字段的来源

--别名

select empno,d.deptno from emp e,dept d;

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

select * from emp,dept where emp.deptno=dept.deptno;

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

--查询30部门 用户的信息及所在部门信息

select * from emp,dept where emp.deptno=dept.deptno and emp.deptno=30;

--非等值连接

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

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

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

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

--需求:想其中的的某张表中所有的数据全部展示,无论是否满足等值连接条件 --外链接

--外链接:确认主表(主表中的内容会全部展示) +对面的表为主表,+所在的表为从表

--左外连接 主表在 , 的左边叫做左连接

--右连接 主表在 , 的右边叫做右连接

--自连接 特殊的连接,自己连接自己 也可以自外链接

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

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

select * from emp e1,emp e2 where e1.mgr=e2.empno; --自连接

--找出所有有员工的部门名称以及员工数

select deptno,count(1) from emp group by deptno;

select dname, c

from dept d, (select deptno, count(1) c from emp group by deptno) e

where d.deptno = e.deptno;

--找出 所有部门的员工数 及部门名称

select dname, nvl(c,0)

from dept d, (select deptno, count(1) c from emp group by deptno) e

where d.deptno = e.deptno(+); --左链接

--找出20部门的所有员工的信息和部门信息

99语法

--实现笛卡尔积效果

--92写法

select * from emp ,dept;

--99写法 交叉连接 cross join

select * from emp cross join dept;

--等值连接

--自然连接 natural join 内部自动查找同名字段|主外键关系 自动实现等值连

--不能指定限定词

select deptno from emp natural join dept;

--using 连接 指定字段等值连接

select * from emp join dept using(deptno);

--非等值连接|等值连接 join ..on.. (Inner join内连接)//jion左右两边是要连接的表,on后面是连接的条件

select * from emp join dept on emp.deptno = dept.deptno;

--查询工资答应1500的用户的信息和薪资等级

select * from emp e join salgrade s on e.sal between s.losal and s.hisal where sal>1500;

--查询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);

--外连接

--左外连接 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;

select 1 no, 'a' "name"

from dual

union

select 2 no, 'b' "name"

from dual;

select 1 no, 'c' "name" from dual union select 3 no, 'd' "name" from dual;

--等值连接

select *

from (select 1 no, 'a' "name"

from dual

union

select 2 no, 'b' "name"

from dual) a

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

left 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

right 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

on a.no = b.no;

--视图: 是介于表和结果集之间的一个虚拟表 ,修改..视图不影响原表结构和数据

--视图如何创建 create or replace view 视图名 as select语句 [with read only];

select * from emp where deptno =30;

create or replace view vw_haha as (select * from emp where deptno =30);

--视图的优点: 1.使用更简单 2.封装 3.隐藏

--视图如何使用

select * from vw_haha where empno=7654;

--视图如何删除 drop view 视图名;

drop view vw_haha;

--查询部门经理人平均薪资最低的部门名称

--找出所有经理人

select distinct mgr from emp where mgr is not null;

--每个部门经理人的平均薪资

select avg(sal) svg_sal , deptno

from emp

where empno in (select distinct mgr from emp where mgr is not null)

group by deptno;

--最低平均薪资

select min(avg(sal))

from emp

where empno in (select distinct mgr from emp where mgr is not null)

group by deptno;

--找到每个部门经理人的平均薪资等于最低平底平均薪资的部门编号

select deptno from (select avg(sal) avg_sal , deptno

from emp

where empno in (select distinct mgr from emp where mgr is not null)

group by deptno) where avg_sal=(select min(avg(sal))

from emp

where empno in (select distinct mgr from emp where mgr is not null)

group by deptno);

--根据部门编号求部门名称

select dname

from dept

where deptno =

(select deptno

from (select avg(sal) avg_sal, deptno

from emp

where empno in

(select distinct mgr from emp where mgr is not null)

group by deptno)

where avg_sal =

(select min(avg(sal))

from emp

where empno in

(select distinct mgr from emp where mgr is not null)

group by deptno));

select dname

from dept

where deptno =

(select deptno

from (select avg(sal) avg_sal, deptno

from emp

where empno in

(select * from vw_nonull_mgr)

group by deptno)

where avg_sal =

(select min(avg(sal))

from emp

where empno in

(select * from vw_nonull_mgr)

group by deptno));

--创建视图

create or replace view vw_nonull_mgr as select distinct mgr from emp where mgr is not null;

select * from vw_nonull_mgr;

--索引是数据库的对象之一,索引是透明的,对于表的使用没有区别

--作用: 加快查询|检索速度

--索引相当于目录

--索引不是任何都有用, 大量数据的查询才有效果, 如果做增删等操作,添加了索引反而会降低效率,索引也是对象,需要维护和更新

--主键都会默认创建索引

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

--drop index 索引名