Name & Roll no:Humna Rauf 18CS40
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:
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:
DROP VIEW IF EXISTS view_1, view_2 ...
Creating View in DB
Program:
CREATE VIEW FRUIT
AS
SELECT s.fruit,s.fruitid,s.dateentered,ci.customername,ci.customercity
FROM sales s INNER JOIN customerinformation ci
ON s.customercnic = ci.customerid;
SELECT * FROM FRUIT;
Output
Modifying a View
Program:
ALTER VIEW FRUIT
AS
SELECT
customerinformation.customername,customerinformation.customercity,s
ales.fruit,sales.fruitid FROM sales INNER JOIN customerinformation
ON sales.customercnic = customerinformation.customerid;
SELECT * FROM FRUIT;
Output:
Dropping or Deleting View in DB
Program:
ALTER VIEW FRUIT
AS
SELECT
customerinformation.customername,customerinformation.customercity,s
ales.fruit,sales.fruitid FROM sales INNER JOIN customerinformation
ON sales.customercnic = customerinformation.customerid;
SELECT * FROM FRUIT;
DROP VIEW FRUIT;
Output
