SQL

概要

SQLは、データベースを操作するための言語。RDMS(MySQL, PostgreSQL)の種類によって実装・挙動が微妙に異なる。

Tasks

TODO O’Reilly Japan - SQLアンチパターン

データベース開発におけるアンチパターンを紹介している。

Memo

プロセス一覧を見る

SQLで、DB関連の情報を得ることができる。 

show processlist;

LinuxでのDB閲覧/操作ツール

Macでは、Sequel Pro が多いはず。 Linuxでは、DBeaver が有力のよう。

インストールページ。 Download | DBeaver Community 

sudo snap install dbeaver-ce

↑snapだとバージョンが古く、エラーが出た。最初のローディング画面は出るが、それ以降が何も表示されない。

org.eclipse.swt.SWTException: Graphic is disposed

↓このissueと同じ状況に見える。 https://github.com/dbeaver/dbeaver/issues/8319

公式サイトからdebファイルをダウンロードして入れると、無事起動した。 Download | DBeaver Community

  • レコード編集機能がある
    • 表示レコードをそのままいじれる機能。
    • DBeaverのほかにsqlelectronもいい感じだったのだが、カラムから編集できる機能がないのは致命的で使うのを見送った。
  • ER図を自動生成してくれる
    • 関連できたりできなかったりだが、最初から入ってるのはありがたい。

正規表現

~ で正規表現が使える。 

select *
from customer
where status_cd ~ '^[A-F]'

カラムにない項目で集計する

重複してださい方法。 

-- 人口階級ごとに地方名に再分類する
SELECT CASE WHEN population < 100 THEN '01'
       WHEN population >= 100 AND population < 200 THEN '02'
       WHEN population >= 200 AND population < 300 THEN '03'
       WHEN population >= 300 THEN '04'
       ELSE NULL END AS pop_class,
       COUNT(*) AS cnt
  FROM PopTbl
 GROUP BY CASE WHEN population < 100 THEN '01'
          WHEN population >= 100 AND population < 200 THEN '02'
          WHEN population >= 200 AND population < 300 THEN '03'
          WHEN population >= 300 THEN '04'
          ELSE NULL END;

DBによっては名前をつけて参照できる。 

-- 県名を地方名に分類する
SELECT CASE pref_name
       WHEN '徳島' THEN '四国'
       WHEN '香川' THEN '四国'
       WHEN '愛媛' THEN '四国'
       WHEN '高知' THEN '四国'
       WHEN '福岡' THEN '九州'
       WHEN '佐賀' THEN '九州'
       WHEN '長崎' THEN '九州'
       ELSE 'その他' END AS district,
       SUM(population)
  FROM PopTbl
 GROUP BY district;

条件を指定して集計する

where句で異なる条件を記述して2回SQLを発行するような場面。 

  -- 男性の人口
SELECT pref_name,
       population
  FROM PopTbl2
 WHERE sex = '1';

-- 女性の人口
SELECT pref_name,
       population
  FROM PopTbl2
 WHERE sex = '2';

case句で便利に書くとこう。 

SELECT pref_name,
  -- 男性の人口
       SUM(CASE WHEN sex = '1' THEN population ELSE 0 END) AS cnt_m,
  -- 女性の人口
       SUM(CASE WHEN sex = '2' THEN population ELSE 0 END) AS cnt_f
  FROM PopTbl2
 GROUP BY pref_name;

SUM関数を必要性をチェックしてみる。

複数の列の条件関係を定義する

CHECK制約。

↓条件法。 

CONSTRAINT check_salary CHECK
  (CASE WHEN sex = '2'
    THEN CASE WHEN salary <= 200000
      THEN 1 ELSE 0 END
  ELSE 1 END = 1)

↓論理積。 

CONSTRAINT CHECK_salary CHECK
  (sex = '2' AND salary <= 200000)

条件法はゆるい。

UPDATEの順番で結果が変わるとき

1回目のupdateをした結果、2回目の条件に入ってしまうような場合。 caseをつかって1度に処理しないといけない。 

UPDATE Personnel
   SET salary = CASE WHEN salary >= 300000
       THEN salary * 0.9
       WHEN salary >= 250000 AND salary < 280000
       THEN salary * 1.2
       ELSE salary END;

主キーを入れ替えることもできる。普通にUPDATE3回だと退避させる必要があるが、whenだと一気にできる。 

-- CASE式で主キーを入れ替える
UPDATE SomeTable
   SET p_key = CASE WHEN p_key = 'a'
       THEN 'b'
       WHEN p_key = 'b'
       THEN 'a'
       ELSE p_key END
       WHERE p_key IN ('a', 'b');

Refences

SQLZOO

SQLの練習ができるサイト。

data.world | The Cloud-Native Data Catalog

サンプルのデータを使って、SQLの練習ができる。