6日目に引き続き、MySQLの操作を行う。
関連テーブル使用は、目次のCASE WHENの項以降
#データベースとテーブル作成、データ追加
##データベース作成
mysql
CREATE DATABASE univ1;
##テーブル作成
mysql
USE univ1_development;
# 生徒テーブル作成
CREATE TABLE `students` (
`id` int(11) NOT NULL 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,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;
# 生徒テーブルにデータ追加
INSERT INTO students (name, grade, email, age, gender, others, created_at, updated_at) VALUE ('じょじ',1, 'jo@gmail.com', 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());
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;
# 試験結果テーブルにデータ追加
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());
mysql
# 部活テーブル作成
CREATE TABLE `clubs` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
# 部活テーブルにデータ追加
INSERT INTO clubs (name, created_at, updated_at) VALUE
('サッカー', now(), now()),('野球', now(), now()),('囲碁', now(), now()),('空手', now(), now()),('合気道', now(), now()),('自転車',now(),now()),('軽音',now(),now());
mysql
# 生徒ー部活関連テーブル作成
CREATE TABLE `club_students` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`student_id` int(11) NOT NULL,
`club_id` int(11) 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;
# 生徒ー部活関連テーブルへデータ追加
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());
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());
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());
INSERT INTO club_students (student_id, club_id, created_at, updated_at) VALUE
(15, 4, now(), now()),(15, 5, now(), now()),(15 ,6, now(), now()),(15, 4, now(), now());
#本題：MySQL操作
scoreの最高、最小、平均、(score / max_score)の最大値
mysql
SELECT MAX(score),MIN(score),AVG(score),MAX(score/max_score)
FROM exam_results;
# 結果
+------------+------------+------------+----------------------+
| MAX(score) | MIN(score) | AVG(score) | MAX(score/max_score) |
+------------+------------+------------+----------------------+
| 100 | 30 | 74.4000 | 1.0000 |
+------------+------------+------------+----------------------+
GROUP BY : 科目毎の最大値、最小値、平均値を求め、名前を最大、最小、平均に変更
mysql
SELECT name,
MAX(score) as 最大 ,
MIN(score) as 最小,
AVG(score) as 平均
FROM exam_results
GROUP BY name;
# 結果
+--------+--------+--------+---------+
| name | 最大 | 最小 | 平均 |
+--------+--------+--------+---------+
| 化学 | 99 | 55 | 78.2000 |
| 数学 | 92 | 43 | 67.4000 |
| 物理 | 100 | 30 | 77.6000 |
+--------+--------+--------+---------+
##INNER JOIN
studentsとexam_resultsを結合し、生徒毎の、最高得点、最少得点、平均得点を出力
mysql
SELECT students.name, MAX(score), MIN(score), AVG(score)
FROM students
INNER JOIN exam_results
ON students.id = exam_results.student_id
GROUP BY students.name;
# 結果
+-----------+------------+------------+------------+
| name | MAX(score) | MIN(score) | AVG(score) |
+-----------+------------+------------+------------+
| masaya | 99 | 30 | 57.3333 |
| yuka | 85 | 65 | 73.6667 |
| おりば | 100 | 92 | 97.0000 |
| じょじ | 85 | 55 | 68.0000 |
| なつこ | 88 | 67 | 76.0000 |
+-----------+------------+------------+------------+
##CASE WHEN 条件分岐
###NULLがあったら、出力上の表記を変える
mysql
SELECT
students.name,
students.age,
CASE WHEN clubs.name IS NOT NULL THEN clubs.name
ELSE '無所属' end as club_name
FROM
students
LEFT JOIN club_students
ON students.id = club_students.student_id
LEFT JOIN clubs
ON club_students.club_id = clubs.id;
# 結果
+-----------+-----+--------------+
| name | age | club_name |
+-----------+-----+--------------+
| じょじ | 20 | サッカー |
| じょじ | 20 | 囲碁 |
| じょじ | 20 | 合気道 |
| じょじ | 20 | 軽音 |
| yuka | 22 | 野球 |
| yuka | 22 | 空手 |
| yuka | 22 | 自転車 |
| じょじ | 20 | サッカー |
| なつこ | 28 | 囲碁 |
| なつこ | 28 | 空手 |
| なつこ | 28 | 合気道 |
| おりば | 23 | 空手 |
| おりば | 23 | 合気道 |
| おりば | 23 | 自転車 |
| masaya | 20 | 無所属 |
+-----------+-----+--------------+
# INNER JOINの場合、masayaのレコード自体が表示されない。
#宿題：部活ごとに、男性と女性それぞれ何人所属しているか、出力せよ
下の様に、出力しないといけない。
##解答
()の中に、CASE WHEN THENを入れられるとは
mysql
SELECT
clubs.name,
SUM(CASE WHEN students.gender = 0 THEN 1 ELSE 0 END) as male,
SUM(CASE WHEN students.gender = 1 THEN 1 ELSE 0 END) as female
FROM
clubs
LEFT JOIN club_students
ON clubs.id = club_students.club_id
LEFT JOIN students
ON club_students.student_id = students.id
GROUP BY clubs.name