SQLの構文復習

SQLはCakePHPを使った案件で使ったことがあるくらいで、実はあまり覚えていない。

使い方を覚えているのは基本のCRUD(INSERT文,SELECT文,UPDATE文,DELETE文)とFROM句とWHERE句くらいだった。レコードを昇順に取得したり、WHEREを使った条件検索で重複行を無くしたかったりした場合にはその都度、調べなければならず、かなりプログラミングの効率が落ちていることに気付いたので、効率を上げる為にも、この機会にSQLの基礎を記事にまとめてみる。

SQL関連用語

CRUD

CRUD（クラッド）とは、ほとんど全てのコンピュータソフトウェアが持つ永続性の4つの基本機能のイニシャルを並べた用語。その4つとは、Create（生成）、Read（読み取り）、Update（更新）、Delete（削除）である。ユーザインタフェースが備えるべき機能（情報の参照/検索/更新）を指す用語としても使われる。

名前	操作	SQL
Create	生成	INSERT
Read	読み取り	SELECT
Update	更新	UPDATE
Delete	削除	DELETE

ワイルドカード

SQL文ではワイルドカードが使用できる。ワイルドカードは、検索などの際に指定するパターンに使用する特殊文字の種類で、どんな対象文字、ないし文字列にもマッチするもののことである。カードゲームのワイルドカードに由来する呼称。
Unixのシェルでは「?」（疑問符）を任意の一文字、「*」（アスタリスク）を長さ0文字以上の任意の文字列にマッチするパターンとして、コマンドライン上で、存在するファイルの名前に対して展開される（たとえば ac abc abd というファイルがある場合、cat a*c とコマンドを入力すると、cat abc ac のように展開されたうえで、cat プログラムが起動される）。

以下より、SQL文の構文について説明する。

DESC文

DESC文はテーブル構造を確認することができる。

構文

DESC [テーブル名];

例

usersテーブルの構造を出力する。

DESC users;

INSERT文

INSERT文はレコードを新たに1件追加することができる。

構文

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

例

addressbookテーブルにユーザーの情報を追加する。

INSERT INTO addressbook (name, zip, address, create_at) VALUES ('test', '1234', '東京都', '2008/07/17 01:10:00');

SELECT文

SELECT文はレコードを検索することができる。

構文

SELECT カラム1,カラム2... FROM テーブル名;

例

「*」(アスタリスク)のワイルドカードを使用し、addressbookテーブルに含まれる全てのレコード取得する。

SELECT * FROM addressbook;

UPDATE文

UPDATE文はレコードを更新することができる。

構文

UPDATE テーブル名 SET カラム1 = 新しい値1, カラム2 = 新しい値2...;

例

UPDATE addressbook SET zip = '0001', address = '海外' WHERE id = '1';

例では、addressbookテーブルに含まれる、idカラムの値が1になっているレコードを対象にして更新をかけている。

DELETE文

テーブルやレコードを削除することができる。

構文

テーブルを削除する

DELETE FROM テーブル名;

レコードを削除する

DELETE FROM テーブル名 WHERE 条件;

例

addressbookテーブルを削除する

DELETE FROM addressbook;

addressbookテーブルのnameカラムの値がtestになっているレコードを削除する

DELETE FROM addressbook WHERE name = 'test';

条件検索（WHERE句）

SQL文に条件付けを行うにはWHERE句を使用する。WHERE句を使用しない場合には対象のテーブルに存在する全てのレコードに対してSQL文が適用されることになるが、そのようなSQL文の適用を行うことはかなり稀だと思う。特定のレコードに対してSELECT文やUPDATE文やDELETE文を適用したい場合にはWHERE句を使用してSQL文の適用範囲を条件指定する必要がある。

構文

SELECT 取得するカラム名 FROM テーブル名 WHERE 検索条件;

例

idカラムが「1」のレコードを取得する例

SELECT * FROM addressbook WHERE id = 1;

idカラムが「4」より小さいレコードを取得する例

SELECT * FROM addressbook WHERE id < 4;

条件検索に文字列を使用する例

SELECT * FROM addressbook WHERE address = '愛知';

あいまい検索(LIKE演算子)

「指定した文字列が含まれる」という検索条件を指定するにはLIKE演算子を使用する。

構文

SELECT 取得するカラム名 FROM テーブル名 WHERE 検索するカラム名 LIKE '検索文字列';

例

addressbookテーブルのnameカラムが「佐藤」で始まるレコードを検索する。

SELECT * FROM addressbook WHERE name LIKE '佐藤%';

ワイルドカード	説明
%（パーセント）	何文字かと一致する。
_（アンダースコア）	1文字と一致する。

並べ替え（ORDER BY句）

SELECT文で取得したレコードの順番を並べ替えるには、ORDER BY句を使用する。ORDER BY句にはどのカラムを使って並べ替えるのかを指定する必要がある。

構文

ORDER BY句のみを使う場合

SELECT 取得するカラム名 FROM テーブル名 ORDER BY 並び替える対象のカラム名 昇順／降順;

WHERE句と一緒にORDER BY句を使う場合

SELECT 取得するカラム名 FROM テーブル名 WHERE 検索条件 ORDER BY 並び替える対象のカラム名 昇順／降順;

キーワード	説明
ASC	昇順（小さい順）に並び替える。
DESC	降順（大きい順）に並び替える。

例

昇順（カラムの値が小さい順に並び替える場合）
addressbookテーブルのidカラムが1以上のレコードを検索し、昇順で並べる。

SELECT * FROM addressbook WHERE id >= 1 ORDER BY id ASC;

降順（カラムの値が大きい順に並び替える場合）
addressbookテーブルのidカラムが1以上のレコードを検索し、降順で並べる。

SELECT * FROM addressbook WHERE id >= 1 ORDER BY id DESC;

グルーピング（GROUP BY句）

同じ値を持つレコードが何件あるのかを調べるには、GROUP BY句を使用して同じ値のレコードをグループ化して取得する必要がある。例えば、膨大な量のレコードを持つテーブルから、addressカラムが「宮城」になっているレコードが何件あるかを知りたい場合にはGROUP BY句を使用する必要がある。

構文

GROUP BY句のみを使う場合

SELECT 取得するカラム名 FROM テーブル名 GROUP BY グルーピングするカラム名;

WHERE句やORDER BY句と一緒にGROUP BY句を使う場合

SELECT 取得するカラム名 FROM テーブル名 WHERE 検索条件 GROUP BY グルーピングするカラム名 ORDER BY 並び替える対象のカラム名;

例

住所ごとの件数を取得する例

SELECT address, COUNT(*) FROM addressbook GROUP BY address;

※基本的にGROUP BY句は何かのSQLの関数(COUNT()やAVG()など)の為にグルーピングするという用途になる。

重複の取り除き(DISTINCT句)

値が同じデータを１つにまとめるには、重複を取り除くDISTINCT句を使用する。どの値が重複しているかをチェックするために、チェック対象のカラムを指定する必要がある。

構文

SELECT DISTINCT 取得するカラム名 FROM テーブル名;

例

SELECT DISTINCT address FROM addressbook;

取得する件数の制限(LIMIT句, OFFSET句)

取得する件数を指定するにはLIMIT句を使用する。LIMIT句には、何件取得するのかを指定する。また、LIMIT句はWHERE句やORDER BY句、GROUP BY句よりもあとに書く。また、OFFSET句も使用すると取得開始件数を指定できる。

構文

基本構文

SELECT 取得するカラム名 FROM テーブル名 LIMIT 取得件数;

OFFSET句も使用して取得開始件数を指定する

SELECT 取得するカラム名 FROM テーブル名 LIMIT 取得件数 OFFSET 取得開始件数

例

3件だけ取得する例

SELECT * FROM addressbook LIMIT 3;

4件目から3件だけ取得する例

SELECT * FROM addressbook LIMIT 3 OFFSET 4;

範囲検索(BETWEEN演算子, IN句)

「ある値より小さく、且つ、ある値よりも大きい」という条件は「>」(大なり)と「<」(小なり)をANDで組み合わせることによって検索することができるが、1つにまとめて書く方法がある。BETWEEN演算子は範囲をANDでつなぎ、ある値と値の間のレコードを取得することができる。また、IN句を使用すると3,4,6のように特定の値を含んだレコードを取得することもできる。

構文

SELECT 取得するカラム名 FROM テーブル名 WHERE 範囲を指定するカラム BETWEEN 条件1 AND 条件2

SELECT 取得するカラム名 FROM テーブル名 WHERE 範囲を指定するカラム IN (条件1, 条件2, ...)

例

・idの値が3～6のレコードを取得する例

SELECT * FROM addressbook WHERE id BETWEEN 3 AND 6;

・idが3,4,6のいずれかであるレコードを取得する例

SELECT * FROM addressbook WHERE id IN (3, 4, 6);

トランザクション(START TRANSACTION文, COMMIT文, ROLLBACK文)

登録、更新、削除の処理が複数あり、その処理がすべて成功した時だけ処理を確定させたい、また、それ以外の場合は全ての処理をなかったことにしたい場合がある。このような「どこからどこまでを1つの処理として扱うか？」という処理単位をトランザクションという。トランザクションはデータベースによって対応状況が変わる。以下はMySQLでトランザクションを使う場合である。

トランザクションを開始するにはSTART TRANSACTION文を使用する。START TRANSACTIONを実行した後に、データの登録、更新、削除処理を行う。
　
トランザクションの開始

START TRANSACTION;

全ての登録、更新、削除処理が終わり、処理を確定させたい場合は、COMMIT文を使用する。

トランザクションの確定

COMMIT;

一方、トランザクション開始からの処理を全て破棄したい場合は、ROLLBACK文を使用する。実行すると、全ての処理内容が破棄され、トランザクションを開始する前の状態に戻る。

トランザクションの破棄

ROLLBACK;

列やテーブルに別名をつける(AS句)

列に別名をつけるにはAS句を使用する。

構文

基本構文

SELECT 列名 AS 別名 FROM テーブル名 AS 別名;

例

sample_idにexample_idという別名をつける例

SELECT sample_id AS example_id FROM samples AS examples;

AS句は，列名が一見して分かりにくい場合などに使うと良い。

サブクエリ

サブクエリとは，中間結果を得る為に別のSQL文の中で使用するSELECT文である。WHERE句の条件として使ったり，選択リストの中や，FROM句の中でテーブルの代わりとして使う場合などがある。

構文

SELECT 列名 FROM テーブル名 WHERE 条件 ( サブクエリ );

例

サブクエリの結果に含まれていないsample_idのみを取得する例

SELECT * FROM samples WHERE sample_id NOT IN ( SELECT example_id FROM examples );

テーブルの内部結合(INNER JOIN句)

内部結合とは，複数のテーブル同士をある列の値に基づいて，1つの結果にまとめていくことである。内積なので指定した列の値がどちらかにしかないレコードについては結果に含まれない。INNER JOIN を省略して，単に JOIN と書くことも出来る。

構文

SELECT 列名 FROM 主体テーブル名 INNER JOIN 結合するテーブル名 ON 結合条件;

例

sampleテーブルとexampleテーブルのsample_idとexample_idが同じレコード同士を結合する例(sample_idとexample_idはプライマリキー)。

SELECT * FROM samples JOIN examples ON samples.sample_id = examples.example_id;

テーブルの外部結合(LEFT OUTER JOIN句，RIGHT OUTER JOIN句)

指定したカラムについて同じ値を持つレコード同士を結びつける。基本的には INNER JOIN とあまり変わらない。
LEFT OUTER JOINの場合は，左のテーブル(SQLの構文上での左側)を基準にしてテーブルを結合する。値が右のテーブルにあり左のテーブルにない場合は INNER JOIN 同様結果に含まれないが，値が左のテーブルにあり右のテーブルにない場合は INNER JOIN と異なり右のテーブルのカラムには全て NULL がパディングされ，結果に含まれる。

RIGHT ONTER JOIN は，動作が LEFT OUTER JOIN とは逆で，右のテーブルを基準にしてテーブルを結合する。
LEFT OUTER JOIN ， RIGHT OUTER JOIN を省略して，単に LEFT JOIN ， RIGHT JOIN と書くことも出来る。

構文

SELECT 列名 FROM 主体テーブル名 LEFT OUTER JOIN 結合するテーブル名 ON 結合条件;

例

sampleテーブルのsample_idとexampleテーブルのexample_idを比較し，同じ値を持つレコード同士を結合する例(sample_idとexample_idはプライマリキー)。

SELECT * FROM samples LEFT OUTER JOIN examples ON samples.sample_id = examples.example_id;

クエリの結果を和結合する(UNION句，UNION ALL句)

UNION演算子を使用すると，2つのクエリの結果を統合し，1つの結果とすることができる(和結合)。UNION句は，同じ行を結果から省く。同じ行を結果から省きたくない場合は，UNION ALL句を使用する。

構文

SELECT 列名 FROM テーブル名 UNION SELECT 列名 FROM テーブル名;

例

2つのクエリの結果を和結合する。samplesテーブルのsample_idとexamplesテーブルのexample_idを結果として出力する。UNIONを使っているので重複する行は除外される。

SELECT sample_id FROM samples UNION SELECT example_id FROM examples;

クエリの結果を積結合する(INTERSECT演算子)

INTERSECT演算子を使用すると，2つのクエリの結果から同じものだけを結果とすることができる(積結合)。

構文

SELECT 列名 FROM テーブル名 INTERSECT SELECT 列名 FROM テーブル名;

例

2つのクエリの結果を積結合する。samplesテーブルのsample_idとexamplesテーブルのexample_idを比較し，両方に同じ値が含まれているものだけを結果とする。

SELECT sample_id FROM samples INTERSECT SELECT example_id FROM examples;

クエリの結果を差結合する(EXCEPT演算子)

EXCEPT演算子を使用すると，2つのクエリの結果からどちらか片方にしか含まれていないものだけを結果とすることができる(差結合)。

構文

SELECT 列名 FROM テーブル名 EXCEPT SELECT 列名 FROM テーブル名;

例

2つのクエリの結果を差結合する。samplesテーブルのsamples_idにしか含まれていないものだけを結果とする。examplesテーブルのexample_idにsamplesテーブルのsample_idと同じ値が含まれている場合は，それを結果から除外する。

SELECT sample_id FROM samples EXCEPT SELECT example_id FROM examples;

参考
書籍はじめてのPHPプログラミング基本編
 書籍改訂新版反復学習ソフト付き SQL書き方ドリル
 MySQL トランザクション – とみぞーノート
 SQL 基礎 – JOIN (表結合)の意味の違い|てくめも@ecoop.net
SQL実践講座（9）：SELECT文を統合する「UNION」 – ＠IT
[SQL] 8. クエリの結合 | TECHSCORE(テックスコア)