连接查询，视图

表Member:

表F:

表score:

内连接查询：

SELECT Mname,Fname,Score FROM Member INNER JOIN score ON Member.MID=score.MID INNER JOIN F ON F.FID=score.FID

视图的创建，查询

IF EXISTS(SELECT *FROM sysobjects WHERE id=OBJECT_ID('Student_View'))
DROP VIEW Student_View
go
CREATE VIEW Student_View AS
SELECT Mname,Fname,Score FROM Member INNER JOIN score ON Member.MID=score.MID INNER JOIN F ON F.FID=score.FID  

SELECT *FROM Student_View

查询每个人的总分

SELECT MName,total.total_score FROM Member INNER JOIN (SELECT MID,SUM(Score) total_score FROM score GROUP BY MID)  total
ON Member.MID=total.MID

带参数的存储过程：

IF EXISTS(SELECT *FROM sysobjects WHERE id=OBJECT_ID('student_SumScore'))
DROP PROCEDURE student_SumScore

CREATE PROCEDURE student_SumScore
(@mid CHAR(10))
AS
SET NOCOUNT OFF
BEGIN
SELECT MName,total.total_score FROM Member INNER JOIN (SELECT MID,SUM(Score) total_score FROM score GROUP BY MID)  total
ON Member.MID=total.MID WHERE Member.MID=@mid
END 
SET NOCOUNT ON

EXEC student_SumScore 'M001'