DB/SQL/ɽ¤ÎºîÀ®
̿̾µ¬Â§†
ɽ¤Î̾Á°¡¢¤Þ¤¿¤ÏÎó¤Î̾Á°¤È¤·¤Æ»È¤¨¤ëʸ»úÎó¤Î¾ò·ï¤Ï°Ê²¼¤ÎÄ̤ꡣ
- ʸ»ú¤Ç³«»Ï¤¹¤ë¤³¤È
- 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 ¶ç¤ÎÃͤ˺ÇŬ¤Ê¥¨¥¯¥¹¥Æ¥ó¥È¥µ¥¤¥º¤¬ºÇÄã¸ÂɬÍפʸĿô¤¬¼«Æ°¤Ç³ä¤êÅö¤Æ¤é¤ì¤ë¡£uniform size ¤Î¾ì¹ç¤Ï uniform size ¤Ç»ØÄꤷ¤¿¥µ¥¤¥ºÃ±°Ì¤Ç initial ¶ç¤òËþ¤¿¤¹ºÇÄã¤Î¸Ä¿ô¤¬¼«Æ°¤Ç³ä¤êÅö¤Æ¤é¤ì¤ë
- next ¶ç¤ÏÄɲädzä¤êÅö¤Æ¤Æ¤¤¤¯¥¨¥¯¥¹¥Æ¥ó¥È¥µ¥¤¥º¤Î»ØÄê¡£initial ¤ÈƱÍÍ¡¢É½Îΰè¤Î¥¨¥¯¥¹¥Æ¥ó¥È´ÉÍý¤Ë¤è¤Ã¤Æ³ä¤êÅö¤Æ¤é¤ìÊý¤¬ÊѤï¤ë
- ¤Û¤«¤Îɽ¤ò¥Ñ¥¯¤Ã¤Æºî¤ë¤È¤¤Ï¡¢Îó̾¡Ê¾å¤ÎÎã¤Ç¤¤¤¦ fuga, piyo ¤ÎÉôʬ¡Ë¤Ï¤Ê¤¯¤Æ¤â¤¤¤¤
¥Ç¡¼¥¿·¿†
Îó¤Î¼ç¤Ê¥Ç¡¼¥¿·¿¤Ï°Ê²¼¤ÎÄ̤ꡣ
- ¥Ç¡¼¥¿·¿
¥Ç¡¼¥¿·¿ ÀâÌÀ 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 ¤Ïµö²Ä¤µ¤ì¤ë¡£¤¿¤È¤¨¤Ð¾å¤ÎÆó¸ÄÌܤÎÎã¤Ç¡¢°Ê²¼¤Î¾õÂ֤ˤʤäƤâÀ©Ìó°ãÈ¿¤Ë¤Ï¤Ê¤é¤Ê¤¤
fuga piyo 1 null 2 null null 1 null null null null
- Ê£¹ç°ì°Õ¥¡¼¤Ç¤â NULL ¤Ïµö²Ä¤µ¤ì¤ë¡£¤¿¤È¤¨¤Ð¾å¤ÎÆó¸ÄÌܤÎÎã¤Ç¡¢°Ê²¼¤Î¾õÂ֤ˤʤäƤâÀ©Ìó°ãÈ¿¤Ë¤Ï¤Ê¤é¤Ê¤¤
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