How To Create MySQL View

SQL views represent a subset of data in database, offer better security & simplicity for data analysis & reporting. Here’s how to create MySQL view to store queries that are dynamically run to generate results, instead of storing actual data.

Table of Contents

How To Create MySQL View

Here are the steps to create MySQL view.

SQL views are basically stored SQL queries that have been assigned a ‘view’ name. When you access a SQL view, their query is run dynamically to return the result set.

SQL views do not actually contain any data but only the stored query, and occupy very little space.

Bonus Read : How to Get Total Users Per Day in MySQL

What are the uses of SQL view

Here are the key uses of SQL view

Reporting – SQL views represent only subset of data. So they can be used to create reporting tables for data analysis
Security – SQL views are used to offer better security. You can safely give user access to views instead of underlying tables
Simplicity – You can create single view from multiple tables using joins. So users can easily access data without knowing the underlying database schema.

Bonus Read : How to Get New Users Per Day in MySQL

What are the advantages of views in SQL

Here are some of the advantages of views in SQL:

A View represents a virtual table and not actual table. So they don’t occupy any space other than the stored SQL query
You can use views to get a subset of table data, and limit user exposure to underlying data
Similarly, you can create MySQL view with aggregated data (e.g sum, count, etc)
You can create custom views which contain data from multiple tables, summaries, partitions or even calculated data, without modifying underlying data.

Bonus Read : How to Calculate Revenue in MySQL

How to Create MySQL View

It is very easy to create view in MySQL. The basic syntax to create view in MySQL is

CREATE VIEW view_name AS
SELECT column1, column2.....
FROM table_name
WHERE [condition];

Let’s say you have a table orders(order_date,product_id,quantity)

mysql> select * from orders;
+------------+------------+----------+
| order_date | product_id | quantity |
+------------+------------+----------+
| 2020-05-01 |          1 |       23 |
| 2020-05-01 |          2 |       35 |
| 2020-05-02 |          1 |       45 |
| 2020-05-02 |          2 |       23 |
| 2020-05-03 |          1 |       19 |
| 2020-05-03 |          2 |       15 |
| 2020-05-04 |          1 |       34 |
| 2020-05-04 |          2 |       56 |
+------------+------------+----------+

Let’s say you want to create MySQL view (e.g order_view) that shows only the order_date & quantity. Here’s the SQL query to create view

mysql> create view order_view as
      select order_date,quantity
      from orders;

Now you can query this view just as you would a normal table.

mysql> select * from order_view;
+------------+----------+
| order_date | quantity |
+------------+----------+
| 2020-05-01 |       23 |
| 2020-05-01 |       35 |
| 2020-05-02 |       45 |
| 2020-05-02 |       23 |
| 2020-05-03 |       19 |
| 2020-05-03 |       15 |
| 2020-05-04 |       34 |
| 2020-05-04 |       56 |
+------------+----------+

Bonus Read : How to Get Last 3 Months Sales Data in MySQL

You can also replace view using CREATE OR REPLACE statement. This will replace the SQL query for view.

mysql> create or replace view order_view as
       select order_date,product_id,quantity
       from orders;

mysql> select * from order_view;
+------------+------------+----------+
| order_date | product_id | quantity |
+------------+------------+----------+
| 2020-05-01 |          1 |       23 |
| 2020-05-01 |          2 |       35 |
| 2020-05-02 |          1 |       45 |
| 2020-05-02 |          2 |       23 |
| 2020-05-03 |          1 |       19 |
| 2020-05-03 |          2 |       15 |
| 2020-05-04 |          1 |       34 |
| 2020-05-04 |          2 |       56 |
+------------+------------+----------+

You can also create view in MySQL with JOIN. Here’s an example

mysql> create view order_view as
       select orders.order_date,orders.quantity,sales.sale
       from orders INNER JOIN sales on orders.order_date=sales.order_date;
mysql> select * from order_view;
+------------+----------+----------+
| order_date | quantity |    sale  |
+------------+----------+----------+
| 2020-05-01 |       23 |      350 |
| 2020-05-01 |       23 |      375 |
| 2020-05-02 |       45 |      423 |
| 2020-05-02 |       23 |      350 |
| 2020-05-03 |       19 |      230 |
| 2020-05-03 |       15 |      180 |
| 2020-05-04 |       34 |      450 |
| 2020-05-04 |       56 |      650 |
+------------+----------+----------+