Here, I explain what MySQL "views" are and how to use them.

MySQL provides us with the ability to create views. A view is defined as a stored query that when invoked, produces a result set. Some folk refer to views as "virtual tables".

Clear as mud? Let's try again.

What is a View?

A view is a query that you save to the database. You can then run it later simply by calling that view (rather than writing out the query again).

The view could consist of a complex query but it will present the results as though it was a table. Therefore, you can query the view as though it was a table.

For example, you could have a complex query that selects data from three different tables. You could either type this complex query out every time you need to run it, or you could save the query as a view. Once it has been saved as a view, you can then run a simple SELECT statement to return the results of the complex query. But of course, you could also write a complex query against the view if need be.

Create a View

Creating views are very simple. You simply preceed your query with one line of code and run it. The view will immediately be created in your database.

Syntax

To create a view, type the following statement, followed by the query:

CREATE VIEW view_name AS

Replace view_name with whatever name you'd like to use for the view.

Example

If we run the following code against the FruitShop database:

CREATE VIEW vFruitInventory AS

SELECT 

   Fruit.FruitName,

   Fruit.Inventory,

   Units.UnitName

FROM

Fruit INNER JOIN Units ON

   Fruit.UnitId = Units.UnitId;

We now see a view called vFruitInventory listed under Views (you may need to click the Refresh button for the SCHEMAS menu first):

It's a good idea to think of a naming convention for your views (as with any other database object) and stick to it. Many developers prefix their view names with v, vw, v_ or vw_ so that it makes it easier to distinguish views from tables in their queries. However, other developers disagree with this convention and prefer their table and view names to be interchangable.

Querying a View

Now we can query the view just like we'd query a table:

SELECT * FROM vFruitInventory;

Result:

Of course, we can use a more specific query too. For example, this one that selects only those records where the inventory is greater than or less than 10:

SELECT FruitName 

FROM vFruitInventory

WHERE Inventory <= 10;

But we can't query columns that aren't referenced in the view (even if they are in the underlying tables that the view queries).

For example, we can query the Fruit table like this:

SELECT * 

FROM Fruit

WHERE FruitId = 1;

But we can't query the above vFruitInventory view like this:

SELECT * 

FROM vFruitInventory

WHERE FruitId = 1;

This is because the view doesn't return the FruitId column. We specified the exact columns in the view and those are all that are returned. As mentioned, the result set of the view is just like a table and some like to call it a "virtual table". If the "table" doesn't include those columns, you can't query them.

Rather than being a limitation, this is actually a feature of views. This feature means that we can grant users access to some columns of a table but not others (via the view). In other words, we can grant a user access to a view without granting that user access to the underlying tables that the view accesses. Some tables might store sensitive information that the user isn't allowed to access. But the same tables might also store non-sensitive information that they need to access. What to do? Create a view! And that view can select only the non-sensitive information from those tables.

Modifying a View

Here are two different methods to modify your view.

Option 1: Use the ALTER VIEW Statement

You can modify a view by using the ALTER VIEW statement. Like this:

ALTER VIEW view_name AS

Replace view_name with the name of the view that you'd like to alter.

Example

Let's add the Fruit.FruitId field to the view:

ALTER VIEW vFruitInventory AS

SELECT 

Fruit.FruitId,

   Fruit.FruitName,

   Fruit.Inventory,

   Units.UnitName

FROM

Fruit INNER JOIN Units ON

   Fruit.UnitId = Units.UnitId;

Now, when we try to return the FruitId field in our queries we will get results.

But note that we can't try to access this field as Fruit.FruitId. We can only access it as FruitId. And this is how it should be. After all, the view is a "virtual table" and we have no need to know the structure of the tables that it queries.

Option 2: Use CREATE OR REPLACE

Note that the view must exist before you run the ALTER VIEW statement. If it doesn't exist, you'll receive an error. You can avoid this issue by using a CREATE OR REPLACE statement. This will create the view if it doesn't exist, or replace it if it does.

So we could've created the above view like this:

CREATE OR REPLACE VIEW vFruitInventory AS

SELECT 

   Fruit.FruitName,

   Fruit.Inventory,

   Units.UnitName

FROM

Fruit INNER JOIN Units ON

   Fruit.UnitId = Units.UnitId;

And then we could update it by using the same CREATE OR REPLACE statement, but just modifying the definition. For example, adding in the Fruit.FruitId field:

CREATE OR REPLACE VIEW vFruitInventory AS

SELECT 

Fruit.FruitId,

   Fruit.FruitName,

   Fruit.Inventory,

   Units.UnitName

FROM

Fruit INNER JOIN Units ON

   Fruit.UnitId = Units.UnitId;

Dropping a View

You an drop a view by using the DROP VIEW statement. Like this:

DROP VIEW vFruitInventory

The above statement will remove the view called vFruitInventory.

Dropping Multiple Views

You can drop multiple views using the same DROP VIEW statement. Just separate each view name with a comma. Like this:

DROP VIEW view_1, view_2 ...

The IF EXISTS Clause

You can also use the IF EXISTS clause to prevent an error from occuring if a view doesn't exist: