予備知識

ポートのデフォルトは3306
大文字小文字は区別されない。混じっていても実行できる。区別する場合はbinaryをつける(下記参照）
データベースごとに作業ユーザを設定するのが一般的
order byでnullは一番上に、外すにはwhereでis not nullを指定する
selectでつけた別名はorderby区でも使える

ログインする

useしたいデータベースがあれば引数で指定できる。下記の場合はmyapp

mysql -u root
mysql -u myapp_user -p myapp

1 2	mysql -u root mysql -u myapp_user -p myapp

mysqlコマンドの一覧を表示する

help;

				1

						help;

現在ログインしているユーザ情報を表示する

select user();

				1

						select user();

root@localhost

dbuser@localhost

データベースを表示する。

information_schema/performance_schema/mysqlはmysqlが管理しているものなので、うっかり消さないように注意すること。

show databases;

				1

						show databases;

データベースを作成する

create database db名;

				1

						create database db名;

データベースを削除する

drop database db名;

				1

						drop database db名;

選択されているDBを確認する

select database();

				1

						select database();

データベースを選択する

use データベース名;

				1

						use データベース名;

ユーザを作成・設定する

localhostはローカルホストからアクセスしてくるユーザという意味。

アクセスできるIPアドレスも設定できるらしいので、localhostとしておけばサーバ内からのアクセスだけに制限できるみたい。

identified byはパスワードを設定する用語。dbuser01/dbuser01にした。

create user ユーザ名@localhost identified by 'password'

				1

						create user ユーザ名@localhost identified by 'password'

ユーザに権限を与える grant命令

allは全ての権限を与える。onでDB名を指定、*が全てのテーブルに対してという意味。
これでrootユーザを抜けて、一般ユーザで入ってもデータベースにアクセスできる。実際は入ってみるとshow databases;で確認しても権限を与えられたDBしか見れない。select user();で確認してもちゃんと一般ユーザの名前が表示される。

grant all on db名.* to ユーザー名@localhost;

				1

						grant all on db名.* to ユーザー名@localhost;

ユーザにある権限を確認する

作ったユーザに何の権限が与えられているかを確認する。

show grants for 'ユーザ名'@'ホスト名';

例 show grants for 'dbuser'@'localhost';
例 show grants for 'root'@'localhost';

show grants for 'ユーザ名'@'ホスト名';

例 show grants for 'dbuser'@'localhost';

例 show grants for 'root'@'localhost';

ユーザを削除する

localhostはどこからアクセスしているのかを示す。

drop user ユーザ名@localhost;

				1

						drop user ユーザ名@localhost;

ユーザ一覧を表示する

select host,user,password from mysql.user;

				1

						select host,user,password from mysql.user;

コマンドを書かれた外部ファイルを作る。

テキストファイルを作るのだが、拡張子をsqlにする。

ファイル名は、create_myapp.sqlと仮定して

# myappデータベースがあれば先にdropする
drop database if exists myapp;

# データベースを作る
create database myapp;

-- grantとcreate userは一緒に書くことができる
grant all on myapp.* to myapp_user@localhost identified by 'myapp_user';

				
					
				1
2
3
4
5
6
7
8

						# myappデータベースがあれば先にdropする
drop database if exists myapp;
 
# データベースを作る
create database myapp;
 
-- grantとcreate userは一緒に書くことができる
grant all on myapp.* to myapp_user@localhost identified by 'myapp_user';

					

			

外部ファイルを実行する

書き方は２通りでsqlにログインしていない状態から、sqlファイルがあるカレントディレクトリにCDして実行

mysql -u root &lt; create_myapp.sql

				1

						mysql -u root &lt; create_myapp.sql

もしくはmysqlにrootでログインしてから

\. ./create_myapp.sql

				1

						\. ./create_myapp.sql

既存データベースをバックアップ（ダンプ）する

上記のように外部ファイルを作るように既存データベースをバックアップできる。

上記と同じようにsqlにログインしていない状態から下記コマンドを実行。

mysqldump -u ユーザ名 -p データベース名 > データベース名.sql

1	mysqldump -u ユーザ名 -p データベース名 > データベース名.sql

するとDBのテーブルからレコードなど一切合切を作る.sqlファイルが出来上がる。

出来る場所はサーバのカレントディレクトリなのでFTPか何かで吸い取ればいい。

コメントの書き方

-- 一行
# 一行
/* 
複数行
*/

				
					
				1
2
3
4
5

						-- 一行
# 一行
/* 
複数行
*/

					

			

テーブルを作成するSQl例

unsignedは符号無しの負の値無しで設定。

drop table if exists users;
create table users (
  id int unsigned primary key auto_increment,
  name varchar(20) unique,
  -- score float not null
  score float default 0.0
);

				
					
				1
2
3
4
5
6
7

						drop table if exists users;
create table users (
  id int unsigned primary key auto_increment,
  name varchar(20) unique,
  -- score float not null
  score float default 0.0
);

					

			

テーブルやビューの一覧を取得する

useでデータベースを選んでおいたうえでshow tables;これでviewも一緒に見ることができる。

show tables;

				1

						show tables;

テーブルの構造を確認する

desc テーブル名;

				1

						desc テーブル名;

テーブルを削除する

drop table テーブル名;

				1

						drop table テーブル名;

MySQLが扱えるデータ型

数値number（正も負も）

整数はint
浮動小数点はfloat
floatより精度の高いものはdouble

数値number（整数のみ）

int unsigned

文字列string

固定長ならchar
可変長ならvarcharかtext
長さがわからない可変長はtext

商品コードのようなものはchar、メールアドレスのように長さが変わるものはvarcharなどの使い分けをするとよい。

例）　char(4)、varchar(255)

				1

						例）　char(4)、varchar(255)

日付型date/time

日付ならdate
時間ならtime
日時ならdatetime ‘2017-07-22 17:22:33’のように直感的に表現できる

真偽値true/false

boolean true1/false0

enum型

複数の文字列から１つだけを格納できるデータ型。この中の１つしか格納できない。これ以外のデータを格納しようとすると、何も代入されない。ブランクになる。有効なデータ以外を無効にすることができる。また自動的に配列のように1,2,3の値が添え字として使えるので、whereでrank = 3のように指定することもできる。

rank enum('gold','silver','bronze')

				1

						rank enum('gold','silver','bronze')

set型

enum型は１つしか選べないが、set型は複数選ぶことができる。値を代入するときはカンマ区切りで渡す。

coins set('gold','silver','bronze')
insert into users (name, score, coins) values ('taguchi', 5.8,'gold,silver');

				1
2

						coins set('gold','silver','bronze')
insert into users (name, score, coins) values ('taguchi', 5.8,'gold,silver');

レコードの挿入

insert into users(id,name,score) values
  (1,'taguchi',5.8),
  (2,'fkoji',8.2),
  (3,'dotinstall',6.1),
  (4,'yamada',null);

insert into users(id,name) values
  (5,'tanaka');

				
					
				1
2
3
4
5
6
7
8

						insert into users(id,name,score) values
  (1,'taguchi',5.8),
  (2,'fkoji',8.2),
  (3,'dotinstall',6.1),
  (4,'yamada',null);
 
insert into users(id,name) values
  (5,'tanaka');

					

			

フィールドの制限

not null – nullを許さない
unique – 重複を許さない
primary key レコードを一意に特定できるフィールド
auto_increment 自動採番

カラムを追加する

alter table テーブル名が基本、その後にaddやdropやchangeなど何をするのかが来る。

末尾に追加
alter table users add column email varchar(255);

nameの後に追加
alter table users add column email varchar(255) after name;

				1
2
3
4
5

						末尾に追加
alter table users add column email varchar(255);
 
nameの後に追加
alter table users add column email varchar(255) after name;

カラムを削除する

alter table users drop column score;

				1

						alter table users drop column score;

カラムのデータ型を変える

名前をnameからuser_nameに変更し、varchar(80)に変更する
alter table users change name user_name varchar(80);

				1
2

						名前をnameからuser_nameに変更し、varchar(80)に変更する
alter table users change name user_name varchar(80);

テーブル名を変更する

usersテーブルをpersonsに変更する

alter table users rename persons;

				1

						alter table users rename persons;

大文字・小文字厳密に判定するbinary演算子

binaryをつけたら、tanakaは含まれない。

select * from users where name like binary 'T%';

				1

						select * from users where name like binary 'T%';

任意の文字ワイルドカード

nameが6文字だけを抽出
select * from users where name like '______';

nameの２文字目がaだけを抽出
select * from users where name like '_a%';

				1
2
3
4
5

						nameが6文字だけを抽出
select * from users where name like '______';
 
nameの２文字目がaだけを抽出
select * from users where name like '_a%';

件数を絞る

上から３件だけ表示する

select * from users limit 3;

				1

						select * from users limit 3;

取り出す行を指定する

最初の３行を除外して、３件取り出す

select * from users limit 3 offset 3;

				1

						select * from users limit 3 offset 3;

スコアの上位３名を表示する

select * from users order by score desc limit 3;

				1

						select * from users order by score desc limit 3;

レコードの更新

カンマ区切りでフィールド名と値を区切る

update users set name = 'sasaki', score = 2.9 where name = 'tanaka';

				1

						update users set name = 'sasaki', score = 2.9 where name = 'tanaka';

レコードの削除

条件式を入れないと全て消えてしまうので注意

delete users where 条件式;

				1

						delete users where 条件式;

四則演算

+ - * / %　全て使える

				1

						+ - * / %　全て使える

四捨五入関数round

select round(5.355); -- 5
select round(5.355,1); -- 5.4

				1
2

						select round(5.355); -- 5
select round(5.355,1); -- 5.4

切り捨て関数floor

select floor(5.833); -- 5

				1

						select floor(5.833); -- 5

切り上げ関数ceil

select ceil(5.238); --6

				1

						select ceil(5.238); --6

ランダム値関数rand

0.9404359906357117のような小数点数を返す

select rand();

				1

						select rand();

rand関数を使って抽選

select * from users order by rand() limit 1;

				1

						select * from users order by rand() limit 1;

文字列の長さを調べるlength関数

select length('Hello'); -- 5

				1

						select length('Hello'); -- 5

文字列の一部を取り出すsubstr関数

select substr('Hello',2); -- ello
select substr('Hello',2,3); -- ell

				1
2

						select substr('Hello',2); -- ello
select substr('Hello',2,3); -- ell

文字を全て大文字にするupper関数

select upper('Hello'); -- HELLO

				1

						select upper('Hello'); -- HELLO

文字を全て小文字にするlower関数

select lower('Hello'); -- hello

				1

						select lower('Hello'); -- hello

文字列を連結するconcat関数

select concat('Hello','World'); -- HelloWorld

				1

						select concat('Hello','World'); -- HelloWorld

別名をつけるAS

別名はorderby区でも使える

select length(name) as len, name from users order by len

				1

						select length(name) as len, name from users order by len

条件分岐をするif文

scoreを比較して３つ目のカラムにOKやNGを表示する。

select 
  name,
  score,
  if (score &gt; 5.0,'OK','NG') as result
from 
users;

				
					
				1
2
3
4
5
6

						select 
  name,
  score,
  if (score &gt; 5.0,'OK','NG') as result
from 
users;

					

			

条件分岐をするcase文

case文の最後はendで締める。asを使って別名をつける方がベター。下記はtypeという別名。

select name,score,
case floor(score) % 2
    when 0 then 'even'
    when 1 then 'odd'
    else null
    end as type
from users;

				
					
				1
2
3
4
5
6
7

						select name,score,
case floor(score) % 2
    when 0 then 'even'
    when 1 then 'odd'
    else null
    end as type
from users;

					

			

whenのところに直接書く書き方もある。

select name,score,
case 
    when floor(score) % 2 = 0 then 'even'
    when floor(score) % 2 = 1 then 'odd'
    else null
    end as type
from users;

				
					
				1
2
3
4
5
6
7

						select name,score,
case 
    when floor(score) % 2 = 0 then 'even'
    when floor(score) % 2 = 1 then 'odd'
    else null
    end as type
from users;

					

			

抽出結果をテーブルにする（テーブルのコピーもできる）

create tableをしつつ、asをつけて後ろにselect文。asは省略できる。

create table users_with_team as 
select
  id,
  name,
  score,
  case
    when score &gt; 8.0 then 'Team-A'
    when score &gt; 6.0 then 'Team-B'
    else 'Team-C'
  end as team
from
  users;

				
					
				1
2
3
4
5
6
7
8
9
10
11
12

						create table users_with_team as 
select
  id,
  name,
  score,
  case
    when score &gt; 8.0 then 'Team-A'
    when score &gt; 6.0 then 'Team-B'
    else 'Team-C'
  end as team
from
  users;

					

			

テーブルのコピー

create users_copy select * from users;

				1

						create users_copy select * from users;

構造だけをコピーしてテーブルを作成する

create table users_empty like users;

				1

						create table users_empty like users;

行数をカウントする

nullがあるカラムを指定するとそれは除外される。全体の行を取得する場合はcount(*)とする

select count(score) * from users_with_team;
select count(*) * from users_with_team;

				1
2

						select count(score) * from users_with_team;
select count(*) * from users_with_team;

合計、最小、最大、平均値を表示

select sum(score) from users_with_team;
select min(score) from users_with_team;
select max(score) from users_with_team;
select avg(score) from users_with_team;

				
					
				1
2
3
4

						select sum(score) from users_with_team;
select min(score) from users_with_team;
select max(score) from users_with_team;
select avg(score) from users_with_team;

					

			

重複したレコードを除いて表示 distinct

例えば、チームに分かれた選手一覧があったとして、何のチームがあるのか一覧で表示したい場合

select distinct team from users_with_team; -- チーム一覧
select distinct count(team) from users_with_team; -- チーム数

				1
2

						select distinct team from users_with_team; -- チーム一覧
select distinct count(team) from users_with_team; -- チーム数

グループ集計 group by

チームごとの合計スコア

select sum(score),team from users_with_team group by team;

				1

						select sum(score),team from users_with_team group by team;

グループ集計からの抽出 having

whereと一緒に使った場合は、where条件で集計した後にhavingで抽出することになる。

select sum(score),team from users_with_team group by team having sum(score) &gt; 10;

				1

						select sum(score),team from users_with_team group by team having sum(score) &gt; 10;

サブクエリ

集計用の別テーブルを作る手間を省ける。

select
  sum(t.score),
  t.team
from
  (select
    id,
    name,
    score,
    case
      when score &gt; 8.0 then 'Team-A'
      when score &gt; 6.0 then 'Team-B'
      else 'Team-C'
    end as team
  from users) as t 
group by t.team;

				
					
				1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

						select
  sum(t.score),
  t.team
from
  (select
    id,
    name,
    score,
    case
      when score &gt; 8.0 then 'Team-A'
      when score &gt; 6.0 then 'Team-B'
      else 'Team-C'
    end as team
  from users) as t 
group by t.team;

					

			

抽出条件に名前をつけてテーブルのように扱うview

*AS400の場合はビュー名にライブラリを入れること。

create view ビュー名 as セレクト文;
create view top3 as select * from users order by score desc limit 3;
select * from top3;

				1
2
3

						create view ビュー名 as セレクト文;
create view top3 as select * from users order by score desc limit 3;
select * from top3;

どうやってViewが作られたかを確認する

show create view top3;

				1

						show create view top3;

もしテーブルやビューがあったら削除する

drop table if exists users;
drop view if exists top3;

				1
2

						drop table if exists users;
drop view if exists top3;

一覧の処理を必ずまとめて行いたいトランザクションtransaction

start transactionからcommitという処理は必ずまとめて行われる保証がされる。ちなみに下記２行目のupdateをupdataにして、やってみると、１行目は成功して、２行目は失敗だけど、両方の数字は変わらなかった。transactionをつけなければ、確かに１行目だけ実行されてしまった。なるほど。

start transaction;

update users set score = score - 1.2 where name = 'fkoji';
update users set score = score + 1.2 where name = 'taguchi';

commit;

				1
2
3
4
5
6

						start transaction;
 
update users set score = score - 1.2 where name = 'fkoji';
update users set score = score + 1.2 where name = 'taguchi';
 
commit;

索引を設定する（データ抽出が早くなる効果）

ちなみにPrimaryKeyには必ず索引が作られる。索引はデータの追加や更新処理を行うたびに作り直されるので、逆にそちらの処理が遅くなるデメリットもあるので、考えて索引を付けるようにすること。アプリケーションの速度が遅い場合は索引を付けたり外したりして調整してみるのも良い。

alter table テーブル名 add index 索引名 (索引を設定したいカラム名);
alter table users add index index_score (score);

				1
2

						alter table テーブル名 add index 索引名 (索引を設定したいカラム名);
alter table users add index index_score (score);

テーブルに作られた索引を確認する

show index from テーブル名;
show index from users;

				1
2

						show index from テーブル名;
show index from users;

索引が使われているか確認する

索引を設置したscoreをwhere文で使うと、explainのpossible_keysで使っているのが解る。逆にnameをwhere文で使うと、explainで何も表示されない。

explain select文;
explain select * from users where score &gt; 5.0;

				1
2

						explain select文;
explain select * from users where score &gt; 5.0;

索引の削除

alter table テーブル名 drop index 索引名;
alter table users drop index index_score;

				1
2

						alter table テーブル名 drop index 索引名;
alter table users drop index index_score;

内部結合のinner join

２つのテーブルに共通のデータだけを取得する方法。カラムを指定する場合は、テーブル名を前につけるが、同じものが無い場合はつけなくてもよい。

select posts.id, posts.title, posts.body, comments.body from posts inner join comments on posts.id = comments.post_id;

				1

						select posts.id, posts.title, posts.body, comments.body from posts inner join comments on posts.id = comments.post_id;

外部結合のouter join

２つのテーブルで一致しないデータも含めてデータを取得する方法。外部結合はどちらのテーブルを軸にするかで構文が異なる。尚、outerは省略できる。

select * from posts left outer join comments on posts.id = comments.post_id;
select * from posts right outer join comments on posts.id = comments.post_id;

				1
2

						select * from posts left outer join comments on posts.id = comments.post_id;
select * from posts right outer join comments on posts.id = comments.post_id;

外部キー制約

ウチでいう、JNF010が無いのに、JNF020が作られるのはおかしい。それをそもそもそういった変なデータが登録できない制約をかける。設定するときは関連づけるカラムの型が一致していないとダメ。これを設定してからいわゆるJNF020側に一方的に追加した場合、エラーメッセージが表示され、登録されない。データの整合性をとるには便利な仕組み。そして外部キーが設定されていれば、削除することも難しくなる。

alter table 外部テーブル名 add constraint 制約名 foreign key (外部キー) references 内部テーブル（内部キー）;
alter table comments add constraint fk_comments foreign key (post_id) references posts(id);

				1
2

						alter table 外部テーブル名 add constraint 制約名 foreign key (外部キー) references 内部テーブル（内部キー）;
alter table comments add constraint fk_comments foreign key (post_id) references posts(id);

外部キー制約を削除

alter table テーブル名 drop foreign key 制約名
alter table comments drop foreign key fk_comments;

				1
2

						alter table テーブル名 drop foreign key 制約名
alter table comments drop foreign key fk_comments;

直前に挿入されたレコードのIDを調べる last_insert_id()

下記の例ではcommentsのpost_idに使っている。これはpostsのincreamentのIDと同じ値なので、直前の値を取得すればＯＫ。comments内の１つ前のIDをincrement的に取得するのではないので注意。ちなみにこの下に同じinsert文を付けるのはダメ。すると、commentsに挿入したIDを取得してしまうから。あくまで直前のIDということ。

insert into posts(title,body) value('new title','new body');
insert into comments (post_id, body) values(last_insert_id(),'first comment for new post');

				1
2

						insert into posts(title,body) value('new title','new body');
insert into comments (post_id, body) values(last_insert_id(),'first comment for new post');

トリガーを使う

テーブルに処理を施したのをイベントとして処理を施す。使い方次第で、insert/update/deleteの前や後に処理を追加することができる。
afterではなくbeforeにすることで処理前のデータを取得できる。

create trigger トリガー名 いつ どんな時 on トリガーを設置するテーブル名 for each row 処理名 何のテーブルに 何の値を
create trigger post_insert_trigger after insert on posts for each row insert into logs values('post added!');

				1
2

						create trigger トリガー名 いつ どんな時 on トリガーを設置するテーブル名 for each row 処理名 何のテーブルに 何の値を
create trigger post_insert_trigger after insert on posts for each row insert into logs values('post added!');

トリガーを削除する

drop trigger if exists post_insert_trigger;

				1

						drop trigger if exists post_insert_trigger;

トリガーの一覧を表示する

\Gをつけると見やすく表示してくれる。小文字のGはエラーになる。

show triggers;
show triggers \G;

				1
2

						show triggers;
show triggers \G;

区切り文字;を変更する

終わったらまたdelimiterで元に戻しておくこと。

delimiter 別の文字
delimiter //
処理;
処理;
処理//
delimiter ;

				
					
				1
2
3
4
5
6

						delimiter 別の文字
delimiter //
処理;
処理;
処理//
delimiter ;

					

			

１つのトリガーで複数の処理を行う

delimiter //
create trigger トリガー名 いつ どんな時 on トリガーを設置するテーブル名 for each row 
 begin
  処理名 何のテーブルに 何の値を;
  処理名 何のテーブルに 何の値を;
 end
//
delimiter ;

				
					
				1
2
3
4
5
6
7
8

						delimiter //
create trigger トリガー名 いつ どんな時 on トリガーを設置するテーブル名 for each row 
 begin
  処理名 何のテーブルに 何の値を;
  処理名 何のテーブルに 何の値を;
 end
//
delimiter ;

					

			

トリガーの中で処理前や処理後のカラムの値を取得する

postsテーブルをupdateした後に処理が走る。oldとnewで値を取得できる。

drop trigger if exists logs;
delimiter //
create trigger posts_update_trigger after update on posts for each row
 begin
     insert into logs(msg) values('post updated!');
     insert into logs(msg) values(concat(old.title, ' -&gt; ', new.title));
 end
//
delimiter ;
insert into posts (title, body) values ('title 1', 'body 1');
insert into posts (title, body) values ('title 2', 'body 2');
insert into posts (title, body) values ('title 3', 'body 3');
update posts set title = 'title 2 updated' where id = 2;

				
					
				1
2
3
4
5
6
7
8
9
10
11
12
13

						drop trigger if exists logs;
delimiter //
create trigger posts_update_trigger after update on posts for each row
 begin
     insert into logs(msg) values('post updated!');
     insert into logs(msg) values(concat(old.title, ' -&gt; ', new.title));
 end
//
delimiter ;
insert into posts (title, body) values ('title 1', 'body 1');
insert into posts (title, body) values ('title 2', 'body 2');
insert into posts (title, body) values ('title 3', 'body 3');
update posts set title = 'title 2 updated' where id = 2;

					

			

NOTを使う

いつもwhere文の否定は<>で判定しているが、NOTを使うのも悪くないかも。

最大で何件データを取得するかLIMIT

使い方は簡単。5件ならばLIMIT 5と入力すればいい。ORDER BYとLIMITを併用する場合は、LIMITの方を後にする。

重複データを省くDISTINCT

使い方はSELECTの後に指定するだけ。いままで括ったことなかったけど、カッコでくくるらしい。

SELECT DISTINCT(name) FROM words;

				1

						SELECT DISTINCT(name) FROM words;

今まで項目をまとめるという意味でGROUP BYでやっていたけど、よく考えたらDISTINCTでいいんだよな。グルーピングして金額を集計するとかそういのじゃなくて、単なる項目をリスト化するだけならこちらの方が使いやすそう。GROUP BYでまとめたけど、特に集計したい項目が無い場合はこちら。

NULLのデータはカウントされない

たとえばCOUNT関数しようと思っても、NULLがあるカラムのNULLデータはカウントされない。NULLも含めてカウントしたいのならCOUNT(*)にすること。そうすればレコード数でカウントしてくれる。

SQLの実行順序

Where->Group by->関数->Having

HAVINGの注意点

Whereで絞り込み、Groupbyで集計された後のデータをさらに絞り込む場合に使う。指定するカラムは必ずグループ化された後のカラムを使うこと。

データベースの照合順序を表示する

select * from information_schema.schemata;

( utf8mb4_general_ci / utf8_general_ci )

MySQL 自分の備忘録