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 'sample1%'

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

update 
user a
left join group b
on a.keyword = b.keyword
set 
a.group_id = b.id

更新できないものは無視

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_INDEX(email, '@', -1) as domain,count(*) 
from users where email is not null 
group by domain order by count(*) desc;

日付期間

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

日付明日や昨日

select DATE_ADD('2019-11-05', INTERVAL 1 DAY); // 2019-11-06
select DATE_SUB('2019-11-05', INTERVAL 1 DAY); // 2019-11-04
select DATE_ADD(NOW(), INTERVAL 1 DAY); // 2019-11-23 12:43:26
select DATE_SUB(NOW(), INTERVAL 1 DAY); // 2019-11-21 12:43:26

値の後にbetweenで、カラム名とカラム名をandで指定する方法

select * from user where '2024-10-30 10:41:49' between create_at and update_at;

コマンドラインを出力

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';