This guide introduces SQL Views in MariaDB, virtual tables based on the result-set of a stored query. Learn how views simplify complex queries, enhance data security by restricting access, and provide an abstraction layer over your database tables through practical examples.
Prerequisites
A basic understanding of SQL, particularly JOIN operations. (You may want to refer to guides like "Basic Joins Guide" or "More Advanced Joins" if available.)
Access to a MariaDB database.
Privileges to CREATE TABLE and CREATE VIEW.
Setup: Example Employee Database
First, we'll create and populate two tables, Employees and Hours, to use in our examples. If you have already completed a tutorial using this database structure (e.g., from a "More Advanced Joins" guide), you might be able to skip this setup.
Building a Complex Query (Example: Employee Tardiness)
Let's say Human Resources needs a report on employees who are late (clock in after 7:00:59 AM) and do not make up the time at the end of their shift (work less than 10 hours and 1 minute).
Initial Query (Helmholtz's Lateness):
This query finds instances where Helmholtz was late within a specific week:
SELECT
`Employees`.`First_Name`,
`Employees`.`Last_Name`,
`Hours`.`Clock_In`,
`Hours`.`Clock_Out`
FROM `Employees`
INNER JOIN `Hours` ON `Employees`.`ID` = `Hours`.`ID`
WHERE `Employees`.`First_Name` = 'Helmholtz'
AND DATE_FORMAT(`Hours`.`Clock_In`, '%Y-%m-%d') >= '2005-08-08'
AND DATE_FORMAT(`Hours`.`Clock_In`, '%Y-%m-%d') <= '2005-08-12'
AND DATE_FORMAT(`Hours`.`Clock_In`, '%H:%i:%S') > '07:00:59';
The refined query is becoming complex. Storing this query logic in application code makes it harder to manage and means changes to table structures require application code changes. Views can simplify this.
A view is a virtual table based on the result-set of a stored query.
Creating the Employee_Tardiness View:
We use the refined query to create a view. SQL SECURITY INVOKER means the view runs with the permissions of the user querying it.
CREATE SQL SECURITY INVOKER VIEW Employee_Tardiness AS
SELECT
`Employees`.`First_Name`,
`Employees`.`Last_Name`,
`Hours`.`Clock_In`,
`Hours`.`Clock_Out`,
(601 - TIMESTAMPDIFF(MINUTE, `Hours`.`Clock_In`, `Hours`.`Clock_Out`)) AS Difference
FROM `Employees`
INNER JOIN `Hours` USING (`ID`)
WHERE DATE_FORMAT(`Hours`.`Clock_In`, '%Y-%m-%d') BETWEEN '2005-08-08' AND '2005-08-12'
AND TIME(`Hours`.`Clock_In`) > '07:00:59'
AND TIMESTAMPDIFF(MINUTE, `Hours`.`Clock_In`, `Hours`.`Clock_Out`) < 601;
Querying the View:
Now, retrieving the tardiness data is much simpler:
SELECT * FROM Employee_Tardiness;
This will produce the same results as the complex "Refined Query" above.
You can also apply further conditions when querying the view:
SELECT * FROM Employee_Tardiness WHERE Difference >= 5;
Output (example structure, showing those at least 5 minutes short):
Simplifying Complex Queries: As demonstrated, views hide complex joins and calculations.
Restricting Data Access (Column-Level Security): Views can expose only a subset of columns from underlying tables, preventing users or applications from seeing sensitive information (e.g., Home_Address, Home_Phone were not included in our Employee_Tardiness view).
Implementing Row-Level Security: A view can include a WHERE clause that filters rows based on the user querying it or other criteria, effectively providing row-level access control. For updatable views, defining them with WITH CHECK OPTION (or the similar effect of a CASCADE clause mentioned in original text, usually WITH CASCADED CHECK OPTION) can ensure that INSERTs or UPDATEs through the view adhere to the view's WHERE clause conditions.
Pre-emptive Optimization: Complex, frequently used queries can be defined as views with optimal join strategies and indexing considerations. Other users or applications query the already optimized view, reducing the risk of running inefficient ad-hoc queries.
Abstracting Table Structures: Views provide a consistent interface to applications even if the underlying table structures change (e.g., tables are normalized, split, or merged). The view definition can be updated to map to the new structure, while applications continue to query the unchanged view.
Summary of View Advantages
Views offer a powerful way to:
Simplify data access: Make complex queries easier to write and understand.
Abstract database logic: Separate application code from the complexities of the database schema.
Enhance security: Control access to specific rows and columns.
Improve maintainability: Changes to underlying tables can often be managed by updating the view definition without altering application queries.
