DB/SQL/ɽκ

Top / DB / SQL / ɽκ

̿̾§

ɽ̾ޤ̾ȤƻȤʸξϰʲ̤ꡣ

  • ʸdzϤ뤳
  • 1 ʸ 30 ʸǤ뤳
  • [A-Za-z0-9_$#] ʤ뤳
  • Ʊ桼νͭ¾Υ֥Ȥ̾Ƚʣʤ
  • ͽǤʤ

ɽκ

  • ˡ
    -- ʤ
    create table hoge
        ( <column-name> <data-type> [, <column-name> <data-type> [, ...]] );
    
    -- ŪˤϤ
    create table hoge
        ( fuga number(1),
          piyo number(1),
          foo  varchar2(2),
          bar  varchar2(2) );
    
    -- ۤɽѥäƺȤϤ
    create table hoge (fuga, piyo)
        as select bar, baz
           from foo
           where bar > 5;
    
    -- Ȥɽΰʤɤꤹ
    create table hoge ......
        tablespace fuga
        storage (
            initial 64k
            next 64k
        );
    
  • ­
    • ۤɽѥäƺȤϡ̾ʾǤ fuga, piyo ʬˤϤʤƤ⤤
      • ʤˤ硢hoge ɽˤ bar baz 󤬤Ǥ
    • ۤɽѥäƤȤϡȤ˻äƤ
    • ۤɽѥäƤƤ⡢NOT NULL ʳŬѤʤ
    • initial ϽƥȤΥλꡣɽΰΥƥȴ autoallocate ξϡinitial ͤ˺ŬʥƥȥɬפʸĿưdzƤ롣uniform size ξ uniform size ǻꤷñ̤ initial θĿưdzƤ
    • next ɲädzƤƤƥȥλꡣinitial Ʊ͡ɽΰΥƥȴˤäƳƤѤ

ǡ

μʥǡϰʲ̤ꡣ

  • ǡ
    ǡ
    varchar2(<size>)Ĺʸ
    char(<size>)Ĺʸ
    number(<precision>, <size>)ʲ<size> ޥʥˤȴݤ롣<size> ϻꤷʤƤɤ
    dateդȻ
    longĹʸ
    timestamp [with [local] timezone]ôޤ฽߻
    interval year to monthδֳ֤¸ʤ褯狼ġġ

ץ

DEFAULT ץ

Υǥեͤꤹ

  • ˡ
    create table hoge
        ( fuga number(1)  default 0,
          piyo varchar(3) default 'foo');
    

̾ɬܤǤϤʤɤ⡢ѾĤۤɤ

ʤʤ顢̾ϱѾ塢ޤѾ塢ʹ֤ܤ˿ǻȤ뤳Ȥ¿顣

PRIMARY KEY UNIQUE 줿ˤ̾Ʊ̾ΰպ뤳Ȥ顢ƥˤ̿̾ǤȽ˷礱롣뤤ϡ̵̾ͭڤؤݡSQL*Loader ʤɤɤ߹Ȥʤɡˤˤ⡢ƥˤ̿̾Ǥ̾λȤ˼֤롣

PRIMARY KEY

祭Ǥ롣NULL ͤϵʤNOT NULL 󤬼ưղäˡ

٥롢ޤɽ٥ǽʣ祭ɽ٥Τߡ

  • ˡ
    -- ٥Ǥ
    create table hoge
        ( fuga number(1) constraint hoge_pk primary key);
    
    -- ɽ٥Ǥ
    create table hoge
        ( fuga number(1),
          piyo number(1),
          constraint hoge_pk primary key (fuga, piyo));
    

UNIQUE

󤬰դǤ뤳Ȥݾڤ롣NULL ϤϵĤ롣

٥롢ޤɽ٥ǽʣեɽ٥Τߡ

  • ˡ
    -- ٥Ǥ
    create table hoge
        ( fuga number(1) constraint hoge_fuga_uk unique);
    
    -- ɽ٥Ǥ
    create table hoge
        ( fuga number(1),
          piyo number(1),
          constraint hoge_fugapiyo_uk unique (fuga, piyo));
    
  • ­
    • ʣեǤ NULL ϵĤ롣Ȥоܤǡʲξ֤ˤʤäƤȿˤϤʤʤ
      fugapiyo
      1null
      2null
      null1
      nullnull
      nullnull

NOT NULL

NULL γǼĤʤ

٥Τߤǽ

  • ˡ
    -- ٥Ǥ
    create table hoge
        ( fuga number(1) constraint hoge_fuga_nn not null);
    

FOREIGN KEY

󤬳Ǥ롣

٥롢ޤɽ٥ǽʣ糰ɽ٥Τߡ

  • ˡ
    -- ٥Ǥ
    -- foo ɽ bar 򻲾Ȥ
    create table hoge
        ( fuga number(1) constraint hoge_fuga_fk foreign key references foo (bar));
    
    -- ɽ٥Ǥ
    -- foo ɽ bar  baz 򻲾Ȥ
    create table hoge
        ( fuga number(1),
          piyo number(1),
          constraint hoge_fugapiyo_fk foreign key (fuga, piyo)
              references foo (bar, baz));
    
  • ­
    • ¾ΥץȤưʲʻǽ
      ץ
      on delete cascadeιԤȻȸιԤ
      on delete set nullιԤȻȸιԤ NULL ˤ
    • 嵭ץꤷʤϡ˻ȸιԤʤ¤ꡢιԤϺǤʤʤʥơ֥뤴Ȥäʤ
    • ϰդǤɬפPRIMARY KEY ⤷ UNIQUE

CHECK

˳Ǽͤ¤롣

  • ˡ
    -- ٥Ǥ
    create table hoge
        ( fuga number(1) constraint hoge_fuga_min check (fuga > 5));
    
    -- ɽ٥Ǥ
    create table hoge
        ( fuga number(1),
          piyo number(1),
          constraint hoge_fuga_min check (fuga > 5));
    

ɽѹ

¸ɽѹ롣

  • ˡ
    -- ɲ
    alter table hoge add (fuga number(1));
    
    -- ˥ǥեȤ
    alter table hoge add (fuga number(1) default 0);
    
    -- ¸ѹ
    alter table hoge modify (fuga number(1));
    
    -- ¸
    alter table hoge drop fuga;
    
  • ­
    • ѹ˰ڤ̷⤬ʤ˸¤ѹǤ
      • ȤСǤ 123 ǼƤ number(3) number(1) ˤѹϤǤʤ
      • number(3) 󤬤٤ NULL äꡢ1 οäƤʤ֤Ǥ number(1) ˤѹǤ

ɽκ

  • ˡ
    drop table hoge;
    
  • ­
    • DDL ʸʤΤǡαΥȥ󥶥ޤƼưǥߥåȤʥХåǤʤ
    • ӥ塼ȥΥ˥ϻĤ뤬̵ˤʤ

ɽγǧ

SQL*Plus ΥޥɤʤΤѤ SQL ǤϤʤ

¹Ԥ˸¤ɬפʤȤ¤ʤƤ desc ϤǤ롣

  • ˡ
    desc <tablename>
    

Last-modified: 2011-11-07 () 14:31:49 (2449d)