Sqlite安装 现在各大Linux发型版均有现成的软件包可供安装,而且大部份系统都是自带有的,想确认系统里有没有运行下 $sqlite3 非Linux系统到:http://www.sqlite.org/download.html下 载安装 接下来就是sqlite命令行工具的使用
打开或创建数据库 $sqlite3 test.db3 这样就能打开或者创建一个新的数据库文件 $sqlite3 test.db3 SQLite version 3.6.23 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> 它看起来就是这个样子
SQlite内置命令 它除了能执行SQL语句以外还提供一组内置的命令,它们是以点.开始,比如说查看帮助信息就是.help退 出是.exit跟.quit
创建表 在命令行里大部份的SQL语句它都是支持的,现在来新建两个表 sqlite>create table Artists ( --->ArtistID INTEGER PRIMARY KEY, --->ArtistName TEXT); sqlite 对SQL语句大小写不敏感,所以大写小写随便 sqlite>create table CDs ( --->CDID INTEGER PRIMARY KEY, --->ArtistID INTEGER NOT NULL, --->Title TEXT NOT NULL, --->Date TEXT); 这里注意,除了INTEGER PRIMARY KEY其它字段是都可以是无类型的,也就是不关声明什么或者不声明,这个字段是可以存储任何数据的。
插入数据 sqlite>insert into Artists (ArtistID,ArtistName) values (NULL,'Peter Gabriel'); sqlite>insert into Artists (ArtistID,'Bruce Hornsby'); sqlite>insert into Artists (ArtistID,'Lyle Lovett'); sqlite>insert into Artists (ArtistID,'Beach Boys'); sqlite>insert into CDs (CDID,ArtistID,Title,Date) values (NULL,1,'So','1984'); sqlite>insert into CDs (CDID,'Us','1992'); sqlite>insert into CDs (CDID,2,'The Way It Is','1986'); sqlite>insert into CDs (CDID,'Scenes from the Southside','1990'); sqlite>insert into CDs (CDID,'Security',3,'Joshua Judges Ruth',4,'Pet Sounds','1966'); sqlite是支持导入sql文件的,只要使用内置命令.read即可,比如说我们将以上的命令建成一个sql文件, 命名为insert_table.sql
insert into Artists (ArtistID,’Peter Gabriel’); insert into Artists (ArtistID,’Bruce Hornsby’); insert into Artists (ArtistID,’Lyle Lovett’); insert into Artists (ArtistID,’Beach Boys’); insert into CDs (CDID,’So’,'1984′); insert into CDs (CDID,’Us’,'1992′); insert into CDs (CDID,’The Way It Is’,'1986′); insert into CDs (CDID,’Scenes from the Southside’,'1990′); insert into CDs (CDID,’Security’,’Joshua Judges Ruth’,’Pet Sounds’,'1966′);
接着在命令行里运行 sqlite>.read insert_table.sql
表查询 来看看现在这两张表里都有那些内容,执行 sqlite>select * from Artists; 和 sqlite>select * from CDs; 如果要同时看表头,请在运行查询语句前打开headers选项 sqlite>.headers ON 输出结果看起来应该是这样子
ArtisID|ArtistName 1 |Peter Gabriel 2 |Bruce Hornsby 3 |Lyle Lovett 4 |Beach Boys 和
CDID|ArtisID|Title |Date 1 |1 |So |1984 2 |1 |Us |1992 3 |2 |The Way It Is |1986 4 |2 |Scenes from the Southside|1990 5 |1 |Security |1990 6 |3 |Joshua Judges Ruth |1992 7 |4 |Pet Sounds |1966 其它的一些查询语句 sqlite>SELECT Title AS AlbumName FROM CDs;
sqlite>SELECT Title FROM CDs WHERE Date>=1990 ORDER BY Title;
sqlite>SELECT Date FROM CDs;
sqlite>SELECT DISTINCT Date FROM CDs;
sqlite>SELECT Title FROM CDs GROUP BY ArtistID;
多表查询 执行 sqlite>SELECT t1.ArtistName,CDs.Title FROM Artists t1,CDs WHERE t1.ArtistID=CDs.ArtistID 得到的结果 ArtistName |Title Peter Gabriel|So Peter Gabriel|Us Peter Gabriel|Security Bruce Hornsby|The Way It Is Bruce Hornsby|Scenes from the Southside Lyle Lovett |Joshua Judge Ruth Beach Boys |Pet Sounds
更新字段 插入一条数据 sqlite>insert into Artists (ArtistID,'Supernatural'); 如果要更改歌手名字为Santana sqlite>UPDATE Artists SET ArtistName ='Santana' WHERE ArtistID=5; 即可
删除字段 首先执行 sqlite>select * FROM CDs WHERE Title LIKE 'Super%'; 看看是不是想要删除的数据,是的话执行 sqlite>DELETE FROM CDs WHERE Title LIKE 'Super%'; 再运行 sqlite>select * FROM CDs WHERE Title LIKE 'Super%'; 看看是不是已经删除了?
如果嫌上面的命令行不够直观高效,而你又非常喜欢多用鼠标,那么推荐你安装SQLite Manager这个Firefox扩展程序,它真的非常方便。 (编辑:李大同)
【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!
|