一 oracle表和对象基础维护笔记
目前创新互联公司已为上1000+的企业提供了网站建设、域名、虚拟空间、成都网站托管、企业网站设计、平鲁网站维护等服务，公司将坚持客户导向、应用为本的策略，正道将秉承"和谐、参与、激情"的文化，与客户和合作伙伴齐心协力一起成长，共同发展。
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;
