常用sqlite语句

sqlite语句

//创建表
create table if not exists yourTableName("
                @"id integer primary key autoincrement,"
                @"userId integer not null,"
                @"purchaseId integer not null,"
                @"title text,"
                @"dateId datetime default (datetime('now', 'localtime'))"
                @")
//删除整张表内容
delete from yourTableName
//使用%@，%ld占位
delete from yourTableName where purchaseId = %ld and userId = %ld
//插入数据
insert into yourTableName (userId, purchaseId, title, dateId) values (%ld, %ld, %@, %@) 
//更新数据
update yourTableName set dateId = datetime('now', 'localtime') where purchaseId = %ld and userId = %ld 
//据用户ID查询整张表有多少条数据
select count(*) from yourTableName where userId = 416673 
//根据一些约束条件查询数据库
select * from yourTableName where userId in (%d, 0) and createUserId != %d order by updateTime desc limit %d offset %d
//查询是否已存在某条记录
select count(*) from yourTableName where purchaseID = 101 and userID = 416673 
//给已存在表添加列
alter table yourTableName add createUserId integer default 0