EDUCBA

EDUCBA

MENUMENU
  • Blog
  • Free Courses
  • All Courses
  • All in One Bundle
  • Login
Home Data Science Data Science Tutorials MySQL Tutorial Views in MySQL

Views in MySQL

By Priya PedamkarPriya Pedamkar

Views in MySQL

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:

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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 Email 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 Email 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 Email 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.

  1. Top 3 MySQL Operators
  2. Different MySQL Query Commands
  3. Key Differences of Inner Join vs. Outer Join
  4. MySQL Outer Join | How to Use?
C++ PROGRAMMING Certification Course
38+ Hours of HD Videos
9 Courses
5 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
ASP.NET Certification Course
149+ Hours of HD Videos
28 Courses
5 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
SQL - Everything in SQL
253+ Hours of HD Videos
51 Courses
6 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
SOFTWARE TESTING Certification Course
74+ Hour of HD Videos
13 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
Primary Sidebar
Popular Course in this category
MYSQL Certification Course
 115+ Hours of HD Videos
18 Courses
3 Mock Tests & Quizzes
  Verifiable Certificate of Completion
  Lifetime Access
4.5
Price

View Course
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more