oracle学习笔记(四)

primary key约束：

主键约束的定义：

第一种定义形式：列级约束
create table test(c number primary key ); 列级约束

第二种定义形式：表级约束
create table test(c number , primary key(c) ) ; 表级约束

create table test( c1 number constraints pkc1 primary key );
此约束有名字: pkc1

create table test(c number , c2 number , primary key (c ,c1) ) ; 用表级约束可以实现联合主键

foregin key (fk) 外键约束:

(先定义父表，再定义子表）

carete table parent(c1 number primary key );

create table child (c number primary key , c2 number references parent(c1));

或表级约束定义:
create table child( c number primary key , c2 number , foreign key(c2) references parent(c1));

如果两个字段都为唯一且非空,这时可以定义成UK+NOT NULL

(PK或UK)一对多(FK)
(PK+UK)一对一(FK) 或 (PK)一对一(PK)

多对对多关系,一般都通过一张中间表来分解成两个一对多的表

非空约束是唯一一个可以用desc看到的约束

check 约束

create table test(c1 number primary key); 设置主键
create table test(c1 number constraints test_c1 primary key); 定义约束名,默认约束名为SYS_ 在列后面定义约束称为列级约束
create table test(c1 number primary key(c1)); 所有列定义完后再定义约束称为表级约束(能定义联合主键)
cretae table test(c1 number,c2 number,priary key(c1,c2)); 定义联合主键
create table test(c1 number primary key,c2 number unique); unique 唯一约束
create table test(c1 number primary key,c2 number not null unique); 一个字段上可以加多个约束
create table test(c1 number check(c1>100)); 约束c1的值大于100 ，列级约束
create table test(c1 number,check(c1>100)); 表级约束

create table child(c1 number primary key); 先要定义父表
create table child(c1 number primary key, c2 number references parent(c1)); 然后定义子表 references parent定义外键
create table child(c1 number primary key, c2 number references parent(c1) on delete cascade); on delete cascade为级联删除,删除父表时子表也被删除
create table child(c1 number primary key, c2 number references parent(c1) on delete set null); on delete set null删除后将外键置空
create table child (c1 number primary key, c2 number,foreignkey(c2) references parent(c1));

DML操作

online transaction procession OLTP 联机事务处理

1、insert操作，插入记录

insert into 表名 values(值1，值2，......);
注意这种方法插入记录时，要对所有字段进行插入，没有非空约束时，又不想插入值时，要用空值替代，并且要按照字段的顺序插值（要清楚表结构），且要注意数据类型一致。

insert into 表名(字段名1，字段名2，.....) values(值1，值2，......);
这种方法可以对指定的字段进行插入，不想插值的就可以不写，前提是该字段没有非空约束。

例：insert into student value(1,'xxx','xxx');
insert into student(id,name,address) value(1,'xxx','xxx');

注意：有空值的话:
隐式插入
INSERT INTO s_dept (id, name) VALUES (12, 'MIS');
不往想为空的字段中插数据，系统默认为NULL
显示插入
INSERT INTO s_dept VALUES (13, 'Administration', NULL);

select * from s_emp where 1=2; 这样选不出纪录，方便察看表结构

2、update修改操作

update table 表名 set 字段名1=数据1或表达式1， 字段名2=数据2或表达式2
[where ....=....];

例：update shenfenzhen set num=99 where sid=2;

3、delete删除操作

delete from 表名 [where ...=...];

例：update shenfenzhen set num=99 where sid=2;

用delete 删除一张大表会花很长的时间。
用delete操作删除的记录可以通过 rollback命令回滚操作，会恢复delete操作删除的数据。
delete操作不会释放表所占用的空间，delete不适合删除记录多的大表。
delete操作会占用大量的系统资源。

事务

OLTP （ online transaction procession ） 联机事务处理
数据库中操作的应是事务，而不是DML语句
事务是有生命周期的，commit；事务结束
系统中充满了并发的transation,每个连接是一个session，每个操作都是一个transaction
DDL、DCL语句是自动提交的
sqlplus正常退出（exit），系统自动提交
上个事务的结束就是下个事务的开始
事务保证数据的一致性，保证原子操作的安全
一个没有结束的事务，叫做活动的事务 (active transaction),活动的事务中修改的数据，只有本会话才能看见。
read committed，只可以读取已经做提交操作的数据，本会话可以看到自己的所作的没有提交的操作。

在活动事务中，当多个用户同时对同一张表进行操作时，会对表加上表级共享锁，当用户对操作该表某一条记录进行操作时会对该条记录加上行级排它锁，
只允许一个用户对该条记录进行DML操作，只有提交操作commit;或回滚操作rollback;时，才可让其他用户操作对该记录进行DML操作，也就是释放了该条
记录的行级排它锁。如果没有提交操作或回滚操作，那么该用户就不能对该条记录加锁，该用户的DML操作就会进入等待状态，但是在对表作drop操作（DDL操作）时，
如果还有用户在操作该表，也就是没有释放表级共享锁，就会直接报错。

事务越大，就会消耗更多的资源，并长时间持有事务会造成无法进行其他的操作，事物提交太频繁的话，会对I/O造成很大的负担，所以要合理确定事务的大小。
commit;提交操作，事物的结束
rollback;回滚操作，会将先前的活动事务中的操作（DML操作）的结果进行回滚，撤销全部操作，恢复成事务开始时的数据，也就是恢复成事务开始时的状态。

alter table命令

alter table 命令用于修改表的结构(这些命令不会经常用)：

增加字段：
alter table 表名　add(字段字，字段类型)

删除字段：
alter tbale 表名 drop column 字段; (8i 以后才支持)

给列改名:9.2.0才支持
alter table 表名 rename column 旧字段名 to 新字段名;

修改字段
alter table 表名 modify( 字段，类型)
(此时应注意的问题，更改时要看具体值情况之间的转达换，　改为字符类型时，必须要为空)

not null约束是使用alter table .. modify (.. not null)，来加上的。

alter table test modify(c11 null);

增加约束：
alter table 表名 add constraint [约束名] 约束(字段);
只能够增加表级约束。

解除约束：(删除约束)

alter table 表名 drop 约束;
alter table 表名 drop primary key;
（对于主键约束可以直接用此方法，因为一张表中只有一个主键约束名, 注意如果主键此时还有其它表引用时删除主键时会出错）

alter table father drop primary key cascade;
(如果有子表引用主键时，要用此语法来删除主键,这时子表还存在只是子表中的外键约束被及联删除了）

数据字典

数据字典是由系统维护的，包含的数据库的信息

数据字典示图
user_XXXXX 用户示图
all_XXXXX 所有示图
dba_XXXXX 数据库中所有示图
v$_XXXXX 动态性能示图

dict 表示数据字典的数据字典。

user_constraints 用户的表中约束的表
其中有constraint_name字段存放的是约束名，r_constraint_name字段表示外键引用pk或uk的名字
这两个字段之间有自连接的关系，也就是约束名和外键约束名之间的自连接。

user_cons_columns表，是用户的列级约束表

U 代表UK,R (references)代表FK,C 代表check和NOT NULL，P代表PK

找出s_emp表的主键约束
select c.constraint_name,c.constraint_type,cc.column_name
from user_constraints c,user_cons_columns cc
where c.constraint_name=cc.constraint_name and c.table_name=cc.table_name and c.table_name='S_EMP' and c.constraint_type='P';

练习： 列出每一个表的外键的定义，主表表名，主表字段名，子表表名，子表字段名 （画出E-R图）

alter table 表名 drop constraint 约束名;
(怎样取一个约束名：
a、人为的违反约束规定根据错误信息获取!
b、查询示图获取约束名!)

使约束失效或者生效

alter table 表名 disable from primary key; (相当于把一个表的主键禁用)

alter table 表名 enable primary key;
（enable 时会自动去检查表的记录是不是符合要求,如果有脏数据时必须要先删除脏数据才可以 enable）