✦

AI Reading

Quick summary of this article

A MySQL VIEW is a virtual table that acts like a stored query, pulling data from underlying tables each time you use it. It simplifies complex queries, improves security by hiding sensitive columns, and makes your database design more modular. You can create views with joins, filters, and aggregations, but only simple views based on a single table without aggregates or joins can be updated directly.

• Views do not store data physically—they run the underlying SELECT query each time you access them.
• You can create views with JOINs, WHERE conditions, GROUP BY, and aggregate functions to simplify complex reporting.
• Only simple views based on one table (without DISTINCT, GROUP BY, aggregates, or JOINs) allow UPDATE and INSERT operations.
• Use CREATE OR REPLACE VIEW to modify an existing view without dropping it first.
• Drop a view with DROP VIEW view_name when it is no longer needed.

In MySQL, a VIEW is a virtual table based on the result of a SELECT query. It acts like a stored query that you can reference just like a regular table, simplifying complex queries and enhancing security and modularity.

✅ What is a VIEW?

A view is a named query stored in the database. It does not store data physically but pulls from underlying tables each time it is queried.

📌 Syntax:

CREATE VIEW view_name AS
SELECT columns
FROM tables
WHERE conditions;

📌 Example Table – employees

employee_id	name	department_id	manager_id
1	Alice	1	NULL
2	Bob	2	1
3	Charlie	3	1
4	David	NULL	2
5	Eva	2	2

🔹 Creating a Basic VIEW

Example 1: Create a View of Active Employees with Department ID

CREATE VIEW active_employees AS
SELECT employee_id, name, department_id
FROM employees
WHERE department_id IS NOT NULL;

Use the View:

SELECT * FROM active_employees;

Result: Returns only employees with assigned departments.

🔹 Creating JOIN-based VIEW

Example 2: Employee Name and Department Name

Assume departments table:

department_id	department_name
1	HR
2	IT
4	Marketing

CREATE VIEW employee_department AS
SELECT e.name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

Query:

SELECT * FROM employee_department;

🔹 Updating Data Through a View

Some views can be updatable, but with conditions:

• Only one base table
• No aggregates, DISTINCT, GROUP BY, or joins

Example 3: Update Through View

CREATE VIEW basic_view AS
SELECT employee_id, name FROM employees;

UPDATE basic_view
SET name = 'Robert'
WHERE employee_id = 2;

✅ This works because the view is based on a single table with direct columns.

🔹 Read-Only Views

Views with JOINs, aggregates, or GROUP BY cannot be updated.

Example 4: Read-only View with Aggregate

CREATE VIEW department_summary AS
SELECT department_id, COUNT(*) AS total_employees
FROM employees
GROUP BY department_id;

This view cannot be updated directly.

🔹 Dropping a View

DROP VIEW view_name;

Example:

DROP VIEW active_employees;

🔹 Modifying a View

CREATE OR REPLACE VIEW view_name AS
SELECT ...

Example:

CREATE OR REPLACE VIEW basic_view AS
SELECT employee_id, name, manager_id FROM employees;

🔹 Benefits of Using Views

• Simplify complex SQL queries.
• Improve security by hiding sensitive columns.
• Encapsulate logic: Useful for business rules and reusability.
• Enable modular design: Acts like an abstraction layer.

✅ Summary Table

Feature	Can Use in VIEW?
JOIN	✅ Yes
WHERE	✅ Yes
GROUP BY	✅ Yes
Aggregate Func	✅ Yes
UPDATE/INSERT	🚫 Sometimes
Subqueries	✅ Yes
Parameters	🚫 No

🧪 Practice Tasks

1. Create a view that lists employees without a manager.
2. Create a view to count employees in each department.
3. Try updating a basic view and see the effect.
4. Replace a view with new logic using CREATE OR REPLACE.