はじめに
本記事はサーバーサイドエンジニアの筆者が、SQLの技術書で学んだ内容と、実際に実務で使用した経験のアウトプットとしての内容をまとめたものになります。
内容はSQLの基本構文であるSELECTを始め、INSERT、UPDATE、DELETEなどを使いデータの検索、追加、更新、削除をする方法について解説していきます。
本記事を読めばデータの検索、追加、更新、削除については（ある程度）理解していただける内容になっています。（と思いたい。）
（サーバーサイドエンジニアの場合）実務に入るとSQLを扱うことは必須となるので、「技術書を読む時間はないけど、SQLはある程度理解しておきたい」という方や、「SQLとはなんぞや」というプログラミング初学者の方や、エンジニア転職を目指してポートフォリオを作成中の方、これから実務に入る方などには読んでいただき、理解を深めていただければと思います。
テーブル
今回解説に使うSoccerPlayerテーブルを用意しました。
※サッカー詳しく知らない方はすいません(^^;ですが、詳しくなくてもこれから先に支障はないのでご安心を。
| player_id | player_name | nationality | clubteam | position | kikiashi | age |
|---|---|---|---|---|---|---|
| 1 | メッシ | アルゼンチン | バルセロナ | FW | 左 | 33 |
| 2 | クリスティアーノ・ロナウド | ポルトガル | ユベントス | FW | 右 | 36 |
| 3 | ネイマール | ブラジル | パリSG | FW | 右 | 29 |
| 4 | ポール・ポグバ | フランス | マンチェスターU | MF | NULL | 28 |
| 5 | イニエスタ | スペイン | ヴィッセル神戸 | MF | 右 | 36 |
| 6 | イブラヒモビッチ | スウェーデン | ACミラン | FW | 右 | 39 |
| 7 | セルヒオ・ラモス | スペイン | レアル・マドリード | DF | 右 | 34 |
| 8 | マッツ・フンメルス | ドイツ | ドルトムント | DF | NULL | 32 |
| 9 | アリソン | ブラジル | リバプール | GK | NULL | 28 |
| 10 | テア・シュテーゲン | ドイツ | バルセロナ | GK | NULL | 28 |
| 11 | トニ・クロース | ドイツ | レアル・マドリード | MF | NULL | 31 |
※ageは2021年3月21日時点の年齢です
SELECT文
SELECTは出力する際の列を選択する文です。
列を選択して出力する場合
SELECT player_name, nationality ←出力する列名を記述。 複数ある場合はカンマ（,)で区切り記述する。
FROM SoccerPlayer; ←テーブル名。 必ず記述する。
実行結果
| player_name | nationality |
|---|---|
| メッシ | アルゼンチン |
| クリスティアーノロナウド | ポルトガル |
| ネイマール | ブラジル |
| ポール・ポグバ | フランス |
| イニエスタ | スペイン |
| イブラヒモビッチ | スウェーデン |
| セルヒオ・ラモス | スペイン |
| マッツ・フンメルス | ドイツ |
| アリソン | ブラジル |
| テア・シュテーゲン | ドイツ |
| トニ・クロース | ドイツ |
全ての列を出力する場合
SELECT * ←アスタリスク（＊）は全列を意味する。
FROM SoccerPlayer;
実行結果
| player_id | player_name | nationality | clubteam | position | kikiashi | age |
|---|---|---|---|---|---|---|
| 1 | メッシ | アルゼンチン | バルセロナ | FW | 左 | 33 |
| 2 | クリスティアーノ・ロナウド | ポルトガル | ユベントス | FW | 右 | 36 |
| 3 | ネイマール | ブラジル | パリSG | FW | 右 | 29 |
| 4 | ポール・ポグバ | フランス | マンチェスターU | MF | NULL | 28 |
| 5 | イニエスタ | スペイン | ヴィッセル神戸 | MF | 右 | 36 |
| 6 | イブラヒモビッチ | スウェーデン | ACミラン | FW | 右 | 39 |
| 7 | セルヒオ・ラモス | スペイン | レアル・マドリード | DF | 右 | 34 |
| 8 | マッツ・フンメルス | ドイツ | ドルトムント | DF | NULL | 32 |
| 9 | アリソン | ブラジル | リバプール | GK | NULL | 28 |
| 10 | テア・シュテーゲン | ドイツ | バルセロナ | GK | NULL | 28 |
| 11 | トニ・クロース | ドイツ | レアル・マドリード | MF | NULL | 31 |
補足①
SELECTや FROMなどの構文を構成する単語を句（く）と言います。(後ほど出てくるWHERE GROUP BY ORDER BYなども)
この句は小文字で入力してもエラーになりません。
ですが、大文字で入力するのが慣習となっているようです。
補足②
句ごとに改行を入れて記述するのが慣習となっているようです。
ですが、
SELECT * FROM SoccerPlayer;
のように記述してもエラーにはなりません。
しかし、構文が長くなってくると改行なしでは読みらづらくなるので、こちらも慣習通りに改行を入れて記述する方がよいでしょう。
補足③
文末には必ずセミコロン( ; )を付けます。
セミコロンを付けないとEnterを押しても改行されてしまい、実行できません。
SELECT *
FROM SoccerPlayer
-> ←セミコロン(;)がないと改行されてしまい実行できない。
->
AS
ASは列に別名をつけることができるキーワードです。
SELECT player_name AS namae, nationality AS kokuseki, clubteam AS syozokukurabu
FROM SoccerPlayer;
実行結果
| namae | kokuseki | syozokukurabu |
|---|---|---|
| メッシ | アルゼンチン | バルセロナ |
| クリスティアーノ・ロナウド | ポルトガル | ユベントス |
| ネイマール | ブラジル | パリSG |
| ポール・ポグバ | フランス | マンチェスターU |
| イニエスタ | スペイン | ヴィッセル神戸 |
| イブラヒモビッチ | スウェーデン | ACミラン |
| セルヒオ・ラモス | スペイン | レアル・マドリード |
| マッツ・フンメルス | ドイツ | ドルトムント |
| アリソン | ブラジル | リバプール |
| テア・シュテーゲン | ドイツ | バルセロナ |
| トニ・クロース | ドイツ | レアル・マドリード |
別名には日本語を使うこともできます。その場合は別名をダブルクォーテーション( " )で囲みます。
シングルクォーテーション( ' )ではないのでご注意下さい。
SELECT player_name AS "名前", nationality AS "国籍", clubteam AS "所属クラブ"
FROM SoccerPlayer;
実行結果
| 名前 | 国籍 | 所属クラブ |
|---|---|---|
| メッシ | アルゼンチン | バルセロナ |
| クリスティアーノ・ロナウド | ポルトガル | ユベントス |
| ネイマール | ブラジル | パリSG |
| ポール・ポグバ | フランス | マンチェスターU |
| イニエスタ | スペイン | ヴィッセル神戸 |
| イブラヒモビッチ | スウェーデン | ACミラン |
| セルヒオ・ラモス | スペイン | レアル・マドリード |
| マッツ・フンメルス | ドイツ | ドルトムント |
| アリソン | ブラジル | リバプール |
| テア・シュテーゲン | ドイツ | バルセロナ |
| トニ・クロース | ドイツ | レアル・マドリード |
DISTINCT
DISTINCTは重複行を省くキーワードです。
たとえば下記の場合、ブラジル、スペイン、ドイツが重複しています。
SELECT nationality
FROM SoccerPlayer;
実行結果
| nationality |
|---|
| アルゼンチン |
| ポルトガル |
| ブラジル |
| フランス |
| スペイン |
| スウェーデン |
| スペイン |
| ドイツ |
| ブラジル |
| ドイツ |
| ドイツ |
この重複を省いたデータを出力する際に使うのがDISTINCTというキーワードです。
SELECT DISTINCT nationality
FROM SoccerPlayer;
実行結果
| nationality |
|---|
| アルゼンチン |
| ポルトガル |
| ブラジル |
| フランス |
| スペイン |
| スウェーデン |
| ドイツ |
NULLがある場合はNULLも一種類のデータとして扱われ、複数行にNULLがある場合は同じく一つのNULLにまとめられます。
SELECT DISTINCT kikiashi AS "利き足"
FROM SoccerPlayer;
実行結果
| 利き足 |
|---|
| 左 |
| 右 |
| NULL |
WHERE
WHEREは出力する行に条件を指定することができる句です。
たとえば、ポジションがFWの選手だけを出力したい場合このように記述します。
SELECT player_name, position
FROM SoccerPlayer
WHERE position = 'FW'; ←指定する条件はシングルクォーテーション(')で囲む。
実行結果
| player_name | position |
|---|---|
| メッシ | FW |
| クリスティアーノ・ロナウド | FW |
| ネイマール | FW |
| イブラヒモビッチ | FW |
WHERE句で指定する列名は、SELECTで指定しなくても実行できます。
SELECT player_name
FROM SoccerPlayer
WHERE position = 'FW';
実行結果
| player_name |
|---|
| メッシ |
| クリスティアーノ・ロナウド |
| ネイマール |
| イブラヒモビッチ |
WHERE句による条件指定では比較演算子も使うことができます。
※比較演算子の詳しい説明は本記事では割愛します。
たとえば、30歳以上の選手を出力したい場合はこのように記述します。
SELECT player_name, age
FROM SoccerPlayer
WHERE age >= '30';
実行結果
| player_name | age |
|---|---|
| メッシ | 33 |
| クリスティアーノ・ロナウド | 36 |
| イニエスタ | 36 |
| イブラヒモビッチ | 39 |
| セルヒオ・ラモス | 34 |
| マッツ・フンメルス | 32 |
| トニ・クロース | 31 |
AND演算子
AND演算子は複数条件を指定する場合に使います。
たとえば、30歳以上のドイツ国籍の選手を出力する場合はこのように記述します。
SELECT player_name, nationality, age
FROM SoccerPlayer
WHERE age >= '30'
AND nationality = 'ドイツ';
実行結果
| player_name | nationality | age |
|---|---|---|
| マッツ・フンメルス | ドイツ | 32 |
| トニ・クロース | ドイツ | 31 |
OR演算子
OR演算子も複数条件を指定する場合に使います。
しかし先ほどのAND演算子とは違い、どちらかでも条件を満たせば出力されます。
説明を聞いてもわかりにくいと思うので実際に例を見てみましょう。
今回は30歳以上、もしくはドイツ国籍の選手を出力します。
SELECT player_name, nationality, age
FROM SoccerPlayer
WHERE age >= '30'
OR nationality = 'ドイツ';
実行結果
| player_name | nationality | age |
|---|---|---|
| メッシ | アルゼンチン | 33 |
| クリスティアーノ・ロナウド | ポルトガル | 36 |
| イニエスタ | スペイン | 36 |
| イブラヒモビッチ | スウェーデン | 39 |
| セルヒオ・ラモス | スペイン | 34 |
| マッツ・フンメルス | ドイツ | 32 |
| テア・シュテーゲン | ドイツ | 28 |
| トニ・クロース | ドイツ | 31 |
このようにOR演算子はどちらか片方の条件を満たせば出力されます。
AND演算子とOR演算子の組み合わせ
さて、次はANDとORを組み合わせた少し複雑な検索条件をみてみましょう。
今回は、「利き足が右」且つ「ポジションがMFまたはDF」という条件で出力してみたいと思います。
SELECT player_name, position, kikiashi
FROM SoccerPlayer
WHERE kikiashi = '右'
AND position = 'MF'
OR position = 'DF';
実行結果
| player_name | position | kikiashi |
|---|---|---|
| イニエスタ | MF | 右 |
| セルヒオ・ラモス | DF | 右 |
| マッツ・フンメルス | DF | NULL |
さて、困ったことに利き足は右ではない、マッツ・フンメルスが出力されてしまいました。
なぜこのような結果になってしまったかというと、OR演算子よりAND演算子の方が優先されるからです。
なので先ほどの構文では、「利き足が右、且つポジションがMF」または「ポジションがDF」という解釈をされてしまいました。
なのでこのようにOR演算子を優先させたい場合は下記のようにカッコ（）で囲みます。
SELECT player_name, position, kikiashi
FROM SoccerPlayer
WHERE kikiashi = '右'
AND (position = 'MF'
OR position = 'DF'); ←OR演算子とその両辺をカッコ（）で囲む
実行結果
| player_name | position | kikiashi |
|---|---|---|
| イニエスタ | MF | 右 |
| セルヒオ・ラモス | DF | 右 |
このようにカッコ（）で囲むことでOR演算子を優先させて出力させることができます。
LIKE
LIKEは文字列の部分一致検索を行う時に述語です。
部分一致には大きく分けて前方一致、中間一致、後方一致の3種類があります。
順番に解説します。
- 前方一致
たとえばplayer_nameが「イ」から始まる選手を検索する場合、下記のように書きます。
SELECT *
FROM SoccerPlayer
WHERE player_name LIKE 'イ%'; ←検索したい文字列をシングルクォーテーション(')で囲む
実行結果
| player_id | player_name | nationality | clubteam | position | kikiashi | age |
|---|---|---|---|---|---|---|
| 5 | イニエスタ | スペイン | ヴィッセル神戸 | MF | 右 | 36 |
| 6 | イブラヒモビッチ | スウェーデン | ACミラン | FW | 右 | 39 |
「%」は「0文字以上の任意の文字列」を意味する特殊な記号です。
前方一致の場合は検索する文字列の後ろに%を書きます。
- 中間一致
たとえばplayer_nameに「イ」を含む選手を検索する場合、下記のように書きます。
SELECT *
FROM SoccerPlayer
WHERE player_name LIKE '%イ%';
実行結果
| player_id | player_name | nationality | clubteam | position | kikiashi | age |
|---|---|---|---|---|---|---|
| 2 | クリスティアーノ・ロナウド | ポルトガル | ユベントス | FW | 右 | 36 |
| 3 | ネイマール | ブラジル | パリSG | FW | 右 | 29 |
| 5 | イニエスタ | スペイン | ヴィッセル神戸 | MF | 右 | 36 |
| 6 | イブラヒモビッチ | スウェーデン | ACミラン | FW | 右 | 39 |
中間一致の場合は検索する文字列の前後に%を書きます。
なお、「イ」で検索すると「ィ」もヒットするようです。（予想外でした 笑）
- 後方一致
今回はplayer_nameが「イ」で終わる選手はいないので、「ス」で終わる選手を検索します。 その場合は下記のように書きます。
SELECT *
FROM SoccerPlayer
WHERE player_name LIKE '%ス';
実行結果
| player_id | player_name | nationality | clubteam | position | kikiashi | age |
|---|---|---|---|---|---|---|
| 7 | セルヒオ・ラモス | スペイン | レアル・マドリード | DF | 右 | 34 |
| 8 | マッツ・フンメルス | ドイツ | ドルトムント | DF | NULL | 32 |
| 11 | トニ・クロース | ドイツ | レアル・マドリード | MF | NULL | 31 |
後方一致の場合は検索する文字列の前に%を書きます。
BETWEEN
BETWEENは範囲検索を行う述語です。
たとえば年齢が31~34歳の選手を検索する時は下記のように書きます。
SELECT *
FROM SoccerPlayer
WHERE age BETWEEN 31 AND 34;
実行結果
| player_id | player_name | nationality | clubteam | position | kikiashi | age |
|---|---|---|---|---|---|---|
| 1 | メッシ | アルゼンチン | バルセロナ | FW | 左 | 33 |
| 7 | セルヒオ・ラモス | スペイン | レアル・マドリード | DF | 右 | 34 |
| 8 | マッツ・フンメルス | ドイツ | ドルトムント | DF | NULL | 32 |
| 11 | トニ・クロース | ドイツ | レアル・マドリード | MF | NULL | 31 |
結果から見て分かるとおり、範囲指定に記載した数字を含めての出力になります。
IS NULL / IS NOT NULL
ある列がNULLの行を選択するためには=を使うことはできません。
SELECT *
FROM SoccerPlayer
WHERE kikiashi = NULL; ← = NULLは使えないのでエラーになる
このようにNULLを指定して検索するときに使うのが IS NULLという述語になります。
早速例をみてみましょう。
SELECT *
FROM SoccerPlayer
WHERE kikiashi IS NULL;
実行結果
| player_id | player_name | nationality | clubteam | position | kikiashi | age |
|---|---|---|---|---|---|---|
| 4 | ポール・ポグバ | フランス | マンチェスターU | MF | NULL | 28 |
| 8 | マッツ・フンメルス | ドイツ | ドルトムント | DF | NULL | 32 |
| 9 | アリソン | ブラジル | リバプール | GK | NULL | 28 |
| 10 | テア・シュテーゲン | ドイツ | バルセロナ | GK | NULL | 28 |
| 11 | トニ・クロース | ドイツ | レアル・マドリード | MF | NULL | 31 |
このようにkikiashiがNULLの選手を検索できました。
今度は逆に、kikiashiがNULLではない選手を検索してみたいと思います。
その場合、IS NOT NULLという述語を使います。
SELECT *
FROM SoccerPlayer
WHERE kikiashi IS NOT NULL;
実行結果
| player_id | player_name | nationality | clubteam | position | kikiashi | age |
|---|---|---|---|---|---|---|
| 1 | メッシ | アルゼンチン | バルセロナ | FW | 左 | 33 |
| 2 | クリスティアーノ・ロナウド | ポルトガル | ユベントス | FW | 右 | 36 |
| 3 | ネイマール | ブラジル | パリSG | FW | 右 | 29 |
| 5 | イニエスタ | スペイン | ヴィッセル神戸 | MF | 右 | 36 |
| 6 | イブラヒモビッチ | スウェーデン | ACミラン | FW | 右 | 39 |
| 7 | セルヒオ・ラモス | スペイン | レアル・マドリード | DF | 右 | 34 |
このようにkikiashiがNULLではない選手を検索することができました。
IN / NOT IN
positionがGK以外の選手を検索する場合、前述したOR演算子を使えば検索できます。
SELECT *
FROM SoccerPlayer
WHERE position = 'FW'
OR position = 'MF'
OR position = 'DF';
実行結果
| player_id | player_name | nationality | clubteam | position | kikiashi | age |
|---|---|---|---|---|---|---|
| 1 | メッシ | アルゼンチン | バルセロナ | FW | 左 | 33 |
| 2 | クリスティアーノ・ロナウド | ポルトガル | ユベントス | FW | 右 | 36 |
| 3 | ネイマール | ブラジル | パリSG | FW | 右 | 29 |
| 4 | ポール・ポグバ | フランス | マンチェスターU | MF | NULL | 28 |
| 5 | イニエスタ | スペイン | ヴィッセル神戸 | MF | 右 | 36 |
| 6 | イブラヒモビッチ | スウェーデン | ACミラン | FW | 右 | 39 |
| 7 | セルヒオ・ラモス | スペイン | レアル・マドリード | DF | 右 | 34 |
| 8 | マッツ・フンメルス | ドイツ | ドルトムント | DF | NULL | 32 |
| 11 | トニ・クロース | ドイツ | レアル・マドリード | MF | NULL | 31 |
確かにGK以外の選手を検索できましたが、書き方が冗長ですね。
そんな時に使えるのが、IN述語です！
早速書き方をみてみましょう。
SELECT *
FROM SoccerPlayer
WHERE position IN ('FW', 'MF', 'DF'); ← 文字列の場合はシングルクォーテーション(')で囲む
実行結果
| player_id | player_name | nationality | clubteam | position | kikiashi | age |
|---|---|---|---|---|---|---|
| 1 | メッシ | アルゼンチン | バルセロナ | FW | 左 | 33 |
| 2 | クリスティアーノ・ロナウド | ポルトガル | ユベントス | FW | 右 | 36 |
| 3 | ネイマール | ブラジル | パリSG | FW | 右 | 29 |
| 4 | ポール・ポグバ | フランス | マンチェスターU | MF | NULL | 28 |
| 5 | イニエスタ | スペイン | ヴィッセル神戸 | MF | 右 | 36 |
| 6 | イブラヒモビッチ | スウェーデン | ACミラン | FW | 右 | 39 |
| 7 | セルヒオ・ラモス | スペイン | レアル・マドリード | DF | 右 | 34 |
| 8 | マッツ・フンメルス | ドイツ | ドルトムント | DF | NULL | 32 |
| 11 | トニ・クロース | ドイツ | レアル・マドリード | MF | NULL | 31 |
先ほどのOR演算子を使った書き方に比べて短くなり、パッとみてわかりやすいですね。
ちなみにINのカッコ（）の中に書く値は、文字列であれば、シングルクォーテーションで囲み、数値(データ型がINTEGER)であれば囲む必要はありません。
INとは逆に、NOT INという述語があります。
これは説明しなくてもどういった述語なのか予想はついていると思いますので、例をみてみましょう。
たとえば28歳、34歳、36歳以外の選手を検索する場合下記のように書きます。
SELECT *
FROM SoccerPlayer
WHERE age NOT IN (28, 34, 36); ←検索するのが数値であり、且つINTEGER型なのでシングルクォーテーション(')はいらない
実行結果
| player_id | player_name | nationality | clubteam | position | kikiashi | age |
|---|---|---|---|---|---|---|
| 1 | メッシ | アルゼンチン | バルセロナ | FW | 左 | 33 |
| 3 | ネイマール | ブラジル | パリSG | FW | 右 | 29 |
| 6 | イブラヒモビッチ | スウェーデン | ACミラン | FW | 右 | 39 |
| 8 | マッツ・フンメルス | ドイツ | ドルトムント | DF | NULL | 32 |
| 11 | トニ・クロース | ドイツ | レアル・マドリード | MF | NULL | 31 |
28歳、34歳、36歳以外の選手が検索されています。
検索対象（検索対象外）の値が複数ある場合はOR演算子よりIN述語（NOT IN述語）を使うことをおすすめします。
LIMIT
LIMITは出力する行数を制限する句になります。
たとえば、上から5行のみを出力するときは下記のように書きます。
SELECT *
FROM SoccerPlayer
LIMIT 5;
実行結果
| player_id | player_name | nationality | clubteam | position | kikiashi | age |
|---|---|---|---|---|---|---|
| 1 | メッシ | アルゼンチン | バルセロナ | FW | 左 | 33 |
| 2 | クリスティアーノ・ロナウド | ポルトガル | ユベントス | FW | 右 | 36 |
| 3 | ネイマール | ブラジル | パリSG | FW | 右 | 29 |
| 4 | ポール・ポグバ | フランス | マンチェスターU | MF | NULL | 28 |
| 5 | イニエスタ | スペイン | ヴィッセル神戸 | MF | 右 | 36 |
また、○行目のデータから△行を出力するといった指定もできます。
たとえば、3行目のネイマールから5行出力する時は下記のように書きます。
SELECT *
FROM SoccerPlayer
LIMIT 2, 5; ←行数は0からのカウントなので、3行目は２になる。 [出力開始の行], [出力する行数] といった形で書く
実行結果
| player_id | player_name | nationality | clubteam | position | kikiashi | age |
|---|---|---|---|---|---|---|
| 3 | ネイマール | ブラジル | パリSG | FW | 右 | 29 |
| 4 | ポール・ポグバ | フランス | マンチェスターU | MF | NULL | 28 |
| 5 | イニエスタ | スペイン | ヴィッセル神戸 | MF | 右 | 36 |
| 6 | イブラヒモビッチ | スウェーデン | ACミラン | FW | 右 | 39 |
| 7 | セルヒオ・ラモス | スペイン | レアル・マドリード | DF | 右 | 34 |
実務で実際に使うデータベースはデータが数千、数万とあることも珍しくありません。
そんな数千、数万のデータを全て出力しようとすると、出力するのに数十分、数時間かかってしまいます。
そんな時にLIMIT句を使用し、出力行数を制限します。
COUNT関数
COUNTとは、テーブルの行数を数える関数になります。
早速例をみていきましょう。
SELECT COUNT(*) ←カッコ（）の中が引数。 アスタリスクは全列を表している。
FROM SoccerPlayer;
実行結果
| COUNT(*) |
|---|
| 11 |
COUNT関数ではNULLは除外されて出力されされます。
SELECT COUNT(kikiashi)
FROM SoccerPlayer;
実行結果
| COUNT(kikiashi) |
|---|
| 6 |
また、先ほど紹介した重複を省くDISTINCTと組み合わせて使うこともできます。
SELECT COUNT(DISTINCT nationality) ←DISTINCTはカッコ（）の中に書く
-> FROM SoccerPlayer;
実行結果
| COUNT(DISTINCT nationality) |
|---|
| 7 |
SUM関数
SUMとは数値の合計を出力する関数になります。
SELECT SUM(age) ←引数に取れるのは数値を格納している列名のみ
FROM SoccerPlayer;
実行結果
| SUM(age) |
|---|
| 354 |
MAX関数 / MIN関数
MAXは最大値を求める、MINは最小値を求める関数になります。
SELECT MAX(age), MIN(age)
FROM SoccerPlayer;
実行結果
| MAX(age) | MIN(age) |
|---|---|
| 39 | 28 |
AVG関数
AVGは平均値を求める関数になります。
SELECT AVG(age)
FROM SoccerPlayer;
実行結果
| AVG(age) |
|---|
| 32.1818 |
GROUP BY
GROUP BYは、行をグループ分けして出力する句になります。
文章だけの説明ではわかりにくいと思うので、実際に例をみてみましょう！
SELECT nationality, COUNT(*)
FROM SoccerPlayer
GROUP BY nationality;
実行結果
| nationality | COUNT(*) |
|---|---|
| アルゼンチン | 1 |
| ポルトガル | 1 |
| ブラジル | 2 |
| フランス | 1 |
| スペイン | 2 |
| スウェーデン | 1 |
| ドイツ | 3 |
このようにGROUP BY句はテーブルを切り分け、グループ化します。
GROUP BY句に指定する列のことを集約キーやグループ化列と呼びます。
それでは次にkikiashiを集約キーとしてグループ化してみます。
SELECT kikiashi AS "利き足", COUNT(*)
FROM SoccerPlayer
GROUP BY kikiashi;
実行結果
| 利き足 | COUNT(*) |
|---|---|
| 左 | 1 |
| 右 | 5 |
| NULL | 5 |
今回は、集約キーにNULLが含まれていますが、上記のようにNULLも一つのグループに分類されます。
GROUP BY句を使うSELECT文でも、WHERE句の併用はできます。
その場合下記のように書きます。
SELECT nationality, COUNT(*)
FROM SoccerPlayer
WHERE position = 'MF'
GROUP BY nationality;
実行結果
| nationality | COUNT(*) |
|---|---|
| フランス | 1 |
| スペイン | 1 |
| ドイツ | 1 |
HAVING
HAVINGは出力するグループに条件を指定する句になります。
実際に例をみてみましょう！
今回はポジション別の平均年齢を出力します。
まずはHAVINGを使わないと下記のようになります。
SELECT position, AVG(age)
FROM SoccerPlayer
GROUP BY position;
実行結果
| position | AVG(age) |
|---|---|
| FW | 34.2500 |
| MF | 31.6667 |
| DF | 33.0000 |
| GK | 28.0000 |
上記を平均年齢33歳以上のグループだけを出力したい場合、下記のようにHAVINGを使います。
SELECT position, AVG(age)
FROM SoccerPlayer
GROUP BY position
HAVING AVG(age) >= 33;
実行結果
| position | AVG(age) |
|---|---|
| FW | 34.2500 |
| DF | 33.0000 |
もう一つ例をみてみましょう。
今度は、(SoccerPlayerテーブルにおける)各クラブチームの在籍選手数を出力します。
SELECT clubteam, COUNT(*)
FROM SoccerPlayer
GROUP BY clubteam;
| clubteam | COUNT(*) |
|---|---|
| バルセロナ | 2 |
| ユベントス | 1 |
| パリSG | 1 |
| マンチェスターU | 1 |
| ヴィッセル神戸 | 1 |
| ACミラン | 1 |
| レアル・マドリード | 2 |
| ドルトムント | 1 |
| リバプール | 1 |
上記を二人以上在籍選手がいるクラブチームのみを出力する場合、下記のようにHAVINGを使います。
SELECT clubteam, COUNT(*)
FROM SoccerPlayer
GROUP BY clubteam
HAVING COUNT(*) >= 2;
実行結果
| clubteam | COUNT(*) |
|---|---|
| バルセロナ | 2 |
| レアル・マドリード | 2 |
ORDER BY
ORDER BYは検索結果を並び替える句になります。
たとえば年齢の若い順、つまり昇順に並べる場合は下記のようになります。
SELECT *
FROM SoccerPlayer
ORDER BY age;
実行結果
| player_id | player_name | nationality | clubteam | position | kikiashi | age |
|---|---|---|---|---|---|---|
| 4 | ポール・ポグバ | フランス | マンチェスターU | MF | NULL | 28 |
| 9 | アリソン | ブラジル | リバプール | GK | NULL | 28 |
| 10 | テア・シュテーゲン | ドイツ | バルセロナ | GK | NULL | 28 |
| 3 | ネイマール | ブラジル | パリSG | FW | 右 | 29 |
| 11 | トニ・クロース | ドイツ | レアル・マドリード | MF | NULL | 31 |
| 8 | マッツ・フンメルス | ドイツ | ドルトムント | DF | NULL | 32 |
| 1 | メッシ | アルゼンチン | バルセロナ | FW | 左 | 33 |
| 7 | セルヒオ・ラモス | スペイン | レアル・マドリード | DF | 右 | 34 |
| 2 | クリスティアーノ・ロナウド | ポルトガル | ユベントス | FW | 右 | 36 |
| 5 | イニエスタ | スペイン | ヴィッセル神戸 | MF | 右 | 36 |
| 6 | イブラヒモビッチ | スウェーデン | ACミラン | FW | 右 | 39 |
上記の例とは反対に年齢の高い順、つまり降順に並べる場合は列名の後ろにDESC(デスク)キーワードを使います。
SELECT *
FROM SoccerPlayer
ORDER BY age DESC;
実行結果
| player_id | player_name | nationality | clubteam | position | kikiashi | age |
|---|---|---|---|---|---|---|
| 6 | イブラヒモビッチ | スウェーデン | ACミラン | FW | 右 | 39 |
| 2 | クリスティアーノ・ロナウド | ポルトガル | ユベントス | FW | 右 | 36 |
| 5 | イニエスタ | スペイン | ヴィッセル神戸 | MF | 右 | 36 |
| 7 | セルヒオ・ラモス | スペイン | レアル・マドリード | DF | 右 | 34 |
| 1 | メッシ | アルゼンチン | バルセロナ | FW | 左 | 33 |
| 8 | マッツ・フンメルス | ドイツ | ドルトムント | DF | NULL | 32 |
| 11 | トニ・クロース | ドイツ | レアル・マドリード | MF | NULL | 31 |
| 3 | ネイマール | ブラジル | パリSG | FW | 右 | 29 |
| 4 | ポール・ポグバ | フランス | マンチェスターU | MF | NULL | 28 |
| 9 | アリソン | ブラジル | リバプール | GK | NULL | 28 |
| 10 | テア・シュテーゲン | ドイツ | バルセロナ | GK | NULL | 28 |
ご覧のようにDESCを使ったことにより、最年長のイブラヒモビッチが最初に来ています。
実は昇順に並べる場合は、正式にはASC(アスク)というキーワードがかるのですが、省略した場合は昇順に並べられるるようになっています。
ちなみにORDER BY句に書く列名をソートキーと呼びます。
次に複数のソートキーを指定した場合をみてみましょう。
先ほどの例の実行結果を見てください。
36歳が二人、28歳が三人います。
年齢が同じの場合は、player_idの昇順になっています。
これをソートキーを複数指定し、年齢が同じ場合player_idの降順で並び替えたいと思います。
SELECT *
FROM SoccerPlayer
ORDER BY age DESC, player_id DESC; ←ソートキーが複数ある場合はカンマ(,)で区切る。
| player_id | player_name | nationality | clubteam | position | kikiashi | age |
|---|---|---|---|---|---|---|
| 6 | イブラヒモビッチ | スウェーデン | ACミラン | FW | 右 | 39 |
| 5 | イニエスタ | スペイン | ヴィッセル神戸 | MF | 右 | 36 |
| 2 | クリスティアーノ・ロナウド | ポルトガル | ユベントス | FW | 右 | 36 |
| 7 | セルヒオ・ラモス | スペイン | レアル・マドリード | DF | 右 | 34 |
| 1 | メッシ | アルゼンチン | バルセロナ | FW | 左 | 33 |
| 8 | マッツ・フンメルス | ドイツ | ドルトムント | DF | NULL | 32 |
| 11 | トニ・クロース | ドイツ | レアル・マドリード | MF | NULL | 31 |
| 3 | ネイマール | ブラジル | パリSG | FW | 右 | 29 |
| 10 | テア・シュテーゲン | ドイツ | バルセロナ | GK | NULL | 28 |
| 9 | アリソン | ブラジル | リバプール | GK | NULL | 28 |
| 4 | ポール・ポグバ | フランス | マンチェスターU | MF | NULL | 28 |
このようにソートキーを複数指定し、一つ目のソートキーの数値が同じ場合、二つ目のソートキーの数値で並び替えをすることができます。
ちなみにORDER BY句ではソートキーに別名を使うことができます。
SELECT player_id, player_name, age AS "年齢"
FROM SoccerPlayer
ORDER BY 年齢 DESC; ←ソートキーに指定する場合、日本語でもダブルクォーテーション(")はいらない
実行結果
| player_id | player_name | 年齢 |
|---|---|---|
| 6 | イブラヒモビッチ | 39 |
| 2 | クリスティアーノ・ロナウド | 36 |
| 5 | イニエスタ | 36 |
| 7 | セルヒオ・ラモス | 34 |
| 1 | メッシ | 33 |
| 8 | マッツ・フンメルス | 32 |
| 11 | トニ・クロース | 31 |
| 3 | ネイマール | 29 |
| 4 | ポール・ポグバ | 28 |
| 9 | アリソン | 28 |
| 10 | テア・シュテーゲン | 28 |
INSERT文
INSERT文は既存のテーブルにデータを登録する文になります。
実際に例をみてみましょう。
今回は下記のような値を持つ1行をSoccerPlayerテーブルに挿入します。
| player_id | player_name | nationality | clubteam | position | kikiashi | age |
|---|---|---|---|---|---|---|
| 12 | グリーズマン | フランス | アトレティコ・マドリード | FW | 左 | 30 |
そのためのINSERT文は下記のようになります。
INSERT INTO SoccerPlayer
VALUES (12, 'グリーズマン', 'フランス', 'アトレティコ・マドリード', 'FW', '左', 30);
#列名の値をカッコ（）の中に左から順にカンマ(,)区切りで入れていく。
#NULLの場合はシングルクォーテーション(')で囲む必要はない
#実行結果
Query OK, 1 row affected (0.01 sec)
INSERTが成功すると上記のようなサクセス文が表示されます。
そしてテーーブルを確認するとしっかりとデータが追加されています。
| player_id | player_name | nationality | clubteam | position | kikiashi | age |
|---|---|---|---|---|---|---|
| 1 | メッシ | アルゼンチン | バルセロナ | FW | 左 | 33 |
| 2 | クリスティアーノ・ロナウド | ポルトガル | ユベントス | FW | 右 | 36 |
| 3 | ネイマール | ブラジル | パリSG | FW | 右 | 29 |
| 4 | ポール・ポグバ | フランス | マンチェスターU | MF | NULL | 28 |
| 5 | イニエスタ | スペイン | ヴィッセル神戸 | MF | 右 | 36 |
| 6 | イブラヒモビッチ | スウェーデン | ACミラン | FW | 右 | 39 |
| 7 | セルヒオ・ラモス | スペイン | レアル・マドリード | DF | 右 | 34 |
| 8 | マッツ・フンメルス | ドイツ | ドルトムント | DF | NULL | 32 |
| 9 | アリソン | ブラジル | リバプール | GK | NULL | 28 |
| 10 | テア・シュテーゲン | ドイツ | バルセロナ | GK | NULL | 28 |
| 11 | トニ・クロース | ドイツ | レアル・マドリード | MF | NULL | 31 |
| 12 | グリーズマン | フランス | アトレティコ・マドリード | FW | 左 | 30 |
UPDATE文
UPDATEはデータの更新を行う文になります。
早速例をみてみましょう。
今回は先ほど追加したグリーズマンのclubteamをバルセロナに更新します。
UPDATE SoccerPlayer ←テーブル名
SET clubteam = 'バルセロナ' ←更新したい列名と、更新後の値を記入する。値が数値（INTEGER型)の場合はシングルクォーテーションは不要
WHERE player_id = 12; ←更新する行を特定させるWHERE句
# 実行結果
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
更新が成功すると上記のようなサクセス文が表示されます。
そしてしっかりと更新されています。
| player_id | player_name | nationality | clubteam | position | kikiashi | age |
|---|---|---|---|---|---|---|
| 1 | メッシ | アルゼンチン | バルセロナ | FW | 左 | 33 |
| 2 | クリスティアーノ・ロナウド | ポルトガル | ユベントス | FW | 右 | 36 |
| 3 | ネイマール | ブラジル | パリSG | FW | 右 | 29 |
| 4 | ポール・ポグバ | フランス | マンチェスターU | MF | NULL | 28 |
| 5 | イニエスタ | スペイン | ヴィッセル神戸 | MF | 右 | 36 |
| 6 | イブラヒモビッチ | スウェーデン | ACミラン | FW | 右 | 39 |
| 7 | セルヒオ・ラモス | スペイン | レアル・マドリード | DF | 右 | 34 |
| 8 | マッツ・フンメルス | ドイツ | ドルトムント | DF | NULL | 32 |
| 9 | アリソン | ブラジル | リバプール | GK | NULL | 28 |
| 10 | テア・シュテーゲン | ドイツ | バルセロナ | GK | NULL | 28 |
| 11 | トニ・クロース | ドイツ | レアル・マドリード | MF | NULL | 31 |
| 12 | グリーズマン | フランス | バルセロナ | FW | 左 | 30 |
DELETE文
DELETE文はテーブル内の行を削除するための文になります。
早速例をみてみましょう。
今回はplayer_idが11のトニ・クロースを削除します。
その場合は下記のような書き方になります。
DELETE FROM SoccerPlayer ←テーブル名を記述
WHERE player_id = 11; ←消したい行を特定できるWHERE文
#実行結果
Query OK, 1 row affected (0.00 sec)
削除が成功すると上記のようなサクセス文が表示されます。
そしてしっかりと削除されています。
| player_id | player_name | nationality | clubteam | position | kikiashi | age |
|---|---|---|---|---|---|---|
| 1 | メッシ | アルゼンチン | バルセロナ | FW | 左 | 33 |
| 2 | クリスティアーノ・ロナウド | ポルトガル | ユベントス | FW | 右 | 36 |
| 3 | ネイマール | ブラジル | パリSG | FW | 右 | 29 |
| 4 | ポール・ポグバ | フランス | マンチェスターU | MF | NULL | 28 |
| 5 | イニエスタ | スペイン | ヴィッセル神戸 | MF | 右 | 36 |
| 6 | イブラヒモビッチ | スウェーデン | ACミラン | FW | 右 | 39 |
| 7 | セルヒオ・ラモス | スペイン | レアル・マドリード | DF | 右 | 34 |
| 8 | マッツ・フンメルス | ドイツ | ドルトムント | DF | NULL | 32 |
| 9 | アリソン | ブラジル | リバプール | GK | NULL | 28 |
| 10 | テア・シュテーゲン | ドイツ | バルセロナ | GK | NULL | 28 |
| 12 | グリーズマン | フランス | バルセロナ | FW | 左 | 30 |
さいごに
いかがでしたでしょうか？
ぜひ参考になったという方は、LGTMを押していただけると大変励みになるのでよろしくお願いします。
もっと深く学びたいという方はぜひ技術書を読むことをお勧めします。
最後までお付き合いいただき、ありがとうございました。
参考書籍
SQL 第2版 ゼロからはじめるデータベース操作 著者：ミック