個人的にメモしておきたいWHERE等の条件集を載せてます。
否定(!=)
●countriesテーブルのCグループ以外のグループ(group_name)を全て抽出
SELECT * FROM countries WHERE group_name != "C";他にも「NOT」や「<>」を使った方法がある。
SELECT * FROM countries WHERE NOT group_name = "C";SELECT * FROM countries WHERE group_name <> "C";
範囲指定(BETWEEN)
●countriesテーブルの1~50位(ranking)の国から20位を基準に前後10位(10~30位)を抽出
10と30を含めたくない範囲指定の場合、BETWEEN句は使えない。
SELECT * FROM countries WHERE ranking BETWEEN 10 AND 30;
日時を指定する時の例
BETWEEN "2014-6-13 0:00:00" AND "2014-6-27 23:59:59";時間を指定しなかった場合は、「2014-6-27」は出力データに含まれません。
指定した行数を表示(LIMIT)
●playersテーブルの身長(height)の高い選手(name)ベスト5を出力。
SELECT name, height, weight FROM players ORDER BY height DESC LIMIT 5;
SELECT name, height, weight FROM players ORDER BY height DESC LIMIT 5, 15;LIMIT句はこのようにカンマ区切りで値を2つ指定すると、抽出するデータのスタート位置を指示することができます。
上記のように5を指定すると、6位（6行目）から表示となります。これはゼロから数えはじめ、ゼロが1行目を表すからです。
2つ目の値は前問と同じように表示する行数を表し、今回は6位～20位の15選手を抽出する必要がありますので15となります。
指定要素取得(IN)
●playersテーブルのポジション(position)が「GK」「DF」「MF」の選手を抽出
なお、NOT IN句にすれば、「GK」「DF」「MF」以外のポジションの選手を抽出できる。
SELECT * FROM players WHERE position IN("GK", "DF", "MF");
NULL取得(IS NULL)
●playersテーブルの名前(name)がNULLの選手を抽出
なお、NOT IS NULL句にすれば、NULL以外の名前の選手を抽出できる。
SELECT * FROM players WHERE name IS NULL;
曖昧検索(LIKE)
●playersテーブルの名前(name)の末尾が「太」の選手を抽出
LIKE句はワイルドカード文字という、曖昧検索を指示する記号が二種類ある。
それが「％」と「＿」
・「%」 ・・・ 0文字以上の任意の文字列
・「_」・・・ 任意の1文字
SELECT * FROM players WHERE name LIKE "%太";
『かつ』と『または』の複合(AND・OR)
●playersテーブルのポジション(position)が「FW」か「MF」の中で身長(height)170未満の選手を抽出
ただし、ANDとORを使用すること。
SELECT * FROM players WHERE height < 170 AND (position = "FW" OR position = "MF");「position = ‘FW’ OR position = ‘MF’ AND height < 170」
括弧を付けていないこのようなSQLでは、身長180cm以上のFW選手が抽出されてしまいます。
これは、SQLでは「AND演算子の方がOR演算子より先に評価される」というルールがあるためです。
つまり、上記のSQLは、「FWの選手、もしくはMFで170cm未満の選手」を抽出していることになります。
AND演算子とOR演算が1つのSQLの中にどちらも登場するケースでは必ず括弧を付けるようにするとこのような間違いを起こしにくいためオススメです。
重複データの集約(DISTINCT)
●playersテーブルのポジション(position)をそれぞれ一つの行にまとめる
SELECT DISTINCT position FROM players;
算術演算子(SELECT)
●playersテーブルの全選手の身長(height)と体重(weight)を一つのカラムにまとめる
SELECT height + weight FROM players;・「+」・・・加算
・「-」・・・減算
・「*」・・・乗算
・「/」・・・除算
・「DIV」・・・除算(整数値の返却)
・「％」・・・剰余
・「MOD」・・・剰余
SQLではWHERE句だけでなくSELECT句で算術演算子を使用することも可能です。
計算式がそのままカラム名として使用され、プログラムとSQLを組み合わせる場合にはこのようにカラム名に演算子が含まれていたり、長い名前となってしまうと都合が悪くなるため、一般的にはAS句を使用して別名を付けることが多いようです。
文字列結合(CONCAT)
●playersテーブルの選手名(name)とポジション(position)を以下の形式に沿って出力
「○○選手のポジションは’GK’です」
SELECT CONCAT(name, '選手のポジションは\'', position, '\'です') FROM players;1つ目のポイントは文字列結合です。各DBMSによって文字列結合の方法は異なりますので、注意が必要です。今回題材にしているMySQLではCONCAT関数を使用します。ちなみにOracleではMySQLと同じようにCONCAT関数も使用できますが、それ以外にも「||」パイプ2つの演算子を使用して文字列結合を行うことが可能です。他には「+」プラス演算子を使用することができるDBMSもあります。
2つ目のポイントは文字列の中に「’」シングルクォートが含まれている点です。SQLでの文字列リテラルは「シングルクォート」で括るというルールがありますので、その文字列リテラルの中に「シングルクォート」をそのまま使用することはできません。そのため、シングルクォートの中のシングルクォートには「\」円マーク/バックスラッシュをエスケープシーケンスとして記述します。
昇順・降順(ORDER BY)
●countriesテーブルの国名をランキング(ranking)の高い順に表示
SELECT * FROM countries ORDER BY ranking;SQLで降順ソートしたい場合には、「ORDER BY」句で指定したカラムの末尾に「DESC」を付ければOKです。
文字列の一部を取得(SUBSTRING)
●playersテーブルの全選手(name)の各ポジション(position)名を先頭の一文字だけ抽出
GKであれば「G」、FWであれば「F」
SELECT name, SUBSTRING(position, 1, 1) FROM players;・第二引数に、取得する文字列の開始地点
・第三引数に、取得する文字数
その他の使用方法は下記の公式サイトを参照。
文字数が長い順にソート(LENGTH)
●countriesテーブルの国名(name)を長いものから順に出力
SELECT name, LENGTH(name) as len FROM countries ORDER BY len DESC;「as」はカラム名を「len」に置き換えて表示するために使用。
文字列の長さを求めるためにはLENGTH関数（単一行関数）を使用することで可能です。
問題は「国名が長いものから順に」という指示がありますので、ORDER BY句を併用することになります。回答例のようにORDER BY句でも単一行関数を使用することができます。
そのほかの使用方法に関してはマニュアルがありますので、こちらを参照。
似たような関数にはCHAR_LENGTHというものもあります。
年月日を別の形式に変更する(DATE_FORMAT)
●playersテーブルの全選手(name)の誕生日(birth)をカラム名「birthday」として、「XXXX年XX月XX日」の形式で出力
SELECT name, DATE_FORMAT(birth, '%Y年%m月%d日') as birthday FROM players;birth列はdate型ですので、DATE_FORMAT関数（単一行関数）を使用することで出力時の書式を設定することができます。
4桁の西暦は「%Y」、2桁の月は「%m」、2桁の日は「%d」で指定します。書式を指定する第2引数は文字列で指定する必要がありますので、シングルクォートを忘れないようにしましょう。
その他の使用方法に関してはマニュアルがありますので、こちらを参照。
時差を修正する(CONVERT_TZ)
●pairingsとcountriesテーブルでブラジル（my_country_id = 1）対クロアチア（enemy_country_id = 4）戦のキックオフ時間（現地時間）を表示してください。
ただし、DB上に保持されているキックオフ時間は日本時間であるため、日本と現地の時差であるマイナス12時間をして表示すること。
表示するカラム
・キックオフ時間（現地）
・キックオフ時間（日本）
SELECT p.kickoff, DATE_FORMAT(CONVERT_TZ(p.kickoff, '+00:00', '-12:00'), '%Y-%m-%d %k:%i:%s') as kickoff_jp FROM pairings p LEFT JOIN countries c1 ON p.my_country_id = c1.id LEFT JOIN countries c2 ON p.enemy_country_id = c2.id WHERE p.my_country_id = 1 AND p.enemy_country_id = 4;対象となる日時(第一引数)に対して現在のタイムゾーン(第二引数)から別のタイムゾーン(第三引数) に変更した場合の日時を取得します。タイムゾーンの指定には ‘Asia/Tokyo’ や ‘America/New_York’ などの名前付きタイムゾーンや ‘+09:00’ や ‘-05:00’ などの UTC(協定世界時) との差で指定することができます。
今回は上記の構文でも求めることができましたが、他にも公式HPの関数リファレンスによるとぱっと見、6種類の方法がありました。
ADDTIME(), SUBTIME()
DATE_ADD(), DATE_SUB()
ADDDATE(), SUBDATE()
ADD系の関数の場合には、マイナス記号を付ける必要がありますが
使用感に関してはほとんど違いがないようです。
NULLを置換(IFNULL)
●goalsテーブルのゴール情報をすべて出力
ただし、player_idがNULLのデータはそれを「9999」と表示する。
SELECT IFNULL(player_id, 9999) as player_id, goal_time FROM goals;SQL文を作成するにあたってNULLの含まれるデータは様々な問題を引き起こします。
そこで、今回の問題のようにNULLを他のデータへ置き換えるという手法を取る場合があります。MySQLではこのような用途で使用できる単一行関数は「CASE」「IF」「IFNULL」があります。
この問題で使用するIFNULL関数の場合は、NULLデータは第2引数の値に置き換わります。
他のRDBMSでは関数名が異なる場合がありますので、注意してください。（Oracleでは「NVL」という関数になります。）
その他の使用方法に関してはマニュアルがありますので、こちらを参照。
NULLを置換(CASE)
●goalsテーブルのゴール情報をすべて出力
ただし、player_idがNULLのデータはそれを「9999」と表示する。
SELECT CASE WHEN player_id IS NULL THEN 9999 ELSE player_id END as player_id, goal_time FROM goals;この問題で使用するCASE関数の場合は、前問のIFNULL関数よりもより汎用的に表示データの条件付けや加工を行うことができます。
WHEN句で指定した条件のうちtrueである最初の条件の結果が返されます。一致する結果値がなかった場合は、ELSE のあとの結果が返され、ELSE 部分がない場合は、NULL が返されます。
その他の使用方法に関してはマニュアルがありますので、こちらを参照。
平均算(AVG)
●playersテーブルの全選手の平均身長(height)と平均体重(weight)を出力
SELECT AVG(height) as "平均身長", AVG(weight) as "平均体重" FROM players;平均値を求めるグループ関数AVGを使用する問題です。グループ関数を使用すると指定したグループ単位にデータをまとめて表示することが可能です。
playersテーブルには736行のデータが存在しますが、実行結果を見ると1行になっています。今回は全736行を1つのグループとみなしてグループ関数を使用しているので、抽出される件数も1件ということになります。
グループを1つではなく、いくつかのグループに分けたい場合には、GROUP BY句を使用します。
これは「MAX」「MIN」「SUM」も同様。
行数の取得(COUNT)
●goalsテーブルのplayer_idが714～736の選手数を出力
SELECT COUNT(player_id) FROM goals WHERE 714 <= player_id AND player_id <= 736;・COUNT(*)のように*を指定した場合は、NULLも含めたテーブルのレコード数(行数)をカウントします。
・COUNT(列名)のように列名を指定した場合は、NULLはカウントしません。NULL以外のレコード数(行数)を数えます。
COUNT関数の括弧の中には、アスタリスクもしくはカラム名を指定することができますが、一般的には回答例のようにアスタリスクとするかそのテーブルのPK（プライマリーキー）を指定するのが普通です。
場合によっては、アスタリスクも使用を控えるようにと言われるケースもあります。これは、アスタリスクに比べPKを指定する方がSQLの実行が早くなるためです。
年齢計算(TIMESTAMPDIFF)
●playersテーブルのワールドカップ開催当時（2014-06-13）の年齢(age)・誕生日(birth)・名前(name)・ポジション(position)をプレイヤー毎に年齢の降順で表示する。
SELECT birth, TIMESTAMPDIFF(YEAR, birth, "2014-06-13") as age, name, position FROM players ORDER BY age DESC;年齢はplayersテーブルのbirthカラムが誕生日になっていますので、ここから計算することができます。
MySQLの場合には日付計算用の関数（TIMESTAMPDIFF）を使用することで簡単に求めることができます。
Oracleなどの他のRDBMSでは、このような関数がない場合もあるようです。
ちなみに年齢を10歳毎に分けたい場合は、TRUNCATEやCASEを使うことでもできますが、
DIVの小数点以下を切り捨て、整数を返すという特性を利用して以下のように記述することもできます。
SELECT TIMESTAMPDIFF(YEAR, birth, "2014-06-13") DIV 10 * 10 as age, COUNT(id) as player_count FROM players GROUP BY age;5歳毎に分けたい時は5で割って5を掛けましょう。
これはFLOOR関数でも求められます。
内部結合(INNER JOIN)
●playersとcountriesテーブルを内部結合し、国名(name)、選手名(name)、背番号(uniform_num)を出力
SELECT c.name, p.name, p.uniform_num FROM players as p INNER JOIN countries as c ON p.country_id = c.id;JOINを用いてテーブル同士を内部結合を行う問題です。内部結合を行うことで異なる名前のテーブルをまとめて表示することが可能です。
内部結合をする際には、メインとなるテーブルをFROM句に指定し、それに結合するテーブルをJOIN句に指定します。メインテーブルの外部キーとサブテーブルのプライマリーキーを結合条件としてON句に指定します。
なお、別名を使わない場合はテーブル名.カラム名の形になります。
INNER JOINはJOINと省略することも可能です。
複数の内部結合をした例
SELECT c.name, p.name, g.goal_time FROM players as p INNER JOIN countries as c ON p.country_id = c.id INNER JOIN goals as g ON p.id = g.player_id;
左側外部結合・右側外部結合(LEFT JOIN・RIGHT JOIN)
●playersとgoalsテーブルを内部結合し、ゴール時間(goal_time)と選手名(name)を出力
ただし、左側外部結合・右側外部結合それぞれを使用してオウンゴール（player_idがNULL）も表示すること
・左側外部結合
SELECT g.goal_time, p.uniform_num, p.position, p.name FROM goals as g LEFT JOIN players as p ON p.id = g.player_id;・右側外部結合
SELECT g.goal_time, p.uniform_num, p.position, p.name FROM players as p RIGHT JOIN goals as g ON p.id = g.player_id;JOIN句を用いてテーブルの外部結合を行う問題です。FROM句とJOIN句に記述するテーブルをどちらにするかは特に守らなくてはいけないルールがあるわけではありません。
しかし基本的には、抽出されるデータの特性から結合する2つのテーブルをメインとなるテーブルとサブテーブルに分類して、メインテーブルをFROM句に記述、サブテーブルをJOIN句へ記述することが多いようです。
今回の問題では、「全ての試合のゴール時間」すなわちgoalsテーブルのNULLデータも含めて抽出する必要があるため、ゴール時間を管理しているgoalsテーブルをメインテーブルと考えた方がベターです。
そのため本来はgoalsテーブルをFROM句へ指定するべきですが、右側外部結合ではplayersテーブルがFROM句となります。
また、必須ではないですが、テーブル結合を行う際はテーブルに別名を付けのが一般的ですので忘れずに行いましょう。
複数の外部結合をした例
SELECT c.name as "country_name", g.goal_time, p.position, p.name as player_name FROM goals as g LEFT JOIN players as p ON g.player_id = p.id LEFT JOIN countries as c ON p.country_id = c.id;3つのテーブルを外部結合する問題です。オウンゴールの場合には、goalsテーブルのplayer_idがNULLとなっているため、playerテーブルを結合するときもcountriesテーブルを結合するときも左側外部結合（LEFT JOIN）を使う必要があります。実際にシステム開発でSQLを作る場合にもこのように多数のテーブルが外部結合されているケースがほとんどです。複雑なSQLになるほどFROM句に「メインとなるテーブルを記述する」というルールを守っておくことが、後からSQLを修正するときや他の人のソースを修正するときに威力を発揮します。
自己結合(JOIN・LEFT・RIGHT)
●pairingsテーブルとcountriesテーブルで、全ての試合のキックオフ時間(kickoff)と自国(my_country)・対戦国の国名(enemy_country)を出力。
SELECT p.kickoff, c01.name as my_country, c02.name as enemy_country FROM pairings as p INNER JOIN countries as c01 ON p.my_country_id = c01.id INNER JOIN countries as c02 ON p.enemy_country_id = c02.id;厳密には自己結合ではありませんが、このように同じテーブルを2度以上つかって結合することもできます。
pairingsテーブルのmy_country_idとenemy_country_idはどちらもcountriesテーブルのPKを格納している外部キーになります。そのため今回のようなデータを抽出する場合には、my_countryを表示するためとenemy_countryを表示するために2つのcountriesテーブルを別々にJOINする必要があります。
副問合せ(SELECT)
●playersとgoalsテーブルで、全てのゴール時間(goal_time)と得点を上げたプレイヤー名(name)を出力。
オウンゴールは表示しないでください。ただし、結合は使わずに副問合せを用いてください。
SELECT g.id, g.goal_time, (SELECT p.name FROM players as p WHERE p.id = g.player_id) as name FROM goals as g WHERE g.player_id IS NOT NULL;SELECT句で副問合せを使用する問題です。副問合せはこのようなSELECT句だけでなくWHERE句やFROM句に記述することもできますが、このようにSELECT句の中にSELECT句を記述する場合の副問合せでは、埋め込まれているSELECT文が返す行数は必ず1行にする必要があります。（単一行副問合せといいます。）
複数行を返すようなSQLの場合にはエラーとなってしまいますので、注意しましょう。
内部結合を使った場合
SELECT g.id, g.goal_time, p.name FROM goals as g INNER JOIN players as p ON g.player_id = p.id;同じ設問ですが、今度は結合を使用する問題です。
実は結合を用いても副問合せを用いても同じことができるケースは珍しくありません。どちらを使用した方がいいかは一概にいうことは難しいですが、基本的には結合を用いた方がベターではないかと筆者は考えます。副問合せを乱用することでSQLの実行速度が低下してしまうからです。
だからといって副問合せを全く使用しないというわけではなく、使いどころを見極めれば非常に強力な構文です。是非、結合も副問合せもどちらもマスターしておきたいところです。
また、前問ではオウンゴールを表示しないという要件を満たすためにWHERE句にIS NOT NULL条件を付けていましたが、回答例のように内部結合を使用すればこの条件は削除することが可能になります。
●playersテーブルの各ポジション(poisition)の最大身長と、その選手名(name)、所属クラブ(club)を表示してください。
ただし、SELECT句に副問合せを使用してください。
SELECT p1.position, MAX(p1.height) as 最大身長, (SELECT p2.name FROM players as p2 WHERE MAX(p1.height) = p2.height AND p1.position = p2.position) as 名前 FROM players as p1 GROUP BY p1.position;副問合せを、FROM句ではなくSLECT句に使用するパターンです。
このパターンで注意しなくてはならないのは、playersテーブル内のデータ構造です。今回使用しているサンプルデータでは問題なく実行することが可能ですが、仮に同じポジションで同じ最大身長の選手が2名以上いる場合には、実行するとエラーとなってしまいます。
SELECT句に副問合せを記述する場合には、その副問合せでは1件しかデータを返さないようにしないといけません。複数件のデータが返るようなデータ構造ならば、前問のようにFROM句での副問合せを使用しましょう。
副問合せ(FROM)
●playersテーブルの各ポジションごと(GK、FWなど)に最大身長と、その選手名(name)、所属クラブ(club)を出力。
ただし、FROM句に副問合せを使用してください。
左側外部結合の場合
SELECT p1.position, p1.最大身長, p2.name, p2.club FROM (SELECT position, MAX(height) as 最大身長 FROM players GROUP BY position) as p1 LEFT JOIN players as p2 ON p1.最大身長 = p2.height AND p1.position = p2.position;FROM句に副問合せを使用する代表的なケースといっていいかもしれません。FROM句に記述したSELECT文があたかもテーブルのような扱いになります。
SQLを覚えたばかりの方がよくしてしまうのが以下のような間違いです。
SELECT position, MAX(height) as 最大身長, name, club FROM players GROUP BY position;これは、「グループ化しているとき（GROUP BY句を記述しているとき）は、SELECT句にはグループ関数を用いた列かGROUP BY句で指定した列しか記述できない」というルールに反してしまっています。
（SELECT句にname、clubがあるのが誤りです。）
MySQLではこのような誤ったSQLもエラーにならず実行できてしまいますが、構文間違いとなりますので注意しましょう。
内部結合の場合(構文)
SELECT (取得したい列) FROM テーブルA INNER JOIN (SELECT グループ列, MAX(最大値を求める列) as 最大値 FROM テーブルA GROUP BY グループ列) as テーブルB ON テーブルA.グループ列 = テーブルB.グループ列 AND テーブルA.最大値を求める列 = テーブルB.最大値;内部結合の場合(例)
SELECT p2.position, p2.最大身長, p1.name, p1.club FROM players as p1 INNER JOIN (SELECT position, MAX(height) as 最大身長 FROM players GROUP BY position) as p2 ON p1.position = p2.position AND p1.height = p2.最大身長;
副問合せ(WHERE)
●playersテーブルの全選手の平均身長より低い選手をすべて出力。
表示する列は、背番号(uniform_num)、ポジション(position)、名前(name)、身長(height)としてください。
SELECT uniform_num, position, name, height FROM players WHERE height < (SELECT AVG(height) FROM players);平均身長を求めるためにはグループ関数AVGを使用する必要があります。この結果を使ってWHERE句に条件を設定することで目的を達成することができます。このようにWHERE句に副問合せを使用する場合には、内側のSELECT句の結果を1行だけ返すようにする必要があります。なお、条件式に<や=ではなく、IN句を用いていれば複数行返すようなSELECT句でも問題ありません。
グループ関数の抽出条件(HAVING)
●countriesテーブルの各グループ(group_name)の最上位と最下位を表示し、その差が50より大きいグループを出力。
SELECT group_name, MAX(ranking), MIN(ranking) FROM countries GROUP BY group_name HAVING MAX(ranking) - MIN(ranking) >= 50;グループ関数（MAXやMIN）の結果をつかって抽出条件を作りたい場合は、WHERE句ではなくHAVING句に記述します。WHERE句に記述するとエラーとなってしまいますので、注意してください。
結果の統合(UNION・UNION ALL)
UNIONの場合
●playersテーブルの1980年生まれと、1981年生まれの選手が何人いるか調べる。
ただし、日付関数は使用せず、UNION句を使用してください。
SELECT 1980 as 誕生年, COUNT(id) FROM players WHERE birth BETWEEN "1980-1-1" AND "1980-12-31"
UNION
SELECT 1981 as 誕生年, COUNT(id) FROM players WHERE birth BETWEEN "1981-1-1" AND "1981-12-31";日付関数を使用すると同じことが可能かもしれませんが、今回はUNION句を使用してみました。UNION句を使うと、2つのSELECTの結果を行方向（縦）につなげることができます。注意点としては、2つのSELECT句は同じ列数を返す必要があることです。それほど使用頻度の多いものではありません。
UNION ALLの場合
●playersテーブルの身長(height)が195㎝より大きいか、体重(weight)が95kgより大きい選手(name)を出力。
ただし、以下の画像のように、どちらの条件にも合致する場合には2件分のデータとして抽出してください。また、結果はidの昇順としてください。
SELECT id, position, name, height, weight FROM players WHERE 195 < height
UNION ALL
SELECT id, position, name, height, weight FROM players WHERE 95 < weight ORDER BY id;「どちらの条件にも合致する場合には2件分のデータとして抽出」という指示があるため、WHERE句で条件を設定する方法ではうまく抽出することができません。そこでUNION句を使用するのですが、前問のようにUNION ALL句ではなくUNION句を使用してしまうと、相変わらず2件として抽出することができません。これは、UNION句では同じ行は自動的にマージ（2行が1行にまとまってしまう）されてしまうためです。同じ行があったとしてもマージせず2件分として抽出したい場合にはUNION ALL句を使います。
注意事項としては、UNION句と同じように2つのSELECTの列数を同じにしておく必要があります。
仮想テーブル(View)
Viewとは
Viewの作成
Viewの変更・更新
Viewの削除
