Códigos parciales (índices parciales)
Al crear un índice, puede especificar una condición para que una fila se incluya en el índice, por ejemplo, una de las columnas no está vacía, pero la otra es igual al valor especificado.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Índices de la expresión (índices de expresiones)
Si una expresión se usa a menudo en consultas a una tabla, entonces puede construir un índice sobre ella. Sin embargo, debe tenerse en cuenta que si bien el optimizador no es muy flexible, la permutación de columnas en la expresión conducirá al rechazo del uso del índice.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 tableColumna calculada (columnas generadas)
Si los datos de la columna son el resultado de evaluar una expresión en otras columnas, puede crear una columna virtual. Hay dos tipos: VIRTUAL (calculado cada vez que se lee la tabla y no ocupa espacio) y ALMACENADO (calculado al escribir datos en la tabla y ocupa espacio). Por supuesto, no puede escribir datos directamente en dichas columnas.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
);Índice de árbol R
El índice está diseñado para una búsqueda rápida en un rango de valores / anidamiento de objetos, es decir, tareas típicas de los geo-sistemas, cuando los objetos rectangulares se especifican por su posición y tamaño y se requiere encontrar todos los objetos que se cruzan con el actual. Este índice se implementa como una tabla virtual (ver más abajo) y este es un índice solo en su esencia. La compatibilidad con el índice R-Tree requiere la construcción de SQLite con una marcaSQLITE_ENABLE_RTREE(no marcada de forma predeterminada).
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;
Cambiar el nombre de una columna
SQLite no admite cambios en la estructura de las tablas, por lo que, después de crear una tabla, no puede cambiar una restricción o eliminar una columna. Desde la versión 3.25.0, puede cambiar el nombre de una columna, pero no cambiar su tipo.alter table tbl1 rename column a to b;Para otras operaciones, también se propone todo para crear una tabla con la estructura deseada, transferir datos allí, eliminar la antigua y renombrar la nueva.
Agregar línea, de lo contrario actualizar (Upsert)
Usando la clase deon conflictoperador insert, puede agregar una nueva fila, y si ya tiene una con el mismo valor por clave, actualícela.
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;Actualización desde declaración
Si es necesario actualizar una fila en función de los datos de otra tabla, antes tenía que usar una subconsulta para cada columna owith. Desde la versión 3.33.0, el operador se ha updateampliado con una palabra clave fromy ahora puede hacer esto
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;Consultas CTE, clase con (Expresión de tabla común)
La clasewithse puede utilizar como representación temporal para una solicitud. En la versión 3.34.0 se declara la posibilidad de usarlo en su withinterior with.
with tab2 as (select * from tab1 where a > 10),
tab3 as (select * from tab2 inner join ...)
select * from tab3;
Con la adición de una palabra clave
recursive, withse puede utilizar para consultas en las que desee operar con datos relacionados.
--
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;Función de ventana ( funciones de ventana)
Desde la versión 3.25.0, las funciones de ventana, también llamadas a veces funciones analíticas, están disponibles en SQLite, lo que le permite realizar cálculos en una pieza de datos (ventana).--
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;
Utilidades SQLite
Además de la CLI de sqlite3 , hay dos utilidades más disponibles. El primero, sqldiff , le permite comparar bases de datos (o una tabla separada) no solo por estructura, sino también por datos. El segundo, sqlite3_analizer, se usa para mostrar información sobre cómo las tablas e índices usan el espacio de manera eficiente en el archivo de base de datos. Se puede obtener información similar de la tabla virtual dbstat (requiere una banderaSQLITE_ENABLE_DBSTAT_VTABal compilar SQLite).
A partir de la 3.22.0, la CLI de sqlite3 contiene un comando .expert (experimental) que puede indicarle qué índice agregar para una consulta de entrada.
Crea un vacío en la copia de seguridad
Desde la versión 3.27.0, el comando se havacuumampliado con una palabra clave intoque le permite crear una copia de la base de datos sin detenerla directamente desde SQL. Es una alternativa simple a la API de respaldo .
vacuum into 'D:/backup/' || strftime('%Y-%M-%d', 'now') || '.sqlite';Función printf
La función es análoga a la función C. En este caso, losNULLvalores-se interpretan como una cadena vacía para %sy 0para el marcador de posición del número.
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
Hora y fecha
En SQLite DateyTime . Aunque es posible crear una tabla con columnas de estos tipos, será lo mismo que crear columnas sin especificar un tipo, por lo que los datos en dichas columnas se almacenan como texto. Esto es conveniente cuando se visualizan datos, pero tiene una serie de desventajas: búsqueda ineficaz, si no hay índice, los datos ocupan mucho espacio y no hay zona horaria. Para evitar esto, puede almacenar los datos como tiempo Unix , es decir, número de segundos desde la medianoche del 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
Desde la versión 3.9.0, puede trabajar con json en SQLite (se requiereSQLITE_ENABLE_JSON1un indicador de tiempo de compilación o una extensión cargada). Los datos de JSON se almacenan como texto. El resultado de las funciones también es texto.
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] ()Búsqueda de texto completo
Al igual que json, la búsqueda de texto completo requiereSQLITE_ENABLE_FTS5que se establezca una marca al compilar o cargar una extensión. Para trabajar con la búsqueda, primero se crea una tabla virtual con campos indexados, y luego los datos se cargan allí usando la habitual insert. Hay que tener en cuenta que para su funcionamiento la extensión crea tablas adicionales y la tabla virtual creada utiliza sus datos.
create virtual table emails using fts5(sender, body);
SELECT * FROM emails WHERE emails = 'fts5'; -- sender body fts5
Extensiones
Las capacidades de SQLite se pueden agregar a través de módulos cargables. Algunos de ellos ya se han mencionado anteriormente: json1 y fts .Las extensiones se pueden utilizar tanto para agregar funciones definidas por el usuario (no solo escalares, como, por ejemplo
crc32, sino también agregadas o incluso en ventanas ), como tablas virtuales. Las tablas virtuales son tablas que están presentes en la base de datos, pero sus datos son procesados por la extensión, mientras que, dependiendo de la implementación, algunas de ellas requieren creación.
create virtual table temp.tab1 using csv(filename='thefile.csv');
select * from tab1;Otros, denominados con valores de tabla , se pueden utilizar inmediatamente.
select value from generate_series(5, 100, 5);Algunas de las tablas virtuales se enumeran aquí .
Una extensión puede implementar tanto funciones como tablas virtuales. Por ejemplo, json1 contiene 13 funciones escalares y 2 agregadas y dos tablas virtuales
json_eachy json_tree. Para escribir su propia función, solo necesita tener conocimientos básicos de C y analizar el código de extensión del repositorio SQLite . Implementar sus propias tablas virtuales es un poco más complicado (aparentemente por eso hay pocas). Aquí podemos recomendar el libro un poco desactualizado Using SQLite de Jay A. Kreibich , el artículo de Michael Owens , la plantilla del repositorio y el código generate_series como funciones con valores de tabla.
Además, las extensiones pueden implementar elementos específicos del sistema operativo, como el sistema de archivos, para brindar portabilidad. Los detalles se pueden encontrar aquí .
miscelánea
- Utilice
'(comillas simples) para las constantes de cadena y"(comillas dobles) para los nombres de columnas y tablas. - Para obtener información sobre la tabla tab1 puede utilizar
-- main select * from pragma_table_info('tab1'); -- temp (attach) select * from pragma_table_info('tab1') where schema = 'temp' - SQLite tiene su propio foro oficial , donde participa el creador de SQLite, Richard Hipp, y donde puede publicar un informe de error.
- Editores de SQLite: SQLite Studio , DB Browser para SQLite y (¡anuncios!) Sqlite-gui (solo Windows).