📖 MySQL Database Views

Views are used in MySQL to create a limited access to table data through user-level filtering. A view is a tool used to limit access to a database for a specific application or user. Views are derived from one or more base tables in a database. Views are created using a defining query, much like creating a table, and once created, can be used just like a table.

Benefits of Using a View
  • Simplifies query creation.
  • Can increase security by hiding sensitive information.
  • Provides customized information.
  • Provides data independence. If a base table is changed, the view can still be used - possibly without changes.
  • With certain restrictions views can be used to update data from a base table (see below).

Format

CREATE VIEW viewName AS
Required. Identifies the name of the view to be created.
SELECT ...
Required. Identifies the query that defines the view. Any valid SELECT statement can be used here.
WITH CHECK OPTION
Optional. For updatable views, will not allow the update if it will remove the data row from the view. The WITH CHECK OPTION prevents a view from updating or inserting rows that are not visible through it.
; (semi-colon)
Required. Indicates the end of the SQL statement.

Example

CREATE VIEW us_customers AS
    SELECT 
        *
    FROM
        invoice
    WHERE
        BillingCountry = 'USA';
-- creates a view of customers in the US from the invoice table
Using a View to Update Data

Updating a database may or may not be possible through a view. Updatable views cannot include any of the following.

  • DISTINCT clause
  • Aggregate functions
  • GROUP BY or HAVING
  • UNION

To Insert data using a view, any columns not in the view must accept nulls. Updates and deletions can be made through a view if only one table is included in the view. Updates to joined tables in a view can be made if both tables have the same primary key and are joined on that primary key.

Dropping a View

Format

DROP VIEW viewName;