MySQL Views

What Is a View?

A view is a named, saved SELECT statement that you can query as if it were an ordinary table. MySQL does not store the view's result rows on disk (unless you use a materialized approach with a real table); instead, every time you query the view, MySQL re-runs the underlying query against the current data. This means views always reflect the latest state of the base tables.

Views are useful for hiding complexity, enforcing consistent business logic, restricting which columns or rows a user can see, and giving a friendlier name to a query that joins several tables.

Creating a View

Use CREATE VIEW followed by a view name and an AS clause containing the query. Column names are inherited from the SELECT list unless you specify them explicitly.

CREATE VIEW active_customers AS
SELECT customer_id, first_name, last_name, email
FROM customers
WHERE status = 'active';

-- Query it exactly like a table
SELECT * FROM active_customers WHERE last_name = 'Nguyen';

CREATE VIEW order_summary AS
SELECT o.order_id,
       c.first_name,
       c.last_name,
       o.order_date,
       SUM(oi.quantity * oi.unit_price) AS order_total
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
JOIN order_items oi ON oi.order_id = o.order_id
GROUP BY o.order_id, c.first_name, c.last_name, o.order_date;

SELECT * FROM order_summary WHERE order_total > 500;

Updating and Removing Views

CREATE OR REPLACE VIEW redefines a view without dropping it first, which preserves any privileges granted on it. DROP VIEW removes a view entirely; it never affects the underlying table data.

CREATE OR REPLACE VIEW active_customers AS
SELECT customer_id, first_name, last_name, email, signup_date
FROM customers
WHERE status = 'active';

DROP VIEW IF EXISTS order_summary;

Updatable Views

Simple views built from a single table without GROUP BY, DISTINCT, aggregate functions, or UNION are updatable: you can run INSERT, UPDATE, and DELETE against them, and MySQL applies the change to the underlying table. Views involving joins, aggregation, or derived columns are generally read-only.

• A view is a saved query, not a copy of the data — it always reflects live rows.
• CREATE OR REPLACE VIEW lets you redefine a view while keeping its granted privileges.
• Single-table views without aggregation are usually updatable through INSERT/UPDATE/DELETE.
• Views can restrict columns (hide sensitive fields) or rows (WHERE clause) for a given audience.
• WITH CHECK OPTION prevents updates through the view from producing rows the view itself wouldn't show.