Views in MySQL
1. What is a View?
- A view is a virtual table created from the result of an SQL query.
- It does not store data physically, but acts like a table.
- Useful for simplifying queries, reusability, and security (restricting column access).
2. Creating a View
-- Basic view
CREATE VIEW view_name AS
SELECT column1, column2
FROM table_name
WHERE condition;
Example:
CREATE VIEW customer_emails AS
SELECT name, email
FROM customers
WHERE age > 25;
Now you can use:
SELECT * FROM customer_emails;
3. Updating a View
-- Modify an existing view
CREATE OR REPLACE VIEW view_name AS
SELECT column1, column2
FROM table_name
WHERE new_condition;
4. Dropping a View
DROP VIEW view_name;
5. Advantages of Views
- ✅ Simplifies complex queries → Reuse instead of rewriting joins.
- ✅ Security → Expose only specific columns to users.
- ✅ Reusability → Write once, query many times.
- ✅ Logical Independence → Underlying table changes don’t affect query logic.
6. Updatable Views
- Some views can be updatable (you can
INSERT,UPDATE,DELETEthrough them). - Conditions for updatable views:
- Based on single table.
- No
DISTINCT,GROUP BY,HAVING,UNION,LIMIT. - Must include primary key column.
Example:
CREATE VIEW customer_basic AS
SELECT id, name, email
FROM customers;
You can now:
UPDATE customer_basic SET email='new@mail.com' WHERE id=1;
7. Check Option (Enforcing View Condition)
CREATE VIEW adult_customers AS
SELECT * FROM customers
WHERE age >= 18
WITH CHECK OPTION;
-
Prevents inserting/updating data that violates the condition (e.g., age < 18).
✅ Summary:
Views = Saved SQL queries that behave like tables. They simplify, secure, and organize database access.