Views

Code

-- Create a view
CREATE VIEW active_customers AS
SELECT c.id, c.name, c.email, COUNT(o.id) AS order_count
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE c.active = 1
GROUP BY c.id;

-- Query it like a table
SELECT * FROM active_customers WHERE order_count > 5;

-- Updatable view (no aggregate, no JOIN limits)
CREATE VIEW customer_emails AS
SELECT id, email FROM customers;
UPDATE customer_emails SET email = '[email protected]' WHERE id = 1;

-- Replace and drop
CREATE OR REPLACE VIEW active_customers AS SELECT id, name FROM customers;
DROP VIEW active_customers;

Explanation

Views store a SELECT statement as a virtual table, simplifying complex joins and providing a security layer by hiding columns. Simple views are updatable, allowing INSERT/UPDATE/DELETE to flow through to the underlying tables. CREATE OR REPLACE updates the view definition without dropping it first.

More MySQL Snippets