MySQLでよく使うコマンド一覧まとめ ref: http://qiita.com/masarufuruya/items/9ff82e885709980ded0e

file0.txt

select host,user from mysql.user

file1.txt

mysqlにログインしてselect version()

file10.txt

truncate table テーブル名

file11.txt

mysqldump -u root -p データベース名 > test.sql

特定のテーブルをダンプする
mysqldump -u root -p データベース名 テーブル名 > test.sql

file12.txt

mysql -u root -p データベース名 < test.sql

file13.txt

alter table テーブル名 rename as 新テーブル名

file14.txt

alter table テーブル名 change column 既存の列名 新しい列名 型名 制約

file15.txt

describe テーブル名

file16.txt

create database testdb;
grant all on testdb.* to root@localhost identified by 'hogehoge'; 

file17.txt

drop database 〜

file2.txt

create table if not exists users(
user_id INT AUTO_INCREMENT,
password VARCHAR(30) NOT NULL,
name VARCHAR(140) NOT NULL,
regist_date TIMESTAMP NOT NULL,
PRIMARY KEY(user_id)
)ENGINE=MyISAM DEFAULT CHARSET=utf8;

file3.txt

drop table テーブル名

file4.txt

select * from tweets;
select * FROM tweets WHERE UID='nak';
select * FROM tweets WHERE MESSAGE LIKE 'he%';

file5.txt

select cm.club_name from club_master as cm where club_id 
in(select uj.club_id from user_master as um inner join user_join_club as uj on um.user_id = uj.user_id)

file6.txt

select um.name_kanji,cm.club_name from user_master as um
inner join user_join_club as uj on um.user_id = uj.user_id
inner join club_master as cm on uj.club_id = cm.club_id

file7.txt

insert into tweets
('TID','UID','MESSAGE','DATEOFMSG')
values('1','nak','oraora','2012-10-22');

file8.txt

update tweets set
message='おらおら' where uid='nak';

file9.txt

delete from tweets where uid='wo2';