How to Create View in MariaDB?

Creating views in MariaDB involves defining a query that selects data from one or more tables and then saving this query as a view. Views can be used to simplify complex queries, provide a layer of abstraction over the underlying tables, and improve performance by storing the results of frequently used queries.

The CREATE VIEW statement is used to create or replace an existing view by providing the view name and column names which is optional. We can create a view with the help of WHERE, GROUP BY, HAVING, and ORDER BY. We will understand the below examples to get a better understanding as follows.

1. Creating a View Based on Multiple Tables
2. Creating a View Based on Another View
3. Creating a View Based on The Summary Data of Other Tables

Syntax of Create View:

CREATE [OR REPLACE] VIEW [IF NOT EXISTS] view_name [column1,column2, ...]
AS SELECT ... FROM .... [WHERE ...] [GROUP BY ...] [HAVING ...] [ORDER BY ...];

Let’s set up an environment to CREATE View in MariaDB

To understand How to Create a View in MariaDB we need a table on which we will perform various operations and queries. Here we will consider two table called Departments which contain id and name as Columns. Also,

The following query creates the table departments.

CREATE TABLE departments 
(
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL
);

Output:

The following query creates the table Jobs which consists of id, title as Columns.

CREATE TABLE jobs (
  id INT PRIMARY KEY AUTO_INCREMENT,
  title VARCHAR(50) NOT NULL
);

Output:

The following query creates the table employees which consists of id, name, department_id, job_id as Columns. Here department_id specifies that the department_id column in the employees table is a foreign key.

The FOREIGN KEY (job_id) REFERENCES jobs(id) statement in the query ensures that the job_id column in the employees table references the id column in the jobs table for maintaining referential integrity between the two tables.

CREATE TABLE employees (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  department_id INT NOT NULL,
  job_id INT NOT NULL,
  FOREIGN KEY (department_id) REFERENCES departments(id),
  FOREIGN KEY (job_id) REFERENCES jobs(id)
);

Output:

After Inserting Some data into the departments table. The Table looks:

Insertion in Jobs Table

After Inserting Some data into the Jobs table. The Table looks:

Insertion in Employees Table

After Inserting Some data into the employees table. The Table looks:

Output:

Views in MariaDB are powerful tools that allow us to create virtual tables based on the result set of a SELECT query. They provide a way to simplify complex queries, improve security by limiting access to certain columns,, and enhance performance by precomputing expensive operations. In this article, we will explore how to create views in MariaDB along with multiple examples.