Nuestra base de conocimientos sólo está disponible actualmente en inglés. Estamos en el proceso de traducir estas páginas y las publicaremos cuando estén disponibles.
Knowledge Base
How to create and use MySQL views
This article describes how to create and use MySQL views on your Webhost.Berlin 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 Webhost.Berlin 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;
You can run the previous SQL command (and the following SQL commands) from the command line using the MySQL tool, or in your web browser using phpMyAdmin.
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.
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.
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.
