How to Remove Records from Table With MySQL Workbench?
MySQL Workbench is a unified visual tool designed for developers, database administrators, and database architects. It provides SQL development, data modeling, and administration tools for server configuration, backup, user administration, and many more. In the MySQL database, data is stored in tabular form. Different queries are used for adding, deleting, and updating table records.
In this blog, we will discuss:
- How to Remove Records from Table with MySQL Workbench Using “WHERE” Clause?
- How to Remove Records from Table with MySQL Workbench Using “LIMIT” Clause and “ORDER BY” Clause?
- How to Remove Records from Table with MySQL Workbench Using “LIMIT” Clause and “ORDER BY” Clause With “WHERE” Clause?
How to Remove Records from Table With MySQL Workbench Using “WHERE” Clause?
To delete the records from the table with MySQL Workbench using the “WHERE” clause, follow the below-given instructions.
Step 1: Launch MySQL Workbench
Initially, search and open the “MySQL Workbench” through the Startup menu:
Step 2: Open MySQL Connection
Then, click on the desired MySQL connection and open it:
Step 3: Create New SQL File
Now, create a new SQL file by pressing the below-given file button:
Step 4: Change MySQL Database
Next, execute the “USE” statement to change the database:
Step 5: List Available Tables
Run the “SHOW” statement to list all tables of the current database:
According to the provided output, two tables named “customer” and “mariadb” currently exist in the database:
We want to access the “customer” table content:
Step 6: Display Table Content
To view the selected table content, execute the “SELECT * FROM customer” statement:
Step 7: Delete Record Using WHERE Clause
Finally, remove the desired record of the selected table by running the provided command:
Here:
- “DELETE” clause is used to delete the records from the table.
- “FROM” clause is used to select records from the database table.
- “Customer” is our table name.
- “WHERE” clause filters the particular records that fulfill the specified condition.
- “Id” is our table column name.
- “2” is the student id that needs to be deleted.
As a result, the particular row will be deleted that has the student id as “2”:
Step 8: Verify Deleted Record
To ensure that the provided student record is deleted from the table or not, execute the below-listed command:
It can be seen that the specified record has been deleted from the table:
How to Remove Records from Table with MySQL Workbench Using “LIMIT” Clause and “ORDER BY” Clause?
Another way to remove the rows from the table is by utilizing the “LIMIT” clause and the “ORDER BY” clause with MySQL Workbench. To do so, try out the below-provided instructions.
Step 1: Display Table Content
First, use the “SELECT” statement to show the table content:
Step 2: Delete Rows Using ORDER BY Clause and LIMIT Clause
Now, run the “SELECT” query with the “LIMIT” clause and “ORDER BY” clause:
Here:
- “ORDER BY” clause is utilized for arranging the table rows in ascending order.
- “LIMIT” clause specifies the number of rows/records to return.
- “3” is the specified number of records:
Step 3: Show Table Content
Execute the “SELECT * FROM” command with the table name to display its content and verify the deleted records:
According to the given output, the specified number of records have been deleted:
How to Remove Records from Table with MySQL Workbench Using “LIMIT” Clause and “ORDER BY” Clause With “WHERE” Clause?
We can also use the “LIMIT” clause and the “ORDER BY” clause with the “WHERE” clause to remove the records from the database table. To do so, try out the following instructions.
Step 1: Check Table Content
First, view the table content by running the “SELECT” statement:
Step 2: Remove Content
Now, remove the rows from the table by executing the below-stated command:
Here, we have specified the “WHERE” clause to filter the “country” name as “Mexico” with the “ORDER BY” clause and “LIMIT” clause:
Step 3: Verification
Use the below-given query to verify whether records are deleted from the “customer” table or not:
That’s all! We have provided the process of deleting records from a table with MySQL Workbench.
Conclusion
To remove the records from the table with MySQL Workbench, the “DELETE FROM <tablename> WHERE <condition>;” command, “DELETE FROM <table> ORDER BY <col> LIMIT <number>;” command, and the “DELETE FROM <table> WHERE <condition> ORDER BY <col> LIMIT <number>;” commands are used. This blog demonstrated several ways to remove the records from a table with MySQL Workbench.
Source: linuxhint.com