Continuing (from here) the "Discover MySQL's enterprise features" trip, let's check out the Views.
As the name suggests, a view is like a specific predefined way to look at a table or tables. You can create a View out of one table or you can JOIN
a few tables. Back to the `blog`
database example.
Creating a `posts`
table and inserting some data in it.
mysql> CREATE TABLE posts ( -> id INT(11) not null auto_increment primary key, -> title char(255), -> body text -> ); Query OK, 0 rows affected (0.09 sec) mysql> INSERT INTO posts VALUES -> ('','title 1', 'body 1'), -> ('','title 2', 'body 2'), -> ('','title 3', 'body 3'), -> ('','title 4', 'body 4'), -> ('','title 5', 'body 5'), -> ('','title 6', 'body 6') -> ; Query OK, 6 rows affected, 6 warnings (0.03 sec) Records: 6 Duplicates: 0 Warnings: 6
Is the data there?
mysql> SELECT * FROM posts; +----+---------+--------+ | id | title | body | +----+---------+--------+ | 1 | title 1 | body 1 | | 2 | title 2 | body 2 | | 3 | title 3 | body 3 | | 4 | title 4 | body 4 | | 5 | title 5 | body 5 | | 6 | title 6 | body 6 | +----+---------+--------+ 6 rows in set (0.00 sec)
Now we're interested in the last 5 posts (we want to create an RSS feed or display in the "see also" nav or something similar)
mysql> SELECT * FROM posts ORDER BY id DESC LIMIT 0,5; +----+---------+--------+ | id | title | body | +----+---------+--------+ | 6 | title 6 | body 6 | | 5 | title 5 | body 5 | | 4 | title 4 | body 4 | | 3 | title 3 | body 3 | | 2 | title 2 | body 2 | +----+---------+--------+ 5 rows in set (0.00 sec)
Good, it works. Now let's create a view using only ID
and title
.
mysql> CREATE VIEW last_posts AS -> SELECT id, title FROM posts ORDER BY id DESC LIMIT 0,5; Query OK, 0 rows affected (0.01 sec)
Yep, that easy! Now we can SELECT
from the view as if it was a normal table.
mysql> SELECT * FROM last_posts; +----+---------+ | id | title | +----+---------+ | 6 | title 6 | | 5 | title 5 | | 4 | title 4 | | 3 | title 3 | | 2 | title 2 | +----+---------+ 5 rows in set (0.00 sec)
The similarity with a table goes even further, the view will SHOW
up as a table...
mysql> SHOW TABLES; +----------------+ | Tables_in_blog | +----------------+ | last_posts | | posts | +----------------+ 2 rows in set (0.00 sec)
(BTW, SHOW FIELDS FROM last_posts
will also work)
... but you can always tell if a SHOW
n table is a table or a view ...
mysql> SHOW FULL TABLES; +----------------+------------+ | Tables_in_blog | Table_type | +----------------+------------+ | last_posts | VIEW | | posts | BASE TABLE | +----------------+------------+ 2 rows in set (0.00 sec)
... and you can get only views if you want to.
mysql> SHOW FULL TABLES WHERE Table_type = 'VIEW'; +----------------+------------+ | Tables_in_blog | Table_type | +----------------+------------+ | last_posts | VIEW | +----------------+------------+ 1 row in set (0.00 sec)
The views you create will be displayed in phpMyAdmin as well.
A view can be used in JOIN
s as if it was a normal table.
mysql> SELECT last_posts.*, posts.body FROM last_posts -> LEFT JOIN posts ON -> last_posts.id = posts.id; +----+---------+--------+ | id | title | body | +----+---------+--------+ | 6 | title 6 | body 6 | | 5 | title 5 | body 5 | | 4 | title 4 | body 4 | | 3 | title 3 | body 3 | | 2 | title 2 | body 2 | +----+---------+--------+ 5 rows in set (0.00 sec) mysql> SELECT last_posts.*, CONCAT(SUBSTRING(posts.body,1,3), '...') -> AS excerpt -> FROM last_posts -> LEFT JOIN posts ON -> last_posts.id = posts.id; +----+---------+---------+ | id | title | excerpt | +----+---------+---------+ | 6 | title 6 | bod... | | 5 | title 5 | bod... | | 4 | title 4 | bod... | | 3 | title 3 | bod... | | 2 | title 2 | bod... | +----+---------+---------+ 5 rows in set (0.01 sec)
And, as said in the beginnig, you can create a view from a table JOIN
. Or even from joining a table and another view.
mysql> CREATE VIEW last_posts_details AS -> SELECT last_posts.*, CONCAT(SUBSTRING(posts.body,1,3), '...') -> AS excerpt -> FROM last_posts -> LEFT JOIN posts ON -> last_posts.id = posts.id; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM last_posts_details; +----+---------+---------+ | id | title | excerpt | +----+---------+---------+ | 6 | title 6 | bod... | | 5 | title 5 | bod... | | 4 | title 4 | bod... | | 3 | title 3 | bod... | | 2 | title 2 | bod... | +----+---------+---------+ 5 rows in set (0.00 sec) mysql> muahaah-haha! -> ; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'muaha ah-haha!' at line 1 mysql>
Although a view shows up in SHOW TABLES
, you cannot drop it with DROP TABLE
, you need DROP VIEW
.
More in the MySQL manual's Views entry.