トップページに戻る
オートコンプリート
MySQLメモ
目次
001 WindowsXPと7でのPHP環境構築
WindowsXP(SP2)で動くMySQLの最新バージョンは、5.5.62
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
Windows7 64ビットでMySQL8.0.15のインストール手順
その1
.net framework 4.5.2をインストールしたが、これは、おそらく不要
その2
WindowsUpdateでWindows7をSP1にする
その3
Microsoft Visual C++ 2015
Redistributable(X64) - 14.0.23026
vc_redist.x64.exe
をインストールする
その4
mysql-installer-community-8.0.15.0.msi
をインストールする
002 mysqldプロセス開始、mysqlコマンドプロンプトの起動
mysqldプロセス開始
D:\MySQL5.5\mysql-5.5.62-win32\bin\mysqld
mysqlコマンドプロンプトの起動
D:\MySQL5.5\mysql-5.5.62-win32\bin\mysql -D test
mysqldプロセス終了
D:\MySQL5.5\mysql-5.5.62-win32\bin\mysqladmin -u root shutdown
003 データベース名とバージョンを調べる
select database(),version();
+------------+-----------+
| database() | version() |
+------------+-----------+
| test | 5.5.62 |
+------------+-----------+
004 Oracleのdump関数のような関数
MySQLには、ないっぽい
CTASしてdesc テーブル名で代用するしかなさそう
005 主キーのUpdate
create table TestTable(ColA int primary key);
insert into TestTable values(1),(2),(3);
update TestTable set ColA = ColA + 1;
update文中の一意制約違反であっても
ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'
が発生する。
update TestTable set ColA = ColA + 1 order by ColA desc;
で、降順にソートすれば防げる(競プロのDPのループの向きの感覚)
006 Date型とDateTime型のデフォルト書式
MySQLのデフォルトのDate/DateTime書式は何ですか？
MySQLのデフォルトのDateフィールド書式はYYYY-MM-DDです。
DateTime対応は日付と時間の組み合わせで、YYYY-MM-DD HH:MM:SS書式でデータを保存します。
007 数値と文字列の相互変換
create table CastCheck as
select cast('123' as Signed) as CharToNum,
cast(123 as char(3)) as NumToChar;
desc CastCheck;
+-----------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------+------+-----+---------+-------+
| CharToNum | int(3) | NO | | 0 | |
| NumToChar | varchar(3) | NO | | | |
+-----------+------------+------+-----+---------+-------+
008 数値の0パディング
select LPad('123',6,'0');
+-------------------+
| LPad('123',6,'0') |
+-------------------+
| 000123 |
+-------------------+
009 RollUp集計
select ID,group_concat(Val) as concatVal
from (select 1 as ID,'A' as Val union
select 1 ,'B' union
select 2 ,'C' union
select 2 ,'D' union
select 3 ,'E') tmp
group by ID With RollUp;
+------+-----------+
| ID | concatVal |
+------+-----------+
| 1 | A,B |
| 2 | D,C |
| 3 | E |
| NULL | A,B,D,C,E |
+------+-----------+
group by ColA,ColB,ColC With RollUp
は、Oracleの下記と同じっぽい
group by RollUp(ColA,ColB,ColC)
010 正規表現
select '123ABC' RegExp '[A-Z]+' as tes1,
'123ABC' RegExp '^[A-Z]+$' as tes2;
+------+------+
| tes1 | tes2 |
+------+------+
| 1 | 0 |
+------+------+
マッチパターンが部分一致すれば1を返し、マッチしなければ0を返す。
8.0でも、肯定先読み、否定先読み、肯定戻り読み、否定戻り読み
はサポートされない。
MySQL8.0で、RegExp_ReplaceやRegExp_SubStrやRegExp_InStrが追加されました。
12.5.2 Regular Expressions
011 Least関数とGreatest関数
引数にnullがあるとnullを返す。
select Least(10,20,30) as tes1,
Greatest(10,20,30) as tes2,
Least(1,2,null) as tes3,
Greatest(1,2,null) as tes4;
+------+------+------+------+
| tes1 | tes2 | tes3 | tes4 |
+------+------+------+------+
| 10 | 30 | NULL | NULL |
+------+------+------+------+
012 一か月後を求める
select date '2019-01-31' as tes1,
date '2019-01-31' + InterVal 1 month as tes2;
+------------+------------+
| tes1 | tes2 |
+------------+------------+
| 2019-01-31 | 2019-02-28 |
+------------+------------+
013 末日を求める
select Last_day(date '2019-02-01') as tes1,
Date_Format(date '2019-02-01','%d') as tes2;
+------------+------+
| tes1 | tes2 |
+------------+------+
| 2019-02-28 | 01 |
+------------+------+
014 Date_Format関数
select Date_Format(Val,'%Y%m')
from (select date '2018-11-01' as Val union
select date '2018-12-15' as Val union
select date '2018-12-30' as Val union
select date '2019-01-01' as Val) as tmp;
+-------------------------+
| Date_Format(Val,'%Y%m') |
+-------------------------+
| 201811 |
| 201812 |
| 201812 |
| 201901 |
+-------------------------+
015 Find_IN_Set関数
select Find_IN_Set('a','a,b,c') as tes1,
Find_IN_Set('b','a,b,c') as tes2,
Find_IN_Set('c','a,b,c') as tes3,
Find_IN_Set('d','a,b,c') as tes4;
+------+------+------+------+
| tes1 | tes2 | tes3 | tes4 |
+------+------+------+------+
| 1 | 2 | 3 | 0 |
+------+------+------+------+
016 IF関数
select IF(1 = 1,'真','偽') as tes1,
IF(1 = 2,'真','偽') as tes2;
+------+------+
| tes1 | tes2 |
+------+------+
| 真 | 偽 |
+------+------+
017 IfNull関数
--引数が2つのCoalesce関数
select IfNull(null,'nullです') as tes1,
IfNull( 1,'nullです') as tes2;
+----------+------+
| tes1 | tes2 |
+----------+------+
| nullです | 1 |
+----------+------+
018 Concat_ws関数
select Concat_ws('---','1','2','3') as concated;
+-----------+
| concated |
+-----------+
| 1---2---3 |
+-----------+
019 Field関数
select Field('a','a','b','c') as tes1,
Field('b','a','b','c') as tes2,
Field('c','a','b','c') as tes3,
Field('d','a','b','c') as tes4;
+------+------+------+------+
| tes1 | tes2 | tes3 | tes4 |
+------+------+------+------+
| 1 | 2 | 3 | 0 |
+------+------+------+------+
020 Elt関数
select Elt(1, 'a', 'b', 'c', 'd') as tes1,
Elt(2, 'a', 'b', 'c', 'd') as tes2,
Elt(3, 'a', 'b', 'c', 'd') as tes3,
Elt(4, 'a', 'b', 'c', 'd') as tes4,
Elt(5, 'a', 'b', 'c', 'd') as tes5;
+------+------+------+------+------+
| tes1 | tes2 | tes3 | tes4 | tes5 |
+------+------+------+------+------+
| a | b | c | d | NULL |
+------+------+------+------+------+
021 他テーブルを参照してのUpdate
5.6のマニュアル Update文
create table MasterTable(
ID int,
Val varchar(10),
primary key(ID)
)DEFAULT CHARSET=utf8;
insert into MasterTable values(1,'牛若丸'),
(2,'木下藤吉郎'),
(3,'徳川家康'),
(4,'明智光秀');
create table TranTable(
ID int,
Val varchar(10),
primary key(ID)
)DEFAULT CHARSET=utf8;
insert into TranTable values(1,'源義経'),
(2,'豊臣秀吉');
update MasterTable,TranTable
set MasterTable.Val = TranTable.val
where MasterTable.ID = TranTable.ID;
-- JoinでonやUsingを使ってもOK
update MasterTable a
Join TranTable b Using(ID)
set a.Val = b.val;
select * from MasterTable;
+----+----------+
| ID | Val |
+----+----------+
| 1 | 源義経 |
| 2 | 豊臣秀吉 |
| 3 | 徳川家康 |
| 4 | 明智光秀 |
+----+----------+
本番環境のデータ編集で誤更新が怖い場合は、
更新テーブルを明示する、下記の相関サブクエリを使ったUpdateのほうが良い
update MasterTable a
set a.Val = (select b.Val
from TranTable b where b.ID = a.ID)
where exists(select 1 from TranTable b where b.ID = a.ID);
022 Insert文でMerge文もどき
5.6のマニュアル Insert文
create table MergeTest(
ID int,
Val varchar(10),
primary key(ID)
)DEFAULT CHARSET=utf8;
insert into MergeTest values(1,'牛若丸'),
(2,'木下藤吉郎');
insert into MergeTest(ID,Val)
values(1,'源義経'),
(2,'豊臣秀吉'),
(3,'徳川家康'),
(4,'明智光秀')
on duplicate key update MergeTest.Val= Values(Val);
select * from MergeTest;
+----+----------+
| ID | Val |
+----+----------+
| 1 | 源義経 |
| 2 | 豊臣秀吉 |
| 3 | 徳川家康 |
| 4 | 明智光秀 |
+----+----------+
023 Insert文で一意制約違反をIgnore
5.6のマニュアル Insert文 より引用
重複キー違反の原因になる行を無視するには、Ignore を指定します。
create table DupkeyTest(ID int primary key);
insert Ignore into DupkeyTest values(1);
insert Ignore into DupkeyTest values(1);
insert Ignore into DupkeyTest values(2);
select Group_Concat(cast(ID as Char(10))) as Concat_Str from DupkeyTest;
+------------+
| Concat_Str |
+------------+
| 1,2 |
+------------+
024 Replace文でDeleteしてInsert
Replace構文 より引用
Replaceは、Insertとまったく同じように機能します。
ただし、テーブル内の古い行に、
PrimaryKeyまたは Uniqueインデックスに関して新しい行と同じ値が含まれている場合、
その古い行は新しい行が挿入される前に削除されます。
MySQLは、REPLACE (および LOAD DATA ... REPLACE) に次のアルゴリズムを使用します。
1. テーブルへの新しい行の挿入を試みます。
2. 主キーまたは一意のインデックスに関する重複キーエラーが発生して挿入が失敗した場合、次のことを行います。
a. 重複キー値を含む競合している行をテーブルから削除します。
b. テーブルへの新しい行の挿入を再試行します。
create table RepT(
ID int,
Val varchar(10),
primary key(ID));
insert into RepT values(1,'AAAA'),
(2,'BBBB');
select * from RepT;
+----+------+
| ID | Val |
+----+------+
| 1 | AAAA |
| 2 | BBBB |
+----+------+
replace into RepT values(1,'CCCC'),
(3,'DDDD');
select * from RepT;
+----+------+
| ID | Val |
+----+------+
| 1 | CCCC |
| 2 | BBBB |
| 3 | DDDD |
+----+------+
025 Update文とDelete文でのLimit句指定
create table LimitTest(Val int);
insert into LimitTest values(1),(2),(3),(4),(5);
5.6のマニュアル Update文
update LimitTest set Val = 8 Order by Val desc Limit 2;
select Group_Concat(cast(Val as Char(10))) as Concat_Str from LimitTest;
+------------+
| Concat_Str |
+------------+
| 1,2,3,8,8 |
+------------+
5.6のマニュアル Delete文
delete from LimitTest Order by Val Limit 2;
select Group_Concat(cast(Val as Char(10))) as Concat_Str from LimitTest;
+------------+
| Concat_Str |
+------------+
| 3,8,8 |
+------------+
026 MySQLのBoolean型
Boolean型は、C++の論理型のように使える。
select min(Col),max(Col)
from (select 0=1 as Col union
select 1=1) tmp;
+----------+----------+
| min(Col) | max(Col) |
+----------+----------+
| 0 | 1 |
+----------+----------+
027 count関数の複数式distinct
count関数の複数式distinctは、1つでもnullだとカウントしない。
select count(distinct ColA,ColB) as DisCnt
from (select 1 as ColA,null as ColB union
select null , 1 union
select null ,null union
select 1 , 1) tmp;
+--------+
| DisCnt |
+--------+
| 1 |
+--------+
028 Update文でDateTime型の重複を解消
create table UniqTimeTest(
col_ID int,
col_Time TimeStamp);
insert into UniqTimeTest values(1,now()),
(2,now()),
(3,now());
select * from UniqTimeTest;
+--------+---------------------+
| col_ID | col_Time |
+--------+---------------------+
| 1 | 2019-02-08 00:27:21 |
| 2 | 2019-02-08 00:27:21 |
| 3 | 2019-02-08 00:27:21 |
+--------+---------------------+
update UniqTimeTest
set col_Time = col_Time
+ InterVal (select count(*) from (select * from UniqTimeTest) tmp
where tmp.col_ID < UniqTimeTest.col_ID ) second;
select * from UniqTimeTest;
+--------+---------------------+
| col_ID | col_Time |
+--------+---------------------+
| 1 | 2019-02-08 00:27:21 |
| 2 | 2019-02-08 00:27:22 |
| 3 | 2019-02-08 00:27:23 |
+--------+---------------------+
029 Auto_Incrementの調査
Auto_Increment指定すると、
テーブルデータの最大値の次の値と、
テーブルのAuto_Incrementの値で、
大きいほうを採番する。
Auto_Increment型の列をValues句で指定して、任意の値をInsertすることもできる。
create table inc_test(ColA int auto_increment primary key,ColB text);
insert into inc_test(ColB) values('AAA');
insert into inc_test(ColA,ColB) values(900,'BBB');
insert into inc_test(ColB) values('CCC');
insert into inc_test(ColB) values('DDD');
insert into inc_test(ColA,ColB) values(100,'EEE');
alter table inc_test auto_increment = 12345;
insert into inc_test(ColB) values('FFF');
select * from inc_test;
+-------+------+
| ColA | ColB |
+-------+------+
| 1 | AAA |
| 100 | EEE |
| 900 | BBB |
| 901 | CCC |
| 902 | DDD |
| 12345 | FFF |
+-------+------+
030 Left関数とRight関数
select Left ('ABCDE', 3) as tes1,
Right('ABCDE', 3) as tes2;
+------+------+
| tes1 | tes2 |
+------+------+
| ABC | CDE |
+------+------+
031 Concat関数や数値型の0割りでのnull
concat関数は、引数にnullがあるとnullを返す。
concat_ws関数は、接続文字列にnullがあったら無視する。
MySQLで1/0のようなゼロ割りはnullとなる。
IfNull関数は、使用されているコンテキストに応じて、
数値または文字列値を返す。
select IfNull(1/0,'yes'); -- 'yes'
032 Usingを使ったJoin
select * from
(select 1 ID union select 2) a Left Join
(select 1 ID union select 3) b Using(ID);
+----+
| ID |
+----+
| 1 |
| 2 |
+----+
033 Truncate関数での切捨て
select truncate(123.456,-2) as tes1,
truncate(123.456,-1) as tes2,
truncate(123.456, 0) as tes3,
truncate(123.456, 1) as tes4,
truncate(123.456, 2) as tes5,
truncate(123.456, 3) as tes6;
+------+------+------+-------+--------+---------+
| tes1 | tes2 | tes3 | tes4 | tes5 | tes6 |
+------+------+------+-------+--------+---------+
| 100 | 120 | 123 | 123.4 | 123.45 | 123.456 |
+------+------+------+-------+--------+---------+
034 コマンドラインの結果をテキストに出力
tee '出力先のフルパス'
select 12345;
notee
035 テーブル定義の変更のサンプル
drop table DDLTest;
create table DDLTest(OldColumnName varchar(111) not null);
desc DDLTest;
-- 列名の変更
ALTER TABLE DDLTest CHANGE COLUMN OldColumnName NewColumnName varchar(222) not null;
desc DDLTest;
-- 列の定義の変更
ALTER TABLE DDLTest MODIFY COLUMN NewColumnName varchar(333) not null;
desc DDLTest;
-- 列の追加
ALTER TABLE DDLTest ADD COLUMN TuikaColumnName int not null AFTER NewColumnName;
desc DDLTest;
-- 列の削除
ALTER TABLE DDLTest DROP COLUMN TuikaColumnName;
desc DDLTest;
日々の覚書: MySQL 8.0ではカラムのリネームに ALTER TABLE RENAME COLUMN 構文が使える
036 テーブル定義のコンペア用のSelect文
select a.TABLE_NAME,a.COLUMN_NAME, a.DATA_TYPE, a.IS_NULLABLE, a.COLUMN_DEFAULT,
b.TABLE_NAME,b.COLUMN_NAME, b.DATA_TYPE, b.IS_NULLABLE, b.COLUMN_DEFAULT
from INFORMATION_SCHEMA.COLUMNS a
Left Join INFORMATION_SCHEMA.COLUMNS b
on b.table_schema = 'db_name'
and b.TABLE_NAME Like 'abcdef%' -- このLike述語の条件を交換して2回実行すればOK
and b.TABLE_NAME = a.TABLE_NAME
and b.COLUMN_NAME = a.COLUMN_NAME
where a.TABLE_NAME Like 'ghijk%' -- このLike述語の条件を交換して2回実行すればOK
and a.table_schema = 'db_name'
and (b.TABLE_NAME is Null
or a.DATA_TYPE <=> b.DATA_TYPE
or a.IS_NULLABLE <=> b.IS_NULLABLE
or a.COLUMN_DEFAULT <=> b.COLUMN_DEFAULT)
order by a.COLUMN_NAME;
037 SQLモードの確認
select @@GLOBAL.sql_mode;
でSQLモードを確認できる。
NO_BACKSLASH_ESCAPES が有効だと \' は、文字としての \と' となる。
NO_BACKSLASH_ESCAPES が無効だと \' は、文字としての ' となる。
038 mysql.exeの-Dというコマンドラインオプション
mysql.exe のコマンドラインオプションで
-D test
を指定するとtestデータベースに接続できる。
4.5.1.1 mysql のオプション
039 Update文でソートキーをユニークに座標圧縮
create table UniqZaatu(ID int primary key,SortKey int);
insert into UniqZaatu values(10, 1), -- 1が座圧後の値
(30, 1), -- 2が座圧後の値
(90, 1), -- 3が座圧後の値
(50, 2), -- 4が座圧後の値
(60, 3), -- 5が座圧後の値
( 1, 4), -- 6が座圧後の値
( 2, 7), -- 7が座圧後の値
( 3, 8), -- 8が座圧後の値
( 4,100), -- 9が座圧後の値
( 5,110); -- 10が座圧後の値
update UniqZaatu a
set SortKey = (select count(*) + 1
from (select * from UniqZaatu) b
where b.SortKey < a.SortKey
or (b.SortKey = a.SortKey and b.ID < a.ID));
select * from UniqZaatu order by SortKey;
+----+---------+
| ID | SortKey |
+----+---------+
| 10 | 1 |
| 30 | 2 |
| 90 | 3 |
| 50 | 4 |
| 60 | 5 |
| 1 | 6 |
| 2 | 7 |
| 3 | 8 |
| 4 | 9 |
| 5 | 10 |
+----+---------+
040 GroupBy句での列番号指定
select ID,count(*) as cnt
from (select 'A' as ID union all
select 'B' union all
select 'B') tmp
group by 1;
+----+-----+
| ID | cnt |
+----+-----+
| A | 1 |
| B | 2 |
+----+-----+
13.2.9 SELECT 構文
カラム名、カラムのエイリアス、またはカラム位置を使用して、
出力のために選択されたカラムを ORDER BY および GROUP BY 句で参照できます。
カラム位置は整数であり、1 から始まります。
041 group_concat関数の長さ上限
group_concat関数の結果の長さには上限があって、
そのデフォルト値は1024バイト
SET SESSION group_concat_max_len = 9876;
で変更することができる。
042 現在日時の表示
select Date_Format(Now(),'%Y/%m/%d %H:%i:%S') as tes;
+---------------------+
| tes |
+---------------------+
| 2020/01/01 23:59:59 |
+---------------------+
043 OrderByでのNullの判定
MySQLのOrderByはNULLを最小とみなす。
select Val
from (select 1 as Val union
select 2 union
select 3 union
select null) a
order by Val;
+------+
| Val |
+------+
| NULL |
| 1 |
| 2 |
| 3 |
+------+
044 文字列の指定文字列以降を抽出
5.7までは、InStr関数とSubStr関数を組み合わせる必要あり
select SubStr(WkStr,InStr(WkStr,'5')) as tes
from (select '123456789' as WkStr) tmp;
+-------+
| tes |
+-------+
| 56789 |
+-------+
MySQL8.0からは、正規表現が使える
select RegExp_SubStr(WkStr,'5.*$') as tes
from (select '123456789' as WkStr) tmp;
045 InStr関数とFind_IN_Set関数とChar_Length関数
InStr関数とFind_IN_Set関数は、
ヒットしたら、その位置(1以上)を返す
ヒットしなかったら、0を返す。
0か1以上を返すので、そのまま、論理演算に使うこともできる。
Length関数とChar_Length関数も、文字列の空判定として、そのまま、論理演算に使える。
select Val,Char_Length(Val) as Len
from (select null as Val union all
select '' union all
select 'ABC') t;
+------+------+
| Val | Len |
+------+------+
| NULL | NULL |
| | 0 |
| ABC | 3 |
+------+------+
046 オートインクリメントと主キーの設定
create Temporary table Test1(
ColA int Auto_Increment Primary Key,
ColB int);
desc Test1;
create Temporary table Test2(
ColA int Auto_Increment,
ColB int,
Primary Key(ColA));
desc Test2;
047 全部のテーブルのdrop文を作成
select concat('drop table ' , TABLE_NAME , ';') as ddl
from Information_Schema.Tables
where TABLE_SCHEMA = 'test';
048 テーブルの列を出力
select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME like 'テーブル名'
049 MySQL徹底入門(4版)の読書メモ
232ページ
MySQLのデフォルトのトランザクション分離レベルは、
RepeatableReadで、
同じトランザクションでは、一貫性のあるデータ読取ができる。
250ページ
MySQLに接続し、statusコマンドを使うと
基本的な情報を表示できる。
269ページ
helpコマンドは、関数の指定もできる。
help find_in_set
help instr
help left
513ページ
MySQL 5.7.6から
Oracleの仮想列のような機能である
generated columnという機能が使える。