View in MySQL Database Syntax

Sql View is another very useful database object, like any other RDBMS, we also can create view in mysql database, here we learn how to create view with multiple tables in mysql database.

View is a logical set of data, can be fetched from single or multiple tables, here are some of the reasons view is so popular to developer

1. View is easy way to exchange data with different user group
2. Creating and altering view never impact actual data
3. We can create view from data source based on business demand
4. We can set where clause and joining multiple tables, that make the code reusable

In this tutorial you will learn about MySql View data handling following points

• How to create view in mysql database
• Alter View in MySQL
• Drop View in MySQL

Create View in mysql database

Before creating any object in database, you need to login with database credential like user name and password, if the user name is associated (has permission) with multiple databases, then you need to select the database you want to work with by using following statement.

    
Create VIEW
    wtrdb.vwadminuser AS
SELECT    
    tbadminprofile.apid,
    tbadminprofile.fname,
    tbadminprofile.lname,
    tbadminprofile.email,
    tbadminprofile.mobile
FROM
    tbadminprofile

Alter View in mySql database

Alter statement allow you to add or rename any column name in table of current database.

Here in example below we are adding a new column name GeoLocation in tbBookmark table, optionally we want the column to be added after column user

ALTER VIEW
    wtrdb.vwadminuser AS
SELECT
    tbadminuser.auid,
    tbadminuser.username,
    tbadminuser.password,
    tbadminuser.isActive,
    tbadminprofile.apid,
    tbadminprofile.fname,
    tbadminprofile.lname,
    tbadminprofile.email,
    tbadminprofile.mobile
FROM
    tbadminuser
INNER JOIN tbadminprofile ON tbadminuser.auid = tbadminprofile.auid;

Notice, the below query is same as above query, but it look much cleaner and short, you can use alias name of big table name, it will be easy to write query and become easy to read and modify later if required.

    ALTER VIEW
    wtrdb.vwadminuser AS
SELECT
    t1.auid,
    t1.username,
    t1.password,
    t1.isActive,
    t2.apid,
    t2.fname,
    t2.lname,
    t2.email,
    t2.mobile
FROM
    tbadminuser AS t1
INNER JOIN tbadminprofile AS t2
ON
    t1.auid = t2.auid;

Delete and Drop View in mySql database

As we have seen that View is just a logical set of data , so writing delete statement will never delete actual data from tables , so the statement make no sense, even same for Insert, we cannot execute insert statement in view.

But, we can drop a view, if we drop any view that will not make any impact on actual data, but the view object will be deleted permanently.

drop view `vwadminusertest`;