Oracle学习(三)

一.SQL99 中的内外连接
   1.1 sql99 中的内连接
   列出每个员工的 first_name  和对应的部门名
      select first_name,name
          from   s_emp  e, s_dept d
              where e.dept_id=d.id;
   from   a表  join  b表  on 连接条件; 
   from   a表 inner join  b表  on 连接条件;
    select first_name,name
          from   s_emp  e join s_dept d
              on e.dept_id=d.id; 
     select first_name,name
          from   s_emp  e inner join s_dept d
              on e.dept_id=d.id; 
  1.2 sql99 中的外连接
    a表  left  outer  join b 表 on 连接条件;
    a表  right  outer  join b 表 on 连接条件;
    a表  full  outer  join b 表 on 连接条件;
    
    /* (+) 字段对面的表的数据全部匹配出来 
       left outer join  就是左边的表发起
       连接,左边的表的数据全部匹配出来*/   
    增加一个新部门 
    insert  into  s_dept values(100,'test',NULL);
    commit; 
    列出所有的部门名 和 对应的地区名
    (+) 
       select  d.name,r.name    
           from  s_dept  d,s_region  r
               where d.region_id=r.id(+);
      left  outer  join 
       select  d.name,r.name    
           from  s_dept  d left outer 
               join s_region  r
               on d.region_id=r.id;   
      right  outer   join 
      select  d.name,r.name    
           from  s_region  r right outer join s_dept  d
               on d.region_id=r.id; 
      /* 使用左外连接 完成谁是普通员工?*/
      先使用 (+)   id  first_name 
         select distinct m.id,m.first_name
             from  s_emp e,s_emp m
                 where e.manager_id(+)=m.id
                 and e.manager_id is null;
         select distinct m.id,m.first_name
             from  s_emp m left outer join s_emp e
                 on e.manager_id=m.id
                 where  e.manager_id is null;        
     全外连接的结果集 等于左外连接的结果集
     加上右外连接的结果集 然后减去重复的记录。
     oracle 如何实现全外连接?
     oracle不是通过两端都加(+) 
     是通过关键字 
     union   合并两个结果集 并排重
     union  all  合并两个结果集 不排重
     select  id  from  s_emp  union 
     select  id  from  s_emp;
     select  id  from  s_emp  union  all
     select  id  from  s_emp;
     
     多表查询:
         内连接
             等值连接  
             非等值连接 
             自连接 
         外连接         
             等值连接  
             非等值连接 
             自连接 
  SQL99  内外连接
     内连接
     a表  join  b表  on  连接条件;
     a表  inner join  b表  on  连接条件;
     外连接
     a表 left outer join b 表 on 连接条件;
     a表 right outer join b 表 on 连接条件;
     a表 full outer join b 表 on 连接条件;
        union
        union  all
 二.组函数
    2.1 特点
    无论sql语句影响多少行 最终返回一个结果
    2.2 常见的组函数
    count    统计个数
    max      统计一组数据的最大值
    min      统计最小值
    sum      统计和
    avg      统计平均值
    2.3 举例
    统计  s_emp 表中的 员工人数 工资的
    最大值  工资的最小值
    select  count(id),max(salary),
        min(salary)  from s_emp; 
    2.4 统计s_emp 中的工资的和  和 
       工资的平均值。
    select  sum(salary),avg(salary)
        from s_emp;   
    select  sum(distinct salary),
        avg(distinct salary)
        from s_emp;      
    2.5 测试组函数对NULL值的处理方式
    统计s_emp提成的个数  提成的平均值
    忽略
    select  count(commission_pct),
        avg(commission_pct) from  s_emp; 
       
 三.分组
    3.1  按照一定的标准 把数据分成若干组。
    3.2  where 条件 group  by  分组标准.
    3.3  按照部门编号分组 统计每个部门的人数
     select  dept_id,count(id)
         from  s_emp 
             where  1=1
                 group by   dept_id;
    3.4   按照部门编号分组 统计每个部门的
      平均工资。
     select  dept_id,avg(salary)
         from  s_emp 
             where  1=1
                 group by   dept_id;
     按照部门编号分组 统计每个部门的
      平均工资。列出平均工资大于1400的部门
      和 平均工资。 
     select  dept_id,avg(salary)
         from  s_emp 
             where  1=1 
                 group by   dept_id;    
    3.5 如何对组数据进行过滤 
       需要使用 having  数据过滤条件 
     select  dept_id,avg(salary)
         from  s_emp 
             where  1=1 
                 group by   dept_id
                     having avg(salary)>1400;
    3.6 按照部门号分组  统计每个部门的人数
      显示人数大于2的部门编号 和 人数。
     select  dept_id,count(id) ec
         from  s_emp  
             group  by  dept_id
                 having  count(id)>2
                     order by ec;  
     from  
         where 
             group by 
                 having 
                     select   
                         order by 
   3.7  列出每个部门的平均工资  
       要求显示平均工资大于1400的部门
       编号和 平均工资。还要求显示部门名。
       s_emp   s_dept
   select dept_id,avg(salary),d.name    
       from  s_emp e,s_dept d
           where  e.dept_id=d.id 
               group by  dept_id,d.name
                   having avg(salary)>1400;
       
   select dept_id,avg(salary),max(d.name)    
       from  s_emp e,s_dept d
           where  e.dept_id=d.id 
               group by  dept_id
                   having avg(salary)>1400;    
   结论:在分组语句中  select 后的字段 要么
   是分组标准要么是经过合适的组函数处理过的。    
                             
四.子查询
   4.1 概念
   把一条sql 的执行结果  另一条sql的操作基础。
   4.2 子查询出现的位置
   where 
   having 
   from
   4.3  where 之后
   /* 查询所有的领导编号 */
   select  distinct  manager_id from s_emp;
   /* 如果一个员工的编号 出现在领导编号中
   则这个员工是领导*/
   in 等于其中的任何一个
   select  id,first_name  from s_emp
       where id  in (select  distinct 
        manager_id from s_emp);
   /* 查询所有的非领导 列出 id ,first_name*/
   not in 不等于其中任何一个数据
   id  not in (1,2,3,NULL);
   select  id,first_name  from s_emp
       where id not in (select  distinct 
        manager_id from s_emp);
   
   select  id,first_name  from s_emp
       where id not in (select  distinct 
        manager_id from s_emp where
        manager_id is not null);
          
   4.4  子查询出现在having 后 
   按照部门号分组,列出每个部门的平均工资
   要求显示平均工资大于42部门的平均工资
   的部门，显示部门号 和平均工资.
   select  dept_id ,avg(salary)
       from  s_emp   
           group by  dept_id
               having  avg(salary)>(
               select  avg(salary) from 
               s_emp where dept_id=42);
   4.5  子查询出现在from之后
   任何的查询语句 可以看成一张内存表
   select id,first_name name ,salary
       from s_emp; 
   select * from  ( select id,first_name 
   name ,salary from s_emp )  
   where salary>1000; 
   
   
   select*from(select  dept_id ,avg(salary) 
       asal from  s_emp   
       group by  dept_id) where asal>(
        select  avg(salary) from 
        s_emp where dept_id=42);
五.建表 和 删表
   5.1 建立表
   create  table  表名(
       字段名    类型,
       字段名    类型,
       字段名    类型 
   );
   类型:
   number
   name   char(20)    定长字符串  
   name   varchar2(20) 变长字符串
   date   日期类型
   5.2 建立一张员工表
   create  table  myemp9528( 
   id     number,
   fname  char(10),
   sname  varchar2(10),
   salary number
   );
   5.3 删除表
   drop   table   myemp9528;   
   create  table  myemp9528( 
   id     number,
   fname  char(10),
   sname  varchar2(10),
   salary number
   );
   
六.DML (数据操作语句)
   6.1  insert  增加数据
   insert   into  表名  values(字段1值,
   字段2值,字段3值);   
   insert   into  myemp9528 values(
   9527,'xsy','xsy',123456);
   commit;
    
   select  length(fname),length(sname)
       from myemp9528 where id=9527;
   select * from myemp9528 
       where fname='xsy ';
   select * from myemp9528 
       where sname='xsy';    
   
   可以选择部分字段 插入值 
   没有选择的字段 以NULL值插入
   必须包含所有的非空字段
   insert  into 表名(字段名1,字段名3)
   values(字段1值,字段3值);
   
   insert  into myemp9528(id,sname)
   values(2008,'bjtest');
   commit;
   select  * from myemp9528;
   
   insert   into s_dept values(100,
   'test',NULL);
   
   insert  into myemp9528(id,sname)
   values(1,'bdl');
   commit;
  
  6.2 删除数据
  delete  from  表名  where  条件;
  commit;
  
  delete  from  myemp9528 where id=1;
  commit; 
    
  6.3 更新数据
  update   表名    set 字段1名=值
      where 条件;
  update   表名    set 字段1名=值1,
      字段2名=值2 
          where 条件;    
  commit; |  rollback; 
  update   myemp9528  set salary=8888
      where sname='bdl'; 
  update   s_emp   set salary=12500
      ,first_name='bdl' where id=12;    
  commit;
   
 七.事务 （交易） 
  commit;  提交
  rollback; 回滚
  savepoint  保存点名;
  
  原子性:
      事务中的操作 是一个不可分割的整体。
      转账
      update  account 
      set salary=salary-20000 where ano='1';
      a 
      update  account 
      set salary=salary+20000 where ano='2';
      b
      if(a&&b){
          commit;
      }else{
          rollback;
      }
      事务中的操作 要么一起成功 要么一起失败
  隔离性:
      一个事务中的操作 在没有提交以前 对
      另一个事务而言 数据的变化是不可见的。
      drop     table  testtcl;
      create   table  testtcl(
          id   number primary key,
          salary  number
      );
      insert  into  testtcl values(1,2000);
      insert  into  testtcl values(2,4000);
  一致性:
      -20000;
      +20000
      -50
      
  持久性:
       
   
  保存点:
      让事务做到 部分成功 部分失败。
      insert 
      savepoint  a;
      update
      savepoint  b;
      insert 
      savepoint  c;
      
      if(c){
          rollback to b;
          commit;
      }
      
      drop     table  testtcl2;
      create   table  testtcl2(
          id   number primary key,
          salary  number
      );  
     insert  into  testtcl2 values(2,2000);
     savepoint  a;
     insert  into  testtcl2 values(3,3000); 
     savepoint  b;
     insert  into  testtcl2 values(4,4000);
     savepoint  c;
     insert  into  testtcl2 values(5,5000); 
     savepoint  d;
     rollback to b; 
     commit; 
     
八.日期类型 
   date
   8.1 日期类型的表达
   'dd-MON-yy'
   select  start_date from s_emp; 
   8.2 把s_emp 表中 id  first_name start_date
   要求按照start_date排序
   select  id,first_name,start_date
       from  s_emp  order by start_date;
   8.3 日期格式显示
   to_char(日期数据,'日期格式')
   yyyy   四位年
   mm     2位月
   dd     两位天
   
   hh     12小时制
   hh24   
   mi     分钟
   ss     秒
   
   day    星期几
   month  月的全写
   mon    月的缩写
   pm     上午am  下午pm  
   
   select  id,first_name,
     to_char(start_date,
     'yyyy-mm-dd hh24:mi:ss')
     from  s_emp  order by start_date; 
     
   select  id,first_name,
     to_char(start_date,
     'yyyy-mm-dd hh24:mi:ss day  pm')
     from  s_emp  order by start_date;  
   8.4 如何插入日期
   建立一张订单表
   drop    table  myorder9527;
   create  table  myorder9527(
   ono     varchar2(50) primary key,
   oname   varchar2(30),
   omoney  number,
   odate   date
   );
   insert  into myorder9527 values(
   'bj002','test002',168.75,'18-AUG-14');
   commit;
   
   select  ono,to_char(odate,
      'yyyy-mm-dd hh24:mi:ss')  
      from myorder9527;
   8.5 直接插入当前系统时间
   insert  into myorder9527 values(
   'bj003','test003',168.75,sysdate);
   commit;  
   
   to_date('日期字符串','日期格式')
   根据日期格式 把日期字符串 转换成日期
   2008-08-08  20:08:08 
   2012-12-21  23:59:59
   2020-01-01  00:48:15