MySQL - What is a View

1. What is a View?

A View is a virtual table in MySQL that is based on the result of a SELECT query.

• It does not store data physically (unless it’s a materialized view in other DBMS).

• It simplifies complex queries, improves security, and can provide a specific perspective of the data.

Think of it as a saved query that behaves like a table.

2. Why Use Views?

1. Simplify queries: Reuse complex joins or aggregations without rewriting.

2. Security: Limit user access to certain columns or rows.

3. Abstraction: Hide complexity of the underlying tables.

4. Consistency: Ensure all users query the same pre-defined dataset.

3. Creating a View

Syntax

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

Example

Suppose we have a table employees:

We want a view showing only IT employees:

CREATE VIEW it_employees AS
SELECT emp_id, name, salary
FROM employees
WHERE department = 'IT';

4. Using a View

Once created, you can query it like a normal table:

SELECT * FROM it_employees;

Result:

5. Updating a View

• Some views are updatable, meaning you can INSERT, UPDATE, or DELETE through the view.

• Only allowed if the view references one table and does not contain joins, aggregates, or GROUP BY.

UPDATE it_employees
SET salary = 6500
WHERE emp_id = 2;

• This will update the underlying employees table.

6. Modifying a View

CREATE OR REPLACE VIEW it_employees AS
SELECT emp_id, name, salary
FROM employees
WHERE department = 'IT' AND salary > 6000;

7. Deleting a View

DROP VIEW it_employees;

8. Advantages of Views

• Simplifies repetitive queries

• Provides security by exposing only selected columns

• Hides complexity of joins or calculations

• Makes maintenance easier