sqlite命令.doc

Sqlite命令操作建立数据库档案用sqlite3建立数据库的方法很简单，只要在shell下键入（以下$符号为shell提示号，请勿键入）：$ sqlite3 foo.db 如果目录下没有foo.db，sqlite3就会建立这个数据库。sqlite3并没有强制数据库档名要怎么取，如果你喜欢，也可以取个foo.icannameitwhateverilike的档名。在sqlite3提示列下操作进入了sqlite3之后，会看到以下文字：SQLite version 3.1.3Enter .help for instructionssqlite 这时如果使用.help可以取得求助，.quit则是离开（请注意：不是quit）SQL的指令格式所以的SQL指令都是以分号（;）结尾的。如果遇到两个减号（-）则代表注解，sqlite3会略过去。建立资料表假设我们要建一个名叫film的资料表，只要键入以下指令就可以了：create table film(title, length, year, starring); 这样我们就建立了一个名叫film的资料表，里面有name、length、year、starring四个字段。这个create table指令的语法为：create table table_name(field1, field2, field3, .); table_name是资料表的名称，fieldx则是字段的名字。sqlite3与许多SQL数据库软件不同的是，它不在乎字段属于哪一种资料型态：sqlite3的字段可以储存任何东西：文字、数字、大量文字（blub），它会在适时自动转换。建立索引如果资料表有相当多的资料，我们便会建立索引来加快速度。好比说：create index film_title_index on film(title); 意思是针对film资料表的name字段，建立一个名叫film_name_index的索引。这个指令的语法为create index index_name on table_name(field_to_be_indexed); 一旦建立了索引，sqlite3会在针对该字段作查询时，自动使用该索引。这一切的操作都是在幕后自动发生的，无须使用者特别指令。加入一笔资料接下来我们要加入资料了，加入的方法为使用insert into指令，语法为：insert into table_name values(data1, data2, data3, .); 例如我们可以加入insert into film values (Silence of the Lambs, The, 118, 1991, Jodie Foster);insert into film values (Contact, 153, 1997, Jodie Foster);insert into film values (Crouching Tiger, Hidden Dragon, 120, 2000, Yun-Fat Chow);insert into film values (Hours, The, 114, 2002, Nicole Kidman); 如果该字段没有资料，我们可以填NULL。查询资料讲到这里，我们终于要开始介绍SQL最强大的select指令了。我们首先简单介绍select的基本句型：select columns from table_name where expression; 最常见的用法，当然是倒出所有数据库的内容：select * from film; 如果资料太多了，我们或许会想限制笔数：select * from film limit 10; 或是照着电影年份来排列：select * from film order by year limit 10; 或是年份比较近的电影先列出来：select * from film order by year desc limit 10; 或是我们只想看电影名称跟年份：select title, year from film order by year desc limit 10; 查所有茱蒂佛斯特演过的电影：select * from film where starring=Jodie Foster; 查所有演员名字开头叫茱蒂的电影(% 符号便是 SQL 的万用字符）：select * from film where starring like Jodie%; 查所有演员名字以茱蒂开头、年份晚于1985年、年份晚的优先列出、最多十笔，只列出电影名称和年份：select title, year from film where starring like Jodie% and year = 1985 order by year desc limit 10; 有时候我们只想知道数据库一共有多少笔资料：select count(*) from film; 有时候我们只想知道1985年以后的电影有几部：select count(*) from film where year = 1985; （进一步的各种组合，要去看SQL专书，不过你大概已经知道SQL为什么这么流行了：这种语言允许你将各种查询条件组合在一起而我们还没提到跨数据库的联合查询呢！）如何更改或删除资料了解select的用法非常重要，因为要在sqlite更改或删除一笔资料，也是靠同样的语法。例如有一笔资料的名字打错了：update film set starring=Jodie Foster where starring=Jodee Foster; 就会把主角字段里，被打成Jodee Foster的那笔（或多笔）资料，改回成Jodie Foster。delete from film where year output.sql 利用输出的资料，建立一个一模一样的数据库（加上以上指令，就是标准的SQL数据库备份了）：sqlite3 film.db .help.bailON|OFFStopafterhittinganerror.DefaultOFF.databasesListnamesandfilesofattacheddatabases.dump?TABLE?.DumpthedatabaseinanSQLtextformat.echoON|OFFTurncommandechoonoroff.exitExitthisprogram.explainON|OFFTurnoutputmodesuitableforEXPLAINonoroff.header(s)ON|OFFTurndisplayofheadersonoroff.helpShowthismessage.importFILETABLEImportdatafromFILEintoTABLE.indicesTABLEShownamesofallindicesonTABLE.loadFILE?ENTRY?Loadanextensionlibrary.modeMODE?TABLE?SetoutputmodewhereMODEisoneof:csvComma-separatedvaluescolumnLeft-alignedcolumns.(See.width)htmlHTMLcodeinsertSQLinsertstatementsforTABLElineOnevalueperlinelistValuesdelimitedby.separatorstringtabsTab-separatedvaluestclTCLlistelements.nullvalueSTRINGPrintSTRINGinplaceofNULLvalues.outputFILENAMESendoutputtoFILENAME.outputstdoutSmptMAINCONTINUEReplacethestandardprompts.quitExitthisprogram.readFILENAMEExecuteSQLinFILENAME.schema?TABLE?ShowtheCREATEstatements.separatorSTRINGChangeseparatorusedbyoutputmodeand.import.showShowthecurrentvaluesforvarioussettings.tables?PATTERN?ListnamesoftablesmatchingaLIKEpattern.timeoutMSTryopeninglockedtablesforMSmilliseconds.widthNUMNUM.Setcolumnwidthsforcolumnmodesqlite改变输出格式 sqlite3程序可以以八种不同的格式显示一个查询的结果：csv, 列, html, 插入, 行, 制表和tcl。你可以用.mode点命令在这些输出格式之间切换。 默认的输出格式是“列表”。在列表模式下，每条查询结果记录被写在一行中并且每列之间以一个字符串分割符隔开。默认的分隔符是一个管道符号（“|”）。列表符号在当你输出查询结果到另外一个符加处理的程序（如AWK）中去是尤为有用。sqlite.modelistsqliteselect*fromtbl1;hello|10goodbye|20sqlite 你可以用“.separator”点命令来改变分界符。例如，为了把分割符改为一个逗号和一个空格，你可以这样做：sqlite.separator,sqliteselect*fromtbl1;hello,10goodbye,20sqlite 在“line模式下，每一个位于条记录中的列在它自己那行显示。每行由列名、一个等号和列数据组成。下一条记录以一个空行隔开。这是一个行模式输出的例子：sqlite.modelinesqliteselect*fromtbl1;one=hellotwo=10one=goodbyetwo=20sqlite 在列模式下，每条记录在一个单独的行中以数据列对齐的方式显示。列如：sqlite.modecolumnsqliteselect*fromtbl1;onetwo-hello10goodbye20sqlite 在默认的情况下，每列至少10个字符宽。太宽的数据将被截取。你可以用“.width”命令来调整列宽。如下所示： sqlite.width126sqliteselect*fromtbl1;onetwo-hello10goodbye20sqlite上面例子中.width命令设置第一列宽为12第二列宽为6。其它的列宽不变。你可以指定与你查询结果需要的列数一样多的“.width”参数。 如果你指定一列宽为0，那么这个列宽将自动以下面三个数字中的最大值做为列宽：10、表头宽度和最宽的数据列的宽度。这可以让列自动调整宽度。每列的默认设置为自动调整的0值。 出现在输出开头两行的列标示可以用.header点命令关闭。在上面的例子中，列标示是打开的。可以用下面的方法关闭列标示： sqlite.headeroffsqliteselect*fromtbl1;hello10goodbye20sqlite 另外一个有用的输出模式是insert。在插入模式下，被子格式化为看起来像SQL INSERT语句的样式。你可以用插入模式来产生文件（便于）以后用于不同数据库的输入。 当指定插入模式时，你必须给定一个特定参数就是要插入的表名。例如： sqlite.modeinsertnew_tablesqliteselect*fromtbl1;INSERTINTOnew_tableVALUES(hello,10);INSERTINTOnew_tableVALUES(goodbye,20);sqlite 最新的输出格式是“html”。在这种模式下，sqlite3把查询的结果写做XHTML表。开始的和结束的（标记）没有写出，但有、和等分界符。html输出对 CGI来说是相当有用地。把结果写到文件中 默认情况下，sqlte3把结送到标准输出。你可以用“.output”命令改变它。只须把输出文件名做为.output命令的输出参数然后所有后续查询结果将被写到那个文件中。用“.output stdout”再一次改为标准输出。例如： sqlite.modelistsqlite.separator|sqlite.outputtest_file_1.txtsqliteselect*fromtbl1;sqlite.exit$cattest_file_1.txthello|10goodbye|20$ 查询数据库结构sqlite3程序提供几个有用的用于查询数据库结构的快捷命令。这些不是不可以用别的方式来实现。这些命令仅仅是一个快捷方式而已。 例如，为了查看数据库的表列表，你可以敲入“.tables”。 sqlite.tablestbl1tbl2sqlite “.tables”命令相似于设置列表模式然后执行接下来的查询： SELECT name FROM sqlite_master WHERE type IN (table,view) AND name NOT LIKE sqlite_%UNION ALL SELECT name FROM sqlite_temp_master WHERE type IN (table,view) ORDER BY 1事实上，你可以查看sqlite3的源代码（可以在源文件树的src/shell.c中），你可找到上面的具体的查询。“.indices”命令作用类似的方式是列出特定表的所有的索引。“.indics”命令须一个参数即所要索引表的表名。最后，但不是至少，是“.schema”命令。不带任何参数，“.schema”命令显示原始的用于创建当前数据库的 CREATE TABLE和CREATE INDEX语句。如果你给.schema命令一个表名，它显示原始的创建该表和它所有索引的CREATE语句。我们可以： sqlite.schemacreatetabletbl1(onevarchar(10),twosmallint)CREATETABLEtbl2(f1varchar(30)primarykey,f2text,f3real)sqlite.schematbl2CREATETABLEtbl2(f1varchar(30)primarykey,f2text,f3real)sqlite.schema命令可以用设置列表然后执行以下查询来实现： SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master)WHERE type!=metaORDER BY tbl_name, type DESC, name或者，如果你给.schema命令一个参数，由于你只想得到一个表的结构，查询可以是这样： SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master)WHERE type!=meta AND sql NOT NULL AND name NOT LIKE sqlite_%ORDER BY substr(type,2,1), name你可以担供一个参数给.schema命令。如果这橛，查询可以是这样的： SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master)WHERE tbl_name LIKE %s AND type!=meta AND sql NOT NULL AND name NOT LIKE sqlite_%ORDER BY substr(type,2,1), name在查询中“%S“为你的参数所取代。这使你可以询数据库结构的某个子集。 sqlite.schema%abc%与这些一起，“.table”命令也接受一个模式作为他的参数。如果你给“.table”一个参数，“%”将被前后扩展并且一个LIKE子句被附加到查询上。这让你可以列出只与特定模式相匹配的的表。“.datebasae”命令显示所有当前连接打开的数据库的一个列表。将允许一次到少两个。第一个是“main”，最初打开的那个数据库。第二个是temp，用于临时表的数据库。对于用ATTACH语句附加的数据也许有附加数据库列表。输出的第一列与之相联的数据库名，第二列是外部文件名。 sqlite.databases将整个数据库转换为ASCII文本文件.dump命令