Oracle学习笔记（四）
外键
--主表(父表) 被参照的表
create table major_ning(
mid number(2) primary key,
mname varchar2(30)
);
--从表(子表) 参照别的表
create table student_ning(
id number(5) primary key,
name varchar2(20),
mid number(2),
constraint stuning_mid_fk foreign key (mid) references major_ning(mid)
);
insert into major_ning
values(1, 'computer');
insert into major_ning
values(2, 'music');
insert into major_ning
values(3, 'movie');
insert into student_ning
values(101,'peter',1);
insert into student_ning
values(102,'chris',3);
insert into student_ning
values(103,'king', 2);
select s.name, m.mname
from student_ning s join major_ning m
on s.mid = m.mid
and s.name = 'peter';
--试图增加一个学生记录,指定一个不存在的专业编码10
insert into student_ning
values(110,'dawson',10);
--报错:未找到父项关键字
--表示:在父表中没有编码为10的专业
ORA-02291: integrity constraint (OPENLAB.STUNING_MID_FK) violated - parent key not found
--试图删除major_ning表中的1专业,这个专业被某些学生选修(被参照),此时删除不成功,
delete from major_ning where mid = 1;
--报错:已找到子记录
ERROR at line 1:
ORA-02292: integrity constraint (NINGLJ.STUNING_MID_FK) violated
- child record found
--在建立子表时,外键约束增加设定条件
--on delete cascade
create table student_ning(
id number(5) primary key,
name varchar2(20),
mid number(2),
constraint stuning_mid_fk foreign key (mid) references major_ning(mid) on delete cascade);
-- on delete set null
create table student_ning(
id number(5) primary key,
name varchar2(20),
mid number(2),
constraint stuning_mid_fk foreign key (mid) references major_ning(mid) on delete set null);
select
create / drop /
insert / delete / update
commit / rollback
--insert
insert into dept(deptno, dname, loc)
values(80, 'market', 'beijing');
create table mydept(
id number(2) primary key,
name varchar2(20),
location varchar2(30));
insert into mydept(id, name, location)
select deptno, dname, loc from dept;
create table emp_ning(
id number(4) primary key,
name varchar2(20),
deptno number(20));
--新增一条记录
insert into emp_ning
values(1234,'peter',10);
--利用子查询,新增多条记录
insert into emp_ning(id,name, deptno)
select empno, ename, deptno
from ninglj.emp
where deptno = 10;
--复制表:包括结构和数据
create table emp_dup
as
select * from ninglj.emp;
--复制表结构,不复制数据.
create table emp_bak
as
select * from ninglj.emp
where empno > 9999;
--update
update emp_ning set sal = 1000
where empno = 7369;
update emp_ning set sal = 1500,
deptno = 20, job = 'salesman'
where empno = 7369;
--delete
delete emp_ning where deptno = 10;
DML: insert / update/ delete
事务语句
commit / rollback / savepoint
create table temp_ning(
id number primary key);
insert into temp_ning values(1);
savepoint A;
insert into temp_ning values(2);
savepoint B;
insert into temp_ning values(3);
savepoint C;
insert into temp_ning values(4);
rollback to B;
rollback to C;--检查结果
rollback to A;
commit;
--
数据库的主要对象
表 table
视图 view
索引 index
序列 sequ