How to Create View in MariaDB

In this MariaDB tutorial, we will learn about the “MariaDB create view” and cover the following topics.

MariaDB create view syntax
How to create view with parameters in MariaDB
MariaDB create view with index and primary key
MariaDB create view with subquery
MariaDB create view union
MariaDB show create view
MariaDB grant create view

Table of Contents

MariaDB create view syntax

The CREATE VIEW statement generates a new view or replaces an existing one if the OR REPLACE clause is specified in the MariaDB. The view is a virtual table that exists for a temporary time.

The syntax to create a view is given below.

CREATE
    [OR REPLACE]
    VIEW [IF NOT EXISTS] view_name [(column_list)]
    AS select_statement;

Run the below query to create a new view as temp_view from a table large_csv that contains the information about the state and city in the United States.

CREATE VIEW temp_view AS SELECT city,state_id FROM large_csv;

Here in the above code, we are creating the new view as temp_view with columns city and state_id, these two columns exist in the table large_csv which already exists in the current database.

Let’s show the temp_view table.

SELECT * FROM temp_view;

The above output shows that temp_view contains the two columns city and state_id from the large_csv table.

This is how to create a view in MariaDB.

Read Replace Function in MariaDB

MariaDB create view with index and primary key

We cannot create the index and primary key on view in the MariaDB because views are temporary table or relation that are dropped after the session end or it exist for the current session.

MariaDB create view with subquery

The subquery is a query within the query or when one query contains another query. CREATE VIEW statement can create a view from the subquery in MariaDB.

Let’s create the new view from a subquery as query_view.

CREATE VIEW query_view AS SELECT city, population FROM (SELECT * FROM large_csv where density < 2600) t1;

Here in the above code, creating a view as query_view that contains the columns city, a population whose density is less than 2600 from a table large_csv using the filter WHERE clause and SELECT statement.

Now show the records from the query_view.

SELECT * FROM query_view;

This is how to create view with subquery in MariaDB.

Read How to import CSV files in MariaDB

MariaDB create view union

The UNION operator in the MariaDB is used to group the result of two or more SELECT statements. The UNION operator removes the duplicate values or rows between the SELECT statements. Here we will create a view using the UNION operator from a table.

Create the table as Employee using the below command.


CREATE TABLE Employee(
    emp_id            INT,
   emp_first_name    VARCHAR(15),
   emp_last_name     VARCHAR(15),
   emp_start_date    DATE,
   emp_end_date      DATE,
   emp_salary        FLOAT(8,2),
   emp_address          VARCHAR(50),
   emp_description   VARCHAR(15));

Insert the following records.

insert into Employee(emp_id,emp_first_name, emp_last_name, emp_start_date, emp_end_date,   emp_salary,  emp_address,       emp_Description)
             values (1,'Jason',    'Martin',  '19960725',  '20060725', 1234.56, 'Canada',  'Programmer');

 insert into Employee(emp_id,emp_first_name, emp_last_name, emp_start_date, emp_end_date,   emp_salary,  emp_address,       emp_Description)
              values(2,'Alison',   'Mathews',  '19760321', '19860221', 6661.78, 'United States','Tester');


 insert into Employee(emp_id,emp_first_name, emp_last_name, emp_start_date, emp_end_date,   emp_salary,  emp_address,       emp_Description)
              values(3,'James',    'Smith',    '19781212', '19900315', 6544.78, 'United States','Tester');



 insert into Employee(emp_id,emp_first_name, emp_last_name, emp_start_date, emp_end_date,   emp_salary,  emp_address,       emp_Description)
              values(4,'Celia',    'Rice',     '19821024', '19990421', 2344.78, 'United States','Manager');



 insert into Employee(emp_id,emp_first_name, emp_last_name, emp_start_date, emp_end_date,   emp_salary,  emp_address,       emp_Description)
              values(5,'Robert',   'Black',    '19840115', '19980808', 2334.78, 'United States','Tester');



 insert into Employee(emp_id,emp_first_name, emp_last_name, emp_start_date, emp_end_date,   emp_salary,  emp_address,       emp_Description)
              values(6,'Linda',    'Green',    '19870730', '19960104', 4322.78,'New York',  'Tester');



 insert into Employee(emp_id,emp_first_name, emp_last_name, emp_start_date, emp_end_date,   emp_salary,  emp_address,       emp_Description)
              values(7,'Davemp_id',    'Larry',    '19901231', '19980212', 7897.78,'New York',  'Manager');



 insert into Employee(emp_id,emp_first_name, emp_last_name, emp_start_date, emp_end_date,   emp_salary,  emp_address,       emp_Description)
              values(8,'James',    'Cat',     '19960917',  '20020415', 1232.78,'United States', 'Tester');

Let’s create the view as Union_View.

CREATE VIEW Union_View AS
    SELECT * FROM Employee where emp_id = 1
    UNION SELECT * FROM Employee where emp_id = 2
    UNION SELECT * FROM Employee where emp_id = 3;

Here, we are creating the view as Union_View that contains the unique records from the table Employee by combining the three SELECT statements using the Union operator.

Now, display the records of view using the below query.

SELECT * FROM Union_View;

The above output shows the records from the Union_View which has been created using the Union operator in MariaDB.

This is an example of MariaDB creating a view union.

Read MariaDB Insert Multiple Rows

MariaDB show create view

SHOW CREATE VIEW statement shows a CREATE VIEW statement that generates the given view, as well as the character set used by the connection when the view was created in the MariaDB.

Let’s create the new view as para_view using the below query.

CREATE VIEW para_view AS SELECT city, state_name FROM large_csv WHERE density < 2600;

In the above code, creating the new view as para_view with columns city, state_name that exist in the table large_csv.

How to Create View in MariaDB — MariaDB para view

Now, use the SHOW CREATE VIEW on the para_view.

SHOW CREATE VIEW para_view;

Check out, MariaDB Timestamp

MariaDB grant create view

In the MariaDB, the GRANT statement is used to grant the privileges on database objects like database, table and columns. So here we will grant the CREATE VIEW privilege on the table.

The syntax to grant the privilege is given below.

GRANT privileges_name ON db_name.table_name TO 'user'@'server_address';

Where privileges_name is the naming privilege that we want to apply on database objects, db_name.table_name is the name of database objects like columns, table, here it is the table name. ‘user’ is the current user of the MariaDB and ‘server_address’ where MariaDB server is running like localhost.

Now we are going to grant the CREATE VIEW privilege on the table countries which already exist in the database.

GRANT CREATE VIEW ON mysql.countries TO 'root'@'localhost';

MariaDB grant create view

As the above output, we can the result of running query is Query OK which means we have applied the CREATE VIEW on table countries.