Oracle之SQL基础创建表及表结构修改

Alert table 语句

 

追加新的列

Alter TABLE table

add (column datatype [default expr][,column datatype]...);

修改现在的列

Alter TABLE table

modify (column datatype [default expr][,column datatype]...);

为追加的列定义默认值

Alter TABLE table

drop column;

删除一列

这些操作都无法回滚

 

create table emp02 as select * from emp;

 

alter table emp_02

add(phone varchar2(20));

修改和增加列表是自動提交,无法回滚.

 

alter table emp_02

create table emp_03 as select * from emp;

 

alter table emp_03

add(phone varchar(200),address varchar2(200));

 

drop table emp_02;

通过子查询创建表

create table emp_02 as select * from emp;

alter table emp_02

add(phone varchar2(20));

 

alter table emp_02

modify (phone varchar2(200));

 

desc table emp_02;

 

alter table emp_02

modify (phone number(8));

desc emp_02;

 

alter table emp_02

modify (ename varchar2(4));

在修改一张表的字段的长度的时候要考虑原来的数据大小,如果原来的表中存在某些数据并且数据长度大于4,那么修改将会失败.

 

alter table emp_02

modify (ename number(8));

在修改一张表的字段的长度的时候要考虑原来已有的数据的类型

 

alter table emp_02

modifty (ename varchar2(20) default 'fang');

 

insert into emp_02 (empno) values(12);

commit;

一定要提交才能生效

 

如果一列的数据类型不是你将要修改的类型,那么就应该先将这一列对应的数据全部设置为空然后再修改类型

 

update emp_02 set ename=null;

 

alter table emp_02

modify (ename number(8));

 

alter table emp_02

drop colum phone;

 

 

Drop table emp_02;

该操作无法回滚

 

改名

rename emp_02 to emp_20;

 

 

delete,truncate

二者都可以删除表当中的数据

前者可以回滚后者不能回滚

delete DML

truncate DDL

 

delete table emp_20;

select * from emp_20;

rollback;

select * from emp_20;

 

truncate table emp_20;

 

create Table table_fangfanA (student_id int,student_name varchar2(20),class_id int,grade_id int);

 

 

save tarena:/sqlscript.sql

get  tarena:/sqlscript.sql

 

 

create Table fangfan_B (classroom_id number(4) PRIMARY KEY, classroom_name varchar2(20));

create table fangfan_A(student_id number(4) primary key,class_id number(4) not null constraint fk_fangfan references fangfan_B(classroom_id),student_name varchar2(20),score number(4));

alter Table fangfan_A add CONSTRAINT p_FK foreign key (class_id) references fangfan_B(classroom_id) ON DELETE CASCADE;

 

insert into fangfan_B values(1,'d8');

delete fangfan_B;

insert into fangfan_B values(1,'tarena wuhan 7');

insert into fangfan_B values(2,'tarena wuhan 8');

insert into fangfan_B values(3,'tarena wuhan 9');

insert into fangfan_B values(4,'tarena wuhan 10');

 

insert into fangfan_A values(1,2,'fangfan',60);

 

insert into fangfan_A values(2,2,'xufeng',90);

insert into fangfan_A values(3,3,'wudong',90);

insert into fangfan_A values(4,4,'liumei',90);

/

 

select fangfan_A.student_id,fangfan_A.class_id,fangfan_B.classroom_name,fangfan_A.student_name,fangfan_A.score from fangfan_A,fangfan_B where fangfan_A.class_id=fangfan_B.classroom_id;