OSS-DB Silver 暗記 C-1-1

開発/SQL(32%):::SQL:SELECT, INSERT, UPDATE, DELETE【重要度:13】

 

 

1. SELECT

 

  • 書式:SELECT  column_name  FROM  table_name;

 

member-table postgresql
Fig.1 member table

 

 

 

=> 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つの問い合わせ結果は、和・積・差の集合演算を使って結合できる

 

table1 table2 postgresql
Fig.2 table1, table2

 

 

1.5.1 UNION

  • 構文: query1  UNION  [ ALLquery2
    • 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  [ALLquery2
    • ALL:重複行出力
 
=> SELECT * FROM table1 EXCEPT SELECT * FROM table2;
----+------
 id | name
----+------
  2 | b
----+------

 

 

1.6 テーブル間を結合

 

table1, new table2 postgresql
Fig.3 table1, new table2

 

 

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  [ INNERJOIN  table2  USING  ( column_name );
  • 書式:SELECT  column_name  FROM  table1  NATURAL  [ INNERJOIN  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 ]

 

member-table postgresql
Fig.4 member table

 

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

  • サブクエリ―:副問い合わせ

 

member project postgresql
Fig.5 member-project

 

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

 

project table postgresql
Fig.6 project table

 

 

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

 

スポンサーリンク

コメントを残す

メールアドレスが公開されることはありません。