MySQL Views and Virtual Tables

Introduction

In this lab, you will learn how to work with MySQL views, which are virtual tables based on the result-set of an SQL statement. The lab covers creating a view from a SELECT query, updating the view definition using ALTER VIEW, querying data through the view, and finally, dropping the view to clean up.

You'll start by connecting to a MySQL server, creating a database named company, and populating it with an employees table. Then, you'll create a view called sales_employees that filters the employees table to show only employees from the 'Sales' department. You'll learn how to verify the view's structure and query it like a regular table.

Skills Graph

Create a View from a SELECT Query

In this step, you will learn how to create a view in MySQL from a SELECT query. A view is a virtual table based on the result-set of an SQL statement. Views are useful for simplifying complex queries, hiding data complexity, and providing a level of abstraction for data access.

First, let's connect to the MySQL server. Open a terminal in the LabEx VM. You should already be in the ~/project directory.

mysql -u root -p

When prompted, enter the root password. If you haven't set a root password, just press Enter.

Now, let's create a database named company.

CREATE DATABASE company;

Next, switch to the company database.

USE company;

Let's create a simple table named employees with some sample data.

CREATE TABLE employees (
    id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    salary DECIMAL(10, 2),
    department VARCHAR(50)
);

INSERT INTO employees (id, first_name, last_name, salary, department) VALUES
(1, 'John', 'Doe', 60000.00, 'Sales'),
(2, 'Jane', 'Smith', 75000.00, 'Marketing'),
(3, 'Robert', 'Jones', 50000.00, 'Sales'),
(4, 'Emily', 'Brown', 80000.00, 'Engineering'),
(5, 'Michael', 'Davis', 65000.00, 'Marketing');

Now, let's create a view named sales_employees that shows only the employees from the 'Sales' department.

CREATE VIEW sales_employees AS
SELECT id, first_name, last_name, salary
FROM employees
WHERE department = 'Sales';

This statement creates a view named sales_employees. The view selects the id, first_name, last_name, and salary columns from the employees table, but only for rows where the department is 'Sales'.

To verify that the view has been created, you can describe it:

DESCRIBE sales_employees;

This command will show the structure of the sales_employees view, similar to describing a table.

You can also query the view like a regular table:

SELECT * FROM sales_employees;

This will display all the employees in the Sales department, based on the data in the employees table.

+------+------------+-----------+----------+
| id   | first_name | last_name | salary   |
+------+------------+-----------+----------+
|    1 | John       | Doe       | 60000.00 |
|    3 | Robert     | Jones     | 50000.00 |
+------+------------+-----------+----------+
2 rows in set (0.00 sec)

You have now successfully created a view from a SELECT query.

Update the View Definition with ALTER VIEW

In this step, you will learn how to update the definition of an existing view using the ALTER VIEW statement in MySQL. This is useful when you need to modify the columns selected, the filtering criteria, or any other aspect of the view's underlying query.

Continuing from the previous step, you should still be connected to the MySQL server and using the company database. If not, reconnect using:

mysql -u root -p

Enter the password if prompted, and then:

USE company;

Recall that we created a view named sales_employees that shows employees from the 'Sales' department. Let's say we want to add the department column to this view. We can use the ALTER VIEW statement to modify the view definition.

ALTER VIEW sales_employees AS
SELECT id, first_name, last_name, salary, department
FROM employees
WHERE department = 'Sales';

This statement modifies the sales_employees view to include the department column in the result set. The ALTER VIEW statement is very similar to the CREATE VIEW statement; you simply replace CREATE with ALTER.

To verify that the view has been updated, you can describe it again:

DESCRIBE sales_employees;

You should now see the department column in the output.

You can also query the updated view:

SELECT * FROM sales_employees;

This will now display the department column along with the other columns for employees in the Sales department.

+------+------------+-----------+----------+------------+
| id   | first_name | last_name | salary   | department |
+------+------------+-----------+----------+------------+
|    1 | John       | Doe       | 60000.00 | Sales      |
|    3 | Robert     | Jones     | 50000.00 | Sales      |
+------+------------+-----------+----------+------------+
2 rows in set (0.00 sec)

Let's modify the view again to include employees with salary greater than 55000 in Sales department.

ALTER VIEW sales_employees AS
SELECT id, first_name, last_name, salary, department
FROM employees
WHERE department = 'Sales' AND salary > 55000;

Now, query the updated view:

SELECT * FROM sales_employees;

This will now display the employees in the Sales department with salary greater than 55000.

+------+------------+-----------+----------+------------+
| id   | first_name | last_name | salary   | department |
+------+------------+-----------+----------+------------+
|    1 | John       | Doe       | 60000.00 | Sales      |
+------+------------+-----------+----------+------------+
1 row in set (0.00 sec)

You have now successfully updated the definition of a view using the ALTER VIEW statement.

Query Data Using the View

In this step, you will learn how to query data using the view you created and modified in the previous steps. Views can be queried just like regular tables, allowing you to retrieve specific data based on the view's definition.

Continuing from the previous step, you should still be connected to the MySQL server and using the company database. If not, reconnect using:

mysql -u root -p

Enter the password if prompted, and then:

USE company;

Recall that we have a view named sales_employees that currently shows employees from the 'Sales' department with salary greater than 55000.

To retrieve all data from the sales_employees view, you can use a simple SELECT statement:

SELECT * FROM sales_employees;

This will display all columns and rows that satisfy the view's definition. Based on the previous step, you should see only the employee(s) from the Sales department with a salary greater than 55000.

+------+------------+-----------+----------+------------+
| id   | first_name | last_name | salary   | department |
+------+------------+-----------+----------+------------+
|    1 | John       | Doe       | 60000.00 | Sales      |
+------+------------+-----------+----------+------------+
1 row in set (0.00 sec)

You can also use WHERE clauses to further filter the data retrieved from the view. For example, to find the employee with id = 1 from the sales_employees view:

SELECT * FROM sales_employees WHERE id = 1;

This will return only the row where the id is 1, but only if that employee also satisfies the view's definition (Sales department and salary greater than 55000).

+------+------------+-----------+----------+------------+
| id   | first_name | last_name | salary   | department |
+------+------------+-----------+----------+------------+
|    1 | John       | Doe       | 60000.00 | Sales      |
+------+------------+-----------+----------+------------+
1 row in set (0.00 sec)

You can also select specific columns from the view:

SELECT first_name, last_name FROM sales_employees;

This will only display the first_name and last_name columns for employees in the sales_employees view.

+------------+-----------+
| first_name | last_name |
+------------+-----------+
| John       | Doe       |
+------------+-----------+
1 row in set (0.00 sec)

You can use aggregate functions with views as well. For example, to find the average salary of employees in the sales_employees view:

SELECT AVG(salary) FROM sales_employees;

This will calculate and display the average salary.

+-------------+
| AVG(salary) |
+-------------+
|  60000.0000 |
+-------------+
1 row in set (0.00 sec)

By querying the view, you are effectively querying the underlying employees table, but with the constraints defined in the view. This simplifies complex queries and provides a level of abstraction.

Drop the View to Clean Up

In this step, you will learn how to drop (delete) the view you created in the previous steps. Dropping a view removes it from the database. It's important to clean up resources when they are no longer needed.

Continuing from the previous step, you should still be connected to the MySQL server and using the company database. If not, reconnect using:

mysql -u root -p

Enter the password if prompted, and then:

USE company;

We have a view named sales_employees that we created and modified. To drop this view, use the DROP VIEW statement:

DROP VIEW sales_employees;

This statement removes the sales_employees view from the company database.

To verify that the view has been dropped, you can try to describe it:

DESCRIBE sales_employees;

This should return an error message indicating that the table (or view) does not exist.

ERROR 1146 (42S02): Table 'company.sales_employees' doesn't exist

Alternatively, you can try to select from the view:

SELECT * FROM sales_employees;

This will also return an error message indicating that the table (or view) does not exist.

ERROR 1146 (42S02): Table 'company.sales_employees' doesn't exist

Finally, let's drop the employees table and the company database to completely clean up.

DROP TABLE employees;
DROP DATABASE company;

You have now successfully dropped the view and cleaned up the database.

Summary

In this lab, you learned how to create a view in MySQL from a SELECT query. This involved connecting to the MySQL server, creating a database and a table named employees with sample data. You then created a view named sales_employees that filters the employees table to show only employees from the 'Sales' department.

The lab demonstrated how to define a view using the CREATE VIEW statement, specifying the columns to include and the filtering criteria. You also learned how to verify the view's creation using DESCRIBE and how to query the view like a regular table using SELECT.