2018-12-11

SQLメモ

基本的なコマンド等

SELECT

SELECT直後の列指定には、式も指定できる。

SELECT id, name AS '商品名', price, price - genka AS '利益'
  FROM items
  WHERE price >= 1000
    AND price - genka >= 100
    AND name IS NOT NULL
    AND (created_at < '2010-10-10' OR created_at > '2017-10-10');

GROUP BY

SELECT instance_id, COUNT(*) AS '合計'
  FROM instance_logs
  WHERE instance_id > 50
  GROUP BY instance_id
  HAVING COUNT(*) > 400;

INSERT

INSERT INTO items
  (
    name,
    price,
    genka,
    category
  )
  VALUES (
    'pencil',
    200,
    NULL,
    DEFAULT
  );

他のテーブルからコピー

VALUES (~)の代わりにSELECTを使って他のテーブルからデータをコピーして挿入することが可能。

INSERT INTO items_master
  (
    name,
    price,
    genka,
    category
  )
  SELECT name, price, genka, category
    FROM items;

DELETE

DELETE FROM items
  WHERE price > 200;

全件削除

WHEREの指定をしなかった場合、指定したテーブルのレコードを全件削除する。

DELETE FROM items;

ただし、処理速度の観点からTRUNCATE テーブル名を使用するのが一般的。

UPDATE

UPDATE items
  SET price = 200
  WHERE id = 4;

DELETEと同様に、WHEREを指定しなかった場合は、全レコードに対して処理が実行される。

CREATE VIEW

よく使う SELECT のショートカットを作るみたいな機能。

CREATE VIEW items_view (id, name, price)
  AS
    SELECT id, name, price
      FROM items
      WHERE price > 100;

上記のようにビューを作っておくと、SELECT * FROM items_view;みたいな感じでビューを元に SELECT が叩ける。

サブクエリ

SELECTの結果を SQL 文内で使用する書き方の事。

SELECT id, name, price
  FROM (
      SELECT id, name, price
        FROM items
        WHERE price > 100
    ) AS sub_items
  WHERE name = 'pen';

スカラサブクエリ

SELECTの結果を定数として使用するサブクエリの事。

SELECT id, name, price, (
    SELECT AVG(price) FROM items
  ) AS average
  FROM items;

上記クエリで、平均価格が各レコードの右端に表記される。

相関サブクエリ

例えば、「カテゴリー毎の平均販売価格より高い商品を抜き出す」といった処理を行いたい場合に使う。

SELECT id, name, price, category
  FROM items
  WHERE price > (
      SELECT AVG(price)
        FROM items AS sub_items
        WHERE items.category = sub_items.category
        GROUP BY category
    );

述語

述語とは、返り値が論理値になる関数のこと。

LIKE

文字列の部分一致検索を行う。

-- 前方一致（例だとpencilとかが該当）
SELECT * FROM items WHERE name = 'pen%';
-- 中間一致（pencil,append,deepenとかが該当）
SELECT * FROM items WHERE name = '%pen%';
-- 後方一致（deepenが該当）
SELECT * FROM items WHERE name = '%pen';

BETWEEN

数値の範囲検索を行う。

-- 100以上1000以下の値を検索
SELECT * FROM items WHERE price BETWEEN 100 AND 1000;

-- 以下のクエリと同じ意味
SELECT * FROM items WHERE price >= 100 AND price <= 1000;

IN

ORの省略形

-- priceが100か200か300のレコードを検索
SELECT * FROM items WHERE price IN (100, 200, 300);

-- 以下のクエリと同じ意味
SELECT * FROM items WHERE price = 100 OR price = 200 OR price = 300;

-- INと組み合わせることも可能
SELECT *
  FROM items
  WHERE id IN (
      SELECT id
        FROM items
        WHERE price > 100
    );

-- NOT INとも
SELECT *
  FROM items
  WHERE id NOT IN (
      SELECT id
        FROM items
        WHERE price > 100
    );

CASE 式

条件分岐が出来る。

SELECT name
  CASE WHEN category = 'PHP'
    THEN 'A:' || category
  CASE WHEN category = 'JavaScript'
    THEN 'B:' || category
  ELSE NULL
  FROM items;

集合演算

集合の演算も可能。（和集合、差集合、積集合）

SELECT *
  FROM items
  WHERE id IN (1, 2)
UNION
SELECT *
  FROM items
  WHERE id IN (2, 3);

結果として id が 1,2,3 のレコードが取得される。

UNION（和集合）以外にもINTERSECT（積集合）とEXCEPT（差集合）が使用できる。

ALL オプション

和集合、積集合には集合同士の交差部分が存在している。デフォルトだと、この交差している、つまり重複しているレコードはひとつにまとめられるが、ALLオプションを使用することで重複レコードも残しておくことが出来る。

SELECT *
  FROM items
  WHERE id IN (1, 2)
UNION ALL
SELECT *
  FROM items
  WHERE id IN (2, 3);

この SQL では、id2 のレコードが２回出てくる。（ただ id が primary_key の場合はエラーになるか…？）

JOIN

テーブル同士を横方向に結合する処理。

INNER JOIN

itemsテーブル

id	name	price
1	pencil	100
2	pen	NULL
3	eraser	50

shopsテーブル

id	name
1	first
2	second
3	third

item_shopテーブル

id	shop_id	item_id	num
1	1	1	10
2	1	2	20
3	2	3	5

この３テーブルが存在しているとする。

SELECT
  item_shop.id
  item_shop.shop_id
  item_shop.item_id
  item_shop.num
  items.name
  items.price
  FROM item_shop INNER JOIN items
  ON item_shop.item_id = items.id;

こうすると、

id	shop_id	item_id	num	name	price
1	1	1	10	pencil	100
2	1	2	20	pen	NULL
3	2	3	5	eraser	50

という結果を得ることが出来る。

その他知識

NULL に関して

テーブルを定義する際には、原則としてNOT NULL制約を設定すべきらしい。

理由は色々あるみたいだが、勉強した限りでは大きく以下の２点。

比較演算子がつかえない

NULLを検索したい場合はIS NULL、またはIS NOT NULLを使用する必要がある。

usersテーブルにnameカラムがNULLのレコードがあるとする。

その際にSELECT * FROM users WHERE name = NULL;とクエリを叩いたとしても、１レコードも返ってこない。

なので、SELECT * FROM users WHERE name IS NULL;といった形にしないといけない。

NULL 値を取りうるカラムに対する比較演算の注意

id	name	price
1	pencil	100
2	pen	NULL
3	eraser	50

上記のようなitemsテーブルがある際に、

SELECT *
  FROM items
  WHERE name = 'pen'
    AND price <> 100;

このようなクエリを叩いたとする。

感覚的にはpenのレコードが返ってきそうな気がするが、実際はレコードは１行も返ってこない。

理由はprice <> 100の部分、penのpriceはNULLである。NULLに対する論理演算の結果はtrueでもfalseでもなく、unknownという状態になってしまう。unknownはfalseに近い扱いをされる（実際には細かい部分で違うが）ので、ANDの条件を満たさず結果的にレコードは１行も返されない。

集約関数

COUNTとかSUMとかのこと。

集約関数が使えるのは、SELECT句、HAVING句、ORDER BY句のみ。（WHERE句では使用できないので注意。）

集約関数をSELECT句で使用すると、結果は 1 行になる。（ex: SELECT COUNT(*) FROM items;）

記法

id	name	price
1	pencil	100
2	pen	NULL
3	eraser	50

関数名(対象カラム名)という風に書く。例えばAVG(price)。

注意として、集約関数はNULLを除外する。例えばAVG(price)の結果は(100 + 50) / 2とNULLを除外して計算し、結果として75となる。NULLを特定の値にみなした上で含めたい場合はCOALESCE関数を使用する模様。

例外として、COUNT(*)はNULLも含めて数える。

重複を省いて計算

DISTINCTを使用する。例えばテーブル内のcategoryカラムのパターンを数えたい場合は、SELECT COUNT(DISTINCT category) FROM items;みたいな感じで。

トランザクション

BEGIN TRANSACTIONをしておくと、COMMITするまで実データに影響を与えること無く SQL を実行できる。トランザクションをキャンセルする場合はROLLBACKをすればいい。

トランザクションに関する疑問

トランザクション A 開始
トランザクション B 開始
トランザクション A コミット
トランザクション B コミット

このような状況において、トランザクション A で B に影響があるような SQL を実行していた場合どうなるのか？

疑問

アプリケーションプログラムでの対応をした方がいいパターンも多いのでは？

SQL で出来ることはかなり多いけど、一種のシェル芸みたいな感じになって時間が経つと理解が出来ないようなクエリが出てきたりするのではないかという疑問。

それをするのであれば、ある程度はアプリケーション内でフィルタ処理等をしたほうがいいのではとか思った。