宿舍管理系统1（pyhon与mysql）_宿舍管理信息系统创建存储过程mysql示意图

宿舍管理系统

前言

此次大作业共分为四个阶段，四次实验分别为数据库设计，存储过程，数据库应用开发,数据库设计与应用开发综合。

需求分析

• 对所有学生信息统计建表存储。
• 对宿管成员组信息建表存储。
• 对各楼层寝室居住信息进行建表存储。
• 对居住学生寝室动态进行跟踪记录。
• 对学生居住寝室每月卫生进行评分统计。
• 生活服务管理(水费，电费信息呈现)。

概念模型

E-R图

E-R图1

E-R图2

E-R图3

E-R图4

实体与属性关系

公寓楼（公寓号，公寓名，宿管编号）；

寝室（公寓号，寝室楼层，寝室号，寝室成员人数，寝室长学号）；

学生表（学号，性别，姓名，所属学院，班级，入住年月，公寓楼，寝室号，电话号码，班主任编号）；

老师表（班主任编号，姓名，班级，电话号码）；

宿管表（公寓号，工作编号，性别，姓名，电话号码）；

电费表（公寓号，寝室号，费用，日期）；

水费表（公寓号，寝室号，费用，日期）；

快递表（快递号，学号，学生姓名，电话，日期）；

评分表（公寓号，寝室号，评分，日期）；

更换宿舍表（学生学号，目的宿舍楼，目的宿舍编号，原宿舍楼，原宿舍编号）；

毕业表（学号，性别，姓名，所属学院，班级，退宿年月，公寓楼，电话号码）；

字典

公寓楼表(flat)

寝室表(dorm)

学生表(student)

老师表(teacher)

宿管表(manager)

电费表(electric)

水费表(water)

快递表(deliver)

评分表(mark)

更换宿舍表(change)

逻辑结构定义

flat

dorm

student

teacher

manager

electric

water

deliver

mark

change

数据实施

建立数据表

flat表

create table flat(
f_id int PRIMARY key COMMENT '公寓楼编号',
f_name varchar(32) not null COMMENT '公寓楼名字',
m_id int not null comment '宿管编号'); 
1
2
3
4

dorm表

create table dorm(
f_id int not null COMMENT '公寓楼编号',
d_floor int not null COMMENT '宿舍楼层',
d_id int not null comment '宿舍编号',
d_num int not null comment '宿舍人数',
d_s_id varchar(24) not null comment '寝室长学号' );
1
2
3
4
5
6

student表

create table student(
s_id varchar(24) PRIMARY key COMMENT '学号',
s_sex varchar(8) not null COMMENT '性别',
s_name varchar(32) not null COMMENT '姓名',
s_major varchar(32) not null comment '非空',
s_class varchar(32) not null comment '班级',
s_in_date date not null COMMENT '入住日期',
f_id int not null COMMENT '公寓楼编号',
d_id int not null comment '寝室号',
s_numb varchar(24) not null comment '电话号码',
t_id int not null COMMENT '老师编号');
1
2
3
4
5
6
7
8
9
10
11

teacher表

create table teacher(
t_id int PRIMARY key comment '老师编号',
t_name varchar(32) not null comment '老师姓名',
t_class varchar(32) not null comment '所带班级',
t_numb varchar(24) not null COMMENT '电话');
1
2
3
4
5

manager表

create table manager(
f_id int not null comment '公寓楼编号',
m_id int not null comment '工作编号',
m_sex VARCHAR(8) not null COMMENT '性别',
m_name VARCHAR(32) not null comment '姓名',
m_numb VARCHAR(24) not null comment '电话号码');
1
2
3
4
5
6

electric表

create table electric(
f_id int not null comment '公寓楼编号',
d_id int not null comment '寝室编号',
e_cost DECIMAL(7,2) not null comment '电费',
e_date date not null comment '日期'); 
1
2
3
4
5

water表

create table water(
f_id int not null comment '公寓楼编号',
d_id int not null comment '寝室编号',
w_cost DECIMAL(7,2) not null comment '水费',
w_date date not null comment '日期'); 
1
2
3
4
5

运行结果：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-W7sdeNxY-1591152473183)(7water.png)]

mark表

create table mark(
f_id int not null comment '公寓楼编号',
d_id int not null comment '寝室编号',
m_num int not null comment '得分',
m_date date not null comment '日期'); 
1
2
3
4
5

deliver表

create table deliver(
d_id varchar(32) PRIMARY key COMMENT '快递单号',
s_id varchar(24)  not null comment '学生学号',
s_name varchar(32) not null comment '姓名',
s_numb VARCHAR(24) not null comment '电话号码',
d_date date comment '存放日期');
1
2
3
4
5
6

changee表

create table changee(
s_id varchar(24) not null comment '学号',
a_f_id int not null comment '目的楼',
a_d_id int not null comment '目的寝',
s_f_id int not null comment '原楼',
s_d_id int not null comment '原寝');
1
2
3
4
5
6

gra 毕业表

create table gra(
s_id varchar(24) PRIMARY key COMMENT '学号',
s_sex varchar(8) not null COMMENT '性别',
s_name varchar(32) not null COMMENT '姓名',
s_major varchar(32) not null comment '非空',
s_class varchar(32) not null comment '班级',
s_in_date date not null COMMENT '入住日期',
f_id int not null COMMENT '公寓楼编号',
d_id int not null comment '寝室号',
s_numb varchar(24) not null comment '电话号码',
t_id int not null COMMENT '老师编号')charset utf8;
1
2
3
4
5
6
7
8
9
10
11

插入数据

-- flat表
insert into flat VALUES (1,'练剑楼',1),
												(2,'归云楼',3),
												(3,'烟雨楼',4),
												(4,'潮声楼',2),
												(5,'碧海楼',5);
								
-- dorm表

-- 试剑楼
insert into dorm VALUES (1,1,101,4,'18010541'),
						(1,2,205,4,'18010542');
-- 归云楼
insert into dorm VALUES (2,1,101,4,'18010101'),
						(2,2,201,4,'18010122');
-- 烟雨楼
insert into dorm VALUES (3,1,101,4,'18010201'),
						(3,2,201,4,'18010222');
-- 潮声楼
insert into dorm VALUES (4,1,101,4,'18010111'),
						(4,2,201,4,'18010512');
-- 碧海楼
insert into dorm VALUES (5,1,101,4,'18010116'),
						(5,2,201,4,'18010516');

-- student表
INSERT into student VALUES 
('18010101','男','郭靖','数计','18级1班','2018-09-01',2,101,'18825567788',1),
('18010102','男','李靖','数计','18级1班','2018-09-01',2,101,'15825567788',1),
('18010103','男','郭破胡','数计','18级1班','2018-09-01',2,101,'18829567788',1),
('18010104','男','易靖','数计','18级1班','2018-09-01',2,101,'18829967788',1),
													 
('18010111','女','黄蓉','数计','18级1班','2018-09-01',4,101,'18824597908',1),
('18010112','女','小龙女','数计','18级1班','2018-09-01',4,101,'18824598888',1),
('18010113','女','郭襄阳','数计','18级1班','2018-09-01',4,101,'18820597908',1),
('18010114','女','黄蓉','数计','18级1班','2018-09-01',4,101,'18824597908',1),
													  
('18010116','女','郭芙蓉','数计','18级1班','2018-09-01',5,101,'18828867766',1),
('18010117','女','瑛姑','数计','18级1班','2018-09-01',5,101,'18828809766',1),
('18010118','女','梦姑','数计','18级1班','2018-09-01',5,101,'18898809766',1),
('18010119','女','韩小青','数计','18级1班','2018-09-01',5,101,'17878809766',1),
													  
('18010122','男','洪七公','数计','18级1班','2018-09-01',2,201,'18828866666',1),
('18010123','男','黄药师','数计','18级1班','2018-09-01',2,201,'18826666666',1),
('18010124','男','欧阳锋','数计','18级1班','2018-09-01',2,201,'18828867777',1),
('18010125','男','中神通','数计','18级1班','2018-09-01',2,201,'18829999966',1),
													 													 
('18010201','男','杨过','数计','18级2班','2018-09-01',3,101,'17825567788',2),
('18010202','男','忽必烈','数计','18级2班','2018-09-01',3,101,'17800567788',2),
('18010203','男','鲁有脚','数计','18级2班','2018-09-01',3,101,'17825007788',2),
('18010204','男','杨康','数计','18级2班','2018-09-01',3,101,'17899567788',2),

('18010222','男','乔峰','数计','18级2班','2018-09-01',3,201,'18855566688',2),
('18010223','男','肖峰','数计','18级2班','2018-09-01',3,201,'18809876688',2),
('18010224','男','张无忌','数计','18级2班','2018-09-01',3,201,'17879566688',2),
('18010225','男','谢逊','数计','18级2班','2018-09-01',3,201,'18859966608',2),
													 
('18010541','男','逍遥子','数计','18级5班','2018-09-01',1,101,'15770816917',3),
('18010540','男','黄尚','数计','18级5班','2018-09-01',1,101,'15770666917',3),
('18010539','男','庄子','数计','18级5班','2018-09-01',1,101,'15770899917',3),
('18010538','男','老子','数计','18级5班','2018-09-01',1,101,'15770810017',3),
													 
('18010542','男','段誉','数计','18级5班','2018-09-01',1,205,'17870816917',3),
('18010543','男','路飞','数计','18级5班','2018-09-01',1,205,'17870810017',3),
('18010544','男','索隆','数计','18级5班','2018-09-01',1,205,'17890800917',3),
('18010545','男','乌索普','数计','18级5班','2018-09-01',1,205,'17870811917',3),
													 
('18010512','女','汶川','数计','18级5班','2018-09-01',4,201,'15770817917',3),
('18010513','女','济川','数计','18级5班','2018-09-01',4,201,'15770822917',3),
('18010514','女','发儿','数计','18级5班','2018-09-01',4,201,'15770017917',3),
('18010515','女','哎呦','数计','18级5班','2018-09-01',4,201,'15770997917',3),
													  
('18010516','女','穆念慈','数计','18级5班','2018-09-01',5,201,'15770816917',3),
('18010517','女','穆容','数计','18级5班','2018-09-01',5,201,'15770666917',3),
('18010518','女','倩倩','数计','18级5班','2018-09-01',5,201,'15770816666',3),
('18010519','女','呈呈','数计','18级5班','2018-09-01',5,201,'15770776917',3);

-- teacher表
insert into teacher VALUES (1,'张老师',1,'17879382727'),
						   (2,'李老师',2,'17879386767'),
						   (3,'吴老师',5,'17879383636');
						 
-- electric表
insert into electric VALUES (1,101,200,'2020-04-18'),
							(1,205,300,'2020-04-20'),
							(2,101,500,'2020-03-18'),
							(2,201,400,'2020-04-04'),
							(3,101,100,'2020-04-01'),
							(3,201,300,'2020-04-15'),
							(4,101,350,'2020-03-31'),
							(4,201,400,'2020-04-01'),
							(5,101,150,'2020-04-02'),
							(5,201,260,'2020-04-18');
							
-- water表
insert into water VALUES    (1,101,60,'2020-03-18'),
							(1,205,50,'2020-03-20'),
							(2,101,50,'2020-04-18'),
							(2,201,40,'2020-03-04'),
							(3,101,100,'2020-03-01'),
							(3,201,70,'2020-03-15'),
							(4,101,35,'2020-04-30'),
							(4,201,40,'2020-03-01'),
							(5,101,75,'2020-04-02'),
							(5,201,86,'2020-03-18');
-- mark表
insert into mark VALUES (1,101,9,'2020-04-18'),
						(1,205,7,'2020-04-18'),
						(2,101,9,'2020-04-18'),
						(2,201,10,'2020-04-18'),
						(3,101,6,'2020-04-18'),
						(3,201,7,'2020-04-18'),
						(4,101,8,'2020-04-18'),
						(4,201,9,'2020-04-18'),
						(5,101,7,'2020-04-18'),
						(5,201,10,'2020-04-18'),
												
						(1,101,8,'2020-04-28'),
						(1,205,9,'2020-04-28'),
						(2,101,7,'2020-04-28'),
						(2,201,8,'2020-04-28'),
						(3,101,9,'2020-04-28'),
						(3,201,5,'2020-04-28'),
						(4,101,6,'2020-04-28'),
						(4,201,8,'2020-04-28'),
						(5,101,10,'2020-04-28'),
						(5,201,9,'2020-04-28');


-- deliver表
insert into deliver values ('20204187312','18010541','逍遥子','15770816917','2020-04-18'),
						   ('20204156662','18010101','郭靖','18825567788','2020-04-22'),
						   ('20204199889','18010222','乔峰','18855566688','2020-04-20'),
						   ('20204120098','18010122','洪七公','18828866666','2020-04-22'),
				           ('20204210876','18010114','黄蓉','18824597908','2020-04-23');

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136

存储过程创建

存储过程1

in_stu()

说明：0,通过学生id删除学生信息 1，查询所有学生信息 2，添加学生信息 3，按学号查询信息；

delimiter //
create procedure in_stu(in i int,in in_s_id varchar(24),
in in_s_sex varchar(8),
in in_s_name varchar(32),
in in_s_major varchar(32),
in in_s_class VARCHAR(32),
in in_s_in_date date,
in in_f_id int,
in in_d_id int,
in in_s_numb varchar(24),
in in_t_id int)
BEGIN
  IF i = 1 THEN 
		SELECT 
		s_id as '学号',s_sex as '性别',s_name as '姓名',s_major as '学院',
		s_class as '班级',s_in_date as '入住日期',f_id as '楼号',d_id as '寝室号',
		s_numb as '电话号码',t_id as '班主任编号'
		from student;
  ELSEIF i = 0 THEN
    DELETE FROM student where s_id = in_s_id;
  ELSEIF i = 3 THEN
    SELECT 
		s_id as '学号',s_sex as '性别',s_name as '姓名',s_major as '学院',
		s_class as '班级',s_in_date as '入住日期',f_id as '楼号',d_id as '寝室号',
		s_numb as '电话号码',t_id as '班主任编号'
		from student WHERE s_id = in_s_id;
  ELSE		
		INSERT into student VALUES (in_s_id,in_s_sex,in_s_name,in_s_major,in_s_class,in_s_in_date,in_f_id,in_d_id,in_s_numb,in_t_id);

END IF;
END //

-- i为0时
call in_stu(0,'18010545',null,null,null,null,null,null,null,null,null);
-- i为1时
call in_stu(1,null,null,null,null,null,null,null,null,null,null);
-- i为2时
call in_stu(2,'1801055','男','乌索普','数计','18级5班','2018-09-01',1,205,'17890811917',3);
-- i为3时
call in_stu(3,'1801045',null,null,null,null,null,null,null,null,null);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40

运行结果：

2.

注：此处易出现存储过程字符集不符的情况

可以通过：

-- 用于修改字符集
set character_set_client = utf8;
set character_set_server = utf8;
set character_set_connection = utf8;
set character_set_database = utf8;
set character_set_results = utf8;
set collation_connection = utf8_general_ci;
set collation_database = utf8_general_ci;
set collation_server = utf8_general_ci;	
1
2
3
4
5
6
7
8
9

也可以在my.ini文件中修改。

存储过程2

in_栋楼信息
注：1查询栋楼具体信息，2查询输入公寓号的公寓全部学生信息,in_flat变量表示公寓号。

delimiter //
create procedure in_flat(in in_jud int,in in_flat int)
begin
	IF in_jud = 1 THEN
	SELECT f_id as '公寓号',f_name as '公寓名',count(d_id) as '寝室数',a as '公寓楼人数',m_id as '宿管工号' 
  FROM (SELECT f_id,count(s_id) a FROM student GROUP BY f_id) j 
  join dorm d using(f_id) 
  join flat f USING(f_id)
  GROUP BY f_id;
	ELSEIF in_jud = 2 THEN
	SELECT
  s_id as '学号',s_sex as '性别',s_name as '姓名',s_major as '学院',
	s_class as '班级',s_in_date as '入住日期',f_id as '楼号',d_id as '寝室号',
	s_numb as '电话号码',t_id as '班主任编号'
	FROM student WHERE f_id = in_flat;
  END IF;
end //
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

运行结果：

存储过程3

宿管信息查询

delimiter //
-- 1查询全部信息，2查询指定公寓宿管信息。
create PROCEDURE in_man(in i int , in in_f_id int)
BEGIN
  IF i = 1 THEN
	SELECT f_id as '公寓号',m_id as '工号',m_sex as '性别',m_name as '姓名',m_numb as '电话号码' FROM manager;
	ELSEIF i = 2 THEN
	SELECT f_id as '公寓号',m_id as '工号',m_sex as '性别',m_name as '姓名',m_numb as '电话号码'
	FROM manager WHERE f_id = in_f_id;
	end if;
END //
call in_man(1,1)
call in_man(2,1)
1
2
3
4
5
6
7
8
9
10
11
12
13

运行结果：

存储过程4

cost()

返回指定寝室的水费消耗电费消耗及总和。

注：cost(in_f_id,in_d_id) in_f_id为公寓号,in_d_id为寝室号。

delimiter //
create procedure cost(in in_f_id int,in in_d_id int)
BEGIN
  SELECT w_cost as '水费',e_cost as '电费',(w_cost+e_cost) as '总和',w_date as '日期' FROM
  (SELECT w_cost,w_date FROM water where f_id = in_f_id and d_id = in_d_id ) i join 
  (SELECT e_cost,e_date FROM electric where f_id = in_f_id and d_id = in_d_id ) j on w_date = e_date;
END //
call cost(1,101)
1
2
3
4
5
6
7
8

运行结果：

存储过程5

score()评分系统

注：1返回全部评分排名，2根据公寓楼和寝室号查询评分，3添加评分信息。

delimiter //
create procedure score(in i int,in in_f_id int,in in_d_id int,in in_m_num int,in in_m_date date)
begin
	ig i = 1 THEN
	select a as '公寓号',b as '寝室号',c as '得分',d as '日期',(@a:=@a+1) 排名 
	from (select f_id a ,d_id b ,m_num c ,m_date d FROM mark ORDER BY m_date,m_num desc) j ,(SELECT @a:=0) k ORDER BY 排名;
  ELSEif i = 2 then
	select f_id as '公寓号', d_id as '寝室号', m_num as '得分', m_date as '日期' FROM mark WHERE f_id = 1 and d_id = 101;
	else
	insert into mark VALUES (in_f_id,in_d_id,in_m_num,in_m_date);
  END IF;
end //
1
2
3
4
5
6
7
8
9
10
11
12

运行结果：

存储过程6

毕业表插入，之后用触发器删除学生表内容。

delimiter //
create procedure in_gra(in in_s_id varchar(24),
in in_s_sex varchar(8),
in in_s_name varchar(32),
in in_s_major varchar(32),
in in_s_class VARCHAR(32),
in in_s_in_date date,
in in_f_id int,
in in_d_id int,
in in_s_numb varchar(24),
in in_t_id int)
begin
  insert into gra VALUES 
	(in_s_id,in_s_sex,in_s_name,in_s_major,in_s_class,in_s_in_date,in_f_id,in_d_id,in_s_numb,in_t_id);
end //
call in_gra('18010545','男','乌索普','数计','18级5班','2018-09-01',1,205,'17890811917',3);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

运行结果：

触发器1

当毕业表有学生数据插入时，学生表删除相应的信息，对应寝室总人数减少。

delimiter //
CREATE TRIGGER stu_change AFTER INSERT ON gra FOR EACH ROW
BEGIN
	DELETE FROM student WHERE s_id = new.s_id;
	UPDATE dorm d SET d.d_num = d.d_num - 1 WHERE d.d_id = new.d_id and d.f_id = new.f_id;
END //
1
2
3
4
5
6

运行call in_gra(‘18010545’,‘男’,‘乌索普’,‘数计’,‘18级5班’,‘2018-09-01’,1,205,‘17890811917’,3);后，各表的情况：

student：成功减少了一条数据：

dorm：相应宿舍人数成功减少：