mysql tips(コマンド集)

---shellから------

▼DB一覧の表示
mysqlshow

▼DB内テーブルの表示
mysqlshow [dbname]

▼パスワードの変更
$ mysql -u root mysql
mysql> update user set Password=password('hissql') where user='root';
もしくは
$ mysqladmin -u root password [newpassword]


---MySQLから---

▼DB一覧の表示
show databases;

▼テーブルの表示
show tables from [dbname];

▼カラムの表示
show fields from [tablename] from [dbname];


---基本SQL文---

▼検索
select * from [tablename];
ex) mysql> select * from test_table;


▼挿入
insert into [tablename] ([Field],[Field],......) values (*,*,....);
ex) mysql> insert into test_table (hoge,moe) values (1,'aa');


▼更新
update [tablename] set [Field]='value' where [Field] = 'value';
ex) mysql> update test_table set hoge=2 where moe='aa';

▼削除
delete from [tablename] where....;
ex) mysql> delete from shintaro where hoge=1;

▼テーブル作成
create table [tablename] (field kata,field kata);
ex) mysql> create table test_table (num int(2),moji char(2));

▼行の数を知る
mysql> select count(*) from [tablename];


---定義SQL文---

▼テーブル名変更
alter table OLDTBLNAME rename to NEWTBLNAME;

▼カラム追加
alter table TBLNAME add column (COLNAME DATATYPE);

▼カラム削除
alter table TBLNAME drop column COLNAME;

▼インデックス追加
alter table TBLNAME add index IDXNAME (IDXCOLUMN);

---権限SQL文---

▼権限付与
mysql> grant all privileges on *.* to mysql@localhost identified by 'yoursql' with grant option;
mysql> grant all privileges on *.* to mysql@"%" identified by 'yoursql' with grant option;

▼完全なMYSQLスーパーユーザーを作る
mysql> GRANT ALL PRIVILEGES ON *.* TO [username]@localhost IDENTIFIED BY '[password]' WITH GRANT OPTION;
mysql> GRANT ALL PRIVILEGES ON *.* TO [username]@"%" IDENTIFIED BY '[password]' WITH GRANT OPTION;



---その他---

▼DBの選択
mysql>use DBNAME;

▼DB作成
mysql>create database DBNAME;

▼DB削除
mysql>drop database DBNAME;

▼外部SQLファイルの実行
$ mysql DATABASE_NAME < temp.sql
（これでtemp.sqlに格納されているSQL文が実行される。）

▼ファイルへのエクスポート(実行結果を外部ファイルへ出力)
$ mysql test > out.txt
mysql> select * from test_table;
mysql> quit
（out.txtにselect文の実行結果が返ってくる。）

▼mysqldump(dumpファイル出力)
/usr/local/mysql/bin/mysqldump --all-databases > /path/to/backup_dir/dump.dat -u root --password=[password]

▼ファイルからインポート
mysql> load data local infile "hoge.csv" into table TABLE_NAME;
（※標準はタブ区切り。もしカンマ区切りのCSVファイルをインポートしたい場合は、最後に「fields terminated by ','」と付け加える。）

▼ファイルからインポート(その2)
最新のMySQLではload data....の文は使えなくなった様子なので、mysqlimportを使ったインポートをします。
mysqlimport --local --fields-terminated-by=',' DBNAME /FULL/PATH/TABLENAME.txt
--localがないとエラーになってしまうので必ずつけましょう。

▼ファイルからインポート(その3)
mysql -u root --password=[password] TARGET_DB < batch_sql.txt


▼SQLログの記録
mysqld_safe --log &
で起動すれば実行したSQLのログを記録してくれる。ログの出力場所は/usr/local/var。(たぶん環境によって異なります)
ウェブアプリのデバッグなどに便利です。

▼一括置換
update TABLE set column = replace(column, "置換前ワード", "置換後ワード");

▼今日の日付と月、前の月をまとめてゲット
select
DATE_FORMAT(current_date, '%Y/%m/%d') as today,
DATE_FORMAT(current_date, '%Y/%m') as this_month,
DATE_FORMAT(DATE_ADD(current_date, INTERVAL -1 MONTH), '%Y/%m') as before_month;


---チューニング関連---

▼ステータスの確認
# mysqladmin status
# mysqladmin variables

▼データファイルの検査
# myisamchk /usr/local/var/DBNAME/TBLNAME.MYI
※「・・・is usable but should be fixed」と出たら修復の必要がある。
# myisamchk -d /usr/local/var/DBNAME/TBLNAME.MYI

▼データファイルの修復
#myisamchk -r /usr/local/var/DBNAME/TBLNAME.MYI
#myisamchk -o /usr/local/var/DBNAME/TBLNAME.MYI
ヘルプによると、-rはrecoverで、-oはold recoverらしい。念のため両方やっておいたほうがよさそう。