oracle_DDL

表 create table 表名(字段1 类型, 字段2 类型, …)

类型有varchar2、char、number、date、long

       constraint约束(主键、外键、唯一、非空、check)：字段级约束、表级约束

       create table class

(

              cidnumber(4),

              cnamevarchar2(20) not null,

              constraintclass_cid_pk primary key(cid)

);

create table stu

(

              stunonumber(9) primary key,

              namevarchar2(10) constraint stu_name_not_null not null,

              sexnumber(1),

              classnumber(4),

              sdatedate default sysdate,

              telchar(11) unique,

              agenumber(3),

              constraintstu_class_fk foreign key(class) references class(cid)

);

       create table dept2 as select * from dept; 相当于将表dept备份

       alter 修改表结构

              alter table dept2 add(tell number(11));增加字段

      alter table dept2 modify(tell varchar2(11)); 修改字段

              alter table dept2 drop(tell); 删除字段

              alter table dept2 add constraint dept2_pk_deptno primarykey(deptno);增加约束

              alter table dept2 dropconstraint dept2_pk_deptno;删除约束

       drop 删除表 drop table dept2;

索引 相当于字典里的索引，当某1个或几个字段访问次数比较多时可在该字段上建立索引

create index idx_dept2_dname on dept2 (dname);

drop index idx_dept2_dname;

视图 一个子查询（虚表），一般以v$开头 简化查询、维护麻烦、保护私有数据，视图也可更新表数据，但一般不这么用

       例如：求平均薪水的等级最低的部门名称

create view v$_dept_avg_sal_info as

select deptno, grade,avg_sal from

  (selectdeptno,avg(sal) avg_sal from emp group by deptno) t

join salgrade s on (t.avg_sal between s.losal ands.hisal);

select dname, t1.deptno, grade, avg_sal from

v$_dept_avg_sal_info t1 join dept on(t1.deptno =dept.deptno)

where t1.grade =

(select min(grade) from v$_dept_avg_sal_info);

删除视图  drop view v$_dept_avg_sal_info;

序列 sequence 产生唯一、不间断的序列，用来作主键

       create sequence seq; 自动递增

       create sequence sequ start with 1 increment by 1;

select seq.nextval from dual;

insert into dept2 values(sequ.nextval, 'A', 'Beijing');

删除序列drop sequence seq;