MySQL Views
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:
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:
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:
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:
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:
But we can't query the above vFruitInventory view like this:
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:
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:
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:
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:
Dropping a View
You an drop a view by using the DROP VIEW statement. Like this:
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:
The IF EXISTS Clause
You can also use the IF EXISTS clause to prevent an error from occuring if a view doesn't exist: