Oracle-分区表

1.查询学生表所有记录
  select * from xs;
2.查询学生表结构
 desc xs
3.修改学生课程表的列属性
  alter table xs_kc modify(XH varchar2(6));
4.给学生表添加主键约束
  alter table xs add constraint primarykeyname primary key(XH);
5.给学生课程表添加外键约束
  alter table xs_kc add constraint foreignkeyname foreign key(XH) references xs(XH);
6.根据现有的表 创建学生表的备份表
  create table xscp  as select * from xs;
7.删除表xscp的所有内容
  truncate table xscp;
8.删除表xscp的某一列
  alter table xscp drop column bz;
9.向表xscp中添加一列
  alter table xscp add (bz varchar2(20));
10.计算8+8结果
  select 8+8 from dual;
11.显示dual表结构
  desc dual;
12.拼接字符串
  select 'str_1' || 'str_2' from dual;
13.显示系统日期
  select sysdate from dual;
14.格式化日期
  select to_date('20180808','yyyymmdd') from dual;  
15.手动输入a的值 并且加1后输出
  select &a+1 from dual;
16.手动输入表名并显示查询结果
  select * from &xs;  
17.创建表xs1
  Create table xs1(XH VARCHAR2(6),XM VARCHAR2(6),ZYM VARCHAR2(6),XB VARCHAR2(2),CSSJ DATE,ZXF NUMBER(2));
18.创建表 通过出版社分区
  create table part_book1
          (  
             bid number(4),
             bookname VARCHAR2(20),
             bookpress VARCHAR2(30),
             booktime date
          )
          partition by list(bookpress)
          (
           partition part_1 values('清华大学出版社') tablespace system,
           partition part_2 values('教育出版社') tablespace users
          );
19.插入记录到表part_book1
  begin
    insert into part_book1 values(1,'oralce','清华大学出版社',to_date('20110102','yyyymmdd'));
    insert into part_book1 values(2,'音乐基础欣赏','教育出版社',to_date('20120102','yyyymmdd'));
    end;
20.查询表分区里面的内容
  select * from  part_book1 partition(part_1);
21.查询分区表
  select * from dba_tab_partitions;
22.添加分区到表  
  alter table part_book1 add partition part3 values(default);
23.创建表part_book 根据时间范围分区
  create table part_book
      (  bid number(4),
         bookname  VARCHAR2(20),
         bookpress  VARCHAR2(30),
         booktime date
      )
      partition by range(booktime)
      (
          partition part1 values less than (to_date('20100101','yyyymmdd')) tablespace system,
        partition part2 values less than (to_date('20180101','yyyymmdd')) tablespace users,
        partition part3 values less than (MAXVALUE) tablespace users
      );
24.切割分区         
   alter table part_book
         split partition part3
         at(to_date('20190101','yyyymmdd'))   
         into (partition part3,partition p4); 
25.复合分区表
  create table MobileMessage  
(  
 ACCT_MONTH VARCHAR2(6), -- 帐期 格式：年月 YYYYMM  
 AREA_NO VARCHAR2(10), -- 地域号码   
 DAY_ID VARCHAR2(2), -- 本月中的第几天 格式 DD  
 SUBSCRBID VARCHAR2(20), -- 用户标识   
 SVCNUM VARCHAR2(30) -- 手机号码  
)  
partition by range(ACCT_MONTH,AREA_NO) subpartition by list(DAY_ID)  
(  
  partition p1 values less than('200705','012')  
  (  
    subpartition shangxun1 values('01','02','03','04','05','06','07','08','09','10'),  
    subpartition zhongxun1 values('11','12','13','14','15','16','17','18','19','20'),  
    subpartition xiaxun1 values('21','22','23','24','25','26','27','28','29','30','31')  
  ),  
  partition p2 values less than('200709','014')  
  (  
    subpartition shangxun2 values('01','02','03','04','05','06','07','08','09','10'),  
    subpartition zhongxun2 values('11','12','13','14','15','16','17','18','19','20'),  
    subpartition xiaxun2 values('21','22','23','24','25','26','27','28','29','30','31')  
  ),  
  partition p3 values less than('200801','016')  
  (  
    subpartition shangxun3 values('01','02','03','04','05','06','07','08','09','10'),  
    subpartition zhongxun3 values('11','12','13','14','15','16','17','18','19','20'),  
    subpartition xiaxun3 values('21','22','23','24','25','26','27','28','29','30','31')  
  )  
);
26.插入数据
begin
insert into MobileMessage values('200701','010','04','ghk001','13800000000');  
insert into MobileMessage values('200702','015','12','myx001','13633330000');  
insert into MobileMessage values('200703','015','24','hjd001','13300000000');  
insert into MobileMessage values('200704','010','04','ghk001','13800000000');  
insert into MobileMessage values('200705','010','04','ghk001','13800000000');  
insert into MobileMessage values('200705','011','18','sxl001','13222000000');  
insert into MobileMessage values('200706','011','21','sxl001','13222000000');  
insert into MobileMessage values('200706','012','11','tgg001','13800044400');  
insert into MobileMessage values('200707','010','04','ghk001','13800000000');  
insert into MobileMessage values('200708','012','24','tgg001','13800044400');  
insert into MobileMessage values('200709','014','29','zjj001','13100000000');  
insert into MobileMessage values('200710','014','29','zjj001','13100000000');  
insert into MobileMessage values('200711','014','29','zjj001','13100000000');  
insert into MobileMessage values('200711','013','30','wgc001','13444000000');  
insert into MobileMessage values('200712','013','30','wgc001','13444000000');  
insert into MobileMessage values('200712','010','30','ghk001','13800000000');  
insert into MobileMessage values('200801','015','22','myx001','13633330000');
end;