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:

emp_id name department salary
1 Alice HR 5000
2 Bob IT 6000
3 Carol IT 7000
4 Dave HR 5500

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:

emp_id name salary
2 Bob 6000
3 Carol 7000

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