[SQL]SQL入門 - テーブルの作成 -

はじめに

こちらはスッキリわかるSQL入門をまとめた記事となります。
今回は「テーブルの作成」についてです。
プログラミング初学者向けの内容となっていますので、お気づきの点があれば、コメントの方をよろしくお願いします。

SQL4種類の命令（DML,TCL,DDL,DCL）

データベースを利用する際にSQLでは様々な命令が出せます。

立場①　データベースにデータの出し入れを指示する立場
DML:Data Mnipulation Language
データ操作言語：データの格納や取り出し、更新、削除などの命令
SELECT,INSERT,UPDATE,DELETE
EXPLAN,LOCKTABLE

TCL:Transaction Control Language
トランザクション制御言語：トランザクションの開始や終了の命令
COMMIT,ROLLBACK,SET TRANSACTION,SAVEPOINT

立場②　立場①の人が、効率よく、安全にデータの出し入れができるよう必要なテーブル準備や各種設定を指示する立場
DDL:Data Definition Language
データ定義言語：テーブルなどの作成や削除、各種設定などの命令
CREATE,ALTER,DROP,TRUNCATE

DCL:Data Control Language
データ制御言語：DMLやDDLの利用に関する許可や禁止を設定する命令
GRANT,REVOKE

GRANT文とREVOKE文

GRANT 権限名 TO ユーザー名 -- 権限を付与する
REVOKE 権限名 FROM ユーザー名 -- 権限を剥奪する

これらは立場②の中でも特にデータベースの全権を管理する、データベース管理者（DBA:Database Administrator）の立場の人だけが使う命令。
DBMS製品によって構文や位置づけが大きく異なる。

テーブル作成（基本的な構文）

CREATE TABLE文

作成したいテーブルの名前、テーブルを構成する列と型の一覧を指定し、テーブルを定義します。

テーブルの作成

CREATE TABLE テーブル名(
列名1 列1の型名,
列名2 列2の型名,
:
列名X 列Xの型名
)

家計簿テーブルを作成

CREATE TABLE 家計簿 (日付 DATE, 費目ID INTEGER, メモ VARCHAR(100),入金額 INTEGER,出金額 INTEGER);

デフォルト値の設定

家計テーブルに対する行の追加

INSERT INTO 家計簿 (日付, メモ, 出金額) VALUES('2018-04-12', '詳細は後で', 60000);

結果: 「費目ID」と「入金額」の列の内容は、次の結果表にあるようにNULLとなります。

INSERT文で具体的な値を指定しなかった場合に、NULLが格納されてしまいますが、特定のデフォルト値（初期値）を格納することも可能です。
テーブルを作成する際に、デフォルト値を決めておくことで、「特に指定しなければ入金額には0が格納される」というような設定を行うことが可能です。
そのためにはDFAULTキーワードを指定します。

デフォルト値の指定を含むテーブルの作成

CREATE TABLE テーブル名(
列名 型名 DEFAULT デフォルト値,
:
)

家計簿テーブルを作成する（デフォルト値を活用）

CREATE TABLE 家計簿(
日付 DATE, 
費目ID INTEGER, 
メモ VARCHAR(100) DEFAULT '不明', 
入金額 INTEGER DEFAULT 0, 
出金額 INTEGER DEFAULT 0
)

テーブルの削除

DROP TABLE文

テーブル自体を削除する構文
DBMS製品によってはロールバックできず、一度実行してしまうと取り消しすることができなくなる場合があるので、バックアップしておくなど安全への配慮もしておく。

テーブルの削除（基本的な構文）

DROP TABLE テーブル名

家計簿テーブルを削除する場合

TRUNCATE TABLE文

テーブル全行を削除する場合に利用することがある
・ DELETEはWHERE句で指定した行だけ削除できるが、TRUNCATEは必ず全行を削除する。
・ DELETEはDMLだが、TRUNCATEはDDLに属する命令である。
・ DELETEはロールバックに備えて記録を残しながら仮削除していくが、TRUNCATEは記録を残さずに行を削除する（よってロールバックできない）。
・ DELETEは記録を残す為低速だが、TRUNCATEは高速。

テーブルの全行を削除する場合

TRUNCATE TABLE 家計簿     -- 家計簿テーブルの全行を削除 --

結果

テーブル定義の変更

ALTER TABLE文

テーブル定義の変更

-- 列の追加
ALTER TABLE テーブル名 ADD 列名 型 制約
-- 列の削除
ALTER TABLE テーブル名　DROP 列名 型 制約

列の追加と削除

-- 追加する時
ALTER TABLE 家計簿 ADD 関連日 DATE;
-- 削除する時
ALTER TABLE 家計簿 DROP 関連日;

制約

DBMSは**制約（constraint）**という仕組みを備えており、予期しない値を格納できないように制限をかけることで、人為的ミスによるデータ破壊の可能性を減らすことができる。

基本的な３つの制約

NOT NULL 制約

NULLの格納を防ぐことができる。
デフォルト値が設定されていればエラーにならない。

UNIQUE 制約

重複した値の格納を防ぐことができる。

CHECK 制約

格納しようとする値が妥当かどうかをチェックできる。

基本的な3つの制約を活用

CREATE TABLE 家計簿(
日付 DATE NOT NULL, -- NOT NULL制約
費目ID INTEGER,
メモ VARCHAR(100) DEFAULT '不明' NOT NULL,　-- NOT NULL制約
入金額 INTEGER DEFAULT 0 CHECK( 入金額 >= 0), -- CHECK制約
出金額 INTEGER DEFAULT 0 CHECK( 出金額 >= 0) -- CHECK制約
);
CREATE TABLE 費目(
ID INTEGER,
名前 VARCHAR(40) UNIQUE -- UNIQUE制約
);

主キー制約

主キーとして取り扱いたい列には、主キー制約を設定する。

主キー制約の指定(1)

CREATE TABLE 費目(
ID INTEGER PRIMARY KEY,
名前 VARCHAR(40) UNIQUE
)

また、以下のように記載して複合主キーの指定も可能。

主キー制約の指定(2)

CREATE TABLE 費目(
ID INTEGER PRIMARY KEY,
名前 VARCHAR(40) UNIQUE,
PRIMARY KEY(ID,名前)
)

外部キーと参照整合性

データの更新や削除によって外部キーによる参照整合性が崩れることがないように、外部キー制約を設定する。
参照整合性・・・外部キーが指し示す先にきちんと行が存在してリレーションシップが成立していること

その際は以下のようにREFERENCESを使うと、参照整合性が崩れるようなデータ操作をしようとした場合にエラーを発生させ強制的に処理を中断させます。

外部キー制約の指定１

CREATE TABLE テーブル名(
列名 型 REFERENCES 参照先テーブル名(参照先列名)
)

外部キー制約の指定

CREATE TABLES 家計簿(
日付 DATE NOT NULL,
費目ID INTEGER REFERENCES 費目(ID), -- 外部キー制約
メモ VARCHAR(100) DEFAULT '不明' NOT NULL,
入金額 INTEGER DEFAULT 0 CHECK(入金額>=0),
出金額 INTEGER DEFAULT 0 CHECK(出金額>=0)
)

以下の構文を用いる場合の家計簿テーブルの例では、費目IDの列定義には制約を記述しない代わりに、最後に「FOREIGN KEY(費目ID)REFERENCES費目(ID)」という記述を加えることになるでしょう。

外部キー制約の指定2

CREATE TABLE テーブル名(
FOREIGN KEY (参照元列名) REFERENCES 参照先テーブル名(参照先列名)
)

まとめ

・４種類のSQL命令（DML,TCL,DDL,DCL）
・テーブルの作成（CREATE TABLE文）と削除（DROP TABLE文）
・テーブル作成時に各列に制約を設定し、予期しない値が格納されないようにすることができる。また、参照整合性が崩れないように外部キーを設定する。

メンバー記事

【SQL】トランザクションをマスターしよう！
【SQL】SQL文を使って対象をロックする方法
SQLのまとめ③(インデックスについて)
【SQL書籍勉強会】テーブル設計