MySQL 5.0 New Features: Views

by Trudy Pelzer

Introduction

This book is for the long-time MySQL user who wants to know "what's new" in version 5. The short answer is "stored procedures, triggers, views, and information schema". The long answer is the MySQL 5.0 New Features series, and this book is the third in that series.

What I'm hoping to do is make this look like a hands-on session where you, as if you're working it out yourself on your keyboard, can walk through the sample problems. To do this, I'll go through each little item, building up slowly. By the end, I'll be showing larger views that do something useful, as well as some things that you might have thought were tough.

Conventions and Styles

Whenever I want to show actual code, such as something that comes directly from the screen of my mysql client program, I switch to a Courier font, which looks different from the regular text font. For example:

mysql> DROP VIEW v CASCADE;
Query OK, 0 rows affected (0.00 sec)

When the example is large and I want to draw attention to a particular line or phrase, I highlight it with a double underline and a small arrow on the right of the page. For example:

mysql> CREATE VIEW v AS
    -> SELECT column1 AS c /* view col name is c */
    -> FROM table1;
Query OK, 0 rows affected (0.01 sec)

Sometimes I will leave out the mysql> and -> prompts, so that you can cut the examples and paste them into your copy of the mysql client program. (If you aren't reading the text of this book in a machine-readable form, try looking for the script on the mysql.com web site.)

All of the examples in this book were tested with the publicly-available alpha version of MySQL 5.0.3 on the SUSE Linux operating system (version 9.1). By the time you read this, the version number will be higher and the available operating systems will include Windows, Sparc, and HP-UX. So I'm confident that you'll be able to run every example on your own computer. But if not, well, as an experienced MySQL user you know that help and support is always available.

A Definition and an Example

A table is any collection of one or more columns and zero or more rows:

  +---------------+---------------+-------+
  | column name   | column name   | ...   |
  +---------------+---------------+-------+
  | column value  | column value  | ...   |
  | ...           | ...           | ...   |
  +---------------+---------------+-------+

With MySQL and the MyISAM storage engine, a table's contents (the column values) are in a pair of files (the .MYD and .MYI data and index files), stored on disk. This type of table is a base table, called "base" because it is basic, foundational, the basement of the structure. On a level above the base tables we find derived tables, whose column values come from base tables, from literals, or from environmental variables like CURRENT_TIME. You produce a derived table with SQL whenever you issue commands like "FROM table1, table2" or "GROUP BY x" or just "SELECT ..." - all of these are operations that, given a table, produce another table. The table that a SELECT produces is a result set. It has no name. But if you could give a result set a name and store that name (along with other data related to the definition, i.e., the metadata) you would have a viewed table, usually called - for short - a view. Thus:

A view is a named, derived table whose definition is a persistent part of the database.

To make a view, you say CREATE VIEW, plus the view name, plus the SELECT that defines the view. Here's an example:

mysql> CREATE VIEW v AS SELECT column1 FROM t;
Query OK, 0 rows affected (0.01 sec)

You can always SELECT from a view. Some views are updatable — that is, you can perform INSERT and DELETE operations on them. And some updatable views are also "insertable-into" — that is, you can perform INSERT operations on them. For example:

mysql> INSERT INTO v VALUES (1);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM v;
+---------+
| column1 |
+---------+
|       1 |
+---------+
1 row in set (0.00 sec)

Read On

The whole article is long, so we thought it better to make it a PDF. To download, click here (no registration required!) You could even print out the PDF so you can peruse at your leisure.