MySQL Views Explained

What is a View in MySQL?

A View in MySQL is nothing but a virtual table with an associated name that is made up of a query and stored as a permanent object. View returns a result set of filtered rows and columns when invoked. Views are used for security purposes because they provide encapsulation of the tables names.

What are the advantages and disadvantages of Views?

Advantages of using Views:

• Provides encapsulation and improves data security.
• Hides the complexity of Complex SQL Queries.
• Provides rows and columns level security.
• Provides simplicity in structure and queries.
• Simplify data access and offers aggregated data instead of detailed data.
• Reduces data distraction by hiding the irrelevant columns.
• Allows to implements all types of queries that are available for base tables.

Disadvantages of using Views:

• Views can cause performance degradation.
• Views can only create the appearance of a table, and not a real table.
• Increase load on query processor because it must translate queries against the view into queries against the underlying source tables.
• Views cannot be created based on Temporary Tables in MySQL.
• When a MySQL table is dropped, then the associated view with that table become irrelevant.
• When views are created for large tables, it consumes more memory.

What is the syntax of creating View in MySQL?

Below is the official standard syntax of creating a view in MySQL:

CREATE
[OR REPLACE]
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
[DEFINER = user]
[SQL SECURITY { DEFINER | INVOKER }]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]

How to create View in MySQL?

You can create a view in MySQL with the help of CREATE VIEW statement. But before that let's create few tables named Products, Orders, OrderProducts to get proper understanding.

Tables usages:

• Products: this table will hold the products information.
• Orders: this table will hold the orders details.
• OrderProducts: this table will hold the products details for the orders.

Create Products Table as follows:

CREATE TABLE `products` (
  `productid` bigint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `mrp` decimal(8,2) NOT NULL DEFAULT '0.00',
  `price` decimal(8,2) NOT NULL DEFAULT '0.00',
  `created_at` timestamp NULL DEFAULT NOW(),
  `updated_at` timestamp NULL DEFAULT NOW(),
  PRIMARY KEY (`productid`),
  UNIQUE KEY `products_name_unique` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Create Orders Table as follows:

CREATE TABLE `orders` (
  `orderid` bigint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `subtotal` decimal(8,2) NOT NULL DEFAULT '0.00',
  `discount` decimal(8,2) NOT NULL DEFAULT '0.00',
  `total` decimal(8,2) NOT NULL DEFAULT '0.00',
  `status` ENUM('created','processing','packed','shipped','delivered') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'created',
  `created_at` timestamp NULL DEFAULT NOW(),
  `updated_at` timestamp NULL DEFAULT NOW(),
  PRIMARY KEY (`orderid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Create OrderProducts Table as follows:

CREATE TABLE `order_products` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `orderid` bigint unsigned NOT NULL,
  `productid` bigint unsigned NOT NULL,
  `quantity` int unsigned NOT NULL DEFAULT 0,
  `mrp` decimal(8,2) NOT NULL DEFAULT '0.00',
  `discount` decimal(8,2) NOT NULL DEFAULT '0.00',
  `price` decimal(8,2) NOT NULL DEFAULT '0.00',
  `total` decimal(8,2) NOT NULL DEFAULT '0.00',
  `created_at` timestamp NULL DEFAULT NOW(),
  `updated_at` timestamp NULL DEFAULT NOW(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Next, let's dump some dummy data into these tables so that we can create view from these tables.

Insert sample data into Products table:

INSERT INTO `products` 
(`productid`, `name`, `mrp`, `price`) 
VALUES 
(1, 'Red Shirt', '999', '899'),
(2, 'Yellow Shirt', '1099', '999'),
(3, 'Blue Jeans', '1199', '1099'),
(4, 'Black Jeans', '1999', '1599');

Insert sample data into Orders table:

INSERT INTO `orders` 
(`orderid`, `name`, `subtotal`, `discount`, `total`) 
VALUES 
(1, 'Ram Verma', '999', '100', '899'),
(2, 'Gopal Mishra', '2298', '200', '2098'),
(3, 'Komal Singh', '3998', '800', '3198');

Insert sample data into OrderProducts table:

INSERT INTO `order_products`
(`id`, `orderid`, `productid`, `quantity`, `mrp`, `discount`, `price`, `total`)
VALUES
(1, 1, 1, 1, '999', '100', '899', '899'),
(2, 2, 2, 1, '1099', '100', '999', '999'),
(3, 2, 3, 1, '1199', '100', '1099', '1099'),
(4, 3, 4, 2, '1999', '400', '1599', '3198');

Verify inserted data using SELECT queries:

mysql> SELECT * FROM `products`;
+-----------+--------------+---------+---------+---------------------+---------------------+
| productid | name         | mrp     | price   | created_at          | updated_at          |
+-----------+--------------+---------+---------+---------------------+---------------------+
|         1 | Red Shirt    |  999.00 |  899.00 | 2022-08-25 13:22:54 | 2022-08-25 13:22:54 |
|         2 | Yellow Shirt | 1099.00 |  999.00 | 2022-08-25 13:22:54 | 2022-08-25 13:22:54 |
|         3 | Blue Jeans   | 1199.00 | 1099.00 | 2022-08-25 13:22:54 | 2022-08-25 13:22:54 |
|         4 | Black Jeans  | 1999.00 | 1599.00 | 2022-08-25 13:22:54 | 2022-08-25 13:22:54 |
+-----------+--------------+---------+---------+---------------------+---------------------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM `orders`;
+---------+--------------+----------+----------+---------+---------+---------------------+---------------------+
| orderid | name         | subtotal | discount | total   | status  | created_at          | updated_at          |
+---------+--------------+----------+----------+---------+---------+---------------------+---------------------+
|       1 | Ram Verma    |   999.00 |   100.00 |  899.00 | created | 2022-08-25 13:23:22 | 2022-08-25 13:23:22 |
|       2 | Gopal Mishra |  2298.00 |   200.00 | 2098.00 | created | 2022-08-25 13:23:22 | 2022-08-25 13:23:22 |
|       3 | Komal Singh  |  3998.00 |   800.00 | 3198.00 | created | 2022-08-25 13:23:22 | 2022-08-25 13:23:22 |
+---------+--------------+----------+----------+---------+---------+---------------------+---------------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM `order_products`;
+----+---------+-----------+----------+---------+----------+---------+---------+---------------------+---------------------+
| id | orderid | productid | quantity | mrp     | discount | price   | total   | created_at          | updated_at          |
+----+---------+-----------+----------+---------+----------+---------+---------+---------------------+---------------------+
|  1 |       1 |         1 |        1 |  999.00 |   100.00 |  899.00 |  899.00 | 2022-08-25 13:23:35 | 2022-08-25 13:23:35 |
|  2 |       2 |         2 |        1 | 1099.00 |   100.00 |  999.00 |  999.00 | 2022-08-25 13:23:35 | 2022-08-25 13:23:35 |
|  3 |       2 |         3 |        1 | 1199.00 |   100.00 | 1099.00 | 1099.00 | 2022-08-25 13:23:35 | 2022-08-25 13:23:35 |
|  4 |       3 |         4 |        2 | 1999.00 |   400.00 | 1599.00 | 3198.00 | 2022-08-25 13:23:35 | 2022-08-25 13:23:35 |
+----+---------+-----------+----------+---------+----------+---------+---------+---------------------+---------------------+
4 rows in set (0.00 sec)

Creating Views

Now, let's create a view that will represent the sales orders which has exactly 1 product:

CREATE VIEW SingleProductOrders AS
SELECT o.orderid, o.name, o.status, o.subtotal, o.discount, o.total, p.name AS product, COUNT('op.*') AS no_of_products
FROM orders o
INNER JOIN order_products op ON op.orderid=o.orderid
INNER JOIN products p ON p.productid=op.productid
GROUP BY o.orderid
HAVING COUNT('op.*') = 1
ORDER BY o.orderid DESC
LIMIT 10;

Next, let's create a view that will represent the sales orders which has more than 1 products:

CREATE VIEW MultipleProductOrders AS
SELECT o.orderid, o.name, o.status, o.subtotal, o.discount, o.total, GROUP_CONCAT(p.name) AS products, COUNT('op.*') AS no_of_products, SUM(op.quantity) AS quantity
FROM orders o
INNER JOIN order_products op ON op.orderid=o.orderid
INNER JOIN products p ON p.productid=op.productid
GROUP BY o.orderid
HAVING COUNT('op.*') > 1
ORDER BY o.orderid DESC
LIMIT 10;

How to query a View in MySQL?

You can query a view in MySQL as follows:

SELECT * FROM SingleProductOrders;
SELECT * FROM MultipleProductOrders;

OUTPUT:

mysql> SELECT * FROM SingleProductOrders;
+---------+-------------+---------+----------+----------+---------+-------------+----------------+
| orderid | name        | status  | subtotal | discount | total   | product     | no_of_products |
+---------+-------------+---------+----------+----------+---------+-------------+----------------+
|       3 | Komal Singh | created |  3998.00 |   800.00 | 3198.00 | Black Jeans |              1 |
|       1 | Ram Verma   | created |   999.00 |   100.00 |  899.00 | Red Shirt   |              1 |
+---------+-------------+---------+----------+----------+---------+-------------+----------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM MultipleProductOrders;
+---------+--------------+---------+----------+----------+---------+-------------------------+----------------+----------+
| orderid | name         | status  | subtotal | discount | total   | products                | no_of_products | quantity |
+---------+--------------+---------+----------+----------+---------+-------------------------+----------------+----------+
|       2 | Gopal Mishra | created |  2298.00 |   200.00 | 2098.00 | Yellow Shirt,Blue Jeans |              2 |        2 |
+---------+--------------+---------+----------+----------+---------+-------------------------+----------------+----------+
1 row in set (0.00 sec)

How to show existing views in MySQL?

There are following ways to show up the existing views in MySQL:

=> Showing views using SHOW FULL TABLE statement.

Since MySQL considers views as tables with the type 'VIEW'. So, we can show all the existing views in the currently selected database using below query:
 
Example: SHOW FULL TABLES WHERE `table_type` = 'VIEW';

mysql> SHOW FULL TABLES WHERE `table_type` = 'VIEW';
+-----------------------+------------+
| Tables_in_sample      | Table_type |
+-----------------------+------------+
| MultipleProductOrders | VIEW       |
| SingleProductOrders   | VIEW       |
+-----------------------+------------+
2 rows in set (0.00 sec)

You can also show views from different database with the help of FROM or IN clause as follows:

Syntax:

SHOW FULL TABLES [{FROM | IN} database_name] WHERE table_type='VIEW';

Example 1: SHOW FULL TABLES FROM `sample` WHERE `table_type` = 'VIEW';

mysql> SHOW FULL TABLES FROM `sample` WHERE `table_type` = 'VIEW';
+-----------------------+------------+
| Tables_in_sample      | Table_type |
+-----------------------+------------+
| MultipleProductOrders | VIEW       |
| SingleProductOrders   | VIEW       |
+-----------------------+------------+
2 rows in set (0.01 sec)

Example 2: SHOW FULL TABLES IN `sample` WHERE `table_type` = 'VIEW';

mysql> SHOW FULL TABLES IN `sample` WHERE `table_type` = 'VIEW';
+-----------------------+------------+
| Tables_in_sample      | Table_type |
+-----------------------+------------+
| MultipleProductOrders | VIEW       |
| SingleProductOrders   | VIEW       |
+-----------------------+------------+
2 rows in set (0.01 sec)

You can also show views using LIKE clause based on some pattern as follows:

Syntax:

SHOW FULL TABLES [{FROM | IN} database_name] LIKE pattern;

Example: SHOW FULL TABLES FROM `sample` LIKE 'Single%';

mysql> SHOW FULL TABLES FROM `sample` LIKE 'Single%';
+----------------------------+------------+
| Tables_in_sample (Single%) | Table_type |
+----------------------------+------------+
| SingleProductOrders        | VIEW       |
+----------------------------+------------+
1 row in set (0.00 sec)

=> Showing views using INFORMATION_SCHEMA.

As mentioned in the MySQL official documentation, the INFORMATION_SCHEMA provides access to database metadata, information about the MySQL server such as the name of the database or table, the data type of a column, or access privileges. It is also sometime called as data dictionary and system catalogue.

We can also show views using INFORMATION_SCHEMA as follows:

Example:
SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE FROM INFORMATION_SCHEMA.tables WHERE `TABLE_SCHEMA` = 'sample' AND TABLE_TYPE='VIEW';

mysql> SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE FROM INFORMATION_SCHEMA.tables WHERE `TABLE_SCHEMA` = 'sample' AND TABLE_TYPE='VIEW';
+--------------+-----------------------+------------+
| TABLE_SCHEMA | TABLE_NAME            | TABLE_TYPE |
+--------------+-----------------------+------------+
| sample       | MultipleProductOrders | VIEW       |
| sample       | SingleProductOrders   | VIEW       |
+--------------+-----------------------+------------+
2 rows in set (0.01 sec)