はじめに

以下はスッキリわかるSQL入門第10章(テーブル作成)の学習備忘録です。

引用されている個所は書籍の内容をそのまま書き、引用されていない箇所は私が調べて補足した内容を書いてあります。
SQLのタグが付いているコードブロックに関しては、書籍の引用と私が調べて補足したものを織り交ぜた内容になっています。

RDMSについてはMySQLを想定しています。

第10章テーブル作成

10.1 SQLの命令

データベースを利用する2つの立場

立場1. データベースにデータの出し入れを指示する立場
立場2. 立場1の人が、効率よく、安全にデータの出し入れができるよう必要なテーブルの準備や各種設定を支持する立場

立場1の開発者が扱うSQLの命令：DML、TCL

DML: Data Manipulation 

* SELECT
* INSERT
* UPDATE
* DELETE
* EXPLAIN
* LOCK TABLE
※ SELECT、INSERT、UPDATE、DELETEは4大命令と言われる。

データ操作言語: データの格納や取り出し、更新、削除などの命令

TCL: Transaction Control Language

* COMMIT
* ROLLBACK
* SET TRANSACTION
* SAVEPOINT

トランザクション制御言語: トランザクションの開始や終了の命令

立場2の開発者が扱うSQLの命令：DDL、DCL

DDL: Data Difinition Lauguage

* CREATE
* ALTER
* DROP
* TRUNCATE

データ定義言語: テーブルなどの作成や削除、各種設定などの命令

DCL: Data Control Language

* GRANT
* REVOKE

データ制御言語: DMLやDDLの利用に関する許可や禁止を設定する命令

DCLとは

DCLとは、誰に、どのようなデータ操作やテーブル操作を許すかといった権限を設定するためのSQLの命令の総称のこと。権限を付与するGRANT文と剥奪するREVOKE文がある。
また、DCLは立場2の中でも特にデータベースの全権を管理する、データベース管理者(DBA: Database Administer)の立場の人だけが使う命令。

sql

-- 現在ユーザーの確認
select user();

-- ユーザーの作成
create user {ユーザー名}@{接続元のホスト名} identified by 'password';
create user 'test_user'@'localhost' identified by 'pwd';

-- ユーザー一覧
select * from mysql.user;

-- 権限の確認
show grants for test_user@localhost;
 
-- 権限の付与
grant {権限名} on {対象のDBオブジェクト} to {ユーザー};
grant all on test_db.* to 'test_user'@'localhost';
grant update, insert on test_db.* to 'test_user'@'localhost';
grant create, alter on test_db.* to 'test_user'@'localhost';

-- 権限の削除
revoke all on test_db.* from 'test_user'@'localhost';

-- ユーザーの削除
drop user 'test'@'%'

以降はテーブル作成、削除、更新、テーブル定義確認のSQL文。

テーブル作成(CREATE TABLE)

sql

/*
データ型
INT: 整数値
FLOAT: 浮動小数点
※正の値に限定する場合は unsigned を使用。
DATETIME: 日時
TIMESTAMP: 日時
CHAR: 固定長文字列
VARCHAR: 可変長文字列
BLOB: バイナリデータ（画像や音声、動画など）

制約
UNIQUE: 一意制約
NOT NULL: NOT NULL制約
CHECK: チェック制約
PRIMARY KEY: 主キー制約
FOREIGN KEY: 外部キー制約
*/

create table テーブル名 (
    カラム名 データ型 default デフォルト値 制約 comment 'コメント',
    ...,
    表制約
) ENGINE = [INNODB | MyISAM];

create table test_db.test_table (
	id int(6) unsigned default 0 comment 'ID',
    val varchar(20) default 'hello' comment '値' -- 最後は,で終わらないように
);

テーブルの削除(DROP TABLE)

sql

DROP TABLE テーブル名

全権のデータを高速に削除(TRANCATE TABLE)

sql

TRANCATE TABLE テーブル名

TRANCATE TABLEは、厳密にはデータ削除ではなく、テーブル初期化(TABLE DROP -> TABLE CREATE)の命令。
また、『DELETE FROM テーブル名』のテーブル削除との違いは以下。

DELETEはWHERE句で指定した行だけ削除。TRANCATEは全行を削除。

DELETEはDML、TRANCATEはDDLに属する命令

DELETEはロールバックに備えて記録を残しながら仮削除するが、TRANCATEは記録を残さずに削除(ロールバックできない)

DELETEは記録を残すため低速だが、TRANCATEは高速。

テーブル定義確認

sql

desc test_table;
 
show full columns from test_table;
 
show create table test_table;

DESCコマンドは、指定したテーブルのカラム（列）情報を表示するコマンドで、以下の情報を表示。

カラム名
データ型
NULL許容（YES/NO）
キー（PRI/UNI/MUL）
デフォルト値
追加情報

SHOW FULL COLUMNSコマンドは、指定したテーブルのカラム情報を詳細に表示するコマンドで、以下の情報を表示。

カラム名
データ型
NULL許容（YES/NO）
キー（PRI/UNI/MUL）
デフォルト値
追加情報
カラムコメント

SHOW CREATE TABLEコマンドは、指定したテーブルのCREATE文を表示するコマンドで、以下の情報を表示。

テーブル名
CREATE文

10.3 制約

制約は、CREATE TABLE文でテーブルを定義する際に、列定義の末尾（commentよりも前）で使用することが可能。

sql

制約
UNIQUE: 一意制約
NOT NULL: NOT NULL制約
CHECK: チェック制約
PRIMARY KEY: 主キー制約
FOREIGN KEY: 外部キー制約
*/

create table テーブル名 (
    カラム名 データ型 default デフォルト値 制約 comment 'コメント',
    ...,
    表制約
) ENGINE = [INNODB | MyISAM];

基本的な3つの制約

1. NOT NULL制約
　NULLの格納が許可されない。DEFAULT指定と組み合わせて利用されることが大半。
　デフォルトで値が設定されていればINSERT文で値を入力していなくてもエラーにならない。
2. UNIQUE制約
　ある列の内容が決して重複してはならない場合につける。
　なおUNIQUE制約がかけられていても、NULLが格納された行が複数存在する事は許される。
3. CHECK制約
　ある列に格納される値が妥当であるかを細かく判定したい場合は、CHECK制約を用いる。
　CHECKの後ろのカッコ内に記述した条件式が真となるような値だけが格納を許される。

上記以外の2つの制約(PRIMARY KEY、FOREIGN KEY)

主キー制約(PRIMARY KEY)

主キーの列とは、『その列の値を指定すれば、どの1行のことか完全に特定できる』の列のこと。
単なる『NULLも重複も許さない列』ではなく、主キーとしての役割が期待されているという意味(セマンティクス)を持つ。主キーの制約の指定方法には2つある。

sql

/* 主キー制約の指定1 */
CREATE TABLE テーブル名 (
    ID INTEGER PRIMARY KEY
    name VARCHER UNIQUE
)

/* 
主キー制約の指定2 
下記はprimary keyに2つのカラムが指定されているので複合主キー扱い
*/
CREATE TABLE テーブル名 (
    ID INTEGER,
    name VARCHER UNIQUE
    PRIMARY KEY(ID, name)
)

外部キー制約

参照整合性

外部キーが指し示す先にきちんと行が存在してリレーションシップが整理していることを参照整合性と言う。外部キーが参照しているメインテーブルの主キーのレコードが存在しない状態のことを参照整合性の崩壊と呼ぶ。

参照整合性の崩壊を引き起こすデータ操作

1. 『他の行から参照されている行』を削除してしまう
2. 『他の行から参照されている』行の主キーを変更してしまう
3. 『存在しない行を参照』する行を追加してしまう
4. 『存在しない行を参照』する行に変更してしまう
※ 上記のデータ操作をしようとした際にエラーを発生させ、強制的に処理を中断させる制約が外部キー制約(FOREIGN KEY 制約)。

sql

/* 
外部キー制約の指定1
*/
CREATE TABLE テーブル名 (
    ID INTEGER,
    name VARCHER UNIQUE
    category_id INTEGER REFERENCES 参照先テーブル名(参照先列名)
)

/* 
外部キー制約の指定2
主キーの場合と同様に、CREATE TABLE文の最後にまとめて定義
*/
CREATE TABLE テーブル名 (
    :
    FOREIGN KEY (参照先テーブル名) REFERENCES 参照先テーブル名(参照先列名)
)

制約がついてなくても主キー
主キー制約は、あくまでもその列に『主キーであれば果たすべき2つの責任(NOT NULL制約とUNIQUE制約)を確実に果たせるための安全装置』に過ぎず、主キー制約が設定されていなくても、『行を識別するための列』として利用する列があれば、それは主キー列となりうる。外部も主キーになりうる。

外部キーにはON DELETEとON UPDATEという親テーブルが更新、削除された際の子テーブルの動作(アクション)を設定することが出来る。デフォルトのアクションはRESTRICT。

sql

/*
ON DELETE: レコードが削除された際のアクション
ON UPDATE: レコードが更新された際のアクション
 
CASCADE:
親テーブルの行を削除または更新し、子テーブル内の一致する行を自動的に削除または更新する。
 
RESTRICT:
親テーブルに対する削除または更新操作を拒否します。デフォルト値。
ON DELETE または ON UPDATE 句を省略することと同義。
*/
 
-- 外部キーの作成
alter table テーブル名
add constraint 制約名（※削除する際に使用）
foreign key (対象のキー名)
      references 親テーブル名(テーブルキー名)
      on update cascade 
      on delete restrict; -- 省略可