Partial codes (Partial Indexes)
When building an index, you can specify a condition for a row to be included in the index, for example, one of the columns is not empty, but the other is equal to the specified value.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 indexIndices on the expression (Indexes On Expressions)
If an expression is often used in queries to a table, then you can build an index on it. However, it should be borne in mind that while the optimizer is not very flexible, permutation of columns in the expression will lead to the abandonment of the 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 tableCalculated column (Generated Columns)
If the column data is the result of evaluating an expression on other columns, you can create a virtual column. There are two types: VIRTUAL (calculated every time the table is read and does not take up space) and STORED (calculated when writing data to the table and takes up space). Of course, you cannot directly write data to such columns.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-Tree index
The index is intended for fast search in a range of values / nesting of objects, i.e. tasks typical for geo-systems, when rectangular objects are given by their position and size and it is required to find all objects that intersect with the current one. This index is implemented as a virtual table (see below) and this is an index only in its essence. To support R-Tree index, you need to build SQLite with a flagSQLITE_ENABLE_RTREE(not checked by default).
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;
Renaming a column
SQLite poorly supports changes in the structure of tables, so, after creating a table, you cannot change a constraint or drop a column. Since version 3.25.0, you can rename a column, but not change its type.alter table tbl1 rename column a to b;For other operations, everything is also proposed to create a table with the desired structure, transfer data there, delete the old one and rename the new one.
Add line, otherwise update (Upsert)
Using theon conflictoperator class insert, you can add a new row, and if you already have one with the same value by key, update it.
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;Update from statement
If a row needs to be updated based on data from another table, then you previously had to use a subquery for each column orwith. Since version 3.33.0, the operator has been updateextended with a keyword fromand now you can do this
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 queries, class with (Common Table Expression)
The classwithcan be used as a temporary representation for a request. In version 3.34.0, the possibility of using it withinside is declared with.
with tab2 as (select * from tab1 where a > 10),
tab3 as (select * from tab2 inner join ...)
select * from tab3;
With the addition of a keyword
recursive, withit can be used for queries where you want to operate on related data.
--
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 function (Window Functions)
Since version 3.25.0, window functions, also sometimes called analytical functions, are available in SQLite, allowing you to perform calculations on a piece of data (window).--
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 utilities
In addition to the sqlite3 CLI , two more utilities are available. The first - sqldiff , allows you to compare databases (or a separate table) not only by structure, but also by data. The second, sqlite3_analizer, is used to display information about how space is effectively used by tables and indexes in the database file. Similar information can be obtained from the dbstat virtual table (requires a flagSQLITE_ENABLE_DBSTAT_VTABwhen compiling SQLite).
As of 3.22.0, the sqlite3 CLI contains an (experimental) .expert command that can tell you which index to add for an input query.
Create a Vacuum Into backup
Since version 3.27.0, the command has beenvacuumextended with a keyword intothat allows you to create a copy of the database without stopping it directly from SQL. It is a simple alternative to the Backup API .
vacuum into 'D:/backup/' || strftime('%Y-%M-%d', 'now') || '.sqlite';Printf function
The function is analogous to the C-function. In this case, theNULL-values are interpreted as an empty string for %sand 0for the number placeholder.
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
Time and date
In SQLite DateandTime . Although it is possible to create a table with columns of these types, it will be the same as creating columns without specifying a type, so the data in such columns is stored as text. This is convenient when viewing data, but it has a number of disadvantages: ineffective search, if there is no index, the data takes up a lot of space, and there is no time zone. To avoid this, you can store the data as unix time , i.e. the number of seconds since midnight 01/01/1970.
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')Json
Since version 3.9.0, you can work with json in SQLite (either aSQLITE_ENABLE_JSON1compile-time flag or a loaded extension is required ). Json data is stored as text. The result of the functions is also text.
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] ()Full text search
Like json, full text search requires a flagSQLITE_ENABLE_FTS5to be set when compiling or loading an extension. To work with search, first a virtual table with indexed fields is created, and then the data is loaded there using the usual one insert. It should be borne in mind that for its work the extension creates additional tables and the created virtual table uses their data.
create virtual table emails using fts5(sender, body);
SELECT * FROM emails WHERE emails = 'fts5'; -- sender body fts5
Extensions
SQLite capabilities can be added through loadable modules. Some of them have already been mentioned above - json1 and the fts .Extensions can be used to add user-defined functions (not only scalar ones, such as, for example
crc32, but also aggregating or even windowed ones ), and virtual tables. Virtual tables are tables that are present in the database, but their data is processed by the extension, while, depending on the implementation, some of them require creation
create virtual table temp.tab1 using csv(filename='thefile.csv');
select * from tab1;Others, so called table-valued , can be used immediately.
select value from generate_series(5, 100, 5);Some of the virtual tables are listed here .
One extension can implement both functions and virtual tables. For example, json1 contains 13 scalar and 2 aggregate functions and two virtual tables
json_eachand json_tree. To write your own function, you just need to have basic knowledge of C and parse the extension code from the SQLite repository . Implementing your own virtual tables is a little more complicated (apparently that's why there are few of them). Here we can recommend the slightly outdated book Using SQLite by Jay A. Kreibich , article by Michael Owens , the template from the repository, and the generate_series code as table-valued functions.
In addition, extensions can implement operating system-specific things, such as the file system, to provide portability. Details can be found here .
miscellanea
- Use
'(single quote) for string constants and"(double quote) for column and table names. - To get information on table tab1 you can use
-- main select * from pragma_table_info('tab1'); -- temp (attach) select * from pragma_table_info('tab1') where schema = 'temp' - SQLite has its own official forum , where the creator of SQLite - Richard Hipp participates, and where you can post a bug report.
- SQLite editors: SQLite Studio , DB Browser for SQLite and (ads!) Sqlite-gui (Windows only).