MySQL – Part2 – 演習課題

ここでは SQL の学習のまとめとして、期待する結果のとおり出力するSQLを作成していきます。

サンプルデータの準備

MySQLの eldb データベース上に以下の2つのテーブルを準備します。

• categories
• courses

MariaDB [eldb]> select * from categories;
+----+-------------+
| id | title       |
+----+-------------+
|  1 | Programming |
|  2 | Design      |
|  3 | Marketing   |
+----+-------------+
3 rows in set (0.00 sec)

MariaDB [eldb]> select * from courses;
+----+------------------+---------------+-------------+
| id | title            | learning_time | category_id |
+----+------------------+---------------+-------------+
|  1 | PHP Basic        |            30 |           1 |
|  2 | PHP Database     |            50 |           1 |
|  3 | Python Basic     |            40 |           1 |
|  4 | Web Design       |            50 |           2 |
|  5 | History of Japan |           100 |        NULL |
+----+------------------+---------------+-------------+
5 rows in set (0.00 sec)

MariaDB [eldb]> 

これまでのデータと変わりありません。ここではデータを再登録しておいてください。

（準備）テーブル作成用SQL

drop table if exists categories;
create table categories(
  id integer primary key,
  title varchar(100)
);

insert into categories (id, title) values (1, 'Programming');
insert into categories (id, title) values (2, 'Design');
insert into categories (id, title) values (3, 'Marketing');

drop table if exists courses;
create table courses(
  id integer primary key,
  title varchar(100),
  learning_time integer,
  category_id integer
);

insert into courses (id, title, learning_time, category_id) values (1, 'PHP Basic', 30, 1);
insert into courses (id, title, learning_time, category_id) values (2, 'PHP Database', 50, 1);
insert into courses (id, title, learning_time, category_id) values (3, 'Python Basic', 40,  1);
insert into courses (id, title, learning_time, category_id) values (4, 'Web Design', 50, 2);
insert into courses (id, title, learning_time, category_id) values (5, 'Japan''s History', 100, null);

---

1 検索条件（ where 句）

categories テーブルの中から id が 2 のレコードを表示します。

MariaDB [eldb]> select title from categories ???;
+--------+
| title  |
+--------+
| Design |
+--------+
1 row in set (0.00 sec)

MariaDB [eldb]> 

---

2 like 演算子によるパターンマッチング

categories テーブルの中から title が ing で終わるレコードを表示します。

MariaDB [eldb]> select * from categories ???;
+----+-------------+
| id | title       |
+----+-------------+
|  1 | Programming |
|  3 | Marketing   |
+----+-------------+
2 rows in set (0.00 sec)

MariaDB [eldb]> 

---

3 並び替え（ order by 句）

categories テーブルのレコードを id の降順で表示します。

MariaDB [eldb]> select * from categories ???;
+----+-------------+
| id | title       |
+----+-------------+
|  3 | Marketing   |
|  2 | Design      |
|  1 | Programming |
+----+-------------+
3 rows in set (0.00 sec)

MariaDB [eldb]> 

---

4 関数

categories テーブルの title を大文字で表示します。

MariaDB [eldb]> select ??? from categories; 
+-------------+
| u_title     |
+-------------+
| PROGRAMMING |
| DESIGN      |
| MARKETING   |
+-------------+
3 rows in set (0.00 sec)

MariaDB [eldb]> 

---

5 グループ関数

categories テーブルのレコード数を表示します。

MariaDB [eldb]> select ??? from categories; 
+----------------+
| category_count |
+----------------+
|              3 |
+----------------+
1 row in set (0.00 sec)

MariaDB [eldb]> 

---

6 サブクエリ（副問合せ）

categories テーブルの id が最大値（ 3 ）のレコードを表示します。

MariaDB [eldb]> select * from categories ???;
+----+-----------+
| id | title     |
+----+-----------+
|  3 | Marketing |
+----+-----------+
1 row in set (0.00 sec)

MariaDB [eldb]> 

---

7 テーブルの結合（内部結合）

categories テーブルと courses テーブルを内部結合で表示します。

MariaDB [eldb]> select ca.title category_title, co.title course_title from ???; 
+----------------+--------------+
| category_title | course_title |
+----------------+--------------+
| Programming    | PHP Basic    |
| Programming    | PHP Database |
| Programming    | Python Basic |
| Design         | Web Design   |
+----------------+--------------+
4 rows in set (0.00 sec)

MariaDB [eldb]> 

---

8 テーブルの結合（内部結合 - 検索条件の指定）

categories テーブルと courses テーブルを内部結合し、 categories テーブルの id が 1 のレコードを表示します。

MariaDB [eldb]> select ca.title category_title, co.title course_title from ???;
+----------------+--------------+
| category_title | course_title |
+----------------+--------------+
| Programming    | PHP Basic    |
| Programming    | PHP Database |
| Programming    | Python Basic |
+----------------+--------------+
3 rows in set (0.00 sec)

MariaDB [eldb]> 

9 テーブルの結合（左外部結合）

courses テーブルと categories テーブルを左外部結合で表示します。

MariaDB [eldb]> select ca.title category_title, co.title course_title from ???;
+----------------+-----------------+
| category_title | course_title    |
+----------------+-----------------+
| Programming    | PHP Basic       |
| Programming    | PHP Database    |
| Programming    | Python Basic    |
| Design         | Web Design      |
| NULL           | Japan's History |
+----------------+-----------------+
5 rows in set (0.00 sec)

MariaDB [eldb]> 

10 テーブルの結合（左外部結合 - グループ化 - 並び替え）

categories テーブルと courses テーブルを左外部結合し、 categories テーブルの id ごとのレコード件数を、件数の多いものから順に表示します。

MariaDB [eldb]> select ca.title, count(co.id) course_count from ???;
+-------------+--------------+
| title       | course_count |
+-------------+--------------+
| Programming |            3 |
| Design      |            1 |
| Marketing   |            0 |
+-------------+--------------+
3 rows in set (0.00 sec)

MariaDB [eldb]>