MariaDB Create View

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.

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:

Creation of Departments Table

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:

Creation of Jobs Table

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:

Creation of Employees Table

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

Insertion in Departments Table

Insertion in Jobs Table

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

Insertion in Jobs Table

Insertion in Employees Table

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

Output:

Insertion in Employees Table

Creating a View Based on Multiple Tables

The following view is created to display the employees name along with their department and job title with respect to their id. The INNER JOIN keyword is used to match the id of employees, departments and jobs table to provide the respective result.

CREATE VIEW employee_details AS
SELECT e.name, d.name AS department, j.title AS job_title
FROM employees e
INNER JOIN departments d ON e.department_id = d.id
INNER JOIN jobs j ON e.job_id = j.id;

SELECT * FROM employee_details;

Output:

Explanation: In the above query, we have creates a virtual table named employee_details that combines data from the employees, departments, and jobs tables. It selects the employee’s name, their department’s name, and their job title by joining these tables based on their respective IDs.

Creating a View Based on Another View

The following view is created to count the employees based on the job title from the employee_details view. We use the COUNT( ) to get the number of employees which we group by job title.

CREATE VIEW employee_count_by_job_title AS
SELECT job_title, COUNT(*) AS employee_count
FROM employee_details
GROUP BY job_title;
SELECT * FROM employee_count_by_job_title ;

Output:

Creating a view based on another view

Explanation: In the above query, we have creates a virtual table named employee_count_by_job_title that counts the number of employees for each job title. It selects the job title and the count of employees with that title from the employee_details view, grouping the results by job title. The SELECT * FROM employee_count_by_job_title statement then retrieves the data from this view.

Creating a View Based on The Summary Data of other Tables

The following view is used to get the count of the employees based on the department. We use the COUNT( ) to get the number of employees which we group by department name. To join the employees and department table we use the INNER JOIN keyword to display result by matching the employees id to the respective department.

CREATE VIEW employee_count_by_department AS
SELECT d.name AS department, COUNT(*) AS employee_count
FROM employees e
INNER JOIN departments d ON e.department_id = d.id
GROUP BY d.name;

SELECT * FROM employee_count_by_department;

Output:

Creating a view based on the summary data of other tables

Explanation: In the above query, we have creates a virtual table named employee_count_by_department that counts the number of employees in each department. It selects the department name (d.name) and the count of employees in that department from the employees and departments tables, joining them on the department_id field. The results are grouped by department name. The SELECT * FROM employee_count_by_department statement then retrieves the data from this view.

Conclusion

MariaDB CREATE VIEW statement is a very useful and time saving features as we can save the SELECT statements which is frequently being queried. It can also be used to simplify complex queries and manipulate them and you can also use it to replace an existing query without any deletion. Knowing when to use the statement is a deciding factor for efficient data monitoring and management.