首页 > 移动开发 > Android > 正文
2.2.2 SQLite数据库示例
2015-04-22 14:11:59     我来说两句      
收藏    我要投稿

现在,已经学习了SQLite的一些功能,我们准备开发一个完整的应用程序:一个简单的contacts数据库。首先创建contacts表:

sqlite> create table contacts (

   ...> _id integer primary key autoincrement,

   ...> name text not null);

sqlite>

主键列的名称由Android系统需求确定(有关这一点后面的章节有详细讨论)。或许,略作思考后,给表添加一列以记录联系信息最近修改时间是一个好主意。这通过添加额外的一列和一对触发器来实现:
 

sqlite> alter table contacts add last_modified text;
sqlite> create trigger t_contacts_audit_i
   ...> after insert on contacts begin
   ...> update contacts set last_modified=datetime('now', 'utc')
   ...> where rowid = new.rowid;
   ...> end;
sqlite> create trigger t_contacts_audit_u
   ...> after update on contacts begin
   ...> update contacts set last_modified=datetime('now', 'utc')
   ...> where rowid = new.rowid;
   ...> end;
sqlite>
现在,尝试添加一条记录来验证一切是否正常工作:
sqlite> insert into contacts(name) values("Dianne");
sqlite> select * from contacts;
1|Dianne|2012-06-30 08:29:18
sqlite>
联系人需要地址。为此,再创建一个表:
sqlite> create table addresses(
   ...> _id integer primary key autoincrement,
   ...> number integer not null,
   ...> unit text,
   ...> street text not null,
   ...> city integer references cities);
sqlite>

如前所述,如果引用完整性的支持已启用,此表定义会导致一个错误,因为cities表还不存在。不过,在SQLite的默认配置中,则不会有问题:可以稍后定义它。

现在缺少的是把联系人及其地址关联起来的方法。为此,还需要新建一个表:
 

sqlite> create table contact_addresses(
   ...> contact integer references contacts,
   ...> address integer references addresses);
sqlite>
现在,添加数据:
sqlite> insert into contacts(name) values("Guy");
sqlite> insert into contacts(name) values("Chet");
sqlite> insert into contacts(name) values("Tim");
sqlite> insert into addresses(number, street)
   ...> values(651, "North 34th Street");
sqlite> insert into addresses(number, street)
   ...> values(345, "Spear Street");
sqlite> insert into addresses(number, street)
   ...> values(1600, "Amphitheatre Parkway");
sqlite> select * from contacts;
1|Dianne|2012-06-30 09:46:42
2|Guy|2012-06-30 09:46:42
3|Chet|2012-06-30 09:46:42
4|Tim|2012-06-30 09:46:42
sqlite> select * from addresses;
1|651||North 34th Street|
2|345||Spear Street|
3|1600||Amphitheatre Parkway|
sqlite> insert into  contact_addresses(contact, address) values(1,1);
sqlite> insert into  contact_addresses(contact, address) values(2,2);
sqlite> insert into  contact_addresses(contact, address) values(3,3);
sqlite> insert into  contact_addresses(contact, address) values(4,2);
sqlite>
联系人现在有了地址。可以使用join查询查看地址:
sqlite> select name, number, street
   ...> from contacts, addresses, contact_addresses
   ...> where contacts._id = contact_addresses.contact
   ...> and addresses._id = contact_addresses.address;
Dianne|651|North 34th Street
Guy|345|Spear Street
Chet|1600|Amphitheatre Parkway
Tim|345|Spear Street
sqlite>
也许你想确定每个地址分别有多少联系人。这可以使用count函数和group by子句来实现:
sqlite> select count(name), number, street
   ...> from contacts, addresses, contact_addresses
   ...> where contacts._id = contact_addresses.contact
   ...> and addresses._id = contact_addresses.address
   ...> group by number, street;
2|345|Spear Street
1|651|North 34th Street
1|1600|Amphitheatre Parkway
sqlite>

或许,现在你知道了每一个地址住着多少联系人,也许,你想要通过地址排序显示新联系人列表。在像这里的小型数据库中,那是没有问题的。但是,如果存在大量的地址,而你将使用门牌号和街道作为排序关键词,就应该创建索引(index)。索引只是优化了在索引列中查找特定值的过程。实现索引,需要消耗存储索引所需的空间,以及写操作时更新索引所需的时间。不改变和经常用作选择或排序标准的列(或一组列),是索引很好的选择。可以为联系人的名称和地址都创建索引。
 

sqlite> create index t_contacts_name on contacts(name);
sqlite> create index t_addresses_num_street on addresses(number,street);
sqlite>
再看一看联系人列表,这次以地址分组:
sqlite> select number, street, name
   ...> from contacts, addresses, contact_addresses
   ...> where contacts._id = contact_addresses.contact
   ...> and addresses._id = contact_addresses.address
   ...> order by number asc, street desc, name asc;
345|Spear Street|Guy
345|Spear Street|Tim
651|North 34th Street|Dianne
1600|Amphitheatre Parkway|Chet
sqlite>

假设一个联系人搬到了一个新的地址。也许你想跟踪联系人的当前地址,以及他们之前的地址。通过添加新的列到contact_addresses表中,称为moved_in,用于记录联系人搬入特定地址的日期。

...> alter table contact_addresses add moved_in text;

sqlite>

请注意,作为时间戳的新字段的类型是文本。在SQLite中,表示时间戳的标准方法是作为文本字段,其中的时间由固定格式字符串表示。

注意:关于时间格式的标准表示的更多信息请查看:http://www.sqlite.org/ lang_datefunc.html。

 

下面的代码给在数据库中所有记录设置搬入日期的默认值:
sqlite> update contact_addresses set moved_in=datetime(0, 'unixepoch');
sqlite> select * from contact_addresses;
1|1|1970-01-01 00:00:00
2|2|1970-01-01 00:00:00
3|3|1970-01-01 00:00:00
4|2|1970-01-01 00:00:00
sqlite>
现在,将联系人Guy的地址从Spear Street移到Amphithetre Parkway:
sqlite> insert into contact_addresses(contact, address, moved_in)
   ...> values(2, 3, datetime("2012-05-01"));
sqlite> select * from contact_addresses order by contact desc, moved_in asc;
4|2|1970-01-01 00:00:00
3|3|1970-01-01 00:00:00
2|2|1970-01-01 00:00:00
2|3|2012-05-01 00:00:00
1|1|1970-01-01 00:00:00
sqlite>
请注意,现在contact_addresses表有联系人2的两条记录,其中一个日期比另一个更新。类似于上一个的查询会显示Guy的所有地址:
sqlite> select name, number, street, moved_in
   ...> from contacts, addresses, contact_addresses
   ...> where contacts._id = contact_addresses.contact
   ...> and addresses._id = contact_addresses.address
   ...> order by name asc, moved_in desc;
Chet|1600|Amphitheatre Parkway|1970-01-01 00:00:00
Dianne|651|North 34th Street|1970-01-01 00:00:00
Guy|1600|Amphitheatre Parkway|2012-05-01 00:00:00
Guy|345|Spear Street|1970-01-01 00:00:00
Tim|345|Spear Street|1970-01-01 00:00:00
sqlite>
使用having子句,可以只显示至少搬过一次住所的联系人:
sqlite> select name, number, street
   ...> from contacts, addresses, contact_addresses
   ...> where contacts._id = contact_addresses.contact
   ...> and addresses._id = contact_addresses.address
   ...> group by name
   ...> having count(contacts._id) > 1
   ...> order by name desc;
Guy|1600|Amphitheatre Parkway
sqlite>
最后,使用一个子选择(sub-select),可以只显示每个联系人的最新地址:
sqlite> select name, number, street
   ...> from contacts, addresses, contact_addresses
   ...> where contacts._id = contact_addresses.contact
   ...> and addresses._id = contact_addresses.address
   ...> and moved_in = (
   ...> select max(moved_in)
   ...> from contact_addresses
   ...> where contact = contacts._id)
   ...> order by name desc;
Tim|345|Spear Street
Guy|1600|Amphitheatre Parkway
Dianne|651|North 34th Street
Chet|1600|Amphitheatre Parkway
此时,数据库模式(schema)看起来如下:
sqlite> .schema
CREATE TABLE addresses(
_id integer primary key autoincrement,
number integer not null,
unit text,
street text not null,
city integer references cities);
CREATE TABLE contact_addresses(
contact integer references contacts,
address integer references addresses,
moved_in text);
CREATE TABLE contacts (
_id integer primary key autoincrement,
name text not null, last_modified text);
CREATE INDEX t_addresses_num_street on addresses(number, street);
CREATE INDEX t_contacts_name on contacts(name);
CREATE TRIGGER contacts_audit_i
after insert on contacts begin
update contacts set last_modified=datetime('now', 'utc')
where rowid = new.rowid;
end;
CREATE TRIGGER contacts_audit_u
after update on contacts begin
update contacts set last_modified=datetime('now', 'utc')
where rowid = new.rowid;
end;
sqlite>

虽然简单,但这个示例演示了甚至非常复杂的数据库也在使用的许多概念。第3章将展示如何在Android应用程序中使用这些概念。



 



点击复制链接 与好友分享!回本站首页
分享到: 更多
您对本文章有什么意见或着疑问吗?请到论坛讨论您的关注和建议是我们前行的参考和动力  
上一篇:2.2.1 SQLite命令行
下一篇:2.3 本章小结
相关文章
图文推荐
排行
热门
文章
下载
读书

关于我们 | 联系我们 | 广告服务 | 投资合作 | 版权申明 | 在线帮助 | 网站地图 | 作品发布 | Vip技术培训
版权所有: 红黑联盟--致力于做最好的IT技术学习网站