Mysql/sql文DML

DBを指定する方法（カレントDBを決定）

use [DB名]
例) use db1

テーブルリネーム

rename table [table_from] to [table_to];

datetimeからunixtimeへ

select UNIX_TIMESTAMP(mydatetime)

unixtimeからdatetimeへ

select FROM_UNIXTIME(1200310304);

テーブルの一覧

show tables
テーブル名を絞りたいとき
show tables like 'xxx%'

別テーブルの情報からデータを更新

update 
table1 a
left join table2 b
on a.keyword = b.keyword
set 
a.view0 = b.view0

更新できないものは無視

INSERT IGNORE INTO test VALUES(1,100);

テーブル情報表示

desc table1;

テーブルcreate文表示

show create table table1;

カラム追加

alter table table1 add column column1 int after column0

カラム情報変更

alter table table1 modify column1 int default 0 not null

カラム名変更

alter table table1 change column1 column2 int

ロックを取得

SELECT GET_LOCK('test', 10)  --タイムアウト時間 10秒

ロックを開放

SELECT RELEASE_LOCK('test')

トランザクション(innodbでのみ使用可能）

begin;
commit;
rollback;

MyISAM,InnoDBを指定して作成

create table table1(id int) engine=myisam

DBエンジンを変更する

alter table table1 engine=innodb;

初期文字コードを指定して作成

create table table1(id int) default charset=utf8

DBの文字コードを指定して作成

CREATE DATABASE db1 DEFAULT CHARACTER SET utf8;

DBの文字コードを変更

ALTER DATABASE db1 DEFAULT CHARACTER SET utf8;

主キー設定

alter table table1 add primary key(id1, id2);

主キー削除

alter table table1 drop primary key

unique作成

alter table table1 add unique index_id1(id1)
                              ↑index名

index作成

alter table table1 add index index_id1(id1)
                              ↑index名

index削除

alter table table1 drop index index_id1

auto_increment設定

create table test(id1 int auto_increment, primary key(id1));

変更（indexをつけたフィールドのみ適用

alter table test modify id1 int auto_increment

もしテーブルがあるとき

drop table IF EXISTS [TableName];
create table IF NOT EXISTS select * from [TableName]

実行中sqlコマンド参照

mysql processlist;

mysql設定一覧

SHOW VARIABLES

mysqlのコネクションのタイムアウトを調べるsql文

SHOW VARIABLES LIKE '%timeout%';

システム変数一覧 http://www.limy.org/program/db/mysql/mysql_variables.html

メールドメイン一覧

SELECT SUBSTRING(email ,instr(email ,'@')) , count(*)
FROM `t_test`
group by 
SUBSTRING(email ,instr(email ,'@'))

日付期間

where date1 between '2009/3/1 0:0:0' and '2009/3/31 23:59:59'

where date1 > now() - INTERVAL 7 HOUR
where date1 > now() - INTERVAL 7 SECOND
where date1 > now() - INTERVAL 7 DAY

コマンドラインを出力

tee d:\test.txt
desc test;
notee

key重複挿入

// 重複があっても無視
INSERT IGNORE INTO table (f1,f2) VALUES ('d1','d2');
// 重複があると更新
INSERT INTO table (f1,f2) VALUES ('d1','d2') ON DUPLICATE KEY UPDATE f2='d2';

auto_increment変更

alter table table1 auto_increment=1;

大文字小文字を区別しない検索

select * from table1 where lower(column1) = 'test';