MySQL & Database Design

Views

13 min Lesson 20 of 40

Views

Views are virtual tables based on the result of SQL queries. They don't store data themselves but provide a way to simplify complex queries, enhance security, and present data in different formats. In this lesson, we'll explore how to create and use views effectively.

What is a View?

A view is a saved SQL query that appears as a virtual table. When you query a view, MySQL executes the underlying SELECT statement and returns the results.

Key Concept: Views don't store data - they're simply stored queries that dynamically fetch data from underlying tables when accessed.

Creating Basic Views

Use the CREATE VIEW statement to define a view:

-- Simple view showing active products
CREATE VIEW active_products AS
SELECT product_id, product_name, price, stock_quantity
FROM products
WHERE status = 'active';

-- Query the view like a regular table
SELECT * FROM active_products
WHERE price > 50;

Views with JOIN Operations

Views are excellent for simplifying complex queries with multiple JOINs:

-- View combining order information
CREATE VIEW order_details AS
SELECT
    o.order_id,
    o.order_date,
    c.customer_name,
    c.email,
    p.product_name,
    oi.quantity,
    oi.unit_price,
    (oi.quantity * oi.unit_price) AS line_total
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id;

-- Now query complex data simply
SELECT * FROM order_details
WHERE customer_name LIKE 'John%'
ORDER BY order_date DESC;

Tip: Use views to hide complex JOINs from application developers. They can query the view without understanding the underlying table relationships.

Calculated Fields in Views

Views can include calculated columns and aggregate data:

-- View with calculated fields
CREATE VIEW product_inventory_value AS
SELECT
    product_id,
    product_name,
    stock_quantity,
    cost_price,
    (stock_quantity * cost_price) AS inventory_value,
    CASE
        WHEN stock_quantity = 0 THEN 'Out of Stock'
        WHEN stock_quantity < 10 THEN 'Low Stock'
        ELSE 'In Stock'
    END AS stock_status
FROM products;

-- View with aggregations
CREATE VIEW monthly_sales_summary AS
SELECT
    DATE_FORMAT(order_date, '%Y-%m') AS month,
    COUNT(*) AS total_orders,
    SUM(total_amount) AS total_revenue,
    AVG(total_amount) AS avg_order_value
FROM orders
GROUP BY DATE_FORMAT(order_date, '%Y-%m');

Updatable Views

Some views allow INSERT, UPDATE, and DELETE operations. A view is updatable if it meets specific criteria:

-- Updatable view (single table, no aggregates)
CREATE VIEW high_value_customers AS
SELECT customer_id, customer_name, email, total_spent
FROM customers
WHERE total_spent > 1000;

-- You can update through this view
UPDATE high_value_customers
SET email = 'newemail@example.com'
WHERE customer_id = 5;

-- The update affects the underlying customers table

Important: Views with JOINs, DISTINCT, GROUP BY, HAVING, UNION, or aggregate functions are NOT updatable.

View Algorithms

MySQL offers three algorithms for processing views:

-- MERGE: Combines view query with user query (default)
CREATE ALGORITHM=MERGE VIEW customer_orders AS
SELECT c.customer_name, o.order_id, o.order_date
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id;

-- TEMPTABLE: Creates temporary table (required for aggregates)
CREATE ALGORITHM=TEMPTABLE VIEW sales_by_category AS
SELECT category, COUNT(*) AS product_count, SUM(price) AS total_value
FROM products
GROUP BY category;

-- UNDEFINED: Let MySQL choose (recommended)
CREATE ALGORITHM=UNDEFINED VIEW recent_orders AS
SELECT * FROM orders WHERE order_date > DATE_SUB(NOW(), INTERVAL 30 DAY);

Performance Tip: MERGE is faster but not always possible. TEMPTABLE is used when MERGE can't be applied (like with aggregates).

Security with Views

Views can restrict access to sensitive data:

-- Expose only safe columns to application users
CREATE VIEW public_user_info AS
SELECT user_id, username, email, created_at
FROM users;
-- Password hash, security questions, etc. are hidden

-- Grant access to view but not to underlying table
GRANT SELECT ON database_name.public_user_info TO 'app_user'@'localhost';

-- View with row-level security
CREATE VIEW employee_department_view AS
SELECT employee_id, employee_name, salary, department_id
FROM employees
WHERE department_id IN (
    SELECT department_id FROM user_departments
    WHERE user_id = CURRENT_USER()
);

Managing Views

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

-- Show view definition
SHOW CREATE VIEW active_products;

-- Modify existing view
CREATE OR REPLACE VIEW active_products AS
SELECT product_id, product_name, price, stock_quantity, category
FROM products
WHERE status = 'active' AND price > 0;

-- Drop a view
DROP VIEW IF EXISTS active_products;

-- Check if view exists before creating
DROP VIEW IF EXISTS order_summary;
CREATE VIEW order_summary AS
SELECT order_id, customer_id, order_date, total_amount
FROM orders;

Materialized Views Concept

MySQL doesn't have built-in materialized views, but you can simulate them:

-- Create a table to cache view results
CREATE TABLE cached_sales_summary AS
SELECT
    DATE_FORMAT(order_date, '%Y-%m') AS month,
    COUNT(*) AS total_orders,
    SUM(total_amount) AS total_revenue
FROM orders
GROUP BY DATE_FORMAT(order_date, '%Y-%m');

-- Add indexes for performance
CREATE INDEX idx_month ON cached_sales_summary(month);

-- Refresh materialized view (run periodically)
TRUNCATE TABLE cached_sales_summary;
INSERT INTO cached_sales_summary
SELECT
    DATE_FORMAT(order_date, '%Y-%m') AS month,
    COUNT(*) AS total_orders,
    SUM(total_amount) AS total_revenue
FROM orders
GROUP BY DATE_FORMAT(order_date, '%Y-%m');

Views with CHECK OPTION

Prevent updates that would make rows disappear from the view:

-- View with CHECK OPTION
CREATE VIEW active_products_view AS
SELECT product_id, product_name, price, status
FROM products
WHERE status = 'active'
WITH CHECK OPTION;

-- This update succeeds
UPDATE active_products_view
SET price = 29.99
WHERE product_id = 10;

-- This update FAILS (would make row disappear from view)
UPDATE active_products_view
SET status = 'inactive'
WHERE product_id = 10;
-- Error: CHECK OPTION failed

Real-World View Examples

-- Dashboard statistics view
CREATE VIEW dashboard_stats AS
SELECT
    (SELECT COUNT(*) FROM orders WHERE order_date = CURDATE()) AS today_orders,
    (SELECT SUM(total_amount) FROM orders WHERE order_date = CURDATE()) AS today_revenue,
    (SELECT COUNT(*) FROM customers WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)) AS new_customers_week,
    (SELECT AVG(rating) FROM product_reviews WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)) AS avg_rating_month;

-- Customer lifetime value view
CREATE VIEW customer_lifetime_value AS
SELECT
    c.customer_id,
    c.customer_name,
    c.email,
    COUNT(DISTINCT o.order_id) AS total_orders,
    SUM(o.total_amount) AS lifetime_value,
    AVG(o.total_amount) AS avg_order_value,
    MAX(o.order_date) AS last_order_date,
    DATEDIFF(NOW(), MAX(o.order_date)) AS days_since_last_order
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name, c.email;

Practice Exercise:

Create a Product Performance View:

Create a view called 'product_performance' that shows:
- Product ID and name
- Total quantity sold
- Total revenue generated
- Average rating from reviews
- Number of times reviewed
Add a calculated column 'performance_score' (quantity * average rating)
Test the view by querying top 10 performing products

Best Practices

✓ Use descriptive view names (e.g., vw_customer_orders)
✓ Document view purpose and dependencies
✓ Avoid nested views (views based on other views)
✓ Use views to simplify complex queries
✓ Leverage views for security and data abstraction
✓ Consider performance impact on complex views
✓ Use indexes on underlying tables
✗ Don't use views for frequently changing data structures
✗ Avoid too many calculated columns in views

Summary

In this lesson, you learned:

Views are virtual tables based on SELECT queries
Views simplify complex queries and enhance security
Some views are updatable (single table, no aggregates)
View algorithms: MERGE, TEMPTABLE, UNDEFINED
WITH CHECK OPTION prevents invalid updates
Views can hide sensitive data from users
Materialized views can be simulated with tables

Next Up: In the next lesson, we'll learn about Stored Procedures to encapsulate complex business logic in the database!