1、数据库大型实验报告数据库大型实验报告 一 课题设计概要1 需求分析概要随着电子计算机的发展，人们发现计算机管理能带给人们更高的效率和方便。在现代管理工作中应用电脑可以极大地提高管理工作的效率，扩大管理的覆盖面，提高管理工作的精确度，加快信息交流的速度。采用传统的手工管理模式，其工作效率、管理质量和管理水平已不能满足当今经营管理发展的要求，也无法和国外的企业进行竞争。只有采用先进的计算机管理技术，把一些先进的管理技术和管理方式融入到企业销售管理中去，才能提高企业的工作效率和管理水平，使企业能够随着市场的动态变化而随时调整自身的销售业务水平，在瞬息万变的市场竞争中脱颖而出。2 系统功能概要商品管理
2、的主要任务是利用计算机对商品销售和客户流动进行日常的管理，如商品查询，客户统计。我们针对系统服务对象的具体要求，设计了本商品管理系统。该系统主要包含以下几大功能：对各种商品的基础信息进行维护和管理。能够完成商品信息的查询、统计、添加、修改和删除；对客户的基本信息进行维护和管理。能够完成对客户信息的查询、统计、添加、修改和删除；对销售的商品进行登记、统计和查询；对出入库的商品进行登记、统计和查询。二 系统功能结构设计 三 数据库设计结合实际情况和需求分析，在商业管理系统数据库G_Sale中设计了4张表： 商品表（Goods）、客户表（Client）、商品临时表（Gtemp）、客户临时表（Ctem
3、p）这些表描述的商业信息有商品编号、商品名称、商品规格、商品数量、商品金额、商品库存编号、客户名称、客户编号、客户联系方式、销售编号等。表中字段结构设计： 商品表（Goods）字段名称字段类型字段大小编号int4类别Char10名称Char10规格Char10数量Int4单价Float8金额Int4计量单位Char10折扣Float8供应商Char4生产商Char4库存编号Int4存放位置Char10库存日期Datetime8备注varchar255 主键编号 外键计量单位客户信息表（Client）字段名称字段类型字段大小编号Int4名称char10类型Char10性别Char4地址Char5
4、0联系电话Char10移动电话Char10电子邮件Char10QQInt4MSNInt4信用Char10重要性Char10备注Varchar255 主键编号 外键计量单位存储过程说明:存储过程名称描述作用Proc_CClearCREATE PROCEDURE proc_CClearASdelete from CtempGO删除客户表Ctemp中内容Proc_ClientCREATE PROCEDURE proc_ClientASdeclare 编号 intdeclare 名称 char(10)declare 类型 char(10)declare 性别 char(2)declare 地址 var
5、char(250)declare 联系电话 char(10)declare 移动电话 char(10)declare 电子邮件 char(50)declare QQ intdeclare MSN intdeclare 信用 char(10)declare 重要性 char(10)declare 备注 varchar(255)declare get_kh cursorfor select * from Clientdelete from Ctempopen get_khfetch get_kh into 编号,名称,类型,性别,地址,联系电话,移动电话,电子邮件,QQ,MSN,信用,重要性,备注
6、while fetch_Status=0begininsert into Ctemp values(编号,名称,类型,性别,地址,联系电话,移动电话,电子邮件,QQ,MSN,信用,重要性,备注)fetch get_kh into 编号,名称,类型,性别,地址,联系电话,移动电话,电子邮件,QQ,MSN,信用,重要性,备注endGO添加所有的客户表信息到客户临时表CtempProc_GClearCREATE PROCEDURE proc_GClearASdelete from GtempGO删除商品临时表内容Proc_GoodsCREATE PROCEDURE proc_GoodsASdecla
7、re 编号 intdeclare 类别 char(10)declare 名称 char(10)declare 规格 char(10)declare 数量 intdeclare 单价 float(8)declare 金额 intdeclare 计量单位 char(10)declare 折扣 float(8)declare 供应商 char(10)declare 生产商 char(10)declare 库存编号 intdeclare 存放位置 char(10)declare 库存日期 datetimedeclare 备注 varchar(255)declare get_kh cursorfor s
8、elect * from Goodsdelete from Gtempopen get_khfetch get_kh into 编号,类别,名称,规格,数量, 单价,金额,计量单位,折扣,供应商,生产商,库存编号,存放位置,库存日期,备注while fetch_Status=0begininsert into Gtemp values(编号,类别,名称,规格,数量, 单价,金额,计量单位,折扣,供应商,生产商,库存编号,存放位置,库存日期,备注)fetch get_kh into 编号,类别,名称,规格,数量, 单价,金额,计量单位,折扣,供应商,生产商,库存编号,存放位置,库存日期,备注en
9、dGO添加所有商品信息到商品临时表Gtemp四 界面设计开始界面功能：点击框内任意无字位置即进入“启动界面“。实现：Private Sub Form_Click()Timer1.Enabled = False启动菜单.ShowEnd SubPrivate Sub Label1_Click()EndEnd SubPrivate Sub Timer1_Timer()Timer1.Enabled = FalseForm2.ShowEnd SubPrivate Sub Form_MouseMove(Button As Integer, Shift As Integer, X As Single, Y
10、As Single)Label1.ForeColor = &HFF8080End SubPrivate Sub Label1_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)Label1.ForeColor = &H80FF&End Sub启动界面功能：鼠标点击白框内字体进入相应模块实现：Private Sub Form_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)Label1.ForeColor =
11、 &H80000012: Label2.ForeColor = &H80000012Label3.ForeColor = &H80000012: Label4.ForeColor = &H80000012Label5.ForeColor = &H80000012End SubPrivate Sub Label1_Click()商品管理菜单.ShowEnd SubPrivate Sub Label1_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)Label1.ForeColor = &HFF&End
12、 SubPrivate Sub Label2_Click()客户管理菜单.ShowEnd SubPrivate Sub Label2_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)Label2.ForeColor = &HFF&End SubPrivate Sub Label3_Click()商品销售菜单.ShowEnd SubPrivate Sub Label3_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Si
13、ngle)Label3.ForeColor = &HFF&End SubPrivate Sub Label4_Click()库存管理菜单.ShowEnd SubPrivate Sub Label4_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)Label4.ForeColor = &HFF&End SubPrivate Sub Label5_Click()EndEnd SubPrivate Sub Label5_MouseMove(Button As Integer, Shift As Integ
14、er, X As Single, Y As Single)Label5.ForeColor = &HFF&End Sub商品管理界面功能：鼠标下拉菜单进入相应模块实现：Private Sub SD_Click()Load 商品管理菜单1商品管理菜单1.ShowEnd SubPrivate Sub CT_Click()Load 商品管理菜单3商品管理菜单3.ShowEnd SubPrivate Sub TS_Click()Load 商品管理菜单2商品管理菜单2.ShowEnd SubPrivate Sub TCJM_Click()Load 启动菜单启动菜单.ShowEnd SubPrivate
15、Sub TCXT_Click()EndEnd Sub客户管理菜单功能：通过鼠标下拉菜单，进入相应模块实现：Private Sub CT_Click()Load 客户管理菜单3客户管理菜单3.ShowEnd SubPrivate Sub SD_Click()Load 客户管理菜单1客户管理菜单1.ShowEnd SubPrivate Sub TCJM_Click()Load 启动菜单启动菜单.ShowEnd SubPrivate Sub TCXT_Click()EndEnd SubPrivate Sub TS_Click()Load 客户管理菜单2客户管理菜单2.ShowEnd Sub库存管理界
16、面功能：通过鼠标点击字体，进入相应模块实现：Private Sub Label2_Click()Load 库存管理菜单2库存管理菜单2.ShowEnd SubPrivate Sub Label2_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)Label2.ForeColor = &HFFFF00End SubPrivate Sub Label1_Click()Load 库存管理菜单1库存管理菜单1.ShowEnd SubPrivate Sub Label1_MouseMove(Button As
17、Integer, Shift As Integer, X As Single, Y As Single)Label1.ForeColor = &HFFFF00End SubPrivate Sub Form_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)Label1.ForeColor = &HFF00FF: Label2.ForeColor = &HFF00FFEnd SubPrivate Sub Label3_Click()Load 启动菜单启动菜单.ShowEnd Sub商品销售界面功能：通过
18、鼠标电击变色字体，进入相应模块实现：Private Sub Label1_Click()Load 商品销售菜单1商品销售菜单1.ShowEnd SubPrivate Sub Label2_Click()Load 商品销售菜单2商品销售菜单2.ShowEnd SubPrivate Sub Label3_Click()Load 启动菜单启动菜单1.ShowEnd SubPrivate Sub Label2_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)Label2.ForeColor = &HFF&E
19、nd SubPrivate Sub Label3_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)Label3.ForeColor = &HFF&End SubPrivate Sub Label1_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)Label1.ForeColor = &HFF&End SubPrivate Sub Form_MouseMove(Button As Integer, Shif
20、t As Integer, X As Single, Y As Single)Label1.ForeColor = &H80000012: Label2.ForeColor = &H80000012Label3.ForeColor = &H80000012End Sub五 功能模块设计1商品管理商品浏览 商品查询浏览和刷新cm.ExecuteAdodc1.RefreshEnd SubPrivate Sub Command7_Click()Dim cn As New ADODB.ConnectionDim cm As New ADODB.CommandDim mystr As Stringmys
21、tr = Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;User ID=sa;Initial Catalog=G_Sale;Data Source=(LOCAL)cn.ConnectionString = mystrcn.Opencm.ActiveConnection = cncm.CommandType = adCmdStoredProccm.CommandText = proc_GClearcm.ExecuteAdodc1.RefreshEnd SubPrivate Sub Command8
22、_Click()Dim cn As New ADODB.ConnectionDim cm As New ADODB.CommandDim mystr As Stringmystr = Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;User ID=sa;Initial Catalog=G_Sale;Data Source=(LOCAL)cn.ConnectionString = mystrcn.Opencm.ActiveConnection = cncm.CommandType = adCmdSt
23、oredProccm.CommandText = proc_GDailycm.ExecuteAdodc1.RefreshEnd SubPrivate Sub Command9_Click()Dim cn As New ADODB.ConnectionDim cm As New ADODB.CommandDim mystr As Stringmystr = Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;User ID=sa;Initial Catalog=G_Sale;Data Source=(L
24、OCAL)cn.ConnectionString = mystrcn.Opencm.ActiveConnection = cncm.CommandType = adCmdStoredProccm.CommandText = proc_GClearcm.ExecuteAdodc1.RefreshEnd SubPrivate Sub SSTab1_DblClick()End Sub添加和删除(1) Public Sub CmdEnd(frm As Form) EndEnd SubPrivate Sub Command2_Click()Load 商品管理菜单商品管理菜单.ShowEnd SubPri
25、vate Sub Form_Load() 向cboOperators组合框中加入查询所需要使用的运算符号 cboOperators CboOp 通过传递进来的SQL语句，取得表的各个字段的结构 Dim rs As New ADODB.Recordset rs.Open select * from Goods, Cnn, adOpenKeyset, adLockReadOnly 将字段填入到字段列表中 Dim fld For Each fld In rs.Fields CboFields.AddItem fld.Name Next fld rs.Close CboFields.ListIndex
26、 = 0End SubPrivate Sub Command1_Click()EndEnd SubPrivate Sub CmdAdd_Click() Dim bh As Long Dim rs1 As New ADODB.Recordset rs1.Open select * from Goods order by 编号, Cnn, adOpenKeyset, adLockOptimistic If rs1.RecordCount 0 Then If rs1.EOF = False Then rs1.MoveLast bh = Val(rs1.Fields(编号) + 1 商品管理菜单4.T
27、ext1(0).Text = Format(bh, 00000) Else 商品管理菜单4.Text1(0).Text = 00001 End If Load 商品管理菜单4 商品管理菜单4.ShowEnd SubPrivate Sub CmdFind_Click() If CboOp.Text = Like Then Adodc1.RecordSource = select * from Goods where & CboFields.Text & & CboOp.Text & % & TxtExpression.Text & % Adodc1.Refresh Else Adodc1.Rec
28、ordSource = select * from Goods where & CboFields.Text & & CboOp.Text & & TxtExpression.Text & Adodc1.Refresh End IfEnd SubPrivate Sub CmdModify_Click() For i = 0 To 14 对应列 DataGrid1.Col = i 商品管理菜单4.Text1(i) = DataGrid1.Text Next i Load 商品管理菜单4 商品管理菜单4.ShowEnd SubPrivate Sub CmdDelete_Click() Dim a
29、As String If Adodc1.Recordset.RecordCount 0 Then a = MsgBox(您确实要删除这条数据吗?, vbYesNo) If a = vbYes Then Adodc1.Recordset.Delete Adodc1.Refresh End If Else MsgBox (没有要删除的数据!) End IfEnd SubPrivate Sub Form_Unload(Cancel As Integer) Call CmdEnd(Me)End Sub(2) Dim i As Integer 定义整型变量Dim rs1 As New ADODB.Recordset 定义数据集对象Public Function Cnn() As Stri
