Heim >Datenbank >MySQL-Tutorial >sqlite入门练习,通讯录增删改查
选择了sqlite作为sql语言入门练习的数据库,因而也选择了同样轻便的编程工具AAuto,其封装了sqlite库,方便好用且很快写出一个带界面的小工具,有兴趣的OSC友只需到官网下载快手开发工具,解压缩,新建工程运行以下代码即可看到效果。 透过编写这个工具了解及
选择了sqlite作为sql语言入门练习的数据库, 因而也选择了同样轻便的编程工具AAuto ,其封装了sqlite 库,方便好用且很快写出一个带界面的小工具 ,有兴趣的OSC友只需到官网下载快手开发工具,解压缩,新建工程运行以下代码即可看到效果。
透过编写这个工具了解及学习了以下知识:
1.sql基本语言, sqlite数据库创建、数据增加、修改、删除及模糊查询,命名参数运用。
2.列表视图listview控件的简单操作运用。
3.windows控件、窗体的命令响应、通知,消息循环等知识。
4.aauto的编程语言的数据类型、名字空间等特点。
SQLite AAuto Quicker
import win.ui; import crreaDb; /*DSG{{*/ var winform = ..win.form(text="快手通讯录 by 菲菲OSC";right=558;bottom=427;parent=...) winform.add( button={cls="button";text="添加记录";left=19;top=19;right=100;bottom=48;z=1}; button2={cls="button";text="编辑记录";left=115;top=19;right=196;bottom=48;z=2}; button3={cls="button";text="删除记录";left=211;top=19;right=292;bottom=48;z=3}; button4={cls="button";text="查找";left=476;top=22;right=536;bottom=48;z=5}; dress={cls="edit";left=375;top=152;right=541;bottom=176;edge=1;z=9}; edit={cls="edit";text="输入查找条件...";left=318;top=21;right=457;bottom=48;edge=1;z=4}; emal={cls="edit";left=375;top=193;right=541;bottom=217;edge=1;z=10}; listview={cls="listview";left=19;top=69;right=292;bottom=408;bgcolor=16777215;edge=1;fullRow=1;z=6}; mark={cls="richedit";text="备注";left=313;top=280;right=543;bottom=408;edge=1;hscroll=1;multiline=1;vscroll=1;z=17}; name={cls="edit";left=375;top=71;right=541;bottom=95;edge=1;z=7}; qq={cls="edit";left=375;top=234;right=541;bottom=258;edge=1;z=11}; static={cls="static";text="姓名";left=319;top=69;right=359;bottom=93;transparent=1;z=12}; static2={cls="static";text="电话";left=319;top=110;right=359;bottom=134;transparent=1;z=13}; static3={cls="static";text="地址";left=319;top=152;right=359;bottom=176;transparent=1;z=14}; static4={cls="static";text="邮箱";left=319;top=193;right=359;bottom=217;transparent=1;z=15}; static5={cls="static";text="QQ/MSN";left=319;top=234;right=359;bottom=258;transparent=1;z=16}; tel={cls="edit";left=375;top=112;right=541;bottom=136;edge=1;z=8} ) /*}}*/ var sqlConnection = sqlite("\res\contact.db") crreaDb.careaTable(sqlConnection) // 显示数据 var showData = function(sql){ for id,姓名,电话, 地址,邮箱,QQ ,备注 in sqlConnection.each("select rowid,* from tl") { winform.listview.addItem({tostring(id);姓名;电话; 地址;邮箱;QQ;备注}) } } winform.listview.insertColumn("id",1) winform.listview.insertColumn("姓名",100) winform.listview.insertColumn("电话",100) winform.listview.insertColumn("地址",120) winform.listview.insertColumn("邮箱",120) winform.listview.insertColumn("QQ",80) winform.listview.insertColumn("备注",150) showData() winform.show() winform.button.oncommand = function(id,event){ // 添加 var name = winform.name.text var tel = winform.tel.text var dress = winform.dress.text var em = winform.emal.text var qq = winform.qq.text var mark = winform.mark.text crreaDb.addData(sqlConnection,name,tel,dress,em,qq,mark) id = sqlConnection.lastInsertRowid() var data = sqlConnection.stepQuery("select rowid,* from [tl] where rowid = "+id) winform.listview.addItem({tostring(data.id);data.name;data.tel;data.dress;data.em;data.qq;data.mark}) } winform.button3.oncommand = function(id,event){ // 删除 var coun = winform.listview.selIndex var id = winform.listview.getItemText(coun) if(id){ crreaDb.delData(sqlConnection,id) winform.listview.clear() showData() winform.listview.selIndex = coun } else { winform.msgbox("请选择要删除的列") } } winform.button2.oncommand = function(id,event){ // 编辑修改 crreaDb.editData(sqlConnection,name,tel,dress,em,qq,mark,id) } winform.button4.oncommand = function(id,event){ // 查询 var vv = winform.edit.text var id = crreaDb.inQuiry(sqlConnection,vv) winform.listview.clear() if(id){ for(i=1;#id;1){ winform.listview.addItem({tostring(id[i].rowid);id[i].name;id[i].tel; id[i].dress;id[i].em;id[i].qq;id[i].mark}) } } else { winform.msgbox("找不到记录") } } winform.edit.wndproc = function(hwnd,message,wParam,lParam){ if( message = 0x202/*_WM_LBUTTONUP*/ ) winform.edit.text = "" } winform.listview.onnotify = function(id,code,ptr){ select(code) { case 0xFFFFFF9B/*_LVN_ITEMCHANGED*/ { if(winform.listview.selIndex){ var lvw = winform.listview winform.name.text = lvw.getItemText(lvw.selIndex,2) winform.tel.text = lvw.getItemText(lvw.selIndex,3) winform.dress.text = lvw.getItemText(lvw.selIndex,4) winform.emal.text = lvw.getItemText(lvw.selIndex,5) winform.qq.text = lvw.getItemText(lvw.selIndex,6) winform.mark.tex = lvw.getItemText(lvw.selIndex,7) } } } } win.loopMessage(); sqlConnection.exec("VACUUM") sqlConnection.close() return winform;
namespace crreaDb; import sqlite; careaTable = function(db){ if( not db.existsTable("tl") ){ //创建表 db.exec( "create table tl( name, tel , dress, em, qq , mark );" ) } } // 添加数据 addData = function(db,name,tell,dress,em,qq,mark){ var command = db.prepare("insert into [tl] values ( @name,@tel,@dress,@em,@qq,@mark );" ) command.bind.parameterAtNames( name = name; tel = tell; dress = dress; em = em; qq = qq; mark = mark ).step() command.finalize() } // 删除 delData = function(db,id){ db.exec("DELETE FROM [%s] where rowid=%d;","tl",id) } // 修改 editData = function(db,name,tel,dress,em,qq,mark,id){ db.exec("UPDATE [tl] SET name = @name,tel=@te1 ,dress=@dress,em=@em,QQ=@qq,mark=@mark WHERE rowid = @id;",{ name = name; tel = tel; dress = dress; em = em; qq = qq; mark = mark; id = id }); } // 模糊查询 inQuiry = function(db,re){ var tab = db.getTable("select rowid,* from [tl] where ifnull(name,'') || ifnull(tel,'') || ifnull(QQ,'') like '%"+ re+ "%'") return tab }