DDL文
本日もまた、SQL基礎の勉強まとめ。今回はやっとDDL文の実行とかの話が中心。
DDL文とは、データベースのデータ構造を定義する言語を用いたSQL文
- CREATE
- DROP
- ALTER
- COMMIT
- TRUNCATE
- PRUGE ...など
実行後に現在のトランザクションをコミットする文を指す。
また、スキーマ・オブジェクトとはデータベースに格納する表やビュー、
索引などの総称で特定のユーザーに所有されるもの。
記憶域やロール、ユーザー等システム全体で共有されるものは
スキーマ・オブジェクトではない。
ALTER TABLE文
既存の表の定義を変更するにはALTER TABLE文を使用します。
--▽新しい列の追加
ALTER TABLE '表名' ADD
( '列名' 'データ型' [,
'列名' 'データ型', ...]
);
--▽既存の列への制約の定義
ALTER TABLE '表名' ADD [CONSTRAINT '制約名'] '制約タイプ' ('列名');
--※但し、NOT NULL制約はALTER TABLE 文のADD CONSTRAINT句では追加できません。
--NOT NULL制約はMODIFY句で追加する必要があります。
ALTER TABLE '表名' MODIFY ('列名' [CONSTRAINT '制約名'] '制約タイプ');
--▽表のモードの変更
ALTER TABLE '表名' [READ WRITE | READ ONLY];
上記、記載の通り、
NOT NULL制約はALTER TABLE 文のADD CONSTRAINT句では追加できません。
ALTER TABLE文では、次の操作を行うことができます。
・新しい列の追加
・既存の列のデータ型の変更
・既存の列へのデフォルト値の設定
・既存の列への制約の定義
・既存の列の削除(データの削除は不可)
・既存の列名の変更
・表のモード変更(読み取り/書き込みモード、読み取り専用モード)
CREATE TABLE文
表の作成はCREATE ANY TABLE権限を持つユーザーによって行われます。
CREATE TABLE ['スキーマ名'.]'表名'
(
'列名' 'データ型'
[,'列名' 'データ型' ...]
);
表の作成時に、列にDEFAULTオプションを指定すると
その列のデフォルト値を設定できます。
DEFAULTオプションに他の列を参照する式は指定することはできませんが、
関数やDEFAULTオプションを指定した列を指定することはできます。
DROP TABLE文
DROP TABLE文は表をごみ箱に移動したり、
完全に削除したりすることができます。
ごみ箱に移動した表は復元することができます。
DROP TABLE [IF EXISTS] '表名1' [, '表名2'] ...
※表の削除はDROP ANY TABLE権限を持つ全てのユーザーによって行うことが可能です。
表を削除すると、表と表内のデータ、表に定義した制約、索引も同時に削除されます。
その表を参照しているビューやシノニムは削除されませんが、無効になります。
無効になったビューやシノニムにアクセスするとエラーになります。
表を完全に削除するには、DROP TABLE文にPURGEオプションを指定して実行します。
PURGEオプションを指定しないと、表はごみ箱に移動され、後で復元することができます。
□ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - □
作成された表に、親子関係があった場合
DROP TABLE文では、FOREIGN KEY制約(参照整合性制約とも呼ばれます)で
参照されている親表は子表よりも先に削除できない制限があります。
実行した場合は、参照されている行がない場合でも、
子表よりも先に親表を削除できないため、DROP TABLE文はエラーとなります。
しかしデータの削除に関しては、
FOREIGN KEY制約にON DELETEオプションを指定することによって、
親表の行を削除した場合に子表の行をどのようにするかを指定できます。
TRUNCATE TABLE文
表のデータだけを削除します。
TRUNCATE TABLE '表名';
DELETE文
DELETE文もまた、データを削除するDDL文です。
--テーブルのデータを削除する
DELETE FROM '表名1'
WHERE '条件';
--テーブルの値を削除する
DELETE FROM '表名'
WHERE '列名1' = '条件1';
--テーブルの値を削除する
DELETE FROM '表名1';
通常、親子関係のある表のデータ削除は、実行できません。
しかし、表には、下記のようなオプションを付与することができます。
| オプション | 説明 |
|---|---|
| 無し | 小兵が親表の値を参照している場合、親表の行の削除はできない |
| ON DELETE CASCADE | 小兵が親表の値を参照している場合、参照されている親表の行を削除すると、参照している小兵の行も削除される |
| ON DELETE SET NULL | 小兵が親表の値を参照している場合、参照されている親表の行を削除すると、参照している小兵の行にNULLが設定される |
表のFOREIGN KEY制約に「ON DELETE CASCADE」が指定されている場合、
DELETE文は正常に実行でき親表の行を削除すると子表の行も削除されます。
表のモード
表には読み取り/書き込みモードと読み取り専用モードの2つの状態があります。
| モード | 特徴 | 説明 |
|---|---|---|
| READ WRITE | 読取/書込みモード | 表のデータを変更することができる |
| READ ONLY | 読取専用モード | 表のデータを変更することができない。また、FOR UPDATEを指定したSELECT句も実行できない。表の削除は行うことができる |
副問合せによる表の作成
副問合せによる表の作成時、副問合せで取り出されたデータとデータ型は一緒に
NOT NULL制約は問合せた表から新たに作成する表へとコピーされますが、
NOT NULL制約以外の制約やデフォルト値はコピーされません。
※PRIARY KEY制約も破棄となります。
なお、副問合せの結果取り出されるデータが1件もなかった場合は
表の構造だけがコピーされます。
ただし、副問合せによる表の作成時にデフォルト値を設定したり、
制約を定義することはできます。
副問合せを使用した表の作成では、列のデータ型は指定できません。
副問合せを使用して新しい表を作成するには、次のように記述します。
CREATE TABLE '表名'[('列名' [, '列名' ...])]
AS
'副問合せ'
CREATE TABLE文に指定する列名の数と、
副問合せのSELECT句に指定する列名（列別名）の数は、同数にしなければなりません。
CREATE TABLE文の列名を省略すると、
副問合せのSELECT句に指定された列名または列別名と同名の列が定義されます。
なお、副問合せのSELECT句に計算式や関数を指定する場合は、計算式や関数に列別名を指定するか、CREATE TABLE文で列名を指定しなければなりません。
また、副問合せによる表の作成では、制約やデフォルト値に関する次の規則があります。
□ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - □
●データ型の指定はできない
データ型は副問合せのSELECT句に指定した列のデータ型、
または式の値のデータ型から自動的に定義されるため、
CREATE TABLE文にデータ型を指定するとエラーとなります。
●デフォルト値を設定できる
副問合せによる表の作成時、新たに作成する表にデフォルト値を設定できます。
●制約を定義できる
副問合せによる表の作成時、新たに作成する表に制約を定義することができます。
●NOT NULL制約のみ引継がれる
列に定義されているデフォルト値や制約のうち、
明示的に定義されたNOT NULL制約だけが、
副問合せによって新たに作成された表にコピーされます。
・PRIMARY KEY制約
・UNIQUE制約
・FOREGIN KEY制約
・CHECK制約
・デフォルト値
はコピーされません。
□ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - □
次回
DDL文がやっと終わって、一息つく木曜の午前です。
SQL基礎って、簡単なSQL文だけかと高を括っていた私を締めたい。。。
次は、現状引っかかっているところをまたぼちぼちまとめていきたいと思っております。
随時更新中＠あんず飴