How do I use a view in MySQL?
Author: Deron Eriksson
Description: This tutorial describes using a View in MySQL.
Tutorial created using: Windows XP || JDK 1.5.0_09 || Eclipse Web Tools Platform 1.5.1 || MySQL 5.0.27
A view allows you to simplify and process query results. This occurs on the databaseW end, which typically is very efficient since databases are optimized to return results quickly. You can think of a view as a virtual table.
As a small example of the use of a view, suppose we have the following script.
USE testdatabase; DROP TABLE IF EXISTS employees; CREATE TABLE employees (id INT, first_name VARCHAR(20), last_name VARCHAR(30)); INSERT INTO employees (id, first_name, last_name) VALUES (1, 'John', 'Doe'); INSERT INTO employees (id, first_name, last_name) VALUES (2, 'Bob', 'Smith'); INSERT INTO employees (id, first_name, last_name) VALUES (3, 'Jane', 'Doe'); SELECT * FROM employees; DROP VIEW employees_view; CREATE VIEW employees_view AS SELECT id, CONCAT(first_name, ' ', last_name) AS full_name FROM employees; SELECT * FROM employees_view;
In this script, we create a view based on the employees table that consists of an id column called id and a column called full_name that consists of first_name concatenated to last_name (with a space in the middle). If we execute this script, we see the following results.
In the results above, we can see the results of querying the employees table followed by the results of querying the employees_view view. As you can see the results of the view query are formatted identically to the results of the table query, although processing has occurred before returning the final results of the view query. This is a very simple example but showcases the processing power of views.