MySQL: إنشاء طرق عرض MySQL واستخدامها

SELECT c.name, COUNT(o.id), SUM(o.amount)
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE c.status = 'active'
GROUP BY c.id;

CREATE VIEW active_customer_orders AS
SELECT c.name, COUNT(o.id) AS order_count, SUM(o.amount) AS total_amount
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE c.status = 'active'
GROUP BY c.id;

-- Query the view directly in the future
SELECT * FROM active_customer_orders;

-- Create a View
CREATE VIEW v_active_users AS
SELECT id, username, email, created_at
FROM users
WHERE status = 'active';

-- Using Views（Query like a table）
SELECT * FROM v_active_users;
SELECT * FROM v_active_users WHERE created_at >= '2026-01-01';

-- Order Statistics View
CREATE VIEW v_order_stats AS
SELECT 
    c.id AS customer_id,
    c.name AS customer_name,
    COUNT(o.id) AS total_orders,
    SUM(o.amount) AS total_amount,
    AVG(o.amount) AS avg_amount,
    MAX(o.order_date) AS last_order_date
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name;

-- Edit View
ALTER VIEW v_active_users AS
SELECT id, username, email, phone, created_at
FROM users
WHERE status = 'active' AND email_verified = TRUE;

-- or use CREATE OR REPLACE
CREATE OR REPLACE VIEW v_active_users AS
SELECT id, username, email FROM users WHERE status = 'active';

-- Delete View
DROP VIEW IF EXISTS v_active_users;

-- View View Definition
SHOW CREATE VIEW v_active_users;

CREATE VIEW v_active_users AS
SELECT * FROM users WHERE status = 'active'
WITH CHECK OPTION;

-- Allowed: status is active
UPDATE v_active_users SET username = 'new_name' WHERE id = 1;

-- Prohibited: After the revision status is no longer active, will be rejected
UPDATE v_active_users SET status = 'inactive' WHERE id = 1;
-- ERROR: CHECK OPTION failed

-- Create a view that contains only public information
CREATE VIEW v_user_public AS
SELECT id, username, avatar FROM users;

-- Grant only view permissions to regular users
GRANT SELECT ON mydb.v_user_public TO 'readonly_user'@'localhost';