MySQL Lesson 10: Creating Views

Hello everybody,

This is Michael, and today’s post will be about creating views. This will be the last post in this series of MySQL lessons, but I’ll likely have more MySQL lessons and/or analyses in the future, so keep following my posts.

Anyway, views are basically virtual tables created from queries that allow you to query the data within them. Here’s an example:

Here, I am building a view from the query, which consists of the block of code between the parentheses. In the query, I selected the foreign key Album_idAlbum and the fields Ceremony and Won/Nominated from the Awards table. I narrowed down the criteria so that Album_idAlbum with keys greater than 30 and less than 58 will be displayed. In other words, I only want the query-and in turn, the view-to include awards considerations for albums released in the 2010s, hence the view’s name 2010s Album Awards.

• The syntax for views is ALWAYS:
  • CREATE VIEW (name of view) AS (query)
• Parentheses aren’t necessary when creating views. I just use them to improve readability of the code.

If you press the refresh button on the “Schemas” tab, the view will appear.

You can even query the data in the view. Here’s an example:

Using every field from the view, I asked the query to only display the Album_idAlbum of any album that was considered (whether they won or were just nominated) for a Teen Choice Award, which only applied to one album in this case (Usher’s 2010 album Raymond v. Raymond).

Let’s try another example. First we’ll create a view consisting of any debut albums listed on the database (WHERE Album Number = 1 refers to debut albums), along with the corresponding [Album] Release Date and Singer Name (referring to which singer/group created the album):

• JOINs are perfectly fine to include in views-honestly anything you can include in a normal query you can include in a view.

Then we hit the refresh button on the “Schema” tab to see if the view was created (which it was).

And finally, let’s query this view:

In this query, I selected the fields [Album] Name and Singer Name from the view and asked the query to only display albums WHERE MONTH(Release Date) = 1 OR MONTH(Release Date) = 2, which refers to albums released in January or February. As you can see, two albums fit the criteria.

Thanks for reading,

Michael