調べるのがめんどくさい人のためのMySQLのSQL集

Oracle、PostgreSQLと触ってきてMySQLにやってきました。世界は広いですね。

ちょっとしたSQLなんだけどいちいち調べることが多い今日この頃……。

なんかいろいろと辛い……。

自分のためのSQL集です。随時更新していきたいと思います。

実行環境は5.6を対象としています。

そのままコピペで実行できるようにしているので実行してみて下さい！！

MySQLの情報を確認用SQL

MySQLのバージョンを確認する

SELECT VERSION();

VERSION()
5.6.10-log

現在のタイムゾーンを確認する

SHOW VARIABLES LIKE '%time_zone%';

Variable_name	Value
system_time_zone	UTC
time_zone	SYSTEM

文字コードの確認

SHOW VARIABLES LIKE '%character\_set\_%';

Variable_name	Value
character_set_client	utf8
character_set_connection	utf8
character_set_database	utf8
character_set_filesystem	binary
character_set_results	utf8
character_set_server	utf8
character_set_system	utf8

それぞれの項目についてはこちらを參考に：MySQL 文字コード確認 - Qiita

SHOW VARIABLES LIKE 'collation%';

Variable_name	Value
collation_connection	utf8_general_ci
collation_database	utf8_general_ci
collation_server	utf8_general_ci

データベースの一覧

  SELECT DISTINCT 
         table_schema AS database_name
    FROM information_schema.tables
   WHERE table_schema NOT IN ('mysql', 'perfomance_schema', 'information_schema')
ORDER BY table_schema
;

database_name
hoge
fuga

テーブルの一覧

  SELECT table_schema AS database_name
       , table_name   AS table_name
    FROM information_schema.tables
   WHERE table_schema NOT IN ('mysql', 'perfomance_schema', 'information_schema')
     AND table_type = 'BASE TABLE'
ORDER BY table_schema
       , table_type
       , table_name
;

database_name	table_name
hoge	hoge_hoge
hoge	hoge_fuga
fuga	fuga_fuga
fuga	fuga_hoge

テーブルごとの文字コードの確認

  SELECT table_schema    AS database_name
       , table_name      AS table_name
       , table_collation AS character_info
    FROM information_schema.tables
   WHERE table_schema NOT IN ('mysql', 'perfomance_schema', 'information_schema')
     AND table_type = 'BASE TABLE'
ORDER BY table_schema
       , table_name
       , table_collation
;

database_name	table_name	character_info
hoge	hoge_hoge	utf8_general_ci
hoge	hoge_fuga	utf8_general_ci
fuga	fuga_fuga	utf8_general_ci
fuga	fuga_hoge	utf8_general_ci

テーブルごとのAUTO_INCREMENTの確認

  SELECT table_schema   AS database_name
       , table_name     AS table_name
       , auto_increment AS auto_increment
    FROM information_schema.tables
   WHERE table_schema NOT IN ('mysql', 'perfomance_schema', 'information_schema')
     AND table_type = 'BASE TABLE'
ORDER BY table_schema
       , table_name
;

database_name	table_name	auto_increment
hoge	hoge_hoge	1
hoge	hoge_fuga	9562
fuga	fuga_fuga	133
fuga	fuga_hoge	10

テーブルごとのカラム一覧

-- テーブルごとのカラム一覧
  SELECT table_schema              AS database_name
       , table_name                AS table_name
       , GROUP_CONCAT(column_name) AS column_names
    FROM information_schema.columns
   WHERE table_schema NOT IN ('mysql', 'perfomance_schema', 'infomation_schema')
GROUP BY table_schema
      ,  table_name  

database_name	table_name	column_names
hoge	hoge_hoge	id,hoge_id,hoge
hoge	hoge_fuga	id,hoge_id,fuga_id,fuga
fuga	fuga_fuga	id,fuga_id,fuga
fuga	fuga_hoge	id,fuga_id,hoge_id,hoge

カラム名からテーブルを検索する

-- カラム名からどのテーブルか検索する
  SELECT table_schema
       , table_name
       , column_name
    FROM information_schema.columns
   WHERE table_schema NOT IN ('mysql', 'perfomance_schema', 'infomation_schema')
     AND column_name LIKE '%検索したい項目名%'
ORDER BY table_schema
       , table_name
       , column_name
;

hogeで検索した場合

database_name	table_name	column_name
hoge	hoge_hoge	hoge_id
hoge	hoge_fuga	hoge_id
fuga	fuga_hoge	hoge_id

VIEWの一覧

-- VIEWの一覧
  SELECT table_schema AS database_name
       , table_name   AS view_name
    FROM information_schema.tables
   WHERE table_schema NOT IN ('mysql', 'perfomance_schema', 'information_schema')
     AND table_type = 'VIEW'
ORDER BY table_schema
       , table_name
;

database_name	view_name
hoge	view_1
hoge	view_2
fuga	view_3

---

テーブルのキー情報確認用SQL

テーブルごとのプライマリーキー制約の一覧

-- テーブルごとのプライマリーキー制約の一覧
SELECT table_schema AS database_name
     , table_name   AS table_name
     , column_name  AS primary_key 
  FROM information_schema.KEY_COLUMN_USAGE 
 WHERE constraint_name = 'PRIMARY'

database_name	table_name	primary_key
hoge	hoge_hoge	id
hoge	hoge_fuga	id
fuga	fuga_fuga	id
fuga	fuga_hoge	id

テーブルごとのユニークキー制約の一覧

-- テーブルごとのユニークキー制約の一覧
  SELECT table_schema              AS database_name
       , table_name                AS table_name
       , GROUP_CONCAT(column_name) AS unique_keys 
    FROM information_schema.KEY_COLUMN_USAGE
   WHERE position_in_unique_constraint = 1 
GROUP BY table_schema
       , table_name
;

database_name	table_name	unique_keys
hoge	hoge_hoge	fuga_id
hoge	hoge_fuga	hoge_id,fuga_id,hoge_fuga_id
fuga	fuga_fuga	hoge_id
fuga	fuga_hoge	fuga_id,hoge_id,fuga_hoge_id

テーブルごとの外部キー制約の一覧

-- テーブルごとの外部キー制約の一覧
  SELECT table_schema                                                                               AS database_name
       , table_name                                                                                 AS table_name
       , GROUP_CONCAT(CONCAT(column_name, '=', referenced_table_name, '.', referenced_column_name)) AS referenced
    FROM information_schema.KEY_COLUMN_USAGE
   WHERE referenced_table_name IS NOT NULL
GROUP BY table_schema
       , table_name
;

外部キーの項目がどのテーブルのどのカラムと紐付いているかわかる

database_name	table_name	referenced
hoge	hoge_hoge	fuga_id=fuga.id
hoge	hoge_fuga	hoge_id=hoge.id,fuga_id=fuga.id
fuga	fuga_fuga	hoge_id=hoge.id
fuga	fuga_hoge	fuga_id=fuga.id,fuga_id=fuga.id

テーブルごとのプライマリーキー・ユニークキー・外部キー一覧

キー情報の一覧をまとめたSQLです

全テーブルのキー情報が一括で見れるので超絶便利！！

とりあえずキー情報が知りたかったらこれを実行すればいいかも

-- テーブルごとのプライマリーキー・ユニークキー・外部キー一覧
   SELECT table_info.*
        , primary_info.primary_key
        , unique_info.unique_keys
        , reference_info.referenced
     FROM (
               SELECT table_schema AS database_name
                    , table_name   AS table_name
                 FROM information_schema.tables
                WHERE table_type = 'BASE TABLE'
          ) AS table_info
LEFT JOIN (
                 SELECT table_schema              AS database_name
                      , table_name                AS table_name
                      , GROUP_CONCAT(column_name) AS unique_keys 
                   FROM information_schema.KEY_COLUMN_USAGE
                  WHERE position_in_unique_constraint = 1 
               GROUP BY table_schema
                      , table_name
          ) AS unique_info        
       ON table_info.database_name = unique_info.database_name
      AND table_info.table_name   = unique_info.table_name
LEFT JOIN (
                 SELECT table_schema AS database_name
                      , table_name   AS table_name
                      , column_name  AS primary_key 
                   FROM information_schema.KEY_COLUMN_USAGE
                  WHERE constraint_name = 'PRIMARY'
          ) AS primary_info
       ON table_info.database_name = primary_info.database_name
      AND table_info.table_name    = primary_info.table_name
LEFT JOIN (
                 SELECT table_schema                                                                               AS database_name
                      , table_name                                                                                 AS table_name
                      , GROUP_CONCAT(CONCAT(column_name, '=', referenced_table_name, '.', referenced_column_name)) AS referenced
                   FROM information_schema.KEY_COLUMN_USAGE
                  WHERE referenced_table_name IS NOT NULL
               GROUP BY table_schema
                      , table_name
          ) AS reference_info
       ON table_info.database_name = reference_info.database_name
      AND table_info.table_name    = reference_info.table_name

database_name	table_name	primary_key	unique_keys	referenced
hoge	hoge_hoge	id	fuga_id	fuga_id=fuga.id
hoge	hoge_fuga	id	hoge_id,fuga_id,hoge_fuga_id	hoge_id=hoge.id,fuga_id=fuga.id
fuga	fuga_fuga	id	hoge_id	hoge_id=hoge.id
fuga	fuga_hoge	id	fuga_id,hoge_id,fuga_hoge_id	fuga_id=fuga.id,fuga_id=fuga.id

---

データ容量確認用SQL

データベースごとのサイズ表示

  SELECT table_schema                                                   AS database_name
       , CONCAT(SUM(data_length + index_length) / (1024 * 1024), ' MB') AS db_size
    FROM information_schema.tables
GROUP BY table_schema
ORDER BY table_schema
;

database_name	db_size
hoge	3000.48 MB
fuga	90.6875 MB

データベースごとのテーブル数表示

  SELECT table_schema AS database_name
       , COUNT(*)     AS table_count
    FROM information_schema.tables
   WHERE table_type = 'BASE TABLE'
GROUP BY table_schema
;

database_name	table_count
hoge	59
fuga	22

テーブルごとのサイズ表示

  SELECT table_schema                                                AS database_name
       , table_name                                                  AS table_name
       , CONCAT((data_length + index_length) / (1024 * 1024), ' MB') AS table_size
    FROM information_schema.tables
   WHERE table_type = 'BASE TABLE'
     AND table_schema NOT IN ('mysql', 'perfomance_schema', 'information_schema')
ORDER BY table_schema
       , (data_length + index_length) DESC
       , table_name
;

database_name	table_name	table_size
hoge	hoge_hoge	4964.0000 MB
hoge	hoge_fuga	826.2031 MB
fuga	fuga_fuga	1.5313 MB
fuga	fuga_hoge	0.0469 MB

テーブルごとのレコード数を確認する

  SELECT table_schema                   AS database_name
       , table_name                     AS table_name
       , table_rows                     AS table_rows
    FROM information_schema.tables AS `target`
   WHERE table_type = 'BASE TABLE'
     AND table_schema NOT IN ('mysql', 'perfomance_schema', 'information_schema')
ORDER BY table_schema
       , table_rows DESC
       , table_name
;

database_name	table_name	table_rows
hoge	hoge_hoge	3000
hoge	hoge_fuga	2500
fuga	fuga_fuga	900
fuga	fuga_hoge	13

---

　データベース・テーブル情報なんでも確認用SQL

上の方で細かい単位の確認用SQLを紹介しましたが、めんどくさいので一括で情報を表示するためのSQLです

このSQLを実行すればだいたいの情報は取得できると思います！！

-- データベース、テーブルごとの件数・容量・カラム・キー情報・文字コード・AUTO_INCREMENTを一括で表示する
   SELECT table_info.table_schema    AS database_name
        , database_info.table_count  AS table_count
        , database_info.db_size      AS db_size
        , table_info.table_name      AS table_name
        , table_info.table_rows      AS table_rows
        , table_info.table_size      AS table_size
        , columns_info.column_names  AS columns
        , key_info.primary_key       AS primary_key
        , key_info.unique_keys       AS unique_keys
        , key_info.referenced        AS referenced
        , table_info.auto_increment  AS auto_increment
        , table_info.table_collation AS character_info
     FROM ( 
               -- 全テーブル情報
               SELECT * 
                    , CONCAT((data_length + index_length) / (1024 * 1024), ' MB') AS table_size
                 FROM information_schema.tables 
                WHERE table_schema NOT IN ('mysql', 'perfomance_schema', 'information_schema') 
                  AND table_type = 'BASE TABLE'
          ) AS table_info
LEFT JOIN (
               -- データベースの容量とテーブル数情報
                 SELECT table_schema                                                   AS database_name
                      , CONCAT(SUM(data_length + index_length) / (1024 * 1024), ' MB') AS db_size
                      , SUM(CASE WHEN table_type = 'BASE TABLE' THEN 1 ELSE 0 END)     AS table_count
                   FROM information_schema.tables
               GROUP BY table_schema
          ) AS database_info
       ON table_info.table_schema = database_info.database_name
LEFT JOIN (
               -- テーブルごとのカラム情報
                 SELECT table_schema              AS database_name
                      , table_name                AS table_name
                      , GROUP_CONCAT(column_name) AS column_names
                   FROM information_schema.columns
                  WHERE table_schema NOT IN ('mysql', 'perfomance_schema', 'infomation_schema')
               GROUP BY table_schema
                     ,  table_name           
          ) AS columns_info
       ON table_info.table_schema = columns_info.database_name
      AND table_info.table_name   = columns_info.table_name
LEFT JOIN (
             -- テーブルのキー情報（プライマリー、ユニーク、外部）
                 SELECT table_info.*
                      , primary_info.primary_key
                      , unique_info.unique_keys
                      , reference_info.referenced
                   FROM (
                             SELECT table_schema AS database_name
                                  , table_name   AS table_name
                               FROM information_schema.tables
                              WHERE table_type = 'BASE TABLE'
                        ) AS table_info
              LEFT JOIN (
                               SELECT table_schema              AS database_name
                                    , table_name                AS table_name
                                    , GROUP_CONCAT(column_name) AS unique_keys 
                                 FROM information_schema.KEY_COLUMN_USAGE
                                WHERE position_in_unique_constraint = 1 
                             GROUP BY table_schema
                                    , table_name
                        ) AS unique_info        
                     ON table_info.database_name = unique_info.database_name
                    AND table_info.table_name   = unique_info.table_name
              LEFT JOIN (
                               SELECT table_schema AS database_name
                                    , table_name   AS table_name
                                    , column_name  AS primary_key 
                                 FROM information_schema.KEY_COLUMN_USAGE
                                WHERE constraint_name = 'PRIMARY'
                        ) AS primary_info
                     ON table_info.database_name = primary_info.database_name
                    AND table_info.table_name    = primary_info.table_name
              LEFT JOIN (
                               SELECT table_schema                                                                               AS database_name
                                    , table_name                                                                                 AS table_name
                                    , GROUP_CONCAT(CONCAT(column_name, '=', referenced_table_name, '.', referenced_column_name)) AS referenced
                                 FROM information_schema.KEY_COLUMN_USAGE
                                WHERE referenced_table_name IS NOT NULL
                             GROUP BY table_schema
                                    , table_name
                        ) AS reference_info
                     ON table_info.database_name = reference_info.database_name
                    AND table_info.table_name    = reference_info.table_name
          ) AS key_info
       ON table_info.table_schema = key_info.database_name
      AND table_info.table_name   = key_info.table_name
 ORDER BY table_info.table_schema
        , table_info.table_name
;

database_name	table_count	db_size	table_name	table_rows	table_size	column_names	primary_key	unique_keys	referenced	auto_increment	character_info
hoge	59	3000.48 MB	hoge_hoge	3000	4964.0000 MB	id,hoge_id,hoge	id	fuga_id	fuga_id=fuga.id	1	utf8_general_ci
hoge	59	3000.48 MB	hoge_fuga	2500	826.2031 MB	id,hoge_id,fuga_id,fuga	id	hoge_id,fuga_id,hoge_fuga_id	hoge_id=hoge.id,fuga_id=fuga.id	9562	utf8_general_ci
fuga	22	90.6875 MB	fuga_fuga	900	1.5313 MB	id,fuga_id,fuga	id	hoge_id	hoge_id=hoge.id	133	utf8_general_ci
fuga	22	90.6875 MB	fuga_hoge	13	0.0469 MB	id,fuga_id,hoge_id,hoge	id	fuga_id,hoge_id,fuga_hoge_id	fuga_id=fuga.id,fuga_id=fuga.id	10	utf8_general_ci

---

権限関連のSQL

ユーザーごとの権限一覧

-- ユーザーごとの権限一覧
  SELECT grantee                      AS user
       , is_grantable                 AS is_grantable   -- GRANT権限があるかどうか
       , GROUP_CONCAT(privilege_type) AS privileges
    FROM information_schema.user_privileges
GROUP BY grantee
       , is_grantable
ORDER BY grantee
;

user	is_grantable	privileges
Administrator	YES	CREATE,LOCK TABLES,EVENT,REFERENCES,CREATE VIEW,DELETE,CREATE...
hoge	NO	CREATE,DELETE,ALTER,UPDATE,INDEX,INSERT,DROP,SELECT
fuga	NO	SELECT

データベースごとの権限一覧

-- データベースごとの権限一覧
  SELECT grantee                      AS user
       , table_schema                 AS db
       , GROUP_CONCAT(privilege_type) AS privileges 
    FROM information_schema.schema_privileges 
GROUP BY grantee
       , table_schema
ORDER BY grantee
       , table_schema
;

テーブルごとの権限一覧

-- テーブルごとの権限一覧
  SELECT grantee                      AS user
       , table_schema                 AS db
       , table_name                   AS `table`
       , GROUP_CONCAT(privilege_type) AS privileges 
    FROM information_schema.table_privileges  
GROUP BY grantee
       , table_schema
       , table_name
ORDER BY grantee
       , table_schema
       , table_name
;

user	db	table	privileges
hoge	hoge	hoge_hoge	SELECT
fuga	fuga	fuga_fuga	SELECT,UPDATE

カラムごとの権限一覧

-- カラムごとの権限一覧
  SELECT grantee                      AS user
       , table_schema                 AS db
       , table_name                   AS `table`
       , column_name                  AS `column`
       , GROUP_CONCAT(privilege_type) AS privileges 
    FROM information_schema.column_privileges 
GROUP BY grantee
       , table_schema
       , table_name
       , column_name
ORDER BY grantee
       , table_schema
       , table_name
       , column_name
;

user	db	table	column	privileges
hoge	hoge	hoge_hoge	hoge_name	UPDATE
fuga	fuga	fuga_fuga	fuga_name	UPDATE

ユーザー、データベース、テーブル、カラムごとの権限一覧

全権限が見れて超絶便利！！

參考URL：MySQL権限一覧をきれいに作る方法と、rootユーザー以外で棚卸しする方法 - Qiita

-- ユーザー、データベース、テーブル、カラムごとの権限一覧
  SELECT *
    FROM (
                        SELECT grantee AS user, is_grantable AS is_grantable, '-'          AS db, '-'        AS `table`, '-'         AS `column`, GROUP_CONCAT(privilege_type) AS privileges FROM information_schema.user_privileges   GROUP BY grantee, is_grantable
              UNION ALL SELECT grantee AS user, is_grantable AS is_grantable, table_schema AS db, '-'        AS `table`, '-'         AS `column`, GROUP_CONCAT(privilege_type) AS privileges FROM information_schema.schema_privileges GROUP BY grantee, is_grantable, table_schema
              UNION ALL SELECT grantee AS user, is_grantable AS is_grantable, table_schema AS db, table_name AS `table`, '-'         AS `column`, GROUP_CONCAT(privilege_type) AS privileges FROM information_schema.table_privileges  GROUP BY grantee, is_grantable, table_schema, table_name
              UNION ALL SELECT grantee AS user, is_grantable AS is_grantable, table_schema AS db, table_name AS `table`, column_name AS `column`, GROUP_CONCAT(privilege_type) AS privileges FROM information_schema.column_privileges GROUP BY grantee, is_grantable, table_schema, table_name, column_name
         ) AS authority
ORDER BY user
       , db
       , `table`
       , `column`
;

---

日付関連のSQL

タイムゾーンを変換して現在時刻を取得

UTCからAsia/Tokyoの現在時刻を取得

SELECT NOW()                                  AS utc
     , CONVERT_TZ(NOW(), 'UTC', 'Asia/Tokyo') AS asia_tokyo
;

utc	asia_tokyo
2019-07-06 02:56:43	2019-07-06 11:56:43

Date型を好きな形式に変換する

-- 9999-99-99形式に日付を変換する
SELECT @a := DATE_FORMAT(NOW(), '%Y-%m-%d');
-- 99:99:99形式に日付を変換する
SELECT @b := DATE_FORMAT(NOW(), '%H:%i:%S');
-- （曜日）の形式に日付を変換する
SELECT @c := CASE DATE_FORMAT(NOW(), '%w')
                 WHEN 0 THEN '（日）'
                 WHEN 1 THEN '（月）'
                 WHEN 2 THEN '（火）'
                 WHEN 3 THEN '（水）'
                 WHEN 4 THEN '（木）'
                 WHEN 5 THEN '（金）'
                 WHEN 6 THEN '（土）'
              END;
-- 「9999-99-99 99:99:99（曜日）」の形式
SELECT CONCAT(@a, ' ', @b, @c) AS result;

result
2019-07-06 02:54:35（土）

下記の指定子を使用することができます

指定子	説明
%a	簡略曜日名 (Sun..Sat)
%b	簡略月名 (Jan..Dec)
%c	月、数字 (0..12)
%D	英語のサフィクスを持つ日付 (0th, 1st, 2nd, 3rd, …)
%d	日、数字 (00..31)
%e	日、数字 (0..31)
%f	マイクロ秒 (000000..999999)
%H	時間 (00..23)
%h	時間 (01..12)
%I	時間 (01..12)
%i	分、数字 (00..59)
%j	年間通算日 (001..366)
%k	時 (0..23)
%l	時 (1..12)
%M	月名 (January..December)
%m	月、数字 (00..12)
%p	AM または PM
%r	時間、12 時間単位 (hhss に AM または PM が続く)
%S	秒 (00..59)
%s	秒 (00..59)
%T	時間、24 時間単位 (hhss)
%U	週 (00..53)、日曜日が週の初日、WEEK() モード 0
%u	週 (00..53)、月曜日が週の初日、WEEK() モード 1
%V	週 (01..53)、日曜日が週の初日、WEEK() モード 2、%X とともに使用
%v	週 (01..53)、月曜日が週の初日、WEEK() モード 3、%x とともに使用
%W	曜日名 (Sunday..Saturday)
%w	曜日 (0=Sunday..6=Saturday)
%X	年間の週、日曜日が週の初日、数字、4 桁、%V とともに使用
%x	年間の週、月曜日が週の初日、数字、4 桁、%v とともに使用
%Y	年、数字、4 桁
%y	年、数字 (2 桁)
%%	リテラル 「%」 文字
%x	x (上記にないすべての 「x」)

MySQL :: MySQL 5.6 リファレンスマニュアル :: 12.7 日付および時間関数より

今日、昨日、明日

SELECT @now       := NOW();
SELECT @yesterday := (NOW() - INTERVAL 1 DAY);
SELECT @tomorrow  := (NOW() + INTERVAL 1 DAY);

SELECT @now       AS 今日
     , @yesterday AS 昨日
     , @tomorrow  AS 明日

今日	昨日	明日
2019-06-27 00:41:41	2019-06-26 00:41:41	2019-06-28 00:41:41

１週間前、１週間後

SELECT @own_week_ago    := (NOW() - INTERVAL 7 DAY);
SELECT @own_week_before := (NOW() + INTERVAL 7 DAY);

SELECT @own_week_ago    AS １週間前
     , @own_week_before AS １週間後

１週間前	１週間後
2019-06-20 00:41:41	2019-07-04 00:41:41

先月、来月、３ヶ月後、３ヶ月末

SELECT @last_month         := DATE_FORMAT((NOW() - INTERVAL 1 MONTH), '%Y-%m-%d');
SELECT @next_month         := DATE_FORMAT((NOW() + INTERVAL 1 MONTH), '%Y-%m-%d');
SELECT @three_month_before := DATE_FORMAT((NOW() - INTERVAL 3 MONTH), '%Y-%m-%d');
SELECT @three_month_ago    := DATE_FORMAT((NOW() + INTERVAL 3 MONTH), '%Y-%m-%d');

SELECT @last_month         AS 先月
     , @next_month         AS 来月
     , @three_month_before AS ３ヶ月前
     , @three_month_ago    AS ３ヶ月後

来月	先月	３ヶ月前	３ヶ月後
2019-06-27	2019-05-27	2019-03-27	2019-09-27

今月の月初と月末

SELECT @begin_month := DATE_FORMAT(NOW(), '%Y-%m-01');
SELECT @end_month   := LAST_DAY(NOW());

SELECT @begin_month AS 今月の月初
     , @end_month   AS 今月の月末

今月の月初	今月の月末
2019-06-01	2019-06-30

先月の月初と月末、来月の月初と月末

-- [先月の月初]今月の月末＋１から２ヶ月前を取得し求める
SELECT @last_month_start := DATE_ADD(DATE_ADD(LAST_DAY(NOW()), INTERVAL 1 DAY), INTERVAL -2 MONTH);
-- [先月の月末]今月の月末＋１から１ヶ月前から−１日を取得し求める
SELECT @last_month_end   := DATE_ADD(DATE_ADD(DATE_ADD(LAST_DAY(NOW()), INTERVAL 1 DAY), INTERVAL -1 MONTH),INTERVAL -1 DAY);
-- [来月の月初]現在日付の月末を求めた後＋１日を取得し求める
SELECT @next_month_start := DATE_ADD(LAST_DAY(NOW()), INTERVAL 1 DAY);
-- [来月の月末]現在日付の１ヶ月後の最終日を取得し求める
SELECT @next_month_end := LAST_DAY(DATE_ADD(NOW(), INTERVAL 1 MONTH));

SELECT @last_month_start AS 先月の月初
     , @last_month_end   AS 先月の月末
     , @next_month_start AS 来月の月初
     , @next_month_end   AS 来月の月末

先月の月初	先月の月末	来月の月初	来月の月末
2019-05-01	2019-05-31	2019-07-01	2019-07-31

去年、来年、３年前、３年後

SELECT @last_year         := DATE_FORMAT((NOW() - INTERVAL 1 YEAR), '%Y-%m-%d');
SELECT @next_year         := DATE_FORMAT((NOW() + INTERVAL 1 YEAR), '%Y-%m-%d');
SELECT @three_year_before := DATE_FORMAT((NOW() - INTERVAL 3 YEAR), '%Y-%m-%d');
SELECT @three_year_ago    := DATE_FORMAT((NOW() + INTERVAL 3 YEAR), '%Y-%m-%d');

SELECT @last_year         AS 去年
     , @next_year         AS 来年
     , @three_year_before AS ３年前
     , @three_year_ago    AS ３年後

去年	来年	３年前	３年後
2018-06-27	2020-06-27	2016-06-27	2022-06-27

---

NULL値変換

こちらにわかりやすく書かれていました

mysqlのifnullとcoalesceの違いは何ですか？ - コードログ

NULL値の比較対象の数に応じて使い分けるのが良いかも

IFNULL

引数を２つ取り、１つ目の値がNULLの時は２つ目の引数の値を返す

SELECT IFNULL(NULL, 1), IFNULL(1, 2)
;

IFNULL(NULL, 1)	IFNULL(1, 2)
1	1

COALESCE

２つ以上のパラメータを取り、最初の非NULL値を返す。非NULL値がない場合は、NULLを返す

１つはパラメータをNULL以外にしないと変換ができない……

SELECT COALESCE(NULL, 1)          
     , COALESCE(NULL, 2, NULL)    
     , COALESCE(NULL, 2, 3)       
     , COALESCE(NULL, NULL, NULL) 
;

COALESCE(NULL, 1)	COALESCE(NULL, 2, NULL)	COALESCE(NULL, 2, 3)	COALESCE(NULL, NULL, NULL)
1	2	2	NULL

---

Tips集

数値を３桁区切りにする

SELECT FORMAT(999999999999, 0) AS digit_12
     , FORMAT(999999999, 0)    AS digit_9
     , FORMAT(999999, 0)       AS digit_6
     , FORMAT(999, 0)          AS digit_3
     , FORMAT(9, 0)            AS digit_1

digit_12	digit_9	digit_6	digit_3	digit_1
999,999,999,999	999,999,999	999,999	999	9

好きなデータの表を作成する

何かSQLの構文を試したい時は下記のような感じで記述するとテーブルが無くても好きなデータの表を取得することができます

これを使うとSELECTの結果でINSERTする時に便利です（入れるデータをSELECT文で確認できるし修正が楽ちん）

-- 好きなデータの表を作成する
SELECT *
  FROM (
                      SELECT 'ゴリラ'     AS animal, '地上' AS habitat
            UNION ALL SELECT 'キリン'     AS animal, '地上' AS habitat
            UNION ALL SELECT 'カバ'       AS animal, '水上' AS habitat
            UNION ALL SELECT 'ワニ'       AS animal, '水上' AS habitat
            UNION ALL SELECT 'マントヒヒ' AS animal, '地上' AS habitat
       ) AS animals

habitat	animal
地上	ゴリラ
地上	キリン
水上	カバ
水上	ワニ
地上	マントヒヒ

連番を振る

ROW_NUMBER関数が無いので変数を使用して再現する

-- 連番を振る
SET @row_number = 0;
  SELECT @row_number := @row_number + 1 AS `row`
       , animal
    FROM (
                        SELECT 'ゴリラ'     AS animal
              UNION ALL SELECT 'キリン'     AS animal
              UNION ALL SELECT 'カバ'       AS animal
              UNION ALL SELECT 'ワニ'       AS animal
              UNION ALL SELECT 'マントヒヒ' AS animal
         ) AS animals
ORDER BY animal

row	animal
1	カバ
2	キリン
3	ゴリラ
4	マントヒヒ
5	ワニ

グループごと連番を振る

WINDOW関数が無いので仕方なく下記のやり方で再現する

WINDOW関数使いたいよー・ﾟ・(つД｀)・ﾟ・　　
新しいバージョンはWINDOW関数があるので注意

參考サイト：[SQL]mysqlで同項目毎に連番をつける | 目黒で働く分析担当の作業メモ

これにさらに条件を追加することでグループごと５件取得するなどが可能になる

-- グループごとの連番を振る
  SELECT @row_num := IF(@prev_value = habitat, @row_num + 1, 1) AS no
       , habitat                                                AS habitat
       , animal                                                 AS animal
       , @prev_value := habitat                                 AS prev_value
    FROM (
                        SELECT 'ゴリラ'     AS animal, '地上' AS habitat
              UNION ALL SELECT 'キリン'     AS animal, '地上' AS habitat
              UNION ALL SELECT 'カバ'       AS animal, '水上' AS habitat
              UNION ALL SELECT 'ワニ'       AS animal, '水上' AS habitat
              UNION ALL SELECT 'マントヒヒ' AS animal, '地上' AS habitat
         ) AS animals
       , ( SELECT @row_num    := 1  ) AS ROW_DATA
       , ( SELECT @prev_value := '' ) AS PREV_DATA
ORDER BY habitat
       , animal

no	habitat	animal	prev_value
1	地上	キリン	地上
2	地上	ゴリラ	地上
3	地上	マントヒヒ	地上
1	水上	カバ	水上
2	水上	ワニ	水上

項目の値ごとカウントを取る

CASEを使用しカウント対象かどうかを判断しSUMを使用することで項目の値ごとカウントを取ることが出来る

-- 項目の値ごとカウントを取る
  SELECT COUNT(*)                                        AS all_count
       , SUM(CASE habitat WHEN '地上' THEN 1 ELSE 0 END) AS chijou_count
       , SUM(CASE habitat WHEN '水上' THEN 1 ELSE 0 END) AS suijou_count
    FROM (
                        SELECT 'ゴリラ'     AS animal, '地上' AS habitat
              UNION ALL SELECT 'キリン'     AS animal, '地上' AS habitat
              UNION ALL SELECT 'カバ'       AS animal, '水上' AS habitat
              UNION ALL SELECT 'ワニ'       AS animal, '水上' AS habitat
              UNION ALL SELECT 'マントヒヒ' AS animal, '地上' AS habitat
         ) AS animals

all_count	chijou_count	suijou_count
5	3	2

特定の値を全レコードに付加する

１レコードしか無いテーブルのデータを付加したい時に使ったりします

そもそもCROSS JOINはこんなことぐらいでしか使用したことない……

結合先のデータが２レコード以上あると結合元 * 結合先の数のレコードが出来上がってしまう

-- 特定の値を全レコードに付加する
    SELECT *
      FROM (
                          SELECT 'ゴリラ'     AS animal, '地上' AS habitat
                UNION ALL SELECT 'キリン'     AS animal, '地上' AS habitat
                UNION ALL SELECT 'カバ'       AS animal, '水上' AS habitat
                UNION ALL SELECT 'ワニ'       AS animal, '水上' AS habitat
                UNION ALL SELECT 'マントヒヒ' AS animal, '地上' AS habitat
           ) AS animals
CROSS JOIN (SELECT 'なんか付加するぞ' AS add_data) AS add_data
;

habitat	animal	add_data
地上	ゴリラ	なんか付加するぞ
地上	キリン	なんか付加するぞ
水上	カバ	なんか付加するぞ
水上	ワニ	なんか付加するぞ
地上	マントヒヒ	なんか付加するぞ

特定の項目の一覧を１レコードで取得する

GROUP_CONCATを使用することで特定の項目の一覧を１レコードに収めた状態で取得できます

  SELECT habitat
       , GROUP_CONCAT(animal) AS animal_list
    FROM (
                        SELECT 'ゴリラ'     AS animal, '地上' AS habitat
              UNION ALL SELECT 'キリン'     AS animal, '地上' AS habitat
              UNION ALL SELECT 'カバ'       AS animal, '水上' AS habitat
              UNION ALL SELECT 'ワニ'       AS animal, '水上' AS habitat
              UNION ALL SELECT 'マントヒヒ' AS animal, '地上' AS habitat
         ) AS animals
GROUP BY habitat
;

habitat	animal_list
地上	ゴリラ,キリン,マントヒヒ
水上	カバ,ワニ

ランキングデータを作成

GROUP_CONCAT と SUBSTRING_INDEX と FIND_IN_SET を使用することによりランキングを作成出来ます

GROUP_CONCAT と SUBSTRING_INDEX でTOP〇〇まで作成
FIND_IN_SET でTOP〇〇に含まれているデータを抽出

-- 10都市の人口データからTOP5を求める
    SELECT population_info.*
         , FIND_IN_SET(population_info.population, rank_info.rank) AS rank
      FROM (
                          SELECT '京都市'     AS city, 1475183 AS population
                UNION ALL SELECT '川崎市'     AS city, 1475213 AS population
                UNION ALL SELECT '横浜市'     AS city, 3724844 AS population
                UNION ALL SELECT '名古屋市'   AS city, 2295638 AS population
                UNION ALL SELECT '福岡市'     AS city, 1538681 AS population
                UNION ALL SELECT '大阪市'     AS city, 2691185 AS population
                UNION ALL SELECT '神戸市'     AS city, 1537272 AS population
                UNION ALL SELECT '広島市'     AS city, 1194034 AS population
                UNION ALL SELECT 'さいたま市' AS city, 1263979 AS population
                UNION ALL SELECT '札幌市'     AS city, 1952356 AS population
           ) AS population_info 
CROSS JOIN (
                SELECT SUBSTRING_INDEX(
                                             GROUP_CONCAT(population ORDER BY population DESC)
                                           , ','
                                           , 5
                                      ) AS rank
                  FROM (
                                      SELECT '京都市'     AS city, 1475183 AS population
                            UNION ALL SELECT '川崎市'     AS city, 1475213 AS population
                            UNION ALL SELECT '横浜市'     AS city, 3724844 AS population
                            UNION ALL SELECT '名古屋市'   AS city, 2295638 AS population
                            UNION ALL SELECT '福岡市'     AS city, 1538681 AS population
                            UNION ALL SELECT '大阪市'     AS city, 2691185 AS population
                            UNION ALL SELECT '神戸市'     AS city, 1537272 AS population
                            UNION ALL SELECT '広島市'     AS city, 1194034 AS population
                            UNION ALL SELECT 'さいたま市' AS city, 1263979 AS population
                            UNION ALL SELECT '札幌市'     AS city, 1952356 AS population
                       ) AS population_info 
           ) AS rank_info
     WHERE FIND_IN_SET(population_info.population, rank_info.rank)
  ORDER BY FIND_IN_SET(population_info.population, rank_info.rank)
;

city	population	rank
横浜市	3724844	1
大阪市	2691185	2
名古屋市	2295638	3
札幌市	1952356	4
福岡市	1538681	5

VIEWの一括DROP文作成

  SELECT CONCAT('DROP VIEW ', table_name, ';') AS view_drop_sql  
    FROM information_schema.tables
   WHERE table_schema NOT IN ('mysql', 'perfomance_schema', 'information_schema')
     AND table_type = 'VIEW'
ORDER BY table_schema
       , table_name
;

view_drop_sql
DROP VIEW view_1;
DROP VIEW view_2;
DROP VIEW view_3;

VIEWの一覧を作成するCREATE文の一覧

SELECT CONCAT(TABLE_SCHEMA, '.' , TABLE_NAME)                                                                                                          AS target_view
     , CONCAT('CREATE ALGORITHM=UNDEFINED DEFINER=`', DEFINER, '` SQL SECURITY ', SECURITY_TYPE, ' VIEW `', TABLE_NAME, '` AS ', VIEW_DEFINITION, ';') AS create_view
  FROM information_schema.views
;

target_view	create_view
view_1	CREATE ALGORITHM=UNDEFINED DEFINER=user@% SQL SECURITY DEFINER VIEW view_1 AS select * from hoge_fuga;
view_2	CREATE ALGORITHM=UNDEFINED DEFINER=user@% SQL SECURITY DEFINER VIEW view_2 AS select * from fuga_hoge;
view_3	CREATE ALGORITHM=UNDEFINED DEFINER=user@% SQL SECURITY DEFINER VIEW view_3 AS select * from hoge_hoge;

---

その他

データベースへのコネクション数を確認する

詳しくは公式のドキュメントを参照すること

  SELECT PROCESSLIST_HOST AS PROCESSLIST_HOST
       , COUNT(*)         AS CONNECTION_COUNT
    FROM performance_schema.threads
   WHERE TYPE = 'FOREGROUND' 
GROUP BY PROCESSLIST_HOST
ORDER BY PROCESSLIST_HOST
;

PROCESSLIST_HOST	CONNECTION_COUNT
999.999.999.999	10
888.888.888.888	2
777.777.777.777	3

INFORMATION_SCHEMA.PROCESSLIST テーブルでも確認はできるが下記の理由より performance_schema.threads を使う方が良さそうです

公式ドキュメントより

threads へのアクセスには相互排他ロックは必要なく、サーバーパフォーマンスへの影響は最小です。INFORMATION_SCHEMA.PROCESSLIST と SHOW PROCESSLIST では相互排他ロックが必要になるため、パフォーマンスの低下につながります

トランザクションを使用してデータを更新

詳しくは公式のドキュメントを参照すること

-- トランザクションを開始
START TRANSACTION;

-- データを更新
UPDATE hogehoge SET hoge = 'ゴリラ' WHERE id = 1;
UPDATE hogehoge SET hoge = 'キリン' WHERE id = 2;

-- コミット
-- 更新が失敗しているようだったらROLLBACK
COMMIT;

テーブルのロック状態を確認する

それぞれの項目の意味は公式ドキュメントを参照すること

ストレージエンジンがInnoDBの場合

SELECT * FROM information_schema.innodb_locks;

SQLの実行計画を確認する

なんかこのSQL遅くないって時は実行計画をとりあえずみますよね

EXPLAINの後にSELECT文などを記述すると確認できる

EXPLAIN SELECT * FROM information_schema.tables;

詳しくは下記サイトを參考

• MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.8.2 EXPLAIN 構文
• 漢(オトコ)のコンピュータ道: MySQLのEXPLAINを徹底解説!!
• MySQLでのSQLチューニングについて（EXPLAINの見方） | Opentone Labs.

---

最後に

最近はFrameworkを使用しているとO/Rマッパー君が勝手にSQLを発行してくれるので自分で書くことが少なくなりました。

たまにはSQLを書きたいんじゃー！！