学生宿舍管理信息系统课程设计

CREATE TABLE Student ( StudentID INT PRIMARY KEY, Name VARCHAR(50), Gender CHAR(1), EnrollmentDate DATE, DormitoryID INT, FOREIGN KEY (DormitoryID) REFERENCES Dormitory(DormitoryID) ); CREATE TABLE Dormitory ( DormitoryID INT PRIMARY KEY, BuildingID INT, PhoneNumber VARCHAR(20), ClosingTime TIME ); CREATE TABLE Property ( PropertyID INT PRIMARY KEY, PropertyName VARCHAR(50), DormitoryID INT, FOREIGN KEY (DormitoryID) REFERENCES Dormitory(DormitoryID) ); CREATE TABLE Repair ( RepairID INT PRIMARY KEY, PropertyID INT, RepairTime DATETIME, Reporter VARCHAR(50), FOREIGN KEY (PropertyID) REFERENCES Property(PropertyID) ); CREATE TABLE NightReturn ( ID INT PRIMARY KEY IDENTITY, StudentID INT, DormitoryID INT, ReturnTime DATETIME, Reason VARCHAR(100), FOREIGN KEY (StudentID) REFERENCES Student(StudentID), FOREIGN KEY (DormitoryID) REFERENCES Dormitory(DormitoryID) ); CREATE TABLE LeaveSchool ( ID INT PRIMARY KEY IDENTITY, StudentID INT, LeaveTime DATETIME, ReturnTime DATETIME, FOREIGN KEY (StudentID) REFERENCES Student(StudentID) ); INSERT INTO Dormitory (DormitoryID, BuildingID, PhoneNumber, ClosingTime) VALUES (1, 101, '1234567890', '22:00'), (2, 102, '0987654321', '23:00'), (3, 103, '1357924680', '21:30'); INSERT INTO Student (StudentID, Name, Gender, EnrollmentDate, DormitoryID) VALUES (1, '张三', 'M', '2023-09-01', 1), (2, '李四', 'F', '2023-09-01', 2), (3, '王五', 'M', '2023-09-01', 1); INSERT INTO Property (PropertyID, PropertyName, DormitoryID) VALUES (1, '桌子', 1), (2, '椅子', 1), (3, '电视', 2); INSERT INTO Repair (RepairID, PropertyID, RepairTime, Reporter) VALUES (1, 1, '2024-05-20 10:30:00', '张三'), (2, 3, '2024-05-22 15:45:00', '李四'); INSERT INTO NightReturn (StudentID, DormitoryID, ReturnTime, Reason) VALUES (1, 1, '2024-05-24 23:30:00', '晚自习'), (2, 2, '2024-05-23 22:00:00', '参加社团活动'); INSERT INTO LeaveSchool (StudentID, LeaveTime, ReturnTime) VALUES (1, '2024-05-25 08:00:00', NULL), (2, '2024-05-24 09:00:00', '2024-05-25 14:00:00'); 宿舍的基本信息管理： 查询所有宿舍的基本信息： SELECT * FROM Dormitory; 插入新的宿舍信息： INSERT INTO Dormitory (DormitoryID, BuildingID, PhoneNumber, ClosingTime) VALUES (4, 104, '1234567890', '22:30'); 更新宿舍信息： UPDATE Dormitory SET PhoneNumber = '9876543210' WHERE DormitoryID = 1; 删除宿舍信息： DELETE FROM Dormitory WHERE DormitoryID = 3; 学生基本信息管理： 查询所有学生的基本信息： SELECT * FROM Student; 插入新的学生信息： INSERT INTO Student (StudentID, Name, Gender, EnrollmentDate, DormitoryID) VALUES (4, '赵六', 'M', '2023-09-01', 2); 更新学生信息： UPDATE Student SET Gender = 'F' WHERE StudentID = 3; 删除学生信息： DELETE FROM Student WHERE StudentID = 2; 宿舍财产的基本信息管理： 查询所有宿舍财产的基本信息： SELECT * FROM Property; 插入新的宿舍财产信息： INSERT INTO Property (PropertyID, PropertyName, DormitoryID) VALUES (4, '空调', 2); 更新宿舍财产信息： UPDATE Property SET PropertyName = '电脑' WHERE PropertyID = 3; 删除宿舍财产信息： DELETE FROM Property WHERE PropertyID = 1; 报修的基本信息管理： 查询所有报修的基本信息： SELECT * FROM Repair; 插入新的报修信息： INSERT INTO Repair (RepairID, PropertyID, RepairTime, Reporter) VALUES (3, 2, '2024-05-28 10:00:00', '王五'); 更新报修信息： UPDATE Repair SET RepairTime = '2024-05-30 09:00:00' WHERE RepairID = 1; 删除报修信息： DELETE FROM Repair WHERE RepairID = 2; 夜归的基本信息管理： 查询所有夜归的基本信息： SELECT * FROM NightReturn; 插入新的夜归信息： INSERT INTO NightReturn (StudentID, DormitoryID, ReturnTime, Reason) VALUES (3, 1, '2024-05-31 23:00:00', '学习到夜里'); 更新夜归信息： UPDATE NightReturn SET Reason = '考试复习' WHERE ID = 1; 删除夜归信息： DELETE FROM NightReturn WHERE ID = 2; 离校的基本信息管理： 查询所有离校的基本信息： SELECT * FROM LeaveSchool; 插入新的离校信息： INSERT INTO LeaveSchool (StudentID, LeaveTime, ReturnTime) VALUES (2, '2024-06-01 10:00:00', '2024-06-02 12:00:00'); 更新离校信息： UPDATE LeaveSchool SET ReturnTime = '2024-06-03 14:00:00' WHERE ID = 1; 删除离校信息： DELETE FROM LeaveSchool WHERE ID = 2; 查询及统计报表： 统计每个宿舍的学生人数： SELECT DormitoryID, COUNT(*) AS StudentCount FROM Student GROUP BY DormitoryID; 查询某个宿舍的所有报修记录： SELECT * FROM Repair WHERE PropertyID IN (SELECT PropertyID FROM Property WHERE DormitoryID = 1); 查询某个学生的夜归记录： SELECT * FROM NightReturn WHERE StudentID = 1; 查询某个时间段内的离校学生信息： SELECT * FROM LeaveSchool WHERE LeaveTime >= '2024-05-01' AND LeaveTime < '2024-06-01';