Create View in MySQL
A view in MySQL can be created based on a single table or multiple tables. The CREATE VIEW statement is used to create a view in MYSQL.
1. Create a View Based On Single Table
Syntax:
CREATE VIEW <view_name> AS
SELECT <column1>, <column2>……., <columnN>
FROM <table-name>
WHERE [conditions];
Example:
Let’s us create a view named “IITHyderabadStudentsView” from the StudentDetails table. This view selects the students from the StudentDetails table who study in “IIT Hyderabad” university and outputs their details like student id, name, and age.
CREATE VIEW IITHyderabadStudentsView AS
SELECT sid, sname, age
FROM StudentDetails
WHERE university = "IIT Hyderabad";
Output:
Create VIEW – Single table
Now to view the tuples in the IITHyderabadStudentsView, we will query the view the just like how we query a normal table.
SELECT * FROM IITHyderabadStudentsView;
Output:
Query Output – Create VIEW – Single table
2. Create a View Based On Multiple Tables With JOIN Clause
We can create a view by joining multiple tables using the JOIN clause and additionally using WHERE keyword or GROUP BY, HAVING clauses.
Syntax:
CREATE VIEW <view_name> AS
SELECT <column1>, <column2>……., <columnN>
FROM <table1>
[ INNER | LEFT | RIGHT | FULL ] JOIN <table2> ON <joining-column>
WHERE [condition1 | condition2 | ……];
Example:
Let’s us create a view named “PythonEnrolledView” using the StudentDetails, CourseDetails, and EnrolledIn table. This view outputs the students who are enrolled in “Python Fundamentals” course the details as student id, name, and age.
CREATE VIEW PythonEnrolledView AS
SELECT S.sid, S.sname, S.age
FROM StudentDetails S JOIN EnrolledIn E ON S.sid = E.sid
JOIN CourseDetails C ON C.cid = E.cid
WHERE C.cname = "Python Fundamentals";
Output:
Create VIEW – Multiple table using JOIN
Now to view the tuples in the PythonEnrolledView, we will again query the view the just like how we query a normal table.
SELECT * FROM PythonEnrolledView;
Output:
Query Output – Create VIEW – Multiple tables (JOIN)
MYSQL View
MySQL is an open-source RDBMS, i.e. Relational Database Management System which is maintained by Oracle. MySQL has support for major operating systems like Windows, MacOS, Linux, etc. MySQL makes it easy for users to interact with your relational databases, which store data in the form of tables. You can write SQL queries to get the required data from the databases using MySQL.
In this article, we will look at “Views in MySQL”, which act as virtual tables, and understand its advantages, as well as the syntax of Views for creation, updation, and deletion, with the help of awesome examples.