A Simple Course Database: Learn MySQL Basics

Introduction

In this project, you will learn how to create a simple course database using MySQL. The project involves setting up a MySQL database, creating tables, and importing data from CSV files into the database.

👀 Preview

Unfinished course database preview

🎯 Tasks

In this project, you will learn:

How to start the MySQL database and access it using the MySQL command line client
How to create a database and a user with the necessary privileges
How to import a database schema from a SQL script
How to import data from CSV files into the database tables

🏆 Achievements

After completing this project, you will be able to:

Understand the basic structure and setup of a MySQL database
Perform common database operations such as creating a database, tables, and importing data
Apply your knowledge to build and manage a simple course database system

Start the MySQL Database and Access MySQL

In this step, you will learn how to start the MySQL database and access it using the MySQL command line client.

Start the MySQL database service using the following command:
```
sudo service mysql start
```
Access the MySQL command line client using the following command:
```
sudo mysql
```
This will log you in to the MySQL command line client without a password.

Create the LabEx Database and User

In this step, you will learn how to create the labex database and the labex user with the necessary privileges.

Create the labex database using the following SQL statement:
```
CREATE DATABASE labex;
```
Create the labex user with the necessary privileges using the following SQL statement:
```
GRANT ALL PRIVILEGES ON labex.* TO 'labex'@'%' IDENTIFIED BY 'labex';
FLUSH PRIVILEGES;
```
This will create the labex user with the password labex and grant all privileges on the labex database to this user.

Import the Database Schema

In this step, you will learn how to import the database schema from the labex_db_info.sql script.

Execute the following SQL statement to import the database schema:
```
SOURCE /home/labex/project/labex_db_info.sql;
```
This will create the necessary tables (user, course, and usercourse) in the labex database.

Import the CSV Data Files

In this step, you will learn how to import the CSV data files into the corresponding tables.

Import the labex_user.csv file into the user table using the following SQL statement:

LOAD DATA LOCAL INFILE '/home/labex/project/labex_user.csv' INTO TABLE user FIELDS TERMINATED BY ',';

Import the labex_course.csv file into the course table using the following SQL statement:

LOAD DATA LOCAL INFILE '/home/labex/project/labex_course.csv' INTO TABLE course FIELDS TERMINATED BY ',';

Import the labex_usercourse.csv file into the usercourse table using the following SQL statement:

LOAD DATA LOCAL INFILE '/home/labex/project/labex_usercourse.csv' INTO TABLE usercourse FIELDS TERMINATED BY ',';

Verify the Data Import

In this step, you will learn how to verify the data import by querying the tables.

Execute the following SQL statement to check the data in the usercourse table:
```
SELECT * FROM usercourse LIMIT 10;
```
This will display the first 10 rows of the usercourse table, allowing you to verify that the data has been imported correctly.

The output should look similar to the following:

MariaDB [labex]> SELECT * FROM usercourse LIMIT 10;
+---------+-----------+------------+
| user_id | course_id | study_time |
+---------+-----------+------------+
|     279 |       742 |         96 |
|     114 |       744 |         74 |
|     492 |       680 |         84 |
|      65 |       498 |         46 |
|     141 |       729 |         23 |
|     193 |       729 |         18 |
|     943 |        63 |         98 |
|     437 |       498 |         28 |
|     942 |       742 |         76 |
|     616 |         1 |          7 |
+---------+-----------+------------+
10 rows in set (0.000 sec)

Congratulations! You have successfully created the LabEx database and imported the necessary data. You can now use this database for further analysis and development.

Summary

Congratulations! You have completed this project. You can practice more labs in LabEx to improve your skills.