11.SQL - YukaKoshiba/MyknowledgeDocs GitHub Wiki

SQL @Japanese Version
Create Date：2025/3/31
Last Update Date：2025/4/23

SQL

SQL(Structured Query Language,構造化照会言語)は、データベース専用に設計されたプログラミング言語
関係データベースにおける標準的な操作言語で、現在ではほとんどのRDBMSがSQLを採用している
主にリレーショナルデータベース管理システム(RDBMS)で使用されるが、
近年ではNoSQLデータベースなど、他の種類のデータベースでも使用されることがある

SQLでできることは、データベースにおける基本的な4つの操作であるCRUDに基づいている
また、関係データベースにおける数関係データベースの理論的な基礎を担っている関係代数を実際に表現し、DBまたはデータを操作するための言語がSQLとも言える

SQL Keywords Reference

用語

テーブル：DBの表のこと
マスタ：テーブルの内、滅多に変わることが無い基本情報が格納されたテーブル(顧客情報,企業情報など)
トランザクション：テーブルの内、システム運用上、日々の記録が格納されたテーブル(取引情報,問合せ情報など)
リテラル：固定文字列
シノニム：DBオブジェクト(テーブル,ビュー,シーケンスなど)に対する別名
エイリアス：SQLクエリ内でテーブルやカラムに一時的な別名、または機能

コメント文


-- Comment with only one sentence


/*
Comment with multiple sentences
*/

お作法

名称やインデントなど、詳細はSQLスタイルガイド参照のこと

SQLキーワード(SELECT,FROMなど)は、大文字/小文字の区別はされないことが多い
ただし、DBによって異なったり、設定で変更出来る
可読性向上の為に、SQLキーワードは大文字、カラム名は小文字で書く
パフォーマンス向上の為に、必要な列のみデータ取得の対象とし、
WHERE句などを駆使して絞り込み、読み込みデータ数を減らす
サブクエリ(副問合せ)を使用する場合は、可能であればJOIN句に書き換えるなど、最適化を検討する
DBによって、固有のルールがある為、使用するDBによって使い分ける
文末に;(セミコロン)を区切り文字で使用する
多くのDBMSでは必須ではないため、;がなくても実行できる
しかし、複数のクエリ(SQL文)を実行する際は、必ず;で区切る必要がある

エイリアス

SQLクエリ内でテーブルやカラムに一時的な別名を付ける機能
ASを使用して、エイリアスを付与する
CTE(Common Table Expression,共通テーブル式)やサブクエリ(副問合せ),JOINの際など、
わかりやすい名称へ変更したり、名称が短くなることで、可読性が向上する


SELECT AVG(clumn1) AS AVERAGE FROM table


SELECT column1, column1, ...
FROM table1 AS T1
JOIN table2  AS T2 ON T1.join-key1 = T2.join-key2;


WITH temp-table AS (
  SELECT column1, column2, ...
  FROM table1
  WHERE ....
)

コマンド実行順序

SQLクエリの論理的な実行順序
実際のデータベースエンジンは、クエリの最適化のために、異なる順序で処理を実行する場合がある
特に、データベースの"SQLオプティマイザ"と呼ばれる機能が、クエリの実行計画を分析し、最も効率的な実行順序を決定する

FROM / JOIN
WHERE
GROUP BY
集約関数(COUNT,SUM,AVG,MAX.MIN)
HAVING
SELECT
DISTINCT
ORDER BY
LIMIT / OFFSET

データベースシステムによっては、EXPLAINコマンドを使用することで、実際の実行計画を確認できる
これは、クエリのパフォーマンスを分析し、改善するために役立つ

集約関数の結果に基づいてグループをフィルタリングする際は、WHERE句ではなく、HAVING句を使用すること

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

DBからデータの取得や追加、更新、削除など、DBのデータを操作するための言語

SELECT/FROM/DISTINCT

行の取得を行う

SELECT FROM 表明データを取得したい列名…

*をしていすると、全ての列が選択されるが、レスポンスが低下するため、必要な列のみ選択する

DISTINCT句を利用すると、結果セットから重複する行を排除できる
DISTINCTは、SELECT句中で使うことができ、SELECTの後ろに記述する
※DISTINCTは、DML(データ操作言語)ではなく、DQL(データ問い合わせ言語)に分類される

WHERE

GROUP BY

レコードの並び替絵を行う
ASC：昇順(デフォルト)　DESC：降順並び変えしたい列名が複数ある場合は、対象の列の後ろに昇順/降順を設定する


SELECT CITY,LENGTH(CITY) AS LEN
FROM STATION
ORDER BY LEN DESC, CITY ASC

GROUP BYを利用する場合、SELECTで指定できる列は、以下のいずれかという制限がある
・集約関数を使用する列
・GROUP BY句に指定された列

HAVING

WHEREと同じ様に抽出結果の絞り込みを行う
コマンド実行順序がWHEREはGROUP BYの前であるため、グループ化前のデータの抽出が出来るのに対し、
HAVINGはGROUP BY後であるため、グループ化した後のデータの抽出条件の指定が出来る

ORDER BY

レコードの並び替え
並び変えしたい列名が複数ある場合は、対象の列の後ろに昇順/降順を設定する
ASC:昇順(デフォルト) DESC:降順
GROUP BY句で指定した場合、SELECT句で指定する出来るのは、GROUP BYした列 or 集計関数 or 定数式のみ


SELECT CITY,LENGTH(CITY) AS LEN FROM STATION ORDER BY LEN DESC, CITY ASC

LIMIT

大量のデータの中から、指定の行数だけ抽出する


-- 5行だけ読み込む
SELECT column
FROM table
LIMIT 5;

INSERT

行の挿入を行う


INSERT INTO テーブル名 (列名1, 列名2, 列名3) SELECT (列名1, 列名2, 列名3) FROM TableName;
INSERT INTO table_name (column1, column2, column3) VALUES ('abc', 10, 'XX'), ('def', 11, 'YY');

UPDATE

行の値を更新する


UPDATE テーブル名 SET 列名 = 値 WHERE (条件文);
UPDATE table_name SET column1 = 'abc' WHERE (column2 = 10);

DELETE

行の値を削除する


DELETE FROM テーブル名 WHERE (条件式);
DELETE FROM table_name WHERE column1 = 'abc';

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

DBの表の作成や変更など、DBの構造を定義する言語

CREATE

表の作成

CREATE TABLE table_name
(data-type column name1, data-type column name2, data-type column name3);

CREATE TABLE rental_dvd
(int id, name varchar, int price, date date);

ALTER

表の更新(列の追加/削除/変更、主キー/外部キー、属性変更など)


-- 表の名前を変更する
ALTER TABLE old_table_name RENAME new_table_name


-- 列の制約を追加
ALTER TABLE テーブル名 ADD CONSTRAINT 制約名を指定 制約の種類 (列名);


-- 列の制約を削除
ALTER TABLE テーブル名 DROP CONSTRAINT 制約名;


-- 列の追加
ALTER TABLE テーブル名 ADD 列名 データ型 制約;


-- 列の削除
ALTER TABLE テーブル名 DROP COLUMN 列名;


-- 列のデータ型の変更
ALTER TABLE テーブル名 MODIFY COLUMN 列名 データ型;


-- 列名の変更
ALTER TABLE テーブル名 RENAME COLUMN 古い列名 TO 新しい列名;

[列名] [データ型/定義名] [DEFAULT句] [列制約定義]

列の制約の種類：`CREATE`/`ALTER`で使用する
種類	説明	定義の仕方
UNIQUE	設定された列が他行がもつ値との重複を認めない制約 NULLを許容(1列のみ)する	UNIQUE (column1, column2)
PRIMARY KEY	設定された列が他行がもつ値との重複を認めない制約 NULLを許容しない主キーとしてインデックスを自動作成する特徴：1.一意性(UNIQUE) 2.NOT NULL 3.永続性(不変)	`PRIMARY KEY(column)`
FOREIGN KEY	外部キー FK担っている値は削除できない参照される表にあるものしか参照できない NULLでも可	FOREIGN KEY (column) REFERENCES external table(referenced_column)
CHECK	列に格納される値が特定の条件を満たすかを検証	CHECK (column condition) CHECK (num BETWEEN 1 AND 5)
DEFAULT	列の値が指定されなかった際、自動的にデフォルト値を挿入する `UPDATE`句で特定の行にのみ適用は不可	CHAR(4) DEFAULT '1234' NOT NULL

DROP

データベース内のオブジェクト(テーブル、ビュー、インデックスなど)を完全に削除するために使用される
DROPは非常に強力なコマンドで、一度実行すると削除されたオブジェクトを復元することは困難 DROP文を実行する前に、削除するオブジェクトをよく確認し、バックアップを取っておくことを強く推奨

DROP OBJECT_TYPE OBJECT_NAME;

OBJECT_TYPE：削除するオブジェクトの種類(TABLE,VIEW,INDEX,DATABASEなど)

TRUNCATE TABLE table_name;
TRUNCATE

テーブルの構造を保持したまま、データを初期化できる

テーブル内のすべてのデータを高速に削除する

条件を指定して特定のデータを削除するDELETEよりも高速に処理できる

ロールバックできない場合があるため、使用の際は注意が必要

※テーブルの構造(スキーマ)は保持

※自動インクリメントカウンタはリセットされる

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

アクセス権の設定やトランザクション処理など、DBを制御する言語
GRANT

ユーザ権限の付与

表の特定の利用者に対し、表への問合せ、更新/追加/削除などの操作権限を付与する
権限の種類：

SELEC(データの閲覧), INSERT(データの挿入), UPDATE(データの更新), DELETE(データの削除),

REFERENCES(外部キー制約の参照), EXECUTE(プロシージャを実行できる権限), ALL(上記すべての権限)

-- テーブルのアクセス権を付与する
GTANT SELECT, INSERT, UPDATE ON Table1 TO user1

-- 特定の列だけを指定する
GTANT SELECT(列1, 列2) ON Table1 TO user1

REVOKE

データベースオブジェクトに対するユーザーやロールの権限を取り消すために使用する

REVOKE 権限リスト
ON オブジェクトの種類 オブジェクト名
FROM ユーザーまたはロールリスト;

権限リスト: 
取り消す権限の種類(SELECT, INSERT, UPDATE, DELETEなど)をカンマ区切りで指定

ALL PRIVILEGESを指定すると、すべての権限を取り消します。


TCL(Transaction Control Language:トランザクション制御言語)

レースコンディション解消

BEGIN

トランザクションの開始
BEGIN TRANSACTION;
COMMIT

表の所有者や許可された人だけ処理できる
COMMIT;/code>
ROLLBACK

ROLLBACK;

演算子

算術演算子


  
    演算子
    説明
    演算子
    説明
  
  
    +
    加算
    -
    減算
  
  
    *
    乗算
    /
    除算
  
  
    %
    剰余
    
    
  

比較演算子


  
    演算子
    説明
    例文
  
  
    < または >

        <= または >=
    小なり/大なり

        以上/以下

        日付に用いることも出来る

        X < Y (XよりYが未来)

        NULL値は判別できない

        →IS NULL以外全てFALSE
    
    
  
  
    =
    等号
    
X = Y -- XとYが1対1で等しいかどうかを比較

  
  
    <>または=!
    不等号
    
  

論理演算子

SQLは基本的に記号ではなく、文字で論理演算子を書く

ただし、一部のDBMS(例えばMySQL)では、&&(AND), ||(OR), !(NOT)などの記号も使用できるが、標準的なSQLではない
上から処理の優先順度が高い順

  
    演算子
    説明
    例文
  
  
    IS NULL
IS NOT NULL
    指定された列がNULL値かどうかを判定する

        指定された列がNULL値ではないかどうかを判定する
    
SELECT *　FROM table_name　WHERE column_name(s) IS NULL;

        SELECT *　FROM table_name　WHERE column_name(s) IS NOT NULL;

  
  
    NOT
    否定
    
  
  
    AND
    論理積
    
  
  
    OR
    論理和
    
  
  
    XOR
    排他的論理和

        2つの条件のうち、どちらか一方が真の場合のみ真を返す

        ※一部のDBMSではサポートされていない場合がある
    
  

その他の演算子


  
    種類
    処理
  
  
    BETWEEN
NOT BETWEEN
    範囲指定
範囲外の指定
    WHERE num BETWEEN 0 AND 10;
  
  
    IN
NOT IN
    値のリストまたはサブクエリの結果と

        値の完全一致/不一致を比較
    
SELECT column_name(s)
FROM table_name
WHERE 列名 IN (value1, value2, ...);

  
  
    EXISTS
NOT EXISTS
    サブクエリの結果の存在確認

        サブクエリ以外に使用できない

        比較する値が事前にわからないかったり、

        比較する値の数が多い際に有効
    
SELECT column_name(s)
FROM table_name
WHERE EXISTS
  (SELECT * FROM subquery);

  
  
    ALL
    サブクエリの全ての値が指定された条件を満たす場合に真(TRUE)を返す
    
SELECT column
FROM table_name
WHERE column relational operator ALL (subquery);

  
  
    ANY/SOME
    サブクエリの値が指定された条件をいずれか満たす場合に真(TRUE)を返す
    
SELECT column
FROM table_name
WHERE column relational operator ANY (subquery);

  
  
    PIVOT
    行を列に変換し、データを集計する
クロス集計表を作成する

        SQL Server,Oracle Databaseでサポートされている
    

  

集合演算子

複数の結果セット(クエリの結果)を組合せて、単一の結果セットを生成するために使用されるSQLの演算子

数学的な集合演算の概念に基づいており、データベース内のデータを操作する強力なツール
JOIN

複数のテーブルを関連カラムに基づいて横方向に結合し、1つの結果セットとして取得する

UNION句やINTERSECT句のように、列数および順番が一致している必要はなく、非常に柔軟な結合が可能

  
    種類
    処理
  
  
    INNER JOIN/JOIN
    2つのテーブルで共通のカラムの値が一致する行のみを結合する

        JOINだけ記載すると、INNER JOINを省略形として扱われる
  
  
    LEFT JOIN
    左側のテーブルの全ての行と、右側のテーブルで共通のカラムの値が一致する行を結合する
  
  
    RIGHT JOIN
    右側のテーブルの全ての行と、左側のテーブルで共通のカラムの値が一致する行を結合する
  
  
    FULL OUTER JOIN
    2つのテーブルのすべての行を結合する

        共通のカラムの値が一致しない場合は、NULL値が含まれる
  
  
    CROSS JOIN
    直積(全ての組み合わせ)演算子、デカルト積,クロス結合とも呼ばれる

        2つのテーブルのすべての行の組合せを生成する結合操作を行う
  

結合条件の指定

結合条件の指定方法は、ON句を使用する方法と、USING句を使用する方法がある
ON句：

結合するテーブル間で結合条件を柔軟に指定できる

結合するカラム名が異なる場合や、複数の結合条件を指定する場合に使用する
USING句：

結合するテーブル間で共通のカラム名を1つのみ結合条件として指定できる

ON句のような柔軟性は無いが、簡潔に記述できる


SELECT * FROM Table1 JOIN Table2 ON Table1.columnA = Table1.columnB;


SELECT * FROM Table1 JOIN Table2 USING (CommonColumnName);

テーブルの左右

JOIN句を使って2つのテーブルを指定した場合、LEFT JOINやRIGHT JOINなど、JOINの種類に寄らず、以下の通りになる

SELECT column1, column2, column3
FROM Table1 -- the table on the left
JOIN Table2 -- the table on the right
ON/USING ....;

UNION

和集合演算子

複数のSELECT文の結果セットを縦方向に結合し、1つの結果セットとして取得する

NULL値を許容する

結合する表は、列数および順番が一致している必要がある

  
    種類
    処理
  
  
    UNION
    重複する行を削除して結果を結合する
  
  
    UNION ALL:
    重複する行を残して結果を結合する
  


(SELECT CITY,LENGTH(CITY) AS LEN FROM STATION ORDER BY LEN ASC, CITY LIMIT 1)
UNION
(SELECT CITY,LENGTH(CITY) AS LEN FROM STATION ORDER BY LEN DESC, CITY LIMIT 1)

INTERSECT

複数のSELECT文の結果セットに共通する行を抽出する

結果セット内の重複する行を削除する

結果セットの順序は保証されない

結合する表は、列数および順番が一致している必要がある
EXCEPT/MINUS

差集合演算子

最初のSELECT文の結果セットから、2番目のSELECT文の結果セットに含まれる行を除外する

結果セット内の重複する行を削除する

結果セットの順序は保証されない

INNER JOINとINTERSECTの違い：

INNER JOIN

・DISTINCTを使わない限り、同じ値も複数扱い

・結合するテーブルの列数と順番が異なっていても構わない

INTERSECT

・重複は排除される

・結合するテーブルの列数と順番が一致している必要がある
OUTER JOINとUNIONの違いも、
INNER JOINとINTERSECTの違いと同様でさる
特殊な値

NULL


  
    値
    意味
  
  
    NULL
    値そのものが存在しない

        IS NULLおよびIS NOT NULL以外で、 
        TRUEが返却されることは無い
  
  
    NaN

        (Not a Number)
    数値として表現できない値

        主に浮動小数点数の演算結果として現れる

        (例)0で割った場合や負の数の平方根を計算した場合など

        DBMSによってはサポートされていない場合がある
  
  
    Infinity

        (無限大)
    数値の最大値を超える値

        主に浮動小数点数の演算結果として現れる

        (例)非常に大きい数を0で割った場合など

        DBMSによってはサポートされていない場合がある
  
  
    ''(空文字)
    長さが0の文字列

        NULLとは異なり、値が存在する状態
  
  
    0
    数字の0

        NULLとは異なり、値が存在する状態
  

ワイルドカード

LIKE演算子とともに使用され、文字列のパターンマッチングを行うための特殊文字

  
    ワイルドカード
    用途
    例文
  
  
    %
    0文字以上の任意の文字列
    
'abc%'

        'abc'で始まるすべての文字列
  
  
    _
    任意の1文字
    
'a_c'

        'abc', 'adc'など3文字の文字列
  


関数

算術関数

複個々のデータに対する演算を行う関数

  
    関数
    説明
    例文
  
  
    ABS()
    絶対値を算出
    ABS(-5) -> 5
  
  
    FLOOR()
    レコード数を算出
    FLOOR(5.9) -> 5
  
  
    ROUND()
    指定桁数で四捨五入する
    ROUND(5.45, 1) -> 5.5
  
  
    CEIL()
    引数以上の最小の整数を返す
    CEIL(5.1) -> 6
  
  
    MOD()
    除算の剰余
    MOD(10, 3) -> 1
  
  
    POWER()
    数値のべき乗を返す
    POWER(2, 3) -> 8
  
  
    SQRT()
    数平方根を返す
    SQRT(16) -> 4
  
  
    LOG()
    自然対数を返す
    LOG(10, 100) -> 2
  

集約関数

複数のデータに対する集計する関数

集約関数の使用にあたっていくつか注意点がある

  使用箇所の制限

      WHERE句内、GROUP BY句内で、直接集約関数を使用できない

      SELECT句内またはGROUP BY句内でしか使用できない

      集約関数が使えるのは、SELECT句内、HAVING句内、ORDER BY句内
  WHERE句の制限/HAVINGの利用

      処理の優先順の都合で、集約関数を使用した際、WHERE句の絞り込みは適用されない

      ＝WHERE句は行のフィルタリングを行い、集約関数の結果(集計値)に基づいて行を絞り込むことはできない

      集約関数の結果でフィルタリングを行いたい場合は、HAVING句を使用する必要がある
  
  NULL値の扱い

      集約関数は、一般的にNULL値を無視して集計を行う為、指定のカラム名必ずを選択すること

      COUNT(*)：NULL値を含むすべての行数をカウントする

      COUNT(column_name)：NULL値を含まない行数をカウントする

      ※SUM(), AVG(), MAX(), MIN()などの算術系の関数は、NULL値を無視する
  


  
    関数
    説明
    例文
  
  
    AVG
    合計値を算出
    
  
  
    COUNT
    レコード数を算出
    
  
  
    MIN
MAX
    最大値の算出
最小値の算出
    
  

文字列関数


  
    関数
    説明
    例文
  
  
    LIKE
    パターンマッチング

        ワイルドカードによる曖昧検索ができる

        []による範囲指定も可
    
WHERE name = LIKE('%a%')

        LIKE '[abcd]%'

  
  
    LOWER
UPPER
    小文字にする
大文字にする
    
  
  
    CONCAT()
    文字列を連結
    
CONCAT('Hello', ' ', 'World')

        -> 'Hello World' 
  
  
    SUBSTRING()
    文字列の一部を抽出し
    
SUBSTRING('HelloWorld', 6, 5)

        -> 'World'
  
  
    LENGTH()
    文字列の長さを取得
    
LENGTH('HelloWorld')

       -> 10
  
  
    TRIM()
    文字列の先頭と末尾の
空白文字を削除
    
TRIM(' HelloWorld ')

       -> 'HelloWorld'
  
  
    REPLACE()
    文字列の置換
    
REPLACE('HelloWorld', 'World', 'SQL')

       -> 'HelloSQL'
  
  
    INSTR()
    指定の文字列が最初に出現
する位置を取得する
    
INSTR('HelloWorld', 'World')

       -> 6
  

WINDOW関数

SQLの強力な分析機能であり、データセット内の特定の"ウィンドウ"(行の集合)に対して計算を行う関数

従来の集約関数とは異なり、行をグループ化せずに、行ごとにことなる計算を行うことができる
2000年代初頭から存在する関数ではあるが、近年のデータ分析の重要性が高まるにつれて、WINDOW関数の利用も増加している
特徴：

・データ分析に特化：データの傾向、順位、移動平均などを分析するために設計されており、従来の集約関数(SUM,AVGなど)と異なり、各行に対して詳細な分析結果を提供できる

・ウィンドウの定義：OVER句を使用して、計算対象となるウィンドウを定義し、パーティション(グループ)や順序を指定する柔軟な定義が可能
通常の集約関数もOVER句を指定してウィンドウを定義することで、WINDOW関数として使用できる

また、その他に、以下のような分析専用のWINDOW関数の独自の関数も存在する

  
    関数名
    処理
  
  
    ROW_NUMBER()
    行に連続した番号を割り当てる
  
  
    RANK()
DENSE_RANK()
    行にランクを割り当てる

        DENSE_無：同順位後に番号が飛ぶ

        DENSE_有：同順位後に番号が飛ばない
  
  
    PERCENT_RANK()
    パーティションまたはクエリ結果セットの値の相対ランクを計算
  
  
    NTILE()
    行をN個のグループに分割し、
各グループに番号を割当てる
  
  
    FIRST_VALUE()
    ウィンドウの最初の行の値を返す
  
  
    FIRST_VALUE()
LAST_VALUE()
    ウィンドウの最初/最後の行の値を返す
  
  
    LAG()
LAG()
    現在の行から指定されたオフセット前/後の行の値を返す
  
  
    CUME_DIST()
    パーティションまたはクエリ結果セットの値の累積分布を計算
  

書き方は、関数名() OVER (PARTITION BY column1 ORDER BY column2)

PARTITION BY：column1の値に基づいて行をグループに分割する ※省略可能

ORDER BY：column2に基づいてソートする ※省略可能

SELECT
    product,
    sales,
    RANK() OVER (ORDER BY sales DESC) AS sales_rank
FROM
    sales_table;

NULL関連の関数


  
    関数名
    処理
    例文
  
  
    COALESCE(A，B, ...)
    引数リストの中でNULLでない最初の値を返す

        引数の数が2つ以上の場合に利用可能です
    SELECT COALESCE(column1, column2, 'default value') FROM table_name;
  
  
    NVL(A，B)
    AがNULLでないときはAを，AがNULLのときはBを返す

        Oracleデータベースのみ利用でき、引数は2つ
    SELECT NVL(column1, 'alternative value') FROM table_name;
  
  
    NVL2(A，B, C)
    AがNULLでないときはBを，AがNULLのときはCを返す

        Oracleデータベースのみ利用でき、引数は3つ
    SELECT NVL2(column1, 'NOT NULL', 'NULL') FROM table_name;
  

条件分岐

CASE

他のプログラミング言語のSWITCH文のような使い方が出来る

SELECT
    product_name,
    price,
    CASE
        WHEN price >= 100 THEN 'high'
        WHEN price >= 50 THEN 'middle'
        ELSE 'low'
    END AS prce_range
FROM
    products;


変数

SQLにおける変数は、一時的なデータ値を格納するための名前付きの記憶領域で、

変数は、SQL文の中で値を保持し、操作するために使用される

条件分岐やループ処理など、動的なSQL文を生成したい時に役立つ
SQLにおける変数は、主に以下の2つの場面で使用される：

・ローカル変数：

　ストアドプロシージャや関数内で一時的に使用される変数

　特定の処理ブロック内でのみ有効

・ストアドプロシージャのパラメータ(引数)

　呼び出し元から値を受け渡すために使用される
変数の使用方法は、DBMSにより異なる
ローカル変数

MySQL

SETステートメントと、変数の先頭に@を使用する

SET @my_variable = 10;
SELECT @my_variable;

SQL Server (Transact-SQL)

DECLAREステートメントで宣言する

SETステートメントまたはSELECTステートメントを使用して変数を初期化する

変数の先頭に@を使用する

DECLARE @my_variable INT;
SET @my_variable = 10;
SELECT @my_variable;

PostgreSQL

DECLAREステートメントで宣言する

:=演算子または、DEFAULT句を使用して変数を初期化する

DO $$
DECLARE
    my_variable INT := 10;
BEGIN
    RAISE NOTICE '%', my_variable;
END $$;

Oracle (PL/SQL)

DECLAREセクションで変数を宣言し、:=演算子を使用して変数を初期化する

DECLARE
    my_variable NUMBER := 10;
BEGIN
    DBMS_OUTPUT.PUT_LINE(my_variable);
END;
/

ストアドプロシージャにおける変数宣言

MySQL

DECLAREステートメントを使用して変数を宣言する

CREATE PROCEDURE my_procedure()
BEGIN
    DECLARE my_variable INT DEFAULT 10;
    -- Processing using variables
END;

SQL Server (Transact-SQL)

DECLAREステートメントを使用して変数を宣言する

CREATE PROCEDURE my_procedure
AS
BEGIN
    DECLARE @my_variable INT = 10;
    -- Processing using variables
END;

PostgreSQL (PL/pgSQL)

DECLAREセクションで宣言する

CREATE OR REPLACE PROCEDURE my_procedure()
LANGUAGE plpgsql
AS $$
DECLARE
    my_variable INT := 10;
BEGIN
    -- Processing using variables
END;
$$;

Oracle (PL/SQL)

DECLAREセクションで変数を宣言する

CREATE OR REPLACE PROCEDURE my_procedure
AS
    my_variable NUMBER := 10;
BEGIN
    -- Processing using variables
END;
/

繰り返し処理

REPEAT/REPLICATE/LPAD

指定された文字列を指定された回数だけ繰り返した文字列を返す関数で、

DBMSによって、使える関数や動作が若干異なる
基本構文は、繰り返し関数(文字列, 繰り返し回数);で記述する
MySQL

繰り返し回数が0以下の場合、空文字列を返す

SELECT REPEAT('abc', 3); -- 'abcabcabc'

SQL Server (Transact-SQL)


REPLICATE(文字列, 繰り返し回数);
SELECT REPLICATE('abc', 3); --　'abcabcabc'

PostgreSQL


SELECT REPEAT('abc', 3); -- 'abcabcabc'

Oracle (PL/SQL)


SELECT LPAD('abc', 9, 'abc'); -- 'abcabcabc'

ループ処理

変数とREPEAT()などの繰り返し処置を行う関数を組み合わせると、

他のプログラミン言語のfor文の様な処理を実施することが出来る
MySQLの例

-- Variable Declaration
SET @row := 0;
-- Repeat data get by SELECT while @row < 21
SELECT REPEAT('* ', @row := @row + 1) AS PATTERN
FROM information_schema.tables
WHERE @row < 21;

このクエリを実行すると、以下のような結果が得らる

*
* *
* * *
(omission)
* * * * * * * * * * * * * * * * * * 
* * * * * * * * * * * * * * * * * * * 
* * * * * * * * * * * * * * * * * * * *

information_schema.tablesはMySQLにおける仮想テーブル

information_schema：
DBのメタデータ(データに関するデータ)を提供する仮想的なDB

データベース、テーブル、カラム、インデックスなど、データベースオブジェクトに関する情報が格納されている

information_schema内のテーブルやビューは、実際のデータファイルとしてディスクに保存されているわけではなく、MySQLサーバーが起動時に動的に生成し、メモリ上に保持しているもの

→ユーザーはinformation_schemaに対してクエリを実行して情報を取得できるが、データの挿入/更新/削除などの操作はできない

.tablesはテーブル情報の取得をシており、その他にも様々な選択出来る情報がある
information_schemaで選択できる情報は、DBMSによって異なる

例えば、Oracleでは「データディクショナリビュー」という名称で同様の機能が提供されている
一時テーブルの作成(メモリ上)

CREATE TABLEで作成される物理的なテーブルと違い、メモリ上に一時的に作成されるテーブル
SQLクエリ内で一時的な名前付き結果セットを定義する機能

SQLクエリをより効率的かつ可読性の高いものにする強力なツール
WITH句

共通テーブル式(CTE:Common Table Expression)

SQLクエリ内で一時的な名前付き結果セットを定義する機能

SQLクエリをより効率的かつ可読性の高いものにする強力なツール

副問合せと違って、後から使いまわしが出来る

WITH NewTableName AS (
    -- Write Query
    SELECT ...
)
SELECT column FROM NewTableName;

1度のクエリで、複数の共通テーブルを作成したい場合、SQLの標準的な構文では、

WITH句はクエリの先頭に一度だけ記述する

カンマ区切りで複数のCTE(Common Table Expression)を定義する

WITH T1 AS (
    -- Write Query
    SELECT ...
),
T2 AS (
    -- Write Query
    SELECT ...
)
SELECT T1.column
FROM T1
JOIN T2 ON T1.key = T2.key


副問合せ(サブクエリ)

SELECTステートメント内で別のSELECTステートメントを使用する方法

WHERE句、FROM句、SELECT句、HAVING句など、SQLクエリのさまざまな部分で使用できる

主にデータのフィルタリング、集計、または他のクエリで使用する値の取得に使用される

WITH句と違って、1度しか使用できない

-- サブクエリ (WHERE句で使用)
SELECT *
FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders WHERE order_total > 100);

派生テーブル

派生テーブルは、FROM句で使用される特定のタイプのサブクエリ

エイリアスが必要

-- 派生テーブル (FROM句で使用)
SELECT avg(order_total)
FROM (SELECT order_total FROM orders WHERE order_date >= '2023-01-01') AS recent_orders;

相関的副問合せ

サブクエリ）の一種で、外側のクエリ(親クエリ)の行に依存して実行されるものを指す

外部クエリが処理する行ごとに、内部の副問い合わせが繰り返し評価される

副問い合わせが外部クエリの各行に対して異なる結果を返す必要がある場合に特に役立つ一方で、

外部クエリの行ごとに副問い合わせが実行されるため、データ量が多い場合にはパフォーマンスが低下する可能性がある

-- Example
SELECT employee_name
FROM employees e1
WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE e1.department_id = e2.department_id);

一時テーブルの作成(物理上)

CREATE TABLEで作成されるテーブルと同様に、物理的にデータベース内に保存される
ビュー

SQLの機能の1つで、RDBにおいて使用される概念

仮想的なテーブルとしてデータベース内に定義され、永続的に存在する

実際のデータは格納されず、定義されたクエリに基づいて動的に結果が生成される

データベースのメタデータとして保存され、ディスク上に物理的なテーブルとしては存在しない
用途：

・複雑なクエリを簡略化し、頻繁に使用されるデータセットを仮想的なテーブルとして提供

・セキュリティ上の理由から、特定の列や行のみをユーザーに公開する

・データの整合性を保ち、アプリケーションの変更に対する影響を軽減する

・ビュー作成の元の表(基底表)とは別の名称を付けることが出来る

・SQL文の繰り返しを避けられる

→Webアプリケーションにおいて、サーバー側とのやり取りの減少や簡潔なSQL文になり、レスポンスの向上に繋がる


CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

ビューの権限は、基となるテーブルの権限と密接に関連しており、一般的に基となるテーブルの権限を継承する
ビュー作成時のSQL文の構造によって、ビューの更新可否が異なる

  
    ビューの種類
    SQL文の構造
    
  
  
    更新可能なビュー
    (更新不可能なビューで示した構造を持たないもの)
    基底表(元の表)に対して行を挿入/更新/削除が可能
  
  
    更新不可能なビュー
    1.集約関数(AVG、COUNT、SUM、MIN、MAXなど)

        2.2つ以上の表の結合(更新可能な結合，和集合及び列を除く)

        3.GROUP BY,ORDER BY,MODEL,CONNECT BY,START WITH,DISTINCTのいずれかを利用している

        4.SELECT構文のリストにコレクション式

        5.SELECT構文のリストにあるサブクエリ(副問合せ)

        6.WITH READ ONLYが指定された副問合せ
    基底表(元の表)に対して行を挿入/更新/削除ができない

        集計関数や結合を含む複雑なビューは、基テーブルのデータと1:1で対応しない場合がある

        このようなビューを更新すると、どの基テーブルのデータを変更すべきかが曖昧になり、

        データの整合性が損なわれる可能性がある
  

一時テーブル

一時的にデータベース内に作成されるテーブルであり、セッションまたはトランザクションの終了時に自動的に削除される

CREATE TEMPORARY TABLEステートメントで作成する

CREATE TEMPORARY TABLE temp_table AS
SELECT column1, column2
FROM table1
WHERE condition;
SELECT *
FROM temp_table
WHERE column1 = 'value';

処理時間を早める

INDEX

DBへのアクセス効率を向上させる為に、DBのメモリ内に独自のインデックスを作ることができる

インデックスを作成すると、特定のクエリに対して、インデックス作成前よりも高速にデータ操作を行うことができるようになる

CREATE INDEX indeName ON tableName (columnName);

上記のクエリにより、インデックスを作成すると、表面上は何も変わらないが、

データ構造がB木へ変化しており、高速にデータ操作を行うことができるようになる
埋め込みSQL(Embedded SQL)

データベース操作を行うSQL文を、CやJavaなどのプログラミング言語のソースコードに直接記述する技術

これにより、プログラムの実行中にデータベースと連携した柔軟なデータ処理が可能になる
埋め込みSQLには、静的SQLと動的SQLという2つの主要なタイプがある：

  
    種類
    説明
  
  
    静的SQL
    プログラムのコンパイル時にSQL文が確定しているもの

        ＝SQL文がプログラムのソースコードに固定的に記述されているもの

        SQL文が固定されているため、柔軟性に欠けるが、

        実行時のオーバーヘッドが少なく、高速に処理できる
  
  
    動的SQL
    プログラムの実行時にSQL文を組み立てて実行するもの

        ＝SQL文がプログラムの実行中に動的に生成される

        ユーザーの入力や実行時の状況に応じて、SQL文を動的に変更し、

        実行時にSQL文を組み立てるため、柔軟なデータ処理が可能だが、

        SQL文の組み立てや実行計画の作成に時間がかかるため、

        静的SQLよりも処理が遅くなることがある

        また、SQLインジェクション攻撃に対する対策が重要になる
  

埋め込みSQLの機能

データベースから複数の行を効率的に処理するために、埋め込みSQLにはカーソルとフェッチという機能がある
カーソル：

・データベースの問い合わせ結果(複数の行からなるデータセット)を、プログラム内で一つずつ順番に処理するための仕組み

・データベースから取得した結果セット全体を一度にメモリに読み込むのではなく、カーソルを使って必要な行だけを順番に読み込むことで、メモリの使用量を抑え、効率的な処理を実現する

・カーソルは、データベースの問い合わせ結果を指し示すポインタのようなもの

※全てのDBサポートされているわけではない
フェッチ：

・カーソルを使って、現在カーソルが指し示している行のデータをプログラムの変数に取り出す操作

・フェッチを繰り返すことで、カーソルは結果セットの次の行へと移動し、全ての行を順番に処理できる
実際カーソル処理をコードに示す
ホスト変数：

埋め込みSQLにおいて、プログラムの変数とデータベースのデータをやり取りするために使用される変数のこと

SQL文の中で、プログラムの変数の値を参照したり、SQL文の実行結果をプログラムの変数に格納したりするために使用される

特徴(通常の変数との違い)

・SQL文との連携できる

・宣言文ではなく、宣言セクションで宣言する

・通常の変数とは異なるスコープとライフサイクルを持つ場合がある

・通常の変数としてもアクセスでき、SELECT文の条件として使用可能
非カーソル処理

＝カーソルを用いて1行ずつ読み込むのではなく、一度に全てを読み込む

EXEC SQL SELECT 列名(取得したいデータ) INTO :ホスト変数名(データを格納する)


// The example of C
#include 

// ホスト変数の宣言セクション
EXEC SQL BEGIN DECLARE SECTION;
    int emp_id;
    char emp_name[50];
    char department[50];
EXEC SQL END DECLARE SECTION;

int main() {
    emp_id = 123; // 従業員IDを指定

    EXEC SQL SELECT emp_name, department INTO :emp_name, :department FROM employees WHERE emp_id = :emp_id;

    printf("従業員名: %s, 部署: %s\\n", emp_name, department);

    return 0;
}

カーソル処理

＝カーソルを用いて1行ずつ読み込む処理

SELECT文の結果が複数行になる可能性がある場合に使用する

// The example of C
#include <stdio.h>
// ホスト変数の宣言セクション
EXEC SQL BEGIN DECLARE SECTION;
    char emp_name[50];
    char department[50];
EXEC SQL END DECLARE SECTION;

int main() {
    // カーソルの宣言
    EXEC SQL DECLARE emp_cursor CURSOR FOR SELECT emp_name, department FROM employees;
    // カーソルを開く
    EXEC SQL OPEN emp_cursor;
    // フェッチして結果を表示(＝1行ずつホスト変数からデータを取り出す)
    EXEC SQL FETCH emp_cursor INTO :emp_name, :department;
    while (SQLCODE == 0) { // SQLCODEが0の間はループ
        printf("従業員名: %s, 部署: %s\\n", emp_name, department);
        EXEC SQL FETCH emp_cursor INTO :emp_name, :department; // 次の行をフェッチ
    }
    // カーソルを閉じる
    EXEC SQL CLOSE emp_cursor;
    return 0;
}

演算子	説明	例文
< または > <= または >=	小なり/大なり以上/以下日付に用いることも出来る X < Y (XよりYが未来) NULL値は判別できない →IS NULL以外全てFALSE
=	等号	`X = Y -- XとYが1対1で等しいかどうかを比較`
<>または=!	不等号

演算子	説明	例文
IS NULL IS NOT NULL	指定された列がNULL値かどうかを判定する指定された列がNULL値ではないかどうかを判定する	`SELECT *　FROM table_name　WHERE column_name(s) IS NULL;` `SELECT *　FROM table_name　WHERE column_name(s) IS NOT NULL;`
NOT	否定
AND	論理積
OR	論理和
XOR	排他的論理和 2つの条件のうち、どちらか一方が真の場合のみ真を返す ※一部のDBMSではサポートされていない場合がある

種類	処理
BETWEEN NOT BETWEEN	範囲指定範囲外の指定	`WHERE num BETWEEN 0 AND 10;`
IN NOT IN	値のリストまたはサブクエリの結果と値の完全一致/不一致を比較	`SELECT column_name(s) FROM table_name WHERE 列名 IN (value1, value2, ...);`
EXISTS NOT EXISTS	サブクエリの結果の存在確認サブクエリ以外に使用できない比較する値が事前にわからないかったり、比較する値の数が多い際に有効	`SELECT column_name(s) FROM table_name WHERE EXISTS (SELECT * FROM subquery);`
ALL	サブクエリの全ての値が指定された条件を満たす場合に真(TRUE)を返す	`SELECT column FROM table_name WHERE column relational operator ALL (subquery);`
ANY/SOME	サブクエリの値が指定された条件をいずれか満たす場合に真(TRUE)を返す	`SELECT column FROM table_name WHERE column relational operator ANY (subquery);`
PIVOT	行を列に変換し、データを集計するクロス集計表を作成する SQL Server,Oracle Databaseでサポートされている

種類	処理
INNER JOIN/JOIN	2つのテーブルで共通のカラムの値が一致する行のみを結合する JOINだけ記載すると、INNER JOINを省略形として扱われる
LEFT JOIN	左側のテーブルの全ての行と、右側のテーブルで共通のカラムの値が一致する行を結合する
RIGHT JOIN	右側のテーブルの全ての行と、左側のテーブルで共通のカラムの値が一致する行を結合する
FULL OUTER JOIN	2つのテーブルのすべての行を結合する共通のカラムの値が一致しない場合は、NULL値が含まれる
CROSS JOIN	直積(全ての組み合わせ)演算子、デカルト積,クロス結合とも呼ばれる 2つのテーブルのすべての行の組合せを生成する結合操作を行う

種類	処理
UNION	重複する行を削除して結果を結合する
UNION ALL:	重複する行を残して結果を結合する

値	意味
NULL	値そのものが存在しない `IS NULL`および`IS NOT NULL`以外で、 `TRUE`が返却されることは無い
NaN (Not a Number)	数値として表現できない値主に浮動小数点数の演算結果として現れる (例)0で割った場合や負の数の平方根を計算した場合など DBMSによってはサポートされていない場合がある
Infinity (無限大)	数値の最大値を超える値主に浮動小数点数の演算結果として現れる (例)非常に大きい数を0で割った場合など DBMSによってはサポートされていない場合がある
''(空文字)	長さが0の文字列 NULLとは異なり、値が存在する状態
0	数字の0 NULLとは異なり、値が存在する状態

ワイルドカード	用途	例文
%	0文字以上の任意の文字列	`'abc%'` 'abc'で始まるすべての文字列
_	任意の1文字	`'a_c'` 'abc', 'adc'など3文字の文字列

関数	説明	例文
ABS()	絶対値を算出	ABS(-5) -> 5
FLOOR()	レコード数を算出	FLOOR(5.9) -> 5
ROUND()	指定桁数で四捨五入する	ROUND(5.45, 1) -> 5.5
CEIL()	引数以上の最小の整数を返す	CEIL(5.1) -> 6
MOD()	除算の剰余	MOD(10, 3) -> 1
POWER()	数値のべき乗を返す	POWER(2, 3) -> 8
SQRT()	数平方根を返す	SQRT(16) -> 4
LOG()	自然対数を返す	LOG(10, 100) -> 2

関数	説明	例文
AVG	合計値を算出
COUNT	レコード数を算出
MIN MAX	最大値の算出最小値の算出

関数	説明	例文
LIKE	パターンマッチングワイルドカードによる曖昧検索ができる `[]`による範囲指定も可	`WHERE name = LIKE('%a%')` `LIKE '[abcd]%'`
LOWER UPPER	小文字にする大文字にする
CONCAT()	文字列を連結	`CONCAT('Hello', ' ', 'World')` -> 'Hello World'
SUBSTRING()	文字列の一部を抽出し	`SUBSTRING('HelloWorld', 6, 5)` -> 'World'
LENGTH()	文字列の長さを取得	`LENGTH('HelloWorld')` -> 10
TRIM()	文字列の先頭と末尾の空白文字を削除	`TRIM(' HelloWorld ')` -> 'HelloWorld'
REPLACE()	文字列の置換	`REPLACE('HelloWorld', 'World', 'SQL')` -> 'HelloSQL'
INSTR()	指定の文字列が最初に出現する位置を取得する	`INSTR('HelloWorld', 'World')` -> 6

関数名	処理
ROW_NUMBER()	行に連続した番号を割り当てる
RANK() DENSE_RANK()	行にランクを割り当てる DENSE_無：同順位後に番号が飛ぶ DENSE_有：同順位後に番号が飛ばない
PERCENT_RANK()	パーティションまたはクエリ結果セットの値の相対ランクを計算
NTILE()	行をN個のグループに分割し、各グループに番号を割当てる
FIRST_VALUE()	ウィンドウの最初の行の値を返す
FIRST_VALUE() LAST_VALUE()	ウィンドウの最初/最後の行の値を返す
LAG() LAG()	現在の行から指定されたオフセット前/後の行の値を返す
CUME_DIST()	パーティションまたはクエリ結果セットの値の累積分布を計算

関数名	処理	例文
COALESCE(A，B, ...)	引数リストの中でNULLでない最初の値を返す引数の数が2つ以上の場合に利用可能です	`SELECT COALESCE(column1, column2, 'default value') FROM table_name;`
NVL(A，B)	AがNULLでないときはAを，AがNULLのときはBを返す Oracleデータベースのみ利用でき、引数は2つ	`SELECT NVL(column1, 'alternative value') FROM table_name;`
NVL2(A，B, C)	AがNULLでないときはBを，AがNULLのときはCを返す Oracleデータベースのみ利用でき、引数は3つ	`SELECT NVL2(column1, 'NOT NULL', 'NULL') FROM table_name;`

ビューの種類	SQL文の構造
更新可能なビュー	(更新不可能なビューで示した構造を持たないもの)	基底表(元の表)に対して行を挿入/更新/削除が可能
更新不可能なビュー	1.集約関数(AVG、COUNT、SUM、MIN、MAXなど) 2.2つ以上の表の結合(更新可能な結合，和集合及び列を除く) 3.GROUP BY,ORDER BY,MODEL,CONNECT BY,START WITH,DISTINCTのいずれかを利用している 4.SELECT構文のリストにコレクション式 5.SELECT構文のリストにあるサブクエリ(副問合せ) 6.WITH READ ONLYが指定された副問合せ	基底表(元の表)に対して行を挿入/更新/削除ができない集計関数や結合を含む複雑なビューは、基テーブルのデータと1:1で対応しない場合があるこのようなビューを更新すると、どの基テーブルのデータを変更すべきかが曖昧になり、データの整合性が損なわれる可能性がある

種類	説明
静的SQL	プログラムのコンパイル時にSQL文が確定しているもの＝SQL文がプログラムのソースコードに固定的に記述されているもの SQL文が固定されているため、柔軟性に欠けるが、実行時のオーバーヘッドが少なく、高速に処理できる
動的SQL	プログラムの実行時にSQL文を組み立てて実行するもの＝SQL文がプログラムの実行中に動的に生成されるユーザーの入力や実行時の状況に応じて、SQL文を動的に変更し、実行時にSQL文を組み立てるため、柔軟なデータ処理が可能だが、 SQL文の組み立てや実行計画の作成に時間がかかるため、静的SQLよりも処理が遅くなることがあるまた、SQLインジェクション攻撃に対する対策が重要になる

11.SQL - YukaKoshiba/MyknowledgeDocs GitHub Wiki

目次

SQL

用語

コメント文

お作法

エイリアス

コマンド実行順序

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

SELECT/FROM/DISTINCT

WHERE

GROUP BY

HAVING

ORDER BY

LIMIT

INSERT

UPDATE

DELETE

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

CREATE

ALTER

DROP

TRUNCATE

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

GRANT

REVOKE

TCL(Transaction Control Language:トランザクション制御言語)

レースコンディション解消

BEGIN

COMMIT

ROLLBACK

演算子

算術演算子

比較演算子

論理演算子

その他の演算子

集合演算子

JOIN

UNION

INTERSECT

EXCEPT/MINUS

特殊な値

NULL

ワイルドカード

関数

算術関数

集約関数

文字列関数

WINDOW関数

NULL関連の関数

条件分岐

CASE

変数

ローカル変数

MySQL

SQL Server (Transact-SQL)

PostgreSQL

Oracle (PL/SQL)

ストアドプロシージャにおける変数宣言

MySQL

SQL Server (Transact-SQL)

PostgreSQL (PL/pgSQL)

Oracle (PL/SQL)

繰り返し処理

REPEAT/REPLICATE/LPAD

MySQL

SQL Server (Transact-SQL)

PostgreSQL

Oracle (PL/SQL)

ループ処理

一時テーブルの作成(メモリ上)

WITH句

副問合せ(サブクエリ)

派生テーブル

相関的副問合せ

一時テーブルの作成(物理上)

ビュー

一時テーブル

処理時間を早める

INDEX

⚠️ GitHub.com Fallback ⚠️