OSS-DB Silver 暗記 C-1-1
Contents
開発/SQL（32%）：：：SQL：SELECT, INSERT, UPDATE, DELETE【重要度：13】
1. SELECT
- 書式：SELECT column_name FROM table_name;
=> SELECT * FROM member;
1.1 WHERE
- 構文：WHERE condition
- condition：boolean型を返す任意の式
=> SELECT * FROM member WHERE age < 25; -- ----+--------+-----+--------- id | name | age | project ----+--------+-----+--------- 1 | tanaka | 22 | A ----+--------+-----+---------
1.2 GROUP BY
- 構文：GROUP BY grouping_element
=> SELECT project,max(age) FROM member GROUP BY project; -- project の最高齢 ---------+----- project | max ---------+----- B | 35 C | 35 A | 45 ---------+-----
1.3 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 ---------+-------
1.4 DISTINCT
- 書式：SELECT DISTINCT [ ON column_name ] column_name FROM table_name;
- 説明：重複除去
=> SELECT DISTINCT age FROM member; ----- age ----- 22 29 35 45 -----
1.5 クエリ―の結合
- 2つの問い合わせ結果は、和・積・差の集合演算を使って結合できる
1.5.1 UNION
- 構文： query1 UNION [ ALL ] query2
- ALL：重複行出力
=> SELECT * FROM table1 UNION SELECT * FROM table2; ----+------ id | name ----+------ 2 | b 9 | x 3 | c 1 | a ----+------
1.5.2 INTERSECT
- 構文：query1 INTERSECT [ ALL ] query2
- ALL：重複行出力
=> SELECT * FROM table1 INTERSECT SELECT * FROM table2; ----+------ id | name ----+------ 1 | a 3 | c ----+------
1.5.3 EXCEPT
- 構文：query1 EXCEPT [ALL] query2
- ALL：重複行出力
=> SELECT * FROM table1 EXCEPT SELECT * FROM table2; ----+------ id | name ----+------ 2 | b ----+------
1.6 テーブル間を結合
1.6.1 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 ----+------+----+------
1.6.2 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 ----+------+----+------ 1 | a | 1 | xxx 3 | c | 3 | yyy ----+------+----+------
=> SELECT * FROM table1 JOIN table2 USING (id); ----+------+------ id | name | name ----+------+------ 1 | a | xxx 3 | c | yyy ----+------+------
=> SELECT * FROM table1 NATURAL JOIN table2; ----+------+------ id | name | name ----+------+------ 1 | a | xxx 3 | c | yyy ----+------+------
1.6.3 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;
- LEFT： table1の情報がすべて残る ☞ 左が残る
- RIGHT： table2の情報がすべて残る ☞ 右が残る
- FULL： table1とtable2の情報がすべて残る ☞ 左右が残る
=> 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 ----+------
1.7 ORDER BY
- 構文：ORDER BY { column_name | column_number } [ ASC | DESC ]
=> 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 ----+-----+--------
1.8 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 ----+-------+-----+---------
1.9 SUBQUERY
- サブクエリ―：副問い合わせ
1.9.1 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
----+--------+-----+------------
1.9.2 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 ----+--------+-----+------------
1.9.3 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 ----+--------+-----+------------
1.10 IN, ANY, BETWEEN
1.10.1 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 ----+--------+-----+------------
1.10.2 ANY / SOME
- 書式：SELECT column_name FROM table_name WHERE column_name operator ANY ( 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
----+--------+-----+------------
1.10.3 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 ----+--------+-----+------------
2. INSERT, UPDATE, DELETE
2.1 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
------------+--------------
2.2 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
------------+--------------
2.3 DELETE（TRUNCATE）
- 書式：DELETE FROM table_name [ WHERE condition ];
- 書式：TRUNCATE FROM table_name;
=> DELETE FROM project WHERE project_id=5;
------------+--------------
project_id | project_name
------------+--------------
1 | A
2 | B
3 | C
------------+--------------
