SQLite3的使用
***数据库*** 严格地说,数据库是“按照数据结构来组织、存储和管理数据的仓库”。在经济管理的日常工作中,常常需要把某些相关的数据放进这样的“仓库”,并根据管理的需要进行相应的处理。例如,企业或事业单位的人事部门常常要把本单位职工的基本情况(职工号、姓名、年龄、性别、籍贯、工资、简历等)存放在表中,这张表就可以看成是一个数据库。有了这个"数据仓库"我们就可以根据需要随时查询某职工的基本情况,也可以查询工资在某个范围内的职工人数等等。 这种数据集合具有如下特点:尽可能不重复,以最优方式为某个特定组织的多种应用服务,其数据结构独立于使用它的应用程序,对数据的增、删、改和检索由统一软件进行管理和控制。从发展的历史看,数据库是数据管理的高级阶段,它是由文件管理系统发展起来的。 ***结构语言SQL (Structured Query Language)*** 一种对关系数据库中的数据进行定义和操作的句法,为大多数关系数据库管理系统所支持的工业标准。 结构化查询语言是一种数据库查询和程序设计语言,用于存取数据以及查询、更新和管理关系数据库系统。它不要求用户指定对数据的存放方法,也不需要用户了解具体的数据存放方式,所以具有完全不同底层结构的不同数据库系统可以使用相同的结构化查询语言语言作为数据输入与管理的接口。结构化查询语言语句可以嵌套,这使他具有极大的灵活性和强大的功能。 结构化查询语言包含6个部分:(需掌握一、二、五) 一:数据查询语言(DQL):其语句,也称为“数据检索语句”,用以从表中获得数据,确定数据怎样在应用程序给出。保留字SELECT是DQL(也是所有SQL)用得最多的动词,其他DQL常用的保留字有WHERE,ORDER BY,GROUP BY和HAⅥNG。这些DQL保留字常与其他类型的SQL语句一起使用。 二:数据操作语言(DML):其语句包括动词INSERT,UPDATE和DELETE。它们分别用于添加,修改和删除表中的行。也称为动作查询语言。 三:事务处理语言(TPL):它的语句能确保被DML语句影响的表的所有行及时得以更新。TPL语句包括BEGIN TRANSACTION,COMMIT和ROLLBACK。 四:数据控制语言(DCL):它的语句通过GRANT或REVOKE获得许可,确定单个用户和用户组对数据库对象的访问。某些RDBMS可用GRANT或REVOKE控制对表单个列的访问。 五:数据定义语言(DDL):其语句包括动词CREATE和DROP。在数据库中创建新表或删除表(CREAT TABLE或DROP TABLE);为表加入索引等。DDL包括许多与人数据库目录中获得数据有关的保留字。它也是动作查询的一部分。 六:指针控制语言(CCL):它的语句,像DECLARE CURSOR,FETCH INTO和UPDATE WHERE CURRENT用于对一个或多个表单独行的操作。 数据类型 结构化查询语言中有五种数据类型:字符型,文本型,数值型,逻辑型和日期型。 1.字符型 VARCHAR VS CHAR VARCHAR型和CHAR型数据的这个差别是细微的,但是非常重要。他们都是用来储存字符串长度小于255的字符。VARCHAR型字段的另一个突出的好处是它可以比CHAR型字段占用更少的内存和硬盘空间。当你的数据库很大时,这种内存和磁盘空间的节省会变得非常重要。使用VARCHAR型字段时,你不需要为剪掉你数据中多余的空格而操心。 2.文本型 TEXT 使用文本型数据,你可以存放超过二十亿个字符的字符串。当你需要存储大串的字符时,应该使用文本型数据。 注意文本型数据没有长度,而上一节中所讲的字符型数据是有长度的。一个文本型字段中的数据通常要么为空,要么很大。 无论何时,只要你能避免使用文本型字段,你就应该不适用它。文本型字段既大且慢,滥用文本型字段会使服务器速度变慢。文本型字段还会吃掉大量的磁盘空间。一旦你向文本型字段中输入了任何数据(甚至是空值),就会有2K的空间被自动分配给该数据。除非删除该记录,否则你无法收回这部分存储空间。 3.数值型整数INT、小数NUMERIC、钱数MONEY 一个INT型数据占用四个字节。这看起来似乎差别不大,但是在比较大的表中,字节数的增长是很快的。另一方面,一旦你已经创建了一个字段,要修改它是很困难的。因此,为安全起见,你应该预测以下,一个字段所需要存储的数值最大有可能是多大,然后选择适当的数据类型。 4.逻辑型 BIT 如果你使用复选框(CHECKBOX)从网页中搜集信息,你可以把此信息存储在BIT型字段中。BIT型字段只能取两个值:0或1。 当心,在你创建好一个表之后,你不能向表中添加BIT型字段。如果你打算在一个表中包含BIT型字段,你必须在创建表时完成。 5.日期型 DATETIME VS SMALLDATETIME 一个DATETIME型的字段可以存储的日期范围是从1753年1月1日第一毫秒到9999年12月31日最后一毫秒。DATETIME型字段在你输入日期和时间之前并不包含实际的数据,认识这一点是重要的。 SQL使用方式 简单的结构化查询语言查询只包括SELECT选择列表、FROM子句和WHERE子句。它们分别说明所查询列、查询的表或视图、以及搜索条件等。 一、选择列表 选择列表(select_list)指出所查询列,它可以是一组列名列表、星号、表达式、变量(包括局部变量和全局变量)等构成。 1、选择所有列 例如,下面语句显示testtable表中所有列的数据: SELECT *FROM testtable 2、选择部分列并指定它们的显示次序 查询结果集合中数据的排列顺序与选择列表中所指定的列名排列顺序相同。 3、更改列标题 在选择列表中,可重新指定列标题。定义格式为: 列标题=列名 列名列标题 如果指定的列标题不是标准的标识符格式时,应使用引号定界符,例如,下列语句使用汉字显示列标题: SELECT昵称=nickname,电子邮件=emailFROM testtable 4、删除重复行 SELECT语句中使用ALL或DISTINCT选项来显示表中符合条件的所有行或删除其中重复的数据行,默认为ALL。使用DISTINCT选项时,对于所有重复的数据行在SELECT返回的结果集合中只保留一行。 5、限制返回的行数 使用TOP n [PERCENT]选项限制返回的数据行数,TOP n说明返回n行,而TOP n PERCENT时,说明n是表示一百分数,指定返回的行数等于总行数的百分之几。 二、FROM子句 FROM子句指定SELECT语句查询及与查询相关的表或视图。在FROM子句中最多可指定256个表或视图,它们之间用逗号分隔。 在FROM子句同时指定多个表或视图时,如果选择列表中存在同名列,这时应使用对象名限定这些列所属的表或视图。 三、WHERE子句 WHERE子句设置查询条件,过滤掉不需要的数据行。 WHERE子句可包括各种条件运算符: 比较运算符(大小比较):>;、>=、=、<;、<=、<>;、!>;、!< 范围运算符(表达式值是否在指定的范围):BETWEEN…AND… NOT BETWEEN…AND… 列表运算符(判断表达式是否为列表中的指定项):IN(项1,项2……) NOT IN(项1,项2……) 模式匹配符(判断值是否与指定的字符通配格式相符):LIKE、NOT LIKE 空值判断符(判断表达式是否为空):IS NULL、IS NOT NULL 逻辑运算符(用于多条件的逻辑连接):NOT、AND、OR 1、范围运算符例:age BETWEEN 10 AND 30相当于age>=10 AND age<=30 2、列表运算符例:country IN ('Germany','China') 3、模式匹配符例:常用于模糊查找,它判断列值是否与指定的字符串格式相匹配。可用于char、varchar、text、ntext、datetime和smalldatetime等类型查询。 可使用以下通配字符: 百分号%:可匹配任意类型和长度的字符,如果是中文,请使用两个百分号即%%。 下划线_:匹配单个任意字符,它常用来限制表达式的字符长度。 方括号[]:指定一个字符、字符串或范围,要求所匹配对象为它们中的任一个。[^]:其取值也[]相同,但它要求所匹配对象为指定字符以外的任一个字符。 四、查询结果排序 使用ORDER BY子句对查询返回的结果按一列或多列排序。ORDER BY子句的语法格式为: ORDER BY {column_name [ASC|DESC]} [,…n] 其中ASC表示升序,为默认值,DESC为降序。ORDER BY不能按ntext、text和image数据类型进行排序。 ***SQL语句的添加、删除、修改*** ☆数据记录筛选☆注意:单双引号的用法可能有误(没有测式) Sql = "Select Distinct字段名From数据表" Distinct函数,查询数据库存表内不重复的记录 Sql = "Select Count(*) From数据表where字段名1>#18:0:0# and字段名1< #19:00# " count函数,查询数库表内有多少条记录,“字段名1”是指同一字段 例: set rs=conn.execute("select count(id) as idnum from news") response.write rs("idnum") sql="select * from数据表where字段名between值1 and值2" Sql="select * from数据表where字段名between #2003-8-10# and #2003-8-12#" 在日期类数值为2003-8-10 19:55:08的字段里查找2003-8-10至2003-8-12的所有记录,而不管是几点几分。 select * from tb_name where datetime between #2003-8-10# and #2003-8-12# 字段里面的数据格式为:2003-8-10 19:55:08,通过sql查出2003-8-10至2003-8-12的所有纪录,而不管是几点几分。 Sql="select * from数据表where字段名=字段值order by字段名[desc]" Sql="select * from数据表where字段名like '%字段值%' order by字段名[desc]" 模糊查询 Sql="select top 10 * from数据表where字段名order by字段名[desc]" 查找数据库中前10记录 Sql="select top n * form数据表order by newid()" 随机取出数据库中的若干条记录的方法top n,n就是要取出的记录数 Sql="select * from数据表where字段名in ('值1','值2','值3')" ☆添加数据记录☆ sql="insert into数据表(字段1,字段2,字段3 ?) valuess (值1,值2,值3 ?)" sql="insert into数据表valuess (值1,值3 ?)" 不指定具体字段名表示将按照数据表中字段的顺序,依次添加 sql="insert into目标数据表select * from源数据表" 把源数据表的记录添加到目标数据表 ☆更新数据记录☆ Sql="update数据表set字段名=字段值where条件表达式" Sql="update数据表set字段1=值1,字段2=值2 ??字段n=值n where条件表达式" 字段2=值2 ??字段n=值n " 没有条件则更新整个数据表中的指定字段值 ☆删除数据记录☆ Sql="delete from数据表where条件表达式" Sql="delete from数据表" 没有条件将删除数据表中所有记录) ☆数据记录统计函数☆ AVG(字段名)得出一个表格栏平均值 COUNT(*|字段名)对数据行数的统计或对某一栏有值的数据行数统计 MAX(字段名)取得一个表格栏最大的值 MIN(字段名)取得一个表格栏最小的值 SUM(字段名)把数据栏的值相加 引用以上函数的方法: sql="select sum(字段名) as别名from数据表where条件表达式" set rs=conn.excute(sql) 用rs("别名")获取统的计值,其它函数运用同上。 ☆数据表的建立和删除☆ CREATE TABLE数据表名称(字段1类型1(长度),字段2类型2(长度) ?? ) 例:CREATE TABLE tab01(name varchar(50),datetime default now()) DROP TABLE数据表名称(永久性删除一个数据表) ☆记录集对象的方法☆ rs.movenext将记录指针从当前的位置向下移一行 rs.moveprevious将记录指针从当前的位置向上移一行 rs.movefirst将记录指针移到数据表第一行 rs.movelast将记录指针移到数据表最后一行 rs.absoluteposition=N将记录指针移到数据表第N行 rs.absolutepage=N将记录指针移到第N页的第一行 rs.pagesize=N设置每页为N条记录 rs.pagecount根据pagesize的设置返回总页数 rs.recordcount返回记录总数 rs.bof返回记录指针是否超出数据表首端,true表示是,false为否 rs.eof返回记录指针是否超出数据表末端,true表示是,false为否 rs.delete删除当前记录,但记录指针不会向下移动 rs.addnew添加记录到数据表末端 rs.update更新数据表记录 ***常见关系型数库*** Oracle:大型 MySQL:小型 SQLite,是一款轻型的数据库,是遵守ACID的关联式数据库管理系统,它的设计目标是嵌入式的,而且目前已经在很多嵌入式产品中使用了它,它占用资源非常的低,在嵌入式设备中,可能只需要几百K的内存就够了。它能够支持Windows/Linux/Unix等等主流的操作系统,同时能够跟很多程序语言相结合,比如Tcl、C#、PHP、Java等,还有ODBC接口,同样比起Mysql、PostgreSQL这两款开源世界著名的数据库管理系统来讲,它的处理速度比他们都快。不像常见的客户端/服务器结构范例,SQLite引擎不是个程序与之通信的独立进程,而是连接到程序中成为它的一个主要部分。所以主要的通信协议是在编程语言内的直接API调用。这在消耗总量、延迟时间和整体简单性上有积极的作用。整个数据库(定义、表、索引和数据本身)都在宿主主机上存储在一个单一的文件中。它的简单的设计是通过在开始一个事务的时候锁定整个数据文件而完成的。 SQLite支持跨平台,操作简单,能够使用很多语言直接创建数据库,而不象Access一样需要Office的支持。如果你是个很小型的应用,或者你想做嵌入式开发,没有合适的数据库系统,那么现在你可以考虑使用SQLite。同时因为数据库结构简单,系统源代码也不是很多,也适合想研究数据库系统开发的专业人士。 SQLite支持哪些数据类型些? NULL 值为NULL INTEGER值为带符号的整型,根据类别用1,2,3,4,6,8字节存储 REAL 值为浮点型,8字节存储 TEXT 值为text字符串,使用数据库编码(UTF-8,UTF-16BE or UTF-16-LE)存储 BLOB 值为二进制数据,具体看实际输入;比如要在数据库中存放一张图片,这张图片就会以二进制形式存放,在sqlite中对应的数据类型就是BLOB 但实际上,sqlite3也接受如下的数据类型: smallint 16位元的整数 interger 32位元的整数 decimal(p,s) p精确值和s大小的十进位整数,精确值p是指全部有几个数(digits)大小值,s是指小数点後有几位数。如果没有特别指定,则系统会设为p=5; s=0。 float 32位元的实数。 double 64位元的实数。 char(n) n长度的字串,n不能超过254。 varchar(n) 长度不固定且其最大长度为n的字串,n不能超过4000。 graphic(n) 和char(n)一样,不过其单位是两个字元double-bytes,n不能超过127。这个形态是为了支援两个字元长度的字体,例如中文字。 vargraphic(n) 可变长度且其最大长度为n的双字元字串,n不能超过2000。 date 包含了年份、月份、日期。 time 包含了小时、分钟、秒。 timestamp 包含了年、月、日、时、分、秒、千分之一秒。 @如果不往数据库里面添加任何的表,这个数据库等于没有建立,不会在硬盘上产生任何文件,如果数据库已经存在,则会打开这个数据库。 @SQL标准规定,在字符串中,单引号需要使用逃逸字符,即在一行中使用两个单引号。 @INTEGER PRIMARY KEY属性,有什么特性? 如果将声明表的一列设置为INTEGER PRIMARY KEY,则具有: 1.每当你在该列上插入一NULL值时,NULL自动被转换为一个比该列中最大值大1的一个整数; 2.如果表是空的,将会是1; 注意该整数会比表中该列上的插入之前的最大值大1。该键值在当前的表中是唯一的。但有可能与已从表中删除的值重叠。要想建立在整个表的生命周期中唯一的键值,需要在INTEGER PRIMARY KEY上增加AUTOINCREMENT声明。那么,新的键值将会比该表中曾能存在过的最大值大1。
/*对于SQlite3,所有的API函数都有一个前缀:sqlite3_。这个前缀表明这些APIs由SQlite数据库产品提供,3代表版本。所有的常量都有一个前缀:SQLITE_。SQlite数据库的源码是完全开放的,对于提供给客户应用程序调用的API,函数名全部由小写字符组成。如sqlite3_create_function_v2就是一个PUBLIC API,而sqlite3CreateFunc就是一个SQlite内部函数。 对于使用SQlite数据库的客户来说,提供一套稳定的API非常重要,否则SQlite每出来一个Release版本,之前的API就焕然一新,那客户应用程序就需要修改自己的APP,付出很大的维护代价。所以SQlite数据库的API,一旦发布就不会删除或者修改,如果某个API确实有必要改进,就会提供一个加了“v2”后缀的新API函数,而保留以前的旧版本,这样客户APP无需修改依然可以正常运行。比如:sqlite3_create_function、sqlite3_create_function_v2。 SQlite的C APIs包括一定数量的数据结构,接近二百个函数,还有两三百个常量。虽然API的数量比较多,但用起来并不复杂,其中只有一部分函数是经常使用的,还有很多函数的功能相似。 该例直接使用SQLite提供的C接口API,而且使用新版的带_v2的API. 为求简单没有界面显示数据库内容,需要数据库查看工具辅助。 */ 以下是代码实现:
|