oracle表和对象基础维护笔记

  1. 云栖社区>
  2. 博客>
  3. 正文

oracle表和对象基础维护笔记

科技小能手 2017-11-12 16:04:00 浏览493
展开阅读全文

 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.第二种方法,是增加一个与被修改的列类型一样的列,之后将要修改列的数据复制到新增的列并置空要修改的列,之后修改数据类型,再从新增列将数据拷贝回来,该过程涉及两次数据复制,如果是数据量很多,会比较慢同时也会产生很多undoredo;优点是数据不会发生行迁移。

 

 

 

 

 

 

 

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


网友评论

登录后评论
0/500
评论
科技小能手
+ 关注