SCUT2023 C# 数据库

– 学生信息表
CREATE TABLE StudentInfo
(
StudentID INT PRIMARY KEY, – 学号
Name VARCHAR(50)NOT NULL, – 姓名
Gender VARCHAR(10) check(Gender=‘男’ or Gender=‘女’), – 性别
);

– 课程信息表
CREATE TABLE CourseInfo
(

CourseID INT IDENTITY(1,1) PRIMARY KEY,   -- 课程号
CourseName VARCHAR(50)      -- 课程名

);

– 成绩表
CREATE TABLE Score
(
StudentID INT, – 学号
CourseID INT, – 课程号
Score INT check(Score>0), – 成绩
PRIMARY KEY(StudentID, CourseID), – 学号和课程号作为联合主键
FOREIGN KEY(StudentID) REFERENCES StudentInfo(StudentID), – 学号作为外键关联学生信息表
FOREIGN KEY(CourseID) REFERENCES CourseInfo(CourseID) – 课程号作为外键关联课程信息表
);

SELECT Student.sid, Student.sname, Course.cname, Grades.grade FROM Student INNER JOIN Grades ON Student.sid = Grades.sid INNER JOIN Course ON Grades.cid = Course.cid

SELECT s.sname, g.grade FROM Student s, Grades g, Course c WHERE s.sid = g.sid AND g.cid = c.cid AND c.cname = @cname ORDER BY g.grade DESC

INSERT INTO Course (cname) OUTPUT INSERTED.cid VALUES (@CourseName)

INSERT INTO Student (sid,sname, ssex) VALUES (@StudentID,@Name, @Gender)

UPDATE Grades SET grade=@Score WHERE sid=@StudentID AND cid=@CourseID

INSERT INTO Grades (sid, cid, grade) VALUES (@StudentID, @CourseID, @Score)