mysql views Tutorial

the database view is a virtual table or logical table which is defined as a mysql select query with joins.a database view is similar to a database table, which consists of rows and columns, so you can query data against it.

the fields in a view are fields from one or more real tables in the database.you can add mysql functions, where and join statements to a view and present the data as if the data were coming from one single table.

advantages of database view

  • a database view provides extra security layer.a database view allows you to create only    read-only view to expose read-only data to specific users
  • database view enables backward compatibility
  • a database view enables computed columns

disadvantages of database view

there are several disadvantages of using database views:

  • performance : querying data from a database view can be slow especially if the view is    created based on other views.
  • a view cannot be associated with a trigger
  • tables dependency:you create view based on underlying tables of a database.whenever    you change the structure of those tables that view associates with, you have to change    the view as well.

mysql process queries to the views in two ways:

mysql creates a temporary tables based on the view definition statement and then executes the incoming query on this temporary table.

first, mysql combines the incoming query with the query defined the view into one query.then mysql executes the combined query.


mysql views

a view is a specific look on data from one or more tables.it can arrange data in some specific order, highlight or hide some data.

there are several restrictions that apply to views.here are some of them:

  • the select statement cannot contain a subquery in the from clause
  • the select statement cannot refer to system or user variables
  • a temporary view cannot be created
  • a view cannot be associated with a trigger

the create view statement is used to create the view in mysql:

MySQL Create View syntax:

          create   view    [database_name].[view_name]

                                
                        as        [select    statement]

views must have unique column names with no duplicates, just like base tables.

by default, the names of the columns retrieved by the select statement are used for the view column names.


MySQL Create View example:

                 create  view   saleperorder

                 as
              
                 select     ordernumber,
              
                 sum       (quantityordered * priceeach) total
              
                 from      orderdetails
              
                 group by  ordernumber
              
                 order by   toral  desc;

you can query the view above as follows:

     select    *     from    saleperorder ;

create view with subquery

the following illustrates how to create a view with subquery.

                 create view   products   as

                 select     productcode, productname, buyprice
                 
                 from       products
                 
                 where      buyprice  > ( select   avg  (buyprice)
                 
                                                   from        products
                 )
                 
                 order   by  buyprice   desc;

mysql update view statement

mysql, views are not only read-only but also updateable.however in order to create an updateable view, the select statement that defines the view has to follow several following rules:

  • the select statement must only refer to one database table
  • the select statement must not refer to read-only views
  • the select statement must not contain any expression(aggregates, functions,computed    columns...)
  • the select statement must not use group by or having clause
  • not use distinct

example of creating updateable view

first step, we create a view named officeinfo against the offices table.

the view refer to the three columns of the office table : officecode, phone, city.

    create   view    officeinfo

                     as    select  officecode,  phone,  city
                     
                     from  offices;

in the next step, we can change the phone number using update statement

      update   officeinfo

                set   phone =  '+33147235555'
                
                where  officecode = 4

mysql show  view definition

mysql provides the show create view statement that helps you show view definition.

syntax:

          show    create    view   [database_name].[view_name];

modifying views

you can modify it by using the alter view statement.

the syntax of the alter view statement is similar to the create view statement except the create keyword is replaced by the alter keyword.

syntax:

          alter    view      [database_name].[view_name]

                       as       select   statement;

dropping a view

you can delete a view with the drop view command.

drop view syntax:

          deop view   view_name