テーブル作成の方法
SQLでテーブルを作成したい場合は、「CREATE」文を使います。
CREATEはテーブル作成だけではなく、データベース上のさまざまなオブジェクトを作成する際に使われるSQLクエリです。
テーブルを作成する際には、基本的に次のように記述します。
CREATE TABLE テーブル名 ( カラムの名前1 データ型 オプション, カラムの名前2 データ型 オプション, …… 全体向けのオプション );
データ型やオプションについては後ほど詳しく解説します。
テーブルをデータベース外部から作成する場合は、テーブル名の箇所を「データベース名.テーブル名」とすれば作成可能です。
では実際にテーブルを作成してみましょう。
CREATE TABLE user_data_list(
user_id INT(6) AUTO_INCREMENT NOT NULL,
first_name VARCHAR(15) NOT NULL ,
last_name VARCHAR(15) NOT NULL ,
age INT(3) NOT NULL,
address VARCHAR(30) NOT NULL ,
PRIMARY KEY (user_id)
);
上記のクエリを実行すると、テーブルの作成が完了です。
テーブルの情報を確認してみると、次のようになっています。
mysql> SHOW COLUMNS FROM user_data_list; +------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+----------------+ | user_id | int | NO | PRI | NULL | auto_increment | | first_name | varchar(15) | NO | | NULL | | | last_name | varchar(15) | NO | | NULL | | | age | int | NO | | NULL | | | address | varchar(30) | NO | | NULL | | +------------+-------------+------+-----+---------+----------------+ 5 rows in set (0.01 sec)
作成時に指定したカラムとそのオプションが付与されているのがわかりますね。
テーブル作成で指定できるデータ型
テーブルを作成する際には、カラムにデータ型を指定します。
データ型とは、カラムに格納できるデータの種類を制限する機能です。
先ほどのテーブル情報にある「Type」の列がデータ型に対応しています。
データ型を設定するのには、次のような理由があります。
- データ入力の誤りを防ぐ
- メモリ上の領域が確保しやすくやる
決まったルールに従うことで、データベースを問題なく使用することが可能です。
代表的なデータ型には、次のものが挙げられます。
| データ種別 | 区分 | 代表的なデータ型名 |
|---|---|---|
| 数値 | 整数値 | INTEGER型 |
| 小数 | DECIMAL型、REAL型 | |
| 文字列 | 固定長 | CHAR型 |
| 可変長 | VARCHAR型 | |
| 日付と時刻 | – | DATETIME型、DATE型、TIME型 |
「数値型」「文字列型」「日付型」は基本的に用意されていますが、利用可能なデータ型の種類はDBMS製品によって異なります。
特に、数値型の「INTEGER型」と文字列型の「CHAR型」「VARCHAR型」は、多くのDBMS製品共通で利用可能です。
使えるデータ型の詳細は、各製品のリファレンスを確認しましょう。
テーブル作成で指定できるオプション
テーブル作成時に、カラムにはオプションを指定することも可能です。
ここでは、CREATE TABLE文で使用できる次の6つのオプションを紹介します。
- NOT NULL
- AUTO_INCREMENT
- DEFAULT
- PRIMARY KEY
- UNIQUE
- CHECK
1つずつ見ていきましょう。
NOT NULL
NOT NULL制約を設定すると、指定した列にはNULLを格納できなくなります。
列の値にNULLが入ることを防ぎたい場合に有効な設定です。
また、NOT NULL制約は、後ほど紹介するDEFAULT制約と組み合わせて利用することが多いです。
デフォルト値を設定すれば、INSERT文で値を指定しなくても自動的に値が設定されるのでエラーを防げます。
AUTO_INCREMENT
AUTO_INCREMENT制約は、設定したカラムに一意の識別子を連番で付与する機能です。
会員番号やユーザーIDなど、連続した値を一意に設定したい場合によく使います。
DEFAULT
DEFAULT制約は、INSERT文で具体的な値を指定しなかった場合に、NULLではなく特定のデフォルト値（初期値）を格納する機能です。
テーブル作成の際に、デフォルト値を決めておくと「特に指定がなければageには0が格納される」といった設定ができます。
CREATE TABLE user_data_list(
user_id INT(6) AUTO_INCREMENT NOT NULL,
first_name VARCHAR(15) NOT NULL ,
last_name VARCHAR(15) NOT NULL ,
age INT(3) NOT NULL DEFAULT 0,
address VARCHAR(30) NOT NULL ,
PRIMARY KEY (user_id)
);
PRIMARY KEY（主キー）
PRIMARY KEYは、値を指定することである1行を完全に特定できる役割を担った列を指します。
PRIMARY KEYには、何かしらのデータが格納され、他の行と値が重複しないことが特徴です。
データベース管理において、特定行の削除や更新は頻繁に起こります。
そのため、あらゆるデータをいつでも自由に特定するためにも、すべてのテーブルには主キーとなるような設定が必要です。
UNIQUE
UNIQUE制約は、ある列の内容が他とデータと重複させたくない場合に設定する制約です。
例えば、ユーザー情報を格納したテーブルで同じ名前が登録されないようにしたい場合などに、UNIQUE制約を使います。
PRIMARY KEYとの違いは、NULLの設定可否です。
PRIMARY KEYがNULLを格納できないのに対し、UNIQUE制約はNULLを許容します。
CHECK
CHECK制約は、ある列に格納される値が妥当であるか、細かく判定したい場合に設定する制約です。
CHECKの後ろに記述した条件式が真である場合のみ値の格納が許されます。
例えば、次テーブルはage列の値が0より大きい値（正の値）だけデータの格納が可能です。
CREATE TABLE user_data_list(
user_id INT(6) AUTO_INCREMENT NOT NULL,
first_name VARCHAR(15) NOT NULL ,
last_name VARCHAR(15) NOT NULL ,
age INT(3) NOT NULL CHECK (age > 0),
address VARCHAR(30) NOT NULL ,
PRIMARY KEY (user_id)
);
【補足】FOREIGN KEY（外部キー）
FOREIGN KEYは、他テーブルのある列（主キー列など）の値を格納し、その行が他テーブルのどの行と関連しているのかを明確にする制約のことです。
FOREIGN KEYを使う場合は、次のように使いましょう。
親テーブル
CREATE TABLE 親テーブル名(
親カラム名 データ型 PRIMARY KEY
);
子テーブル
CREATE TABLE 子テーブル名(
子カラム名 データ型,
INDEX インデックス名(子カラム名),
FOREIGN KEY 外部キー名(子カラム名) REFERENCES 親テーブル名(親カラム名)
);
テーブル作成後に削除する方法
作成したテーブルを削除するには、「DROP TABLE」文を使います。
使い方は次の通りです。
DROP TABLE テーブル名;
DROP TABLEの後ろに、削除したいテーブル名を入力すればOKです。
DROPTABLE文を実行し、テーブルが削除されると次のように「Query OK」と表示されます。
mysql> DROP TABLE user_data_list; Query OK, 0 rows affected (0.13 sec)
テーブル作成後に更新する方法
テーブルを作成後に「新しくカラムを追加したい」など、更新したい場合は「ALTER TABLE」文を使いましょう。
カラムを追加する場合は、次のように記述します。
ALTER TABLE テーブル名 ADD カラム名 データ型;
実際にカラムをテーブルに追加してみましょう。
以下のSQLクエリを実行します。
mysql> ALTER TABLE user_data_list ADD tel varchar(15); Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0
テーブル情報を確認してみると、ちゃんと追加したカラムも表示されていますね。
mysql> SHOW COLUMNS FROM user_data_list; +------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+----------------+ | user_id | int | NO | PRI | NULL | auto_increment | | first_name | varchar(15) | NO | | NULL | | | last_name | varchar(15) | NO | | NULL | | | age | int | NO | | NULL | | | address | varchar(30) | NO | | NULL | | | tel | varchar(15) | YES | | NULL | | +------------+-------------+------+-----+---------+----------------+ 6 rows in set (0.01 sec)
一方、カラムを削除する場合は、次のように記述します。
ALTER TABLE テーブル名 DROP カラム名;
こちらも実行してみると、次のようにカラムが削除できているのが確認できるはずです。
mysql> ALTER TABLE user_data_list DROP tel; Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0
テーブルを作成後にコピーする方法
すでに作成してあるテーブル情報をコピーして、新しくテーブルを作成したい場合は、次のようにCREATE TABLE文を記述します。
CREATE TABLE 新テーブル (SELECT * FROM 既存テーブル);
記述方法は使用するDBMS製品によって若干異なるので、確認しておきましょう。
では、先ほど作成したuser_data_listテーブルのコピーを作成してみます。
mysql> CREATE TABLE copy_table (SELECT * FROM user_data_list); Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0
コピーができました。テーブル情報を確認してみると、次のようになっています。
mysql> SHOW COLUMNS FROM copy_table; +------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+-------+ | user_id | int | NO | | 0 | | | first_name | varchar(15) | NO | | NULL | | | last_name | varchar(15) | NO | | NULL | | | age | int | NO | | NULL | | | address | varchar(30) | NO | | NULL | | +------------+-------------+------+-----+---------+-------+ 5 rows in set (0.00 sec) mysql> SHOW COLUMNS FROM user_data_list; +------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+----------------+ | user_id | int | NO | PRI | NULL | auto_increment | | first_name | varchar(15) | NO | | NULL | | | last_name | varchar(15) | NO | | NULL | | | age | int | NO | | NULL | | | address | varchar(30) | NO | | NULL | | +------------+-------------+------+-----+---------+----------------+ 5 rows in set (0.00 sec)
コピー元のテーブルと比較すると、PRIMARY KEYやAUTO_INCREMENTの制限は反映されていません。
このようにテーブルの基本情報はコピーできますが、制限まではコピーできない（製品による）ので注意しましょう。
テーブル作成後に結合する方法
テーブル作成後に、テーブル同士を結合するには「JOIN句」を使います。
JOIN句を使ったテーブル結合の方法は、以下の記事で解説していますので参考にしてみてください。
【関連記事】
▶︎SQLのテーブル結合を理解しよう！JOIN句の種類と使い方とは？
まとめ
SQLでテーブルを作成する方法について解説しました。
テーブル作成時にはさまざまなデータ型やオプションが使用可能です。
目的に合わせたテーブルの作成に、ぜひこの記事を参考にしてみてください！
