■よく使うコマンド
C:\>mysql -u root -p ログイン。
パスワード設定してなければEnter。
show databases; データベース一覧を見る。
show tables from test_db; テーブル一覧を見る。
use test_db; データベースを掴む。
show tables; 掴むとfrom以下を省略できる。。
show columns from list_t; テーブルの定義を見る。
desc list_t; テーブルの定義を見る。上と同じ。
select * from list_t; レコードを全部見てみる。
select * from list_t where coach_id=14; レコードを見る。
insert list_t set coach_serial=kwt00002; レコードを追加する。
update list_t set coach_serial=kwt00002 where coach_id=14; レコードを更新する。
delete from list_t where id=4; レコードの削除。
delete from serial where serial between a and 1000;
create database test2_db; データベース作成。
drop database test_db; データベース削除。
ALTER TABLE list_t ADD test_c INT(11) AFTER test2_c; カラムの追加。
ALTER TABLE list_t CHANGE test_c test_cc INT; カラム名の変更。
ALTER TABLE m_inventory MODIFY arrival_date DATE NOT NULL DEFAULT 0; カラム定義の変更。
ALTER TABLE m_order_header DROP serial_pair; カラムの削除。
drop table テーブル名; テーブルの削除。
alter table tablename rename to newtablename;　テーブル名の変更
exit; 終了
C:\>net stop mysql MySQLサービス終了
C:\>net start mysql MySQLサービス開始
テーブル追加
CREATE TABLE t_inventory_record (serial INT(11) NOT NULL DEFAULT 0, upd_date DATE NOT NULL DEFAULT 0, status INT(1) NOT NULL DEFAULT 0, stock INT(11) NOT NULL DEFAULT 0, arrival_date DATE NOT NULL DEFAULT 0, before_status INT(1) NOT NULL DEFAULT 0, before_stock INT(11) NOT NULL DEFAULT 0, before_arrival_date DATE NOT NULL DEFAULT 0, before_lastup_date DATE NOT NULL DEFAULT 0);
CREATE TABLE access_log_r (campaign_id INT(11) NOT NULL, log_date date NOT NULL,log_kind text NOT NULL,raw_log longblob NOT NULL,update_date datetime NOT NULL,insert_date datetime NOT NULL);
プライマリーキーの追加
alter table access_log_r add primary key(campaign_id,log_date);
プライマリーキーに変更
ALTER TABLE categories MODIFY id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY;
テーブル作成レコード作成の例
CREATE TABLE users (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
loginid VARCHAR(50),
password VARCHAR(50),
created DATETIME DEFAULT NULL,
updated DATETIME DEFAULT NULL
);
INSERT INTO users (name,loginid,password,created,updated)
VALUES ('テストマン', 'aaaa', 'aaaa', NOW(), NOW());
CREATE TABLE new_hoge LIKE hoge; テーブルのスキーマをコピー
INSERT INTO new_hoge SELECT * FROM hoge; テーブルのデータを挿入

■ＤＢ構築（削除→作成→復旧）
ﾌﾟﾛﾝﾌﾟﾄ
mysqldump -u root -p ec > "ec508.dump"
MySQL
drop database ec;
create database ec;
ﾌﾟﾛﾝﾌﾟﾄ
mysql -u root -p ec < "ec511.dump"

■スキーマだけダンプ
mysqldump hoge --no-data > hoge.dump

■DBが他のサーバーにある場合
mysql -hhoge.host.jp -u hogeuser -phogepasswd hogedatabase
mysqldump -hhoge.host.jp -u hogeuser -phogepasswd hogedatabase > "hoge.dump"

■部分的に重複しているレコード
作成日の年月別で調べたいときとか
select distinct left(created,7) from reports;
+-----------------+
| left(created,7) |
+-----------------+
| 2011-09 |
| 2011-10 |
| 2011-11 |
+-----------------+

■echoを使った例
echo "select * from m_table"|mysql -u root pパスワード dbname;
echo "select * from m_table"|mysql -u root pパスワード dbname > result.txt;

■バージョンを調べる
mysqladmin version

■自動起動の設定方法
# chkconfig mysqld on
# chkconfig --list mysqld
mysqld 0:off 1:off 2:on 3:on 4:on 5:on 6:off
2?5がonであることを確認。

■大量データーを扱うときの注意
The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay
対象データーが大きくなると、↑のエラーがぼちぼち出ます。
設定を変えることによってスルーすることも出来ますが根本的な解決にはならないので、以下。
・JOIN しすぎると返ってこなくなります。
　INNER JOIN とかもってのほか。
　ソースが多少ダサくなってもSQL文を数回に分けたり、有る程度絞ってからループ文で回すなどすると100倍早くなります。というか処理してくれます。
・WHERE テーブル名 IN (v1,v2,v3,......) も危険。
　WHERE IN は結構早くて重宝するのですが、引数の部分で、対象のデーターが巨大だったりで、引数が多くなったりすると処理してくれなくなります。
　ここは反対に括弧の中の v1,v2,v3,...... の部分を SELECT ID FROM ? に置き換えてやると帰ってくるようになります。実質的には同じ内容でもSELECT文を書くのが良いみたいです。

■複数のデータベースを削除する
以下の様なテキストファイルでも作って cat drop.sql | mysql -u root -p
「drop.sql」
drop database if exists masu1;
drop database if exists masu2;
drop database if exists masu3;
drop database if exists masu4;
drop database if exists masu5;

■table2 に存在しない id 値を持つすべてのレコード（table2 に対応するレコードがないすべてのレコード）が table1 で検索される。
mysql> SELECT table1.* FROM table1 LEFT JOIN table2 ON table1.id=table2.id WHERE table2.id IS NULL;

■重複しているものを１つにまとめて表示
SELECT * FROM t_account GROUP BY name1_1 HAVING COUNT(name1_1)>1;

■テーブル名にAとかBとか
SELECT A.* FROM admin A, license B WHERE B.paid_date LIKE "2009%" AND A.id = B.admin_id GROUP BY A.id;

■日本語が入らない時
/etc/my.conf に以下を書き足して /etc/init.d/mysqld restart
[client]
default-character-set=ujis
[mysql]
default-character-set=ujis

■文字の置換え
TRANSLATEは、検索文字列式と置換文字列式を一文字づつ対応させて置き換えます。
REPLACEは、単語単位の置換えです。
例：
　文字列式=abcdefdcba
　検索文字列式=abcd
　置換文字式=置換文字
TRANSLATE(文字列式, 検索文字列式, 置換文字列式)　→　置換文字ef字文換置
REPLACE(文字列式, 検索文字列式, 置換文字列式)　　→　置換文字efdcba

■文字連結
update contents set meta=concat('aaaaa',カラム名);

■検索条件文
voice_? と index 以外。
select meta from contents where not(filename like "voice_%") and not(filename = 'index');

■IDをふりなおす（＋値）
update image set id=id+値;

■IDをふりなおす（１から）
mysql> ALTER TABLE my_table DROP id;
mysql> ALTER TABLE my_table ADD id INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

■MySQL最大値
select max(id) from image;

■csvﾌｧｲﾙに出力
select A.iNumber,A.szTitle,A.szBody,B.szTitle,B.szBody from bbs_oya A,bbs_ko B where A.iNumber=B.iOya_number order by A.iNumber into outfile '/home/bbs.csv' fields terminated by ',';

■特定のテーブルだけダンプ
mysqldump -u root -p databasename tablename > "tablename.dump"

■それを違うＤＢにコピー
mysql -u root -p databasename2 < "tablename.dump"

■メモ
update const set disp='当選者数／応募者数' where group_code='data_kind' and item_id='3';

■keyカラムを指定しないとdeleteできないようにmy.cnfにsafe-updatesと書く
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
safe-updates
或いは起動時に --safe-updates を付ける
mysql -u root -p --safe-updates

■safe-updatesを無視する
my.cnf に書いてある場合は --safe-updates=0 をつける
mysql -u root -p --safe-updates=0

■my.cnf の作成
以下のところからコピーして置きます。
cp /usr/local/mysql/share/mysql/my-small.cnf /etc/my.cnf

■safe-updates時のﾚｺｰﾄﾞのdelete
delete from m_inventory where serial>0 limit100;

■圧縮の方法
tar zcvf tabi.tgz patchfiles
左圧縮後ファイル名　右圧縮されるファイル

■解凍の方法
tar zxf 圧縮ファイル名

■ユーザ一覧
mysql -u root -p
use mysql;
select * from user;

■ユーザ追加
mysql database
（全権限）
GRANT ALL ON *.* TO user@localhost IDENTIFIED BY 'password';
(権限制限)
GRANT SELECT,INSERT,UPDATE,DELETE ON *.* TO user@localhost IDENTIFIED BY 'password';
※database,user,passwordは任意の文字列

■ユーザ削除
mysql> DELETE FROM mysql.user WHERE user='username' and host='hostname';
mysql> FLUSH PRIVILEGES;

■文字コード変更
/etc/my.cnf
default-character-set ujis
とかにする。ujis は EUC Japanese のこと。
コマンドラインではこう。
set names ujis;

■現在の文字コードの状態を調べる
１．現在の設定を表示
status
２．文字コード関係(ぽい)変数を表示
show variables like "char%";

■文字化け、違う環境にて
$dbh->do('set character set ujis') がバージョンによって要ったり要らなかったりなので
my $dbh = DBI->connect( get_property('data_source'), get_property('db_user'), get_property('db_password') );
$dbh->do('set character set ujis') if((get_property('mysql_version') || '5')=~ /^4/o);
return $dbh;

■localPCのMySQL
/usr/local/mysql/bin/mysqld_safe --user=mysql &
/usr/local/mysql/bin/mysqladmin -u root -p shutdown

■データベースからデータだけ抜く
grep INSERT db20070409.dump > db20070409.insert.dump

■ログの設定
/etc/my.cnf
log=/var/log/mysql.log
　> /var/log/mysql.log
/etc/init.d/mysqld restart

■シンボリックリンクを貼る
ln -s /usr/local/mysql/bin/mysqldump mysqldump

■リード例
select * from $B where name like '%$name%' and del_flg='active' order by disp_order,serial"
like あいまい検索 %%ではさむと部分検索になる。
and 条件追加。
order by ソートのキー。複数指定可能。後ろにDESCをつけると降順。

■my.conf
上コマンドライン用のツーツの設定
下PerlやPHPからアクセスするときに必要なので追加して下さい。
[mysql]
default-character-set=sjis

[mysqld]
default-character-set=sjis
old-passwords

（2006.1.11 書き忘れててもﾌﾞﾗｳｻﾞ(Perl)から見れた(^-^;)

■ERROR 2003: Can't connect to MySQL server on 'localhost' (10061)
ある日突然繋がらなくなりました。
＿|￣|○
my.iniを旧に戻しても繋がりませんでした。
my.iniを旧に戻してＰＣを再起動したら繋がりました。
このあとmy.iniを元に戻して見たら見えました。ＰＣ再起動はしていません。
b
↑
# The default character set that will be used when a new schema or table is
# created and no character set is defined
default-character-set=sjis
ここだけshisにして他のdefault-character-setを全てeucにしたら繋がるし化けません

■データベースの保存復元のLinux→Windowsで日本語が文字化け
いろいろ試したけどutf8で半分だけ見える。(-_-;
linuxでダンプとると強制的にutf8になる模様。バージョンによっては以下の対応で直
ることがあるらしいが、今の環境ではダメでした。binalyが指定できない。
↓
問題になったのは、２の部分で文字化けに悩まされてました。
結果的には、mysqldumpの文字コードの問題（内部で強制的にutf-8に変換してしまう）のためで、
my.cnfの[mysqldump]のdefault-character-setをbinaryにしたら解決しました。
/etc/my.cnf に下記行を追加
[mysqldump]
default-character-set=binary
ちなみにcharacter-set = ujis等に指定しても文字化けするのでbinary以外はだめみたいです。
あとは、Linux上バックアップ
$ mysqldump -u root -pパスワード DB名 > backup.sql
Windows上でデータベースを作成してからリストア
mysql > create database データベース名
$ mysql -u root -pパスワード DB名 < backup.sql

■データベースの保存と復元
Windowsのコマンドプロンプトから。
保存
C:\>mysqldump -u root -p test_db > "test_db.dump"
復元
C:\>mysql -u root -p test_db < "c:\test_db.dump"
違うMySQLへの復元するときはあらかじめ受け皿を作っておく。
C:\mysql -u root -p
mysql> create database test_db;
mysql> exit;
削除
また、既存のＤＢの中にテーブルを復旧させる場合は、同じ名前のテーブルがすでにその
ＤＢにあると復旧は行われませんので、そのテーブルを削除しておきます。
mysql> drop database test_db;