MySql view – basics

By Peter

The name of this database object,  view in MySQL can give straight away an answer of the functionality. A view or “virtual table” can be created to retrieve data from tables actually hold the data or from other views.  Update, delete, insert statements can be made on the base tables trough a view which can be really useful.

  • Views can be used to define a complicated query to give a specific set of data from tables which gives a good shortcut for later work.
  • View can be used as layer of security by defining what sort of data is available trough a view and to allow modifications or not. If a client(PHP) has access for view’s in a database just the necessary data can be retrieved and/or modified.
  • Another good advantage of using views to handle data from an application is that if there is a need to split tables for performance reasons, the application is “calling” the view so base tables can be split up, the view can be modified to retrieve the same data from more tables but the application doesn’t need to be modified.

The CREATE VIEW syntax and a simple view:

(The detailed explanation about creating view can be find here!)
The OR REPLACE can be used if there is already a view with same name and obviously it will be replaced with the new view just been defined.
The algorithm can be defined, but if it’s undefined MySQL will pick the best to use. Also if set MERGE but the view is defined in the way it prevents the use of MERGE, MySQL issues a warning and automatically resets the algorithm to UNDEFINED. If TEMPTABLE is the algorithm type the statement in the view will be processed and the data will be stored in a temporary table which has the advantage that the data in the base tables can’t be updated.
WITH CHECK OPTION is checking data modifications and if the modification is not possible eg. the modified row would not meet the criteria set in the view or base table the modification won’t be issued. An example for this

Statements for view in MySQL:

  • Show full tables; – to see the tables and views in a database
  • Alter VIEW viewName as select_statment; – to modify a view but it works as  if REPLACE is used
  • Drop view [if exists] viewName; – to delete view
  • CHECK TABLE viewName\G  – to check base table connection
  • SHOW CREATE VIEW viewName\G – to see the how the view was created and what is in the select statement
  • select, insert, update, delete

[post_view]