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

CREATE DATABASE VATSA

 

 

Create a table

CREATE TABLE Employee(
    id int,
    first_name VARCHAR(15),
    last_name VARCHAR(15),
    start_date DATE,
    end_date DATE,
    city VARCHAR(10),
    description VARCHAR(15)
);

 

 

Insert records into table:-

INSERT INTO Employee(id, first_name, last_name, start_date, end_Date, City, Description) VALUES(1, 'Admin', 'Martin', '19960725', '20060725', 'Toronto', 'Programmer');
INSERT INTO Employee(id, first_name, last_name, start_date, end_Date, City, Description) VALUES(2, 'Test', 'Mathews', '19760321', '19860221', 'Vancouver', 'Tester');
INSERT INTO Employee(id, first_name, last_name, start_date, end_Date, City, Description) VALUES(3, 'Vatsa', 'Smith', '19781212', '19900315', 'Vancouver', 'Tester');
INSERT INTO Employee(id, first_name, last_name, start_date, end_Date, City, Description) VALUES(4, 'Rohit', 'Rice', '19821024', '19990421', 'Vancouver', 'Manager');
INSERT INTO Employee(id, first_name, last_name, start_date, end_Date, City, Description) VALUES(5, 'Vijay', 'Black', '19840115', '19980808', 'Vancouver', 'Tester');
INSERT INTO Employee(id, first_name, last_name, start_date, end_Date, City, Description) VALUES(6, 'John', 'Green', '19870730', '19960104', 'New York', 'Tester');
INSERT INTO Employee(id, first_name, last_name, start_date, end_Date, City, Description) VALUES(7, 'David', 'Larry', '19901231', '19980212', 'New York', 'Manager');

 

 

View table

 

To view a table Employee, we use a select query that returns all the records from a table Employee.

select * from Employee;

 

 

Create a view

 

Create View is used to create a view in the database. Let’s see:

CREATE OR REPLACE VIEW myView AS
SELECT id, first_name, city, description FROM employee
WHERE id = 3 WITH LOCAL CHECK OPTION;

 

 

View

SELECT * FROM myView;

 

 

SHOW View

 

To show all the present “Views” inside the database, type the following command in your Workbench.

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.

 

 

 

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

rename table myview to mytestview

 

 

To test the new name, type the SHOW FULL TABLES command in your Workbench

 

 

 

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

DROP View vatsa.myview;

 

 

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!