SQL Create View

A view is a virtual table whose contents are defined by a query.

The CREATE VIEW command creates a new view.

The ALTER VIEW command modifies an existing view.

To delete a view use DROP VIEW.

Example

#

This example creates a view that lists customers from Sweden.

CREATE VIEW SwedishCustomer 
AS
  SELECT FirstName, LastName, Phone
    FROM Customer
   WHERE Country = 'Sweden'

A view is just a SQL statement, but one that can be used as if it were a table, like so:

SELECT * 
  FROM SwedishCustomer

Syntax

Syntax to create a new view.

CREATE VIEW view-name 
AS
  SELECT column1, column2, ..., columnN
    FROM table-name
   WHERE condition

Syntax to change (alter) a view.

ALTER VIEW view-name 
AS
  SELECT column1, column2, ..., columnN
    FROM table-name
   WHERE condition

Syntax to remove (drop) a view.

DROP VIEW view-name

More Examples

ALTER VIEW

Problem: Add the City column to the existing SwedishCustomer view.
ALTER VIEW SwedishCustomer 
AS
  SELECT FirstName, LastName, Phone, City
    FROM Customer
   WHERE Country = 'Sweden'
Result:  view modified

USE VIEW

Problem: Lists all Swedish customers (using the above-created view).
SELECT * 
  FROM SwedishCustomer
Result:  2 records.
FirstName LastName Phone City
Christina Berglund 0921-12 34 65 Luleå
Maria Larsson 0695-34 67 21 Bräcke

DROP VIEW

Problem: Delete the SwedishCustomer view.
DROP VIEW SwedishCustomer
Result:  view dropped

You may also like



Guides


vsn 3.1