Working with Views in MariaDB

Views in MariaDB are virtual tables that are defined by a query. They allow you to encapsulate complex SQL queries and present the results as a structured table-like object. Here's an overview of working with views in MariaDB:

 Creating Views:

Views are created using the CREATE VIEW statement followed by the view name and the query that defines the view's structure.

CREATE VIEW view_name AS

SELECT column1, column2, ...

FROM table_name

WHERE condition;

 Example:

Let's create a view that retrieves the names and salaries of employees with a salary greater than $50,000:

CREATE VIEW high_salary_employees AS

SELECT name, salary

FROM employees

WHERE salary > 50000;

 Querying Views:

Once a view is created, you can query it just like a regular table using the SELECT statement:

SELECT * FROM high_salary_employees;

 Modifying Views:

You can modify views using the ALTER VIEW statement to redefine the view's query:

ALTER VIEW view_name AS

SELECT new_column1, new_column2, ...

FROM new_table

WHERE new_condition;

 Dropping Views:

To drop a view, use the DROP VIEW statement followed by the view name:

DROP VIEW view_name;

 Updatable Views:

In MariaDB, views can be updatable if they meet certain criteria, such as having a single table in the underlying query, not containing grouping functions, and not using DISTINCT. Updatable views allow you to perform INSERT, UPDATE, and DELETE operations directly on the view, which are then translated into corresponding operations on the underlying table.

 Example of an Updatable View:

CREATE VIEW updatable_view AS

SELECT id, name

FROM employees

WHERE department = 'Sales';

You can then perform operations like:

INSERT INTO updatable_view (id, name) VALUES (101, 'John');

UPDATE updatable_view SET name = 'Jane' WHERE id = 101;

DELETE FROM updatable_view WHERE id = 101;

Views provide a convenient way to encapsulate complex queries and present them as virtual tables. By using views, you can simplify query complexity, improve code readability, and enhance data security in your MariaDB databases. Additionally, with updatable views, you can perform CRUD operations directly on the view, providing a seamless interface for data manipulation.