Four different types of tables base on how data store:
- Permanent tables (i.e., created using the create table statement)
- Derived tables (i.e., rows returned by a subquery and held in memory)
- Temporary tables (i.e., volatile data held in memory)
- Virtual tables (i.e., created using the create view statement)
Derived tables(subquery-generated)
mysql> SELECT concat(cust.last_name, ', ', cust.first_name) full_name
-> FROM
-> (SELECT first_name, last_name, email
-> FROM customer
-> WHERE first_name = 'JESSIE'
-> ) cust;Temporary tables
Data inserted into a temporary table will disappear at some point (generally at the end of a transaction or when your database session is closed)
mysql> CREATE TEMPORARY TABLE actors_j
-> (actor_id smallint(5),
-> first_name varchar(45),
-> last_name varchar(45)
-> );