Database Reference
In-Depth Information
MySQL creates the columns in dst_tbl based on the name, number, and type of the
columns in src_tbl . To copy only certain rows, add an appropriate WHERE clause. To
create an empty table, use a WHERE clause that selects no rows:
CREATE TABLE dst_tbl SELECT * FROM src_tbl WHERE FALSE ;
To copy only some of the columns, name the ones you want in the SELECT part of the
statement. For example, if src_tbl contains columns a , b , c , and d , copy just b and d
like this:
CREATE TABLE dst_tbl SELECT b , d FROM src_tbl ;
To create columns in an order different from that in which they appear in the source
table, name them in the desired order. If the source table contains columns a , b , and c
that should appear in the destination table in the order c , a , b , do this:
CREATE TABLE dst_tbl SELECT c , a , b FROM src_tbl ;
To create columns in the destination table in addition to those selected from the source
table, provide appropriate column definitions in the CREATE TABLE part of the statement.
The following statement creates id as an AUTO_INCREMENT column in dst_tbl and adds
columns a , b , and c from src_tbl :
CREATE TABLE dst_tbl
(
id INT NOT NULL AUTO_INCREMENT ,
PRIMARY KEY ( id )
)
SELECT a , b , c FROM src_tbl ;
The resulting table contains four columns in the order id , a , b , c . Defined columns are
assigned their default values. This means that id , being an AUTO_INCREMENT column, is
assigned successive sequence numbers starting from 1 (see Recipe 13.1 ).
If you derive a column's values from an expression, its default name is the expression
itself, which can be difficult to work with later. In this case, it's prudent to give the column
a better name by providing an alias (see Recipe 3.2 ). Suppose that src_tbl contains
invoice information that lists items in each invoice. The following statement generates
a summary that lists each invoice named in the table and the total cost of its items, using
an alias for the expression:
CREATE TABLE dst_tbl
SELECT inv_no , SUM ( unit_cost * quantity ) AS total_cost
FROM src_tbl GROUP BY inv_no ;
CREATE TABLE SELECT is extremely convenient, but has some limitations that arise
from the fact that the information available from a result set is not as extensive as what
you can specify in a CREATE TABLE statement. For example, MySQL has no idea whether
a result set column should be indexed or what its default value is. If it's important to
include this information in the destination table, use the following techniques:
Search WWH ::




Custom Search