--建数据表--
USE Student
GO
CREATE TABLE Member --学生表
(
MID char(10) primary key, --学生号
MName CHAR(50) NOT NULL --姓名
)
GO
CREATE TABLE F --课程表
(
FID char(10) primary key, --课程号
FName CHAR(50) NOT NULL --课程名
)
GO
CREATE TABLE score --学生成绩表
(
SID int identity(1,1) primary key, --成绩记录号
FID char(10) foreign key(FID) references F(FID) , --课程号
MID char(10) foreign key(MID) references Member(MID) , --学生号
Score int NOT NULL --成绩
)
GO
--课程表中插入数据--
INSERT INTO F(FID,FName)VALUES( 'F001 ', '语文 ')
INSERT INTO F(FID,FName)VALUES( 'F002 ', '数学 ')
INSERT INTO F(FID,FName)VALUES( 'F003 ', '英语 ')
INSERT INTO F(FID,FName)VALUES( 'F004 ', '历史 ')
--学生表中插入数据--
INSERT INTO Member(MID,MName)VALUES( 'M001 ', '张萨 ')
INSERT INTO Member(MID,MName)VALUES( 'M002 ', '王强 ')
INSERT INTO Member(MID,MName)VALUES( 'M003 ', '李三 ')
INSERT INTO Member(MID,MName)VALUES( 'M004 ', '李四 ')
INSERT INTO Member(MID,MName)VALUES( 'M005 ', '阳阳 ')
INSERT INTO Member(MID,MName)VALUES( 'M006 ', '虎子 ')
INSERT INTO Member(MID,MName)VALUES( 'M007 ', '夏雪 ')
INSERT INTO Member(MID,MName)VALUES( 'M008 ', '璐璐 ')
INSERT INTO Member(MID,MName)VALUES( 'M009 ', '珊珊 ')
INSERT INTO Member(MID,MName)VALUES( 'M010 ', '香奈儿 ')
--成绩表中插入数据--
INSERT INTO Score(FID,MID,Score)VALUES( 'F001 ', 'M001 ',78)
INSERT INTO Score(FID,MID,Score)VALUES( 'F002 ', 'M001 ',67)
INSERT INTO Score(FID,MID,Score)VALUES( 'F003 ', 'M001 ',89)
INSERT INTO Score(FID,MID,Score)VALUES( 'F004 ', 'M001 ',76)
INSERT INTO Score(FID,MID,Score)VALUES( 'F001 ', 'M002 ',89)
INSERT INTO Score(FID,MID,Score)VALUES( 'F002 ', 'M002 ',67)
INSERT INTO Score(FID,MID,Score)VALUES( 'F003 ', 'M002 ',84)
INSERT INTO Score(FID,MID,Score)VALUES( 'F004 ', 'M002 ',96)
INSERT INTO Score(FID,MID,Score)VALUES( 'F001 ', 'M003 ',70)
INSERT INTO Score(FID,MID,Score)VALUES( 'F002 ', 'M003 ',87)
INSERT INTO Score(FID,MID,Score)VALUES( 'F003 ', 'M003 ',92)
INSERT INTO Score(FID,MID,Score)VALUES( 'F004 ', 'M003 ',56)
INSERT INTO Score(FID,MID,Score)VALUES( 'F001 ', 'M004 ',80)
INSERT INTO Score(FID,MID,Score)VALUES( 'F002 ', 'M004 ',78)
INSERT INTO Score(FID,MID,Score)VALUES( 'F003 ', 'M004 ',97)
INSERT INTO Score(FID,MID,Score)VALUES( 'F004 ', 'M004 ',66)