続きです
まとめ
集合演算の条件
- 両方のテーブルの列の数が同じでなければならない
- テーブルを組み合わせるときに対になる列のデータ型が同じでなければならない
ちなみに集合演算というのはunionとかintersectとかexceptとかのことです。イマイチ使ってるところを見たことがない(あとソフトによって名前が違ったりするらしい)
QUOTE
quote(Let's Split!)
みたいな感じで書くと、中のクオーテーションをエスケープして出力してくれる。
予め決まってる文章をインポートするときとかに使えるかもしれない。もちろんこれだけでSQLインジェクションが防げるわけではない。
REGEXP
SELECT *
FROM employee
WHERE employee.name REGEXP '^佐藤'
REGEXPを使えばMySQLでも正規表現が使える！RLIKEでもいいぞ！
ちなみに先輩はそんな高度なことはできないだろうと高をくくってたらしく、結構感動してました。
SELECT節でのLIKE, REGEXP
LIKEやREGEXPは一致したときに1を返し、それ以外は0を返すのでSELECT節に指定すればDBからレコードを取ってきた時点で真偽に関する列が作れて、もしかしたらビューとかに不要なロジックを持ち込まなくて済む…のかもしれない
COUNTについて
前のまとめの時に式の評価順序について調べたのだが、GROUP BYでレコードがまとめられたあとにSELECT文が参照されるなら、COUNTで数えられるのは何なんだろうと思っていた。
理解の助けになったのは、GROUP BYはレコードをケーキのように切り分けているという表現で、GROUP BYがあるとCOUNTはその小分けにされたテーブルの中のレコードを見に行く、と考えるとイメージがつかめた。
とはいえDISTINCTやらCOUNT(hoge.type=fuga or null)みたいなのが沢山出てくると混乱するときもあるので用心しなければ
グローバルタイムゾーン セッションタイムゾーン
サーバ起動時にホストマシンのタイムゾーンを読み取ってシステムタイムゾーンにするんだけど、必ずしもホストマシンとログインユーザーのタイムゾーンが一致しているとは限らないのでこういうオプションが必要になってくる(?)
SUPERな権限を持つユーザはグローバルタイムゾーンを指定できるけど、そうでないときはSET time_zone = 'Europe/Zurich';とかでセッションタイムゾーンを変更してあげると、CURDATE()とかNOW()とかで返ってくる時間が意図したものになる
実はあんまり良くわかってない。
時刻のセパレータ
2005-3-27 15:30:00
2005/03/27 15:30:00
2005,03,27,15,30,00
20050327153000
これ全部暗黙的に日付として変換してくれるらしい。要素の順番には厳格だが、セパレータには寛容とのこと。
他にも日付を生成するためのstr_to_date()関数などがあり、STR_TO_DATE('March 27, 2005', '%M %d, %Y')とかで読み取ってくれるらしい。一々DateTimeクラスをnewしてformatして…とかやらなくて済むようになるかも？
interval
５日後の日付が欲しいときはDATE_ADD(CURRENT_DATE(), INTERVAL 5 DAY)で指定ができる。もちろんDAYの部分にはYEARとかSECONDとかも入れられるしYEAR_MONTHで9年11ヶ月とかも指定できるっぽい。
あとよく使いそうなのはLAST_DAYで、指定した月の最終日を出してくれる。プログラム側で出そうとすると意外と気にすることが多かったりするので、いつか楽をさせてくれるかもしれない。
GROUP BYの複数指定について
GROUP BYには複数の列を指定できる。最初見た時はまとめたものを更にまとめるからレコードの量は減るのかなぁとか思ってたんだけど、ここでもケーキのように切り分ける的な表現が役に立って、実際は一度切り分けたものを更に細かく切り分けるから最終的なレコードの量は増える、ということだった。
wtih_rollup
SELECT product_cd, open_branch_id,
SUM(avail_balance) tot_balance
FROM ACCOUNT
GROUP BY product_cd, open_branch_id WITH ROLLUP
のような形で使うと、GROUP BYされたレコードの小計を出すことが出来る
分かりにくいので図に出すと
| product_cd | open_branch_id | tot_balance |
|---|---|---|
| BUS | 2 | 934.55 |
| BUS | 4 | 10.00 |
| BUS | NULL | 944.55 |
| CD | 1 | 11500.00 |
| CD | NULL | 11500.00 |
| CHK | 1 | 782.16 |
| ... | ... | ... |
| みたいな感じになる |
以前Riot.jsで表を出すとき、eachタグの中でグループごとの小計を出すのに頭を悩ませ、結局サーバ側で小計含めた配列を渡すようにしたんだけど、遡ってSQLで出せることを知っていればもっと綺麗に書けたのかもしれないなぁ。