気ままに趣味旅行

面白いコト「拡がる/拡げる」

データベース、SQLの基礎2

どうもhiroakiです。

 

今回は前回に引き続きデータベースを操作する言語、SQLについて紹介したいと思います。ウェブサービスなどを作る場合に必要となりますので、押さえましょう。

hiroakies.hatenablog.com

前回から章立てしてなくてすみません(笑)後々、直しますので少し待っててください。それでは、前回の続きをやっていきたいと思います。

 

「~と違った」という意味のDISTINCTを用いると、検索結果から重複するデータを除くことができます。SELECT文の後に「DISTINCT (カラム名)」として使います。

  1. SELECT distinct(character_name)
  2. FROM purchases;

 

複数のテーブルを1つに結合したいときは、「JOIN テーブル ON 条件」を使います。結合条件は「ON (fromに指定したテーブル名).カラム名 = (結合するテーブル名).カラム名」で指定します。結合すると、1つのテーブルとしてデータを取得できます。

例えばAテーブルとBテーブルを紐づけるためには、Aテーブルの外部キーとBテーブルの主キーを紐付けます。下のように「JOIN 外部テーブル ON 外部キー=主キー」で行ないます。これが「リレーション」です。

  1. SELECT earnings.id, earnings.total,customers.name FROM earnings
  2. join customers on earnings.customer_id=customers.id
  3. LIMIT 5

もし、結合されたあるデータが、左にm行、右にn行あれば、m × n行のデータとなり結合されます。

上記の他にも、内部結合をする「INNER JOIN テーブル ON 条件」や、左外部結合といって左側のテーブルにあるデータを結合する「LEFT OUTER JOIN ON 条件」、右外部結合の「RIGHT OUTER JOIN ON 条件」などがあります。

JOINでの結合は、FROMで指定したテーブルを基準に実行されます。ただし、外部キーがNULLのレコードは、実行結果に表示されません。NULLのレコードも取得したい場合は、LEFT JOINを使うと、外部キーがNULLのレコードも実行結果に表示できます。

  1. SELECT *
  2. FROM players
  3. join countries
  4. on players.country_id=countries.id
  5. left join teams
  6. on players.previous_team_id=teams.id;

上のようにJOINは1つのSQL文で、複数回使用できます。

また、テーブルを結合すると、カラム名が重複することがあります。その場合は「テーブル名.カラム名」としてデータを指定しましょう。

 

次は、データベースにレコードを追加します。id 、 name の2つのカラムを持つテーブル sample にレコードを追加しましょう。 

  1. INSERT INTO sample VALUES (2, 'hiroaki');

全カラムを対象に追加する場合は、カラム名を省略できます。また、テーブルsampleの id は主キーになるので、同じデータの重複を許しません。なので、2回同じINSERT文を実行するとエラーになります。このようにデータの重複を許さないカラムを「UNIQUE制約のカラム」と呼びます。ちなみにname カラムは重複しても良いので何度も「hiroaki」という名前を登録できます。

 

データベースのレコードを更新するには、「UPDATE テーブル名 SET カラム名1=値1, カラム名2=値2 WHERE 条件」と書きます。

  1. UPDATE users SET name='王貞治' where id=1;

 

データベースのレコードを削除するには、「DELETE FROM テーブル名 WHERE 条件」と指定します。

  1. delete from users where id=1;

where句をつけ忘れると、全部消えるので注意してください。

 

日次のアクセス数を求める関数を紹介します。DATE()は日付、YEAR()は年数を表示できます。他にも、「 CURRENT_DATE() AS 現在日時」として現在の日時を求めることもできます。

  1. SELECT DATE(startTime), COUNT(logID)
  2. FROM eventlog
  3. WHERE DATE(startTime) BETWEEN "2015-04-01" AND "2015-04-30"
  4. GROUP BY DATE(startTime);

月次のアクセス数を求めるには「DATE_FORMAT(カラム名,指定フォーマット)」関数を使います。

  1. SELECT DATE_FORMAT(startTime, '%Y-%m'), COUNT(logID)
  2. FROM eventlog
  3. GROUP BY DATE_FORMAT(startTime, '%Y-%m');

 

期間を整数で求めるには「TIMESTAMPDIFF(YEAR, (指定日), (指定日))」を使います。例えば、下のようにして満年齢を求めます。

  1. SELECT TIMESTAMPDIFF(YEAR, birth, '2016-12-01') AS '満年齢'
  2. FROM users;

 

サブクエリについてです。

SQL文(クエリといいます)には、( )で囲んで他のSQL文を挿入することができます。この()内のSQL文をサブクエリと言います。サブクエリを使えば、2つ以上のSQL文を1つにまとめることができるので、より複雑なデータを取得可能です。サブクエリの書き方は通常のSQL文と同じですが、()内に「;」は不要です。

  1. SELECT * FROM earnings
  2. WHERE customer_id IN (select id from customers where rank='A');
  1. SELECT name
  2. FROM players
  3. WHERE goals > (
  4. SELECT goals
  5. FROM players
  6. WHERE name='ウィル');

サブクエリを作る場合は、下記のようにフォーマットにいくつか注意点があります。

  1. -- FROM句に書く場合
  2. SELECT *
  3. FROM (サブクエリ) AS (サブクエリ名);
  1. -- WHERE句に書く場合
  2. SELECT *
  3. FROM users
  4. WHERE level = (サブクエリ);
  1. -- SELECT句に書く場合
  2. SELECT (サブクエリ) AS (サブクエリ名)
  3. FROM users;

なお、サブクエリ内でas句を使って、カラム名を指定してる場合、外側でそのカラム名を使用できます。

 

CASEについてです。CASEは以下のフォーマットで作れます。

  1. CASE
  2. WHEN (条件式1)THEN (出力1)
  3. WHEN (条件式2) THEN (出力2)
  4. ElSE (出力3)
  5. END AS "名前"

では最後に、このCASEを使って、クロス集計を行ってみましょう。クロス集計とは2つの項目が掛け合わされた集計です。例えば下図のような場合です。

年齢性別ごとの利用客数(人)
年齢\性別 合計
20歳未満 1019 2250 3269
20歳以上 3022 4210 7232

これまで紹介してきたSQLは単純集計でした。これをクロス集計に変えてみます。クロス集計を行うためには以下の手順を踏んでください。

① クロス集計の元になるデータを用意し、サブクエリとして読み込む。
② CASEで、特定の値だったら1にする。このとき別名を、特定の値と同じにする。

コード例

  1. select
  2. 日付,
  3. sum(case when クラス='初級' then 1 else 0 end) as 初級,
  4. sum(case when クラス='中級' then 1 else 0 end) as 中級,
  5. sum(case when クラス='上級' then 1 else 0 end) as 上級
  6. from(SELECT date_format(startTime,'%Y-%m')as 日付,eventlog.userID as ユーザー, case when users.level>=4 then '上級' when users.level>=2 then '中級' else '初級' end as クラスFROM eventlog INNER JOIN users ON users.userID = eventlog.userID) as クラス分け
  7. group by 日付;

内容はこれまでの知識で読み解いてください。もし分からなければコメントくだされば解説します。

 

ちょっと長くなったので今回は以上です。

次回以降で、実際にデータベースを作り、SQLを実行させてみたいと思いますので、よろしくお願いします。

では。