SQLのコマンド一覧 ~ データベースで分析できるようになる

よこのじ(@yokonoji_work)です。

この記事では、SQLを使ってデータベースから望む形でデータを抽出するためのコマンドを紹介します。

レコード(=行)とカラム(=列)で構成されるテーブルから条件を指定して、特定のレコードやカラムのデータを抽出・・・

と聞くとなんだか難しい感じがするのですが、SQLは言葉で指定するように自然に書けるので覚えやすいと思います。

 

SQLを学ぶならこのベストセラー本が、おすすめです。

SQL のコマンド一覧

カラムの選択

抽出カラムを指定します。

SELECT カラム名

テーブルの選択

どのテーブルを扱うのか指定します。

FROM テーブル名

どのテーブルからのどのカラムを指定するのか?という下記の記述が基本的な形になります。

SELECT karamu
FROM table;

コマンドの最後には ; (セミコロン)が必要です。

複数のカラムを選択

SELECT カラム名1, カラム名2

すべてのカラムを選択

SELECT *

特定のカラムから該当するデータを取得

WHERE でどのようなデータを取得するのか条件を付けます。

WHERE カラム名 = "データ名"

例えば、次のように「本」という文字列や「1000」という数値を持つレコードのみを取得することができます。

SELECT *
FROM table
WHERE item = "本";

数値の場合は
WHERE number = 1000;

日付の場合は
WHERE create_at = "2019-04-27";

数値や日付においては、比較演算子により条件を指定できます。

WHERE number > 1000;
WHERE create_at >= 1000;
WHERE number < 1000;
WHERE create_at <= 1000;

LIKE演算子(ワイルドカード)

次のように item カラムから「パソコン」という語句が含まれるものを取得することができます。

ある語句を含む
WHERE item LIKE "%パソコン%";

パソコンで始まるデータ(前方一致)
WHERE item LIKE "パソコン%";

パソコンで終わるデータ(後方一致)
WHERE item LIKE "%パソコン";

NOT演算子

NOT条件は NOT と記述します。

「1000より大きい」ではない
WHERE NOT number > 1000;

「"パソコン"をどこかに含む」ではない
WHERE item LIKE "%パソコン%";

なお、比較演算子として<> や != を使うこともできます。

NULLデータを取得

dataカラムにデータがない(NULL)レコードがあった場合、ここまでに紹介した条件では取得することができません。NULLデータを取得したい場合は次のように記述します。

WHERE item IS NULL;

また、NULLではないデータを取得する場合はNOTを付けます。

WHERE item IS NOT NULL;

複数の条件を指定する

ANDとORで複数の条件を指定することもできます。

WHERE item = "時計"
AND number >= 2000;

WHERE item = "電話"
OR number = 3000;

データの並び替え

取得したデータは、昇順または降順に並べ替えることができます。

昇順
ORDER BY カラム名 ASC;
降順
ORDER BY カラム名 DESC;

記述例はこのようになります。

SELECT *
FROM table
WHERE item = "時計"
ORDER BY price ASC;

例えば、itemカラムに「時計」というデータを持つレコードを取得して、priceというカラムにある値段データを使って小さい順に並べることができます。

必要な数だけデータを取得

条件をして取得データのうち、一定数のデータのみ取得できれば良い場合はLIMITで件数を制限します。

LIMIT 件数;

次のように記述すると、時計というデータを持つ10件のデータを取得できます。

SELECT *
FROM table
WHERE item = "時計"
LIMIT 10;

大きい順に並べた上位5件を取得するといったことも可能です。

SELECT *
FROM table
ORDER BY price DESC
LIMIT 5;

重複データを取り除く

パソコン
時計
パソコン

のように重複がある場合、DISTINCT(カラム名)で重複を取り除くこともできます。

SELECT DISTINCT(item)
FROM table;

 

四則演算

取得したデータに計算を加えて、データを加工することができます。

カラム名 + 10
カラム名 - 10
カラム名 * 10
カラム名 / 10

例えば、取得した値段データに消費税を加えるということが可能です。

SELECT item, price * 1.08
FROM table;

取得データを合計する

SUM関数を使うと、合計したデータを取得できます。

SELECT SUM(price)
FROM table
WHERE item = "時計";

取得データを平均する

AVG関数を使うと、平均したデータを取得できます。

SELECT AVG(price)
FROM table;

データの数をカウントする

COUNT関数を使うと、データの件数を取得できます。NULLデータは数えません。

SELECT COUNT(*)
FROM table;

データの最大・最小を取得する

MAX関数、MIN関数で指定したカラムの最大、最小のデータを取得できます。

MAX(カラム名)
MIN(カラム名)

このように記述します。

SELECT MAX(price)
FROM table;

データをグループ化

GROUP BY カラム名 を利用すると、あるデータでグループ分けされたデータを取得できます。

SELECT SUM(price), day
FROM table
GROUP BY day

このように記述すると

1000円 2019-04-27
1500円 2019-04-28

のように日付ごとに利用金額をまとめたデータを取得できます。

GROUP BYを使用する場合は、SELECTで使えるのは、GROUP BYで指定しているカラムと、集計関数(SUMやAVG)のみとなります。

複数カラムのグループ化

複数のカラムをグループ化することもできます。

例えば、次のような取得結果です。

2000円 パソコン 2019-04-27
1000円 時計   2019-04-27
3000円 パソコン 2019-04-28
4000円 時計   2019-04-28

このようなグループ分けをするには、GROUP BY カラム名1, カラム名2 のように記述します。

SELECT SUM(price), item, day
FROM table
GROUP BY item, day

GROUP BYでグループ化したデータを更に絞り込む

この場合は、WHERE ではなく HAVING を用います。

SELECT SUM(price), day
FROM table
GROUP BY day
HAVING SUM(price) > 2000;

サブクエリ(複数のSQL文)

SQL文の中でSQL文を書いて、条件を指定することができます。

SELECT item
FROM table
WHERE price > (
  SELECT price
  FROM table
  WHERE item = "時計"
);

この場合は、「時計」というデータを持つレコードからpriceデータを取得して、そのデータよりも大きなデータを持つものをitemカラムから取得します。

別名を定義

ASを使うことでカラム名などに別の名前を付けることができます。

SELECT item AS "アイテム一覧"
FROM table
WHERE price >= 200

テーブルの結合

複数のテーブルがある場合、主キーと外部キーのカラムを用意してテーブルを紐付けることができます。

SELECT *
FROM テーブル1
JOIN テーブル2
ON テーブル1.カラム名 = テーブル2.カラム名

例えば、table1 の item_id カラムと table2 の id が対応するデータであれば、これらのidを紐付けることで1つのテーブルに結合することができます。

SELECT *
FROM table1
JOIN table2
ON table1.item_id = table2.id;

複数テーブルで同一のカラム名がある場合

table1 の item カラムと table2 の item カラムがある場合、それぞれ区別して扱う必要があります。

この例のように、table2のitemカラムは table2.item と記述します。

SELECT SUM(number), table2.item
FROM table1
JOIN table2
ON table1.item_id = table2.id;
GROUP BY table2.item;

NULLを表示するようにテーブルを結合する

JOIN と ON によるテーブル結合では、外部キーがNULLのレコードを取得しません。取得したい場合は、LEFT JOIN を利用します。

SELECT *
FROM table1
LEFT JOIN table2
ON table1.item_id = table2.id;

3つ以上のテーブルを結合する

テーブルを3つ以上結合する場合は、JOIN ON や LEFT JOIN ON を複数利用します。

SELECT *
FROM table1
JOIN table2
ON table1.item_id = table2.id;
LEFT JOIN table3
ON table1.model_id = table3.id;

 

SQLのベストセラー本です。解説+222問の例題でしっかり身に付く!