Oracle-基本建表语句资料讲解

--创建用户

create user han identified by han default tablespace

users Temporary TABLESPACE Temp;

grant connect,resource,dba to han; //授予用户han开发人员的权利

--------------------对表的操作--------------------------

创建表格语法:

create table 表名(

字段名1 字段类型(长度) 是否为空,

字段名2 字段类型是否为空

);

-增加主键

alter table 表名 add constraint 主键名 primary key (字段名1);

-增加外键:

alter table 表名

add constraint 外键名 foreign key (字段名1)

references 关联表 (字段名2);

在建立表格时就指定主键和外键

create table T_STU (

STU_ID char(5)

not null,

STU_NAME varchar2(8)

not null,

constraint PK_T_STU primary key (STU_ID)

);

主键和外键一起建立：

create table T_SCORE (

EXAM_SCORE number(5,2),

EXAM_DATE date,

AUTOID number(10)

not null,

STU_ID char(5),

SUB_ID char(3),

constraint PK_T_SCORE primary key (AUTOID),

constraint FK_T_SCORE_REFE foreign key (STU_ID)

references T_STU (STU_ID)

)

--创建表

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,'软件一班')

commit;

--更新数据

update stu_account set username='aaa' 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,'张三',1,sysdate);

insert into stuinfo values(stu_seq.currval,'威海');

select * from stuinfo;

create table zhuce(

zc_id number(9) not null primary key,

stu_id number(9) not null,

zhucetime date default sysdate