Chapter 8: Views

 

In the last three chapters, we learned to write some pretty complex SELECT statements to get information from our databases.

 

In this chapter, we’ll learn another concept that is closely related to SELECT statements - the concept of views.

 

What is a view?

 

Simply stated, an SQL view is a virtual table.

 

In contrast to actual tables, views do not contain data. Instead, they contain SELECT statements. The data to display is retrieved using those SELECT statements.

 

One advantage of using views is that it allows programmers to simplify their code. They can write relatively complex SELECT statements to join multiple tables into a single virtual table. Once that is done, they can access that virtual table like a normal table and perform simple searches on it.

 

In addition, views also allow us to restrict access to certain data in our tables. For instance, suppose we have a table with three columns - id, password and email. If we do not want other programmers to have access to all the data in the tables (such as the password column), we can create a view with only the id and email columns and let them have access to the view instead.

 

Creating a View

 

The syntax for creating a view is:

 

CREATE VIEW name_of_view AS

SELECT statement;

 

Quite straightforward, right?

 

Let’s look at an example.

 

In the previous chapter, we wrote a SELECT statement to join the employees table with the mentorships table.

 

The code below shows how we can create a view for that SELECT statement:

 

CREATE VIEW myView AS

SELECT employees.id, mentorships.mentor_id, employees.em_name AS 'Mentor', mentorships.project AS 'Project Name'

FROM

mentorships

JOIN

employees

ON

employees.id = mentorships.mentor_id;

 

The only additional code is the first line (CREATE VIEW myView AS).

 

To use this view, we need to first execute the CREATE VIEW statement above to create the view.

 

Next, we can select data from it like how we select data from a table.

 

For instance, we can write

 

SELECT * FROM myView;

 

to view all the data...

Altering a View

 

After creating a view, if we want to make any changes to it, we use the ALTER VIEW keywords.

 

The syntax is

 

ALTER VIEW name_of_view AS

SELECT statement;

 

For instance, we can use the following statement to alter myView:

 

ALTER VIEW myView AS

SELECT employees.id, mentorships.mentor_id, employees.em_name AS 'Mentor', mentorships.project AS 'Project'

FROM

mentorships

JOIN

employees

ON

employees.id = mentorships.mentor_id;

 

This changes the alias for the mentorships.project column from Project Name to Project (refer to underlined code).

 

Deleting a View

 

Last but not least, to delete a view, we use the DROP VIEW keywords. The syntax is

 

DROP VIEW [IF EXISTS] name_of_view;

 

To delete myView, we write

 

DROP VIEW IF EXISTS myView;