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