三、oracle数据定义ddl和dml34426

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,ORACLE数据定义DDL和数据操纵DML,本章重点,ORACLE数据类型,表创建/变动/删除,表分区的使用,同义词对象创建/删除,数据操纵语言DML,ORACLE中常见函数,ORACLE主要的数据类型,Character数据类型,Number数据类型,Date数据类型,RAW和LongRaw数据类型,LOB数据类型,Character数据类型,char类型,固定大小，范围为12000,varchar2类型,可变长字符串,范围为14000,long类型,可变长的字符数据，最大可达2GB；特殊要求如下：,1）表中只有一列为long类型,2）long列不能定义为主键或者唯一约束,3）不能在该列上建立索引,4）过程或者存储过程不能使用该类型作为参数,Number数据类型,存储精度为38的浮点数、正数、负数等等,使用格式:1)number,2)number(p),3)number(p,s),说明：p是精度即总位数,s为右边的小数位数,Date数据类型,Raw和LongRaw数据类型,Raw数据类型,存储面向字节的数据，如：二进制数和字符串,最大范围,为2000个字节，使用时要指定大小,LongRaw数据类型,存储二进制数据，最大为2GB，跟long类型的限制一样,LOB数据类型,CLOB,存储字符串Charactor的LOB类型,BLOB,存储二进制数据Binary的LOB类型，一般来存储大型的,二进制对象,BFILE,存储二进制的文件,存储非结构化的数据，最大的范围是4GB,表的创建,CREATE TABLE schema.table,(column datatype DEFAULT expr column_constraint.,|table_constraint,),PCTFREE integer PCTUSED integer,INITRANS integer MAXTRANS integer,TABLESPACE tablespace,STORAGE storage_clause,CACHE|NOCACHE ,|CLUSTER cluster(column,column.),ENABLE enable_clause,|DISABLE disable_clause .,AS subquery,说明：,1、TABLESPACE指定了该表使用的表空间,2、AS subquery表示使用子查询创建表，只需要指定列名和列上的约束，,不需要指定数据类型（或者列名也不指定）,3、Enable|Disable表示是否使用完整性约束,4、如果需要指定schema，即为其他用户创建表，则需要有Create any,Table的系统权限,示例-表创建(1),create table tab_student(sno char(11)primary key,sname varchar2(30)not null,sbirth date,sage int default 0,ssex char(2)not null,remark varchar2(60),constraint ck_tab_student_sage check(ssex in(男,女)tablespace ts_stock,说明：,1、表创建时，就跟约束一起创建，该例中使用到的约束有：primary,key,not null,default,check(未出现的还有foreign key，unique等),2、思考建表后是如何建立约束呢？,示例-表创建(2),1、建表和外键约束,create table tab_score(sno char(11)primary key,scno char(3)not null,score number(8,3)default 0,constraint fk_tab_score_tab_student_sno foreign key(sno)references tab_student(sno),2、用as子句建表,create table tab_dup(aa,bb),as select sno,sname from tab_student,示例-表变动,1、增加/改动/删除列,alter table tab_student add(col1 varchar2(10)not null,col2 int);,alter table tab_student modify(ssex char(1)not null,remark varchar2(100),alter table tab_student drop(remark),2、启用/禁止/删除表约束,alter table tab_student disable constraint,ck_tab_student_sage,alter table tab_student disable primary key;,alter table tab_student enable primary key;,alter table tab_student drop constraint,ck_tab_student_sage;,示例-表删除,Drop table tab_student cascade constraints,说明：,当表中建立了完整性约束，则需要使用cascade constraints选项，表示在删除表时，将表中的所有约束一起删除；,表改名：rename tab_student to new_student,表/列注释,COMMENT ON TABLE schema.table|view|snapshot,|COLUMN schema.table|view|snapshot.column,IS text,示例如下：,comment on table tab_student is 学生表;,comment on column tab_student.sno is 学号,表分区1,分区的概念是指可以将表拆分为几个部分,在数据仓库应用程序中非常有用,根据键值范围对表进行拆分,表的每个部分称为“分区”,分区被独立存储和管理,通过引用逻辑表名称进行查询和更新,表分区2,分区方法,分区类型,范围分区,散列分区,复合分区,列表分区,表分区3,范围分区,根据列值的范围将行映射到分区,由表的分区规范进行定义：,PARTITION BY RANGE(column_list),由每个单独分区的分区规范进行定义：,VALUES LESS THAN(value_list),其中 column_list 是列的有序列表,value_list 是 column_list 中列值的有序列表,表分区4,范围分区的语法：,create table,(column_name data type,column_name data type,.),partition by range(column_name),(partition values less than,partition values less than);,Create table emp1(,Empno number(4),Ename varchar2(10),Job varchar2(9),Mgr number(4),Hibernate date,Sal number(7,2),Comm number(7,2),Depto number(2),Partition by range(empno),(partition p1 values less than(250),Partition p2 values less than(500);,表分区5,散列分区,在分区列上使用 hash 函数,是比范围分区更好的选择,如果不知道将有多少数据映射到指定的范围，散列分区非常有用,分区的数目应是 2 的幂,可以对散列分区进行命名，并将其存储在特定的表空间中,表分区6,散列分区的语法：,CREATE TABLE(.),PARTITION BY HASH(column_list),PARTITIONS,(PARTITION,TABLESPACE,PARTITION,TABLESPACE tablespace_name);,例子,Create table dept1(,Deptno number2(2),Dname varchar2(14),LOC VARCHAR(13),Partition by hash(deptno),(partition p1,partition p2);,表分区7,复合分区,使用范围分区方法对数据进行分区，并在每一个分区内使用散列分区方法将其划分为子分区,允许命名子分区,允许将子分区存储在特定的表空间,兼具范围分区和散列分区的优点,表分区8,复合分区的语法：,CREATE TABLE(),PARTITION BY RANGE(column_list),SUBPARTITION BY HASH(column_list),SUBPARTITIONS ,STORE IN(,.)(PARTITION VALUES LESS THAN(),(SUBPARTITION TABLESPACE ,.,SUBPARTITION TABLESPACE,SUBPARTITION TABLESPACE),PARTITION VALUES LESS THAN(),);,复合分区举例,Create table salgrade1(grade number,losal number,hisal number),Partition by range(losal,hisal),(partition p1 values less than(10),(subpartition sp1,subpartition sp2).,Partition p2 values less than(20),(subpartition sp3,subparation sp4);,表分区9,列表分区,基于 PARTITION BY LIST 中指定的分区键对表进行分区,明确地控制行到分区的映射,指定分区键的离散值,不同于范围分区和散列分区,Create table sales_details,(sales_id number(6),sman_name varchar(20),sales_state varchar2(15),sales_date date,amount number(10),Partition by list(sman_name),(partition sales_east values(antony,henry,jack),Partition sales_west values(peter,serena,venus),表分区10,将记录插入已分区表,记录被存储到表的分区中,基于指定的分区键执行,与定义的分区边界进行比较,示例：,SQL INSERT INTO ord_mast VALUES(o001,12-jan-96,V001,p,12-jan-97);,SQL INSERT INTO ord_mast VALUES(o010,12-feb-99,V002,c,15-mar-99);,SQL INSERT INTO ord_mast VALUES(o012,12-feb-99,V002,c,15-mar-99);,表分区11,分区维护操作,修改已分区表的一个分区,划分为两类事件：,计划事件-定期删除最旧分区的操作,非计划事件 解决应用程序或系统问题,单独查询分区,Select*from ord_mast partition(om2);,数字字典,partition,表分区12,维护分区,各种分区维护操作如下：,添加分区,移动,删除,结合,截断,拆分,合并,交换表分区,增添分区：Alter Tab