oracle——笔记——4内容

--外连接
 作用：查询不满足连接条件的数据
 select * from emp e,dept d
 where e.deptno = d.deptno;
 select * from dept;
 select * from emp e,dept d
 where e.deptno(+) = d.deptno;    ---右外
 
 insert into emp(empno) values(1122);
 select * from emp e,dept d
 where e.deptno = d.deptno(+);    ---左外
 select * from emp e,dept d
 where e.deptno(+) = d.deptno(+);  ---不存在这种写法 
 ----外连接的另外一种写法：
 select * 
 from emp e left outer join dept d
   on e.deptno = d.deptno;   ---left 显示左边表不满足条件的数据
                             ---outer 可以省略
                             ---on 只能写连接条件，其他条件 写到where里
 select * 
 from emp e right outer join dept d
   on e.deptno = d.deptno;    ---右外
 select *    
 from emp e full outer join dept d
   on e.deptno = d.deptno;     ---全外
   
 select * 
 from emp e right outer join dept d
   on e.deptno = d.deptno;  
 select * 
 from dept d left outer join emp e
   on e.deptno = d.deptno;   
   
  ---查询出没有员工的部门信息
 select d.* 
 from emp e right outer join dept d
   on e.deptno = d.deptno
 where e.empno is null; 

---自连接
 ---查询员工姓名和他的上级姓名
 select * from emp;
 
 员工的mgr = 上级的empno
 
 select *
 from emp worker,emp manager
 where worker.mgr = manager.empno;
 
  select * from emp worker;
  select * from emp manager;
   --查询出入职比上级早的员工
 select *
 from emp worker,emp manager
 where worker.mgr = manager.empno
   and worker.hiredate < manager.hiredate;
 
---分组查询
select
from
where    分组前的条件(不允许出现分组函数)
group by 列1,列2,……
having   分组后的条件(关于分组函数的条件)
order by
 --分组函数 
   avg() sum() max() min() count() wm_concat()
   平均数 求和 最大  最小   统计    列转行
  select avg(sal),sum(sal),max(sal),min(sal),count(sal) from emp;
  select avg(comm),sum(comm),count(comm) from emp;
    ---分组函数不计算空值
  --查询每个部门的平均工资
  select avg(sal) from emp;
  
  select deptno,avg(sal)
  from emp
  group by deptno;

  --查询每种工作的最高工资
  select job,max(sal)
  from emp
  group by job;
  --查询每个部门中每种工作的平均工资
  select deptno,job,avg(sal)
  from emp
  group by deptno,job
  order by deptno;
    --出现在select中的列，必须出现在group by语句里
      select集合包含于group by集合
  
  --查询平均工资大于2000 的部门
  select deptno,avg(sal)
  from emp
  group by deptno
  having avg(sal) > 2000;
  --查询平均工资大于2000 的部门信息（号、名称、所在地）
  select d.*,avg(sal)
  from emp e,dept d
  where e.deptno = d.deptno
  group by d.deptno,d.dname,d.loc
  having avg(sal) > 2000
  order by d.deptno;
  
  select deptno,wm_concat(ename),count(ename)
  from emp
  group by deptno;
  
---子查询
 单行子查询 多行子查询 多列子查询
 
 --查询与SCOTT同部门的员工信息
  1）select deptno from emp where ename = 'SCOTT';
  2）select * from emp where deptno = 20;
  select * from emp 
  where deptno = (select deptno from emp where ename = 'SCOTT');
 --查询与JONES 同上级的员工
 select * from emp
 where mgr = (select mgr from emp where ename = 'JONES');
 
 --查询工资比MILLER 低，奖金比ALLEN 高的员工信息
 select *
 from emp
 where sal < (select sal from emp where ename = 'MILLER')
   and comm > (select comm frpm emp where ename = 'ALLEN');
 --查询与MARTIN 同工作，并且在1981年5 月之前入职的员工
 select *
 from emp
 where job = (select job from emp where ename = 'MARTIN')
   and hiredate < to_date('1981-5-1','YYYY-MM-DD');

 --查询 上级是JONES 的员工信息
 select * from emp
 where mgr = (select empno from emp where ename = 'JONES');
  
 --查询工资比平均工资高的员工
 select * from emp
 where sal > (select avg(sal) from emp);
 
 --查询工资比10 部门平均工资高的员工
  select * from emp
 where sal > (select avg(sal) from emp where deptno = 10);
 
 --使用子查询，查询SALES 部门的员工信息
 select * 
 from emp e,dept d
 where e.deptno = d.deptno
   and d.dname = 'SALES';
   
 select * from emp 
 where deptno = (select deptno from dept where dname = 'SALES');
 
  --使用子查询，查询出蔡成功同学 四大神术的成绩
  select * from score
  where ano = (select ano from student where aname='蔡成功')
    and bno = (select bno from class where bname = '四大神术');
    
 ---多行子查询 >all <all >any <any =any in()
 
  ---查询工资比30 部门所有员工工资都要高的员工信息
  select * from emp
  where sal >all (select sal from emp where deptno = 30);
  select * from emp
  where sal > (select max(sal) from emp where deptno = 30);
  
  >all  大于最大
  <all  小于最小
  
  >any  大于最小
  <any  小于最大
  
  =any
  in()
  
  select * from emp where sal in(select sal from emp where deptno = 30);
  select * from emp where sal in(1600,1250,2850,1500,950)
  --查询哪个部门没有员工
  存在于dept表，但是不存在于emp表
  select * from dept
  where deptno not in(select distinct deptno from emp);
  
  select * from emp;  --deptno 不能有空值
  
  --
  select * from emp
  where (sal,job) = (select sal,job from emp where ename = 'SCOTT' );
  
 ---查询每个部门的部门信息和部门人数(考虑40 部门)
 P237-238 rownum
 ---不使用组函数，查询最高工资
 ---查询工资第二高到第八高的员工信息
 
---DML 数据操作语句：insert  update delete
 --新增 
   insert into 表名 values();
   insert into 表名(列) 子查询; 
   insert into emp94(eid,ename) select 1122,'abc' from dual
                                union
                                select 1123,'abd' from dual
                                union
                                select 1124,'acd' from dual; 
   create table emp94(eid integer,
                      ename varchar(10),
                      birth date,
                      classno number(2));
   select * from emp94;
   insert into emp94(eid,classno) values(1234,11);
   insert into emp94(eid,classno) select empno,deptno from emp;  
   
  --更改 update
  update 表名 set 列名=值;
  update 表名 set 列名=值 where ……;
  update 表名 set 列1=值1,列2=值2,…… where ……;
  update 表名 set 列=子查询 where ……;
  update 表名 set 列1=子查询,列2=子查询,…… where ……;
 
  update emp set sal = 9000,deptno=40 where job = 'CLERK';
 
  ---更改，把emp中，员工的工资翻倍 奖金在原奖金基础上+500 
  update emp set sal=sal*2,comm=nvl(comm,0)+500;
 
  ---更改，把SMITH 的工资改成与KING 一样
  update emp set sal = (select sal from emp where ename = 'KING')
  where ename = 'SMITH';
 
  ---更改，把ALLEN 改成 与CLARK同部门
  update emp set deptno = (select deptno from emp where ename='CLARK')
  where ename = 'ALLEN';
  ---更改，把与BLAKE 同工作的员工的上级，改成SCOTT
  update emp set mgr = SCOTT的empno
  where job = BLAKE的job;
  
  ---更改，把处于平均工资以下的员工，都调到BOSTON (部门的loc)
  update emp set deptno = (select deptno from dept where loc='BOSTON')
  where sal < 平均工资;
  
  ---更改，把WARD 的职位与工资，都调到与KING 相同
 ---删除
 delete from 表名;      ---删除整张表的数据
 delete from 表名 where ……; --删除符合where条件的数据
 
 delete from emp where deptno = 10;
 select * from emp;

---数据库事务
 事务由一句DDL语句或一组DML语句组成；
 DDL事务 create alter drop truncate
 DML事务 insert update delete
 
 DML事务，以执行第一句DML语句为开始，以手动commit/rollback为结束
 DDL事务，语句执行结果是自动提交commit，不能rollback
 
 DML事务---锁
 DML语句执行之后，commit之前：
 1）当前用户/当前窗口能够看到语句执行结果
 2）其他用户窗口看不到
 3）结果是可以回退的(rollback)
 4）数据状态，加锁的状态--资源争用
 
 ---锁的处理 sysdba
 update emp set sal = 9000 where deptno =10;
 select * from emp;
 
 
--DDL语句
 --create 创建表
  ---默认值
  create table emp94(eid number(4) default 1234,
                     ename varchar2(10) default 'abc',
                     birth date default to_date('2007-7-1','YYYY-MM-DD'));
  insert into emp94(eid) values(1122);
  insert into emp94 values(1122,null,null);
  ---复制表 CTAS
  create table 表名 as 子查询语句;
  create table db_emp2 as select * from emp;
  create table demp as select * 
                       from emp e join dept d 
                       on e.deptno = d.deptno;
 --alter 修改表
  alter table emp94 add addr varchar2(40);    --新增列
  alter table emp94 add addr varchar2(40) default 'ZZZ'; --同时设置默认值
  alter table emp94 modify addr char(10);   --修改列 类型和长度
  alter table emp94 modify birth default sysdate; --修改列 默认值
  alter table emp94 modify eid default null; --清除默认值
  alter table emp94 rename column birth to birthday; --修改列名
  alter table emp_94 drop column birthday; --删除列
  rename emp94 to emp_94;  ---修改表名
  select * from emp_94;
  
 --drop 删除表
 drop table 表名;
 drop table db_emp;
 --truncate 截断表
 truncate table 表名;
 truncate table db_emp;  --delete from db_emp
 select * from db_emp;
 比较drop、truncate、delete
 1）drop和truncate属于DDL语句，执行结果自动提交/保存；
    delete属于DML语句，执行之后需要手动提交；在commit之前可以rollback
 2）drop删除表中数据，同时删除表
    truncate和delete只删除表中的数据
 3）truncate只能删除整张表的数据；
    delete可以删除整张表的数据，也可以删除where条件指定的数据
 4）执行速度：drop > truncate > delete
 5）drop和truncate释放存储空间，delete不释放
    
---约束
1）主键约束  primary key，唯一性和非空性
2）唯一约束  unique，     值唯一，但是可以为空
3）非空约束  not null，   值非空，但是可以重复
4）检查约束  check，      规定列的取值范围/值域
                  check(sal between 6000 and 10000)
                  check(sex in('男','女','不详'))
5）外键约束  foreign key，表与表之间的关系
select * from emp;
select * from dept;

insert into emp(empno,deptno) values(4972,50);
insert into dept(deptno) values(50);
delete from dept where deptno = 50;

 ---创建表的同时创建约束
 create table db_emp(sid number(4),
                     sname varchar2(10) not null,
                     age number(3),
                     addr varchar2(40),
                     parents varchar(10),
                     classno number(2),
                     constraints pk_db_emp primary key(sid),
                     constraints uk_db_emp unique(addr),
                     constraints ck_db_emp check(age between 12 and 18),
                     constraints fk_db_emp foreign key(classno) 
                                           references dept(deptno)
                      );
 insert into db_emp values(14,'XX',18,'NanJing',null,10)                    
 select * from db_emp2; 
 delete from db_emp where sid = 13;
 
 ---表已经存在，创建/删除、约束失效/生效
 alter table db_emp drop constraints UK_DB_EMP;
 alter table db_emp add constraints uk_db_emp unique(addr);
     ---主键 外键 唯一 检查约束

 alter table db_emp modify sname null;
 alter table db_emp modify parents not null;
 
 alter table db_emp enable constraints UK_DB_EMP;  --生效
 alter table db_emp disable constraints UK_DB_EMP; --失效


---视图 view
view是逻辑表，查看数据的窗口，本质上只是sql语句，不存储数据
table是物理表,占用存储空间，实际存储数据
作用：简化查询，提高安全性
create view 视图名 as 子查询;
create or replace view 视图名 as 子查询;

create or replace view e_view as select ename 姓名 ,sal 工资
                                 from emp where deptno = 20;
select * from e_view;
drop view e_view;
 
---DCL 数据控制语句
grant 权限 to 用户;    ---赋予权限
revoke 权限 from 用户; ---收回权限

 --sysdba
grant create view to scott;
grant create user to scott;
grant create session to scott;
……

create user a94 identified by a123;

 --普通用户 
grant select on emp to a94;
grant delete,update,insert on emp to a94;
 
revoke delete,update,insert on emp from a94;
 
 
---索引 index
作用：提高查询速度
创建索引的规则：
1）经常出现在where中的列 适合创建为索引列
2）经常作为表之间的连接条件的列
3）值域/取值范围广、空值多
4）表中数据量大

关于索引的sql语句：
1、create index 索引名 on 表(列);
2、drop index 索引名;
3、alter index 索引名 rebuild; --重建索引


---存储过程
存储过程，sql语句的集合
一次编译，多次运行
select * from emp;
语法语义检查
权限检查
生成执行计划

create or replace procedure insertUser(uName in varchar2)
is
I integer;
begin
  for  I in 1..100 loop
       insert into db_emp2(empno,ename)  values(I,uName||I);
  end loop;
  commit;
end;
  ---调用：
begin
  insertUser('user');
end;

---触发器
  销售表  销售数量  +100
  库存表  库存  -100

create table spb(            --商品表
spbm         integer,         --商品编码
spmc         varchar2(30),    --商品名称
shangbi         varchar2(20),    --商标
chandi          varchar2(12),    --产地
guige         varchar2(20),    --规格
danwei         varchar2(4),    --单位
danjia         number(10,2),    --单价
kucun         number(10,2)    --库存数量
);

create table stock(        --采购表
djh     integer,        --单据号
spbm     integer,        --商品编码
cgy     varchar2(8),    --采购员
cgd     varchar2(30),    --采购地
cgsl     number(10,2),    --采购数量
cgpc     varchar2(16),    --采购批次    
cgrq     date);        --采购日期

创建第一个触发器：
一旦采购表中被插入采购商品的记录，就更新商品表的数据，使相应的商品的库存增加：

create or replace trigger stock_ins
after insert on stock
for each row
begin
update spb set kucun=kucun + :NEW.cgsl where spbm=:NEW.spbm;
end;

---------------------------
 ---查询每个部门的部门信息和部门人数(考虑40 部门)
 select d.*,count(e.empno)
 from emp e right join dept d
   on e.deptno = d.deptno
 group by d.deptno,d.dname,d.loc;
 
 select d.deptno,nvl(con,0)
 from (select deptno,count(empno) con from emp group by deptno) t
      right join dept d
   on t.deptno = d.deptno;
 ---不使用组函数，查询最高工资 
 rownum 存在查询结果中，不属于任何一张表
 rownum 针对查询结果排序，
        序号从1开始
 rownum 比较运算符  = 1   <   <=
 select * from emp where rownum = 1 order by sal desc;
 
 select * from (select * from emp order by sal desc) t
 where rownum = 1;
 ---查询工资第二高到第八高的员工信息   分页查询
 1)where rownum between 2 and 8
   前八名 - 第一名
 select * from (select * from emp order by sal desc) t
 where rownum <= 8
 minus
 select * from (select * from emp order by sal desc) t
 where rownum = 1; 
 
 2)select * from emp where rownum between 2 and 8;
   select * from (select emp.*,rownum r from emp) t
   where r between 2 and 8;
   
 3)排序函数
 select rank() over(order by sal desc) ran,
        dense_rank() over(order by sal desc) dran,
        row_number() over(order by sal desc) rown,
  emp.*
 from emp;