【まとめ】mysql便利コマンド

・ログインコマンド(unix)

（例1）ローカルのmysqlサーバにログイン
$> mysql -u USERNAME -p
$> mysql -uUSERNAME -pPASSWORD
（例2）ネットワーク上のmysqlサーバにログイン
$> mysql -h HOSTNAME -u USERNAME -p
$> mysql -hHOSTNAME -uUSERNAME -pPASSWORD

・SHOW DATABASES --- データベース一覧表示

mysql> SHOW DATABASES;

・USE --- デフォルトデータベース設定

mysql> use DBNAME;

・SHOW TABLES --- テーブル一覧表示

mysql > SHOW TABLES;

・DATE_FORMAT --- DATETIME型の整形

mysql> SELECT DATE_FORMAT(create_date, '%Y/%m') 年月 FROM items;

・STR_TO_DATE --- 文字列のDATETIME型変換

mysql> SELECT COUNT(1) FROM items WHERE create_date = STR_TO_DATE('2014-11-21 13:10:40', '%Y-%m-%d %H:%i:%s');

・INTO OUTFILE --- テーブルデータのCSVエクスポート

mysql> SELECT shop_name FROM items INTO OUTFILE 'C:\\Users\hogehoge\\items.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"';

・LOAD DATA INFILE --- CSVファイルのインポート

（例1）ローカルマシンのCSVをmysqlサーバ上のテーブルにインポート
mysql> LOAD DATA LOCAL INFILE 'C:\\Users\hogehoge\\items.csv' INTO TABLE items FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"';
（例2）mysqlサーバ上のCSVをテーブルにインポート
mysql> LOAD DATA INFILE '/home/hogehoge/items.csv' INTO TABLE items FIELDS TERMINATED BY ',';

・もっと簡単なデータエクスポート（unixコマンド）

$> mysql -uUSERNAME -pPASSWORD -B -N -e 'SELECT shop_name FROM items;' > items.tsv
※1行目ヘッダーなしでタブ区切りで出力

・REGEXP --- 正規表現検索

（例）数字-数字-数字（4桁）でヒットするもの
mysql> SELECT shop_name FROM items WHERE shop_name REGEXP '[0-9]+-[0-9]+-[0-9]{4}';
（mysql正規表現のお試し方法）
mysql> SELECT if('aaa' REGEXP 'aa','hit','nohit');

・遅いクエリーを探す

（例1）その瞬間遅いクエリーを探す
mysql> SELECT * FROM `information_schema`.`PROCESSLIST` WHERE COMMAND='Query' ORDER BY TIME DESC;
（例2）過去に遅かったクエリーを探す
→my.cnfからslow.logの場所を特定↓が例
　log-slow-queryes = /var/lib/mysql/slow.log
　→ slow.logをgrepして遅いクエリをmysqlデータベース単位に集計
　　$> cat /var/lib/mysql/slow.log|grep use|sort|uniq -c | sort -rn

・Oracleで言うところのROWNUMをつける

（例）上から順番に番号を振る
mysql> SELECT  @i:=@i+1 AS rownum, shop_name FROM items, (SELECT @i:=0) DUMMY;

・RAND(MD5(CURRENT_DATE())) --- ○○次ランダムソート

（例1）日次ランダムソート
mysql> SELECT shop_name FROM items ORDER BY RAND(MD5(CURRENT_DATE()));
（例2）月次ランダムソート
mysql> SELECT shop_name FROM items ORDER BY RAND(MD5(DATE_FORMAT(NOW(),'%Y%m')));
（例3）時次ランダムソート
mysql> SELECT shop_name FROM items ORDER BY RAND(MD5(DATE_FORMAT(NOW(),'%Y%m%d%H')));
（例4）分次ランダムソート
mysql> SELECT shop_name FROM items ORDER BY RAND(MD5(DATE_FORMAT(NOW(),'%Y%m%d%H%i')));
（例5）秒次ランダムソート
mysql> SELECT shop_name FROM items ORDER BY RAND(MD5(DATE_FORMAT(NOW(),'%Y%m%d%H%i%s')));
（例6）週次ランダムソート
mysql> SELECT shop_name FROM items ORDER BY RAND(MD5(DATE_FORMAT(NOW(),'%X%V')));

・テーブルのコピー

mysql> CREATE TABLE items_new LIKE items;
mysql> INSERT INTO items_new SELECT * FROM items;

・CASE WHEN --- 条件集計

mysql> SELECT
        SUM(CASE WHEN kbnA = '5' THEN 1 ELSE 0 END) AS kbnA5_count
       ,SUM(CASE WHEN kbnB = 'S' THEN 1 ELSE 0 END) AS kbnBS_count
       FROM items;