MySQL Views

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!

Soeng Souy

Soeng Souy

Website that learns and reads, PHP, Framework Laravel, How to and download Admin template sample source code free.

Post a Comment

CAN FEEDBACK
close