Mastering Database Modifications and Deletions

Introduction

In today's lab, we will learn and practice how to modify, delete, rename the database table and the contents of the table and other operations.

Learning Objective

Database operations
Data table operations
Update and delete data

Preparation

Before we start, we need to prepare the environment.

Start MySQL service and log in as root.

cd ~/project
sudo service mysql start
mysql -u root

Load data in the file. You need to enter the command in the MySQL console to build the database:

source ~/project/init-database.sql

Modify database

Use the command SHOW DATABASES; to see the databases:

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| mysql_labex        |
| performance_schema |
| sys                |
| test_01            |
+--------------------+
6 rows in set (0.000 sec)

Now we delete the test_01 database by running the command below:

DROP DATABASE test_01;

Run the command SHOW DATABASES again and we'll see test_01 database is deleted successfully.

MariaDB [(none)]> DROP DATABASE test_01;
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| mysql_labex        |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.000 sec)

Modify a Table

Rename

There are multiple ways to rename a table. The three formats listed below have the same result:

RENAME TABLE <originalname> TO <newname>;

ALTER TABLE <originalname> RENAME <newname>;

ALTER TABLE <originalname> RENAME TO <newname>;

Use the command to change table_1 totable_2 :

USE mysql_labex;
SHOW TABLES;
RENAME TABLE table_1 TO table_2;
SHOW TABLES;

MariaDB [(none)]> USE mysql_labex;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [mysql_labex]> SHOW TABLES;
+-----------------------+
| Tables_in_mysql_labex |
+-----------------------+
| department            |
| employee              |
| project               |
| table_1               |
+-----------------------+
4 rows in set (0.000 sec)

MariaDB [mysql_labex]> RENAME TABLE table_1 TO table_2;
Query OK, 0 rows affected (0.002 sec)

MariaDB [mysql_labex]> SHOW TABLES;
+-----------------------+
| Tables_in_mysql_labex |
+-----------------------+
| department            |
| employee              |
| project               |
| table_2               |
+-----------------------+
4 rows in set (0.000 sec)

Delete a table

Similar to the statement we just used to delete the database, here's how we delete a table:

DROP TABLE <table name>;

For example, let's delete table_2:

DROP TABLE table_2;

MariaDB [mysql_labex]> DROP TABLE table_2;
Query OK, 0 rows affected (0.002 sec)

MariaDB [mysql_labex]> SHOW TABLES;
+-----------------------+
| Tables_in_mysql_labex |
+-----------------------+
| department            |
| employee              |
| project               |
+-----------------------+
3 rows in set (0.000 sec)

Modify a Column (Modify Table Format)

How to modify table format is the tricky part of this lab. Sometimes a tiny mistake may result in a big margin of error. So please be patient and meticulous.

Add a Column

Here's the statement format to add a column:

ALTER TABLE <tablename> ADD COLUMN <columnname> <datatype> <constraint>;

Or:  ALTER TABLE <tablename> ADD <columnname> <datatype> <constraint>;

We have 6 columns in the employee table: id name age salary phone in_dpt. Let's add a height column and assign the DEFAULT constraint:

ALTER TABLE employee ADD COLUMN height INT(4) DEFAULT 170;

MariaDB [mysql_labex]> ALTER TABLE employee ADD COLUMN height INT(4) DEFAULT 170;
Query OK, 0 rows affected (0.003 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [mysql_labex]> SELECT * FROM employee;
+----+------+------+--------+--------+--------+--------+
| id | name | age  | salary | phone  | in_dpt | height |
+----+------+------+--------+--------+--------+--------+
|  1 | Tom  |   26 |   2500 | 119119 | dpt4   |    170 |
|  2 | Jack |   24 |   2500 | 120120 | dpt2   |    170 |
|  3 | Rose |   22 |   2800 | 114114 | dpt3   |    170 |
|  4 | Jim  |   35 |   3000 | 100861 | dpt1   |    170 |
|  5 | Mary |   21 |   3000 | 100101 | dpt2   |    170 |
|  6 | Alex |   26 |   3000 | 123456 | dpt1   |    170 |
+----+------+------+--------+--------+--------+--------+
6 rows in set (0.000 sec)

We can see that the newly added column is placed at the very right by default. If we want to move it to a specified place, then at the end of the statement, we need to use the AFTER keyword ("AFTER column 1" means the new column will be placed after "column 1")

For example, we add a new column weight after age:

ALTER TABLE employee ADD COLUMN weight INT(4) DEFAULT 120 AFTER age;

MariaDB [mysql_labex]> ALTER TABLE employee ADD COLUMN weight INT(4) DEFAULT 120 AFTER age;
Query OK, 0 rows affected (0.004 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [mysql_labex]> SELECT * FROM employee;
+----+------+------+--------+--------+--------+--------+--------+
| id | name | age  | weight | salary | phone  | in_dpt | height |
+----+------+------+--------+--------+--------+--------+--------+
|  1 | Tom  |   26 |    120 |   2500 | 119119 | dpt4   |    170 |
|  2 | Jack |   24 |    120 |   2500 | 120120 | dpt2   |    170 |
|  3 | Rose |   22 |    120 |   2800 | 114114 | dpt3   |    170 |
|  4 | Jim  |   35 |    120 |   3000 | 100861 | dpt1   |    170 |
|  5 | Mary |   21 |    120 |   3000 | 100101 | dpt2   |    170 |
|  6 | Alex |   26 |    120 |   3000 | 123456 | dpt1   |    170 |
+----+------+------+--------+--------+--------+--------+--------+
6 rows in set (0.000 sec)

You should get a result like the figure above. If we want to make it the first column, then we will use the FIRST keyword.

ALTER TABLE employee ADD test INT(10) DEFAULT 11 FIRST;

MariaDB [mysql_labex]> ALTER TABLE employee ADD test INT(10) DEFAULT 11 FIRST;
Query OK, 0 rows affected (0.004 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [mysql_labex]> SELECT * FROM employee;
+------+----+------+------+--------+--------+--------+--------+--------+
| test | id | name | age  | weight | salary | phone  | in_dpt | height |
+------+----+------+------+--------+--------+--------+--------+--------+
|   11 |  1 | Tom  |   26 |    120 |   2500 | 119119 | dpt4   |    170 |
|   11 |  2 | Jack |   24 |    120 |   2500 | 120120 | dpt2   |    170 |
|   11 |  3 | Rose |   22 |    120 |   2800 | 114114 | dpt3   |    170 |
|   11 |  4 | Jim  |   35 |    120 |   3000 | 100861 | dpt1   |    170 |
|   11 |  5 | Mary |   21 |    120 |   3000 | 100101 | dpt2   |    170 |
|   11 |  6 | Alex |   26 |    120 |   3000 | 123456 | dpt1   |    170 |
+------+----+------+------+--------+--------+--------+--------+--------+
6 rows in set (0.001 sec)

Delete a Column

We only need to change the keyword ADD to DROP. No data type, constraint, or place info.

ALTER TABLE <tablename> DROP COLUMN <columnname>;

Or:  ALTER TABLE <tablename> DROP <Columnname>;

Let's delete the test:

ALTER TABLE employee DROP COLUMN test;

MariaDB [mysql_labex]> ALTER TABLE employee DROP COLUMN test;
Query OK, 0 rows affected (0.003 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [mysql_labex]> SELECT * FROM employee;
+----+------+------+--------+--------+--------+--------+--------+
| id | name | age  | weight | salary | phone  | in_dpt | height |
+----+------+------+--------+--------+--------+--------+--------+
|  1 | Tom  |   26 |    120 |   2500 | 119119 | dpt4   |    170 |
|  2 | Jack |   24 |    120 |   2500 | 120120 | dpt2   |    170 |
|  3 | Rose |   22 |    120 |   2800 | 114114 | dpt3   |    170 |
|  4 | Jim  |   35 |    120 |   3000 | 100861 | dpt1   |    170 |
|  5 | Mary |   21 |    120 |   3000 | 100101 | dpt2   |    170 |
|  6 | Alex |   26 |    120 |   3000 | 123456 | dpt1   |    170 |
+----+------+------+--------+--------+--------+--------+--------+
6 rows in set (0.000 sec)

Rename a Column

(CHANGE) can do more than just rename a column. To be more precise, this statement can modify a column.

ALTER TABLE <tablename> CHANGE <oldcolumnname> <newcolumnname> <datatype> <constraint>;

Please note: if you forget to include "data type", then rename will fail.

When the original column name is the same as the new column name, assign the new data type or constraint can change its data type and constraint. Pay attention here - changing data type might lead to data loss so be careful.

Let's rename the "height" column to shengao:

ALTER TABLE employee CHANGE height shengao INT(4) DEFAULT 170;

MariaDB [mysql_labex]> ALTER TABLE employee CHANGE height shengao INT(4) DEFAULT 170;
Query OK, 0 rows affected (0.005 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [mysql_labex]> SELECT * FROM employee;
+----+------+------+--------+--------+--------+--------+---------+
| id | name | age  | weight | salary | phone  | in_dpt | shengao |
+----+------+------+--------+--------+--------+--------+---------+
|  1 | Tom  |   26 |    120 |   2500 | 119119 | dpt4   |     170 |
|  2 | Jack |   24 |    120 |   2500 | 120120 | dpt2   |     170 |
|  3 | Rose |   22 |    120 |   2800 | 114114 | dpt3   |     170 |
|  4 | Jim  |   35 |    120 |   3000 | 100861 | dpt1   |     170 |
|  5 | Mary |   21 |    120 |   3000 | 100101 | dpt2   |     170 |
|  6 | Alex |   26 |    120 |   3000 | 123456 | dpt1   |     170 |
+----+------+------+--------+--------+--------+--------+---------+
6 rows in set (0.000 sec)

Change Data Type

Other than the CHANGE statement we learnd earlier, we can also use the MODIFY statement:

ALTER TABLE <tablename> MODIFY <columnname> <newdatatype>;

Attention: be aware of potential data loss when you modify data type. Be cautious.

Modify Table Content

Modify a specific value

Most of the time, we want to change a single value or multiple values instead of the entire table or database. Then we will use the statement below:

UPDATE <tablename> SET <column 1> = <value> 1, <column 2> = <value 2> WHERE <condition>;

For example, change Tom's age to 21, and his salary to 3,000:

UPDATE employee SET age = 21, salary = 3000 WHERE name = 'Tom';

MariaDB [mysql_labex]> SELECT * FROM employee WHERE name = 'Tom';
+----+------+------+--------+--------+--------+--------+---------+
| id | name | age  | weight | salary | phone  | in_dpt | shengao |
+----+------+------+--------+--------+--------+--------+---------+
|  1 | Tom  |   26 |    120 |   2500 | 119119 | dpt4   |     170 |
+----+------+------+--------+--------+--------+--------+---------+
1 row in set (0.000 sec)

MariaDB [mysql_labex]> UPDATE employee SET age = 21, salary = 3000 WHERE name = 'Tom';
Query OK, 1 row affected (0.000 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [mysql_labex]> SELECT * FROM employee WHERE name = 'Tom';
+----+------+------+--------+--------+--------+--------+---------+
| id | name | age  | weight | salary | phone  | in_dpt | shengao |
+----+------+------+--------+--------+--------+--------+---------+
|  1 | Tom  |   21 |    120 |   3000 | 119119 | dpt4   |     170 |
+----+------+------+--------+--------+--------+--------+---------+
1 row in set (0.000 sec)

Please note: make sure you have the WHERE condition, otherwise failure occurs

Delete a row

Make sure the WHERE condition is included when you delete a row, otherwise, the entire column data will be deleted:

DELETE FROM <tablename> WHERE <condition>;

For example, delete Tom's data:

DELETE FROM employee WHERE name = 'Tom';

MariaDB [mysql_labex]> DELETE FROM employee WHERE name = 'Tom';
Query OK, 1 row affected (0.001 sec)

MariaDB [mysql_labex]> SELECT * FROM employee WHERE name = 'Tom';
Empty set (0.000 sec)

Summary

Congratulations! You have successfully completed the lab on modifying and deleting data from a MySQL database.

Modify and Delete