oracle_day03

 
 十.外连接
    10.1 概念      
     外连接的结果集 等于内连接的
           结果集加上匹配不上的记录。
           ( 一个也不能少 )   
    10.2 举例
     能找出谁是普通员工?
     25-8=17     
       select distinct m.id,m.first_name
          from  s_emp  e,s_emp m
              where e.manager_id!=m.id;  
         // logic error         
    10.3 外连接的语法
    (+)  (+)字段对面的表的数据全部匹配
    出来。
       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;
    10.4 列出每个部门的名字 和 部门对应
    的地区名。
      select d.name,r.name
          from s_dept d,s_region r
              where d.region_id=r.id;
    公司业务扩展 增加了新的部门
    insert  into  s_dept values(9527,
    'Test100',NULL);
    commit;         
    列出每个部门的名字 和 部门对应
    的地区名。没有地区的部门也要显示。
     select d.name,r.name
          from s_dept d,s_region r
              where d.region_id=r.id(+); 
              
      select d.name,r.name
          from s_dept d,s_region r
              where d.region_id=r.id(+)
              and  r.id is null; 
    10.5  列出每个员工的工资 和 工资对应
    的工资级别。没有工资级别的员工也要显示。
     select id, salary,grade
         from  s_emp e,salgrade s
             where e.salary between 
             losal(+) and hisal(+)
             order by id;          
     给老板涨工资
     update  s_emp set salary=12500
         where id=1;
     commit;    
    10.6 外连接:
     等值连接
         新增部门和地区      
     非等值连接
         涨工资 和 工资级别
     自连接
         谁是普通 ?     
    10.7 sql99中的内外连接
     sql99内连接:
     from  a join b  on 连接条件;
     from  a inner join b  on 连接条件;
      
     列出每个部门的名字 和 对应的地区名
     select d.name,r.name
         from  s_dept d ,s_region r
             where d.region_id=r.id;              
     select d.name,r.name
         from  s_dept d join s_region r
             on d.region_id=r.id; 
   
     sql99外连接:
     from  a表 left outer  join b表  
        on  连接条件 where 条件;
     from  a表 right outer  join b表  
        on  连接条件 where 条件;   
     from  a表 full outer  join b表  
        on  连接条件 where 条件;  
     所谓的左外连接就是左边的表发起连接
     ,谁发起连接就谁中的所有的数据匹配
     出来。
      select d.name,r.name
         from  s_dept d ,s_region r
             where d.region_id=r.id(+);
     select d.name,r.name
         from  s_dept d left outer join 
             s_region r
             on d.region_id=r.id;
     
     使用sql99 的左外连接 把 谁是普通
     员工查找出来？
     oracle  (+)               
       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;               
     
     left  outer join 
       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;
    10.8 全外连接
    from  a 表 full outer  join b 表 
        on  连接条件 where 条件;
    把a表的数据全部匹配出来  再把b表的
    数据全部匹配出来 然后排出重复的记录。
    oracle 不是通过字段两端都加 (+) 实现
    的。而是通过集合操作关键字 union实现。                  
    
    全外连接的结果集 等于 左外连接结果
    集 加上 右外连接的结果集 然后排出
    重复的记录。
    ar  union    br;
    ar  union all br;
    select  id  from s_emp  union 
    select  id  from s_emp;
    select  id  from s_emp  union  all
    select  id  from s_emp;
    
    
  十一.组函数 
   11.1 组函数
   对一组数据处理之后 返回一个结果
   11.2 常见的组函数
   count    统计个数
   max      统计最大值
   min      统计最小值
   avg      统计平均值
   sum      统计和
   11.3 举例
   统计s_emp 表中员工的个数  和 工资的
   最大值  工资的最小值
   select  count(id),max(salary),
       min(salary)  from s_emp;  
   统计s_emp  表中的工资的平均值 和 
   工资的和。
   select  avg(salary),sum(salary)
       from  s_emp;      
   11.4 组函数中可以使用 distinct 
    select  avg(distinct salary),
       sum(distinct salary)
       from  s_emp;  
   11.5 测试组函数 对NULL处理策略
    (忽略)
    统计提成的个数 提成的和  以及提成的
    平均值。
    select  count(commission_pct),
       sum(commission_pct),
       avg(commission_pct) from s_emp;  
  十二.分组
   12.1 概念
   按照一定的标准  把数据分成若干组。
   12.2 语法
   where  条件 group  by  分组标准;
   按照部门编号分组 统计每个部门的人数
   select dept_id,count(id)
       from s_emp  
           where 1=1  
               group by  dept_id; 
   12.3 按照部门号分组  统计每个部门的
   平均工资。
    select  dept_id,avg(salary)  
        from s_emp
            where 1=1
                group by dept_id; 
   按照部门号分组  统计每个部门的
   平均工资。如果只显示平均工资大于1500
   的部门怎么办?
   对组数据的过滤 需要使用 having
   where 条件  group by  分组标准
       having  过滤条件;
    select  dept_id,avg(salary)  
        from s_emp
            where  1=1
                group by dept_id
                    having avg(salary)>1500;
   12.4 sql的执行顺序?
       from  
           where 
               group 
                   having
                       select
                           order by
                       
   select  dept_id,avg(salary) asal  
        from s_emp
            where  1=1
                group by dept_id
                    having avg(salary)>1500
                        order by asal;   
   12.5 按照部门号分组 统计每个部门的人数
   显示人数大于等于2的部门。 
     select dept_id,count(id) 
         from  s_emp
             where 1=1
                 group  by dept_id
                     having count(id)>=2;
   12.6 按照部门号分组 统计每个部门的人数
   显示人数大于等于2的部门要求显示部门名。
     select dept_id,count(s_emp.id),name
         from  s_emp,s_dept
             where dept_id=s_dept.id
                 group  by dept_id,name
                     having count(s_emp.id)>=2;
     
    select dept_id,count(s_emp.id),min(name) 
         from  s_emp,s_dept
             where dept_id=s_dept.id
                 group  by dept_id
                     having count(s_emp.id)>=2;  
    
    结论:在分组语句中 select后的字段 
    要么是分组标准 要么是经过合适的
    组函数处理过的。
    
 十三.子查询
    13.1 把一个查询的结果 作为另一个查询
    的基础。
    13.2 子查询可以出现的位置
    where 之后
    having 之后
    from   之后
    13.3 where  之后
    /* 把所有的领导id查询出来 */
    select distinct manager_id from s_emp;
    
    select distinct id,first_name 
        from s_emp where id in(select 
        distinct manager_id from s_emp);  
    /* 查询工资 大于25号员工 工资的人 
       列出 id first_name salary */
    select  salary from s_emp where id=25;
    
    select id,first_name,salary
        from s_emp 
            where salary>(select  salary 
            from s_emp where id=25);    
    /* 注意NULL 值
       能不能使用子查询找出那些人是
       普通员工? */ 
    select distinct id,first_name 
        from s_emp where id not in(select 
        distinct manager_id from s_emp
        where  manager_id is not null);
    
    13.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); 
   13.5 from 之后
   任何一个查询的结果 都可以看成一张内
   存表。 
   select  id,first_name name ,salary
       from s_emp;
   
   select id,name from(select  id,
   first_name name ,salary from s_emp)
      where id>10;     
    按照部门号分组 求每个部门的平均工资
    显示大于42部门平均工资的部门。     
    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);
 十四. DDL 
  14.1 建表语句
  create table  表名(
      字段名  类型,
      字段名  类型,
      字段名  类型 
  );   
  /* 建立一张员工表   
     id       number
     efname   varchar2(10)
     esname   char(10)
     salary   number */
  create  table  emp9527(
      id     number,
      efname  varchar2(30),
      esname  char(10),
      salary  number
  );
  14.2 修改表 
  /* 删除一个字段 */
  alter  table emp9527 drop column salary; 
  /* 增加一个字段 */
  alter  table emp9527 add  salary number; 
    
  14.3 删除一张表
  drop  table  表名;   
  drop  table  emp9527;  
    
  14.4  建立一张订单表  order9527
  id     number
  ename  varchar2(30)
  esalary number
  edate   date 
                    
  create table  order9527(
      id  number,
      ename varchar2(30),
      esalary number,
      edate   date
  );                  
  
 十五.DML 
  drop    table  emp9527;
  create  table  emp9527(
      id     number,
      efname  varchar2(30),
      esname  char(10),
      salary  number
  );
  15.1 增加数据
  insert  into  表名 values(值1,值2,值3);
  commit;  
  
  insert  into  emp9527  values(
  100,'xsy','xsy',12345); 
  commit;
  insert  into  emp9527  values(
   101,'zbs','zbs',NULL);
   commit; 
  
  insert  into  表名(字段1,字段3)
      values(值1,值3);
  commit;
  insert  into  emp9527(id,esname)
      values(10086,'zgyd');
  这种语法 必须包含所有的非空字段。
      
  15.2 删除数据
  delete  from  表名 where 条件;
  commit;
  delete  from  emp9527 where id=110;
  
  15.3 修改数据 
  update  表名 set 字段名1=值1,
      字段名2=值2 where 条件;
  commit;    
  
  update  emp9527  set esname='bsds',
      salary=12345678 where id=101;
  commit;       
  
  针对日期类型的值 
  sysdate   系统的当前时间 
  
  
  15.4 练习
  drop   table  order9528;
  create table  order9528(
      id  number,
      ename varchar2(30),
      esalary number,
      edate   date
  ); 
  把下面的数据放入 order9528 
  1     'jdtest1'    12.89  sysdate
  2     'jdtest2'    1000.85 sysdate
  
  把id=1的 esalary 改成128.9  
  把id=2的ename 改成 testjd2  
  insert  into  order9528  values(
  1,'jdtest1',12.89,sysdate);
  insert  into  order9528  values(
  2,'jdtest2',1000.85,sysdate);
  update  order9528  set esalary=128.9
      where id=1;
  update  order9528  set ename='testjd2'
      where id=2;     
  commit;
    
 
 十六.事务控制 
  16.1 概念
  transation   事务 交易 
  多个操作的一个整体   
  16.2 事务的四大特性 
  原子性:事务中的操作时一个整体 要么
         一起成功  要么一起失败。
      转账: account   ano    asalary
                       A     30000
                       B     100
            update account set 
               asalary=asalary-20000 
               where ano='A';
            a                       
            update account set 
               asalary=asalary+20000 
               where ano='B';  
            b
            if(a&&b){
                commit;
            }else{
                rollback;
            }    
  隔离性:一个事务的操作 在没有提交以前
      对另外一个事务而言 数据的变化是
      不可见的。
      commit;
       
  一致性
      
  持久性 
  
  16.3 保存点   savepoint 
  打破 一起成败   一起失败
  允许 部分成功   部分失败
  
  update    a  set  salary=salary+100 
       where id=1; 
  savepoint  pa;
  update    a  set  salary=salary+100 
       where id=3;
  savepoint  pb;       
  insert    into  a  values(....);
  savepoint  pc;
  rollback  to pa;
  commit;
  
  drop    table  emp9529;
  create  table  emp9529(
      id     number,
      efname  varchar2(30),
      esname  char(10),
      salary  number
  );
  insert  into emp9529 values(
  1,'ta','ta',12);
  savepoint   pa;
  insert  into emp9529 values(
  2,'tb','tb',13);
  savepoint   pb;
  insert  into emp9529 values(
  3,'tc','tc',6);
  savepoint   pc;
  insert  into emp9529 values(
  4,'td','td',18);
  rollback  to pb;
  commit;
 
 十七.sql中的类型
      number   数字类型
      char     定长字符串
      varchar2 变长字符串
      date     日期类型
    17.1 建立一张表  叫订单表
    id       number
    fname    char(10)
    sname    varchar(10)
    osalary  number
    odate    date
           
    drop  table  myorder100;
    create table myorder100(
        id   number,
        fname char(10),
        sname varchar(10),
        osalary number,
        odate   date
    );
    insert  into  myorder100 
        values(1,'abc','abc',1.0,sysdate);
    commit;    
    
    select  length(fname),length(sname)
        from myorder100 where id=1; 
    select * from myorder100  where 
        fname='abc ';
    select * from myorder100  where 
        sname='abc ';       
   17.2 日期类型的数据 无论你存储的数据
   是什么样的，显示时 只显示 年 月 日
   格式是  'DD-MON-YY'
   如何改变 日期类型数据的默认格式
   to_char(日期类型数据,'日期格式串')
   yyyy    四位年
   mm      两位月
   dd      两位天
   
   hh      12小时制
   hh24    24小时制
   mi      分钟
   ss      秒
   
   day     星期几
   mon     英文月的缩写
   month   全写
   pm      上午就显示成am  下午 pm
   
   select  to_char(odate,
      'yyyy-mm-dd hh24:mi:ss')
      from  myorder100 where id=1;
   按照入职日期排序 显示 s_emp 表中
   的id   first_name  start_date
   select  id,first_name,start_date
       from s_emp  
           order by start_date; 
           
   select  id,first_name,
       to_char(start_date,
       'yyyy-mm-dd hh24:mi:ss')
       from s_emp  
           order by start_date;
   17.3  如何把日期放入数据库
      to_date('日期字符串','日期格式')
      把一个日期字符串 根据日期格式 
      转换成日期。
      '2012-12-21 23:59:59' 
      '2008-08-08 20:08:08'        
             
     insert  into myorder100 values(
     2008,'bjo','bjo',200000,
     to_date('2008-08-08 20:08:08',
     'yyyy-mm-dd hh24:mi:ss') );  
     
     select  odate  from myorder100
         where id=2008; 
     select  to_char(odate,
     'yyyy-mm-dd hh24:mi:ss day pm mon')  
         from myorder100
             where id=2008;