oracle_day04

   十七.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;   
   17.4 日期调整
   按照天  小时 分钟 秒为单位进行调整
   select  to_char(sysdate,
      'yyyy-mm-dd hh24:mi:ss') from dual;    
   select  to_char(sysdate,
      'yyyy-mm-dd hh24:mi:ss'),
      to_char(sysdate+4,
      'yyyy-mm-dd hh24:mi:ss') from dual;
      
   select  to_char(sysdate,
      'yyyy-mm-dd hh24:mi:ss'),
      to_char(sysdate+4*(1/24),
      'yyyy-mm-dd hh24:mi:ss') from dual;
      
   /* 把当前时间 向后调34分钟 */
   select  to_char(sysdate,
      'yyyy-mm-dd hh24:mi:ss'),
      to_char(sysdate+34*(1/(24*60)),
      'yyyy-mm-dd hh24:mi:ss') from dual;   
   /* 把当前时间 向前调一秒 */
   select  to_char(sysdate,
      'yyyy-mm-dd hh24:mi:ss'),
      to_char(sysdate-1*(1/(24*60*60)),
      'yyyy-mm-dd hh24:mi:ss') from dual;
   17.5 其它调整 
      按照月为单位进行调整
      add_months(日期,月数)
    select  to_char(sysdate,
      'yyyy-mm-dd hh24:mi:ss'),
      to_char(add_months(sysdate,-2),
      'yyyy-mm-dd hh24:mi:ss') 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;
   
    '2014-09-01 08:01:09' 
    对应的月的最后一天的时间点 
    select  to_char(last_day(
     to_date('2014-09-01 08:01:09',
    'yyyy-mm-dd hh24:mi:ss')),
    'yyyy-mm-dd hh24:mi:ss') from dual;
    
    求下一个星期几     
    next_day(日期,'星期几')
    select next_day(sysdate,'friday')
        from dual;
    select next_day(sysdate,'friday')+7
        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;
    select  to_char(sysdate,
      'yyyy-mm-dd hh24:mi:ss'),
      to_char(round(sysdate,'yy'),
      'yyyy-mm-dd hh24:mi:ss') from dual; 
      
    select  to_char(sysdate,
      'yyyy-mm-dd hh24:mi:ss'),
      to_char(round(sysdate,'mm'),
      '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;
    select  to_char(sysdate,
      'yyyy-mm-dd hh24:mi:ss'),
      to_char(trunc(sysdate,'yy'),
      'yyyy-mm-dd hh24:mi:ss') from dual; 
   select  to_char(sysdate,
      'yyyy-mm-dd hh24:mi:ss'),
      to_char(trunc(sysdate,'mm'),
      'yyyy-mm-dd hh24:mi:ss') from dual;    
   17.6 实例
   给定一个日期  获取这个日期的对应的
   月的最后一天的最后一秒    
   sysdate  7  '2014-07-31 23:59:59'
   
   '2008-08-08 20:08:08'
   '2008-08-31 23:59:59'
   
   select to_char(
     trunc(last_day(sysdate))+1
     -(1/(24*60*60)),
     'yyyy-mm-dd hh24:mi:ss') from dual;
     
   select to_char(add_months(
    trunc(sysdate,'mm'),1)-(1/(24*60*60)),
     'yyyy-mm-dd hh24:mi:ss') from dual;
    
十八.约束
   18.1  概念
   数据库表中的字段 可以设置一些限制,
   满足限制条件就可以进入数据库,如果
   违反限制条件则不能进入数据库。
   18.2 约束的种类
   主键        primary  key
       如果一个字段设置了主键约束 则
       这个字段必须满足值唯一,还要满足
       非空。
       一个表中只能有一个主键。
   唯一性      unique
       这个字段的值 不能重复。
   非空        not null
       这个字段的值 不能为NULL值 
   检查        check
       这个字段的值  必须符合检查条件
       不符合则不允许进入数据库
   外键        references 
               foreign  key 
               on  delete  cascade
               on  delete  set null                                        
   
   18.3  约束实现方式
   列级约束:在定义表的某一列时  直接对
       这一列加约束限制。 
   
   表级约束:在定义完表的所有列之后，再
       选择某些列加约束限制。 
   18.4 主键的列级约束实现
   create  table  test_col_cons(
       id   number primary key,
       name   varchar2(30)
   );
   insert  into test_col_cons values(
    1,'test1');
  ERROR at line 1:
  ORA-00001: unique constraint
  (OPENLAB.SYS_C00481124)violated
  18.5 如果一个约束不起名字 则系统默认
  给这个约束起一个名字。
  可以给这个约束 自己进行命名。
   constraint   约束名   约束关键字。
   drop    table  test_col_cons;
   create  table  test_col_cons(
       id   number constraint
         test_col_cons_id_pk primary key,
       name   varchar2(30)
   );
   insert  into test_col_cons values(
    1,'test1');
   ERROR at line 1:
   ORA-00001: unique constraint 
   (OPENLAB.TEST_COL_CONS_ID_PK) violated  
  
  18.6 建立一张表 使用列级约束
   要求约束名按照 表名_字段名_约束类型
   id    number    primary key
   fname  varchar2(30) unique 
   sname  varchar2(30) not null 
   salary  number
   (给salary 加一个检查约束 要求满足
   检查条件 大于4500 )
   drop    table  emp1001;
   create  table  emp1001(
       id  number  constraint  
         emp1001_id_pk  primary key,
       fname  varchar2(30) constraint 
         emp1001_fname_uk  unique,
       sname  varchar2(30) constraint 
         emp1001_sname_nn  not null,
       salary  number      constraint 
         emp1001_salary_ck  
           check(salary>4500)        
   );
   
   insert  into emp1001  values(
   1,'abc','abc',4501);
   
  18.7 主键的表级约束
   create  table  emp1002(
       id   number ,
       fname varchar2(30),
       sname varchar2(30),
       salary   number , constraint 
         emp1002_id_pk  
           primary key(id,fname)
   ); 
   表级约束的优势在于可以做联合约束。
   not  null  数据库层面上 没有联合约束
   的需求 所有not null 没有表级约束。
   id   fname 
   1     a 
   1     b
   create  table  emp1001(
       id  number  constraint  
         emp1001_id_pk  primary key,
       fname  varchar2(30) constraint 
         emp1001_fname_uk  unique,
       sname  varchar2(30) constraint 
         emp1001_sname_nn  not null,
       salary  number      constraint 
         emp1001_salary_ck  
           check(salary>4500)        
   );  
   drop    table  emp1002;
   create  table  emp1002(
       id   number ,
       fname varchar2(30),
       sname varchar2(30) constraint
       emp1002_sname_nn not null,
       salary   number , constraint 
         emp1002_id_pk  
           primary key(id),constraint 
         emp1002_fname_uk unique(fname),
       constraint  emp1002_salary_ck
         check(salary>4500)     
   ); 


十九.外键约束
   19.1 概念
   如果一张表一个字段 引用另外一张表的
   一个字段值,这张表的字段 要么取引用
   表的字段值 要么取空值,则这两张表的
   关系是具有主外键关系的。
   
   子表:定义了外键的表 就是子表。
   19.2 语法
      19.2.1 建立表
      一般先建立父表 后建立子表
      drop   table  parent100 
         cascade constraints;
      create table  parent100(
          id  number constraint 
              parent100_id_pk primary key,
          name varchar2(30)     
      );
      drop   table  child200 
         cascade constraints;  
      create table  child200(
          id  number  constraint 
              child200_id_pk  primary key,
          name  varchar2(30),
          fid  number constraint 
              child200_fid_fk references 
              parent100(id)      
      );  
      19.2.2 插入数据
      一般先插入父表数据 后插入子表数据
      除非子表中的外键字段值 是NULL值。
      insert  into  child200 values(
         1,'abc',1); 
      ERROR at line 1:
      ORA-02291: integrity constraint 
      (OPENLAB.CHILD200_FID_FK) violated 
      - parent  not found  
      insert  into  child200 values(
         1,'abc',NULL); 
      insert  into  parent100 values
      (1,'testa'); 
      insert  into  parent100 values
      (2,'testb');   
      insert  into  child200 values(
         2,'abc',2);           
     
     19.2.3 删除数据
       一般先删除子表中和 父表关联的数据
       再删除父表数据。除非父表的数据没有
       和子表进行关联。           
      ORA-02292: integrity constraint (OPENLAB.CHILD200_FID_FK) violated - child
      record found
     19.2.4 删除表 
       一般先删子表  后删父表 
       除非使用 cascade  constraints;
       drop   table  parent100;
     ERROR at line 1:
     ORA-02449: unique/primary keys in 
     table referenced by foreign keys
       drop   table  parent100 cascade
           constraints;
   19.3 使用列级别的外键约束 
       建立两张表  一张 叫部门表
       id      number   primary key    
       name    varchar2(30)
       
       再建立一张表 叫 员工表
       id     number   primary  key
       name   varchar2(30)
       salary  number
       dept_id  number   外键 
       
       分别向部门表中插入两条数据 
       1    test1
       2    test2
       员工表中的数据  
       1    empa   2000  1
       2    empb   3000  1
       3    empc   5000  1
       4    empd   7000  2
       5    empe   9000  2         
         
       要求先删除表  后建立表 再插入数据  
       drop   
   
       1.按照需求书写脚本
       2.把脚本上传到服务端
       3.执行脚本
   19.4 如何做级联?
      on  delete cascade 
      (级联删除 )
      on  delete set null 
      (级联置空 )
   19.5 外键的表级约束
   create table  myemp200bdl(
    id  number constraint myemp200bdl_id_pk
      primary key,
    name  varchar2(30),
    salary  number,
    dept_id   number, constraint 
      myemp200bdl_dept_id_fk 
        foreign key(dept_id) references 
        mydept200bdl(id)  
        on delete set null
  );
  
  二十.数据库中的其它对象
     20.1 序列  sequence 
        作用:用来产生主键的值。
        语法:
            创建序列
            create sequence  序列名;
            使用序列
            insert  into 表名 values(
            序列名.nextval,
            'test'||序列名.currval);   
        举例:
        create table test1405seq(
            id   number  primary key,
            name varchar2(30)
        );    
        create sequence test1405seq_id;
        insert into   test1405seq values(
           test1405seq_id.nextval,
           'test'||test1405seq_id.currval
        );
        
        'jd20140728_0000001'
    20.2 索引   index
        目的:加速查询 
        3亿条 不加任何优化  500s
              索引优化      0.01s  
        set timing on;
        如何做到:
        底层通过树状结构 来组织数据
        这样消耗掉了大量的空间 和 时间
        达到加速查询的目的。
        语法:
        主键字段 和 唯一性字段 系统会自动
        建立索引 叫唯一性索引。
        create  index  索引名 on
            表名(字段名);
        
        create  table  test1405indemp as
           select id,first_name name,
           dept_id  from  s_emp;    
       
       create  index  
         test1405indemp_name_ind
         on  test1405indemp(name);      
        
       /* 删除索引 */
       drop  index  索引名;     
   20.2 视图   view
   
   
   
   分页