Insert, update and delete data rows using MySQL Workbench. For this, we need a table.
So, let us first create a database called the school and then create a table called
students within the school database. Then we will see how to insert, update, delete
records from the Students table. So, please use the below SQL Script to create the
school database and Students table.
CREATE DATABASE School;
USE School;
CREATE TABLE `School`.`Students`(
StudentId INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Class VARCHAR(50),
Age INT
);
Once you execute the above scripts, then the School database and Students table will
be created. Under the SCHEMAS menu, select the database name i.e. School, expand
the tables folder, and you can see the table Students that we created as shown in the
below image.
When we hover the mouse pointer, over the ‘Students’ table name, then you can see
three icons appears. The first icon will give you information about the table. If you want
to update the table schema like adding a new column(s), updating an existing column,
changing data type, or deleting a column, then you need to click on the second icon. The
third icon shows you the table data. Click on the third table icon as shown in the below
image.
Once you click on the table icon i.e. the third icon, it which will open a new window, in
which the upper section shows the MySQL statement, while the lower section shows the
data rows present in the Students table. At this moment, we don’t have any data row in
the Students table. So, the table shows NULL data row values as shown in the below
image.
How to Insert Data Rows to MySQL Database table using MySQL
Workbench?
To enter a new data row, just select the respected column and type the data value. It’s
something similar to the Microsoft Excel spreadsheet. Enter a couple of data rows and
click on the Apply button as shown in the below image. Here, I am going to insert three
records.
Once you click on the Apply button, it will open the below Apply SQL Script to Database
window. Simply verify the SQL Script and if everything fine, then click on the Apply
button to save the data rows as shown in the below image.
Once you click on the Apply button, it will open the following popup, simply click on the
click Finish button as shown in the below image.
We can also import the data rows from an external ‘CSV file with a similar table structure
and data columns and that we will discuss in our next article.
How to Update Data Rows using MySQL Workbench?
We can edit the previously saved data row using MySQL Workbench. Let’s say we want
to update the FirstName as Preety and Age as 16 of Student whose StudentId is 2. Then
you can update the data directly here and then click on the Apply button as shown in the
below image.
Once you click on the Apply button, it will open the following Apply SQL Script to
Database window and if you further notice this time, it generates an SQL update
statement and Click on the Apply button save the changes to the database as shown in
the below image.
Once you click on the Apply button, it will open the below window, and here simply click
on the Finish button as shown in the below image.
How to Delete Data Rows using MySQL Workbench?
To delete an individual row from the table, select the data row, right-click the ‘right icon’
in-front of the row and select the ‘delete row’ option. For example, if you want to delete
the student whose id is 3, then select the third row and then right-click on it and click on
the Delete Row(s) as shown in the below image.
This will delete the row immediately from the GUI. To delete the row from the database,
click on the Apply button as shown in the below image.
Once you click on the Apply button, it will open the below window with the Delete
statement. Click on the Apply button to save the changes into the database as shown in
the below image.
Once you click on the Apply, it will open the below window, simply click on the Finish
button.
As of now, we have seen how to create, update and delete rows using the GUI provided
by MySQL Workbench. It is also possible in MySQL workbench, to create, update and
delete data rows using raw SQL script.
Insert, Update, Delete using SQL Script in MySQL Workbench:
Let us see how to perform insert, update and delete operations using SQL Script in
MySQL Workbench. First, open a new query tab and then execute the following SQL
Script which will Insert two records into the Students table of the School database.
INSERT INTO `school`.`students` (StudentId, FirstName, LastName,
Class, Age)
VALUES (3, 'Hina', 'Sharma', 'First', 18);
INSERT INTO `school`.`students` (StudentId, FirstName, LastName,
Class, Age)
VALUES (4, 'Rakesh', 'Sahoo', 'Second', 17);
Now, we want to update the FirstName of the Student whose Id is 3 and LastName of
the Student whose Id is 4. Then the following Update SQL Script will update the same in
the Students table.
UPDATE `school`.`students` SET `FirstName` = 'Suresh' WHERE
(`StudentId` = '3');
UPDATE `school`.`students` SET `LastName` = 'Mohanty' WHERE
(`StudentId` = '4');
Now, we want to delete the student whose IDs are 3 and 4. Then the following DELETE
SQL Script will delete the same from the Students table.
DELETE FROM `school`.`students` WHERE (`StudentId` = '3');
DELETE FROM `school`.`students` WHERE (`StudentId` = '4');
In the next article, I am going to discuss Database Export and Import using MySQL
Workbench. Here, in this article, I try to explain How to Insert, Update, Delete Data
Rows using MySQL Workbench and I hope you enjoy this Insert, Update, Delete Data
Rows using MySQL Workbench article.