sqlite3命令方式操作大全.docx

sqlite3命令方式操作大全.docx

《sqlite3命令方式操作大全.docx》由会员分享，可在线阅读，更多相关《sqlite3命令方式操作大全.docx（28页珍藏版）》请在冰豆网上搜索。

sqlite3命令方式操作大全

SQLite3命令操作大全

SQLite库包含一个名字叫做sqlite3的命令行,它可以让用户手工输入并执行面向SQLite数据库的SQL命令。

本文档提供一个样使用sqlite3的简要说明.

一.qlite3一些常用Sql语句操作

创建表:

createtable表名（元素名类型,…）;

删除表:

droptable表名;

插入数据:

insertinto表名values（,,,）;

创建索引:

create[unique]index索引名on表名（col….）;

删除索引：

dropindex索引名（索引是不可更改的，想更改必须删除重新建）

删除数据:

deletefrom表名;

更新数据:

update表名set字段=’修改后的内容’where条件;

增加一个列:

Altertable表名addcolumn字段数据类型;

选择查询:

select字段（以”,”隔开）from表名where条件;

日期和时间:

Selectdatetime（'now'）

日期:

selectdate（'now'）;

时间:

selecttime（'now'）;

总数：

selectcount（*）fromtable1;

求和：

selectsum（field1）fromtable1;

平均：

selectavg（field1）fromtable1;

最大：

selectmax（field1）fromtable1;

最小：

selectmin（field1）fromtable1;

排序：

select字段fromtable1orderby字段（desc或asc）;（降序或升序）

分组：

select字段fromtable1groupby字段,字段…;

限制输出:

select字段fromtable1limitxoffsety;

=select 字段 from table1limity,x;

（备注:

跳过y行，取x行数据）

（操作仍待完善）…

SQLite支持哪些数据类型些？

NULL值为NULL

INTEGER值为带符号的整型，根据类别用1，2，3，4，6，8字节存储

REAL值为浮点型，8字节存储

TEXT值为text字符串，使用数据库编码（UTF-8,UTF-16BEorUTF-16-LE）存储

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 包含了年、月、日、时、分、秒、千分之一秒。

如果将声明表的一列设置为INTEGERPRIMARYKEY，则具有：

1．每当你在该列上插入一NULL值时，NULL自动被转换为一个比该列中最大值大1的一个整数；

2．如果表是空的，将会是1；

算术函数

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（）

二.有关事务的操作

（成批操作的时候，启动事务，比不启动事务快n倍）

开始事物处理

BEGINTRANSACTION;

…………..

进行对数据库操作

…………..

事物提交

COMMIT;

具体事例如下:

假设有一个t1表，其中有"a","b","c"三列，如果要删除列c，以下过程描述如何做:

BEGINTRANSACTION;

CREATETEMPORARYTABLEt1_backup（a,b）;

INSERTINTOt1_backupSELECTa,bFROMt1;

DROPTABLEt1;

CREATETABLEt1（a,b）;

INSERTINTOt1SELECTa,bFROMt1_backup;

DROPTABLEt1_backup;

COMMIT;

三.启动sqlite3程序

仅仅需要敲入带有SQLite数据库名字的"sqlite3"命令即可。

如果文件不存在，则创建一个新的（数据库）文件。

然后sqlite3程序将提示你输入SQL。

敲入SQL语句（以分号“；”结束），敲回车键之后，SQL语句就会执行。

  例如，创建一个包含一个表"tb11"名字为"ex1"的SQLite数据库，你可以这样做：

数据库、表的建立，记录的添加、查询、修改和删除

F:

\>sqlite3database.db

sqlite>createtableadmin（usernametext,ageinteger）;

sqlite>insertintoadminvalues（'kuang',25）;

sqlite>select*fromadmin;

sqlite>updateadminsetusername='kk',age=24whereusername='kuang'andage=25;

sqlite>deletefromadminwhereusername='kk';

注：

每条sql语句后必须以";"号结尾！

$sqlite3ex1

SQLiteversion3.3.17

Enter".help"forinstructions

sqlite>createtabletbl1（onevarchar（10）,twosmallint）;

sqlite>insertintotbl1values（'hello!

',10）;

sqlite>insertintotbl1values（'goodbye',20）;

sqlite>select*fromtbl1;

hello!

|10

goodbye|20

sqlite>

    你可以通过敲你所用系统的文件结束符（通常是Ctrl+D）或者中断字符（通常是Ctrl+C）。

来终止sqlite3程序。

确定你在每个SQL语句结束敲入分号！

sqlite3程序通过查找分号来决定一个SQL语句的结束。

如果你省略分号，sqlite3将给你一个连续的命令提示符并等你给当前的SQL命令添加更多的文字。

这个特点让你输入多行的多个SQL语句，例如：

sqlite>createtabletbl2（

  ...>f1varchar（30）primarykey,

  ...>f2text,

  ...>f3real

  ...>）;

sqlite>

四.题外话：

查询SQLITE_MASTER表

  SQLite数据库的框架被保存在一个名叫"sqlite_master"的特殊的表中。

你可以像查询其它表一样通过执行“SELECT”查询这个特殊的表。

例如：

$sqlite3ex1

SQlitevresion3.3.10

Enter".help"forinstructions

sqlite>select*fromsqlite_master;

   type=table

   name=tbl1

tbl_name=tbl1

rootpage=3

   sql=createtabletbl1（onevarchar（10）,twosmallint）

sqlite>

  但你不能在sqlite_master表中执行诸如DROPTABLE,UPDATE,INSERT或者DELETE命令。

sqlite_master表在你创建、删除和索引数据库时自动更新这个表。

你不能手工更改sqlite_master表。

  TEMPORARY表的结构没有存储在"sqlite_master"表中，由于TEMPORARY表对应用是不可见的，而不是应用程序创建这个表。

TEMPORARY表结构被存储在另外一个名叫"sqlite_temp_master"的特定的表中。

"sqlite_temp_master"表是临时表自身。

五.sqlite3的特殊命令

  大多数候，sqlite3读入输入行，并把它们传递到SQLite库中去运行。

但是如果输入行以一个点（“.”）开始，那么这行将被sqlite3程序自己截取并解释。

这些“点命令”通常被用来改变查询输出的格式，或者执行鞭个预封包（预定义prepackaged）的查询语句。

你可以在任何时候输入“.help”，列出可用的点命令。

例如

sqlite>.help

.bailON|OFF      

Stopafterhittinganerror. DefaultOFF（遇到错误时不再继续,默认为OFF）

.databases      

 Listnamesandfilesofattacheddatabases（列出附加到数据库的数据库和文件）

.dump?

TABLE?

...   

DumpthedatabaseinanSQLtextformat（保存表到SQL格式的文件中,没有指表名,则保存所有.如果要保存到磁盘上需要结合.output命令.）

.echoON|OFF      

Turncommandechoonoroff（打开/关闭命令行回显）

.exit         

Exitthisprogram（退出该命令行）

.explainON|OFF    

TurnoutputmodesuitableforEXPLAINonoroff.（以合适的方式显示表头,不带参数则为开启）

.header（s）ON|OFF   

Turndisplayofheadersonoroff（是否显示表头,和.explain差别不是很大）

.help         

Showthismessage（显示帮助信息）

.importFILETABLE   

ImportdatafromFILEintoTABLE（从文件中导入表）

.indicesTABLE     

ShownamesofallindicesonTABLE（显示索引）

.loadFILE?

ENTRY?

Loadanextensionlibrary（加载一个扩展库）

.modeMODE?

TABLE?

SetoutputmodewhereMODEisoneof:

（设置输出模式,模式可以是以下几种）:

             csv   Comma-separatedvalues（ 以逗号分隔的值）

             column  Left-alignedcolumns. （See.width）（表头左对齐（参见.width））

             html   HTML

             insert  SQLinsertstatementsforTABLE（ SQL插入语句）

             line   Onevalueperline（ 一行一个值）

             list   Valuesdelimitedbyseparatorstring（值用string分隔）

             tabs   Tab-separatedvalues（以tab分隔的值）

             tcl   TCLlistelements（TCL列表元素）

.nullvalueSTRING   

PrintSTRINGinplaceofNULLvalues（以STRING代替NULL值的输出）

.outputFILENAME    

SendoutputtoFILENAME（输出到文件,而不是显示在屏幕上）

.outputstdout     

Sendoutputtothescreen（输出到屏幕上）

.promptMAINCONTINUE 

Replacethestandardprompts（替换默认的命令提示信息,默认就是sqlite>）

.quit         

Exitthisprogram（退出命令行）

.readFILENAME    

ExecuteSQLinFILENAME（执行FILENAME中的SQL语句）

.schema?

TABLE?

ShowtheCREATEstatements（显示CREATE语句）

.separatorSTRING   

Changeseparatorusedbyoutputmodeand.import

.show         

Showthecurrentvaluesforvarioussettings（显示各种设置）

.tables?

PATTERN?

ListnamesoftablesmatchingaLIKEpattern（查看数据库的表列表）

.timeoutMS      

TryopeninglockedtablesforMSmilliseconds（在MS时间内尝试打开被锁定的表）

.widthNUMNUM...   

Setcolumnwidthsfor"column"mode（设置column模式中的列的宽度）

.timerON|OFF

（显示CPU时间）

.vfsname?

AUX?

（显示VFS栈信息）

.restore?

DB?

FILE

（从文件中还原数据到表, 默认表为main）

sqlite>

六.改变输出格式（.mode）

  sqlite3程序可以以八种不同的格式显示一个查询的结果：

"csv","列","html","插入","行","制表"和"tcl"。

你可以用".mode"点命令在这些输出格式之间切换。

  默认的输出格式是“列表”。

在列表模式下，每条查询结果记录被写在一行中并且每列之间以一个字符串分割符隔开。

默认的分隔符是一个管道符号（“|”）。

列表符号在当你输出查询结果到另外一个符加处理的程序（如AWK）中去是尤为有用。

sqlite>.modelist

sqlite>select*fromtbl1;

hello|10

goodbye|20

sqlite>

  你可以用“.separator”点命令来改变分界符。

例如，为了把分割符改为一个逗号和一个空格，你可以这样做：

sqlite>.separator","

sqlite>select*fromtbl1;

hello,10

goodbye,20

sqlite>

  在“line"模式下，每一个位于条记录中的列在它自己那行显示。

每行由列名、一个等号和列数据组成。

下一条记录以一个空行隔开。

这是一个行模式输出的例子：

sqlite>.modeline

sqlite>select*fromtbl1;

one=hello

two=10

one=goodbye

two=20

sqlite>

  在列模式下，每条记录在一个单独的行中以数据列对齐的方式显示。

列如：

sqlite>.modecolumn

sqlite>select*fromtbl1;

one     two    

---------- ----------

hello    10    

goodbye   20    

sqlite>

    在默认的情况下，每列至少10个字符宽。

太宽（超过设定的宽度）的数据将被截取。

你可以用“.width”命令来调整列宽。

如下所示：

sqlite>.width126（第一列12,第二列6）

sqlite>select*fromtbl1;

one      two  

------------ ------

hello     10  

goodbye    20  

sqlite>

   上面例子中".width"命令设置第一列宽为12第二列宽为6。

其它的列宽不变。

你可以指定与你查询结果需要的列数一样多的“.width”参数。

  如果你指定一列宽为0，那么这个列宽将自动以下面三个数字中的最大值做为列宽：

10、表头宽度和最宽的数据列的宽度。

这可以让列自动调整宽度。

每列的默认设置为自动调整的0值。

  出现在输出开头两行的列标示可以用".header"点命令关闭。

在上面的例子中，列标示是打开的。

可以用下面的方法关闭列标示：

sqlite>.headeroff

sqlite>select*fromtbl1;

hello     10  

goodbye    20  

sqlite>

  另外一个有用的输出模式是"insert"。

在插入模式下，被子格式化为看起来像SQLINSERT语句的样式。

你可以用插入模式来产生文件（便于）以后用于不同数据库的输入。

  当指定插入模式时，你必须给定一个特定参数就是要插入的表名。

例如：

sqlite>.modeinsertnew_table

sqlite>select*fromtbl1;

INSERTINTO'new_table'VALUES（'hello',10）;

INSERTINTO'new_table'VALUES（'goodbye',20）;

sqlite>

  最新的输出格式是“html”。

在这种模式下，sqlite3把查询的结果写做XHTML表。

开始的

html输出对CGI来说是相当有用地。

把结果写到文件中

七.查询数据库结构

   sqlite3程序提供几个有用的用于查询数据库结构的快捷命令。

这些不是不可以用别的方式来实现。

这些命令仅仅是一个快捷方式而已。

  例如，为了查看数据库的表列表，你可以敲入“.tables”。

sqlite>.tables

tbl1

tbl2

sqlite>

  “.tables”命令相似于设置列表模式然后执行接下来的查询：

SELECTnameFROMsqlite_masterWHEREtypeIN（'table','view'）ANDnameNOTLIKE'sqlite_%'UNIONALLSELECTnameFROMsqlite_temp_masterWHEREtypeIN（'table','view'）ORDERBY1sqlite>.schema

createtabletbl1（onevarchar（10）,twosmallint）

CREATETABLEtbl2（

 f1varchar（30）primarykey,

  f2text,

  f3real

）

sqlite>.schematbl2

CREATETABLEtbl2（

  f1varchar（30）primarykey,

  f2text,

  f3real

）

sqlite>

八.事实上

你可以查看sqlite3的源代码（可以在源文件树的src/shell.c中），你可找到上面的具体的查询。

“.indices”命令作用类似的方式是列出特定表的所有的索引。

“.indics”命令须一个参数即所要索引表的表名。

最后，但不是至少，是“.schema”命令。

不带任何参数，“.schema”命令显示原始的用于创建当前数据库的CREATETABLE和CREATEINDEX语句。

如果你给".schema"命令一个表名，它显示原始的创建该表和它所有索引