018day知识点

–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=in
select deptno,dname,rownum,n from (select deptno,dname,rownum n from dept order by deptno desc) where n>=5 and n<=6 ;

–查询所有用户的信息及所在部门信息
–查询的数据: 用户信息 部门信息
–来源: 用户表 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(+); --左链接

–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 索引名