MySQL Database Management Fundamentals

Introduction

In this project, you will learn how to perform various DDL (Data Definition Language) operations on a MySQL database. You will create a database, tables, modify table structures, delete data, and drop tables and databases.

👀 Preview

🎯 Tasks

In this project, you will learn:

• How to start the MySQL service and access the database using the sudo command
• How to create a database with a specific character set
• How to create tables with various data types and primary keys
• How to modify table structures, such as making primary keys auto-incremented
• How to delete data from tables using DELETE and TRUNCATE
• How to drop tables and databases

🏆 Achievements

After completing this project, you will be able to:

• Understand the basic DDL operations in MySQL
• Create and manage databases and tables
• Modify table structures to fit your needs
• Efficiently delete data from tables
• Drop tables and databases when necessary

Skills Graph

Start MySQL and Access the Database

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

1. Start the MySQL service using the following command:

sudo service mysql start

2. Access the MySQL database using the sudo command without any password:

sudo mysql

Now you are ready to proceed to the next step.

Create the Database and Tables

In this step, you will learn how to create the testddl database with the character set specified as utf8mb4, and then create the emp and dept tables within the database.

1. Create the testddl database with the character set specified as utf8mb4:

CREATE DATABASE IF NOT EXISTS `testddl` DEFAULT CHARACTER SET utf8mb4;

2. Use the testddl database:

USE `testddl`;

3. Create the emp table with the following structure:

CREATE TABLE `emp` (
  `eid` int(5) NOT NULL,
  `ename` varchar(50),
  `sal` DECIMAL,
  `deptno` int(5),
  PRIMARY KEY (`eid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

4. Create the dept table with the following structure:

CREATE TABLE `dept` (
  `did` int(5) NOT NULL,
  `dname` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`did`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Now you have created the testddl database and the emp and dept tables. Let's move on to the next step.

Modify the Tables

In this step, you will learn how to modify the primary keys of the emp and dept tables to be auto-incremented, starting from 10. You will also modify the data type of the sal field in the emp table to integer.

1. Modify the primary key of the emp table to be auto-incremented, starting from 10:

ALTER TABLE `emp` AUTO_INCREMENT=10;

2. Modify the primary key of the dept table to be auto-incremented, starting from 10:

ALTER TABLE `dept` AUTO_INCREMENT=10;

3. Modify the data type of the sal field in the emp table to integer:

ALTER TABLE `emp` MODIFY `sal` INT(5);

Now the primary keys of the emp and dept tables are auto-incremented, starting from 10, and the data type of the sal field in the emp table is modified to integer.

Delete Data From the Tables

In this step, you will learn how to delete all data from the emp and dept tables.

1. Delete all data from the emp table using the DELETE statement:

DELETE FROM `emp`;

2. Delete all data from the dept table using the TRUNCATE statement:

TRUNCATE TABLE `dept`;

Now the emp and dept tables are empty.

Drop the Table and Database

In this final step, you will learn how to drop the emp table and the testddl database.

1. Drop the emp table:

DROP TABLE `emp`;

2. Drop the testddl database:

DROP DATABASE `testddl`;

You can refer to the following output:

MariaDB [(none)]> USE `testddl`;
Database changed
MariaDB [testddl]> CREATE TABLE `emp` (
    ->   `eid` int(5) NOT NULL,
    ->   `ename` varchar(50),
    ->   `sal` DECIMAL,
    ->   `deptno` int(5),
    ->   PRIMARY KEY (`eid`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.005 sec)

MariaDB [testddl]> CREATE TABLE `dept` (
    ->   `did` int(5) NOT NULL,
    ->   `dname` varchar(50) DEFAULT NULL,
    ->   PRIMARY KEY (`did`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.004 sec)

MariaDB [testddl]> desc emp;
+--------+---------------+------+-----+---------+-------+
| Field  | Type          | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+-------+
| eid    | int(5)        | NO   | PRI | NULL    |       |
| ename  | varchar(50)   | YES  |     | NULL    |       |
| sal    | decimal(10,0) | YES  |     | NULL    |       |
| deptno | int(5)        | YES  |     | NULL    |       |
+--------+---------------+------+-----+---------+-------+
4 rows in set (0.001 sec)

MariaDB [testddl]> ALTER TABLE `emp` AUTO_INCREMENT=10;
Query OK, 0 rows affected (0.005 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [testddl]> ALTER TABLE `dept` AUTO_INCREMENT=10;
Query OK, 0 rows affected (0.003 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [testddl]> ALTER TABLE `emp` MODIFY `sal` INT(5);
Query OK, 0 rows affected (0.006 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [testddl]> desc emp;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| eid    | int(5)      | NO   | PRI | NULL    |       |
| ename  | varchar(50) | YES  |     | NULL    |       |
| sal    | int(5)      | YES  |     | NULL    |       |
| deptno | int(5)      | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.001 sec)

MariaDB [testddl]> DELETE FROM `emp`;
Query OK, 0 rows affected (0.000 sec)

MariaDB [testddl]> TRUNCATE TABLE `dept`;
Query OK, 0 rows affected (0.003 sec)

MariaDB [testddl]> show tables;
+-------------------+
| Tables_in_testddl |
+-------------------+
| dept              |
| emp               |
+-------------------+
2 rows in set (0.000 sec)

MariaDB [testddl]> DROP TABLE `emp`;
Query OK, 0 rows affected (0.002 sec)

MariaDB [testddl]> show tables;
+-------------------+
| Tables_in_testddl |
+-------------------+
| dept              |
+-------------------+
1 row in set (0.000 sec)

MariaDB [testddl]> show schemas;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| testddl            |
+--------------------+
5 rows in set (0.000 sec)

MariaDB [testddl]> DROP DATABASE `testddl`;
Query OK, 1 row affected (0.004 sec)

MariaDB [(none)]> show schemas;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.000 sec)

Congratulations! You have completed the DDL Comprehensive Challenge. You have learned how to create a database, create tables, modify table structures, delete data from tables, and drop tables and databases.********

Summary

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