GROUP BY -> WITH ROLLUP

Posted by muchag | MySQL |
初回投稿:2015-02-12 (木) 22:59:00 | 最終更新:2015-02-12 (木) 23:02:01

素敵な集計オプション。

【環境】
MySQL:5.5.27, 5.5.38

MySQL:12.17.2 GROUP BY Modifiers

効能

集計の集計をしてくれる。
 

2015-02-12_WITH ROLLUP_1これが一覧表。

 
2015-02-12_WITH ROLLUP_2WITH ROLLUP をつけると、こういう風に集計できる。
黄色い網掛けは、国別集計。
緑の網掛けは、年度集計。
赤の網掛けは、全体集計。
素晴らしい!

 

参考サイト

FLATZ:MySQLの知っていると便利な構文(その2)

Posted by muchag | MySQL |
初回投稿:2015-02-12 (木) 22:59:00 | 最終更新:2015-02-12 (木) 23:02:01

Date and Time

Posted by muchag | MySQL |
初回投稿:2015-02-12 (木) 21:50:26 | 最終更新:2015-02-14 (土) 1:08:28

日付関数は便利~! 😎
MySQL の勉強は遅れてしまってるにゃ><

【環境】
MySQL:5.5.27, 5.5.38

MySQL:12.7 Date and Time Functions
 
DATE, TIME, DATETIME, TIMESTAMP, YEAR 型のフィールドに対して使える関数。

YEAR, MONTH, DAY, DAYNAME

YEAR(date):年だけ返す
MONTH(date):月だけ返す
DAY(date):日だけ返す
DAYNAME(date):曜日だけ返す
※ date は日付型のカラム名
 

  1. mysql> SELECT YEAR('1987-01-01');
  2.         -> 1987
  3.  
  4. mysql> SELECT MONTH('2008-02-03');
  5.         -> 2

これを hoge テーブルに使うと

  1. mysql> SELECT YEAR(date) as year, num FROM hoge;
year num
2014 3
2014 2
2015 6

これだと、だから? ってなっちゃうけど、集計を考えるとすぐわかる。
 

集計

年別の個数集計は、下記のように取れる。

  1. mysql> SELECT YEAR(date) as year, SUM(num) FROM hoge GROUP BY YEAR(date);
year num
2014 5
2015 6

素敵~♪
 

DATE_FORMAT

MySQL:DATE_FORMAT(date,format)
日付データをフォーマットして返す

  1. SELECT DISTINCT DATE_FORMAT( date, "%Y-%m" ) FROM table

これで date カラムのデータを YYYY-MM というフォーマットへ変換し
重複行を除いて取り出してくれる。

フォーマット指定方法については、マニュアル参照。
 

EXTRACT

MySQL:EXTRACT(unit FROM date)
EXTRACT():指定した部分だけ返す

YEAR なんかの複合型。

  1. mysql> SELECT EXTRACT(YEAR FROM '2009-07-02');
  2.        -> 2009
  3. mysql> SELECT EXTRACT(YEAR_MONTH FROM '2009-07-02 01:02:03');
  4.        -> 200907
  5. mysql> SELECT EXTRACT(DAY_MINUTE FROM '2009-07-02 01:02:03');
  6.        -> 20102
  7. mysql> SELECT EXTRACT(MICROSECOND
  8.     ->                FROM '2003-01-02 10:30:00.000123');
  9.         -> 123

これもすごい~!
 

参考サイト

FLATZ:MySQLの知っていると便利な構文(その2)
→2007年の記事なのに、検索の2番目に出てきた。すごいね~。ありがとうございます!

Posted by muchag | MySQL |
初回投稿:2015-02-12 (木) 21:50:26 | 最終更新:2015-02-14 (土) 1:08:28

INSERT…ON DUPLICATE KEY UPDATE

Posted by muchag | MySQL |
初回投稿:2015-02-09 (月) 16:07:51 | 最終更新:2015-02-09 (月) 20:11:46

ON DUPLICATE KEY UPDATE という、すごく便利な命令を随分前に知って使っていたけど、まだまだ奥が深かったので、メモ。

【環境】
[MySQL] 5.5.27
基本
  1. INSERT INTO table_name (id, name)
  2.     VALUES (val1, val2)
  3.     ON DUPLICATE KEY UPDATE name = val2;

これで
・新しいレコードを挿入してね
・値は、id が val1 で、name は val2 でお願いね
・でももし val1 という値が id に既にあったら、そのレコードの name を val2 で上書きしてね
というお願いになる。
便利だよね~。
事前に存在を確認して、あれば UPDATE、なければ INSERT を発行する、という手間がなくなった。
 

DUPLICATE KEY

ここで「でももし val1 という値が id に既にあったら」この部分について。

特定のキーとは、Uniqueキーとして登録してあるか、Primary Keyとして登録してある必要がありますが、1つのカラムに限らず、複数のカラムでも一つの Uniqueキー(Primary Key)の設定を行うことで対応が可能です。
 
ただ、Uniqueキーの設定が必要ですので、設定するカラムで複数レコードが存在するような仕組みの場合は利用することはできません。

— snip —

「Unique」は、nullは重複しないと見なします。
「Primary Key」は、nullは重複するとみなすため、nullを入れようとするとエラーになります。

有限会社 エス技研:Insert On Duplicate Key Update構文の使い方

ふむふむ。キー周りの知識が怪しいので、ひとまずはこれにて。
 

式も入れられる

参考サイトを読み取っただけで、実証していない。

  1. INSERT INTO table_name (id, name, point)
  2.     VALUES (val1, val2, val3)
  3.     ON DUPLICATE KEY UPDATE name = val2, point = val3;

このとき

  1. INSERT INTO table_name (id, name, point)
  2.     VALUES (val1, val2, val3)
  3.     ON DUPLICATE KEY UPDATE name = val2, point = point + VALUES(val3);

このようにすることで、id が被った場合は、point に val3 を上書きするのではなく、元の値に val3 を加算して上書きする。

  1. INSERT INTO table_name (id, name, point)
  2.     VALUES (val1, val2, val3)
  3.     ON DUPLICATE KEY UPDATE name = val2, point = point + 10;

こうすると、元の値に 10 を加算して上書きする。(新しい値 val3 は無視)
すごい。。。でも、少し複雑になってきた。
 

条件式

CASE や IF も使えるらしい・・・。
すぐにはわからないので、丸コピー。

DUPLICATE KEY UPDATE にはCASEやIFが使えるので、フィールド毎に更新条件を設定できる。
デフォルト値を使って値を更新されないようにもできる。

Qiita:MySQL: INSERT…ON DUPLICATE KEY UPDATEまとめ

  1. INSERT INTO
  2.        table (user_id, fb_like, tweet, updated)
  3.    VALUES
  4.        (1, 12, 10, NOW()), (2, 8, 9, NOW())...()...,(1000, 42, 30, NOW())
  5.    ON DUPLICATE KEY UPDATE
  6.        fb_like = IF(fb_like > VALUES(fb_like), fb_like, VALUES(fb_like))
  7.        , tweet = IF(tweet > VALUES(tweet), tweet, VALUES(tweet))
  8.         , updated = NOW()

 

プリペアドステートメント

プリペアドステートメントを利用する場合は、下記のように記述。

  1. INSERT INTO table_name (id, name, point)
  2.     VALUES (?,?,?)
  3.     ON DUPLICATE KEY UPDATE name = VALUES(name), point = VALUES(point);

 

REPLACE

同じ結果を得られるものとして、REPLACE 文がある。
両者の違いは、下記の通りらしい。

ON DUPLICATE KEY UPDATE:UPDATE
Replace:DELETE & INSERT

 

参考サイト

Qiita:MySQL: INSERT…ON DUPLICATE KEY UPDATEまとめ
極上の人生:MySQL でプレースホルダを使ったINSERT … ON DUPLICATE KEY UPDATE 文を書く
有限会社 エス技研:Insert On Duplicate Key Update構文の使い方

Posted by muchag | MySQL |
初回投稿:2015-02-09 (月) 16:07:51 | 最終更新:2015-02-09 (月) 20:11:46

INSERT

Posted by muchag | MySQL |
初回投稿:2015-02-09 (月) 15:39:31 | 最終更新:2015-02-12 (木) 0:10:46

勘違い。。。
そのうち書くことができたら書く。

Posted by muchag | MySQL |
初回投稿:2015-02-09 (月) 15:39:31 | 最終更新:2015-02-12 (木) 0:10:46

MySQL Index

Posted by muchag | MySQL |
初回投稿:2015-02-09 (月) 15:27:39 | 最終更新:2019-11-23 (土) 13:29:30

ずぅっとお世話になりっ放しの MySQL さん。
今更だけど、目次を追加。

丸っきりの書きかけ項目は Not found になる。

基本

 

SQL 構文(Statement)
複合(Compound)
  • フロー制御(Flow Control)
句(clause)
関数(function)
phpMyAdmin
最適化
mysqldump
Workbench
CLI
Tips
がい~ん!(はまったこと)
参考

MySQL 日本公式
公式ドキュメント
MySQL演算子・関数

Posted by muchag | MySQL |
初回投稿:2015-02-09 (月) 15:27:39 | 最終更新:2019-11-23 (土) 13:29:30

大規模ソーシャルゲーム参考サイト

Posted by muchag | MySQL,PHP |
初回投稿:2012-01-07 (土) 18:04:22 | 最終更新:2012-01-07 (土) 18:04:22

大規模ソーシャルゲーム用覚書

株式会社インフィニットループ:大規模ソーシャルゲーム開発から学んだ PHP & MySQL 実践テクニック

Posted by muchag | MySQL,PHP |
初回投稿:2012-01-07 (土) 18:04:22 | 最終更新:2012-01-07 (土) 18:04:22

MySQL Workbench

Posted by muchag | MySQL,Tool |
初回投稿:2011-10-24 (月) 19:19:40 | 最終更新:2018-12-19 (水) 20:16:13

MySQL のテーブル設計用 GUI ツール。

新しい記事

【環境】
MySQL Workbench: 5.2.35
DL

MySQL Workbench の Download Now リンクより
プラットフォームを選択して、希望する項目の Download ボタンをクリック。

私は以下を選択した。
プラットフォーム:Microsoft Windows
ファイル:Windows (x86, 32-bit), MSI Installer
      ( mysql-workbench-gpl-5.2.35-win32.msi )

ログインページが現れるので
ログインまたは登録する。

後はリンクから DL 。
 

インストール

私はインストーラ付きなのでダブルクリックでOK。
 

Visual C++ 2010 Runtime

と思いきや、Visual C++ 2010 Runtime がないからインストールできねーよ。
と言われるので
Microsoft Visual C++ 2010 再頒布可能パッケージ (x86)
ここから DL してインストール。
 

インストール

今度こそ~と、ダブルクリック。
後は普通にインストールできる。
 

ローカライズ(日本語化)

ありがたいことに日本語化パッチを配布してくださっている方がおられる。
感謝感謝♪
SOURCEFORGE.JP:OSS_Ja_JPN こちらより
mysql-workbench-gpl_5.2.34_ja6.deb を DL 。
(ちょこっとだけリヴィジョンが違うが気にしない)

で、どうやってパッチを当てればいいのか試行錯誤したけ。
deb なんていう拡張子とは初対面だし・・・ね・・・。

でも、deb を tar.gz に変更すると解凍できるそうで
拡張子を変更して解凍。

そこで出てきた
data フォルダの中に

data\usr\local\share\mysql-workbench\data

また data フォルダが存在する。

そこで MySQL Workbench インストールディレクトリ内にある
data ディレクトリをリネームし、新たに先ほど解凍して出てきた data ディレクトリ

data\usr\local\share\mysql-workbench\data

を、MySQL Workbench インストールディレクトリ内に配置。

これでローカライズ完成。

MySQL Workbench が便利です

Posted by muchag | MySQL,Tool |
初回投稿:2011-10-24 (月) 19:19:40 | 最終更新:2018-12-19 (水) 20:16:13

MySQL でカラム種別を tinyint(1) にしてハマッタ

Posted by muchag | CakePHP,MySQL |
初回投稿:2011-08-09 (火) 19:10:38 | 最終更新:2011-08-09 (火) 19:12:33