sql server学生成绩管理数据库代码
时间: 2024-06-12 13:07:06 浏览: 9
以下是一个简单的SQL Server数据库代码,用于管理学生成绩:
--创建学生表
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Gender VARCHAR(10),
DateOfBirth DATE
)
--创建课程表
CREATE TABLE Courses (
CourseID INT PRIMARY KEY,
CourseName VARCHAR(50),
Credits INT
)
--创建成绩表
CREATE TABLE Grades (
GradeID INT PRIMARY KEY,
StudentID INT FOREIGN KEY REFERENCES Students(StudentID),
CourseID INT FOREIGN KEY REFERENCES Courses(CourseID),
Grade DECIMAL(4, 2)
)
--插入学生数据
INSERT INTO Students (StudentID, FirstName, LastName, Gender, DateOfBirth)
VALUES
(1, '张', '三', '男', '2000-01-01'),
(2, '李', '四', '女', '2001-02-02'),
(3, '王', '五', '男', '2002-03-03'),
(4, '赵', '六', '女', '2003-04-04'),
(5, '刘', '七', '男', '2004-05-05')
--插入课程数据
INSERT INTO Courses (CourseID, CourseName, Credits)
VALUES
(1, '语文', 4),
(2, '数学', 4),
(3, '英语', 4)
--插入成绩数据
INSERT INTO Grades (GradeID, StudentID, CourseID, Grade)
VALUES
(1, 1, 1, 85),
(2, 1, 2, 90),
(3, 1, 3, 95),
(4, 2, 1, 80),
(5, 2, 2, 85),
(6, 2, 3, 90),
(7, 3, 1, 75),
(8, 3, 2, 80),
(9, 3, 3, 85),
(10, 4, 1, 70),
(11, 4, 2, 75),
(12, 4, 3, 80),
(13, 5, 1, 65),
(14, 5, 2, 70),
(15, 5, 3, 75)
--查询所有学生的成绩
SELECT s.FirstName, s.LastName, c.CourseName, g.Grade
FROM Students s
JOIN Grades g ON s.StudentID = g.StudentID
JOIN Courses c ON g.CourseID = c.CourseID
--查询某个学生的成绩
SELECT s.FirstName, s.LastName, c.CourseName, g.Grade
FROM Students s
JOIN Grades g ON s.StudentID = g.StudentID
JOIN Courses c ON g.CourseID = c.CourseID
WHERE s.FirstName = '张' AND s.LastName = '三'