- MySQL Home
- MySQL tutorial
- MySQL Installation
- MySQL Statements
- MySQL Data Types
- Mysq CreateDatabase
- MySQL Create Table
- MySQL Constraints
- MySQL Primary key
- MySQL Foreign key
- Auto Increment Field
- MySQL Default Cons
- MySQL Insert Into
- MySQL Update
- MySQL Drop
- MySQL Delete
- MySQL Select
- MySQL Where
- MySQL Group By
- MySQL Having clause
- MySQL Order By
- MySQL Alter table
- MySQL Alias
- MySQL Create Index
- MySQL Subqueries
- MySQL Operators
- Mysql IsNull IsNotNull
- MySQL And or
- MySQL Between
- MySQL In Operator
- MySql Like Operator
- Mysql Union Operator
- Mysql Storage Engine
- Mysql Stored Procedure
- Mysql if Statement
- Mysql Case Statement
- MySQL Loops
- MySQL Cursor
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.
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:
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.
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.
show create view [database_name].[view_name];
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.
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