python: sql server

/*
学生类:姓名、年龄、学号、成绩

班级类:班级名称、学生列表显示所有学生
根据学号查找学生
添加一个学生
删除一个学生(学生对象、学号)
根据学号升序排序
根据成绩降序排序

*/

--学生表
DROP TABLE StudentList
GO
create table StudentList
(
	 StudentId INT IDENTITY(1,1) PRIMARY KEY,
	 StudentName nvarchar(50),
	 StudentNO varchar(50),                    --学号
	 StudentBirthday datetime				   --学生生日
	 

)
go

insert into StudentList(StudentName,StudentNO,StudentBirthday) values(N'刘三','001','2007-12-27')
go
insert into StudentList(StudentName,StudentNO,StudentBirthday) values(N'王二','002','2008-2-14')
go

select * from StudentList
go


--课程名称
DROP TABLE Course
GO
create table Course
(
   CourseId INT IDENTITY(1,1) PRIMARY KEY,
   CourseName nvarchar(50)					--课程名称
)
go

insert into Course(CourseName) values(N'语文')
go
insert into Course(CourseName) values(N'英语')
go
insert into Course(CourseName) values(N'数学')
go

select * from Course
go




--班表名称
DROP TABLE GradeClass
GO
create table GradeClass
(
	ClassId INT IDENTITY(1,1) PRIMARY KEY,
	ClassName nvarchar(50)				--班级名称
)
go

insert into GradeClass(ClassName) values(N'二年级一班')
go

insert into GradeClass(ClassName) values(N'二年级二班')
go

select * from GradeClass
go



--成绩表
DROP TABLE StudentScore
GO
create table StudentScore
(
	 ScoreId INT IDENTITY(1,1) PRIMARY KEY,
	 StudentId int
		Foreign Key REFERENCES StudentList(StudentId),  --学生ID 外键
	 CourseId int
	    Foreign Key REFERENCES Course(CourseId),      --课程ID 外键
	 Score float									  --成绩
)
go

insert into StudentScore(StudentId,CourseId,Score) values(1,1,90)
insert into StudentScore(StudentId,CourseId,Score) values(1,2,56)
insert into StudentScore(StudentId,CourseId,Score) values(1,3,80)
go

insert into StudentScore(StudentId,CourseId,Score) values(2,1,92)
insert into StudentScore(StudentId,CourseId,Score) values(2,2,83)
insert into StudentScore(StudentId,CourseId,Score) values(2,3,78)
go

select * from StudentScore
go




---班级学生
DROP TABLE StudentClass
GO
create table StudentClass
(
	StudentClassId INT IDENTITY(1,1) PRIMARY KEY,
	StudentId int 
		Foreign Key REFERENCES StudentList(StudentId),   --学生ID 外键
	ClassId int 
		Foreign Key REFERENCES GradeClass(ClassId),		--班级ID 外键
)
go

insert into StudentClass(StudentId,ClassId) values(1,1)
go
insert into StudentClass(StudentId,ClassId) values(2,1)
go
select * from StudentClass
go

-- 成绩视图
select a.*,b.StudentNO,b.StudentName,b.StudentBirthday,c.CourseName from StudentScore as a--,as a,StudentList as b,Course as c StudentClass as d,GradeClass as f  
LEFT JOIN StudentList as b on a.StudentId=b.StudentId 
LEFT JOIN Course as c on a.CourseId =c.CourseId
LEFT JOIN StudentClass as d on d.StudentId=b.StudentId
go

-- 班级视图
select a.*,b.StudentNO,b.StudentNO,b.StudentName,d.ClassName from StudentClass as a
left join StudentList as b on a.StudentId=b.StudentId
left join GradeClass as d on a.ClassId=d.ClassId
go

"""
StudentListInfo.py
学生类
date 2023-06-16
edit: Geovin Du,geovindu, 涂聚文
ide:  PyCharm 2023.1 python 11
"""

import datetime
import sys
import os

class StudentList(object):
    """
    学生类
    """
    def __init__(self,StudentName:str, StudentNO:str,StudentBirthday:datetime.datetime):
        self._StudentName=StudentName
        self._StudentNO=StudentNO
        self._StudentBirthday=StudentBirthday
        self._StudentId=0
        self._age=0

    def __del__(self):
        print(f"{self._StudentName}")

    def setStudentName(self,StudentName):
        self._StudentName=StudentName

    def getStudentName(self):
        return self._StudentName

    def setStudentNO(self,StudentNO):
        self._StudentNO=StudentNO

    def getStudentNO(self):
        return self._StudentNO

    def setStudentId(self,StudentId):
        self._StudentId=StudentId

    def getStudentId(self):
        return  self._StudentId

    def setStudentBirthday(self,StudentBirthday):
        self._StudentBirthday=StudentBirthday

    def setAge(self,age):
        self._age=age

    def getAge(self):
        return  self._age

    def __str__(self):
        return f"{self._StudentId},{self._StudentName},{self._StudentNO},{self._StudentBirthday}{self._age}"



"""
CourseInfo.py
课程类
date 2023-06-16
edit: Geovin Du,geovindu, 涂聚文
ide:  PyCharm 2023.1 python 11
"""

class Course(object):
    """

    """

    def __init__(self,CourseName:str):
        self._CourseName=CourseName
        self._CourseId=0

    def __del__(self):
        print(f"{self._CourseName}")

    def setCourseId(self,CourseId):
        self._CourseId=CourseId

    def getCourseId(self):
        return self._CourseId

    def setCourseName(self,CourseName):
        self._CourseName=CourseName

    def getCourseName(self):
        return self._CourseName

    def __str__(self):
        return f"CourseId:{self._CourseId},CourseName:{self._CourseName}"