How do you create and execute views in MySQL?

In MySQL, creating and executing views is a highly effective method to simplify complex queries while also enhancing data access security. Below, I will guide you through specific steps and examples to explain how to create and execute views.

Step 1: Creating Views

To create a view in MySQL, use the CREATE VIEW statement. The basic syntax is as follows:

sql
CREATE VIEW view_name AS
SELECT column_names
FROM table_name
WHERE conditions;

Example:

Suppose we have a table named Employees that stores employee IDs, names, departments, and salaries. Now, we want to create a view to display the names and departments of all employees with salaries exceeding 3000.

sql
CREATE VIEW HighSalaryEmployees AS
SELECT Name, Department
FROM Employees
WHERE Salary > 3000;

This view HighSalaryEmployees now contains the names and departments of all employees with salaries over 3000.

Step 2: Executing Views

After creating a view, you can query it just like a regular table. Use the SELECT statement to access the data within the view.

Example:

To retrieve the names and departments of all employees with salaries above 3000, execute the following query:

sql
SELECT * FROM HighSalaryEmployees;

This query returns all rows from the view HighSalaryEmployees, which includes the names and departments of all employees with salaries exceeding 3000.

Step 3: Updating or Deleting Views

If you need to modify the structure or conditions of a view, use the ALTER VIEW statement. To delete a view, use the DROP VIEW statement.

Modifying a View:

sql
ALTER VIEW HighSalaryEmployees AS
SELECT Name, Department, Salary
FROM Employees
WHERE Salary > 3000 AND Department = 'IT';

Deleting a View:

sql
DROP VIEW HighSalaryEmployees;

Summary

Creating and executing views can simplify database query operations, making data management more efficient and secure.