**分别在四台服务器上建立双向的数据库链接，以创建计算机系到外语系的链接服务器
**
（student1）为例。
EXEC sp_addlinkedserver
@server='student1',
@datasrc='tred',
@srvproduct='',
@provider='SQLOLEDB';
EXEC sp_addlinkedsrvlogin
@rmtsrvname='student1',
@useself='false'
分别在三个系服务器上创建分布式分区视图，以计算机系为例。
create view info_student
as
select * from student
union all
select * from student1.dbo.student1
union all
select * from student2.dbo.student2
例子：
按系别分站点
1.在站点student上访问student2
select sc.sno,sc.cno,grade ,sdept
from sc, student2.dbo.student
where student2.dbo.student.sno=sc.sno
2.查询某站点所有同学中的最高分的同学信息
select top 1 sc.sno, sname, sc.cno,grade ,cname,sdept
from sc, course,student1.dbo.student
where student1.dbo.student.sno=sc.sno and sc.cno=course.cno
order by grade desc
3.查询两个站点的sno, sname,sdept
select sno, sname,sdept
from student1.dbo.student
union
select sno, sname,sdept
from student2.dbo.student
4.查询所有站点男同学成绩（重构法）
select sc.sno, sname,ssex,sdept,grade
from student1.dbo.student,sc
where student1.dbo.student.sno=sc.sno and ssex='男'
union
select sc.sno, sname,ssex,sdept,grade
from student2.dbo.student,sc
where student2.dbo.student.sno=sc.sno and ssex='男'
union
select sc.sno, sname,ssex,sdept,grade
from student,sc
where student.sno=sc.sno and ssex='男'
访问另一个，查询选修了‘数据库’的同学的成绩
select sc.sno, sname,sdept,grade,cname
from student1.dbo.student,sc,course
where student1.dbo.student.sno=sc.sno and sc.cno=course.cno and cname='Java'
6.找出选修数据库的最高分的同学的学号，姓名，成绩
select top 1 sc.sno, sname,cname,sdept,grade
from student,sc,course
where student.sno=sc.sno and course.cno=sc.cno and cname='数据库'
order by grade desc
select top 1 sc.sno, sname,cname,sdept,grade
from student1.dbo.student,sc,course
where student1.dbo.student.sno=sc.sno and course.cno=sc.cno and cname='数据库'
order by grade desc
select top 1 sc.sno, sname,cname,sdept,grade
from student2.dbo.student,sc,course
where student2.dbo.student.sno=sc.sno and course.cno=sc.cno and cname='数据库'
order by grade desc
7.从一个站点向另一个站点插入一条数据
insert
into student1.dbo.student(sno,sname,ssex,sage,sdept)
values ( '201212009','dd','男',19,'数学')
8.创建化学系平均分，最高分，最低分视图
create view T_huxue(sno,avg_grade,min_g,max_grade,cmane)
as
select sc.sno,avg(grade),min(grade),max(grade),cname
from sc, student2.dbo.student ,course
where student2.dbo.student.sno=sc.sno and course.cno=sc.cno
group by sc.sno,cname
9.创建计算机系视图平均分大于60的
create view Ji_60(cname,avg_g)
as
select cmane,avg_grade
from T_huxue
where avg_grade>60
向计算机某一姓视图中插入一条数据
insert
into Ji_xin
values ('201212022','黄丽丽',24,'女','计算机')
select max(grade) 最高分,min(grade) 最低分,avg(grade) 平均分
from T_jisuanji
查询数学系视图中的人数
select count( distinct sno) 人数
from T_shuxue
更新某一姓视图中女同学的年龄加一
update Ji_xin
set sage=sage+1
where ssex='女'
把所有系人数创建视图
create view hh(renshu,sdept)
as
select count(sno),sdept
from student1.dbo.student
group by sdept
union
select count(sno),sdept
from student2.dbo.student
group by sdept
union
select count(sno),sdept
from student
group by sdept
把所有系选课人数创建视图
create view hh2(renshu,sdept)
as
select count(distinct sc.sno),sdept
from student1.dbo.student,sc
where sc.sno=student1.dbo.student.sno
group by sdept
union
select count(distinct sc.sno),sdept
from student2.dbo.student,sc
where sc.sno=student2.dbo.student.sno
group by sdept
union
select count(distinct sc.sno),sdept
from student,sc
where sc.sno=student.sno
group by sdept
删除其他站点数据
delete student2.dbo.student
where sno='ll'
删除视图
drop view ji_xin
drop view T_shuxue1
网友评论