根据旧表创建新表

 

使用旧表创建新表的命令是什么？用create命令可以实现吗？
select * into newTable from jiuTable

select * into 新表 from 旧表 where 1=2

create table tab_old as select * from tab_new

create table tb  (id int,name varchar(10))
insert into tb select 1,'a'

create table tb2  (id int,name varchar(10))
insert into tb2 select * from tb

select * from tb2

 

create table new_table
as
(select * from old_table)
with data

 

在ansi 语法中可以使用(比如oracle)
create table table_name 
as 
select * from table_name2

在sql_2000中 
是
select * 
into table_name
from 
table_name2
(table_name2 可以是嵌套表)

 

oracle利用现有表创建新表
关键字: 数据库 oracle 利用现有表创建新表

CREATE TABLE <newtable> AS SELECT {* | column(s)} FROM <oldtable> [WHERE <condition>];
exp:
    SQL> CREATE TABLE yonghu_bak AS SELECT * FROM yonghul;
    SQL> CREATE TABLE yonghu_bak AS SELECT id, name,sex FROM yonghu;
    SQL> CREATE TABLE yonghu_bak AS SELECT * FROM yonghu WHERE 1=2;

当遇到一个部门有多个员工记录，需要去取出每个部门薪水最少的那笔时，就可以用到分析函数row_number()
select * from(

        select manager_id,employee_id,first_name,salary,row_number()
        over(partition by manager_id order by salary) as currowid
       from hr.employees)
where currowid = 1

PS：

（1）建一个新表，架构、字段属性、约束条件、数据记录跟旧表完全一样：

Create Table print_his_0013 as Select * from print_his_0007

（2）建一个新表，架构跟旧表完全一样，但没有内容：

Create Table print_his_0013 as Select * from print_his_0007 where 1=2

例：现在有一个salgrade表，表中有如下记录：
  SQL> select * from salgrade;

     GRADE      LOSAL      HISAL                                               
---------- ---------- ----------                                               
         1        700       1200                                               
         2       1201       1400                                               
         3       1401       2000                                               
         4       2001       3000                                               
         5       3001       9999    

现在想利用这个salgrade表创建一个新表salgrade1，并在最后面添加一条记录：
create table salgrade1 as select * from salgrade;

select * from salgrade1;
     GRADE      LOSAL      HISAL                                               
---------- ---------- ----------                                               
         1        700       1200                                               
         2       1201       1400                                               
         3       1401       2000                                               
         4       2001       3000                                               
         5       3001       9999    

添加一条记录：
insert into salgrade1 values(6,10000,15000);

删除一条记录：
delete from salgrade1 where grade=6;