Views in MySQL Tutorial: Create, Join & Drop with Examples

What are the views?

VIEWS are virtual tables that do not store any data of their own but display data stored in other tables. In other words, VIEWS are nothing but SQL Queries. A view can contain all or a few rows from a table. A MySQL view can show data from one table or many tables.

Views syntax

Let's now look at the basic syntax used to create a view in MySQL.

CREATE VIEW `view_name` AS SELECT statement;

WHERE

• "CREATE VIEW `view_name`" tells MySQL server to create a view object in the database named `view_name`
• "AS SELECT statement" is the SQL statement to be packed in the views. It can be a SELECT statement can contain data from one table or multiple tables.

Let's now create our first view using the "myflixdb" we will create a simple view that restricts the columns seen in the member's table.

Suppose authorization requirements state that the accounts department can only see member's number, name, and gender from the member's table. To achieve this you can create a VIEW -

CREATE VIEW `accounts_v_members` AS SELECT `membership_number`,`full_names`,`gender` FROM `members`;

Executing the above script in MySQL workbench against the myflixdb and expanding the views node in the database explorer gives us the following results.

Note the accounts_v_members object is now visible in the database views objects. Let's now execute a SELECT statement that selects all the fields from the view.

SELECT * FROM `accounts_v_members`;

Executing the above script in MySQL workbench against myflixdb gives us the following results shown below.

y the authorized columns for the accounts department have been returned. Other details found in the member's table have been hidden.

If we want to see the SQL statements that make up a particular view, we can use the script shown below to do that.

SHOW CREATE VIEW `accounts_v_members`;

Executing the above script gives you the view name and the SQL SELECT statements used to create the view.

Joins and views

Let's now look at a fairly complex example that involves multiple tables and uses joins.

We will package the JOIN created that gets information from three (3) tables namely members, movies, and movie rentals. Below is the script that helps us to achieve that.

CREATE VIEW `general_v_movie_rentals` AS SELECT mb.`membership_number`,mb.`full_names`,mo.`title`,mr.`transaction_date`,mr.`return_date` FROM `movierentals` AS mr INNER JOIN `members` AS mb ON mr.`membership_number` = mb.`membership_number` INNER JOIN `movies` AS mo ON mr.`movie_id` = mo.`movie_id`;

Executing the above scripts creates the view named general_v_movie_rentals in our myflixdb

Let's now select all the fields from a table named general_v_movie_rentals.

SELECT * FROM `general_v_movie_rentals`;

Executing the above script in MySQL workbench against the myflixdb gives us the following results shown below.

membership_number
full_names
title
transaction_date
return_date
1
Janet Jones
Pirates of the Caribean 4
20-06-2012
28-06-2012
1
Janet Jones
Forgetting Sarah Marshal
22-06-2012
25-06-2012
3
Robert Phil
Forgetting Sarah Marshal
22-06-2012
25-06-2012
2
Janet Smith Jones
Forgetting Sarah Marshal
21-06-2012
24-06-2012
3
Robert Phil
X-Men
23-06-2012
28-06-2012

Note we didn't have to write the complex JOIN query to get information about members, movies, and movie rental details. We simply used the view in a regular SELECT statement as any other ordinary table. The view can be called from anywhere in the application system running on top of the myflixdb.

Dropping views

The DROP command can be used to delete a view from the database that is no longer required. The basic syntax to drop a view is as follows.

DROP VIEW ` general_v_movie_rentals `;

Why use views?

You may want to use views primarily for the following 3 reasons

• Ultimately, you will use your SQL knowledge, to create applications, which will use a  database for data requirements. It's recommended that you use VIEWS of the original table structure in your application instead of using the tables themselves. This ensures that when you refactor your DB, your legacy code will see the original schema via the view without breaking the application.
• VIEWS increase re-usability. You will not have to create complex queries involving joins repeatedly. All the complexity is converted into a single line of query use VIEWS. Such a condensed code will be easier to integrate into your application. This will eliminates chances of typos and your code will be more readable.
• VIEWS helps in data security. You can use views to show only authorized information to users and hide sensitive data like credit card numbers.

Summary

• Views are virtual tables; they do not contain the data that is returned. The data is stored in the tables referenced in the SELECT statement.
• Views improve the security of the database by showing only the intended data to authorized users. They hide sensitive data.
• Views make life easy as you do not have to write complex queries time and again.
• It's possible to use INSERT, UPDATE, and DELETE on a VIEW. These operations will change the underlying tables of the VIEW.  The only consideration is that VIEW should contain all NOT NULL columns of the tables it references. Ideally, you should not use VIEWS for updating.

MySQL CREATE VIEW

Summary: in this tutorial, you will learn how to use the MySQL CREATE VIEW  statement to create a new view in the database.

Introduction to MySQL CREATE VIEW statement

The CREATE VIEW statement creates a new view in the database. Here is the basic syntax of the CREATE VIEW statement:

CREATE [OR REPLACE] VIEW [db_name.]view_name [(column_list)]
AS
  select-statement;

In this syntax:

First, specify the name of the view that you want to create after the CREATE VIEW keywords. The name of the view is unique in a database. Because views and tables in the same database share the same namespace, the name a view cannot the same as the name of an existing table.

Second, use the OR REPLACE option if you want to replace an existing view if the view already exists. If the view does not exist, the OR REPLACE has no effect.

Third, specify a list of columns for the view. By default, the columns of the view are derived from the select list of the SELECT statement. However, you can explicitly specify the column list for the view by listing them in parentheses following the view name.

Finally,  specify a SELECT statement that defines the view. The SELECT  statement can query data from tables or views. MySQL allows you to use the ORDER BY clause in the SELECT statement but ignores it if you select from the view with a query that has its own ORDER BY clause.

By default, the CREATE VIEW statement creates a view in the current database. If you want to explicitly create a view in a given database, you can qualify the view name with the database name.

MySQL CREATE VIEW examples

Let’s take some example of using the CREATE VIEW statement to create new views.

1) Creating a simple view example

Let’s take a look at the orderDetails table from the sample database:

This statement uses the CREATE VIEW statement to create a view that represents total sales per order.

CREATE VIEW salePerOrder AS
    SELECT 
        orderNumber, 
        SUM(quantityOrdered * priceEach) total
    FROM
        orderDetails
    GROUP by orderNumber
    ORDER BY total DESC;

If you use the SHOW TABLE command to view all tables in the classicmodels database, you will see the viewsalesPerOrder is showing up in the list.

SHOW TABLES;

This is because the views and tables share the same namespace as mentioned earlier.

To know which object is a view or table, you use the SHOW FULL TABLES command as follows:

SHOW FULL TABLES;

The table_type column in the result set specifies the type of the object: view or table (base table).

If you want to query total sales for each sales order, you just need to execute a simple SELECT  statement against the SalePerOrder  view as follows:

SELECT * FROM salePerOrder;

2) Creating a view based on another view example

MySQL allows you to create a view based on another view.

For example, you can create a view called bigSalesOrder based on the salesPerOrder view to show every sales order whose total is greater than 60,000 as follows:

CREATE VIEW bigSalesOrder AS
    SELECT 
        orderNumber, 
        ROUND(total,2) as total
    FROM
        salePerOrder
    WHERE
        total > 60000;

Now, you can query the data from the bigSalesOrder view as follows:

SELECT 
    orderNumber, 
    total
FROM
    bigSalesOrder;

3) Creating a view with join example

The following example uses the CREATE VIEW statement to create a view based on multiple tables. It uses the INNER JOIN clauses to join tables.

CREATE OR REPLACE VIEW customerOrders AS
SELECT 
    orderNumber,
    customerName,
    SUM(quantityOrdered * priceEach) total
FROM
    orderDetails
INNER JOIN orders o USING (orderNumber)
INNER JOIN customers USING (customerNumber)
GROUP BY orderNumber;

This statement selects data from the customerOrders view:

SELECT * FROM customerOrders 
ORDER BY total DESC;

This picture shows the partial output:

4) Creating a view with a subquery example

The following example uses the CREATE VIEW statement to create a view whose SELECT statement uses a subquery. The view contains products whose buy prices are higher than the average price of all products.

CREATE VIEW aboveAvgProducts AS
    SELECT 
        productCode, 
        productName, 
        buyPrice
    FROM
        products
    WHERE
        buyPrice > (
            SELECT 
                AVG(buyPrice)
            FROM
                products)
    ORDER BY buyPrice DESC;

This query data from the aboveAvgProducts is simple as follows:

SELECT * FROM aboveAvgProducts;

5) Creating a view with explicit view columns example

This statement uses the CREATE VIEW statement to create a new view based on the customers and orders tables with explicit view columns:

CREATE VIEW customerOrderStats (
   customerName , 
   orderCount
) 
AS
    SELECT 
        customerName, 
        COUNT(orderNumber)
    FROM
        customers
            INNER JOIN
        orders USING (customerNumber)
    GROUP BY customerName;

This query returns data from the customerOrderStats view:

SELECT 
    customerName,
    orderCount
FROM
    customerOrderStats
ORDER BY 
 orderCount, 
    customerName;

In this tutorial, we have shown you how to use the MySQL CREATE VIEW statement to create views in the database.