Hexo
0%

学校教室管理信息系统

Posted on Edited on

数据库部分

创建数据库

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
--创建数据库School Classroom Management Information System-SCMIS
create database SCMIS
on primary
(
name='f1',
filename='F:\sqlserver\sqlwj\scmis\f1.mdf',
size=3mb,
maxsize=unlimited,
filegrowth=3%
),
(
name='f2',
filename='F:\sqlserver\sqlwj\scmis\f2.ndf',
size=10mb,
maxsize=500mb,
filegrowth=6mb
)
log on
(
name='f3',
filename='F:\sqlserver\sqlwj\scmis\f3.ldf',
size=1mb,
maxsize=unlimited,
filegrowth=2%
)

创建教师信息表

1
2
3
4
5
6
7
8
9
10
--创建教师信息表
create table 教师信息
(
教师编号 varchar(15) not null,
教师姓名 varchar(15) not null,
性别 varchar(5) not null,
所属院系 varchar(10) not null,
职称 varchar(15) not null,
身份证号 varchar(18) not null
)

创建学生信息表

1
2
3
4
5
6
7
--创建学生信息表
create table 学生信息
(
学号 varchar(9) not null,
院系号 varchar(5) not null,
身份证号 varchar(18) not null
)

创建教室信息

1
2
3
4
5
6
7
--创建教室信息表
create table 教室信息
(
教室编号 varchar(5) not null,
教学楼编号 varchar(2) not null,
楼层号 varchar(2)
)

创建教学楼信息表

1
2
3
4
5
6
--创建教学楼信息表
create table 教学楼信息
(
教学楼名称 varchar(10) not null,
教学楼编号 varchar(5) not null
)

有个信息不匹配，进行修改

1
2
alter table 教室信息
alter column 教学楼编号 varchar(5) not null

创建教室使用信息表

1
2
3
4
5
6
7
8
--创建教室使用信息表
create table 教室使用信息
(
教学楼编号 varchar(5) not null,
教室编号 varchar(5) not null,
时间 varchar(10) not null,
状态 varchar(10) not null
)

创建课程信息表

1
2
3
4
5
6
7
--创建课程信息表
create table 课程信息
(
课程名称 varchar(15) not null,
课程编号 varchar(10) not null,
教师编号 varchar(15) not null,
)

创建课程时间表

1
2
3
4
5
6
7
8
--创建课程时间表
create table 课程时间
(
课程编号 varchar(10) not null,
教师编号 varchar(15) not null,
教师名称 varchar(10) not null,
上课时间 varchar(10) not null
)

修改

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
alter table 学生信息 add 密码 varchar(15)

alter table 课程时间 add 上课教室 varchar(5)

alter table 教室使用信息 add 开设课程 varchar(15) default '无'

alter table 学生信息 add 姓名 varchar(15)

alter table 教师信息
alter column 所属院系 varchar(15) 

alter table 学生信息
alter column 院系号 varchar(15) 

alter table 教室使用信息 add 课程编号 varchar(10)

约束

1
2
3
4
5
--将教师信息表中教师编号设置为主键约束，身份证号设为唯一约束
alter table 教师信息 
add constraint PK_教师编号 primary key (教师编号)
alter table 教师信息
add constraint UQ_身份证号 unique(身份证号)
1
2
3
4
5
--将学生信息表中学号设置为主键约束，身份证号设为唯一约束
alter table 学生信息 
add constraint PK_学号 primary key (学号)
alter table 学生信息
add constraint UQ_身份证号学生 unique(身份证号)
1
2
3
--将教室信息中教室编号设置为主键
alter table 教室信息
add constraint PK_教室编号 primary key(教室编号)
1
2
3
--将教学楼信息中教学楼编号设置为主键
alter table 教学楼信息
add constraint PK_教学楼编号 primary key(教学楼编号)
1
2
3
--将教学楼信息中的教学楼编号与教室信息中的教学楼编号添加外键
alter table 教室信息
add constraint FK_教学楼编号 foreign key(教学楼编号)references 教学楼信息(教学楼编号)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
--将教室使用信息表中教学楼编号与教学楼信息表中的教学楼编号添加外键
alter table 教室使用信息
add constraint FK_教学楼编号教室使用信息 foreign key (教学楼编号)references 教学楼信息(教学楼编号)

--将教室使用信息表中教室编号与教室信息表中的教室编号添加外键
alter table 教室使用信息
add constraint FK_教室编号教室使用信息 foreign key (教室编号)references 教室信息(教室编号)

--将教室使用信息表中教师状态做约束
alter table 教室使用信息
add constraint CK_状态 check(状态='有课' or 状态='讲座' or 状态='活动' or 状态='空闲' or 状态='其他')//删除掉该约束

--将教室使用信息表中教室状态添加默认约束空闲
alter table 教室使用信息
add constraint DF_状态 default '空闲' for 状态
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
--对课程信息表中课程编号添加主键约束
alter table 课程信息
　add constraint PK_课程编号 primary key (课程编号)




--对课程信息表中教师编号添加外键约束
alter table 课程信息
add constraint FK_教师编号课程信息 foreign key(教师编号)references 教师信息(教师编号)

--对课程时间表中教师编号添加外键约束
alter table 课程时间
add constraint FK_教师编号课程时间 foreign key(教师编号)references 教师信息(教师编号)

--对课程时间表中课程编号添加外键约束
alter table 课程时间
add constraint FK_课程编号课程时间 foreign key(课程编号)references 课程信息(课程编号)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
--对教师信息中的性别添加约束
　　alter table 教师信息
　　add constraint CK_性别教师 check (性别='男' or 性别='女')

--对教室使用信息的时间进行约束
  alter table 教室使用信息
  add constraint CK_时间 check (时间='周一一二' or 时间='周一三四' or 时间='周一五六' or 时间='周一七八' or 
  时间='周二一二' or 时间='周二三四' or 时间='周二五六' or 时间='周二七八' or 
  时间='周三一二' or 时间='周三三四' or 时间='周三五六' or 时间='周三七八' or 
  时间='周四一二' or 时间='周四三四' or 时间='周四五六' or 时间='周四七八' or 
  时间='周五一二' or 时间='周五三四' or 时间='周五五六' or 时间='周五七八' or 
  时间='周六一二' or 时间='周六三四' or 时间='周六五六' or 时间='周六七八' or 
  时间='周日一二' or 时间='周日三四' or 时间='周日五六' or 时间='周日七八' 
  )

修改

1
2
3
4
5
6
7
8
alter table 课程时间
add constraint FK_教室 foreign key(上课教室) references 教室信息(教室编号)

alter table 教室使用信息
alter column 状态 varchar(15) not null

alter table 学生信息
add constraint UQ_ID unique (身份证号)

插入的信息

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
insert into 教师信息
 values('001','张三','男','计算机','教授','123456789')
 insert into 教师信息
 values('002','李四','女','地理与海洋','教授','123123123')
 insert into 教师信息
values('003','王五','男','计算机','教授','456456456')

 select * from 教师信息

insert into 教学楼信息
values('仙一','001')

insert into 教学楼信息
values('仙二','002')
select * from 教学楼信息

insert into 教室信息
values('1101','001','1')
insert into 教室信息
values('1102','001','1')
insert into 教室信息
values('1103','001','1')
insert into 教室信息
values('1104','001','1')
insert into 教室信息
values('1201','001','2')
insert into 教室信息
values('1202','001','2')
insert into 教室信息
values('1203','001','2')
insert into 教室信息
values('1204','001','2')

insert into 教室信息
values('2101','002','1')
insert into 教室信息
values('2102','002','1')
insert into 教室信息
values('2103','002','1')
insert into 教室信息
values('2104','002','1')
insert into 教室信息
values('2201','002','2')
insert into 教室信息
values('2202','002','2')
insert into 教室信息
values('2203','002','2')
insert into 教室信息
values('2204','002','2')
select * from 教室信息

插入课程信息

1
2
3
4
5
6
7
8
exec information_entry '数据库','001','周一三四','张三','001','001','1102'
exec information_entry '数据库','001','周二三四','张三','001','001','1102'
exec information_entry '地理综合认知','002','周三三四','李四','002','001','1202'
exec information_entry '地理综合认知','002','周四三四','李四','002','001','1202'
exec information_entry '机器学习','003','周一五六','张三','001','002','2102'
exec information_entry '机器学习','003','周二五六','张三','001','002','2102'
exec information_entry '机器学习','004','周四七八','王五','003','002','2202'
exec information_entry '机器学习','004','周五七八','王五','003','002','2202'

存储

插入课程信息存储

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
--插入课程信息

go
create procedure information_entry
 @coursename varchar(15),--输入参数，课程名称
 @coursenumber varchar(10),--输入参数，课程编号
 @coursetime varchar(10),--输入参数，课程时间
 @teachername varchar(10),--输入参数，教师名称
 @teachernumber varchar(15),--输入参数，教师编号
 @buildingnumber varchar(5),--输入参数，教学楼编号
 @roomnumber varchar(5)--输入参数教室编号
AS
 insert into 课程信息
 values(@coursename,@coursenumber,@teachernumber)
 insert into 课程时间
 values(@coursenumber,@teachernumber,@teachername,@coursetime)
 insert into 教室使用信息
 values(@buildingnumber,@roomnumber,@coursetime,'有课')
go

修改

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
create procedure information_entry
 @coursename varchar(15),--输入参数，课程名称
 @coursenumber varchar(10),--输入参数，课程编号
 @coursetime varchar(10),--输入参数，课程时间
 @teachername varchar(10),--输入参数，教师名称
 @teachernumber varchar(15),--输入参数，教师编号
 @buildingnumber varchar(5),--输入参数，教学楼编号
 @roomnumber varchar(5)--输入参数教室编号
AS
 insert into 课程信息
 values(@coursename,@coursenumber,@teachernumber)
 insert into 课程时间
 values(@coursenumber,@teachernumber,@teachername,@coursetime,@roomnumber)
 insert into 教室使用信息
 values(@buildingnumber,@roomnumber,@coursetime,'有课',@coursename,@coursenumber)
go

查询指定教室使用情况

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
--查询指定教室使用情况
create procedure classroom_query
 @room varchar(5),
 @time varchar(10),
 @state varchar(10)='空闲' output
 AS
 declare @state1 varchar(10)
 select @state=状态
 from 教室使用信息
 where 教室编号=@room and 时间=@time
 if @state IS NULL
   set  @state='空闲'
 go

修改
create procedure classroom_query
 @room varchar(5),
 @time varchar(10),
 @state varchar(10)='空闲' output
 AS

 select @state=状态
 from 教室使用信息
 where 教室编号=@room and 时间=@time
 if @state IS NULL
   set  @state='空闲'
   else if @state='有课'
   begin
    select @state=开设课程
 from 教室使用信息
 where 教室编号=@room and 时间=@time

   end
 go

查询指定课程

1
2
3
4
5
6
7
8
9
10
create procedure course_query
 @name varchar(10),
 @state varchar(10) output
 AS

 select 课程信息.课程名称,课程信息.课程编号,课程时间.教师名称,课程时间.上课时间
 from 课程时间 inner join 课程信息 on 课程信息.课程编号=课程时间.课程编号
 where 课程信息.课程名称=@name
 go

租借教室

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
go
 create procedure borrow
 @building varchar(5),
 @room varchar(5),
 @time varchar(10),
 @reason varchar(10)
AS
declare @state varchar(10)
 select @state=状态
 from 教室使用信息
 where 教室编号=@room and 时间=@time
 if @state IS NULL
 begin
   set  @state='空闲'
   print '此时教室空闲，可以借用'
   insert into 教室使用信息
   values(@building,@room,@time,@reason)
  end
else
  print '此教室被占用，正在'+@state
  go

修改为

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
declare @state varchar(10)
go
create procedure borrow
 @building varchar(5),
 @room varchar(5),
 @time varchar(10),
 @reason varchar(10),
 @state varchar(10) output
AS
declare @state1 varchar(10)
 select @state1=状态
 from 教室使用信息
 where 教室编号=@room and 时间=@time
 if @state1 IS NULL
 begin
   set  @state1='空闲'
   
   print '此时教室空闲，可以借用'
   insert into 教室使用信息
   values(@building,@room,@time,@reason,'其他','其他')
  end
else
  print '此教室被占用，正在'+@state1
  set @state=@state1
  print @state
  go

  select *from 教室使用信息
  declare @state varchar(10)
  exec borrow '001','1102','周一三四','其他',@state output
  print @state

判断密码是否正确

1
2
3
4
5
6
7
8
9
10
11
12
13
create procedure password_query
 @user varchar(9),
 @password varchar(15),
 @state varchar(10)='false' output
AS
declare @password1 varchar(15)
 select @password1=密码  from 学生信息
 where 学号=@user
 if @password1 IS NOT NULL and @password1=@password
 set @state='true'
 else
 set @state='false'
go

删除

1
2
3
4
5
6
create procedure delete_course
 @number varchar(10)
 AS
delete from 课程信息 where 课程编号=@number
delete from 教室使用信息 where 课程编号=@number
delete from 课程时间 where 课程编号=@number

编写存储插入大量数据

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
--创建循环插入1000条记录的存储过程
CREATE  PROCEDURE SP_CREATE_DATA1 
AS
declare @cnt INT=0;
while @cnt<100
begin
declare @a varchar(8)
set @a=(SELECT   LEFT(LOWER(NEWID()),8))
declare @b varchar(8)
set @b=(SELECT   LEFT(LOWER(NEWID()),9))
declare @c INT
set @c=(SELECT 1 + RAND() * 2)
    INSERT INTO 学生信息
    VALUES
    (
     @a
    
    ,CASE @c  --随机选取3个枚举值
          when 1 then '001'
          when 2 then '002'
          when 3 then '003'         
     END
	 ,@b
	  ,@a
	   ,@b
   
    );
	set @cnt=@cnt+1;
end;
    

GO
exec SP_CREATE_DATA1 

CREATE  PROCEDURE SP_CREATE_DATA3 
AS
declare @cnt INT=0;
while @cnt<100
begin
declare @a varchar(8)
set @a=(SELECT   LEFT(LOWER(NEWID()),8))
declare @b varchar(8)
set @b=(SELECT   LEFT(LOWER(NEWID()),9))
declare @c INT
set @c=(SELECT 1 + RAND() * 2)
declare @d varchar(8)
set @d=replace(str(@cnt,8),' ','0')
    INSERT INTO 学生信息
    VALUES
    (
     @d
    
    ,CASE @c  --随机选取3个枚举值
          when 1 then '001'
          when 2 then '002'
          when 3 then '003'         
     END
	 ,@b
	  ,@a
	   ,@b
   
    );
	set @cnt=@cnt+1;
end;
exec SP_CREATE_DATA3 

declare @cnt INT=1
declare @d varchar(8)
set @d=replace(str(@cnt,8),' ','0')
print @d
select * from 学生信息

视图

创建视图，查询张三老师课程

1
2
3
4
5
6
7
8
--创建视图，查询计算机学院张三老师所有课程名称
go
create VIEW view_张三
as 
select 课程名称 
from 课程信息
where 教师编号='001'
go

例子

1
2
3
4
5
6
exec information_entry '机器学习','004','周六七八','王五','003','002','2202'
select *from 教室使用信息
declare @state varchar(15)
exec classroom_query '2202','周六七八',@state output
print @state

1
2
3
declare @state varchar(10)
exec password_query '191830076','0323',@state output
print @state