SELECT構文
SELECT [STRAIGHT_JOIN]
[SQL_SMALL_RESULT] [SQL_BIG_RESULT]
[SQL_BUFFER_RESULT]
[SQL_CACHE |
SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS] [HIGH_PRIORITY]
[DISTINCT|
DISTINCTROW |
ALL]
フィールド名 / 式 [ AS 別名 ], ...
[INTO {OUTFILE |
DUMPFILE} 'ファイル名' オプション]
[FROM テーブル名
[WHERE 条件式]
[GROUP BY {整数
| フィールド名| 式} [ASC |
DESC], ... [WITH ROLLUP]]
[HAVING 条件式]
[ORDER BY {整数
| フィールド名| 式} [ASC |
DESC] ,...]
[LIMIT [オフセット,]
行指定]
[PROCEDURE
手続き名(属性一覧)]
[FOR UPDATE
| LOCK IN SHARE MODE]
]
SELECT構文の基本構成は、SELECT句の次に表示させたいフィールド名のリストを並べます。ここで指定したフィールドの値が結果として返されます。フィールド名の変わりにメタ文字のアスタリスク( * )を使用すると、すべてのフィールドの値が返されます。
続いてFROM句では、参照するテーブル名を指定します。FROM句以降はオプションとして、条件式を設定するWHERE句や、レコードをグループ化するGROUP BY句、レコードの表示順を設定するORDER BY句を追加設定していきます。
SELECTを使う前の準備
ここでは、顧客テーブル（テーブル名=customer）を作成し、そのデータを使って説明していきます。まずは準備としてCREATE TABLEとINSERTでテーブルの作成とデータの挿入をしておきましょう。
顧客(customer)テーブルの作成とデータの挿入
# 顧客テーブルの作成
CREATE TABLE customer (
id_c int unsigned NOT NULL auto_increment,
fullname varchar(40) NOT NULL,
age tinyint unsigned NOT NULL ,
sex tinyint unsigned NOT NULL,
email varchar(50),
PRIMARY KEY (id_c)
);
# データの挿入
INSERT customer SET fullname='エラ・フィッツジェラルド',age='48',sex='2',email='ella@exsample.jp';
INSERT customer SET fullname='トミー・ゲレロ',age='34',sex='1',email='tommy@exsample.jp';
INSERT customer SET fullname='マディ・ウォーターズ',age='55',sex='1',email='muddy@exsample.jp';
INSERT customer SET fullname='エディ・ロバーツ',age='28',sex='1';
INSERT customer SET fullname='マーサ・リーブス',age='38',sex='2',email='martha@exsample.jp';
INSERT customer SET fullname='スライ･ストーン',age='34',sex='1';
上記命令文で作成されるテーブルは次のようになります。
顧客（customer）テーブル
顧客テーブルの性別には"男性"と"女性"という属性がありますが、ここでは、それらの値を数値に対応させてフィールドに格納しています。値が1の場合は男性、値が2の場合は女性を意味します。
性別のように、いくつかの決まった文字列が複数のレコードで繰り返し使われる場合、一般的に、その文字列を数値と対応させて、フィールドには数値の方を格納するといった方法がよく使われます。理由としては、文字列で管理するよりも、数値で管理したほうがデータ容量が少なくて済み、検索処理で発生する負荷を軽減させることができるからです。
SELECT命令文で全レコードを選択
SELECT命令文を使ってテーブルに登録された全レコードを選択する場合は、SELECTの後に続くアスタリスク( * )を指定し、FROMで表示対象のテーブル名を指定します。
SELECT * FROM テーブル名;
アスタリスク( * )は全フィールドを意味し、蒸気の命令文で使った場合、選択されたレコードの全フィールドを表示します。表示フィールドの数が多いほど、処理の負荷が高くなりパフォーマンスが低下しますので、注意してください。
顧客(customer)テーブルから全レコードを選択するには、下記のように簡単な1行命令文になります。
SELECT * FROM customer;
+------+--------------------------------------+-----+-----+--------------------+
| id_c | fullname | age | sex | email |
+------+--------------------------------------+-----+-----+--------------------+
| 1 | エラ・フィッツジェラルド | 48 | 2 | ella@exsample.jp |
| 2 | トミー・ゲレロ | 34 | 1 | tommy@exsample.jp |
| 3 | マディ・ウォーターズ | 55 | 1 | muddy@exsample.jp |
| 4 | エディ・ロバーツ | 28 | 1 | NULL |
| 5 | マーサ・リーブス | 38 | 2 | martha@exsample.jp |
| 6 | スライ･ストーン | 34 | 1 | NULL |
+------+--------------------------------------+-----+-----+--------------------+
6 rows in set (0.00 sec)
顧客(customer)テーブルに登録されいている全レコードの全部の値が表示されました。
表示するフィールド名を指定
クエリを発行する際は、無駄な処理でリソースを消費しないように、必要なレコード、必要なフィールドを指定します。たとえば顧客テーブルから[姓名(fullname)]のみが必要な場合は、アスタリスク( * )ですべてのフィールドを指定せずに、[姓名(fullname)]のみを指定します。
表示するフィールドを指定するには、SELECTの後にフィールド名を指定します。
SELECT フィールド名 FROM テーブル名;
下記では、顧客(customer)テーブルから[姓名(fullname)]フィールドの値のみを表示させるために、SELECTにフィールド名の"fullname"を指定しています。
SELECT fullname FROM customer;
+--------------------------------------+
| fullname |
+--------------------------------------+
| エラ・フィッツジェラルド |
| トミー・ゲレロ |
| マディ・ウォーターズ |
| エディ・ロバーツ |
| マーサ・リーブス |
| スライ･ストーン |
+--------------------------------------+
6 rows in set (0.00 sec)
複数フィールドの指定
複数のフィールドの値を取得したい場合は、複数のフィールド名をカンマ( , )で区切って指定します。
SELECT フィールド名1, フィールド名2
FROM テーブル名;
顧客（customer）テーブルから、名前と年齢を取り出すときは、次のようにします。
SELECT fullname, age FROM customer;
+--------------------------------------+-----+
| fullname | age |
+--------------------------------------+-----+
| エラ・フィッツジェラルド | 48 |
| トミー・ゲレロ | 34 |
| マディ・ウォーターズ | 55 |
| エディ・ロバーツ | 28 |
| マーサ・リーブス | 38 |
| スライ･ストーン | 34 |
+--------------------------------------+-----+
6 rows in set (0.00 sec)
ALL / DISTINCT
選択されたすべての行を返す / 選択された行から重複行の削除
ALLはデフォルトのオプションで、選択されたすべての行を返します。DISTINCTは、指定したフィールドの値を調べて、重複した値があれば1行にまとめて返します。
SELECT DISTINCT フィールド名
FROM テーブル名;
ALLはデフォルトのオプションなので、ALLかDISTINCTの指定が無ければ、ALLを選択したことになります。一般的にALLは記述しません。
DISTINCTは選択された全レコードを対象に、指定したフィールドの値を調べて、重複した値があればそれらのレコードを1行にまとめて返します。以下はALLも同様ですが、DISTINCTは1つのSELECT文に1回だけ使うことができます。SELECTの後にDISTINCTを指定し、その後にフィールド名を列挙、もしくはアスタリスク( * )を指定します。
DISTINCTの使い方
# ALL は省略可能
SELECT sex FROM customer;
+-----+
| sex |
+-----+
| 2 |
| 1 |
| 1 |
| 1 |
| 2 |
| 1 |
+-----+
# DISTINCT を指定
SELECT DISTINCT sex FROM customer;
+------+
| sex |
+------+
| 1 |
| 2 |
+------+
DISTINCTの後にアスタリスク( * )を指定した場合、すべての値が同じ場合のみ適用されます。
AS
フィールド名に別名を設定
DBMSの種類によりますが、SELECTを実行すると最初の行に見出しが表示されます。この見出しにあるフィールド名は変更可能で、「フィールド名 AS 別名」 という構文を使用します。ASは省略可能です。
SELECT フィールド名 AS 別名
FROM テーブル名;
ASキーワードを使って、[姓名(fullname)]フィールドに"name"という別名を設定してみましょう。
SELECT fullname AS name FROM customer;
+--------------------------------------+
| name |
+--------------------------------------+
| エラ・フィッツジェラルド |
| トミー・ゲレロ |
| マディ・ウォーターズ |
| エディ・ロバーツ |
| マーサ・リーブス |
| スライ･ストーン |
+--------------------------------------+
6 rows in set (0.00 sec)
見出しが別名に指定した"name"へ変更されています。
HAVINGで別名を使う
ASを使って作成した別名は、ORDER BY句やHAVING句で利用することができます。たとえば下記は、AVG関数を使って性別毎の平均年齢を計算しています。
SELECT sex, AVG(age) AS avgage
FROM customer GROUP BY sex;
+-----+----------+
| sex | avgage |
+-----+----------+
| 1 | 37.7500 |
| 2 | 43.0000 |
+-----+----------+
2 rows in set (0.00 sec)
平均年齢40歳以上のグループのみを表示させるには、上記SELECT文にHAVINGを追加します。その際、ASでつけた別名avgageを利用できます。
SELECT sex, AVG(age) AS avgage
FROM customer GROUP BY sex
HAVING avgage>38;
+-----+---------+
| sex | avgage |
+-----+---------+
| 2 | 43.0000 |
+-----+---------+
1 row in set (0.00 sec)
テーブル名も、"テーブル名 AS 別名"を使って表示する名称を変更することができます。テーブル名に別名をつけても一般的なDBMSでは表示上は変化がありませんが、後ほど紹介する自己結合などのときに役立ちます。
SELECT *
FROM テーブル名 AS 別名
ASC / DESC
昇順に並び替え / 降順に並び替え
ASCとDESCは、ORDER BYで指定したフィールドの並び順を変更することができます。昇順の場合はASC（デフォルト）、降順の場合はDESCです。
SELECT *
FROM テーブル名
ORDER BY フィールド名 DESC;
ORDER BYはデフォルトで昇順に並び替えるので、ASCは指定する必要はありません。
ORDER BYでDESCを使う
顧客(customer)テーブルに格納された[年齢(age)]フィールドの値で降順に並び替える場合は、ORDER BY句に[年齢(age)]フィールドを指定し、DESCキーワードを追加します。
# 年齢順に並び替える
SELECT fullname, age FROM customer
ORDER BY age DESC;
+--------------------------------------+-----+
| fullname | age |
+--------------------------------------+-----+
| マディ・ウォーターズ | 55 |
| エラ・フィッツジェラルド | 48 |
| マーサ・リーブス | 38 |
| トミー・ゲレロ | 34 |
| スライ･ストーン | 34 |
| エディ・ロバーツ | 28 |
+--------------------------------------+-----+
[性別(sex)]フィールドを昇順で並び替えたデータを、さらに[年齢(age)]フィールドで降順に並び替えることも可能です。
SELECT fullname, sex, age
FROM customer
ORDER BY sex, age DESC;
+--------------------------------------+-----+-----+
| fullname | sex | age |
+--------------------------------------+-----+-----+
| マディ・ウォーターズ | 1 | 55 |
| トミー・ゲレロ | 1 | 34 |
| スライ･ストーン | 1 | 34 |
| エディ・ロバーツ | 1 | 28 |
| エラ・フィッツジェラルド | 2 | 48 |
| マーサ・リーブス | 2 | 38 |
+--------------------------------------+-----+-----+
6 rows in set (0.00 sec)
GROUP BY
フィールドの値や式の値でグループ化
GROUP BYは特定の列のフィールドの値や式の値でグループ化します。グループ化したレコードは、集計関数などを使って各グループの合計値や平均値などを算出することが可能です。
SELECT COUNT(*) FROM テーブル名
GROUP BY フィールド名;
複数のフィールドをグループ化したい場合は、GROUP BYの後に複数のフィールド名をカンマ( , )で区切って指定します。
SELECT COUNT(*) FROM テーブル名
GROUP BY フィールド名, フィールド名, ... ;
GROUP BYの使い方
GROUP BYを使ってcustomerテーブルのsexフィールドの値でグループ化し、COUNT関数で男性と女性毎の合計人数を出力します。
# 男女別に購入点数の合計を計算
SELECT sex, COUNT(*) FROM customer
GROUP BY sex;
+-----+----------+
| sex | count(*) |
+-----+----------+
| 1 | 4 |
| 2 | 2 |
+-----+----------+
上記では、SELECT句に列の合計値を求めるCOUNT関数を指定し、GROUP BYで集計のキーとなるフィールド名、[性別(sex)]を指定しています。これによって、GROUP BYでグループ化した[性別(sex)]の合計人数を求めることができます。
COUNT関数やAVG関数といった、GROUP BYでよく使う集計関数を紹介します。
集計関数の一覧
選択された行をロックします。ページ／レコードロックとともにテーブルハンドラ上でFOR UPDATEを使用すると、検査されるレコードは書き込みロックされます。
SELECT * FROM customer FOR UPDATE;
FROM
参照するテーブルを設定
FROM句には、選択対象のテーブルを指定します。複数のテーブルを結合したい場合はテーブル名をカンマ( , )で区切って記述します。JOINを使えば、結合条件をFROM句で指定することも可能です。
SELECT * FROM テーブル名;
SELECT * FROM テーブル名, テーブル名, ...;
FROMの使い方
顧客（customer）テーブルの[姓名(fullname)]フィールドの値を全て表示するには、次のような命令文になります。
SELECT fullname FROM customer;
+--------------------------------------+
| fullname |
+--------------------------------------+
| エラ・フィッツジェラルド |
| トミー・ゲレロ |
| マディ・ウォーターズ |
| エディ・ロバーツ |
| マーサ・リーブス |
| スライ･ストーン |
+--------------------------------------+
6 rows in set (0.00 sec)
顧客（customer）テーブルからすべての[姓名(fullname)]フィールドが出力されました。
HAVING
選択したレコードに対して条件を設定する
HAVING句はWHERE句と同じように、条件にマッチしたレコードを選択します。HAVING句がWHERE句と異なる点は、HAVING句の場合は集計後に条件式を適用するということです。WHERE句は集計前に条件式を適用します。
HAVING句は集計関数とセットでよく使われます。たとえば、顧客(customer)テーブルの[性別(sex)]フィールドでグループ化し、その後、[性別(sex)]ごとに[年齢(age)]の平均を算出する場合、以下のようなクエリになります。
SELECT sex, AVG(age) AS avgage
FROM customer
GROUP BY sex;
+-----+---------+
| sex | avgage |
+-----+---------+
| 1 | 37.7500 |
| 2 | 43.0000 |
+-----+---------+
この後、平均年齢40歳以上の性別だけを選択したい場合は、以下のようにHAVINGで条件を設定します。
SELECT sex, AVG(age) AS avgage
FROM customer
GROUP BY sex
HAVING avgage>40;
+------+--------+
| name | total |
+------+--------+
| PDA | 350000 |
+------+--------+
最頻値の算出
最頻値とは、最も度数( その値を取るデータ数 )の多い値のことです。
たとえば、あるサイトの1週間のページビューを集計した結果、スポットで広告を掲載した月曜日のページビューが50万程度、他の曜日は1万ページビューで、広告があった場合とない場合では50倍以上の差があったとします。1日の平均を計算すると、1週間のトータルが56万ページビューとなり、それを7で割った1日の平均が8万ページビューです。
計算上では1日の平均は8万ページビューとなりますが、広告がなければ、1日の平均は1万ページビューです。このことから分かるように、例外の値が大きい場合は、平均値も大きく影響されます。こういうケースでは、集団の傾向をもっと正確に示す指標、たとえば最頻値を使うとよいでしょう。最頻値は単純に最も多かった値になるので、ページビューの最頻値は1万となります。
DBMSによっては、最頻値を求める独自の関数を用意している場合もありますが、HAVINGを使って、最頻値を求めることもできます。customerテーブルの年齢の最頻値を求める場合、以下のようなSQL文となります。
SELECT age FROM customer
GROUP BY age
HAVING COUNT(*) >= ALL(SELECT COUNT(*) FROM customer GROUP BY age);
+-----+
| age |
+-----+
| 34 |
+-----+
SELECT ... INTO OUTFILE "ファイル名"
構文は、選択された行をファイルに書き込みます。既に存在するファイルには書き込めません。SELECT ...
INTO OUTFILEで書き出したファイルは、LOAD DATA INFILEで読み込むことができます。
オプションの構文は、LOAD DATA INFILE 構文で使われるFIELDSやLINESと同じです。詳しくは【LOAD
DATA INFILE 構文】を参照してください。
一番簡単な INTO OUTFILE の利用法です。
SELECT * INTO OUTFILE "c:/customer.txt"
FROM customer;
"customer.txt"ファイルの中身は次のようになります。
1 エラ・フィッツジェラルド 48 2
2 トミー・ゲレロ 34 1
3 マディ・ウォーターズ 55 1
4 エディ・ロバーツ 28 1
5 マーサ・リーブス 38 2
6 スライ･ストーン 34 1
INTO OUTFILEを使うとき、エスケープ・キャラクタ、ASCIIの0
、全てのターミネータ・キャラクタはエスケープされます。また、オプションの ESCAPED BYで指定した文字、FIELDS TERMINATED BY、LINES TERMINATED BYの最初の文字をエスケープします。
次は、CSV形式のファイルを作成するためのオプションです。
SELECT * INTO OUTFILE "/home/user/dump.txt"
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY "\n"
FROM table;
SELECT ... INTO OUTFILEはローカルで使用されることを前提に最適化されているので、別のホスト上では使用できません。その場合は、mysqldump
--tab や mysql -e "SELECT ..." > outfile を代わりに使ってください。
INTO OUTFILEの代わりにINTO DUMPFILEを使用すると、
ファイルに１レコードだけを書きます。フィールドや行の終端とすべてのエスケープを含みません。これはファイル内にBLOBを格納したい場合に便利です。
※INTO OUTFILEとINTO DUMPFILEによって生成されたファイルは、すべてのユーザに読み出し可能です。
LIKE
ワイルドカードを使った文字列検索
LIKEは、WHEREの条件式内で、ワイルドカードを使った文字列検索に使用できます。
LIKEの詳しい情報は【WHEREでレコードを検索する】を参照してください。
LIMIT
選択された行数の件数指定
LIMITを使って、SELECTが返す行数を指定することができます。LIMITは、2つまでの引数を受け付けます。
SELECT * FROM テーブル名
LIMIT 件数;
SELECT * FROM テーブル名
LIMIT 最初の行からのオフセット, 最大数;
引数が1つの場合は、行の最大数を指定したことになります。
# 3行まで返す
SELECT * FROM customer LIMIT 3;
+------+--------------------------------------+-----+-----+-------------------+
| id_c | fullname | age | sex | email |
+------+--------------------------------------+-----+-----+-------------------+
| 1 | エラ・フィッツジェラルド | 48 | 2 | ella@exsample.jp |
| 2 | トミー・ゲレロ | 34 | 1 | tommy@exsample.jp |
| 3 | マディ・ウォーターズ | 55 | 1 | muddy@exsample.jp |
+------+--------------------------------------+-----+-----+-------------------+
3 rows in set (0.00 sec)
引数が2つの場合は、最初の引数は最初の行からのオフセット、2つめの引数は行の最大数です。初めの行のオフセットは 0
です。
# 3～4行目を返す
SELECT * FROM customer LIMIT 2, 2;
+------+--------------------------------+-----+-----+-------------------+
| id_c | fullname | age | sex | email |
+------+--------------------------------+-----+-----+-------------------+
| 3 | マディ・ウォーターズ | 55 | 1 | muddy@exsample.jp |
| 4 | エディ・ロバーツ | 28 | 1 | NULL |
+------+--------------------------------+-----+-----+-------------------+
2 rows in set (0.00 sec)
ORDER BY
フィールドの値でレコードの順番を並び替え
ORDER BYは、指定したフィールドの値で行をソートします。ORDER BYの後に並び替えしたい列のフィールド名、もしくは式を指定します。
SELECT * FROM テーブル名
ORDER BY フィールド名
顧客(customer)テーブルを[年齢(age)]フィールドで昇順、降順に並び替えてみましょう。
# 顧客(customer)テーブルを[年齢(age)]フィールドで昇順に並び替え
SELECT fullname, age FROM customer
ORDER BY age;
+--------------------------------------+-----+
| fullname | age |
+--------------------------------------+-----+
| エディ・ロバーツ | 28 |
| トミー・ゲレロ | 34 |
| スライ･ストーン | 34 |
| マーサ・リーブス | 38 |
| エラ・フィッツジェラルド | 48 |
| マディ・ウォーターズ | 55 |
+--------------------------------------+-----+
ORDER BY句はデフォルトで昇順に並び替えますが、フィールド名の後にDESCをつけることで、並び順を降順にすることができます。
# 顧客(customer)テーブルを年齢で降順に並び替え
SELECT fullname, age FROM customer
ORDER BY age DESC;
+--------------------------------------+-----+
| fullname | age |
+--------------------------------------+-----+
| マディ・ウォーターズ | 55 |
| エラ・フィッツジェラルド | 48 |
| マーサ・リーブス | 38 |
| トミー・ゲレロ | 34 |
| スライ･ストーン | 34 |
| エディ・ロバーツ | 28 |
+--------------------------------------+-----+
複数のフィールドをカンマ( , )で区切って列挙することもできます。その場合は、指定された先頭のフィールドから順に並び替えられます。
SELECT * FROM テーブル名
ORDER BY フィールド名1, フィールド名2, ...;
顧客(customer)テーブルを[性別(sex)]フィールドで並び替えてから、[年齢(age)]フィールドで昇順に並び替えてみましょう。
SELECT fullname, sex, age FROM customer
ORDER BY sex, age;
+--------------------------------------+-----+-----+
| fullname | sex | age |
+--------------------------------------+-----+-----+
| エディ・ロバーツ | 1 | 28 |
| トミー・ゲレロ | 1 | 34 |
| スライ･ストーン | 1 | 34 |
| マディ・ウォーターズ | 1 | 55 |
| マーサ・リーブス | 2 | 38 |
| エラ・フィッツジェラルド | 2 | 48 |
+--------------------------------------+-----+-----+
PROCEDURE節には、結果セット内のデータの処理に使用するプロシージャを指定します。
WHERE
条件にマッチしたレコードからフィールドの値を取得
条件がTRUEとなる行だけを選択します。
任意のフィールドから、条件にマッチしたデータのみを取得するには、WHEREに条件式を指定します。
SELECT フィールド名 FROM テーブル名 WHERE 条件式;
条件式は検索フィールドのフィールド名と演算子、関数などを使って表現します。たとえば、顧客（customer）テーブルの中から女性の名前を全て表示する場合を考えてみます。
たとえば、顧客（customer）テーブルの中から女性（sex=2）の名前（fullname）を全て表示するには次のような命令文になります。
SELECT fullname FROM customer WHERE sex=2;
顧客（customer）テーブルを設計する際、性別フィールドの値が男性の場合は数値の1、女性の場合は数値の2と定義しました。そのため、女性のみを選択したい場合は、WHEREに"sex=2"という式を指定します。WHEREの詳しい使い方は、【WHEREでレコードを検索する】を参照してください。
出力結果は下記の通りです。
+--------------------------------------+
| fullname |
+--------------------------------------+
| エラ・フィッツジェラルド |
| マーサ・リーブス |
+--------------------------------------+
2 rows in set (0.01 sec)
WHEREの詳しい情報は【WHEREでレコードを検索する】を参照してください。
GROUP BYにはWITH ROLLUP修飾子を使用できます（MySQLバージョン4.1.1以降）。
独自拡張構文と実装していない構文
