MySQL – CREATE VIEW

In MySQL, a view is a virtual table created as a saved query result that can be used like a regular table in SELECT, INSERT, UPDATE, and DELETE statements. Views allow you to encapsulate complex queries, simplify data access, and provide a level of abstraction over the underlying data. Here’s an example of how to use the CREATE VIEW statement in MySQL:

Let’s assume we have a table called “employees” with the following structure:

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department VARCHAR(100),
    hire_date DATE
);

Now, let’s create a view that includes only the names and department of employees hired after a certain date:

CREATE VIEW recent_employees AS
SELECT first_name, last_name, department
FROM employees
WHERE hire_date >= '2023-01-01';

In this example, we created a view named “recent_employees.” This view is based on a SELECT statement that retrieves the first name, last name, and department of employees from the “employees” table who were hired on or after January 1, 2023. The view does not store any data itself; it simply provides a virtual representation of the data that matches the query results.

Now, you can use the “recent_employees” view in SELECT queries as if it were a regular table:

SELECT * FROM recent_employees;

This query will return the names and departments of employees hired after January 1, 2023, as defined by the view.

Views are particularly useful when you have complex queries that need to be reused frequently. They can simplify your application code and provide a more straightforward and standardized way to access specific subsets of data without exposing the underlying table structure.

Keep in mind that a view is only a virtual representation of the data. Any changes made to the underlying table will be reflected in the view’s results, as the view is dynamically updated when queried. Also, remember to grant appropriate privileges to users who need access to the view, just like with regular tables.