OSS-DB Silver 暗記 C-1-1
Contents
開発/SQL（32%）：：：SQL：SELECT, INSERT, UPDATE, DELETE【重要度：13】dokoQL可
- dokoQL で実習が可能です
SELECT
- 書式：SELECT column_name FROM table_name;
=> SELECT * FROM member; ----+--------+-----+--------- id | name | age | project ----+--------+-----+--------- 1 | tanaka | 22 | A 2 | kimura | 35 | B 3 | ogawa | 29 | B 4 | suzuki | 45 | A 5 | maeda | 29 | B 6 | suzuki | 35 | C ----+--------+-----+---------
暗記
- 条件式
- グループ化する前： WHERE
- グループ化した後： HAVING
- 並べ替え
- ORDER BY は必ずクエリー文の一番最後に記述
WHERE
- 構文：WHERE condition
- condition：boolean型を返す任意の式
=> SELECT * FROM member WHERE age < 25; -- コメント ----+--------+-----+--------- id | name | age | project ----+--------+-----+--------- 1 | tanaka | 22 | A ----+--------+-----+---------
暗記
- WHERE と HAVING の違い
- WHERE condition
- GROUP BY grouping_element HAVING condition
GROUP BY
- 構文：GROUP BY grouping_element
=> SELECT project,max(age) FROM member GROUP BY project; -- project の最高齢 ---------+----- project | max ---------+----- B | 35 C | 35 A | 45 ---------+-----
HAVING
- 構文：HAVING condition
- condition：boolean型を返す任意の式
- 説明：グループ化された行の中で、条件を満たす行を取り出す
=> SELECT project, avg(age) FROM member GROUP BY project HAVING avg(age)<35; ---------+------- project | avg ---------+------- B | 31.0 C | 33.5 ---------+-------
DISTINCT
- 書式：SELECT DISTINCT [ ON ( column_name ) ] column_name FROM table_name;
- 説明：重複除去
- 副作用：結果がソートされる
例：ON を指定しない
=> SELECT DISTINCT age FROM member; -- 昇順にソートされる ----- age ----- 22 29 35 45 -----
例：ON を指定する
=> SELECT DISTINCT ON (age) * FROM member; -- 昇順にソートされる ----+--------+-----+--------- id | name | age | project ----+--------+-----+--------- 1 | tanaka | 22 | A 3 | ogawa | 29 | B 2 | kimura | 35 | B 4 | suzuki | 45 | A ----+--------+-----+---------
クエリ―の結合
- 2つの問い合わせ結果は、和・積・差の集合演算を使って結合できる
暗記
- 和：query1 UNION query2
- 積：query1 INTERSECT query2
- 差：query1 EXCEPT query2
UNION（和）
- 構文： query1 UNION [ ALL ] query2
- ALL：重複行出力
- 意味： query1 ∪ query2
例：ALL を指定しない
=> SELECT * FROM table1 UNION SELECT * FROM table2; ----+------ id | name ----+------ 2 | b 9 | x 3 | c 1 | a ----+------
例：ALL を指定
=> SELECT * FROM table1 UNION SELECT * FROM table2; ----+------ id | name ----+------ 1 | a -- 2 | b 3 | c -- 1 | a ++ 3 | c ++ 9 | x ----+------
INTERSECT（積）
- 構文： query1 INTERSECT [ ALL ] query2
- ALL：重複行出力
- 意味： query1 ∩ query2
=> SELECT * FROM table1 INTERSECT SELECT * FROM table2; ----+------ id | name ----+------ 1 | a 3 | c ----+------
EXCEPT（差）
- 構文： query1 EXCEPT [ALL] query2
- ALL：重複行出力
- 意味： query1 – query2
=> SELECT * FROM table1 EXCEPT SELECT * FROM table2; ----+------ id | name ----+------ 2 | b ----+------
テーブル間を結合
- 結合方法：CROSS JOIN, INNER JOIN, OUTER JOIN がある
- 結合キー：結合の際に条件として使用する列
暗記
- 結合キー
- CROSS JOIN：結合キーを使わない
- INNNER JOIN ：結合キーを使う
- OUTER JOIN
- INNER JOIN の結果を拡張したもの
CROSS JOIN
- 書式：SELECT column_name FROM table1 CROSS JOIN table2 WHERE condition;
- 書式：SELECT column_name FROM table1 , table2 WHERE condition;
- 説明：出力tableの行数（m×n） = table1の行数（m）×table2の行数（n）
=> SELECT * FROM table1, table2; -- table（9） = table1（3）×table2（3）
=> SELECT * FROM table1, table2 WHERE table1.id=1; ----+------+----+------ id | name | id | name ----+------+----+------ -- 結合キーを指定していない 1 | a | 1 | xxx 1 | a | 3 | yyy 1 | a | 9 | zzz ----+------+----+------
INNER JOIN
- 書式：SELECT column_name FROM table1 [ INNER ] JOIN table2 ON table1.column_name = table2.column_name;
- 書式：SELECT column_name FROM table1 [ INNER ] JOIN table2 USING ( column_name );
- 書式：SELECT column_name FROM table1 NATURAL [ INNER ] JOIN table2;
=> SELECT * FROM table1 JOIN table2 ON table1.id=table2.id; ----+------+----+------ id | name | id | name ----+------+----+------ -- 結合キー：id 1 | a | 1 | xxx 3 | c | 3 | yyy ----+------+----+------
=> SELECT * FROM table1 JOIN table2 USING (id); ----+------+------ id | name | name ----+------+------ -- 結合キー：id 1 | a | xxx 3 | c | yyy ----+------+------
=> SELECT * FROM table1 NATURAL JOIN table2; ----+------+------ id | name | name ----+------+------ -- 結合キー：自動選択（同じ名前で）→ id 1 | a | xxx 3 | c | yyy ----+------+------
OUTER JOIN
- 書式：SELECT column_name FROM table1 { LEFT | RIGHT | FULL } [ OUTER ] JOIN table2 ON table1.column_name = table2.column_name;
- 書式：SELECT column_name FROM table1 { LEFT | RIGHT | FULL } [ OUTER ] JOIN table2 USING ( column_name );
- 書式：SELECT column_name FROM table1 NATURAL { LEFT | RIGHT | FULL } [ OUTER ] JOIN table2;
暗記
- 左のテーブル（table1）の情報がすべて残る ☞ 左が残る ☞ LEFT
- 右のテーブル（table2）の情報がすべて残る ☞ 右が残る ☞ RIGHT
- 左右テーブル（table1, table2）の情報がすべて残る ☞ 左右が残る ☞ FULL
=> SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id; ----+------+--------+-------- id | name | id | name ----+------+--------+-------- 1 | a | 1 | xxx 2 | b | (NULL) | (NULL) 3 | c | 3 | yyy ----+------+--------+--------
=> SELECT * FROM table1 RIGHT JOIN table2 USING (id); ----+--------+------ id | name | name ----+--------+------ 1 | a | xxx 3 | c | yyy 9 | (NULL) | zzz ----+--------+------
=> SELECT * FROM table1 NATURAL FULL JOIN table2; ----+------ id | name ----+------ 1 | a 1 | xxx 2 | b 3 | c 3 | yyy 9 | zzz ----+------
ORDER BY
- 構文：ORDER BY { column_name | column_number } [ ASC | DESC ]
- 意味：SELECT文で検索した結果をソートする[ 昇順 | 降順 ]
=> SELECT id, age, name FROM member ORDER BY age; ----+-----+-------- id | age | name ----+-----+-------- 1 | 22 | ogawa 3 | 29 | tanaka 5 | 29 | maeda 2 | 35 | kimura 6 | 35 | suzuki 4 | 45 | suzuki ----+-----+--------
LIMIT（OFFSET）
- 構文：LIMIT { number | ALL } [ OFFSET number ]
- 説明：ORDER BY と一緒に使用する。OFFSETはスキップする行数
=> SELECT * FROM member ORDER BY age LIMIT 1 OFFSET 2; ----+-------+-----+--------- id | name | age | project ----+-------+-----+--------- 5 | maeda | 29 | B ----+-------+-----+---------
SUBQUERY
- サブクエリ―：副問い合わせ
EXISTS（NOT EXISTS）
- 構文：EXISTS ( subquery )
- 書式：SELECT column_name FROM table_name WHERE EXISTS ( subquery );
=> SELECT * FROM member
WHERE NOT EXISTS (SELECT 1 FROM project
WHERE member.project_id=project.project_id);
----+--------+-----+------------
id | name | age | project_id
----+--------+-----+------------
7 | toyota | 25 | 5
----+--------+-----+------------
IN（NOT IN）
- 構文：IN ( subquery )
- 書式：SELECT column_name FROM table_name WHERE IN ( subquery );
=> SELECT * FROM member WHERE project_id NOT IN (SELECT project_id FROM project); ----+--------+-----+------------ id | name | age | project_id ----+--------+-----+------------ 7 | toyota | 25 | 5 ----+--------+-----+------------
ANY / SOME
- 構文：ANY ( subquery )
- 構文：SOME ( subquery )
- 書式：SELECT column_name FROM table_name WHERE column_name operator ANY ( subquery );
- 説明：SOMEはANYの同義語。INはANYと等価
=> SELECT * FROM member WHERE project_id = ANY (SELECT project_id FROM project); ----+--------+-----+------------ id | name | age | project_id ----+--------+-----+------------ 1 | ogawa | 22 | 1 2 | kimura | 35 | 2 3 | tanaka | 29 | 2 4 | suzuki | 45 | 1 5 | maeda | 29 | 2 6 | suzuki | 35 | 3 ----+--------+-----+------------
IN, ANY, BETWEEN
IN（NOT IN）
- 書式：SELECT column_name FROM table_name WHERE column_name IN ( values );
=> SELECT * FROM member WHERE project_id IN (1, 5); ----+--------+-----+------------ id | name | age | project_id ----+--------+-----+------------ 1 | ogawa | 22 | 1 ----+--------+-----+------------ 4 | suzuki | 45 | 1 ----+--------+-----+------------ 7 | toyota | 25 | 5 ----+--------+-----+------------
ANY / SOME
- 書式：SELECT column_name FROM table_name WHERE column_name operator ANY ( array expression );
- array expression：値を指定する場合は、配列形式にする
=> SELECT * FROM member
WHERE project_id = ANY ( '{1, 5}' );
----+--------+-----+------------
id | name | age | project_id
----+--------+-----+------------
1 | ogawa | 22 | 1
----+--------+-----+------------
4 | suzuki | 45 | 1
----+--------+-----+------------
7 | toyota | 25 | 5
----+--------+-----+------------
BETWEEN
- 書式：SELECT column_name FROM table_name WHERE column_name BETWEEN value AND value;
=> SELECT * FROM member WHERE project_id BETWEEN 3 AND 99; ----+--------+-----+------------ id | name | age | project_id ----+--------+-----+------------ 6 | suzuki | 35 | 3 ----+--------+-----+------------ 7 | toyota | 25 | 5 ----+--------+-----+------------
INSERT, UPDATE, DELETE
INSERT
- 書式：INSERT INTO table_name [ ( column_name [, …] ) ] VALUES ( { expression | value | DEFAULT } [, …] );
- 書式：INSERT INTO table_name [ ( column_name [, …] ) ] query;
=> INSERT INTO project VALUES ( 4, 'D' );
------------+--------------
project_id | project_name
------------+--------------
1 | A
2 | B
3 | C
4 | D
------------+--------------
UPDATE
- 書式：UPDATE table_name SET column_name = value [, …] WHERE condition;
=> UPDATE project SET project_id=5, project_name='E' WHERE project_id=4;
------------+--------------
project_id | project_name
------------+--------------
1 | A
2 | B
3 | C
5 | E
------------+--------------
DELETE（TRUNCATE）
- 書式：DELETE FROM table_name [ WHERE condition ];
- 書式：TRUNCATE FROM table_name;
- 説明：テーブルのすべての行を削除する場合は TRUNCATE を使用する
=> DELETE FROM project WHERE project_id=5;
------------+--------------
project_id | project_name
------------+--------------
1 | A
2 | B
3 | C
------------+--------------
参考
- IT資格といえばLPI-Japan | LinuC/OSS-DB/HTML5/ACCEL/OPCEL
- PostgreSQL 11.5文書
- OSS教科書 OSS-DB Silver Ver2.0対応
- 徹底攻略OSS-DB Silver問題集［Ver.2.0］対応
- dokoQL 学習用オンラインSQL実行環境
- スッキリわかるSQL入門 第2版 ドリル222問付き! (スッキリシリーズ)
