SQLの書き方

はじめに

　SQL文の書き方についてまとめた記事。

　内容は以下である
　 1. 基礎構文
　 2. 詳細な指定をするための各句の役割

　DBMSの種類によってSQLの構文や挙動が異なるため、
　この記事に記載したものが必ずしも正しいものでない。

用語

文	SQLの実行単位であり、複数の句で構成
句	キーワードと式(定数、条件、列名などのまとまり)で構成
キーワード	SELECT, WHEREなどのSQLコマンド

記述順序

　各句は、上から順番に記述していく。

SELECT
FROM
JOIN ON
WHERE
GROUP BY
HAVING
ORDER BY
LIMIT

実行順序

　各句は、上から順番に実行される。

FROM
JOIN ON
WHERE
GROUP BY
HAVING
SELECT
UNION
DISTINCT
ORDER BY
LIMIT

テーブル操作

　テーブル構造の定義や変更に関するSQL文。

テーブル作成

CREATE TABLE テーブル名 (カラム名1　データ型　制約,
　　　　　　　　　　　　 [カラム名1　データ型　制約,...],
　　　　　　　　　　　　 PRIMARY KEY ());

データ型	役割
INTEGER	整数値
CHER()	固定長文字列 (文字数に満たない場合、半角スペースで補完する)
VARCHAR()	可変長文字列
DATA	日付

制約	役割
NOT NULL	値の未定義を許可しない
DEFAULT	デフォルト値を設定する

テーブル削除

DROP TABLE テーブル名;

カラムの追加/削除

追加：ALTER TABLE テーブル名 ADD COLUMN カラム名 データ型 制約 [挿入位置];

・挿入位置指定は、FIRSTやAFTER カラム名など。

削除：ALTER TABLE テーブル名 DROP COLUMN カラム名;

テーブル名変更

ALTER TABLE テーブル名 RENAME TO 変更後テーブル名;

カラム名変更

ALTER TABLE テーブル名 RENAME COLUMN 列名 TO 変更後カラム名;

カラム定義変更

ALTER TABLE テーブル名 MODIFY COLUMN 列名 変更後データ型 変更後制約;

レコード全削除

TRUNCATE TABLE テーブル名;

・DELETE FROMとの違いは次の2点、
　条件(WHERE句)を指定できない。
　DDLであるため、DBMSによってロールバックすることができない。

基本レコード操作

　レコードに対する4つの基本的なSQL文。

抽出　

SELECT カラム名 FROM テーブル名;

更新

UPDATE テーブル名 SET カラム名1 = 値1[, カラム名2 = 値2...]　[WHERE 条件];

　条件を指定しない場合、全レコードが対象になる。

挿入

INSERT INTO テーブル名 (カラム名1[, カラム名2...]) VALUES (値1[, 値2...]);

　テーブルにあるカラムの内、一部しか指定しない場合、
　指定されていないカラムにはデフォルト値が設定される。

削除

DELETE FROM テーブル名 [WHERE 条件式];

　条件を指定しない場合、全レコードが削除される。

詳細操作

　各句の役割や処理フローについて触れる。

WHERE句

　テーブルのレコードを1行ずつ条件に合致するか評価していき、
　trueとなる行を選択する。

　WHERE句では集約関数を使用することはできない。

集約関数は使用不可：
　WHERE句の役割は抽出するレコードを選定する句である
　一方、集約関数は抽出したレコードに対する処理である。
　そのため、WHERE句では使用できないが、WHERE句以降に実行される
　GROUP BY、HAVING、SELECT句で使用することができる。
　(あくまで個人的な推測なので真実ではない)

GROUP BY句

　指定したカラムが持つ値毎に、レコードをグループ分けする。

　GROUP BY句を用いた場合、SELECT句に指定できるものはGROUP BY句に指定したカラム、
　もしくは集約関数である。

理由：
　GROUP BY句は指定したカラムの値毎に該当する複数レコードを1つにまとめる。
　例えば次のようなテーブルが存在するとして、

カラム1	カラム2
1	100
1	110
2	200
2	210

　上のテーブルに対してGROUP BY カラム1とすると下記の様になる。

カラム1	カラム2
1	100, 110
2	200, 210

　この状態でカラム2をSELECT句で指定すると、1つのレコードのカラムに値が複数
　存在している(100, 110など)ため、どの値を抽出すればよいのか判断できない。
　集約関数においては、全ての値を対象に演算を行うため複数値が存在して
　いても有効である。

集約関数

　テーブルの複数行に対して、操作や計算を行う。
　集約関数を指定することができるのは、SELECT句、HAVING句、ORDER BY句のみである。

関数名	役割
COUNT()	行数を数える
SUM()	合計値を算出
AVG()	平均値を算出
MAX()	最大値を取得
MIN()	最小値を取得

NULLの扱い：
　集約関数は、NULL値を除外して処理するが、
　COUNT(*)の場合、NULL値を含めてカウントを行う。

HAVING句

　GROUP BY句の結果に対して、抽出条件を指定する。
　指定できるものは、GROUP BY句で指定したカラム、もしくは集約関数の比較式。

ORDER BY句

　抽出結果に対してソートを行う。
　並び順は、デフォルトでは昇順(ASC)に設定されていて、降順はDESCを指定する。
　複数カラムを指定した場合、左に記述したカラムから順に整列される。

例

  SELECT * FROM users WHERE age > 20 ORDER BY name;      /*ASC順*/
  SELECT * FROM users WHERE price > 20 ORDER BY age DESC;
  SELECT * FROM users WHERE price > 20 ORDER BY age DESC, name ASC; /*複数指定*/

INNER JOIN句

　2つのテーブルを結合する。
　INNER JOIN句でテーブル名を指定し、ON句で結合条件を指定する。

　結合条件に指定したカラムの値の内、テーブル間で一致する値を持つレコードのみ
　を結合させる。

例

  SELECT *
  FROM book_sales
    INNER JOIN stores
      ON book_sales.store_id = stores.id  /*book_salesとstoresのどちらかにしかない*/
  WHERE price >= 3500                     /*カラム値を持つレコードは結合されない*/
  ;

OUTER JOIN句

　2つのテーブルを結合する。
　OUTER JOIN句でテーブル名を指定し、ON句で結合条件を指定する。

　条件に指定したカラムの値の内、一方のテーブル(結合元)に存在する全ての
　レコードを結合させる。
　もう一方のテーブル(結合先)に値が存在しない場合、NULLが格納される。

　LEFT OUTER JOINの場合、結合元はfrom句で指定したテーブルになる。
　RIGHT OUTER JOINの場合、結合元はRIGHT OUTER JOIN句で指定したテーブルになる。

例

  SELECT *
  FROM book_sales
    LEFT OUTER JOIN stores                  /*LEFTの場合、book_salesを基準にして*/
      ON book_sales.store_id = stores.id    /*storesを結合させる*/
  WHERE price >= 3500
  ;

UNION/UNION ALL句

　2つのSELECT文の結果を統合する。

　UNIONはSELECT結果のカラムが、同じ数でかつ同じデータ型でなければいけない。
　UNIONは重複行を1行にまとめる、UNION ALLはそれをしない。

例

  SELECT id, name, Department FROM users1
  UNION
  SELECT id, name, Department FROM users2;

サブクエリ

　入れ子になったクエリの内の、内側のクエリのこと。
　外側のクエリ(メインクエリ)の色々な句に記述することができる。
　サブクエリは、カッコ()内に記述する。

　実行順はメインクエリよりも、サブクエリが先に実行される。

例

  SELECT id, name, price
  FROM book_sales
  WHERE price IN (     /*サブクエリの返り値が複数行になる場合、どの値と比較するか*/
    SELECT MAX(price)  /*わからなくなるため、複数値扱えるINやANY句などが必要になる*/
    FROM book_sales
    GROUP BY store_id
  );

相関サブクエリ

　サブクエリ内でメインクエリのテーブルを参照しているサブクエリ。

　相関サブクエリはメインクエリのテーブルから1行参照し、それを用いて
　自身の処理を行いメインクエリに結果を渡す。それを全ての行分繰り返す。

例

  SELECT *
  FROM book_sales AS mainquery_t
  WHERE price = (
    SELECT MAX(price)
    FROM book_sales AS subquery_t
    WHERE mainquery_t.store_id = subquery_t.store_id
  );
  /*mainquery_tから1行参照しサブクエリの処理が行われ、結果をメインクエリのWHERE句で比較。
  その後、同じようにmainquery_tから次の1行参照し･･･を繰り返していく。*/

DISTINCT句

　SELECT句で抽出した結果から重複行を削除する。
　複数カラムを指定した場合、全てのカラムの値が一致するレコードを削除する。

例

  SELECT DISTINCT department FROM users;

LIMIT句

　抽出した結果の内、先頭から指定した行数分だけ取得する。
　OFFSET句で、先頭から除外する行数を指定できる。

例

  SELECT * FROM users LIMIT 10;           /*先頭から1～10行目分取得*/
  SELECT * FROM users LIMIT 10 OFFSET 10; /*先頭から11～20行目分取得*/

EXISTS句

　条件に合致するレコードが存在するか確認し、真偽値を返す。
　サブクエリと併用して使用される。

　条件に1つでも当てはまるレコードがあればtrue、なければfalseを返す。

IN句

　指定した値もしくはサブクエリの結果が、カラムに含まれているかを調べる。
　返り値はレコード。

例

  SELECT * FROM users WHERE name IN('a', 'b');
  SELECT * FROM users WHERE name NOT IN('a', 'b');   /*NOT INで否定を表す*/
  SELECT * FROM users WHERE name IN(select name from users where age >= 20);  /*サブクエリ*/

AS句

　テーブルやカラムに別名を付ける。

LIKE句

　カラムに対して文字列検索を行う。

ワイルドカード	役割
%	0文字以上の任意の文字列
_	任意の1文字

例

  SELECT * FROM users WHERE name LIKE '%太郎%';

演算子

比較演算子	役割
=	左右が等しい
<>, !=	左右が等しくない
<	左が右より小さい
<=	左が右以下
>	左が右より大きい
>=	左が右以上

論理演算子	役割
AND	かつ
OR	または
NOT	否定

優先順位：
　論理演算子には処理される順番が決まっていて、
　NOT、AND、ORの順で演算される。

例

SELECT * FROM users WHERE NOT age = 20;  /*NOTは先頭に書く*/
SELECT * FROM books WHERE (age = 21 OR age = 22) AND gender = male;
/*()内に書かれた処理が一番優先されるため、ANDより先に実行される*/