oracle数据库建表语句.doc

oracle数据库建表语句 篇一：Oracle 基本建表语句 --创建用户 create user han identified by han default tablespace users Temporary TABLESPACE Temp; grant connect,resource,dba to han; //授予用户han开发人员的权利 --------------------对表的操作-------------------------- --创建表 create table classes( id number(9) not null primary key, classname varchar2(40) not null ) --查询表 select * from classes; --删除表 drop table students; --修改表的名称 rename alist_table_copy to alist_table; --显示表结构 describe test --不对没查到 -----------------------对字段的操作 ----------------------------------- --增加列 alter table test add address varchar2(40); --删除列 alter table test drop column address; --修改列的名称 alter table test modify address addresses varchar(40; --修改列的属性 alter table test modi create table test1( id number(9) primary key not null, name varchar2(34) ) rename test2 to test; --创建自增的序列 create sequence class_seq increment by 1 start with 1 MAXVALUE 999999 NOCYCLE NOCACHE; select class_seq.currval from dual --插入数据 insert into classes values(class_seq.nextval,#39;软件一班#39;) commit; --更新数据 update stu_account set username=#39;aaa#39; where count_id=2; commit; --创建唯一索引 create unique index username on stu_account(username);--唯一索引 不能插入相同的数据 --行锁 在新打开的对话中不能对此行进行操作 select * from stu_account t where t.count_id=2 for update; --行锁 --alter table stuinfo modify sty_id to stu_id; alter table students drop constraint class_fk; alter table students add constraint class_fk foreign key (class_id) references classes(id);--外键约束 alter table stuinfo add constraint stu_fk foreign key (stu_id) references students(id) ON DELETE CASCADE;--外键约束,级联删除 alter table stuinfo drop constant stu_fk; insert into students values(stu_seq.nextval,#39;张三#39;,1,sysdate); insert into stuinfo values(stu_seq.currval,#39;威海#39;); select * from stuinfo; create table zhuce( zc_id number(9) not null primary key, stu_id number(9) not null, zhucetime date default sysdate ) create table feiyong ( fy_id number(9) not null primary key, s