LoginSignup

This article is a Private article. Only a writer and users who know the URL can access it.
Please change open range to public in publish setting if you want to share this article with other users.

More than 5 years have passed since last update.

@joji(U Joji)

大学生管理アプリの作成5（MySQLにデータを入れよう）

Last updated at Posted at 2019-05-20

MySQLのテーブル定義を作成しよう

UbuntuにMySQLが入ってない人は、こちらのコマンドでインストールして下さい。
この記事も参考になります。

UbuntuでMySQLをインストールします

sudo apt-get update

sudo apt-get install mysql-server

sudo mysql --user=root mysql

mysqlのパスワードを「pass1」に設定します。

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'pass1';

テーブルを作成します

mysqlにログインする

mysql -uroot -ppass1

データベースを作成する

create database cebu_college_1;

データベースを選択

use cebu_college_1;
all
CREATE TABLE `students` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `subject_id` bigint(20) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `gender` int(11) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `opinion` text,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `subjects` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB CHARSET=utf8;

CREATE TABLE `clubs` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `club_students` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `student_id` bigint(20) DEFAULT NULL,
  `club_id` bigint(20) DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `exam_results` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `student_id` bigint(20) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `score` int(11) DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

テーブルができたことを確認します。

show tables;

+--------------------------+
| Tables_in_cebu_college_1 |
+--------------------------+
| club_students |
| clubs |
| exam_results |
| students |
| subjects |
+--------------------------+

INSERT文について

insert文は、INSERT INTO テーブル名(カラム名1, カラム名2) VALUE ('データ1', 'データ2')という感じで書きます。

データを入れます

subjectsにデータを入れます

insert_subjects
INSERT INTO subjects (name, created_at, updated_at) VALUE  ('工学部', now(), now());
INSERT INTO subjects (name, created_at, updated_at) VALUE  ('農学部', now(), now());
INSERT INTO subjects (name, created_at, updated_at) VALUE  ('国際学部', now(), now());

データがあることを確認

SELECT * FROM subjects;

+----+--------------+---------------------+---------------------+
| id | name | created_at | updated_at |
+----+--------------+---------------------+---------------------+
| 1 | 工学部 | 2019-05-20 05:35:52 | 2019-05-20 05:35:52 |
| 2 | 農学部 | 2019-05-20 05:35:52 | 2019-05-20 05:35:52 |
| 3 | 国際学部 | 2019-05-20 05:35:53 | 2019-05-20 05:35:53 |
+----+--------------+---------------------+---------------------+

studentsにデータを入れます

ここで入れるsubject_idの1,2,3は、subjectsテーブルのデータなので、工学部、農学部、国際学部を意味します。

insert_students
INSERT INTO students (subject_id, name,  email, gender, age, opinion, created_at, updated_at) VALUE  (1, '山田1太郎', 'cebu_college+1@gmail.com', 0, 19, 'その他1', now(), now());
INSERT INTO students (subject_id, name,  email, gender, age, opinion, created_at, updated_at) VALUE  (2, '山田2太郎', 'cebu_college+2@gmail.com', 1, 20, 'その他2', now(), now());
INSERT INTO students (subject_id, name,  email, gender, age, opinion, created_at, updated_at) VALUE  (3, '山田3太郎', 'cebu_college+3@gmail.com', 0, 21, 'その他3', now(), now());
INSERT INTO students (subject_id, name,  email, gender, age, opinion, created_at, updated_at) VALUE  (3, '山田4太郎', 'cebu_college+4@gmail.com', 1, 22, 'その他4', now(), now());
INSERT INTO students (subject_id, name,  email, gender, age, opinion, created_at, updated_at) VALUE  (3, '山田5太郎', 'cebu_college+5@gmail.com', 0, 25, 'その他5', now(), now());
INSERT INTO students (subject_id, name,  email, gender, age, opinion, created_at, updated_at) VALUE  (3, '山田6太郎', 'cebu_college+6@gmail.com', 1, 20, 'その他6', now(), now());
INSERT INTO students (subject_id, name,  email, gender, age, opinion, created_at, updated_at) VALUE  (1, '山田7太郎', 'cebu_college+7@gmail.com', 0, 21, 'その他7', now(), now());
INSERT INTO students (subject_id, name,  email, gender, age, opinion, created_at, updated_at) VALUE  (2, '山田8太郎', 'cebu_college+8@gmail.com', 1, 22, 'その他8', now(), now());
INSERT INTO students (subject_id, name,  email, gender, age, opinion, created_at, updated_at) VALUE  (3, '山田9太郎', 'cebu_college+9@gmail.com', 0, 24, 'その他9', now(), now());
INSERT INTO students (subject_id, name,  email, gender, age, opinion, created_at, updated_at) VALUE  (1, '山田10太郎', 'cebu_college+10@gmail.com', 1, 28, 'その他10', now(), now());

データがあることを確認

SELECT * FROM students;

+----+------------+----------------+---------------------------+--------+------+-------------+
| id | subject_id | name | email | gender | age | opinion |
+----+------------+----------------+---------------------------+--------+------+-------------+
| 1 | 1 | 山田1太郎 | cebu_college+1@gmail.com | 0 | 19 | その他1 |
| 2 | 2 | 山田2太郎 | cebu_college+2@gmail.com | 1 | 20 | その他2 |
| 3 | 3 | 山田3太郎 | cebu_college+3@gmail.com | 0 | 21 | その他3 |
| 4 | 3 | 山田4太郎 | cebu_college+4@gmail.com | 1 | 22 | その他4 |
| 5 | 3 | 山田5太郎 | cebu_college+5@gmail.com | 0 | 25 | その他5 |
| 6 | 3 | 山田6太郎 | cebu_college+6@gmail.com | 1 | 20 | その他6 |
| 7 | 1 | 山田7太郎 | cebu_college+7@gmail.com | 0 | 21 | その他7 |
| 8 | 2 | 山田8太郎 | cebu_college+8@gmail.com | 1 | 22 | その他8 |
| 9 | 3 | 山田9太郎 | cebu_college+9@gmail.com | 0 | 24 | その他9 |
| 10 | 1 | 山田10太郎 | cebu_college+10@gmail.com | 1 | 28 | その他10 |
+----+------------+----------------+---------------------------+--------+------+-------------+

exam_resultsにデータを入れます

insert_exam_results
INSERT INTO exam_results (student_id, name,  score, created_at, updated_at) VALUE  (1, '英語', 90, now(), now());
INSERT INTO exam_results (student_id, name,  score, created_at, updated_at) VALUE  (1, '心理学', 80, now(), now());
INSERT INTO exam_results (student_id, name,  score, created_at, updated_at) VALUE  (2, 'フランス語', 88, now(), now());
INSERT INTO exam_results (student_id, name,  score, created_at, updated_at) VALUE  (2, 'ジェンダー学', 80, now(), now());
INSERT INTO exam_results (student_id, name,  score, created_at, updated_at) VALUE  (2, '体育', 60, now(), now());
INSERT INTO exam_results (student_id, name,  score, created_at, updated_at) VALUE  (3, 'ドイツ語', 74, now(), now());
INSERT INTO exam_results (student_id, name,  score, created_at, updated_at) VALUE  (4, '体育', 70, now(), now());
INSERT INTO exam_results (student_id, name,  score, created_at, updated_at) VALUE  (5, '心理学', 35, now(), now());
INSERT INTO exam_results (student_id, name,  score, created_at, updated_at) VALUE  (6, 'ジェンダー学', 98, now(), now());
INSERT INTO exam_results (student_id, name,  score, created_at, updated_at) VALUE  (7, '英語', 98, now(), now());
INSERT INTO exam_results (student_id, name,  score, created_at, updated_at) VALUE  (8, 'フランス語', 75, now(), now());
INSERT INTO exam_results (student_id, name,  score, created_at, updated_at) VALUE  (9, 'ドイツ語', 85, now(), now());
INSERT INTO exam_results (student_id, name,  score, created_at, updated_at) VALUE  (10, '体育', 100, now(), now());
INSERT INTO exam_results (student_id, name,  score, created_at, updated_at) VALUE  (10, 'フランス語', 32, now(), now());
INSERT INTO exam_results (student_id, name,  score, created_at, updated_at) VALUE  (10, 'ドイツ語', 41, now(), now());

データがあることを確認

SELECT id, student_id, name, score FROM exam_results;

+----+------------+--------------------+-------+
| id | student_id | name | score |
+----+------------+--------------------+-------+
| 1 | 1 | 英語 | 90 |
| 2 | 1 | 心理学 | 80 |
| 3 | 2 | フランス語 | 88 |
| 4 | 2 | ジェンダー学 | 80 |
| 5 | 2 | 体育 | 60 |
| 6 | 3 | ドイツ語 | 74 |
| 7 | 4 | 体育 | 70 |
| 8 | 5 | 心理学 | 35 |
| 9 | 6 | ジェンダー学 | 98 |
| 10 | 7 | 英語 | 98 |
| 11 | 8 | フランス語 | 75 |
| 12 | 9 | ドイツ語 | 85 |
| 13 | 10 | 体育 | 100 |
| 14 | 10 | フランス語 | 32 |
| 15 | 10 | ドイツ語 | 41 |
+----+------------+--------------------+-------+

clubsにデータを入れます。

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());
INSERT INTO clubs (name, created_at, updated_at) VALUE  ('将棋', now(), now());

データがあることを確認

SELECT id, name FROM clubs;

+----+--------------+
| id | name |
+----+--------------+
| 1 | サッカー |
| 2 | 囲碁 |
| 3 | 野球 |
| 4 | 柔道 |
| 5 | 空手 |
| 6 | 将棋 |
+----+--------------+

studentsとclubsのデータを繋げるためにclub_studentsにデータを入れます。

INSERT INTO club_students (student_id, club_id, created_at, updated_at) VALUE  (1, 1, now(), now());
INSERT INTO club_students (student_id, club_id, created_at, updated_at) VALUE  (1, 2, now(), now());
INSERT INTO club_students (student_id, club_id, created_at, updated_at) VALUE  (2, 3, now(), now());
INSERT INTO club_students (student_id, club_id, created_at, updated_at) VALUE  (3, 4, now(), now());
INSERT INTO club_students (student_id, club_id, created_at, updated_at) VALUE  (3, 5, now(), now());
INSERT INTO club_students (student_id, club_id, created_at, updated_at) VALUE  (6, 6, now(), now());
INSERT INTO club_students (student_id, club_id, created_at, updated_at) VALUE  (6, 1, now(), now());
INSERT INTO club_students (student_id, club_id, created_at, updated_at) VALUE  (7, 1, now(), now());
INSERT INTO club_students (student_id, club_id, created_at, updated_at) VALUE  (8, 3, now(), now());
INSERT INTO club_students (student_id, club_id, created_at, updated_at) VALUE  (9, 5, now(), now());
INSERT INTO club_students (student_id, club_id, created_at, updated_at) VALUE  (10, 4, now(), now());
INSERT INTO club_students (student_id, club_id, created_at, updated_at) VALUE  (10, 5, now(), now());
INSERT INTO club_students (student_id, club_id, created_at, updated_at) VALUE  (10, 2, now(), now());

データがあることを確認

SELECT 
  students.name,
  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
ORDER BY students.id ASC;

+----------------+--------------+
| name | name |
+----------------+--------------+
| 山田1太郎 | サッカー |
| 山田1太郎 | 囲碁 |
| 山田2太郎 | 野球 |
| 山田3太郎 | 柔道 |
| 山田3太郎 | 空手 |
| 山田6太郎 | 将棋 |
| 山田6太郎 | サッカー |
| 山田7太郎 | サッカー |
| 山田8太郎 | 野球 |
| 山田9太郎 | 空手 |
| 山田10太郎 | 柔道 |
| 山田10太郎 | 空手 |
| 山田10太郎 | 囲碁 |
+----------------+--------------+

大学生管理アプリの作成6

0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin