EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials MySQL Tutorial Views in MySQL
 

Views in MySQL

Priya Pedamkar
Article byPriya Pedamkar

Updated May 10, 2023

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.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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 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?

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - 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
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

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
EDUCBA

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

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW