Delete Table's Record in MySQL Workbench

• The `DELETE` statement in MySQL Workbench is used to remove one or more rows from a table based on specified conditions. It's a powerful tool for removing unwanted or obsolete data from a database.

Here's a detailed explanation of how the `DELETE` query works, along with examples:

• Basic Syntax:

    DELETE FROM table_name WHERE condition;

• Consider a "employees" table with the following data:

    CREATE TABLE employees (

        employee_id INT PRIMARY KEY,

        first_name VARCHAR(50),

        last_name VARCHAR(50),

        salary DECIMAL(10, 2)

    );

    INSERT INTO employees (employee_id, first_name, last_name, salary)

    VALUES

        (1, 'John', 'Smith', 50000.00),

        (2, 'Jane', 'Doe', 45000.00),

        (3, 'Michael', 'Johnson', 60000.00),

        (4, 'Emily', 'Brown', 52000.00),

        (5, 'David', 'Lee', 48000.00);

• Deleting a Specific Row: To delete a specific row based on a condition:

    DELETE FROM employees WHERE employee_id = 2;

• This query deletes the employee with "employee_id" 2.

• Deleting Multiple Rows: To delete multiple rows that meet a condition:

    DELETE FROM employees WHERE salary < 48000.00;

• This query deletes employees with a salary less than 48000.00.

• Deleting All Rows: Be cautious when omitting the `WHERE` clause, as it will delete all rows:

    DELETE FROM employees;

• This query deletes all rows from the "employees" table.

• Deleting with Subquery: You can use a subquery to delete based on values from another table:

    DELETE FROM employees WHERE department = 'IT';

• This query deletes employees in the 'IT' department.

• Limiting the Number of Deleted Rows: You can use the `LIMIT` clause to limit the number of rows deleted:

    DELETE FROM employees WHERE salary > 55000.00 LIMIT 1;

• This query deletes the first employee with a salary greater than 55000.00.

• Deleting with Joins: You can use a `DELETE` query with a `JOIN` to delete records from multiple related tables:

    DELETE employees, departments

    FROM employees

    JOIN departments ON employees.department_id = departments.department_id

    WHERE departments.department_name = 'Sales';

• This query deletes employees and their corresponding departments in the 'Sales' department.

• You can delete multiple employees based on their employee IDs by using the IN operator in the WHERE clause of the DELETE query. The IN operator allows you to specify a list of values for a column, and the DELETE query will remove the rows that match any of the specified values. 

Here's how you can delete multiple employees using their employee IDs:

    DELETE FROM employees WHERE employee_id IN (2, 4, 5);

• In this example, the query will delete the employees with employee IDs 2, 4, and 5 from the "employees" table.

• Make sure to adjust the list of employee IDs inside the IN parentheses to match the IDs of the employees you want to delete. Always double-check before executing a DELETE query, as it can have a significant impact on your data. Consider taking backups or testing in a controlled environment before performing such operations in a production database.

• The `DELETE` query can significantly affect your data, so use it carefully. Always provide the appropriate `WHERE` clause to ensure you're targeting the correct rows for deletion. Additionally, consider taking backups before performing bulk delete operations to avoid data loss.