今日の授業は、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つの部活には複数の学生が所属。
当然、まったく所属していない学生も居る可能性がある
生徒 | 部活1 | 部活2 | 部活3 | 部活4 |
---|---|---|---|---|
Aさん | 自転車部 | 軽音部 | 水泳部 | バスケ部 |
Bさん | ||||
Cさん | 茶道部 | 華道部 | 水泳部 | |
Dさん | 茶道部 | 軽音部 |
###多対多テーブルの問題
DB上では1つのカラムに複数のデータは入れることが出来ない。
上の表では、同義のカラム(部活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
##データベース作成
# 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に付加される「'」に似た記号「`」は何?
###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')
- 入力するデータ量に従って、最適なデータ型を選んだ方が、色々と良いようだ。
##DESC :テーブル情報の確認(Describeの略
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テーブルも作成
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 :カラム変更
ALTER TABLE テーブル名 ADD COLUMN カラム名 カラム定義,
DROP COLUMN カラム名,
CHANGE COLUMN 変えるカラム名 新しいカラム名 カラム定義,
MODIFY COLUMN カラム名 新しいカラム定義;
##INSERT INTO :データ追加
# 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 |
+----+--------+-------+--------------+-----+--------+-----------------------------------+-
: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点満点に変更してみる
# 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 :複数テーブル結合
###基本構文
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試験結果の、主キーと外部キーを使って結合
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 |
+--------+-------+--------+-----------------+-------+
##データ削除
###テーブル情報の削除
# DELETE FROM テーブル名 WHERE 条件;
DELETE FROM students;
DELETE FROM exam_results;
###テーブルの削除
# DROP TABLE テーブル名;
DROP TABLE students;
DROP TABLE exam_results;
###データベースの削除
# DROP DATABASE データベース名;
DROP DATABASE univ;
この続きは、7日目(1):MySQLの操作