求大侠指点如何在数据库里找出他们相关的信息?
求大侠指点如何在数据库里找出他们相关的信息?比如我在查询里输入刘英,点击查询,在msgbox里得到你查询的是刘英,他(她)父亲是:刘能,所属的电视剧为:乡村爱情,如果我查询谢永强,也是一样,得到你查询的是谢永强,他(她)父亲是:谢广坤,所属的电视剧为:乡村爱情 我只想说你的数据做得一点都不有条理。
你可以分2个表嘛。
一个表是 关系表,一个是作品表
然后就可以用一条SQL语句查出来。
select 关系 from 关系表 where 姓名=查询的姓名
select 作品 from 作品表 where 姓名=(select 关系 from 关系表 where 姓名=查询的姓名) 回复 2# nivisde
我是刚学,就这个我都还是改人家的,都想了几天了,都没有想得出来 回复 2# nivisde
我是刚学,就这个我都还是改人家的,都想了几天了,都没有想得出来
要求表格一张,如果两张表格的话就不方便查找 了 楼主,二楼对问题的解决方法说得很清楚了呀。
用两个表格比用一个表格解决起来应该更方便,信不信当然由你了。
不过用你现在的这样一个表看来是难以解决的。你的问题涉及到人物、关系、所属作品。可你的数据表里没有作品这一字段,虽有关系字段,但其内容也都不是表示关系的。
但愿有高手有空且愿意来给你说说如何用一个表格能更方便地解决,但关键的前提是你相信他所说的,你能理解他所说的,才有可能如你所愿呵。 我觉得二楼的话很有道理。
如果你非得用一个数据表的话,我建议你的数据库表更改一下
改为
姓名|关系|电视剧
内容
刘英|刘能|乡村爱情
如果你非得用一个数据表,按照你上传的内容,如果姓名一栏没有重复的话,你可以用两个语句,如,先查询刘英,得到刘能,再查询刘能,得到乡村爱情。 用一张表的话,表要改一下,代码如下
$mdb_data_path=@ScriptDir&"\DB0.mdb"
$mdb_data_pwd="test"
$T="*"
$tblname="mydb"
If Not FileExists($mdb_data_path) Then new()
xie()
xun("刘英")
xun("谢永强")
Func new()
$newMdb = ObjCreate('ADOX.Catalog')
$newMdb.Create("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & $mdb_data_path & ";Jet Oledb:Database Password=" & $mdb_data_pwd)
$newmdb.ActiveConnection.Close;建数据库
$addtbl = ObjCreate("ADODB.Connection")
$addTbl.Open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & $mdb_data_path & ";Jet Oledb:Database Password=" & $mdb_data_pwd)
$addTbl.Execute("CREATE TABLE " & $tblname)
$addtbl.Close;建表
$addfld = ObjCreate("ADODB.Connection")
$addfld.Open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & $mdb_data_path & ";Jet Oledb:Database Password=" & $mdb_data_pwd)
$addfld.Execute("ALTER TABLE " & $tblname & " ADD id identity(1, 1) primary key,name text(255) ,ssex text(50),tv text(50)");id identity(1, 1) primary key为自动编号和主键
$addfld.Close;建列表名
MsgBox(0,'提示:','创建成功!!!')
EndFunc
Func xie()
$addfld = ObjCreate("ADODB.Connection")
$addfld.Open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &$mdb_data_path & ";Jet Oledb:Database Password=" & $mdb_data_pwd)
$addfld.execute("insert into "&$tblname&" (name,ssex,tv) values('刘英','刘能','乡村爱情')")
$addfld.execute("insert into "&$tblname&" (name,ssex,tv) values('谢永强','谢广坤','乡村爱情')")
$addfld.close
MsgBox(0,"","写入成功!")
EndFunc
Func xun($h1)
$addfld = ObjCreate("ADODB.Connection")
$addfld.Open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & $mdb_data_path & ";Jet Oledb:Database Password=" & $mdb_data_pwd)
$RS = ObjCreate("ADODB.Recordset")
$RS.ActiveConnection = $addfld
$RS.Open("SELECT id,name,ssex,tv FROM " & $tblname&" WHERE name='"&$h1&"'")
MsgBox(0, "获取性别", "你查询的" & " " & $RS.Fields (1).value & " " & "他(她)父亲是: " & $RS.Fields (2).value &"所属的电视剧为:"& $RS.Fields (3).value )
$RS.close
$addfld.Close
EndFunc ;==>xun 回复 1# qsy666888
利用正则,看看是不是你要的?
#include <ButtonConstants.au3>
#include <ComboConstants.au3>
#include <EditConstants.au3>
#include <GUIConstantsEx.au3>
#include <ListViewConstants.au3>
#include <StaticConstants.au3>
#include <WindowsConstants.au3>
#include <GuiListView.au3>
#NoTrayIcon
#Region ### START Koda GUI section ### Form=
$Form1 = GUICreate("Access 操作实例", 320, 455, 553, 128)
$ListView1 = GUICtrlCreateListView("编号|姓名|关系|备注", 5, 5, 310, 200)
GUICtrlSendMsg(-1, 0x101E, 0, 50)
GUICtrlSendMsg(-1, 0x101E, 1, 80)
GUICtrlSendMsg(-1, 0x101E, 2, 50)
GUICtrlSendMsg(-1, 0x101E, 3, 100)
$Group1 = GUICtrlCreateGroup("创建/读取/修改/删除", 5, 210, 310, 60)
GUICtrlSetColor(-1, 0x0000FF)
$Button1 = GUICtrlCreateButton("创建", 10, 235, 75, 25)
$Button2 = GUICtrlCreateButton("读取", 85, 235, 75, 25)
$Button3 = GUICtrlCreateButton("修改", 160, 235, 75, 25)
$Button4 = GUICtrlCreateButton(" 删除", 235, 235, 75, 25)
GUICtrlCreateGroup("", -99, -99, 1, 1)
$Group2 = GUICtrlCreateGroup("添加/修改记录", 5, 275, 310, 135)
GUICtrlSetColor(-1, 0xFF00FF)
$Input1 = GUICtrlCreateInput("", 55, 295, 96, 21)
;$Input3 = GUICtrlCreateInput("", 55, 355, 96, 21,0X2000)
$Input3_1 = GUICtrlCreateInput("", 55, 325, 95, 25)
$Label1 = GUICtrlCreateLabel("姓名:", 15, 300, 31, 17)
$Label2 = GUICtrlCreateLabel("关系:", 15, 330, 31, 17)
;$Label3 = GUICtrlCreateLabel("不填:", 20, 360, 23, 17)
$Input2 = GUICtrlCreateInput("", 210, 295, 91, 21)
;$Input4 = GUICtrlCreateInput("", 210, 355, 91, 21,0X2000)
$Input4_1 = GUICtrlCreateInput("", 210, 325, 90, 25)
;GUICtrlSetData(-1, "男|女", "女")
$Label4 = GUICtrlCreateLabel("姓名:", 170, 300, 31, 17)
$Label5 = GUICtrlCreateLabel("关系:", 170, 330, 31, 17)
;$Label6 = GUICtrlCreateLabel("QQ:", 170, 360, 23, 17)
$Button5 = GUICtrlCreateButton("添加", 45, 380, 75, 25)
$Button6 = GUICtrlCreateButton("保存修改", 195, 380, 75, 25)
GUICtrlCreateGroup("", -99, -99, 1, 1)
$Group3 = GUICtrlCreateGroup("查询", 5, 410, 310, 40)
$Input5 = GUICtrlCreateInput("", 80, 423, 121, 21)
$Button7 = GUICtrlCreateButton("查询", 225, 421, 75, 25)
$Label7 = GUICtrlCreateLabel("姓名:", 35, 428, 30, 17)
GUICtrlCreateGroup("", -99, -99, 1, 1)
GUISetState(@SW_SHOW)
#EndRegion ### END Koda GUI section ###
$mdb_data_path = @ScriptDir & "\DB0.mdb"
$mdb_data_pwd = "test"
$T = "*"
$tblname = "mydb"
If FileExists($mdb_data_path) Then du()
While 1
$nMsg = GUIGetMsg()
Switch $nMsg
Case $GUI_EVENT_CLOSE
Exit
Case $Button1
If Not FileExists($mdb_data_path) Then
new()
Else
MsgBox(0, '欢迎使用', '版权公有 传播不究')
EndIf
Case $Button2
du()
Case $Button3
$Strn = GUICtrlRead(GUICtrlRead($ListView1))
$Strnspin = StringSplit($Strn, "|")
If $Strnspin = '0' Then
MsgBox(48, '提示', '请选择要更新的条目')
Else
up()
EndIf
Case $Button4
$Strn = GUICtrlRead(GUICtrlRead($ListView1))
$Strnspin = StringSplit($Strn, "|")
If $Strnspin = '0' Then
MsgBox(48, '提示', '请选择要删除的条目')
Else
del()
du()
EndIf
Case $Button5
xie()
Case $Button6
back()
Case $Button7
xun()
EndSwitch
WEnd
Func new()
$newMdb = ObjCreate('ADOX.Catalog')
$newMdb.Create("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & $mdb_data_path & ";Jet Oledb:Database Password=" & $mdb_data_pwd)
$newMdb.ActiveConnection.Close;建数据库
$addtbl = ObjCreate("ADODB.Connection")
$addtbl.Open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & $mdb_data_path & ";Jet Oledb:Database Password=" & $mdb_data_pwd)
$addtbl.Execute("CREATE TABLE " & $tblname)
$addtbl.Close;建表
$addfld = ObjCreate("ADODB.Connection")
$addfld.Open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & $mdb_data_path & ";Jet Oledb:Database Password=" & $mdb_data_pwd)
$addfld.Execute("ALTER TABLE " & $tblname & " ADD id identity(1, 1) primary key,name text(255) ,ssex text(50),qq int");id identity(1, 1) primary key为自动编号和主键
$addfld.Close;建列表名
MsgBox(0, '提示:', '创建成功!!!')
EndFunc ;==>new
Func du()
GUICtrlSendMsg($ListView1, $LVM_DELETEALLITEMS, 0, 0)
$addfld = ObjCreate("ADODB.Connection")
$addfld.Open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & $mdb_data_path & ";Jet Oledb:Database Password=" & $mdb_data_pwd)
$RS = ObjCreate("ADODB.Recordset")
$RS.ActiveConnection = $addfld
$RS.Open("Select " & $T & " From " & $tblname)
While Not $RS.eof And Not $RS.bof
If @error = 1 Then ExitLoop
GUICtrlCreateListViewItem($RS.Fields(0).value & "|" & $RS.Fields(1).value & "|" & $RS.Fields(2).value & "|" & $RS.Fields(3).value, $ListView1)
$RS.movenext
WEnd
$RS.close
$addfld.Close
EndFunc ;==>du
Func up() ;更新数据库内的某条记录到修改窗口
$Strn = GUICtrlRead(GUICtrlRead($ListView1))
$Strnspin = StringSplit($Strn, "|")
GUICtrlSetData($Input2, '')
;GUICtrlSetData($Input4,'')
$addfld = ObjCreate("ADODB.Connection")
$addfld.Open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & $mdb_data_path & ";Jet Oledb:Database Password=" & $mdb_data_pwd)
$RS = ObjCreate("ADODB.Recordset")
$RS.ActiveConnection = $addfld
$RS.Open("Select " & $T & " From " & $tblname & " WHERE id" & " = " & $Strnspin) ;$t : 表示一个连接$adtable :表示的是这个表的名称
GUICtrlSetData($Input2, $RS.Fields(1).value)
GUICtrlSetData($Input4_1, $RS.Fields(2).value)
;GUICtrlSetData($Input4,$RS.Fields (3).value)
$RS.movenext
$RS.close
$addfld.Close
EndFunc ;==>up
Func del()
$Strn = GUICtrlRead(GUICtrlRead($ListView1))
$Strnspin = StringSplit($Strn, "|")
$addfld = ObjCreate("ADODB.Connection")
$addfld.Open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & $mdb_data_path & ";Jet Oledb:Database Password=" & $mdb_data_pwd)
$sQuery = "DELETE FROM " & $tblname & " IN '" & $mdb_data_path & "' WHERE id" & " = " & $Strnspin
$addfld.execute($sQuery)
$addfld.close
EndFunc ;==>del
Func xie()
If GUICtrlRead($Input1) <> '' Then
$read1 = GUICtrlRead($Input1)
$read2 = GUICtrlRead($Input3_1)
$read3 = ""
$addfld = ObjCreate("ADODB.Connection")
$addfld.Open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & $mdb_data_path & ";Jet Oledb:Database Password=" & $mdb_data_pwd)
$addfld.execute("insert into " & $tblname & " (name,ssex,qq) values('" & $read1 & "','" & $read2 & "','" & $read3 & "')")
$addfld.close
du()
Else
MsgBox(48, '出错啦', '请检查输入是否完整')
EndIf
EndFunc ;==>xie
Func back()
If GUICtrlRead($Input2) <> '' Then
$read4 = GUICtrlRead($Input2)
$read5 = GUICtrlRead($Input4_1)
$read6 = ""
$Strn = GUICtrlRead(GUICtrlRead($ListView1))
$Strnspin = StringSplit($Strn, "|")
$addfld = ObjCreate("ADODB.Connection")
$addfld.Open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & $mdb_data_path & ";Jet Oledb:Database Password=" & $mdb_data_pwd)
$addfld.execute("update " & $tblname & " setname='" & $read4 & "',ssex='" & $read5 & "',qq='" & $read6 & "' WHERE id" & " = " & $Strnspin)
$addfld.close
du()
GUICtrlSetData($Input2, '')
;GUICtrlSetData($Input4,'')
Else
MsgBox(48, '出错啦', '请检查输入是否完整')
EndIf
EndFunc ;==>back
Func xun()
Dim $a1, $a2, $a3, $a4
$h1 = GUICtrlRead($Input5)
; GUICtrlSendMsg($ListView1, $LVM_DELETEALLITEMS, 0, 0)
$addfld = ObjCreate("ADODB.Connection")
$addfld.Open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & $mdb_data_path & ";Jet Oledb:Database Password=" & $mdb_data_pwd)
$RS = ObjCreate("ADODB.Recordset")
$RS.ActiveConnection = $addfld
$RS.Open("SELECT id,name,ssex,qq FROM " & $tblname & " WHERE name='" & $h1 & "'")
;这里添加
;-------------------------------------------------------------------------------------------------------------------
Local $count = _GUICtrlListView_GetItemCount($ListView1);检索列表视图的项目数量.
Local $txt = ""
For $i = 0 To $count - 1
;检索列表视图指定项目及其子项的全部文本,返回分隔的字符串
$txt &= _GUICtrlListView_GetItemTextString($ListView1, $i)
Next
StringRegExp($txt, "\|" & $h1 & "\|", 3);判断输入的名字是否正确
If Not @error Then
Local $m = StringRegExp($txt, $h1 & "\|([^\|\d]+)", 1)
If Not @error Then
Local $m2 = StringRegExp($txt, $m & "\|([^\|\d]+)", 1)
EndIf
;GUICtrlCreateListViewItem($h0 & "|" & $h1 & "|" & $h2 & "|" & $h3 , $ListView1);&"|"& $RS.Fields (7).value
If Not @error Then MsgBox(0, "获取性别", "你查询的" & " " & $h1 & " " & "他(她)父亲是: " & $m & " 所属的电视剧为:" & $m2)
EndIf
;-------------------------------------------------------------------------------------------------------------------
$RS.movenext
$RS.close
$addfld.Close
EndFunc ;==>xun
回复 8# lixiaolong
正是要这样的,谢谢lixiaolong大侠 额,感谢,学习了,学习了
页:
[1]