Theme
SD MILIEU

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 のレコードが2回出てくる。(ただ id が primary_key の場合はエラーになるか…?)

JOIN

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

INNER JOIN

itemsテーブル

idnameprice
1pencil100
2penNULL
3eraser50

shopsテーブル

idname
1first
2second
3third

item_shopテーブル

idshop_iditem_idnum
11110
21220
3235

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

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;

こうすると、

idshop_iditem_idnumnameprice
11110pencil100
21220penNULL
3235eraser50

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

その他知識

NULL に関して

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

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

比較演算子がつかえない

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

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

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

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

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

idnameprice
1pencil100
2penNULL
3eraser50

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

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

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

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

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

集約関数

COUNTとかSUMとかのこと。

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

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

記法

idnameprice
1pencil100
2penNULL
3eraser50

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

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

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

重複を省いて計算

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

トランザクション

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

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

  1. トランザクション A 開始
  2. トランザクション B 開始
  3. トランザクション A コミット
  4. トランザクション B コミット

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

疑問

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

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

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