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 ------------+--------------