MySQL

Chapter 9 - Advanced Topics

Database View

In MySQL, a view is a virtual table that provides a way to simplify complex queries and present data in a specific format. Views are essentially stored queries that you can use like tables. They do not store data themselves but provide a way to query data from one or more tables. Understanding and using views effectively can improve query performance, enhance security, and simplify data management.

Here's a breakdown of advanced topics related to MySQL views:

Creating a View

You can create a view using theCREATE VIEW statement. This view can encapsulate complex queries, making it easier to reuse and manage.

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

Updating Data Through Views

Views can be used to update data, but only under certain conditions. The view must be updatable, which means it should be based on a single table or a combination of tables with specific characteristics.

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

-- Updating through the view
UPDATE employee_department
SET department_name =  'Sales'
WHERE employee_id =  1;

View with Aggregated Data

Views can also include aggregated data. This is useful for summarizing information, such as calculating total salaries by department.

CREATE VIEW  department_salary_summary AS
SELECT d.department_name, SUM (e.salary) AS total_salary
FROM employees e
JOIN departments d ON e.department_id = d.department_id
GROUP BY d.department_name;

Dropping a View
- If a view is no longer needed, it can be removed using theDROP VIEW statement.
- ```
 DROP VIEW IF EXISTS employee_summary;
```
Using Views for Security
- Views can help in restricting access to sensitive data. By creating a view that excludes certain columns or rows, you can control what data is visible to different users.
- ```
CREATE VIEW  employee_public AS
SELECT employee_id, name
FROM employees;
```
View Performance Considerations
- While views simplify query writing, they can impact performance if not used wisely. For complex views, it might be beneficial to index the underlying tables properly.
- It's important to analyze and optimize queries that use views to ensure they perform efficiently.

By understanding these advanced topics, you can leverage views in MySQL to streamline your database interactions, enhance data security, and improve query performance.

Definition

A view is a result set of a stored query on the data, which the database users can query just like they would in a real table. Unlike a real table, a view does not store data physically; it is a set of queries that dynamically retrieve data from the database tables as needed.

Uses and Advantages

Security: Views can be used to restrict user access to specific rows and columns of data.
Simplicity: Complex queries can be encapsulated in views.
Consistency: Views can present a consistent, unchanging image of the structure of the database.
Logical Data Independence: Views provide a layer of abstraction.
Data Integrity: Views can be used to ensure data integrity by exposing only valid data to the user.

Types of Views

Updatable Views: Views that allow data modification operations.
Read-Only Views: Views that do not allow data modification operations.
Materialized Views: A materialized view is a database object that contains the results of a query and is stored on disk, acting like a cache to improve query performance. Unlike standard views that dynamically retrieve data every time they are accessed, materialized views hold the actual query result and can be indexed, thus significantly speeding up access to complex aggregated or joined data. They are particularly useful in data warehousing scenarios where queries are run on large volumes of data and require heavy computation. However, because they store a snapshot of the data, materialized views must be refreshed periodically to remain up-to-date with the underlying data changes. This trade-off between data freshness and query speed is a key consideration when deciding to use materialized views in a database system.

Creating a View

Here is the basic SQL syntax for creating a view:


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

Example

Consider you have a tableemployees with columnsid,name,salary,department. You can create a view to show only thenameanddepartment


CREATE VIEW department_view AS
SELECT name, department
FROM employees;

Modifying a View

To change a view, you generally have to drop and recreate it. Some SQL dialects support theCREATE OR REPLACE VIEWStatement


CREATE OR REPLACE VIEW department_view AS
SELECT name, department
FROM employees;

Removing a View

To remove a view from the database, you use theDROPVIEWStatement:

DROP VIEW view_name;

Limitations and Considerations

Views do not have associated indexes, so queries might be slower than direct table queries.
Some views are not updatable.
Overusing views can lead to maintenance challenges and performance issues.

In summary, views are a powerful feature in SQL databases that provide a way to simplify complex operations, enhance security, and offer a level of abstraction for database operations.

DATABASE VIEW EXAMPLE

Let's establish a database view to consolidate data for the order management system. Our objective is to extract the client's name, their order numbers, including the order date, order number, number of products ordered, order status, order amount, paid amount, and the agent who processed the order on their behalf. This requirement is depicted in the data model below, with the necessary columns highlighted with red lines.


CREATE VIEW get_order_details AS
SELECT
    org_client.first_name as client_name,
    act_order.order_number,
    act_order.order_date,
    act_lkp_order_status.order_status,
    count(act_order_detail.product_id) as product_count,
    sum(act_order.sub_total "+" act_order.tax_amount "+" act_order.shipping_amount) as invoice_amount,
    sum(act_payment.paid_amount) as paid_amount,
    org_employee.first_name as booking_agent_name
FROM act_order
INNER JOIN act_lkp_order_status ON act_lkp_order_status.order_status_id = act_order.order_status_id
INNER JOIN act_order_detail ON act_order_detail.order_id = act_order.order_id
LEFT JOIN act_payment ON act_payment.order_id = act_order.order_id
INNER JOIN org_client ON org_client.client_id = act_order.client_id
INNER JOIN org_employee ON org_employee.employee_id = act_order.sales_agent_employee_id
GROUP BY
    org_client.first_name,
    act_order.order_number,
    act_order.order_date,
    act_lkp_order_status.order_status,
    org_employee.first_name
ORDER BY act_order.order_date;

Previous Next

Comments(0 comments)

Comments Not Found