Introduction
In this lab, you will learn how to work with temporary tables in MySQL, focusing on their creation and usage within a single session. You will start by connecting to the MySQL server and creating a temporary table named temp_employees.
Throughout the lab, you will insert data into this table, query it to retrieve information, and explore its session-specific scope by attempting to access it from a different session. Finally, you will learn how to explicitly remove a temporary table and understand its automatic cleanup process. This lab will provide you with a practical understanding of how temporary tables can be used for session-specific data manipulation.
Connect to MySQL and Create a Temporary Table
In this step, you will connect to the MySQL server and create your first temporary table. Temporary tables are special tables that exist only for the duration of a single client session. They are automatically dropped when the session ends, making them useful for storing intermediate results or data that is only needed for a short time.
First, open a terminal in the LabEx VM. Connect to the MySQL server as the root user using the following command.
sudo mysql -u root
After running the command, you should see the MySQL prompt, which looks like mysql>. This indicates that you are successfully connected to the MySQL server and can now execute SQL commands.
Before creating the table, you need to select a database. We will use the test database, which is available by default for testing. If it doesn't exist, the following command will create it.
CREATE DATABASE IF NOT EXISTS test;
USE test;
Next, create a temporary table named temp_employees. The syntax is similar to creating a regular table, but you must include the TEMPORARY keyword.
Execute the following SQL command in the mysql> prompt:
CREATE TEMPORARY TABLE temp_employees (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255),
department VARCHAR(255)
);
This command creates a temporary table with three columns:
id: An integer that serves as the primary key and automatically increments for each new row.name: A string (up to 255 characters) to store an employee's name.department: A string (up to 255 characters) to store an employee's department.
To confirm that the temporary table was created successfully within your current session, use the DESCRIBE command.
DESCRIBE temp_employees;
The output will display the structure of the temp_employees table, confirming its columns and data types:
+------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| name | varchar(255) | YES | | NULL | |
| department | varchar(255) | YES | | NULL | |
+------------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
This output confirms that the temp_employees table is ready for use in your current MySQL session.
Insert and Query Data in the Temporary Table
Now that you have created the temp_employees table, you can interact with it just like a regular table. In this step, you will insert data into it and then query that data.
You should still be in the MySQL session from the previous step, with the mysql> prompt visible.
Let's insert a few rows of data into the temp_employees table using the INSERT INTO statement. Execute these commands one by one:
INSERT INTO temp_employees (name, department) VALUES ('Alice Smith', 'Sales');
INSERT INTO temp_employees (name, department) VALUES ('Bob Johnson', 'Marketing');
INSERT INTO temp_employees (name, department) VALUES ('Charlie Brown', 'IT');
These commands add three employee records to your temporary table.
To view the data you just inserted, use the SELECT statement. The asterisk (*) is a wildcard that means "all columns".
SELECT * FROM temp_employees;
This command retrieves all columns and rows from the temp_employees table. The output should show the data you inserted:
+----+---------------+------------+
| id | name | department |
+----+---------------+------------+
| 1 | Alice Smith | Sales |
| 2 | Bob Johnson | Marketing |
| 3 | Charlie Brown | IT |
+----+---------------+------------+
3 rows in set (0.00 sec)
You can also filter the results using a WHERE clause. For example, to find only the employees in the 'IT' department, run the following query:
SELECT name FROM temp_employees WHERE department = 'IT';
The output will show only the name of the employee who matches the condition:
+---------------+
| name |
+---------------+
| Charlie Brown |
+---------------+
1 row in set (0.00 sec)
This demonstrates that standard data manipulation and querying operations work as expected on temporary tables.
Understand Temporary Table Scope
A key characteristic of temporary tables is their session-specific scope. This means a temporary table created in one MySQL session is not visible or accessible in another, even if both sessions are connected to the same database. This step will demonstrate this behavior.
Keep your current MySQL session open in the first terminal window.
Now, open a new terminal window. You can do this by right-clicking on the desktop background and selecting "Open Terminal Here".
In this new terminal, connect to the MySQL server again. This will create a second, independent session.
sudo mysql -u root
After running the command, you should see the MySQL prompt, which looks like mysql>. This indicates that you are successfully connected to the MySQL server and can now execute SQL commands.
Next, select the test database.
USE test;
You now have two separate terminals, each with its own MySQL session. In this new session, try to query the temp_employees table that you created in the first session:
SELECT * FROM temp_employees;
You will receive an error message because this session has no knowledge of the temporary table created in the other session.
ERROR 1146 (42S02): Table 'test.temp_employees' doesn't exist
This error confirms that the temp_employees table is isolated to the session in which it was created. You can also try to describe the table, which will result in the same error.
DESCRIBE temp_employees;
ERROR 1146 (42S02): Table 'test.temp_employees' doesn't exist
This behavior is fundamental to how temporary tables work and is what makes them safe for storing temporary data without interfering with other users or processes.
You can now close this new terminal window and return to your original terminal.
Drop the Temporary Table and End the Session
Temporary tables are automatically dropped when the MySQL session ends. However, you can also explicitly drop one using the DROP TEMPORARY TABLE statement. This is good practice if you are finished with the table before your session ends, as it frees up resources.
You should be back in your original terminal window, where the temp_employees table exists.
To explicitly drop the temporary table, use the following command in the mysql> prompt:
DROP TEMPORARY TABLE temp_employees;
After a successful drop, you will see a Query OK message. To confirm that the table has been removed, try to query it again:
SELECT * FROM temp_employees;
You should now receive the "Table doesn't exist" error, confirming that the table is no longer available in this session.
ERROR 1146 (42S02): Table 'test.temp_employees' doesn't exist
Finally, to end your MySQL session, type exit and press Enter.
exit
This will close the connection to the MySQL server and return you to the regular terminal prompt. If you had not explicitly dropped the temporary table, it would have been automatically removed at this point.
Summary
In this lab, you have learned the fundamentals of working with temporary tables in MySQL. You successfully connected to the MySQL server, created a temporary table using CREATE TEMPORARY TABLE, and populated it with data using INSERT INTO. You then practiced retrieving this data with SELECT queries.
Crucially, you explored the session-specific scope of temporary tables by demonstrating that they are invisible to other sessions. Finally, you learned how to explicitly remove a temporary table with DROP TEMPORARY TABLE and understood that they are automatically cleaned up when a session ends. Temporary tables are a valuable tool for managing session-specific data and intermediate results in your database workflows.
