Introduction

A view is a virtual table. With a view you can take result sets or a joined set of tables and treat them as a single table. You can perform selects and joins on these virtual tables in the same way you can on a 'regular' table. Views are an excellent way of providing short cuts for commonly used joins, or for limiting access to data.

Unlike normalized tables, views may contain repeated data, but that is usually the point of a view. A views job is often to aggregate the data in multiple tables.

Syntax

CREATE VIEW Syntax

        
CREATE VIEW view-name
({column-definition}, ..) AS select-statement

The SELECT statement used to create a view must be a valid selection statement. This makes it easy to test a view creation before it is implemented by simply testing the SELECT you intend to use to create it.

Views can also be created in a GUI such as HeidiSQL, where you only have to provide the appropriate SELECT statement.

Examples

A View Listing Member Books

          
CREATE VIEW vMemberBooks AS
  SELECT member.*, pubTitle
    FROM member
    JOIN pub ON(memberId = pubMemberId)
    WHERE pubPubType = 'b'

Creates a view in the DCRIS schema that lists all member information along with the title of any books the member has written. Each book has its own resulting tuple.

member.* is a short cut that means "include all columns from the member table". pubTitle tells the view to include only that column from the pub table. If not including all columns from a table, the required columns must be named individually.

The clause WHERE pubPubType = 'b' limits the view to only books published by a member.

A View that Lists All Joined Student, Course, and Term Information

          
CREATE VIEW vStudentCourseTerm AS
  SELECT s.studentId, s.surname, s.forename, c.courseId,c.title,
    c.desc AS courseDesc, t.desc AS termDesc,
    t.startDate, t.endDate, sct.status, sct.grade
    FROM studentCourseTerm AS sct
    JOIN student AS s ON(s.studentId = sct.studentId)
    JOIN course AS c ON(c.courseId = sct.courseId)
    JOIN term AS t ON(t.termId = sct.termId)

Creates a view from the multiple joins on the various tables that relate the student, course, and term information:

Note that the course.desc (course description) and term.desc (term description) columns both have the name desc, and one or both of the columns require an alias (courseDesc and termDesc in this case) since column names must be unique. The rest of the columns keep their names less the table aliases.

A View With a Virtual Column

          
CREATE VIEW vSales AS
  SELECT item, sold, price, sold * price AS total
    FROM sales

This view contains the generated column total that is calculated by multiplying the contents of the sold and price columns in each tuple. The value in total is automatically updated whenever the view is used to appropriately match the values in the columns that make it up.

Like all views, this view can then be used like any other table in a selection:

        
SELECT *
  FROM vSales
  WHERE total > 1000
  ORDER BY item

SQL Views

Introduction

Syntax

Examples