学生科目成绩创建表与数据库

USE master
GO
IF EXISTS(SELECT name FROM sys.sysdatabases WHERE name = ‘STUDENT‘)
DROP DATABASE STUDENT
GO
CREATE DATABASE STUDENT
GO
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 dbo.Member ( MID, MName )VALUES  ( ‘M001‘,  ‘张三‘  )
INSERT INTO dbo.Member ( MID, MName )VALUES  ( ‘M002‘,  ‘李四‘  )
INSERT INTO dbo.Member ( MID, MName )VALUES  ( ‘M003‘,  ‘王五‘  )
INSERT INTO dbo.Member ( MID, MName )VALUES  ( ‘M004‘,  ‘赵六‘  )
INSERT INTO dbo.Member ( MID, MName )VALUES  ( ‘M005‘,  ‘小七‘  )
INSERT INTO dbo.Member ( MID, MName )VALUES  ( ‘M006‘,  ‘林一‘  )
INSERT INTO dbo.Member ( MID, MName )VALUES  ( ‘M007‘,  ‘何格‘  )
INSERT INTO dbo.Member ( MID, MName )VALUES  ( ‘M008‘,  ‘山山‘  )
INSERT INTO dbo.Member ( MID, MName )VALUES  ( ‘M009‘,  ‘天利‘  )
INSERT INTO dbo.Member ( MID, MName )VALUES  ( ‘M010‘,  ‘刘凯‘  )
INSERT INTO dbo.F ( FID, FName )VALUES  ( ‘F001‘,  ‘语文‘)
INSERT INTO dbo.F ( FID, FName )VALUES  ( ‘F002‘,  ‘数学‘)
INSERT INTO dbo.F ( FID, FName )VALUES  ( ‘F003‘,  ‘英语‘)
INSERT INTO dbo.F ( FID, FName )VALUES  ( ‘F004‘,  ‘历史‘)
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)
INSERT INTO Score(FID,MID,Score)VALUES(‘F001‘,‘M006‘,88)
INSERT INTO Score(FID,MID,Score)VALUES(‘F002‘,‘M006‘,55)
INSERT INTO Score(FID,MID,Score)VALUES(‘F003‘,‘M006‘,86)
INSERT INTO Score(FID,MID,Score)VALUES(‘F004‘,‘M006‘,79)
INSERT INTO Score(FID,MID,Score)VALUES(‘F002‘,‘M007‘,77)
INSERT INTO Score(FID,MID,Score)VALUES(‘F003‘,‘M008‘,65)
INSERT INTO Score(FID,MID,Score)VALUES(‘F004‘,‘M007‘,48)
INSERT INTO Score(FID,MID,Score)VALUES(‘F004‘,‘M009‘,75)
INSERT INTO Score(FID,MID,Score)VALUES(‘F002‘,‘M009‘,88)

学生科目成绩创建表与数据库

标签：