Views in MySQL 5.0
Posted by: Adam Plocher on May 04, 2006
WHAT IS A VIEW?
A view is a pre-compiled virtual table that is generated by a user-defined SELECT statement. Unlike tables, views don't physically store data on the database server, instead they act as aliases to existing tables.
WHY USE VIEWS?
Views are used to customize the data that gets returned from a SELECT query.
BEFORE WE BEGIN
The following CREATE TABLE queries should be executed on a test database, if you plan to follow along with my examples:
CREATING A VIEW
Views are relatively easy to use. The syntax for creating a view is:
The following command will create a simple view that pulls the title and txt field from the News table:
To see your view in action all you will need to do is run a normal SELECT statement against the view (instead of a table). The following query will run the view you just created, which will select all title and txt fields from the News table.
To create a view with MySQL Query Browser, simply right click on the database (in the right column) and click Create New View. Enter a view name and click Create View. You will then be presented with a code template for creating a view.
MODIFYING A VIEW
To get a list of views, you can use the SHOW TABLES command. Here is a SHOW TABLES query that will display only views and filter out tables:
To see the code behind the view, run:
The code it returns will probably be slightly different than the code you created, but it should do the same thing. It will include all of the optional CREATE VIEW attributes, which you didn't include in the example above. You should be able to copy and paste the code and change the CREATE VIEW to ALTER VIEW. Let's also JOIN the NewsCategories table so that we can see the CatName each news record is associated with, while SELECTing from our view. So it should look something like this:
You can easily create and modify views using the Query Browser tool. If you're using the latest version of MySQL Query Browser, you should see your view listed along with your tables under your selected database in the right column. If this is the case, you can right click on the View and click Edit View.
A view is a pre-compiled virtual table that is generated by a user-defined SELECT statement. Unlike tables, views don't physically store data on the database server, instead they act as aliases to existing tables.
WHY USE VIEWS?
Views are used to customize the data that gets returned from a SELECT query.
BEFORE WE BEGIN
The following CREATE TABLE queries should be executed on a test database, if you plan to follow along with my examples:
CREATE TABLE NewsCategories
( catID int not null auto_increment, catName varchar(32), primary key(catID));
CREATE TABLE News
( newsID int not null auto_increment, catID int not null, title
varchar(32) not null, txt blob, primary key(newsID));
INSERT INTO NewsCategories (catName) VALUES ('Main');
INSERT INTO News (catID, title, txt) VALUES (1, 'My Article!', 'Hello World');
CREATING A VIEW
Views are relatively easy to use. The syntax for creating a view 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 following command will create a simple view that pulls the title and txt field from the News table:
CREATE VIEW newsView AS SELECT title, txt FROM News;
To see your view in action all you will need to do is run a normal SELECT statement against the view (instead of a table). The following query will run the view you just created, which will select all title and txt fields from the News table.
SELECT * FROM newsView
To create a view with MySQL Query Browser, simply right click on the database (in the right column) and click Create New View. Enter a view name and click Create View. You will then be presented with a code template for creating a view.
MODIFYING A VIEW
To get a list of views, you can use the SHOW TABLES command. Here is a SHOW TABLES query that will display only views and filter out tables:
SHOW FULL TABLES WHERE Table_type='VIEW'
To see the code behind the view, run:
SHOW CREATE VIEW newsView
The code it returns will probably be slightly different than the code you created, but it should do the same thing. It will include all of the optional CREATE VIEW attributes, which you didn't include in the example above. You should be able to copy and paste the code and change the CREATE VIEW to ALTER VIEW. Let's also JOIN the NewsCategories table so that we can see the CatName each news record is associated with, while SELECTing from our view. So it should look something like this:
ALTER ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `newsview` AS
SELECT n.newsID, n.catID, n.title, n.txt, c.catName FROM News AS n JOIN NewsCategories AS c ON c.catID=n.catID
You can easily create and modify views using the Query Browser tool. If you're using the latest version of MySQL Query Browser, you should see your view listed along with your tables under your selected database in the right column. If this is the case, you can right click on the View and click Edit View.
Comments temporarily disabled
