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