facebook twitter hatena line email

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

提供: 初心者エンジニアの簡易メモ
移動: 案内、 検索
(→‎別テーブルの情報からデータを更新)
(→‎カラムの順序移動)
 
(同じ利用者による、間の21版が非表示)
行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)
 +
 
 +
<pre>
 +
SELECT UNIX_TIMESTAMP('2025-04-01 00:00:00') AS unix_timestamp;
 +
+----------------+
 +
| unix_timestamp |
 +
+----------------+
 +
|    1743433200 |
 +
+----------------+
 +
</pre>
  
 
===unixtimeからdatetimeへ===
 
===unixtimeからdatetimeへ===
−
  select FROM_UNIXTIME(1200310304);
+
  SELECT FROM_UNIXTIME(1200310304);
 +
 
 +
===日付をyyyy-mm-ddへ===
 +
SELECT DATE_FORMAT(updated, '%Y-%m-%d') FROM TABLE1; // 2025-08-19
  
 
===テーブルの一覧===
 
===テーブルの一覧===
−
  show tables
+
  SHOW TABLES
 
  テーブル名を絞りたいとき
 
  テーブル名を絞りたいとき
−
  show tables like 'xxx%'
+
  SHOW TABLES LIKE 'sample1%'
  
 
===別テーブルの情報からデータを更新===
 
===別テーブルの情報からデータを更新===
−
  update
+
  UPDATE
 
  user a
 
  user a
−
  left join group b
+
  LEFT JOIN team b
−
  on a. = b.keyword
+
  ON a.keyword = b.keyword
−
  set
+
  SET
−
  a.group_id = b.id
+
  a.team_id = b.id
 +
 
 +
===ONの複数カラム紐づけ===
 +
ANDで関連付ける
 +
<pre>
 +
SELECT * FROM user a
 +
LEFT JOIN team b
 +
ON a.keyword = b.keyword
 +
AND a.platform = b.platform
 +
</pre>
  
 
===更新できないものは無視===
 
===更新できないものは無視===
行29: 行50:
  
 
===テーブル情報表示===
 
===テーブル情報表示===
−
  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
 +
ADD COLUMN column1 int AFTER column0
 +
ADD COLUMN column2 int AFTER column1;
  
 
===カラム情報変更===
 
===カラム情報変更===
−
  alter table table1 modify column1 int default 0 not null
+
  ALTER TABLE table1 MODIFY column1 int default 0 not null
  
 
===カラム名変更===
 
===カラム名変更===
−
  alter table table1 change column1 column2 int
+
  ALTER TABLE table1 CHANGE column1 column2 int;
 +
 
 +
===カラムの順序移動===
 +
ALTER TABLE table1 MODIFY column1 int AFTER column2; // column2の後にcolumn1を移動
 +
 
 +
===カラム名削除===
 +
ALTER TABLE table1 DROP COLUMN column2;
 +
 
 +
複数
 +
ALTER TABLE table1
 +
  DROP COLUMN column2,
 +
  DROP COLUMN column3;
  
 
===ロックを取得===
 
===ロックを取得===
行58: 行95:
  
 
===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の文字コードを指定して作成===
行70: 行107:
  
 
===主キー設定===
 
===主キー設定===
−
  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
  
 
===もしテーブルがあるとき===
 
===もしテーブルがあるとき===
−
  drop table IF EXISTS [TableName];
+
  DROP TABLE IF EXISTS [TableName];
−
  create table IF NOT EXISTS select * from [TableName]
+
  CREATE TABLE IF NOT EXISTS SELECT * FROM [TableName]
  
 
===実行中sqlコマンド参照===
 
===実行中sqlコマンド参照===
行110: 行147:
 
  from users where email is not null  
 
  from users where email is not null  
 
  group by domain order by count(*) desc;
 
  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化した状態から検索するもの
  
 
===日付期間===
 
===日付期間===
行117: 行164:
 
  where date1 > now() - INTERVAL 7 SECOND
 
  where date1 > now() - INTERVAL 7 SECOND
 
  where date1 > now() - INTERVAL 7 DAY
 
  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;
  
 
===コマンドラインを出力===
 
===コマンドラインを出力===
行133: 行189:
  
 
==大文字小文字を区別しない検索==
 
==大文字小文字を区別しない検索==
−
  select * from table1 where lower(column1) = 'test';
+
  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);

2025年8月7日 (木) 02:02時点における最新版

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

日付をyyyy-mm-ddへ

SELECT DATE_FORMAT(updated, '%Y-%m-%d') FROM TABLE1; // 2025-08-19

テーブルの一覧

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 
ADD COLUMN column1 int AFTER column0
ADD COLUMN column2 int AFTER column1;

カラム情報変更

ALTER TABLE table1 MODIFY column1 int default 0 not null

カラム名変更

ALTER TABLE table1 CHANGE column1 column2 int;

カラムの順序移動

ALTER TABLE table1 MODIFY column1 int AFTER column2; // column2の後にcolumn1を移動

カラム名削除

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);
「https://wiki.nonip.net/index.php?title=Mysql/sql文DML&oldid=20613」から取得