[MySQL] チートシート

select version();

show databases;

use database_name;

select database();

-- データベースの文字セットをUTF8MB4で作成
create database database_name character set utf8mb4;
show databases;

-- パスワードを生成
select password('user_password');

-- 生成したパスワードでユーザーを新規作成
create user 'user_name'@'localhost' identified by password '*xxxx'; -- ↑「*xxxx」はpassword()で取得した値

-- 新規作成したユーザーにデータベースへのアクセス権を付与
grant all on *.* to 'user_name'@'localhost';
grant file on *.* to 'user_name'@'localhost';

select
  table_schema
  , sum(data_length + index_length) / 1024 / 1024 / 1024 AS GB
from
  information_schema.tables 
group by
  table_schema 
order by
  sum(data_length + index_length) desc
;

-- データベースの照合順序確認と変更
show variables like '%collation%';
alter database collate 'utf8mb4_bin';

select * from mysql.user;

-- パスワードを生成
select password('user_password');

-- 生成したパスワードでユーザーを新規作成
create user 'user_name'@'localhost' identified by password '*xxxx'; -- ↑「*xxxx」はpassword()で取得した値

-- 新規作成したユーザーにデータベースへのアクセス権を付与
grant all on *.* to 'user_name'@'localhost';
grant file on *.* to 'user_name'@'localhost';

-- ユーザーごとの権限確認
show grants for 'username'@'localhost';

-- データベースに対するユーザーの権限確認
select * from mysql.db;

show create table table_name;

alter table table_name add primary key (column_name_1, column_name_2);

select
  t.table_name, t.table_comment, t.table_type, t.engine, t.row_format
  , c.column_name, c.column_comment, c.data_type, c.is_nullable, c.character_maximum_length, c.character_octet_length, c.numeric_precision, c.numeric_scale, datetime_precision
from
  information_schema.tables t
  inner join information_schema.columns c
    on c.table_schema = t.table_schema
    and c.table_name = t.table_name
where
  t.table_schema = 'table_schema'
  AND t.table_name = 'table_name'
order by
  t.table_name
  , c.ordinal_position
;

show table status from database_name;
alter table table_name collate 'utf8mb4_bin';

show index from table_name;

create index index_name on table_name(column_name_1, column_name_2);

select * from information_schema.columns c where c.table_schema = 'table_name';

alter table table_name add column column_name {data_type} {null/not null} comment 'comment' after column_name_1;

-- 型変更
alter table table_name modify column_name {data_type} {null/not null};

-- カラム名・デフォルト値変更
alter table table_name change column_name new_column_name {data_type} {null/not null} {default value};

alter table table_name drop column column_name;

select table_schema, table_name, column_name, data_type, character_maximum_length, character_set_name, collation_name
  from information_schema.columns
  where table_schema = 'database_name'
;
alter table table_name modify column column_name varchar(256) collate 'utf8mb4_bin';

delimiter //
create procedure create_number_table(in max_number int)
begin
  declare num int;
  set num = 1;
 
  drop table if exists __numbers;
  create table __numbers(
    number int not null
    , primary key (number)
  );
 
  while max_number > num do
    insert into __numbers(number) values(num);
    set num = num + 1;
  end while;
END;
//

drop procedure if exists stored_procedure_name;

call stored_procedure_name(10);

select * from table_name where column_1 = 'condition'

insert into table_name(column_1, column_2) values ('value_1', 'value_2');
insert into table_name select column_1, column_2 from table_name_1;

update table_name set column_1 = 'value_1' where column_1 = 'condition';

update
  main m
  left outer join sub s
    on s.key = m.key
set
  m.value1 = s.value1
where
  m.key in (1, 2)
;

delete from table_name where column_1 = 'condition';

mysql -hlocalhost -P3306 -uuser_name -ppassword database_name

mysql -hlocalhost -P3306 -uuser_name -ppassword database_name --default-character-set=utf8 < test.sql

mysqldump -hlocalhost -P3306 -uuser_name -ppassword database_name --default-character-set=utf8 --set-gtid-purged=OFF > dump.sql

mysqldump -hlocalhost -P3306 -uuser_name -ppassword database_name table_name_1 table_name_2 > table_data.sql

mysqldump -hlocalhost -P3306 -uuser_name -ppassword -t database_name table_name_1 table_name_2 > table_data.sql

show full processlist;

kill 999;

set global slow_query_log = 1;
set global slow_query_log_file = 'C:/xxx/slow_query.log';
set global long_query_time = 10;

set global general_log = 'ON';

explain select * from xxx;

select STRAIGHT_JOIN * from base_table innser join join_table_1 on ...;

select * from base_table use index (base_table_idx1) inner join join_table_1 on ...;

[mysqld]
port = 3306
default-time-zone = 'Asia/Tokyo'
default_authentication_plugin = mysql_native_password
character-set-server = utf8mb4
collation-server = utf8mb4_ja_0900_as_cs
init-connect = SET NAMES utf8mb4
 
basedir  = "C:/mysql8"
datadir  = "C:/mysql8/data"
tmpdir   = "C:/mysql8/temp"
pid-file = "C:/mysqld.pid"

C:\mysql8bin\mysqld --initialize
C:\mysql8bin\mysqld --standalone --console

C:\mysql8> C:\mysql8bin\mysql -u root -p -h localhost -P 3306
Enter password: {コピーしたパスワードを貼り付け}
mysql> alter user root@'localhost' identified by '{新しパスワード}';

mysql> use mysql
mysql> \. C:¥Users\xxxx\Downloads\timezone_posix.sql

C:\mysql8\bin\mysqld.exe ----defaults-file=C:\mysql8\my.ini --console

mysqldump -hloclahost -P3306 -uuser_name -ppassword database_name > dump.sql
mysqladmin --user root create new_database_name
mysql -hloclahost -P3306 -uuser_name -ppassword new_database_name < dump.sql

select * from table_name
  into outfile 'table_name.csv'
  fields terminated by ','
  enclosed by '"'
  lines terminated by '\r\n';

load data local infile
  'c:/table_name.csv'
  into table table_name
  fields terminated by ','
  enclosed by '"'
  lines terminated by '\r\n'
  ignore 1 lines;

load data infile
  'c:/table_name.csv'
  into table table_name
  fields terminated by ','
  enclosed by '"'
  lines terminated by '\r\n'
  ignore 1 lines;

SELECT
  0 AS statement_orderby
  , NULL AS TABLE_NAME
  , NULL AS export
  , NULL AS import_sqlserver
  , 'set NLS_DATE_FORMAT=YYYY-MM-DD HH24:MI:SS' AS import_oracle
UNION ALL
SELECT
  1 AS statement_orderby
  , t.table_name
  , concat('mysqldump -uuser_name -ppassword --compact --skip-quote-names --complete-insert --skip-extended-insert -t database_name ', t.table_name,' > ', t.table_name, '.sql') AS export
  , concat('sqlcmd -S instance_name\\SERVER_NAME -U sa -P sqlserverP@ssw0rd -d database_name -f i:65001 -i ', t.table_name,'.sql') AS import_sqlserver
  , concat('echo exit | sqlplus.exe USER_NAME/password@SERVER_NAME:1521/DATABASE_NAME @', t.table_name, '.sql') AS import_oracle
FROM
  information_schema.tables t
WHERE
  t.table_schema NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys', 'phpmyadmin')
  AND t.table_schema = 'schema_name'
  AND t.table_type = 'BASE TABLE'
  AND t.table_rows > 0
  AND t.table_name NOT LIKE '%_logs'
  AND t.table_name NOT LIKE '$$%'
  AND t.table_name NOT LIKE '\_%'
ORDER BY
  1
  , 2
;

SELECT
  t.table_schema, t.table_name, t.table_comment, t.table_type, t.engine
  , concat('drop table if exists ', t.TABLE_SCHEMA, '.', t.TABLE_NAME, ';') AS "drop table"
  , concat('truncate table ', t.TABLE_SCHEMA, '.', t.TABLE_NAME, ';') AS "truncate table"
  , concat('create table _', t.table_name, ' select * from ', t.table_name, ';') AS "create backup table"
  , concat('mysqldump.exe -hlocalhost -P3307 -u' , :username , ' -p' , :password, ' ', :table_schema, ' ', t.table_name, ' > ', t.table_name, '.sql') AS "mysqldump command cd /d %~dp0"
  , concat('mysql.exe -hlocalhost -P3307 -u' , :username , ' -p' , :password, ' ', :table_schema, ' --default-character-set=utf8 < ', t.table_name, '.sql') AS "mysql command cd /d %~dp0"
  , concat('zip ', t.table_name, '.zip ', t.table_name, '.sql') AS "zip command"
  , concat('select * from ', t.table_name, ' into outfile ', concat('\'', t.table_name, '.csv\''), ' fields terminated by \',\'', ' optionally enclosed by \'"\'', ';') AS "csv data"
FROM
  information_schema.tables t
WHERE
  t.table_schema NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys', 'phpmyadmin')
  AND t.table_type = 'BASE TABLE'
  AND t.table_schema = :table_schema
ORDER BY
  t.table_schema
  , t.table_name

SELECT
  a.key_column
  , b.key_column
  , group_concat(b.value_column ORDER BY b.sub_key_column separator ', ') AS b_values
FROM
  (
          SELECT 1 AS key_column, 'key 1' AS value_column
    UNION SELECT 2 AS key_column, 'key 2' AS value_column
  ) a
  INNER JOIN (
          SELECT 1 AS key_column, 1 AS sub_key_column, 'key 1-1' AS value_column
    UNION SELECT 2 AS key_column, 1 AS sub_key_column, 'key 2-1' AS value_column
    UNION SELECT 2 AS key_column, 2 AS sub_key_column, 'key 2-2' AS value_column
  ) b
    ON a.key_column = b.key_column
GROUP BY
  a.key_column
;

select convert('1', signed);

date_format(date_add(concat(table_name.yyyymm_column, '01'), INTERVAL -6 MONTH), '%Y%m')

SELECT TRUNCATE(1.234, 1);

SELECT
  test_table.key
  , @row_num := @row_num + 1 AS index_number  -- インクリメントした値を変数にセットしつつ行データとして返す
FROM
  test_table
  , (SELECT @row_num := 0) AS a  -- ここで変数の初期化

select
  t.group_key_value
  , t.key_value
  -- 境界として使うキーの値が前回と違っていたら1を、そうでなければインクリメントした値をセット
  , @row_num := if(@prev_group_key_value = t.group_key_value, @row_num + 1, 1) as seq
  -- 境界として使うキーの値をセット
  , @prev_group_key_value := t.group_key_value as prev_group_key_value
from
  (
          select '1' as group_key_value, '1-1' as key_value
    union select '2' as group_key_value, '2-1' as key_value
    union select '2' as group_key_value, '2-2' as key_value
    union select '3' as group_key_value, '3-1' as key_value
    union select '3' as group_key_value, '3-2' as key_value
    union select '3' as group_key_value, '3-3' as key_value
  ) t
  , (select @row_num := 0) as a -- 行番号用
  , (select @prev_group_key_value := 0) as b -- 境界として使うキーの値保持用
order by
  t.group_key_value
;

SELECT
  index_number
FROM
  test_table
WHERE
  AND rand() <= (100 / 10000) + 0.001
LIMIT
  100
;

show master logs;

purge master logs TO 'binlog.000001';

GRANT SESSION_VARIABLES_ADMIN ON *.* TO 'user_name'@'%';

# ログをファイル出力
log-output=FILE

# 一般クエリログ
general_log=0
general_log_file="general.log"

# スロークエリログ
slow_query_log=1
slow_query_log_file="slow_query.log"
long_query_time=10

# エラーログ
log_error="error.log"

set global general_log = 'ON';
set global slow_query_log = 'ON';