MariaDB Views

Summary: in this tutorial, you will learn about the MariaDB views and their advantages including simplicity, consistency, and security.

Introduction to MariaDB Views

See the following countries, regions, and continents tables from the sample database.

The following statement uses inner join clauses to find the detailed country information including regions and continents:

select 
    c.name country,
    r.name region,
    t.name continent,
    area
from countries c
inner join regions r 
    using (region_id)
inner join continents t 
    using (continent_id)
order by country;
Code language: SQL (Structured Query Language) (sql)

To reuse this query, you may save it into a text file. Whenever you want to use the query, you can open the file, copy and paste the content of the query, and execute it.

MariaDB provides a better way to save and share a query across application through a view.

A view is a query that you can assign it a name. Sometimes, a view is known as a named query.

MariaDB stores a view as an object in the data dictionary so that you can reference it like a table.

Because you can use the name of a view as a table, a view is often referred to as a virtual table.

A view is a virtual, not a real table because it does not store the data. When you use the select statement to query data from a view, MariaDB executes a query that defines the view to select data from the underlying tables.

The underlying tables to which a view refers is called base tables.

The following picture illustrates a view based on multiple tables:

Creating a MariaDB view

To create a view in MariaDB, you use the create view statement. For example, the following statement creates a view called country_details based on the query above:

create view country_details 
as
select 
    c.name country,
    r.name region,
    t.name continent,
    area
from countries c
inner join regions r 
    using (region_id)
inner join continents t 
    using (continent_id)
order by country;
Code language: SQL (Structured Query Language) (sql)

Note that the new part is:

create view country_details as
Code language: SQL (Structured Query Language) (sql)

Once the view is created, you can use it in the select statement as if it was a table. For example, you can view the contents of the base tables via the view as follows:

select * 
from country_details;
Code language: SQL (Structured Query Language) (sql)

The advantages of using MariaDB views

MariaDB views provide the following advantages:

Simplicity

Views allow you to encapsulate complex queries with joins to simplify the table access. Instead of typing a long and complex query in multiple applications, you can create a view based on the query and use the view name in the applications.

Security

In terms of security, you can grant a user account to access partial data of the base tables through a view. By doing this, you can hide the sensitive data from the users who do not have the privilege to view it.

Consistency

Views can be used to encapsulate business logic and formulas and make them consistent across applications.

In this tutorial, you learned about the MariaDB view and its advantages including simplicity, consistency, and security.

Managing MariaDB views

• Create view  – show you how to use the create view statement to create a new view in a database.
• Drop view – learn how to remove one or more views from a database.