VIEW in MySQL

INTRODUCTION

 
In this tutorial, I am going to explain about VIEW in MySQL with examples. Without wasting time, let’s start.
 
In this tutorial, I have described VIEW in MySQL with examples. This article covers the following topics:
  1. View
  2. Advantages of MySQL View
  3. Create View
  4. Show View
  5. Rename View
  6. Drop View
You are advised to read Installing MySQL on Windows.
 

VIEW

 
Views are stored queries. A view acts as a virtual table. A view consists of rows & columns just like the table. The difference between table and view is that view are definitions built on top of other tables (or views).
 
There are some points to define a View as follows:
  • It is used to restrict access to the database.
  • Hide data complexity.
  • A view is stored as a select statement in the database.
  • DML operations on a view like Insert, Update, Delete effects.
There are several rules which SELECT statement has to follows:
  • In the SELECT statement, subquery cannot be included.
  • Variables such as local, user, and session variables cannot be used in the SELECT statement.
  • A prepared statement cannot be used in the view.
  • Temporary tables or views cannot be used in the SELECT statements and any tables or views which referred by views must exist.
  • View cannot be associated with triggers.

Advantages of MySQL Views

 
MySQL has various advantages as
  1. It simplifies complex queries.
  2. It adds an extra security layer.
  3. Make business login consistent

Creating a View

 
Create View statement is used to create a view in the database.
 
Syntax :
 
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
 
Now let’s discuss an example. First of all, we have to create a database and a table ‘Employee’.
 
Create a Database
  1. CREATE DATABASE VATSA  
createdatabase 
 
Create a table
  1. CREATE TABLE Employee(  
  2.     id int,  
  3.     first_name VARCHAR(15),  
  4.     last_name VARCHAR(15),  
  5.     start_date DATE,  
  6.     end_date DATE,  
  7.     city VARCHAR(10),  
  8.     description VARCHAR(15)  
  9. );  
createtable 
 
Insert records into table:-
  1. INSERT INTO Employee(id, first_name, last_name, start_date, end_Date, City, Description) VALUES(1, 'Admin''Martin''19960725''20060725''Toronto''Programmer');  
  2. INSERT INTO Employee(id, first_name, last_name, start_date, end_Date, City, Description) VALUES(2, 'Test''Mathews''19760321''19860221''Vancouver''Tester');  
  3. INSERT INTO Employee(id, first_name, last_name, start_date, end_Date, City, Description) VALUES(3, 'Vatsa''Smith''19781212''19900315''Vancouver''Tester');  
  4. INSERT INTO Employee(id, first_name, last_name, start_date, end_Date, City, Description) VALUES(4, 'Rohit''Rice''19821024''19990421''Vancouver''Manager');  
  5. INSERT INTO Employee(id, first_name, last_name, start_date, end_Date, City, Description) VALUES(5, 'Vijay''Black''19840115''19980808''Vancouver''Tester');  
  6. INSERT INTO Employee(id, first_name, last_name, start_date, end_Date, City, Description) VALUES(6, 'John''Green''19870730''19960104''New York''Tester');  
  7. INSERT INTO Employee(id, first_name, last_name, start_date, end_Date, City, Description) VALUES(7, 'David''Larry''19901231''19980212''New York''Manager');  
insert 
 
View table
 
To view a table Employee, we use a select query that returns all the records from a table Employee.
  1. select * from Employee;  
viewtable  
 
Create a view
 
Create View is used to create a view in the database. Let’s see:
  1. CREATE OR REPLACE VIEW myView AS  
  2. SELECT id, first_name, city, description FROM employee  
  3. WHERE id = 3 WITH LOCAL CHECK OPTION;  
createview 
 
View
  1. SELECT * FROM myView;   
view 
 

SHOW View

 
To show all the present “Views” inside the database, type the following command in your Workbench.
  1. SHOW FULL TABLES  
If you are using the “SHOW FULL TABLES” command to view all the tables inside the database, then myView is also shown here.
 
showview 
 

RENAME VIEW

 
Rename is used to change the name of the view table, views and tables share the same namespace and may create confusion.
 
Syntax
 
Rename table original_view_name to new_view_name
 
Rename a view
  1. rename table myview to mytestview  
renameview 
 
To test the new name, type the SHOW FULL TABLES command in your Workbench
 
showfullview 
 

DROP View

 
Drop View is used to delete a View from the database.
 
Syntax
 
DROP View IF EXISTS schema_name.View_name;
 
Note:
If you want to remove multiple views at once, then use the following syntax.
 
DROP View IF EXISTS View_name1, View_name2, View_name3…;
 
Drop a View
  1. DROP View vatsa.myview;  
 dropview
 

CONCLUSION

 
In this article, I have discussed the concept of VIEW in MySQL with various examples.
 
I hope you enjoyed this article. Follow C# Corner to learn more new and amazing things about MySQL.
 
Thanks for reading this article!


Similar Articles