MySQL Views CREATE VIEW Updatable Views: Tutorial, Examples, FAQs & Interview Tips

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 tl-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 tl-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 tl-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 tl-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 tl-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 tl-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';

Previous Next

MySQL Views CREATE VIEW Updatable Views: Tutorial, Examples, FAQs & Interview Tips

What is a View?

CREATE OR REPLACE VIEW and Updatable Views

Level Up Your My sql Skills

Popular Tutorials

Ready to Level Up Your Skills?