SQLによるデータ抽出の基本構文

駆け出しデータサイエンティストのNishです
今回はSQLの基本構文についてまとめてみました
かなり基本的な内容ですが、SQLの復習をしていて僕自身が忘れている部分もあったので、
備忘録として記事にしました
(構文の解説がかなりざっくりしてますが、ご容赦下さい。。)

解説する内容は以下の通りです

• SELECT文でデータ抽出
• WHERE句でデータ抽出の条件指定
• GROUP BYとHAVINGでグループ集計
• ORDER BYとLIMITでデータを整える

以前にMySQLの環境構築についての記事を書きましたが、そこで作成した仮想マシン上で作業します。
以前の記事はコチラ

事前準備

まずは、仮想マシンにログインして、MySQLサーバが起動していることを確認します

$ sudo systemctl status mysqld

上記コマンドを打って、active(running)というような表示が出ればok

MySQLにログインできることも確認しましょう

$ mysql -u root -p

テストデータを投入

テスト用のDBとTable、ダミーデータを用意します
以下の作業を順に行い、テスト用の環境を作成しましょう

• テスト用のDBを作成するクエリをcreate_de.sqlに記述
• テスト用のTableを作成するクエリをcreate_table.sqlに記述
• ダミーデータをテスト用Tableに投入するクエリをinsert_dummy_data.sqlに記述

create_db.sql

CREATE DATABASE training_db;

create_table.sql

USE training_db;

CREATE TABLE IF NOT EXISTS page_view (
	user_id   INT     NOT NULL,
	age       INT     NOT NULL,
	sex       CHAR(1) NOT NULL,
	page_id   INT     NOT NULL
);

insert_dummy_data.sql

USE training_db;

INSERT INTO page_view (user_id, age, sex, page_id) VALUES
(1, 25, 'M', 10001),
(1, 25, 'M', 10002),
(1, 25, 'M', 10003),
(2, 35, 'F', 10001),
(2, 35, 'F', 10003),
(2, 35, 'F', 10004),
(2, 35, 'F', 10005),
(3, 42, 'M', 10002),
(4, 10, 'F', 10003),
(4, 10, 'F', 10004),
(4, 10, 'F', 10005)

作成したクエリを順に実行
(毎回認証するのが面倒なので、回避方法あったら教えて下さい。。。)

$ mysql -u root -p < create_db.sql
$ mysql -u root -p < create_table.sql
$ mysql -u root -p < insert_dummy_data.sql

SQL基本構文

ここからは、データを抽出してその結果を確認しながらSQLの基本構文について解説します

SELECT文でデータ抽出

SELECT文によって、テーブルから欲しいデータを抽出することができます
SELECT文の書き方は単純で、欲しいカラムを指定するだけです

SELECT
  user_id,
  page_id
FROM
  page_view

上記のクエリで、指定したカラム(user_id, page_id)を抽出できます

+---------+---------+
| user_id | page_id |
+---------+---------+
|       1 |   10001 |
|       1 |   10002 |
|       1 |   10003 |
|       2 |   10001 |
|       2 |   10003 |
|       2 |   10004 |
|       2 |   10005 |
|       3 |   10002 |
|       4 |   10003 |
|       4 |   10004 |
|       4 |   10005 |
+---------+---------+
11 rows in set (0.00 sec)

WHERE句でデータ抽出の条件指定

データの抽出は上に書いたとおりSELECT文で行いますが、特定の条件を満たすレコードだけ抽出する場合には、WHERE句で条件を指定します
例えば、page_viewテーブルのレコードのうち、性別が男性(Male)のものだけを取り出すには、次のクエリを実行します

SELECT
  *
FROM
  page_view
WHERE
  sex = 'M'

以下のように、性別が男性のレコードのみ取り出せました

+---------+-----+-----+---------+
| user_id | age | sex | page_id |
+---------+-----+-----+---------+
|       1 |  25 | M   |   10001 |
|       1 |  25 | M   |   10002 |
|       1 |  25 | M   |   10003 |
|       3 |  42 | M   |   10002 |
+---------+-----+-----+---------+
4 rows in set (0.02 sec)

条件はANDやORを使用して複数指定することができます
性別が男性でかつ、page_idが10002のレコードを抽出する場合には、以下のクエリを実行します

SELECT
  *
FROM
  page_view
WHERE
  sex = 'M'
  AND page_id = 10002

結果は以下の通り

+---------+-----+-----+---------+
| user_id | age | sex | page_id |
+---------+-----+-----+---------+
|       1 |  25 | M   |   10002 |
|       3 |  42 | M   |   10002 |
+---------+-----+-----+---------+
2 rows in set (0.00 sec)

GROUP BYとHAVINGでグループ集計

レコードをまとめて、集計する際に必要なのがGroupBy句とHaving句です
どのカラムでレコードをグループ訳するかを指定して集計することができます
例えば、性別ごとのレコード数を知りたければ、次のクエリを実行しましょう

SELECT
  sex,
  COUNT(*) AS record_count
FROM
  page_view
GROUP BY
  sex

+-----+--------------+
| sex | record_count |
+-----+--------------+
| M   |            4 |
| F   |            7 |
+-----+--------------+
2 rows in set (0.05 sec)

グループ分けしたあとに条件を指定するにはHAVING句を使用します
例えば、2回以上閲覧されたpage_idを抽出するには、以下のクエリを実行します

SELECT
  page_id,
  COUNT(*) AS pv
FROM
  page_view
GROUP BY
  page_id
HAVING
  COUNT(*) >= 2

+---------+----+
| page_id | pv |
+---------+----+
|   10001 |  2 |
|   10002 |  2 |
|   10003 |  3 |
|   10004 |  2 |
|   10005 |  2 |
+---------+----+
5 rows in set (0.02 sec)

ORDER BYとLIMITでデータを整える

最後に、レコードをソートするORDER BY句と、指定レコード数だけ抽出するLIMIT句です
多く閲覧された上位2つのpage_idを取得するには、次のクエリを実行すればokです

SELECT
  page_id,
  COUNT(*) AS pv
FROM
  page_view
GROUP BY
  page_id
ORDER BY
  pv DESC
LIMIT 2

+---------+----+
| page_id | pv |
+---------+----+
|   10003 |  3 |
|   10001 |  2 |
+---------+----+
2 rows in set (0.00 sec)