今日の学習時間。
- 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, …)のいずれかと等しい |
順番を決める「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というのに取り掛かっていきます。