1.约束
约束是数据库能够实现业务规则以及保证数据遵循ER模型的一种手段。
约束的语法
列级约束：只能引用一个列，表中可以有多个列级约束
表级约束：引用一个或多个列，通常用来定义主键
追加约束:建表后，再通过alter table追加约束
select * from user_constraints；
select * from user_cons_columns；
五种约束的语法
非空约束
列级定义
create table student(no int not null,name varchar(40));
追加非空约束
alter table emp1 modify ename not null;
select * from user_constraints
alter table emp1 modify ename null
alter table emp1 modify ename constraint c_emp1_ename not null;
select * from user_constraints
唯一性约束
列级定义
drop table student;
create table student(no int unique,name varchar(40));
insert into student
values(null,'zzl');
insert into student
values(null,'z');
insert into student
values(1,'zzl');
insert into student
values(1,'z');
表级定义
drop table student;
create table student(no int ,name varchar(40),constraint c_uno unique(no));
追加定义
drop table student;
create table student(no int ,name varchar(40));
alter table student add constraint c_uno unique(no);
主键约束
每个表只能建立一个主键约束 primary key = unique + not null
主键列上需要索引，如果该列没有索引，则自动创建unique索引，
主键约束和唯一约束不能同时建立在一个列上
主键约束的六种写法；
列级定义
drop table student
create table student(no int primary key,name varchar(40));
drop table student
create table student(no int constraint pk_student primary key,name varchar(40));
表级定义
drop table student
create table student(no int,name varchar(40),primary key (no));
drop table student
create table student(no int,name varchar(40),constraint pk_studnet primary key (no));
追加定义
drop table student;
create table student(no int,name varchar(40));
alter table student add primary key (no);
drop table student;
create table student(no int,name varchar(40));
alter table student add constraint pk_student primary key (no);
主键和索引关联的问题
drop table student
create table student(no int ,name varchar(40));
create index index_student_no on student(no);
alter table student add constraint pk_no primary key (no);
drop table student;
create table student(no int ,name varchar(40));
create index index_student_no on student(no);
alter table student add constraint pk_no primary key (no) using index index_student_no;
alter table student drop constraint pk_no;
select * from user_indexes where table_name ='STUDENT';
create table student(no int,name varchar(40),constraint pk_studnet primary key (no) using index(create index index_student_no on student(no)
));
外键约束
作用，是为了和同一个表或其他表的主键建立连接关系，外键值必须和父表中的值匹配或者为空
外键约束和unique约束都可以有空值
外键需要参考主键的约束，但也可以参考唯一键约束
外键和主键一般分别在两个不同的表中，但也可以同处在一个表中
drop table emp1;
create table emp1 as select * from emp;
create table dept1 as select * from dept;
列级定义：
alter table dept1 add constraint pk_dept1 primary key (deptno);
create table emp2 (empno int ,deptno int references dept1(deptno),deptno2 int);
表级定义
create table emp3(empno int ,deptno int,foreign key (deptno) references dept1(deptno));
追加定义
alter table emp1 add constraint fk_emp1 foreign key(deptno) references dept1(deptno);
insert into emp3 values(1,99);
insert into emp3 values(1,null);
delete from dept1 where deptno =10;
alter table emp1 drop constraint fk_emp1;
alter table emp1 add constraint fk_emp1 foreign key(deptno) references dept1(deptno) on delete cascade;
delete from dept1 where deptno =10;
select * from emp1;
alter table emp1 drop constraint fk_emp1;
alter table emp1 add constraint fk_emp1 foreign key(deptno) references dept1(deptno) on delete set null;
delete from dept1 where deptno =10;
select * from emp1;
check 约束
列级定义
create table emp4(empno int ,sal int check(sal>0),comm int)
表级定义
create table emp5(empno int ,sal int,comm int， check(sal>5000))
追加定义
alter table emp5 add constraint e_no_ck check(empno is not null)
insert into emp5
values(null,1,1);
check约束中的表达式中不能使用变量日期函数
alter table emp1 add constraint emp_chk_date check (hiredate < sysdate);
alter table emp1 add constraint emp_chk_date check (hiredate < to_date('2016.08.16','yyyy.mm.dd'))
级联约束
drop table test;
create table test(
pk number primary key,
fk number,
col1 number,
col2 number,
constraint fk_constraint foreign key (fk) references test,
constraint ck1 check(pk >0 and col1 >0),
constraint ck2 check(col2 > 0)
)
alter table test drop column col2;
alter table test drop column fk;
alter table test drop column pk;
alter table test drop column col1;
alter table test drop column pk cascade constraint//级联
alter table test drop column col1 cascade constraint
约束的四种状态
enable validate :无法输入违反约束的行，而且表中所有行都要符合约束
enable novalidate：表中可以存在不符合约束的状态，但对新加入数据必须符合约束条件
disable novalidate：可以输入任何数据，表中或已存在不符合约束条件的数据
disable validate：不能对表进行插入，更新，删除等操作，相当于对整个表的read only 设定。
drop table emp1;
create table emp1 as select * from emp;
update emp1 set empno =NULL where empno =7900
select * from emp1
alter table emp1 add constraint ck_emp1 check (empno is not null);
alter table emp1 add constraint ck_emp1 check (empno is not null) enable novalidate;
insert into emp1(empno)
values(null);
将disable novalidate,enable novalidate,enable validate 三种状态组合起来使用
这种组合，可以避免因有个别不符合条件的数据，而导致大数据量的传输失败
假设a表示源数据，其中有空值，b表示a表的归档表，设有非空约束，现将a表插入到b表中
alter table b modify constraint b_nnl disable ,novalidate;
insert into b select * from a;
alter table b modify constraint b_nnl enable,novalidate;
update b set channel = 'NOT KNOWN' where channel is null;
alter table b modify constraint b_nnl enable ,validate;
延迟约束
alter table emp1 add constraint chk_sal check(sal > 500) deferrable;//延迟约束
insert into emp1(empno,sal)
values(3030,100);
set constraint chk_sal immediate;
insert into emp1(empno,sal)
values(3030,100)
set constraint chk_sal deferred;//延迟
insert into emp1(empno,sal)
values(3030,100)
alter table emp1 drop constraint chk_sal;
alter table emp1 add constraint chk_sal check(sal > 500) deferrable initially immediate;
insert into emp1(empno,sal)
values(3030,100);
alter table emp1 drop constraint chk_sal;
alter table emp1 add constraint chk_sal check(sal > 500) deferrable initially deferred;
insert into emp1(empno,sal)
values(3030,100);
2.视图
为什么使用视图
1）限制数据的存取
用户只能看到基表的部分信息。方法：赋予用户访问视图对象的权限，而不是表的对象权限
2）使得复杂的查询变得容易
对于多表连接等复杂语句的映射，或内联视图的使用
3）提供数据的独立性
基表的多个独立子集的映射
简单视图
视图与基表的记录一对一，故可以通过视图修改表
复杂视图
视图与基表的记录一对多，无法修改视图
特性 简单视图 复杂视图
表的个数 一个 一个或多个
含函数 无 有
含组函数 无 有
含distinct 无 有
DML操作 可以 不一定
语法
create [or replace] [force|noforce] view view_name
[(alias[,alias],...)]
as subquery
[with check option [constraint constraint]]
[with read only];
create view v1 as select empno,sal,deptno from emp1 where deptno =10;
select * from user_views
create view v2 as select empno,sal,sal+100,deptno from emp1 where deptno =10;
create view v2 as select empno,sal,sal+100 as sal2,deptno from emp1 where deptno =10;
create view v3(a1,a2,a3,a4) as select empno,sal,sal+100 ,deptno from emp1 where deptno =10;
drop view v1;
drop table emp1;
create view v3(a1,a2,a3,a4) as select empno,sal,sal+100 ,deptno from emp1 where deptno =10;
create force view v3(a1,a2,a3,a4) as select empno,sal,sal+100 ,deptno from emp1 where deptno =10;
select * from v3
flashback table emp1 to before drop
select * from v3
update v3 set a4=99
select * from v3;
select * from emp1;
--with check option
create or replace view v3(a1,a2,a3,a4) as select empno,sal,sal+100 ,deptno from emp1 where deptno =10 with check option;
update v3 set a4=99
update emp1 set deptno =10 where deptno =99
update v3 set a4=99
update v3 set a1=a1+100
grant connect,resource to zzl identified by bsoft
grant select on v3 to zzl
--zzl用户
select * from scott.v3
select * from scott.emp1;
复杂视图
drop table emp1;
drop table dept1;
create table emp1 as select * from emp;
create table dept1 as select * from dept;
create or replace view v5 as select empno,dname from emp1,dept1 where emp1.deptno = dept1.deptno and emp1.deptno =10;
select * from v5
update v5 set dname =3030
3.同义词
同义词通常是数据库对象的别名
公有同义词
公有同义词一般有DBA创建，使所有用户都可使用
create user zzl identified by bsoft;
select * from session_privs
grant create session to zzl;
select * from session_privs
创建者需要有create public synonym权限
create public synonym syn1 for scott.v3
--grant select on syn1 to public
--grant public to zzl
zzl用户执行
select * from syn1;
select * from scott.v3;
--revoke public from zzl;
select * from syn1;
select * from scott.v3;
私有同义词
create synonym abc for emp1;
grant select on abc to zzl;
select * from scott.abc
select * from scott.emp1
revoke select on abc from zzl
select * from scott.abc
select * from scott.emp1
grant select on emp1 to zzl
select * from scott.abc
select * from scott.emp1
4.序列
create sequence seq1
select seq1.currval from dual;
select seq1.nextval from dual;
select seq1.currval from dual;
create sequence seq2 start with 10 increment by 2 maxvalue 20 cycle nocache;
select seq2.nextval from dual;
alter sequence seq2 increment by 1;
select seq2.nextval from dual
alter sequence seq2 start with 19
select * from user_sequences
drop sequence seq2
5.insert 总结
一次插入一行
create table test(id int,name varchar(10) default 'zzl');
insert into test values(1,'abc');
insert into test values(2,default);
insert into test values(3,null);
insert into test(id) values(4);
insert into (select id from test) values(5);
insert into test values(6,(select dname from dept where deptno =10))
insert with check option
insert into (select id from test where id <10 with check option ) values(10);
insert into (select id from test where id <10 with check option ) values(9);
insert into (select name from test where id <10 with check option ) values('zzl');
insert into (select name from test where id <10 ) values('zzl');
insert into (select id,name from test where id <10 with check option ) values(9,'zzl');
update (select sal from emp1) set sal=(select sal from emp1 where empno =7788);
update (select ename from emp1) set sal=(select sal from emp1 where empno =7788);
delete (select sal from emp1) where sal >3000;
一次插入多行
create table emp1 as select * from emp
insert into emp1 select * from emp where deptno =10;
multiable insert
insert all
create table test(x number(10),y varchar2(10));
insert into test values(1,'a');
insert into test values(2,'b');
insert into test values(3,'c');
insert into test values(4,'d');
insert into test values(5,'e');
insert into test values(6,'f');
commit;
create table test1 as select * from test where 0=1;
create table test2 as select * from test where 0=1;
insert all into test1 into test2 select * from test;
select * from test1;
select * from test2;
delete from test1;
delete from test2;
insert all when x >= 3 then into test1 when x >=2 then into test2 select * from test;
insert first
delete from test1;
delete from test2;
insert first when x >= 3 then into test1 when x >=2 then into test2 select * from test;
旋转insert
create table test(empno number(10),week_id number(2),
sal_mon number(8,2),sal_tun number(8,2),
sal_wed number(8,2),sal_thur number(8,2),sal_fri number(8,2))
insert into test values(3030,56,1000,2000,3000,4000,5000)
create table test2(empno number(10),week number(2),sales number(8,2))
insert all
into test2 values(empno,week_id,sal_mon)
into test2 values(empno,week_id,sal_tun)
into test2 values(empno,week_id,sal_wed)
into test2 values(empno,week_id,sal_thur)
into test2 values(empno,week_id,sal_fri)
select empno,week_id,sal_mon,sal_tun,sal_wed,sal_thur,sal_fri from test;
merge
create table emp1 as select * from emp where empno =7788
update emp1 set ename = 'aaa'
insert into emp1(empno,ename)
values(7900,'zzl')
select * from emp1
merge into emp1
using emp
on (emp1.empno = emp.empno)
when matched then
update set emp1.ename = emp.ename
delete where emp1.empno =7900
when not matched then
insert values(emp.empno,emp.ename,emp.job,emp.mgr,emp.hiredate,emp.sal,emp.comm,emp.deptno)
6.with 语句
我们可以用一个关键字with给一个查询块起一个别名。然后在后面的查询中引用这个查询块的别名。
好处：
1.使用with语句，可以避免在select语句中重复书写相同的语句块
2.with语句将该子句中的语句块执行一次并存储到用户的临时表空间中
3.使用with语句可以避免重复解析，提高查询效率
with
dept_costs as(
select d.dname,sum(e.sal) as dept_total
from emp e,dept d
where e.deptno = d.deptno
group by d.dname),
avg_cost as(
select sum(dept_total)/count(*) as dept_avg from dept_costs)
select * from dept_costs
where dept_total < (select dept_avg from avg_cost)
order by dname