How To Create Views In MariaDB

This MariaDB tutorial will teach how to create views in the MariaDB server. In this section, we’ll examine and wrap up a few scenarios to help you better comprehend the subject. Below is a list of every subject we’ll cover.

• How to create a view on the table in the MariaDB?
• How to create a view on the table by the MariaDB SELECT statement?
• How to create a view on the table by the MariaDB CREATE VIEW IF NOT EXISTS statement?
• How to show created view of the table in the MariaDB?
• How to create a view with the help of the MariaDB WHERE condition on the table?
• How to create a view on the table with the MariaDB LIKE clause?
• How to create a view on the table with the MariaDB JOIN clause?
• How to create a view on the table with the MariaDB LEFT JOIN clause?
• How to create or replace the view on the table?
• How to use the MariaDB SUBQUERY for the creation of a view on the table?
• How to create a view on the table by using the MariaDB YEAR function?

How to Create Views In MariaDB

Here, we will learn and understand how to create a view on the table by using the MariaDB CREATE VIEW statement and which is explained with the help of syntax and an example.

In MariaDB, the CREATE VIEW statement is used to create a new view in the database. Here is the syntax of the MariaDB CREATE VIEW statement by the following query:

SYNTAX:

CREATE [ OR REPLACE ] VIEW [ IF NOT EXISTS ] VIEW_NAME
[ COLUMN_LISTS ] 
AS 
SELECT_STATEMENT;

In the syntax explanation:

• First, we need to specify the name of the view by using the CREATE VIEW keywords.
• Second, we can use the REPLACE keyword to replace the existing view if that view already exists in the MariaDB Server.
• Third, specify the IF NOT EXISTS clause to conditionally create a view if the view does not exist. Please remember that we can’t use the IF NOT EXISTS or REPLACE keyword at the same time.
• Finally, use the SELECT statement by following the AS keyword. The column names in the view are taken from the choice list in the select statement. You can explicitly give different column names in parenthesis after the view name if you want to use them.

Here is a sample example of the MariaDB CREATE VIEW statement on the table by the following query:

EXAMPLE:

CREATE VIEW USASTATES 
(STATE_ID, STATE_NAME,STATE_SHORTFORM,STATE_POPULATION,STATE_SIZE)
AS SELECT * FROM STATES_OF_USA;

SELECT * FROM USASTATES;

In the above query, we created a view called USASTATES on the STATES_OF_USA table using the CREATE VIEW statement. In the CREATE VIEW statement, it will carry out all column_list from the STATES_OF_USA table. Then we used the SELECT statement to retrieve all records from the STATES_OF_USA table which is followed by the AS keyword.

If we want to see the name of the VIEW, we have used the SELECT statement to retrieve all records from the USASTATES view.

We hope that you have understood the subtopic “Learning to create views in MariaDB” by using the MariaDB CREATE VIEW and SELECT statements on the table by the query. For a better understanding, we have used a sample example and explained it in deepness.

Read: Add Data To Table MariaDB

How To Create Views In MariaDB Select

Here, we will learn and understand how to create a view on the table by using the MariaDB SELECT statement which is shown in the following query:

EXAMPLE:

CREATE VIEW STATES_IN_USA 
(STATE_ID, STATE_NAME,STATE_SHORTFORM,STATE_POPULATION,STATE_SIZE)
AS SELECT * FROM USA_STATES
WHERE STATE_ID>=20
ORDER BY STATE_SHORTFORM DESC;

SELECT * FROM STATES_IN_USA;

In the query mentioned above, we have used the CREATE VIEW statement to create a view called STATES_IN_USA on the USA_STATES table. In the STATES_IN_USA view, it carries all the column_list which are similar to all column_name in the USA_STATES table.

Then we used the SELECT statement to retrieve all records from the USA_STATES table with the WHERE condition and which is followed by the AS keyword.

In the WHERE condition, the STATE_ID column is used with the GREATER THAN or EQUAL TO operator to find a value greater than or equal to 20 from the USA_STATES table and then arrange the records of the STATE_SHORTFORM column in descending order by using the DESC keyword.

If the WHERE condition turns out to be TRUE then the SELECT statement will retrieve all records from the USA_STATES table and the view will be created only then. If the SELECT statement retrieves an empty record set from the USA_STATES table and has been executed successfully only when the WHERE condition gets a FALSE value.

Once the view has been created with the help of CREATE VIEW and SELECT statement on the current table. If we want to check all records from the STATESIN_USA view then we will use the SELECT statement to retrieve all records from it.

We hope that you have understood the subtopic “Learning to Create Views in MariaDB Select” by using the MariaDB CREATE VIEW statement to create a view on the table by the query. We have used a sample example and explained it in deepness, for better understanding.

Read: MariaDB See If Table Exists

Create Views In MariaDB If Not Exists

In this MariaDB subtopic tutorial, we will learn and understand how to create a view on the table if not exists in the MariaDB Server, which is explained with the help of a sample example.

EXAMPLE:

CREATE VIEW IF NOT EXISTS ANTHEM_COMPANY
AS 
SELECT * FROM USA_ANTHEM_COMPANY
WHERE PATIENT_ID>=10
GROUP BY GENDER
ORDER BY PATIENT_FIRSTNAME ASC, 
PATIENT_LASTNAME DESC;

SELECT * FROM ANTHEM_COMPANY;

As we see in the above query, we have created a view called ANTHEM_COMPANY on the USA_ANTHEM_COMPANY table by using the CREATE VIEW statement. Then we used the IF NOT EXISTS statement to check if the ANTHEM_COMPANY view already exists in the current database or not. But if it already exists then the MariaDB will throw an error.

Then in the AS clause which is followed by the SELECT statement. In the SELECT statement, it will retrieve all records from the USA_ANTHEM_COMPANY table with the WHERE condition.

In the WHERE condition, the PATIENT_ID column is used with the GREATER THAN or EQUAL TO operator to find a value greater than or equal to 10 from the USA_ANTHEM_COMPANY table. Then we used the GROUP BY clause to arrange the records of the GENDER column in a group which is based on the gender types and shown in the USA_ANTHEM_COMPANY table.

In the end, we have then arranged the records of the PATIENT_FIRSTNAME column in ascending order and the records of the PATIENT_LASTNAME column in descending order by using the ORDER BY expression [ DESC | ASC ].

Once the view has been created in the current database then we will use the SELECT statement to retrieve all records from the ANTHEM_COMPANY view.

We hope that you have understood the subtopic “Learning to Create Views In MariaDB If Not Exists” by using the MariaDB CREATE VIEW IF NOT EXISTS statement on the table by the query. We have used an illustrated example and defined it in abysm, for a better understanding.

Read: MariaDB Add Column With Default Value

Create Views in MariaDB Show

We will learn and understand how to create a view on the table and then show created view by the query, which is explained with the help of a sample example.

In MariaDB, the ORDER BY clause is used to arrange the records of the column_name or expression by the query. If we want to arrange the records in ascending order then use the ASC keyword after the ORDER BY clause and in descending order use the DESC keyword after the clause.

Normally, programmers don’t use the ASC keyword to arrange the records of the column in ascending order. It automatically arranges the records of the table in ascending order without the ASC keyword.

The group by clause in MariaDB divides a result’s rows into categories. The aggregate functions count(), min(), max(), sum(), and avg() are frequently used with the group by function to obtain attributes of groups.

For example, the number of elements (count), the total of values (sum), the maximum element (max), the minimum element (min), and the average of elements (avg).

EXAMPLE:

CREATE VIEW WALMART_COMPANY
(ID,FIRST_NAME,LAST_NAME,CUSTOMER_DATE,IP_ADDRESS)
AS SELECT * FROM USA_WALMART_COMPANY
GROUP BY LAST_NAME
ORDER BY FIRST_NAME DESC;

SHOW CREATE VIEW WALMART_COMPANY;

In the as query mentioned above, we have used the CREATE VIEW statement to create a view called WALMART_COMPANY on the USA_WALMART_COMPANY table. In the WALMART_COMPANY view, it carries all the column_list which are the same as column_names in the USA_WALMART_COMPANY table.

Then followed by the AS keyword, we used the SELECT statement to retrieve all records from the USA_WALMART_COMPANY table with the GROUP BY clause. In the GROUP BY clause, we have grouped them by the LAST_NAME column then we have arranged them in descending order of the FIRST_NAME column by using the DESC keyword in the ORDER BY clause.

If we want a full view of the CREATE VIEW statement then we will use the SHOW CREATE VIEW statement on the view_name. And it is shown in the second above query.

By utilizing the MariaDB CREATE VIEW statement on the table by the query, we hope you have grasped the subtopic “Learning to Create Views in MariaDB Show.” We used a concrete example and went into great detail to describe it for a better understanding.

Read: How to Show Tables in MariaDB

How To Create Views In MariaDB Based On Condition

With the help of a sample example and an explanation using the MariaDB WHERE condition in the query, we will learn and comprehend how to create a view on the table in this lesson on the MariaDB subtopic.

EXAMPLE:

CREATE VIEW USA_WALMARTCOMPANY
AS 
SELECT * FROM WALMART_CUSTOMER
WHERE ID>=10;

SELECT * FROM USA_WALMARTCOMPANY;

In the above query, we created a view called USA_WALMARTCOMPANY on the WALMART_CUSTOMER table using the CREATE VIEW statement. Then followed by the AS keyword, we used the SELECT statement to retrieve all records from the WALMART_CUSTOMER table with the WHERE condition.

In the WHERE condition, the ID column is used with the GREATER THAN or EQUAL TO operator to find a value greater than or equal to 10 from the WALMART_CUSTOMER table. If the WHERE condition turns out to be TRUE then the SELECT statement to retrieve all records from the WALMART_CUSTOMER table otherwise vice-versa.

If we want to check if new records are there in the USA_WALMARTCOMPANY view then we will again use the SELECT statement to retrieve all records from it.

We hope that you have understood how to create a view with the help of the WHERE condition on the table by the query. For a better understanding, we have used a demonstrated example and defined it in deepness.

Read: MariaDB Alter Table Add Index

How to Create Views In MariaDB Like Query

Here, we will learn and understand how to create a view on the table with the help of the MariaDB LIKE clause in the query, which will be explained with the help of a sample example.

EXAMPLE:

CREATE VIEW LISTOF_CUSTOMER
AS 
SELECT * FROM CUSTOMER_LIST
WHERE ID>=5 
AND LAST_NAME LIKE '%a';

SELECT * FROM LISTOF_CUSTOMER;

In the comprehend query, we have created a view called LISTOF_CUSTOMER on the CUSTOMER_LIST table by using the CREATE VIEW statement. Then the AS keyword followed by the SELECT statement will retrieve all records from the CUSTOMER_LIST table with the WHERE condition.

In the WHERE condition, the ID column is used with the GREATER THAN or EQUAL TO operator to find a value greater than or equal to 5 from the CUSTOMER_LIST table. And in the end, we have used the LIKE clause on the LAST_NAME column to find name ends with the alphabet a from the CUSTOMER_LIST table.

If the WHERE condition turns out to be TRUE then the SELECT statement will retrieve all records from the CUSTOMER_LIST table otherwise vice-versa.

By utilizing the MariaDB CREATE VIEW statement on the table with the LIKE clause in the query, we hope that you have comprehended the subtopic “Learning to Create Views in MariaDB Like Query.” To facilitate understanding, we provided a detailed explanation and presented a sample example.

Read: MariaDB Add Auto Increment Column

How to Create Views In MariaDB Join

Here, we will learn and understands how to create a view on two tables by using the MariaDB JOIN statement and which is explained with the help of a sample example.

In MariaDB, JOIN is used to merge the rows from more than one table based on common columns in tables. In other words, The data is extracted from more than one table into a single table using the JOIN clause.

• The JOIN clause can be used when there are two or more two tables with common columns.

There are four types of JOIN in MariaDB:

• INNER JOIN: It is a simple JOIN that retrieves all the rows from more than one table where the JOIN condition is True.
• LEFT JOIN: It is a LEFT OUTER JOIN that retrieves all the rows from the left table based on whatever is specified in the ON condition and returns those rows from another table where the JOIN condition is TRUE.
• RIGHT JOIN: It is a RIGHT OUTER JOIN that retrieves all the rows from the right table based on whatever is specified in the ON condition and returns those rows from another table where the JOIN condition is TRUE.
• CROSS JOIN: It returns the result set where each row in a table is joined with each row in another table.

EXAMPLE:

CREATE VIEW JOIN_STATEMENT
AS 
SELECT SKULLCANDY.NAME,SKULLCANDY.PRICE,SKULLCANDY_USA.SKULLCANDY_NAME,
SKULLCANDY_USA.SKULLCANDY_USPRICE 
FROM SKULLCANDY
INNER JOIN SKULLCANDY_USA
ON SKULLCANDY.ID=SKULLCANDY_USA.SKULLCANDY_ID
WHERE SKULLCANDY.ID>=2;

SELECT * FROM JOIN_STATEMENT;

As we see in the above query, we have created a view called JOIN_STATEMENT on two join tables i.e; SKULLCANDY and SKULLCANDY_USA. Then we used the SELECT statement followed by the AS keyword in the query.

In the SELECT statement, it retrieves all records from the NAME, PRICE, SKULLCANDY_NAME, and SKULLCANDY_USPRICE columns from both tables i.e; SKULLCANDY and SKULLCANDY_USA. And it makes the inner join the SKULLCANDY_USA table to the SKULLCANDY table which is based on the ON condition.

On the ON condition, both tables’ common column makes joins with the EQUAL TO operator which will help to make joins.

In the WHERE condition, the ID column of the SKULLCANDY table is used with the GREATER THAN or EQUAL TO operator to find a value greater than or equal to 2 in the query.

If we want to make confirm whether the view has been created yet or not then use the SELECT statement to retrieve all records from the JOIN_STATEMENT view.

By creating a view on the table with the MariaDB CREATE VIEW statement and utilizing the INNER JOIN statement in the query, we hope you have gained an understanding of the subtopic “Learning to Create Views in MariaDB Join.” We have thoroughly explained it with a sample example to aid in understanding.

Read: MariaDB Create Database And User

How to Create Views In MariaDB Left Join

In this MariaDB subtopic tutorial, we will learn and understand how to create a view on the table by using the MariaDB LEFT JOIN in the query, which is explained with the help of an illustrated example.

EXAMPLE:

CREATE VIEW LEFTJOIN_STATEMENT
AS 
SELECT SKULLCANDY.NAME,skullcandy.PRICE,SKULLCANDY_USA.SKULLCANDY_NAME,
SKULLCANDY_USA.SKULLCANDY_USPRICE 
FROM SKULLCANDY
LEFT JOIN SKULLCANDY_USA
ON SKULLCANDY.ID=SKULLCANDY_USA.SKULLCANDY_ID;


SELECT * FROM LEFTJOIN_STATEMENT;

As we see in the above query, we have created a view called LEFTJOIN_STATEMENT on two join tables i.e; SKULLCANDY and SKULLCANDY_USA. Then we used the SELECT statement followed by the AS keyword in the query.

In the SELECT statement, it retrieves all records from the NAME, PRICE, SKULLCANDY_NAME, and SKULLCANDY_USPRICE columns from both tables i.e; SKULLCANDY and SKULLCANDY_USA. And it makes the inner join the SKULLCANDY_USA table to the SKULLCANDY table which is based on the ON condition.

On the ON condition, both tables’ common column makes joins with the EQUAL TO operator which will help to make joins.

To make the view has been created and has been there in the current database, we have used the SELECT statement to retrieve all records from the LEFTJOIN_STATEMENT view. Instead, we can use the SHOW FULL TABLES statement to view_name on the table with the WHERE condition.

By creating a view on the table with the MariaDB CREATE VIEW statement and utilising the LEFT JOIN statement in the query, we hope you have gained an understanding of the subtopic “Learning to Create Views in MariaDB Left Join.” We have thoroughly explained it with a sample example to aid in understanding.

Read: MariaDB Unique Key

How to Create Views In MariaDB or Replace

Here we will learn how to create or replace a view on the table by the query, which will be explained with the help of a sample example.

EXAMPLE:

CREATE OR REPLACE VIEW USA_HARVARD_UNIVERSITY
AS 
SELECT * FROM HARVARD_UNIVERSITY
WHERE STUDENT_ID>=5;

SELECT * FROM USA_HARVARD_UNIVERSITY;

In the first query, we used the CREATE OR REPLACE VIEW statement to create a view called USA_HARVARD_UNIVERSITY on the HARVARD_UNIVERSITY table. This statement main purpose is that if view_name with this name has been already created in the current database then the MariaDB will throw an error.

Then the AS keyword with the SELECT statement has been used in the query. In the SELECT statement, it will retrieve all records from the HARVARD_UNIVERSITY table with the WHERE condition.

In the WHERE condition, the STUDENT_ID column is used with the GREATER THAN or EQUAL TO operator to find a value greater than or equal to 5 from the HARVARD_UNIVERSITY table. If the WHERE condition turns out to be TRUE then the SELECT statement will receive all records from the HARVARD_UNIVERSITY table otherwise vice-versa.

Once the view has been created and all records of the HARVARD_UNIVERSITY table has been transferred. To confirm, we have used the SELECT statement to retreive all records from the USA_HARVARD_UNIVERSITY view.

By utilizing the MariaDB CREATE VIEW OR REPLACE statement to create a view on the table by the query, we hope you have understood the subtopic “Learning to Create Views In MariaDB or Replace.” For better comprehension, we also used a sample example and provided a thorough explanation.

Read: MariaDB Select Unique

How to Create Views In MariaDB Subquery

In this MariaDB subtopic section, we will learn and understand how to create a view on the table by using the MariaDB SUBQUERY by the query. And which will be explained with the help of a sample example.

A subquery in MariaDB is a query inside another query. Within your SQL statements, subqueries can be created. These subqueries may be found in the SELECT, FROM, or WHERE clauses. It is also called an INNER QUERY or INNER SELECT statement.

EXAMPLE:

CREATE VIEW SUBQUERY
AS
SELECT S.ID, S.NAME FROM SKULLCANDY S 
WHERE S.ID IN
(SELECT SK.SKULLCANDY_ID
FROM SKULLCANDY_USA SK WHERE SK.SKULLCANDY_ID > 2);

SELECT * FROM SUBQUERY;    

In the preceding query, the CREATE VIEW statement has been used to create a view called SUBQUERY on the two tables i.e; SKULLCANDY and SKULLCANDY_USA. Then we have used the SELECT statement which is followed by the AS keyword in the query.

In the SELECT statement, it will retrieve all records of the ID and NAME columns from the SKULLCANDY table with the WHERE condition. In the WHERE condition, the ID column is used with the IN condition for the subquery of another table in the query.

In the subquery, the SELECT statement will retrieve all records of the SKULLCANDY_ID column from the SKULLCANDY_USA table with the WHERE condition. In the WHERE condition, the SKULLCANDY_ID column is used with the GREATER THAN operator to find a value greater than 2 from the SKULLCANDY_USA table.

If the WHERE condition turns out to be TRUE then the SELECT statement will retrieve all records from the SKULLCANDY table otherwise vice-versa. Once the view has been created and available in the current database but we want to check it, we will use the SELECT statement to retrieve all records from the SUBQUERY view.

By utilizing the MariaDB CREATE VIEW statement on the table returned by the query, we hope you have comprehended the subtopic “Learning to Create Views in MariaDB Subquery.” We have thoroughly explained it with a sample example to aid in understanding.

Read: MariaDB Date Greater Than

How to Create Views In MariaDB Year

We will learn and understand how to create a view on the table with the help of the YEAR function by the query, which will be explained with the help of a sample example.

In MariaDB, the YEAR function is used to extract the year portion value from the expression or column_name by the query.

EXAMPLE:

CREATE VIEW YEAR_VIEW
AS 
SELECT STUDENT_FIRSTNAME,STUDENT_LASTNAME,
YEAR(STUDENT_ADMITDATE) AS YEAR_VALUE FROM HARVARD_UNIVERSITY
WHERE STUDENT_ID>=5
ORDER BY STUDENT_FIRSTNAME DESC;

SELECT * FROM YEAR_VIEW;

In the aforementioned query, the CREATE VIEW statement used to create a view called YEAR_VIEW on the HARVARD_UNIVERSITY table. Then the AS keyword is followed by the SELECT statement.

In the SELECT statement, it will retrieve all records from the STUDENT_FIRSTNAME and STUDENT_LASTNAME columns from the HARVARD_UNIVERSITY table. In the YEAR function, it will retrieve year portion value from the STUDENT_ADMITDATE column from the HARVARD_UNIVERSITY table. And to shorter the function name, we have used the ALIAS clause with the AS keyword and given the name as YEAR_VALUE for the output column_name.

In the WHERE condition, the STUDENT_ID column is used with the GREATER THAN or EQUAL TO operator to find a value greater than or equal to 5 from the HARVARD_UNIVERSITY table and then arrange the records of the STUDENT_FIRSTNAME column in descending order by using the DESC keyword.

If the WHERE condition turns out to be TRUE then the SELECT statement will retrieve all records from the HARVARD_UNIVERSITY table otherwise vice-versa.

Once the view has been created by the name, we have used the SELECT statement to retrieve all records from the YEAR_VIEW view.

By utilizing the MariaDB CREATE VIEW statement to create a view on the table by using the YEAR function in the query, we hope that you have understood the subtopic “Learning to Create Views in MariaDB Year.” We have thoroughly defined it and included a sample example to help with understanding.

Also, take a look at some more MariaDB tutorials.

• MariaDB Check Empty String
• MariaDB Check String Length
• MariaDB Check Constraint
• MariaDB Insert Into Select

After reading this lesson, we were able to use the Learning To Create Views In MariaDB as described in this tutorial. To better understand the concept, we have also explored a few examples. The subjects we have discussed are listed below.

• How to create a view on the table in the MariaDB?
• How to create a view on the table by the MariaDB SELECT statement?
• How to create a view on the table by the MariaDB CREATE VIEW IF NOT EXISTS statement?
• How to show created view of the table in the MariaDB?
• How to create a view with the help of the MariaDB WHERE condition on the table?
• How to create a view on the table with the MariaDB LIKE clause?
• How to create a view on the table with the MariaDB JOIN clause?
• How to create a view on the table with the MariaDB LEFT JOIN clause?
• How to create or replace the view on the table?
• How to use the MariaDB SUBQUERY for the creation of a view on the table?
• How to create a view on the table by using the MariaDB YEAR function?