--表的创建与管理
--表的创建
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 '参加活动的成员编号';
--表的创建
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 '参加活动的成员编号';