Oracle表的创建与管理

--表的创建与管理


--表的创建


create table member(
  mid number(5),
  name varchar2(50) default '无名氏',
  age number(3),
  birthday date default sysdate,
  note clob
);


select * from tab;


desc member;


insert into member(mid,name,age,birthday,note) 
values (1,'陈飞鹏',30,to_date('1992-09-12','yyyy-mm-dd'),'总公司活动提倡者');
insert into member(mid,name,age,birthday,note)
values (2,'董鸣楠',29,to_date('1980-08-13','yyyy-mm-dd'),'积极响应者');


select * from member;


--表的复制


drop table myemp;


create table myemp as select * from emp;


select * from myemp;


create table myemp10
as
select * from emp where deptno=10;


select * from myemp10;


create table employee1
as
select * from emp where 1=2;


select * from employee1;


desc employee1;


create table department1
  as
select d.deptno,d.dname,d.loc,
  count(e.empno) count,sum(e.sal+nvl(e.comm,0)) sum,
  round(avg(e.sal+nvl(e.comm,0)),2) avg,max(e.sal) max,min(e.sal) min
from emp e,dept d
where e.deptno(+)=d.deptno
group by d.deptno,d.dname,d.loc
order by d.deptno;


--数据表的重命名


select * from department1;


select * from user_tables;


rename member to mldnuser;


select * from user_tables;


--截断表 desc


truncate table mldnuser;


select * from mldnuser;


rollback;


drop table mldnuser;
drop table myemp;
drop table myemp10;
drop table employee1;
drop table department1;




select * from tab;


flashback table myemp to before drop;


select object_name,original_name,operation,type from recyclebin;


select * from myemp10;


drop table myemp purge;


drop table myemp10 purge;


purge table myemp;


purge recyclebin;


--修改表结构


drop table member purge;


create table member(
  mid number,
  name varchar2(50) default'无名氏'
);


insert into member(mid,name)values(1,'李兴华');
insert into member(mid,name)values(2,'董鸣楠');
insert into member(mid,name)values(3,'王月清');


commit;


select * from member;


alter table member add(age number(3));
alter table member add(sex varchar(10) default'男');
alter table member add(photo varchar2(100) default'nophoto.jpg');


desc member;


alter table member modify(name varchar(30));
alter table member modify(sex varchar2(3) default'女');


alter table member drop column photo;
alter table member drop column age;


alter table member set unused(sex);
alter table member set unused column name;


select * from member;


alter table member drop unused columns;




--添加注释


drop table member purge;


create table member(
  mid number,
  name varchar2(50) default'无名氏',
  age number(3),
  birthday date
);


commit;


select * from user_tab_comments where table_name='MEMBER';


comment on table member is '用于记录参加活动的成员信息';


select * from user_col_comments where table_name='MEMBER';


comment on column member.mid is '参加活动的成员编号';