Oracle学习(四)

     
八.日期类型 
   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 
   8.6  to_date('日期字符串','日期格式')
   insert  into  表名 values(to_date(
   '2008-08-08  20:08:08','yyyy-mm-dd hh24:mi:ss'));
   
   insert  into  myorder9527(ono,odate) 
   values('testbj003',
   to_date('2008-08-08  20:08:08',
   'yyyy-mm-dd hh24:mi:ss')); 
        
   insert  into  myorder9527(odate) values(
   to_date('2008-08-08  20:08:08',
   'yyyy-mm-dd hh24:mi:ss'));  
   8.7 日期的调整
   select  to_char(sysdate,
       'yyyy-mm-dd hh24:mi:ss') from dual;
   /* 1代表一天 */    
   select  to_char(sysdate+1,
       'yyyy-mm-dd hh24:mi:ss') from dual; 
   select  to_char(sysdate,
       'yyyy-mm-dd hh24:mi:ss'),
        to_char(sysdate+1/24,
       'yyyy-mm-dd hh24:mi:ss') from dual; 
       
   select  to_char(sysdate,
       'yyyy-mm-dd hh24:mi:ss'),
        to_char(sysdate+1/(24*60),
       'yyyy-mm-dd hh24:mi:ss') from dual;  
       
   1  1/24  1/(24*60)   1/(24*60*60)
 8.8 特殊调整 
 按照月为单位进行调整
 add_months(日期,月数)
 select  to_char(sysdate,
       'yyyy-mm-dd hh24:mi:ss'),
        to_char(add_months(sysdate,3),
       'yyyy-mm-dd hh24:mi:ss') from dual; 


 select  months_between(sysdate,sysdate+5)
     from dual;
 
 last_day(日期)  一个月份对应的这个月的最后一天
                 的时间点
                 
 select  to_char(sysdate,
       'yyyy-mm-dd hh24:mi:ss'),
        to_char(last_day(sysdate),
       'yyyy-mm-dd hh24:mi:ss') from dual;
 
 next_day(日期,'星期几')
   select  to_char(sysdate,
       'yyyy-mm-dd hh24:mi:ss'),
        to_char(next_day(sysdate,'monday'),
       'yyyy-mm-dd hh24:mi:ss') from dual; 
       
   select  to_char(sysdate,
       'yyyy-mm-dd hh24:mi:ss'),
        to_char(next_day(
        next_day(sysdate,'friday'),'friday'),
       'yyyy-mm-dd hh24:mi:ss') from dual;
 
  round(日期)   默认以天为单位对日期进行四舍五入
   select  to_char(sysdate,
       'yyyy-mm-dd hh24:mi:ss'),
        to_char(round(sysdate),
       'yyyy-mm-dd hh24:mi:ss') from dual;
   round(日期,'日期单位格式')
    select  to_char(sysdate,
       'yyyy-mm-dd hh24:mi:ss'),
        to_char(round(sysdate,'mm'),
       'yyyy-mm-dd hh24:mi:ss') from dual; 
    select  to_char(sysdate,
       'yyyy-mm-dd hh24:mi:ss'),
        to_char(round(sysdate,'hh'),
       'yyyy-mm-dd hh24:mi:ss') from dual;       
  trunc(日期)   默认以天为单位对日期进行截取  
    select  to_char(sysdate,
       'yyyy-mm-dd hh24:mi:ss'),
        to_char(trunc(sysdate),
       'yyyy-mm-dd hh24:mi:ss') from dual;
   round(日期,'日期单位格式')
    select  to_char(sysdate,
       'yyyy-mm-dd hh24:mi:ss'),
        to_char(trunc(sysdate,'mm'),
       'yyyy-mm-dd hh24:mi:ss') from dual; 
    select  to_char(sysdate,
       'yyyy-mm-dd hh24:mi:ss'),
        to_char(trunc(sysdate,'hh'),
       'yyyy-mm-dd hh24:mi:ss') from dual;  
  
  8.9  把一个日期字符串 '2008-08-08 20:08:08'
     经过转换 得到一个对应的日期。然后得到
     这个日期对应的月的最后一天的最后一秒
     对应的时间点。使用to_char 验证是否正确。
     2008-08-31 23:59:59
     
     2012-12-21 10:05:04
     2012-12-31 23:59:59
     select  to_char(
     trunc(last_day(to_date('2008-08-08 20:08:08',
     'yyyy-mm-dd hh24:mi:ss'))+1)-1/(24*60*60),
     'yyyy-mm-dd hh24:mi:ss') from dual;  
     
     select to_char(
     trunc(add_months(to_date('2008-08-08 20:08:08',
     'yyyy-mm-dd hh24:mi:ss'),1),'mm')
     -1/(24*60*60),'yyyy-mm-dd hh24:mi:ss')
     from dual;
  九.数据库中的约束
    9.1  约束
    对数据库中的字段 对应的数据可以加限制。
    9.2  种类
    主键    primary  key
        字段的值 非空 并且 唯一
        一个表只能有一个主键
    唯一    unique
         字段的值  必须保持不能重复
    非空    not  null
         字段的值 不能是NULL值
    检查    check
         字段的值 必须符合检查条件
    外键约束   reference (关联 引用)
               foreign  key
               on  delete  cascade
               on  delete  set null
    9.3 约束的具体实现
    列级约束:在定义表的某一列时 直接对这一列加
        约束限制。
    
    表级约束:在定义完表的所有列之后 再选择某些
        列加约束限制。
    9.4 主键的列级约束
    drop   table  testcolumn_cons;
    create table  testcolumn_cons(
        id    number  primary  key,
        name  varchar2(20)
    );                                          
    insert   into  testcolumn_cons 
        values(1,'test1'); 
    ERROR at line 1:
    ORA-00001: unique constraint 
    (OPENLAB.SYS_C00444679) violated  
    如果加约束时 不给约束起名字 则系统会自动
    分配一个约束名。 
    
    可以给约束命名    
    drop   table  testcolumn_constest1;
    create table  testcolumn_constest1(
        id    number constraint 
         testcolumn_constest1_id_pk  primary  key,
        name  varchar2(20)
    );                                          
    insert   into  testcolumn_constest1 
        values(1,'test1'); 
        
   ERROR at line 1:
   ORA-00001: unique constraint 
   (OPENLAB.TESTCOLUMN_CONSTEST1_ID_PK) violated
  9.5 建立一张表
     id     number   设置成主键
     fname  varchar2(30)  设置成非空
     sname  varchar2(30)  设置成唯一
     salary number        设置检查 必须大于3500
    drop   table  testemp1986abc; 
    create table  testemp1986abc(
        id    number constraint 
          testemp1986abc_id_pk primary key,
        fname  varchar2(20)  constraint 
          testemp1986abc_fname_nn not null,
        sname  varchar2(20)  constraint 
          testemp1986abc_sname_uk  unique,
        salary  number   constraint
          testemp1986abc_salary_ck  check(
          salary>3500 )  
     );         
   9.6 表级约束 主键
      表级约束 可以完成联合约束
      create  table  testtable_cons(
          id   number,
          name  varchar2(30),
          salary  number ,
          constraint testtable_cons_id_pk  
          primary  key(id,name)   
      );
       create  table  testtable_cons(
          id   number,
          name  varchar2(30),
          salary  number ,
          constraint testtable_cons_id_pk  
          primary  key(id),
          constraint testtable_cons_name_uk
          unique (name),
          constraint testtable_cons_salary_ck
          check(salary>3500)   
       );
 十.外键约束
    10.1  涉及到两张表   一张叫父表(主表)  
    一张叫子表(从表)。子表中的外键字段的取值
    受限于父表中字段的取值。
    外键的取值 要么取父表中字段的值 
    要么取NULL值。
    10.2 外键关系的实现
       10.2.1 建立表
       一般先建立父表  后建立子表
       drop    table  parent1234a;
       create  table  parent1234a(
           id    number   primary  key,
           name  varchar2(30)
       );                        
       drop    table  child1234a;                     
       create  table  child1234a(
           id    number   primary  key,
           age   number,
           fid   number  constraint 
               child1234a_fid_fk references  
               parent1234a(id)
       ); 
       10.2.2  插入数据
          一般先插入父表数据 后插入子表数据
          否则子表的外键取值要取NULL
          insert  into child1234a values(
          9527,50,1);                                   
          ERROR at line 1:
         ORA-02291: integrity constraint 
         (OPENLAB.CHILD1234A_FID_FK) violated -
          parent key not found                                  
         insert  into child1234a values(
          9527,50,NULL);
          
         insert  into parent1234a values(
          1,'parent1');
         insert  into child1234a values(
          9529,55,1);  
      10.2.3  删除数据?
         先删子表中和父表关联的数据
         再删父表数据
         级联 （on delete cascade ） 
      10.2.4  删除表 
         先删子表  后删父表
         /* 先解除主外键关系 后删表 */
         drop   table   表名 cascade constraints;
   10.3 级联删除 和 级联置空
       on  delete  cascade
       员工表   m  (子表)
       id  
       ename
       eage
       dept_id
       
       部门表   1 （主表）
       id
       name 
       
       drop    table  mydept1402 cascade constraints;
       create  table  mydept1402(
           id    number   primary key,
           name  varchar2(30)
       );
       insert  into mydept1402 values(1,'app');
       insert  into mydept1402 values(2,'test');
       commit;
       drop    table  myemp1402 cascade constraints;
       create  table  myemp1402(
           id    number  primary key,
           ename  varchar2(30),
           eage   number,
           dept_id   number  constraint  
           myemp1402_dept_id_fk references 
           mydept1402(id)
       );
       insert  into  myemp1402 values(1,'ea',
       24,1);
       insert  into  myemp1402 values(2,'eb',
       25,1);
       insert  into  myemp1402 values(3,'ec',
       26,2);
       insert  into  myemp1402 values(4,'ed',
       24,2);
       insert  into  myemp1402 values(5,'ee',
       27,2);
       commit;
   
      on delete cascade  删除主表数据时 会把
      和主表关联的子表数据删除。
      on delete set  null 删除主表数据时 会把
      和主表关联的子表数据的外键置成null。
      
   10.4 修改脚本  把列级约束的外键实现 写成
       表级约束的实现。
       
       ALTER TABLE s_emp
       ADD CONSTRAINT s_emp_dept_id_fk
       FOREIGN KEY (dept_id) 
       REFERENCES s_dept (id);
       先建立两张表  然后通过修改表结构 增加
       外键约束，但这样对数据要求非常严格。
 十一.数据库中其它对象
      11.1 序列  sequence  
          用来产生主键的值。      
          如何创建序列
          create   sequence  序列名;
          如何使用 
          在需要主键值的地方 写 序列名.nextval      
   
          create  sequence  testmyseqbdl_id;             
          create  table     testmyseqbdl(
              id    number  primary key,
              name  varchar2(30) 
          );            
                      
          insert  into  testmyseqbdl  values(
          testmyseqbdl_id.nextval,
          'test'||testmyseqbdl_id.currval);            
      11.2  索引 
          目的:
           加速查询。
           3亿       8*60  全表扫描
                     0.01  索引查找 
           原理:
           通过树状结构组织数据  通过消耗
           大量的时间 和 空间 来加速查询。
           
           语法:
           具有唯一性字段的数据 会自动建立索引
           叫唯一性索引。
           create   index  索引名  
               on  表名(字段名);
           set  timing on;    
           create   table    testemp1402 
             as select id,first_name name,
             salary from s_emp;    
           create   index  testemp1402_name_ind
               on  testemp1402(name);  
           删除索引
           drop  index  序列名;
       11.3 视图  view
           本质:
           视图本质上 就是一条sql语句。
           如何创建视图
           create   or replace view  视图名  as
               select  语句;
           可以对同一份物理数据 做不同的表现  
           可以简化查询  
           create   or replace  view  myview 
               as select id ,first_name name,
               salary  from s_emp;   
           select  * from (select id ,first_name
               name, salary  from s_emp);
           select  * from  myview;     
           
  十二.分页技术
       oracle    rownum 
       sqlserver  top 
       mysql      limit  m,n 
       
       rownum  行号  伪列
       select  rownum, id,first_name  from s_emp; 
       一页显示 5 行   取第一页数据
       select  rownum, id,first_name  from s_emp
           where rownum<6;
       select * from(select  rownum r, id,
         first_name  from s_emp where rownum<11)
         where r>5;   
         
       按照salary 排序  一页显示 5 条数据 
       显示第二页数据。
          first_name  salary
       select  first_name ,salary  from s_emp
            order by salary;
       先排序 还是先编号?
       select rownum, first_name ,salary  
           from s_emp
              order by salary;  
       先排序 后编号
       select * from(
         select rownum r,first_name,salary from
          (select first_name ,salary  
           from s_emp
           order by salary
          )where rownum<11
       )where  r>5;  
      按照某个字段排序 要第n页数据 至少三层查询
      最内层 负责排序
      中间层 负责编号 并去rownum特性 
      最外层 在去除rownum特性的基础上
             过滤掉 第n-1页数据      
      按照salary 排序  一页显示 5 条数据 
       显示第三页数据。
       select * from(
         select rownum r,first_name,salary from
          (select first_name ,salary  
           from s_emp
           order by salary
          )where rownum<n*pagesize+1
       )where  r>(n-1)*pagesize;         
       
       select * from(
         select rownum r,first_name,salary from
          (select first_name ,salary  
           from s_emp
           order by salary
          )where rownum<3*5+1
       )where  r>(3-1)*5;  
       
       
       select * from(
         select rownum r,first_name,salary from
          (select first_name ,salary  
           from s_emp
           order by salary
          )where rownum<3*11+1
       )where  r>(3-1)*11;