freedom-_-qの勉強履歴

メモ書きが主になるかと思います。勉強強制のために一日一記事目指してます。頭良くないので間違いが多々あるかと思います。

MySQLチュートリアル（結合編）

MySQL Ubuntu

対象テーブルの準備

MySQLチュートリアル（取得編）で作成したuserテーブルを使う。
新たに2つのテーブルを作成する。

  • 本を定義するbooksテーブル
  • 借りたものを表すborrowedテーブル
mysql> CREATE TABLE books(id INT(4) ZEROFILL AUTO_INCREMENT NOT NULL, 
    -> name VARCHAR(30) NOT NULL,
    -> PRIMARY KEY (id));
Query OK, 0 rows affected, 2 warnings (0.02 sec)

Warning (Code 1681): The ZEROFILL attribute is deprecated and will be removed in a future release. Use the LPAD function to zero-pad numbers, or store the formatted numbers in a CHAR column.
Warning (Code 1681): Integer display width is deprecated and will be removed in a future release.
mysql> INSERT INTO books (name) VALUES('ABC');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO books (name) VALUES('DEF');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO books (name) VALUES('GHI');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO books (name) VALUES('JKL');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO books (name) VALUES('MNO');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM books;
+------+------+
| id   | name |
+------+------+
| 0001 | ABC  |
| 0002 | DEF  |
| 0003 | GHI  |
| 0004 | JKL  |
| 0005 | MNO  |
+------+------+
5 rows in set (0.00 sec)


mysql> CREATE TABLE borrowed(id INT(2) ZEROFILL AUTO_INCREMENT NOT NULL,
   -> user_id INT(3) ZEROFILL NOT NULL,
   -> book_id INT(4) ZEROFILL NOT NULL,
   -> PRIMARY KEY (id));
Query OK, 0 rows affected, 6 warnings (0.02 sec)

Warning (Code 1681): The ZEROFILL attribute is deprecated and will be removed in a future release. Use the LPAD function to zero-pad numbers, or store the formatted numbers in a CHAR column.
Warning (Code 1681): Integer display width is deprecated and will be removed in a future release.
Warning (Code 1681): The ZEROFILL attribute is deprecated and will be removed in a future release. Use the LPAD function to zero-pad numbers, or store the formatted numbers in a CHAR column.
Warning (Code 1681): Integer display width is deprecated and will be removed in a future release.
Warning (Code 1681): The ZEROFILL attribute is deprecated and will be removed in a future release. Use the LPAD function to zero-pad numbers, or store the formatted numbers in a CHAR column.
Warning (Code 1681): Integer display width is deprecated and will be removed in a future release.
mysql> INSERT INTO borrowed (user_id, book_id) VALUES(1, 1);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO borrowed (user_id, book_id) VALUES(1, 2);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO borrowed (user_id, book_id) VALUES(1, 3);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO borrowed (user_id, book_id) VALUES(3, 4);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO borrowed (user_id, book_id) VALUES(3, 5);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM borrowed;
+----+---------+---------+
| id | user_id | book_id |
+----+---------+---------+
| 01 |     001 |    0001 |
| 02 |     001 |    0002 |
| 03 |     001 |    0003 |
| 04 |     003 |    0004 |
| 05 |     003 |    0005 |
+----+---------+---------+
5 rows in set (0.00 sec)

mysql> SHOW TABLES;
+-------------------------+
| Tables_in_test_database |
+-------------------------+
| books                   |
| borrowed                |
| users                   |
+-------------------------+
3 rows in set (0.00 sec)

Warning出まくっているけど練習だから無視無視！

結合

結合方法には

  • INNER
  • OUTER
  • LEFT
  • RIGHT

があるが、自分はpandasで使ったことあるため説明は省く。
まずは、「本を借りている人名(+id)」と「その人が借りている本名」のみ表示する。

mysql> SELECT users.id AS user_id,
   -> users.name AS user_name,
   -> books.name AS book_name
   -> FROM borrowed
   -> INNER JOIN books
   -> ON borrowed.book_id = books.id
   -> INNER JOIN users
   -> ON users.id = borrowed.user_id;
+---------+--------------+-----------+
| user_id | user_name    | book_name |
+---------+--------------+-----------+
|     001 | Yamada Tarou | ABC       |
|     001 | Yamada Tarou | DEF       |
|     001 | Yamada Tarou | GHI       |
|     003 | Yamada Jirou | JKL       |
|     003 | Yamada Jirou | MNO       |
+---------+--------------+-----------+

そのまま結合すると、以下のようにカラム名が被ってしまうので、ASで別名をつけているのがミソ。

mysql> SELECT *
   -> FROM borrowed
   -> INNER JOIN books
   -> ON borrowed.book_id = books.id
   -> INNER JOIN users
   -> ON users.id = borrowed.user_id;
+----+---------+---------+------+------+-----+--------------+------+------------+
| id | user_id | book_id | id   | name | id  | name         | sex  | birth      |
+----+---------+---------+------+------+-----+--------------+------+------------+
| 01 |     001 |    0001 | 0001 | ABC  | 001 | Yamada Tarou | M    | 2000-01-01 |
| 02 |     001 |    0002 | 0002 | DEF  | 001 | Yamada Tarou | M    | 2000-01-01 |
| 03 |     001 |    0003 | 0003 | GHI  | 001 | Yamada Tarou | M    | 2000-01-01 |
| 04 |     003 |    0004 | 0004 | JKL  | 003 | Yamada Jirou | M    | 2000-03-03 |
| 05 |     003 |    0005 | 0005 | MNO  | 003 | Yamada Jirou | M    | 2000-03-03 |
+----+---------+---------+------+------+-----+--------------+------+------------+

…今日はもう遅いから明日更新しますぅ...