ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 创建表

创建表

原创 Linux操作系统 作者:hjianping 时间:2011-04-24 12:42:30 0 删除 编辑

1、创建表及主键
create table dept
 (deptno number(2) primary key,
  dname varchar2(10),
  loc varchar2(10)
 );

create table dept
 (deptno number(2),
  dname varchar2(10),
  loc varchar2(10),
  constraint pk_dept primary key (deptno)
 );

2、创建表及外键
create table emp
 (empno number(4),
  ename varchar2(10),
  job varchar2(10),
  sal number(7,2) check (sal between 1800 and 10000),
  deptno number(2),
  constraint pk_emp primary key(empno),
  constraint fk_deptno foreign key(deptno) references dept(deptno) on delete cascade
 );

create table emp
 (empno number(4),
  ename varchar2(10),
  job varchar2(10),
  sal number(7,2) check (sal between 1800 and 10000),
  deptno number(2)
  constraint fk_deptno references dept(deptno) on delete cascade,
  constraint pk_emp primary key(empno)
 );

3、创建外键
alter table article add constraint pk_art primary key(art_no);
alter table art_grp add constraint pk_art_grp primary key( art_grp_no);
alter table article add constraint fk_art
 foreign key(art_grp_no) references art_grp(art_grp_no)

alter table emp add constraint fk_deptno
 foreign key (deptno) references dept(deptno);

4、删除外键
alter table emp drop constraint fk_deptno;

5、创建索引(唯一性约束)
create unique index indx_emp on emp(job);

alter table emp add constraint index_emp unique (ename);

6、修改表属性
alter table emp add constraint check_sal check (sal >= 0);
alter table TBS_MONITOR add systime date;


创建用户
create user hjp identified by password
default tablespace hjp
temporary tablespace temp;

grant connect to hjp;
grant resource to hjp;

删除用户
drop user XXXX cascade;
drop tablespace XXXX INCLUDING CONTENTS;

删除用户下所有表

select 'drop table ' || table_name ||';'||chr(13)||chr(10) from user_tables; --delete tables
select 'drop view ' || view_name||';'||chr(13)||chr(10) from user_views; --delete views
select 'drop sequence ' || sequence_name||';'||chr(13)||chr(10) from user_sequences;--delete seqs
select 'drop function ' || object_name||';'||chr(13)||chr(10) from user_objects where object_type='FUNCTION';--delete functions
select 'drop procedure ' || object_name||';'||chr(13)||chr(10) from user_objects where object_type='PROCEDURE';--DELETE PROCEDURE
select 'drop package ' || object_name||';'||chr(13)||chr(10) from user_objects where object_type='PACKAGE';--delete pags

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/17012874/viewspace-693285/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-04-24

  • 博文量
    80
  • 访问量
    72862