How to Add, Update, Delete records in MySQL Database?

In this article, we will cover in detail how can we add, update, delete the records in MySQL Database. There are different ways in which you can perform these operations on a table like.

  • By using the command-line client.
  • By using the MYSQL workbench.
  • By using the SQLYOG community.

As a beginner in learning Databases, you should always create the database using the Command Line Client. First, we will look at what is command Line Client of Mysql?

What is the Command Line Client of MySQL?

MySQL Command Line Client is a text-based program that communicates with the server and in that we enter SQL queries and then the response is returned to the display screen itself.

  • Whenever you install MySQL, The Client also gets installed along with it so that you don’t need to install it again.
  • It’s has a black screen which makes it simple to use.
  • You just need to write the SQL queries on it and execute it.

Now we will perform the add, update, delete operation using MySQL CMD. First Open your MySQL Command Line Client (Go to Start> Type MySQL Command Line Client>Open it ). In order to perform this operation first, we will follow the below steps.

  • Create a Database
mysql> create database codebun;
Query OK, 1 row affected (0.26 sec)
  • Use the Database and create a table
mysql> use codebun;
mysql> create table staff (id int auto_increment primary key,
    -> name varchar(25) not null,
    -> city varchar(25) not null,
    -> email varchar(25) not null)
    -> ;
Query OK, 0 rows affected (1.23 sec)
mysql> desc staff;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int         | NO   | PRI | NULL    | auto_increment |
| name  | varchar(25) | NO   |     | NULL    |                |
| city  | varchar(25) | NO   |     | NULL    |                |
| email | varchar(25) | NO   |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.36 sec)
  • Add Data to the Tables and fire select query to view the records.

we are using INSERT query of SQL to insert the records into tables and this can be done in the following two ways.

//This is one way we can insert the record
mysql> insert into staff values (null,"Nicolas","London","nic@123.com");
Query OK, 1 row affected (0.21 sec)

//This is another way we can insert the record
mysql> insert into staff (id,name,city,email) values (null,"John","London","john@123.com");
Query OK, 1 row affected (0.20 sec)

mysql> select * from staff;
+----+---------+--------+--------------+
| id | name    | city   | email        |
+----+---------+--------+--------------+
|  1 | Nicolas | London | nic@123.com  |
|  2 | John    | London | john@123.com |
+----+---------+--------+--------------+
2 rows in set (0.13 sec)
  • Update the record

If we want to perform some editing we can use the UPDATE query of SQL. Here we are updating the city of a person with id.

mysql> update staff set city = "Scotland" where id = 2;
Query OK, 1 row affected (0.10 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from staff;
+----+---------+----------+--------------+
| id | name    | city     | email        |
+----+---------+----------+--------------+
|  1 | Nicolas | London   | nic@123.com  |
|  2 | John    | Scotland | john@123.com |
+----+---------+----------+--------------+
2 rows in set (0.02 sec)
  • Delete the record

Sometimes, You want to remove the records, so you can use the DELETE query of SQL to delete the records of the table. Here we are deleting the id = 2.

mysql> delete from staff where id = 2;
Query OK, 1 row affected (0.24 sec)

mysql> select * from staff;
+----+---------+--------+-------------+
| id | name    | city   | email       |
+----+---------+--------+-------------+
|  1 | Nicolas | London | nic@123.com |
+----+---------+--------+-------------+
1 row in set (0.00 sec)

Following is the screenshot of all the steps done above.

Now, we will look at how to perform this operation using the MYSQL workbench.

What is MYSQL Workbench?

MySQL Workbench provides a proper GUI for the developers to work on the tables like creating, updating, deleting, and so on.

  • It not only supports this functionality but also has all the Administrator tools for server configuration, maintaining the database and etc.
  • Whenever you install MySQL, MySQL Workbench also gets installed along with it so that you don’t need to install it again.

Now we will perform the add, update, delete operation using MySQL Workbench. First, launch your MySQL Workbench (Go to Start> Type MySQL Workbench>Open it ). In order to perform this operation first, we will follow the below steps.

  • Create a database schema

First Go to the navigation tab, click on the database icon as shown on the following screen.

Now, a new screen will open and type the name of the database in the name field, and click Apply.

Now, One Apply SQL Script window will open click on the apply button, and once again one pop-up screen will appear just click Finish.

Now, You will be able to see the database you created under the schemas section on the left-hand side of your screen. (If you are not able to see the database click on the refresh button)

  • Create a table inside the database you just created now.

Expand your database and right-click on the Table and click on create Table

Now, One window will be open and fill in the details like Table_name, Column name and check the box if you want the primary key, unique key, and click on Apply button. Now, one window will open click apply once again and then click on Finish you can see under your schema your table is created.

If you want to see the data from the table click on the option below(arrow sign)

  • Add a record in the table

When you click on the option above you will get the table data. Now you can just easily add the records inside it and click on Apply > Apply > Finish.

  • Update a record

If you want to update the record just edit the fields and click on Apply>Apply>Finish.

  • Delete a record

Select the row and right-click on it and choose the option Delete a Row and click Apply>Apply>Finish. You can delete multiple rows by selecting multiple rows. Now, you can see the data is deleted successfully.

Thus, this is How we can add, update, delete the records from the database using MySQL Command Line & MYSQL Workbench.