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.
You can create and use views on any A2 Hosting server that uses MySQL.
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:
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);
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.
If you look at a list of the tables in the database, you will see the minimumPriceView view listed alongside the other tables. Remember, however, that views are virtual tables containing queries. They do not contain any actual data.
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.