Clever Geek Handbook

您可能会错过的SQLite功能

如果您使用SQLite，但不遵循它的发展，那么可能会使代码变得更容易且查询速度更快的某些事情没有引起注意。根据削减，我试图列出其中最重要的。



部分代码（部分索引）

构建索引时，可以指定要包含在索引中的行的条件，例如，其中一列不为空，但另一列等于指定值。 



create index idx_partial on tab1(a, b) where a is not null and b = 5;
select * from tab1 where a is not null and b = 5; --> search table tab1 using index


表达式上的索引（表达式上的索引）

如果在查询表时经常使用表达式，则可以在其上建立索引。但是，应该记住，尽管优化器不是很灵活，但表达式中列的排列将导致拒绝使用索引。 



create index idx_expression on tab1(a + b);
select * from tab1 where a + b > 10; --> search table tab1 using index ...
select * from tab1 where b + a > 10; --> scan table


计算列（生成列）

如果列数据是对其他列上的表达式求值的结果，则可以创建一个虚拟列。有两种类型：VIRTUAL（每次读取表时都会计算并且不占用空间）和STORED（STORED）（将数据写入表并占用空间时会计算）。当然，您不能直接将数据写入此类列。 



create table tab1 (
	a integer primary key,
	b int,
	c text,
	d int generated always as (a * abs(b)) virtual,
	e text generated always as (substr(c, b, b + 1)) stored
);


R树索引

该索引旨在在一系列值/对象嵌套中进行快速搜索，即 当矩形对象通过其位置和大小指定并且需要查找与当前对象相交的所有对象时，这是地理系统的典型任务。该索引被实现为虚拟表（请参见下文），这仅是本质上的索引。R-Tree索引支持要求使用标记构建SQLite SQLITE_ENABLE_RTREE（默认情况下未选中）。 



create virtual table idx_rtree using rtree (
	id,              -- 
	minx, maxx,      --   c x 
	miny, maxy,      --   c y 
	data             --    
);  

insert into idx_rtree values (1, -80.7749, -80.7747, 35.3776, 35.3778); 
insert into idx_rtree values (2, -81.0, -79.6, 35.0, 36.2);

select id from idx_rtree 
where minx >= -81.08 and maxx <= -80.58 and miny >= 35.00  and maxy <= 35.44;


重命名列

SQLite无法很好地支持表结构的更改，因此，在创建表后，您将无法更改约束或删除列。从3.25.0版开始，您可以重命名列，但不能更改其类型。 



alter table tbl1 rename column a to b;


对于其他操作，还提出了一切以创建具有所需结构的表，在其中传输数据，删除旧表并重命名新表的建议。



添加行，否则更新（Upsert）

使用on conflict运算符class insert，可以添加新行，如果已经有一个具有相同值的键，则对其进行更新。 



create table vocabulary (word text primary key, count int default 1);
insert into vocabulary (word) values ('jovial') 
  on conflict (word) do update set count = count + 1;


声明更新

如果需要根据另一张表中的数据更新一行，则您以前必须为每列或使用子查询with。从3.33.0版开始，运算符已update使用关键字进行了扩展，from现在您可以执行此操作 



update inventory
   set quantity = quantity - daily.amt
  from (select sum(quantity) as amt, itemid from sales group by 2) as daily
 where inventory.itemid = daily.itemid;


CTE查询，带有（公用表表达式）的类

该类with可用作请求的临时表示。在版本3.34.0中，声明了with在内部使用它的可能性with。 



with tab2 as (select * from tab1 where a > 10), 
  tab3 as (select * from tab2 inner join ...)
select * from tab3;


加上关键字recursive，with它可用于要对相关数据进行操作的查询。 



--  
with recursive cnt(x) as (
  values(1) union all select x + 1 from cnt where x < 1000
)
select x from cnt;

--         
create table tab1 (id, parent_id);
insert into tab1 values 
  (1, null), (10, 1), (11, 1), (12, 10), (13, 10),
  (2, null), (20, 2), (21, 2), (22, 20), (23, 21);

--    
with recursive tc (id) as (
	select id from tab1 where id = 10	
	union 
	select tab1.id from tab1, tc where tab1.parent_id = tc.id
)

--      
with recursive tc (id, parent_id) as (
	select id, parent_id from tab1 where id in (12, 21)
	union 
	select tc.parent_id, tab1.parent_id 
	from tab1, tc where tab1.id = tc.parent_id
)
select distinct id from tc where parent_id is null order by 1;

--    , .   
create table org(name text primary key, boss text references org);
insert into org values ('Alice', null), 
  ('Bob', 'Alice'), ('Cindy', 'Alice'), ('Dave', 'Bob'), 
  ('Emma', 'Bob'), ('Fred', 'Cindy'), ('Gail', 'Cindy');

with recursive
  under_alice (name, level) as (
    values('Alice', 0)
    union all
    select org.name, under_alice.level + 1
      from org join under_alice on org.boss = under_alice.name
     order by 2
  )
select substr('..........', 1, level * 3) || name from under_alice;


窗口功能（Window Functions）

从3.25.0版开始，SQLite中提供了窗口函数（有时也称为分析函数），使您可以对一条数据（窗口）执行计算。 



--    
create table tab1 (x integer primary key, y text);
insert into tab1 values (1, 'aaa'), (2, 'ccc'), (3, 'bbb');
select x, y, row_number() over (order by y) as row_number from tab1 order by x;

--     
create table tab1 (a integer primary key, b, c);
insert into tab1 values (1, 'A', 'one'),
  (2, 'B', 'two'), (3, 'C', 'three'), (4, 'D', 'one'), 
  (5, 'E', 'two'), (6, 'F', 'three'), (7, 'G', 'one');

--        
select a, b, group_concat(b, '.') over (order by a rows between 1 preceding and 1 following) as prev_curr_next from tab1;

--    (,   c)       
select c, a, b, group_concat(b, '.') over (partition by c order by a range between current row and unbounded following) as curr_end from tab1 order by c, a;

--      
select c, a, b, group_concat(b, '.') filter (where c <> 'two') over (order by a) as exceptTwo from t1 order by a;


SQLite实用程序

除了sqlite3 CLI之外，还有两个实用程序可用。第一个-sqldiff，使您不仅可以按结构，而且可以按数据比较数据库（或单独的表）。第二个sqlite3_analizer用于显示有关数据库文件中的表和索引如何有效利用空间的信息。可以从dbstat虚拟表中获得类似的信息（SQLITE_ENABLE_DBSTAT_VTAB在编译SQLite时需要一个标志）。



从3.22.0开始，CLI sqlite3包含一个（实验性）.expert命令，该命令可以告诉您为输入的查询添加哪个索引。



创建真空备份

从3.27.0版开始，该命令已vacuum扩展了一个关键字into，该关键字使您可以创建数据库的副本而无需直接从SQL停止它。它是Backup API的简单替代方案。 



vacuum into 'D:/backup/' || strftime('%Y-%M-%d', 'now') || '.sqlite';


Printf功能

该功能类似于C功能。在这种情况下，NULL-值被解释为一个空字符串%s和0用于数字的占位符。 



select 'a' || ' 123 ' || null; --> null
select printf('%s %i %s', 'a', 123, null); --> 123 a
select printf('%s %i %i', 'a', 123, null); --> 123 a 0


时间和日期

在SQLite Date和中Time。尽管可以使用这些类型的列创建表，但与不指定类型的创建列相同，因此此类列中的数据将存储为文本。这在查看数据时很方便，但是有很多缺点：搜索无效，如果没有索引，则数据会占用大量空间，并且没有时区。为避免这种情况，您可以将数据存储为unix time，即 自1970年1月1日午夜以来的秒数。 



select strftime('%Y-%M-%d %H:%m', 'now'); --> UTC 
select strftime('%Y-%M-%d %H:%m', 'now', 'localtime'); -->  
select strftime('%s', 'now'); --  Unix- 
select strftime('%s', 'now', '+2 day'); -->  unix-   
--  unix-     - 21-11-2020 15:25:14
select strftime('%d-%m-%Y %H:%M:%S', 1605961514, 'unixepoch', 'localtime')


杰森

从3.9.0版开始，您可以在SQLite中使用json（需要SQLITE_ENABLE_JSON1编译时标志或已加载的扩展名）。Json数据存储为文本。功能的结果也是文本。 



select json_array(1, 2, 3); --> [1,2,3] ()
select json_array_length(json_array(1, 2, 3)); --> 3
select json_array_length('[1,2,3]'); --> 3
select json_object('a', json_array(2, 5), 'b', 10); --> {"a":[2,5],"b":10} ()
select json_extract('{"a":[2,5],"b":10}', '$.a[0]');  --> 2
select json_insert('{"a":[2,5]}', '$.c', 10); --> {"a":[2,5],"c":10} ()
select value from json_each(json_array(2, 5)); --> 2  2, 5
select json_group_array(value) from json_each(json_array(2, 5)); --> [2,5] ()


全文搜索

像json一样，全文搜索需要SQLITE_ENABLE_FTS5在编译或加载扩展名时设置标志。为了进行搜索，首先创建一个具有索引字段的虚拟表，然后使用通常的表将数据加载到该表中insert。应该记住的是，扩展程序在工作时会创建其他表，而创建的虚拟表会使用其数据。 



create virtual table emails using fts5(sender, body);
SELECT * FROM emails WHERE emails = 'fts5'; -- sender  body  fts5


扩展名

可以通过可加载模块添加SQLite功能。其中一些已经在上面提到了-json1和fts。



扩展可以用于添加用户定义的函数（不仅是标量函数，例如crc32，而且可以是聚合函数甚至是窗口函数）和虚拟表。虚拟表是数据库中存在的表，但是其数据由扩展名处理，而根据实现的不同，其中一些需要创建 



create virtual table temp.tab1 using csv(filename='thefile.csv');
select * from tab1;


其他（所谓的表值）可以立即使用。 



select value from generate_series(5, 100, 5);
...这里

列出了一些虚拟表。



一个扩展可以同时实现功能和虚拟表。例如，json1包含13个标量和2个聚合函数以及两个虚拟表json_each和json_tree。要编写自己的函数，您只需要具备C的基本知识，并从SQLite存储库中解析扩展代码。实现自己的虚拟表要稍微复杂一些（显然这就是为什么它们很少的原因）在这里，我们可以推荐Jay A. Kreibich撰写的《Using SQLite》，Michael Owens的文章，存储库中的模板以及generate_series代码作为表值函数的稍作过时的书。



另外，扩展可以实现特定于操作系统的内容，例如文件系统，以提供可移植性。详细信息可以在这里找到。



杂项



  • '对于字符串常量，请使用（单引号），"对于列和表名称，请使用（双引号）。
  • 要获取有关表tab1的信息，您可以使用 



    --  main 
select * from pragma_table_info('tab1');
--  temp    (attach) 
select * from pragma_table_info('tab1') where schema = 'temp'
  • SQLite拥有自己的官方论坛，SQLite的创建者Richard Hipp参加了该论坛，并且您可以在其中发布错误报告。

  • SQLite编辑器：SQLite Studio，SQLite的数据库浏览器和（ads！）Sqlite-gui（仅Windows）。



More articles:


All Articles