oracle建表语句.docVIP

oracle建表语句 篇一：oracle基本建表语句及编辑操作 --增加主键 altertable表名addconstraint主键名primarykey (字段名1); --增加外键: altertable表名 addconstraint外键名foreignkey (字段名1) references关联表 (字段名2); --在建立表格时就指定主键和外键 createtable T_STU ( STU_IDchar(5) notnull, STU_NAME varchar2(8)notnull, constraint PK_T_STU primarykey (STU_ID) ); --主键和外键一起建立： createtable T_SCORE ( EXAM_SCORE number(5,2),76543 EXAM_DATEdate, AUTOIDnumber(10) notnull, STU_IDchar(5), SUB_IDchar(3), constraint PK_T_SCORE primarykey (AUTOID) ) --constraint FK_T_SCORE_REFE foreign key (STU_ID) --references T_STU (STU_ID) --创建表 createtable lbffInfo( personid varchar2(40) notnullprimarykey, depid number, officeid number, lbid number, lbfftime date, lbdqtime date ) --查询表 select * from classes; --删除表 droptable personinfo; --修改表的名称 rename alist_table_copy to alist_table; --显示表结构 describe test--不对没查到 -----------------------对字段的操作----------------------------------- --增加列 altertable personinfo add autoid number; --删除列76543 altertabletestdropcolumn address; --修改列的名称 altertabletestmodify address addresses varchar(40; --修改列的属性 altertabletest modi createtable test1( idnumber(9) primarykeynotnull, namevarchar2(34) ) rename test2 totest; --创建自增的序列 --创建sequence CREATESEQUENCE laobao_sequence1 INCREMENTBY1-- 每次加几个 STARTWITH1-- 从1开始计数 NOMAXVALUE-- 不设置最大值 NOCYCLE-- 一直累加，不循环 CACHE10; --创建触发器 CREATEORREPLACETRIGGER Laobao_trigger_personinfo BEFOREINSERTON personinfo FOREACHROW BEGIN SELECT laobao_sequence1.NEXTVAL INTO :new.AUTOID FROM DUAL; END; insertinto personInfo (officeid,Deptid,Id,name) values(1,1,#39;GF082103#39;,#39;么亚群3#39;); deletefrom personinfo where deptid=1 createsequence class_seq incrementby1startwith1MAXVALUE999999NOCYCLENOCACHE; select class_seq.currval from dual --插入数据 insertinto classes values(class_seq.nextval,#39;软件一班#39;) commit; --更新数据 update stu_account set username=#39;aaa#39;where count_id=2; commit; --创建唯一索引 createuniqueindex username on stu_accou