ここではORACLEデータベースで、テーブルを作成・変更・削除するSQLを紹介しています。
テーブルの一覧やサイズを確認する方法は↓で紹介しています。
＞＞【ORACLE】テーブルの一覧やサイズを確認するSQL
テーブルとは
テーブルとは、データベースでデータを格納しているオブジェクトです。
テーブルは行(ROW)と列(COLUMN)からなる表形式でデータを保持します。
保存したデータはSQLを使って中身を確認したり、更新や削除、新しいデータを登録することが出来ます。
データは1件を1行や1レコードと言ったりします。
テーブルを作成するSQL
テーブルの作成はCREATE TABLE文を使います。
テーブルの名前は最大30バイトです。
テーブルの作成や変更、削除には↓の権限が必要です。
作成：CREATE TABLE、CREATE ANY TABLE
変更：ALTER ANY TABLE
削除：DROP ANY TABLE
システム権限の付与の方法については↓で紹介していますので参考にしてください。
＞＞【ORACLE】システム権限を付与・取消するSQL
CREATE TABLE文の基本構文
CREATE TABLE文の基本構文は次の通りです。
CREATE TABLE テーブル名(
列名 データの型 [列の制約]
[,列名 データの型 [列の制約]]
)
[TABLESPACE 表領域名]
;[TABLESPACE 表領域名]でテーブルを作成する表領域を指定します。指定しない場合はデフォルト表領域になります。
CREATE TABLE文を使ったSQLサンプル
CREATE TABLE文のサンプルです。
次のSQLでは、TAB1というCOL1~3のカラムを持ったテーブルをTBS1という表領域に作成しました。
CREATE TABLE tab1(
col1 varchar(10)
,col2 NUMBER
,col3 TIMESTAMP
)
TABLESPACE tbs1
;CREATE TABLE文で指定できるその他のパラメータ
CREATE TABLE文ではTABLESPACEの他にも指定できるパラメータがあります。
主なパラメータは次の通りです。
CREATE TABLE テーブル名(
列名 データの型 [列の制約]
[,列名 データの型 [列の制約]]
)
[TABLESPACE 表領域名]
[PCTFREE ブロックの空き領域の割合]
[PCTUSED ブロックの使用領域割合]
[STRAGE
(
[INITIAL サイズ]
[NEXT サイズ]
[MINEXTENTS エクステント数]
[MAXEXTENTS エクステント数 | UNLIMITED]
[PCTINCREASE エクステントサイズ拡大率]
)
]
;・[PCTFREE ブロックの空き領域の割合]
ブロックに対する空き領域の割合を指定します。デフォルトは10です。
・[PCTUSED ブロックの使用領域割合]
ブロックの使用する領域の割合を指定します。デフォルトは40です。
・[INITIAL サイズ]
初期エクステントのサイズをバイト単位（KまたはM）で指定します。
・[NEXT サイズ]
初期エクステントが不足したときに追加するサイズをバイト単位（KまたはM）で指定します。
・[MINEXTENTS 最小エクステント数]
オブジェクト作成時の最小エクステント数を指定します。
・[MAXEXTENTS エクステント数 | UNLIMITED]
エクステントの最大数を指定します。UNLIMITEDの場合は無制限になります。
・[PCTINCREASE エクステントサイズ拡大率]
エクステントを増加する割合を指定します。デフォルトは50です。
別スキーマのテーブルの参照
別スキーマの表を参照するには、事前にシステム権限のSELECT ANY TABLE権限を付与する必要があります。
SELECT ANY TABLE権限はGRANT文で付与します。
次のSQLではGRANT文を使って、user1にSELECT ANY TABLE権限を付与しています。
GRANT SELECT ANY TABLE TO user1;システム権限の付与や取消については↓で詳しく解説していますので参考にしてください。
＞＞【ORACLE】システム権限を付与・取消するSQL
別スキーマのテーブルを参照するときは、テーブル名をFROM句で指定するとき「スキーマ名.テーブル名」とします。
次のSQLでは、user2スキーマのtab1テーブルを参照しています。
SELECT * FROM user2.tab1テーブルをコピーして別のテーブルを作成する
あるテーブルをコピーし、別のテーブルを作成するにはCREATE ～ AS SELECT文を使います。
この方法を使うことで、SELECT結果から簡易的にバックアップテーブルを作成することが出来ます。
CREATE ～ AS SELECT文の基本構文は次の通りです。
CREATE TABLE テーブル名
AS SELECT 列名 FROM コピー元テーブル名;次のSQLでは、TAB1テーブルの列名、データ型、データをTAB2テーブルとしてコピーしています。
CREATE TABLE tab2
AS SELECT * FROM tab1;テーブルの定義は同じでレコードをコピーしたくない場合は、WHERE句で条件を指定してレコード件数が0になるようにするとテーブル定義だけコピーすることが出来ます。
CREATE TABLE tab2
AS SELECT * FROM tab1 WHERE 0=1;テーブルの定義情報（DDL文）を取得してコピーすることもできます。
テーブルのDDL文（CREATE文）は、DBMS_METADATAパッケージのGET_DDLファンクションを使うことで取得することが出来ます。
DBMS_METADATAについては↓で紹介していますので参考にしてください。
＞＞テーブルの定義、列名一覧、名前（コメント）、DDLの確認方法
テーブルを変更するSQL
ALTER文を使うことで、テーブルを変更することが出来ます。
テーブル名を変更するSQL
ALTER TABLE tab1 RENAME TO tab2;テーブルの表領域を変更するSQL
ALTER TABLE tab1 MOVE TABLESPACE tbs2;テーブルを読取専用に変更するSQL
ALTER TABLE tab1 READ ONLY;テーブルを削除するSQL
DROP：テーブル定義とデータ全ての削除
表・テーブルの定義と全てのレコードを削除するときは、DROP文を使います。
DELETEやTRUNCATEと違い、テーブルそのものを定義ごと削除します。十分に注意して使ってください。
基本構文は次の通りです。
DROP TABLE テーブル名;外部キー制約を同時に削除する場合は「CASCADE CONTRAINTS」を指定します。
DROP TABLE tab1 CASCADE CONTRAINTS;DELETE：表のデータを削除
表・テーブルのレコードを削除するには、DELETE文を使います。
DELETE文の基本構文は次の通りです。WHERE句の条件は任意でつけることが可能です。
DELETE FROM テーブル名 [WHERE 条件];削除する対象のレコードを絞りたい場合はWHERE句に条件を指定します。次のSQLでは、COL1列の値が’00001’のレコードだけを削除しています。
DELETE FROM tab1 WHERE col1 = '00001';TRUNCATE文：表のレコードを全件削除する
表・テーブルのレコードを全件削除するときは、DELETE文のほかに、TRUNCATE文が使えます。
TRUNCATE文は、DELETE文と違い、「１．一括削除しかできない」「２．ロールバックできない」「３．DELETE文よりも処理時間が速い」という特長があります。
特にロールバック出来ないので、実行する際は注意してください。
TRUNCATE文の基本構文は次の通りです。
TRUNCATE TABLE テーブル名;DROPで削除したテーブルの復活
ドロップした表は、削除した直後はゴミ箱にあります。ゴミ箱から元の場所に戻すことで表を復活させることが出来ます。
削除したテーブルの名前を確認し、復活するSQLを実行します。
削除したテーブルはDBA_RECYCLEBINで参照することが出来ます。
SELECT * FROM DBA_RECYCLEBIN WHERE TYPE = 'TABLE';削除したテーブルはFLASHBACK TABLEで復活することが出来ます。
FLASHBACK TABLE テーブル名 TO BEFORE DROP;まとめ
テーブルの作成はCREATE TABLE、変更はALTER文、削除はDROP文を使います。
テーブルの作成や変更、削除には↓の権限が必要です。
作成：CREATE TABLE、CREATE ANY TABLE
変更：ALTER ANY TABLE
削除：DROP ANY TABLE
システム権限の付与の方法については↓で紹介していますので参考にしてください。
＞＞【ORACLE】システム権限を付与・取消するSQL