oracle数据库开发参考

sql语法

• 1.完整性约束条件

  • 列级完整性约束

  	NOT NULL
  

  • 表级列级均可用

  unique,check,primary key,foreign key
  

• 2.表操作

  • 创建表

  create [[global]temporory|table|schema.]tablename
  (
  	<列名> <数据类型> [列级完整性约束条件],
  	...,
  	[表级完整性约束条件]
  )
  [on commit {delete|preserve}rows]
  [organizition {heap|index|external...}] -- 表类型
  [partition by...] --分区及子分区信息
  [tablespace 表空间名]
  [logging|nologging] --是否保存重做日志
  [compress|nocompress] --是否压缩
  

  • 增加列

  //括号有多个的时候使用
  alter table [schema.]tablename add (<列名> <数据类型> [default...])
  

  • 修改列

  alter table [schema.]tablename modify (<列名> [数据类型，长度，默认值...] )
  

  • 删除列

  alter table [schema.]tablename drop (<列名>,<列名>) [cascade constraints]
  
  //为了应对不影响高并发环境，使用unused标记字段不可用
  alter table [schema.]tablename set unused (<列名>,<列名>) [cascade constraints]
  

• 3.索引

  • 创建索引

  create [unique]|[bitmap]index [schema.]index_name on [schema.]table_name([column1[ASC|DESC]...] | [express])
  [tablespace 表空间]
  [pctfree n1] --为insert预留的空间百分比
  [storage(initial n2)]
  [compress n3 | nocompress]
  [logging|nologging]
  [online]
  [compute statistics]
  [reverse | nosort]
  
  //demo
  create unique index tenantid_name_uk on hook(tenantId,name); --唯一索引
  create index tenantid_key on hook(tenantId);
  

  • 删除索引

  drop index index_name;
  

• 4.视图

  • 创建视图

  create [or replace | force] view view_name
  [(column1, column2...)]
  as select ... from ... where ...
  [with check option][constraint 约束名]
  [with read only]
  

  • 删除视图

  drop view view_name;
  

• 5.数据操作

  • 复制表插入记录

  create table table_name as select ....;
  

  • 使用视图

  //插入数据
  insert into v_view. --需要主表存在此信息
  //update
  update v_view set ...
  //删除
  delete from v_view
  

  • insert 数据

  insert into [user.]table [@db_link] (column1...) values (express...);
  //批量更新
  insert into table(...) select .... --select语法
  

  • update 数据

  update table_name set {column1=12, (column2[, column3]) = (select query)} [where ...]
  

  • 删除 数据

  delete from table [where ...]
  
  truncate table table_name [reuse storage | drop storage]
  

  • 授权

  grant <权限...> [on <对象类型> <对象>] [to <用户...>] [with grant option(可以分配给其他用户)]
  
  // demo
  grant select on table table_name to user1;
  grant all privileges on table table_name to user2;
  

  • 回收权限

  revoke <权限> [on <对象类型> 对象] from <用户...>
  

  • 内置函数

  //子符类函数
  ASCII(c1)	--c1是字符串，返回第一个字母的ASCII
  chr(i) --返回i对应的ascii，i是整数
  concat(c1,c2) --把c2连接到c1后面。
  initcap(c1)	--c1的每个单词的首字母大写，其余字母小写。
  instr(c1,c2,i,j) --返回从c1的第i个字符开始，c2出现第j次的位置号。
  length(c1) --返回c1的长度
  lower(c1) --返回c1的小写字母
  ltrim(c1,c2) --将c1最左边的字符去掉，使其第一个字符不在c2中。
  replace(c1,c2,c3) --用c3替换c2在c1的位置。
  substr(c1,i[,j]) --从第i个位置开始，返回j个长度的字符串。
  

  数学函数
  日期类函数
  
  转换类函数
  
  聚集类函数
  
  

表空间

• 在空间充足下的管理 （建议做好空间评估，减少资源浪费）
  1.通过数据字典监视

select * from user_free_space;
select * from dba_free_space;

2.增加数据文件

alter tablespace t_space add datafile '/data/info.dbf' size 2M;

• 空间不足的管理
  1.增加system表空间的数据文件大小

alter database orcl datafile '/data/info.dbf' resize 2M;

2.创建新表空间

create tablespace t_sp
datafile '/data/info.dbf' size 2M
default storage(
	inital 2M
	next 2M
	minextents 2
	maxextents 10
	pctincrease 20
)
online;

3.动态增加表空间

alter tablespace t_sp 
add datafile '/data/info.dbf' size 5M, '/data/info2.dbf' size 10M;

实战

• 1.创建表空间

create tablespace webapp
datafile '/data/oracleData' size 50M
default storage(
	inital 500K
	next 500K
	minextents 1
	maxextents unlimited
	pctincrease 0
)
online;

• 2.创建用户

create user 用户名 identified by 密码 default tablespace space_data（表空间名称） temporary tablespace space_temp（临时表空间名称）;

//授权
grant connect,dba to 用户名;

//修改限额
ALTER USER "用户名" QUOTA UNLIMITED ON SPACE_DATA(表空间名称);

//删除用户及数据
drop user user_name cascade;

• 3.创建表

create table 表名称 (
id  varchar2(50) primary key ,
name char(200） not null,
phone number(11) unique,
class carchar(10)， 
foreign key (name)
)
tablespace USERS ----表放在USERS表空间
pctfree 10 ----保留10%空间给更新该块数据使用
initrans 1 -----初始化事物槽的个数
maxtrans 255 ----最大事务槽的个数
storage ----存储参数
(
initial 64K ---区段一次扩展64k
next 1M
minextents 1 ---最小区段数
maxextents unlimited --最大区段无限制
)

1、添加主键约束（将stuNo作为主键）

alter table stuInfo add constraint PK_stuNo primary key (stuNo)

2、添加外键约束 (主表stuInfo和从表stuMarks建立关系，关联字段stuNo)
alter table stuInfo 
add constraint FK_stuNo foreign key(stuNo) references stuinfo(stuNo)

3、添加唯一约束（身份证号唯一）
alter table stuInfo
add constraint UQ_stuID unique(stuID)

4、添加默认约束（如果地址不填 默认为“地址不详”）
alter table stuInfo
add constraint DF_stuAddress default (‘地址不详’) for stuAddress

5、添加检查约束 （对年龄加以限定 15-40岁之间）
alter table stuInfo
add constraint CK_stuAge check (stuAge between 15 and 40)

6、添加表注释：学生信息表

comment on table STUINFO 
is '学生信息表';

7、添加列名称：学号

comment on column STUINFO.stuid 
is '学号';
comment on column STUINFO.stuname
is '学生姓名';

• 4.创建序列

CREATE SEQUENCE student_seq 	-- student_seq:序列名称
     INCREMENT BY 1 -- 每次增加1个
     START WITH 1 --从1开始计数
     NOMAXVALUE -- 不设置最大值
     NOCYCLE --直累加，不循环
     NOCACHE --不建立缓冲区