MySQL views

From Cosmin's Wiki

Jump to: navigation, search

Home > MySQL > MySQL views



MySQL 5.0 supports a range of features that earlier versions of MySQL did not support. Unnamed views became possible in MySQL 4.1 with the inclusion of subselects that allowed for the use of derived tables. Beginning with version 5.0.1, MySQL includes support for named views, usually referred to simply as “views.”

Views are also known as virtual tables, because they are defined in terms of (other) tables through the use of queries. A view can be thought of as a “window” into a table, or perhaps a viewport that shows us a selected portion of a table at any given time. Even if views are seen as tables (you could try the following:)

SHOW TABLES;

they are quite a bit more. That is, if one of the tables (in the select statement defining the view) changes, then so does the view contents, that is, the view gives you a live or dynamic snapshot of the tables' contents.

Enough talking. The general syntax for views in MySQL is:

CREATE
    [OR REPLACE]
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = { user | CURRENT_USER }]
    [SQL SECURITY { DEFINER | INVOKER }]
    VIEW view_name [(column_list)]
    AS select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]

The CREATE VIEW statement creates a new view, or replaces an existing one if the OR REPLACE clause is given. If the view does not exist, CREATE OR REPLACE VIEW is the same as CREATE VIEW. If the view does exist, CREATE OR REPLACE VIEW is the same as ALTER VIEW.

It’s also quite possible to select data from one view in defining another view; generally this isn’t a recommended practice, but it can be and is done with databases that support views, the most notable exception to this being PostgreSQL.

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. To define explicit names for the view columns, the optional column_list clause can be given as a list of comma-separated identifiers. The number of names in column_list must be the same as the number of columns retrieved by the SELECT statement.

When you want to retrieve the query that was used to define a view, while in the case of tables you would normally do:

SHOW CREATE TABLE <tablename>;

here you will do:

SHOW CREATE VIEW <viewname>;

Dropping a view is also very simple:

DROP VIEW <viewname>;

Examples

Imagine we have 2 related tables, CITY and COUNTRY (CITY contains a countryid column, foreign key to COUNTRY(id)). Now, if we want to create a view which will display cityname and countryname, we can do like this:

CREATE TABLE COUNTRY 
( 
	id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
	name varchar(50)
) engine=innodb;
 
CREATE TABLE CITY 
( 
	id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
	name varchar(50),
	countryid int NOT NULL,
	FOREIGN KEY (countryid) REFERENCES COUNTRY(id)
) engine=innodb;
 
CREATE OR REPLACE VIEW view_city_country (city, country)
AS
    SELECT ci.name, co.name
    FROM CITY ci
    JOIN COUNTRY co ON co.id = ci.countryid;

which, when queried will output:

mysql> SELECT * FROM view_city_country;
+-------------+----------------+
| city        | country        |
+-------------+----------------+
| New York    | United States  |
| Los Angeles | United States  |
| Washington  | United States  |
| London      | United Kingdom |
| Bucharest   | Romania        |
+-------------+----------------+
5 rows IN SET (0.00 sec)


However, if you try:

CREATE OR REPLACE VIEW view_city_country
AS
    SELECT ci.name, co.name
    FROM CITY ci
    JOIN COUNTRY co ON co.id = ci.countryid;

you will get an error message like this:

ERROR 1060 (42S21): Duplicate COLUMN name 'name'

because you're not allowed to have the same column name twice (and in this case, as we didn't specity the column names for the view, they were automatically taken from the select statement. However, if you do :

CREATE OR REPLACE VIEW view_city_country
AS
    SELECT ci.name AS 'city', co.name AS 'country'
    FROM CITY ci
    JOIN COUNTRY co ON co.id = ci.countryid;

you will get the same result as above.