DML＠SQL¶

はじめに¶

本サイトにつきまして、以下をご認識のほど宜しくお願いいたします。

https://hiroki-it.github.io/tech-notebook/

01. DMLとは¶

テーブル上のレコードを操作するクエリのこと。

https://morizyun.github.io/database/sql-ddl-dml-dcl.html#DML-Data-Manipulation-Language

02. SELECT¶

はじめに¶

▼ 句の処理の順番¶

FROM ---> JOIN ---> WHERE ---> GROUP BY ---> HAVING ---> SELECT ---> ORDER BY

`SELECT`句¶

▼ なし¶

指定したカラムを取得する。

MySQLでは、取得結果の並び順が毎回異なるため、プライマリーキーの昇順で取得したい場合は、ORDER BY句を使用して、明示的に並び替えるようにする。

https://www.quora.com/What-is-the-default-order-of-records-for-a-SELECT-statement-in-MySQL

SELECT * FROM <テーブル名>;

▼ `SUM`関数¶

指定したカラムで、『フィールド』の合計を取得する。

SELECT SUM(<カラム名>) FROM <テーブル名>;

▼ `AVG`関数¶

指定したカラムで、『フィールド』の平均値を取得する。

SELECT AVG(<カラム名>) FROM <テーブル名>;

▼ `MIN`関数¶

指定したカラムで、『フィールド』の最小値を取得する。

SELECT MIN(<カラム名>) FROM <テーブル名>;

▼ `MAX`関数¶

指定したカラムで、『フィールド』の最大値を取得する。

SELECT MAX(<カラム名>) FROM <テーブル名>;

▼ `COUNT`関数¶

指定したカラムで、『フィールド』の個数を取得する。

SELECT <カラム名> COUNT(*) FROM <テーブル名>;

※消去法の小技：集合関数を入れ子状にはできない

＊実装例＊

集合関数を集合関数の中に入れ子状にできない。

SELECT AVG(SUM(<カラム名>)) FROM <テーブル名>;

指定したカラムで、値無しも含む『フィールド』を取得する。

SELECT COUNT(<カラム名>) FROM <テーブル名>;

指定したカラムで、値無しを除いた『フィールド』を取得する。

SELECT COUNT(<カラム名>);

▼ `LAST_INSERT_ID`関数¶

任意のテーブルに最後に挿入されたIDを読み出す。

テーブル名を指定する必要はない。

SELECT LAST_INSERT_ID();

▼ `MD5`関数¶

文字列をハッシュ化

SELECT MD5("foo");

`CASE`句¶

カラム1がtrueだったら、カラム2を取得する。

falseであったら、カラム3を取得する。

SELECT
    CASE
        WHEN <エイリアス>.<カラム名1> = 1 THEN <エイリアス>.<カラム名2>
        ELSE <エイリアス>.<カラム名3>
    END AS name
FROM
    <テーブル名> AS <エイリアス>;

`FROM`句¶

▼ `JOIN`句の種類¶

内部結合のベン図

▼ `LEFT JOIN` (左外部結合)¶

『users』テーブルと『items』テーブルの商品IDが一致しているデータと、元となる『users』テーブルにしか存在しないデータが、セットで取得される。

LEFT_JOIN

▼ `INNER JOIN` (内部結合)¶

基本情報技術者試験では、内部結合 (A∩B) しか出題されない。

▼ 内部結合に`WHERE`を使用する場合¶

2個のWHERE文が、ANDで結びつけられている時、まず1つ目のWHEREを満たすレコードを取得した後、取得したレコードの中から、2つ目のWHEREを満たすレコードを取得する。

＊実装例＊

-- 『カラム』のみでなく、どの『表』なの物なのかも指定
SELECT
    <テーブル名1>.<カラム名1>,
    -- 複数の表を指定
FROM
    <テーブル名1>,
    <テーブル名2>,
    -- まず、1つ目のフィールドと2つ目のフィールドが同じレコードを取得する。
WHERE
    -- 次に、上記で取得したレコードのうち、次の条件も満たすレコードのみを取得する。
    <レコード名1> = <レコード名2>
    AND <レコード名2> = <レコード名3>

▼ 内部結合に`INNER JOIN ON`を使用する場合¶

＊実装例＊

-- 『カラム』のみでなく、どの『表』なの物なのかも指定
SELECT
    <テーブル名1>.<カラム名1>,
    -- 複数の表を指定
FROM
    <テーブル名1>
    -- 2つ目の表の『レコード』と照合
    INNER JOIN <テーブル名2>
    ON <テーブル名1>.<カラム名1> = <テーブル名2>.<カラム名2>
    -- 3個目の表の『レコード』と照合
    INNER JOIN <テーブル名3>
    ON <テーブル名1>.<カラム名1> = <テーブル名3>.<カラム名3>

`ORDER BY`句¶

▼ 使い方¶

＊実装例＊

<?php
$joinedIdList = implode(",", $idList);

// 並び替え条件を設定
$expression = call_user_func(function () use ($orders, $joinedIdList) {
    if ($orders) {
        foreach ($orders as $key => $order) {
            switch ($key) {
                case "id":
                    return sprintf("ss.id %s", $order);
            }
        }
    }

    // IN句順の場合
    return sprintf("FIELD(ss.id, %s)", $idList);
});

$sql = <<<SQL
            SELECT
                name
            FROM
                table
            ORDER BY {$expression}
        SQL;

`IN`句、`ANY`句の違い¶

▼ `IN`句の使い方¶

指定した値と同じ『フィールド』を取得する。

＊実装例＊

指定したカラムで、指定した値の『フィールド』を取得する。

SELECT * FROM <テーブル名> WHERE <カラム名> in (foo, bar,...);

指定したカラムで、指定した値以外の『フィールド』を取得する。

SELECT * FROM <テーブル名> WHERE <カラム名> not in (<レコード名1>, <レコード名2>,...);

指定したカラムで、SELECTで読み出した値以外の『フィールド』を取得する。

SELECT * FROM <テーブル名> WHERE <カラム名> not in (SELECT <カラム名> FROM <テーブル名> WHERE <レコード名> >= 160);

【IN句を使用しなかった場合】

SELECT * FROM fruit WHERE name = "みかん" OR name = "りんご";

【IN句を使用した場合】

SELECT * FROM fruit WHERE name IN("みかん", "りんご");

▼ `ANY`句の使い方¶

書き方が異なるのみで、inと同じ出力

SELECT
    *
FROM
    <テーブル名>
WHERE
    <カラム名> = ANY(foo, bar, baz);

`GROUP BY`句¶

▼ 使い方¶

カラムをグループ化し、集合関数を使用して、フィールドの値を算出する。

＊実装例＊

指定したカラムをグループ化し、フィールドの値の平均値を算出する。

SELECT <カラム名1> AVG(<カラム名2>) FROM <テーブル名> GROUP BY <カラム名1>;

`HAVING`句¶

▼ 使い方¶

各句の処理の順番から考慮して、GROUP BYでグループ化した結果から、HAVINGで『フィールド』を取得する。

SELECTにおける集計関数が、HAVINGにおける集計関数の結果を指定していることに注意せよ。

＊実装例＊

-- HAVINGによる集計結果を指定して出力。
SELECT
    <カラム名1>,
    COUNT(<カラム名2>)
FROM
    <テーブル名>
GROUP BY
-- グループ化した結果を集計し、2個以上の『フィールド』を取得する。
    <カラム名1>
HAVING
    COUNT(*) >= 2;

以下の場合、GROUP BY + HAVINGやWHEREを使用しても、同じ出力結果になる。

SELECT <カラム名> FROM <テーブル名> GROUP BY <カラム名> HAVING <レコード名>;

SELECT <カラム名> FROM <テーブル名> WHERE <レコード名> GROUP BY <カラム名>;

`WILDCARD`句¶

▼ 使い方¶

＊実装例＊

SELECT * FROM <テーブル名> WHERE <カラム名> LIKE "%営業";

SELECT * FROM <テーブル名> WHERE <カラム名> LIKE "_営業";

`BETWEEN`句¶

▼ 使い方¶

＊実装例＊

指定したカラムで、1以上10以下の『フィールド』を取得する。

SELECT
    *
FROM
    <テーブル名>
    BETWEEN 1
    AND 10;

`SET`句¶

▼ 使い方¶

＊実装例＊

SET
    @A = <パラメーター値>;

SET
    @B = <パラメーター値>;

UPDATE
    <テーブル名>
SET
    <カラム名> = @A,
WHERE
    <カラム名> = @B;

サブクエリ¶

▼ 使い方¶

掛け算と同様に、括弧内から先に処理を実行する。

＊実装例＊

-- Main-query
SELECT
    *
FROM
    <テーブル名>
WHERE
    <カラム名> != (
        -- Sub-query
        SELECT
            max(<カラム名>)
        FROM
            <テーブル名>
    );

Tips¶

▼ 各DBサイズの確認¶

SELECT
    table_schema,
    sum(data_length) / 1024 / 1024 AS mb
FROM
    information_schema.tables
GROUP BY
    table_schema
ORDER BY
    sum(data_length + index_length) DESC;

▼ カラムの検索¶

SELECT table_name, column_name FROM information_schema.columns WHERE column_name = <検索したいカラム名> AND table_schema = <検索対象のDB名>

03. EXPLAIN¶

実行計画¶

設定したSELECT句が仮に実行された場合、いずれのテーブルのいずれのカラムを取得することになるか (実行計画) を取得する。

また、想定実行時間も検出できるため、スロークエリの検出に役立つ。

EXPLAIN SELECT
    *
FROM
    t1,
    t2
WHERE
    t1.c1 = 1
    AND t1.c2 = t2.c3

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: ref
possible_keys: index_t1_on_c1_and_c2
          key: index_t1_on_c1_and_c2
      key_len: 5
          ref: const
         rows: 10
        Extra: Using where; Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: t2
         type: ref
possible_keys: index_t2_on_c3
          key: index_t2_on_c3
      key_len: 5
          ref: sample.t1.c2
         rows: 1
        Extra: Using index

https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

読み方¶

▼ `select_type`¶

SQLの種類が表示される。

サブクエリを含まないSQLはSIMPLEとなり、サブクエリを含むと、サブクエリの種類に応じて、PRIMARY、SUBQUERY、DEPENDENT SUBQUERY、UNCACHEABLE SUBQUERY、DERIVED、のいずれかが表示される。

▼ `table`¶

設定したSELECT句がアクセスするテーブル名が表示される。

▼ `type`¶

設定したSELECT句がテーブルにアクセスする時に、どの程度の数のカラムを検索するのかが表示される。

検索するカラムが多いSQLほど、想定実行時間が長くなる。

種類	条件	検索するカラム数	補足
ALL	・DBインデックスを使用していない。	全てのカラム	全てのカラムを検索するため、実行時間が最も長く、改善する必要がある。
index	・DBインデックスを使用していない。	全てのインデックスのカラム
range	・セカンダリーDBインデックスを使用している。・`WHERE`句に重複したレコード値、`IN`句、`BETWEEN`句を使用している。	特定の複数カラム
ref	・セカンダリーDBインデックスを使用している。・`WHERE`句に重複しないレコード値	特定の複数カラム
eq_ref	・クラスタDBインデックスを使用している。	`1`個のカラム	`1`個のカラムしか`fetch`しないため、`JOIN`句を使用したアクセスの中で、実行時間が最も短い。
const	・クラスタDBインデックスを使用している。・`JOIN`句を使用していない。	`1`個のカラム	`1`個のカラムしか`fetch`しないため、実行時間が最も短い。

▼ `possible_keys`¶

DBインデックスとして設定されたカラムのうちで、実際に利用できるものの一覧が表示される。

05. INSERT¶

バルクインサート¶

一度のINSERT文で複数の値を挿入する。

INSERT INTO <テーブル名> VALUES ('<カラム名>','<レコード値>'), ('<カラム名>','<レコード値>'), ('<カラム名>','<レコード値>');

06. SHOW¶

DBを閲覧する。

主に動作確認使用できる。

mysql> SHOW TABLES;

+-------------------------------+
| Tables_in_<DB名>              |
+-------------------------------+

07. EXEC¶

stored procedure¶

▼ stored procedureとは¶

あらかじめ一連のSQL文をDBに格納しておき、Call文でコールする方式。

p325

https://www.amazon.co.jp/dp/4297124513

▼ 使い方¶

＊実装例＊

SELECT文のstored procedureを作成する例を考える。

-- PROCEDUREを作成し、DBへ格納しておく。
CREATE PROCEDURE SelectContact AS SELECT <カラム名> FROM <テーブル名>

-- PROCEDUREを実行
EXEC SelectContact

DML＠SQL¶

はじめに¶

01. DMLとは¶

02. SELECT¶

はじめに¶

▼ 句の処理の順番¶

SELECT句¶

▼ なし¶

▼ SUM関数¶

▼ AVG関数¶

▼ MIN関数¶

▼ MAX関数¶

▼ COUNT関数¶

▼ LAST_INSERT_ID関数¶

▼ MD5関数¶

CASE句¶

FROM句¶

▼ JOIN句の種類¶

▼ LEFT JOIN (左外部結合)¶

▼ INNER JOIN (内部結合)¶

▼ 内部結合にWHEREを使用する場合¶

▼ 内部結合にINNER JOIN ONを使用する場合¶

ORDER BY句¶

▼ 使い方¶

IN句、ANY句の違い¶

▼ IN句の使い方¶

▼ ANY句の使い方¶

GROUP BY句¶

▼ 使い方¶

HAVING句¶

▼ 使い方¶

WILDCARD句¶

▼ 使い方¶

BETWEEN句¶

▼ 使い方¶

SET句¶

▼ 使い方¶

サブクエリ¶

▼ 使い方¶

Tips¶

▼ 各DBサイズの確認¶

▼ カラムの検索¶

03. EXPLAIN¶

実行計画¶

読み方¶

▼ select_type¶

▼ table¶

▼ type¶

▼ possible_keys¶

05. INSERT¶

バルクインサート¶

06. SHOW¶

07. EXEC¶

stored procedure¶

▼ stored procedureとは¶

▼ 使い方¶

`SELECT`句¶

▼ `SUM`関数¶

▼ `AVG`関数¶

▼ `MIN`関数¶

▼ `MAX`関数¶

▼ `COUNT`関数¶

▼ `LAST_INSERT_ID`関数¶

▼ `MD5`関数¶

`CASE`句¶

`FROM`句¶

▼ `JOIN`句の種類¶

▼ `LEFT JOIN` (左外部結合)¶

▼ `INNER JOIN` (内部結合)¶

▼ 内部結合に`WHERE`を使用する場合¶

▼ 内部結合に`INNER JOIN ON`を使用する場合¶

`ORDER BY`句¶

`IN`句、`ANY`句の違い¶

▼ `IN`句の使い方¶

▼ `ANY`句の使い方¶

`GROUP BY`句¶

`HAVING`句¶

`WILDCARD`句¶

`BETWEEN`句¶

`SET`句¶

▼ `select_type`¶

▼ `table`¶

▼ `type`¶

▼ `possible_keys`¶