データベース、SQLの基礎2
どうもhiroakiです。
今回は前回に引き続きデータベースを操作する言語、SQLについて紹介したいと思います。ウェブサービスなどを作る場合に必要となりますので、押さえましょう。
前回から章立てしてなくてすみません(笑)後々、直しますので少し待っててください。それでは、前回の続きをやっていきたいと思います。
「~と違った」という意味のDISTINCTを用いると、検索結果から重複するデータを除くことができます。SELECT文の後に「DISTINCT (カラム名)」として使います。
- SELECT distinct(character_name)
- FROM purchases;
複数のテーブルを1つに結合したいときは、「JOIN テーブル ON 条件」を使います。結合条件は「ON (fromに指定したテーブル名).カラム名 = (結合するテーブル名).カラム名」で指定します。結合すると、1つのテーブルとしてデータを取得できます。
例えばAテーブルとBテーブルを紐づけるためには、Aテーブルの外部キーとBテーブルの主キーを紐付けます。下のように「JOIN 外部テーブル ON 外部キー=主キー」で行ないます。これが「リレーション」です。
- SELECT earnings.id, earnings.total,customers.name FROM earnings
- join customers on earnings.customer_id=customers.id
- LIMIT 5
もし、結合されたあるデータが、左にm行、右にn行あれば、m × n行のデータとなり結合されます。
上記の他にも、内部結合をする「INNER JOIN テーブル ON 条件」や、左外部結合といって左側のテーブルにあるデータを結合する「LEFT OUTER JOIN ON 条件」、右外部結合の「RIGHT OUTER JOIN ON 条件」などがあります。
JOINでの結合は、FROMで指定したテーブルを基準に実行されます。ただし、外部キーがNULLのレコードは、実行結果に表示されません。NULLのレコードも取得したい場合は、LEFT JOINを使うと、外部キーがNULLのレコードも実行結果に表示できます。
- SELECT *
- FROM players
- join countries
- on players.country_id=countries.id
- left join teams
- on players.previous_team_id=teams.id;
上のようにJOINは1つのSQL文で、複数回使用できます。
また、テーブルを結合すると、カラム名が重複することがあります。その場合は「テーブル名.カラム名」としてデータを指定しましょう。
次は、データベースにレコードを追加します。id 、 name の2つのカラムを持つテーブル sample にレコードを追加しましょう。
- INSERT INTO sample VALUES (2, 'hiroaki');
全カラムを対象に追加する場合は、カラム名を省略できます。また、テーブルsampleの id は主キーになるので、同じデータの重複を許しません。なので、2回同じINSERT文を実行するとエラーになります。このようにデータの重複を許さないカラムを「UNIQUE制約のカラム」と呼びます。ちなみにname カラムは重複しても良いので何度も「hiroaki」という名前を登録できます。
データベースのレコードを更新するには、「UPDATE テーブル名 SET カラム名1=値1, カラム名2=値2 WHERE 条件」と書きます。
- UPDATE users SET name='王貞治' where id=1;
データベースのレコードを削除するには、「DELETE FROM テーブル名 WHERE 条件」と指定します。
- delete from users where id=1;
where句をつけ忘れると、全部消えるので注意してください。
日次のアクセス数を求める関数を紹介します。DATE()は日付、YEAR()は年数を表示できます。他にも、「 CURRENT_DATE() AS 現在日時」として現在の日時を求めることもできます。
- SELECT DATE(startTime), COUNT(logID)
- FROM eventlog
- WHERE DATE(startTime) BETWEEN "2015-04-01" AND "2015-04-30"
- GROUP BY DATE(startTime);
月次のアクセス数を求めるには「DATE_FORMAT(カラム名,指定フォーマット)」関数を使います。
- SELECT DATE_FORMAT(startTime, '%Y-%m'), COUNT(logID)
- FROM eventlog
- GROUP BY DATE_FORMAT(startTime, '%Y-%m');
期間を整数で求めるには「TIMESTAMPDIFF(YEAR, (指定日), (指定日))」を使います。例えば、下のようにして満年齢を求めます。
- SELECT TIMESTAMPDIFF(YEAR, birth, '2016-12-01') AS '満年齢'
- FROM users;
サブクエリについてです。
SQL文(クエリといいます)には、( )で囲んで他のSQL文を挿入することができます。この()内のSQL文をサブクエリと言います。サブクエリを使えば、2つ以上のSQL文を1つにまとめることができるので、より複雑なデータを取得可能です。サブクエリの書き方は通常のSQL文と同じですが、()内に「;」は不要です。
- SELECT * FROM earnings
- WHERE customer_id IN (select id from customers where rank='A');
- SELECT name
- FROM players
- WHERE goals > (
- SELECT goals
- FROM players
- WHERE name='ウィル');
サブクエリを作る場合は、下記のようにフォーマットにいくつか注意点があります。
- -- FROM句に書く場合
- SELECT *
- FROM (サブクエリ) AS (サブクエリ名);
- -- WHERE句に書く場合
- SELECT *
- FROM users
- WHERE level = (サブクエリ);
- -- SELECT句に書く場合
- SELECT (サブクエリ) AS (サブクエリ名)
- FROM users;
なお、サブクエリ内でas句を使って、カラム名を指定してる場合、外側でそのカラム名を使用できます。
CASEについてです。CASEは以下のフォーマットで作れます。
- CASE
- WHEN (条件式1)THEN (出力1)
- WHEN (条件式2) THEN (出力2)
- ElSE (出力3)
- END AS "名前"
では最後に、このCASEを使って、クロス集計を行ってみましょう。クロス集計とは2つの項目が掛け合わされた集計です。例えば下図のような場合です。
年齢\性別 | 男 | 女 | 合計 |
---|---|---|---|
20歳未満 | 1019 | 2250 | 3269 |
20歳以上 | 3022 | 4210 | 7232 |
これまで紹介してきたSQLは単純集計でした。これをクロス集計に変えてみます。クロス集計を行うためには以下の手順を踏んでください。
① クロス集計の元になるデータを用意し、サブクエリとして読み込む。
② CASEで、特定の値だったら1にする。このとき別名を、特定の値と同じにする。
コード例
- select
- 日付,
- sum(case when クラス='初級' then 1 else 0 end) as 初級,
- sum(case when クラス='中級' then 1 else 0 end) as 中級,
- sum(case when クラス='上級' then 1 else 0 end) as 上級
- 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 クラス分け
- group by 日付;
内容はこれまでの知識で読み解いてください。もし分からなければコメントくだされば解説します。
ちょっと長くなったので今回は以上です。
次回以降で、実際にデータベースを作り、SQLを実行させてみたいと思いますので、よろしくお願いします。
では。