6日目：MySQL操作

今日の授業は、Railsを使わず、MySQLのみでデータ入力、操作する内容でした。

#はじめに(教訓）
データ入力操作を、頭の中で考えてやるのは、今は無理。紙なり、エクセルなり使用。

#テーブル同士の関係Association
##belong_to : 1対1の関係
(例)　exam_result(試験点数) belong_to student
      とある教科の試験の点数は、特定の1人の生徒に帰属
##has many : 1対多の関係
(例)　student has many exam_result
　　　生徒は複数の試験を受けるので。
##many to many :多対多の関係
(例)  学生と部活の関係。学生は複数の部活に所属し、1つの部活には複数の学生が所属。
　　　当然、まったく所属していない学生も居る可能性がある

| 生徒 | 部活１ | 部活２ | 部活３ | 部活4 |
|:-:|:-:|:-:|:-:|:-:|
| Aさん  | 自転車部  | 軽音部  | 水泳部  | バスケ部  |
| Bさん  |   |   |   |   |
| Cさん  | 茶道部  | 華道部  | 水泳部 |   |
| Dさん |茶道部|軽音部|||

###多対多テーブルの問題
DB上では1つのカラムに複数のデータは入れることが出来ない。
上の表では、同義のカラム（部活１、部活2．。。）を増やしているが、
データベースでは、これを2次元で表現することができない

###解決法：中間テーブル(/関連テーブル)、
まず、生徒と部活にIDを持たせる

| 生徒 | 生徒ID |
|:-:|:-:|
| Aさん  | 1  |
| Bさん  | 2  |
| Cさん  | 3  |
| Dさん  | 4  |

| 部活 | 部活ID |
|:-:|:-:|
| 自転車部  | 1  |
| 茶道部  | 2  |
| 水泳部  | 3  |
| バスケ部  | 4  |
| 軽音部  | 5  |
|華道部| 6 |

中間テーブル

| 生徒ID | 部活ID |
|:-:|:-:|
| 1  | 1  |
| 1  | 3  |
| 1  | 4  |
| 1  | 5  |
| 3  | 2  |
| 3  |  3 |
| 3  |  6 |
| 4  | 2  |
| 4  | 5  |

実際にDBで触るときは、生徒テーブルと中間テーブルをJOINし、さらに中間テーブルと部活テーブルをJOINする
実際のデータいじりは、明日分のアウトプットにて。


#使うデータ
- データベース:univ
- 生徒テーブル：students
    - name, grade, email, age, gender, others, created_at, updated_at
- 試験結果テーブル: exam_results
    - name, student_id, score, max_score, created_at, updated_at

#本題：MySQL操作 CREATE DATABASE
##データベース作成
```sql:mysql
# CREATE DATABASE データベース名;
CREATE DATABASE univ;
``
##CREATE TABLE :テーブル作成
テーブル名は複数形で。

```sql:mysql
# CREATE TABLE `students`(`カラム名`　データ型　その他指定, `カラム名`　データ型　その他指定, ...);
CREATE TABLE `students`(
`id` INT NOT NULL PRIMARY KEY  AUTO_INCREMENT,
`name` VARCHAR(255) NOT NULL,
`grade` INT(128) NOT NULL,
`email` VARCHAR(255) NOT NULL,
`age` INT(128) NOT NULL,
`gender` INT(128) NOT NULL,
`others` TEXT,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
```
###バッククォートとシングルクォート
- バッククォート：テーブル名に使う
- シングルクォート：データの文字列に使う

**但し、バッククォートは必須ではない**
バッククォートよって、テーブル名やカラム名で使用できないように指定されている予約語（Reserved Words）を使えるようにできる。が使わない方が良い。
[参照：phpMyAdminのSQLに付加される「'」に似た記号「`」は何？](https://php1st.com/1184/)

###PRIMARY KEY、AUTO_INCREMENT
PRIMARY KEY : 主キーの意。なお、外部キーはforeign key
AUTO_INCREMENT : 値が指定しないと、自動的にシーケンス番号を割り当てられる。
　　　　　　　　　 整数型で、1ずつ増加し連番。
###MySQLのデータ型
- INT : 整数型（他にも、TINYINT < SMALLINT < MEDIUMINT < INT < BIGINT
- CHAR : 文字型（似たようなのには、VARCHARがある。）
- TIMESTAMP : 日付時刻型（'YYYY-MM-DD HH:MM:SS'）
- 入力するデータ量に従って、最適なデータ型を選んだ方が、色々と良いようだ。
    - [参照：MySQL 5.6 リファレンスマニュアル  /  データ型](https://dev.mysql.com/doc/refman/5.6/ja/data-types.html)

##DESC :テーブル情報の確認(Describeの略
```sql:mysql
DESC students;
# 結果
+------------+--------------+------+-----+-------------------+----------------+
| Field      | Type         | Null | Key | Default           | Extra          |
+------------+--------------+------+-----+-------------------+----------------+
| id         | int(11)      | NO   | PRI | NULL              | auto_increment |
| name       | varchar(255) | NO   |     | NULL              |                |
| grade      | int(128)     | NO   |     | NULL              |                |
| email      | varchar(255) | NO   |     | NULL              |                |
| age        | int(128)     | NO   |     | NULL              |                |
| gender     | int(128)     | NO   |     | NULL              |                |
| others     | text         | YES  |     | NULL              |                |
| created_at | timestamp    | NO   |     | CURRENT_TIMESTAMP |                |
| updated_at | timestamp    | NO   |     | CURRENT_TIMESTAMP |                |
+------------+--------------+------+-----+-------------------+----------------+
```
同様に、exam_resultsテーブルも作成

```sql:mysql
CREATE TABLE `exam_results` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
　`student_id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `score` int(128) NOT NULL,
  `max_score` int(128) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8

DESC subjects;
# 結果
sql> DESC exam_results;
+------------+--------------+------+-----+-------------------+----------------+
| Field      | Type         | Null | Key | Default           | Extra          |
+------------+--------------+------+-----+-------------------+----------------+
| id         | int(11)      | NO   | PRI | NULL              | auto_increment |
| student_id | int(11)      | NO   |     | NULL              |                |
| name       | varchar(255) | NO   |     | NULL              |                |
| score      | int(128)     | NO   |     | NULL              |                |
| max_score  | int(128)     | NO   |     | NULL              |                |
| created_at | timestamp    | NO   |     | CURRENT_TIMESTAMP |                |
| updated_at | timestamp    | NO   |     | CURRENT_TIMESTAMP |                |
+------------+--------------+------+-----+-------------------+----------------+
```
###ALTER TABLE :カラム変更
```sql:mysql
ALTER TABLE テーブル名 ADD COLUMN カラム名 カラム定義,
                      DROP COLUMN カラム名,
                      CHANGE COLUMN 変えるカラム名 新しいカラム名 カラム定義,
                      MODIFY COLUMN カラム名 新しいカラム定義;
```

##INSERT INTO :データ追加
```sql:mysql
# INSERT INTO テーブル名 (カラム1, カラム2, カラム3, ...) 
VALUE (カラム1データ, カラム2データ, カラム3データ, ...),(カラム1データ, ........);

# students生徒テーブル
INSERT INTO students (name, grade,email, age, gender, others, created_at, updated_at ) value('taro',1, 'taro@email',23,0,'nothing_special',NOW(),NOW()), ('oriver',3, 'oriver@email',25,1,'別に',NOW(),NOW()),('yuki',1, 'jo@gmail.com', 20, 1, 'こんにちは、備考です。', now(), now());

# SELECT * FROM tables;で見る
+----+--------+-------+--------------+-----+--------+-----------------------------------+---------------------+---------------------+
| id | name   | grade | email        | age | gender | others                            | created_at          | updated_at          |
+----+--------+-------+--------------+-----+--------+-----------------------------------+---------------------+---------------------+
| 14 | taro   |     1 | taro@email   |  23 |      0 | nothing_special                   | 2019-03-12 22:58:04 | 2019-03-12 22:58:04 |
| 15 | oriver |     3 | oriver@email |  25 |      1 | 別に                              | 2019-03-12 22:58:04 | 2019-03-12 22:58:04 |
| 16 | yuki   |     1 | jo@gmail.com |  20 |      1 | こんにちは、備考です。            | 2019-03-12 22:58:04 | 2019-03-12 22:58:04 |
+----+--------+-------+--------------+-----+--------+-----------------------------------+-
```
```sql
:mysql
#exam_results試験結果テーブル
INSERT INTO exam_results (student_id,name, score, max_score, created_at, updated_at ) value 
(14,'育種学',65,100,NOW(),NOW()),(14,'技術者倫理',70,100,NOW(),NOW()),(14,'農業機械',89,100,NOW(),NOW()),(14,'植物病理学',91,100,NOW(),NOW()),(14,'応用昆虫学',66,100,NOW(),NOW()),
(15,'育種学',83,100,NOW(),NOW()),(15,'技術者倫理',74,100,NOW(),NOW()),(15,'農業機械',56,100,NOW(),NOW()),(15,'植物病理学',45,100,NOW(),NOW()),(15,'応用昆虫学',72,100,NOW(),NOW()),
(16,'育種学',72,100,NOW(),NOW()),(16,'技術者倫理',83,100,NOW(),NOW()),(16,'農業機械',69,100,NOW(),NOW()),(16,'植物病理学',99,100,NOW(),NOW()),(16,'応用昆虫学',69,100,NOW(),NOW());

#結果
+----+------------+-----------------+-------+-----------+---------------------+---------------------+
| id | student_id | name            | score | max_score | created_at          | updated_at          |
+----+------------+-----------------+-------+-----------+---------------------+---------------------+
| 51 |         14 | 育種学          |    65 |       100 | 2019-03-12 23:01:58 | 2019-03-12 23:01:58 |
| 52 |         14 | 技術者倫理      |    70 |       100 | 2019-03-12 23:01:58 | 2019-03-12 23:01:58 |
| 53 |         14 | 農業機械        |    89 |       100 | 2019-03-12 23:01:58 | 2019-03-12 23:01:58 |
| 54 |         14 | 植物病理学      |    91 |       100 | 2019-03-12 23:01:58 | 2019-03-12 23:01:58 |
※長いので、以下省略
```
##UPDATE SET WHERE:テーブル情報の更新
今の時点では、max_scoreが100点だが、200点満点に変更してみる

```sql:mysql
# UPDATE テーブル名 SET カラム名 = 新しい情報 WHERE 条件
UPDATE exam_results SET max_score = 200;
# 結果
+----+------------+-----------------+-------+-----------+---------------------+---------------------+
| id | student_id | name            | score | max_score | created_at          | updated_at          |
+----+------------+-----------------+-------+-----------+---------------------+---------------------+
| 51 |         14 | 育種学          |    65 |       200 | 2019-03-12 23:01:58 | 2019-03-12 23:01:58 |
| 52 |         14 | 技術者倫理      |    70 |       200 | 2019-03-12 23:01:58 | 2019-03-12 23:01:58 |
| 53 |         14 | 農業機械        |    89 |       200 | 2019-03-12 23:01:58 | 2019-03-12 23:01:58 |
| 54 |         14 | 植物病理学      |    91 |       200 | 2019-03-12 23:01:58 | 2019-03-12 2
(※以下略)
```

##JOIN :複数テーブル結合
###基本構文
```sql:mysql
SELECT table1.column1, table1.column2
       table2,column1
FROM table1
INNER JOIN table2
     ON table1.primary_key = table2.foreign_key
```

###INNER JOIN と　OUTER JOIN
-INNER JOIN : 内部結合の意。
    -主キー＝外部キーになるところだけ表示
    - 結合できなかったレコードは表示しない

- outer join :外部結合
    - 結合できなかったレコードは、NULLで表示
    - LEFT JOIN :主キー側のテーブルに合わせて表示
    - RIGHT JOIN :外部キー側のテーブルに合わせて表示

###INNER JOINで結合してみる
students生徒テーブルとexam_results試験結果の、主キーと外部キーを使って結合

```sql:mysql
SELECT students.name, students.grade, students.gender, 
       exam_results.name, exam_results.score
FROM students
INNER JOIN exam_results
ON students.id = exam_results.student_id;

# 結果
+--------+-------+--------+-----------------+-------+
| name   | grade | gender | name            | score |
+--------+-------+--------+-----------------+-------+
| taro   |     1 |      0 | 育種学          |    65 |
| taro   |     1 |      0 | 技術者倫理      |    70 |
| taro   |     1 |      0 | 農業機械        |    89 |
| taro   |     1 |      0 | 植物病理学      |    91 |
| taro   |     1 |      0 | 応用昆虫学      |    66 |
| oriver |     3 |      1 | 育種学          |    83 |
| oriver |     3 |      1 | 技術者倫理      |    74 |
| oriver |     3 |      1 | 農業機械        |    56 |
| oriver |     3 |      1 | 植物病理学      |    45 |
| oriver |     3 |      1 | 応用昆虫学      |    72 |
| yuki   |     1 |      1 | 育種学          |    72 |
| yuki   |     1 |      1 | 技術者倫理      |    83 |
| yuki   |     1 |      1 | 農業機械        |    69 |
| yuki   |     1 |      1 | 植物病理学      |    99 |
| yuki   |     1 |      1 | 応用昆虫学      |    69 |
+--------+-------+--------+-----------------+-------+
```
##データ削除
###テーブル情報の削除
```sql:mysql
# DELETE FROM テーブル名 WHERE 条件;
DELETE FROM students;
DELETE FROM exam_results;
```
###テーブルの削除
```sql:mysql
# DROP TABLE テーブル名;
DROP TABLE students;
DROP TABLE exam_results;
```
###データベースの削除
```sql:mysql
# DROP DATABASE データベース名;
DROP DATABASE univ;
```
この続きは、[7日目(1)：MySQLの操作](https://qiita.com/OriverK/items/cb697d8dec5c45b8c7f2)