oracle——笔记——4内容

迷南。 2023-10-02 08:13 6阅读 0赞

--外连接
     作用：查询不满足连接条件的数据
     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;