Learn MySQL: Create, Update, and Delete Data in Database

A database is a structured set of data stored electronically. The concept of a database was known to our ancestors even when there were no computers. However, creating and maintaining such databases was a very tedious job. For instance, in a manual database of 100 pages, searching for all employees whose salaries were less than 10,000 would have been quite difficult.

In today’s world, you cannot escape databases. Right now, millions of databases are working around the world to store and fetch data of every kind, whether it be strategic data, employee records, or web technologies.

Databases are often termed as backend processes because they are neither visible to end users nor do end users interact directly with the database. Instead, they work on frontend processes like PHP, VB, ASP.NET, etc., and ask the frontend to deal with the database in the backend.

There are several database servers and clients available, such as Oracle, MySQL, MySQLi, MariaDB, and MongoDB. The syntax for all of these is more or less the same. Mastering one means gaining control over most of them, and learning the queries of a database is both easy and enjoyable.

Let’s start with simple queries on databases. We will use MySQL, which comes bundled with most Linux distributions by default. You can install it manually from the repository if it is not installed by default in your case.

A database query is a simple piece of code sent to the database to obtain custom and refined results as required.

Install MySQL Database in Linux

Use the “yum” or “apt” package manager to install the MySQL database.

sudo yum install mysql mysql-client mysql-server  (on Yum-based systems)
sudo apt install mysql mysql-client mysql-server  (on Apt-based systems)

Once installed, start the MySQL database service with:

sudo systemctl start mysqld
Or
sudo systemctl start mysql

Installing a fresh copy of the MySQL database will take you to a configuration step where you will be asked to set up an root password and answer questions regarding security settings.

sudo mysql_secure_installation

Once you finish installing and securing the server, go to your MySQL prompt.

sudo mysql -u root -p

Now, executing queries at this prompt is both educational and enjoyable.

Create a MySQL Database

Create a database named “tecmint“.

create database tecmint;

Note: The message indicates that the query was successful, meaning the database is created.

You can verify your newly created database by running:

show databases; 

Notice your database in the output below.

Create Tables in MySQL Database

Now you need to select the database to work on:

use tecmint;

Here we will create a table called “minttec” with three fields:

CREATE TABLE minttec (
    id INT(3), 
    first_name VARCHAR(15), 
    email VARCHAR(20)
);

Note: The above query returns “OK“, indicating that the table was created without any errors.

To verify the table, run the following query:

show tables; 

You can view the columns you created in the “minttec” table as follows:

Anyway, let me explain the types of declarations and their meanings.

• INT is an Integer.
• VARCHAR is a character type with a variable length as defined. The value after the type indicates the maximum length of the field in which it can store data.

Add Columns in MySQL Database

Now, we need to add a column named ‘last_name‘ after the column ‘first_name‘:

ALTER TABLE minttec ADD last_name VARCHAR(20) AFTER first_name;

Verify the change in your table:

show columns from minttec; 

Now we will add a column named ‘country‘ to the right of the email field:

ALTER TABLE minttec ADD country VARCHAR(15) AFTER email; 

Verify the column addition:

show columns from minttec; 

Insert Values into Fields of MySQL Database

Now let’s insert values into the fields:

INSERT INTO minttec VALUES ('1', 'Ravi', 'Saive', '[email protected]', 'India');

Now let’s insert multiple values at once into the table.

INSERT INTO minttec (id, first_name, last_name, email, country) VALUES 
  ('2', 'Narad', 'Shrestha', '[email protected]', 'India'), 
  ('3', 'user', 'singh', '[email protected]', 'Aus'), 
  ('4', 'tecmint', 'com', '[email protected]', 'India');

Verify the inserted values:

select * from minttec; 

The values have been inserted successfully.

Update Values in MySQL Table

Now, how about changing the last name of the user whose first name is “Narad“?

UPDATE minttec SET last_name = 'Shrestha' WHERE first_name = 'Narad';

Check to verify the changes.

select * from minttec; 

Delete Values from MySQL Table

What about deleting a row from the table? For example, let’s delete the last entry of the user whose first name is “tecmint“.

DELETE FROM minttec WHERE first_name = 'tecmint';

Now, check the entries after the deletion.

select * from minttec; 

Now, as you can see, the user “tecmint” has been deleted successfully.

Rename Table in MySQL

To rename a table in MySQL, you can use the RENAME TABLE statement. Let’s say you want to rename the table minttec to users. You would run the following command.

RENAME TABLE minttec TO users;

After renaming the table, you can verify the change by listing the tables in your current database:

SHOW TABLES;

Backup a Database

To back up a MySQL database, you can use the mysqldump command, which creates a logical backup by generating a SQL script file containing all the commands to recreate the database.

mysqldump -u root -p tecmint > tecmint_backup.sql

You can verify that the backup file was created by listing the files in your current directory:

ls -l tecmint_backup.sql

Restore a Database

To restore a MySQL database from a backup file, you can use the mysql command.

mysql -u root -p tecmint < tecmint_backup.sql

Make sure the database you are trying to restore to already exists. If it doesn’t, you can create it using:

mysql -u root -p -e "CREATE DATABASE tecmint;"

Conclusion

You have now learned the basics of creating and manipulating a MySQL database, including creating a database, creating tables, inserting records, updating records, deleting records, and dropping tables. This foundation is essential for working with databases effectively.