数据库进阶
一. rowid和rownum
1. rowid相当于对象的地址,在数据插入到表中时候已经存在,rowid不是地址,根据地址转换的. rowid是伪列.
--项目数据值保留唯一的,删除重复的
--查询到要保留的数据
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是伪列.
--分页 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 ;
二.连表查询
1,定义: 查询所有用户的信息及所在部门信息
--查询的数据: 用户信息 部门信息
--来源: 用户表 emp 部门表 dept
--多个表中才能拿到我们想要的数据==连表查询
常用的方法:92语法 99语法
2.92语法:1992年颁布的语法,
--笛卡尔积
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; --自连接
3.99语法: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内连接)
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 索引名
