Views, or virtual tables, are structures which allow us to access ad-hoc generated tables based from the real tables in our database. Because they are virtual, they do not store any data but generated at runtime from the base tables - which are the tables that are referenced within a view
Creating a View
In these examples I will be using the MySQL Sakila example database
-- Creating a View CREATE VIEW vw_AllActor AS SELECT * FROM actor;
As a convention, views have this
vw_ prefix in their names.
Gathering Data from a View
-- Using a view SELECT * FROM vw_AllActor
This statement will load the data from the actor table in the view and presents us with it.
These are the basic examples, let’s go into more complex - and realistic - stuff:
CREATE VIEW vw_ActorsStartingWithA AS SELECT * FROM actor WHERE first_name LIKE 'A%'
As you can imagine now
vw_ActorsStartingWithA is poised to get a filtered data from the database. The thing is I can filter it even more by using a WHERE statement:
SELECT * FROM vw_ActorsStartingWithA WHERE last_name LIKE 'A%';
So instead of typing the first gatherer query, I can save it into a view and encapsulate its function into a view.
I also can do nested views - MySQL supports this, and I think most of the DB softare also is able to do that but still check with your vendor nonetheless - that is views using another view. Let’s write the previous query with a nested view:
CREATE VIEW vw_AlliterativeActorsWithA AS SELECT * FROM vw_ActorsStartingWithA WHERE last_name LIKE 'A%'
Now I can call the whole deal with:
SELECT * FROM vw_AlliterativeActorsWithA ORDER BY last_name
Updating Data With Views
Views also enable us to create a mask on data manipulation operations as well. For example for some purpose I may want to limit the data entry to a table with some fields. This limiting can be achieved with a view:
-- Creating the Filter CREATE VIEW vw_LanguageFilterer AS SELECT language_id, name, last_update FROM language
This creates the template for inserting, updating and deleting data:
-- Inserting with the filter INSERT INTO vw_LanguageFilterer (name, last_update) VALUEs ('Turkisch','2018-11-15'); -- Updating with the filter UPDATE vw_LanguageFilterer SET name = 'Turkish' WHERE name = 'Turkisch'; -- Deleting with the filter DELETE FROM vw_LanguageFilterer WHERE name = 'Turkish';
Deleting a View
We can delete a view with a very simple command:
DROP VIEW vw_viewname;
so for example for dropping the mask view I discussed previously:
DROP VIEW vw_LanguageFilterer;