0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

自分用SQLチートシート( 「スッキリわかるSQL入門」まとめ)

Last updated at Posted at 2024-08-21

書籍を読んだ内容をまとめて、自分用チートシートを作成しました。
元ネタは以下書籍です。
(初心者のため、間違い等ありましたら、お知らせいただけると助かります。)

参考書籍「スッキリわかるSQL入門」

データ型

INTEGER は数値
VARCHAR は可変文字列
CHAR(10) は固定文字列 10文字未満の場合は、空欄が追加される。
DECIMAL 小数(他にもある)
TIMESTAMP 日付+時刻
DATE 日付のみ
TIME 時刻のみ

基本コマンド(コマンドの終わりに ; を忘れずに。)

SELECT 列名 FROM テーブル名 WHERE 条件;
UPDATE テーブル名 SET 列名= WHERE 条件;
DELETE FROM テーブル名 WHERE 条件;
--DELETEの後は列名無し
INSERT INTO テーブル名 (列名) VALUES ();
--すべての項目がVALUESにあれば列名は省略できる。VALUESは、CHAR型なら’’が必要!

列名に' 'はいらないが、値に文字列を指定するときは' 'が必要。
AS を使うと、列名に「別名」を付けることができる。

WHERE条件

条件A AND 条件B AかつB 
条件A OR 条件B AまたはB
--注意! ANDのほうが先に判定されるので、ORを先に適用するには(A OR B)で囲む。

--比較
<= 以下
>= 以上
<> 一致しない

BETWEEN A AND B AからBの間(AB含む。日付の場合使いやすい。)
IS NULL NULLの値
IS NOT NULL NULLではない
IN (A,B) AB

<ANY (サブクエリ) サブクエリすべてのOR未満
>=ALL (サブクエリ) サブクエリすべてのAND以上

LIKE '正規表現文字列' 正規表現文字列に該当するか
--正規表現文字列
 % 0文字以上の任意
 _ 1文字の任意
 %A Aで終わる。
 %B% Bを含む。

重複・並び替え・TOPランキング

SELECT DISTINCT 列名 FROM テーブル名; 列の重複を削除してリスト化する。
ORDER BY 列名 ASC 昇順
ORDER BY 列名 DESC 降順

OFFSET 数値A ROWS 
FETCH NEXT 数値B ROWS ONLY --先頭行からA行飛ばして、B行分表示
LIMIT 数値C --先頭行からC行表示

結果の和集合・差集合・積集合

使い方
・集合を取るときは、列名の数とデータ型が一致しなければならない。
 一致しない場合は、列名にNULLを入れる。
ORDER BYで並び替えるなら、最後に書く。
 列名は一番上のSELECT文の列名(DBMSによっては、列を左から数えた数値1,2,...で指定しなければならない。)
ALLを付けると、重複を削除しない。

--和集合 シンプルに両方をまとめる
SELECT 列名 FROM テーブル名 WHERE 条件A
UNION (ALL)
SELECT 列名 FROM テーブル名 WHERE 条件B;

--差集合 Cの表から、Dの表と一致する部分を除外する
SELECT 列名 FROM テーブル名 WHERE 条件C
EXCEPT (ALL)
SELECT 列名 FROM テーブル名 WHERE 条件D;

--積集合 EとFの共通する部分のみ
SELECT 列名 FROM テーブル名 WHERE 条件E
INTERSECT (ALL)
SELECT 列名 FROM テーブル名 WHERE 条件F;

計算

--列名(数値)に100を足して更新する
UPDATE テーブル名 SET 列名=列名 +100;

日付 +5; 5日あとの日付
日付 5; 5日まえの日付
日付 -日付; 日付の間の日数

--列名に「です。」を付けて更新する
UPDATE テーブル名 SET 列名=列名 || 'です。';

条件分岐

--書き方1 CASEからEND ASまでを一つの列としてSELECT表示
SELECT 列名,
 CASE 列名 WHEN A THEN C
           WHEN B THEN D
           ELSE E(デフォルト値)
 END AS 新規でつくる列の別名
FROM テーブル名;

--書き方2 一つの列ではなく、条件式で分岐して表示
SELECT 列名,
 CASE WHEN 条件式A THEN C
      WHEN 条件式B THEN D
      ELSE E(デフォルト値)
 END AS 新規でつくる列の別名
FROM テーブル名;

関数

--文字数
LENGTH(引数:列名でもよい) AS 新規列名

--空欄削除(CHAR型で余分な空欄を削除)
TRIM(引数:列名でもよい) AS 新規列名

--置換
REPLACE (引数:列名でもよい,'検索文字','置換する文字') AS 新規列名

--一部抽出
SUBSTRING (引数:列名でもよい,抽出を開始する位置,抽出する文字数) AS 新規列名
例: WHERE SUCSTRING(列名,1,3) LIKE '%A%'; 1~3文字目にAを含む

--連結
CONCAT (引数:列名でもよい,連結する文字列) AS 新規列名

--四捨五入(有効とする桁数は、小数点指定なら正の値、整数指定なら負の値)
ROUND (引数:列名でもよい,有効とする桁数) AS 新規列名
例: ROUND(列名,-3); 100の位で四捨五入

--切り捨て(有効とする桁数は、小数点指定なら正の値、整数指定なら負の値)
TRUNC (引数:列名でもよい,有効とする桁数) AS 新規列名
例: TRUNC(列名,-2); 10の位で切り捨て

--現在の日時
CURRENT_TIMESTAMP AS 新規列名; 日時
CURRENT_DATE AS 新規列名; 日付
CURRENT_TIME AS 新規列名; 時刻

--型の変換
CAST (引数:列名でもよい AS 新しい型) AS 新規列名

--NULLを代替(明示)して表示する
COALESCE (引数:列名や式) AS 新規列名
例: SELECT 日付,費目,
      COALESCE(メモ,'(メモはNULL)') AS メモ,
      入金額,出金額,
  FROM テーブル名

集計関数

--NULLは無視される。全行NULLならNULLとなる。
SUM(列名) AS 列名の合計
AVG(列名) AS 列名の平均
MAX(列名) AS 列名のうち、最大値(文字列は文字コード、日付は最終日)
MIN(列名) AS 列名のうち、最小値(文字列は文字コード、日付は最初の日)

--NULLを0として計算したい場合(COALESCEを使う)
SELECT AVG(COALESCE(列名,0)) AS 列名の平均
FROM テーブル名

--NULLは無視される。全行NULLなら0となる。
COUNT(列名) AS 列名の行数

--NULLは無視されない。
COUNT(*) AS 全行数

SELECT文の基本構文

処理順は、WHERE ➡ GROUP BY ➡ 集計関数 ➡ HAVING ➡ ORDER BY

※注意 🔲は実行される順番
 --したがって、選択列リストの列は、2⃣ 又は 「選択列リストの集計関数列」と同一でなければならない。
SELECT 選択列リスト(集計関数を書ける)--3⃣ 
FROM テーブル名
WHERE 条件式       --1⃣
GROUP BY グループ化列名  --2⃣
HAVING 集計関数結果に対する条件式 --4⃣ ←ここで指定する列は、1⃣のSELECT選択列の列名とする(ASより前の列名)
ORDER BY 並び替え列名   --5⃣
--意訳
SELECT リストを作りたい列名,作ったリストの知りたい集計関数 --3⃣ 
FROM テーブル名
WHERE 行に対する絞り込み条件       --1⃣
GROUP BY リストを作りたい列名「~ごとに」の意味  --2⃣
HAVING リストに対する絞り込み条件 --4⃣ ←ここで指定する列は、1⃣のSELECT選択列の列名とする(ASより前の列名)
ORDER BY 並び替え列名   --5⃣

サブクエリ

--INでテーブル2のリスト(NULL無し)をもとにテーブル1を抽出
AllANYも同様
SELECT * FROM テーブル名1
WHERE 列名 IN (SELECT DISTINCT 列名 FROM テーブル名2) 

--INでテーブル2のリスト(NULLあり)をもとにテーブル1を抽出
SELECT * FROM テーブル名
WHERE 列名 IN (SELECT COALESCE(列名,'NULL代替語') FROM テーブル名2)

--サブクエリから得たテーブルに別名を付ける
SELECT SUM(SUB.列名) AS テーブル名1
FROM (SELECT 列名
      FROM テーブル名2
      UNION
      SELECT 列名
      FROM テーブル名3
      WHERE 条件式) AS SUB;
      
--テーブル2の集計をINSERTして追加する文
--INSERT文のみ、サブクエリの( )は不要
INSERT INTO 集計テーブル名1(列名リスト)
SELECT 列名
FROM テーブル名2
WHERE 条件式
GROUP BY 集計関数のグループ単位

テーブルの結合(JOIN):

--基本形
SELECT 選択列リスト
FROM テーブルA
JOIN テーブルB
ON 両テーブルの結合条件

注意)
.右表に該当する行が複数あるとき
左表に対して、右表の該当が複数行あるとき、左表を複製して(増やし て)結合する。
.右表に結合相手がいないとき(左表の結合条件の列がNULLのとき含む)
結合結果から消える。
.ON句の複数条件
ONにはWHERE句のように、ANDでさらなるフィルター条件(特定の列が一致、不一致など)を書くことができる。
.複数テーブル(A,B,C)から同一テーブル(X)を参照と結合する場合
JOINONは、Xを3回して、それぞれにASで別名を付ける必要がある。(ANDは使えない。)

--副問い合わせの結果と結合するSQL
SELECT 選択列リスト
FROM テーブルA
JOIN (SELECT * FROM テーブルB
       WHERE 条件式) AS テーブルB 
ON 結合条件

--自分自身と結合するSQL(テーブルA内で、列が別の列と結びついているような場合)
--例えば日付と関連日、子と親など
SELECT A.選択列,B.選択列
FROM テーブルA AS A
LEFT JOIN テーブルA AS B 
ON A.選択列 = B.選択列 の結合条件

**注意4の例**
--OK
SELECT S1.社員番号 AS 社員番号, S1.NM AS NM,
       K1.NM AS 本人勤務地, K2.NM AS 上司勤務地
  FROM 社員 AS S1
  JOIN 社員 AS S2
    ON S1.上司ID = S2.社員番号
   AND S1.勤務地ID <> S2.勤務地ID
  JOIN 支店 AS K1         ←← 1回目
    ON S1.勤務地ID = K1.支店ID   ←← 1回目
  JOIN 支店 AS K2         ←← 2回目
    ON S2.勤務地ID = K2.支店ID;  ←← 2回目

--NG
SELECT S1.社員番号 AS 社員番号, S1.NM AS NM,
       K1.NM AS 本人勤務地, K1.NM AS 上司勤務地
  FROM 社員 AS S1
  JOIN 社員 AS S2
    ON S1.上司ID = S2.社員番号
   AND S1.勤務地ID <> S2.勤務地ID
  JOIN 支店 AS K1
    ON S1.勤務地ID = K1.支店ID  ←←
    AND S2.勤務地ID = K1.支店ID; ←← 

トランザクション

--開始
BEGIN;

--完了
COMMIT;

--BEGINまでキャンセル(ただし,自動コミット機能があると,戻せなくなる)
ROLLBACK;

それぞれの分離レベルで発生する問題 ○:発生する ×:発生しない

分離レベル ダーティリード ファジーリード ファントムリード
READ UNCOMMITTED
READ COMMITTED ×
REPEATABLE READ × ×
SERIALIZABLE × × ×

分離レベルを設定

SET TRANSACTION ISOLATION LEVEL 上記の分離レベル
SET CURRENT ISOLATION 上記の分離レベル

ロックはトランザクション内で使う。

--明示的な行ロック
SELECT ... FOR UPDATE NOWAIT
通常は,トランザクション解除まで待つが,NOWAITをつけることで,ロックできなければ即座にエラーとなる。

--テーブルロック
LOCK TABLE テーブル名 IN モード名 MODE NOWAIT;
モード名はEXCLUSIVE(排他ロック),SHARE(共有ロック)

デッドロックを予防する方法

1.トランザクションの時間を短くする

2.行やテーブルを常に同じ順番でロックする

テーブル操作(DDL)

--テーブル作成
CREATE TABLE テーブル名(
	列名0 型 制約,
	列名1 INTEGER PRIMARY KEY,
	列名2 DATE NOT NULL,
	列名3 VARCHAR(10) DEFAULT '不明' NOT NULL,
	列名4 INTEGER DEFAULT 0 CHECK(列名4>=0),
	列名5 VARCHAR(10) UNIQUE,
	列名6 REFERENCES 外部テーブル名(外部列名),
	PRIMARY KEY(列名1,列名3) --複合主キーの場合
)

--テーブル列の追加
ALTER TABLE テーブル名 ADD 列名 
--テーブル列の追加
ALTER TABLE テーブル名 DROP 列名 

--テーブルの存在を確認してから、操作する
CREATE TABLE IF NOT EXISTS テーブル名(
...)

テーブル権限操作(DCL)

--権限付与
GRANT 権限名 TO ユーザー名

--権限削除
REVOKE 権限名 TO ユーザー名

インデックス

(where句に使用する列で作成することで検索が速くなる。ただし、データ容量を食う。複数行インデックスも可能)

--インデックス作成
CREATE INDEX インデックス名 ON テーブル名(列名)

--インデックス削除
DROP INDEX インデックス名

高速化の効果検証コマンド

EXPLAIN SELECT * FROM テーブル名 WHERE 条件;

ビュー

テーブルから、使用する列・行だけを持った仮想テーブルを別途作成しておける。閲覧権限の設定などに有用。
元のテーブルデータが更新されると、自動で、ビューのデータも更新される。

--ビュー作成(WHEREなどによる条件も追加可能)
CREATE VIEW ビュー名 AS SELECT

--ビュー削除
DROP VIEW ビュー名

テーブルの自動採番機能

ただし、シーケンスは一部のDBMSのみ。主に、IDに使う。
最近はUUIDを用いるケースも多い。(なお、UUIDは連番ではない。)

INSERT INTO テーブル名(列名)
VALUES (SELECT NEXTVAL('列名シーケンス'))

テーブル設計

概念設計

概念上のエンティティをRDBで使いやすい形のテーブルER図を作成する。

  • エンティティ
    テーブルのようなもの
  • 属性
    テーブルの列のようなもの
  • 関連
    テーブル間の関係

論理設計(正規化)

  • 概念テーブルを複数のテーブルに分けて、矛盾しないようにする。
  1. 第1正規形
    テーブルのすべての行のすべての列に一つずつ値が入るようにする。(エクセルの碁盤の目に沿うように)
  2. 第2正規形
    複合主キー全体へ関数従属させる(複合主キーが一つ決まれば、一つのデータに確定するようにする。複合主キーの一部で決まるものは分ける。)(部分関数従属しないように。)
  3. 第3正規形
    主キーのみで、一意に決まるように分ける。主キー以外で一意になるものは分ける。(推移関数従属しないように。)

物理設計

  • 主キーや制約やデフォルトなどを設定し、DDLでデータベースを作成する。
0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?