Oracle数据库学习Day03——表连接

92语法

–当要查询的数据来自于不同的表,需要使用表连接
–select 数据 from 数据来源1,数据来源2; 对乘 笛卡尔积

select * from emp,dept; --笛卡尔积  48条

–select 数据 from 数据来源1,数据来源2 where 表连接条件|行过滤条件;

select * from emp,dept where emp.deptno = dept.deptno;
select ename,sal,e.deptno from emp e,dept d where e.deptno = d.deptno;  --同名字段指明出处

–查询有上级的员工的信息以及上级经理人信息
–来源: 员工表e1 经理人 e2
–连接条件: e1.mgr = e2.empno

select * from emp e1,emp e2 where e1.mgr = e2.empno;  --11条,有一个king没有上级

外连接

–外链接
–当你想要表连接中某一张表中所有的数据全部展示,无论是否满足满足连接条件都显示,可以把这张表设置为主表
–在外连接中主表的表中所有数据都能展示
–如何设置外连接中的主表,在连接条件位置 主表对象加(+)
–左外连接 : 主表在逗号左边就是左连接
–右外连接 : 主表在逗号右边就是右连接

–查所有员工的信息以及上级经理人信息
–员工表为主表

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

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

99语法（join）

内连接 (inner) join

–笛卡尔积 对乘
–cross join

select * from emp inner cross join dept;  --99
select * from emp,dept; 

–查询30部门的员工信息以及所在部门信息

–99

select *
  from (select * from emp where deptno in 30)
 cross join (select * from dept where deptno in 30);

–92

select *
  from (select * from emp where deptno in 30),
       (select * from dept where deptno in 30);

等值连接

–自然连接 natural join 自动根据同名字段|主外键关系

select ename,sal,deptno from emp natural join dept;  

–同名字段不能指明出处

–jion using(等值连接字段名) 当存在多个同名字段,可以指明使用哪一个做等值连接

select ename,sal,deptno from emp join dept using(deptno); 

– 数据来源1 join 数据来源2 on 连接条件 ; 即可以实现等值连接 可以实现非等值连接

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

非等值连接

–查询员工信息以及每一个员工的薪资等级

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

– 查询非20部门并且薪资>1500的员工信息以及薪资等级信息

select *
  from emp e
  join salgrade s
    on e.sal between s.losal and s.hisal
 where e.deptno != 20
   and sal > 1500
 order by sal desc;

外链接

–想要某张表中不满足连接条件的数据都显示,把这张表定义为主表
–左外 left join
–右外 right join

select * from emp e1 right join emp e2 on e1.mgr = e2.empno;

–全连接 full join 两张表都作为主表

select * from emp e1 full join emp e2 on e1.mgr = e2.empno;

rowid

–rowid 和 rownum 伪列(表没有,但是可以在select查询)
–rowid 相当于对象的地址,区分表中的数据,是记录的唯一,在数据插入表中时就存在
–作用: 可以多相同数据做去重(没有主键的表)
select *

  from dept
 where rowid in (select rowid from dept where deptno = 10);

–实现去重,表中没有主键,但是存在多条重复数据,重复数据只保留一条,实现去重
–查询要保留的那些数据的rowid

--select distinct id,name,course,score,rowid from tb_student;
select max(rowid) from tb_student group by id;

–查到要删除的数据的rowid

select rowid from tb_student where not rowid in(select max(rowid) from tb_student group by id);

–删除数据 delete from 表名 where 条件; 满足条件的语句删除

delete from tb_student
 where rowid in
       (select rowid
          from tb_student
         where not rowid in (select max(rowid) from tb_student group by id));

rownum

–rownum 伪列 结果集的序号 只要有select就有结果集就有rownum,每一套结果集都有自己的rownum
–规律: 把以确定结果集中的数据从第一个开始 设置rownum,从1开始,依次+1
–优点: 有规律,规律可循,是数字,可以进行判断

–如果在使用rownum值判断之前就已经确定了一个结果集,这个结果集中的rownum就是已经确定的
–再嵌套一个select

select empno,ename,rownum n from emp;  --数据来源  确定rownum
select *
  from (select empno, ename, rownum n from emp)
 where n >= 5
   and n <= 10;  --where中rownum

要使用别名,确定内部select语句的字段,如果直接写rownum,被认为是外部select语句的

–如果存在排序,rownum的序号可能出现问题(乱号),如果:根据主键进行order by,先排序再rownum,如果根据其他字段排序,一般会先rownum,再order by

select deptno,dname,rownum from dept order by deptno;
select empno,ename,deptno,rownum from emp order by deptno;

–如果rownum乱掉怎么办?
–解决方案: 外层嵌套一个select

select empno,ename,deptno,rownum num from emp order by deptno;

–以确定的有规律的rownum,如果判断,根据这个rownum判断,把当前select当做数据源使用

select empno, ename, deptno, rownum 外层的rownum, num 内层rownum
  from (select empno, ename, deptno, rownum num from emp order by deptno);

–分页

–按照工资降序排序 查询第三页的数据， 每一页显示4条记录

select * from (select empno, ename, deptno,sal,rownum n
  from (select * from emp order by sal desc))
  where n between 4*3-3 and 4*3;

拓展，同时显示工资等级

select * from (select empno, ename, deptno,sal,grade,rownum n
  from (select * from emp e join salgrade s on e.sal between s.losal and s.hisal order by sal desc))
  where n between 4*3-3 and 4*3;

试图

–create or replace view 试图名字 as 结果集 with read only;
–建立在表和结果集之间的就是试图,其实也是结果集,但是这个结果集可以被存储,以后可以查询试图中的数据
–最大的优点:就是封装,简化sql
–合理使用试图,不要过于使用

create or replace view vm_emp as select empno,ename, sal,deptno from emp where deptno in (20,30) with read only;

–删除试图

drop view vw_emp;

–思考题（难，存疑）:*
–查询每个部门中所有经理的人的平均薪资,以部门为单位,求出最低平均薪资的那个部门的部门名称
–找到所有经理人

select distinct mgr from emp where mgr is not null;

–打印所有经理人信息，分组

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

–计算每组经理人平均薪资，部门编号

select deptno, avg(sal)
  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, avg(sal)
   from emp
  where empno in (select distinct mgr from emp where mgr is not null)
  group by deptno
 having 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 emp
         where empno in (select distinct mgr from emp where mgr is not null)
         group by deptno
        having avg(sal) = (select min(avg(sal))
                            from emp
                           where empno in (select distinct mgr
                                             from emp
                                            where mgr is not null)
                           group by deptno));

–创建试图简化代码

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

select dname
  from dept
 where deptno = (select deptno
                   from emp
                  where empno in (select * from vw_mgr)
                  group by deptno
                 having avg(sal) = (select min(avg(sal))
                                     from emp
                                    where empno in (select * from vw_mgr)
                                    group by deptno));

权限不够

–切换管理员sys用户
–授权: grant dba to scott;
–回收: revoke dba from scott;

索引（数据库优化）

–索引
–是数据库的对象之一
–字典的目录
–只有在大量数据查询的时候效率较高
–大量数据 查询,如果增删改效率降低,因为对象要维护
–索引的创建与删除完全不影响字段的使用
–唯一字段适合设置索引
–oracle自动为主键设置索引

select * from emp where sal>900;

–创建索引
–create index 索引名 on表名 (字段列表…)

create index index_sal on emp(sal);

–删除索引
–drop index 索引名

drop index index_sal;

表设计

–表设计
–表 表名 字段 约束 表与表之间的关系
–三范式
–表与表之间的关系: 一对一 一对多|多对一(主外键) 多对多{中间表}

–创建表与约束问题
–1)创建表的同时不创建约束, 结束后追加约束
–2)创建表的同时为字段添加约束
–3)创建表的结构结束之间添加约束

–约束的添加: 1)物理约束 :表中字段上添加
2)逻辑约束:java代码上使用逻辑判断

–主键 外键 非空 唯一 检查

–1)创建表的同时不添加约束

create table 表名(
字段 字段类型,
字段 字段类型,
…
)

create table sxt_student(
       sid number(5), --5代表有效数字 (5,2)其中2为是小数位
       sname varchar2(15), --可变长字符 默认字节个数  指明字符个数:(5 char)
       sage number(3),
       sgender char(1 char), --定长字符
       hiredate date
) 

–2）创建表的同时添加约束 1)字段后直接添加约束,没有约束名 不便于后期维护,但是编写简单 2)字段后直接添加约束包括约束名

create table sxt_student(
       --学生编号 主键约束
       sid number(5) primary key,
       --学生姓名 非空约束 
       sname varchar2(15) not null unique, 
       --年龄  检查约束0~150
       sage number(3) check(sage between 0 and 150),
       --性别  检查约束 '男' '女'
       sgender char(1 char) check(sgender in('男','女')), 
       --入学日期  默认值 sysdate
       hiredate date default(sysdate),
       cid number(5)
) 

–3）字段后添加约束并指定约束名 constraints

```sql
```sql
create table sxt_student(
       --学生编号 主键约束
       sid number(5) constraints pk_sxt_student_sid primary key,
       --学生姓名 非空约束 
       sname varchar2(15) constraints sxt_student_sname_notnull not null, 
       --年龄  检查约束0~150
       sage number(3) check(sage between 0 and 150),
       --性别  检查约束 '男' '女'
       sgender char(1 char), 
       --入学日期  默认值 sysdate
       hiredate date default(sysdate),
       cid number(5),

   --创建表结构结束前 添加约束

   constraint ck_user_pwd check(length(userpwd) between 4 and 18),
  -- constraints pk_sgender check(sgender in('男','女')),
   constraints sxt_student_sname_unique unique(sname)  )

–后续追加约束

alter table sxt_student add constraints pk_sgender check(sgender in('男','女'));

–删除约束

alter table sxt_student drop constraints sxt_student_sname_notnull;

–加入注释

comment on table sxt_student is '学生表';
comment on column sxt_student.sid is '学号,主键';
comment on column sxt_student.sname is '学生姓名';
comment on column sxt_student.hiredate is '入学日期';
comment on column sxt_student.sage is '年龄';
comment on column sxt_student.sgender is '性别';
comment on column sxt_student.cid is '班级编号';

create table sxt_student(
       --学生编号 主键约束
       sid number(5) constraints pk_sxt_student_sid primary key,
       --学生姓名 非空约束 
       sname varchar2(15) constraints sxt_student_sname_notnull not null, 
       --年龄  检查约束0~150
       sage number(3) check(sage between 0 and 150),
       --性别  检查约束 '男' '女'
       sgender char(1 char), 
       --入学日期  默认值 sysdate
       hiredate date default(sysdate),
       
       --外键约束  关联sxt_class班级表的主键cid
       --cid number(5) references sxt_class(cid)
        cid number(5) --constraints fk_sxt_student_cid_class references sxt_class(cid)
        --追加外键约束
       -- constraints fk_sxt_student_cid_class foreign key(cid) references sxt_class(cid)
) 

–追加外键约束

alter table sxt_student add  constraints fk_sxt_student_cid_class foreign key(cid) references sxt_class(cid) on delete cascade;

create table sxt_class(
       cid number(5) primary key ,
       cname varchar2(15) constraints haha not null unique
)

–删除表

drop table sxt_student;
drop table sxt_class cascade constraints; --cascade constraints删除表的同时级联删除约束

–插入数据

insert into sxt_class values(1001,'java35期');
insert into sxt_class values(1002,'java36期');

insert into sxt_student values(01,'古力娜扎',18,'女',sysdate,1001);

–主键关系|约束
–主从表的概念 从表|子表 主表|父表

–如果两张表存在主外键约束:
–删除表:
–默认先删除从表 再删除主表
–cascade constraints 删除主表的同时级联删除约束

–删除数据:
–删除从表数据: 直接删除
–删除主表数据:
–从表中是否有数据引用了当前的主表数据
–没有引用可以直接删除
–有引用,想要删除需要一下三种方式处理:
–默认 先删除从表引用的数据,再删除被引用的主表数据
–添加外键约束时,on delete set null 删除主表数据的同时,从表引用字段设置为null
–添加外键约束时,on delete cascade 删除主表数据的同时,级联删除从表引用了的数据

数据截断

–约束的禁用
ALTER TABLE tb_user disable constraint nn_user_name;
–约束启用
ALTER TABLE tb_user enable constraint nn_user_name;

–delete 与 truncate 数据截断
–共同点: 可以实现删除全部数据

–区别
delete: 可以删除全部,删除删除某一部分
自动开启事务
检查主表数据是否有被从表中引用…3中解决方案

truncate 删除全部数据
不会开启事务
直接检查表结构上是否有被从表关联

select * from sxt_class;
select * from sxt_student;
select * from t_user;


delete from sxt_class;
truncate table sxt_class;

create table sxt_student(
       sid number(5),
       sname varchar2(15),
       cid number(5) references sxt_class(cid)
)

insert into sxt_class values(1001,'java35');
insert into sxt_student values(01,'lisi',1001);

insert into t_user values(01,'wangwu','12345');
insert into t_user values(02,'zhangsan','123');

序列

–作用:帮助我们管理为表中字段添加的数据值 ,数值型 可以按照一定的规律进行增长和管理
–create sequence 序列名 start with 起始值 increment by 步长;

create sequence sq_haha_user_id start with 100 increment by 2;