How to create a view in MySQL
MySQL is an open-source relational database management system (RDBMS) that uses SQL. It is mainly used to query and manage database systems. A view is a virtual table composition of predefined SQL queries that are not stored but only shown to the user. It is created by selecting particular rows from the tables and showing only the required rows in the new table form to avoid confusion. A view can be created by joining one or more than one table.
Views in MySQL have advantages and disadvantages. Let’s take a look at both.
Pros of using views in MySQL:
- Simplified data access
- Data abstraction
- Security and access control
- Performance optimization
- Data consistency
Cons of using views in MySQL:
- Performance overhead
- Limited update functionality
- Increased maintenance complexity
- Dependency management
- Limited portability
Syntax of a view
The following is the syntax for creating a view in a database:
CREATE VIEW viewname ASSELECT col1, col2, .... colnFROM tablenameWHERE <condition>
Working flow of a view
We can see the working flow of a view in the illustration given below:
Code example
Now we can see the view by using the example below:
-- Create a view and assign a name to the tableCREATE VIEW Highest_Paid_Employee AS-- Select the rows we need to show in the table by using the viewSELECT ID, Name, Salary, DesignationFROM Employees-- Apply condition to select the particular data we needWHERE salary > 60000;-- Here, we display the table we made by using the viewSELECT * from Highest_Paid_Employee
Code explanation
- Line 2: We create a
VIEWand assign a table name that we can easily use without making the view multiple times. - Lines 4–5: We
SELECTthe attributes from theEmployeestable, which we need to display. - Line 7: We apply the condition using the
WHEREclause to make a view table. - Line 10: We use the
SELECTstatement to display the view table.
Free Resources