Author: Francis Ndungu
Last Updated: Tue, Sep 28, 2021In MySQL, views are virtual tables for running pre-defined queries. Thus, they're suitable in database applications that require tight security. For instance, you can design an application that allows end-users to run complex queries using views without directly interacting with base tables to hide sensitive information. In such a case, clients connecting to your database will only view the relevant columns. This reduces data distraction and allows you to move some of your business logic to the database.
In simple terms, you can define MySQL users that only have SELECT
privileges on views and no other database permissions. In this guide, you'll create, use, and delete views on your MySQL 8.0 database server.
To complete this MySQL views tutorial, you need:
SSH to your Linux server.
Log in to your MySQL server as root
.
$ sudo mysql -u root -p
Enter the root
password for your database server and press ENTER to proceed.
Create a sample web_store
database.
mysql> CREATE DATABASE web_store;
Switch to the new database.
mysql> USE web_store;
Views can save you time when you implement them to run complex queries from multiple tables. First, create two tables and populate them by completing the steps below.
Create a products
table to store some items in your sample store.
mysql> CREATE TABLE products
(
product_id BIGINT PRIMARY KEY AUTO_INCREMENT,
product_name VARCHAR(50),
retail_price DOUBLE
) ENGINE = InnoDB;
Populate the products
table, but don't assign the product_id
column values. MySQL automatically assigns new values as you insert records to this AUTO_INCREMENT
field.
mysql> INSERT INTO products(product_name, retail_price) VALUES ('CLOUD COMPUTE', 10);
INSERT INTO products(product_name, retail_price) VALUES ('BARE METAL', 185);
INSERT INTO products(product_name, retail_price) VALUES ('DEDICATED CLOUD', 60);
INSERT INTO products(product_name, retail_price) VALUES ('MANAGED DABASES', 15);
Verify the data from the products
table.
mysql> SELECT
product_id,
product_name,
retail_price
FROM products;
Output.
+------------+-----------------+--------------+
| product_id | product_name | retail_price |
+------------+-----------------+--------------+
| 1 | CLOUD COMPUTE | 10 |
| 2 | BARE METAL | 185 |
| 3 | DEDICATED CLOUD | 60 |
| 4 | MANAGED DABASES | 15 |
+------------+-----------------+--------------+
4 rows in set (0.00 sec)
Create a sales_products
table. This table stores the quantities of products sold in your sample store. To normalize the database, don't repeat the product_names
in this table; just use the product_id
to identify the products. Later, you'll use the MySQL JOIN
statement to spell out the product names from the products
table.
mysql> CREATE TABLE sales_products
(
ref_id BIGINT PRIMARY KEY AUTO_INCREMENT,
product_id BIGINT,
unit_price DOUBLE,
qty DOUBLE
) ENGINE = InnoDB;
Populate the sales_products
table with some data.
mysql> INSERT INTO sales_products(product_id, unit_price, qty) VALUES (1, 10, 5);
INSERT INTO sales_products(product_id, unit_price, qty) VALUES (4, 15, 18);
INSERT INTO sales_products(product_id, unit_price, qty) VALUES (2, 185, 7);
INSERT INTO sales_products(product_id, unit_price, qty) VALUES (2, 185, 4);
INSERT INTO sales_products(product_id, unit_price, qty) VALUES (4, 15, 1);
INSERT INTO sales_products(product_id, unit_price, qty) VALUES (1, 10, 6);
INSERT INTO sales_products(product_id, unit_price, qty) VALUES (2, 185, 10);
INSERT INTO sales_products(product_id, unit_price, qty) VALUES (2, 185, 11);
INSERT INTO sales_products(product_id, unit_price, qty) VALUES (4, 15, 18);
INSERT INTO sales_products(product_id, unit_price, qty) VALUES (1, 10, 8);
INSERT INTO sales_products(product_id, unit_price, qty) VALUES (2, 185, 4);
Query the sales_products
table to confirm the records.
mysql> SELECT
product_id,
unit_price,
qty
FROM sales_products;
Output.
+------------+------------+------+
| product_id | unit_price | qty |
+------------+------------+------+
| 1 | 10 | 5 |
| 4 | 15 | 18 |
| 2 | 185 | 7 |
| 2 | 185 | 4 |
| 4 | 15 | 1 |
| 1 | 10 | 6 |
| 2 | 185 | 10 |
| 2 | 185 | 11 |
| 4 | 15 | 18 |
| 1 | 10 | 8 |
| 2 | 185 | 4 |
+------------+------------+------+
11 rows in set (0.00 sec)
In MySQL, follow the syntax below to create a view.
mysql> CREATE VIEW SAMPLE_VIEW
AS
SAMPLE_SELECT STATEMENT;
For instance, create a sold_products
view that links the products
and sales_products
table to get the line_total
for each record and the respective product_name
using a JOIN
statement. The SQL query for a view might be long, but you'll always get the benefit of running a simpler query when invoking it.
mysql> CREATE VIEW sold_products
AS
SELECT
sales_products.product_id,
product_name,
sales_products.qty,
sales_products.unit_price,
(
sales_products.unit_price * sales_products.qty
) as line_total
FROM sales_products
LEFT JOIN products
ON sales_products.product_id = products.product_id;
Invoke the sold_products
view by running a SELECT
statement against the object as you would a normal table. As you can see, the SQL command for your sold_products
view is short and simple.
mysql> SELECT
*
FROM sold_products;
In the output below, you have generated the product_names
from the base products
table and calculated the line_total
only by executing a simple view statement.
+------------+-----------------+------+------------+------------+
| product_id | product_name | qty | unit_price | line_total |
+------------+-----------------+------+------------+------------+
| 1 | CLOUD COMPUTE | 5 | 10 | 50 |
| 4 | MANAGED DABASES | 18 | 15 | 270 |
| 2 | BARE METAL | 7 | 185 | 1295 |
| 2 | BARE METAL | 4 | 185 | 740 |
| 4 | MANAGED DABASES | 1 | 15 | 15 |
| 1 | CLOUD COMPUTE | 6 | 10 | 60 |
| 2 | BARE METAL | 10 | 185 | 1850 |
| 2 | BARE METAL | 11 | 185 | 2035 |
| 4 | MANAGED DABASES | 18 | 15 | 270 |
| 1 | CLOUD COMPUTE | 8 | 10 | 80 |
| 2 | BARE METAL | 4 | 185 | 740 |
+------------+-----------------+------+------------+------------+
11 rows in set (0.00 sec)
You can also use MySQL aggregate functions like SUM
when creating views. For instance, to generate a sales summary report for your products, create a sales_summary
view.
mysql> CREATE VIEW sales_summary
AS
SELECT
sales_products.product_id,
product_name,
sum(sales_products.qty) as units_sold,
SUM(
(
sales_products.unit_price * sales_products.qty
)
) as total_sales
FROM sales_products
LEFT JOIN products
ON sales_products.product_id = products.product_id
GROUP BY sales_products.product_id
;
Run the sales_summary
view.
mysql> SELECT
*
FROM sales_summary;
You should now see a list of total units you've sold and the total revenue you've generated in your sample store.
+------------+-----------------+------------+-------------+
| product_id | product_name | units_sold | total_sales |
+------------+-----------------+------------+-------------+
| 1 | CLOUD COMPUTE | 19 | 190 |
| 4 | MANAGED DABASES | 37 | 555 |
| 2 | BARE METAL | 36 | 6660 |
+------------+-----------------+------------+-------------+
3 rows in set (0.00 sec)
In the output above, the product DEDICATED CLOUD
with a product_id
of 3
is missing because it has no entry in the sales_products
table. However, in MySQL, you can use the JOIN
statement to link a user-defined view with normal tables when running queries. For instance, join the sales_summary
view and the products
table to generate a more meaningful report that includes products with 0 sales.
mysql> SELECT
products.product_id,
products.product_name,
IFNULL(sales_summary.units_sold, 0) AS units_sold,
IFNULL(sales_summary.total_sales, 0) AS total_sales
FROM products
LEFT JOIN sales_summary
ON products.product_id = sales_summary.product_id;
You're now able to view products including those with 0 sales as shown below.
+------------+-----------------+------------+-------------+
| product_id | product_name | units_sold | total_sales |
+------------+-----------------+------------+-------------+
| 1 | CLOUD COMPUTE | 19 | 190 |
| 2 | BARE METAL | 36 | 6660 |
| 3 | DEDICATED CLOUD | 0 | 0 |
| 4 | MANAGED DABASES | 37 | 555 |
+------------+-----------------+------------+-------------+
4 rows in set (0.00 sec)
To see how the security aspect of MySQL views works, execute the steps below.
Create a new MySQL user.
mysql> CREATE USER 'sample_v_user'@'localhost' IDENTIFIED WITH mysql_native_password BY 'EXAMPLE_PASSWORD';
Grant the sample_v_user
privileges to run the sold_products
and sales_summary
views. Don't issue any other permissions to the sample_v_user
.
mysql> GRANT SELECT ON web_store.sold_products TO 'sample_v_user'@'localhost';
GRANT SELECT ON web_store.sales_summary TO 'sample_v_user'@'localhost';
FLUSH PRIVILEGES;
Exit your root
session from the MySQL server.
mysql> EXIT;
Log back in as the sample_v_user
.
$ mysql -u sample_v_user -p
Key in the password for the sample_v_user
and press ENTER to log in. Then, switch to the web_store
database.
mysql> USE web_store;
Run the sold_products
view.
mysql> SELECT
*
FROM sold_products;
Output.
+------------+-----------------+------+------------+------------+
| product_id | product_name | qty | unit_price | line_total |
+------------+-----------------+------+------------+------------+
| 1 | CLOUD COMPUTE | 5 | 10 | 50 |
| 4 | MANAGED DABASES | 18 | 15 | 270 |
| 2 | BARE METAL | 7 | 185 | 1295 |
| 2 | BARE METAL | 4 | 185 | 740 |
| 4 | MANAGED DABASES | 1 | 15 | 15 |
| 1 | CLOUD COMPUTE | 6 | 10 | 60 |
| 2 | BARE METAL | 10 | 185 | 1850 |
| 2 | BARE METAL | 11 | 185 | 2035 |
| 4 | MANAGED DABASES | 18 | 15 | 270 |
| 1 | CLOUD COMPUTE | 8 | 10 | 80 |
| 2 | BARE METAL | 4 | 185 | 740 |
+------------+-----------------+------+------------+------------+
11 rows in set (0.00 sec)
Run the sales_summary
view.
mysql> SELECT
*
FROM sales_summary;
Output.
+------------+-----------------+------------+-------------+
| product_id | product_name | units_sold | total_sales |
+------------+-----------------+------------+-------------+
| 1 | CLOUD COMPUTE | 19 | 190 |
| 4 | MANAGED DABASES | 37 | 555 |
| 2 | BARE METAL | 36 | 6660 |
+------------+-----------------+------------+-------------+
3 rows in set (0.00 sec)
As the sample_v_user
, you're able to run the views. Now, query the products
and sales_products
base tables.
The products
table.
mysql> SELECT
*
FROM products;
The sales_products
table.
mysql> SELECT
*
FROM sales_products;
You should now receive the errors below because you're not authorized to access these base tables.
ERROR 1142 (42000): SELECT command denied to user 'sample_v_user'@'localhost' for table 'products'
ERROR 1142 (42000): SELECT command denied to user 'sample_v_user'@'localhost' for table 'sales_products'
You can use MySQL views to hide tables or columns with sensitive information such as users' passwords, addresses, credit card numbers, and more in a production environment.
Exit from the MySQL server.
mysql> EXIT;
Just like other database objects, you can DROP
a MySQL view if you no longer need it or if you want to redefine its SQL statement.
Follow the syntax below to DROP
a view.
mysql> DROP VIEW IF EXISTS SAMPLE_VIEW;
For example, to DROP
the sales_summary
view, log in back to the MySQL server as root.
$ sudo mysql -u root -p
Enter the root
password for the MySQL server and press ENTER to proceed. Then, switch to the web_store
database.
mysql> USE web_store;
Drop the sales_summary
view.
mysql> DROP VIEW IF EXISTS sales_summary;
Output.
Query OK, 0 rows affected (0.01 sec)
In this guide, you've created, executed, and dropped views on your MySQL 8 database server.