Introduction
In this lab, you will learn how to work with MySQL views. 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. You will learn how to create, update, query, and drop MySQL views.
You will 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.
Connect to MySQL and Create Database/Table
In this step, you will connect to the MySQL server and set up the necessary database and table for the lab. This involves creating a database named company and an employees table within it, populated with some sample data.
First, open a terminal in the LabEx VM. You should already be in the ~/project directory.
Connect to the MySQL server as the root user. Since you have sudo privileges and the root user in MySQL is configured to use the auth_socket plugin, you can connect without a password using sudo.
sudo mysql -u root
You are now in the MySQL shell. The prompt will change to mysql>.
Inside the MySQL shell, create a database named company:
CREATE DATABASE company;
Switch to the company database so that subsequent commands operate within this database:
USE company;
Now, create a table named employees to store employee information:
CREATE TABLE employees (
id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
salary DECIMAL(10, 2),
department VARCHAR(50)
);
Insert some sample data into the employees table:
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');
You can verify the data in the employees table by selecting all rows:
SELECT * FROM employees;
The output should show the inserted employee data:
+----+------------+-----------+----------+-------------+
| id | first_name | last_name | last_name | department |
+----+------------+-----------+----------+-------------+
| 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 |
+----+------------+-----------+----------+-------------+
5 rows in set (0.00 sec)
You have successfully connected to MySQL, created a database, and populated a table with data.
Create a View
In this step, you will create a view based on the employees table. A view is a stored SQL query that acts like a virtual table. It does not store data itself but presents data from one or more underlying tables.
You should still be in the MySQL shell, connected to the company database. If not, reconnect using sudo mysql -u root and then USE company;.
We will create a view named sales_employees that will show only the employees who work in the 'Sales' department. This view will select specific columns from the employees table.
CREATE VIEW sales_employees AS
SELECT id, first_name, last_name, salary
FROM employees
WHERE department = 'Sales';
This statement defines a view named sales_employees. The view's definition is the SELECT query that follows the AS keyword. This query selects the id, first_name, last_name, and salary columns from the employees table, but only for rows where the department column is equal to 'Sales'.
To confirm that the view has been created, you can list the tables and views in the current database:
SHOW TABLES;
You should see both employees and sales_employees listed.
+-------------------+
| Tables_in_company |
+-------------------+
| employees |
| sales_employees |
+-------------------+
2 rows in set (0.00 sec)
You can also describe the structure of the view, just like you would a table:
DESCRIBE sales_employees;
This will show the columns included in the view:
+------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| id | int | NO | | NULL | |
| first_name | varchar(50) | YES | | NULL | |
| last_name | varchar(50) | YES | | NULL | |
| salary | decimal(10,2) | YES | | NULL | |
+------------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
You have successfully created a view named sales_employees.
Query Data Using the View
In this step, you will learn how to query data using the sales_employees view you created. Views can be queried just like regular tables, providing a simplified way to access specific subsets of data.
You should still be in the MySQL shell, connected to the company database. If not, reconnect using sudo mysql -u root and then USE company;.
To retrieve all data from the sales_employees view, use a standard SELECT statement:
SELECT * FROM sales_employees;
This query executes the underlying SELECT statement defined in the view and returns the result. You should see only the employees from the 'Sales' department:
+------+------------+-----------+----------+
| id | first_name | last_name | salary |
+------+------------+-----------+----------+
| 1 | John | Doe | 60000.00 |
| 3 | Robert | Jones | 50000.00 |
+------+------------+-----------+----------+
2 rows in set (0.00 sec)
You can also apply additional filtering or sorting to the data retrieved from the view. For example, to find the sales employee with a salary greater than 55000:
SELECT * FROM sales_employees WHERE salary > 55000;
This will return only the rows from the view where the salary is greater than 55000:
+------+------------+-----------+----------+
| id | first_name | last_name | salary |
+------+------------+-----------+----------+
| 1 | John | Doe | 60000.00 |
+------+------------+-----------+----------+
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 display only the first and last names of the sales employees:
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| John | Doe |
| Robert | Jones |
+------------+-----------+
2 rows in set (0.00 sec)
Querying a view is essentially the same as querying a table, but the view provides a predefined filter and selection of columns from the underlying data source.
Update the View Definition with ALTER VIEW
In this step, you will learn how to modify the definition of an existing view using the ALTER VIEW statement. This is useful when you need to change the columns included in the view or the filtering criteria.
You should still be in the MySQL shell, connected to the company database. If not, reconnect using sudo mysql -u root and then USE company;.
Currently, the sales_employees view includes id, first_name, last_name, and salary. Let's modify the view to also include the department column.
ALTER VIEW sales_employees AS
SELECT id, first_name, last_name, salary, department
FROM employees
WHERE department = 'Sales';
This statement uses ALTER VIEW followed by the view name and the new SELECT query that defines the view. The new query now includes the department column.
To verify that the view has been updated, describe it again:
DESCRIBE sales_employees;
You should now see the department column in the output:
+------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| id | int | NO | | NULL | |
| first_name | varchar(50) | YES | | NULL | |
| last_name | varchar(50) | YES | | NULL | |
| salary | decimal(10,2) | YES | | NULL | |
| department | varchar(50) | YES | | NULL | |
+------------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
Query the updated view to see the new column:
SELECT * FROM sales_employees;
The output will now include the department column:
+------+------------+-----------+----------+------------+
| 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)
You have successfully updated the definition of the sales_employees view.
Drop the View and Clean Up
In this final step, you will learn how to drop (delete) the view and clean up the database and table created during this lab.
You should still be in the MySQL shell, connected to the company database. If not, reconnect using sudo mysql -u root and then USE company;.
To remove the sales_employees view, use the DROP VIEW statement:
DROP VIEW sales_employees;
This command permanently removes the sales_employees view from the company database.
To confirm that the view has been dropped, you can try to describe it:
DESCRIBE sales_employees;
This should result in an error message indicating that the view does not exist:
ERROR 1146 (42S02): Table 'company.sales_employees' doesn't exist
Now, let's clean up the employees table and the company database.
Drop the employees table:
DROP TABLE employees;
Drop the company database:
DROP DATABASE company;
You can exit the MySQL shell by typing:
exit
You have successfully dropped the view, the table, and the database, cleaning up the resources used in this lab.
Summary
In this lab, you learned how to work with MySQL views. You started by connecting to the MySQL server and setting up a database and table. You then created a view named sales_employees to provide a filtered view of the employees table.
You practiced querying the view using SELECT statements, demonstrating how views simplify data access. You also learned how to modify an existing view's definition using the ALTER VIEW statement to include additional columns. Finally, you learned how to remove a view using the DROP VIEW statement and cleaned up the database and table.
You now understand the basic concepts and operations for working with MySQL views, which are valuable tools for managing and accessing data in a database.
