MySQL — Views

Basic View

A view is a stored query that behaves like a table:

CREATE VIEW active_customers AS
SELECT id, name, email FROM customers WHERE status = 'active';

SELECT * FROM active_customers;

View with JOIN

CREATE VIEW order_summary AS
SELECT o.id, c.name, o.total, o.order_date
FROM orders o
JOIN customers c ON o.customer_id = c.id;

SELECT * FROM order_summary WHERE total > 100;

Modifying a View

CREATE OR REPLACE VIEW active_customers AS
SELECT id, name, email, phone FROM customers WHERE status = 'active';

Dropping a View

DROP VIEW IF EXISTS active_customers;

Updatable Views

Simple views can be updated (INSERT, UPDATE, DELETE):

UPDATE active_customers SET email = 'new@email.com' WHERE id = 1;

Views are NOT updatable if they contain:

• Aggregate functions (SUM, COUNT, AVG)
• DISTINCT, GROUP BY, HAVING
• Subqueries
• JOINs

View Advantages

Benefit	Description
Security	Hide columns or rows from users
Simplicity	Complex queries stored as simple names
Consistency	One definition, many consumers
Performance	Can be indexed in MySQL

Indexing a View

CREATE VIEW order_totals AS
SELECT customer_id, SUM(total) AS total_spent
FROM orders GROUP BY customer_id;

CREATE UNIQUE INDEX idx_order_totals ON order_totals(customer_id);

Mini Practice

Write SQL code that:

1. Creates a view that joins two tables
2. Queries the view with a WHERE clause
3. Replaces the view definition with CREATE OR REPLACE
4. Drops the view

Up Next

Continue with Indexes — speeding up data retrieval.