syntax :
CREATE TABLE [IF NOT EXISTS] new_tbl [AS] SELECT * FROM existing_tbl;
1. Create an exact copy of an existing table.
create table products_bk
as
select * from products
2. Create a new table based on one or more existing tables.
create table product_sold_by_order
as
select distinct y.OrderID,
y.ProductID,
x.ProductName,
y.UnitPrice,
y.Quantity,
y.Discount,
round(y.UnitPrice * y.Quantity * (1 - y.Discount), 2) as TotalSales
from Products x
inner join Order_Details y on x.ProductID = y.ProductID
order by y.OrderID;
3. Create a new table based on one or more existing tables,
and at the same time create extra new column(s).
create table product_sold_by_order2(ID int not null auto_increment, PRIMARY KEY (ID))
as
select distinct y.OrderID,
y.ProductID,
x.ProductName,
y.UnitPrice,
y.Quantity,
y.Discount,
round(y.UnitPrice * y.Quantity * (1 - y.Discount), 2) as TotalSales
from Products x
inner join Order_Details y on x.ProductID = y.ProductID
order by y.OrderID;
ref and more detail at http://www.geeksengine.com/database/manage-table/create-table-as.php
ไม่มีความคิดเห็น:
แสดงความคิดเห็น