Views — Virtual Table

View কী?

View হলো একটি virtual table — saved SELECT query। ডেটা সংরক্ষণ করে না।

উপকারিতা

• জটিল query সরল
• Security (নির্দিষ্ট column/row দেখানো)
• Abstraction

View তৈরি

CREATE VIEW active_users AS
SELECT id, name, email, city
FROM users
WHERE status = 'active';

-- ব্যবহার
SELECT * FROM active_users WHERE city = 'ঢাকা';

জটিল JOIN View

CREATE VIEW order_details AS
SELECT o.id AS order_id, u.name AS customer,
    u.email, o.total, o.status, o.created_at,
    COUNT(oi.product_id) AS items
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;

SELECT * FROM order_details
WHERE status = 'completed'
ORDER BY total DESC;

View সংশোধন

CREATE OR REPLACE VIEW active_users AS ...;
ALTER VIEW active_users AS ...;
DROP VIEW active_users;

SHOW FULL TABLES WHERE Table_type = 'VIEW';

Updatable View

CREATE VIEW adult_users AS
SELECT id, name, age FROM users
WHERE age >= 18
WITH CHECK OPTION;

UPDATE adult_users SET name='নতুন' WHERE id=1;  -- ✅
INSERT INTO adult_users (name, age) VALUES ('খোকা', 10); -- ❌

Analytics View

CREATE VIEW monthly_sales AS
SELECT DATE_FORMAT(created_at, '%Y-%m') AS month,
    COUNT(*) AS orders, SUM(total) AS revenue
FROM orders WHERE status='completed'
GROUP BY month;

SELECT * FROM monthly_sales WHERE month >= '2025-01';