MySQL Database Management

Introduction

In this project, you will learn how to work with MySQL databases, including starting the MySQL service, accessing the MySQL command-line interface, importing a database, and using the TRUNCATE statement to delete data from a table.

👀 Preview

MariaDB [world]> SELECT * FROM city;
Empty set (0.001 sec)

🎯 Tasks

In this project, you will learn:

• How to start the MySQL database service
• How to access the MySQL command-line interface using the sudo command
• How to import a database into MySQL
• How to use the TRUNCATE statement to delete all data from a table

🏆 Achievements

After completing this project, you will be able to:

• Manage a MySQL database, including starting the service and accessing the command-line interface
• Import data into a MySQL database
• Use SQL statements like TRUNCATE to manipulate data in a database

Start MySQL Database and Access MySQL

In this step, you will learn how to start the MySQL database and access it using the sudo command without a password.

1. Start the MySQL database service:

sudo service mysql start

2. Access the MySQL command-line interface using the sudo command without a password:

sudo mysql

You should now be able to access the MySQL prompt.

Import World Database

In this step, you will learn how to import the world.sql database file into the MySQL database.

1. Import the world.sql file into the MySQL database:

MariaDB [(none)]> SOURCE /home/labex/project/world.sql;

This will import the world database into your MySQL instance.

2. Switch to the world database:

MariaDB [(none)]> USE world;

You should now be in the world database.

Truncate City Table

In this step, you will learn how to use the TRUNCATE statement to delete all data from the city table.

1. Open the truncateCity.sql file.
2. Add the following SQL statement to the file:

TRUNCATE TABLE city;

This will delete all data from the city table.

3. Run the truncateCity.sql script in the MySQL prompt:

MariaDB [world]> SOURCE /home/labex/project/truncateCity.sql;

4. Verify that the city table is empty:

MariaDB [world]> SELECT * FROM city;

You should see an empty result set, indicating that the city table has been truncated.

MariaDB [world]> SOURCE /home/labex/project/truncateCity.sql;
Query OK, 0 rows affected (0.004 sec)

MariaDB [world]> SELECT * FROM city;
Empty set (0.001 sec)

Summary

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