This post is Private. Only a writer or those who know its URL can access this post.

この授業では以下の内容を勉強します。

  • CREATE文、INSERT文、SELECT文
  • データベースのモデリング
  • テーブル同士の関連性
  • INNER JOINとLEFT JOINの違い
  • 集計関数の使い方
  • CASE WHENの使い方
  • SUM(CASE WHEN)の使い方

モデリングの勉強

  • 生徒とテスト結果の関係を考えてもらう
  • 生徒と学部の関係を考えてもらう
  • 生徒とサークルの関係を考えてもらう

関係性は、このように表す

一人の生徒は、複数のテスト結果を持っています。
一つのテスト結果は、一人の生徒に属しています。

生徒 has_many テスト結果
テスト結果 belongs_to 生徒

一人の生徒は、一つの学科に属しています。
一つの学科にはは、複数の生徒が所属しています。

生徒 belongs_to 学科
学科 has_many 生徒

一人の生徒は、複数のサークルに属しています。
一つのサークルには、複数の生徒が属しています。

生徒 has_many サークル
サークル has_many 生徒

イメージしやすいように、先にGoogle Docsでデータを作成しておこう

データ構造だけで、テーブルを定義してみる!

テーブル名は複数形です!

生徒テーブル

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 DEFAULT CHARSET=utf8;

学部テーブル

CREATE TABLE `majors` (
  `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;

サークルテーブル

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;

テスト結果テーブル

CREATE TABLE `exam_results` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `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 DEFAULT CHARSET=utf8;

上記のテーブルには、関連性がないので、関連性を作成してやります。

テーブル名の単数形_idで関連性を表現する

生徒テーブルに学部id(major_id)を追加

CREATE TABLE `students` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `major_id` int(11) NOT NULL,
  `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 DEFAULT CHARSET=utf8

テスト結果に生徒id(student_id)を追加

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 DEFAULT CHARSET=utf8 

1対多のデータを取得するために、生徒、学部試験結果のデータを生成する

生徒データを作成
major_idに入るidは、学部テーブルのidと合わせる必要があります。

INSERT INTO students (name, major_id , grade, email, age, gender, others, created_at, updated_at) VALUE  ('浦野', 1, 1, 'jo@gmail.com', 20, 1, 'こんにちは、備考です。', now(), now());
INSERT INTO students (name, major_id, grade, email, age, gender, others, created_at, updated_at) VALUE  ('じょじ', 2 , 1, 'jo@gmail.com', 20, 1, 'こんにちは、備考です。', now(), now());
INSERT INTO students (name, major_id, grade, email, age, gender, others, created_at, updated_at) VALUE  ('JOJO', 2 , 2, 'jo@gmail.com', 25, 1, 'こんにちは、備考ですよっと', now(), now());

学部データを生成

INSERT INTO majors (name, created_at, updated_at) VALUE  ('応用', now(), now());
INSERT INTO majors (name, created_at, updated_at) VALUE  ('応用農業化学', now(), now());

試験結果データを生成
student_idに入るidは、生徒テーブルに入るidは、生徒テーブルのidと合わせる必要があります。

INSERT INTO exam_results (name, student_id, score, max_score, created_at, updated_at) VALUE  ('物理', 10, 80, 100, now(), now());
INSERT INTO exam_results (name, student_id, score, max_score, created_at, updated_at) VALUE  ('数学', 10 ,60, 200, now(), now());
INSERT INTO exam_results (name, student_id, score, max_score, created_at, updated_at) VALUE  ('化学', 10 ,50, 100, now(), now());

INSERT INTO exam_results (name, student_id, score, max_score, created_at, updated_at) VALUE  ('物理',11 ,85, 100, now(), now());
INSERT INTO exam_results (name, student_id, score, max_score, created_at, updated_at) VALUE  ('数学',11 ,65, 200, now(), now());
INSERT INTO exam_results (name, student_id, score, max_score, created_at, updated_at) VALUE  ('化学',11, 55, 100, now(), now());

結合してデータを取得してみる

生徒と学部テーブルを結合する

SELECT
    students.name,
    students.age,
    subjects.name
FROM
    students
INNER JOIN subjects
    ON students.major_id = majors.id;

生徒と試験結果テーブルを結合する

SELECT
    students.name,
    students.age,
    exam_results.name,
    subjects.name,
    exam_results.score,
    (exam_results.score / exam_results.max_score) * 100 as ratio
FROM
    students
INNER JOIN exam_results
    ON students.id = exam_results.student_id
INNER JOIN subjects
    ON students.major_id = majors.id;

多対多のデータを取得する練習

サークルデータを生成する

INSERT INTO clubs (name, created_at, updated_at) VALUE  ('サッカー', now(), now());
INSERT INTO clubs (name, created_at, updated_at) VALUE  ('野球', now(), now());
INSERT INTO clubs (name, created_at, updated_at) VALUE  ('囲碁', now(), now());
INSERT INTO clubs (name, created_at, updated_at) VALUE  ('空手', now(), now());
INSERT INTO clubs (name, created_at, updated_at) VALUE  ('合気道', now(), now());

多対多の場合は、中間テーブルを生成する

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 DEFAULT CHARSET=utf8 

中間テーブルにデータを入れる。
student_idとclub_idは実際のデータと合わせて下さい。

INSERT INTO club_students (student_id, club_id, created_at, updated_at) VALUE  (12, 1, now(), now());
INSERT INTO club_students (student_id, club_id, created_at, updated_at) VALUE  (12, 2, now(), now());
INSERT INTO club_students (student_id, club_id, created_at, updated_at) VALUE  (13, 1, now(), now());
INSERT INTO club_students (student_id, club_id, created_at, updated_at) VALUE  (13, 3, now(), now());
INSERT INTO club_students (student_id, club_id, created_at, updated_at) VALUE  (13, 4, now(), now());
INSERT INTO club_students (student_id, club_id, created_at, updated_at) VALUE  (13, 5, now(), now());

生徒が所属しているサークルの一覧を取得する。

何人の生徒を取得できているか?
このSQLで、サークルに所属していない生徒を取得できるか?

SELECT
    students.name,
    students.age,
    clubs.name
FROM
    students
INNER JOIN club_students
    ON students.id = club_students.student_id
INNER JOIN clubs
    ON club_students.club_id = clubs.id;

サークルに所属していない生徒を無所属として表示する。

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;

集計関数の勉強

生徒の名前と、生徒の最高得点、生徒の最少得点、生徒の平均得点を出して下さい。

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;

genderが0で、サークルに所属している部員の数を数えるSQL

SELECT
     COUNT(students.id)
FROM students
LEFT JOIN club_students
    ON students.id = club_students.student_id
LEFT JOIN clubs
    ON club_students.club_id = clubs.id
WHERE students.gender = 0
GROUP BY clubs.name;

genderが0の人とgenderが1の人で、サークルに所属している部員の数を同時に数えるSQL

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
Sign up for free and join this conversation.
Sign Up
If you already have a Qiita account log in.