woshidan's loose leaf

ぼんやり勉強しています。

実践SQL入門 2章まとめ

2章は特にExplain関係なかったので、普通に個人的なまとめ。

内容

  • ビューを作る
  • INの中のサブクエリ
    • 作業用SQLのメモ
  • 検索CASE式と単純CASE式
  • CASE式を文字列の読み替えに使う
  • UNION
  • INTERSECTとEXCEPTはMySQLにない
  • ウィンドウ関数もMySQLにはない
    • ランキングまでSQLで出す必要はあるか?

ビューの作成

ビューは実際全然使ったことないからどぎまぎであった。

CREATE VIEW count_address (v_address, cnt)
AS
SELECT address, count(*)
FROM addresses
GROUP BY address;
SELECT * FROM count_address;
+--------------+-----+
| v_address    | cnt |
+--------------+-----+
| 三重県       |   1 |
| 千葉県       |   2 |
| 和歌山県     |   1 |
| 東京都       |   3 |
| 福島県       |   2 |
+--------------+-----+
5 rows in set (0.00 sec)

INの中のサブクエリ

FROM句を直接指定したSELECT文をサブクエリと呼ぶ。サブクエリを使って、同じ構造のテーブルのデータを探してみたり。

INの中でサブクエリを使用すると、

  • ユーザがテーブルデータの更新を意識しなくてよい
  • 定数を直接書くよりコードのメンテナンス性がよくなる

といった利点がある。

SELECT name from addresses
where name IN (SELECT name from addresses2);
作業用SQLのメモ

作業用のメモとしてテーブルの複製、とその後差分作ったSQL.

create table addresses2 like addresses;
insert into addresses2 select * from addresses;
delete from addresses2 where address IN ('三重県','和歌山県','福島県'); # 手抜きです

検索CASE式と単純CASE式

検索CASE式

CASE WHEN 評価式 THEN 式
     WHEN 評価式 THEN 式
     WHEN 評価式 THEN 式
     WHEN 評価式 THEN 式
     略
     ELSE 式
END

単純CASE式

CASE 式
  WHEN 値 THEN 式 
  WHEN 値 THEN 式
  略 
  ELSE 式
END 

CASE式を文字列の読み替えに使う

SQLのCASE式は、特定の値、定数を返す。

実際、CASE式を文字列の読み替えのために使うのは、ポピュラーな用途です。

SELECT name, address,
       CASE WHEN address IN ('東京都', '千葉県') THEN '関東'
            ELSE 'そのほか'
       END AS district
FROM addresses;

CASE式の強力なところは、これが式であるがゆえに、式を書ける場所ならどこでも書く事が出来ることです。

UNION

SELECT *
  FROM addresses
UNION
SELECT * 
  FROM addresses2
;

UNIONは同じ行と認識される行はダブルカウントされないようになっている、が、こないださわった奴はダブルカウントされてたけど、 列名かなにか違ったのかしら...。

INTERSECTとEXCEPTはMySQLにはない

ANDはINTERSECTだが、MySQLは対応していないのであった。

SELECT *
  FROM addresses
INTERSECT
SELECT * 
  FROM addresses2
;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT * 
  FROM addresses2' at line 4
SELECT *
  FROM addresses
EXCEPT
SELECT * 
  FROM addresses2
;
ERROR 1064 (42000): your MySQL server version for the right syntax ...

EXCEPTも同じく。INTERSECTはINNER JOINでEXCEPTはOUTER JOINを使って書きます。

ウィンドウ関数もMySQLにはない

ウィンドウ関数も使った事無い。

一言で言うと「集約機能を省いたGROUP BY句」

GROUP BY句はカットと集約。

GROUP BY = PARTITION BY(ウィンドウ関数) + 集約 の関係にあるらしい。

SELECT address, COUNT(*)
FROM addresses
GROUP BY address;

SELECT address, COUNT(*) OVER(PARTITION BY address)
FROM addresses;

同じと。

まあ、残念なことにPARTITION BYもMySQLでは実装ありません(http://codezine.jp/article/detail/3105)。

ランキングまでSQLで出す必要はあるか?

RANKとかの機能も、実際はデータベースの側で順位までつけたい、ということはないので無くていいかな (アプリケーションの側で順位をつけるのが容易なため)、ということで飛ばします。

個人的にはいまじ、cssでもランキングの実装が出来るご時世で、サーバサイドで負荷なくできる事であれば、 サーバサイドでやったほうが楽ではという気がします。

ソートはSQLでやったことがよいけれど、ランキングはそこまでたくさん返すのでなければJSONなりHTMLなりのビューでやればいい感あります。