Day 6, 7： MySQL 操作

2019年12月4日 /

#cebu #mysql

from Qiita:
- 6日目：MySQL操作
- 7日目(1)：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

中間テーブル

生徒ID	部活ID
1	1
1	3
1	4

実際に 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

データベース作成

1
CREATE DATABASE データベース名;
2
CREATE DATABASE univ;
3
CREATE TABLE
4
-- テーブル名は複数形で。

1
-- CREATE TABLE `students`(`カラム名`　データ型　その他指定, `カラム名`　データ型　その他指定, ...);
2
CREATE TABLE `students`(
3
`id` INT NOT NULL PRIMARY KEY  AUTO_INCREMENT,
4
`name` VARCHAR(255) NOT NULL,
5
`grade` INT(128) NOT NULL,
6
`email` VARCHAR(255) NOT NULL,
7
`age` INT(128) NOT NULL,
8
`gender` INT(128) NOT NULL,
9
`others` TEXT,
10
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
11
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
12
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

バッククォートとシングルクォート

バッククォート：テーブル名に使う
シングルクォート：データの文字列に使う

ただし、バッククォートは必須でない バッククォートよって、テーブル名やカラム名で使用できないように指定されている予約語（Reserved Words）を使えるようにできる。が、使わない方が良い。

参照：phpMyAdminのSQLで付加される「‘」に似た記号「`」は何？

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 リファレンスマニュアル / データ型

DESC :テーブル情報の確認(Describeの略

1
DESC students;
2
-- =>
3
-- +------------+--------------+------+-----+-------------------+----------------+
4
-- | Field      | Type         | Null | Key | Default           | Extra          |
5
-- +------------+--------------+------+-----+-------------------+----------------+
6
-- | id         | int(11)      | NO   | PRI | NULL              | auto_increment |
7
-- | name       | varchar(255) | NO   |     | NULL              |                |
8
-- | grade      | int(128)     | NO   |     | NULL              |                |
9
-- | email      | varchar(255) | NO   |     | NULL              |                |
10
-- | age        | int(128)     | NO   |     | NULL              |                |
11
-- | gender     | int(128)     | NO   |     | NULL              |                |
12
-- | others     | text         | YES  |     | NULL              |                |
13
-- | created_at | timestamp    | NO   |     | CURRENT_TIMESTAMP |                |
14
-- | updated_at | timestamp    | NO   |     | CURRENT_TIMESTAMP |                |
15
-- +------------+--------------+------+-----+-------------------+----------------+

同様に、exam_results テーブルも作成

1
CREATE TABLE `exam_results` (
2
  `id` int(11) NOT NULL AUTO_INCREMENT,
3
　`student_id` int(11) NOT NULL,
4
  `name` varchar(255) NOT NULL,
5
  `score` int(128) NOT NULL,
6
  `max_score` int(128) NOT NULL,
7
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
8
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
9
  PRIMARY KEY (`id`)
10
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8
11

12
DESC subjects;
13

14
-- 結果
15
DESC exam_results;
16
-- +------------+--------------+------+-----+-------------------+----------------+
17
-- | Field      | Type         | Null | Key | Default           | Extra          |
18
-- +------------+--------------+------+-----+-------------------+----------------+
19
-- | id         | int(11)      | NO   | PRI | NULL              | auto_increment |
20
-- | student_id | int(11)      | NO   |     | NULL              |                |
21
-- | name       | varchar(255) | NO   |     | NULL              |                |
22
-- | score      | int(128)     | NO   |     | NULL              |                |
23
-- | max_score  | int(128)     | NO   |     | NULL              |                |
24
-- | created_at | timestamp    | NO   |     | CURRENT_TIMESTAMP |                |
25
-- | updated_at | timestamp    | NO   |     | CURRENT_TIMESTAMP |                |
26
-- +------------+--------------+------+-----+-------------------+----------------+

ALTER TABLE :カラム変更

1
ALTER TABLE テーブル名 ADD COLUMN カラム名 カラム定義,
2
                      DROP COLUMN カラム名,
3
                      CHANGE COLUMN 変えるカラム名 新しいカラム名 カラム定義,
4
                      MODIFY COLUMN カラム名 新しいカラム定義;

INSERT INTO :データ追加

1
-- INSERT INTO テーブル名 (カラム1, カラム2, カラム3, ...)
2
VALUE (カラム1データ, カラム2データ, カラム3データ, ...),(カラム1データ, ........);
3

4
-- students生徒テーブル
5
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, '[email protected]', 20, 1, 'こんにちは、備考です。', now(), now());
6

7
-- SELECT * FROM tables;で見る
8
-- +----+--------+-------+--------------+-----+--------+-----------------------------------+---------------------+---------------------+
9
-- | id | name   | grade | email        | age | gender | others                            | created_at          | updated_at          |
10
-- +----+--------+-------+--------------+-----+--------+-----------------------------------+---------------------+---------------------+
11
-- | 14 | taro   |     1 | taro@email   |  23 |      0 | nothing_special                   | 2019-03-12 22:58:04 | 2019-03-12 22:58:04 |
12
-- | 15 | oriver |     3 | oriver@email |  25 |      1 | 別に                              | 2019-03-12 22:58:04 | 2019-03-12 22:58:04 |
13
-- | 16 | yuki   |     1 | [email protected] |  20 |      1 | こんにちは、備考です。            | 2019-03-12 22:58:04 | 2019-03-12 22:58:04 |
14
-- ...

1
-- exam_results試験結果テーブル
2
INSERT INTO exam_results (student_id,name, score, max_score, created_at, updated_at ) value
3
(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()),
4
(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()),
5
(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());
6

7
-- 結果
8
-- +----+------------+-----------------+-------+-----------+---------------------+---------------------+
9
-- | id | student_id | name            | score | max_score | created_at          | updated_at          |
10
-- +----+------------+-----------------+-------+-----------+---------------------+---------------------+
11
-- | 51 |         14 | 育種学          |    65 |       100 | 2019-03-12 23:01:58 | 2019-03-12 23:01:58 |
12
-- | 52 |         14 | 技術者倫理      |    70 |       100 | 2019-03-12 23:01:58 | 2019-03-12 23:01:58 |
13
-- | 53 |         14 | 農業機械        |    89 |       100 | 2019-03-12 23:01:58 | 2019-03-12 23:01:58 |
14
-- | 54 |         14 | 植物病理学      |    91 |       100 | 2019-03-12 23:01:58 | 2019-03-12 23:01:58 |
15
-- ...

UPDATE SET WHERE:テーブル情報の更新

今の時点では、max_score が 100 点だが、200 点満点に変更してみる

1
-- UPDATE テーブル名 SET カラム名 = 新しい情報 WHERE 条件
2
UPDATE exam_results SET max_score = 200;
3
-- =>
4
-- +----+------------+-----------------+-------+-----------+---------------------+---------------------+
5
-- | id | student_id | name            | score | max_score | created_at          | updated_at          |
6
-- +----+------------+-----------------+-------+-----------+---------------------+---------------------+
7
-- | 51 |         14 | 育種学          |    65 |       200 | 2019-03-12 23:01:58 | 2019-03-12 23:01:58 |
8
-- | 52 |         14 | 技術者倫理      |    70 |       200 | 2019-03-12 23:01:58 | 2019-03-12 23:01:58 |
9
-- | 53 |         14 | 農業機械        |    89 |       200 | 2019-03-12 23:01:58 | 2019-03-12 23:01:58 |
10
-- | 54 |         14 | 植物病理学      |    91 |       200 | 2019-03-12 23:01:58 | 2019-03-12 2
11
--  ...

JOIN :複数テーブル結合

基本構文

1
SELECT table1.column1, table1.column2
2
       table2,column1
3
FROM table1
4
INNER JOIN table2
5
     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 試験結果の、主キーと外部キーを使って結合

1
SELECT students.name, students.grade, students.gender,
2
       exam_results.name, exam_results.score
3
FROM students
4
INNER JOIN exam_results
5
ON students.id = exam_results.student_id;
6

7
-- =>
8
-- +--------+-------+--------+-----------------+-------+
9
-- | name   | grade | gender | name            | score |
10
-- +--------+-------+--------+-----------------+-------+
11
-- | taro   |     1 |      0 | 育種学          |    65 |
12
-- | taro   |     1 |      0 | 技術者倫理      |    70 |
13
-- | taro   |     1 |      0 | 農業機械        |    89 |
14
-- ...

データ削除

テーブル情報の削除

1
-- DELETE FROM テーブル名 WHERE 条件;
2
DELETE FROM students;
3
DELETE FROM exam_results;

テーブルの削除

1
-- DROP TABLE テーブル名;
2
DROP TABLE students;
3
DROP TABLE exam_results;

データベースの削除

1
-- DROP DATABASE データベース名;
2
DROP DATABASE univ;

7日目

データベースとテーブル作成、データ追加

データベース作成a

1
CREATE DATABASE univ1;

テーブル作成

1
USE univ1_development;
2
-- 生徒テーブル作成
3
CREATE TABLE `students` (
4
  `id` int(11) NOT NULL AUTO_INCREMENT,
5
  `name` varchar(255) NOT NULL,
6
  `grade` int(128) NOT NULL,
7
  `email` varchar(255) NOT NULL,
8
  `age` int(128) NOT NULL,
9
  `gender` int(128) NOT NULL,
10
  `others` text,
11
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
12
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
13
  PRIMARY KEY (`id`)
14
)
15

16
-- 生徒テーブルにデータ追加
17
INSERT INTO students (name, grade, email, age, gender, others, created_at, updated_at) VALUE ('じょじ',1, '[email protected]', 20, 0, 'こんにちは、備考です。', now(), now()),('yuka',2, 'yuka@email', 22, 1,'特に',now(),now()),('なつこ',1, 'natsu@email', 28, 1,'夏来たれ',now(),now()), ('おりば',3,'oriver@email',23,0,'nothing',now(),now()),('masaya',2,'masaya@email',20,0,'nothing',now(),now());

1
-- 試験結果テーブル作成
2
CREATE TABLE `exam_results` (
3
  `id` int(11) NOT NULL AUTO_INCREMENT,
4
  `student_id` int(11) NOT NULL,
5
  `name` varchar(255) NOT NULL,
6
  `score` int(128) NOT NULL,
7
  `max_score` int(128) NOT NULL,
8
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
9
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
10
  PRIMARY KEY (`id`)
11
)
12

13
-- 試験結果テーブルにデータ追加
14
INSERT INTO exam_results (name, student_id, score, max_score, created_at, updated_at) VALUE  ('物理', 12, 85, 100, now(), now()), ('数学', 12 ,64, 100, now(), now()), ('化学', 12 ,55, 100, now(), now()),('物理',13 ,85, 100, now(), now()), ('数学',13 ,65, 100, now(), now()), ('化学',13, 71, 100, now(), now()),('物理',14 ,88, 100, now(), now()), ('数学',14 ,73, 100, now(), now()), ('化学',14, 67, 100, now(), now()),('物理',15 ,100, 100, now(), now()), ('数学',15 ,92, 100, now(), now()), ('化学',15, 99, 100, now(), now()),('物理',16 ,30, 100, now(), now()), ('数学',16 ,43, 100, now(), now()), ('化学',16, 99, 100, now(), now());

1
-- 部活テーブル作成
2
CREATE TABLE `clubs` (
3
  `id` int(11) NOT NULL AUTO_INCREMENT,
4
  `name` varchar(255) NOT NULL,
5
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
6
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
7
  PRIMARY KEY (`id`)
8
)
9

10
-- 部活テーブルにデータ追加
11
INSERT INTO clubs (name, created_at, updated_at) VALUE
12
('サッカー', now(), now()),('野球', now(), now()),('囲碁', now(), now()),('空手', now(), now()),('合気道', now(), now()),('自転車',now(),now()),('軽音',now(),now());

1
-- 生徒ー部活関連テーブル作成
2
CREATE TABLE `club_students` (
3
  `id` int(11) NOT NULL AUTO_INCREMENT,
4
  `student_id` int(11) NOT NULL,
5
  `club_id` int(11) NOT NULL,
6
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
7
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
8
  PRIMARY KEY (`id`)
9
)
10

11
-- 生徒ー部活関連テーブルへデータ追加
12
INSERT INTO club_students (student_id, club_id, created_at, updated_at) VALUE  (12, 1, now(), now()),(12, 3, now(), now()),(12, 5, now(), now()),(12, 7, now(), now());
13
INSERT INTO club_students (student_id, club_id, created_at, updated_at) VALUE  (13, 2, now(), now()),(13, 4, now(), now()),(13, 6, now(), now());
14
INSERT INTO club_students (student_id, club_id, created_at, updated_at) VALUE  (14, 1, now(), now()),(14, 3, now(), now()),(14, 4, now(), now()),(14, 5, now(), now());
15
INSERT INTO club_students (student_id, club_id, created_at, updated_at) VALUE
16
(15, 4, now(), now()),(15, 5, now(), now()),(15 ,6, now(), now()),(15, 4, now(), now());

本題：MySQL操作

scoreの最高、最小、平均、(score / max_score)の最大値

1
SELECT MAX(score),MIN(score),AVG(score),MAX(score/max_score)
2
FROM exam_results;
3
-- 結果
4
-- +------------+------------+------------+----------------------+
5
-- | MAX(score) | MIN(score) | AVG(score) | MAX(score/max_score) |
6
-- +------------+------------+------------+----------------------+
7
-- |        100 |         30 |    74.4000 |               1.0000 |

GROUP BY

科目ごとの最大値、最小値、平均値を求め、名前を最大、最小、平均に変更

1
SELECT name,
2
MAX(score) as 最大 ,
3
 MIN(score) as 最小,
4
AVG(score) as 平均
5
FROM exam_results
6
GROUP BY name;
7
-- 結果
8
-- +--------+--------+--------+---------+
9
-- | name   | 最大   | 最小   | 平均    |
10
-- +--------+--------+--------+---------+
11
-- | 化学   |     99 |     55 | 78.2000 |
12
-- | 数学   |     92 |     43 | 67.4000 |
13
-- | 物理   |    100 |     30 | 77.6000 |

INNER JOIN

students と exam_results を結合し、生徒ごとの、最高得点、最少得点、平均得点を出力

1
SELECT students.name, MAX(score), MIN(score), AVG(score)
2
FROM students
3
INNER JOIN exam_results
4
ON students.id = exam_results.student_id
5
GROUP BY students.name;
6
-- 結果
7
-- +-----------+------------+------------+------------+
8
-- | name      | MAX(score) | MIN(score) | AVG(score) |
9
-- +-----------+------------+------------+------------+
10
-- | masaya    |         99 |         30 |    57.3333 |
11
-- | yuka      |         85 |         65 |    73.6667 |
12
-- | おりば    |        100 |         92 |    97.0000

CASE WHEN 条件分岐

NULLがあったら、出力上の表記を変える

1
SELECT
2
    students.name,
3
    students.age,
4
    CASE WHEN clubs.name IS NOT NULL THEN clubs.name
5
　　ELSE '無所属' end as club_name
6
FROM
7
    students
8
LEFT JOIN club_students
9
    ON students.id = club_students.student_id
10
LEFT JOIN clubs
11
    ON club_students.club_id = clubs.id;
12
-- =>
13
-- +-----------+-----+--------------+
14
-- | name      | age | club_name    |
15
-- +-----------+-----+--------------+
16
-- | じょじ    |  20 | サッカー     |
17
-- | じょじ    |  20 | 囲碁         |
18
-- | じょじ    |  20 | 合気道       |
19
-- ...
20
-- INNER JOINの場合、masayaのレコード自体が表示されない。

部活ごとに、男性と女性それぞれ何人所属しているか、出力せよ

1
SELECT
2
    clubs.name,
3
    SUM(CASE WHEN students.gender = 0 THEN 1 ELSE 0 END) as male,
4
    SUM(CASE WHEN students.gender = 1 THEN 1 ELSE 0 END) as female
5
FROM
6
    clubs
7
LEFT JOIN club_students
8
    ON clubs.id = club_students.club_id
9
LEFT JOIN students
10
    ON club_students.student_id = students.id
11
GROUP BY clubs.name