常用数据库操作语句

下面的SQL语句都是在Oracle上进行的。
[color=red]
一、创建新表的同时，将查询结果插入新表[/color]

create table temp as  (
select *
  from (select * from its_ptroadstatus_tbl t where t.roadid > 1000000)
 where rownum < 10001
union
select *
  from its_ptroadstatus_tbl t
 where t.roadid < 1000000
   and rownum < 10
)

[color=red]二、将查询结果直接插入另一个表中：[/color]

insert into roadstatus_20090721
  (RUNNO, roadid, emptytaxispeed, emptytaxitime, intime)
  (select b.RUNNO, b.ROADID, b.EMPTYTAXISPEED, b.EMPTYTAXITIME, b.INTIME
     from autonavi_high_41000 a
    inner join (select *
                 from its_ptroadstatus_tbl t
                where t.intime between
                      to_date('2009-7-21 00:00:00', 'yyyy-mm-dd HH24:mi:ss') and
                      to_date('2009-7-21 23:59:59', 'yyyy-mm-dd HH24:mi:ss')) b on a.road_ = b.roadid);

[color=red]三、大数据表创建分区表的步骤：[/color]
1. 创建分区表：

 CREATE TABLE T_NEW (RUNNO NUMBER(12),ROADID NUMBER(10),EMPTYTAXISPEED NUMBER(6,2), EMPTYTAXITIME DATE, INTIME DATE) PARTITION BY RANGE (INTIME)
    (PARTITION Part1_20090707 VALUES LESS THAN (TO_DATE('2009-07-08', 'YYYY-MM-DD')) tablespace  SUNLI,
     PARTITION Part2_20090714 VALUES LESS THAN (TO_DATE('2009-07-15', 'YYYY-MM-DD')) tablespace  SUNLI,
     PARTITION Part3_20090721 VALUES LESS THAN (TO_DATE('2009-07-22', 'YYYY-MM-DD')) tablespace  SUNLI,
     PARTITION Part4_20090728 VALUES LESS THAN (TO_DATE('2009-07-29', 'YYYY-MM-DD')) tablespace  SUNLI,
     PARTITION Part5_20090804 VALUES LESS THAN (TO_DATE('2009-08-05', 'YYYY-MM-DD')) tablespace  SUNLI,
     PARTITION Part6_20090811 VALUES LESS THAN (TO_DATE('2009-08-12', 'YYYY-MM-DD')) tablespace  SUNLI,
     PARTITION Part7_20090818 VALUES LESS THAN (TO_DATE('2009-08-19', 'YYYY-MM-DD')) tablespace  SUNLI,
     PARTITION Part8_20090825 VALUES LESS THAN (MAXVALUE)) tablespace  SUNLI;

2. 交换分区

ALTER TABLE T_NEW EXCHANGE PARTITION Part1_20090707 WITH TABLE ITS_PTROADSTATUS_TBL without validation;

3. 原表改名

alter table ITS_PTROADSTATUS_TBL rename to T_OLD;

或者：

RENAME ITS_PTROADSTATUS_TBL TO T_OLD;

4. 新表改名

alter table T_NEW  rename to ITS_PTROADSTATUS_TBL;

5. 删除原表

drop table T_OLD;

6. 创建新表触发器和索引

create index idx_its_ptroadstatus_tbl on ITS_PTROADSTATUS_TBL(roadid) local tablespace SUNLI;

[color=red]四：数据表的导入和导出[/color]
1、数据表的导出：

exp 用户名/密码@ORADB  file= d:\RD20090714.dmp tables= (ROADSTATUS_20090714)

2、数据表的导入：

imp 用户名/密码@ORADB  full=y file=E:\路况数据\P20090721.dmp  ignore=y