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';
