做出一个学生成绩管理系统并连接数据库，通过录入，查询，更新，统计等功能方便管理数据

1.课程设计目的

做出一个学生成绩管理系统并连接数据库，通过录入，查询，更新，统计等功能方便管理数据

2.课程设计内容和要求

设计内容：

用户登录窗口，系统总控菜单窗口，编辑数据，查询功能，统计功能

要求：

对学生、课程、学习表三个表更新的功能。

按学号查学生基本信息。

按班级统计不及格人数和比例。

所有数据均用关系数据库进行储存

3.课程设计总体方案及分析

3.1系统功能结构

3.2 系统数据库结构

(1). 数据库概念结构的E-R图

 

(2). 数据库逻辑结构

学生：s(sno,sname，sex，age，dept) 表中属性分别为：学号、姓名、性别、年龄和系名。

课程：c(cno，cname，teacher，credit) 表中属性分别为：课号、课名、教师名和学分。

学习：sc(sno，cno，grade) 表中属性分别为：学号、课号、成绩和学分。

用户：user(hm，pwd，role) 表中属性分别为：用户名、密码、权限（0、1、2三等）。

(3). 数据库物理结构

学生表：

课程表： 

 学习表：

 

用户表：

 

3.3 系统程序结构

登录程序

程序窗口

 确认按钮代码：

Private Sub cmdOK_Click()
Dim sUserName As String
Dim sUserPwd As String
sUserName = Trim(txtUserName.Text)
sUserPwd = Trim(txtUserPwd.Text)
If sUserName = "" Then
Call MsgBox("请输入用户名!", vbOKOnly, "登陆提示")
txtUserName.SetFocus
Exit Sub
End If
If SqlConn.State <> adStateOpen Then DBConnect ("U")
Set SqlRS = ExecSQL("select count(*)num from Users where(username='" + sUserName + "')and(userpwd='" + sUserPwd + "')")
If Not SqlRS.EOF Then
If Int(Trim(SqlRS("num"))) >= 1 Then
SqlRS.Close
Set SqlRS = ExecSQL("select username,role from Users where(username='" + sUserName + "')and(userpwd='" + sUserPwd + "')")
If Not SqlRS.EOF Then
MDIForm1.SetRightForRole (Trim(SqlRS("role")))
Else
MDIForm1.SetRightForRole ("2")
End If
Unload Me
Call MsgBox("登陆成功!", vbOKOnly, "登陆提示")
MDIForm1.Show
Else
Call MsgBox("密码错误,请重新登陆!", vbOKOnly, "登陆提示")
txtUserName.SetFocus
Exit Sub
End If
End If 
End Sub

用户名输入框代码：

Private Sub txtUserName_KeyDown(KeyCode As Integer, Shift As Integer)
If KeyCode = 13 Then txtUserPwd.SetFocus
End Sub

用户密码输入代码：

Private Sub txtUserPwd_KeyDown(KeyCode As Integer, Shift As Integer)
If KeyCode = 13 Then cmdOK_Click
End Sub

退出按钮代码：

Private Sub txtUserPwd_KeyDown(KeyCode As Integer, Shift As Integer)
If KeyCode = 13 Then cmdOK_Click
End Sub

系统主页面

程序窗口

一系列代码:

Public Sub SetRightForRole(role As String)
If role = "2" Then
UpdateStudent.Visible = False
AddGrade.Visible = False
ElseIf role = "1" Then
End If
End Sub

Private Sub AddClass_Click()
frmClassinfo.SetOpenStyle ("I")
frmClassinfo.Show
frmClassinfo.ZOrder 0
End Sub

Private Sub AddGrade_Click()
frmCourseGrade.Show
frmCourseGrade.ZOrder 0
End Sub

Private Sub AddStudent_Click()
frmStudentInfo.SetOpenStyle ("I")
frmStudentInfo.Show
frmStudentInfo.ZOrder 0
End Sub

Private Sub MDIForm_Load()
If SqlConn.State <> adStateOpen Then
DBConnect ("U")
End If
End Sub

Private Sub SearchStudent_Click()
frmStudentSearch.Show
frmStudentSearch.ZOrder 0
End Sub

Private Sub tc_Click()
    Unload Me
End Sub

Private Sub UpdateClass_Click()
frmClassinfo.SetOpenStyle ("U")
frmClassinfo.Show
frmClassinfo.ZOrder 0
End Sub

Private Sub UpdateStudent_Click()
frmStudentInfo.SetOpenStyle ("U")
frmStudentInfo.Show
frmStudentInfo.ZOrder 0
End Sub

 

数据输入编辑程序

学生信息管理

 新增按钮代码

Private Sub cmdNew_Click()
txtSno.Enabled = True
txtSname.Enabled = True
Male.Enabled = True
Female.Enabled = True
txtAge.Enabled = True
txtDept.Enabled = True
txtSno.Text = ""
txtSname.Text = ""
Male.Value = False
Female.Value = False
txtAge.Text = ""
txtDept.Text = ""
txtSno.SetFocus
End Sub

保存按钮代码

Private Sub cmdSave_Click()
Dim strSQL As String
Dim strSex As String
    If Trim(txtSno.Text) = "" Then
        txtSno.SetFocus
        Call MsgBox("请输入学生学号！", vbOKOnly, "提示")
        Exit Sub
    End If
    If Trim(txtSname.Text) = "" Then
        txtSname.SetFocus
        Call MsgBox("请输入学生姓名！", vbOKOnly, "提示")
    Exit Sub
    End If
    If Male.Value = True Then
    strSex = "男"
    ElseIf Female.Value = True Then
    strSex = "女"
    End If

    If strMode = "I" Then
    Set SqlRS = ExecSQL("select count(*) num from s where sno='" + Trim(txtSno.Text) + "'")
    If Not SqlRS.EOF Then
    If Int(Trim(SqlRS("num"))) >= 1 Then
    SqlRS.Close
    Call MsgBox("该学生" + Trim(txtSno.Text) + "已经存在，请重新输入", vbOKOnly, "提示")
    Exit Sub
    End If
    End If
    SqlRS.Close
    strSQL = "INSERT INTO s(sno,sname,ssex,sage,dept)"
    strSQL = strSQL + "VALUES('" + Trim(txtSno.Text) + "','" + Trim(txtSname.Text) + "','" + strSex + "','" + Trim(txtAge.Text) + "','" + Trim(txtDept.Text) + "')"
    ElseIf strMode = "U" Then
    strSQL = "UPDATE s SET sno='" + Trim(txtSno.Text) + "',sname = '" + Trim(txtSname.Text) + "',"
    strSQL = strSQL + "ssex='" + strSex + "',sage='" + Trim(txtAge.Text) + "',dept='" + Trim(txtDept.Text) + "'"
    strSQL = strSQL + "Where sno='" + strNo + "'"
    End If
    ExecSQL (strSQL)
    Call MsgBox("保存成功", vbOKOnly, "提示")
    If strMode = "I" Then cmdNew_Click
End Sub

学生信息更新

 查找按钮代码

查找按钮代码
Private Sub cmdFind_Click()
strNo = Trim(txtSno.Text)
If Trim(txtSno.Text) = "" And Trim(txtSname.Text) = "" Then
Call MsgBox("请输入学生学号或姓名!", vbOKOnly, "提示?")
Exit Sub
End If
Set SqlRS = ExecSQL("select * from s where sno ='" + Trim(txtSno.Text) + "'or s.sname='" + Trim(txtSname.Text) + "'")
If Not SqlRS.EOF Then
txtSno.Text = Trim(SqlRS("sno"))
strNo = Trim(SqlRS("sno"))
txtSname.Text = Trim(SqlRS("sname"))
If SqlRS("ssex") = "男" Then
Male.Value = True
ElseIf SqlRS("ssex") = "女" Then
Female.Value = True
Else
Male.Value = True
Female.Value = True
End If
txtAge.Text = Trim(SqlRS("sage"))
txtDept.Text = Trim(SqlRS("dept"))
Else
SqlRS.Close
Call MsgBox("没有此学生信息", vbOKOnly, "提示")
Exit Sub
End If
If strMode = "U" Then
cmdChang.Enabled = True
cmdDelete.Enabled = True
cmdSave.Enabled = False
End If 
End Sub
修改按钮代码
Private Sub cmdChang_Click()
If strMode = "U" Then
Male.Enabled = True
Female.Enabled = True
txtAge.Enabled = True
txtDept.Enabled = True
cmdSave.Enabled = True
End If
End Sub
删除按钮代码
Private Sub cmdDelete_Click()
If vbYes = MsgBox("是否确认删除", vbYesNo, "询问") Then
SqlConn.BeginTrans
SqlCmd.CommandText = "delete from sc where sno ='" + strNo + "'"
SqlCmd.Execute
SqlCmd.CommandText = "delete from s where sno ='" + strNo + "'"
SqlCmd.Execute
SqlConn.CommitTrans
txtSno.Text = ""
txtSname.Text = ""
Male.Value = False
Female.Value = False
txtAge.Text = ""
txtDept.Text = ""
cmdNew.Enabled = False
cmdFind.Enabled = True
cmdChang.Enabled = False
cmdDelete.Enabled = False
cmdSave.Enabled = False
Male.Enabled = False
Female.Enabled = False
txtAge.Enabled = False
txtDept.Enabled = False
txtSno.SetFocus
End If
退出按钮代码
Private Sub cmdClose_Click()
Unload Me
End Sub

课程信息管理

 

Dim strMode As String
Dim strCno As String
Public Sub SetOpenStyle(str As String)
strMode = str
cmdNew.Enabled = False
cmdSave.Enabled = False
cmdChange.Enabled = False
cmdDelete.Enabled = False
cmdFind.Enabled = False
txtTeacher.Enabled = False
txtCredit.Enabled = False
If str = "F" Then
cmdFind.Enabled = True
ElseIf str = "I" Then
cmdNew.Enabled = True
cmdSave.Enabled = True
txtCno.Enabled = False
txtCname.Enabled = False
ElseIf str = "U" Then
cmdFind.Enabled = True
End If
End Sub
修改按钮
Private Sub cmdChange_Click()
txtCno.Enabled = True
txtCname.Enabled = True
txtTeacher.Enabled = True
txtCredit.Enabled = True
cmdSave.Enabled = True
End Sub
删除按钮
Private Sub cmdDelete_Click()
If vbYes = MsgBox("是否确认删除", vbYesNo, "询问") Then
SqlConn.BeginTrans
SqlCmd.CommandText = "delete from sc where cno ='" + strCno + "'"
SqlCmd.Execute
SqlCmd.CommandText = "delete from c where cno ='" + strCno + "'"
SqlCmd.Execute
SqlConn.CommitTrans
txtCno.Text = ""
txtCname.Text = ""
txtTeacher.Text = ""
txtCredit.Text = ""
cmdNew.Enabled = False
cmdFind.Enabled = True
cmdChange.Enabled = False
cmdDelete.Enabled = False
cmdSave.Enabled = False
txtTeacher.Enabled = False
txtCredit.Enabled = False
txtCno.SetFocus
End If
End Sub
查找按钮
Private Sub cmdFind_Click()
strCno = Trim(txtCno.Text)
If Trim(txtCno.Text) = "" And Trim(txtCname.Text) = "" Then
Call MsgBox("请输入课程课号或课名", vbOKOnly, "提示")
Exit Sub
End If
Set SqlRS = ExecSQL("select * from c where cno ='" + Trim(txtCno.Text) + "'or c.cname='" + Trim(txtCname.Text) + "'")
If Not SqlRS.EOF Then
txtCno.Text = Trim(SqlRS("cno"))
strCno = Trim(SqlRS("cno"))
txtCname.Text = Trim(SqlRS("cname"))
txtTeacher.Text = SqlRS("teacher")
txtCredit.Text = SqlRS("credit")
Else
SqlRS.Close
Call MsgBox("没有此课程信息", vbOKOnly, "提示")
Exit Sub
End If
SqlRS.Close
If strMode = "U" Then
cmdChange.Enabled = True
cmdDelete.Enabled = True
cmdSave.Enabled = False
End If
'End If
End Sub
新增按钮
Private Sub cmdNew_Click()
txtCno.Enabled = True
txtCname.Enabled = True
txtTeacher.Enabled = True
txtCredit.Enabled = True
txtCno.Text = ""
txtCname.Text = ""
txtTeacher.Text = ""
txtCredit.Text = ""
txtCno.SetFocus
End Sub
保存按钮
Private Sub cmdSave_Click()
Dim strSQL As String
 If Trim(txtCno.Text) = "" Then
 txtCno.SetFocus
 Call MsgBox("请输入课号", vbOKOnly, "提示")
 Exit Sub
 End If
 If Trim(txtCname.Text) = "" Then
 txtCname.SetFocus
 Call MsgBox("请输入课程名", vbOKOnly, "提示")
 Exit Sub
 End If
 If Trim(txtTeacher.Text) = "" Then
 txtTeacher.SetFocus
 Call MsgBox("请输入教师名", vbOKOnly, "提示")
 Exit Sub
 End If
 If Trim(txtCredit.Text) = "" Then
 txtCredit.Text = ""
 Call MsgBox("请输入学分", vbOKOnly, "提示")
 Exit Sub
 End If
 If strMode = "I" Then
 Set SqlRS = ExecSQL("select count(*)num from c where cno='" + Trim(txtCno.Text) + "'")
 If Not SqlRS.EOF Then
 If Int(Trim(SqlRS("num"))) >= 1 Then
 SqlRS.Close
 Call MsgBox("该课程" + Trim(txtCno.Text) + "已经存在", vbOKOnly, "提示")
 Exit Sub
 End If
 End If
 SqlRS.Close
strSQL = "INSERT INTO c(cno,cname,teacher,credit)"
strSQL = strSQL + "VALUES('" + Trim(txtCno.Text) + "','" + Trim(txtCname.Text) + "','" + Trim(txtTeacher.Text) + "','" + Trim(txtCredit) + "')"
ElseIf strMode = "U" Then
strSQL = "UPDATE c SET cno='" + Trim(txtCno.Text) + "',cname='" + Trim(txtCname.Text) + "',teacher='" + Trim(txtTeacher.Text) + "',credit='" + Trim(txtCredit.Text) + "'"
strSQL = strSQL + "where cno='" + strCno + "'"
End If
ExecSQL (strSQL)
Call MsgBox("保存成功！", vbOKOnly, "提示")
If strMode = "I" Then cmdNew_Click
End Sub

Private Sub Form_Load()

End Sub

学生成绩管理

                        

 

Private Sub cmdClose_Click()
Unload Me
End Sub
删除按钮
Private Sub cmdDelete_Click()
If vbYes = MsgBox("是否确认删除学号:" + studentNo + ",课程号:'" + courseNo + "'的学生成绩信息？", vbOKOnly, "提示") Then
ExecSQL ("delete from sc where sno = '" + studentNo + "'and cno = '" + courseNo + "'")
gradeADO.Refresh
End If
End Sub
查找按钮
Private Sub cmdFind_Click()
    Dim strSQL, stmp As String
    Dim strSno, sSno As String
    Dim strSname, sSname As String
    strSno = Trim(txtSno.Text)
    strSname = Trim(txtSname.Text)
    strSQL = "select * from V_scgrade"
    sSno = ""
    If ckbSno.Value = 1 Then
    sSno = "sno'" + strSno + "'"
    End If
    sSname = ""
    If ckbSname.Value = 1 Then
    sSname = "sname like'%" + strSname + "%'"
    End If
    stmp = ""
    If sSno <> "" Then
    If sSname <> "" Then
    stmp = sSno + "and" + sSname
    Else
    stmp = sSno
    End If
    Else
    If sSname <> "" Then
    stmp = sSname
    End If
    End If
    If Trim(stmp) <> "" Then
    strSQL = strSQL + "where" + stmp
    End If
    gradeADO.ConnectionString = getConnString
    gradeADO.RecordSource = strSQL
    gradeADO.Refresh
End Sub
修改按钮
Private Sub cmdUpdate_Click()
    Dim sCno, sSQL As String
    If Trim(sNo.Text) = "" Then
    Call MsgBox("请选择学生", vbOKOnly, "提示")
    Exit Sub
    End If
    If Trim(cbxCourse.Text) = "" Then
    Call MsgBox("请选择输入课程名", vbOKOnly, "提示")
    cbxCourse.SetFocus
    Exit Sub
    End If
    If Trim(txtGrade.Text) = "" Then
    Call MsgBox("请输入该学生的课程成绩", vbOKOnly, "提示")
    txtGrade.SetFocus
    Exit Sub
    End If
    sCno = ""
    Set SqlRS = ExecSQL("select cno from c where cname = '" + Trim(cbxCourse.Text) + "'")
    If Not SqlRS.EOF Then
    sCno = Trim(SqlRS("cno"))
    End If
    SqlRS.Close
    If sCno = "" Then
    Call MsgBox("不存在该课程", vbOKOnly, "提示")
    cbxCourse.SetFocus
    Exit Sub
    End If
    Set SqlRS = ExecSQL("select count(*) num from sc where sno = '" + Trim(sNo.Text) + "' and cno = '" + sCno + "'")
    If Not SqlRS.EOF Then
    If Int(Trim(SqlRS("num"))) >= 1 Then
    sSQL = "update sc set grade = " + Trim(txtGrade.Text)
    sSQL = sSQL + "where sno ='" + Trim(sNo.Text) + "'and cno = '" + sCno + "'"
    Else
    sSQL = "insert into sc(sno,cno,grade)"
    sSQL = sSQL + "values('" + Trim(sNo.Text) + "','" + sCno + "'," + Trim(txtGrade.Text) + ")"
    End If
    SqlRS.Close
    ExecSQL (sSQL)
    Call MsgBox("保存成功", vbOKOnly, "提示")
    gradeADO.Refresh
    End If
End Sub

 查询程序

 

查询按钮
Private Sub cmdFind_Click()
Dim strSQL, stmp As String
Dim strSno, sSno As String
Dim strSname, sSname As String
Dim strDept, sDept As String
If ckbSno.Value <> 1 And ckbSname.Value <> 1 Then
Call MsgBox("请选择或输入查询条件！", vbOKOnly, "提示")
Exit Sub
End If
strSno = Trim(txtSno.Text)
strSname = Trim(txtSname.Text)
strSQL = "select * from s "
sSno = ""
If ckbSno.Value = 1 Then
sSno = "(Sno like'" + strSno + "%')"
End If
sSname = ""
If ckbSname.Value = 1 Then
sSname = "(Sname like'%" + strSname + "%')"
End If
stmp = ""
If sSno <> "" Then
stmp = sSno
If sSname <> "" Then
stmp = stmp + "and" + sSname
End If
Else
If sSname <> "" Then
stmp = sSname
End If
End If

If Trim(stmp) <> "" Then
strSQL = strSQL + "where" + stmp
End If
studentADO.ConnectionString = getConnString
studentADO.RecordSource = strSQL
studentADO.Refresh
End Sub
退出按钮
Private Sub Form_Load()
Unload Me
End Sub

统计程序

采用DataGrid和ADODC插件进行简单的统计呈现，使用的RecordSource为：

select left(Sno,4) SqlClass ,count(*) fail from V_scgrade where Grade<60 group by left(Sno,4

注意：一开始vb6.0与mysql连接时要在计算机服务中将与mysql的服务开启，具体是哪些不太清楚，我反正都开了。