「Mysql/sql文DML」の版間の差分

2025年5月14日 (水) 16:40時点における版 (ソースを閲覧) Admin (トーク | 投稿記録) (→‎カラム名変更) ←古い編集		2025年5月14日 (水) 16:42時点における版 (ソースを閲覧) Admin (トーク | 投稿記録) 新しい編集→
行4:		行4:
	===テーブルリネーム===		===テーブルリネーム===
−	rename table [table_from] to [table_to];	+	RENAME TABLE [table_from] TO [table_to];
	===datetimeからunixtimeへ===		===datetimeからunixtimeへ===
−	select UNIX_TIMESTAMP(mydatetime)	+	SELECT UNIX_TIMESTAMP(mydatetime)
	===unixtimeからdatetimeへ===		===unixtimeからdatetimeへ===
−	select FROM_UNIXTIME(1200310304);	+	SELECT FROM_UNIXTIME(1200310304);
	===テーブルの一覧===		===テーブルの一覧===
−	show tables	+	SHOW TABLES
	テーブル名を絞りたいとき		テーブル名を絞りたいとき
−	show tables like 'sample1%'	+	SHOW TABLES LIKE 'sample1%'
	===別テーブルの情報からデータを更新===		===別テーブルの情報からデータを更新===
−	update	+	UPDATE
	user a		user a
−	left join group b	+	LEFT JOIN group b
−	on a.keyword = b.keyword	+	ON a.keyword = b.keyword
−	set	+	SET
	a.group_id = b.id		a.group_id = b.id
行29:		行29:
	===テーブル情報表示===		===テーブル情報表示===
−	desc table1;	+	DESC table1;
	===テーブルcreate文表示===		===テーブルcreate文表示===
−	show create table table1;	+	SHOW CREATE TABLE table1;
	===カラム追加===		===カラム追加===
−	alter table table1 add column column1 int after column0	+	ALTER TABLE table1 ADD COLUMN column1 int after column0
	===カラム情報変更===		===カラム情報変更===
−	alter table table1 modify column1 int default 0 not null	+	ALTER TABLE table1 MODIFY column1 int default 0 not null
	===カラム名変更===		===カラム名変更===
行61:		行61:
	===DBエンジンを変更する===		===DBエンジンを変更する===
−	alter table table1 engine=innodb;	+	ALTER TABLE table1 engine=innodb;
	===初期文字コードを指定して作成===		===初期文字コードを指定して作成===
−	create table table1(id int) default charset=utf8	+	CREATE TABLE table1(id int) default charset=utf8
	===DBの文字コードを指定して作成===		===DBの文字コードを指定して作成===
行73:		行73:
	===主キー設定===		===主キー設定===
−	alter table table1 add primary key(id1, id2);	+	ALTER TABLE table1 ADD PRIMARY key(id1, id2);
	===主キー削除===		===主キー削除===
−	alter table table1 drop primary key	+	ALTER TABLE table1 DROP PRIMARY key
	===unique作成===		===unique作成===
−	alter table table1 add unique index_id1(id1)	+	ALTER TABLE table1 ADD UNIQUE index_id1(id1)
	↑index名		↑index名
	===index作成===		===index作成===
−	alter table table1 add index index_id1(id1)	+	ALTER TABLE table1 ADD INDEX index_id1(id1)
	↑index名		↑index名
	===index削除===		===index削除===
−	alter table table1 drop index index_id1	+	ALTER TABLE table1 DROP INDEX index_id1
	===auto_increment設定===		===auto_increment設定===
−	create table test(id1 int auto_increment, primary key(id1));	+	CREATE TABLE test(id1 int auto_increment, primary key(id1));
	変更（indexをつけたフィールドのみ適用		変更（indexをつけたフィールドのみ適用
	alter table test modify id1 int auto_increment		alter table test modify id1 int auto_increment

---

2025年5月14日 (水) 16:42時点における版

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;

カラム名削除

ALTER TABLE table1 DROP COLUMN column2;

ロックを取得

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