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を外部ファイルから作る際、別のファイルを新規作成。

```php

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

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

);

```

###レコード挿入

```php

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

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

同じ意味


###フィールドへの制限

- 空白を許さない

```php
create table users(

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

);

```


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


```php
create table users←テーブル名(

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

);

```
- 重複した値を許さない

```php
create table users(

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

);

```

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

```php
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型

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

```php
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` を用いる

```php
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

条件分岐

```php

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

```php

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

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

```php

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;
```
###抽出結果をテーブルにする

```php

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;
```

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

```php

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以上のものを抽出してから、グループ化するという話。

###サブクエリ

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


```php

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`を使う。複雑な構造条件を何度も使う際に便利

```php

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`とすれば、作られた構造が見れる。

###トランザクション

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

```php

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`にすると変更を破棄される。
処理が上手くいかないときなどに使う。

###索引

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

```php


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名;`

```php



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()`と書くと、勝手に対応してくれる。

###トリガー