【まとめ】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;