Skip to main content

DB SQL

Overview​

リレーショナルデータベースでのSQLについてまとめるセクション。

SQLとは​

SQL（Structured Query Language）とは、リレーショナルデータベースがデータ操作のために備えている言語。
リレーショナルデータベースには、後述するように様々なソフトウェア製品があるのですが、そのすべてにおいて共通のSQLを使うことができる。

SQL以外のコマンド​

管理コマンドはDBMSが正常に動作しているかを監視したり、DBMSが異常な動作をした問いに問題解決のため情報を収集したりといった用途に使われる。

DML・DCL・DDL​

SQL文はDBMSに与える命令の種類により、次の3つに分類される。

  1. DDL（Data Definition Language：データ定義言語）
  2. DML（Data Manipulation Language：データ操作言語）
  3. DCL（Data Control Language：データ制御言語）
tip

この3つが基本のSQL区分で、実際のデータベース操作ではDMLを中心に使いつつ、DDLでテーブル設計、DCLでセキュリティ管理をする

DDL(Data Definition Language：データ定義言語)​

データベースやテーブルの構造を定義・変更するためのクエリ。

  • CREATE（作成）
  • ALTER（変更）
  • DROP（削除）
  • TRUNCATE（全削除）

DML(Data Manipulation Language：データ操作言語)​

データの追加・更新・削除・取得をするためのクエリ。

  • INSERT（データ追加）
  • UPDATE（データ更新）
  • DELETE（データ削除）
  • SELECT（データ取得）

SELECT​

  • 取得した列の順番は明示的に設定しない限りは表作成時の順番
  • ORDER BY を指定しない場合は、ランダムに表示されるのがSQLの仕様

WHERE​

WHERE句はソートの前に実行される。
そのためソート処理する行数が減り、処理時間が短くなる。

テーブルを集約する関数​

SQLでデータに対して何らかの操作や計算をするには関数を使う。
関数には大きく分けて2種類ある。

  1. 複数の行or行の値に対して集計をする関数
  2. 単一行の値に対して操作や計算をする関数
集計関数​

集計用の関数を集計関数と呼ぶ。
代表的な5つを記載。

  • COUNT
    • テーブルの行数を数える関数
    • 利用できる箇所は以下のみ
      • SELECT
      • ORDER BY
      • HAVING
  • SUM
    • テーブルの数値列のデータを合計する関数
  • AVG
    • テーブルの数値列のデータを平均する関数
  • MAX
    • テーブルの任意列のデータの最大値を求める関数
  • MIN
    • テーブルの任意列のデータの最小値を求める関数
caution

集計関数は基本的に NULL を除外して集計する

GROUP BY​

データをグループに切り分ける。

tip

集計関数の利用対象は、チア商となるデータ全体を範囲として集約してきたが、対象となるデータをいくつかのグループに切り分けて集約することも可能。

HAVING​

グループごとに集約した値を条件にして選択したい場合に使用。

ORDER BY​

ORDER BY による並べ替えを行う場合に注意する点としては、行の順番を確実に同じにするには、行をソートキーで一意（Unique: ユニーク）に特定する必要がある。

caution

ソートキーが同じ値の行が複数ある場合には、その複数行への順番はランダムになる。
ランダムになる箇所をランダムにせず、一意にするには行が一意になるソートキーを指定する必要がある。

ORDER BYで起こること​

ORDER BY 使うと全部並べ替えようとする。
ORDER BY使わずにSELECTするのは速いけど、実際の業務では現実的ではないため使用しない。
そのためインデックス使うのが一般的
インデックスはソートされているようなものなので、インデックスがあればORDER BYはインデックスを元に行われる。

LEFT JOIN では処理の工数が相対的に上がる​

LEFT JOINする時、大抵はONでJOINの結合条件を設定する。
FROMに指定した テーブルA のレコードが10000件、JOINに指定したテーブルBのレコードが1000件あった場合、テーブルA のレコードの件数分、テーブルB の対象レコードを探す処理が必要になる。
これは、最悪の場合10_000_000レコード分の処理になる (10_000 * 1_000)

note

一般的にJOIN遅いって言われているのは、JOINは掛け合わせなので元になる情報が大きければ大きいほど、その結果が膨れ上がることになるため

DCL(Data Control Language：データ制御言語)​

データベースのアクセス権限を管理するクエリ。

  • GRANT（権限付与）
  • REVOKE（権限剥奪）

CTE（Common Table Expression、共通テーブル式）​

CTE（Common Table Expression、共通テーブル式）は、SQLで一時的な結果セットを名前付きで定義し、それをクエリ内で利用できる構文。
クエリを分かりやすく構造化するために使われ、特に複雑なクエリや再帰的なクエリで便利。

-- CTEの基本構文
WITH CTE名 (列名1, 列名2, ...) AS (
    サブクエリ -- 一時的なテーブルとして扱うクエリ
)
SELECT *
FROM CTE名;
-- 例1: シンプルなCTE
-- 以下は、売上が1000を超える注文を取得するCTEの例
WITH HighValueOrders AS (
    SELECT customer_id, order_total
    FROM orders
    WHERE order_total > 1000
)
SELECT customer_id
FROM HighValueOrders;

-- 1. HighValueOrdersは、一時的なテーブルのような役割を果たします。
-- 2. その後、メインクエリでHighValueOrdersを参照して必要なデータを取得します。
-- 例2: 再帰的CTE
-- 再帰CTEは、階層構造（例: 組織のツリー構造やカテゴリ）を処理する場合に使用されます。
WITH RECURSIVE EmployeeHierarchy AS (
    -- 基底部
    SELECT employee_id, manager_id, employee_name, 1 AS level
    FROM employees
    WHERE manager_id IS NULL -- トップマネージャー

    UNION ALL

    -- 再帰部
    SELECT e.employee_id, e.manager_id, e.employee_name, eh.level + 1
    FROM employees e
    INNER JOIN EmployeeHierarchy eh
    ON e.manager_id = eh.employee_id
)
SELECT *
FROM EmployeeHierarchy;
-- この例では、EmployeeHierarchyを使って階層構造を再帰的に処理しています。

CTEのメリット​

  1. クエリの可読性向上 • 複雑なクエリを分割して記述できるため、理解しやすくなる。
  2. 再利用性 • 一時的な結果セットを名前付きで保存し、クエリ内で再利用可能。
  3. 分割統治が可能 • 大きなクエリを分割することで、個別の部分を独立して理解しやすくなる。
  4. ネストの削減 • サブクエリを減らし、よりフラットな構造でクエリを記述可能。

CTEと副問い合わせの違い​

• クエリが複雑で、分割して記述したいとき。 • 同じサブクエリを複数回利用するとき。 • 再帰的な処理（ツリー構造や階層構造の探索）が必要なとき。

CTEはモダンなSQLエンジン（PostgreSQL, SQL Server, MySQL 8.0+, Oracleなど）でサポートされている。