Oracle学习笔记（四）
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
序列 sequence
约束条件
同义词
....
--视图 View
--创建视图
create or replace view v_emp_ning
as
select ename, sal from ninglj.emp;
--和查询表一样使用
select * from v_emp_ning;
desc v_emp_ning
--删除视图
drop view v_emp_ning;
--数据字典
--用户名下的数据表:user_tables;只读
select count(*) from user_tables;
select table_name from user_tables
where rownum < 20; --查前19个表名
select * from user_table
where table_name = 'EMP';--查emp表的信息
--和视图相关的数据字典user_views;
--查看视图总数
select count(*) from user_views;
--查看数据字典的结构
desc user_views
select text from user_views
where view_name = 'V_EMP_NING';
--创建复杂视图
create or replace view emp_sum
as
select deptno, sum(sal) sum_sal
from ninglj.emp
group by deptno;
数据字典:
user_tables:当前用户名下所有的表
all_tables: 当前用户能访问的所有的表
自己的表+别的用户允许自己访问的表.
dba_tables:数据库下所有的表
select count(*) from user_objects;
select count(*) from user_tables;
select count(*) from all_objects;
select count(*) from all_tables;
--索引
创建索引的方式:自动/手动
当创建唯一/pk约束条件时,索引自动创建
create index emp_ename_idx on emp(ename);
