Создайте таблицу избранных курсов в 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)

🎯 Задачи

В этом проекте вы научитесь:

Как обращаться к базе данных MySQL с использованием команды sudo
Как импортировать данные из 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, чтобы улучшить свои навыки.

Поиск избранных курсов