MariaDB Create View

Summary: in this tutorial, you will learn how to use the MariaDB create view statement to create a new view in the database.

Introduction to MariaDB create view statement

The create view statement allows you to create a new view in the database. The following is the basic syntax of the create view statement:

create [or replace] 
view [if not exists] view_name[(column_list)]
as 
select-statement;
Code language: SQL (Structured Query Language) (sql)

In this syntax:

• First, specify the name of the view that you want to create after the create view keywords.
• Second, use the or replace clause if you want to overwrite an existing view if the view that you are creating already exists.
• Third, specify the if not exists clause to conditionally create a view only if the view does not exist. Note that you cannot use the if not exists and or replace clauses at the same time.
• Finally, specify a select statement following the as keyword. The view uses the select list of the select statement for its column names. In case you want to use different column names, you can explicitly specify them in parentheses after the view name.

MariaDB create view statement examples

Let’s take some examples of using the create view statement.

A) Creating a view based on multiple tables

The following statement creates a new view called country_info based on three tables: countries, regions, and continents:

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

In this country_info view, we explicitly specified column names of the view after the view name.

After creating the view, you can reference it as it is a table in select statements:

select * 
from country_info
order by country;
Code language: SQL (Structured Query Language) (sql)

To display the statement that creates a view, you use the show create view statement:

show create view view_name;
Code language: SQL (Structured Query Language) (sql)

For example, the following statement shows the create view statement that creates the country_info view:

show create view country_info;
Code language: SQL (Structured Query Language) (sql)

To show all tables and views from the current database, you can use the show full tables statement:

show full tables;
Code language: SQL (Structured Query Language) (sql)

B) Creating a view based on another view

You can use the create view statement to create a new view based on other views.

The following statement creates a view called continent_areas based on the country_info view:

create view continent_areas
as
    select 
        continent, 
        sum(area) total_area
    from 
        country_info
    group by 
    continent;
Code language: SQL (Structured Query Language) (sql)

C) Creating a view based on the summary data of other tables

The following statement creates a view called areas, which return the continent, country, and total area:

create view areas
as
select 
    continents.name continent,
    regions.name region,
    sum(area) total_area
from countries
inner join regions 
    using (region_id)
inner join continents 
    using (continent_id)
group by 
    region, 
    continent;        
Code language: SQL (Structured Query Language) (sql)

In this example, we don’t explicitly specify column names for the view, therefore, the view uses column names in the select list of the select statement as its column names.

In this tutorial, you learned how to use the MariaDB create view statement to create a new view in the database.