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
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.

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';

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.
Related MariaDB tutorials:
- How to Create Function in MariaDB
- MariaDB ERROR 1064
- MariaDB Window functions
- How to Create Trigger in MariaDB
- Drop Column from MariaDB Table
- How to Add Column in MariaDB
- MariaDB Primary Key With Examples
Here, we have learned about “how to create a view in MariaDB” and covered the following topics.
- MariaDB create view syntax
- MariaDB create view with parameters
- 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
I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.
Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.