VIEW
, and,
Syntax
CREATE [OR REPLACE] VIEW view_name AS SELECT ...; ALTER VIEW view_name AS SELECT ...; DROP VIEW [IF EXISTS] view_name;
Parameters
| Parameters | Description | Examples | Level |
|---|---|---|---|
CREATE VIEW |
CREATE VIEW v_active_users AS SELECT * FROM users WHERE status = 1; |
Common | |
OR REPLACE |
CREATE OR REPLACE VIEW v_orders AS SELECT ...; |
Common | |
WITH CHECK OPTION |
CREATE VIEW v_vip AS SELECT * FROM users WHERE level >= 3 WITH CHECK OPTION; |
Advanced | |
ALTER VIEW |
ALTER VIEW v_active_users AS SELECT id, username FROM users WHERE status = 1; |
Common | |
DROP VIEW |
DROP VIEW IF EXISTS v_active_users; |
Common |
Examples
CREATE OR REPLACE VIEW v_order_summary AS SELECT u.id AS user_id, u.username, COUNT(o.id) AS order_count, SUM(o.total_amount) AS total_spent, MAX(o.created_at) AS last_order_time FROM users u LEFT JOIN orders o ON u.id = o.user_id GROUP BY u.id, u.username;
, SELECT
-- SELECT username, order_count, total_spent FROM v_order_summary WHERE order_count >= 10 ORDER BY total_spent DESC LIMIT 20;
,
()
--, and CREATE VIEW v_user_info AS SELECT id, username, email, phone, status, created_at FROM users; -- GRANT SELECT ON ecommerce.v_user_info TO 'support'@'%';
,
-- SHOW FULL TABLES WHERE Table_type = 'VIEW'; -- SHOW CREATE VIEW v_order_summary;
New
Common Errors
ERROR 1356: View references invalid table(s) or column(s), New
ERROR 1471: The target table is not updatableGROUP BY, DISTINCT, New,
Tips
- , SQL,
- :
- v_, and