SQL: Views

SQL: Views

What is a view in SQL? How do we use it in SQL?


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
FROM actor;

As a convention, views have this vw_ prefix in their names.

Gathering Data from a View

-- Using a view

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
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:

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
FROM vw_ActorsStartingWithA
WHERE last_name LIKE 'A%'

Now I can call the whole deal with:

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
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)

-- Updating with the filter
UPDATE vw_LanguageFilterer
SET name = 'Turkish'
WHERE name = 'Turkisch';

-- Deleting with the filter
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;