SQlite命令行工具的使用.docx

最近使用sqlite较多，这东西现在是越用越着迷，不管是工作或者个人自娱自乐写东西，只要有数据存储首先就想到了sqlite，我一直对C/S模式的数据存储很反感，记得在知道sqlite之前的很长一段时间里一直用的是xml来存数我的数据，我喜欢将数据存储在单一文件里，我可以随时把数据带在身上，而且它们兼容性够好，拿着数据文件到那里都能用，哪怕是在嵌入式系统里照样玩儿的转SQLite特性1. ACID事务2. 零配置 无需安装和管理配置3. 储存在单一磁盘文件中的一个完整的数据库4. 数据库文件可以在不同字节顺序的机器间自由的共享5. 支持数据库大小至2TB6. 足够小, 大致3万行C代码, 250K7. 比一些流行的数据库在大部分普通数据库操作要快8. 简单, 轻松的API9. 包含TCL绑定, 同时通过Wrapper支持其他语言的绑定10. 良好注释的源代码, 并且有着90%以上的测试覆盖率11. 独立: 没有额外依赖12. Source完全的Open, 你可以用于任何用途, 包括出售它13. 支持多种开发语言,C, PHP, Perl, Java, ASP.NET,PythonSqlite安装现在各大Linux发型版均有现成的软件包可供安装，而且大部份系统都是自带有的，想确认系统里有没有运行下$sqlite3非Linux系统到：/download.html下载安装接下来就是sqlite命令行工具的使用打开或创建数据库$sqlite3 test.db3这样就能打开或者创建一个新的数据库文件$sqlite3 test.db3SQLite version 3.6.23Enter .help for instructionsEnter SQL statements terminated with a ;sqlite 它看起来就是这个样子SQlite内置命令它除了能执行SQL语句以外还提供一组内置的命令，它们是以点.开始，比如说查看帮助信息就是 .help退出是 .exit 跟 .quit创建表在命令行里大部份的SQL语句它都是支持的，现在来新建两个表sqlitecreate table Artists (-ArtistID INTEGER PRIMARY KEY,-ArtistName TEXT);sqlite 对SQL语句大小写不敏感，所以大写小写随便sqlitecreate table CDs (-CDID INTEGER PRIMARY KEY,-ArtistID INTEGER NOT NULL,-Title TEXT NOT NULL,-Date TEXT);这里注意，除了INTEGER PRIMARY KEY其它字段是都可以是无类型的，也就是不关声明什么或者不声明，这个字段是可以存储任何数据的。插入数据sqliteinsert into Artists (ArtistID,ArtistName) values (NULL,Peter Gabriel);sqliteinsert into Artists (ArtistID,ArtistName) values (NULL,Bruce Hornsby);sqliteinsert into Artists (ArtistID,ArtistName) values (NULL,Lyle Lovett);sqliteinsert into Artists (ArtistID,ArtistName) values (NULL,Beach Boys);sqliteinsert into CDs (CDID,ArtistID,Title,Date) values (NULL,1,So,1984);sqliteinsert into CDs (CDID,ArtistID,Title,Date) values (NULL,1,Us,1992);sqliteinsert into CDs (CDID,ArtistID,Title,Date) values (NULL,2,The Way It Is,1986);sqliteinsert into CDs (CDID,ArtistID,Title,Date) values (NULL,2,Scenes from the Southside,1990);sqliteinsert into CDs (CDID,ArtistID,Title,Date) values (NULL,1,Security,1990);sqliteinsert into CDs (CDID,ArtistID,Title,Date) values (NULL,3,Joshua Judges Ruth,1992);sqliteinsert into CDs (CDID,ArtistID,Title,Date) values (NULL,4,Pet Sounds,1966);sqlite是支持导入sql文件的，只要使用内置命令.read即可,比如说我们将以上的命令建成一个sql文件，命名为insert_table.sqlinsert into Artists (ArtistID,ArtistName) values (NULL,Peter Gabriel);insert into Artists (ArtistID,ArtistName) values (NULL,Bruce Hornsby);insert into Artists (ArtistID,ArtistName) values (NULL,Lyle Lovett);insert into Artists (ArtistID,ArtistName) values (NULL,Beach Boys);insert into CDs (CDID,ArtistID,Title,Date) values (NULL,1,So,1984);insert into CDs (CDID,ArtistID,Title,Date) values (NULL,1,Us,1992);insert into CDs (CDID,ArtistID,Title,Date) values (NULL,2,The Way It Is,1986);insert into CDs (CDID,ArtistID,Title,Date) values (NULL,2,Scenes from the Southside,1990);insert into CDs (CDID,ArtistID,Title,Date) values (NULL,1,Security,1990);insert into CDs (CDID,ArtistID,Title,Date) values (NULL,3,Joshua Judges Ruth,1992);insert into CDs (CDID,ArtistID,Title,Date) values (NULL,4,Pet Sounds,1966);接着在命令行里运行sqlite.read insert_table.sql表查询来看看现在这两张表里都有那些内容，执行sqliteselect * from Artists; 和sqliteselect * from CDs;如果要同时看表头，请在运行查询语句前打开headers选项sqlite.headers ON输出结果看起来应该是这样子ArtisID|ArtistName1 |Peter Gabriel2 |Bruce Hornsby3 |Lyle Lovett4 |Beach Boys和CDID|ArtisID|Title |Date1 |1 |So |19842 |1 |Us |19923 |2 |The Way It Is |19864 |2 |Scenes from the Southside|19905 |1 |Security |19906 |3 |Joshua Judges Ruth |19927 |4 |Pet Sounds |1966其它的一些查询语句sqliteSELECT Title AS AlbumName FROM CDs; sqliteSELECT Title FROM CDs WHERE Date=1990 ORDER BY Title; sqliteSELECT Date FROM CDs; sqliteSELECT DISTINCT Date FROM CDs; sqliteSELECT Title FROM CDs GROUP BY ArtistID; 多表查询执行sqliteSELECT t1.ArtistName,CDs.Title FROM Artists t1, CDs WHERE t1.ArtistID=CDs.ArtistID得到的结果ArtistName |TitlePeter Gabriel|SoPeter Gabriel|UsPeter Gabriel|SecurityBruce Hornsby|The Way It IsBruce Hornsby|Scenes from the SouthsideLyle Lovett |Joshua Judge RuthBeach Boys |Pet Sounds更新字段插入一条数据sqliteinsert into Artists (ArtistID,ArtistName) values (NULL,Supernatural);如果要更改歌手名字为SantanasqliteUPDATE Artists SET ArtistName =Santana WHERE ArtistID=5;即可删除字段首先执行sqliteselect * FROM CDs WHERE T