Introduction to Views in MySQL
The following article provides an outline for Views in MySQL. SQL view is nothing but a virtual table of the database. The view contains fields like a real table, but those fields are from one or more tables in the database which is executed by running a bunch of MySQL queries. We can perform operations like WHERE and JOIN clauses in the virtual tables. On the other hand, VIEW is nothing but SELECT queries.
Syntax:
CREATE VIEW view_name AS
SELECT column1, column2,…
FROM table;- “CREATE VIEW view_name” commands MySQL to create a view/virtual table in the name of view_name.
- The “AS SELECT column1, column2 FROM table” statement fetches column1 and column2 from the actual table. Then it saves those fields in the virtual table.
How to Create Views in MySQL?
Let’s create one customer table with the following attributes:
| Customer_id | Customer_name | Contact_no | Purchased_amount | City | |
| 184 | Ravi Kumar | 9887463893 | [email protected] | 8000.00 | Kolkata |
| 987 | Vinay Das | 9839878678 | [email protected] | 12000.00 | Delhi |
| 452 | K.Amarnath | 7598759387 | [email protected] | 15000.00 | Kolkata |
| 874 | Abhinash Desai | 7675878798 | [email protected] | 5000.00 | Mumbai |
We will create one customer_archive table with our required attribute.
Code:
CREATE VIEW customer_archive AS
SELECT customer_id, customer_name, contact_no, city
FROM customer;Output:
| Customer_id | Customer_name | Contact_no | City |
| 184 | Ravi Kumar | 9887463893 | Kolkata |
| 987 | Vinay Das | 9839878678 | Delhi |
| 452 | K.Amarnath | 7598759387 | Kolkata |
| 874 | Abhinash Desai | 7675878798 | Mumbai |
We can also apply the condition to this query while creating a view.
Code:
CREATE VIEW customer_archive AS
SELECT customer_id, customer_name, contact_no, purchased_amont, city
FROM customer
WHERE purchased_amont > 10000;Output:
| Customer_id | Customer_name | Contact_no | Purchased_amount | City |
| 987 | Vinay Das | 9839878678 | 12000.00 | Delhi |
| 452 | K.Amarnath | 7598759387 | 15000.00 | Kolkata |
Different View Options in MySQL
Following are the different view options in MySQL:
1. DROP: A view/virtual table can be deleted using the DROP VIEW command. Suppose we want to delete the customer_archive table.
Syntax:
DROP VIEW customer_archive;2. CREATE OR REPLACE: We can update a view / virtual table with the CREATE OR REPLACE VIEW command.
Syntax:
CREATE OR REPLACE VIEW view_name AS
SELECT column1, column2,….
FROM table;3. JOIN: We also can create a view by joining multiple tables. This join will fetch matched records from both tables. Different kinds of joins are named inner join, left join, right join, full outer join, cross join, etc.
Syntax:
CREATE VIEW view-name AS
SELECT column1, column2, column3, …
FROM table_name1 INNER JOIN table_name2
ON table_name1.column = table_name2.column;Above is an example of an inner join. In the same manner, we can also apply other joins. In the above example, a view will be created by consolidating the records present in table_name1 and table_name2 based on a common field.
Examples of Views in MySQL
Let us consider the above example:
| Customer_id | Customer_name | Contact_no | Purchased_amount | City | |
| 184 | Ravi Kumar | 9887463893 | [email protected] | 8000.00 | Kolkata |
| 987 | Vinay Das | 9839878678 | [email protected] | 12000.00 | Delhi |
| 452 | K.Amarnath | 7598759387 | [email protected] | 15000.00 | Kolkata |
| 874 | Abhinash Desai | 7675878798 | [email protected] | 5000.00 | Mumbai |
| 987 | Aseem Kumar | 9697679867 | [email protected] | 18000.00 | Cuttack |
| 989 | Sakti | 9847984788 | [email protected] | 60000.00 | Cuttack |
This is the customer table database.
We will create another view of it, naming it as a premium_customer. Conditions for premium customers will be while purchased_amount will be greater than 10000.
Code:
CREATE VIEW premium_customer AS
SELECT customer_id, customer_name, contact_no, purchased_amont, city
FROM customer
WHERE purchased_amont > 10000;Output:
| Customer_id | Customer_name | Contact_no | Purchased_amount | City |
| 987 | Vinay Das | 9839878678 | 12000.00 | Delhi |
| 452 | K.Amarnath | 7598759387 | 15000.00 | Kolkata |
| 987 | Aseem Kumar | 9697679867 | 18000.00 | Cuttack |
| 989 | Sakti | 9847984788 | 60000.00 | Cuttack |
If we want to drop this premium_customer virtual table, then below is the syntax.
DROP VIEW premium_customer;If some modification is required to this above premium table by modifying some conditions, then
Code:
CREATE OR REPLACE VIEW premium_customer AS
SELECT customer_id, customer_name, contact_no, email, purchased_amont, city
FROM customer
WHERE purchased_amount > 6000;Output:
| Customer_id | Customer_name | Contact_no | Purchased_amount | City | |
| 184 | Ravi Kumar | 9887463893 | [email protected] | 8000.00 | Kolkata |
| 987 | Vinay Das | 9839878678 | [email protected] | 12000.00 | Delhi |
| 452 | K.Amarnath | 7598759387 | [email protected] | 15000.00 | Kolkata |
| 987 | Aseem Kumar | 9697679867 | [email protected] | 18000.00 | Cuttack |
| 989 | Sakti | 9847984788 | [email protected] | 60000.00 | Cuttack |
Advantages and Disadvantages of Views in MySQL
Given below are the advantages and disadvantages mentioned:
Advantages
- Security: Many tables are restricted from many users as some attributes in those tables will be very sensitive. So, if we can create views with some specific attributes for respective users, users can access some views in an authorized database. This can maintain the security and integrity of data, and users can perform their tasks with respective authorized columns.
- Query Simplicity: A view can be created by fetching data from several tables. So, all the cumulative records from all tables can be represented by a single table using the view query.
- Structural Simplicity: we can create a specialized or personalized user-specific view. So we can represent the database as a set of virtual tables that make sense to the user.
- Consistency: we are mentioning consistency here because this view can represent a consistent and unchanged image of the database structure even if we manipulate the main table or master table.
- Data Integrity: if the data is accessed to a view, the database always checks the data to ensure whether it satisfies the integrity constraints or not.
Disadvantages
- Performance: Views are the virtual table or representative of master tables. When we run some queries to create a view, DBMS translates those queries against the views into the queries in underlying tables. So, if the view query is complex and contains multiple sources and difficult algorithms, simple action against those views takes considerable time.
- Update Restrictions: While changing rows in a view, the DBMS must translate the request into an update on rows of the underlying source table. The update can be done in the simple query, but in the case of a complex query, DBMS won’t allow updating as views are often restricted to read-only.
Conclusion
After going through the above-described stuff, we can know the significance of this command. This comes in handy in many real-time scenarios. The significant advantage is that we can perform many complex queries to understand our underlying algorithm’s effectiveness. Maintaining data security and integrity is the crucial advantage of view command.
Recommended Articles
We hope that this EDUCBA information on “Views in MySQL” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
