Using MySQL views

This article describes how to create and use MySQL views on your A2 Hosting account. Views do not actually store any data. Instead, they store predefined queries that return a result set. Because MySQL views look and function like regular tables, they are sometimes called virtual tables.

Views offer a number of advantages. You can use views to hide table columns from users by granting them access to the view and not to the table itself. This helps enhance database security and integrity. Views can also help you construct simplified, abstracted interfaces to complex databases.

Creating and using views

You can create and use views on any A2 Hosting server that uses MySQL.

Setting up a test database

To demonstrate a basic example using views, let's start by creating a database for testing purposes. In the following SQL statement, replace username with your account username:

CREATE DATABASE username_test;

If you are using phpMyAdmin, click the name username_test to select the database. Otherwise, if you are using the mysql command-line program, type the following SQL statement:

USE username_test;

Next, create a table in the username_test database named products. To do this, run the following SQL statement:

CREATE TABLE products (prod_id INT NOT NULL AUTO_INCREMENT, prod_name VARCHAR(20) NOT NULL, prod_cost FLOAT NOT NULL DEFAULT 0.0, prod_price FLOAT NOT NULL DEFAULT 0.0, PRIMARY KEY(prod_id));

The following SQL statement adds some sample data to the products table:

INSERT INTO products (prod_name, prod_cost, prod_price) VALUES ('Basic Widget',5.95,8.35),('Micro Widget',0.95,1.35),('Mega Widget',99.95,140.00);

Creating the view

Now that we have a database and a table to work with, we are ready to create a simple view. Let's create a view named minimumPriceView. This view returns a data set of all products whose cost is greater than 1.00 (in dollars, Euros, or whatever currency unit we're working with).

To create this view, run the following MySQL statement:

CREATE VIEW minimumPriceView AS SELECT prod_name FROM products WHERE prod_cost > 1.00;

As you can see, the basic syntax to create a view is CREATE VIEW name AS query. Replace name with the name of the view, and replace query with the SQL query that you want to use.

Using the view

You can now use the minimumPriceView view in a query:

SELECT * FROM minimumPriceView;

This SQL statement returns the following results:

+--------------+
| prod_name    |
+--------------+
| Basic Widget |
| Mega Widget  |
+--------------+
2 rows in set (0.00 sec)

The minimumPriceView view has done all of the work behind the scenes, pulling out the products whose cost exceeds the value 1.00. Although this is a very simple example of a view, you can use the same techniques in your own databases to build more complex views.

More Information

• For more information about MySQL views, please visit http://dev.mysql.com/doc/refman/5.0/en/views.html.
• For more information about the CREATE VIEW statement, please visit http://dev.mysql.com/doc/refman/5.0/en/create-view.html.