MySQL CREATE VIEW

MySQL CREATE VIEW Statement

A VIEW in MySQL is a virtual table based on the result of a SELECT query. It does not store data itself but dynamically presents data from one or more tables.

1. Syntax

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

view_name → Name of the view.
SELECT column1, column2, ... → Specifies the columns to include in the view.
WHERE condition → Filters the data (optional).

2. Creating a Simple View

📌 Example: Create a view for active employees

CREATE VIEW active_employees AS
SELECT employee_id, employee_name, salary
FROM employees
WHERE status = 'Active';

✔ Usage:

SELECT * FROM active_employees;

✔ Output:

+-------------+--------------+--------+
| employee_id | employee_name| salary |
+-------------+--------------+--------+
| 101         | John Doe     | 6000   |
| 102         | Jane Smith   | 7000   |
+-------------+--------------+--------+

📌 Explanation: The active_employees view displays only employees with status = 'Active'.

3. Creating a View from Multiple Tables (Join)

📌 Example: Create a view to show employee details with department names

CREATE VIEW employee_details AS
SELECT e.employee_id, e.employee_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

✔ Usage:

SELECT * FROM employee_details;

✔ Explanation: The view joins the employees and departments tables.

4. Updating Views Using `ALTER VIEW`

You can modify an existing view using ALTER VIEW.

📌 Example: Modify the active_employees view to include department_id

ALTER VIEW active_employees AS
SELECT employee_id, employee_name, salary, department_id
FROM employees
WHERE status = 'Active';

5. Dropping a View

To remove a view, use DROP VIEW.

📌 Example: Drop the active_employees view

DROP VIEW active_employees;

6. Checking Existing Views

📌 List all views in a database

SHOW FULL TABLES WHERE Table_type = 'VIEW';

📌 Get the definition of a view

SHOW CREATE VIEW employee_details;

7. Can You Update Data in a View?

✔ Updatable Views: If a view is based on a single table, and does not contain aggregates (SUM, COUNT) or DISTINCT, then it can be updated.

UPDATE active_employees 
SET salary = 8000 
WHERE employee_id = 101;

🚨 If a view contains JOINs, GROUP BY, or DISTINCT, it cannot be updated.

8. Summary

✔ Views simplify queries and improve security.
✔ Use CREATE VIEW to create a virtual table.
✔ Use ALTER VIEW to modify it.
✔ Use DROP VIEW to delete it.

Would you like an example based on your database? 🚀

Souy Soeng

MySQL CREATE VIEW