学習12日目：MySQLを使って色々してみた。

2020 7/22

2020.07.22 2020.08.17

今日の学習時間。

Day：12
Today：6h
Total：84h

学習内容について。

条件を絞り込む「WHERE句」。

// INSERT

mysql> INSERT INTO books (title, price) VALUES ("はじめてのMySQL", 2980);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO books (title, price) VALUES ("はじめてのプログラミング", 1980);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO books (title, price) VALUES ("はじめてのHTML", 1000);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO books (title, price) VALUES ("はじめてのCSS", 1000);
Query OK, 1 row affected (0.00 sec)


// SELECTのWHERE句

mysql> SELECT * FROM books WHERE price > 1500;
+----+--------------------------------------+-------+---------------------+
| id | title                                | price | created_at          |
+----+--------------------------------------+-------+---------------------+
|  4 | はじめてのMySQL                   　   |  2980 | 2020-07-22 08:02:46 |
|  5 | はじめてのプログラミング                 |  1980 | 2020-07-22 08:02:57 |
+----+--------------------------------------+-------+---------------------+
2 rows in set (0.00 sec)


//UPDATEのWHERE句

UPDATE books SET price = 1200 WHERE price = 1000;
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2  Changed: 2  Warnings: 0


//確認

mysql> SELECT * FROM books;
+----+--------------------------------------+-------+---------------------+
| id | title                                | price | created_at          |
+----+--------------------------------------+-------+---------------------+
|  4 | はじめてのMySQL                  　    |  2980 | 2020-07-22 08:02:46 |
|  5 | はじめてのプログラミング                 |  1980 | 2020-07-22 08:02:57 |
|  6 | はじめてのHTML               　        |  1200 | 2020-07-22 08:03:09 |
|  7 | はじめてのCSS　　　　　　　　　　　　　    |  1200 | 2020-07-22 08:03:17 |
+----+--------------------------------------+-------+---------------------+
4 rows in set (0.00 sec)


//DELETEのWHERE句

mysql> DELETE FROM books WHERE title = "はじめてのCSS";
Query OK, 1 row affected (0.00 sec)


//確認

mysql> SELECT * FROM books;
+----+--------------------------------------+-------+---------------------+
| id | title                                | price | created_at          |
+----+--------------------------------------+-------+---------------------+
|  4 | はじめてのMySQL　　　　　         　　   |  2980 | 2020-07-22 08:02:46 |
|  5 | はじめてのプログラミング　         　　   |  1980 | 2020-07-22 08:02:57 |
|  6 | はじめてのHTML　　　　　         　　    |  1200 | 2020-07-22 08:03:09 |
+----+--------------------------------------+-------+---------------------+
3 rows in set (0.00 sec)

※INSERT文にはWHERE句はない

a = b	aとbが等しい
a != b	aとbが等しくない
a < b	aがbより小さい
a <= b	aがb以下
a > b	aがbより大きい
a >= b	aがb以上
a IS NULL	aがNULL
a IS NOT NULL	aがNULLでない
a BETWEEN b1 AND b2	aがb1とb2の間
a IN (b1, b2, …)	aが括弧内の値（b1, b2, …）のいずれかと等しい

WHERE句で使える比較演算子

順番を決める「ORDER BY句」。

// 小さい順に並べる

mysql> SELECT * FROM books ORDER BY price;
+----+--------------------------------------+-------+---------------------+
| id | title                                | price | created_at          |
+----+--------------------------------------+-------+---------------------+
|  6 | はじめてのHTML                　       |  1200 | 2020-07-22 08:03:09 |
|  5 | はじめてのプログラミング                 |  1980 | 2020-07-22 08:02:57 |
|  4 | はじめてのMySQL                     　 |  2980 | 2020-07-22 08:02:46 |
+----+--------------------------------------+-------+---------------------+
3 rows in set (0.00 sec)


// 大きい順に並べる

mysql> SELECT * FROM books ORDER BY price DESC;
+----+--------------------------------------+-------+---------------------+
| id | title                                | price | created_at          |
+----+--------------------------------------+-------+---------------------+
|  4 | はじめてのMySQL                    　  |  2980 | 2020-07-22 08:02:46 |
|  5 | はじめてのプログラミング            　　　|  1980 | 2020-07-22 08:02:57 |
|  6 | はじめてのHTML                       　|  1200 | 2020-07-22 08:03:09 |
+----+--------------------------------------+-------+---------------------+
3 rows in set (0.00 sec)

DESC
- 大きい順に並べたいとき
ASC
- 明示的に小さい順に並べたいとき

集計関数でレコードを分析してみる。

// COUNT関数でレコード数を集計

mysql> SELECT COUNT(*) FROM books;
+----------+
| COUNT(*) |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)


// WHEREを用いて、条件に合ったものだけCOUNT(*) 

mysql> SELECT COUNT(*) FROM books WHERE price > 1500;
+----------+
| COUNT(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)


// SUM関数で合計を表示

mysql> SELECT SUM(price) FROM books;
+------------+
| SUM(price) |
+------------+
|       6160 |
+------------+
1 row in set (0.00 sec)


// AVG関数で平均を表示

mysql> SELECT AVG(price) FROM books;
+------------+
| AVG(price) |
+------------+
|  2053.3333 |
+------------+
1 row in set (0.00 sec)

テーブルを新規作成する（正規化）。

// テーブル作成用のSQLを実行

mysql> CREATE TABLE chapters (
    ->     id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
    ->     number INT, // 章番号
    ->     title VARCHAR(100), // 章タイトル
    ->     book_id INT, // 章立てになる書籍のid
    ->     created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
    -> );
Query OK, 0 rows affected (0.02 sec)

// テーブルを確認

mysql> show tables;
+---------------------+
| Tables_in_bookstore |
+---------------------+
| books               |
| chapters            |
+---------------------+
2 rows in set (0.01 sec)


// レコードを確認

mysql> SELECT * FROM books;
+----+--------------------------------------+-------+---------------------+
| id | title                                | price | created_at          |
+----+--------------------------------------+-------+---------------------+
|  4 | はじめてのMySQL                    　  |  2980 | 2020-07-22 08:02:46 |
|  5 | はじめてのプログラミング                 |  1980 | 2020-07-22 08:02:57 |
|  6 | はじめてのHTML                       　|  1200 | 2020-07-22 08:03:09 |
+----+--------------------------------------+-------+---------------------+
3 rows in set (0.00 sec)


// テーブルに3つの章を登録

mysql> INSERT INTO chapters (number, title, book_id) VALUES (1, "MySQLとは", 4);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO chapters (number, title, book_id) VALUES (2, "テーブルとは", 4);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO chapters (number, title, book_id) VALUES (3, "CRUDとは", 4);
Query OK, 1 row affected (0.00 sec)


// テーブルを確認

mysql> SELECT * FROM chapters;
+----+--------+--------------------+---------+---------------------+
| id | number | title              | book_id | created_at          |
+----+--------+--------------------+---------+---------------------+
|  1 |      1 | MySQLとは　         |       4 | 2020-07-22 09:39:25 |
|  2 |      2 | テーブルとは　　　　　 |       4 | 2020-07-22 09:39:36 |
|  3 |      3 | CRUDとは         　 |       4 | 2020-07-22 09:39:47 |
+----+--------+--------------------+---------+---------------------+
3 rows in set (0.00 sec)

テーブルを結合する。

mysql> SELECT * FROM books INNER JOIN chapters ON books.id = chapters.book_id;
+----+----------------------+-------+---------------------+----+--------+--------------------+---------+---------------------+
| id | title                | price | created_at          | id | number | title              | book_id | created_at          |
+----+----------------------+-------+---------------------+----+--------+--------------------+---------+---------------------+
|  4 | はじめてのMySQL      |  2980 | 2020-07-22 08:02:46 |  1 |      1 | MySQLとは          |       4 | 2020-07-22 09:39:25 |
|  4 | はじめてのMySQL      |  2980 | 2020-07-22 08:02:46 |  2 |      2 | テーブルとは       |       4 | 2020-07-22 09:39:36 |
|  4 | はじめてのMySQL      |  2980 | 2020-07-22 08:02:46 |  3 |      3 | CRUDとは           |       4 | 2020-07-22 09:39:47 |
+----+----------------------+-------+---------------------+----+--------+--------------------+---------+---------------------+
3 rows in set (0.00 sec)

テーブルの結合には、INNER JOIN テーブル名 ON 条件 を使用。

SELECT * FROM books INNER JOIN chapters ON books.id = chapters.book_id;
- SELECT * FROM books：テーブルの一覧を表示
- INNER JOIN chapters：テーブルを結合
- ON books.id = chapters.book_id：条件に沿わない結合パターンを消去

mysql> INSERT INTO chapters (number, title, book_id) VALUES (1, "プログラミングとは", 5);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO chapters (number, title, book_id) VALUES (2, "変数とは", 5);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO chapters (number, title, book_id) VALUES (3, "関数とは", 5);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM books INNER JOIN chapters ON books.id = chapters.book_id;
+----+--------------------------------------+-------+---------------------+----+--------+-----------------------------+---------+---------------------+
| id | title                                | price | created_at          | id | number | title                       | book_id | created_at          |
+----+--------------------------------------+-------+---------------------+----+--------+-----------------------------+---------+---------------------+
|  4 | はじめてのMySQL                      |  2980 | 2020-07-22 08:02:46 |  1 |      1 | MySQLとは                   |       4 | 2020-07-22 09:39:25 |
|  4 | はじめてのMySQL                      |  2980 | 2020-07-22 08:02:46 |  2 |      2 | テーブルとは                |       4 | 2020-07-22 09:39:36 |
|  4 | はじめてのMySQL                      |  2980 | 2020-07-22 08:02:46 |  3 |      3 | CRUDとは                    |       4 | 2020-07-22 09:39:47 |
|  5 | はじめてのプログラミング             |  1980 | 2020-07-22 08:02:57 |  4 |      1 | プログラミングとは          |       5 | 2020-07-22 10:16:18 |
|  5 | はじめてのプログラミング             |  1980 | 2020-07-22 08:02:57 |  5 |      2 | 変数とは                    |       5 | 2020-07-22 10:16:27 |
|  5 | はじめてのプログラミング             |  1980 | 2020-07-22 08:02:57 |  6 |      3 | 関数とは                    |       5 | 2020-07-22 10:16:35 |
+----+--------------------------------------+-------+---------------------+----+--------+-----------------------------+---------+---------------------+
6 rows in set (0.00 sec)

上記のようにレコードを追加しても、同様に意味のある結合パターンのみを抽出できる。

GROUP BYでグループ化して集計してみる。

mysql> SELECT * FROM chapters;
+----+--------+-----------------------------+---------+---------------------+
| id | number | title                       | book_id | created_at          |
+----+--------+-----------------------------+---------+---------------------+
|  1 |      1 | MySQLとは                 　 |       4 | 2020-07-22 09:39:25 |
|  2 |      2 | テーブルとは              　 　|       4 | 2020-07-22 09:39:36 |
|  3 |      3 | CRUDとは                   　|       4 | 2020-07-22 09:39:47 |
|  4 |      1 | プログラミングとは          　 |       5 | 2020-07-22 10:16:18 |
|  5 |      2 | 変数とは                     |       5 | 2020-07-22 10:16:27 |
|  6 |      3 | 関数とは                     |       5 | 2020-07-22 10:16:35 |
+----+--------+-----------------------------+---------+---------------------+
6 rows in set (0.00 sec)


// 集計関数とグループ化を同時に使用

mysql> SELECT book_id, COUNT(*) FROM chapters GROUP BY book_id;
+---------+----------+
| book_id | COUNT(*) |
+---------+----------+
|       4 |        3 |
|       5 |        3 |
+---------+----------+
2 rows in set (0.00 sec)


// INNER JOIN によって books テーブルを結合

mysql> SELECT books.title, COUNT(*) AS number_of_chapters FROM chapters INNER JOIN books ON books.id = chapters.book_id GROUP BY book_id;
+--------------------------------------+--------------------+
| title                                | number_of_chapters |
+--------------------------------------+--------------------+
| はじめてのMySQL                      　|                  3 |
| はじめてのプログラミング                 |                  3 |
+--------------------------------------+--------------------+
2 rows in set (0.00 sec)

AS カラム名 とすることで名前を付けられる。集計関数とグループ化を使うことでランキング機能を持たせることが可能。

副問い合わせ。

mysql> SELECT books.title, COUNT(*) AS number_of_chapters FROM chapters INNER JOIN books ON books.id = chapters.book_id WHERE chapters.book_id = 4 GROUP BY book_id;
+----------------------+--------------------+
| title                | number_of_chapters |
+----------------------+--------------------+
| はじめてのMySQL 　     |                  3 |
+----------------------+--------------------+
1 row in set (0.00 sec)

JOINとWHERE を同時に使うことで「はじめてのMySQL」のみのデータを取得できる。

mysql> SELECT book_id FROM chapters WHERE title = "MySQLとは";
+---------+
| book_id |
+---------+
|       4 |
+---------+
1 row in set (0.00 sec)

mysql> SELECT books.title, COUNT(*) AS number_of_chapters FROM chapters INNER JOIN books ON books.id = chapters.book_id WHERE chapters.book_id = (SELECT book_id FROM chapters WHERE title = "MySQLとは") GROUP BY book_id;
+----------------------+--------------------+
| title                | number_of_chapters |
+----------------------+--------------------+
| はじめてのMySQL      |                  3 |
+----------------------+--------------------+
1 row in set (0.01 sec)

book_id が不明で、「『MySQLとは』という名前の章が入っている書籍の章の数」を知りたい場合はひとつのSQLの中に ( )で囲って別のSELECT文を記述する。このSELECT文のことを副問い合わせという。

「world.sql」を使って練習。

mysql> source ~/desktop/world.sql;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)
// 以下省略

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| bookstore          |
| mysql              |
| performance_schema |
| sys                |
| world              |
+--------------------+
6 rows in set (0.00 sec)

mysql> use world;
Database changed
mysql> show tables;
+-----------------+
| Tables_in_world |
+-----------------+
| city            |
| country         |
| countrylanguage |
+-----------------+
3 rows in set (0.00 sec)

MySQLの学習に使えるサンプルデータworld.sqlをインストールしてセットアップ（上記）。

練習問題①：`countrylanguage` テーブルからすべてのデータを取得してください。

mysql> SELECT * FROM countrylanguage;
+-------------+---------------------------+------------+------------+
| CountryCode | Language                  | IsOfficial | Percentage |
+-------------+---------------------------+------------+------------+
| ABW         | Dutch                     | T          |        5.3 |
| ABW         | English                   | F          |        9.5 |
| ABW         | Papiamento                | F          |       76.7 |
| ABW         | Spanish                   | F          |        7.4 |
| AFG         | Balochi                   | F          |        0.9 |
| AFG         | Dari                      | T          |       32.1 |
| AFG         | Pashto                    | T          |       52.4 |

// 以下省略

アンドラの国コードがANDだから不自然にハイライトされてるのが面白い。~~これだけ長いコードだから結果は省略すれば良いとも思ったけど、瞬時に処理されるところに感動したから載せてみた。~~想像以上にページが重くなったので泣く泣く消去。

練習問題②：`country`テーブルから`code`,`name`,`continent`の列だけを取り出したすべてのデータを取得してください。

mysql> SELECT code, name, continent FROM country;
+------+----------------------------------------------+---------------+
| code | name                                         | continent     |
+------+----------------------------------------------+---------------+
| ABW  | Aruba                                        | North America |
| AFG  | Afghanistan                                  | Asia          |
| AGO  | Angola                                       | Africa        |
| AIA  | Anguilla                                     | North America |
| ALB  | Albania                                      | Europe        |
| AND  | Andorra                                      | Europe        |
| ANT  | Netherlands Antilles                         | North America |

// 以下省略

練習問題③：`countrylanguage`テーブルから`CountryCode` が日本（`JPN` ）のデータだけを取得してください。

mysql> SELECT * FROM countrylanguage WHERE CountryCode = 'JPN';
+-------------+----------------------+------------+------------+
| CountryCode | Language             | IsOfficial | Percentage |
+-------------+----------------------+------------+------------+
| JPN         | Ainu                 | F          |        0.0 |
| JPN         | Chinese              | F          |        0.2 |
| JPN         | English              | F          |        0.1 |
| JPN         | Japanese             | T          |       99.1 |
| JPN         | Korean               | F          |        0.5 |
| JPN         | Philippene Languages | F          |        0.1 |
+-------------+----------------------+------------+------------+
6 rows in set (0.00 sec)

練習問題④：`countrylanguage` テーブルのすべてのデータを`Language` の降順（A,B,C,… の逆順）に並び替えて取得してください。

mysql> SELECT * FROM countrylanguage ORDER BY Language;
+-------------+---------------------------+------------+------------+
| CountryCode | Language                  | IsOfficial | Percentage |
+-------------+---------------------------+------------+------------+
| GEO         | Abhyasi                   | F          |        1.7 |
| UGA         | Acholi                    | F          |        4.4 |
| BEN         | Adja                      | F          |       11.1 |
| DJI         | Afar                      | F          |       34.8 |
| ERI         | Afar                      | F          |        4.3 |
| NAM         | Afrikaans                 | F          |        9.5 |
| ZAF         | Afrikaans                 | T          |       14.3 |

// 以下省略

練習問題⑤：`city`テーブルの全件について`CountryCode` 別でデータが何件あるのかを集計してください。

mysql> SELECT CountryCode, COUNT(*) FROM city GROUP BY CountryCode;
+-------------+----------+
| CountryCode | COUNT(*) |
+-------------+----------+
| ABW         |        1 |
| AFG         |        4 |
| AGO         |        5 |
| AIA         |        2 |
| ALB         |        1 |
| AND         |        1 |
| ANT         |        1 |

// 以下省略

練習問題⑥：`city` テーブルと`country` テーブルを`city の CountryCode` 列と `country の Code` 列で結合してください。

mysql> SELECT * FROM city INNER JOIN country ON city.CountryCode = country.Code;

// 結果は省略

結果を貼り付けてプレビューを押したら、画面がフリーズしたので省略しました。4079行もあったので仕方ない。

データベースの作成課題。

-- 1. kadaidb データベースを作成するSQL文
mysql> CREATE DATABASE kadaidb default character set utf8;


-- 2. person テーブルを作成するSQL文
mysql> CREATE TABLE kadaidb.person (
    -> id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
    -> name VARCHAR(50),
    -> age INT);


// 確認

mysql> describe person;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(50) | YES  |     | NULL    |                |
| age   | int(11)     | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)


-- 3. データを1件保存するSQL文

mysql> INSERT INTO person (name, age) VALUES ("きき", 25);


// 確認

mysql> SELECT * FROM person;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | きき   |   25 |
+----+--------+------+
1 row in set (0.00 sec)

今日の反省と明日の目標。

プログラミングをやってる感がまるでなかったけど、テーブルがスッと表示されるのは気持ちが良いですね。体感的にエクセルをちょっと、いや、かなりややこしくしたイメージかな。ちゃんと追加されてなかったり、別の場所に入ってたりで、より慎重さが必要な言語だと思いました。このデータが大きくなるほど責任重大になっていくんだろうなぁ、恐ろしい。。

明日はこのSQLとJavaを統合するJDBCというのに取り掛かっていきます。