MySQL でお気に入りのコーステーブルを作成する

はじめに

このプロジェクトでは、MySQL データベースに「お気に入り」テーブルを作成する方法を学びます。「お気に入り」テーブルは、「usercourse」テーブルの各ユーザーの最長学習時間のコースを格納します。

👀 プレビュー

MariaDB [labex]> select * from favorite limit 10;
+----+-----------+-------------+------------+
| id | user_name | course_name | study_time |
+----+-----------+-------------+------------+
|  2 | user_01   | English     |        101 |
|  3 | user_02   | Chinese     |        102 |
|  4 | user_03   | Chemical    |        103 |
|  5 | user_04   | Physics     |        104 |
|  6 | user_05   | Biology     |        105 |
|  7 | user_06   | Painting    |        106 |
|  8 | user_07   | Music       |        107 |
|  9 | user_08   | Computer    |        108 |
| 10 | user_09   | History     |        109 |
| 11 | user_10   | Math        |        110 |
+----+-----------+-------------+------------+
10 rows in set (0.000 sec)

🎯 タスク

このプロジェクトでは、以下を学びます。

sudo コマンドを使用して MySQL データベースにアクセスする方法
SQL スクリプトからデータを MySQL データベースにインポートする方法
CREATE TABLE AS ステートメントを使用して特定の列で新しいテーブルを作成する方法
複数のテーブルを結合して、「お気に入り」テーブルに必要なデータを取得する方法

🏆 成果

このプロジェクトを完了すると、以下のことができるようになります。

コマンドラインを使用して MySQL データベースを管理する方法を理解する
複数の既存のテーブルのデータに基づいて新しいテーブルを作成する SQL クエリを実装する
テーブル内の各グループの最大値を見つける概念を適用する
特定のデータ管理タスクを達成するために SQL ステートメントを使用する能力を示す

MySQL にアクセスしてデータベースをインポートする

このステップでは、MySQL データベースにアクセスして必要なデータをインポートする方法を学びます。

ターミナルを開き、次のコマンドを使用して MySQL サービスを起動します。

sudo service mysql start

次のコマンドを使用して MySQL クライアントにアクセスします。

sudo mysql

labex_db_info.sql スクリプトからのデータを MySQL にインポートします。

SOURCE /home/labex/project/labex_db_info.sql;

これにより、必要なデータが MySQL データベースにインポートされます。

お気に入りのテーブルを作成する

このステップでは、各ユーザーの最長学習時間のコースを格納する「お気に入り」テーブルを作成します。

searchForFavoriteCourses.sql ファイルを開きます。
searchForFavoriteCourses.sql ファイルで、以下の列で「お気に入り」テーブルを作成します。
- id（主キー）
- user_name（ユーザー名）
- course_name（コース名）
- study_time（学習時間）

create table favorite as
SELECT t1.id, t1.user_name, t2.course_name, t1.study_time
FROM (
         SELECT a.id, a.course_id, a.study_time, b.user_name
         FROM (
                  SELECT t0.id, t0.user_id, t0.course_id, t0.study_time
                  FROM usercourse t0
                  INNER JOIN (
                      SELECT user_id, MAX(study_time) AS max_study_time
                      FROM usercourse
                      GROUP BY user_id
                  ) t_max ON t0.user_id = t_max.user_id AND t0.study_time >= t_max.max_study_time
              ) a LEFT JOIN user b ON a.user_id = b.user_id
) t1 LEFT JOIN course t2 ON t1.course_id = t2.course_id

このクエリは、「usercourse」テーブルと「user」テーブルを結合してユーザー名を取得し、その後「course」テーブルを結合してコース名を取得することで「お気に入り」テーブルを作成します。このテーブルは、各ユーザーの最長学習時間のコースを格納します。

お気に入りのテーブルを確認する

このステップでは、「お気に入り」テーブルの内容を検証します。

MySQL のプロンプトで、次のコマンドを実行して searchForFavoriteCourses.sql スクリプトを実行します。

SOURCE /home/labex/project/searchForFavoriteCourses.sql;

「お気に入り」テーブルの最初の 10 行を表示するには、次のクエリを実行します。

MariaDB [labex]> select * from favorite limit 10;
+----+-----------+-------------+------------+
| id | user_name | course_name | study_time |
+----+-----------+-------------+------------+
|  2 | user_01   | English     |        101 |
|  3 | user_02   | Chinese     |        102 |
|  4 | user_03   | Chemical    |        103 |
|  5 | user_04   | Physics     |        104 |
|  6 | user_05   | Biology     |        105 |
|  7 | user_06   | Painting    |        106 |
|  8 | user_07   | Music       |        107 |
|  9 | user_08   | Computer    |        108 |
| 10 | user_09   | History     |        109 |
| 11 | user_10   | Math        |        110 |
+----+-----------+-------------+------------+
10 rows in set (0.000 sec)

このクエリは、「お気に入り」テーブルの最初の 10 行を表示し、各レコードのユーザー名、コース名、学習時間を示します。

おめでとうございます！「お気に入り」テーブルを正常に作成し、その内容を検証しました。

まとめ

おめでとうございます！このプロジェクトを完了しました。あなたの技術を向上させるために、LabEx でさらに多くの実験を行って練習することができます。

お気に入りのコースを検索する