Views

Views are virtual tables that store a query as their definition. Views help simplify complex queries and control access to data.

Syntax

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

Basic Example

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

-- Use the view like a regular table
SELECT * FROM active_customers;
SELECT COUNT(*) FROM active_customers WHERE created_at > '2024-01-01';

Views with JOINs

CREATE VIEW order_summaries AS
SELECT
    o.id AS order_id,
    c.name AS customer_name,
    o.total_amount,
    o.order_date,
    COUNT(oi.id) AS total_items
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN order_items oi ON o.id = oi.order_id
GROUP BY o.id, c.name, o.total_amount, o.order_date;

-- Use the view
SELECT * FROM order_summaries WHERE total_amount > 500000;

Views with Aggregation

CREATE VIEW sales_by_category AS
SELECT
    p.category,
    SUM(oi.quantity) AS total_quantity,
    SUM(oi.quantity * oi.price) AS total_revenue
FROM products p
JOIN order_items oi ON p.id = oi.product_id
JOIN orders o ON oi.order_id = o.id
WHERE o.status = 'completed'
GROUP BY p.category;

-- A simple query over a complex view
SELECT * FROM sales_by_category ORDER BY total_revenue DESC;

Managing Views

Viewing Views

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

-- Show the view definition
SHOW CREATE VIEW active_customers;

Modifying a View

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

Dropping a View

DROP VIEW IF EXISTS active_customers;

Limitations in TiDB

• Views in TiDB are read-only — they cannot be UPDATED, INSERTed, or DELETEd
• Views are not materialized — the query is re-executed every time the view is accessed

-- This does NOT work in TiDB
UPDATE active_customers SET name = 'test';
DELETE FROM active_customers WHERE id = 1;

Tips

• Use views to simplify frequently used queries
• Use views for security — hide sensitive columns
• Use CREATE OR REPLACE to update a view without dropping it

In the next chapter, we will look at AUTO_RANDOM and AUTO_INCREMENT in TiDB.