Oracle同义词、视图

公有同义词：由DBA创建，用于所有用户之间互相访问

私有同义词：由用户创建，只有创建的用户才可以使用 (如果存在同名那么私有同义词优先)

同义词优点：

1.简化SQL语句

2.隐藏对象的名称和所有者

3.为分布式数据库的远程对象提供了透明的位置

4.提供对象的公共访问

视图：通过定制的表达方式，用来显示一个或者多个表的数据视图也称之为虚拟表，已存储的查询（常用于查询，基本不会出现更改）

视图的优点：

1.安全：根据个人的权限只允许看到某几个字段

2.隐藏数据的复杂性

3.简化用户的SQL命令

4.将引用程序与基表的修改隔离

create tablespace table1
datafile 'D:\table1.dbf'
size 50m
autoextend on 
next 50m maxsize 2048m;

create user student identified by student default tablespace table1;
grant connect,resource to student;
--student授权student2可以查看stuinfo表
grant select on stuinfo to student2;

create table stuinfo 
(
stuid int primary key,
stuname varchar(10) not null,
age int check(age>0 and age<100),
sex varchar(10) default '男',
createTime date default sysdate
)

create table stuscore
(
       scoreid int primary key,  
       stuid int references stuinfo(stuid) not null,  
       subject varchar(10) not null,  
       score float check(score>=0 and score<=100)     
)

select * from user_tables;
select * from user_tablespaces;

create tablespace table2
datafile 'D:\table2.dbf'
size 50m
autoextend on 
next 50m maxsize 2048m;

create user student2 identified by student2 default tablespace table2;
grant connect,resource to student2;
grant create any synonym to student2;--system授予student2创建私有同义词权限

grant create public synonym to student2;--system授予student2创建公有同义词权限
grant drop public synonym to student2;--system授予student2删除公有同义词权限
select * from student.stuinfo;

--创建私有同义词
--create synonym 同义词 for 原对象
create synonym stuinfo for student.stuinfo;
select * from stuinfo;

--创建公有同义词
create public synonym pubstuinfo for student.stuinfo;
select * from pubstuinfo;
--删除同义词
drop synonym newstuinfo;
drop public synonym pubstuinfo;

select * from user_synonyms;

-------------序列-------------
select * from stuinfo;
insert into stuinfo(stuid,stuname,age) values (SQ_ab.Nextval,'11111',18);

delete from stuinfo;

select * from user_sequences;


insert into stuscore(scoreid,stuid,subject,score) values (SQ_bb.Nextval,1,'jsp',82);
insert into stuscore(scoreid,stuid,subject,score) values (SQ_bb.Nextval,2,'jsp',76);
insert into stuscore(scoreid,stuid,subject,score) values (SQ_bb.Nextval,3,'jsp',82);
insert into stuscore(scoreid,stuid,subject,score) values (SQ_bb.Nextval,4,'jsp',82);
insert into stuscore(scoreid,stuid,subject,score) values (SQ_bb.Nextval,5,'jsp',82);
insert into stuscore(scoreid,stuid,subject,score) values (SQ_bb.Nextval,6,'jsp',82);
insert into stuscore(scoreid,stuid,subject,score) values (SQ_bb.Nextval,7,'jsp',82);
insert into stuscore(scoreid,stuid,subject,score) values (SQ_bb.Nextval,8,'jsp',82);
select * from stuscore;
delete from stuscore;
---------------视图---------------
select * from stuinfo;

select * from stuinfo where stuid>5;

create view view1
as
select * from stuinfo where stuid>5;

select * from user_views;

select * from view1;


insert into view2 (stuid,stuname,age) values (1,'111',13);

create view view2
as
select * from stuinfo where stuid>5 with check option;

update view2 set stuid=1 where stuid=300;

select * from view2;
delete from view2 where stuid=1;
insert into view2(stuid,stuname,age) values (400,'111',15);


--id降序排列
select stuInfo.Stuid, stuinfo.stuname,stuscore.subject,stuscore.score from stuinfo inner join stuscore on stuinfo.stuid=stuscore.stuid order by stuId desc;
--统计大于平均成绩
select * from stuscore where score>(select avg(score) from stuscore where subject='jsp');
--成绩降序
select row_number() over(partition by subject order by score desc) as score_index,stuinfo.stuname,stuscore.subject,stuscore.score from stuinfo inner join stuscore on stuinfo.stuid=stuscore.stuid;