MySQL Views CREATE VIEW Updatable Views

What is a View?

A view is a named, stored SELECT query that acts like a virtual table. When you query a view, MySQL executes the underlying SELECT and returns the result. Views don't store data themselves - they're a saved query definition. Key advantages:

Simplification - Wrap complex multi-table JOINs into a simple view that developers query like a table.
Security - Grant users access to a view without exposing the underlying tables or sensitive columns.
Reusability - Define business logic once in a view and reuse it across multiple queries and applications.
Abstraction - Change the underlying table structure without breaking application queries (update the view instead).

CREATE VIEW and SELECT from a View

-- Create a view: customer order summary
CREATE VIEW v_customer_orders AS
SELECT
    c.customer_id,
    CONCAT(c.first_name, ' ', c.last_name) AS customer_name,
    c.email,
    COUNT(o.order_id)                       AS order_count,
    COALESCE(SUM(o.total), 0)               AS total_spent,
    MAX(o.ordered_at)                       AS last_order_date
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.first_name, c.last_name, c.email;

-- Query the view just like a table
SELECT * FROM v_customer_orders ORDER BY total_spent DESC;

-- Filter the view
SELECT customer_name, total_spent
FROM v_customer_orders
WHERE total_spent > 500;

-- Create a view for order details
CREATE VIEW v_order_details AS
SELECT
    o.order_id,
    CONCAT(c.first_name, ' ', c.last_name) AS customer_name,
    p.name                                  AS product_name,
    oi.quantity,
    oi.unit_price,
    (oi.quantity * oi.unit_price)           AS line_total,
    o.status,
    o.ordered_at
FROM orders o
INNER JOIN customers   c  ON o.customer_id  = c.customer_id
INNER JOIN order_items oi ON o.order_id     = oi.order_id
INNER JOIN products    p  ON oi.product_id  = p.product_id;

CREATE OR REPLACE VIEW and Updatable Views

An updatable view allows INSERT, UPDATE, and DELETE operations that pass through to the underlying table. A view is updatable if it references a single table, doesn't use DISTINCT, GROUP BY, HAVING, UNION, or aggregate functions.

CREATE OR REPLACE, Updatable Views, WITH CHECK OPTION

-- CREATE OR REPLACE: update an existing view without dropping it first
CREATE OR REPLACE VIEW v_active_customers AS
SELECT customer_id, first_name, last_name, email, phone
FROM customers
WHERE active = 1;

-- Updatable view: INSERT/UPDATE/DELETE pass through to the base table
INSERT INTO v_active_customers (first_name, last_name, email, phone)
VALUES ('Grace', 'Lee', 'grace@example.com', '555-0200');
-- This inserts into the customers table with active=1 (default)

UPDATE v_active_customers
SET phone = '555-0201'
WHERE customer_id = 10;

-- WITH CHECK OPTION: prevent modifications that would make the row
-- invisible through the view (i.e. violate the WHERE condition)
CREATE OR REPLACE VIEW v_active_customers AS
SELECT customer_id, first_name, last_name, email, active
FROM customers
WHERE active = 1
WITH CHECK OPTION;

-- This UPDATE would fail because it sets active=0, making the row
-- invisible through the view (violates WITH CHECK OPTION)
-- UPDATE v_active_customers SET active = 0 WHERE customer_id = 1;

SHOW VIEWS, ALTER VIEW, DROP VIEW

-- List all views in the current database
SHOW FULL TABLES WHERE Table_type = 'VIEW';

-- Show the definition of a view
SHOW CREATE VIEW v_customer_orders\G

-- ALTER VIEW: redefine a view
ALTER VIEW v_active_customers AS
SELECT customer_id, first_name, last_name, email, phone, created_at
FROM customers
WHERE active = 1;

-- Drop a view
DROP VIEW v_customer_orders;

-- Drop only if it exists
DROP VIEW IF EXISTS v_customer_orders, v_order_details, v_active_customers;

-- Check view metadata in information_schema
SELECT TABLE_NAME, VIEW_DEFINITION, IS_UPDATABLE
FROM information_schema.VIEWS
WHERE TABLE_SCHEMA = 'shop';

Security Boundaries and CHECK OPTION

A view can expose selected columns and rows without granting direct table access, but privileges still depend on SQL SECURITY and the account used for execution. Review the definer account during deployment and avoid a stale or overprivileged definer.

For an updatable filtered view, WITH CHECK OPTION prevents writes through the view from creating rows that immediately disappear from its result. Confirm which joins, aggregates, and expressions make a view non-updatable before exposing it as a write surface.

Dependencies, Performance, and Schema Changes

A normal MySQL view stores a query definition, not a precomputed result. Its underlying joins and filters still execute, so inspect EXPLAIN for the consuming query rather than assuming the view is cached. Avoid deep stacks of views that hide repeated joins and make ownership unclear.

Treat a view definition as deployed schema. Column renames, type changes, and privilege changes can break dependent applications. Version the change, list dependencies, test representative selects and writes, and keep an explicit column list instead of SELECT *.

Before you move on