[SQL]基本文法を個人的にまとめた。

SQLについて、理解を深めるためにまとめる。
随時更新していく。

参考文献
☆中山清喬/飯田理恵子『スッキリわかるSQL入門 第2版』

SQL

・データベースを扱うための言語
・SQLを用いてデータベースを分析する
・DBMS（データベース管理システム）がSQLの指示を受け、処理を行う（1行毎）

データベース

・データ（テキスト、数値など）を保存するためのツール
・テーブル（縦列：カラム、横列：レコード）で管理

データ型

データ（テキスト、数値、日付など）の種類

[代表的なもの]
INTEGER：数値（整数）
DECIMAL、REAL：数値（小数）
CHAR：文字列（固定長）　例）郵便番号や社員番号など
VARCHAR：文字列（可変長）　例）氏名や書籍名など
DATETIME、DATE、TIME：日時

クエリ

・データベースからデータを取得するために、データベースに送る命令
・SQLを用いて指示する（指示を予約語と言う）
・DML（SELECT、UPDATE、DELETE、INSERT）でほとんどの処理を賄える
・処理対象のテーブル名の指定はどの命令においても必要
・クエリの指示終わりには；（セミコロン）
・SQLは大文字・小文字の区別がないため、どちらでも構わない
・データ型のルールにより、テキストは””（ダブルクォーテーション）で囲む

❶SELECT文➡︎データの検索

--どのカラムのデータを取得するかを指定
--指定内容がそのまま出力される
SELECT 列名             --列名
       列名 + 値　       --計算式
       '文字列'          --固定値
SELECT カラム名①，カラム名②    //複数のカラムのデータを取得する場合は，（カンマ）で区切る
SELECT ＊　　　　　　         //全てのカラムのデータを取得する場合
SELECT カラム名 計算記号 数値  //四則演算が可能

FROM テーブル名 --FROM句
              --どのテーブルのカラムかを指定

AS + キーワード //別名の定義
SELECT 費目 AS item, 入金額 AS receive, 出金額 AS pay --別名に書き換え
  FROM 帳簿 AS books
 WHERE 費目 = '給料'

--全貌
SELECT 選択列リスト
  FROM テーブル名
(WHERE 条件式)
（GROUP BY グループ化列名）
（HAVING 集計結果に対する条件式）
（ORDER BY 並び替え列名）

WHERE句➡︎完全一致の条件のみを取得

WHERE レコード名        //どこのレコードを取得するかを指定
WHERE カラム名 = 〇〇　 //あるカラムが〇〇のレコードを指定
WHERE カラム名 <= 〇〇　//比較演算子を用いたレコード指定

DELETE
  FROM 帳簿
 WHERE 出金額 > 0 --条件式（真偽）しか指定できない

NULL：未定義（何も格納されていない）
　　　　0すら入っていない空欄の状態

<>：左右辺の値が等しくない

LIKE演算子➡︎文字列の一致を判定

WHERE カラム名 LIKE “〇〇”    //特定の条件を含むデータを取得
WHERE カラム名 LIKE “%〇〇%”  //どの文字列にも一致するデータを取得
WHERE カラム名 LIKE “〇〇%”   //前方一致       
WHERE カラム名 LIKE “%〇〇”　 //後方一致

SELECT * FROM 帳簿
 WHERE メモ LIKE '%11月%' --11月前後を含む

%（ワイルドカード）：任意の0文字以上の文字列
_（アンダースコア）：任意の1文字

NOT句

WHERE NOT カラム名 LIKE “〇〇”  //条件を含まないデータを取得

ESCAPE句➡︎文字として認識させる

SELECT * FROM 帳簿
 WHERE メモ LIKE '%111$%'
ESCAPE '$' --「＄」を文字として認識させる

BETWEEN演算子➡︎指定の範囲内に値があるかを判定

条件式 BETWEEN 値1 AND 値2

SELECT * FROM 帳簿
 WHERE 出金額 BETWEEN 111 AND 3333

値がちょうどの場合も「真」

IN演算子➡︎値が一致するか判定

条件式 IN (値1, 値2・・・)

SELECT * FROM 帳簿
 WHERE 費目 IN ('食費', '家賃')

複数値との比較が可能

NOT IN演算子➡︎値が一致しないか判定

条件式 NOT IN (値1, 値2・・・)

SELECT * FROM 帳簿
 WHERE 費目 NOT IN ('食費', '家賃')

ANY/ALL演算子➡︎値の大小比較をする

条件式 比較演算子 ANY (値1. 値2・・・) --いずれかが真
条件式 比較演算子 ALL (値1. 値2・・・) --すべて真

単体で利用するメリットはなく「式」や「副問い合わせ」と組み合わせて使う

IS NULL/IS NOT NULL句

WHERE カラム名 IS NULL 　　 //データが空のカラムを取得
WHERE カラム名 IS NOT NULL //データがあるカラムを取得

SELECT *
  FROM 帳簿
 WHERE 出金額 IS NULL

カラム名 = NULL
WHERE NOT カラム名 = NULL
どちらも不可

論理演算子

SELECT *
  FROM 買い物リスト
 WHERE 店名 = 'A'         --条件式1
    OR 店名 = 'B'         --条件式2
   AND ジャンル = 'おもちゃ'  --条件式3
    OR ジャンル = 'PC'      --条件式4

SELECT *
  FROM 買い物リスト
 WHERE (店名 = 'A'         --条件式1
    OR 店名 = 'B')         --条件式2
   AND (ジャンル = 'おもちゃ'  --条件式3
    OR ジャンル = 'PC')      --条件式4

優先度①NOT②AND③OR
条件式をかっこで囲むことで優先度を引き上げる

AND演算子➡︎条件式を組み合わせる

WHERE 条件① AND 条件② //複数の条件のデータを取得
                      --どちらも真

UPDATE 買い物リスト
   SET 金額 = 7777
 WHERE 品物 = '福袋'
   AND 店名 = 'Z'

OR演算子➡︎条件式を組み合わせる

WHERE 条件① OR 条件② //どちらかの条件のデータを取得
                     --いずれかが真

NOT演算子

NOT 条件式 --真偽が逆

WHERE NOT 店名 = 'Z' --Z以外の店名

検索結果の加工

ORDER BY句➡︎結果の並び替え

SELECT 列名 FROM テーブル名
 ORDER BY 並び替えるカラム名 並べ方  

SELECT * FROM 帳簿
 ORDER BY 日付 DESC 

SELECT * FROM 帳簿
 ORDER BY 入金額 DESC, 出金額 ASC --複数列

SELECT * FROM 帳簿
 ORDER BY 1 DESC, 2 ASC --列番号の指定

・並べ方はASC（昇順）もしくはDESC（降順）➡︎省略した場合はASC
・クエリの末尾に記述
・SELECT文内の列名を変更すると、並び替えの結果にも影響が出るため注意
・DBMSにとって負荷のかかる作業であることを認識

OFFSET-FETCH句➡︎先頭の数行のみを取得

SELECT 列名 FROM テーブル名
 ORDER BY 列名
OFFSET 除外する行数 ROWS
 FETCH NEXT 取得行数 ROWS ONLY

SELECT 費目. 出金額 FROM 帳簿
 ORDER BY 出金額 DESC
OFFSET 0 ROWS --1件目から取得
 FETCH NEXT 3 ROWS ONLY --3件目まで取得

SELECT 費目. 出金額 FROM 帳簿
 ORDER BY 出金額 DESC
OFFSET 2 ROWS --3件目から取得
 FETCH NEXT 1 ROWS ONLY --1件取得

・1件目から取得する場合は「0」
　

LIMIT句

LIMIT 取得する件数  //取得するデータを制限

・クエリの末尾に記述
・WHEREと組み合わせが可能

DISTINCT句➡︎重複行を除外

SELECT DISTINCT 列名
  FROM テーブル名

SELECT文の最初に記述しなければならない

集合演算子

データ同士の結合や共通部分の検索を可能にする仕組み
列リストの列数とデータ型が一致していなければならない

UNION演算子➡︎和集合

SELECT 文1
 UNION (ALL)　--UNIONは重複行を1つにまとめる
              --UNION ALLは重複行もそのまま返す
SELECT 文2

EXCEPT演算子➡︎差集合

SELECT 文1
EXCEPT (ALL)　--EXCEPTは重複行を1つにまとめる
              --EXCEPT ALLは重複行もそのまま返す
SELECT 文2

Oracle DBではMINUS句を使う
どの集合を基準にするかで結果が変わるため、注意

INTERCECT演算子➡︎積集合

SELECT 列名 FROM テーブル名
INTERCECT (ALL)　--INTERCECTは重複行を1つにまとめる
                 --INTERCECTALLは重複行もそのまま返す
SELECT 列名 FROM テーブル名

❷UPDATE文➡︎データの更新

UPDATE テーブル名
   SET 列名1 = 値1, 列名2 = 値2・・・

UPDATE 帳簿 
   SET 入金額 = 777   　　　  --SET句
 WHERE 日付 = '2020/11/06'  --WHERE句なしは全更新

UPDATE 帳簿
   SET 出金額 = 出金額 + 150

すでにテーブルに存在するデータを書き換える（1行毎）

❸DELETE文➡︎データの削除

DELETE
  FROM テーブル名

DELETE
  FROM 帳簿
 WHERE 日付 = '2020/11/06'  --WHERE句なしは全削除

すでにテーブルに存在するデータを削除する
特定行のみ削除することはできない（行毎削除する仕組みのため）

主キー➡︎行を特定させる役割を持つ

DELETE
  FROM 社員
 WHERE 社員番号 = '20201106' --主キー

必ず何かしらのデータが格納される（NULLではない）
他行と重複が起きない

❹INSERT文➡︎データの追加

INSERT INTO テーブル名
            （列名1, 列名2・・・） --列すべてに追加する場合は省略可
     VALUES (値1, 値2・・・)    --VALUES句

INSERT INTO 帳簿 (費目, 日付, 出金額)
     VALUES ('通信費', '2020/11/06', 7777)

INSERT INTO 帳簿
     VALUES ('2020/11/06', '通信費', '携帯電話', 0, 7777) --列指定省略の場合はテーブル列順にすべて記述

INSERT INTO 帳簿 (食費)
     VALUES (1000 + 100)

WHERE句は使えない

算術演算子

||：文字列同士の連結

CASE演算子➡︎値の変換

① CASE 列名や条件式 WHEN 値1 THEN 値1に対しての戻り値
                (WHEN 値2 THEN 値2に対しての戻り値）
                (ELSE デフォルト値)
   END

② CASE 列名や条件式 WHEN 条件1 THEN 条件1に対しての戻り値
                (WHEN 条件2 THEN 条件2に対しての戻り値）
                (ELSE デフォルト値)
   END

値や条件式の評価結果に応じて任意の複数値に変換ができる

関数

呼び出し

関数名 (引数)

各行毎に呼び出される
呼び出し完了後に戻り値に化ける
DBMSで仕様が大きく異なることに注意

LENGTH/LEN関数➡︎文字数の取得

SELECT メモ, LENGTH (メモ) AS メモの長さ
  FROM 帳簿

SQL ServerではLEN

TRIM/LTRIM/RTRIM関数➡︎空白の除去

SELECT メモ, TRIM (メモ)   --左右の空白除去 
            LTRIM (メモ)  --左側の空白除去
            RTRIM （メモ）  --右側の空白除去
    AS 空白除去メモ
FROM 帳簿

REPLACE関数➡︎文字の置換

REPLACE (指定の文字列, 置換前の部分文字列, 置換後の部分文字列)

SUBSTRING/SUBSTR関数➡︎部分抽出

SUBSTRING (文字列を表す列, 抽出開始位置, 抽出文字数)
SUBSTR (指定の文字列, 抽出開始位置, 抽出文字数)

DBMSによって異なる

CONCAT関数➡︎文字列の連結

 CONCAT （文字列1, 文字列2・・・）

ROUND関数➡︎指定桁で四捨五入

 ROUND (数値を表す列, 指定桁) --指定桁は正なら小数部、負なら整数部の桁数を表す

TRUNC関数➡︎指定桁で切り捨て

 TRUNC (数値を表す列, 指定桁)

POWER関数➡︎べき乗の計算

 POWER (数値を表す列, 指定値)

CURRENT_DATE/CURRENT_TIME関数➡︎現在日時の取得

引数がいらない

CAST変数➡︎データ型の変換

CAST (変換する値, AS 変換するデータ型)

COALESCE関数➡︎最初に登場するNULLではない値を返す

COALESCE (列や式1, 列や式2・・・)

・引数は任意指定（データ型はすべて一致していなければならない）
・すべての引数がNULLの場合、戻り値もNULL

集計関数➡︎検査結果のデータを集計

・集計対象すべての行をまとめて、1回の計算から答えを導き出す
　➡︎つまり結果は1行になる
・SELECT文のみ（選択列リストもしくはORDER BY句もしくはHAVING句内）
・WHERE句では使用ができない
　➡︎なぜならWHERE句の処理段階ではまだ集計が終了していないから

[使用可能な型]
SUM：数値型のみ
AVG：数値型のみ

MAX：数値型、文字列型（並び替えた最後の文字列）、日付型（最新の日付）すべて
MIN：数値型、文字列型（並び替えた最初の文字列）、日付型（最古の日付）すべて
COUNT：数値型、文字列型、日付型すべて

[NULLの扱い]
❶集計時
　無視➡︎SUM、MAX、MIN、AVG、COUNT（列名指定）
　数える➡︎COUNT（*指定）

❷行すべてがNULL
　NULL➡︎SUM、MAX、MIN、AVG
　0➡︎COUNT（列名指定）
　該当行数➡︎COUNT（*指定）

--NULLを0扱いで平均値を求める
SELECT AVG(COALESCE(出金額, 0)) AS 平均出金額
  FROM 帳簿

SUM/MAX/MIN/AVG関数➡︎合計値/最大値/最小値/平均値を求める

SELECT
  SUM(出金額) AS 合計出金額,
  SUM(出金額 * 1.1) AS 合計出金額  --式の指定も可能

  MAX(出金額) AS 最大出金額,
  MIN(出金額) AS 最小出金額,
  AVG(出金額) AS 平均出金額
 FROM 帳簿

COUNT関数➡︎行数を求める

SELECT
  COUNT(*) AS 食費の行数　　　 --NULLを含む
  COUNT(出金額) AS 食費の行数  --指定列の行数（NULLを含まない）

  FROM 帳簿
 WHERE 費目 = '食費'

--SUM、AVG、COUNTはDISTINCTによる集計が可能
SELECT
  SUM(DISTINCT 費目),
  AVG(DISTINCT 費目),
  COUNT(DISTINCT 費目)
 FROM 帳簿

グループ化➡︎指定基準ごとに集計結果をまとめる

SELECT グループ化の基準列名, 集計関数
  FROM テーブル名
 (WHERE 絞り込み条件)
 GROUP BY グループ化の基準列名

SELECT 費目, SUM(出金額) AS 費目別出金額合計
  FROM 帳簿
 GROUP BY 費目　--費目列でグループ化

HAVING句➡︎集計処理後の結果に対して、絞り込みを行う

SELECT グループ化の基準列名, 集計関数
  FROM テーブル名
 (WHERE もともとの表に対する絞り込み条件)
 GROUP BY グループ化の基準列名
HAVING 集計結果に対する絞り込み条件

SELECT 費目, SUM(出金額) AS 費目別出金額合計
  FROM 帳簿
 GROUP BY 費目
HAVING SUM（出金額） > 0 --合計値0より大きいグループを抽出

集計関数の記述ができる（WHERE句と異なる点）
➡︎なぜなら集計結果が出揃った段階で実行されるから

集計テーブル➡︎集計結果の格納専用テーブル

[メリット]　必要時に応じて、格納されている計算済の集計結果を取り出すことができる
[デメリット]　最新の集計より、古いデータの可能性がある
➡︎そのため、定期的に更新を行わなければならない

副問い合わせ（副照会・サブクエリ）

SQL文(主問い合わせ)内にネストされている別のSELECT文

[状況]
①複数の副問い合わせ
②副問い合わせ内に別の副問い合わせ

[動作]
❶先にネスト構造のSELECT文が実行され、結果（値）に化ける
❷その後、外側のSQL文が実行される

SELECT 費目, 出金額 FROM 帳簿
 WHERE 出金額 = (SELECT MAX(出金額) FROM 帳簿)  --（）で囲まなければならない

単一行副問い合わせ➡︎検索結果が1行1列の値

記述場所はSELECT文の選択列リスト・FROM句・UPDATE文のSET句・WHERE句の条件式など

UPDATE 帳簿集計
   SET 平均 = (SELECT AVG(出金額))
                FROM 帳簿アーカイブ
               WHERE 出金額 > 0
                 AND 費目 = '食費'
 WHERE 費目 = '食費'

SELECT 日付, メモ, 出金額,
       (SELECT 合計 FROM 帳簿集計
         WHERE 費目 = '食費') AS 過去合計額
  FROM 帳簿アーカイブ
 WHERE 費目 = '食費'

複数行副問い合わせ➡︎検索結果がn行1列の値

記述場所はSELECT文のFROM句、WHERE句の条件式（IN、ANY/ALL演算子）など

SELECT * FROM 帳簿
 WHERE 費目 IN ('食費', '水道光熱費')
   もしくは
 WHERE 費目 IN (SELECT DISTINCT 費目 FROM 帳簿)

SELECT * FROM 帳簿
 WHERE 費目 = '食費'
   AND 出金額 < ANYもしくはALL(SELECT 出金額 FROM 帳簿アーカイブ
                    　　　　　WHERE 費目 = '食費')

[NULLへの対応]
NOT IN演算子、<>ALL演算子の副問い合わせ結果にNULLが含まれる場合➡︎検索結果はすべてNULL
IN演算子、=ANY演算子の場合➡︎他に等しい値があれば検索結果を得られる

①絞り込み条件にIS NOT NULL句を追加
    SELECT * FROM 帳簿アーカイブ
     WHERE 費目 IN (SELECT 費目 FROM 帳簿 
                    WHERE 費目 IS NOT NULL)

②COALESCE関数でNULLを別値に置き換え
    SELECT * FROM 帳簿アーカイブ
     WHERE 費目 IN (SELECT COALESCE(費目, '不明') FROM 帳簿)

表副問い合わせ➡︎検索結果がn行m列の値

記述場所はSELECT文のFROM句、INSERT文など

SELECT SUM(SUB, 出金額) AS 合計出金額
  FROM (SELECT 日付, 費目, 出金額
          FROM 帳簿
         UNION
        SELECT 日付, 費目, 出金額
          FROM 帳簿アーカイブ
         WHERE 日付 >= '2020/11/01'
           AND 日付 <= '2020/11/30') AS SUB  --副問い合わせの結果に別名を設けることで、わかりやすくなる

INSERT INTO 帳簿集計(費目, 合計, 平均, 回数) --副問い合わせのおかげで、複数行のデータ追加可能になる（本来1呼び出し1行追加）
SELECT 費目, SUM(出金額), AVG(出金額), 0
  FROM 帳簿
 WHERE 出金額 > 0
 GROUP BY 費目

相関副問い合わせ

副問い合わせ内から主問い合わせの表や値を利用

【EXISTS演算子】

SELECT 列名 FROM テーブル名1
 WHERE EXISTS (SELECT * FROM テーブル名2
                WHERE テーブル名1.列名 = テーブル名2.列名)

SELECT 費目, 合計 FROM 帳簿集計
 WHERE EXISTS (SELECT * FROM 帳簿
                WHERE 帳簿.費目 = 帳簿集計.費目)

・通常の副問い合わせと比べて処理方法・動作原理が異なるため、別物であることに注意
・外側SQL文のテーブルから行を絞り込む過程において、
　各行を抽出して良いかの判断を行うために副問い合わせを繰り返し実行する