Créer une table de cours favoris dans MySQL

Introduction

Dans ce projet, vous allez apprendre à créer une table "favorite" dans une base de données MySQL. La table "favorite" stockera les cours ayant le plus long temps d'étude pour chaque utilisateur dans la table "usercourse".

👀 Aperçu

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 lignes sélectionnées (0,000 sec)

🎯 Tâches

Dans ce projet, vous allez apprendre :

Comment accéder à la base de données MySQL en utilisant la commande sudo
Comment importer des données à partir d'un script SQL dans la base de données MySQL
Comment créer une nouvelle table avec des colonnes spécifiques en utilisant l'instruction CREATE TABLE AS
Comment joindre plusieurs tables pour récupérer les données nécessaires pour la table "favorite"

🏆 Réalisations

Après avoir terminé ce projet, vous serez capable de :

Comprendre comment gérer une base de données MySQL à l'aide de la ligne de commande
Implémenter une requête SQL pour créer une nouvelle table à partir de données provenant de plusieurs tables existantes
Appliquer le concept de recherche de la valeur maximale pour chaque groupe dans une table
Démontrez votre capacité à travailler avec des instructions SQL pour réaliser une tâche de gestion de données spécifique

Accéder à MySQL et importer la base de données

Dans cette étape, vous allez apprendre à accéder à la base de données MySQL et à importer les données nécessaires.

Ouvrez le terminal et démarrez le service MySQL en utilisant la commande suivante :

sudo service mysql start

Accédez au client MySQL en utilisant la commande suivante :

sudo mysql

Importez les données à partir du script labex_db_info.sql dans MySQL :

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

Cela importera les données nécessaires dans la base de données MySQL.

Créer la table favorite

Dans cette étape, vous allez créer la table "favorite", qui stockera les cours ayant le plus long temps d'étude pour chaque utilisateur.

Ouvrez le fichier searchForFavoriteCourses.sql.
Dans le fichier searchForFavoriteCourses.sql, créez la table "favorite" avec les colonnes suivantes :
- id (clé primaire)
- user_name (nom d'utilisateur)
- course_name (nom du cours)
- study_time (temps d'étude)

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

Cette requête crée la table "favorite" en joignant les tables "usercourse" et "user" pour obtenir le nom d'utilisateur, puis en joignant la table "course" pour obtenir le nom du cours. La table stocke les cours ayant le plus long temps d'étude pour chaque utilisateur.

Vérifier la table favorite

Dans cette étape, vous allez vérifier le contenu de la table "favorite".

Dans l'invite MySQL, exécutez la commande suivante pour exécuter le script searchForFavoriteCourses.sql :

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

Exécutez la requête suivante pour afficher les 10 premières lignes de la table "favorite" :

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 lignes sélectionnées (0,000 sec)

Cette requête affichera les 10 premières lignes de la table "favorite", montrant le nom d'utilisateur, le nom du cours et le temps d'étude pour chaque enregistrement.

Félicitations! Vous avez créé avec succès la table "favorite" et vérifié son contenu.

Résumé

Félicitations! Vous avez terminé ce projet. Vous pouvez pratiquer plus de laboratoires sur LabEx pour améliorer vos compétences.

Rechercher les cours favoris