Heim >Datenbank >MySQL-Tutorial >sqlite入门练习,通讯录增删改查

sqlite入门练习,通讯录增删改查

PHP中文网
PHP中文网Original
2016-06-07 14:57:261306Durchsuche

选择了sqlite作为sql语言入门练习的数据库,因而也选择了同样轻便的编程工具AAuto,其封装了sqlite库,方便好用且很快写出一个带界面的小工具,有兴趣的OSC友只需到官网下载快手开发工具,解压缩,新建工程运行以下代码即可看到效果。 透过编写这个工具了解及

  选择了sqlite作为sql语言入门练习的数据库, 因而也选择了同样轻便的编程工具AAuto ,其封装了sqlite 库,方便好用且很快写出一个带界面的小工具 ,有兴趣的OSC友只需到官网下载快手开发工具,解压缩,新建工程运行以下代码即可看到效果。

透过编写这个工具了解及学习了以下知识:
1.sql基本语言, sqlite数据库创建、数据增加、修改、删除及模糊查询,命名参数运用。
2.列表视图listview控件的简单操作运用。
3.windows控件、窗体的命令响应、通知,消息循环等知识。
4.aauto的编程语言的数据类型、名字空间等特点。


        SQLite AAuto Quicker            

       

                                                sqlite入门练习,通讯录增删改查                                                                        

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
}

                               

Stellungnahme:
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn