Mysql/sql文DML

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

use [DB名]
例) use db1

テーブルリネーム

RENAME TABLE [table_from] TO [table_to];

datetimeからunixtimeへ

SELECT UNIX_TIMESTAMP(mydatetime)

SELECT UNIX_TIMESTAMP('2025-04-01 00:00:00') AS unix_timestamp;
+----------------+
| unix_timestamp |
+----------------+
|     1743433200 |
+----------------+

unixtimeからdatetimeへ

SELECT FROM_UNIXTIME(1200310304);

テーブルの一覧

SHOW TABLES
テーブル名を絞りたいとき
SHOW TABLES LIKE 'sample1%'

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

UPDATE 
user a
LEFT JOIN team b
ON a.keyword = b.keyword
SET 
a.team_id = b.id

ONの複数カラム紐づけ

ANDで関連付ける

SELECT * FROM user a
LEFT JOIN team b
ON a.keyword = b.keyword
AND a.platform = b.platform

更新できないものは無視

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;

カラム名削除

ALTER TABLE table1 DROP COLUMN column2;

複数

ALTER TABLE table1
 DROP COLUMN column2,
 DROP COLUMN column3;

ロックを取得

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;

GROUP BYの使い方

SELECT user_id, SUM(point) as sum_point FROM t_user WHERE point > 0 GROUP BY user_id;

合計はSUM()を使う

最小はMIN()で、最大はMAX()を使う。

HAVINGの使い方

SELECT user_id, SUM(point) as sum_point FROM t_user WHERE point > 0 GROUP BY user_id HAVING sum_point > 100;

HAVINGはGROUP化した状態から検索するもの

日付期間

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

IN

1カラムで、条件を複数指定できる

SELECT * FROM table1 WHERE age IN (20, 22);

NOT IN

1カラムで、条件を複数指定できるINの否定

SELECT * FROM table1 WHERE age NOT IN (20, 22);