SQLについての基本事項のメモ
LastUpdate : 08/09/13
やり始めると思い出すけど、しばらく触らないと、まったく忘れ去っているSQL（私だけ？ｗ）についてのメモです。
理屈は知っていても、いざ自分がDBにアクセスし、実作業をやろうとしたら、「あれ、どーゆーふーに書くんだっけ？ｗ」みたいなこと、きっとあるはずです（私だけ？ｗww
というわけで、忘れたときに困るのでメモしておきます。
もくじ
CREATE TABLE Kind
(
KindId INTEGER NOT NULL PRIMARY KEY,
KindTitle VARCHAR(128)
);
CREATE TABLE Item
(
ItemId INTEGER NOT NULL PRIMARY KEY,
KindId INTEGER REFERENCES Kind(Kindid),
Title VARCHAR(128)
);
|
そして上記のテーブルには以下のデータがはいっています。
db2 => SELECT * FROM KIND;
KINDID KINDTITLE
----------- ------------------
1 惣菜
2 精肉
3 水産
4 日配
4 レコードが選択されました。
db2 => SELECT * FROM ITEM;
ITEMID KINDID TITLE
----------- ----------- ----------------------------------------
1 1 おいしい煮物
2 1 さらにおいしい煮物
3 2 黒毛和牛ランプステーキ
4 2 黒毛和牛肩ロース薄切り（しゃぶしゃぶ用）
5 2 黒毛和牛テール（シチュー用）
6 3 刺身盛り合わせ
7 3 網にはいってきた魚 各種
8 3 海で取れたもの盛り合わせ
9 3 海底から拾ってきた貝
10 4 誰でも飲める牛乳
10 レコードが選択されました。
db2 => SELECT * from PRICE;
ITEMID PRICE
----------- -----------
1 850
2 1200
3 1980
4 2180
5 680
6 1280
7 1480
8 100
9 98
10 28000
10 レコードが選択されました。
|
ちなみに、このSQLを使うと、このメモと同じテーブルの状態を作れます(db2inst1でログインし、問答無用でテーブルをつくり、データを挿入します)。
insert、select、update、deleteについてです。それぞれの使用例を書きます。さすがに、この辺はいくら時間がたっても、忘れませんが＾＾；
INSERT INTO Kind(KindId, KindTitle) VALUES(1,'惣菜'); SELECT * FROM Kind WHERE KindId=1; UPDATE Kind SET KindTitle='日配' WHERE KindId=4 DELETE FROM Kind WHERE KindId=10
この辺は（個人的に）良く使う気がするので、忘れにくいかもしれません。
が・・・ｗ
私はSOMEとＡＮＹの違いを私は知りません。PostgreSQLなんかだと同じ意味だったと思います。
が、他のＤＢだとどーなのか知りませんｗｗｗｗｗｗｗｗ。その辺は、お隣のＤＢマニアに問い合わせてくださいｗ
※EXISTSを説明する場合、今回準備したデータだけでは、説明できないため、以下のテーブルを新たに作成しました。
CREATE TABLE SearchId( id Integer );
INSERT INTO SearchId VALUES(1);
INSERT INTO SearchId VALUES(2);
以下の例は「相関サブクエリー」とかいうやつです（たぶんｗ
db2 => SELECT * FROM Item WHERE EXISTS(select * FROM Kind WHERE Kind.KindId=Item.KindId);
ITEMID KINDID TITLE
----------- ----------- ------------------------------------------------------------------
1 1 おいしい煮物
2 1 さらにおいしい煮物
3 2 黒毛和牛ランプステーキ
4 2 黒毛和牛肩ロース薄切り（しゃぶしゃぶ用）
5 2 黒毛和牛テール（シチュー用）
6 3 刺身盛り合わせ
7 3 網にはいってきた魚 各種
8 3 海で取れたもの盛り合わせ
9 3 海底から拾ってきた貝
10 4 誰でも飲める牛乳
10 レコードが選択されました。
db2 => SELECT * FROM Item WHERE EXISTS(SELECT * FROM SearchId WHERE id=ItemId);
ITEMID KINDID TITLE
----------- ----------- ------------------------------------------------------------
1 1 おいしい煮物
2 1 さらにおいしい煮物
2 レコードが選択されました。
db2 => SELECT * FROM Item WHERE NOT EXISTS(SELECT * FROM SearchId WHERE id=ItemId);
ITEMID KINDID TITLE
----------- ----------- ------------------------------------------------------------
3 2 黒毛和牛ランプステーキ
4 2 黒毛和牛肩ロース薄切り（しゃぶしゃぶ用）
5 2 黒毛和牛テール（シチュー用）
6 3 刺身盛り合わせ
7 3 網にはいってきた魚 各種
8 3 海で取れたもの盛り合わせ
9 3 海底から拾ってきた貝
10 4 誰でも飲める牛乳
8 レコードが選択されました。
db2 => SELECT * FROM Item WHERE ItemId IN (1,2);
ITEMID KINDID TITLE
----------- ----------- --------------------------------------------
1 1 おいしい煮物
2 1 さらにおいしい煮物
2 レコードが選択されました。
db2 => SELECT * FROM Item WHERE ItemId NOT IN (1,2);
ITEMID KINDID TITLE
----------- ----------- --------------------------------------------
3 2 黒毛和牛ランプステーキ
4 2 黒毛和牛肩ロース薄切り（しゃぶしゃぶ用）
5 2 黒毛和牛テール（シチュー用）
6 3 刺身盛り合わせ
7 3 網にはいってきた魚 各種
8 3 海で取れたもの盛り合わせ
9 3 海底から拾ってきた貝
10 4 誰でも飲める牛乳
8 レコードが選択されました。
db2 => SELECT * FROM Item WHERE KindId IN (SELECT KindId FROM Kind WHERE KindTitle LIKE '%肉%');
ITEMID KINDID TITLE
----------- ----------- -------------------------------------------------------------------------------
3 2 黒毛和牛ランプステーキ
4 2 黒毛和牛肩ロース薄切り（しゃぶしゃぶ用）
5 2 黒毛和牛テール（シチュー用）
3 レコードが選択されました。
db2 => SELECT * FROM Item WHERE KindId = ANY(select KindId FROM Kind WHERE KindTitle='水産');
ITEMID KINDID TITLE
----------- ----------- -----------------------------------------------------------------------
6 3 刺身盛り合わせ
7 3 網にはいってきた魚 各種
8 3 海で取れたもの盛り合わせ
9 3 海底から拾ってきた貝
4 レコードが選択されました。
db2 => SELECT * FROM Item WHERE KindId <> ANY(select KindId FROM Kind WHERE KindTitle='水産');
ITEMID KINDID TITLE
----------- ----------- -----------------------------------------------------------------------
1 1 おいしい煮物
2 1 さらにおいしい煮物
3 2 黒毛和牛ランプステーキ
4 2 黒毛和牛肩ロース薄切り（しゃぶしゃぶ用）
5 2 黒毛和牛テール（シチュー用）
10 4 誰でも飲める牛乳
6 レコードが選択されました。
db2 => SELECT * FROM Item WHERE KindId = SOME(select KindId FROM Kind WHERE KindTitle='水産');
ITEMID KINDID TITLE
----------- ----------- ------------------------------------------------------------------------
6 3 刺身盛り合わせ
7 3 網にはいってきた魚 各種
8 3 海で取れたもの盛り合わせ
9 3 海底から拾ってきた貝
4 レコードが選択されました。
db2 => SELECT * FROM Item WHERE KindId <> SOME(select KindId FROM Kind WHERE KindTitle='水産');
ITEMID KINDID TITLE
----------- ----------- ------------------------------------------------------------------------
1 1 おいしい煮物
2 1 さらにおいしい煮物
3 2 黒毛和牛ランプステーキ
4 2 黒毛和牛肩ロース薄切り（しゃぶしゃぶ用）
5 2 黒毛和牛テール（シチュー用）
10 4 誰でも飲める牛乳
6 レコードが選択されました。
db2 => SELECT * FROM Item WHERE itemId IN(
db2 (続き) => SELECT itemId FROM Price WHERE price >= ALL(
db2 (続き) => SELECT price FROM PRICE WHERE ItemId BETWEEN 5 AND 9
db2 (続き) => )
db2 (続き) => );
ITEMID KINDID TITLE
----------- ----------- --------------------------------------------------------
3 2 黒毛和牛ランプステーキ
4 2 黒毛和牛肩ロース薄切り（しゃぶしゃぶ用）
7 3 網にはいってきた魚 各種
10 4 誰でも飲める牛乳
4 レコードが選択されました。
※ あまり良い例が思い浮かばなかったｗ・・・のため、コメント。
PriceテーブルにてItemIdが5〜9の中のどれよりも値段の高い商品を出力するSQLです。
素直にMAX(Price)だとかなんだとか使えばこんな回りくどくなくかけるとおもいます。
db2 => SELECT substr(KindTitle,1,8) AS 種類,SUBSTR(Title,1,64) AS 商品名 FROM db2 (続き) => Kind INNER JOIN Item ON Kind.KindId=Item.KindId WHERE Kind.KindId=2; 種類 商品名 -------- ---------------------------------------------------------------- 精肉 黒毛和牛ランプステーキ 精肉 黒毛和牛肩ロース薄切り（しゃぶしゃぶ用） 精肉 黒毛和牛テール（シチュー用） ※ ちなみに、三つ以上の表をINNER JOINした場合は括弧でくくらねばならんらしい。 db2 => SELECT substr(KindTitle,1,8) AS 種類,SUBSTR(Title,1,64) AS 商品名, Price AS 値段 FROM db2 (続き) => (Kind INNER JOIN Item ON Kind.KindId=Item.KindId) INNER JOIN Price ON Item.ItemId=Price.ItemId; 種類 商品名 値段 -------- ---------------------------------------------------------------- ----------- 惣菜 おいしい煮物 850 惣菜 さらにおいしい煮物 1200 精肉 黒毛和牛ランプステーキ 1980 精肉 黒毛和牛肩ロース薄切り（しゃぶしゃぶ用） 2180 精肉 黒毛和牛テール（シチュー用） 680 水産 刺身盛り合わせ 1280 水産 網にはいってきた魚 各種 1480 水産 海で取れたもの盛り合わせ 100 水産 海底から拾ってきた貝 98 日配 誰でも飲める牛乳 28000 10 レコードが選択されました。
db2 => SELECT * FROM Price RIGHT JOIN Item ON Item.ItemId=Price.ItemId;
ITEMID PRICE ITEMID KINDID TITLE
----------- ----------- ----------- ----------- -------------------------------------------------------------
1 850 1 1 おいしい煮物
2 1200 2 1 さらにおいしい煮物
3 1980 3 2 黒毛和牛ランプステーキ
4 2180 4 2 黒毛和牛肩ロース薄切り（しゃぶしゃぶ用）
5 680 5 2 黒毛和牛テール（シチュー用）
6 1280 6 3 刺身盛り合わせ
7 1480 7 3 網にはいってきた魚 各種
8 100 8 3 海で取れたもの盛り合わせ
9 98 9 3 海底から拾ってきた貝
10 28000 10 4 誰でも飲める牛乳
- - 100 1 豪華弁当（高くて買えません）
- - 102 3 見たことない魚（食べれるかどうかは各自判断）
- - 101 2 幻の牛（１頭単位）
- - 103 4 伝説の牛乳（誰も商品をみたことがないから伝説になりました）
14 レコードが選択されました。
db2 => SELECT CASE KindId db2 (続き) => WHEN 1 THEN '惣菜' db2 (続き) => WHEN 2 THEN '精肉' db2 (続き) => WHEN 3 THEN '水産' db2 (続き) => ELSE '日配' db2 (続き) => END, COUNT(*) FROM Item GROUP BY KindId HAVING COUNT(*)>1 db2 (続き) => ; 1 2 ------ ----------- 惣菜 3 精肉 4 水産 5 日配 2
db2 => SELECT itemId FROM Item;
ITEMID
-----------
1
2
3
4
5
6
7
8
9
10
100
101
102
103
14 レコードが選択されました。
db2 => SELECT ItemId FROM Price;
ITEMID
-----------
1
2
3
4
5
6
7
8
9
10
10 レコードが選択されました。
上記のようなデータを使用し、サンプルを書きます。db2 => SELECT ItemId FROM Price
db2 (続き) => UNION
db2 (続き) => SELECT ItemId FROM Item;
ITEMID
-----------
1
2
3
4
5
6
7
8
9
10
100
101
102
103
14 レコードが選択されました。
db2 => SELECT ItemId FROM Price
db2 (続き) => UNION ALL
db2 (続き) => SELECT ItemId FROM Item;
ITEMID
-----------
1
2
3
4
5
6
7
8
9
10
100
101
102
103
1
2
3
4
5
6
7
8
9
10
24 レコードが選択されました。
db2 => SELECT ItemId FROM Price
db2 (続き) => EXCEPT
db2 (続き) => SELECT ItemId FROM Item;
ITEMID
-----------
0 レコードが選択されました。
db2 => SELECT ItemId FROM Item
db2 (続き) => EXCEPT
db2 (続き) => SELECT ItemId FROM Price;
ITEMID
-----------
100
101
102
103
4 レコードが選択されました。
db2 => SELECT ItemId FROM Price
db2 (続き) => INTERSECT
db2 (続き) => SELECT ItemId From Item;
ITEMID
-----------
1
2
3
4
5
6
7
8
9
10
10 レコードが選択されました。
db2 => select itemId,substr(Title,1,64),(SELECT price FROM Price WHERE Itemid=Item.Itemid) FROM Item;
ITEMID 2 PRICE
----------- ---------------------------------------------------------------- -----------
1 おいしい煮物 850
2 さらにおいしい煮物 1200
3 黒毛和牛ランプステーキ 1980
4 黒毛和牛肩ロース薄切り（しゃぶしゃぶ用） 2180
5 黒毛和牛テール（シチュー用） 680
6 刺身盛り合わせ 1280
7 網にはいってきた魚 各種 1480
8 海で取れたもの盛り合わせ 100
9 海底から拾ってきた貝 98
10 誰でも飲める牛乳 28000
100 豪華弁当（高くて買えません） -
102 見たことない魚（食べれるかどうかは各自判断 -
101 幻の牛（１頭単位） -
103 伝説の牛乳（誰も商品をみたことがないから伝 -
14 レコードが選択されました。
db2 => SELECT substr(title,1,64) AS 商品名, price AS 値段 FROM (SELECT * FROM Item JOIN Price ON Item.itemId=Price.ItemId ); 商品名 値段 ---------------------------------------------------------------- ----------- おいしい煮物 850 さらにおいしい煮物 1200 黒毛和牛ランプステーキ 1980 黒毛和牛肩ロース薄切り（しゃぶしゃぶ用） 2180 黒毛和牛テール（シチュー用） 680 刺身盛り合わせ 1280 網にはいってきた魚 各種 1480 海で取れたもの盛り合わせ 100 海底から拾ってきた貝 98 誰でも飲める牛乳 28000 10 レコードが選択されました。
db2 => CREATE VIEW ItemPrice db2 (続き) => AS SELECT substr(kindTitle,1,8) AS 種類名, substr(title,1,64) AS 商品名, price AS 値段 db2 (続き) => FROM Item LEFT JOIN Price On Item.ItemId=Price.ItemId, Kind db2 (続き) => WHERE Kind.KindId=Item.KindId; DB20000I SQL コマンドが正常に完了しました。 db2 => SELECT * FROM ItemPrice; 種類名 商品名 値段 -------- ---------------------------------------------------------------- ----------- 惣菜 おいしい煮物 850 惣菜 さらにおいしい煮物 1200 精肉 黒毛和牛ランプステーキ 1980 精肉 黒毛和牛肩ロース薄切り（しゃぶしゃぶ用） 2180 精肉 黒毛和牛テール（シチュー用） 680 水産 刺身盛り合わせ 1280 水産 網にはいってきた魚 各種 1480 水産 海で取れたもの盛り合わせ 100 水産 海底から拾ってきた貝 98 日配 誰でも飲める牛乳 28000 惣菜 豪華弁当（高くて買えません） - 水産 見たことない魚（食べれるかどうかは各自判断 - 精肉 幻の牛（１頭単位） - 日配 伝説の牛乳（誰も商品をみたことがないから伝 - 14 レコードが選択されました。 db2 => SELECT * FROM ItemPrice WHERE 種類名='惣菜'; 種類名 商品名 値段 -------- ---------------------------------------------------------------- ----------- 惣菜 おいしい煮物 850 惣菜 さらにおいしい煮物 1200 惣菜 豪華弁当（高くて買えません） - 3 レコードが選択されました。