stuliving学生公寓 SQL学生成绩管理StuendtDB.doc

stuliving学生公寓 SQL学生成绩管理StuendtDB 导读：就爱阅读网友为您分享以下“SQL学生成绩管理StuendtDB”资讯，希望对您有所帮助，感谢您对92的支持! 建立数据库 Create database StudentDB 建立表结构的SQL定义语句 create table Student ( Sno char(12) primary key, Sname varchar(10) not null unique, Ssex char(2), Sbirthday datetime, Sdept char(4), Mno char(6) ); create table Course( Cno char(8) primary key, Cname varchar(40) not null unique, Cpon char(8), Csredit numeric(3,1), Chour smallint, Cterm smallint, Foreign key (Cpon) references Course (Cno) ); create table SC( Sno char(12), Cno char(8), Grade numeric(3,1), primary key (Sno,Cno), foreign key(Sno) references Student(Sno), foreign key(Cno) references Course(Cno) ); create table Major( Mno char(6) primary key, Mname varchar(40) not null unique, Sdept char(4) ); create table Users( id int identity primary key, username varchar(10) not null unique, userpwd varchar(200), role varchar(10) not null ); 建立学生成绩视图 create view V_StudentCourseGrade as select Student.*,Major.Mname,Course.Cno,Course.Cname,SC.Grade from Student left outer join SC on SC.Sno=Student.Sno left outer join Course on SC.Cno=Course.Cno left outer join Major on Student.Mno=Major.Mno 定义系统中的全局变量 Public SqlConn As New ADODB.Connection Public SqlRS As New ADODB.Recordset Public SqlCmd As New ADODB.Command 连接数据库 Public Function DBConn() As Boolean Connstr = “Provider = SQLOLEDB.1;Data Source = .\SQLEXPRESS;User ID = sa;Password =Iniyial Catalog = StudentDB” If SqlConn.Statelt;gt;adStateOpen Then ?SqlConn.ConnectionString = ConnStr ?SqlConn.Open SqlConn.Open ConnStr End If SqlCmd.ActiveConnection = SqlConn DBConn = Ture End Function 执行SQL语句的函数ExecSQL Public Function ExecSQL(ByVal sql As String) As ADODB.Recordset If SqlConn.Statelt;gt;adStateOpen Then DBConn End If SqlCmd.CommandText = sql Set ExecSQL = SqlCmd.Execute End Function 登录系统按钮“确定” Private Sub cmdOK_Click() Dim sUserName As String Dim sUserPwd As String sUserName = Yrim(txtUserName.text) sUserPwd = Trim(txtUserPwd.Text) If sUserName = “”Th