FROM

• types of Tables
  • Permanent tables (i.e., created using create table)
  • Derived tables (i.e., returned by a subquery and held in memory)
  • Temporary tables (i.e., volatile data held in memory)
  • Virtual tables (i.e., created using create view statement)

• Derived tables

  • subquery
    • a query contained within another query.
    • be surrounded by parentheses
  • example

    SELECT concat(cust.last_name,', ',cust.first_name) full_name
    FROM
    (SELECT first_name,last_name, email
    FROM customer
    WHERE first_name = 'JESSIE'
    ) cust;
    

    • 括号内部的为subquery且用cust来指代此derived table

• Temporary tables

  • 进入temporary tables的数据将会在某个时刻被删除，如数据处理完成后，或数据库关闭时。
  • CREATE TEMPORARY TABLE $table_name;

• Views
  • tere is no data associated with a view
  • CREATE VIEW $view_name AS SELECT $fields... FROM $table;

• Table Links

  SELECT customer.first_name, customer.last_name,
       time(rental.rental_date) rental_time
  FROM customer
  INNER JOIN rental
  ON customer.customer_id = rental.customer_id
  WHERE date(rental.rental_date) = '2005-06-14';
  

• Defining Table Aliases

  • SELECT $aliases.fields... FROM $table $aliases; 等于 SELECT $alias.filelds... FROM $table AS $aliases;