MySQl数据库课程设计 学生宿舍管理系统
表的创建
(1)create table dormitory( #宿舍信息表
dormitory_id varchar(15) not null, #宿舍号
capacity int, #宿舍人数
bed_id int, #床号
student_name varchar(20), #姓名
student_sex varchar(5) #性别
);
(2)create table suguan( #宿管信息表
sg_id varchar(15) primary key, #宿管人员编号
sg_name varchar(20), #宿管姓名
sg_sex varchar(5), #宿管性别
address varchar(40) #宿舍地址(宿管负责的宿舍)
);
(3)create table sushezhang( #宿舍长信息表
ssz_id varchar(15), #宿舍长编号(跟宿舍号相同)
ssz_name varchar(20), #宿舍长姓名
ssz_sex varchar(5), #宿舍长性别
constraint pk_id1 primary key(ssz_id)
);
(4)create table student( #学生信息表
student_id varchar(15), #学号
student_name varchar(20), #姓名
student_sex varchar(5), #性别
specialization varchar(10), #专业
class varchar(20), #班级
dormitory_id varchar(15), #宿舍号
ssz_name varchar(15), #宿舍长姓名
capacity int, #宿舍人数
tel1 varchar(20), #电话
constraint pk_id2 primary key(student_id)
);
(5)create table dormitory_repair( #宿舍维修表
repair_id int AUTO_INCREMENT PRIMARY KEY, #维修编号
staff_id varchar(15), #维修人员工号
dormitory_id varchar(15), #宿舍号
sg_id varchar(15), #宿管人员编号
ssz_id varchar(15), #宿舍长编号
time1 date, #报修时间
time2 varchar(15), #维修时间
breakdown varchar(100), #损坏的地方
examine_state varchar(16), #审核状态
constraint fk_id
foreign key(ssz_id) references sushezhang(ssz_id)
on update cascade
);
(6)create table repair_staff( #维修人员信息表
staff_id varchar(15) PRIMARY KEY, #维修人员工号
staff_name varchar(20), #维修人员姓名
staff_sex varchar(5), #维修人员性别
tel2 varchar(20) #维修人员电话号码
);
提高管理效率。实现学生宿舍信息的集中化、数字化管理，减少人工操作和纸质记录，提高信息的准确性和及时性。自动化处理宿舍分配、维修管理、人员信息更新等常见业务流程，节省时间和人力成本。第二，优化资源配置。实时掌握宿舍的入住情况、床位使用情况等信息，合理分配宿舍资源，提高宿舍的利用率。根据学生的需求和宿舍的实际情况，进行精准的宿舍调整和优化。第三，提升服务质量。为学生提供便捷的服务渠道，如在线申请宿舍维修、查询宿舍信息等，提高学生的满意度。及时响应学生的需求和问题，加强与学生的沟通和互动，营造良好的住宿环境。第四，加强安全管理。准确记录宿舍人员的信息，便于进行人员出入管理和安全排查。对宿舍的维修情况进行跟踪和管理，确保宿舍设施的完好和安全。第五，数据统计与分析。收集和整理学生宿舍管理相关的数据，为学校的决策提供数据支持，例如宿舍建设规划、管理政策调整等。通过数据分析发现潜在的问题和趋势，提前采取措施进行预防和改进。第六，规范管理流程。建立标准化、规范化的宿舍管理流程和制度，确保各项工作有章可循，提高管理的规范性和公正性。减少人为因素的干扰，降低管理风险，保障学校和学生的利益。
综上所述，学生宿舍管理系统的编写旨在通过信息化手段，提高宿舍管理的效率和质量，优化资源配置，加强安全管理，为学生提供更好的住宿服务，同时为学校的管理决策提供有力支持。
1.2 背景
随着学校规模的不断扩大，学生数量的日益增多，学生宿舍管理工作变得越来越复杂和繁重。传统的手工管理方式效率低下、容易出错，且难以满足学校对学生宿舍管理的精细化、规范化要求。
在过去的管理模式中，宿舍分配往往依靠人工操作，容易出现分配不合理、资源浪费等问题。宿舍维修申请和处理流程繁琐，导致维修不及时，影响学生的正常生活。宿管人员对学生信息的掌握不够全面和及时，在进行人员管理和安全排查时存在困难。同时，学校管理层也难以获取准确、全面的宿舍管理数据，无法为决策提供有力支持。
为了提高学生宿舍管理的效率和质量，优化资源配置，提升服务水平，保障学生的住宿安全和舒适，开发一个功能完善、操作便捷的学生宿舍管理系统显得尤为重要。该系统将利用现代信息技术，实现宿舍信息的数字化管理，自动化处理各类业务流程，为学生、宿管人员和学校管理层提供高效、便捷的服务，促进学校宿舍管理工作的科学化、规范化发展。
1.3目标
1.功能目标：实现学生宿舍信息的全面管理，包括宿舍基本信息、学生入住信息、宿管人员信息等。提供高效的宿舍分配功能，根据学生的年级、专业、性别等因素进行合理分配。建立完善的宿舍维修管理模块，能够及时记录和处理维修申请，跟踪维修进度。支持在线查询功能，学生和宿管人员可以方便地查询宿舍相关信息。
2.性能目标：确保系统响应迅速，在处理大量数据时仍能保持高效的性能，查询操作的响应时间不超过 3 秒。保证系统的稳定性和可靠性，能够 7×24 小时不间断运行，年故障停机时间不超过 8 小时。
3.用户体验目标：设计简洁、直观的用户界面，方便不同用户（学生、宿管人员、管理人员）快速上手操作。提供清晰明确的操作指引和提示信息，降低用户的学习成本。
4.数据管理目标：确保数据的准确性和完整性，对输入的数据进行严格的校验和审核。建立定期的数据备份机制，防止数据丢失或损坏，数据备份周期不超过 24 小时。
1.4需求分析
1.学生需求：能够查询自己的宿舍信息，包括宿舍号、室友信息等；可以在线提交宿舍维修申请，并查看维修申请的处理进度；了解宿舍的规章制度和通知公告。
2.宿管人员需求：管理学生的入住和退宿信息，进行宿舍分配和调整；处理学生的维修申请，安排维修人员并跟踪维修进度；对宿舍进行日常检查，记录违规情况；发布宿舍相关的通知公告。
3.维修管理需求：学生在线提交维修申请，描述维修问题；宿管人员审核维修申请，安排维修人员和维修时间；维修人员完成维修后，记录维修结果和费用；学生能够查看维修进度和反馈维修满意度。
4.学校管理人员：查看宿舍的整体使用情况和统计数据，制定和修改宿舍管理的规章制度，管理宿管人员和维修人员的信息。
1.5 系统总体功能图
如图1.5
1.6系统数据流图
如图1.6
1.7系统数据字典
(1)dormitory表(宿舍信息表):
dormitory_id (varchar(15),not null):宿舍号
number1 (int):宿舍人数
bed_id (int):床号
student_name (varchar(20)):学生姓名
student_sex (varchar(5)):学生性别
(2)suguan表(宿管信息表):
sg_id v(archar(15),primary key):宿管人员编号
sg_name (varchar(20)):宿管姓名
sg_sex (varchar(5)):宿管性别
address (varchar(40)):宿舍地址(宿管负责的宿舍)
(3)sushezhang表(宿舍长信息表)
ssz_id (varchar(15),primary key):宿舍长编号(跟宿舍号相同)
ssz_name (varchar(20)):宿舍长姓名
ssz_sex (varchar(5)):宿舍长性别
(4)student表(学生信息表):
student_id (varchar(15),primary key):学号
student_name (varchar(20)):姓名
student_sex (varchar(5)):性别
specialization (varchar(10)):专业
class (varchar(20)):班级
dormitory_id (varchar(15)):宿舍号
ssz_name (varchar(15)):宿舍长姓名
capacity (int):宿舍人数
tel1 (varchar(20)):电话
(5)dormitory_repair表(宿舍维修表):
repair_id (int,AUTO_INCREMENT,primary key):维修编号
staff_id (varchar(15)):维修人员工号
dormitory_id (varchar(15)):宿舍号
sg_id (varchar(15)):宿管人员编号
dormitory_id (varchar(15),not null):宿舍号
time1 (date):报修时间
time2 (varchar(15)):维修时间
breakdown (varchar(100)):损坏的地方
examine_state (varchar(16)):审核状态
(6)repair_staff(维修人员信息表):
staff_id (varchar(15),primary key):维修人员工号
staff_name (varchar(20)):维修人员姓名
staff_sex (varchar(5)):维修人员性别
tel2 (varchar(20)):维修人员电话号码
1.8实体与数据
(1)宿舍实体:(dormitory_id,capacity,bed_id,student_name,student_sex)
('01101', 4, 1, '李华', '男');('02102', 3, 2, '张华', '女');
('03103', 4, 3, '王敏', '女');('04104', 2, 1, '赵磊', '男');
('05105', 3, 2, '孙悦', '女');('06106', 4, 3, '周婷', '女');
('07107', 2, 1, '吴昊', '男');('08108', 3, 2, '郑怡', '女');
('09109', 4, 3, '陈晨', '男');('10110', 2, 1, '林佳', '女');
('11111', 3, 2, '徐萌', '女');('11501', 3, 2, '郑凯', '男');
('12112', 4, 3, '胡晓', '男');('12211', 4, 1, '王强', '男');
('13113', 2, 1, '朱琳', '女');('13611', 2, 1, '郝蕾', '女');
('13611', 2, 4, '章子怡', '女');('14109', 4, 1, '张青平', '男');
('14109', 4, 2, '李治', '男');('14109', 4, 3, '王刚', '男');
('14109', 4, 4, '陈赫', '男');('14114', 3, 2, '高悦', '女');
('15115', 4, 3, '马俊', '男');('16116', 2, 1, '刘瑶', '女');
('17117', 3, 2, '陈鑫', '男');('18118', 4, 3, '杨雪', '女');
('19119', 2, 1, '黄琪', '女');('20120', 3, 2, '吴波', '男');
(2)宿管实体:(sg_id,sg_name,sg_sex,address)
('001', '李翠红', '女', '1栋');('002', '那英', '女', '1栋');
('003', '陈美丽', '女', '2栋');('004', '赵晓峰', '男', '3栋');
('005', '孙雅琴', '女', '4栋');('006', '吴嘉瑞', '男', '5栋');
('007', '周慧敏', '女', '6栋');('008', '郑俊辉', '男', '7栋');
('009', '林晓燕', '女', '8栋');('010', '徐明轩', '男', '9栋');
('011', '胡梦琪', '女', '10栋');('012', '朱雨欣', '女', '15栋');
('016','王大海', '男', '11栋');('021', '张玲珑', '女', '12栋');
('033', '肖月', '女', '13栋');('036', '肖丽', '女', '14栋');
('045', '刘宝强', '男', '20栋');
(3)宿舍长实体:(ssz_id,ssz_name,ssz_sex)
('01101', '李华', '男');('01102', '李勇', '男');
('02102', '张华', '女');('02403', '张敏', '女');
('03103', '王敏', '女');('04104', '赵磊', '男');
('05105', '孙悦', '女');('06106', '周婷', '女');
('07107', '吴昊', '男');('08108', '郑怡', '女');
('09109', '陈晨', '男');('10110', '林佳', '女');
('11111', '徐萌', '女');('11501', '郑凯', '男');
('12112', '胡晓', '男');('12211', '王强', '男');
('13113', '朱琳', '女');('13611', '郝蕾', '女');
('14109', '张青平', '男');('14114', '高悦', '女');
('15115', '马俊', '男');('16116', '刘瑶', '女');
('17117', '陈鑫', '男');('18118', '杨雪', '女');
('19119', '黄琪', '女');('20120', '吴波', '男');
(4)学生实体:(student_id,student_name,student_sex,specialization,class,dormitory_id,ssz_name,capacity,tel1)
('230201131', '李华', '男','自动化', '2302011', '01101', '李华', 4, '158xxxx1111');
('230201109', '张华', '女', '自动化', '2302011', '02102', '张华', 3, '158xxxx2222');
('230207207', '王敏', '女', '软件工程', '2302072', '03103', '王敏', 4, '158xxxx3333');
('230207344', '赵磊', '男', '软件工程', '2302073', '04104', '赵磊', 2, '158xxxx4444');
('220207105', '孙悦', '女', '软件工程', '2302071', '05105', '孙悦', 3, '158xxxx5555');
('230207106', '周婷', '女', '软件工程', '2302072', '06106', '周婷', 4, '158xxxx6666');
('230206107', '吴昊', '男', '计算机科学', '2302061', '07107', '吴昊', 2, '158xxxx7777');
('230206608', '郑怡', '女', '计算机科学', '2302066', '08108', '郑怡', 3, '158xxxx8888');
('230206614', '陈晨', '男', '计算机科学', '2302066', '09109', '陈晨', 4, '158xxxx9999');
('230208105', '林佳', '女', '网络工程', '2302081', '10110', '林佳', 2, '158xxxx0000');
('230101011', '徐萌', '女', '网络工程', '2302081', '11111', '徐萌', 3, '158xxxx1234');
('230208122', '胡晓', '男', '网络工程', '2302081', '12112', '胡晓', 4, '158xxxx5678');
('230208143', '王强', '男', '网络工程', '2302081', '12211', '王强', 4, '158xxxx9012');
('230202110', '朱琳', '女', '信息安全', '2302021', '13113', '朱琳', 2, '158xxxx3456');
('230102106', '高悦', '女', '信息安全', '2301021', '14114', '高悦', 3, '158xxxx2345');
('230105227', '马俊', '男', '智能制造', '2301052', '15115', '马俊', 4, '158xxxx6789');
('230105108', '刘瑶', '女', '智能制造', '2301051', '16116', '刘瑶', 2, '158xxxx0123');
('230105119', '陈鑫', '男', '智能制造', '2301051', '17117', '陈鑫', 3, '158xxxx4567');
('230301120', '吴波', '男', '会计', '2303011', '20120', '吴波', 3, '158xxxx8901');
('220207141', '张青平', '男', '软件工程', '2202071','14109','张青平',4,'189xxxx9156');
('220207122', '李治', '男', '软件工程', '2202071','14109','张青平',4,'159xxxx0370');
('220207133', '王刚', '男', '软件工程', '2202071','14109','张青平',4,'179xxxx9016');
('220207117', '陈赫', '男', '软件工程', '2202071','14109','张青平',4,'189xxxx6356');
('220302131', '王强', '男', '工商管理', '2203021','12211','王强',1,'179xxxx6250');
('220301145', '郑凯', '男', '英语', '2203011','11501', '郑凯',2,'139xxxx6828');
('220100101', '郝蕾', '女', '飞行器制造', '2201001', '13611', '郝蕾', 2, '139xxxx5167');
('220501141', '章子怡',' 女','表演艺术', '2205011', '13611', '郝蕾', 2, '198xxxx6156');
(5)宿舍维修实体:(repair_id,staff_id,dormitory_id,sg_id,ssz_id,time1,time2,breakdown,examine_state)
('001','01','14109','036','14109','2023-12-16','2024-01-03','电风扇不转','已完成');
('002','16','12211','021','12211','2024-02-26','2024-03-02','门锁故障','已完成');
('003','31','11501','016','11501','2024-02-28','2024-03-04','空调不制冷','已完成');
('004','02','13611','033','13611','2024-05-08','2024-06-03','灯管损坏','已完成');
('005','41','02102','003','02102','2024-05-28','2024-06-03','窗户玻璃破碎','已完成');
('006','41','15115','012','15115','2024-06-08','','窗户玻璃破碎','未审核');
('007','07','04104','005','04104','2024-06-10','','空调不制冷','未审核');
('008','11','20120','045','20120','2024-06-21','','门锁故障','未审核');
('009','23','11111','016','11111','2024-06-24','','水龙头关不上','未审核');
('010','01','17117','050','17117','2024-06-16','','电风扇不转','未审核');
(6)维修人员实体:(staff_id,staff_name,staff_sex,tel2)
('01','高齐强','男','137xxxx5678');
('02','陈龙','男','136xxxx6908');
('07','周晨阳','男','138xxxx9108');
('11','李国强','男','139xxxx6118');
('16','徐恒','女','186xxxx2369');
('23','周一仪','女','189xxxx6918');
('31','高盛','男','175xxxx0921');
('41','齐任贤','男','139xxxx1569');
二、概念结构设计
2.1用E-R图表示各实体之间的联系
如图2.1
三、逻辑设计
3.1关系设计
1.dormitory表与student表存在一对多关系,一个宿舍可以居住多个学生，一个学生只能居住在一个宿舍。
2.suguan表与dormitory表存在一对多关系，一个宿管负责管理多个宿舍，一个宿舍由一个宿管负责。
3.sushezhang表与dormitory表存在一对一关系，一个宿舍拥有一个宿舍长，一个宿舍长负责一个宿舍。
4.student表与sushezhang表存在一对多关系，一个宿舍长对应多个学生，一个学生属于一个宿舍长。
5.dormitory_repair表与dormitory表存在一对多关系，一个宿舍可能拥有多次维修记录，一次维修针对一个宿舍。
6.dormitory_repair表与suguan表存在一对多关系，一个宿管可能处理多次维修，一次维修由一个宿管处理。
7.dormitory_repair表与sushezhang表存在一对多关系，一个宿舍长可能申报多次维修，一次维修由一个宿舍长申报。
8.dormitory_repair表与repair_staff表存在一对多关系，一个维修人员可能进行多次维修，一次维修由一个维修人员进行。
3.2约束设置
1.dormitory_id(dormitory表):非空(not null)，唯一标识每个宿舍。
2.sg_id(suguan表):主键(primary key)，非空(not null)，唯一标识每个宿管。
3.ssz_id(sushezhang表):主键(primary key)，非空(not null)，唯一标识每个宿舍长。
4.student_id(student表):主键(primary key)，非空(not null)，唯一标识每个学生。
5.repair_id(dormitory_repair表):自增主键(AUTO_INCREMENT，primary key)，非空(not null)，唯一标识每个维修记录。
6.ssz_id(dormitory_repair表):外键(foreign key)，指向sushezhang表的 ssz_id，并设置级联更新。
7.staff_id(repair_staff表):主键(primary key)，非空(not null)，唯一标识每个维修人员。
四、物理结构设计
4.1表的创建
(1)create table dormitory( #宿舍信息表
dormitory_id varchar(15) not null, #宿舍号
capacity int, #宿舍人数
bed_id int, #床号
student_name varchar(20), #姓名
student_sex varchar(5) #性别
);
(2)create table suguan( #宿管信息表
sg_id varchar(15) primary key, #宿管人员编号
sg_name varchar(20), #宿管姓名
sg_sex varchar(5), #宿管性别
address varchar(40) #宿舍地址(宿管负责的宿舍)
);
(3)create table sushezhang( #宿舍长信息表
ssz_id varchar(15), #宿舍长编号(跟宿舍号相同)
ssz_name varchar(20), #宿舍长姓名
ssz_sex varchar(5), #宿舍长性别
constraint pk_id1 primary key(ssz_id)
);
(4)create table student( #学生信息表
student_id varchar(15), #学号
student_name varchar(20), #姓名
student_sex varchar(5), #性别
specialization varchar(10), #专业
class varchar(20), #班级
dormitory_id varchar(15), #宿舍号
ssz_name varchar(15), #宿舍长姓名
capacity int, #宿舍人数
tel1 varchar(20), #电话
constraint pk_id2 primary key(student_id)
);
(5)create table dormitory_repair( #宿舍维修表
repair_id int AUTO_INCREMENT PRIMARY KEY, #维修编号
staff_id varchar(15), #维修人员工号
dormitory_id varchar(15), #宿舍号
sg_id varchar(15), #宿管人员编号
ssz_id varchar(15), #宿舍长编号
time1 date, #报修时间
time2 varchar(15), #维修时间
breakdown varchar(100), #损坏的地方
examine_state varchar(16), #审核状态
constraint fk_id
foreign key(ssz_id) references sushezhang(ssz_id)
on update cascade
);
(6)create table repair_staff( #维修人员信息表
staff_id varchar(15) PRIMARY KEY, #维修人员工号
staff_name varchar(20), #维修人员姓名
staff_sex varchar(5), #维修人员性别
tel2 varchar(20) #维修人员电话号码
);
4.2创建视图
(1)create view student_dormitory_view as
select s.student_id, s.student_name, s.student_sex, d.dormitory_id, d.capacity
from student s
join dormitory d on s.dormitory_id = d.dormitory_id;
(2)create view dormitory_repair_detail_view as
select dr.repair_id, dr.time1, dr.time2, dr.breakdown, dr.examine_state, rs.staff_name, sg.sg_name, ssh.ssz_name
from dormitory_repair dr
join repair_staff rs on dr.staff_id = rs.staff_id
join suguan sg on dr.sg_id = sg.sg_id
join sushezhang ssh on dr.ssz_id = ssh.ssz_id;
(3)create view suguan_dormitory_view as
select sg.sg_id, sg.sg_name, sg.sg_sex, d.dormitory_id, d.capacity
from suguan sg
join dormitory d on sg.address = d.dormitory_id;
4.3创建索引
(1)create index idx_student_id on student(student_id);
(2)create index idx_dormitory_id on dormitory(dormitory_id);
(3)create index idx_sg_id on suguan(sg_id);
五、代码清单
5.1数据查询
(1)单表查询(从 student 表中查询所有软件工程专业的学生)
select * from student where specialization='软件工程';(2)单表查询(从repair_staff表中查询男性维修人员)
select * from repair_staff where staff_sex='男';(3)多表查询(每个宿舍长负责的宿舍的维修记录以及维修人员信息)
select sz.ssz_id, sz.ssz_name, dr.repair_id, dr.breakdown, rs.staff_name
from sushezhang sz
join dormitory_repair dr on sz.ssz_id = dr.ssz_id
join repair_staff rs on dr.staff_id = rs.staff_id;(4)多表查询(查询维修人员维修过的宿舍中的宿舍长信息)
select rs.staff_id, ssh.ssz_id, ssh.ssz_name
from repair_staff rs
join dormitory_repair dr on rs.staff_id = dr.staff_id
join sushezhang ssh on dr.ssz_id = ssh.ssz_id;(5)嵌套查询(查询负责有未审核维修记录的宿舍的宿管姓名)
select sg_name from suguan
where sg_id in (
select sg_id
from dormitory_repair
where examine_state = '未审核'
);(6)嵌套查询(查询维修状态为未审核且所属宿舍有女生居住的维修记录)
select * from dormitory_repair
where examine_state='未审核'
and dormitory_id in
(select dormitory_id from dormitory where student_sex='女');5.2数据插入
(1)插入宿舍信息表(dormitory)的数据
insert into dormitory value('01101', 4, 1, '李华', '男');
insert into dormitory value('02102', 3, 2, '张华', '女');
insert into dormitory value('03103', 4, 3, '王敏', '女');
insert into dormitory value('04104', 2, 1, '赵磊', '男');
insert into dormitory value('05105', 3, 2, '孙悦', '女');
insert into dormitory value('06106', 4, 3, '周婷', '女');
insert into dormitory value('07107', 2, 1, '吴昊', '男');
insert into dormitory value('08108', 3, 2, '郑怡', '女');
insert into dormitory value('09109', 4, 3, '陈晨', '男');
insert into dormitory value('10110', 2, 1, '林佳', '女');
insert into dormitory value('11111', 3, 2, '徐萌', '女');
insert into dormitory value('11501', 3, 2, '郑凯', '男');
insert into dormitory value('12112', 4, 3, '胡晓', '男');
insert into dormitory value('12211', 4, 1, '王强', '男');
insert into dormitory value('13113', 2, 1, '朱琳', '女');
insert into dormitory value('13611', 2, 1, '郝蕾', '女');
insert into dormitory value('13611', 2, 4, '章子怡', '女');
insert into dormitory value('14109', 4, 1, '张青平', '男');
insert into dormitory value('14109', 4, 2, '李治', '男');
insert into dormitory value('14109', 4, 3, '王刚', '男');
insert into dormitory value('14109', 4, 4, '陈赫', '男');
insert into dormitory value('14114', 3, 2, '高悦', '女');
insert into dormitory value('15115', 4, 3, '马俊', '男');
insert into dormitory value('16116', 2, 1, '刘瑶', '女');
insert into dormitory value('17117', 3, 2, '陈鑫', '男');
insert into dormitory value('18118', 4, 3, '杨雪', '女');
insert into dormitory value('19119', 2, 1, '黄琪', '女');
insert into dormitory value('20120', 3, 2, '吴波', '男');(2)插入宿管信息表(suguan)的数据
insert into suguan value('001', '李翠红', '女', '1栋');
insert into suguan value('002', '那英', '女', '1栋');
insert into suguan value('003', '陈美丽', '女', '2栋');
insert into suguan value('004', '赵晓峰', '男', '3栋');
insert into suguan value('005', '孙雅琴', '女', '4栋');
insert into suguan value('006', '吴嘉瑞', '男', '5栋');
insert into suguan value('007', '周慧敏', '女', '6栋');
insert into suguan value('008', '郑俊辉', '男', '7栋');
insert into suguan value('009', '林晓燕', '女', '8栋');
insert into suguan value('010', '徐明轩', '男', '9栋');
insert into suguan value('011', '胡梦琪', '女', '10栋');
insert into suguan value('012', '朱雨欣', '女', '15栋');
insert into suguan value('016','王大海', '男', '11栋');
insert into suguan value('021', '张玲珑', '女', '12栋');
insert into suguan value('033', '肖月', '女', '13栋');
insert into suguan value('036', '肖丽', '女', '14栋');
insert into suguan value('045', '刘宝强', '男', '20栋');(3)插入宿舍长信息表(sushezhang)的数据
insert into sushezhang value('01101', '李华', '男');
insert into sushezhang value('01102', '李勇', '男');
insert into sushezhang value('02102', '张华', '女');
insert into sushezhang value('02403', '张敏', '女');
insert into sushezhang value('03103', '王敏', '女');
insert into sushezhang value('04104', '赵磊', '男');
insert into sushezhang value('05105', '孙悦', '女');
insert into sushezhang value('06106', '周婷', '女');
insert into sushezhang value('07107', '吴昊', '男');
insert into sushezhang value('08108', '郑怡', '女');
insert into sushezhang value('09109', '陈晨', '男');
insert into sushezhang value('10110', '林佳', '女');
insert into sushezhang value('11111', '徐萌', '女');
insert into sushezhang value('11501', '郑凯', '男');
insert into sushezhang value('12112', '胡晓', '男');
insert into sushezhang value('12211', '王强', '男');
insert into sushezhang value('13113', '朱琳', '女');
insert into sushezhang value('13611', '郝蕾', '女');
insert into sushezhang value('14109', '张青平', '男');
insert into sushezhang value('14114', '高悦', '女');
insert into sushezhang value('15115', '马俊', '男');
insert into sushezhang value('16116', '刘瑶', '女');
insert into sushezhang value('17117', '陈鑫', '男');
insert into sushezhang value('18118', '杨雪', '女');
insert into sushezhang value('19119', '黄琪', '女');
insert into sushezhang value('20120', '吴波', '男');(4)插入学生信息表(student)的数据
insert into student value('230201131', '李华', '男','自动化', '2302011', '01101', '李华', 4, '158xxxx1111');
insert into student value('230201109', '张华', '女', '自动化', '2302011', '02102', '张华', 3, '158xxxx2222');
insert into student value('230207207', '王敏', '女', '软件工程', '2302072', '03103', '王敏', 4, '158xxxx3333');
insert into student value('230207344', '赵磊', '男', '软件工程', '2302073', '04104', '赵磊', 2, '158xxxx4444');
insert into student value('220207105', '孙悦', '女', '软件工程', '2302071', '05105', '孙悦', 3, '158xxxx5555');
insert into student value('230207106', '周婷', '女', '软件工程', '2302072', '06106', '周婷', 4, '158xxxx6666');
insert into student value('230206107', '吴昊', '男', '计算机科学', '2302061', '07107', '吴昊', 2, '158xxxx7777');
insert into student value('230206608', '郑怡', '女', '计算机科学', '2302066', '08108', '郑怡', 3, '158xxxx8888');
insert into student value('230206614', '陈晨', '男', '计算机科学', '2302066', '09109', '陈晨', 4, '158xxxx9999');
insert into student value('230208105', '林佳', '女', '网络工程', '2302081', '10110', '林佳', 2, '158xxxx0000');
insert into student value('230101011', '徐萌', '女', '网络工程', '2302081', '11111', '徐萌', 3, '158xxxx1234');
insert into student value('230208122', '胡晓', '男', '网络工程', '2302081', '12112', '胡晓', 4, '158xxxx5678');
insert into student value('230208143', '王强', '男', '网络工程', '2302081', '12211', '王强', 4, '158xxxx9012');
insert into student value('230202110', '朱琳', '女', '信息安全', '2302021', '13113', '朱琳', 2, '158xxxx3456');
insert into student value('230102106', '高悦', '女', '信息安全', '2301021', '14114', '高悦', 3, '158xxxx2345');
insert into student value('230105227', '马俊', '男', '智能制造', '2301052', '15115', '马俊', 4, '158xxxx6789');
insert into student value('230105108', '刘瑶', '女', '智能制造', '2301051', '16116', '刘瑶', 2, '158xxxx0123');
insert into student value('230105119', '陈鑫', '男', '智能制造', '2301051', '17117', '陈鑫', 3, '158xxxx4567');
insert into student value('230301120', '吴波', '男', '会计', '2303011', '20120', '吴波', 3, '158xxxx8901');
insert into student value('220207141', '张青平', '男', '软件工程', '2202071','14109','张青平',4,'189xxxx9156');
insert into student value('220207122', '李治', '男', '软件工程', '2202071','14109','张青平',4,'159xxxx0370');
insert into student value('220207133', '王刚', '男', '软件工程', '2202071','14109','张青平',4,'179xxxx9016');
insert into student value('220207117', '陈赫', '男', '软件工程', '2202071','14109','张青平',4,'189xxxx6356');
insert into student value('220302131', '王强', '男', '工商管理', '2203021','12211','王强',1,'179xxxx6250');
insert into student value('220301145', '郑凯', '男', '英语', '2203011','11501', '郑凯',2,'139xxxx6828');
insert into student value('220100101', '郝蕾', '女', '飞行器制造', '2201001', '13611', '郝蕾', 2, '139xxxx5167');
insert into student value('220501141', '章子怡',' 女','表演艺术', '2205011', '13611', '郝蕾', 2, '198xxxx6156');(5)插入宿舍维修表(dormitory_repair)的数据
insert into dormitory_repair value('001','01','14109','036','14109','2023-12-16','2024-01-03','电风扇不转','已完成');
insert into dormitory_repair value('002','16','12211','021','12211','2024-02-26','2024-03-02','门锁故障','已完成');
insert into dormitory_repair value('003','31','11501','016','11501','2024-02-28','2024-03-04','空调不制冷','已完成');
insert into dormitory_repair value('004','02','13611','033','13611','2024-05-08','2024-06-03','灯管损坏','已完成');
insert into dormitory_repair value('005','41','02102','003','02102','2024-05-28','2024-06-03','窗户玻璃破碎','已完成');
insert into dormitory_repair value('006','41','15115','012','15115','2024-06-08','','窗户玻璃破碎','未审核');
insert into dormitory_repair value('007','07','04104','005','04104','2024-06-10','','空调不制冷','未审核');
insert into dormitory_repair value('008','11','20120','045','20120','2024-06-21','','门锁故障','未审核');
insert into dormitory_repair value('009','23','11111','016','11111','2024-06-24','','水龙头关不上','未审核');
insert into dormitory_repair value('010','01','17117','050','17117','2024-06-16','','电风扇不转','未审核');(6)插入维修人员信息表(repair_staff)的数据
insert into repair_staff value('01','高齐强','男','137xxxx5678');
insert into repair_staff value('02','陈龙','男','136xxxx6908');
insert into repair_staff value('07','周晨阳','男','138xxxx9108');
insert into repair_staff value('11','李国强','男','139xxxx6118');
insert into repair_staff value('16','徐恒','女','186xxxx2369');
insert into repair_staff value('23','周一仪','女','189xxxx6918');
insert into repair_staff value('31','高盛','男','175xxxx0921');
insert into repair_staff value('41','齐任贤','男','139xxxx1569');
5.3数据更新
(1)更新student表中某学生的专业
update student set specialization='计算机科学'
where student_id='220207122';(2)在dormitory表中修改某宿舍的宿舍号
update dormitory set dormitory_id='15203' where dormitory_id='14109';(3)更改suguan表中宿管的负责地址
update suguan set address='2栋' where sg_id='002';(4)更新dormitory_repair表中的维修人员工号
update dormitory_repair set staff_id='05' where repair_id='003';(5)把repair_staff表中维修人员的电话号码修改
update repair_staff set tel2 ='156xxxx8790' where staff_id ='16';(6)在student表中调整某学生的学号
update student set student_id ='220207155' where student_name='陈赫';(7)修改dormitory表中某宿舍的学生姓名
update dormitory set student_name ='刘梅' where dormitory_id='11501';(8)调整dormitory_repair表中的审核状态
update dormitory_repair set examine_state='审核中'
where repair_id='004';(9)更改dormitory_repair表中的损坏描述
update dormitory_repair set breakdown='水龙头漏水'
where repair_id='002';(10)更新student表中某宿舍学生的性别
update student set student_sex='女' where dormitory_id='13611';(11)更新student表中某个学生的电话号码
update student set tel1='138xxxx5678' where student_id='220207141';(12)将dormitory表中某宿舍的人数加 1
update dormitory set capacity=capacity+1 where dormitory_id='14109';
5.4数据删除
(1)删除student表中学号为220207141的学生记录
delete from student where student_id='220207141';(2)删除dormitory表中宿舍号为14109的宿舍记录：
delete from dormitory where dormitory_id='14109';(3)删除dormitory_repair表中维修编号为001的维修记录：
delete from dormitory_repair where repair_id='001';
5.5触发器
(1)在修改sushezhang表中的ssz_name之后级联地、自动地修改student表中的ssz_name(宿舍长姓名)
create trigger tr_ssz_student
after update
on sushezhang
for each row
update student set ssz_name=new.ssz_name
where ssz_name=old.ssz_name;(2)在删除student表中的一条记录之后级联删除地、自动地删除dormitory表中的记录
create trigger tr_delete_1
after delete
on student
for each row
delete from dormitory where student_name=old.student_name;(3)当学生从宿舍搬出时，更新宿舍的宿舍人数
delimiter //
create trigger update_info_when_student_move_out
after delete on student
for each row
begin
update dormitory d set d.capacity=d.capacity-1
where d.dormitory_id=old.dormitory_id;
end //
delimiter //(4)当在suguan表中插入新记录时，检查宿舍地址是否已存在其他宿管负责
delimiter //
create trigger tr_suguan_insert after insert on suguan
for each row
begin
if exists (select 1 from suguan where address = new.address and sg_id!= new.sg_id) then
signal sqlstate '45000' set message_text = '该宿舍地址已有其他宿管负责';
end if;
end//
delimiter ;5.6存储过程及存储函数
(1)存储过程:从student表中查询出宿舍号为'14109'的学生的学号、姓名、性别和专业信息。
delimiter @@
create procedure student_p()
begin
select student_id,student_name,student_sex,specialization from student
where dormitory_id='14109';
end @@(2)存储过程:获取所有男生宿舍的信息
delimiter //
create procedure get_male_dormitories()
begin
select * from dormitory where student_sex = '男';
end //(3)存储过程:要获取每个宿舍的学生人数并打印出来;通过游标遍历student表中不同的宿舍dormitory_id,对于每个宿舍,计算该宿舍的学生人数,并打印出类似“宿舍 [宿舍号]的学生人数为:[人数]”这样的信息;当游标遍历完所有数据后(即没有更多数据可获取时),通过continue handler将done变量设置为 true,然后通过if done的判断退出循环,最后关闭游标。
delimiter //
create procedure print_student_count_by_dormitory()
begin
declare done int default false;
declare dormitory_id1 varchar(15);
declare student_count int;
declare cur cursor for select distinct dormitory_id from student;
declare continue handler for not found set done=flase;
set done=true;
set student_count=0;
open cur;
read_loop: loop
fetch cur into dormitory_id1;
if done then
set student_count=0;
select concat('宿舍',dormitory_id1,'的学生人数为: ', student_count);
else
leave read_loop;
end if;
end loop;
close cur;
end//
delimiter ;(4)存储函数:根据所给的宿舍长id(ssz_id),函数返回该宿舍长的姓名(ssz_name)。
delimiter @@
create function ssz_name_fn(sushez_id varchar(15))
returns varchar(20)
begin
return(select ssz_name from sushezhang where sushez_id=ssz_id);
end@@(5)存储函数:根据所给的宿舍号(dormitory_id),函数返回该宿舍的损坏的地方(breakdown)、报修时间(time1)和维修时间(time2)。
delimiter @@
create function breakdown_fn(sushe_id varchar(15))
returns varchar(100)
begin
return(select breakdown from dormitory_repair where sushe_id=dormitory_id);
end @@(6)存储函数:接收一个宿舍的dormitory_id作为参数,通过在student表中查询该宿舍的记录数量,并将结果存储在count变量中,最后返回这个数量。
delimiter @@
create function get_student_count_by_dormitory(dormitory_id varchar(15))
returns int
begin
declare count int;
select count(*) into count from student
where student.dormitory_id=dormitory_id;
return count;
end @@
六、总结与体会
在完成 MySQL 学生宿舍管理系统的开发过程中，我获得了许多宝贵的经验和深刻的体会。
首先，系统的规划和设计是项目成功的关键。在开始编码之前，需要对系统的功能需求、数据库结构、用户界面等进行详细的规划和设计。通过前期的充分准备，能够避免在开发过程中出现重大的结构调整和功能缺失，提高开发效率和质量。
在数据库设计方面，我深刻理解了数据的完整性、一致性和规范化的重要性。合理设计数据表的结构，建立正确的主键、外键关系，以及适当的索引，对于提高数据库的性能和数据的准确性至关重要。同时，通过对数据的分析和优化，减少了数据冗余，提高了查询和更新的效率。
在编码实现过程中，我不断提升了自己的 MySQL 编程技能。熟练掌握了数据的插入、查询、更新和删除操作，以及如何使用存储过程、触发器和视图来优化系统的功能和性能。同时，也学会了如何处理复杂的关联查询和数据聚合，以满足系统的各种业务需求。
通过这次项目开发，我不仅掌握了 MySQL 数据库的应用开发技术，还提高了自己的问题解决能力、逻辑思维能力和团队协作能力。同时，也认识到了自己在技术和知识方面的不足之处，为今后的学习和工作指明了方向。
在未来的学习和工作中，我将继续深入学习数据库技术和相关知识，不断提升自己的技术水平。同时，也将更加注重系统的可扩展性、可维护性和用户体验，努力开发出更加优秀的软件系统。
七、参考文献
数据库原理及应用（MYSQL版）清华大学出版社
DAMO开发者矩阵，由阿里巴巴达摩院和中国互联网协会联合发起，致力于探讨最前沿的技术趋势与应用成果，搭建高质量的交流与分享平台，推动技术创新与产业应用链接，围绕“人工智能与新型计算”构建开放共享的开发者生态。
更多推荐
21
0- 0
已为社区贡献2条内容
所有评论(0)