MySQLを扱う ドットインストールまとめ

今回の課題は、phpMyadminという便利な中継役を使っているので、テーブル作成なんかはあまり使わないかもしれない。
データベースの操作は全部phpMyadminでできる。
本来はvagrant何かを使わないとできない。

このノートにおけるusersは基本ドットインストール内で使われていたテーブル名

MySQLとは

MySQLとはデータベース

tableとは

Excelの表のようなもの。縦横に広がっていて、横が主な項目。縦が個別のデータ数。
データベースの中にいくつでも置いておくことができる。
行＝レコード
属性値＝カラム

SQL(Structured Query Language)

tebleからデータを引っ張ってきたり入れたりする言語

データベースを操作するには

Queryを使用する（大文字小文字は判断されない）

データベースを作ったり消したり

データベースの作成
create database 名前;

テーブルの作成
create table 名前;

作ったデータベースを見る
show databases;

データベースの削除
drop database;

操作対象のデータベースを見る
select database(); いくつも作れるので。もしNULLなら選択されていない

操作対象のデータベースを設定する
use データベース名;

データベースごとに作業用Userを設定

このままだとデータベースがオープンすぎるので

create user ユーザー名@localhost identified by 'パスワード';
grant all on データベース名.* to ユーザー名@localhost;
訳：ユーザー名に対して、データベースの全ての権限を与える。

データベースに接続

$ mysql -u ユーザー名 -p データベース名

外部ファイルを扱う

外部ファイルを扱う際、更新した時にすでに同名のデータベースが存在する場合があるので、
冒頭で

drop exists データ名;

としていればエラーが出なくて済む。

外部ファイルの読み込み

\../create_ファイル名

テーブル操作

desc ユーザー名; で常にテーブルの状態を確認できる

新規テーブル作成

tableを外部ファイルから作る際、別のファイルを新規作成。

drop table if exits users;←今までに同名のテーブルが存在していたらややこしいので削除
create table users(

 id int unsigned,
 name varchar(20),
 score float 

);

レコード挿入

insert into users(id, name, score) values(1, 'taguchi', 5.7);
insert into users(id, name, score) values(2, 'ababa', null);

insert into users(id, name, score) values
(1, 'taguchi', 5.7),
(2, 'ababa', null);

同じ意味

フィールドへの制限

• 空白を許さない

create table users(

 id int unsigned,
 name varchar(20),
 score float not null　←ここ

);

• 値を挿入しなかったときに使用される値

create table users←テーブル名(

 id int unsigned,
 name varchar(20),
 score float default 0.0　←ここ

);

• 重複した値を許さない

create table users(

 id int unsigned,
 name varchar(20) unique, ←ここ
 score float 

);

• レコードをで並び順を変える

create table users(

 id int unsigned primary key,  ←ここ
 name varchar(20),
 score float 

);

テーブル削除

drop table if exists users;

データベースと一緒で、更新する際被るとエラーが出て面倒なので、必ず削除する。
冒頭にこの一文を差し込む。

データ型

number

• int…整数
• float…浮動小数点数
• double…floatより精度の高いもの
• int unsigned…プラスの整数（上の３つはプラスもマイナスも扱えてしまう）

string

• char(4)…固定長のデータ
• varchar(255)…可変長のデータ　メールアドレスと少し長めの時
• text　

date/time

• date
• time
• datetime '2017-07-22 18:11:05'

true/false

テーブルの構造変化

• 後からカラムを追加したり

alter table users add column email varchar(255);

• 後からフィールドを削除したり

alter table users drop column テーブル名;

-フィールドのデータの型を変更

alter table users change name users_name varchar(80 default) 'nobody'←内容も変更できる

• テーブルの名前を変更する

alter table users rename persons;

抽出

レコードを抽出

select id, name from users;

• 範囲指定

select * from users where score >=6.0;
select * from users where score >=3.0 and score >=6.0 ;
select * from users where score between 3.0 and 6.0 ;

他にも
is null/is not null
もある
"かつ"や"もしくは"は、and/orで表す。

文字列を抽出

select * from users where name like = '～～～';

whereは特定のデータのみを抽出する際に良く用いる

'～～～'

• %t…tで終わる文字列全部
• t%…tで始まる
• %t%…tを含む
• ___…文字数（例は３文字）
• _a%…２文字目がa

大文字小文字の区別が無いので

likeの後にbinaryを使う

抽出結果の並び替え

select * from users order by score = '～～～';

• nullを省く＆並び順を逆にする

select * from users where score is not null order by score desc = '～～～';

• 変数を制限する

select * from users limit 3;　３っつだけ
select * from users limit 3 offset 3;　最初の三名を飛ばして３名
select * from users order by score desc limit 3 ; 上位三名

更新と削除

• 更新

update users set score = 5.9,　全部5.9に
update users set score = 5.9 where id = 1; id 1だけが更新
update users set name = 'sasaki' , score = 5.9 where name = 'tanaka';
田中さんが佐々木さんになりスコアも5.9に

-　削除

deleteを使う

delete from users where score <5 5より小さいデータを削除

演算

数値

記号は従来通り

update users set score = score * 1.2 where id % 2 = 0 ;

idが偶数の人のスコアが1.2倍に

• select round(5.3555)=5…四捨五入
  四捨五入する位置を指定する場合はselect round(5.3555,1)=5.4

• select floor(5.8333)=5…切り捨て

• select ceil(5.8333)=6…切り上げ

• select rand()…乱数
  select * from users order by rand() limit 1 ;のように抽選機能に役立つ

文字列

• select length('Hello');=5 長さ
• select substr('Hello' , 2) =ello
• select upper('Hello') = すべて大文字
• select substr('Hello',2,3)　=ell
• select lower('Hello')　= 全て小文字
• select concat('Hello' , 'world')　=連結

文字数準の並び替え

select length(name), name from users order by length (name);
テーブルのデータの文字数が出てくる

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

asを使うとlength(name)がlenに省略される

enum型

複数の文字列の中から一つだけを格納できる

create table users(

 id int unsigned,
 name varchar(20),
 score float not null,
 rank enum('gold','silver',''bronze)←ここ()の中は左から1,2...と連番になっている

);

insert into users (name , score, rank) values ('taguchi', 5.8, 'silver') 
insert into users (name , score, rank) values ('taguchi', 5.8, 'gold')
insert into users (name , score, rank) values ('taguchi', 5.8, 'red') ←これは無視 

select * from users where rank = 'silver'; 
select * from users where rank = '2'; ←連番を利用した指定の仕方

set型

複数のデータから複数格納できる
coins を用いる

create table users(

 id int unsigned,
 name varchar(20),
 score float not null,
 coins set('gold','silver','bronze') ←左から番号指定あり。2の0乗、2の1乗…

);

insert into users (name , score, coins) values ('taguchi', 5.8, 'gold,silver') 
insert into users (name , score, rank) values ('taguchi', 5.8, 'bronze,gold')
insert into users (name , score, rank) values ('taguchi', 5.8, 'red') ←これは無視 

select * from users where coins = 'gold,silver'; 
select * from users where coins like  = '%gold%'←goldを含むすべてのユーザー 
select * from users where coins = 3 ;←ど抽出したいデータの割り振り番号の足し算
この場合goldとsilverを持つユーザーを抽出

coins setで指定した順番は抽出時に忠実に守らなければならない

if,case

条件分岐

select
 name,
 score,
 if (score > 5.0, OK , NG) as result ←resultのカラムができ、scoreによってOK,NG
 from
  users;

select
 name,
 score,
 case floor(score) %2
  when 0 then 'even'
  when 1 then 'odd'
  else null
  end as type                  ←typeカラムができる
 from
  users;

以下のような書き方もできる

select
 name,
 score,
 case floor(score) %2
  when score > 8.0　then 'Team-A'
  when score > 6.0  then 'Team-B'
  else 'Team-C'
  end as type                  ←typeカラムができる
 from
  users;

抽出結果をテーブルにする

select
 name,
 score,
 case floor(score) %2
  when score > 8.0　then 'Team-A'
  when score > 6.0  then 'Team-B'
  else 'Team-C'
  end as type                  ←typeカラムができる
 from
  users;

チームという新しいカラムを追加した状態でテーブルを作成する場合

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 type                 
 from
  users;


以下はテーブルのコピー
create table users_copy select * from users;
select * from users_copy;
こうすることで、usersと同じ構造で中身が空のテーブルを作成することができる。

データの集計処理

• データの個数を調べるcount()

select count(score) from users_with_team;←Nullは含まない
select count(*) from users_with_team;←Nullも含む

• 合計値を求める

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 count(distinct team) from users_with_team;種類の数を表示

カラムのグループで集計する方法(Group 集計)

上記で設定したチームごとに集計を出したい場合、

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

→そうすると、チームごとの集計が表示される。
また、チームAから表示されている形を逆順にしたい場合は

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

ケツにデスクを付ける

また、今までのように、whereで条件を付けて集計したい場合は
whereではなく、havingを使う。

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

*havingを使った場合は、group化したカラムとか集計した値しか使えない

こうすることで、10より大きいチームが抽出される。

whereとhavingを同時に使う場合は

select sum(score), team from users_with_team　where id > 3 group by team ;

idで3以上のものを抽出してから、グループ化するという話。

サブクエリ

• 一時的にしか使わないテーブル

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 type                 
 from
  users) as t  ←ここ
 group by team ;

今まで他のテーブルで設定してきたクエリを、サブクエリを使って借りてくることができる。

抽出条件に名前を付けてテーブルにする

viewを使う。複雑な構造条件を何度も使う際に便利

insert into users(name, score) values('fkoji', 8.2);
insert into users(name, score) values('dotinstall', 6.1);
insert into users(name, score) values('tanaka', 4.2);
insert into users(name, score) values('yamada', null);
insert into users(name, score) values('tashiro', 7.9);

drop view if exits top 3; ←今までにtop3があればエラーになるのであらかじめ削除
create view top3 as select * from order by score desc limit 3;
select from top3 ; 

show tables;とすれば、tableが見れる。

show create view top3とすれば、作られた構造が見れる。

トランザクション

まとめて行いたい処理があるときに使う
以下はスコアの移動を行っている

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

select * from users;

commitの部分をrollbackにすると変更を破棄される。
処理が上手くいかないときなどに使う。

索引

データの抽出が速くなる
索引をつけすぎると他の処理が遅くなる

drop table if exits users;
create table users(

 id int unsigned primary key auto_increment,
 name varchar(20),
 score float 

);

alter table users add index_score(score);←scoreに索引を追加
show index from users;
explain select * from users where score > 5.0; ←索引が使われているかの検査
explain select * from users where name;←nameには索引を追加していないのでNullに

alter table users drop index index_score;←スコア―の削除

複数のテーブルを扱う

内部結合と外部結合

• inner join

内部結合

2つのデータに共通のものがあれば取得する

select * from テーブル名1 inner join テーブル名2 on テーブル名1.カラム名 = テーブル名2.カラム名;

innerは省略できる。書かなくていい

• outer join

外部結合

どちらかのテーブルを軸にして、片方に共通のデータがない場合も取得していく。
逆に軸にない項目があれば、取得されない。Null

select * from テーブル名1 left outer join テーブル名2 on テーブル名1.カラム名 = テーブル名2.カラム名;

select * from テーブル名1 right outer join テーブル名2 on テーブル名1.カラム名 = テーブル名2.カラム名;

こちらも、outerは省略可。

外部key制約

こうするとテーブル名2のカラムに関しては、テーブル名1に値があるものしか挿入できなくなる。これを設定するには、カラムのデータ型が一致している必要がある。

alter table テーブル名2 add constraint fk_テーブル名2←key名は自分で設定 foreigh key (カラム名) reference テーブル名1(カラム名);

外部Key制約で設定されたカラムを削除することはできない。

外部key制約の削除
alter table comments drop foreign key key名;

delete from posts where id = 2;
insert into posts(title, body) values('new title', 'new body');
insert into comments(post_id, body) values(last_insert_id(), 'first comment for new post');

前提の説明はドットインストール見た方が速い。
とにかく、別のテーブルのカラム同士で、対応するid（idは基本連番）が削除したなどの理由によりわからない場合は、last_insert_id()と書くと、勝手に対応してくれる。

トリガー