创建表约束:unique唯一,not null 不为空
create table emp3(id number(10) constraint emp3_id_uk unique,name varchar2(20) constraint emp3_name_nn not null,salary number(10,2) ,email varchar2(20), constraint emp3_email_uk unique(email));
主键:primary key(位置和unique一样)主键不能为空,不能重复
修改约束
alter table emp5 modify(salary number(10,2) not null)
删除约束
alter table emp5 drop constraint emp5_name_nn
添加约束
alter table emp5 add constraint emp5_name_uk unique
创建视图
create view empview as select employee_id,last_name,salary from employees where department_id=80;
修改视图(修改视图,原来的表也会被修改,视图是虚表)
update empview set salary=2000 where employee_id=179;
删除视图数据(增删改都会反映到原表)
delete from empview where employee_id=179
可以基于多个表创建视图
create view empview2 as select employee_id id,last_name name,salary,department_name from employees e ,departments d where e.department_id=d.department_id
创建只读的视图
create view empview2 as select employee_id id,last_name name,salary,department_name from employees e ,departments d where e.department_id=d.department_id with read only
删除视图
drop view empview3
序列,创建序列 每次增长10,从0开始增长,提供最大值100,需要循环,不需要缓存登录
create sequence empseq increment by 10 start width 11 maxvalue 100 cycle nocache
删除序列
drop sequence empseq
使用序列想表中添加数据(dept_id_seq是序列名)
insert into dept01 values (dept_id_seq.nextval,'dsd')