2
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

6日目:MySQL操作

Last updated at Posted at 2019-03-12

今日の授業は、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
##データベース作成

mysql
# 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の略

mysql
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テーブルも作成

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

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 :カラム変更

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

##INSERT INTO :データ追加

mysql
# 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点満点に変更してみる

mysql
# 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 :複数テーブル結合
###基本構文

mysql
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試験結果の、主キーと外部キーを使って結合

mysql
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 |
+--------+-------+--------+-----------------+-------+

##データ削除
###テーブル情報の削除

mysql
# DELETE FROM テーブル名 WHERE 条件;
DELETE FROM students;
DELETE FROM exam_results;

###テーブルの削除

mysql
# DROP TABLE テーブル名;
DROP TABLE students;
DROP TABLE exam_results;

###データベースの削除

mysql
# DROP DATABASE データベース名;
DROP DATABASE univ;

この続きは、7日目(1):MySQLの操作

2
0
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
2
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?