alter table salary add constaint fk_sal_emp foreign key(emp_id) references employee(id);
insert into salary values(1,1,default,30000);
insert into salary values(2,8,default,30000);
--模式对象--用户对象
--序列对象
create sequence seq_sal start with 100;
drop sequence seq_sal;
--使用
select seq_sal.nextval from dual;
select seq_sal.currval from dual;
insert into salary values(seq_sal.nextval,1,default,8);
--视图对象
--授权
grant create view to scott;
drop view vw_pay;
--创建或者修改
create or replace view vw_pay
as
select
e.name,
--e.birth,
to_char(s.pay,'YYYY-MM-DD')time,
s.pay,
s.money
from employee e
inner join salary s on s.emp_id=e.id;
--使用视图
select*from vw_pay where money>1000;
--简单视图
create or replace view vw_employee;
as
select id,name from employee;
select*from vw_employee;
--对象授权
grant all on hr.employees to scott;
select*from hr.employees;
--同义词-外号
grant create synonym to scott;
create or replace synonym hhs for hr.employees;
select*from hhs;
--索引
create index ix_employee_name on employee(name);
create unique index ix_employee_card on employee_card on employee(card);
create bitmap index ix_employee_sex on employee(sex);
drop index ix_employee_name;
insert into salary values(1,1,default,30000);
insert into salary values(2,8,default,30000);
--模式对象--用户对象
--序列对象
create sequence seq_sal start with 100;
drop sequence seq_sal;
--使用
select seq_sal.nextval from dual;
select seq_sal.currval from dual;
insert into salary values(seq_sal.nextval,1,default,8);
--视图对象
--授权
grant create view to scott;
drop view vw_pay;
--创建或者修改
create or replace view vw_pay
as
select
e.name,
--e.birth,
to_char(s.pay,'YYYY-MM-DD')time,
s.pay,
s.money
from employee e
inner join salary s on s.emp_id=e.id;
--使用视图
select*from vw_pay where money>1000;
--简单视图
create or replace view vw_employee;
as
select id,name from employee;
select*from vw_employee;
--对象授权
grant all on hr.employees to scott;
select*from hr.employees;
--同义词-外号
grant create synonym to scott;
create or replace synonym hhs for hr.employees;
select*from hhs;
--索引
create index ix_employee_name on employee(name);
create unique index ix_employee_card on employee_card on employee(card);
create bitmap index ix_employee_sex on employee(sex);
drop index ix_employee_name;