SQL

SQLでよく使う慣用句的なものを集めてみる。

SELECT文

  • テーブルのデータを検索します。
    SELECT 項目名 FROM テーブル名 [WHERE 検索条件] [ORDER BY ソート方法]

重複をまとめて表示する(1)

DISTINCTオプションを使用すると、同じレコードをまとめて表示できる。

SELECT DISTINCT col1, col2 FROM tbl

重複をまとめて表示する(2)

GROUP BY句を用いる方法。重複を認める項目を細かく指定できる利点がある。また、COUNT関数を使うなどして何件の重複があるかを調べたりすることも出来る。

SELECT col1, col2 FROM tbl GROUP BY col1, col2

UPDATE文

  • データを更新する。
  • WHERE句を適切に設定しないと、意図しないレコードが更新されてしまうので注意。
  • WHERE句を指定しない場合は全レコードが対象となる。
    UPDATE テーブル名 SET 項目名1=データ1 , 項目名2=データ2] ... [WHERE 更新レコード条件]

全レコード更新

  • WHERE句を指定しない場合に全レコードが対象となることを利用する。
  • 例えば、あるカラムの値を2倍したい場合
    update tblSample set col_a = col_a * 2

SELECT文の結果をUPDATE

  • テーブル2からテーブル1への更新を行う場合は次のようにする。
  • IDで紐付け、テーブル2に存在しないレコードは対象外。
    UPDATE テーブル1 as A SET (A.カラム1, A.カラム2, ...)
        = (SELECT B.カラム1, B,カラム2, ... FROM テーブル2 as B WHERE A.ID = B.ID)
      WHERE EXISTS (SELECT * FROM テーブル2 WHERE A.ID = B.ID)
  • SQL Serverの場合はFROM句を使って次のように記述する。
    UPDATE テーブル1
       SET カラム1 = B.カラム1, カラム2 = B.カラム2, ...
      FROM テーブル2 as B
      WHERE テーブル1.ID = B.ID

INSERT文

  • データを新規追加する。
  • 値を指定しない項目はNULL値が設定される。
    INSERT INTO テーブル名 (項目名1, 項目名2, ...) VALUES (データ1, データ2, ...)

    INSERT INTO テーブル名 VALUES (データ1, データ2, ...)

SELECT文の結果をINSERT

  • SELECT文の出力を元に新規追加することも出来る。
    INSERT INTO テーブル名 (項目名1, 項目名2, ...) SELECT * FROM テーブル名2

DELETE文

  • データを削除する。
  • WHERE句を適切に設定しないと、意図しないレコードが削除されてしまうので注意。
  • WHERE句を指定しない場合は全レコードが対象となる。
    DELETE FROM テーブル名 [WHERE 削除条件]

TRUNCATE文

  • テーブルの全レコードを対象に削除を行う。
  • WHERE句を指定しないDELETE文と、ほぼ同様の結果が得られる。
  • DELETE文より高速。（DELETE文は1行ずつ削除するため、トランザクションログも1行ずつ作成する、全行ロックも行われる）
    TRUNCATE テーブル名

CASE文

  • 条件によりスカラ値を返す。
  • 簡易的な条件分岐を埋め込めるので便利。

ある値との一致による振り分け

CASE sex WHEN 'M' THEN '男' WHEN 'F' THEN '女' ELSE '他' END

条件式による振り分け 

CASE WHEN age < 20  THEN '未成年'

     WHEN age < 100 THEN '成人'
                    ELSE '100歳以上'
END

EXISTS関数

  • SELECT文が行を見つけることに成功した場合、Trueを返す関数。
  • 単独では使わず、WHERE句などの条件式として使用する。
    EXISTS( SELECT ... FROM ... WHERE ...)

応用編

空き番号を探す

  • ユーザが自由に番号を決められるけど、指定が無い場合はユニークな番号を設定したい、みたいな場合、空き番号を探してくる必要がある。
  • これについて検索すると同じことで悩んだ人が多いらしくいろいろ見つかる。
    そのうちでやり方がシンプルかな、と思った方法を一つメモしておく。
    SELECT MIN(t.number) as no FROM table as t
      WHERE t.deletedAt is null -- 論理削除などの条件があれば記述
        AND (t1.number + 1) not in
             ( SELECT number FROM table WHERE deletedAt is null )
  • 原理
    • 全てのnumberについて走査。
    • サブクエリを使って次の番号が存在していないものを条件として抽出。
    • 抽出した値の中での最小値を返す。
    • 最小値なのでテーブルが空の場合はゼロが返ってくる。
    • この値を使って採番する場合は＋１する。