In this tutorial, we will learn about the Views in MySQL. We will see the database view, how to create it, and how to do operations on it. We are going to learn about views with some simple as well as complex examples. We can also insert, update and delete the data using the views. But, in this tutorial, we will only see how to create views with examples. First, let’s understand database views and why we need them?
Also read: MySQL Select Statement – Fetching Data from MySQL Databases
What is Database View?
A database view or simply a view is a virtual table based on other base tables of the database. Views are the same as the regular table, but those are created using other tables. By definition, views are named queries stored in the database catalog.
You might be thinking why do we need views when we already have tables? The answer is simple; simplicity, security, and backward compatibility.
Let’s take an example. Suppose you have four tables in your database, and you need to gather the data from those four tables and display it in a single table on the front end. The catch is you need to show the data on more than four or five pages, and the query consists of complex logic. How will you do it? You can accomplish it using views.
Views in MySQL help simplify the business logic and complex queries.
Enough theory. Let’s see how to create views using some examples.
Syntax to Create Views in MySQL
Creating views in MySQL is as simple as creating tables. We use CREATE VIEW statement to create a view. Check syntax below-
CREATE VIEW viewName
AS SELECT column_names
FROM table_namesCode language: SQL (Structured Query Language) (sql)You can create a view from either a single table or multiple tables using the above syntax. Along with it, you can use aggregate functions such as MIN, MAX, GROUP By clause, WHERE and HAVING clause, joins, and so on.
We will take examples below.
Examples of Views in MySQL
We will start by creating two tables named students and hobbies. In the first table, we will store all the student information such as id, name, and age. In the second table, we will store the hobby id, hobby name, and the student id that the hobby belongs to.
Note that we are creating simple tables for the sake of simplicity. You can have a more complex table structure in your project where views can be used.
Create table students and insert data into it-
CREATE TABLE students(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100),
age INT(2)
);Code language: SQL (Structured Query Language) (sql)INSERT INTO students(name,age) VALUES
("Jenos", 23), ("John",21),("Vicky",22),
("Gini",22),("Montis",24),("Baki",21);Code language: SQL (Structured Query Language) (sql)SELECT * FROM students;Code language: SQL (Structured Query Language) (sql)
Now create a table hobbies and insert data into it-
CREATE TABLE hobbies(
id INT PRIMARY KEY AUTO_INCREMENT,
stud_id INT,
hobby VARCHAR(50),
FOREIGN KEY(stud_id)
REFERENCES students(id)
);Code language: SQL (Structured Query Language) (sql)We will insert some data in the hobbies table as well. You can check the inserted data below-
SELECT * FROM hobbies;Code language: SQL (Structured Query Language) (sql)
Now, we will create a view to store the id, names of students, and hobbies.
CREATE VIEW stud_hobb
AS SELECT s.id,s.name,h.hobby
FROM students AS s, hobbies AS h
WHERE s.id=h.stud_id;Code language: SQL (Structured Query Language) (sql)Once you execute the above statement, a view named stud_hobb will be created and stored in your database. You can treat it as a table while doing operations on it.
Let’s check what data we have fetched in our view table.
SELECT * FROM stud_hobb;Code language: SQL (Structured Query Language) (sql)
The above data is not in a sorted format. We can order it in ascending format using the ORDER BY clause.
SELECT * FROM stud_hobb ORDER BY id ASC;Code language: SQL (Structured Query Language) (sql)
As stated earlier, the view is exactly the same as a table which allows you to execute queries. You can also use aggregate functions and difference clauses on the views. Check the following example.
SELECT id, name, count(hobby) from stud_hobb GROUP BY id;Code language: SQL (Structured Query Language) (sql)Here, we have used the COUNT aggregate function to get the count of hobbies of each student using the GROUP BY clause.
The above example is straightforward for better understanding the functionality of views in the database. You can take it to the next level and use more complex business logic to create the views.
Get List of Created/Available Views in MySQL
If you want to check the views that are available in your database, you can use the SHOW TABLES statement on the command line. However, it will return a list of all tables along with the Views. It is impossible to find out which is the view. Therefore, you can use the following statement to get the list of available views in your database.
If you have selected the database, then you can use –
SHOW FULL TABLES WHERE TABLE_TYPE LIKE 'VIEW';Code language: SQL (Structured Query Language) (sql)If you have not selected any database and want to get a view list of another database, you can use-
SHOW FULL TABLES IN database_name WHERE TABLE_TYPE LIKE 'VIEW';Code language: SQL (Structured Query Language) (sql)I have chosen the database; therefore, I will get the following output if I use the first query.
Conclusion
In this tutorial, we learned what the view is and how to create it. Apart from just creating a view, we can also use it to update the data in the base table using INSERT and UPDATE statements. This is known as an updatable view, and we will see it in the following tutorial. You can check the updatable views from here.
References
MySQL Official Documentation on views.