22 - MySQL views

MySQL provides a feature of virtual table or logical table called views, user can store its query data into this virtual table and requested from this table when required. Views are stored at the database level like triggers. The query used in the view is generally from the same database although it can be from different database but in that case query should include the database name followed by the table name to identify the table to query the results.

View definition includes a Sql select query ahich may direct query or including JOINS / unions for fetching the results. It is dynamic in nature as it is not related to the physical data, it queries the data at the run time for the given query in definition. So when the data changes, view shows the updated data.

Advantages of View:

  • It gives security as user not able to query the physical table directly and can only access the data from the view defined for this purpose.
  • It simplifies the work as we can store the complex queries and no need to send the query again and again just store it as a definition of a view.
  • View allows complex calculations in the query so that gives extra feature. For example we can calculate count, max, min, average or sum etc in the query.
  • It reduces network because we don’t need to send the query each time to database server just need to call the view by its name.

Disadvantages of View:

View has some disadvantage also, not too much but still has.

  • It degrades the performance to fetch the data from physical level and mostly when a view using other view for the query. Although view results are cached and give performance on next run.
  • It depends upon the existing database structure so if you change the actual database structure than view stop working unless you change its definitions.

MySQL creates the temporary table at the backend which resides in memory, which the query comes into the temporary table and then fetch the data from physical level. View create a single file having the name view_name.frm

Basic syntax of view creation:

      Create View database_name.view_name

      AS

      Select statement…..

For example:

Here test is the database name and view1 is the view created in test database. This view stores the result from the cities table.

Here is the example to fetch the data from the view:

In the above example we insert the data into the cities table and call the view to fetch the data.

To show the definition of a view, you can use the following command:

   SHOW CREATE VIEW databaseName.viewName;

    To modify / alter the definition of view you can use the Alter command:

ALTER
[ALGORITHM =  {MERGE | TEMPTABLE | UNDEFINED}]
  VIEW databaseName.viewName
   AS
[SELECT or JOIN statement]

To delete / drop the view you can use the following command:

      DROP VIEW databaseName.viewName

You can also make a check that If view exists or not so that drop query will not give any error.

     DROP VIEW IF EXISTS databaseName.viewName 

Like us on Facebook