公有同义词:由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;