SQLでデータを挿入するにはINSERT文を使います。
今回もMySQLをもとにしていますが、ほとんど他DBでも流用できる内容です。
目次
INSERTの文法
テーブル名、カラムリスト、各カラムごとの値を設定します。
カラム名部分とVALUES部分は順序が対応している必要があります。
INSERT INTO <テーブル名> (カラム名1, カラム名2, ...) VALUES(カラム名1の値, カラム名2の値,...);複数データのINSERT
複数のデータを1度にINSERTすることもできます。
VALUES部分をカンマ区切りで複数指定することで1度に複数データをINSERTできます。
INSERT INTO <テーブル名> (カラム名1, ...) VALUES
(カラム名1の値, カラム名2の値,...),
(カラム名1の値, カラム名2の値,...),
...
;具体例
以下のようなユーザー情報と権限のテーブルがあったとします。
CREATE TABLE IF NOT EXISTS role (
-- 権限のID
id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
-- 権限の名前。adminやゲストなどの値が入る
role_name VARCHAR(20) NOT NULL
);
CREATE TABLE IF NOT EXISTS user (
-- ユーザーのID。自動で付与される
id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
-- ユーザー名
name VARCHAR(20) NOT NULL,
-- パスワード
password VARCHAR(256) NOT NULL,
-- データ作成日時
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
-- データ更新日時
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE current_timestamp,
-- ユーザーが持っている権限のID
role_id INT NOT NULL,
-- ユーザーが持っている権限IDを、権限テーブルに紐づける
FOREIGN KEY (role_id) REFERENCES role(id) ON DELETE RESTRICT ON UPDATE RESTRICT
);権限テーブルにデータを追加するのは以下のようにできます。
-- 1つのデータをINSERT
INSERT INTO role (role_name) VALUES('admin');
-- 複数データをINSERT
INSERT INTO role (role_name) VALUES
('member'),
('guest')
;ユーザー情報テーブルは以下のようにできます。パスワードはそのままのデータをDBに保存するのはセキュリティ的にダメですが今回は例なので無視してもらえればと思います。
AUTO_INCREMENTになっている部分は自動で割り振ってくれます。
外部キーは必ず入れる必要があります。
サブクエリは後で説明します。
-- 1つのデータINSERT
INSERT INTO user(name, password, role_id) VALUES('テスト太郎', 'test_password', 2);
-- 複数データINSERT、サブクエリ使用
INSERT INTO user(name, password, role_id) VALUES
('テスト次郎', 'test_password', 2),
('テストユーザー', 'test_password', (SELECT id FROM role WHERE role_name='member'))
;AUTO_INCREMENTや外部キーのカラムは？
AUTO_INCREMENTのカラムは自動で値が割り振られるので指定は不要です。
また、外部キーは参照先に存在しているデータを設定する必要があります。
INSERT文内でのSELECT文サブクエリ
先ほどの例で以下のようなINSERT文がありました。INSERTの中にSELECT文があります。
この例はroleテーブルの権限名がmemberのデータのidを取得するようになっています。
このようにSELECTした内容をそのままINSERTすることができます。このSELECT文をサブクエリと言います。
INSERT INTO user(name, password, role_id) VALUES
('テスト次郎', 'test_password', 2),
('テストユーザー', 'test_password', (SELECT id FROM role WHERE role_name='member'))
;サブクエリを使わない場合は1つめのデータのように直接idの値「2」などを指定する必要があります。この「2」はadmin, member, guestの順で登録されているので2番目のmemberを表します。
しかし、直接id指定するにはデータの順序を気にしたり、実際にroleテーブルの中身を見てidを指定しなければいけません。
サブクエリを実行することでmemberという権限名の権限idを取得してくれるため、順序を気にしなくて済みます。
文法は以下の通りです。
特定のカラムをサブクエリの結果の値にする場合は(SELECT文)にします。
全てのデータをSELECT文にする場合はVALUESを省略します。
-- 特定のカラムをサブクエリの結果の値にする
INSERT INTO <テーブル名>(カラムリスト) VALUES
(データ1, (SELECT文), データ3, ...)
;
-- 挿入するカラムの全データをサブクエリの結果の値にする
INSERT INTO <テーブル名>(カラムリスト) SELECT文;他の記事でテーブル作成や外部キーについてもまとめているのでそちらもよかったら見てみてください！
参考サイト
- MySQL公式