予備知識
- ポートのデフォルトは3306
- 大文字小文字は区別されない。混じっていても実行できる。区別する場合はbinaryをつける(下記参照)
- データベースごとに作業ユーザを設定するのが一般的
- order byでnullは一番上に、外すにはwhereでis not nullを指定する
- selectでつけた別名はorderby区でも使える
ログインする
useしたいデータベースがあれば引数で指定できる。下記の場合はmyapp
1 2 |
mysql -u root mysql -u myapp_user -p myapp |
mysqlコマンドの一覧を表示する
1 |
help; |
現在ログインしているユーザ情報を表示する
1 |
select user(); |
root@localhost
dbuser@localhost
データベースを表示する。
information_schema/performance_schema/mysqlはmysqlが管理しているものなので、うっかり消さないように注意すること。
1 |
show databases; |
データベースを作成する
1 |
create database db名; |
データベースを削除する
1 |
drop database db名; |
選択されているDBを確認する
1 |
select database(); |
データベースを選択する
1 |
use データベース名; |
ユーザを作成・設定する
localhostはローカルホストからアクセスしてくるユーザという意味。
アクセスできるIPアドレスも設定できるらしいので、localhostとしておけばサーバ内からのアクセスだけに制限できるみたい。
identified byはパスワードを設定する用語。dbuser01/dbuser01にした。
1 |
create user ユーザ名@localhost identified by 'password' |
ユーザに権限を与える grant命令
allは全ての権限を与える。onでDB名を指定、*が全てのテーブルに対してという意味。
これでrootユーザを抜けて、一般ユーザで入ってもデータベースにアクセスできる。実際は入ってみるとshow databases;で確認しても権限を与えられたDBしか見れない。select user();で確認してもちゃんと一般ユーザの名前が表示される。
1 |
grant all on db名.* to ユーザー名@localhost; |
ユーザにある権限を確認する
作ったユーザに何の権限が与えられているかを確認する。
1 2 3 4 |
show grants for 'ユーザ名'@'ホスト名'; 例 show grants for 'dbuser'@'localhost'; 例 show grants for 'root'@'localhost'; |
ユーザを削除する
localhostはどこからアクセスしているのかを示す。
1 |
drop user ユーザ名@localhost; |
ユーザ一覧を表示する
1 |
select host,user,password from mysql.user; |
コマンドを書かれた外部ファイルを作る。
テキストファイルを作るのだが、拡張子をsqlにする。
ファイル名は、create_myapp.sqlと仮定して
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'; |
外部ファイルを実行する
書き方は2通りでsqlにログインしていない状態から、sqlファイルがあるカレントディレクトリにCDして実行
1 |
mysql -u root < create_myapp.sql |
もしくはmysqlにrootでログインしてから
1 |
\. ./create_myapp.sql |
既存データベースをバックアップ(ダンプ)する
上記のように外部ファイルを作るように既存データベースをバックアップできる。
上記と同じようにsqlにログインしていない状態から下記コマンドを実行。
1 |
mysqldump -u ユーザ名 -p データベース名 > データベース名.sql |
するとDBのテーブルからレコードなど一切合切を作る.sqlファイルが出来上がる。
出来る場所はサーバのカレントディレクトリなのでFTPか何かで吸い取ればいい。
コメントの書き方
1 2 3 4 5 |
-- 一行 # 一行 /* 複数行 */ |
テーブルを作成するSQl例
unsignedは符号無しの負の値無しで設定。
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も一緒に見ることができる。
1 |
show tables; |
テーブルの構造を確認する
1 |
desc テーブル名; |
テーブルを削除する
1 |
drop table テーブル名; |
MySQLが扱えるデータ型
数値number(正も負も)
- 整数はint
- 浮動小数点はfloat
- floatより精度の高いものはdouble
数値number(整数のみ)
- int unsigned
文字列string
- 固定長ならchar
- 可変長ならvarcharかtext
- 長さがわからない可変長はtext
商品コードのようなものはchar、メールアドレスのように長さが変わるものはvarcharなどの使い分けをするとよい。
1 |
例) char(4)、varchar(255) |
日付型date/time
- 日付ならdate
- 時間ならtime
- 日時ならdatetime ‘2017-07-22 17:22:33’のように直感的に表現できる
真偽値true/false
- boolean true1/false0
enum型
複数の文字列から1つだけを格納できるデータ型。この中の1つしか格納できない。これ以外のデータを格納しようとすると、何も代入されない。ブランクになる。有効なデータ以外を無効にすることができる。また自動的に配列のように1,2,3の値が添え字として使えるので、whereでrank = 3のように指定することもできる。
1 |
rank enum('gold','silver','bronze') |
set型
enum型は1つしか選べないが、set型は複数選ぶことができる。値を代入するときはカンマ区切りで渡す。
1 2 |
coins set('gold','silver','bronze') insert into users (name, score, coins) values ('taguchi', 5.8,'gold,silver'); |
レコードの挿入
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など何をするのかが来る。
1 2 3 4 5 |
末尾に追加 alter table users add column email varchar(255); nameの後に追加 alter table users add column email varchar(255) after name; |
カラムを削除する
1 |
alter table users drop column score; |
カラムのデータ型を変える
1 2 |
名前をnameからuser_nameに変更し、varchar(80)に変更する alter table users change name user_name varchar(80); |
テーブル名を変更する
usersテーブルをpersonsに変更する
1 |
alter table users rename persons; |
大文字・小文字厳密に判定するbinary演算子
binaryをつけたら、tanakaは含まれない。
1 |
select * from users where name like binary 'T%'; |
任意の文字ワイルドカード
1 2 3 4 5 |
nameが6文字だけを抽出 select * from users where name like '______'; nameの2文字目がaだけを抽出 select * from users where name like '_a%'; |
件数を絞る
上から3件だけ表示する
1 |
select * from users limit 3; |
取り出す行を指定する
最初の3行を除外して、3件取り出す
1 |
select * from users limit 3 offset 3; |
スコアの上位3名を表示する
1 |
select * from users order by score desc limit 3; |
レコードの更新
カンマ区切りでフィールド名と値を区切る
1 |
update users set name = 'sasaki', score = 2.9 where name = 'tanaka'; |
レコードの削除
条件式を入れないと全て消えてしまうので注意
1 |
delete users where 条件式; |
四則演算
1 |
+ - * / % 全て使える |
四捨五入関数round
1 2 |
select round(5.355); -- 5 select round(5.355,1); -- 5.4 |
切り捨て関数floor
1 |
select floor(5.833); -- 5 |
切り上げ関数ceil
1 |
select ceil(5.238); --6 |
ランダム値関数rand
0.9404359906357117のような小数点数を返す
1 |
select rand(); |
rand関数を使って抽選
1 |
select * from users order by rand() limit 1; |
文字列の長さを調べるlength関数
1 |
select length('Hello'); -- 5 |
文字列の一部を取り出すsubstr関数
1 2 |
select substr('Hello',2); -- ello select substr('Hello',2,3); -- ell |
文字を全て大文字にするupper関数
1 |
select upper('Hello'); -- HELLO |
文字を全て小文字にするlower関数
1 |
select lower('Hello'); -- hello |
文字列を連結するconcat関数
1 |
select concat('Hello','World'); -- HelloWorld |
別名をつけるAS
別名はorderby区でも使える
1 |
select length(name) as len, name from users order by len |
条件分岐をするif文
scoreを比較して3つ目のカラムにOKやNGを表示する。
1 2 3 4 5 6 |
select name, score, if (score > 5.0,'OK','NG') as result from users; |
条件分岐をするcase文
case文の最後はendで締める。asを使って別名をつける方がベター。下記はtypeという別名。
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のところに直接書く書き方もある。
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は省略できる。
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 > 8.0 then 'Team-A' when score > 6.0 then 'Team-B' else 'Team-C' end as team from users; |
テーブルのコピー
1 |
create users_copy select * from users; |
構造だけをコピーしてテーブルを作成する
1 |
create table users_empty like users; |
行数をカウントする
nullがあるカラムを指定するとそれは除外される。全体の行を取得する場合はcount(*)とする
1 2 |
select count(score) * from users_with_team; select count(*) * 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
例えば、チームに分かれた選手一覧があったとして、何のチームがあるのか一覧で表示したい場合
1 2 |
select distinct team from users_with_team; -- チーム一覧 select distinct count(team) from users_with_team; -- チーム数 |
グループ集計 group by
チームごとの合計スコア
1 |
select sum(score),team from users_with_team group by team; |
グループ集計からの抽出 having
whereと一緒に使った場合は、where条件で集計した後にhavingで抽出することになる。
1 |
select sum(score),team from users_with_team group by team having sum(score) > 10; |
サブクエリ
集計用の別テーブルを作る手間を省ける。
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 > 8.0 then 'Team-A' when score > 6.0 then 'Team-B' else 'Team-C' end as team from users) as t group by t.team; |
抽出条件に名前をつけてテーブルのように扱うview
*AS400の場合はビュー名にライブラリを入れること。
1 2 3 |
create view ビュー名 as セレクト文; create view top3 as select * from users order by score desc limit 3; select * from top3; |
どうやってViewが作られたかを確認する
1 |
show create view top3; |
もしテーブルやビューがあったら削除する
1 2 |
drop table if exists users; drop view if exists top3; |
一覧の処理を必ずまとめて行いたいトランザクションtransaction
start transactionからcommitという処理は必ずまとめて行われる保証がされる。ちなみに下記2行目のupdateをupdataにして、やってみると、1行目は成功して、2行目は失敗だけど、両方の数字は変わらなかった。transactionをつけなければ、確かに1行目だけ実行されてしまった。なるほど。
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には必ず索引が作られる。索引はデータの追加や更新処理を行うたびに作り直されるので、逆にそちらの処理が遅くなるデメリットもあるので、考えて索引を付けるようにすること。アプリケーションの速度が遅い場合は索引を付けたり外したりして調整してみるのも良い。
1 2 |
alter table テーブル名 add index 索引名 (索引を設定したいカラム名); alter table users add index index_score (score); |
テーブルに作られた索引を確認する
1 2 |
show index from テーブル名; show index from users; |
索引が使われているか確認する
索引を設置したscoreをwhere文で使うと、explainのpossible_keysで使っているのが解る。逆にnameをwhere文で使うと、explainで何も表示されない。
1 2 |
explain select文; explain select * from users where score > 5.0; |
索引の削除
1 2 |
alter table テーブル名 drop index 索引名; alter table users drop index index_score; |
内部結合のinner join
2つのテーブルに共通のデータだけを取得する方法。カラムを指定する場合は、テーブル名を前につけるが、同じものが無い場合はつけなくてもよい。
1 |
select posts.id, posts.title, posts.body, comments.body from posts inner join comments on posts.id = comments.post_id; |
外部結合のouter join
2つのテーブルで一致しないデータも含めてデータを取得する方法。外部結合はどちらのテーブルを軸にするかで構文が異なる。尚、outerは省略できる。
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側に一方的に追加した場合、エラーメッセージが表示され、登録されない。データの整合性をとるには便利な仕組み。そして外部キーが設定されていれば、削除することも難しくなる。
1 2 |
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 テーブル名 drop foreign key 制約名 alter table comments drop foreign key fk_comments; |
直前に挿入されたレコードのIDを調べる last_insert_id()
下記の例ではcommentsのpost_idに使っている。これはpostsのincreamentのIDと同じ値なので、直前の値を取得すればOK。comments内の1つ前のIDをincrement的に取得するのではないので注意。ちなみにこの下に同じinsert文を付けるのはダメ。すると、commentsに挿入したIDを取得してしまうから。あくまで直前のIDということ。
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にすることで処理前のデータを取得できる。
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!'); |
トリガーを削除する
1 |
drop trigger if exists post_insert_trigger; |
トリガーの一覧を表示する
\Gをつけると見やすく表示してくれる。小文字のGはエラーになる。
1 2 |
show triggers; show triggers \G; |
区切り文字;を変更する
終わったらまたdelimiterで元に戻しておくこと。
1 2 3 4 5 6 |
delimiter 別の文字 delimiter // 処理; 処理; 処理// delimiter ; |
1つのトリガーで複数の処理を行う
1 2 3 4 5 6 7 8 |
delimiter // create trigger トリガー名 いつ どんな時 on トリガーを設置するテーブル名 for each row begin 処理名 何のテーブルに 何の値を; 処理名 何のテーブルに 何の値を; end // delimiter ; |
トリガーの中で処理前や処理後のカラムの値を取得する
postsテーブルをupdateした後に処理が走る。oldとnewで値を取得できる。
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, ' -> ', 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の後に指定するだけ。いままで括ったことなかったけど、カッコでくくるらしい。
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 )