Creating and Managing Views

Learning Focus

Use this lesson to understand MySQL views — virtual tables that simplify complex queries, provide clean data access points, and control what users can see.

Concept Overview

What Is a View?

A view is a saved SELECT query that behaves like a virtual table. It doesn't store data itself — every time you query a view, MySQL executes the underlying SELECT and returns the results.

Think of a view as a window into your data. The data lives in the real tables; the view just gives you a specific way to look at it.

-- This view acts like a table called "active_customers"
CREATE VIEW active_customers AS
SELECT customer_id, full_name, email
FROM customers
WHERE status = 'active';

-- Query it like any table
SELECT * FROM active_customers;

Why Use Views?

Benefit	Explanation
Simplify complex queries	Write a JOIN once in the view, then query it with simple SELECTs
Security and access control	Expose only specific columns to certain users or applications
Consistent business logic	"Active customer" is defined once in the view, not repeated everywhere
API data layers	Backend services query views that return clean, shaped data
Migration safety	When you change the base table, update the view — downstream queries still work

Views Are Not Materialized Tables

MySQL views are not materialized — they don't cache results. Every query re-executes the underlying SELECT. This is important for performance: a view over a complex query on a large table will be just as slow as running that query directly.

---

Basic Syntax & Rules

CREATE VIEW

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

CREATE OR REPLACE VIEW

Update an existing view without DROP + CREATE:

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

View Naming Conventions

Pattern	Example	When to Use
v_<purpose>	v_active_customers	Clearly marks views
<entity>_<qualifier>	customers_active	Reads naturally with the table
rpt_<report>	rpt_monthly_revenue	Reporting views

DROP VIEW

DROP VIEW IF EXISTS active_customers;

SHOW CREATE VIEW

Inspect a view's definition:

SHOW CREATE VIEW active_customers\G

List All Views in a Database

SELECT TABLE_NAME, VIEW_DEFINITION
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_SCHEMA = 'app_db';

---

Step-by-Step Examples

Example 1: Simplify a Complex JOIN

Scenario: Your application frequently needs customer names with their latest order total.

Step 1: The raw query (complex, repeated everywhere)

SELECT
    c.customer_id,
    c.full_name,
    c.email,
    o.order_id,
    o.total_amount,
    o.order_date
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_date = (
    SELECT MAX(o2.order_date)
    FROM orders o2
    WHERE o2.customer_id = c.customer_id
);

Step 2: Create a view

CREATE VIEW v_customer_latest_order AS
SELECT
    c.customer_id,
    c.full_name,
    c.email,
    o.order_id,
    o.total_amount,
    o.order_date
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_date = (
    SELECT MAX(o2.order_date)
    FROM orders o2
    WHERE o2.customer_id = c.customer_id
);

Step 3: Use the view (simple!)

SELECT full_name, total_amount, order_date
FROM v_customer_latest_order
WHERE total_amount > 100
ORDER BY order_date DESC;

Now every developer queries v_customer_latest_order instead of rewriting the JOIN.

---

Example 2: Security — Restricted Column Access

Scenario: Your analytics team needs customer data, but they should not see email addresses or phone numbers.

Step 1: Create a restricted view

CREATE VIEW v_customers_public AS
SELECT customer_id, full_name, city, country, created_at
FROM customers;

Step 2: Grant access only to the view

GRANT SELECT ON app_db.v_customers_public TO 'analytics_user'@'%';
-- No direct access to the customers table

Now the analytics team can query v_customers_public but cannot see email, phone, or other sensitive columns.

---

Example 3: Reporting View with Aggregation

CREATE VIEW rpt_monthly_revenue AS
SELECT
    DATE_FORMAT(order_date, '%Y-%m') AS month,
    COUNT(*)                         AS total_orders,
    SUM(total_amount)                AS revenue,
    AVG(total_amount)                AS avg_order_value
FROM orders
WHERE status = 'paid'
GROUP BY DATE_FORMAT(order_date, '%Y-%m');

-- Dashboard query becomes trivial
SELECT * FROM rpt_monthly_revenue ORDER BY month DESC LIMIT 12;

month	total_orders	revenue	avg_order_value
2026-02	89	28500.00	320.22
2026-01	150	45000.00	300.00

---

Example 4: Updating a View Definition

Requirements change — now you need phone in the active customers view:

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

All queries using active_customers now automatically include phone.

---

Practical Use Cases

1. API Read Models

Backend services query views that return clean, shaped data — decoupling the API from the actual table structure.

2. Multi-Tenant Data Isolation

Create views filtered by tenant_id and grant each tenant access only to their view.

3. Dashboard and Reporting

Pre-define aggregation views so dashboard queries are simple SELECT * statements.

4. Migration Compatibility

When renaming or splitting tables, create a view with the old name pointing to the new structure. Downstream code doesn't break.

5. Denormalized Read Access

Create a view that JOINs normalized tables for easy querying without duplicating data.

---

Common Mistakes & Troubleshooting

Mistake	What Happens	How to Fix
Chaining views (view of a view of a view)	Performance degrades exponentially	Flatten chains — create one view with the final query
Expecting views to cache data	Each query re-executes the underlying SELECT	For heavy aggregations, consider summary tables instead
Ignoring base table schema changes	Adding/dropping columns in base tables can break views	After schema changes, run SHOW CREATE VIEW to verify
Creating views on temporary tables	Views cannot reference temporary tables	Use regular tables or CTEs instead
Not naming views with a convention	Hard to distinguish views from tables	Use a prefix like v_ or rpt_

---

Best Practices

• Use CREATE OR REPLACE — avoids the DROP + CREATE dance
• Name views with a prefix — v_, vw_, or rpt_ to distinguish from tables
• Don't chain views more than one level deep — performance becomes unpredictable
• Document the business logic — add a comment explaining what the view represents
• Review views after schema changes — column additions/removals can silently break views
• Use views for access control — grant SELECT on views, not on base tables

---

Hands-On Practice

Exercise 1: Create a Basic View (Easy)

Create a view called v_products_in_stock that shows product_id, name, price, and stock_quantity for all products where stock_quantity > 0. Query it to verify.

Exercise 2: Reporting View (Medium)

Create a view rpt_daily_sales that shows:

• sale_date (date only)
• total_orders
• total_revenue
• avg_order_value

Grouped by date, filtered to only include paid orders.

Exercise 3: Access Control with Views (Advanced)

You have a users table with columns: user_id, name, email, password_hash, ssn, salary. Create a view that:

1. Hides password_hash, ssn, and salary
2. Only shows users with status = 'active'
3. Then grant SELECT on this view to a 'readonly'@'%' user

---

Connection to Other Concepts

Related Concept	How It Connects
Updatable Views	Some views allow INSERT/UPDATE — next lesson
Stored Procedures	Procedures can query views for cleaner logic
User Management	Views are a key tool for column-level access control
Performance Optimization	Views don't cache — for heavy views, consider indexed summary tables
Indexes	Views don't have their own indexes — the underlying table indexes are used

What to Learn Next

• Updatable vs. Read-Only Views

---

Visual Learning Diagram

flowchart TD
    A["Application / User"] --> B["SELECT * FROM v_customer_data"]
    B --> C["View: v_customer_data"]
    C --> D["Executes underlying SELECT"]
    D --> E["customers table"]
    D --> F["orders table"]
    E --> G["Returns filtered, joined, shaped data"]
    F --> G
    G --> A

    classDef view fill:#0d6efd,stroke:#fff,color:#fff
    class C view

Quick Reference

-- Create a view
CREATE VIEW v_name AS SELECT ... FROM ... WHERE ...;

-- Create or replace
CREATE OR REPLACE VIEW v_name AS SELECT ...;

-- Drop a view
DROP VIEW IF EXISTS v_name;

-- Inspect a view
SHOW CREATE VIEW v_name\G

-- List all views
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_SCHEMA = DATABASE();

-- Grant access to a view only
GRANT SELECT ON db.v_name TO 'user'@'%';

What's Next

• Updatable vs. Read-Only Views