MySQL View. Credits: ngodingdata

An introduction to views: creating MySQL views

This article briefly covers MySQL views, focusing on what distinguishes them and how to create a variety of them.

One SQL data management strategy that makes an interesting topic for study is the view. It is common knowledge that what defines views is their prominence as virtual tables within a database. Their description as ‘virtual’ recurs at the heart of every discussion regarding them. Such a definition clearly and strongly suggests that they are simulations — partial or complete — of existing tables in a database. The implications are that, unlike base tables, which store the data that makes up a database, views are not originally part of a database and do not have data of their own. Views are unequivocally created by users mostly on the basis of outcomes of MySQL queries, showing and allowing access to data in base tables.

Simulations are often partial, combining specific data from several base tables. This hints that views do not provide direct access to all the data in the base tables. Access to views only translates to access to particular data from the base tables that the view references. As an illustration, if two columns out of a six-column base table are referenced in a view, only the two columns can be accessed through the view. The remaining four columns can only be accessed in the base table. It is even more intriguing that a view can also be built on another view(s) kept in the database. Thus, it is logical to conclude that a typical view is set upon one or more underlying tables or existing views in the database to express or show specific data outcomes as well as alterations to data in them.

Significantly, views hold either simple or complex SELECT queries that are saved as objects in the database, making them available for use for as long as they are required. By storing queries — especially the complex ones — for later usage, the views optimise the retrieval of information from the database. The ease of use saves time and effort from rewriting the SELECT statements for similar queries.

We can create a MySQL view with the basic syntax — the CREATE VIEW statement — shown as follows:

CREATE VIEW view_name
AS
SELECT-statement;

The syntax for the SELECT statement is clarified below:

SELECT (column_list)
FROM table_name;

Therefore, the syntax for creating a MySQL view can be elaborately defined as follows:

CREATE VIEW view_name
AS
SELECT (column_list)
FROM table_name;

In the syntax above:

1. Specify a name for the view to be created after the CREATE VIEW command.
2. Use the AS keyword to connect the command and the name with the following SELECT statement.
3. Specify the SELECT statement that determines the view, which can be a simple or complex query, featuring a column list from one or more base tables.

NOTE: If a view exists already, the OR REPLACE keyword can be included in the syntax for creating a new view to replace it, as illustrated below:

CREATE [OR REPLACE] VIEW view_name
AS
SELECT (column_list)
FROM table_name;

MySQL CREATE VIEW MODELS:

Here are some models for how to use the CREATE VIEW statement to create views.

1. Creating a view with a simple SELECT statement.

We are examining the Sales table present in this sample database:

First, we shall write a simple SELECT statement. The SELECT statement will show the ID and the total sales amount for each salesperson. This is demonstrated as follows:

SELECT 
 salesperson_id,
    SUM(sales_amount) AS total_sales_amount
FROM 
 sales 
GROUP BY salesperson_id
ORDER BY salesperson_id;

Next, we shall create a view named ‘vw_total_sales_amount’ and in which the SELECT statement will be stored as an object in the database:

CREATE VIEW vw_total_sales_amount
AS
SELECT 
 salesperson_id,
    SUM(sales_amount) AS total_sales_amount
FROM 
 sales 
GROUP BY salesperson_id
ORDER BY salesperson_id;

A SHOW TABLES command retrieves the list of tables from the express_grocery database, including the view ‘vw_total_sales_amount’ as one of the tables.

SHOW TABLES;

A SHOW FULL TABLES command goes further to clarify the virtual nature of the table as a view in a ‘Table_type’ column, differentiating the view from the five base tables in the database like this:

SHOW FULL TABLES;

A SELECT statement to select all the columns from the view returns a result-set based on the ‘salesperson_id’ and ‘sales_amount’ columns from the base (sales) table.

SELECT * FROM vw_total_sales_amount;

A SELECT statement can also be used to select one or more of columns highlighted in the view. For instance, a SELECT statement is used to retrieve only the total sales amount from the view as shown below:

SELECT 
total_sales_amount 
FROM 
vw_total_sales_amount;

Also, a SELECT statement to select Product ID and Quantity columns that are not included in the view returns an error result in this way:

This proves that access to a view is limited to access to the column list featured in the view from the base table(s).

2. Creating a view with a complex SELECT statement containing a subquery.

Now, we are using the Products and Suppliers tables from the given database.

Products Table

Suppliers Table

We will be creating a view that holds a complex SELECT statement with a subquery to display the name, brand, and price of products supplied by suppliers from Deptford.

First, we will use the SELECT statement to get the name, brand, and price of products supplied by suppliers based in Deptford in this manner:

SELECT 
 product_name,
    brand,
    price
FROM 
 products
WHERE supplier_id IN (SELECT
    supplier_id
FROM 
    suppliers
WHERE
    borough = 'Deptford');

Ultimately, we create the view named ‘deptford_suppliers’ to contain this SELECT query as an object in the database:

CREATE VIEW deptford_suppliers
AS
SELECT 
 product_name,
    brand,
    price
FROM 
 products
WHERE supplier_id IN (SELECT
    supplier_id
FROM 
    suppliers
WHERE
    borough = 'Deptford');

3. Creating a new view based on an existing view.

Presently, we are making use of the view ‘vw_total_sales_amount’ already created based on the sales table in the sample database.

VW_total_sales_amount

We are creating another view, which will be named ‘sales_range’, based on data from ‘vw_total_sales_amount’. The view ‘sales_range’ will represent the sum of sales for each salesperson whose sales amount is between £220 and £300.

At first, we shall use a SELECT query to retrieve total sales amount from the view ‘vw_total_sales_amount’ within the range specified as follows:

SELECT total_sales_amount
FROM 
    vw_total_sales_amount
WHERE 
    total_sales_amount BETWEEN 220 AND 300;

Next, we create the view named ‘sales_range’ that will comprise the SELECT statement, which will be saved as an object in the database:

CREATE VIEW sales_range
AS
SELECT total_sales_amount
FROM 
    vw_total_sales_amount
WHERE 
    total_sales_amount BETWEEN 220 AND 300;

Using the SHOW TABLES command, we see that the three views that we have created — ‘vw_total_sales_amount’, ‘deptford_suppliers’ and ‘sales_range’ — are in the list of tables in the express_grocery database.

The SHOW FULL TABLES command reveals the Table_Type field that separates the three views from the database’s five underlying tables.

We use a SELECT statement to return all data of the three columns (Product name, brand, and price from the Product and Suppliers base tables) in the view ‘deptford_suppliers’ as follows:

SELECT * FROM deptford_suppliers;

Let us remember that we can equally use a SELECT statement to select records from specific columns in the view. For the sake of illustration, a SELECT statement is deployed to select all records only where the brand column has the value ‘June’:

SELECT * FROM deptford_suppliers
WHERE brand = 'June';

We can also make further queries of the view with the SELECT statement. Another example below is a SELECT statement that selects all records where the value of the product name column begins with the letter ‘s’.

SELECT * FROM deptford_suppliers
WHERE product_name LIKE 's%';

Just like the case with deptford_suppliers, a SELECT statement returns data of the only column — total_sales_amount from the views vw_total_sales_amount — in the view ‘sales_range’ as follows:

SELECT * FROM sales_range;

4. Creating a new view to replace an existing view.

Here, we will use the view ‘sales_range’ above, based on the view ‘vw_total_sales_amount’ in the sample database.

We will create another view, which will also be named ‘sales_range’ to replace the existing view with the same name. This new view will show a list of products, together with their brand, price, quantity sold, and sales amount, with sales amounts spanning £5 to £35.

To get started, we use a SELECT statement — consisting of a join — to select the name, brand, price, quantity sold, and sales amount for products where the sales amount is between £5 and £35.

SELECT 
   p.product_name,
   p.brand,
    p.price,
    s.quantity AS quantity_sold,
    s.sales_amount
FROM
 products p
JOIN
 sales s
ON
 p.product_id = s.product_id
Where s.sales_amount BETWEEN 5 AND 35;

Next, we shall create the latest view ‘sales_range’ to replace the previous ‘sales_range’ view.

CREATE OR REPLACE VIEW sales_range
AS
SELECT 
 p.product_name,
 p.brand,
    p.price,
    s.quantity AS quantity_sold,
    s.sales_amount
FROM
 products p
JOIN
 sales s
ON
 p.product_id = s.product_id
Where s.sales_amount BETWEEN 5 AND 35;

A SELECT statement to retrieve all columns from the new view, sales_range, reflects a result-set entirely different from the replaced view. The current SELECT statement returns results for the join, demonstrating that it has successfully replaced the old view.

SELECT * FROM sales_range;

Conclusion

From the foregoing, views are virtual tables typically set upon data from base tables or already existing views in a database, reflecting data results and modifications. This article has argued that views contain ready-to-use SELECT queries within a database, reducing the need to rewrite them and generally improving data retrieval. This article has illustrated the basic syntax for creating views — CREATE VIEW view_name AS Select-statement — by using it to create four views: vw_total_sales_amount(1), deptford_suppliers(2), sales_range (3) and sales_range (4). Using the four views created, this article has demonstrated that a view can be created to hold simple or complex SELECT statement, data from an existing view, or replace an existing view. In the process, this article has established the virtual tabular nature of views in a database, using the SHOW TABLES and SHOW FULL TABLES commands. Also, the article has exemplified that views can be queried using SELECT statements to retrieve data from one or more or all of their columns derived from the base tables.

References

Code First Girls (2023). Data management using SQL coding techniques.

MySQL Tutorial

Udemy

Contact details

Email: bukolaolafenwa@gmail.com

Twitter: Olubukolaruth

LinkedIn: Bukola Olafenwas