oracle对象
1.表
a)创建表1
create table students(
id number(10),
stuno number(10) ,
sex varchar2(2),
age int,
classno varchar2(4) ,
regdate date default sysdate
);
b)创建表2
create table student_2
as select * from students;
修改表:
为表增加列(字段)
alter table students add (province varchar2(10));
为表删除列
alter table students drop column province;
修改列
alter table students modify classno varchar2(6);
重命名表:
alter talbe students rename to students_new;
删除表:
drop table students;//仅删除表
drop table students cascade constraints;//删除表和这个表的视图、约束或触发器
约束:既可以在create table语句进行,也可以在alter table 中进行
非空约束:
1)在create table中:
create table students(
id number(10),
stuno number(10) not null,
sex varchar2(2),
age int,
classno varchar2(4) not null,
regdate date default sysdate
);
2)在alter table中
alter table students modify stuno not null;
主键约束(已经包含非空约束):
增加
1)在create table中:
create table students(
id number(10) primary key,
stuno number(10) not null,
sex varchar2(2),
age int,
classno varchar2(4) not null,
regdate date default sysdate
);
2)在alter table中
alter table students add primary key(id);
alter table students add constraint Students_PK primary key(id);
删除
alter table students drop constraint Students_PK;
外键约束:
1)在create table中:
create table students(
id number(10) primary key,
stuno number(10) not null,
sex varchar2(2),
age int,
classno varchar2(4) not null,
regdate date default sysdate,
classid number(4),
constraint Students_FK foreign key(classid) references class(classid)
);
2)在alter table中:
alter table students add constraint Students_FK foreign key(classid) references class(classid);
索引对象:
B树(Btree)索引;
create index students_stuno_index on students(stuno);
位图索引:
create bitmap index students_sex_index on students(sex);
删除索引:
drop index stuents_stuno_index;
视图对象:
创建视图:
create or replace view students_view as
select id,stuno,sex from students;
创建只读视图:
create or replace view students_view as
select id,stuno,sex from students with read only;
删除视图:
drop view students_view;
序列:
创建序列:
create sequence students_seq
maxvalue 99999
start with 9000
increment by 100
cache 50;
使用序列:
insert into students(id) values(students_seq.nextval);
删除序列:
drop sequence students_seq;
你的能量超乎你想象_________