◇SQL;INSERT/DELETE with SELECT
INSERTの一部カラム値を別テーブルのSELECTで設定する
次のテーブルがあるとします。
CREATE TABLE 人物 ( id integer NOT NULL PRIMARY KEY AUTOINCREMENT, 姓 varchar(256) NOT NULL, 名 varchar(256) NOT NULL, 補助情報 varchar(256) NULL ); CREATE TABLE 持ち物( id integer NOT NULL PRIMARY KEY AUTOINCREMENT, 人物_id integer NOT NULL, 分類 varchar(256) NOT NULL, 詳細 varchar(256) NULL, 個数 integer NOT NULL );
"持ち物"テーブルは"人物_id"で"人物"テーブルを指しています。
人物テーブルに次の情報が入っているとします。
id 姓 名 補助情報 ----- ---------- ------------ ------------ 1 山田 一郎 2 鈴木 次郎 3 山田 三郎
ここで鈴木次郎の持ち物として、「お菓子,柿の種,1個」をINSERTするには人物のSELECTと組み合わせ次のINSERTを実行します
INSERT INTO 持ち物 ( 人物_id,分類,詳細,個数 ) SELECT id,'お菓子','柿の種',1 FROM 人物 WHERE 姓='鈴木' AND 名='次郎';
これはSELECTの「カラム名以外を指定すると、その表記がそのまま返る」という性質を利用したものです。
SELECT id,'お菓子','柿の種',1 FROM 人物 WHERE 姓='鈴木' AND 名='次郎'; -- 2 お菓子 柿の種 1
これがそのままINSERTに使われるので
INSERT INTO 持ち物 ( 人物_id,分類,詳細,個数 ) (2 お菓子 柿の種 1);
という文を実行することに等しくなります。
DELETEの条件を別テーブルのSELECTで設定する
持ち物テーブルに次の情報が入っているとします。(姓名はJOINで得ています)
人物_id 姓 名 分類 詳細 個数 ------- ----- ---- --------- ---------- ----- 1 山田 一郎 お菓子 かっぱえび 2 2 鈴木 次郎 お菓子 柿の種 1 2 鈴木 次郎 お菓子 メルティー 1 3 山田 三郎 お菓子 柿の種 1 3 山田 三郎 撮影器具 ドローン 1
この内鈴木次郎分を削除するには次のようにDELETE条件に人物対するSELECTを用います。
DELETE FROM 持ち物 WHERE 人物_id= (SELECT id FROM 人物 WHERE 姓='鈴木' AND 名='次郎');
結果は次のようになります。
人物_id 姓 名 分類 詳細 個数 ------- ----- ---- --------- ---------- ----- 1 山田 一郎 お菓子 かっぱえび 2 3 山田 三郎 お菓子 柿の種 1 3 山田 三郎 撮影器具 ドローン 1
なおここでの持ち物テーブルは次のSELECTで得ています。
SELECT 人物_id,人物.姓,人物.名,分類,詳細,個数 FROM 持ち物 JOIN 人物 ON 持ち物.人物_id=人物.id;
SELECTの条件を別テーブルのSELECTで設定する
SELECTの条件に別テーブルのSELECTを用いることができます。副問い合わせと呼ぶことがあります。
例えば鈴木次郎のデータを得るには次のようなSELECTを発行します
SELECT 分類,詳細,個数 FROM 持ち物 WHERE 人物_id= (SELECT id FROM 人物 WHERE 姓='鈴木' AND 名='次郎');
次の結果が得られます。
分類 詳細 個数 ----- ---------- ------------ お菓子 柿の種 1 お菓子 メルティーキッス 1
通常はJOINを使います。ONでテーブルの関連を指定します。
SELECT 分類,詳細,個数
FROM 持ち物
JOIN 人物 ON 持ち物.人物_id=人物.id
WHERE 人物.姓='鈴木' AND 人物.名='次郎';
次の結果が得られます。
分類 詳細 個数 ----- ---------- ------------ お菓子 柿の種 1 お菓子 メルティーキッス 1
SQL文
-- テーブル生成
DROP TABLE 人物;
CREATE TABLE 人物 (
id integer NOT NULL PRIMARY KEY AUTOINCREMENT,
姓 varchar(256) NOT NULL,
名 varchar(256) NOT NULL,
補助情報 varchar(256) NULL
);
DROP TABLE 持ち物;
CREATE TABLE 持ち物(
id integer NOT NULL PRIMARY KEY AUTOINCREMENT,
人物_id integer NOT NULL,
分類 varchar(256) NOT NULL,
詳細 varchar(256) NULL,
個数 integer NOT NULL
);
-- 人物データINSERT
INSERT INTO 人物 ( 姓,名 ) VALUES ( '山田','一郎' );
INSERT INTO 人物 ( 姓,名 ) VALUES ( '鈴木','次郎' );
INSERT INTO 人物 ( 姓,名 ) VALUES ( '山田','三郎' );
SELECT * FROM 人物;
-- 持ち物データINSERT
DELETE FROM 持ち物;
-- INSERT SELECT
INSERT INTO 持ち物 ( 人物_id,分類,詳細,個数 )
SELECT id,'お菓子','かっぱえびせん',2
FROM 人物 WHERE 姓='山田' AND 名='一郎';
INSERT INTO 持ち物 ( 人物_id,分類,詳細,個数 )
SELECT id,'お菓子','柿の種',1
FROM 人物 WHERE 姓='鈴木' AND 名='次郎';
INSERT INTO 持ち物 ( 人物_id,分類,詳細,個数 )
SELECT id,'お菓子','メルティーキッス',1
FROM 人物 WHERE 姓='鈴木' AND 名='次郎';
INSERT INTO 持ち物 ( 人物_id,分類,詳細,個数 )
SELECT id,'お菓子','柿の種',1
FROM 人物 WHERE 姓='山田' AND 名='三郎';
INSERT INTO 持ち物 ( 人物_id,分類,詳細,個数 )
SELECT id,'撮影器具','ドローン',1
FROM 人物 WHERE 姓='山田' AND 名='三郎';
SELECT 人物_id,人物.姓,人物.名,分類,詳細,個数
FROM 持ち物
JOIN 人物 ON 持ち物.人物_id=人物.id;
-- 副問い合わせ
SELECT 分類,詳細,個数
FROM 持ち物
WHERE 人物_id=
(SELECT id
FROM 人物
WHERE 姓='鈴木' AND 名='次郎');
-- JOIN
SELECT 分類,詳細,個数
FROM 持ち物
JOIN 人物 ON 持ち物.人物_id=人物.id
WHERE 人物.姓='鈴木' AND 人物.名='次郎';
-- DELETE SELECT
DELETE FROM 持ち物
WHERE 人物_id=
(SELECT id
FROM 人物
WHERE 姓='鈴木' AND 名='次郎');
-- テーブル削除 (後始末)
DROP TABLE 人物;
DROP TABLE 持ち物;
sqliteでの特別指定
sqliteでは次の指定でSELECTの表示にカラム名を出し、カラム間に空間を置いています。
.headers on .mode column
| 固定リンク