軟件：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 datetimedeclare @UserOperation nchar(200)select @UserName = system_userselect @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 from inserted) and exists(select 1 from deleted)then 'Delete' end select @UserOperation = @op INSERT INTO SysLog(UserID,DateAndTime,UserOperation)VALUES (@UserName,@DateTime,@UserOperation)

剛開始的登錄頁面

點擊按鈕顯示新的窗體，這是其中一個按鈕的代碼。

Form2 form2 = new Form2();//新建窗體 form2.Show();//顯示新建窗體 this.Hide();//隱藏當前窗體

確定登錄

string username = textBoxtea.Text.Trim(); //取出賬號string password = EncryptWithMD5(textBoxcher.Text.Trim()); //取出密碼并加密string myConnString = "Data Source=.;Initial Catalog=curricula_variable_system;Persist Security Info=True;User ID=sa;Password=sql";//連接數據庫SqlConnection sqlConnection = new SqlConnection(myConnString); //實例化連接對象sqlConnection.Open();string sql = "select UserID,UserPassWord from Teacher where UserID = '" + username + "' and UserPassWord = '" + password + "'"; //教工號：201210，密碼：123//編寫SQL命令SqlCommand sqlCommand = new SqlCommand(sql, sqlConnection);SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();if (sqlDataReader.HasRows && textBoxyan.Text == code){MessageBox.Show("歡迎使用！"); //登錄成功Form6 form6 = new Form6();form6.Show();this.Hide();}else{MessageBox.Show("登錄失敗！");return;}sqlDataReader.Close(); sqlConnection.Close();

密碼加密

public static string EncryptWithMD5(string source){byte[] sor = Encoding.UTF8.GetBytes(source);MD5 md5 = MD5.Create();byte[] result = md5.ComputeHash(sor);StringBuilder strbul = new StringBuilder(40);for (int i = 0; i < result.Length; i++){strbul.Append(result[i].ToString("x2"));//加密結果"x2"結果為32位,"x3"結果為48位,"x4"結果為64位}return strbul.ToString();}

驗證碼
點擊窗體|在事件里找Load|雙擊，然后輸入以下代碼

public string code; //隨機實例化 Random ran = new Random();int number;char code1;//取五個數 for (int i = 0; i < 5; i++){number = ran.Next();if (number % 2 == 0)code1 = (char)('0' + (char)(number % 10));elsecode1 = (char)('A' + (char)(number % 26)); //轉化為字符 this.code += code1.ToString();}label5.Text = code;

查看照片，根據學號查看

try{string connString = "Data Source=.;Initial Catalog=curricula_variable_system;Persist Security Info=True;User ID=sa;Password=sql";//數據庫連接字符串SqlConnection connection = new SqlConnection(connString);//創建connection對象//打開數據庫連接connection.Open();//創建SQL語句string sql = "select UserPhoto from SysUser where UserSchoolID = '" + textBox1.Text + "'";//創建SqlCommand對象SqlCommand command = new SqlCommand(sql, connection);//創建DataAdapter對象SqlDataAdapter dataAdapter = new SqlDataAdapter(command);//創建DataSet對象DataSet dataSet = new DataSet();dataAdapter.Fill(dataSet, "SysUser");int c = dataSet.Tables["SysUser"].Rows.Count;if (c > 0){Byte[] mybyte = new byte[0];mybyte = (Byte[])(dataSet.Tables["SysUser"].Rows[c - 1]["UserPhoto"]);MemoryStream ms = new MemoryStream(mybyte);pictureBox2.Image = Image.FromStream(ms);}else{pictureBox2.Image = null;MessageBox.Show("無照片");} connection.Close();}catch (Exception ex){MessageBox.Show(ex.Message);}

返回上一界面

Form6 form6 = new Form6();//上一界面的窗體form6.Show();//顯示this.Hide();//隱藏當前窗體

我對性別的填寫進行了限定只能是”男“或“女”。學號也限定是201215開頭再加三位數字。
SQL語句如下

alter table Student add constraint c1 check(Sno between 201215000 and 201215999) alter table Student add constraint c2 check(Ssex IN('男','女'))

刪除

SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=curricula_variable_system;User ID=sa;Password=sql");//定義try{con.Open();string select_id = dataGridView1.SelectedRows[0].Cells[0].Value.ToString();//選擇的當前行第一列的值，也就是IDstring delete_by_id = "delete from Student where Sno=" + select_id;//sql刪除語句SqlCommand cmd = new SqlCommand(delete_by_id, con);cmd.ExecuteNonQuery();}catch{MessageBox.Show("請正確選擇行!");}finally{con.Dispose();}this.studentTableAdapter.Fill(this.curricula_variable_systemDataSet3.Student);//Form10_Load里的那條代碼

修改，根據學號修改姓名

String StuID = textBox1.Text.Trim();String StuName = textBox2.Text.Trim();SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=curricula_variable_system;User ID=sa;Password=sql");//定義try{con.Open();string insertStr = "UPDATE Student SET Sname = '" + StuName + "' WHERE Sno = '" + StuID + "'";SqlCommand cmd = new SqlCommand(insertStr, con);cmd.ExecuteNonQuery();}catch{MessageBox.Show("輸入數據違反要求!");}finally{con.Dispose();}this.studentTableAdapter.Fill(this.curricula_variable_systemDataSet3.Student);//Form10_Load里的那條代碼

查詢，根據學號

String StuID = textBox1.Text.Trim();String conn = "Data Source=.;Initial Catalog=curricula_variable_system;User ID=sa;Password=sql";SqlConnection sqlConnection = new SqlConnection(conn); //實例化連接對象try{sqlConnection.Open();String select_by_id = "select * from Student where Sno='" + StuID + "'";SqlCommand sqlCommand = new SqlCommand(select_by_id, sqlConnection);SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();BindingSource bindingSource = new BindingSource();bindingSource.DataSource = sqlDataReader;dataGridView1.DataSource = bindingSource;}catch{MessageBox.Show("查詢語句有誤，請認真檢查SQL語句!");}finally{sqlConnection.Close();}

清空文本行

textBox1.Text = null;textBox2.Text = null;textBox3.Text = null;textBox4.Text = null;textBox5.Text = null;

添加課程

string Coucno = textBox1.Text.Trim();string Couname = textBox2.Text.Trim();string Coucredit = textBox3.Text.Trim();string Coupno = textBox4.Text.Trim(); SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=curricula_variable_system;User ID=sa;Password=sql");//定義try{con.Open();//打開string insertStr = "INSERT INTO Course (Cno,Cname,Cpno,Ccredit) " +"VALUES ('" + Coucno + "','" + Couname + "','" + Coupno + "','" + Coucredit + "')";SqlCommand cmd = new SqlCommand(insertStr, con);//使用cmd.ExecuteNonQuery();}catch{MessageBox.Show("輸入數據違反要求!");}finally{con.Dispose();//釋放} this.courseTableAdapter.Fill(this.curricula_variable_systemDataSet4.Course);

刪除

SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=curricula_variable_system;User ID=sa;Password=sql");//定義try{con.Open();string select_id = dataGridView1.SelectedRows[0].Cells[0].Value.ToString();//選擇的當前行第一列的值，也就是Cno那列string delete_by_id = "delete from Course where Cno=" + select_id;//sql刪除語句SqlCommand cmd = new SqlCommand(delete_by_id, con);cmd.ExecuteNonQuery();}catch{MessageBox.Show("請正確選擇行!");}finally{con.Dispose();}this.courseTableAdapter.Fill(this.curricula_variable_systemDataSet4.Course);

修改，根據課程號修改課程名

string Cno = textBox1.Text.Trim();string Cname = textBox2.Text.Trim();SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=curricula_variable_system;User ID=sa;Password=sql");//定義try{con.Open();string insertStr = "UPDATE Course SET Cname = '" + Cname + "' WHERE Cno = '" + Cno + "'";SqlCommand cmd = new SqlCommand(insertStr, con);cmd.ExecuteNonQuery();}catch{MessageBox.Show("輸入數據違反要求!");}finally{con.Dispose();}this.courseTableAdapter.Fill(this.curricula_variable_systemDataSet4.Course);

查詢，根據課程號

string Cno = textBox1.Text.Trim();String conn = "Data Source=.;Initial Catalog=curricula_variable_system;User ID=sa;Password=sql";SqlConnection sqlConnection = new SqlConnection(conn); //實例化連接對象try{sqlConnection.Open();String select_by_id = "select * from Course where Cno='" + Cno + "'";SqlCommand sqlCommand = new SqlCommand(select_by_id, sqlConnection);SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();BindingSource bindingSource = new BindingSource();bindingSource.DataSource = sqlDataReader;dataGridView1.DataSource = bindingSource;}catch{MessageBox.Show("查詢語句有誤，請認真檢查SQL語句!");}finally{sqlConnection.Close();}

查詢

string StuID = textBox1.Text.Trim();String conn = "Data Source=.;Initial Catalog=curricula_variable_system;User ID=sa;Password=sql";SqlConnection sqlConnection = new SqlConnection(conn); //實例化連接對象try{sqlConnection.Open();String select_by_id = "select * from SC where Sno='" + StuID + "'";SqlCommand sqlCommand = new SqlCommand(select_by_id, sqlConnection);SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();BindingSource bindingSource = new BindingSource();bindingSource.DataSource = sqlDataReader;dataGridView1.DataSource = bindingSource;}catch{MessageBox.Show("查詢語句有誤，請認真檢查SQL語句!");}finally{sqlConnection.Close();}

修改，根據學號、課程號修改成績

string StuID = textBox1.Text.Trim();string Cno = textBox2.Text.Trim();string Grade = textBox3.Text.Trim();SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=curricula_variable_system;User ID=sa;Password=sql");//定義try{con.Open();string insertStr = "UPDATE SC SET Grade = '" + Grade + "' WHERE Cno = '" + Cno + "'AND Sno='"+ StuID+"'";SqlCommand cmd = new SqlCommand(insertStr, con);cmd.ExecuteNonQuery();}catch{MessageBox.Show("輸入數據違反要求!");}finally{con.Dispose();}this.sCTableAdapter2.Fill(this.curricula_variable_systemDataSet7.SC);

添加

string StuID = textBox1.Text.Trim();string Cno = textBox2.Text.Trim();string Grade = textBox3.Text.Trim();SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=curricula_variable_system;User ID=sa;Password=sql");//定義try{string stu = "select Sno from Student where Sno='" + StuID + "'";if (stu != ""){con.Open();//打開string insertStr = "INSERT INTO SC (Sno,Cno,Grade) " +"VALUES ('" + StuID + "','" + Cno + "','" + Grade + "')";SqlCommand cmd = new SqlCommand(insertStr, con);//使用cmd.ExecuteNonQuery();}else{MessageBox.Show("沒有該學生！請重新輸入");} }catch{MessageBox.Show("輸入數據違反要求!");//新加的學號在已有學號中,課程號在已有的課程中，成績在0到100之間}finally{con.Dispose();//釋放}this.sCTableAdapter2.Fill(this.curricula_variable_systemDataSet7.SC);

刪除，輸入學號、課程號刪除對應行。因為選課表是學號課程號一起作為主碼的所以和前邊的刪除方式不同。

SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=curricula_variable_system;User ID=sa;Password=sql");//定義try{con.Open();string StuID = textBox1.Text.Trim();string Cno = textBox2.Text.Trim();string delete_by_stc= "delete from SC where Sno='"+ textBox1.Text + "' and Cno='" + textBox2.Text + "'";SqlCommand cmd = new SqlCommand(delete_by_stc, con);cmd.ExecuteNonQuery();}catch{MessageBox.Show("請正確選擇行!");}finally{con.Dispose();}this.sCTableAdapter2.Fill(this.curricula_variable_systemDataSet7.SC);

查看專業信息（查看專業信息代碼類似，就是SQL語句那改成SC表)

String conn = "Data Source=.;Initial Catalog=curricula_variable_system;User ID=sa;Password=sql";SqlConnection sqlConnection = new SqlConnection(conn); //實例化連接對象String StuID = textBox1.Text.Trim();if(textBox1.Text==""){MessageBox.Show("請先輸入學號!");return;}else{try{sqlConnection.Open();String select_by_id = "select * from Student where Sno='" + StuID + "'";SqlCommand sqlCommand = new SqlCommand(select_by_id, sqlConnection);SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();BindingSource bindingSource = new BindingSource();bindingSource.DataSource = sqlDataReader;dataGridView1.DataSource = bindingSource;}catch{MessageBox.Show("查詢語句有誤，請認真檢查SQL語句!");}finally{sqlConnection.Close();}}

查看個人信息

String StuID = textBox1.Text.Trim();String conn1 = "Data Source=.;Initial Catalog=curricula_variable_system;User ID=sa;Password=sql";SqlConnection sqlConnection1 = new SqlConnection(conn1); //實例化連接對象if (textBox1.Text == ""){MessageBox.Show("請先輸入學號!");return;}else{ try{sqlConnection1.Open();String select_by_id1 = "select UserID,UserSchoolID,UserMobile,UserBirthday,UserIdentity from SysUser where UserSchoolID='" + StuID + "'";SqlCommand sqlCommand = new SqlCommand(select_by_id1, sqlConnection1);SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();BindingSource bindingSource = new BindingSource();bindingSource.DataSource = sqlDataReader;dataGridView1.DataSource = bindingSource;}catch{MessageBox.Show("查詢語句有誤，請認真檢查SQL語句!");}finally{sqlConnection1.Close();}}

查看照片

if (textBox1.Text == ""){MessageBox.Show("請先輸入學號!");return;}else{try{string connString = "Data Source=.;Initial Catalog=curricula_variable_system;Persist Security Info=True;User ID=sa;Password=sql";//數據庫連接字符串SqlConnection connection = new SqlConnection(connString);//創建connection對象//打開數據庫連接connection.Open();//創建SQL語句string sql = "select UserPhoto from SysUser where UserSchoolID = '" + textBox1.Text + "'";//創建SqlCommand對象SqlCommand command = new SqlCommand(sql, connection);//創建DataAdapter對象SqlDataAdapter dataAdapter = new SqlDataAdapter(command);//創建DataSet對象DataSet dataSet = new DataSet();dataAdapter.Fill(dataSet, "SysUser");int c = dataSet.Tables["SysUser"].Rows.Count;if (c > 0){Byte[] mybyte = new byte[0];mybyte = (Byte[])(dataSet.Tables["SysUser"].Rows[c - 1]["UserPhoto"]);MemoryStream ms = new MemoryStream(mybyte);pictureBox2.Image = Image.FromStream(ms);}elsepictureBox2.Image = null;connection.Close();}catch (Exception ex){MessageBox.Show(ex.Message);}}

修改密碼

確定修改，使用正則表達式約束新密碼的格式

if (textBox3.Text == ""){MessageBox.Show("學號不能為空！");}if(textBox1.Text==""){MessageBox.Show("新密碼不能為空！");}if (textBox2.Text == ""){MessageBox.Show("確認密碼不能為空！");}if(textBox1.Text.Trim()!="")//新密碼不為空時，輸入滿足正則表達式{//使用regex（正則表達式）進行格式設置 至少有數字、大寫字母、小寫字母各一個。最少3個字符、最長20個字符。Regex regex = new Regex(@"(?=.*[0-9])(?=.*[a-z])(?=.*[A-Z]).{3,20}");if (regex.IsMatch(textBox1.Text))//判斷格式是否符合要求{//MessageBox.Show("輸入密碼格式正確!");}else{MessageBox.Show("至少有數字、大寫字母、小寫字母各一個。最少3個字符、最長20個字符！");return;}}if (textBox1.Text == textBox2.Text){string sql = "update SysUser set UserPassWord='"+ EncryptWithMD5(textBox1.Text)+"' where UserSchoolID='"+ textBox3.Text.Trim()+"'";string connString = "Data Source=.;Initial Catalog=curricula_variable_system;Persist Security Info=True;User ID=sa;Password=sql";SqlConnection con = new SqlConnection(connString);//創建connection對象con.Open();SqlCommand command = new SqlCommand(sql, con);command.ExecuteNonQuery();MessageBox.Show("新密碼已經修改完成"); con.Close();}else{MessageBox.Show("請輸入兩次相同的密碼");}

對密碼加密

public static string EncryptWithMD5(string source){byte[] sor = Encoding.UTF8.GetBytes(source);MD5 md5 = MD5.Create();byte[] result = md5.ComputeHash(sor);StringBuilder strbul = new StringBuilder(40);for (int i = 0; i < result.Length; i++){strbul.Append(result[i].ToString("x2"));//加密結果"x2"結果為32位,"x3"結果為48位,"x4"結果為64位}return strbul.ToString();}

主要代碼都在上面了，參考的時候結合自己的稍加改動就可以，背景圖片是pictureBox組件然后選自己喜歡的照片就可以了，注意一下大小模式這里，選這個圖片才顯示完整。
所有代碼我壓縮放在github上了，需要的可以下載然后在Visual studio打開看一下點這里

視頻講解：點這里

總結

以上是生活随笔為你收集整理的数据库大作业-学生信息管理系统的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。