Sqlite – 蓝天白云

sqlite中使用insert or replace into

分类：Database, Sqlite 阅读 (730) No Responses »

4月 022018

　　使用insert or replace into语句时，当要插入的数据对应的主键已存在，则删除原来那条，新建一条数据（类似更新）。

　　使用时的注意事项：

此表必须有一个主键
此主键应该是一个字符串值，如果是整数型的id类的用这个语句好像没什么意义

sqlite实现树型结构的查询

分类：Database, Sqlite 阅读 (1,037) No Responses »

10月 312017

此方法不可用！！！

使用RowID

select id,bname,parentid,1 as Level from boxes where parentid=0 
union all 
select a.id,a.bname,a.parentid,c.RowID as Level from boxes a 
inner join boxes b on a.parentid=b.id 
inner join 
(
      select parentid from boxes group by parentid
) c on a.parentid=c.parentid

select id,bname,parentid,1 as Level from boxes where parentid=0

union all

select a.id,a.bname,a.parentid,c.RowID as Level from boxes a

inner join boxes b on a.parentid=b.id

inner join

(

select parentid from boxes group by parentid

) c on a.parentid=c.parentid

sqlite中获取随机行的方法

分类：Database, Sqlite 阅读 (2,245) No Responses »

1月 082017

　　有的时候我们需要实现随机获取某些数据记录的功能，比如在一套题库内随机生成一组题目给学生进行考试，正常情况下我们可能会考虑通过代码实现，但是使用sqlite中提供的RANDOM()函数就可以轻松实现这样的功能。要将Random()函数放在order by后面以达到随机排序的功能，示例代码如下：

select * from cy order by RANDOM() desc limit 1

1	select * from cy order by RANDOM() desc limit 1

　　limit 1表示只取一条记录

　　现在功能实现了，但是这样效率比较低，我们对sql语句改造如下：

select * from cy where id in (select id from cy order by RANDOM() limit 10)

1	select * from cy where id in (select id from cy order by RANDOM() limit 10)

　　如果只想取其中的一条记录也可以把where id in替换成where id=，如下所示：

select * from cy where id = (select id from cy order by RANDOM() limit 10)

1	select * from cy where id = (select id from cy order by RANDOM() limit 10)

　　如果大家还有效率更高的写法，欢迎留言。

　　另外在MySQL中也有一个RAND()函数可以实现类似功能，在MSSQL中可以通过order by GUID来实现

Sqlite中使用Alter table增加字段

分类：Database, Sqlite 阅读 (2,061) No Responses »

8月 092016

　　Sqlite中增加字段的语法和其他的数据库一样使用Alter关键字，具体例子如下：

ALTER TABLE tablename ADD COLUMN fieldname NUMERIC DEFAULT 0

1	ALTER TABLE tablename ADD COLUMN fieldname NUMERIC DEFAULT 0

　　但是要注意的是，sqlite中不支持一次增加多列，所以如果你要增加多列的话就要执行多次alter table语句

Sqlite防止插入重复数据的方法

分类：Database, Sqlite 阅读 (2,067) No Responses »

4月 132016

为了防止插入重复的数据，我们可以在插入数据时使用insert or ignore into语句，如：

insert or ignore into tags(gid, tagid) values(1, 2)

1	insert or ignore into tags(gid, tagid) values(1, 2)

但是创建表的时候要给防止重复的字段加上UNIQUE唯一约束，如下创建语句：

CREATE TABLE gtags (id INTEGER PRIMARY KEY AUTOINCREMENT,
        gid INTEGER, tagid INTEGER,
        ctime TimeStamp NOT NULL DEFAULT(datetime('now','localtime')),
        UNIQUE(gid, tagid))

CREATE TABLE gtags (id INTEGER PRIMARY KEY AUTOINCREMENT,

gid INTEGER, tagid INTEGER,

ctime TimeStamp NOT NULL DEFAULT(datetime('now','localtime')),

UNIQUE(gid, tagid))

使用insert into插入重复数据时，数据库会报错，但是使用insert or ignore into数据库就不会报错了。