一 oracle表和对象基础维护笔记
1.1 常见概念
1.2 创建表
1.3 表常见字段
1.4 增加或删除字段
1.5 更新字段
1.6 重命名表
1.7 改变表存储表空间和存储参数
1.8 删除表
1.9 表注释
1.10 分区表的管理
1.11 常用数据字典
二 约束
2.1 非空约束
2.2 主键约束
2.3 唯一性约束
2.4 外键约束
2.5 约束管理
三 索引
3.2 创建索引
3.3 改变索引存储参数
3.4 重建索引
3.5 索引碎片整理
3.6 删除索引
3.7 数据字典
四 视图
4.1 建立视图
4.2 视图授权
4.3 删除视图
五 同义词
5.1 创建同义词
5.2删除同义词
六 序列
6.1 建立序列
6.2 删除序列
1.1 常见概念
表命名规范：不能超过30个字，只能有数字，字母,_，#组成，$
1.2 创建表
create table [schema.]table (column datatype [default expr]);
使用子查询创建表
create table table
[(column,column…)]
as subquery;
---创建表
create table cw1(
name varchar2(25) not null,
id number
)
insert into cw1(name,id) values('cw',1);
create table cw2
as select * from cw1;
1.3 表常见字段
varchar2(size) 最大4000字节
char(size) 最大2000字节
number(p[,s]) p总长度，s小数位
date
long 最大可到2G
CLOB 最大可以到4G
RAW and LONG RAW 二进制数据，最大2000字节，2G
BLOB 二进制数据，最大可达到4G
BFILE 存储外部文件的二进制数据，最大可达到4G
ROWID 行地址
create table cw3(
name varchar2(10),
sex char(4),
deptid number(10),
create_date date,
card_id long,
picture blob,
file_id bfile)
--插入数据
insert into cw2(name,id,age,cardid) values('cw1',3,19,'12345');
insert into cw2(name,id,age,cardid) values('cw1',4,20,'242345');
insert into cw2(name,id,age,cardid) values('cw1',5,21,'123322225');
insert into cw2(name,id,age,cardid) values('cw1',6,22,'1242234545');
insert into cw2(name,id,age,cardid) values('cw1',7,23,'1252342345');
insert into cw2(name,id,age,cardid) values('cw1',8,24,'124234245');
insert into cw2(name,id,age,cardid) values('cw1',9,25,'133223445');
insert into cw2(name,id,age,cardid) values('cw1',10,26,'32123345');
1.4 增加或删除字段
alter table employees add(age number(2));
--添加字段
alter table cw2 add(age number(2));
alter table cw2 add(cardid varchar(10));
--删除字段
alter table cw2 drop(cardid );
1.5 更新字段
alter table table_name modify column_name type;
--更改表字段：
alter table cw2 modify(age char(10));
---如果表里面存在数据，需要修改为其它类型数据，会报错
SQL> alter table cw2 modify(age char(10));
alter table cw2 modify(age char(10))
ORA-01439: column to be modified must be empty to change datatype
SQL>
---更改同类数据类型正常
SQL> alter table cw2 modify(age number(10));
Table altered
SQL>
注意：如果是需要修改数据字段类型，需要先处理表里面数据，然后再更改类型。
1.这种方法能满足需求，因新增字段默认添加到表末尾，有可能发生行迁移,对应用程序会产生影响
2.第二种方法，是增加一个与被修改的列类型一样的列，之后将要修改列的数据复制到新增的列并置空要修改的列，之后修改数据类型，再从新增列将数据拷贝回来，该过程涉及两次数据复制，如果是数据量很多，会比较慢同时也会产生很多undo和redo；优点是数据不会发生行迁移。
1.6 重命名表
alter table XXX RENAME to xxxxx;
----重命名表
SQL> alter table cw2 rename to cw4;
Table altered
SQL>
---如果带schema，那么会报错，需要去掉后面的schema
SQL> alter table system.cw4 rename to system.cw2;
alter table system.cw4 rename to system.cw2
ORA-14047: ALTER TABLE|INDEX RENAME may not be combined with other operations
SQL>
---带schema更改如下：
SQL> alter table system.cw4 rename to cw2;
Table altered
SQL>
1.7 改变表存储表空间和存储参数
1.8 删除表
删除表：drop table_name;
SQL> drop table system.cw2;
Table dropped
SQL>
删除表数据：
truncate
delete
区别：1.truncate无法rollback
2.truncate 不能触发任何delete触发器
-----delete删除数据
SQL> select * from cw3;
NAME ID AGE CARDID
-------------------- ------ ----- ----------
cw1 3 19 12345
cw1 4 20 242345
cw1 5 21 123322225
cw1 6 22 1242234545
cw1 7 23 1252342345
cw1 8 24 124234245
cw1 9 25 133223445
cw1 10 26 32123345
8 rows selected
SQL> delete from cw3;
8 rows deleted
SQL> select * from cw3;
NAME ID AGE CARDID
-------------------- ------ ----- ----------
SQL> rollback;
Rollback complete
SQL> select * from cw3;
NAME ID AGE CARDID
-------------------- ------ ----- ----------
cw1 3 19 12345
cw1 4 20 242345
cw1 5 21 123322225
cw1 6 22 1242234545
cw1 7 23 1252342345
cw1 8 24 124234245
cw1 9 25 133223445
cw1 10 26 32123345
8 rows selected
SQL>
----delete
----truncate删除数据
SQL> truncate table cw3;
Table truncated
SQL> rollback;
Rollback complete
SQL> select * from cw3;
NAME ID AGE CARDID
-------------------- ------ ----- ----------
SQL>
----truncate
1.9 表注释
comment on table employees IS '测试';
---添加表测试记录
SQL> comment on table cw3 is '测试';
Comment added
SQL> desc cw3;
Name Type Nullable Default Comments
------ ------------ -------- ------- --------
NAME VARCHAR2(20) Y
ID NUMBER(5) Y
AGE CHAR(5) Y
CARDID VARCHAR2(10) Y
SQL> select * from dba_tab_comments where table_name='CW3';
OWNER TABLE_NAME TABLE_TYPE COMMENTS
------------------------------ ------------------------------ ----------- --------------------------------------------------------------------------------
SYSTEM CW3 TABLE 测试
SQL>
1.10 分区表的管理
分区表的有点：
分区表的分区方法：
范围分区：
hash分区
列表分区
复合分区（范围+hash）（范围+列表）
create table cw_part1(
name varchar(20),
id number(5),
age char(5),
cardid varchar(10))
partition by range(age)
(partition age_1 values less than (22),
partition age_2 values less than(24),
partition age_3 values less than(26))
as select name,id,age,cardid from cw2;
----分区表
SQL> select * from cw_part partition(age_3);
NAME ID AGE CARDID
-------------------- ------ ----- ----------
SQL> insert into cw_part(name,id,age,cardid) values('cw2',11,25,'232432');
1 row inserted
SQL> select * from cw_part partition(age_3);
NAME ID AGE CARDID
-------------------- ------ ----- ----------
cw2 11 25 232432
SQL> select * from cw_part partition(age_1);
NAME ID AGE CARDID
-------------------- ------ ----- ----------
cw2 11 20 232432
SQL> select * from cw_part partition(age_2);
NAME ID AGE CARDID
-------------------- ------ ----- ----------
SQL> select * from cw_part partition(age_3);
NAME ID AGE CARDID
-------------------- ------ ----- ----------
cw2 11 25 232432
----分区表
1.11 常用数据字典
all_col_comments
user_col_comments
all_tab_comments
user_tab_comments
二 约束
2.1 非空约束
not null
--添加非空
SQL> alter table cw2 add(key varchar(2) not null);
alter table cw2 add(key varchar(2) not null)
ORA-01758: table must be empty to add mandatory (NOT NULL) column
SQL> select * from cw1;
NAME ID
------------------------- ----------
cw 1
SQL> truncate cw1;
truncate cw1
ORA-03290: Invalid truncate command - missing CLUSTER or TABLE keyword
SQL> truncate table cw1;
Table truncated
---如果表为空，那么可以添加成功
SQL> alter table cw1 add(key varchar(2) not null);
Table altered
SQL>
---
2.2 主键约束
primary key
create table cw(name varchar constraint pk_name primary key,id number);
----添加主键
SQL> alter table cw2 modify(id number(5) primary key);
Table altered
SQL> select * from cw2;
NAME ID AGE CARDID
-------------------- ------ ----- ----------
cw1 3 19 12345
cw1 4 20 242345
cw1 5 21 123322225
cw1 6 22 1242234545
cw1 7 23 1252342345
cw1 8 24 124234245
cw1 9 25 133223445
cw1 10 26 32123345
8 rows selected
SQL> insert into cw2(id) values(5);
insert into cw2(id) values(5)
ORA-00001: unique constraint (SYSTEM.SYS_C006975) violated
SQL>
----
2.3 唯一性约束
unique
create table cw(name varchar 2(20),
id number
constraint unique_name unique(name)
);
-----唯一索引
SQL> create unique index unique_age on cw2(age);
Index created
SQL> insert into cw2(id,age) values(11,20);
insert into cw2(id,age) values(11,20)
ORA-00001: unique constraint (SYSTEM.UNIQUE_AGE) violated
SQL>
----
2.4 外键约束
foreign key
create table cw(
id number,
name varchar2(20)
constraint fk_cw foreign key(id) references dept(id)
);
2.5 约束管理
修改
alter table cw drop constraint unique_name;
alter table cw add constraint unique_name unique(name);
停止启用
alter table cw disable constraint unique_name;
alter table cw enable constraint constraint_name;
-----约束停止
-----
三 索引
3.1 索引概述
索引的有点：
加快查询，减少Io操作，消除磁盘排序
索引种类：
唯一索引
位图索引
散列索引
函数索引
3.2 创建索引
创建索引时，需要制定索引参数
create index index_name on table_name(field_name)
tablespace tablespace_name
pctfree 5
initrans 2
maxtrans 255
storage
(
minextents 1
maxextents 16382
pctincrease 0
);
tablespace 表空间 --指定建立对象的表空间 pctfree 5
--块预留5%空间用于以后数据更新
initrans 2 --初始化事务槽数
maxtrans 255 --最大事务槽数
storage--下面是存储参数
initial 64K --初始化扩展区为64k next 1M
--下次扩展1m
minextents 1 --最小区数为1
maxextents 16382 --最大区数无限制 );
创建唯一索引
create unique index dept_unique_idx on dept(dept_no) tablespace idx_data;
创建位图索引：
create bitmap index idx_bitm on cw(id) tablespace idx_data;
创建函数索引：
create index idx_fun on emp (upper(ename)) tablespace idx_data;
3.3 改变索引存储参数
alter index unique_name
pctfree 30
storage(next 200k pctincrease 20);
3.4 重建索引
alter index unique_name rebuild tablespace indx;
----重建索引---
SQL> alter index unique_age rebuild;
Index altered
SQL>
-----
3.5 索引碎片整理
alter index cw_id_idx coalesce;
---碎片整理
SQL> alter index unique_age coalesce;
Index altered
SQL>
---
3.6 删除索引
drop index hr.deptartments_name_idx;
需要注意，如果有外键，是无法删除的，需要先禁止外键，然后再删除。
drop table cw cascade constraints;
truncate cw stores;
alter table cw disable constraint fk_cw;
------
SQL> drop index unique_age;
Index dropped
SQL>
---
3.7 数据字典
dba_indexes
dba_ind_columns
dba_ind_expressions
v$object_usage
四 视图
4.1 建立视图
create view temp_cw as select * from cw;
可以创建制度
create view temp_cw as select * from cw
with read only;
4.2 视图授权
grante create view to chenwei;
4.3 删除视图
drop view cw;
五 同义词
同义词优点：
简化SQL语句
隐藏对象的名称和所有者
提供对象的公共访问
分为：公有同义词，私有同义词
5.1 创建同义词
create public synonym table_name from chenwei.cw;
5.2删除同义词
drop public synonym chenwei.cw;
六 序列
6.1 建立序列
create sequence seq_cw
increment by 10
start with 10
minvalue 10 nomaxvalue
查询序列：
select seq_cw.nextval from dual;
访问序列：
当前值： CURRVAL
下一个：NEXTVAL
6.2 删除序列
drop sequence seq_cw
6.3 修改序列
alter sequence cw_seq maxvalue 5000 cycle;
本文转自 woshiwei201 51CTO博客，原文链接:http://blog.51cto.com/chenwei/1695746
