数据库大作业-学生信息管理系统_数据库大作业学生信息管理系统

软件：SQL Server；Visual Studio
语言：C#，SQL
两个身份，管理员和学生。
管理员功能：管理学生专业信息、课程信息、选课信息（增删改查），查看已注册过的同学信息（密码不可见，是乱码）以及照片。
学生功能：注册自己的信息，查看自己的信息包括专业信息、注册时的信息、选课及成绩，修改自己的密码。

在SQL Server创建数据库，在新数据库中新建需要用的表并添加数据。

create database curricula_variable_system;//创建数据库

USE curricula_variable_system;
//建表，记录注册信息的
CREATE TABLE SysUser          
 ( 
 UserID NCHAR(20) ,                          
 UserPassWord NCHAR(32) ,    /*密码32位加密*/ 
 UserSchoolID NCHAR(20) PRIMARY KEY,
 UserMobile NCHAR(11),
 UserBirthday datetime,
 UserIdentity NCHAR(20),
 UserPhoto image
 ); 
 //建表，记录登录信息的
 CREATE TABLE SysLog          
 ( 
 UserID NCHAR(20) ,                          
 DateAndTime datetime,
 UserOperation NCHAR(200)
 ); 
 //建管理员表，存管理员的账号密码
 CREATE TABLE Teacher          
 ( 
 UserID NCHAR(20) ,                          
 UserPassWord NCHAR(32) ,    /*密码32位加密*/ 
 );
 //建学生表
 CREATE TABLE Student          
 ( 
 Sno CHAR(9) PRIMARY KEY,        /* 列级完整性约束条件,Sno是主码*/                  
 Sname CHAR(20) UNIQUE,             /* Sname取唯一值*/
 Ssex CHAR(2),
 Sage SMALLINT,
 Sdept CHAR(20)
 ); 
//课程表
CREATE TABLE  Course
 ( 
 Cno CHAR(4) PRIMARY KEY,
 Cname CHAR(40),            
 Cpno CHAR(4),                                      
 Ccredit SMALLINT,
 FOREIGN KEY (Cpno) REFERENCES  Course(Cno) 
 ); 
 //选课表
 CREATE TABLE  SC
 (
 Sno CHAR(9), 
 Cno CHAR(4),  
 Grade SMALLINT,
 PRIMARY KEY (Sno,Cno),                     /* 主码由两个属性构成，必须作为表级完整性进行定义*/
 FOREIGN KEY (Sno) REFERENCES Student(Sno),  /* 表级完整性约束条件，Sno是外码，被参照表是Student */
 FOREIGN KEY (Cno)REFERENCES Course(Cno)     /* 表级完整性约束条件， Cno是外码，被参照表是Course*/
 ); 
  //插入数据
INSERT  INTO  Student (Sno,Sname,Ssex,Sdept,Sage) VALUES ('201215121','李勇','男','CS',20);
INSERT  INTO  Student (Sno,Sname,Ssex,Sdept,Sage) VALUES ('201215122','刘晨','女','CS',19);
INSERT  INTO  Student (Sno,Sname,Ssex,Sdept,Sage) VALUES ('201215123','王敏','女','MA',18);
INSERT  INTO  Student (Sno,Sname,Ssex,Sdept,Sage) VALUES ('201215125','张立','男','IS',19);
INSERT  INTO  Student (Sno,Sname,Ssex,Sdept,Sage) VALUES ('201215128','陈冬','男','IS',20);

INSERT  INTO Course(Cno,Cname,Cpno,Ccredit) VALUES ('1','数据库',NULL,4);
INSERT  INTO Course(Cno,Cname,Cpno,Ccredit) VALUES ('2','数学',NULL,4);
INSERT  INTO Course(Cno,Cname,Cpno,Ccredit) VALUES ('3','信息系统',NULL,4);
INSERT  INTO Course(Cno,Cname,Cpno,Ccredit) VALUES ('4','操作系统',NULL,4);
INSERT  INTO Course(Cno,Cname,Cpno,Ccredit) VALUES ('5','数据结构',NULL,4);
INSERT  INTO Course(Cno,Cname,Cpno,Ccredit) VALUES ('6','数据处理',NULL,4);
INSERT  INTO Course(Cno,Cname,Cpno,Ccredit) VALUES ('7','Pascal语言',NULL,4);
UPDATE Course SET Cpno = '5' WHERE Cno = '1' 
UPDATE Course SET Cpno = '1' WHERE Cno = '3' 
UPDATE Course SET Cpno = '6' WHERE Cno = '4' 
UPDATE Course SET Cpno = '7' WHERE Cno = '5' 
UPDATE Course SET Cpno = '6' WHERE Cno = '7' 

INSERT  INTO SC(Sno,Cno,Grade) VALUES ('201215121 ','1',92);
INSERT  INTO SC(Sno,Cno,Grade) VALUES ('201215121 ','2',85);
INSERT  INTO SC(Sno,Cno,Grade) VALUES ('201215121 ','3',88);
INSERT  INTO SC(Sno,Cno,Grade) VALUES ('201215122 ','2',90);
INSERT  INTO SC(Sno,Cno,Grade) VALUES ('201215122 ','3',80);

//新建触发器
CREATE TRIGGER regist_recorder
ON SysUser            
AFTER
INSERT
AS 
 declare @UserName    nchar(20)
 declare @DateTime    datetime
 declare @UserOperation nchar(200)
 select @UserName = system_user
 select @DateTime = CONVERT(datetime,GETDATE(),120) 
 declare @op varchar(10)
 select @op=case when exists(select 1 from inserted) and exists(select 1 from deleted)
                   then 'Update'
                   when exists(select 1 from inserted) and not exists(select 1 from deleted)
                   then 'Insert'
                   when not exists(select 
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