Knowledgebase

Introduction

In 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.

Prerequisites

To complete this MySQL views tutorial, you need:

• A Rcs Linux server
• A non-root user
• A LAMP Stack

1. Create a Sample MySQL Database

1. SSH to your Linux server.

2. Log in to your MySQL server as root.

    $ sudo mysql -u root -p

3. Enter the root password for your database server and press Enter to proceed.

4. Create a sample web_store database.

    mysql> CREATE DATABASE web_store;

5. Switch to the new database.

    mysql> USE web_store;

2. Create and Populate Sample Tables

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.

1. 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;

2. 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);

3. 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)

4. 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;

5. 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);     

6. 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)

3. Create and Invoke MySQL Views

1. In MySQL, follow the syntax below to create a view.

    mysql>  CREATE VIEW SAMPLE_VIEW
            AS 
            SAMPLE_SELECT STATEMENT;

2. 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;

3. 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)

4. 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           
            ;

5. 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) 

6. 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)

4. Use Views to Offer Database Security

To see how the security aspect of MySQL views works, execute the steps below.

1. Create a new MySQL user.

    mysql> CREATE USER 'sample_v_user'@'localhost' IDENTIFIED WITH mysql_native_password BY 'EXAMPLE_PASSWORD';

2. 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;

3. Exit your root session from the MySQL server.

    mysql> EXIT;

4. Log back in as the sample_v_user.

    $ mysql -u sample_v_user -p

5. 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;

6. 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)

7. 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)

8. 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.

9. Exit from the MySQL server.

    mysql> EXIT;

5. Drop MySQL Views

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.

1. Follow the syntax below to DROP a view.

    mysql> DROP VIEW IF EXISTS SAMPLE_VIEW;

2. For example, to DROP the sales_summary view, log in back to the MySQL server as root.

    $ sudo mysql -u root -p

3. Enter the root password for the MySQL server and press Enter to proceed. Then, switch to the web_store database.

    mysql> USE web_store;

4. Drop the sales_summary view.

    mysql> DROP VIEW IF EXISTS  sales_summary;

   Output.

    Query OK, 0 rows affected (0.01 sec)

Conclusion

In this guide, you've created, executed, and dropped views on your MySQL 8 database server.