oracle_数据定义语言_ddl

DDL

数据库对象 

存储在用户(schema)下

表 存储的基础单元,行列组成

视图 一个或多个表的数据逻辑子集

序列 生成数字值

索引 提高查询的效率

同义词 对象的别名

建表

命名规则 : 字符开头/汉字也是字符,1-30,_$#,非保留字(加双引号后可以用),

create table (schema) tab (col number default 0,birthday date default sysdate);

alter table tab modify(col default 1);

数据类型 datatype

varchar2  < 4000 若定义变量 < 32767

char          < 2000

number(p,s) p数字个数,s小数个数,

s<0,则从整数开始

date

timestamp 精确到小数秒 with time zone

interval 存储 段时间

interval '100' year(3); 100年

INTERVAL '5-3' YEAR TO MONTH + INTERVAL '20' MONTH =
INTERVAL '6-11' YEAR TO MONTH
表示: 5年3个月 + 20个月 = 6年11个月

long 可变字符型 <2G

clob

约束 constraints

列级 空格分隔,表级 逗号分开

系统自动创建 sys_XXXX

not null 非空约束

只能定义在列级,只有非空约束不能在表级定义

create table a（id number not null）；

alter table a modify （id null） ；

alter table a modify （id constraint a_id_nnull not null） ；

unique 唯一约束

自动建立索引

create table a（id number  unique）；

select constraint_name from user_constraints where table_name = 'A';

alter table a drop constraint sys_XXX;删除约束；

alter table a add constraint a_id_u unique

(id); 添加约束；

primary key 主键约束 

一表一个，联合主键唯一切非空，

alter table a add constraint pk_a primary key(id);

alter drop primary key cascade;

foreign key 外键约束 

外键值只能是主键值的子集

create table b  as select * from a;

alter table b  add constraint pk_b foreign key(id) references a(id);

主表删除自动赋空

alter table b  add constraint pk_b foreign key(id) references a(id) on delete set null;

主表删除自动删除

alter table b  add constraint pk_b foreign key(id) references a(id) on delete cascade

check 检查约束

伪列不能使用 currval,nextval,level,rownum

alter table b  add constraint chk_b check (id > 0);

删除列

alter table a drop (id) cascade constraints;

查看约束

select constraint_name ,constraint_type 约束类型,column_name 约束关联列 from user_constraints where table_name = 'A';

表的操作

子查询建立表

create table subtable as select * from emp;

建立表结构 

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

只读表,不允许进行dml

alter table a read only/write;

删除表

drop table a;

show recyclebin;

flashback table a to before drop;

查看表信息

select table_name from user_tables;

查看表的列

select table_name,column_name from user_tab_columns where table_name = 'A';

表增,删,改列

alter table a add/drop/modify (id2 varchar2(10));

删除前打标签

alter table  a set unused column id2;

alter table a drop id2 ;

修改列名

alter table a rename column id2 to idvar;

view 视图

查询语句的别名;

视图不包含自己的存储空间,数据来源于基表;

简单视图 基表一个 dml操作不受限制

复杂视图 基表>1 dml操作受限制;

限制

删除数据行: group by 分组函数数,distinct,rownum;

更新数据行: group by 分组函数,distinct,rownum;表达式

插入数据行: group by 分组函数,distinct,rownum;表达式;非空约束未包含在视图中

子查询不能加 order by,

强制建立 force

关联where条件with check option

create or replace force view v_m as select * from a with check option with read only;

基表不存在,视图是无效的

select  object_name,status from user_object where object_name = 'V_M'

建立视图的权限

select * from session_privs;--create view

grant create view 头scott

建立视图

create or replace view v_m as select  empno,ename,sal,sal+nvl(comm,0) tsal from  emp;

create or replace view (empno,ename,sal, tsal) v_m as select  empno,ename,sal,sal+nvl(comm,0) tsal from  emp;

删除视图

drop view v_m;

索引 indexes

位图索引不能用dml操作

create index a_idx_id on a(id);

create index a_idx_id on a(id,id2);唯一值少的放到前面

create index a_idx_id on a(upper(id)); 函数索引

drop index a_idx_id;

适于:万中选一

列的值有很多;有null值;作为where/join条件;

返回2-4%;

查看索引

select  uic.index_name,uic.column_name,uic.column_position,ui.uniquencess  from  user_indexs ui , user_ind_column uic where ui.index_name = uic.index_name and uic.table_name = 'A';

序列 sequences

create sequences sq_t 

increament by 1

start with 0 alter时不能修改

maxvalue 999999

minvalue -999999

cycle

cache 20; 宕机,回滚可能出现数值不连续,速度增加 12c有独占序列

select sq_t.currval from dual;当前值

每次连接(session)后要查看currval,必须先调用一次select sq_t.nextval from dual;

insert into a (id) values(sq_t.nextval);下一个值

要查看nectval,必须先调用一次

查看 user_sequences

同义词 synonym

查看权限 

select * from session_privs;

grant create (public) synonym to user_XXX;

创建

create synonym syn_a for object_name;

select object_name from user_objects;

公有用户下的对象,对用户来说会访问自己的对象

create public synonym  p_s for user.tablea;

查看

desc all_synonym;

删除,只能删除自己建的

drop synonym p_s;