哈顿

思考与行动

思源笔记挂件Query使用

Posted on in 笔记 • 470 words • 3 minute read
Tags: 思源笔记

挂件官方仓库：

官方readme.md

相关参考资料：

思源笔记用户指南-数据库表简介

思源数据库表与字段 · 语雀

思源笔记sql使用示例

Q：SQL该怎么用？

查询demo入门

select 
  * 
from 
   blocks 
where 
  content like '%内容块%'
order by
   random()
limit 10

针对背景颜色的指示

背景颜色状态指示:

  • 白色:

    • 初始化
    • 正在处理查询

  • 绿色

    • 查询成功

  • 蓝色

    • 请继续操作

  • 黄色

    • 查询结果为空

  • 红色

    • SQL 语句错误
    • 未知错误

通过渲染属性进行控制

查询帮助文档 请从这里开始 及其下级所有的文档

SELECT
    '[' || b.content || '](siyuan://blocks/' || b.id || ')' AS __1____pre__文档标题,
    b.hpath AS __2__文档路径
FROM
    blocks AS b
WHERE
    b.type = 'd'
    AND b.hpath LIKE '%请从这里开始%'
ORDER BY
    b.path
LIMIT 10

字段详情

思源笔记用户指南-数据库表简介

查询思源数据库 blocks 表中所有字段

pragma table_info('blocks')

熟悉字段SQL

select * from spans;
block_id box content def_block_id def_block_parent_id def_block_path def_block_root_id id markdown path root_id type
1 20220628220937-aaz9mj1 20220705201611-nolr4wn 计算机资料、教程 20220619151302-t1bepna /20220619151302-t1bepna.sy 20220619151302-t1bepna 20220720143529-nqit5jl ((20220619151302-t1bepna '计算机资料、教程')) /20220612162923-ybvuqv7.sy 20220612162923-ybvuqv7

SiYuan搜索挂件实践

思源笔记用户指南

20210808180117-czj9bvb

查询子块数量小于2的文档块 (是空块的文档)

select *
from blocks 
where id in (
	select root_id 
	from blocks 
	where box != '20210808180117-czj9bvb'
	group by root_id
	having count(root_id) <=2
)

查询所有文档按照倒序排列(文章归档)

select 
content as  __1__文档标题, 
tag as __2____tag__标签 ,
created as  __3____date__创建时间,
updated as  __4____date__最后更新时间
from blocks 
where type = 'd'
and
box != '20210808180117-czj9bvb'
order by created desc

所有标签 展示

思路是：通过查询block tag 切割字符串，但是遇到了 split方法CPU打满，电脑文档94度的问题，不能运行这一SQL

select 
	tag as __1__标签1,
 	replace(tag,'##','#') as __3____tag__标签3 
from 
	blocks 
where 
	type = 'd'
	and tag != ''
	and box != '20210808180117-czj9bvb'
order by created desc

select 
	tag as __1__标签1,
	substr(tag,0,instr(tag,' ')),
        substr(tag,instr(tag,' ')+1)
from 
	blocks 
where 
	type = 'd'
	and tag != ''
	and box != '20210808180117-czj9bvb'
order by created desc

sqlite 从固定字符串分隔

WITH split(word, str) AS (
    -- alternatively put your query here
    -- SELECT '', category||',' FROM categories
    SELECT '', 'Auto,A,1234444,dsfdd,sd,sd,77'||','
    UNION ALL SELECT
    substr(str, 0, instr(str, ',')),
    substr(str, instr(str, ',')+1)
    FROM split WHERE str!=''
)

SELECT word FROM split WHERE word!='';

sqlite 从表中获取数据

WITH split(word, str) AS (
    SELECT '', (select chi_sht from tr_map_org_basic)||','
    UNION ALL SELECT
    substr(str, 0, instr(str, ',')),
    substr(str, instr(str, ',')+1)
    FROM split WHERE str!=''
) 
SELECT word FROM split WHERE word!='';

sqlite 从表中获取数据，union all 连接语句

WITH split(word, str) AS (
    SELECT '', (select chi_sht from tr_map_org_basic )||','
    UNION ALL SELECT
    substr(str, 0, instr(str, ',')),
    substr(str, instr(str, ',')+1)
    FROM split WHERE str!=''
) 
SELECT word FROM split WHERE word!=''
union all select eng_sht from tr_map_org_basic;

select 
	tag
from 
	blocks 
where 
	type = 'd'
	and tag != ''
	and substr(tag,0,instr(tag,' ')) != ''
	and box != '20210808180117-czj9bvb'

修改

WITH split(word, str) AS (
	SELECT '', (select tag from blocks where type = 'd' and tag != '' and substr(tag,0,instr(tag,' ')) != '' and box != '20210808180117-czj9bvb')||','
		UNION ALL SELECT
		substr(str, 0, instr(str, ',')),
		substr(str, instr(str, ',')+1)
		FROM split WHERE str!=''
)
SELECT word FROM split WHERE word!='';
 不能运行 CPU打满

WITH split(word, str) AS (
	SELECT '', (select tag from blocks where type = 'd' and tag != '' and substr(tag,0,instr(tag,' ')) != '' and box != '20210808180117-czj9bvb')||','
		UNION ALL SELECT
		substr(str, 0, instr(str, ' ')),
		substr(str, instr(str, ' ')+1)
		FROM split WHERE str!=''
)
SELECT word FROM split WHERE word!='';
 不能运行 CPU打满