SQLite 3 一些基本使用.docx
《SQLite 3 一些基本使用.docx》由会员分享，可在线阅读，更多相关《SQLite 3 一些基本使用.docx（14页珍藏版）》请在冰豆网上搜索。
SQLite3一些基本使用
SQLite3一些基本使用
使用工具：
SQLiteExpertSQLite数据库管理工具，非常好用，类似我之前介绍的EMSSQLManager2007forMySQL
System.Data.SQLiteSQLiteForADO.NET驱动,类似我之前介绍的MySql.Data.dll
这2个工具都包括
1）SQL的指令格式
所有的SQL指令都是以分号（;）结尾的。
如果遇到两个减号（--）则代表注解，sqlite3会略过去。
2）建立资料表
假设我们要建一个名叫film的资料表，只要键入以下指令就可以了：
createtablefilm（title,length,year,starring）;
这样我们就建立了一个名叫film的资料表，里面有name、length、year、starring四个字段。
这个createtable指令的语法为：
createtabletable_name（field1,field2,field3,...）;
table_name是资料表的名称，fieldx则是字段的名字。
sqlite3与许多SQL数据库软件不同的是，它不在乎字段属于哪一种资料型态：
sqlite3的字段可以储存任何东西：
文字、数字、大量文字（blub），它会在适时自动转换。
3）建立索引
如果资料表有相当多的资料，我们便会建立索引来加快速度。
好比说：
createindexfilm_title_indexonfilm（title）;
意思是针对film资料表的name字段，建立一个名叫film_name_index的索引。
这个指令的语法为
createindexindex_nameontable_name（field_to_be_indexed）;
一旦建立了索引，sqlite3会在针对该字段作查询时，自动使用该索引。
这一切的操作都是在幕后自动发生的，无须使用者特别指令。
4）加入一笔资料
接下来我们要加入资料了，加入的方法为使用insertinto指令，语法为：
insertintotable_namevalues（data1,data2,data3,...）;
例如我们可以加入
insertintofilmvalues（'SilenceoftheLambs,The',118,1991,'JodieFoster'）;insertintofilmvalues（'Contact',153,1997,'JodieFoster'）;insertintofilmvalues（'CrouchingTiger,HiddenDragon',120,2000,'Yun-FatChow'）;insertintofilmvalues（'Hours,The',114,2002,'NicoleKidman'）;
如果该字段没有资料，我们可以填NULL。
5）查询资料
讲到这里，我们终于要开始介绍SQL最强大的select指令了。
我们首先简单介绍select的基本句型：
selectcolumnsfromtable_namewhereexpression;
最常见的用法，当然是倒出所有数据库的内容：
select*fromfilm;
如果资料太多了，我们或许会想限制笔数：
select*fromfilmlimit10;
或是照着电影年份来排列：
select*fromfilmorderbyyearlimit10;
或是年份比较近的电影先列出来：
select*fromfilmorderbyyeardesclimit10;
或是我们只想看电影名称跟年份：
selecttitle,yearfromfilmorderbyyeardesclimit10;
查所有茱蒂佛斯特演过的电影：
select*fromfilmwherestarring='JodieFoster';
查所有演员名字开头叫茱蒂的电影（'%'符号便是SQL的万用字符）：
select*fromfilmwherestarringlike'Jodie%';
查所有演员名字以茱蒂开头、年份晚于1985年、年份晚的优先列出、最多十笔，只列出电影名称和年份：
selecttitle,yearfromfilmwherestarringlike'Jodie%'andyear>=1985orderbyyeardesclimit10;
有时候我们只想知道数据库一共有多少笔资料：
selectcount（*）fromfilm;
有时候我们只想知道1985年以后的电影有几部：
selectcount（*）fromfilmwhereyear>=1985;
（进一步的各种组合，要去看SQL专书，不过你大概已经知道SQL为什么这么流行了：
这种语言允许你将各种查询条件组合在一起──而我们还没提到「跨数据库的联合查询」呢！
）
6）如何更改或删除资料
了解select的用法非常重要，因为要在sqlite更改或删除一笔资料，也是靠同样的语法。
例如有一笔资料的名字打错了：
updatefilmsetstarring='JodieFoster'wherestarring='JodeeFoster';
就会把主角字段里，被打成'JodeeFoster'的那笔（或多笔）资料，改回成JodieFoster。
deletefromfilmwhereyear<1970;
就会删除所有年代早于1970年（不含）的电影了。
7）其他sqlite的特别用法
sqlite可以在shell底下直接执行命令：
sqlite3film.db"select*fromfilm;"
输出HTML表格：
sqlite3-htmlfilm.db"select*fromfilm;"
将数据库「倒出来」：
sqlite3film.db".dump">output.sql
利用输出的资料，建立一个一模一样的数据库（加上以上指令，就是标准的SQL数据库备份了）：
sqlite3film.db 在大量插入资料时，你可能会需要先打这个指令：
begin;
插入完资料后要记得打这个指令，资料才会写进数据库中：
commit;
8）SQLite3支持数据类型
NULL
INTEGER
REAL
TEXT
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 包含了年、月、日、时、分、秒、千分之一秒。
9）SQLite分页
如果我要去11-20的Account表的数据
Select*FromAccountLimit9Offset10;
以上语句表示从Account表获取数据，跳过10行，取9行
嗯，我觉得这个特性足够让很多的web中型网站使用这个了。
也可以这样写select*fromaccountlimit10,9和上面的的效果一样。
这种写法MySQL也支持。
10）SQLite建立自动增长字段
简短回答：
声明为INTEGERPRIMARYKEY的列将会自动增长。
长一点的答案：
如果你声明表的一列为INTEGERPRIMARYKEY，那么，每当你在该列上插入一NULL值时，NULL自动被转换为一个比该列中最大值大1的一个整数，如果表是空的，将会是1。
（如果是最大可能的主键9223372036854775807，那个，将键值将是随机未使用的数。
）如，有下列表：
CREATETABLEt1（
aINTEGERPRIMARYKEY,
bINTEGER
）;
在该表上，下列语句
INSERTINTOt1VALUES（NULL,123）;
在逻辑上等价于：
INSERTINTOt1VALUES（（SELECTmax（a）FROMt1）+1,123）;
有一个新的API叫做sqlite3_last_insert_rowid（），它将返回最近插入的整数值。
注意该整数会比表中该列上的插入之前的最大值大1。
该键值在当前的表中是唯一的。
但有可能与已从表中删除的值重叠。
要想建立在整个表的生命周期中唯一的键值，需要在INTEGERPRIMARYKEY上增加AUTOINCREMENT声明。
那么，新的键值将会比该表中曾能存在过的最大值大1。
如果最大可能的整数值在数据表中曾经存在过，INSERT将会失败，并返回SQLITE_FULL错误代码。
11）SQLite内建函数表
算术函数
abs（X）
返回给定数字表达式的绝对值。
max（X,Y[,...]）
返回表达式的最大值。
min（X,Y[,...]）
返回表达式的最小值。
random（*）
返回随机数。
round（X[,Y]）
返回数字表达式并四舍五入为指定的长度或精度。
字符处理函数
length（X）
返回给定字符串表达式的字符个数。
lower（X）
将大写字符数据转换为小写字符数据后返回字符表达式。
upper（X）
返回将小写字符数据转换为大写的字符表达式。
substr（X,Y,Z）
返回表达式的一部分。
randstr（）
quote（A）
like（A,B）
确定给定的字符串是否与指定的模式匹配。
glob（A,B）
条件判断函数
coalesce（X,Y[,...]）
ifnull（X,Y）
nullif（X,Y）
集合函数
avg（X）
返回组中值的平均值。
count（X）
返回组中项目的数量。
max（X）
返回组中值的最大值。
min（X）
返回组中值的最小值。
sum（X）
返回表达式中所有值的和。
其他函数
typeof（X）
返回数据的类型。
last_insert_rowid（）
返回最后插入的数据的ID。
sqlite_version（*）
返回SQLite的版本。
change_count（）
返回受上一语句影响的行数。
last_statement_change_count（）
|xGv00|fcc8d4de8197f69fde70263fb4d52380
5、SQLite学习笔记
（1）创建数据库
在命令行中切换到sqlite.exe所在的文件夹
在命令中键入sqlite3test.db;即可创建了一个名为test.db的数据库
由于此时的数据库中没有任何表及数据存在，这时候是看不到test.db的，必须往里面插入一张表即可看到数据库
（2）创建表
createtableTest（IdIntegerprimarykey,valuetext）;
此时即可完成表的创建，当把主键设为Integer时，则该主键为自动增长，插入数据时，可直接使用如下语句:
insertintoTestvalues（null,'Acuzio'）;
（3）获取最后一次插入的主键
selectlast_insert_rowid（）;
（4）显示行数和头
sqlite>.modecol
sqlite>.headerson
在数据库查询的时候，显示行数和头!
（5）在DOS中，键入Ctrl+C,退出数据库，Unix中，使用Ctrl+D
（6）SQLiteMasterTableSchema
-----------------------------------------------------------------
Name Description
-----------------------------------------------------------------
type Theobject’stype（table,index,view,trigger）
name Theobject’sname
tbl_name Thetabletheobjectisassociatedwith
rootpage Theobject’srootpageindexinthedatabase（whereitbegins）
sql Theobject’sSQLdefinition（DDL）
eg.
sqlite>.modecol
sqlite>.headerson
sqlite>selecttype,name,tbl_name,sqlfromsqlite_masterorderbytype;
这样就能看到所有数据库中的信息，表、索引、视图等等
（7）导出数据
.output[filename],导出到文件中,如果该文件不存在，则自动创建
.dump导出数据命令
.outputstdout返回输出到屏幕（进行其他操作）
eg.
sqlite>.outputAcuzio.sql
sqlite>.dump
sqlite>.outputstdout
这样就可以把数据导入到Acuzio.sql中
（8）导入数据
导入数据使用.read命令
eg.
如导入（7）中的数据
sqlite>.readAcuio.sql
（9）备份数据库
在切换到Sqlite文件夹
sqlite3test.db.dump>test.sql
如果在数据库中
sqlite>.outputfile.sql
sqlite>.dump
sqlite>.exit
（10）导入数据库
在切换到Sqlite文件夹
sqlite3test.db （11）备份二进制格式数据库
vacuum:
释放掉已经被删除的空间（数据和表等被删除，不会被清空空间）
sqlite3test.dbVACUUM
cptest.dbtest.backup
（12）获取数据库信息
升级会员 