一 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;
