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: