MySQL: Creating and Using MySQL Views
Last updated: 2026-08-26
A view is a virtual table—it encapsulates complex queries into a simple interface.
This lesson covers the creation, use, and management of views.
graph TB
A[How Views Work] --> B[CREATE VIEW DefinitionSQL]
B --> C[StorageSQLDefinition]
C --> D[When querying a view]
D --> E[Expand to the bottom layerSQL]
E --> F[Execute a query on the underlying table]
F --> G[Return Results]
B --> H[WITH CHECK OPTION]
H --> I[When modifying data<br/>Validate View Conditions]
1. What You'll Learn
- CREATE VIEW: Create a view
- The Difference Between Views and Tables
- Modify and Delete Views
- Use Cases for Views
- WITH CHECK OPTION
2. Real-Life Scenarios
(1) Pain Point: Repeatedly Writing Complex Queries
I often need to query "order statistics for active customers," but the SQL query is very long:
SQL
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;
I have to write it out every time.
(2) Solution to the View Problem
SQL
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;
3. Creating a View
▶ Example: Basic View
SQL
-- 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';
Output:
TEXT
📖 Display only
Output displayed
▶ Example: Complex Views
Output:
TEXT
📖 Display only
Query OK, 0 rows affected
SQL
-- 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;
Output:
TEXT
📖 Display only
Output displayed
4. Views vs. Tables
| Dimension | View | Table |
|---|---|---|
| Data Storage | No Storage (Virtual) | Stores Actual Data |
| Update | Restrictions (can be updated in Simple View) | Can be freely added, deleted, or modified |
| Performance | Recalculated on Every Query | Read Directly |
| Purpose | Simplify queries and access control | Store data |
5. Modifying and Deleting Views
▶ Example: Management View
Output:
TEXT
📖 Display only
Query OK, 0 rows affected
Query OK, 0 rows affected
Query OK, 0 rows affected
Query OK, 0 rows affected
SQL
-- 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;
Output:
TEXT
📖 Display only
Output displayed
6. WITH CHECK OPTION
Ensure that data modified through a view still meets the view's criteria.
▶ Example: CHECK OPTION
SQL
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
Output:
TEXT
📖 Display only
Output displayed
7. Use Cases for Views
| Scenario | Description |
|---|---|
| Simplify complex queries | Encapsulate multi-table JOINs and provide a simple interface |
| Access Control | Expose Only Selected Fields to Users |
| Data Abstraction | Masking Changes to the Underlying Table Structure |
| Report Statistics | Predefined Statistical Logic |
▶ Example: Access Control
SQL
-- 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';
Output:
TEXT
📖 Display only
Output displayed
❓ FAQ
Q Can indexes be created on views?
A No. Views are virtual tables with no actual storage. Queries on views rely on indexes on the underlying tables for optimization.
Q Can views be nested?
A Yes,
CREATE VIEW v2 AS SELECT * FROM v1. However, excessive nesting can affect performance.Q Is the data in the view real-time?
A Yes. The view is recalculated from the underlying tables with every query, so you see the latest data.
Q Do views take up storage space?
A No, they do not. Views store only the SQL definition; they do not store the actual data.
Q Can views improve performance?
A No. Views are just syntactic sugar; the underlying SQL is re-executed for every query. Performance optimization should rely on indexes.
📖 Summary
- Views are virtual tables that do not store data; they are recalculated with each query.
- CREATE VIEW to create, ALTER VIEW to modify, DROP VIEW to delete
- WITH CHECK OPTION Ensures that modifications meet the view's conditions
- Applications: Simplified Queries, Access Control, Data Abstraction, Reporting and Analytics
📝 Exercises
-
Basic Problem (Difficulty: ⭐): Create a view that displays only the ID, username, and email of active users.
-
Advanced Exercise (Difficulty: ⭐⭐): Create an order statistics view that includes customer name, number of orders, and total amount.
-
Challenge (Difficulty: ⭐⭐⭐): Implement access control using views; create a read-only view and grant access to specific users.
