Views

Views

-- Create a view
CREATE OR REPLACE VIEW active_users AS
SELECT id, name, email, created_at
FROM users
WHERE is_active = 1;

-- Use view like a table
SELECT * FROM active_users WHERE created_at > '2024-01-01';

-- View with joins
CREATE VIEW order_summary AS
SELECT
    o.id,
    u.name AS customer,
    o.total,
    o.status,
    COUNT(oi.id) AS item_count
FROM orders o
JOIN users u ON o.user_id = u.id
LEFT JOIN order_items oi ON o.id = oi.order_id
GROUP BY o.id;

-- Show views
SHOW FULL TABLES WHERE TABLE_TYPE = 'VIEW';

-- Drop
DROP VIEW IF EXISTS active_users;