The world's most popular open source database
Trudy Pelzer is a senior software architect with MySQL AB, co-author of SQL Performance Tuning, and lives in Edmonton, Alberta.
by Trudy Pelzer
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.
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 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
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)
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.