Home arrow MySQL arrow Page 2 - Views and More in MySQL 5.0

Syntax--Creating, Altering, and Dropping Views - MySQL

MySQL 5.0 supports a range of features that earlier versions of MySQL do not support. This article, the fourth in a series, explains views and other new features. It is excerpted from chapter eight of Beginning MySQL Database Design and Optimization: From Novice to Professional, written by Jon Stephens and Chad Russell (Apress, ISBN: 1590593324).

  1. Views and More in MySQL 5.0
  2. Syntax--Creating, Altering, and Dropping Views
  3. Examples
  4. Benefits
  5. Other Improvements in MySQL 5.0
  6. Change in User Variable Behavior
By: Apress Publishing
Rating: starstarstarstarstar / 25
May 11, 2006

print this article



As we’ve already mentioned, a view is based on a SELECT query, and this query usually makes up most of the view’s definition. To define a new view, we use the CREATE VIEW statement, whose standard syntax is shown here. While most databases offer extensions to it, nearly all of them that feature views support the basic form shown here. As you can see, it’s relatively straightforward and shouldn’t be too difficult for you to understand just by looking at it:

CREATE [OR REPLACE] VIEW view (columns) AS select-query

The optional OR REPLACE clause following the CREATE keyword is used to tell MySQL to re-create the view even if it already exists. Otherwise you’ll get an error if you try to create a view with the same name as an existing one.

In the preceding definition, view is the name of the view, which is covered by the same rules that govern other MySQL identifiers. This may be followed optionally by a list of columns--note that these are simply column names, as the types and sizes of the columns are already defined either in the table from which they’re being retrieved or in the SELECT statement. Next comes the AS keyword, which marks the beginning of the select-query used to obtain the data that will serve as the view’s contents.

The optional WITH CHECK OPTION clause following the query is defined in the SQL standard for use in creating a view that permits updates of its underlying table. (Recall that such a view may refer to only one table, as explained in the previous section.) In MySQL, this option doesn’t do anything; it is simply ignored by the command parser.

In addition to CREATE OR REPLACE VIEW , MySQL allows you to update an existing view with an ALTER VIEW command, although this isn’t part of the SQL standard. (Oracle and SQL Server also support this command.) MySQL’s ALTER VIEW replaces the SELECT query currently used in defining the view with a new query. The syntax is as shown here:

ALTER VIEW view (columns) AS

As is the case with CREATE VIEW , the WITH CHECK OPTION clause is ignored in an ALTER VIEW ; it is permitted for compatibility with other databases and the SQL standard but doesn’t actually do anything in MySQL.

In order to retrieve the query that was used to define a view, we use a new variation on the SHOW CREATE command, as shown here:


Dropping a view is also very simple:


The DROP VIEW statement is supported by all major databases that implement views. view stands for the name of the view to be dropped.

These few statements are all that are required to work with views. We’ll provide some demonstrations showing how to use them in the next section.

>>> More MySQL Articles          >>> More By Apress Publishing

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates


Dev Shed Tutorial Topics: