Community

Blog Events Webinars Tutorials Forum
  1. Blog
  2. Events
  3. Webinars
  4. Tutorials
  5. Forum
Create Account
×
Community Blog How to Use MySQL Views on an Alibaba Cloud MySQL Server

How to Use MySQL Views on an Alibaba Cloud MySQL Server

In this tutorial, we will take you through the steps of managing views on your MySQL server hosted on an Alibaba Cloud ECS instance or ApsaraDB RDS for MySQL.

By Francis Ndungu, Alibaba Cloud Community Blog author.

A view is a prepared SQL statement or a virtual table that retrieves data from two or more base tables when run. The definition of a view is built on top of other tables, and thus, when data is changed on the underlying tables, the view is updated accordingly.

Since views work with derived data, their performance is primarily determined by the queries they are built on. Views come in handy when you want to simplify repetitive tasks and put some of your application data retrieval logic on the database side.

The pre-defined query objects are also great for restricting data to specific users. They also work well with pre-computed columns. Also, they help in maintaining a centralized code base and support backward compatibility.

In this tutorial, we will take you through the steps of managing views on your MySQL server hosted on Alibaba Cloud ApsaraDB RDS for MySQL or Elastic Compute Service (ECS) instance.

Prerequisites

Before you begin, ensure you have the following:

Procedure

Step 1: Create Sample Data for Running MySQL Views

In order to understand MySQL views better, we need to first create some base tables and put some data in. To do this, log in to your MySQL server:

$ mysql -u -root -p

Enter your root password when prompted and hit Enter to continue.

Then, run the command below to create a database:

mysql> create database xyz_shop;

Then switch to the database:

mysql> use  xyz_shop;

Next, we are going to create a table named 'products_categories':

mysql> 
create table products_categories (
category_id BIGINT,
category_name VARCHAR(40),
PRIMARY KEY (category_Id)
);

Add some data to the table:

mysql>insert into products_categories(category_Id, category_name)VALUES('1', 'SHOES');
mysql>insert into products_categories(category_Id, category_name)VALUES('2', 'ELECTRONICS');

You may confirm the presence of data by running the code below:

mysql> select * from products_categories;

The output is as follows:

+-------------+---------------+
| category_id | category_name |
+-------------+---------------+
|           1 | SHOES         |
|           2 | ELECTRONICS   |
+-------------+---------------+

Next, we will create a 'products' table:

mysql> 
create table products (
product_id BIGINT,
category_id BIGINT,
product_name VARCHAR(40),
price DOUBLE,
PRIMARY KEY (product_Id)
);

We can add some few items to the 'products' table based on the two categories defined above:

  • Shoes Category Sample Data
mysql>insert into products (product_Id, category_id, product_name, price)VALUES('1010', '1', 'PENNY LOAFER', '45.00');
mysql>insert into products (product_Id, category_id, product_name, price)VALUES('2020', '1', 'DRESS BOOT', '22.00');
mysql>insert into products (product_Id, category_id, product_name, price)VALUES('3030', '1', 'WINGTIP OXFORD', '31.00');
  • Electronics Category Sample Data
mysql>insert into products (product_Id, category_id, product_name, price)VALUES('4040', '2','USB CHARGER', '3.00');
mysql>insert into products (product_Id, category_id, product_name, price)VALUES('5050', '2','10000mAh POWER BANK', '45.90');

Confirm the data by running the code below:

mysql> select * from products;

The output is as follows:

+------------+-------------+---------------------+-------+
| product_id | category_id | product_name        | price |
+------------+-------------+---------------------+-------+
|       1010 |           1 | PENNY LOAFER        |    45 |
|       2020 |           1 | DRESS BOOT          |    22 |
|       3030 |           1 | WINGTIP OXFORD      |    31 |
|       4040 |           2 | USB CHARGER         |     3 |
|       5050 |           2 | 10000mAh POWER BANK |  45.9 |
+------------+-------------+---------------------+-------+

Next, we are going to create a 'sales' table to handle some information about daily sales:

mysql> 
create table sales (
sales_id BIGINT,
sales_date DATE,
amount DOUBLE,
PRIMARY KEY (sales_Id)
);

Next, let's add some data to the sales table based on 3 dates:

  • January 9TH Sales
mysql>insert into sales (sales_Id, sales_date, amount)VALUES('1', '2019-01-09', '60.70');
mysql>insert into sales (sales_Id, sales_date, amount)VALUES('2', '2019-01-09', '250.20');
  • Jan 10th Sales
mysql>insert into sales (sales_Id, sales_date, amount)VALUES('3', '2019-01-10', '128.27');
mysql>insert into sales (sales_Id, sales_date, amount)VALUES('4', '2019-01-10', '1130.83');
  • January 11th Sales
mysql>insert into sales (sales_Id, sales_date, amount)VALUES('5', '2019-01-11', '500.27');
mysql>insert into sales (sales_Id, sales_date, amount)VALUES('6', '2019-01-11', '4350.83');

Confirm the presence of the sales data by running the code below:

mysql> select * from sales;

The output is as follows:

+----------+------------+---------+
| sales_id | sales_date | amount  |
+----------+------------+---------+
|        1 | 2019-01-09 |    60.7 |
|        2 | 2019-01-09 |   250.2 |
|        3 | 2019-01-10 |  128.27 |
|        4 | 2019-01-10 | 1130.83 |
|        5 | 2019-01-11 |  128.27 |
|        6 | 2019-01-11 | 1130.83 |
+----------+------------+---------+

Step 2: MySQL View Syntax

The MySQL CREATE VIEW statement is very simple to understand as shown below:

CREATE VIEW [View Name] AS [SQL Query];

Let us understand the structure of the view statement:

  • View Name: This is the name of your view. That is, it is the name of the database object that you will be calling from your code to retrieve the values from the defined SQL statement, such as vcustomers_orders.

  • SQL Query: This is the SQL statement that retrieves data whenever the view is called. When writing the code, you must keep the below points in mind:

    • Subqueries cannot be used in the FROM clause of MySQL views.
    • MySQL VIEW select statements cannot access user and system variables.
    • Tables and Views defined in the select statement must exist before creating the view.
    • View alias names can be up to 64 characters in length.

Step 3: Creating MySQL Views

We now have the syntax for creating MySQL views and some sample data to test the concept, so we can no go ahead and create the first view.

MySQL View from Multiple Sales

This view will help us to retrieve the names of the products and their respective categories. Even though this information is stored in multiple tables, we are going to use a JOIN statement on the SQL code:

mysql>Create view v_product_details AS 
select p.product_id, 
p.product_name, 
c.category_name
from products p
left join products_categories c
on p.category_id=c.category_id;

Once the view is created, we can check if it is able to retrieve data from our two tables by running the command below:

mysql> select * from v_product_details;

The output is as follows:

+------------+---------------------+---------------+
| product_id | product_name        | category_name |
+------------+---------------------+---------------+
|       1010 | PENNY LOAFER        | SHOES         |
|       2020 | DRESS BOOT          | SHOES         |
|       3030 | WINGTIP OXFORD      | SHOES         |
|       4040 | USB CHARGER         | ELECTRONICS   |
|       5050 | 10000mAh POWER BANK | ELECTRONICS   |
+------------+---------------------+---------------+

As you can see above, our v_product_details view was indeed able to retrieve the name of the product as well as the category associated with it.

MySQL View to Retrieve Computed Values

In our second example, we are going to run a computed view that should return the daily totals from the sales table. Here is the SQL code:

mysql> Create view v_computed_daily_sales AS Select sales_date, ifnull(sum(amount),0) as total_sales from sales group by sales_date order by sales_date asc;

We can retrieve the result of our view above by running the command below:

mysql>select * from v_computed_daily_sales;

The output is as follows:

+------------+-------------+
| sales_date | total_sales |
+------------+-------------+
| 2019-01-09 |       310.9 |
| 2019-01-10 |      1259.1 |
| 2019-01-11 |      4851.1 |
+------------+-------------+

As you can see from the output above, MySQL views are not limited from just select queries. You can run aggregate functions from them to suit your business needs depending on your data and reporting needs.

Step 4: Modifying a MySQL View

You can modify the definition of a MySQL view at any time by running the code below. Remember to replace the name of the view and target SQL statement with the correct values

mysql> ALTER VIEW [View Name] AS [SQL Statement];

Below is an example:

mysql> ALTER VIEW v_product_details AS select product_name from products;

You may run the SELECT query to confirm if the view was modified:

mysql> select * from v_product_details;

The output is as follows:

+---------------------+
| product_name        |
+---------------------+
| PENNY LOAFER        |
| DRESS BOOT          |
| WINGTIP OXFORD      |
| USB CHARGER         |
| 10000mAh POWER BANK |
+---------------------+

Please note, in order to modify the view with the ALTER keyword, the view must exist first, otherwise, you will get an error.

Another way to modify a view is to use the CREATE OR REPLACE keyword. This method creates the view if does not exist in the system

mysql> CREATE OR REPLACE VIEW [View Name] AS [SQL Statement];

Below is another example:

mysql> CREATE OR REPLACE VIEW v_product_details AS select product_name, price from products;

To see if the view has been modified correctly, run the command below:

mysql>select * from v_product_details ;

The output is as follows:

+---------------------+
| product_name        |
+---------------------+
| PENNY LOAFER        |
| DRESS BOOT          |
| WINGTIP OXFORD      |
| USB CHARGER         |
| 10000mAh POWER BANK |
+---------------------+

Step 5: Dropping MySQL Views

Use the DROP statement to delete MySQL view from the database using the below syntax:

mysql> DROP VIEW [View Name];

You can use delete multiple views by separating them with commas after the DROP statement as shown below:

mysql> DROP VIEW [View 1], [View 2], [View 3];

For instance, to delete the two views that we created in this tutorial, we can run the code below:

mysql>DROP VIEW v_product_details, v_computed_daily_sales;

Conclusion

In this article, we have taken you through the steps of creating and using MySQL views to retrieve information from multiple tables in a database server hosted on Alibaba Cloud. We have also shown you how to generate pre-computed data using views.

In order to improve the performance of your views, generate views from base tables and not other views. Also, in case you change the structure of underlying tables, update your views to prevent errors. Remember to test the workings of this tutorial, you can sign up with Alibaba Cloud today and enjoy free trial credit of up to $1200.

MySQL Database Tech_Share Tutorials MySQL Views MySQL Server
0 0 0
Share on

Read previous post:

How to Implement Stored Procedures on an Alibaba Cloud MySQL Server

Read next post:

How to Work with MySQL Subqueries on Alibaba Cloud

francisndungu

31 posts | 8 followers

You may also like

Comments

francisndungu

31 posts | 8 followers

Related Products

More Posts by francisndungu

See All