MySQL Views

MySQL Views

A view in MySQL is a virtual table based on the result of an SQL query. Unlike a physical table, a view does not store data. Instead, it dynamically fetches data from underlying tables whenever the view is queried. Views are useful for simplifying complex queries, improving security, and abstracting database structures.

Key Features of MySQL Views

1. Abstraction: Simplifies complex queries by encapsulating them in a single object.
2. Security: Restricts user access to specific columns or rows without exposing the underlying table.
3. Reusability: Saves time by reusing frequently executed queries.
4. Read-Only or Updatable: Some views allow data modification, while others are read-only.

Basic Syntax

Creating a View

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

Querying a View

SELECT * FROM view_name;

Dropping a View

DROP VIEW view_name;

Updating an Existing View

To modify a view, you must drop it and recreate it or use the CREATE OR REPLACE statement:

CREATE OR REPLACE VIEW view_name AS
SELECT column1, column2
FROM table_name
WHERE new_condition;

Examples

1. Creating a Simple View

Create a view to display employees with a salary above $5000:

CREATE VIEW HighSalaryEmployees AS
SELECT id, name, salary
FROM employees
WHERE salary > 5000;

Query the view:

SELECT * FROM HighSalaryEmployees;

2. View with Joins

Create a view to show customer orders:

CREATE VIEW CustomerOrders AS
SELECT customers.id AS customer_id, customers.name, orders.id AS order_id, orders.total
FROM customers
JOIN orders ON customers.id = orders.customer_id;

Query the view:

SELECT * FROM CustomerOrders;

3. Aggregated View

Create a view to display total sales per customer:

CREATE VIEW TotalSalesPerCustomer AS
SELECT customer_id, SUM(total) AS total_sales
FROM orders
GROUP BY customer_id;

4. Using CREATE OR REPLACE

Update the HighSalaryEmployees view to include department information:

CREATE OR REPLACE VIEW HighSalaryEmployees AS
SELECT employees.id, employees.name, employees.salary, departments.name AS department
FROM employees
JOIN departments ON employees.department_id = departments.id
WHERE employees.salary > 5000;

Read-Only vs. Updatable Views

Read-Only Views

• A view is read-only if it contains:
  • Aggregated data (GROUP BY, COUNT, SUM, etc.).
  • Joins.
  • Subqueries.
  • DISTINCT, UNION, LIMIT, or HAVING clauses.

Example:

CREATE VIEW ReadOnlyView AS
SELECT customer_id, SUM(total) AS total_sales
FROM orders
GROUP BY customer_id;

Updatable Views

• A view is updatable if:
  • It is based on a single table.
  • It does not use aggregates, joins, or subqueries.
  • It includes the primary key of the table.

Example:

CREATE VIEW UpdatableView AS
SELECT id, name, salary
FROM employees;

You can update the underlying table through the view:

UPDATE UpdatableView
SET salary = salary + 500
WHERE id = 1;

Checking Existing Views

List All Views

SHOW FULL TABLES WHERE TABLE_TYPE = 'VIEW';

View Definition

SHOW CREATE VIEW view_name;

Advantages of Views

1. Simplifies complex queries.
2. Enhances security by exposing only required data.
3. Ensures consistent query results.
4. Facilitates code reusability.

Disadvantages of Views

1. Performance overhead due to dynamic data fetching.
2. Limited updatability.
3. Cannot be included ORDER BY unless combined with LIMIT.

Best Practices

1. Use descriptive names for views.
2. Avoid nesting views to minimize performance overhead.
3. Keep views simple for better maintainability.
4. Document the purpose of each view.

Let me know if you'd like examples tailored to a specific use case!