はじめに
教材はドットインストールのMySQL入門 (全36回) を使っており、こちらを写経した内容になっています。 写経内容は以下のPRでまとめています。
開発環境について
開発環境はMacを使ってます。 MySQLのバーションは
mysql Ver 14.14 Distrib 5.7.21, for osx10.13 (x86_64) using EditLine wrapper
です。
学習方法について
基本はこのルールに沿ってひたすら写経です。
学ぼうと思った経緯
ディレクターさんから調査依頼でDBから数値を抽出する作業の際にinner joinやouter joinの違いもちゃんと理解せずに操作をしてしまい、間違ってデータを出してしまい、今まで雰囲気で書いてたSQLをやめて、しっかり基礎文法で学ぼうと思ったのがきっかけです。
基本的なCRUDに関わるSQLなどは、そこまで複雑ではないですが、調査依頼だと複雑な条件が入るので、今後そういった要件に対してもすぐにSQLを書けてデータを抽出できるようにします。
MySQL とは?
- MySQL は高速性と堅牢性を追及したマルチユーザ・マルチスレッドのＳＱＬデータベースです。
- 世界で最も人気のあるオープンソースデータベースです。
ref:MySQL
用語説明
- 行:レコード(
Record/Row)- データ
- 横
- 列:フィールド(
Fields/Column)- 属性値
- 縦
- SQL(
Structured Query Language)DatabaseやTable、FieldやRecordを扱う言語をSQL（Structured Query Language）と呼ぶ
基本的なコマンド
MySQLのクエリは大文字、小文字が区別されないです。
MySQLが動いているか確認する
$ mysql.server status SUCCESS! MySQL running (581)
ログイン
ルートユーザでログインする
mysql -uroot
現在のユーザを確認する
mysql> select user(); +----------------+ | user() | +----------------+ | root@localhost | +----------------+ 1 row in set (0.00 sec)
接続を切る
\q
もしくは
exit;
データベースへの操作
データベースの一覧を取得する
mysql> show databases +--------------------+ | Database | +--------------------+ | information_schema | | homestead | | laravel_app | | laravel_tutorial | +--------------------+
データベースを作成する
create database dotinstall_db Query OK, 1 row affected (0.03 sec)
データベースを削除する
drop database dotinstall_db; Query OK, 0 rows affected (0.00 sec)
データベースの操作対象を切り替える
useコマンドを使う
選択されてない状態
mysql> select database(); +------------+ | database() | +------------+ | NULL | +------------+
選択する
mysql> use dotinstall_db; Database changed mysql> select database(); +---------------+ | database() | +---------------+ | dotinstall_db | +---------------+ 1 row in set (0.00 sec)
作業ユーザを作成して権限を与える
ユーザを作成する
create user dbuser@localhost identified by '****';
ユーザ名dbuser@localhostにdot_installDBに対する操作権限を全て与える
grant all on dotinstall_db.* to dbuser@localhost;
ログイン
mysql -u dbuser -p パスワード入力
ユーザを確認する
mysql> select user(); +------------------+ | user() | +------------------+ | dbuser@localhost | +------------------+ 1 row in set
ユーザを削除する
※ルートユーザで作業を行う
drop user dbuser@localhost
外部からSQLを実行する
リダイレククションで実行する
mysql -u root < mysql/create_mysql.sql
mysqlサーバに入ってsorce or \.コマンドを実行する
mysql> \. ./mysql/create_mysql.sql Query OK, 0 rows affected (0.01 sec) Query OK, 1 row affected (0.00 sec) Query OK, 0 rows affected, 1 warning (0.00 sec)
テーブルへの操作
テーブル一覧を見る
mysql> show tables; +-----------------+ | Tables_in_myapp | +-----------------+ | users | +-----------------+
テーブル構造を見る
descコマンド
mysql> desc users; +-------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------------+------+-----+---------+-------+ | id | int(10) unsigned | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | score | float | YES | | NULL | | +-------+------------------+------+-----+---------+-------+ 3 rows in set (0.01 sec)
テーブルの削除方法
drop <table_name>
mysql> drop table users; Query OK, 0 rows affected (0.00 sec) mysql> show tables; Empty set (0.00 sec)
データ型について
Numberint:整数float:浮動小数double:倍精度浮動小数点int unsigned(マイナスの領域は使わず、プラスの領域だけを使う)
Stringchar:固定varchar:可変長text:長さがわからない大きいデータの場合
Date/Timedate:日付time:時間datetime:日時2017-07-22 17:23:33
True/Falseboolean==tinyint(1)- trueは1(空文字を含むそれ以外の値は全て true になる)
- falseは0と管理されれている
ref:MySQL :: MySQL 5.6 リファレンスマニュアル :: 11 データ型
フィールドの属性について
not null: nullは入らないdefault: デフォルト値を設定できるunique: 重複した値を許さないprimary key:レコードを一意に特定できるようなフィールドには、主キーを付けることが推奨されています。- これを入れるだけで、
nullでなく、unique(重複しない値)である事が保証されます。 - 主キー属性のフィールドには自動的に
UNIQUE&NOT NULL属性がつきます。
- これを入れるだけで、
auto_increment:自動で連番にしてくれる
Alter テーブル構造の変更
フィールドを後から追加する
alterコマンドを使って属性やカラムを追加する
-- フィールドの属性を後から追加する alter table users add column email varchar(255);
afterを指定すると、特定のカラムの後ろに追加する事ができる
-- フィールドの属性を後から追加する alter table users add column email varchar(255) after name;
フィールドを後から削除する
alter table users drop column score ;
フィールドのデータの型や名前を変えたい場合
alter table users change name user_name varchar(80) default 'nobody';
テーブルの名前を変えたい場合
alter table users rename persons;
Select レコード抽出
slect * from table_name:全件表示select id,name ftom table_name:カラムを指定して表示where:条件付きで抽出する- 論理演算
x<=y:xはy以下であるx>=yxはy以上である<>or!=等しくない
nullis null:nullかis not nullnullではない
where column between X and Y:X~Yの範囲に含まれているかin: () の中の値のどれかlike:部分一致%:0文字以上のワイルドカード%endu%:enduを含む%ですね:ですねで終わる文字列
binaly:SQLは大文字、小文字を区別できないがbinaly演算子を使うと厳密に比較をしてくれる_:任意の文字を指定できる_e%:名前の2文字目がeの人
order by:並び替えを行うdesc:降順ack:昇順
limit:件数を絞るoffset:取得開始地点を指定ずる
sql_practice/select.sql at master · Fendo181/sql_practice
Update レコードの更新
update <table_name> set <column_name>コマンドを使う
whereでレコードを指定して更新する
update users set score = 5.7 where id = 1; update users set name = 'tanaka', score = 6.0 where id = 1;
whereでレコードを指定しなければ、条件にマッチするレコードを全て更新する
Delete レコードの削除
全件削除
delete from users
スコアが5.0以下のレコードを削除する
delete from users where score < 5.0;
sql_practice/update.sql at master · Fendo181/sql_practice
組み込み関数
数値計算
rand(5.355):5- 四捨五入
rand(5.355,1):5.4- 小数点 1 桁目で丸めたい場合
floor(5.355):5- 小数点以下切り捨てにできる
ceil(5.355)):6- 小数点以下を切りあげ
rand()- 乱数
select rand(5.355); -- 5 select rand(5.355, 1); -- 5.4 select floor(5.833); -- 5 select ceil(5.238); -- 6 select rand();
スコアをランダムの値でソートして、一番上の値を抽出する際のSQL
select * from users order by rand() limit 1;
文字列の演算
length:長さを取得するsubstr:文字を切り取るupper:文字列を大文字にするlower:小文字にするconcat:文字列を連結する
そのほかに使える関数はこちらをご覧下さい。
ref:MySQL :: MySQL 5.6 リファレンスマニュアル :: 12 関数と演算子
sql_practice/string.sql at master · Fendo181/sql_practice
enum型
複数の値から、1つだけが格納できるようにする。
MySQL の設定にもよりますが、この中にある値以外をはじくことができます。
また内部的には連番で振られているので、1や2で指定できます。
enum型のtableを作成する
create table users ( id int unsigned primary key auto_increment, name varchar(20), score float, rank enum('gold', 'silver', 'bronze') );
sql_practice/enum.sql at master · Fendo181/sql_practice
set型
予め指定した値の中から複数の値を格納できるようにする。
enumと違って複数値を入れられます。ただし、Insert時に順番を変えたとしても、create table で定義した順番に戻っている。
したがって抽出時にはこちらの順番を守りつつ条件を指定してあげる必要があります。
alter table users add column coins set('gold','silver','bronz') insert into users (name, score, coins) values ('taguchi', 5.8, 'silver,gold'); insert into users (name, score, coins) values ('taguchi', 5.8, 'bronz,gold'); -- ERROR 1265 (01000): Data truncated for column 'coins' at row 1 insert into users (name, score, coins) values ('taguchi', 5.8, 'red,gold');
sql_practice/set.sql at master · Fendo181/sql_practice
if,case文
if文を使った例
-- if文で分岐する select name, score, -- 5.0以上だったらOKでそれよりも下だったらNGになる if (score > 5.0 , 'OK' , 'NG' ) as result from users;
結果
+---------+-------+--------+ | name | score | result | +---------+-------+--------+ | endu | 5.8 | OK | | koji | 5.7 | OK | | kikuchi | 4.9 | NG | | hoge | 1 | NG | | tomita | 0.28 | NG | | sakita | 3 | NG | +---------+-------+--------+
case文の例
-- case文で分岐する select name, score, -- 5.0以上だったらOKでそれよりも下だったらNGになる case floor(score) % 2 -- 2で割れたら偶数 when 0 then '偶数' -- 2で割られなかったら奇数 when 1 then '奇数' else NULL end as type from users;
結果
+---------+-------+--------+ | name | score | type | +---------+-------+--------+ | endu | 5.8 | 奇数 | | koji | 5.7 | 奇数 | | kikuchi | 4.9 | 偶数 | | hoge | 1 | 奇数 | | tomita | 0.28 | 偶数 | | sakita | 3 | 奇数 | +---------+-------+--------+
case文ではwhenの後直接、条件分岐がかける
select name, score, -- 5.0以上だったらOKでそれよりも下だったらNGになる case when score > 5.0 then 'Team-A' when score > 4.0 then 'Team-B' else 'Team-C' end as TeamName from users;
結果
+---------+-------+----------+ | name | score | TeamName | +---------+-------+----------+ | endu | 5.8 | Team-A | | koji | 5.7 | Team-A | | kikuchi | 4.9 | Team-B | | hoge | 1 | Team-C | | tomita | 0.28 | Team-C | | sakita | 3 | Team-C | +---------+-------+----------+
sql_practice/if.sql at master · Fendo181/sql_practice
抽出結果で新しくテーブルを作成する
selectで抽出した結果を別のtableとして生成する。
-- selectで抽出した結果を別のテーブルにする create table users_with_team as select id, name, score, -- 5.0以上だったらOKでそれよりも下だったらNGになる case when score > 5.0 then 'Team-A' when score > 4.0 then 'Team-B' else 'Team-C' end as TeamName from users; select * from users_with_team;
結果
+----+---------+-------+----------+ | id | name | score | TeamName | +----+---------+-------+----------+ | 1 | endu | 5.8 | Team-A | | 2 | koji | 5.7 | Team-A | | 3 | kikuchi | 4.9 | Team-B | | 4 | hoge | 1 | Team-C | | 5 | tomita | 0.28 | Team-C | | 6 | sakita | 3 | Team-C | +----+---------+-------+----------+
また既存のtableもコピーする事ができる。
-- users tableをそのままコピーする create table uses_copy select * from users; select * from uses_copy;
結果
+----+---------+-------+ | id | name | score | +----+---------+-------+ | 1 | endu | 5.8 | | 2 | koji | 5.7 | | 3 | kikuchi | 4.9 | | 4 | hoge | 1 | | 5 | tomita | 0.28 | | 6 | sakita | 3 | +----+---------+-------+ 6 rows in set (0.00 sec)
それ以外だとtableの構造だけコピーをしたい時はlike文だけを使うようにする。
-- テーブルの構造だけコピーしたい create table users_empty like users; desc users_empty;
結果
+-------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | name | varchar(20) | YES | | NULL | | | score | float | YES | | NULL | | +-------+------------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec)
sql_practice/abstract_table.sql at master · Fendo181/sql_practice
データの集計処理
count():集計を行うavg():平均値を取るdistinct:重複するレコードを除く ユニークな値だけを取得する
create table users ( id int unsigned primary key auto_increment, name varchar(20), score float ); insert into users(name,score)values ('endu',5.8), ('koji',null), ('kikuchi',4.9), ('hoge',1.0), ('hoge',.28), ('sakita',3.0) ; -- 重複するレコードを除く ユニークな値だけを取得する select distinct name from users_with_team;
結果
+---------+ | name | +---------+ | endu | | koji | | kikuchi | | hoge | | sakita | +---------+ 5 rows in set (0.00 sec)
sql_practice/count.sql at master · Fendo181/sql_practice
groupe by,having
groupe byでグループ集計する事ができる。- グルーピングした場合は
whereではなくhavingで条件をつけるようにするhavingを使う場合は、グループ化に使ったカラムや、集計した値しか条件に使えない。
whereとgroupe byを両方使うと、最初にwhere文が優先される。
-- グループごとで集計を取る select sum(score), TeamName from users_with_team group by TeamName; --降順で select sum(score), TeamName from users_with_team group by TeamName desc; -- スコアが10よりも大きいチームだけを表示する select sum(score), TeamName from users_with_team group by TeamName having sum(score) >10;
ref:sql_practice/groupe_by.sql at master · Fendo181/sql_practice
サブクエリ
新しくテーブルを作らずに一時的な抽出結果を用意する事ができます。
select sum(t.score), t.TeamName from ( select id, name, score, -- 5.0以上だったらOKでそれよりも下だったらNGになる case when score > 5.0 then 'Team-A' when score > 4.0 then 'Team-B' else 'Team-C' end as TeamName from users ) as t group by t.TeamName;
sql_practice/sub_query.sql at master · Fendo181/sql_practice
View
viewとは抽出条件に名前を付けてテーブルのように扱うことができます。 ただし、view は抽出条件であって値のコピーをして新しいテーブルを作るわけではないので、おおもとになる テーブルの値が変われば結果も当然変わります。
-- view(抽出条件に名前をつけてテーブルように扱えるようにする) create view top3 as select * from users order by score desc limit 3; -- 表示する select * from top3; -- 一覧を見る show tables;
-- どうやってそのviewが作られたかを見る show create view top3 \G;
結果
*************************** 1. row *************************** View: top3 Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`myapp_user`@`localhost` SQL SECURITY DEFINER VIEW `top3` AS select `users`.`id` AS `id`,`users`.`name` AS `name`,`users`.`score` AS `score` from `users` order by `users`.`score` desc limit 3 character_set_client: utf8 collation_connection: utf8
sql_practice/view.sql at master · Fendo181/sql_practice
Transaction
一連の処理を必ずまとめて行いたい場合に使います。
- 変更を反映させる場合は
commit
start TRANSACTION; -- 何らかのSQL -- 変更を反映させる COMMIT;
- 行った変更を破棄させる場合は
ROLLBACKです。
start TRANSACTION; -- 何らかのSQL ROLLBACK;
ref:sql_practice/transaction.sql at master · Fendo181/sql_practice
index(索引)
indexをつけるとデータの抽出が速くなる。
primary key には必ず索引が作られます。
索引は抽出時は早いですが、データの追加や更新処理を行うたびに作り直されるので、そちらの処理が遅くなってしまうというデメリットがあります。
とはいえ、抽出処理が遅いなと思ったら索引を付けたり外したりして、パフォーマンスを最適化できるようになっておくと良いです。
- indexの追加
alter table {table_name} add index {index_name} (column);
- indexの削除
alter table {table_name} drop index {index_name};
-- indexを追加する alter table users add index index_score (score); show index from users\G; -- その索引が使われているかはkeyで確認できる explain select * from users where score > 5.0\G; -- 使ってない場合はnullになる explain select * from users where name = 'endu'\G; -- 索引の削除 alter table users drop index index_score; show index from users\G;
sql_practice/index.sql at master · Fendo181/sql_practice
内部結合でデータを抽出する
inner join:内部結合を行う2 つのテーブルに共通のデータだけを取得する方法
select posts.id, title, comments.body from posts inner join comments on posts.id = comments.post_id
実行結果
+----+--------+----------------------------+ | id | title | body | +----+--------+----------------------------+ | 1 | title1 | first comment! for | | 1 | title1 | Secound comment! for post1 | | 3 | title3 | Third comment3 | | 4 | title4 | Yes!Yes!It me 4 | +----+--------+----------------------------+ 4 rows in set (0.00 sec)
sql_practice/inner_join.sql at master · Fendo181/sql_practice
外部結合
outer join:外部結合を行う- 2 つのテーブルで一致しないデータも含めてデータを取得する方法になります。
ただし、外部結合はどちらのテーブルを軸にするかで書き方が変わってくる
left outer join- 左側のテーブルを軸にする
right outer join- 右側のテーブルを軸にする
※outerは省略する事が可能です。
Left Join(左のテーブルを軸にします)
select * from posts left join comments on posts.id = comments.post_id;
実行結果
+----+--------+--------+------+---------+----------------------------+ | id | title | body | id | post_id | body | +----+--------+--------+------+---------+----------------------------+ | 1 | title1 | Hello! | 1 | 1 | first comment! for | | 1 | title1 | Hello! | 2 | 1 | Secound comment! for post1 | | 3 | title3 | Yes! | 3 | 3 | Third comment3 | | 2 | title2 | Bye! | NULL | NULL | NULL | +----+--------+--------+------+
Right Join(右側のテーブルを軸にします)
select * from posts right join comments on posts.id = comments.post_id;
+------+--------+--------+----+---------+----------------------------+ | id | title | body | id | post_id | body | +------+--------+--------+----+---------+----------------------------+ | 1 | title1 | Hello! | 1 | 1 | first comment! for | | 1 | title1 | Hello! | 2 | 1 | Secound comment! for post1 | | 3 | title3 | Yes! | 3 | 3 | Third comment3 | | NULL | NULL | NULL | 4 | 4 | Yes!Yes!It me 4 |
sql_practice/outer_join.sql at master · Fendo181/sql_practice
外部キー制約
外部キー制約を使うとinsertをした時に関連したデータでなければ挿入できないように制限をかける事ができます。
しかし外部キー制約を設定してしまうと、関連するデータがある場合にはデータの削除や更新が簡単にはできなくなります。
comments の post_id に関しては posts テーブルの id に値があるものだけしか挿入できなくなります。加えて、紐付けるカラムの型が一致していないといけないので、統一してあげる。
alter table comments add constraint fk_comments FOREIGN key (post_id) REFERENCES posts(id);
入れようとするとこんなエラーが出る
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`myapp`.`#sql-44e_10`, CONSTRAINT `fk_comments` FOREIGN KEY (`post_id`) REFERENCES `posts` (`id`))
外部キー制約を削除する
alter table comments drop foreigin_key fk_comments;
last_insert_id()を使って、直前にinsertされたレコードのidを取得する
MySQL では直前に挿入されたレコードの id を調べる命令が用意されています。
それがlast_insert_id()です。
これを使えば、直前で挿入されたidを取得する事ができます。
関連した複数のテーブルにデータを挿入する場合は last_insert_id() をよく使います。
INSERT into comments (post_id,body) values ( last_insert_id(), 'first comment for new post' ) ;
ref:sql_practice/foreigin_key.sql at master · Fendo181/sql_practice
Trigger
テーブルでなんらかの変更が起きたときに、それをトリガーにして何らかの処理をすることができる仕組みが Trigger です。
ex)postsテーブルにinsertが走ったらlogsテーブルにメッセージが入るposts_insert_triggerトリガーを作成する
create trigger posts_insert_trigger after insert on posts for each row insert into logs(msg) values('post add');
after:実行後に行うbefore:実行前に行う
triggerを削除する
drop trigger if exists posts_insert_trigger;
sql_practice/trigger.sql at master · Fendo181/sql_practice
1つのTriggerで2つの処理を行う
複数の処理を1つのtriggerで行う場合はbegin~end;文を使います。
加えて、;で終わってしまうので、delimiterを使って複数のSQLが実行できるようにしておきます。
-- 区切り文字(//)を変更する 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 ;
concatはmysqlに備わってい組み込み関数で文字列の連結ができます。
sql_practice/trigger2.sql at master · Fendo181/sql_practice
作成日時と更新日時を簡単に管理する
datetime current_timestampで現在の日時情報を入れてくれます。 -update時に更新をしてほしいときは以下のようにon update current_timestampを入れる。
created datetime default current_timestamp, updated datetime default current_timestamp on update current_timestamp
sql_practice/datetime.sql at master · Fendo181/sql_practice
日時計算を行う
date_add()を使えば、足した日数を自動で出してくれる。
-- 14日を足す select created,date_add(created, interval 14 day ) from posts; -- 2週間追加する select created,date_add(created, interval 2 week ) from posts;
date_formatを使えば、日時の時間のフォーマットを変更できる。
select created, date_format(created, '%W %M %Y') from posts;;
mysqlのマニュアル ref:MySQL :: MySQL 5.6 リファレンスマニュアル :: 12.7 日付および時間関数
sql_practice/datetime2.sql at master · Fendo181/sql_practice
データベースを書き出してみよう
mysqldumpコマンドを使ってsqlを書き出す。
バックアップを取る
mysqldump -u <table_name> -p <db_name> > <faile_name>.backup.sql
バックアップを復元させる
意図させてpostテーブルの一部を削除させる
delete from posts where id = 1; select * from posts
実行結果
+----+--------+-------------+---------------------+---------------------+ | id | title | body | created | updated | +----+--------+-------------+---------------------+---------------------+ | 2 | title2 | Bye! | 2020-07-05 23:50:00 | 2019-07-04 00:42:08 | | 3 | title3 | Yes! | 2019-07-04 00:42:08 | 2019-07-04 00:42:08 | | 4 | title4 | Off Course! | 2019-07-04 00:42:08 | 2019-07-04 00:42:08 | +----+--------+-------------+---------------------+---------------------+ 3 rows in set (0.00 sec)
backupから復元させる
mysql>\. ./****.backup.sql
実行結果
+----+--------+-------------+---------------------+---------------------+ | id | title | body | created | updated | +----+--------+-------------+---------------------+---------------------+ | 1 | title1 | Hello! | 2019-07-04 00:42:08 | 2019-07-04 00:42:08 | | 2 | title2 | Bye! | 2020-07-05 23:50:00 | 2019-07-04 00:42:08 | | 3 | title3 | Yes! | 2019-07-04 00:42:08 | 2019-07-04 00:42:08 | | 4 | title4 | Off Course! | 2019-07-04 00:42:08 | 2019-07-04 00:42:08 | +----+--------+-------------+---------------------+---------------------+ 4 rows in set (0.00 sec)
以上です。 お疲れ様でした。
