MySQL Views

A view is a saved SELECT query stored in the database under a name. You query a view exactly like a table — with SELECT, WHERE, and JOIN — but the view itself stores no data. Every time you query the view, MySQL runs the underlying SELECT fresh against the real tables.

Views simplify complex queries, hide implementation details, and can act as a security layer by exposing only certain columns to certain users.

CREATE VIEW

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

Example — a view that joins customers with their orders and shows only the relevant columns:

CREATE VIEW customer_orders AS
SELECT c.name AS customer, c.city, o.id AS order_id, o.amount
FROM customers AS c
INNER JOIN orders AS o ON c.id = o.customer_id;

Querying a View

Once created, query a view exactly like a table:

SELECT * FROM customer_orders;

-- Filter and sort just as with a real table
SELECT * FROM customer_orders
WHERE city = 'Dhaka'
ORDER BY amount DESC;

CREATE OR REPLACE VIEW

To update a view's definition without dropping and recreating it, use CREATE OR REPLACE VIEW:

CREATE OR REPLACE VIEW customer_orders AS
SELECT c.name AS customer, c.city, o.id AS order_id, o.amount, o.created_at
FROM customers AS c
INNER JOIN orders AS o ON c.id = o.customer_id;

Updatable Views

In some cases you can run INSERT, UPDATE, or DELETE on a view and MySQL will pass the change through to the underlying table. A view is updatable when it meets these conditions:

• It references exactly one table (no joins).
• It does not use DISTINCT, GROUP BY, HAVING, aggregate functions, or subqueries.
• All NOT NULL columns without defaults in the base table are included in the view.

-- A simple updatable view
CREATE VIEW dhaka_customers AS
SELECT id, name, city FROM customers WHERE city = 'Dhaka';

-- This UPDATE modifies the real customers table
UPDATE dhaka_customers SET name = 'Rahim Hossain' WHERE id = 1;

SHOW CREATE VIEW

To inspect the definition of an existing view:

SHOW CREATE VIEW customer_orders;

DROP VIEW

Remove a view from the database. This only removes the saved query — the underlying table data is untouched:

DROP VIEW IF EXISTS customer_orders;

Key Points to Remember

• A view is a saved SELECT query — it stores no data of its own.
• Query a view exactly like a table with SELECT, WHERE, JOIN, etc.
• Use CREATE OR REPLACE VIEW to update a view's definition safely.
• Simple single-table views are updatable — joins, aggregates, and DISTINCT make a view read-only.
• DROP VIEW removes the view definition only — base table data is never affected.