Change Default Value for Countrylanguage Table

Introduction

In this project, you will learn how to change the default value of a field in a MySQL database table. Specifically, you will be modifying the IsOfficial field in the countrylanguage table of the world database.

👀 Preview

MariaDB [world]> DESC countrylanguage;
+-------------+---------------+------+-----+---------+-------+
| Field       | Type          | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| CountryCode | char(3)       | NO   | PRI |         |       |
| Language    | char(30)      | NO   | PRI |         |       |
| IsOfficial  | enum('T','F') | YES  |     | T       |       |
| Percentage  | decimal(4,1)  | NO   |     | 0.0     |       |
+-------------+---------------+------+-----+---------+-------+
4 rows in set (0.001 sec)

🎯 Tasks

In this project, you will learn:

• How to access MySQL using the sudo command without a password
• How to import data from a SQL file into a MySQL database
• How to change the default value of a field in a MySQL table

🏆 Achievements

After completing this project, you will be able to:

• Confidently navigate and manage data in a MySQL database
• Understand how to modify table structures and default values
• Apply your MySQL knowledge to real-world database management tasks

Access MySQL and Import the Data

In this step, you will learn how to access MySQL using the sudo command without any password, and import the data from /home/labex/project/world.sql into MySQL.

1. Open a terminal and navigate to the /home/labex/project directory.
2. Start the MySQL service by running the following command:

sudo service mysql start

3. Access MySQL without a password by running the following command:

sudo mysql

4. Import the data from the world.sql file into MySQL by running the following command:

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

Change the Default Value of the IsOfficial Field

In this step, you will learn how to change the default value of the IsOfficial field in the countrylanguage table to 'T'.

1. Open the changeDefault.sql file and add the following SQL statement:

ALTER TABLE countrylanguage MODIFY COLUMN IsOfficial ENUM('T','F') DEFAULT 'T';

This statement changes the default value of the IsOfficial field in the countrylanguage table to 'T'.

2. Save the changeDefault.sql file.

Run the Script

In this step, you will learn how to run the changeDefault.sql script in MySQL.

1. In the MySQL prompt, run the following command to execute the changeDefault.sql script:

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

2. After the script has finished running, you can verify the changes by running the following command:

DESC countrylanguage;

This will display the updated schema of the countrylanguage table, showing that the default value of the IsOfficial field has been changed to 'T'.

MariaDB [world]> DESC countrylanguage;
+-------------+---------------+------+-----+---------+-------+
| Field       | Type          | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| CountryCode | char(3)       | NO   | PRI |         |       |
| Language    | char(30)      | NO   | PRI |         |       |
| IsOfficial  | enum('T','F') | YES  |     | T       |       |
| Percentage  | decimal(4,1)  | NO   |     | 0.0     |       |
+-------------+---------------+------+-----+---------+-------+
4 rows in set (0.001 sec)

Congratulations! You have successfully completed the project of changing the default value of the IsOfficial field in the countrylanguage table.

Summary

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