SQLite - Guia Rápido

Este capítulo ajuda a entender o que é SQLite, como ele difere do SQL, por que ele é necessário e a forma como trata o banco de dados das aplicações.

SQLite é uma biblioteca de software que implementa um mecanismo de banco de dados SQL transacional independente, sem servidor e com configuração zero. SQLite é um dos mecanismos de banco de dados de crescimento mais rápido, mas isso é crescimento em termos de popularidade, não tem nada a ver com seu tamanho. O código-fonte do SQLite é de domínio público.

O que é SQLite?

SQLite é uma biblioteca em processo que implementa um mecanismo de banco de dados SQL transacional independente, sem servidor e com configuração zero. É um banco de dados com configuração zero, o que significa que, como outros bancos de dados, você não precisa configurá-lo em seu sistema.

O mecanismo SQLite não é um processo autônomo como outros bancos de dados, você pode vinculá-lo estaticamente ou dinamicamente de acordo com sua necessidade com seu aplicativo. O SQLite acessa seus arquivos de armazenamento diretamente.

Por que SQLite?

O SQLite não requer um processo ou sistema de servidor separado para operar (sem servidor).
SQLite vem com configuração zero, o que significa nenhuma configuração ou administração necessária.
Um banco de dados SQLite completo é armazenado em um único arquivo de disco de plataforma cruzada.
SQLite é muito pequeno e leve, menos de 400 KiB totalmente configurado ou menos de 250 KiB com recursos opcionais omitidos.
SQLite é independente, o que significa que não há dependências externas.
As transações SQLite são totalmente compatíveis com ACID, permitindo acesso seguro a partir de vários processos ou threads.
SQLite suporta a maioria dos recursos de linguagem de consulta encontrados no padrão SQL92 (SQL2).
O SQLite é escrito em ANSI-C e fornece API simples e fácil de usar.
O SQLite está disponível em UNIX (Linux, Mac OS-X, Android, iOS) e Windows (Win32, WinCE, WinRT).

SQLite Uma breve história

2000 - D. Richard Hipp desenvolveu o SQLite com o propósito de não exigir nenhuma administração para operar um programa.
2000 - Em agosto, o SQLite 1.0 é lançado com o GNU Database Manager.
2011 - A Hipp anunciou a adição da interface UNQl ao banco de dados SQLite e o desenvolvimento do UNQLite (banco de dados orientado a documentos).

Limitações SQLite

Existem alguns recursos sem suporte do SQL92 no SQLite listados na tabela a seguir.

Sr. Não.	Recurso e descrição
1	RIGHT OUTER JOIN Apenas LEFT OUTER JOIN é implementado.
2	FULL OUTER JOIN Apenas LEFT OUTER JOIN é implementado.
3	ALTER TABLE As variantes RENAME TABLE e ADD COLUMN do comando ALTER TABLE são suportadas. DROP COLUMN, ALTER COLUMN e ADD CONSTRAINT não são suportados.
4	Trigger support Os gatilhos FOR EACH ROW são suportados, mas não os gatilhos FOR EACH STATEMENT.
5	VIEWs VIEWs no SQLite são somente leitura. Você não pode executar uma instrução DELETE, INSERT ou UPDATE em uma exibição.
6	GRANT and REVOKE As únicas permissões de acesso que podem ser aplicadas são as permissões normais de acesso a arquivos do sistema operacional subjacente.

Comandos SQLite

Os comandos SQLite padrão para interagir com bancos de dados relacionais são semelhantes ao SQL. Eles são CREATE, SELECT, INSERT, UPDATE, DELETE e DROP. Esses comandos podem ser classificados em grupos com base em sua natureza operacional -

DDL - linguagem de definição de dados

Sr. Não.	Comando e Descrição
1	CREATE Cria uma nova tabela, uma visão de uma tabela ou outro objeto no banco de dados.
2	ALTER Modifica um objeto de banco de dados existente, como uma tabela.
3	DROP Exclui uma tabela inteira, uma visão de uma tabela ou outro objeto no banco de dados.

DML - linguagem de manipulação de dados

Sr. Não.	Comando e Descrição
1	INSERT Cria um registro
2	UPDATE Modifica registros
3	DELETE Exclui registros

DQL - linguagem de consulta de dados

Sr. Não.	Comando e Descrição
1	SELECT Recupera certos registros de uma ou mais tabelas

O SQLite é famoso por sua excelente configuração zero de recursos, o que significa que nenhuma configuração ou administração complexa é necessária. Este capítulo o levará através do processo de configuração do SQLite no Windows, Linux e Mac OS X.

Instale SQLite no Windows

Step 1- Vá para a página de download do SQLite e baixe os binários pré-compilados da seção do Windows.
Step 2 - Baixe arquivos compactados sqlite-shell-win32 - *. Zip e sqlite-dll-win32 - *. Zip.
Step 3 - Crie uma pasta C: \> sqlite e descompacte acima de dois arquivos compactados nesta pasta, que fornecerá os arquivos sqlite3.def, sqlite3.dll e sqlite3.exe.
Step 4 - Adicione C: \> sqlite em sua variável de ambiente PATH e finalmente vá para o prompt de comando e execute o comando sqlite3, que deve exibir o seguinte resultado.

C:\>sqlite3
SQLite version 3.7.15.2 2013-01-09 11:53:05
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>

Instale SQLite no Linux

Hoje, quase todos os sabores do sistema operacional Linux estão sendo enviados com SQLite. Portanto, basta emitir o seguinte comando para verificar se já possui o SQLite instalado em sua máquina.

$sqlite3
SQLite version 3.7.15.2 2013-01-09 11:53:05
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>

Se você não vir o resultado acima, significa que você não tem o SQLite instalado em sua máquina Linux. A seguir estão as seguintes etapas para instalar o SQLite -

Step 1- Vá para a página de download do SQLite e baixe sqlite-autoconf - *. Tar.gz da seção de código-fonte.
Step 2 - Execute o seguinte comando -

$tar xvfz sqlite-autoconf-3071502.tar.gz
$cd sqlite-autoconf-3071502 $./configure --prefix=/usr/local
$make $make install

O comando acima terminará com a instalação do SQLite em sua máquina Linux. Que você pode verificar conforme explicado acima.

Instale SQLite no Mac OS X

Embora a versão mais recente do Mac OS X venha pré-instalada com o SQLite, se você não tiver a instalação disponível, basta seguir estas etapas -

Step 1- Vá para a página de download do SQLite e baixe sqlite-autoconf - *. Tar.gz da seção de código-fonte.
Step 2 - Execute o seguinte comando -

$tar xvfz sqlite-autoconf-3071502.tar.gz $cd sqlite-autoconf-3071502
$./configure --prefix=/usr/local $make
$make install

O procedimento acima terminará com a instalação do SQLite em sua máquina Mac OS X. Que você pode verificar emitindo o seguinte comando -

$sqlite3
SQLite version 3.7.15.2 2013-01-09 11:53:05
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>

Finalmente, você tem o prompt de comando SQLite onde pode emitir comandos SQLite para seus exercícios.

Este capítulo o levará através de comandos simples e úteis usados por programadores SQLite. Esses comandos são chamados de comandos de ponto SQLite e a exceção a esses comandos é que eles não devem terminar com um ponto e vírgula (;).

Vamos começar digitando um simples sqlite3 comando no prompt de comando que fornecerá o prompt de comando SQLite onde você emitirá vários comandos SQLite.

$sqlite3
SQLite version 3.3.6
Enter ".help" for instructions
sqlite>

Para obter uma lista dos comandos de ponto disponíveis, você pode inserir ".help" a qualquer momento. Por exemplo -

sqlite>.help

O comando acima exibirá uma lista de vários comandos de pontos SQLite importantes, que estão listados na tabela a seguir.

Sr. Não.	Comando e Descrição
1	.backup ?DB? FILE Backup do banco de dados (padrão "principal") para ARQUIVO
2	.bail ON\|OFF Pare depois de encontrar um erro. OFF padrão
3	.databases Listar nomes e arquivos de bancos de dados anexados
4	.dump ?TABLE? Descarregue o banco de dados em um formato de texto SQL. Se TABLE for especificado, apenas as tabelas de dump que correspondem ao padrão LIKE TABLE
5	.echo ON\|OFF Ligue ou desligue o eco de comando
6	.exit Sair do prompt SQLite
7	.explain ON\|OFF Ative ou desative o modo de saída adequado para EXPLAIN. Sem args, ele ativa EXPLAIN
8	.header(s) ON\|OFF Ativar ou desativar a exibição de cabeçalhos
9	.help Mostrar esta mensagem
10	.import FILE TABLE Importar dados de FILE para TABLE
11	.indices ?TABLE? Mostra os nomes de todos os índices. Se TABLE for especificado, mostra apenas os índices para as tabelas que correspondem ao padrão LIKE TABLE
12	.load FILE ?ENTRY? Carregar uma biblioteca de extensão
13	.log FILE\|off Ligue ou desligue o log. ARQUIVO pode ser stderr / stdout
14	.mode MODE Defina o modo de saída onde MODE é um de - csv - valores separados por vírgula column - Colunas alinhadas à esquerda. html - código HTML <table> insert - Instruções de inserção SQL para TABLE line - Um valor por linha list - Valores delimitados por string .separator tabs - Valores separados por tabulação tcl - Elementos da lista TCL
15	.nullvalue STRING Imprimir STRING no lugar de valores NULL
16	.output FILENAME Enviar saída para FILENAME
17	.output stdout Envie a saída para a tela
18	.print STRING... Imprimir literal STRING
19	.prompt MAIN CONTINUE Substitua os prompts padrão
20	.quit Sair do prompt SQLite
21	.read FILENAME Execute SQL em FILENAME
22	.schema ?TABLE? Mostre as instruções CREATE. Se TABLE for especificado, mostra apenas as tabelas que correspondem ao padrão LIKE TABLE
23	.separator STRING Separador de mudança usado pelo modo de saída e .import
24	.show Mostra os valores atuais para várias configurações
25	.stats ON\|OFF Ativar ou desativar estatísticas
26	.tables ?PATTERN? Lista os nomes das tabelas que correspondem a um padrão LIKE
27	.timeout MS Tente abrir tabelas bloqueadas por MS milissegundos
28	.width NUM NUM Defina as larguras das colunas para o modo "coluna"
29	.timer ON\|OFF Ligue ou desligue a medição do temporizador da CPU

Vamos tentar .show comando para ver a configuração padrão para seu prompt de comando SQLite.

sqlite>.show
     echo: off
  explain: off
  headers: off
     mode: column
nullvalue: ""
   output: stdout
separator: "|"
    width:
sqlite>

Certifique-se de que não haja espaço entre o prompt sqlite> e o comando dot, caso contrário, não funcionará.

Saída de formatação

Você pode usar a seguinte sequência de comandos de ponto para formatar sua saída.

sqlite>.header on
sqlite>.mode column
sqlite>.timer on
sqlite>

A configuração acima produzirá a saída no formato a seguir.

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0
CPU Time: user 0.000000 sys 0.000000

A tabela sqlite_master

A tabela mestre contém as principais informações sobre as tabelas do banco de dados e é chamada sqlite_master. Você pode ver seu esquema da seguinte maneira -

sqlite>.schema sqlite_master

Isso produzirá o seguinte resultado.

CREATE TABLE sqlite_master (
   type text,
   name text,
   tbl_name text,
   rootpage integer,
   sql text
);

SQLite é seguido por um conjunto exclusivo de regras e diretrizes chamadas Sintaxe. Este capítulo lista toda a sintaxe SQLite básica.

Sensibilidade ao Caso

O ponto importante a ser observado é que o SQLite é case insensitive, ou seja, as cláusulas GLOB e glob têm o mesmo significado nas instruções SQLite.

Comentários

Comentários SQLite são notas extras, que você pode adicionar em seu código SQLite para aumentar sua legibilidade e eles podem aparecer em qualquer lugar; podem ocorrer espaços em branco, incluindo expressões internas e no meio de outras instruções SQL, mas não podem ser aninhados.

Os comentários SQL começam com dois caracteres "-" consecutivos (ASCII 0x2d) e se estendem até e incluindo o próximo caractere de nova linha (ASCII 0x0a) ou até o final da entrada, o que ocorrer primeiro.

Você também pode usar comentários no estilo C, que começam com "/ *" e se estendem até o próximo par de caracteres "* /" ou até o final da entrada, o que ocorrer primeiro. Os comentários de estilo C podem abranger várias linhas.

sqlite> .help -- This is a single line comment

Instruções SQLite

Todas as instruções SQLite começam com qualquer uma das palavras-chave como SELECT, INSERT, UPDATE, DELETE, ALTER, DROP, etc., e todas as instruções terminam com um ponto e vírgula (;).

Instrução SQLite ANALYZE

ANALYZE;
or
ANALYZE database_name;
or
ANALYZE database_name.table_name;

Cláusula SQLite AND / OR

SELECT column1, column2....columnN
FROM table_name
WHERE CONDITION-1 {AND|OR} CONDITION-2;

Instrução SQLite ALTER TABLE

ALTER TABLE table_name ADD COLUMN column_def...;

Instrução SQLite ALTER TABLE (Renomear)

ALTER TABLE table_name RENAME TO new_table_name;

Instrução SQLite ATTACH DATABASE

ATTACH DATABASE 'DatabaseName' As 'Alias-Name';

Instrução SQLite BEGIN TRANSACTION

BEGIN;
or
BEGIN EXCLUSIVE TRANSACTION;

Cláusula SQLite BETWEEN

SELECT column1, column2....columnN
FROM table_name
WHERE column_name BETWEEN val-1 AND val-2;

Instrução SQLite COMMIT

COMMIT;

Instrução SQLite CREATE INDEX

CREATE INDEX index_name
ON table_name ( column_name COLLATE NOCASE );

Instrução SQLite CREATE UNIQUE INDEX

CREATE UNIQUE INDEX index_name
ON table_name ( column1, column2,...columnN);

Instrução SQLite CREATE TABLE

CREATE TABLE table_name(
   column1 datatype,
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
   PRIMARY KEY( one or more columns )
);

Instrução SQLite CREATE TRIGGER

CREATE TRIGGER database_name.trigger_name 
BEFORE INSERT ON table_name FOR EACH ROW
BEGIN 
   stmt1; 
   stmt2;
   ....
END;

Instrução SQLite CREATE VIEW

CREATE VIEW database_name.view_name AS
SELECT statement....;

Instrução SQLite CREATE VIRTUAL TABLE

CREATE VIRTUAL TABLE database_name.table_name USING weblog( access.log );
or
CREATE VIRTUAL TABLE database_name.table_name USING fts3( );

Instrução SQLite COMMIT TRANSACTION

COMMIT;

Cláusula SQLite COUNT

SELECT COUNT(column_name)
FROM table_name
WHERE CONDITION;

Instrução SQLite DELETE

DELETE FROM table_name
WHERE {CONDITION};

Instrução SQLite DETACH DATABASE

DETACH DATABASE 'Alias-Name';

Cláusula SQLite DISTINCT

SELECT DISTINCT column1, column2....columnN
FROM table_name;

Instrução SQLite DROP INDEX

DROP INDEX database_name.index_name;

Instrução SQLite DROP TABLE

DROP TABLE database_name.table_name;

Instrução SQLite DROP VIEW

DROP INDEX database_name.view_name;

Instrução SQLite DROP TRIGGER

DROP INDEX database_name.trigger_name;

Cláusula SQLite EXISTS

SELECT column1, column2....columnN
FROM table_name
WHERE column_name EXISTS (SELECT * FROM   table_name );

Instrução SQLite EXPLAIN

EXPLAIN INSERT statement...;
or 
EXPLAIN QUERY PLAN SELECT statement...;

Cláusula SQLite GLOB

SELECT column1, column2....columnN
FROM table_name
WHERE column_name GLOB { PATTERN };

Cláusula SQLite GROUP BY

SELECT SUM(column_name)
FROM table_name
WHERE CONDITION
GROUP BY column_name;

Cláusula SQLite HAVING

SELECT SUM(column_name)
FROM table_name
WHERE CONDITION
GROUP BY column_name
HAVING (arithematic function condition);

Instrução SQLite INSERT INTO

INSERT INTO table_name( column1, column2....columnN)
VALUES ( value1, value2....valueN);

Cláusula SQLite IN

SELECT column1, column2....columnN
FROM table_name
WHERE column_name IN (val-1, val-2,...val-N);

Cláusula similar ao SQLite

SELECT column1, column2....columnN
FROM table_name
WHERE column_name LIKE { PATTERN };

Cláusula SQLite NOT IN

SELECT column1, column2....columnN
FROM table_name
WHERE column_name NOT IN (val-1, val-2,...val-N);

Cláusula SQLite ORDER BY

SELECT column1, column2....columnN
FROM table_name
WHERE CONDITION
ORDER BY column_name {ASC|DESC};

Instrução SQLite PRAGMA

PRAGMA pragma_name;

For example:

PRAGMA page_size;
PRAGMA cache_size = 1024;
PRAGMA table_info(table_name);

Instrução SQLite RELEASE SAVEPOINT

RELEASE savepoint_name;

Instrução SQLite REINDEX

REINDEX collation_name;
REINDEX database_name.index_name;
REINDEX database_name.table_name;

Instrução SQLite ROLLBACK

ROLLBACK;
or
ROLLBACK TO SAVEPOINT savepoint_name;

Instrução SQLite SAVEPOINT

SAVEPOINT savepoint_name;

Instrução SQLite SELECT

SELECT column1, column2....columnN
FROM table_name;

Instrução SQLite UPDATE

UPDATE table_name
SET column1 = value1, column2 = value2....columnN=valueN
[ WHERE  CONDITION ];

Instrução SQLite VACUUM

VACUUM;

Cláusula SQLite WHERE

SELECT column1, column2....columnN
FROM table_name
WHERE CONDITION;

O tipo de dados SQLite é um atributo que especifica o tipo de dados de qualquer objeto. Cada coluna, variável e expressão tem tipo de dados relacionado no SQLite.

Você usaria esses tipos de dados ao criar suas tabelas. SQLite usa um sistema de tipo dinâmico mais geral. No SQLite, o tipo de dados de um valor está associado ao próprio valor, não a seu contêiner.

Classes de armazenamento SQLite

Cada valor armazenado em um banco de dados SQLite tem uma das seguintes classes de armazenamento -

Sr. Não.	Classe de armazenamento e descrição
1	NULL O valor é um valor NULL.
2	INTEGER O valor é um número inteiro com sinal, armazenado em 1, 2, 3, 4, 6 ou 8 bytes dependendo da magnitude do valor.
3	REAL O valor é um valor de ponto flutuante, armazenado como um número de ponto flutuante IEEE de 8 bytes.
4	TEXT O valor é uma string de texto, armazenada usando a codificação do banco de dados (UTF-8, UTF-16BE ou UTF-16LE)
5	BLOB O valor é um blob de dados, armazenado exatamente como foi inserido.

A classe de armazenamento SQLite é um pouco mais geral do que um tipo de dados. A classe de armazenamento INTEGER, por exemplo, inclui 6 tipos de dados inteiros diferentes de comprimentos diferentes.

Tipo de afinidade SQLite

SQLite suporta o conceito de type affinitynas colunas. Qualquer coluna ainda pode armazenar qualquer tipo de dados, mas a classe de armazenamento preferida para uma coluna é chamada deaffinity. Cada coluna da tabela em um banco de dados SQLite3 é atribuída a um dos seguintes tipos de afinidades -

Sr. Não.	Afinidade e descrição
1	TEXT Esta coluna armazena todos os dados usando as classes de armazenamento NULL, TEXT ou BLOB.
2	NUMERIC Esta coluna pode conter valores usando todas as cinco classes de armazenamento.
3	INTEGER Se comporta da mesma maneira que uma coluna com afinidade NUMERIC, com exceção em uma expressão CAST.
4	REAL Se comporta como uma coluna com afinidade NUMERIC, exceto que força os valores inteiros na representação de ponto flutuante.
5	NONE Uma coluna com afinidade NONE não prefere uma classe de armazenamento a outra e nenhuma tentativa é feita para forçar os dados de uma classe de armazenamento para outra.

Afinidade SQLite e nomes de tipo

A tabela a seguir lista vários nomes de tipo de dados que podem ser usados ao criar tabelas SQLite3 com a afinidade aplicada correspondente.

Tipo de dados	Afinidade
INT INTEGER TINYINT SMALLINT MEDIUMINT BIGINT BIG INT NÃO ASSINADO INT2 INT8	INTEIRO
CHARACTER(20) VARCHAR(255) CARÁTER VARIÁVEL (255) NCHAR(55) CARÁTER NATIVO (70) NVARCHAR(100) TEXT CLOB	TEXTO
BLOB nenhum tipo de dados especificado	NENHUM
REAL DOUBLE DUPLA PRECISÃO FLOAT	REAL
NUMERIC DECIMAL(10,5) BOOLEAN DATE DATETIME	NUMÉRICO

Boolean Datatype

O SQLite não possui uma classe de armazenamento Booleana separada. Em vez disso, os valores booleanos são armazenados como inteiros 0 (falso) e 1 (verdadeiro).

Tipo de dados de data e hora

O SQLite não tem uma classe de armazenamento separada para armazenar datas e / ou horas, mas o SQLite é capaz de armazenar datas e horas como valores TEXT, REAL ou INTEGER.

Sr. Não.	Classe de armazenamento e formato de data
1	TEXT Uma data em formato como "AAAA-MM-DD HH: MM: SS.SSS"
2	REAL O número de dias desde o meio-dia em Greenwich em 24 de novembro de 4714 AC
3	INTEGER O número de segundos desde 01/01/1970 00:00:00 UTC

Você pode escolher armazenar datas e horas em qualquer um desses formatos e converter livremente entre os formatos usando as funções integradas de data e hora.

No SQLite, sqlite3comando é usado para criar um novo banco de dados SQLite. Você não precisa ter nenhum privilégio especial para criar um banco de dados.

Sintaxe

A seguir está a sintaxe básica do comando sqlite3 para criar um banco de dados: -

$sqlite3 DatabaseName.db

Sempre, o nome do banco de dados deve ser exclusivo no RDBMS.

Exemplo

Se você deseja criar um novo banco de dados <testDB.db>, a instrução SQLITE3 seria a seguinte -

$sqlite3 testDB.db
SQLite version 3.7.15.2 2013-01-09 11:53:05
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>

O comando acima irá criar um arquivo testDB.dbno diretório atual. Este arquivo será usado como banco de dados pelo motor SQLite. Se você notou durante a criação do banco de dados, o comando sqlite3 fornecerá umsqlite> após criar um arquivo de banco de dados com sucesso.

Uma vez que um banco de dados é criado, você pode verificá-lo na lista de bancos de dados usando o seguinte SQLite .databases comando.

sqlite>.databases
seq  name             file
---  ---------------  ----------------------
0    main             /home/sqlite/testDB.db

Você usará SQLite .quit comando para sair do prompt sqlite da seguinte maneira -

sqlite>.quit
$

O comando .dump

Você pode usar .dump comando dot para exportar o banco de dados completo em um arquivo de texto usando o seguinte comando SQLite no prompt de comando.

$sqlite3 testDB.db .dump > testDB.sql

O comando acima irá converter todo o conteúdo de testDB.db banco de dados em instruções SQLite e despejá-lo em um arquivo de texto ASCII testDB.sql. Você pode executar a restauração do testDB.sql gerado de uma maneira simples, como a seguir -

$sqlite3 testDB.db < testDB.sql

Neste momento seu banco de dados está vazio, então você pode tentar os dois procedimentos acima, uma vez que você tenha poucas tabelas e dados em seu banco de dados. Por enquanto, vamos prosseguir para o próximo capítulo.

Considere um caso em que você tem vários bancos de dados disponíveis e deseja usar qualquer um deles ao mesmo tempo. SQLiteATTACH DATABASE A instrução é usada para selecionar um banco de dados específico e, após esse comando, todas as instruções SQLite serão executadas no banco de dados anexado.

Sintaxe

A seguir está a sintaxe básica da instrução SQLite ATTACH DATABASE.

ATTACH DATABASE 'DatabaseName' As 'Alias-Name';

O comando acima também criará um banco de dados caso o banco de dados ainda não tenha sido criado, caso contrário, apenas anexará o nome do arquivo do banco de dados com o banco de dados lógico 'Alias-Name'.

Exemplo

Se você deseja anexar um banco de dados existente testDB.db, a instrução ATTACH DATABASE seria a seguinte -

sqlite> ATTACH DATABASE 'testDB.db' as 'TEST';

Use SQLite .database comando para exibir o banco de dados anexado.

sqlite> .database
seq  name             file
---  ---------------  ----------------------
0    main             /home/sqlite/testDB.db
2    test             /home/sqlite/testDB.db

Os nomes do banco de dados main e tempsão reservados para o banco de dados primário e o banco de dados para conter tabelas temporárias e outros objetos de dados temporários. Ambos os nomes de banco de dados existem para todas as conexões de banco de dados e não devem ser usados para anexos, caso contrário, você receberá a seguinte mensagem de aviso.

sqlite> ATTACH DATABASE 'testDB.db' as 'TEMP';
Error: database TEMP is already in use
sqlite> ATTACH DATABASE 'testDB.db' as 'main';
Error: database TEMP is already in use

SQLite DETACH DATABASEA instrução é usada para desanexar e dissociar um banco de dados nomeado de uma conexão de banco de dados que foi previamente anexada usando a instrução ATTACH. Se o mesmo arquivo de banco de dados foi anexado com vários aliases, o comando DETACH desconectará apenas o nome fornecido e o restante do anexo continuará. Você não pode separar omain ou temp bancos de dados.

Se o banco de dados for um banco de dados na memória ou temporário, o banco de dados será destruído e o conteúdo será perdido.

Sintaxe

A seguir está a sintaxe básica da instrução SQLite DETACH DATABASE 'Alias-Name'.

DETACH DATABASE 'Alias-Name';

Aqui, 'Alias-Name' é o mesmo alias que você usou ao anexar o banco de dados usando a instrução ATTACH.

Exemplo

Considere que você tem um banco de dados, que você criou no capítulo anterior e o anexou com 'test' e 'currentDB', como podemos ver usando .database comando.

sqlite>.databases
seq  name             file
---  ---------------  ----------------------
0    main             /home/sqlite/testDB.db
2    test             /home/sqlite/testDB.db
3    currentDB        /home/sqlite/testDB.db

Vamos tentar separar 'currentDB' de testDB.db usando o seguinte comando.

sqlite> DETACH DATABASE 'currentDB';

Agora, se você verificar o anexo atual, verá que testDB.db ainda está conectado com 'test' e 'main'.

sqlite>.databases
seq  name             file
---  ---------------  ----------------------
0    main             /home/sqlite/testDB.db
2    test             /home/sqlite/testDB.db

SQLite CREATE TABLEinstrução é usada para criar uma nova tabela em qualquer banco de dados fornecido. Criar uma tabela básica envolve nomear a tabela e definir suas colunas e o tipo de dados de cada coluna.

Sintaxe

A seguir está a sintaxe básica da instrução CREATE TABLE.

CREATE TABLE database_name.table_name(
   column1 datatype PRIMARY KEY(one or more columns),
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype
);

CREATE TABLE é a palavra-chave que diz ao sistema de banco de dados para criar uma nova tabela. O nome ou identificador exclusivo da tabela segue a instrução CREATE TABLE. Opcionalmente, você pode especificar database_name junto com table_name .

Exemplo

A seguir está um exemplo que cria uma tabela COMPANY com ID como a chave primária e NOT NULL são as restrições que mostram que esses campos não podem ser NULL durante a criação de registros nesta tabela.

sqlite> CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

Vamos criar mais uma tabela, que usaremos em nossos exercícios nos capítulos subsequentes.

sqlite> CREATE TABLE DEPARTMENT(
   ID INT PRIMARY KEY      NOT NULL,
   DEPT           CHAR(50) NOT NULL,
   EMP_ID         INT      NOT NULL
);

Você pode verificar se sua tabela foi criada com sucesso usando o comando SQLite .tables comando, que será usado para listar todas as tabelas em um banco de dados anexado.

sqlite>.tables
COMPANY     DEPARTMENT

Aqui, você pode ver a tabela COMPANY duas vezes porque mostra a tabela COMPANY para o banco de dados principal e a tabela test.COMPANY para o alias de 'teste' criado para seu testDB.db. Você pode obter informações completas sobre uma tabela usando o seguinte SQLite.schema comando.

sqlite>.schema COMPANY
CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

SQLite DROP TABLE instrução é usada para remover uma definição de tabela e todos os dados, índices, gatilhos, restrições e especificações de permissão associados a essa tabela.

Você deve ter cuidado ao usar este comando porque uma vez que uma tabela é excluída, todas as informações disponíveis na tabela também serão perdidas para sempre.

Sintaxe

A seguir está a sintaxe básica da instrução DROP TABLE. Você pode opcionalmente especificar o nome do banco de dados junto com o nome da tabela da seguinte forma -

DROP TABLE database_name.table_name;

Exemplo

Vamos primeiro verificar a tabela COMPANY e, em seguida, vamos excluí-la do banco de dados.

sqlite>.tables
COMPANY       test.COMPANY

Isso significa que a tabela COMPANY está disponível no banco de dados, então vamos eliminá-la da seguinte forma -

sqlite>DROP TABLE COMPANY;
sqlite>

Agora, se você tentar o comando .TABLES, você não encontrará mais a tabela COMPANY.

sqlite>.tables
sqlite>

Não mostra nada, o que significa que a tabela de seu banco de dados foi eliminada com sucesso.

SQLite INSERT INTO A instrução é usada para adicionar novas linhas de dados em uma tabela no banco de dados.

Sintaxe

A seguir estão as duas sintaxes básicas da instrução INSERT INTO.

INSERT INTO TABLE_NAME [(column1, column2, column3,...columnN)]  
VALUES (value1, value2, value3,...valueN);

Aqui, coluna1, coluna2, ... colunaN são os nomes das colunas na tabela na qual você deseja inserir dados.

Você pode não precisar especificar o (s) nome (s) da (s) coluna (s) na consulta SQLite se estiver adicionando valores para todas as colunas da tabela. No entanto, certifique-se de que a ordem dos valores esteja na mesma ordem das colunas da tabela. A sintaxe SQLite INSERT INTO seria a seguinte -

INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);

Exemplo

Considere que você já criou a tabela COMPANY em seu testDB.db da seguinte forma -

sqlite> CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

Agora, as instruções a seguir criariam seis registros na tabela COMPANY.

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Paul', 32, 'California', 20000.00 );

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2, 'Allen', 25, 'Texas', 15000.00 );

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (5, 'David', 27, 'Texas', 85000.00 );

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (6, 'Kim', 22, 'South-Hall', 45000.00 );

Você pode criar um registro na tabela COMPANY usando a segunda sintaxe da seguinte forma -

INSERT INTO COMPANY VALUES (7, 'James', 24, 'Houston', 10000.00 );

Todas as declarações acima criariam os seguintes registros na tabela COMPANY. No próximo capítulo, você aprenderá como exibir todos esses registros de uma tabela.

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

Preencher uma tabela usando outra tabela

Você pode preencher os dados em uma tabela por meio da instrução select sobre outra tabela, desde que outra tabela tenha um conjunto de campos, que são necessários para preencher a primeira tabela. Aqui está a sintaxe -

INSERT INTO first_table_name [(column1, column2, ... columnN)] 
   SELECT column1, column2, ...columnN 
   FROM second_table_name
   [WHERE condition];

Por enquanto, você pode pular a instrução acima. Primeiro, vamos aprender as cláusulas SELECT e WHERE que serão abordadas nos capítulos subsequentes.

SQLite SELECTdeclaração é usada para buscar os dados de uma tabela de banco de dados SQLite que retorna dados na forma de uma tabela de resultados. Essas tabelas de resultados também são chamadasresult sets.

Sintaxe

A seguir está a sintaxe básica da instrução SQLite SELECT.

SELECT column1, column2, columnN FROM table_name;

Aqui, coluna1, coluna2 ... são os campos de uma tabela, cujos valores você deseja buscar. Se você deseja buscar todos os campos disponíveis no campo, você pode usar a seguinte sintaxe -

SELECT * FROM table_name;

Exemplo

Considere a tabela COMPANY com os seguintes registros -

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

A seguir está um exemplo para buscar e exibir todos esses registros usando a instrução SELECT. Aqui, os três primeiros comandos foram usados para definir uma saída formatada corretamente.

sqlite>.header on
sqlite>.mode column
sqlite> SELECT * FROM COMPANY;

Finalmente, você obterá o seguinte resultado.

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

Se você deseja buscar apenas os campos selecionados da tabela COMPANY, use a seguinte consulta -

sqlite> SELECT ID, NAME, SALARY FROM COMPANY;

A consulta acima produzirá o seguinte resultado.

ID          NAME        SALARY
----------  ----------  ----------
1           Paul        20000.0
2           Allen       15000.0
3           Teddy       20000.0
4           Mark        65000.0
5           David       85000.0
6           Kim         45000.0
7           James       10000.0

Configurando a largura da coluna de saída

Às vezes, você enfrentará um problema relacionado à saída truncada no caso de .mode columno que acontece devido à largura padrão da coluna a ser exibida. O que você pode fazer é definir a largura da coluna exibível usando.width num, num.... comando da seguinte forma -

sqlite>.width 10, 20, 10
sqlite>SELECT * FROM COMPANY;

O de cima .width O comando define a largura da primeira coluna em 10, a largura da segunda coluna em 20 e a largura da terceira coluna em 10. Finalmente, a instrução SELECT acima fornecerá o seguinte resultado.

ID          NAME                  AGE         ADDRESS     SALARY
----------  --------------------  ----------  ----------  ----------
1           Paul                  32          California  20000.0
2           Allen                 25          Texas       15000.0
3           Teddy                 23          Norway      20000.0
4           Mark                  25          Rich-Mond   65000.0
5           David                 27          Texas       85000.0
6           Kim                   22          South-Hall  45000.0
7           James                 24          Houston     10000.0

Informação de esquema

Como todo o dot commands estão disponíveis no prompt do SQLite, portanto, enquanto estiver programando com SQLite, você usará a seguinte instrução SELECT com sqlite_master tabela para listar todas as tabelas criadas em seu banco de dados.

sqlite> SELECT tbl_name FROM sqlite_master WHERE type = 'table';

Supondo que você tenha apenas a tabela COMPANY em seu testDB.db, isso produzirá o seguinte resultado.

tbl_name
----------
COMPANY

Você pode listar informações completas sobre a tabela COMPANY da seguinte forma -

sqlite> SELECT sql FROM sqlite_master WHERE type = 'table' AND tbl_name = 'COMPANY';

Supondo que você tenha apenas a tabela COMPANY em seu testDB.db, isso produzirá o seguinte resultado.

CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
)

O que é um operador no SQLite?

Um operador é uma palavra reservada ou um caractere usado principalmente na cláusula WHERE de uma instrução SQLite para realizar operação (ões), como comparações e operações aritméticas.

Operadores são usados para especificar condições em uma instrução SQLite e servir como conjunções para várias condições em uma instrução.

Operadores aritméticos
Operadores de comparação
Operadores lógicos
Operadores bit a bit

Operadores aritméticos SQLite

Assumir variável a contém 10 e variável b contém 20, então os operadores aritméticos SQLite serão usados da seguinte maneira -

Mostrar exemplos

Operador	Descrição	Exemplo
+ (Adição)	Adiciona valores em qualquer lado do operador	a + b dará 30
- (Subtração)	Subtrai o operando direito do operando esquerdo	a - b dará -10
* (Multiplicação)	Multiplica os valores em ambos os lados do operador	a * b dará 200
/ (Divisão)	Divide o operando esquerdo pelo operando direito	b / a dará 2
% (Módulo)	Divide o operando à esquerda pelo operando à direita e retorna o restante	b% a dará 0

Operadores de comparação SQLite

Assumir variável a contém 10 e variável b contém 20, então os operadores de comparação SQLite serão usados como segue

Mostrar exemplos

Operador	Descrição	Exemplo
==	Verifica se os valores dos dois operandos são iguais ou não, caso positivo a condição torna-se verdadeira.	(a == b) não é verdade.
=	Verifica se os valores dos dois operandos são iguais ou não, caso positivo a condição torna-se verdadeira.	(a = b) não é verdade.
! =	Verifica se os valores dos dois operandos são iguais ou não, se os valores não são iguais, a condição se torna verdadeira.	(a! = b) é verdade.
<>	Verifica se os valores dos dois operandos são iguais ou não, se os valores não são iguais, a condição se torna verdadeira.	(a <> b) é verdadeiro.
>	Verifica se os valores do operando esquerdo são maiores que o valor do operando direito, caso positivo a condição torna-se verdadeira.	(a> b) não é verdade.
<	Verifica se os valores do operando esquerdo são menores que o valor do operando direito, caso positivo a condição torna-se verdadeira.	(a <b) é verdadeiro.
> =	Verifica se o valor do operando esquerdo é maior ou igual ao valor do operando direito, se sim a condição torna-se verdadeira.	(a> = b) não é verdade.
<=	Verifica se o valor do operando esquerdo é menor ou igual ao valor do operando direito, caso positivo a condição torna-se verdadeira.	(a <= b) é verdadeiro.
! <	Verifica se o valor do operando esquerdo não é menor que o valor do operando direito; em caso afirmativo, a condição torna-se verdadeira.	(a! <b) é falso.
!>	Verifica se o valor do operando esquerdo não é maior que o valor do operando direito, caso positivo a condição torna-se verdadeira.	(a!> b) é verdade.

Operadores lógicos SQLite

Aqui está uma lista de todos os operadores lógicos disponíveis no SQLite.

Mostrar exemplos

Sr. Não.	Operador e descrição
1	AND O operador AND permite a existência de várias condições na cláusula WHERE de uma instrução SQL.
2	BETWEEN O operador BETWEEN é usado para pesquisar valores que estão dentro de um conjunto de valores, dados o valor mínimo e o valor máximo.
3	EXISTS O operador EXISTS é usado para pesquisar a presença de uma linha em uma tabela especificada que atenda a certos critérios.
4	IN O operador IN é usado para comparar um valor a uma lista de valores literais que foram especificados.
5	NOT IN A negação do operador IN que é usado para comparar um valor a uma lista de valores literais que foram especificados.
6	LIKE O operador LIKE é usado para comparar um valor a valores semelhantes usando operadores curinga.
7	GLOB O operador GLOB é usado para comparar um valor a valores semelhantes usando operadores curinga. Além disso, GLOB diferencia maiúsculas de minúsculas, ao contrário de LIKE.
8	NOT O operador NOT inverte o significado do operador lógico com o qual é usado. Por exemplo. NÃO EXISTE, NÃO ENTRE, NÃO ENTRE, etc.This is negate operator.
9	OR O operador OR é usado para combinar várias condições na cláusula WHERE de uma instrução SQL.
10	IS NULL O operador NULL é usado para comparar um valor com um valor NULL.
11	IS O operador IS funciona como =
12	IS NOT O operador IS funciona como! =
13	\|\| Adiciona duas strings diferentes e cria uma nova.
14	UNIQUE O operador UNIQUE pesquisa cada linha de uma tabela especificada em busca de exclusividade (sem duplicatas).

Operadores de bit a bit SQLite

O operador bit a bit funciona em bits e executa a operação bit a bit. A seguir está a tabela de verdade para& e |.

p	q	p & q	p \| q
0	0	0	0
0	1	0	1
1	1	1	1
1	0	0	1

Suponha que A= 60; eB = 13, então em formato binário, eles serão os seguintes -

A = 0011 1100

B = 0000 1101

-----------------

A&B = 0000 1100

A | B = 0011 1101

~ A = 1100 0011

Os operadores bit a bit suportados pela linguagem SQLite estão listados na tabela a seguir. Assumir variávelA detém 60 e variável B detém 13, então -

Mostrar exemplos

Operador	Descrição	Exemplo
E	O operador Binário AND copia um bit para o resultado, se existir em ambos os operandos.	(A e B) dará 12, que é 0000 1100
\|	O operador binário OR copia um bit, se existir em qualquer operando.	(A \| B) dará 61, que é 0011 1101
~	O operador de complemento binários é unário e tem o efeito de 'inverter' bits.	(~ A) dará -61 que é 1100 0011 na forma de complemento de 2 devido a um número binário com sinal
<<	Operador binário de deslocamento à esquerda. O valor dos operandos à esquerda é movido para a esquerda pelo número de bits especificado pelo operando à direita.	Um << 2 dará 240, que é 1111 0000
>>	Operador binário de deslocamento à direita. O valor dos operandos à esquerda é movido para a direita pelo número de bits especificado pelo operando à direita.	Um >> 2 dará 15, que é 0000 1111

Uma expressão é uma combinação de um ou mais valores, operadores e funções SQL que avaliam um valor.

As expressões SQL são como fórmulas e são escritas em linguagem de consulta. Você também pode usar para consultar o banco de dados para um conjunto específico de dados.

Sintaxe

Considere a sintaxe básica da instrução SELECT da seguinte maneira -

SELECT column1, column2, columnN 
FROM table_name 
WHERE [CONDITION | EXPRESSION];

A seguir estão os diferentes tipos de expressões SQLite.

SQLite - Expressões Booleanas

As Expressões Booleanas SQLite buscam os dados com base na correspondência de um único valor. A seguir está a sintaxe -

SELECT column1, column2, columnN 
FROM table_name 
WHERE SINGLE VALUE MATCHTING EXPRESSION;

Considere a tabela COMPANY com os seguintes registros -

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

A seguir está um exemplo simples que mostra o uso de Expressões Booleanas SQLite -

sqlite> SELECT * FROM COMPANY WHERE SALARY = 10000;

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
4           James        24          Houston   10000.0

SQLite - Expressão Numérica

Essas expressões são usadas para realizar qualquer operação matemática em qualquer consulta. A seguir está a sintaxe -

SELECT numerical_expression as OPERATION_NAME
[FROM table_name WHERE CONDITION] ;

Aqui, numerical_expression é usado para expressão matemática ou qualquer fórmula. A seguir está um exemplo simples que mostra o uso de Expressões Numéricas SQLite.

sqlite> SELECT (15 + 6) AS ADDITION
ADDITION = 21

Existem várias funções integradas, como avg(), sum(), count(), etc., para realizar o que é conhecido como aggregate data calculations contra uma tabela ou coluna específica da tabela.

sqlite> SELECT COUNT(*) AS "RECORDS" FROM COMPANY; 
RECORDS = 7

SQLite - Expressões de data

Date Expressions retorna os valores atuais de data e hora do sistema. Essas expressões são usadas em várias manipulações de dados.

sqlite> SELECT CURRENT_TIMESTAMP;
CURRENT_TIMESTAMP = 2013-03-17 10:43:35

SQLite WHERE cláusula é usada para especificar uma condição ao buscar os dados de uma tabela ou várias tabelas.

Se a condição fornecida for satisfeita, significa verdadeiro, então ele retorna o valor específico da tabela. Você terá que usar a cláusula WHERE para filtrar os registros e buscar apenas os registros necessários.

A cláusula WHERE não é usada apenas na instrução SELECT, mas também é usada na instrução UPDATE, DELETE, etc., que será abordada nos capítulos subsequentes.

Sintaxe

A seguir está a sintaxe básica da instrução SQLite SELECT com a cláusula WHERE.

SELECT column1, column2, columnN 
FROM table_name
WHERE [condition]

Exemplo

Você pode especificar uma condição usando Comparação ou Operadores Lógicos como>, <, =, LIKE, NOT, etc. Considere a tabela COMPANY com os seguintes registros -

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

A seguir está um exemplo simples que mostra o uso de operadores lógicos SQLite. A instrução SELECT a seguir lista todos os registros onde AGE é maior ou igual a 25AND salário é maior ou igual a 65.000,00.

sqlite> SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 65000;

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0

A instrução SELECT a seguir lista todos os registros onde AGE é maior ou igual a 25 OR salário é maior ou igual a 65.000,00.

sqlite> SELECT * FROM COMPANY WHERE AGE >= 25 OR SALARY >= 65000;

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0

A instrução SELECT a seguir lista todos os registros onde AGE não é NULL, o que significa todos os registros porque nenhum dos registros tem AGE igual a NULL.

sqlite>  SELECT * FROM COMPANY WHERE AGE IS NOT NULL;

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

A instrução SELECT a seguir lista todos os registros onde NAME começa com 'Ki', não importa o que venha depois de 'Ki'.

sqlite> SELECT * FROM COMPANY WHERE NAME LIKE 'Ki%';

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
6           Kim         22          South-Hall  45000.0

A instrução SELECT a seguir lista todos os registros onde NAME começa com 'Ki', não importa o que venha depois de 'Ki'.

sqlite> SELECT * FROM COMPANY WHERE NAME GLOB 'Ki*';

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
6           Kim         22          South-Hall  45000.0

A instrução SELECT a seguir lista todos os registros em que o valor de AGE é 25 ou 27.

sqlite> SELECT * FROM COMPANY WHERE AGE IN ( 25, 27 );

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
2           Allen       25          Texas       15000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0

A instrução SELECT a seguir lista todos os registros em que o valor de AGE não é 25 nem 27.

sqlite> SELECT * FROM COMPANY WHERE AGE NOT IN ( 25, 27 );

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
3           Teddy       23          Norway      20000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

A instrução SELECT a seguir lista todos os registros em que o valor de idade está entre 25 e 27.

sqlite> SELECT * FROM COMPANY WHERE AGE BETWEEN 25 AND 27;

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
2           Allen       25          Texas       15000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0

A instrução SELECT seguinte faz uso da subconsulta SQL, onde a subconsulta encontra todos os registros com o campo AGE tendo SALARY> 65000 e posterior, a cláusula WHERE está sendo usada junto com o operador EXISTS para listar todos os registros onde existe AGE da consulta externa no resultado retornado pela subconsulta -

sqlite> SELECT AGE FROM COMPANY 
   WHERE EXISTS (SELECT AGE FROM COMPANY WHERE SALARY > 65000);

AGE
----------
32
25
23
25
27
22
24

A instrução SELECT a seguir usa a subconsulta SQL onde a subconsulta encontra todos os registros com o campo AGE tendo SALARY> 65000 e posterior, a cláusula WHERE está sendo usada junto com o operador> para listar todos os registros onde AGE da consulta externa é maior do que a idade no resultado retornado pela subconsulta.

sqlite> SELECT * FROM COMPANY 
   WHERE AGE > (SELECT AGE FROM COMPANY WHERE SALARY > 65000);

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0

SQLite AND E ORoperadores são usados para compilar várias condições para restringir os dados selecionados em uma instrução SQLite. Esses dois operadores são chamadosconjunctive operators.

Esses operadores fornecem um meio de fazer várias comparações com diferentes operadores na mesma instrução SQLite.

O operador AND

o ANDoperador permite a existência de várias condições na cláusula WHERE de uma instrução SQLite. Ao usar o operador AND, a condição completa será considerada verdadeira quando todas as condições forem verdadeiras. Por exemplo, [condição1] E [condição2] serão verdadeiras apenas quando ambas as condições1 e 2 forem verdadeiras.

Sintaxe

A seguir está a sintaxe básica do operador AND com a cláusula WHERE.

SELECT column1, column2, columnN 
FROM table_name
WHERE [condition1] AND [condition2]...AND [conditionN];

Você pode combinar Nnúmero de condições usando o operador AND. Para que uma ação seja executada pela instrução SQLite, seja uma transação ou consulta, todas as condições separadas por AND devem ser TRUE.

Exemplo

Considere a tabela COMPANY com os seguintes registros -

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

A instrução SELECT a seguir lista todos os registros onde AGE é maior ou igual a 25 AND salário é maior ou igual a 65.000,00.

sqlite> SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 65000;

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0

O operador OR

O operador OR também é usado para combinar várias condições na cláusula WHERE de uma instrução SQLite. Ao usar o operador OR, a condição completa será considerada verdadeira quando pelo menos qualquer uma das condições for verdadeira. Por exemplo, [condição1] OU [condição2] será verdadeira se a condição1 ou a condição2 forem verdadeiras.

Sintaxe

A seguir está a sintaxe básica do operador OR com a cláusula WHERE.

SELECT column1, column2, columnN 
FROM table_name
WHERE [condition1] OR [condition2]...OR [conditionN]

Você pode combinar Nnúmero de condições usando o operador OR. Para que uma ação seja realizada pela instrução SQLite, seja uma transação ou consulta, apenas UMA das condições separadas por OR deve ser TRUE.

Exemplo

Considere a tabela COMPANY com os seguintes registros.

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

A instrução SELECT a seguir lista todos os registros onde AGE é maior ou igual a 25 OR salário é maior ou igual a 65.000,00.

sqlite> SELECT * FROM COMPANY WHERE AGE >= 25 OR SALARY >= 65000;

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0

SQLite UPDATEA consulta é usada para modificar os registros existentes em uma tabela. Você pode usar a cláusula WHERE com a consulta UPDATE para atualizar as linhas selecionadas, caso contrário, todas as linhas seriam atualizadas.

Sintaxe

A seguir está a sintaxe básica da consulta UPDATE com a cláusula WHERE.

UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];

Você pode combinar N número de condições usando os operadores AND ou OR.

Exemplo

Considere a tabela COMPANY com os seguintes registros -

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

A seguir está um exemplo que atualizará ADDRESS para um cliente cujo ID é 6.

sqlite> UPDATE COMPANY SET ADDRESS = 'Texas' WHERE ID = 6;

Agora, a tabela COMPANY terá os seguintes registros.

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          Texas       45000.0
7           James       24          Houston     10000.0

Se você deseja modificar todos os valores das colunas ADDRESS e SALARY na tabela COMPANY, você não precisa usar a cláusula WHERE e a consulta UPDATE será a seguinte -

sqlite> UPDATE COMPANY SET ADDRESS = 'Texas', SALARY = 20000.00;

Agora, a tabela COMPANY terá os seguintes registros -

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          Texas       20000.0
2           Allen       25          Texas       20000.0
3           Teddy       23          Texas       20000.0
4           Mark        25          Texas       20000.0
5           David       27          Texas       20000.0
6           Kim         22          Texas       20000.0
7           James       24          Texas       20000.0

SQLite DELETEA consulta é usada para excluir os registros existentes de uma tabela. Você pode usar a cláusula WHERE com a consulta DELETE para excluir as linhas selecionadas, caso contrário, todos os registros seriam excluídos.

Sintaxe

A seguir está a sintaxe básica da consulta DELETE com a cláusula WHERE.

DELETE FROM table_name
WHERE [condition];

Você pode combinar N número de condições usando os operadores AND ou OR.

Exemplo

Considere a tabela COMPANY com os seguintes registros.

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

A seguir está um exemplo, que EXCLUIRÁ um cliente cujo ID é 7.

sqlite> DELETE FROM COMPANY WHERE ID = 7;

Agora a tabela COMPANY terá os seguintes registros.

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0

Se você deseja DELETE todos os registros da tabela COMPANY, você não precisa usar a cláusula WHERE com a consulta DELETE, que será a seguinte -

sqlite> DELETE FROM COMPANY;

Agora, a tabela COMPANY não possui nenhum registro, pois todos os registros foram excluídos pela instrução DELETE.

SQLite LIKEoperador é usado para comparar valores de texto com um padrão usando curingas. Se a expressão de pesquisa puder ser correspondida à expressão padrão, o operador LIKE retornará verdadeiro, que é 1. Há dois curingas usados em conjunto com o operador LIKE -

O sinal de porcentagem (%)
O sublinhado (_)

O sinal de porcentagem representa zero, um ou vários números ou caracteres. O sublinhado representa um único número ou caractere. Esses símbolos podem ser usados em combinações.

Sintaxe

A seguir está a sintaxe básica de% e _.

SELECT FROM table_name
WHERE column LIKE 'XXXX%'
or 
SELECT FROM table_name
WHERE column LIKE '%XXXX%'
or
SELECT FROM table_name
WHERE column LIKE 'XXXX_'
or
SELECT FROM table_name
WHERE column LIKE '_XXXX'
or
SELECT FROM table_name
WHERE column LIKE '_XXXX_'

Você pode combinar Nnúmero de condições usando os operadores AND ou OR. Aqui, XXXX pode ser qualquer valor numérico ou string.

Exemplo

A tabela a seguir lista vários exemplos que mostram a parte WHERE com cláusulas LIKE diferentes com os operadores '%' e '_'.

Sr. Não.	Declaração e descrição
1	WHERE SALARY LIKE '200%' Encontra todos os valores que começam com 200
2	WHERE SALARY LIKE '%200%' Encontra qualquer valor que tenha 200 em qualquer posição
3	WHERE SALARY LIKE '_00%' Encontra qualquer valor que tenha 00 na segunda e terceira posições
4	WHERE SALARY LIKE '2_%_%' Encontra qualquer valor que comece com 2 e tenha pelo menos 3 caracteres de comprimento
5	WHERE SALARY LIKE '%2' Encontra todos os valores que terminam com 2
6	WHERE SALARY LIKE '_2%3' Encontra qualquer valor que tenha 2 na segunda posição e termine com 3
7	WHERE SALARY LIKE '2___3' Encontra qualquer valor em um número de cinco dígitos que começa com 2 e termina com 3

Tomemos um exemplo real, considere a tabela COMPANY com os seguintes registros.

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

A seguir está um exemplo, que exibirá todos os registros da tabela COMPANY onde AGE começa com 2.

sqlite> SELECT * FROM COMPANY WHERE AGE LIKE '2%';

Isso produzirá o seguinte resultado.

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

A seguir está um exemplo, que exibirá todos os registros da tabela COMPANY onde ADDRESS terá um hífen (-) dentro do texto.

sqlite> SELECT * FROM COMPANY WHERE ADDRESS  LIKE '%-%';

Isso produzirá o seguinte resultado.

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
4           Mark        25          Rich-Mond   65000.0
6           Kim         22          South-Hall  45000.0

SQLite GLOBoperador é usado para corresponder apenas valores de texto a um padrão usando curingas. Se a expressão de pesquisa puder ser correspondida à expressão padrão, o operador GLOB retornará verdadeiro, que é 1. Ao contrário do operador LIKE, GLOB diferencia maiúsculas de minúsculas e segue a sintaxe do UNIX para especificar OS seguintes curingas.

O sinal de asterisco (*)
O ponto de interrogação (?)

O sinal de asterisco (*) representa zero ou vários números ou caracteres. O ponto de interrogação (?) Representa um único número ou caractere.

Sintaxe

A seguir está a sintaxe básica de * e ?.

SELECT FROM table_name
WHERE column GLOB 'XXXX*'
or 
SELECT FROM table_name
WHERE column GLOB '*XXXX*'
or
SELECT FROM table_name
WHERE column GLOB 'XXXX?'
or
SELECT FROM table_name
WHERE column GLOB '?XXXX'
or
SELECT FROM table_name
WHERE column GLOB '?XXXX?'
or
SELECT FROM table_name
WHERE column GLOB '????'

Você pode combinar Nnúmero de condições usando os operadores AND ou OR. Aqui, XXXX pode ser qualquer valor numérico ou string.

Exemplo

A tabela a seguir lista vários exemplos que mostram a parte WHERE com cláusulas LIKE diferentes com '*' e '?' operadores.

Sr. Não.	Declaração e descrição
1	*WHERE SALARY GLOB '200'** Encontra todos os valores que começam com 200
2	*WHERE SALARY GLOB '200'* Encontra qualquer valor que tenha 200 em qualquer posição
3	*WHERE SALARY GLOB '?00'** Encontra qualquer valor que tenha 00 na segunda e terceira posições
4	WHERE SALARY GLOB '2??' Encontra qualquer valor que comece com 2 e tenha pelo menos 3 caracteres de comprimento
5	*WHERE SALARY GLOB '2'** Encontra todos os valores que terminam com 2
6	*WHERE SALARY GLOB '?23'** Encontra qualquer valor que tenha um 2 na segunda posição e termine com um 3
7	WHERE SALARY GLOB '2???3' Encontra qualquer valor em um número de cinco dígitos que começa com 2 e termina com 3

Vamos dar um exemplo real, considere a tabela COMPANY com os seguintes registros -

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

A seguir está um exemplo, que exibirá todos os registros da tabela COMPANY, onde AGE começa com 2.

sqlite> SELECT * FROM COMPANY WHERE AGE  GLOB '2*';

Isso produzirá o seguinte resultado.

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

A seguir está um exemplo, que irá exibir todos os registros da tabela COMPANY onde ADDRESS terá um hífen (-) dentro do texto -

sqlite> SELECT * FROM COMPANY WHERE ADDRESS  GLOB '*-*';

Isso produzirá o seguinte resultado.

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
4           Mark        25          Rich-Mond   65000.0
6           Kim         22          South-Hall  45000.0

SQLite LIMIT A cláusula é usada para limitar a quantidade de dados retornada pela instrução SELECT.

Sintaxe

A seguir está a sintaxe básica da instrução SELECT com a cláusula LIMIT.

SELECT column1, column2, columnN 
FROM table_name
LIMIT [no of rows]

A seguir está a sintaxe da cláusula LIMIT quando ela é usada junto com a cláusula OFFSET.

SELECT column1, column2, columnN 
FROM table_name
LIMIT [no of rows] OFFSET [row num]

O mecanismo SQLite retornará linhas começando da próxima linha até o OFFSET fornecido, conforme mostrado abaixo no último exemplo.

Exemplo

Considere a tabela COMPANY com os seguintes registros -

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

A seguir está um exemplo, que limita a linha na tabela de acordo com o número de linhas que você deseja buscar na tabela.

sqlite> SELECT * FROM COMPANY LIMIT 6;

Isso produzirá o seguinte resultado.

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0

No entanto, em certas situações, pode ser necessário selecionar um conjunto de registros de um deslocamento específico. Aqui está um exemplo que pega 3 registros a partir da ^3ª posição.

sqlite> SELECT * FROM COMPANY LIMIT 3 OFFSET 2;

Isso produzirá o seguinte resultado.

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0

SQLite ORDER BY cláusula é usada para classificar os dados em ordem crescente ou decrescente, com base em uma ou mais colunas.

Sintaxe

A seguir está a sintaxe básica da cláusula ORDER BY.

SELECT column-list 
FROM table_name 
[WHERE condition] 
[ORDER BY column1, column2, .. columnN] [ASC | DESC];

Você pode usar mais de uma coluna na cláusula ORDER BY. Certifique-se de que qualquer coluna que você está usando para classificar, essa coluna deve estar disponível na lista de colunas.

Exemplo

Considere a tabela COMPANY com os seguintes registros.

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

A seguir está um exemplo, que classificará o resultado em ordem decrescente por SALÁRIO.

sqlite> SELECT * FROM COMPANY ORDER BY SALARY ASC;

Isso produzirá o seguinte resultado.

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
7           James       24          Houston     10000.0
2           Allen       25          Texas       15000.0
1           Paul        32          California  20000.0
3           Teddy       23          Norway      20000.0
6           Kim         22          South-Hall  45000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0

A seguir está um exemplo, que classificará o resultado em ordem decrescente por NOME e SALÁRIO.

sqlite> SELECT * FROM COMPANY ORDER BY NAME, SALARY ASC;

Isso produzirá o seguinte resultado.

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
2           Allen       25          Texas       15000.0
5           David       27          Texas       85000.0
7           James       24          Houston     10000.0
6           Kim         22          South-Hall  45000.0
4           Mark        25          Rich-Mond   65000.0
1           Paul        32          California  20000.0
3           Teddy       23          Norway      20000.0

A seguir está um exemplo, que classificará o resultado em ordem decrescente por NOME.

sqlite> SELECT * FROM COMPANY ORDER BY NAME DESC;

Isso produzirá o seguinte resultado.

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
3           Teddy       23          Norway      20000.0
1           Paul        32          California  20000.0
4           Mark        25          Rich-Mond   65000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0
5           David       27          Texas       85000.0
2           Allen       25          Texas       15000.0

SQLite GROUP BY cláusula é usada em colaboração com a instrução SELECT para organizar dados idênticos em grupos.

A cláusula GROUP BY segue a cláusula WHERE em uma instrução SELECT e precede a cláusula ORDER BY.

Sintaxe

A seguir está a sintaxe básica da cláusula GROUP BY. A cláusula GROUP BY deve seguir as condições da cláusula WHERE e deve preceder a cláusula ORDER BY, se uma for usada.

SELECT column-list
FROM table_name
WHERE [ conditions ]
GROUP BY column1, column2....columnN
ORDER BY column1, column2....columnN

Você pode usar mais de uma coluna na cláusula GROUP BY. Certifique-se de qualquer coluna que você está usando para agrupar, essa coluna deve estar disponível na lista de colunas.

Exemplo

Considere a tabela COMPANY com os seguintes registros.

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

Se você quiser saber o valor total do salário de cada cliente, a consulta GROUP BY será a seguinte -

sqlite> SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME;

Isso produzirá o seguinte resultado -

NAME        SUM(SALARY)
----------  -----------
Allen       15000.0
David       85000.0
James       10000.0
Kim         45000.0
Mark        65000.0
Paul        20000.0
Teddy       20000.0

Agora, vamos criar mais três registros na tabela COMPANY usando as seguintes instruções INSERT.

INSERT INTO COMPANY VALUES (8, 'Paul', 24, 'Houston', 20000.00 );
INSERT INTO COMPANY VALUES (9, 'James', 44, 'Norway', 5000.00 );
INSERT INTO COMPANY VALUES (10, 'James', 45, 'Texas', 5000.00 );

Agora, nossa tabela possui os seguintes registros com nomes duplicados.

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0
8           Paul        24          Houston     20000.0
9           James       44          Norway      5000.0
10          James       45          Texas       5000.0

Novamente, vamos usar a mesma instrução para agrupar por todos os registros usando a coluna NAME da seguinte forma -

sqlite> SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME ORDER BY NAME;

Isso produzirá o seguinte resultado.

NAME        SUM(SALARY)
----------  -----------
Allen       15000
David       85000
James       20000
Kim         45000
Mark        65000
Paul        40000
Teddy       20000

Vamos usar a cláusula ORDER BY junto com a cláusula GROUP BY da seguinte maneira -

sqlite>  SELECT NAME, SUM(SALARY) 
   FROM COMPANY GROUP BY NAME ORDER BY NAME DESC;

Isso produzirá o seguinte resultado.

NAME        SUM(SALARY)
----------  -----------
Teddy       20000
Paul        40000
Mark        65000
Kim         45000
James       20000
David       85000
Allen       15000

A cláusula HAVING permite que você especifique as condições que filtram quais resultados de grupo aparecem nos resultados finais.

A cláusula WHERE coloca condições nas colunas selecionadas, enquanto a cláusula HAVING coloca condições nos grupos criados pela cláusula GROUP BY.

Sintaxe

A seguir está a posição da cláusula HAVING em uma consulta SELECT.

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

A cláusula HAVING deve seguir a cláusula GROUP BY em uma consulta e também deve preceder a cláusula ORDER BY, se usada. A seguir está a sintaxe da instrução SELECT, incluindo a cláusula HAVING.

SELECT column1, column2
FROM table1, table2
WHERE [ conditions ]
GROUP BY column1, column2
HAVING [ conditions ]
ORDER BY column1, column2

Exemplo

Considere a tabela COMPANY com os seguintes registros.

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0
8           Paul        24          Houston     20000.0
9           James       44          Norway      5000.0
10          James       45          Texas       5000.0

A seguir está o exemplo, que exibirá o registro para o qual a contagem de nomes é menor que 2.

sqlite > SELECT * FROM COMPANY GROUP BY name HAVING count(name) < 2;

Isso produzirá o seguinte resultado.

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
2           Allen       25          Texas       15000
5           David       27          Texas       85000
6           Kim         22          South-Hall  45000
4           Mark        25          Rich-Mond   65000
3           Teddy       23          Norway      20000

A seguir está o exemplo, que exibirá o registro para o qual a contagem de nomes é maior que 2.

sqlite > SELECT * FROM COMPANY GROUP BY name HAVING count(name) > 2;

Isso produzirá o seguinte resultado.

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
10          James       45          Texas       5000

SQLite DISTINCT A palavra-chave é usada em conjunto com a instrução SELECT para eliminar todos os registros duplicados e buscar apenas os registros únicos.

Pode haver uma situação em que você tenha vários registros duplicados em uma tabela. Ao buscar esses registros, faz mais sentido buscar apenas registros únicos em vez de buscar registros duplicados.

Sintaxe

A seguir está a sintaxe básica da palavra-chave DISTINCT para eliminar registros duplicados.

SELECT DISTINCT column1, column2,.....columnN 
FROM table_name
WHERE [condition]

Exemplo

Considere a tabela COMPANY com os seguintes registros.

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0
8           Paul        24          Houston     20000.0
9           James       44          Norway      5000.0
10          James       45          Texas       5000.0

Primeiro, vamos ver como a seguinte consulta SELECT retorna registros de salários duplicados.

sqlite> SELECT name FROM COMPANY;

Isso produzirá o seguinte resultado.

NAME
----------
Paul
Allen
Teddy
Mark
David
Kim
James
Paul
James
James

Agora, vamos usar DISTINCT com a consulta SELECT acima e veja o resultado.

sqlite> SELECT DISTINCT name FROM COMPANY;

Isso produzirá o seguinte resultado, onde não há entrada duplicada.

NAME
----------
Paul
Allen
Teddy
Mark
David
Kim
James

SQLite PRAGMAcommand é um comando especial a ser usado para controlar várias variáveis ambientais e sinalizadores de estado dentro do ambiente SQLite. Um valor PRAGMA pode ser lido e também pode ser definido com base nos requisitos.

Sintaxe

Para consultar o valor PRAGMA atual, basta fornecer o nome do pragma.

PRAGMA pragma_name;

Para definir um novo valor para PRAGMA, use a seguinte sintaxe.

PRAGMA pragma_name = value;

O modo de configuração pode ser o nome ou o equivalente inteiro, mas o valor retornado sempre será um inteiro.

auto_vacuum Pragma

o auto_vacuumpragma obtém ou define o modo de vácuo automático. A seguir está a sintaxe simples.

PRAGMA [database.]auto_vacuum;
PRAGMA [database.]auto_vacuum = mode;

Onde mode pode ser qualquer um dos seguintes -

Sr. Não.	Valor e descrição do pragma
1	0 or NONE O vácuo automático está desativado. Este é o modo padrão, o que significa que um arquivo de banco de dados nunca diminuirá de tamanho a menos que seja aspirado manualmente usando o comando VACUUM.
2	1 or FULL O vácuo automático é ativado e totalmente automático, o que permite que um arquivo de banco de dados encolha à medida que os dados são removidos do banco de dados.
3	2 or INCREMENTAL O vácuo automático está habilitado, mas deve ser ativado manualmente. Neste modo, os dados de referência são mantidos, mas as páginas gratuitas são simplesmente colocadas na lista gratuita. Essas páginas podem ser recuperadas usando oincremental_vacuum pragma a qualquer momento.

cache_size Pragma

o cache_sizepragma pode obter ou definir temporariamente o tamanho máximo do cache de página na memória. A seguir está a sintaxe simples.

PRAGMA [database.]cache_size;
PRAGMA [database.]cache_size = pages;

o pagesvalor representa o número de páginas no cache. O cache de página embutido tem um tamanho padrão de 2.000 páginas e um tamanho mínimo de 10 páginas.

case_sensitive_like Pragma

o case_sensitive_likepragma controla a distinção entre maiúsculas e minúsculas da expressão LIKE embutida. Por padrão, esse pragma é falso, o que significa que o operador LIKE integrado ignora a caixa das letras. A seguir está a sintaxe simples.

PRAGMA case_sensitive_like = [true|false];

Não há como consultar o estado atual desse pragma.

count_changes Pragma

count_changespragma obtém ou define o valor de retorno de instruções de manipulação de dados, como INSERT, UPDATE e DELETE. A seguir está a sintaxe simples.

PRAGMA count_changes;
PRAGMA count_changes = [true|false];

Por padrão, esse pragma é falso e essas declarações não retornam nada. Se definido como verdadeiro, cada uma das instruções mencionadas retornará uma tabela de uma coluna e uma linha consistindo em um único valor inteiro indicando as linhas afetadas pela operação.

database_list Pragma

o database_listpragma será usado para listar todos os bancos de dados anexados. A seguir está a sintaxe simples.

PRAGMA database_list;

Este pragma retornará uma tabela de três colunas com uma linha por banco de dados aberto ou anexado, fornecendo o número de sequência do banco de dados, seu nome e o arquivo associado.

codificação de pragma

o encodingpragma controla como as strings são codificadas e armazenadas em um arquivo de banco de dados. A seguir está a sintaxe simples.

PRAGMA encoding;
PRAGMA encoding = format;

O valor do formato pode ser um dos UTF-8, UTF-16le, ou UTF-16be.

freelist_count Pragma

o freelist_countpragma retorna um único inteiro indicando quantas páginas do banco de dados estão marcadas como livres e disponíveis. A seguir está a sintaxe simples.

PRAGMA [database.]freelist_count;

O valor do formato pode ser um dos UTF-8, UTF-16le, ou UTF-16be.

index_info Pragma

o index_infopragma retorna informações sobre um índice de banco de dados. A seguir está a sintaxe simples.

PRAGMA [database.]index_info( index_name );

O conjunto de resultados conterá uma linha para cada coluna contida no índice, fornecendo a sequência da coluna, o índice da coluna na tabela interna e o nome da coluna.

index_list Pragma

index_listpragma lista todos os índices associados a uma tabela. A seguir está a sintaxe simples.

PRAGMA [database.]index_list( table_name );

O conjunto de resultados conterá uma linha para cada índice, fornecendo a sequência do índice, o nome do índice e a bandeira indicando se o índice é único ou não.

journal_mode Pragma

o journal_modepragma obtém ou define o modo de diário que controla como o arquivo de diário é armazenado e processado. A seguir está a sintaxe simples.

PRAGMA journal_mode;
PRAGMA journal_mode = mode;
PRAGMA database.journal_mode;
PRAGMA database.journal_mode = mode;

Existem cinco modos de diário com suporte, conforme listado na tabela a seguir.

Sr. Não.	Valor e descrição do pragma
1	DELETE Este é o modo padrão. Aqui, na conclusão de uma transação, o arquivo de diário é excluído.
2	TRUNCATE O arquivo de diário é truncado para um comprimento de zero bytes.
3	PERSIST O arquivo do diário é deixado no local, mas o cabeçalho é sobrescrito para indicar que o diário não é mais válido.
4	MEMORY O registro do diário é mantido na memória, e não no disco.
5	OFF Nenhum registro de diário é mantido.

max_page_count Pragma

o max_page_countpragma obtém ou define a contagem máxima de páginas permitida para um banco de dados. A seguir está a sintaxe simples.

PRAGMA [database.]max_page_count;
PRAGMA [database.]max_page_count = max_page;

O valor padrão é 1.073.741.823, que é uma giga-página, o que significa que se o tamanho de página padrão for de 1 KB, isso permite que os bancos de dados cresçam até um terabyte.

page_count Pragma

o page_countpragma retorna no número atual de páginas no banco de dados. A seguir está a sintaxe simples -

PRAGMA [database.]page_count;

O tamanho do arquivo de banco de dados deve ser page_count * page_size.

page_size Pragma

o page_sizepragma obtém ou define o tamanho das páginas do banco de dados. A seguir está a sintaxe simples.

PRAGMA [database.]page_size;
PRAGMA [database.]page_size = bytes;

Por padrão, os tamanhos permitidos são 512, 1024, 2048, 4096, 8192, 16384 e 32768 bytes. A única maneira de alterar o tamanho da página em um banco de dados existente é definir o tamanho da página e, em seguida, VACUUM imediatamente o banco de dados.

parser_trace Pragma

o parser_tracepragma controla a impressão do estado de depuração à medida que analisa os comandos SQL. A seguir está a sintaxe simples.

PRAGMA parser_trace = [true|false];

Por padrão, ele é definido como falso, mas quando habilitado definindo-o como verdadeiro, o analisador SQL imprimirá seu estado conforme ele analisa os comandos SQL.

recursive_triggers Pragma

o recursive_triggerspragma obtém ou define a funcionalidade de gatilho recursivo. Se os gatilhos recursivos não estiverem habilitados, uma ação de gatilho não disparará outro gatilho. A seguir está a sintaxe simples.

PRAGMA recursive_triggers;
PRAGMA recursive_triggers = [true|false];

schema_version Pragma

o schema_versionpragma obtém ou define o valor da versão do esquema que é armazenado no cabeçalho do banco de dados. A seguir está a sintaxe simples.

PRAGMA [database.]schema_version;
PRAGMA [database.]schema_version = number;

Este é um valor inteiro assinado de 32 bits que rastreia as alterações do esquema. Sempre que um comando de alteração de esquema é executado (como, CREATE ... ou DROP ...), este valor é incrementado.

secure_delete Pragma

o secure_deletepragma é usado para controlar como o conteúdo é excluído do banco de dados. A seguir está a sintaxe simples.

PRAGMA secure_delete;
PRAGMA secure_delete = [true|false];
PRAGMA database.secure_delete;
PRAGMA database.secure_delete = [true|false];

O valor padrão para o sinalizador de exclusão segura normalmente está desativado, mas isso pode ser alterado com a opção de construção SQLITE_SECURE_DELETE.

sql_trace Pragma

o sql_tracepragma é usado para despejar os resultados do rastreamento SQL na tela. A seguir está a sintaxe simples.

PRAGMA sql_trace;
PRAGMA sql_trace = [true|false];

SQLite deve ser compilado com a diretiva SQLITE_DEBUG para que este pragma seja incluído.

pragma síncrono

o synchronouspragma obtém ou define o modo de sincronização de disco atual, que controla a agressividade com que o SQLite gravará os dados no armazenamento físico. A seguir está a sintaxe simples.

PRAGMA [database.]synchronous;
PRAGMA [database.]synchronous = mode;

SQLite oferece suporte aos seguintes modos de sincronização, conforme listado na tabela.

Sr. Não.	Valor e descrição do pragma
1	0 or OFF Sem sincronização
2	1 or NORMAL Sincronize após cada sequência de operações críticas do disco
3	2 or FULL Sincronizar após cada operação crítica do disco

temp_store Pragma

o temp_storepragma obtém ou define o modo de armazenamento usado por arquivos de banco de dados temporários. A seguir está a sintaxe simples.

PRAGMA temp_store;
PRAGMA temp_store = mode;

SQLite suporta os seguintes modos de armazenamento.

Sr. Não.	Valor e descrição do pragma
1	0 or DEFAULT Use o padrão de tempo de compilação. Normalmente ARQUIVO.
2	1 or FILE Use armazenamento baseado em arquivo.
3	2 or MEMORY Use armazenamento baseado em memória.

temp_store_directory Pragma

o temp_store_directorypragma obtém ou define o local usado para arquivos de banco de dados temporários. A seguir está a sintaxe simples.

PRAGMA temp_store_directory;
PRAGMA temp_store_directory = 'directory_path';

user_version Pragma

o user_versionpragma obtém ou define o valor da versão definida pelo usuário que é armazenado no cabeçalho do banco de dados. A seguir está a sintaxe simples.

PRAGMA [database.]user_version;
PRAGMA [database.]user_version = number;

Este é um valor inteiro assinado de 32 bits, que pode ser definido pelo desenvolvedor para fins de rastreamento de versão.

writable_schema Pragma

o writable_schemapragma obtém ou define a capacidade de modificar tabelas do sistema. A seguir está a sintaxe simples.

PRAGMA writable_schema;
PRAGMA writable_schema = [true|false];

Se este pragma for definido, as tabelas que começam com sqlite_ podem ser criadas e modificadas, incluindo a tabela sqlite_master. Tenha cuidado ao usar o pragma porque pode levar à corrupção completa do banco de dados.

As restrições são as regras aplicadas em colunas de dados na tabela. Eles são usados para limitar o tipo de dados que podem entrar em uma tabela. Isso garante a precisão e confiabilidade dos dados no banco de dados.

As restrições podem ser nível de coluna ou nível de tabela. As restrições de nível de coluna são aplicadas apenas a uma coluna, enquanto as restrições de nível de tabela são aplicadas a toda a tabela.

A seguir estão as restrições comumente usadas disponíveis no SQLite.

NOT NULL Constraint - Garante que uma coluna não possa ter valor NULL.
DEFAULT Constraint - Fornece um valor padrão para uma coluna quando nenhum é especificado.
UNIQUE Constraint - Garante que todos os valores em uma coluna sejam diferentes.
PRIMARY Key - Identifica exclusivamente cada linha / registro em uma tabela de banco de dados.
CHECK Constraint - Garante que todos os valores em uma coluna satisfaçam certas condições.

Restrição NOT NULL

Por padrão, uma coluna pode conter valores NULL. Se você não quiser que uma coluna tenha um valor NULL, então você precisa definir tal restrição nesta coluna especificando que NULL agora não é permitido para aquela coluna.

Um NULL não é o mesmo que nenhum dado; em vez disso, representa dados desconhecidos.

Exemplo

Por exemplo, a seguinte instrução SQLite cria uma nova tabela chamada COMPANY e adiciona cinco colunas, três das quais, ID e NAME e AGE, especifica para não aceitar NULLs.

CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

Restrição DEFAULT

A restrição DEFAULT fornece um valor padrão para uma coluna quando a instrução INSERT INTO não fornece um valor específico.

Exemplo

Por exemplo, a seguinte instrução SQLite cria uma nova tabela chamada COMPANY e adiciona cinco colunas. Aqui, a coluna SALARY é definida como 5000,00 por padrão, portanto, caso a instrução INSERT INTO não forneça um valor para esta coluna, então, por padrão, esta coluna seria definida como 5000,00.

CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL    DEFAULT 50000.00
);

Restrição única

A restrição UNIQUE impede que dois registros tenham valores idênticos em uma coluna específica. Na tabela COMPANY, por exemplo, você pode querer evitar que duas ou mais pessoas tenham a mesma idade.

Exemplo

Por exemplo, a seguinte instrução SQLite cria uma nova tabela chamada COMPANY e adiciona cinco colunas. Aqui, a coluna AGE é definida como UNIQUE, de modo que você não pode ter dois registros com a mesma idade -

CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL UNIQUE,
   ADDRESS        CHAR(50),
   SALARY         REAL    DEFAULT 50000.00
);

Restrição PRIMARY KEY

A restrição PRIMARY KEY identifica exclusivamente cada registro em uma tabela de banco de dados. Pode haver mais colunas UNIQUE, mas apenas uma chave primária em uma tabela. As chaves primárias são importantes ao projetar as tabelas do banco de dados. As chaves primárias são IDs exclusivos.

Nós os usamos para se referir às linhas da tabela. As chaves primárias se tornam chaves estrangeiras em outras tabelas, ao criar relações entre as tabelas. Devido a uma 'supervisão de codificação de longa data', as chaves primárias podem ser NULL no SQLite. Este não é o caso de outros bancos de dados.

Uma chave primária é um campo em uma tabela que identifica exclusivamente cada linha / registro em uma tabela de banco de dados. As chaves primárias devem conter valores exclusivos. Uma coluna de chave primária não pode ter valores NULL.

Uma tabela pode ter apenas uma chave primária, que pode consistir em um ou vários campos. Quando vários campos são usados como uma chave primária, eles são chamados decomposite key.

Se uma tabela tem uma chave primária definida em qualquer campo (s), então você não pode ter dois registros com o mesmo valor desse campo (s).

Exemplo

Você já viu vários exemplos acima, onde criamos a tabela COMPANY com ID como chave primária.

CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

Restrição CHECK

A restrição CHECK permite que uma condição verifique o valor que está sendo inserido em um registro. Se a condição for avaliada como falsa, o registro viola a restrição e não é inserido na tabela.

Exemplo

Por exemplo, o seguinte SQLite cria uma nova tabela chamada COMPANY e adiciona cinco colunas. Aqui, adicionamos um CHEQUE com a coluna SALÁRIO, de forma que você não pode ter nenhum SALÁRIO Zero.

CREATE TABLE COMPANY3(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL    CHECK(SALARY > 0)
);

Eliminando a restrição

SQLite suporta um subconjunto limitado de ALTER TABLE. O comando ALTER TABLE no SQLite permite ao usuário renomear uma tabela ou adicionar uma nova coluna a uma tabela existente. Não é possível renomear uma coluna, remover uma coluna ou adicionar ou remover restrições de uma tabela.

SQLite Joinscláusula é usada para combinar registros de duas ou mais tabelas em um banco de dados. Um JOIN é um meio de combinar campos de duas tabelas usando valores comuns a cada uma.

SQL define três tipos principais de junções -

O CROSS JOIN
O INNER JOIN
O OUTER JOIN

Antes de prosseguirmos, vamos considerar duas tabelas COMPANHIA e DEPARTAMENTO. Já vimos instruções INSERT para preencher a tabela COMPANY. Então, vamos supor que a lista de registros disponíveis na tabela COMPANY -

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

Outra tabela é DEPARTAMENTO com a seguinte definição -

CREATE TABLE DEPARTMENT(
   ID INT PRIMARY KEY      NOT NULL,
   DEPT           CHAR(50) NOT NULL,
   EMP_ID         INT      NOT NULL
);

Aqui está a lista de instruções INSERT para preencher a tabela DEPARTMENT -

INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID)
VALUES (1, 'IT Billing', 1 );

INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID)
VALUES (2, 'Engineering', 2 );

INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID)
VALUES (3, 'Finance', 7 );

Por fim, temos a seguinte lista de registros disponíveis na tabela DEPARTAMENTO -

ID          DEPT        EMP_ID
----------  ----------  ----------
1           IT Billing  1
2           Engineering 2
3           Finance     7

O CROSS JOIN

CROSS JOIN corresponde a cada linha da primeira tabela com cada linha da segunda tabela. Se as tabelas de entrada tiverem linha xey, respectivamente, a tabela resultante terá linha x * y. Como os CROSS JOINs têm o potencial de gerar tabelas extremamente grandes, deve-se tomar cuidado para usá-los apenas quando apropriado.

A seguir está a sintaxe de CROSS JOIN -

SELECT ... FROM table1 CROSS JOIN table2 ...

Com base nas tabelas acima, você pode escrever um CROSS JOIN da seguinte forma -

sqlite> SELECT EMP_ID, NAME, DEPT FROM COMPANY CROSS JOIN DEPARTMENT;

A consulta acima produzirá o seguinte resultado -

EMP_ID      NAME        DEPT
----------  ----------  ----------
1           Paul        IT Billing
2           Paul        Engineering
7           Paul        Finance
1           Allen       IT Billing
2           Allen       Engineering
7           Allen       Finance
1           Teddy       IT Billing
2           Teddy       Engineering
7           Teddy       Finance
1           Mark        IT Billing
2           Mark        Engineering
7           Mark        Finance
1           David       IT Billing
2           David       Engineering
7           David       Finance
1           Kim         IT Billing
2           Kim         Engineering
7           Kim         Finance
1           James       IT Billing
2           James       Engineering
7           James       Finance

O INNER JOIN

INNER JOIN cria uma nova tabela de resultados combinando valores de coluna de duas tabelas (tabela1 e tabela2) com base no predicado de junção. A consulta compara cada linha da tabela1 com cada linha da tabela2 para encontrar todos os pares de linhas que satisfazem o predicado de junção. Quando o predicado de junção é satisfeito, os valores da coluna para cada par de linhas correspondentes de A e B são combinados em uma linha de resultado.

Um INNER JOIN é o tipo de junção mais comum e padrão. Você pode usar a palavra-chave INNER opcionalmente.

A seguir está a sintaxe de INNER JOIN -

SELECT ... FROM table1 [INNER] JOIN table2 ON conditional_expression ...

Para evitar redundância e manter o fraseado mais curto, as condições INNER JOIN podem ser declaradas com um USINGexpressão. Esta expressão especifica uma lista de uma ou mais colunas.

SELECT ... FROM table1 JOIN table2 USING ( column1 ,... ) ...

Um NATURAL JOIN é semelhante a um JOIN...USING, apenas testa automaticamente a igualdade entre os valores de cada coluna que existe em ambas as tabelas -

SELECT ... FROM table1 NATURAL JOIN table2...

Com base nas tabelas acima, você pode escrever um INNER JOIN da seguinte forma -

sqlite> SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT
   ON COMPANY.ID = DEPARTMENT.EMP_ID;

A consulta acima produzirá o seguinte resultado -

EMP_ID      NAME        DEPT
----------  ----------  ----------
1           Paul        IT Billing
2           Allen       Engineering
7           James       Finance

O OUTER JOIN

OUTER JOIN é uma extensão de INNER JOIN. Embora o padrão SQL defina três tipos de OUTER JOINs: LEFT, RIGHT e FULL, o SQLite suporta apenas oLEFT OUTER JOIN.

OUTER JOINs têm uma condição idêntica a INNER JOINs, expressa usando uma palavra-chave ON, USING ou NATURAL. A tabela de resultados iniciais é calculada da mesma maneira. Depois que o JOIN primário é calculado, um OUTER JOIN pegará todas as linhas não unidas de uma ou de ambas as tabelas, preenchê-las com NULLs e anexá-las à tabela resultante.

A seguir está a sintaxe de LEFT OUTER JOIN -

SELECT ... FROM table1 LEFT OUTER JOIN table2 ON conditional_expression ...

Para evitar redundância e manter o fraseado mais curto, as condições OUTER JOIN podem ser declaradas com uma expressão USING. Esta expressão especifica uma lista de uma ou mais colunas.

SELECT ... FROM table1 LEFT OUTER JOIN table2 USING ( column1 ,... ) ...

Com base nas tabelas acima, você pode escrever uma junção interna da seguinte maneira -

sqlite> SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT
   ON COMPANY.ID = DEPARTMENT.EMP_ID;

A consulta acima produzirá o seguinte resultado -

EMP_ID      NAME        DEPT
----------  ----------  ----------
1           Paul        IT Billing
2           Allen       Engineering
            Teddy
            Mark
            David
            Kim
7           James       Finance

SQLite UNION cláusula / operador é usado para combinar os resultados de duas ou mais instruções SELECT sem retornar nenhuma linha duplicada.

Para usar UNION, cada SELECT deve ter o mesmo número de colunas selecionadas, o mesmo número de expressões de coluna, o mesmo tipo de dados e tê-los na mesma ordem, mas não precisam ter o mesmo comprimento.

Sintaxe

A seguir está a sintaxe básica de UNION.

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

UNION

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

Aqui, a condição fornecida pode ser qualquer expressão com base em seus requisitos.

Exemplo

Considere as duas tabelas a seguir, (a) tabela COMPANY da seguinte forma -

sqlite> select * from COMPANY;
ID          NAME                  AGE         ADDRESS     SALARY
----------  --------------------  ----------  ----------  ----------
1           Paul                  32          California  20000.0
2           Allen                 25          Texas       15000.0
3           Teddy                 23          Norway      20000.0
4           Mark                  25          Rich-Mond   65000.0
5           David                 27          Texas       85000.0
6           Kim                   22          South-Hall  45000.0
7           James                 24          Houston     10000.0

(b) Outra tabela é DEPARTAMENTO da seguinte forma -

ID          DEPT                  EMP_ID
----------  --------------------  ----------
1           IT Billing            1
2           Engineering           2
3           Finance               7
4           Engineering           3
5           Finance               4
6           Engineering           5
7           Finance               6

Agora vamos juntar essas duas tabelas usando a instrução SELECT junto com a cláusula UNION da seguinte maneira -

sqlite>  SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT
         ON COMPANY.ID = DEPARTMENT.EMP_ID
         
         UNION
         
         SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT
         ON COMPANY.ID = DEPARTMENT.EMP_ID;

Isso produzirá o seguinte resultado.

EMP_ID      NAME                  DEPT
----------  --------------------  ----------
1           Paul                  IT Billing
2           Allen                 Engineering
3           Teddy                 Engineering
4           Mark                  Finance
5           David                 Engineering
6           Kim                   Finance
7           James                 Finance

A cláusula UNION ALL

O operador UNION ALL é usado para combinar os resultados de duas instruções SELECT incluindo linhas duplicadas.

As mesmas regras que se aplicam ao UNION também se aplicam ao operador UNION ALL.

Sintaxe

A seguir está a sintaxe básica de UNION ALL.

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

UNION ALL

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

Aqui, a condição fornecida pode ser qualquer expressão com base em seus requisitos.

Exemplo

Agora, vamos juntar as duas tabelas mencionadas acima em nossa instrução SELECT da seguinte maneira -

sqlite>  SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT
         ON COMPANY.ID = DEPARTMENT.EMP_ID
         
         UNION ALL

         SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT
         ON COMPANY.ID = DEPARTMENT.EMP_ID;

Isso produzirá o seguinte resultado.

EMP_ID      NAME                  DEPT
----------  --------------------  ----------
1           Paul                  IT Billing
2           Allen                 Engineering
3           Teddy                 Engineering
4           Mark                  Finance
5           David                 Engineering
6           Kim                   Finance
7           James                 Finance
1           Paul                  IT Billing
2           Allen                 Engineering
3           Teddy                 Engineering
4           Mark                  Finance
5           David                 Engineering
6           Kim                   Finance
7           James                 Finance

SQLite NULLé o termo usado para representar um valor ausente. Um valor NULL em uma tabela é um valor em um campo que parece estar em branco.

Um campo com valor NULL é um campo sem valor. É muito importante entender que um valor NULL é diferente de um valor zero ou de um campo que contém espaços.

Sintaxe

A seguir está a sintaxe básica de uso NULL ao criar uma mesa.

SQLite> CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

Aqui, NOT NULLsignifica que a coluna deve sempre aceitar um valor explícito do tipo de dados fornecido. Existem duas colunas onde não usamos NOT NULL, o que significa que essas colunas podem ser NULL.

Um campo com valor NULL é aquele que foi deixado em branco durante a criação do registro.

Exemplo

O valor NULL pode causar problemas na seleção dos dados, pois ao comparar um valor desconhecido com qualquer outro valor, o resultado é sempre desconhecido e não é incluído no resultado final. Considere a seguinte tabela, EMPRESA com os seguintes registros -

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

Vamos usar a instrução UPDATE para definir alguns valores anuláveis como NULL da seguinte forma -

sqlite> UPDATE COMPANY SET ADDRESS = NULL, SALARY = NULL where ID IN(6,7);

Agora, a tabela COMPANY terá os seguintes registros.

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22
7           James       24

A seguir, vamos ver o uso de IS NOT NULL operador para listar todos os registros onde SALARY não é NULL.

sqlite> SELECT  ID, NAME, AGE, ADDRESS, SALARY
        FROM COMPANY
        WHERE SALARY IS NOT NULL;

A instrução SQLite acima produzirá o seguinte resultado -

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0

A seguir está o uso de IS NULL operador, que listará todos os registros em que SALARY é NULL.

sqlite> SELECT  ID, NAME, AGE, ADDRESS, SALARY
        FROM COMPANY
        WHERE SALARY IS NULL;

A instrução SQLite acima produzirá o seguinte resultado.

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
6           Kim         22
7           James       24

Você pode renomear uma tabela ou coluna temporariamente, dando outro nome, que é conhecido como ALIAS. O uso de apelidos de tabela significa renomear uma tabela em uma instrução SQLite particular. A renomeação é uma mudança temporária e o nome real da tabela não muda no banco de dados.

Os apelidos de coluna são usados para renomear as colunas de uma tabela com o propósito de uma consulta SQLite específica.

Sintaxe

A seguir está a sintaxe básica de table alias.

SELECT column1, column2....
FROM table_name AS alias_name
WHERE [condition];

A seguir está a sintaxe básica de column alias.

SELECT column_name AS alias_name
FROM table_name
WHERE [condition];

Exemplo

Considere as duas tabelas a seguir, (a) a tabela COMPANY é a seguinte -

sqlite> select * from COMPANY;
ID          NAME                  AGE         ADDRESS     SALARY
----------  --------------------  ----------  ----------  ----------
1           Paul                  32          California  20000.0
2           Allen                 25          Texas       15000.0
3           Teddy                 23          Norway      20000.0
4           Mark                  25          Rich-Mond   65000.0
5           David                 27          Texas       85000.0
6           Kim                   22          South-Hall  45000.0
7           James                 24          Houston     10000.0

(b) Outra tabela é DEPARTAMENTO da seguinte forma -

ID          DEPT                  EMP_ID
----------  --------------------  ----------
1           IT Billing            1
2           Engineering           2
3           Finance               7
4           Engineering           3
5           Finance               4
6           Engineering           5
7           Finance               6

Agora, a seguir está o uso de TABLE ALIAS onde usamos C e D como aliases para as tabelas COMPANY e DEPARTMENT, respectivamente -

sqlite> SELECT C.ID, C.NAME, C.AGE, D.DEPT
        FROM COMPANY AS C, DEPARTMENT AS D
        WHERE  C.ID = D.EMP_ID;

A instrução SQLite acima produzirá o seguinte resultado -

ID          NAME        AGE         DEPT
----------  ----------  ----------  ----------
1           Paul        32          IT Billing
2           Allen       25          Engineering
3           Teddy       23          Engineering
4           Mark        25          Finance
5           David       27          Engineering
6           Kim         22          Finance
7           James       24          Finance

Considere um exemplo para o uso de COLUMN ALIAS onde COMPANY_ID é um alias da coluna ID e COMPANY_NAME é um alias da coluna de nome.

sqlite> SELECT C.ID AS COMPANY_ID, C.NAME AS COMPANY_NAME, C.AGE, D.DEPT
        FROM COMPANY AS C, DEPARTMENT AS D
        WHERE  C.ID = D.EMP_ID;

A instrução SQLite acima produzirá o seguinte resultado -

COMPANY_ID  COMPANY_NAME  AGE         DEPT
----------  ------------  ----------  ----------
1           Paul          32          IT Billing
2           Allen         25          Engineering
3           Teddy         23          Engineering
4           Mark          25          Finance
5           David         27          Engineering
6           Kim           22          Finance
7           James         24          Finance

SQLite Triggerssão funções de retorno de chamada de banco de dados, que são executadas / chamadas automaticamente quando ocorre um evento de banco de dados especificado. A seguir estão os pontos importantes sobre gatilhos SQLite -

O gatilho SQLite pode ser especificado para disparar sempre que um DELETE, INSERT ou UPDATE de uma determinada tabela de banco de dados ocorrer ou sempre que ocorrer um UPDATE em uma ou mais colunas especificadas de uma tabela.
No momento, o SQLite suporta apenas gatilhos FOR EACH ROW, não gatilhos FOR EACH STATEMENT. Portanto, especificar explicitamente FOR EACH ROW é opcional.
Tanto a cláusula WHEN quanto as ações de gatilho podem acessar elementos da linha que está sendo inserida, excluída ou atualizada usando referências do formulário NEW.column-name e OLD.column-name, em que nome-da-coluna é o nome de uma coluna da tabela à qual o acionador está associado.
Se uma cláusula WHEN for fornecida, as instruções SQL especificadas serão executadas apenas para linhas para as quais a cláusula WHEN é verdadeira. Se nenhuma cláusula WHEN for fornecida, as instruções SQL serão executadas para todas as linhas.
A palavra-chave BEFORE ou AFTER determina quando as ações do gatilho serão executadas em relação à inserção, modificação ou remoção da linha associada.
Os gatilhos são eliminados automaticamente quando a tabela à qual estão associados é eliminada.
A tabela a ser modificada deve existir no mesmo banco de dados que a tabela ou visão à qual o gatilho está anexado e deve-se usar apenas tablename não database.tablename.
Uma função SQL especial RAISE () pode ser usada em um programa disparador para gerar uma exceção.

Sintaxe

A seguir está a sintaxe básica para criar um trigger.

CREATE TRIGGER trigger_name [BEFORE|AFTER] event_name 
ON table_name
BEGIN
 -- Trigger logic goes here....
END;

Aqui, event_namepode ser a operação de banco de dados INSERT, DELETE e UPDATE na tabela mencionadatable_name. Você pode opcionalmente especificar FOR EACH ROW após o nome da tabela.

A seguir está a sintaxe para a criação de um gatilho em uma operação UPDATE em uma ou mais colunas especificadas de uma tabela.

CREATE TRIGGER trigger_name [BEFORE|AFTER] UPDATE OF column_name 
ON table_name
BEGIN
   -- Trigger logic goes here....
END;

Exemplo

Vamos considerar um caso em que queremos manter um teste de auditoria para cada registro inserido na tabela COMPANY, que criamos da seguinte forma (Abandone a tabela COMPANY se você já a tiver).

sqlite> CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

Para dar continuidade ao teste de auditoria, iremos criar uma nova tabela denominada AUDIT onde serão inseridas as mensagens de log, sempre que houver uma entrada na tabela COMPANY para um novo registo.

sqlite> CREATE TABLE AUDIT(
   EMP_ID INT NOT NULL,
   ENTRY_DATE TEXT NOT NULL
);

Aqui, ID é o ID do registro AUDIT, e EMP_ID é o ID que virá da tabela COMPANY e DATE manterá o carimbo de data / hora quando o registro será criado na tabela COMPANY. Agora vamos criar um gatilho na tabela COMPANY da seguinte forma -

sqlite> CREATE TRIGGER audit_log AFTER INSERT 
ON COMPANY
BEGIN
   INSERT INTO AUDIT(EMP_ID, ENTRY_DATE) VALUES (new.ID, datetime('now'));
END;

Agora, vamos começar o trabalho real, vamos começar a inserir o registro na tabela COMPANY que deve resultar na criação de um registro de log de auditoria na tabela AUDIT. Crie um registro na tabela COMPANY da seguinte forma -

sqlite> INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Paul', 32, 'California', 20000.00 );

Isso criará um registro na tabela COMPANY, que é a seguinte -

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0

Ao mesmo tempo, um registro será criado na tabela AUDIT. Este registro é o resultado de uma trigger, que criamos na operação INSERT na tabela COMPANY. Da mesma forma, você pode criar seus gatilhos em operações UPDATE e DELETE com base em seus requisitos.

EMP_ID      ENTRY_DATE
----------  -------------------
1           2013-04-05 06:26:00

Listagem de gatilhos

Você pode listar todos os gatilhos de sqlite_master tabela da seguinte forma -

sqlite> SELECT name FROM sqlite_master
WHERE type = 'trigger';

A instrução SQLite acima listará apenas uma entrada da seguinte forma -

name
----------
audit_log

Se você quiser listar os gatilhos em uma tabela específica, use a cláusula AND com o nome da tabela da seguinte maneira -

sqlite> SELECT name FROM sqlite_master
WHERE type = 'trigger' AND tbl_name = 'COMPANY';

A instrução SQLite acima também listará apenas uma entrada da seguinte forma -

name
----------
audit_log

Soltar gatilhos

A seguir está o comando DROP, que pode ser usado para descartar um gatilho existente.

sqlite> DROP TRIGGER trigger_name;

Os índices são tabelas de pesquisa especiais que o mecanismo de pesquisa do banco de dados pode usar para acelerar a recuperação de dados. Simplificando, umindexé um ponteiro para dados em uma tabela. Um índice em um banco de dados é muito semelhante a um índice no final de um livro.

Por exemplo, se você quiser fazer referência a todas as páginas de um livro que discute um determinado tópico, primeiro consulte o índice, que lista todos os tópicos em ordem alfabética e, em seguida, é referido a um ou mais números de página específicos.

Um índice ajuda a acelerar as consultas SELECT e cláusulas WHERE, mas retarda a entrada de dados, com instruções UPDATE e INSERT. Os índices podem ser criados ou eliminados sem nenhum efeito nos dados.

A criação de um índice envolve a instrução CREATE INDEX, que permite nomear o índice, especificar a tabela e qual coluna ou colunas indexar e indicar se o índice está em ordem crescente ou decrescente.

Os índices também podem ser exclusivos, semelhantes à restrição UNIQUE, no sentido de que o índice evita entradas duplicadas na coluna ou combinação de colunas nas quais há um índice.

O comando CREATE INDEX

A seguir está a sintaxe básica de CREATE INDEX.

CREATE INDEX index_name ON table_name;

Índices de coluna única

Um índice de coluna única é aquele criado com base em apenas uma coluna da tabela. A sintaxe básica é a seguinte -

CREATE INDEX index_name
ON table_name (column_name);

Índices Únicos

Índices exclusivos são usados não apenas para desempenho, mas também para integridade de dados. Um índice exclusivo não permite que nenhum valor duplicado seja inserido na tabela. A sintaxe básica é a seguinte -

CREATE UNIQUE INDEX index_name
on table_name (column_name);

Índices Compostos

Um índice composto é um índice em duas ou mais colunas de uma tabela. A sintaxe básica é a seguinte -

CREATE INDEX index_name
on table_name (column1, column2);

Seja para criar um índice de coluna única ou um índice composto, leve em consideração a (s) coluna (s) que você pode usar com muita frequência na cláusula WHERE de uma consulta como condições de filtro.

Se houver apenas uma coluna usada, um índice de coluna única deve ser a escolha. Se houver duas ou mais colunas que são freqüentemente usadas na cláusula WHERE como filtros, o índice composto seria a melhor escolha.

Índices implícitos

Índices implícitos são índices criados automaticamente pelo servidor de banco de dados quando um objeto é criado. Os índices são criados automaticamente para restrições de chave primária e restrições exclusivas.

Example

A seguir está um exemplo em que criaremos um índice na tabela COMPANY para a coluna de salários -

sqlite> CREATE INDEX salary_index ON COMPANY (salary);

Agora, vamos listar todos os índices disponíveis na tabela COMPANY usando .indices comando da seguinte forma -

sqlite> .indices COMPANY

Isso produzirá o seguinte resultado, onde sqlite_autoindex_COMPANY_1 é um índice implícito que foi criado quando a própria tabela foi criada.

salary_index
sqlite_autoindex_COMPANY_1

Você pode listar todos os índices do banco de dados da seguinte forma -

sqlite> SELECT * FROM sqlite_master WHERE type = 'index';

O comando DROP INDEX

Um índice pode ser eliminado usando SQLite DROPcomando. Deve-se ter cuidado ao descartar um índice, pois o desempenho pode ser reduzido ou melhorado.

A seguir, a sintaxe básica é a seguinte -

DROP INDEX index_name;

Você pode usar a seguinte instrução para excluir o índice criado anteriormente.

sqlite> DROP INDEX salary_index;

Quando os índices devem ser evitados?

Embora os índices tenham como objetivo aprimorar o desempenho de um banco de dados, há momentos em que eles devem ser evitados. As diretrizes a seguir indicam quando o uso de um índice deve ser reconsiderado.

Os índices não devem ser usados em -

Mesas pequenas.
Tabelas que possuem operações de inserção ou atualização de lote frequentes e grandes.
Colunas que contêm um grande número de valores NULL.
Colunas que são frequentemente manipuladas.

A cláusula "INDEXED BY index-name" especifica que o índice nomeado deve ser usado para pesquisar os valores na tabela anterior.

Se index-name não existir ou não puder ser usado para a consulta, a preparação da instrução SQLite falhará.

A cláusula "NOT INDEXED" especifica que nenhum índice deve ser usado ao acessar a tabela anterior, incluindo índices implícitos criados pelas restrições UNIQUE e PRIMARY KEY.

No entanto, a INTEGER PRIMARY KEY ainda pode ser usada para pesquisar entradas, mesmo quando "NOT INDEXED" é especificado.

Sintaxe

A seguir está a sintaxe para a cláusula INDEXED BY e ela pode ser usada com a instrução DELETE, UPDATE ou SELECT.

SELECT|DELETE|UPDATE column1, column2...
INDEXED BY (index_name)
table_name
WHERE (CONDITION);

Exemplo

Considere a tabela COMPANY Vamos criar um índice e usá-lo para realizar a operação INDEXED BY.

sqlite> CREATE INDEX salary_index ON COMPANY(salary);
sqlite>

Agora, selecionando os dados da tabela COMPANY, você pode usar a cláusula INDEXED BY da seguinte forma -

sqlite> SELECT * FROM COMPANY INDEXED BY salary_index WHERE salary > 5000;

Isso produzirá o seguinte resultado.

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
7           James       24          Houston     10000.0
2           Allen       25          Texas       15000.0
1           Paul        32          California  20000.0
3           Teddy       23          Norway      20000.0
6           Kim         22          South-Hall  45000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0

SQLite ALTER TABLEcomando modifica uma tabela existente sem executar um despejo completo e recarregar os dados. Você pode renomear uma tabela usando a instrução ALTER TABLE e colunas adicionais podem ser adicionadas em uma tabela existente usando a instrução ALTER TABLE.

Não há nenhuma outra operação suportada pelo comando ALTER TABLE no SQLite, exceto renomear uma tabela e adicionar uma coluna em uma tabela existente.

Sintaxe

A seguir está a sintaxe básica de ALTER TABLE para RENOMEAR uma tabela existente.

ALTER TABLE database_name.table_name RENAME TO new_table_name;

A seguir está a sintaxe básica de ALTER TABLE para adicionar uma nova coluna em uma tabela existente.

ALTER TABLE database_name.table_name ADD COLUMN column_def...;

Exemplo

Considere a tabela COMPANY com os seguintes registros -

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

Agora, vamos tentar renomear esta tabela usando a instrução ALTER TABLE da seguinte maneira -

sqlite> ALTER TABLE COMPANY RENAME TO OLD_COMPANY;

A instrução SQLite acima renomeará a tabela COMPANY para OLD_COMPANY. Agora, vamos tentar adicionar uma nova coluna na tabela OLD_COMPANY da seguinte forma -

sqlite> ALTER TABLE OLD_COMPANY ADD COLUMN SEX char(1);

A tabela COMPANY foi alterada e, em seguida, será a saída da instrução SELECT.

ID          NAME        AGE         ADDRESS     SALARY      SEX
----------  ----------  ----------  ----------  ----------  ---
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

Deve-se observar que a coluna recém-adicionada é preenchida com valores NULL.

Infelizmente, não temos o comando TRUNCATE TABLE no SQLite, mas você pode usar o SQLite DELETE comando para excluir dados completos de uma tabela existente, embora seja recomendado usar o comando DROP TABLE para eliminar a tabela completa e recriá-la mais uma vez.

Sintaxe

A seguir está a sintaxe básica do comando DELETE.

sqlite> DELETE FROM table_name;

A seguir está a sintaxe básica de DROP TABLE.

sqlite> DROP TABLE table_name;

Se você estiver usando o comando DELETE TABLE para excluir todos os registros, é recomendado usar VACUUM comando para limpar o espaço não utilizado.

Exemplo

Considere a tabela COMPANY com os seguintes registros.

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

A seguir está o exemplo para truncar a tabela acima -

SQLite> DELETE FROM COMPANY;
SQLite> VACUUM;

Agora, a tabela COMPANY está completamente truncada e nada será a saída da instrução SELECT.

Uma visão nada mais é do que uma instrução SQLite que é armazenada no banco de dados com um nome associado. Na verdade, é uma composição de uma tabela na forma de uma consulta SQLite predefinida.

Uma visualização pode conter todas as linhas de uma tabela ou linhas selecionadas de uma ou mais tabelas. Uma visão pode ser criada a partir de uma ou mais tabelas que dependem da consulta SQLite escrita para criar uma visão.

Visualizações, que são uma espécie de tabelas virtuais, permitem que os usuários -

Estruture os dados de uma maneira que os usuários ou classes de usuários considerem natural ou intuitiva.
Restrinja o acesso aos dados de forma que um usuário só possa ver dados limitados em vez de uma tabela completa.
Resuma os dados de várias tabelas, que podem ser usadas para gerar relatórios.

As visualizações SQLite são somente leitura e, portanto, você pode não ser capaz de executar uma instrução DELETE, INSERT ou UPDATE em uma visualização. No entanto, você pode criar um gatilho em uma visão que dispara na tentativa de EXCLUIR, INSERIR ou ATUALIZAR uma visão e fazer o que for necessário no corpo do gatilho.

Criando Vistas

Visualizações SQLite são criadas usando o CREATE VIEWdeclaração. As visualizações SQLite podem ser criadas a partir de uma única tabela, várias tabelas ou outra visualização.

A seguir está a sintaxe básica de CREATE VIEW.

CREATE [TEMP | TEMPORARY] VIEW view_name AS
SELECT column1, column2.....
FROM table_name
WHERE [condition];

Você pode incluir várias tabelas em sua instrução SELECT de maneira semelhante à usada em uma consulta SQL SELECT normal. Se a palavra-chave TEMP ou TEMPORARY opcional estiver presente, a visualização será criada no banco de dados temporário.

Exemplo

Considere a tabela COMPANY com os seguintes registros -

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

A seguir está um exemplo para criar uma visão da tabela COMPANY. Esta visão será usada para ter apenas algumas colunas da tabela COMPANY.

sqlite> CREATE VIEW COMPANY_VIEW AS
SELECT ID, NAME, AGE
FROM  COMPANY;

Agora você pode consultar COMPANY_VIEW de maneira semelhante à de uma tabela real. A seguir está um exemplo -

sqlite> SELECT * FROM COMPANY_VIEW;

Isso produzirá o seguinte resultado.

ID          NAME        AGE
----------  ----------  ----------
1           Paul        32
2           Allen       25
3           Teddy       23
4           Mark        25
5           David       27
6           Kim         22
7           James       24

Queda de visualizações

Para eliminar uma visão, basta usar a instrução DROP VIEW com o view_name. A sintaxe básica de DROP VIEW é a seguinte -

sqlite> DROP VIEW view_name;

O comando a seguir excluirá a visualização COMPANY_VIEW, que criamos na última seção.

sqlite> DROP VIEW COMPANY_VIEW;

Uma transação é uma unidade de trabalho executada em um banco de dados. As transações são unidades ou sequências de trabalho realizadas em uma ordem lógica, seja de forma manual por um usuário ou automaticamente por algum tipo de programa de banco de dados.

Uma transação é a propagação de uma ou mais alterações no banco de dados. Por exemplo, se você estiver criando, atualizando ou excluindo um registro da tabela, estará executando uma transação na tabela. É importante controlar as transações para garantir a integridade dos dados e lidar com os erros do banco de dados.

Praticamente, você agrupará muitas consultas SQLite em um grupo e executará todas elas juntas como parte de uma transação.

Propriedades das transações

As transações têm as quatro propriedades padrão a seguir, geralmente conhecidas pela sigla ACID.

Atomicity- Garante que todas as operações dentro da unidade de trabalho sejam concluídas com sucesso; caso contrário, a transação é abortada no ponto de falha e as operações anteriores são revertidas para seu estado anterior.
Consistency - Garante que o banco de dados mude de estado corretamente após uma transação confirmada com sucesso.
Isolation - Permite que as transações operem de forma independente e transparente entre si.
Durability - Garante que o resultado ou efeito de uma transação confirmada persiste em caso de falha do sistema.

Controle de transações

A seguir estão os seguintes comandos usados para controlar transações:

BEGIN TRANSACTION - Para iniciar uma transação.
COMMIT - Para salvar as alterações, como alternativa, você pode usar END TRANSACTION comando.
ROLLBACK - Para reverter as alterações.

Os comandos de controle transacional são usados apenas com os comandos DML INSERT, UPDATE e DELETE. Eles não podem ser usados ao criar tabelas ou eliminá-los porque essas operações são confirmadas automaticamente no banco de dados.

Comando BEGIN TRANSACTION

As transações podem ser iniciadas usando BEGIN TRANSACTION ou simplesmente o comando BEGIN. Essas transações geralmente persistem até que o próximo comando COMMIT ou ROLLBACK seja encontrado. No entanto, uma transação também será ROLLBACK se o banco de dados for fechado ou se ocorrer um erro. A seguir está a sintaxe simples para iniciar uma transação.

BEGIN;
or 
BEGIN TRANSACTION;

Comando COMMIT

O comando COMMIT é o comando transacional usado para salvar as alterações invocadas por uma transação no banco de dados.

O comando COMMIT salva todas as transações no banco de dados desde o último comando COMMIT ou ROLLBACK.

A seguir está a sintaxe do comando COMMIT.

COMMIT;
or
END TRANSACTION;

Comando ROLLBACK

O comando ROLLBACK é o comando transacional usado para desfazer transações que ainda não foram salvas no banco de dados.

O comando ROLLBACK só pode ser usado para desfazer transações desde que o último comando COMMIT ou ROLLBACK foi emitido.

A seguir está a sintaxe do comando ROLLBACK.

ROLLBACK;

Example

Considere a tabela COMPANY com os seguintes registros.

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

Agora, vamos iniciar uma transação e excluir os registros da tabela com idade = 25. Então, use o comando ROLLBACK para desfazer todas as alterações.

sqlite> BEGIN;
sqlite> DELETE FROM COMPANY WHERE AGE = 25;
sqlite> ROLLBACK;

Agora, se você verificar a tabela COMPANY, ela ainda terá os seguintes registros -

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

Vamos iniciar outra transação e deletar os registros da tabela com idade = 25 e, finalmente, usar o comando COMMIT para confirmar todas as alterações.

sqlite> BEGIN;
sqlite> DELETE FROM COMPANY WHERE AGE = 25;
sqlite> COMMIT;

Se você verificar agora a tabela COMPANY ainda tem os seguintes registros -

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
3           Teddy       23          Norway      20000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

Uma consulta Subconsulta, Interna ou Aninhada é uma consulta dentro de outra consulta SQLite e incorporada na cláusula WHERE.

Uma subconsulta é usada para retornar dados que serão usados na consulta principal como uma condição para restringir ainda mais os dados a serem recuperados.

As subconsultas podem ser usadas com as instruções SELECT, INSERT, UPDATE e DELETE junto com os operadores como =, <,>,> =, <=, IN, BETWEEN, etc.

Existem algumas regras que as subconsultas devem seguir -

As subconsultas devem ser colocadas entre parênteses.
Uma subconsulta pode ter apenas uma coluna na cláusula SELECT, a menos que várias colunas estejam na consulta principal da subconsulta para comparar suas colunas selecionadas.
Um ORDER BY não pode ser usado em uma subconsulta, embora a consulta principal possa usar um ORDER BY. O GROUP BY pode ser usado para executar a mesma função que o ORDER BY em uma subconsulta.
As subconsultas que retornam mais de uma linha só podem ser usadas com vários operadores de valor, como o operador IN.
O operador BETWEEN não pode ser usado com uma subconsulta; no entanto, BETWEEN pode ser usado na subconsulta.

Subconsultas com instrução SELECT

As subconsultas são usadas com mais freqüência com a instrução SELECT. A sintaxe básica é a seguinte -

SELECT column_name [, column_name ]
FROM table1 [, table2 ]
WHERE column_name OPERATOR
   (SELECT column_name [, column_name ]
      FROM table1 [, table2 ]
      [WHERE])

Exemplo

Considere a tabela COMPANY com os seguintes registros.

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

Agora, vamos verificar a seguinte subconsulta com a instrução SELECT.

sqlite> SELECT * 
   FROM COMPANY 
   WHERE ID IN (SELECT ID 
      FROM COMPANY 
      WHERE SALARY > 45000) ;

Isso produzirá o seguinte resultado.

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0

Subconsultas com instrução INSERT

As subconsultas também podem ser usadas com instruções INSERT. A instrução INSERT usa os dados retornados da subconsulta para inserir em outra tabela. Os dados selecionados na subconsulta podem ser modificados com qualquer uma das funções de caractere, data ou número.

A seguir, a sintaxe básica é a seguinte -

INSERT INTO table_name [ (column1 [, column2 ]) ]
   SELECT [ *|column1 [, column2 ]
   FROM table1 [, table2 ]
   [ WHERE VALUE OPERATOR ]

Exemplo

Considere uma tabela COMPANY_BKP com estrutura semelhante à tabela COMPANY e pode ser criada usando o mesmo CREATE TABLE usando COMPANY_BKP como o nome da tabela. Para copiar a tabela COMPANHIA completa em COMPANY_BKP, a seguir está a sintaxe -

sqlite> INSERT INTO COMPANY_BKP
   SELECT * FROM COMPANY 
   WHERE ID IN (SELECT ID 
      FROM COMPANY) ;

Subconsultas com instrução UPDATE

A subconsulta pode ser usada em conjunto com a instrução UPDATE. Uma única coluna ou várias colunas em uma tabela podem ser atualizadas ao usar uma subconsulta com a instrução UPDATE.

A seguir, a sintaxe básica é a seguinte -

UPDATE table
SET column_name = new_value
[ WHERE OPERATOR [ VALUE ]
   (SELECT COLUMN_NAME
      FROM TABLE_NAME)
   [ WHERE) ]

Exemplo

Assumindo que temos a tabela COMPANY_BKP disponível, que é um backup da tabela COMPANY.

O exemplo a seguir atualiza SALÁRIO em 0,50 vezes na tabela EMPRESA para todos os clientes, cuja IDADE é maior ou igual a 27.

sqlite> UPDATE COMPANY
   SET SALARY = SALARY * 0.50
   WHERE AGE IN (SELECT AGE FROM COMPANY_BKP
      WHERE AGE >= 27 );

Isso afetaria duas linhas e, finalmente, a tabela COMPANY teria os seguintes registros -

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  10000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       42500.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

Subconsultas com instrução DELETE

A subconsulta pode ser usada em conjunto com a instrução DELETE como com qualquer outra instrução mencionada acima.

A seguir, a sintaxe básica é a seguinte -

DELETE FROM TABLE_NAME
[ WHERE OPERATOR [ VALUE ]
   (SELECT COLUMN_NAME
      FROM TABLE_NAME)
   [ WHERE) ]

Exemplo

Assumindo que temos a tabela COMPANY_BKP disponível, que é um backup da tabela COMPANY.

O exemplo a seguir exclui registros da tabela COMPANY para todos os clientes cuja IDADE é maior ou igual a 27.

sqlite> DELETE FROM COMPANY
   WHERE AGE IN (SELECT AGE FROM COMPANY_BKP
   WHERE AGE > 27 );

Isso afetará duas linhas e, finalmente, a tabela COMPANY terá os seguintes registros -

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       42500.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

SQLite AUTOINCREMENTé uma palavra-chave usada para incrementar automaticamente um valor de um campo na tabela. Podemos incrementar automaticamente um valor de campo usandoAUTOINCREMENT palavra-chave ao criar uma tabela com nome de coluna específico para incremento automático.

A palavra-chave AUTOINCREMENT pode ser usado apenas com o campo INTEGER.

Sintaxe

O uso básico de AUTOINCREMENT palavra-chave é a seguinte -

CREATE TABLE table_name(
   column1 INTEGER AUTOINCREMENT,
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
);

Exemplo

Considere a tabela COMPANY a ser criada da seguinte forma -

sqlite> CREATE TABLE COMPANY(
   ID INTEGER PRIMARY KEY AUTOINCREMENT,
   NAME           TEXT      NOT NULL,
   AGE            INT       NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

Agora, insira os seguintes registros na tabela COMPANY -

INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ( 'Paul', 32, 'California', 20000.00 );

INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ('Allen', 25, 'Texas', 15000.00 );

INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ('Teddy', 23, 'Norway', 20000.00 );

INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ( 'Mark', 25, 'Rich-Mond ', 65000.00 );

INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ( 'David', 27, 'Texas', 85000.00 );

INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ( 'Kim', 22, 'South-Hall', 45000.00 );

INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ( 'James', 24, 'Houston', 10000.00 );

Isso irá inserir 7 tuplas na tabela EMPRESA e EMPRESA terá os seguintes registros -

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

Se você pegar a entrada do usuário por meio de uma página da Web e inseri-la em um banco de dados SQLite, há uma chance de você ter se deixado vulnerável a um problema de segurança conhecido como SQL Injection. Neste capítulo, você aprenderá como ajudar a evitar que isso aconteça e ajudá-lo a proteger seus scripts e instruções SQLite.

A injeção geralmente ocorre quando você pede a entrada de um usuário, como seu nome, e em vez de um nome eles fornecem uma instrução SQLite que você executará inadvertidamente em seu banco de dados.

Nunca confie nos dados fornecidos pelo usuário, processe esses dados somente após a validação; como regra, isso é feito por correspondência de padrões. No exemplo a seguir, o nome de usuário é restrito a caracteres alfanuméricos mais sublinhados e a um comprimento entre 8 e 20 caracteres - modifique essas regras conforme necessário.

if (preg_match("/^\w{8,20}$/", $_GET['username'], $matches)){ $db = new SQLiteDatabase('filename');
   $result = @$db->query("SELECT * FROM users WHERE username = $matches[0]");
} else {
   echo "username not accepted";
}

Para demonstrar o problema, considere este trecho -

$name = "Qadir'; DELETE FROM users;";
@$db->query("SELECT * FROM users WHERE username = '{$name}'");

A chamada de função deve recuperar um registro da tabela de usuários onde a coluna de nome corresponde ao nome especificado pelo usuário. Sob circunstâncias normais,$nameconteria apenas caracteres alfanuméricos e talvez espaços, como a string ilia. No entanto, neste caso, ao anexar uma consulta inteiramente nova a $ name, a chamada ao banco de dados se transforma em um desastre: a consulta DELETE injetada remove todos os registros dos usuários.

Existem interfaces de bancos de dados que não permitem o empilhamento de consultas ou a execução de várias consultas em uma única chamada de função. Se você tentar empilhar consultas, a chamada falhará, mas SQLite e PostgreSQL, felizmente realizam consultas empilhadas, executando todas as consultas fornecidas em uma string e criando um sério problema de segurança.

Prevenção de injeção de SQL

Você pode lidar com todos os caracteres de escape de forma inteligente em linguagens de script como PERL e PHP. Linguagem de programação PHP fornece a funçãostring sqlite_escape_string() para escapar de caracteres de entrada que são especiais para SQLite.

if (get_magic_quotes_gpc()) {
   $name = sqlite_escape_string($name);
}
$result = @$db->query("SELECT * FROM users WHERE username = '{$name}'");

Embora a codificação torne seguro inserir os dados, ela renderizará comparações de texto simples e LIKE cláusulas em suas consultas inutilizáveis para as colunas que contêm os dados binários.

Note - addslashes()NÃO deve ser usado para citar suas strings para consultas SQLite; isso levará a resultados estranhos ao recuperar seus dados.

A instrução SQLite pode ser precedida pela palavra-chave "EXPLAIN" ou pela frase "EXPLAIN QUERY PLAN" usada para descrever os detalhes de uma tabela.

Qualquer modificação faz com que a instrução SQLite se comporte como uma consulta e retorne informações sobre como a instrução SQLite teria operado se a palavra-chave ou frase EXPLAIN tivesse sido omitida.

A saída de EXPLAIN e EXPLAIN QUERY PLAN destina-se apenas à análise interativa e solução de problemas.
Os detalhes do formato de saída estão sujeitos a alterações de uma versão do SQLite para a próxima.
Os aplicativos não devem usar EXPLAIN ou EXPLAIN QUERY PLAN, pois seu comportamento exato é variável e apenas parcialmente documentado.

Sintaxe

sintaxe para EXPLAIN é o seguinte -

EXPLAIN [SQLite Query]

sintaxe para EXPLAIN QUERY PLAN é o seguinte -

EXPLAIN  QUERY PLAN [SQLite Query]

Exemplo

Considere a tabela COMPANY com os seguintes registros -

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

Agora, vamos verificar a seguinte subconsulta com a instrução SELECT -

sqlite> EXPLAIN SELECT * FROM COMPANY WHERE Salary >= 20000;

Isso produzirá o seguinte resultado.

addr        opcode      p1          p2          p3
----------  ----------  ----------  ----------  ----------
0           Goto        0           19
1           Integer     0           0
2           OpenRead    0           8
3           SetNumColu  0           5
4           Rewind      0           17
5           Column      0           4
6           RealAffini  0           0
7           Integer     20000       0
8           Lt          357         16          collseq(BI
9           Rowid       0           0
10          Column      0           1
11          Column      0           2
12          Column      0           3
13          Column      0           4
14          RealAffini  0           0
15          Callback    5           0
16          Next        0           5
17          Close       0           0
18          Halt        0           0
19          Transactio  0           0
20          VerifyCook  0           38
21          Goto        0           1
22          Noop        0           0

Agora, vamos verificar o seguinte Explain Query Plan com instrução SELECT -

SQLite> EXPLAIN QUERY PLAN SELECT * FROM COMPANY WHERE Salary >= 20000;

order       from        detail
----------  ----------  -------------
0           0           TABLE COMPANY

O comando VACUUM limpa o banco de dados principal copiando seu conteúdo em um arquivo de banco de dados temporário e recarregando o arquivo de banco de dados original da cópia. Isso elimina páginas livres, alinha os dados da tabela para serem contíguos e, de outra forma, limpa a estrutura do arquivo de banco de dados.

O comando VACUUM pode alterar o ROWID das entradas nas tabelas que não possuem uma INTEGER PRIMARY KEY explícita. O comando VACUUM funciona apenas no banco de dados principal. Não é possível limpar um arquivo de banco de dados anexado ao VACUUM.

O comando VACUUM falhará se houver uma transação ativa. O comando VACUUM é autônomo para bancos de dados na memória. Como o comando VACUUM reconstrói o arquivo de banco de dados do zero, o VACUUM também pode ser usado para modificar muitos parâmetros de configuração específicos do banco de dados.

VÁCUO manual

A seguir está uma sintaxe simples para emitir um comando VACUUM para todo o banco de dados a partir do prompt de comando -

$sqlite3 database_name "VACUUM;"

Você pode executar o VACUUM no prompt do SQLite, assim como a seguir -

sqlite> VACUUM;

Você também pode executar o VACUUM em uma tabela específica da seguinte maneira -

sqlite> VACUUM table_name;

Auto-VACCUM

SQLite Auto-VACUUM não faz o mesmo que VACUUM, em vez disso, apenas move as páginas livres para o final do banco de dados, reduzindo assim o tamanho do banco de dados. Ao fazer isso, ele pode fragmentar significativamente o banco de dados, enquanto o VACUUM garante a desfragmentação. Portanto, Auto-VACUUM apenas mantém o banco de dados pequeno.

Você pode habilitar / desabilitar a aspiração automática do SQLite pelos seguintes pragmas executados no prompt do SQLite -

sqlite> PRAGMA auto_vacuum = NONE; -- 0 means disable auto vacuum
sqlite> PRAGMA auto_vacuum = FULL; -- 1 means enable full auto vacuum
sqlite> PRAGMA auto_vacuum = INCREMENTAL; -- 2 means enable incremental vacuum

Você pode executar o seguinte comando no prompt de comando para verificar a configuração de vácuo automático -

$sqlite3 database_name "PRAGMA auto_vacuum;"

SQLite suporta cinco funções de data e hora da seguinte maneira -

Sr. Não.	Função	Exemplo
1	data (sequência de tempo, modificadores ...)	Isso retorna a data neste formato: AAAA-MM-DD
2	tempo (sequência de tempo, modificadores ...)	Isso retorna a hora como HH: MM: SS
3	datetime (timestring, modifiers ...)	Isso retorna AAAA-MM-DD HH: MM: SS
4	julianday (sequência de tempo, modificadores ...)	Isso retorna o número de dias desde o meio-dia em Greenwich em 24 de novembro de 4714 AC
5	strftime (sequência de tempo, modificadores ...)	Isso retorna a data formatada de acordo com a string de formato especificada como o primeiro argumento formatado de acordo com os formatadores explicados abaixo.

Todas as cinco funções de data e hora acima usam uma string de hora como argumento. A sequência de tempo é seguida por zero ou mais modificadores. A função strftime () também recebe uma string de formato como seu primeiro argumento. A seção a seguir fornecerá detalhes sobre os diferentes tipos de strings de tempo e modificadores.

Time Strings

Uma sequência de tempo pode estar em qualquer um dos seguintes formatos -

Sr. Não.	Sequência de tempo	Exemplo
1	AAAA-MM-DD	30/12/2010
2	AAAA-MM-DD HH: MM	30-12-2010 12:10
3	AAAA-MM-DD HH: MM: SS.SSS	30-12-2010 12: 10: 04.100
4	MM-DD-AAAA HH: MM	30-12-2010 12:10
5	HH: MM	12h10
6	AAAA-MM-DDTHH: MM	30-12-2010 12:10
7	HH: MM: SS	12:10:01
8	AAAAMMDD HHMMSS	20101230 121001
9	agora	07/05/2013

Você pode usar o "T" como um caractere literal separando a data e a hora.

Modificadores

A string de hora pode ser seguida por zero ou mais modificadores que irão alterar a data e / ou hora retornada por qualquer uma das cinco funções acima. Os modificadores são aplicados da esquerda para a direita.

Os seguintes modificadores estão disponíveis no SQLite -

NNN dias
NNN horas
NNN minutos
NNN.NNNN segundos
NNN meses
NNN anos
começo do mês
começo do ano
começo do dia
dia da semana N
unixepoch
localtime
utc

Formatadores

SQLite oferece uma função muito útil strftime()para formatar qualquer data e hora. Você pode usar as seguintes substituições para formatar sua data e hora.

Substituição	Descrição
% d	Dia do mês, 31/01
% f	Segundos fracionários, SS.SSS
% H	Hora, 00-23
% j	Dia do ano, 001-366
% J	Número do dia juliano, DDDD.DDDD
% m	Mês, 00-12
% M	Minuto, 00-59
% s	Segundos desde 01/01/1970
% S	Segundos, 00-59
%W	Dia da semana, 0-6 (0 é domingo)
%W	Semana do ano, 01-53
% Y	Ano, AAAA
%%	símbolo%

Exemplos

Vamos tentar vários exemplos agora usando o prompt SQLite. O comando a seguir calcula a data atual.

sqlite> SELECT date('now');
2013-05-07

O comando a seguir calcula o último dia do mês atual.

sqlite> SELECT date('now','start of month','+1 month','-1 day');
2013-05-31

O comando a seguir calcula a data e a hora para um determinado carimbo de data / hora UNIX 1092941466.

sqlite> SELECT datetime(1092941466, 'unixepoch');
2004-08-19 18:51:06

O comando a seguir calcula a data e a hora para um determinado carimbo de data / hora UNIX 1092941466 e compensa seu fuso horário local.

sqlite> SELECT datetime(1092941466, 'unixepoch', 'localtime');
2004-08-19 13:51:06

O comando a seguir calcula o carimbo de data / hora UNIX atual.

sqlite> SELECT strftime('%s','now');
1393348134

O comando a seguir calcula o número de dias desde a assinatura da Declaração de Independência dos Estados Unidos.

sqlite> SELECT julianday('now') - julianday('1776-07-04');
86798.7094695023

O comando a seguir calcula o número de segundos desde um determinado momento em 2004.

sqlite> SELECT strftime('%s','now') - strftime('%s','2004-01-01 02:34:56');
295001572

O comando a seguir calcula a data da primeira terça-feira de outubro para o ano atual.

sqlite> SELECT date('now','start of year','+9 months','weekday 2');
2013-10-01

O comando a seguir calcula o tempo desde a época do UNIX em segundos (como strftime ('% s', 'agora'), exceto inclui a parte fracionária).

sqlite> SELECT (julianday('now') - 2440587.5)*86400.0;
1367926077.12598

Para converter entre UTC e valores de hora local ao formatar uma data, use os modificadores utc ou localtime da seguinte forma -

sqlite> SELECT time('12:00', 'localtime');
05:00:00

sqlite> SELECT time('12:00', 'utc');
19:00:00

O SQLite possui muitas funções integradas para realizar o processamento em strings ou dados numéricos. A seguir está uma lista de algumas funções integradas do SQLite úteis e todas não diferenciam maiúsculas de minúsculas, o que significa que você pode usar essas funções em minúsculas ou maiúsculas ou em forma mista. Para mais detalhes, você pode verificar a documentação oficial do SQLite.

Sr. Não.	Descrição da função
1	SQLite COUNT Function A função de agregação SQLite COUNT é usada para contar o número de linhas em uma tabela de banco de dados.
2	SQLite MAX Function A função de agregação SQLite MAX nos permite selecionar o valor mais alto (máximo) para uma determinada coluna.
3	SQLite MIN Function A função de agregação SQLite MIN nos permite selecionar o valor mais baixo (mínimo) para uma determinada coluna.
4	SQLite AVG Function A função agregada do SQLite AVG seleciona o valor médio para determinada coluna da tabela.
5	SQLite SUM Function A função de agregação SQLite SUM permite selecionar o total para uma coluna numérica.
6	SQLite RANDOM Function A função SQLite RANDOM retorna um inteiro pseudoaleatório entre -9223372036854775808 e +9223372036854775807.
7	SQLite ABS Function A função SQLite ABS retorna o valor absoluto do argumento numérico.
8	SQLite UPPER Function A função SQLite UPPER converte uma string em letras maiúsculas.
9	SQLite LOWER Function A função SQLite LOWER converte uma string em letras minúsculas.
10	SQLite LENGTH Function A função SQLite LENGTH retorna o comprimento de uma string.
11	SQLite sqlite_version Function A função SQLite sqlite_version retorna a versão da biblioteca SQLite.

Antes de começarmos a dar exemplos sobre as funções mencionadas acima, considere a tabela COMPANY com os seguintes registros.

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

Função SQLite COUNT

A função de agregação SQLite COUNT é usada para contar o número de linhas em uma tabela de banco de dados. A seguir está um exemplo -

sqlite> SELECT count(*) FROM COMPANY;

A instrução SQLite SQL acima produzirá o seguinte.

count(*)
----------
7

Função SQLite MAX

A função de agregação SQLite MAX nos permite selecionar o valor mais alto (máximo) para uma determinada coluna. A seguir está um exemplo -

sqlite> SELECT max(salary) FROM COMPANY;

A instrução SQLite SQL acima produzirá o seguinte.

max(salary)
-----------
85000.0

Função SQLite MIN

A função de agregação SQLite MIN nos permite selecionar o valor mais baixo (mínimo) para uma determinada coluna. A seguir está um exemplo -

sqlite> SELECT min(salary) FROM COMPANY;

A instrução SQLite SQL acima produzirá o seguinte.

min(salary)
-----------
10000.0

Função SQLite AVG

A função agregada do SQLite AVG seleciona o valor médio para uma determinada coluna da tabela. A seguir está um exemplo -

sqlite> SELECT avg(salary) FROM COMPANY;

A instrução SQLite SQL acima produzirá o seguinte.

avg(salary)
----------------
37142.8571428572

Função SQLite SUM

A função de agregação SQLite SUM permite selecionar o total para uma coluna numérica. A seguir está um exemplo -

sqlite> SELECT sum(salary) FROM COMPANY;

A instrução SQLite SQL acima produzirá o seguinte.

sum(salary)
-----------
260000.0

Função SQLite RANDOM

A função SQLite RANDOM retorna um inteiro pseudoaleatório entre -9223372036854775808 e +9223372036854775807. A seguir está um exemplo -

sqlite> SELECT random() AS Random;

A instrução SQLite SQL acima produzirá o seguinte.

Random
-------------------
5876796417670984050

Função SQLite ABS

A função SQLite ABS retorna o valor absoluto do argumento numérico. A seguir está um exemplo -

sqlite> SELECT abs(5), abs(-15), abs(NULL), abs(0), abs("ABC");

A instrução SQLite SQL acima produzirá o seguinte.

abs(5)      abs(-15)    abs(NULL)   abs(0)      abs("ABC")
----------  ----------  ----------  ----------  ----------
5           15                      0           0.0

Função SQLite UPPER

A função SQLite UPPER converte uma string em letras maiúsculas. A seguir está um exemplo -

sqlite> SELECT upper(name) FROM COMPANY;

A instrução SQLite SQL acima produzirá o seguinte.

upper(name)
-----------
PAUL
ALLEN
TEDDY
MARK
DAVID
KIM
JAMES

Função SQLite LOWER

A função SQLite LOWER converte uma string em letras minúsculas. A seguir está um exemplo -

sqlite> SELECT lower(name) FROM COMPANY;

A instrução SQLite SQL acima produzirá o seguinte.

lower(name)
-----------
paul
allen
teddy
mark
david
kim
james

Função SQLite LENGTH

A função SQLite LENGTH retorna o comprimento de uma string. A seguir está um exemplo -

sqlite> SELECT name, length(name) FROM COMPANY;

A instrução SQLite SQL acima produzirá o seguinte.

NAME        length(name)
----------  ------------
Paul        4
Allen       5
Teddy       5
Mark        4
David       5
Kim         3
James       5

Função SQLite sqlite_version

A função SQLite sqlite_version retorna a versão da biblioteca SQLite. A seguir está um exemplo -

sqlite> SELECT sqlite_version() AS 'SQLite Version';

A instrução SQLite SQL acima produzirá o seguinte.

SQLite Version
--------------
3.6.20

Neste capítulo, você aprenderá a usar SQLite em programas C / C ++.

Instalação

Antes de começar a usar SQLite em nossos programas C / C ++, você precisa se certificar de que possui a biblioteca SQLite configurada na máquina. Você pode verificar o capítulo Instalação do SQLite para entender o processo de instalação.

APIs de interface C / C ++

A seguir estão importantes rotinas de interface C / C ++ SQLite, que podem ser suficientes para trabalhar com o banco de dados SQLite de seu programa C / C ++. Se você está procurando um aplicativo mais sofisticado, pode consultar a documentação oficial do SQLite.

Sr. Não.	API e descrição
1	*sqlite3_open(const char filename, sqlite3 ppDb) Esta rotina abre uma conexão com um arquivo de banco de dados SQLite e retorna um objeto de conexão de banco de dados a ser usado por outras rotinas SQLite. Se o argumento do nome do arquivo for NULL ou ': memory:', sqlite3_open () criará um banco de dados na memória na RAM que dura apenas a duração da sessão. Se o nome do arquivo não for NULL, sqlite3_open () tenta abrir o arquivo do banco de dados usando seu valor. Se não existir nenhum arquivo com esse nome, sqlite3_open () irá abrir um novo arquivo de banco de dados com esse nome.
2	*sqlite3_exec(sqlite3, const char sql, sqlite_callback, void data, char errmsg) Esta rotina fornece uma maneira rápida e fácil de executar comandos SQL fornecidos pelo argumento sql, que pode consistir em mais de um comando SQL. Aqui, o primeiro argumento sqlite3 é um objeto de banco de dados aberto, sqlite_callback é um retorno de chamada para o qual data é o primeiro argumento e errmsg será retornado para capturar qualquer erro gerado pela rotina. A rotina SQLite3_exec () analisa e executa todos os comandos dados no sql até chegar ao final da string ou encontrar um erro.
3	*sqlite3_close(sqlite3)** Esta rotina fecha uma conexão de banco de dados aberta anteriormente por uma chamada a sqlite3_open (). Todas as instruções preparadas associadas à conexão devem ser finalizadas antes de encerrar a conexão. Se quaisquer consultas que não foram finalizadas permanecerem, sqlite3_close () retornará SQLITE_BUSY com a mensagem de erro Incapaz de fechar devido a declarações não finalizadas.

Conectar ao banco de dados

O segmento de código C a seguir mostra como se conectar a um banco de dados existente. Se o banco de dados não existir, ele será criado e, finalmente, um objeto de banco de dados será retornado.

#include <stdio.h>
#include <sqlite3.h> 

int main(int argc, char* argv[]) {
   sqlite3 *db;
   char *zErrMsg = 0;
   int rc;

   rc = sqlite3_open("test.db", &db);

   if( rc ) {
      fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
      return(0);
   } else {
      fprintf(stderr, "Opened database successfully\n");
   }
   sqlite3_close(db);
}

Agora, vamos compilar e executar o programa acima para criar nosso banco de dados test.dbno diretório atual. Você pode alterar seu caminho de acordo com sua necessidade.

$gcc test.c -l sqlite3
$./a.out
Opened database successfully

Se você for usar o código-fonte C ++, poderá compilar seu código da seguinte maneira -

$g++ test.c -l sqlite3

Aqui, estamos vinculando nosso programa à biblioteca sqlite3 para fornecer as funções necessárias ao programa C. Isso criará um arquivo de banco de dados test.db em seu diretório e você terá o seguinte resultado.

-rwxr-xr-x. 1 root root 7383 May 8 02:06 a.out
-rw-r--r--. 1 root root  323 May 8 02:05 test.c
-rw-r--r--. 1 root root    0 May 8 02:06 test.db

Crie uma mesa

O segmento de código C a seguir será usado para criar uma tabela no banco de dados criado anteriormente -

#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h> 

static int callback(void *NotUsed, int argc, char **argv, char **azColName) {
   int i;
   for(i = 0; i<argc; i++) {
      printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
   }
   printf("\n");
   return 0;
}

int main(int argc, char* argv[]) {
   sqlite3 *db;
   char *zErrMsg = 0;
   int rc;
   char *sql;

   /* Open database */
   rc = sqlite3_open("test.db", &db);
   
   if( rc ) {
      fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
      return(0);
   } else {
      fprintf(stdout, "Opened database successfully\n");
   }

   /* Create SQL statement */
   sql = "CREATE TABLE COMPANY("  \
      "ID INT PRIMARY KEY     NOT NULL," \
      "NAME           TEXT    NOT NULL," \
      "AGE            INT     NOT NULL," \
      "ADDRESS        CHAR(50)," \
      "SALARY         REAL );";

   /* Execute SQL statement */
   rc = sqlite3_exec(db, sql, callback, 0, &zErrMsg);
   
   if( rc != SQLITE_OK ){
      fprintf(stderr, "SQL error: %s\n", zErrMsg);
      sqlite3_free(zErrMsg);
   } else {
      fprintf(stdout, "Table created successfully\n");
   }
   sqlite3_close(db);
   return 0;
}

Quando o programa acima for compilado e executado, ele criará a tabela COMPANY em seu test.db e a listagem final do arquivo será a seguinte -

-rwxr-xr-x. 1 root root 9567 May 8 02:31 a.out
-rw-r--r--. 1 root root 1207 May 8 02:31 test.c
-rw-r--r--. 1 root root 3072 May 8 02:31 test.db

Operação INSERT

O segmento de código C a seguir mostra como você pode criar registros na tabela COMPANY criada no exemplo acima -

#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h> 

static int callback(void *NotUsed, int argc, char **argv, char **azColName) {
   int i;
   for(i = 0; i<argc; i++) {
      printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
   }
   printf("\n");
   return 0;
}

int main(int argc, char* argv[]) {
   sqlite3 *db;
   char *zErrMsg = 0;
   int rc;
   char *sql;

   /* Open database */
   rc = sqlite3_open("test.db", &db);
   
   if( rc ) {
      fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
      return(0);
   } else {
      fprintf(stderr, "Opened database successfully\n");
   }

   /* Create SQL statement */
   sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "  \
         "VALUES (1, 'Paul', 32, 'California', 20000.00 ); " \
         "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "  \
         "VALUES (2, 'Allen', 25, 'Texas', 15000.00 ); "     \
         "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)" \
         "VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );" \
         "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)" \
         "VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );";

   /* Execute SQL statement */
   rc = sqlite3_exec(db, sql, callback, 0, &zErrMsg);
   
   if( rc != SQLITE_OK ){
      fprintf(stderr, "SQL error: %s\n", zErrMsg);
      sqlite3_free(zErrMsg);
   } else {
      fprintf(stdout, "Records created successfully\n");
   }
   sqlite3_close(db);
   return 0;
}

Quando o programa acima é compilado e executado, ele criará os registros fornecidos na tabela COMPANY e exibirá as duas linhas a seguir -

Opened database successfully
Records created successfully

SELECT Operação

Antes de prosseguir com o exemplo real para buscar registros, vejamos alguns detalhes sobre a função de retorno de chamada, que estamos usando em nossos exemplos. Este retorno de chamada fornece uma maneira de obter resultados de instruções SELECT. Possui a seguinte declaração -

typedef int (*sqlite3_callback)(
   void*,    /* Data provided in the 4th argument of sqlite3_exec() */
   int,      /* The number of columns in row */
   char**,   /* An array of strings representing fields in the row */
   char**    /* An array of strings representing column names */
);

Se o retorno de chamada acima for fornecido na rotina sqlite_exec () como o terceiro argumento, o SQLite irá chamar esta função de retorno de chamada para cada registro processado em cada instrução SELECT executada dentro do argumento SQL.

O segmento de código C a seguir mostra como você pode buscar e exibir registros da tabela COMPANY criada no exemplo acima -

#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h> 

static int callback(void *data, int argc, char **argv, char **azColName){
   int i;
   fprintf(stderr, "%s: ", (const char*)data);
   
   for(i = 0; i<argc; i++){
      printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
   }
   
   printf("\n");
   return 0;
}

int main(int argc, char* argv[]) {
   sqlite3 *db;
   char *zErrMsg = 0;
   int rc;
   char *sql;
   const char* data = "Callback function called";

   /* Open database */
   rc = sqlite3_open("test.db", &db);
   
   if( rc ) {
      fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
      return(0);
   } else {
      fprintf(stderr, "Opened database successfully\n");
   }

   /* Create SQL statement */
   sql = "SELECT * from COMPANY";

   /* Execute SQL statement */
   rc = sqlite3_exec(db, sql, callback, (void*)data, &zErrMsg);
   
   if( rc != SQLITE_OK ) {
      fprintf(stderr, "SQL error: %s\n", zErrMsg);
      sqlite3_free(zErrMsg);
   } else {
      fprintf(stdout, "Operation done successfully\n");
   }
   sqlite3_close(db);
   return 0;
}

Quando o programa acima for compilado e executado, ele produzirá o seguinte resultado.

Opened database successfully
Callback function called: ID = 1
NAME = Paul
AGE = 32
ADDRESS = California
SALARY = 20000.0

Callback function called: ID = 2
NAME = Allen
AGE = 25
ADDRESS = Texas
SALARY = 15000.0

Callback function called: ID = 3
NAME = Teddy
AGE = 23
ADDRESS = Norway
SALARY = 20000.0

Callback function called: ID = 4
NAME = Mark
AGE = 25
ADDRESS = Rich-Mond
SALARY = 65000.0

Operation done successfully

Operação ATUALIZAR

O segmento de código C a seguir mostra como podemos usar a instrução UPDATE para atualizar qualquer registro e, em seguida, buscar e exibir os registros atualizados da tabela COMPANY.

#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h> 

static int callback(void *data, int argc, char **argv, char **azColName){
   int i;
   fprintf(stderr, "%s: ", (const char*)data);
   
   for(i = 0; i<argc; i++) {
      printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
   }
   printf("\n");
   return 0;
}

int main(int argc, char* argv[]) {
   sqlite3 *db;
   char *zErrMsg = 0;
   int rc;
   char *sql;
   const char* data = "Callback function called";

   /* Open database */
   rc = sqlite3_open("test.db", &db);
   
   if( rc ) {
      fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
      return(0);
   } else {
      fprintf(stderr, "Opened database successfully\n");
   }

   /* Create merged SQL statement */
   sql = "UPDATE COMPANY set SALARY = 25000.00 where ID=1; " \
         "SELECT * from COMPANY";

   /* Execute SQL statement */
   rc = sqlite3_exec(db, sql, callback, (void*)data, &zErrMsg);
   
   if( rc != SQLITE_OK ) {
      fprintf(stderr, "SQL error: %s\n", zErrMsg);
      sqlite3_free(zErrMsg);
   } else {
      fprintf(stdout, "Operation done successfully\n");
   }
   sqlite3_close(db);
   return 0;
}

Quando o programa acima for compilado e executado, ele produzirá o seguinte resultado.

Opened database successfully
Callback function called: ID = 1
NAME = Paul
AGE = 32
ADDRESS = California
SALARY = 25000.0

Callback function called: ID = 2
NAME = Allen
AGE = 25
ADDRESS = Texas
SALARY = 15000.0

Callback function called: ID = 3
NAME = Teddy
AGE = 23
ADDRESS = Norway
SALARY = 20000.0

Callback function called: ID = 4
NAME = Mark
AGE = 25
ADDRESS = Rich-Mond
SALARY = 65000.0

Operation done successfully

Operação DELETE

O segmento de código C a seguir mostra como você pode usar a instrução DELETE para excluir qualquer registro e, em seguida, buscar e exibir os registros restantes da tabela COMPANY.

#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h> 

static int callback(void *data, int argc, char **argv, char **azColName) {
   int i;
   fprintf(stderr, "%s: ", (const char*)data);
   
   for(i = 0; i<argc; i++) {
      printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
   }
   printf("\n");
   return 0;
}

int main(int argc, char* argv[]) {
   sqlite3 *db;
   char *zErrMsg = 0;
   int rc;
   char *sql;
   const char* data = "Callback function called";

   /* Open database */
   rc = sqlite3_open("test.db", &db);
   
   if( rc ) {
      fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
      return(0);
   } else {
      fprintf(stderr, "Opened database successfully\n");
   }

   /* Create merged SQL statement */
   sql = "DELETE from COMPANY where ID=2; " \
         "SELECT * from COMPANY";

   /* Execute SQL statement */
   rc = sqlite3_exec(db, sql, callback, (void*)data, &zErrMsg);
   
   if( rc != SQLITE_OK ) {
      fprintf(stderr, "SQL error: %s\n", zErrMsg);
      sqlite3_free(zErrMsg);
   } else {
      fprintf(stdout, "Operation done successfully\n");
   }
   sqlite3_close(db);
   return 0;
}

Quando o programa acima for compilado e executado, ele produzirá o seguinte resultado.

Opened database successfully
Callback function called: ID = 1
NAME = Paul
AGE = 32
ADDRESS = California
SALARY = 20000.0

Callback function called: ID = 3
NAME = Teddy
AGE = 23
ADDRESS = Norway
SALARY = 20000.0

Callback function called: ID = 4
NAME = Mark
AGE = 25
ADDRESS = Rich-Mond
SALARY = 65000.0

Operation done successfully

Neste capítulo, você aprenderá como usar SQLite em programas Java.

Instalação

Antes de começar a usar o SQLite em nossos programas Java, você precisa se certificar de que possui o driver JDBC do SQLite e o Java configurados na máquina. Você pode verificar o tutorial Java para instalação do Java em sua máquina. Agora, vamos verificar como configurar o driver JDBC do SQLite.

Baixe a versão mais recente de sqlite-jdbc- (VERSION) .jar do repositório sqlite-jdbc .
Adicione o arquivo jar baixado sqlite-jdbc- (VERSION) .jar em seu caminho de classe, ou você pode usá-lo junto com a opção -classpath conforme explicado nos exemplos a seguir.

A seção a seguir pressupõe que você tenha pouco conhecimento sobre os conceitos do Java JDBC. Caso contrário, sugere-se que você gaste meia hora com o Tutorial JDBC para se familiarizar com os conceitos explicados abaixo.

Conectar ao banco de dados

Os programas Java a seguir mostram como se conectar a um banco de dados existente. Se o banco de dados não existir, ele será criado e, finalmente, um objeto de banco de dados será retornado.

import java.sql.*;

public class SQLiteJDBC {
  public static void main( String args[] ) {
      Connection c = null;
      
      try {
         Class.forName("org.sqlite.JDBC");
         c = DriverManager.getConnection("jdbc:sqlite:test.db");
      } catch ( Exception e ) {
         System.err.println( e.getClass().getName() + ": " + e.getMessage() );
         System.exit(0);
      }
      System.out.println("Opened database successfully");
   }
}

Agora, vamos compilar e executar o programa acima para criar nosso banco de dados test.dbno diretório atual. Você pode alterar seu caminho de acordo com sua necessidade. Estamos assumindo que a versão atual do driver JDBC sqlite-jdbc-3.7.2.jar está disponível no caminho atual.

$javac SQLiteJDBC.java $java -classpath ".:sqlite-jdbc-3.7.2.jar" SQLiteJDBC
Open database successfully

Se você for usar uma máquina Windows, poderá compilar e executar seu código da seguinte maneira -

$javac SQLiteJDBC.java $java -classpath ".;sqlite-jdbc-3.7.2.jar" SQLiteJDBC
Opened database successfully

Crie uma mesa

O seguinte programa Java será usado para criar uma tabela no banco de dados criado anteriormente.

import java.sql.*;

public class SQLiteJDBC {

   public static void main( String args[] ) {
      Connection c = null;
      Statement stmt = null;
      
      try {
         Class.forName("org.sqlite.JDBC");
         c = DriverManager.getConnection("jdbc:sqlite:test.db");
         System.out.println("Opened database successfully");

         stmt = c.createStatement();
         String sql = "CREATE TABLE COMPANY " +
                        "(ID INT PRIMARY KEY     NOT NULL," +
                        " NAME           TEXT    NOT NULL, " + 
                        " AGE            INT     NOT NULL, " + 
                        " ADDRESS        CHAR(50), " + 
                        " SALARY         REAL)"; 
         stmt.executeUpdate(sql);
         stmt.close();
         c.close();
      } catch ( Exception e ) {
         System.err.println( e.getClass().getName() + ": " + e.getMessage() );
         System.exit(0);
      }
      System.out.println("Table created successfully");
   }
}

Quando o programa acima for compilado e executado, ele criará a tabela COMPANY em seu test.db e a lista final do arquivo será a seguinte -

-rw-r--r--. 1 root root 3201128 Jan 22 19:04 sqlite-jdbc-3.7.2.jar
-rw-r--r--. 1 root root    1506 May  8 05:43 SQLiteJDBC.class
-rw-r--r--. 1 root root     832 May  8 05:42 SQLiteJDBC.java
-rw-r--r--. 1 root root    3072 May  8 05:43 test.db

Operação INSERT

O seguinte programa Java mostra como criar registros na tabela COMPANY criada no exemplo acima.

import java.sql.*;

public class SQLiteJDBC {

   public static void main( String args[] ) {
      Connection c = null;
      Statement stmt = null;
      
      try {
         Class.forName("org.sqlite.JDBC");
         c = DriverManager.getConnection("jdbc:sqlite:test.db");
         c.setAutoCommit(false);
         System.out.println("Opened database successfully");

         stmt = c.createStatement();
         String sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " +
                        "VALUES (1, 'Paul', 32, 'California', 20000.00 );"; 
         stmt.executeUpdate(sql);

         sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " +
                  "VALUES (2, 'Allen', 25, 'Texas', 15000.00 );"; 
         stmt.executeUpdate(sql);

         sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " +
                  "VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );"; 
         stmt.executeUpdate(sql);

         sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " +
                  "VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );"; 
         stmt.executeUpdate(sql);

         stmt.close();
         c.commit();
         c.close();
      } catch ( Exception e ) {
         System.err.println( e.getClass().getName() + ": " + e.getMessage() );
         System.exit(0);
      }
      System.out.println("Records created successfully");
   }
}

Quando o programa acima é compilado e executado, ele criará determinados registros na tabela COMPANY e exibirá as duas linhas seguintes -

Opened database successfully
Records created successfully

SELECT Operação

O seguinte programa Java mostra como buscar e exibir registros da tabela COMPANY criada no exemplo acima.

import java.sql.*;

public class SQLiteJDBC {

  public static void main( String args[] ) {

   Connection c = null;
   Statement stmt = null;
   try {
      Class.forName("org.sqlite.JDBC");
      c = DriverManager.getConnection("jdbc:sqlite:test.db");
      c.setAutoCommit(false);
      System.out.println("Opened database successfully");

      stmt = c.createStatement();
      ResultSet rs = stmt.executeQuery( "SELECT * FROM COMPANY;" );
      
      while ( rs.next() ) {
         int id = rs.getInt("id");
         String  name = rs.getString("name");
         int age  = rs.getInt("age");
         String  address = rs.getString("address");
         float salary = rs.getFloat("salary");
         
         System.out.println( "ID = " + id );
         System.out.println( "NAME = " + name );
         System.out.println( "AGE = " + age );
         System.out.println( "ADDRESS = " + address );
         System.out.println( "SALARY = " + salary );
         System.out.println();
      }
      rs.close();
      stmt.close();
      c.close();
   } catch ( Exception e ) {
      System.err.println( e.getClass().getName() + ": " + e.getMessage() );
      System.exit(0);
   }
   System.out.println("Operation done successfully");
  }
}

Quando o programa acima for compilado e executado, ele produzirá o seguinte resultado.

Opened database successfully
ID = 1
NAME = Paul
AGE = 32
ADDRESS = California
SALARY = 20000.0

ID = 2
NAME = Allen
AGE = 25
ADDRESS = Texas
SALARY = 15000.0

ID = 3
NAME = Teddy
AGE = 23
ADDRESS = Norway
SALARY = 20000.0

ID = 4
NAME = Mark
AGE = 25
ADDRESS = Rich-Mond
SALARY = 65000.0

Operation done successfully

Operação ATUALIZAR

O código Java a seguir mostra como usar a instrução UPDATE para atualizar qualquer registro e, em seguida, buscar e exibir os registros atualizados da tabela COMPANY.

import java.sql.*;

public class SQLiteJDBC {

  public static void main( String args[] ) {
  
   Connection c = null;
   Statement stmt = null;
   
   try {
      Class.forName("org.sqlite.JDBC");
      c = DriverManager.getConnection("jdbc:sqlite:test.db");
      c.setAutoCommit(false);
      System.out.println("Opened database successfully");

      stmt = c.createStatement();
      String sql = "UPDATE COMPANY set SALARY = 25000.00 where ID=1;";
      stmt.executeUpdate(sql);
      c.commit();

      ResultSet rs = stmt.executeQuery( "SELECT * FROM COMPANY;" );
      
      while ( rs.next() ) {
         int id = rs.getInt("id");
         String  name = rs.getString("name");
         int age  = rs.getInt("age");
         String  address = rs.getString("address");
         float salary = rs.getFloat("salary");
         
         System.out.println( "ID = " + id );
         System.out.println( "NAME = " + name );
         System.out.println( "AGE = " + age );
         System.out.println( "ADDRESS = " + address );
         System.out.println( "SALARY = " + salary );
         System.out.println();
      }
      rs.close();
      stmt.close();
      c.close();
   } catch ( Exception e ) {
      System.err.println( e.getClass().getName() + ": " + e.getMessage() );
      System.exit(0);
   }
    System.out.println("Operation done successfully");
   }
}

Quando o programa acima for compilado e executado, ele produzirá o seguinte resultado.

Opened database successfully
ID = 1
NAME = Paul
AGE = 32
ADDRESS = California
SALARY = 25000.0

ID = 2
NAME = Allen
AGE = 25
ADDRESS = Texas
SALARY = 15000.0

ID = 3
NAME = Teddy
AGE = 23
ADDRESS = Norway
SALARY = 20000.0

ID = 4
NAME = Mark
AGE = 25
ADDRESS = Rich-Mond
SALARY = 65000.0

Operation done successfully

Operação DELETE

O código Java a seguir mostra como usar a instrução DELETE para excluir qualquer registro e, em seguida, buscar e exibir os registros restantes de nossa tabela COMPANY.

import java.sql.*;

public class SQLiteJDBC {

   public static void main( String args[] ) {
      Connection c = null;
      Statement stmt = null;
      
      try {
         Class.forName("org.sqlite.JDBC");
         c = DriverManager.getConnection("jdbc:sqlite:test.db");
         c.setAutoCommit(false);
         System.out.println("Opened database successfully");

         stmt = c.createStatement();
         String sql = "DELETE from COMPANY where ID=2;";
         stmt.executeUpdate(sql);
         c.commit();

         ResultSet rs = stmt.executeQuery( "SELECT * FROM COMPANY;" );
         
         while ( rs.next() ) {
         int id = rs.getInt("id");
         String  name = rs.getString("name");
         int age  = rs.getInt("age");
         String  address = rs.getString("address");
         float salary = rs.getFloat("salary");
         
         System.out.println( "ID = " + id );
         System.out.println( "NAME = " + name );
         System.out.println( "AGE = " + age );
         System.out.println( "ADDRESS = " + address );
         System.out.println( "SALARY = " + salary );
         System.out.println();
      }
      rs.close();
      stmt.close();
      c.close();
      } catch ( Exception e ) {
         System.err.println( e.getClass().getName() + ": " + e.getMessage() );
         System.exit(0);
      }
      System.out.println("Operation done successfully");
   }
}

Quando o programa acima for compilado e executado, ele produzirá o seguinte resultado.

Opened database successfully
ID = 1
NAME = Paul
AGE = 32
ADDRESS = California
SALARY = 25000.0

ID = 3
NAME = Teddy
AGE = 23
ADDRESS = Norway
SALARY = 20000.0

ID = 4
NAME = Mark
AGE = 25
ADDRESS = Rich-Mond
SALARY = 65000.0

Operation done successfully

Neste capítulo, você aprenderá como usar SQLite em programas PHP.

Instalação

A extensão SQLite3 é habilitada por padrão a partir do PHP 5.3.0. É possível desativá-lo usando--without-sqlite3 em tempo de compilação.

Os usuários do Windows devem habilitar php_sqlite3.dll para usar esta extensão. Esta DLL está incluída nas distribuições do Windows do PHP a partir do PHP 5.3.0.

Para obter instruções detalhadas de instalação, consulte nosso tutorial de PHP e seu site oficial.

APIs de interface PHP

A seguir estão importantes rotinas PHP que podem ser suficientes para trabalhar com o banco de dados SQLite de seu programa PHP. Se você está procurando um aplicativo mais sofisticado, pode consultar a documentação oficial do PHP.

Sr. Não.	API e descrição
1	public void SQLite3::open ( filename, flags, encryption_key ) Abre o banco de dados SQLite 3. Se o build incluir criptografia, ele tentará usar a chave. Se o nome do arquivo for fornecido como':memory:', SQLite3 :: open () criará um banco de dados na memória na RAM que dura apenas a duração da sessão. Se o nome do arquivo for o nome do arquivo do dispositivo real, SQLite3 :: open () tenta abrir o arquivo de banco de dados usando seu valor. Se não existir nenhum arquivo com esse nome, será criado um novo arquivo de banco de dados com esse nome. Sinalizadores opcionais usados para determinar como abrir o banco de dados SQLite. Por padrão, open usa SQLITE3_OPEN_READWRITE \| SQLITE3_OPEN_CREATE.
2	public bool SQLite3::exec ( string $query ) Essa rotina fornece uma maneira rápida e fácil de executar comandos SQL fornecidos pelo argumento sql, que pode consistir em mais de um comando SQL. Esta rotina é usada para executar uma consulta sem resultados em um determinado banco de dados.
3	public SQLite3Result SQLite3::query ( string $query ) Esta rotina executa uma consulta SQL, retornando um SQLite3Result objeto se a consulta retornar resultados.
4	public int SQLite3::lastErrorCode ( void ) Esta rotina retorna o código de resultado numérico da solicitação SQLite com falha mais recente.
5	public string SQLite3::lastErrorMsg ( void ) Esta rotina retorna um texto em inglês que descreve a solicitação SQLite com falha mais recente.
6	public int SQLite3::changes ( void ) Essa rotina retorna o número de linhas do banco de dados que foram atualizadas, inseridas ou excluídas pela instrução SQL mais recente.
7	public bool SQLite3::close ( void ) Esta rotina fecha uma conexão de banco de dados aberta anteriormente por uma chamada a SQLite3 :: open ().
8	public string SQLite3::escapeString ( string $value ) Esta rotina retorna uma string que foi devidamente escapada para inclusão segura em uma instrução SQL.

Conectar ao banco de dados

O código PHP a seguir mostra como se conectar a um banco de dados existente. Se o banco de dados não existir, ele será criado e, finalmente, um objeto de banco de dados será retornado.

<?php
   class MyDB extends SQLite3 {
      function __construct() {
         $this->open('test.db');
      }
   }
   $db = new MyDB(); if(!$db) {
      echo $db->lastErrorMsg();
   } else {
      echo "Opened database successfully\n";
   }
?>

Agora, vamos executar o programa acima para criar nosso banco de dados test.dbno diretório atual. Você pode alterar seu caminho de acordo com sua necessidade. Se o banco de dados for criado com sucesso, ele exibirá a seguinte mensagem -

Open database successfully

Crie uma mesa

O seguinte programa PHP será usado para criar uma tabela no banco de dados criado anteriormente.

<?php
   class MyDB extends SQLite3 {
      function __construct() {
         $this->open('test.db');
      }
   }
   $db = new MyDB(); if(!$db) {
      echo $db->lastErrorMsg(); } else { echo "Opened database successfully\n"; } $sql =<<<EOF
      CREATE TABLE COMPANY
      (ID INT PRIMARY KEY     NOT NULL,
      NAME           TEXT    NOT NULL,
      AGE            INT     NOT NULL,
      ADDRESS        CHAR(50),
      SALARY         REAL);
EOF;

   $ret = $db->exec($sql); if(!$ret){
      echo $db->lastErrorMsg(); } else { echo "Table created successfully\n"; } $db->close();
?>

Quando o programa acima for executado, ele criará a tabela COMPANY em seu test.db e exibirá as seguintes mensagens -

Opened database successfully
Table created successfully

Operação INSERT

O programa PHP a seguir mostra como criar registros na tabela COMPANY criada no exemplo acima.

<?php
   class MyDB extends SQLite3 {
      function __construct() {
         $this->open('test.db'); } } $db = new MyDB();
   if(!$db){ echo $db->lastErrorMsg();
   } else {
      echo "Opened database successfully\n";
   }

   $sql =<<<EOF INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Paul', 32, 'California', 20000.00 ); INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (2, 'Allen', 25, 'Texas', 15000.00 ); INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (3, 'Teddy', 23, 'Norway', 20000.00 ); INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 ); EOF; $ret = $db->exec($sql);
   if(!$ret) { echo $db->lastErrorMsg();
   } else {
      echo "Records created successfully\n";
   }
   $db->close();
?>

Quando o programa acima for executado, ele criará os registros fornecidos na tabela COMPANY e exibirá as duas linhas a seguir.

Opened database successfully
Records created successfully

SELECT Operação

O programa PHP a seguir mostra como buscar e exibir registros da tabela COMPANY criada no exemplo acima -

<?php
   class MyDB extends SQLite3 {
      function __construct() {
         $this->open('test.db');
      }
   }
   
   $db = new MyDB(); if(!$db) {
      echo $db->lastErrorMsg(); } else { echo "Opened database successfully\n"; } $sql =<<<EOF
      SELECT * from COMPANY;
EOF;

   $ret = $db->query($sql); while($row = $ret->fetchArray(SQLITE3_ASSOC) ) { echo "ID = ". $row['ID'] . "\n";
      echo "NAME = ". $row['NAME'] ."\n"; echo "ADDRESS = ". $row['ADDRESS'] ."\n";
      echo "SALARY = ".$row['SALARY'] ."\n\n"; } echo "Operation done successfully\n"; $db->close();
?>

Quando o programa acima for executado, ele produzirá o seguinte resultado.

Opened database successfully
ID = 1
NAME = Paul
ADDRESS = California
SALARY = 20000

ID = 2
NAME = Allen
ADDRESS = Texas
SALARY = 15000

ID = 3
NAME = Teddy
ADDRESS = Norway
SALARY = 20000

ID = 4
NAME = Mark
ADDRESS = Rich-Mond
SALARY = 65000

Operation done successfully

Operação ATUALIZAR

O código PHP a seguir mostra como usar a instrução UPDATE para atualizar qualquer registro e, em seguida, buscar e exibir os registros atualizados da tabela COMPANY.

<?php
   class MyDB extends SQLite3 {
      function __construct() {
         $this->open('test.db'); } } $db = new MyDB();
   if(!$db) { echo $db->lastErrorMsg();
   } else {
      echo "Opened database successfully\n";
   }
   $sql =<<<EOF UPDATE COMPANY set SALARY = 25000.00 where ID=1; EOF; $ret = $db->exec($sql);
   if(!$ret) { echo $db->lastErrorMsg();
   } else {
      echo $db->changes(), " Record updated successfully\n"; } $sql =<<<EOF
      SELECT * from COMPANY;
EOF;
   
   $ret = $db->query($sql); while($row = $ret->fetchArray(SQLITE3_ASSOC) ) { echo "ID = ". $row['ID'] . "\n";
      echo "NAME = ". $row['NAME'] ."\n"; echo "ADDRESS = ". $row['ADDRESS'] ."\n";
      echo "SALARY = ".$row['SALARY'] ."\n\n"; } echo "Operation done successfully\n"; $db->close();
?>

Quando o programa acima for executado, ele produzirá o seguinte resultado.

Opened database successfully
1 Record updated successfully
ID = 1
NAME = Paul
ADDRESS = California
SALARY = 25000

ID = 2
NAME = Allen
ADDRESS = Texas
SALARY = 15000

ID = 3
NAME = Teddy
ADDRESS = Norway
SALARY = 20000

ID = 4
NAME = Mark
ADDRESS = Rich-Mond
SALARY = 65000

Operation done successfully

Operação DELETE

O código PHP a seguir mostra como usar a instrução DELETE para excluir qualquer registro e, em seguida, buscar e exibir os registros restantes da tabela COMPANY.

<?php
   class MyDB extends SQLite3 {
      function __construct() {
         $this->open('test.db'); } } $db = new MyDB();
   if(!$db) { echo $db->lastErrorMsg();
   } else {
      echo "Opened database successfully\n";
   }
   $sql =<<<EOF DELETE from COMPANY where ID = 2; EOF; $ret = $db->exec($sql);
   if(!$ret){ echo $db->lastErrorMsg();
   } else {
      echo $db->changes(), " Record deleted successfully\n"; } $sql =<<<EOF
      SELECT * from COMPANY;
EOF;
   $ret = $db->query($sql); while($row = $ret->fetchArray(SQLITE3_ASSOC) ) { echo "ID = ". $row['ID'] . "\n";
      echo "NAME = ". $row['NAME'] ."\n"; echo "ADDRESS = ". $row['ADDRESS'] ."\n";
      echo "SALARY = ".$row['SALARY'] ."\n\n"; } echo "Operation done successfully\n"; $db->close();
?>

Quando o programa acima for executado, ele produzirá o seguinte resultado.

Opened database successfully
1 Record deleted successfully
ID = 1
NAME = Paul
ADDRESS = California
SALARY = 25000

ID = 3
NAME = Teddy
ADDRESS = Norway
SALARY = 20000

ID = 4
NAME = Mark
ADDRESS = Rich-Mond
SALARY = 65000

Operation done successfully

Neste capítulo, você aprenderá como usar SQLite em programas Perl.

Instalação

O SQLite3 pode ser integrado ao Perl usando o módulo Perl DBI, que é um módulo de acesso ao banco de dados para a linguagem de programação Perl. Ele define um conjunto de métodos, variáveis e convenções que fornecem uma interface de banco de dados padrão.

A seguir estão as etapas simples para instalar o módulo DBI em sua máquina Linux / UNIX -

$ wget http://search.cpan.org/CPAN/authors/id/T/TI/TIMB/DBI-1.625.tar.gz $ tar xvfz DBI-1.625.tar.gz
$ cd DBI-1.625 $ perl Makefile.PL
$ make $ make install

Se você precisar instalar o driver SQLite para DBI, ele pode ser instalado da seguinte maneira -

$ wget http://search.cpan.org/CPAN/authors/id/M/MS/MSERGEANT/DBD-SQLite-1.11.tar.gz $ tar xvfz DBD-SQLite-1.11.tar.gz
$ cd DBD-SQLite-1.11 $ perl Makefile.PL
$ make $ make install

APIs de interface DBI

A seguir estão rotinas DBI importantes, que podem ser suficientes para trabalhar com o banco de dados SQLite de seu programa Perl. Se você está procurando um aplicativo mais sofisticado, pode consultar a documentação oficial do Perl DBI.

Sr. Não.	API e descrição
1	DBI->connect($data_source, "", "", \%attr) Estabelece uma conexão de banco de dados, ou sessão, para o $ data_source solicitado. Retorna um objeto de identificador de banco de dados se a conexão for bem-sucedida. A fonte de dados tem o formato - DBI:SQLite:dbname = 'test.db'onde SQLite é o nome do driver SQLite e test.db é o nome do arquivo de banco de dados SQLite. Se o nome do arquivo for fornecido como':memory:', ele criará um banco de dados na memória na RAM que dura apenas durante a sessão. Se o nome do arquivo for o nome do arquivo do dispositivo real, ele tentará abrir o arquivo do banco de dados usando seu valor. Se não existir nenhum arquivo com esse nome, será criado um novo arquivo de banco de dados com esse nome. Você mantém o segundo e o terceiro parâmetro como strings em branco e o último parâmetro é passar vários atributos, conforme mostrado no exemplo a seguir.
2	$dbh->do($sql) Essa rotina prepara e executa uma única instrução SQL. Retorna o número de linhas afetadas ou undef em caso de erro. Um valor de retorno de -1 significa que o número de linhas não é conhecido, não aplicável ou não disponível. Aqui, $ dbh é um identificador retornado pela chamada DBI-> connect ().
3	$dbh->prepare($sql) Essa rotina prepara uma instrução para execução posterior pelo mecanismo de banco de dados e retorna uma referência a um objeto de manipulação de instrução.
4	$sth->execute() Essa rotina executa todo o processamento necessário para executar a instrução preparada. Um undef é retornado se ocorrer um erro. Uma execução bem-sucedida sempre retorna verdadeiro, independentemente do número de linhas afetadas. Aqui,$sth is a statement handle returned by $dbh-> prepare ($ sql) chamada.
5	$sth->fetchrow_array() Esta rotina busca a próxima linha de dados e a retorna como uma lista contendo os valores do campo. Os campos nulos são retornados como valores undef na lista.
6	$DBI::err Isso é equivalente a $ h-> err, onde $h is any of the handle types like $dbh, $sth, or $drh. Isso retorna o código de erro do mecanismo de banco de dados nativo do último método de driver chamado.
7	$DBI::errstr Isso é equivalente a $ h-> errstr, onde $h is any of the handle types like $dbh, $sth, or $drh. Isso retorna a mensagem de erro do mecanismo de banco de dados nativo do último método DBI chamado.
8	$dbh->disconnect() Esta rotina fecha uma conexão de banco de dados aberta anteriormente por uma chamada para DBI-> connect ().

Conectar ao banco de dados

O código Perl a seguir mostra como se conectar a um banco de dados existente. Se o banco de dados não existir, ele será criado e, finalmente, um objeto de banco de dados será retornado.

#!/usr/bin/perl

use DBI;
use strict;

my $driver   = "SQLite"; 
my $database = "test.db"; my $dsn = "DBI:$driver:dbname=$database";
my $userid = ""; my $password = "";
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 }) 
   or die $DBI::errstr;

print "Opened database successfully\n";

Agora, vamos executar o programa acima para criar nosso banco de dados test.db no diretório atual. Você pode alterar seu caminho de acordo com sua necessidade. Mantenha o código acima no arquivo sqlite.pl e execute-o conforme mostrado abaixo. Se o banco de dados for criado com sucesso, ele exibirá a seguinte mensagem -

$ chmod +x sqlite.pl
$ ./sqlite.pl
Open database successfully

Crie uma mesa

O seguinte programa Perl é usado para criar uma tabela no banco de dados criado anteriormente.

#!/usr/bin/perl

use DBI;
use strict;

my $driver   = "SQLite";
my $database = "test.db"; my $dsn = "DBI:$driver:dbname=$database";
my $userid = ""; my $password = "";
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 })
   or die $DBI::errstr; print "Opened database successfully\n"; my $stmt = qq(CREATE TABLE COMPANY
   (ID INT PRIMARY KEY     NOT NULL,
      NAME           TEXT    NOT NULL,
      AGE            INT     NOT NULL,
      ADDRESS        CHAR(50),
      SALARY         REAL););

my $rv = $dbh->do($stmt); if($rv < 0) {
   print $DBI::errstr; } else { print "Table created successfully\n"; } $dbh->disconnect();

Quando o programa acima for executado, ele criará a tabela COMPANY em seu test.db e exibirá as seguintes mensagens -

Opened database successfully
Table created successfully

NOTE - Caso você veja o seguinte erro em qualquer uma das operações -

DBD::SQLite::st execute failed: not an error(21) at dbdimp.c line 398

Nesse caso, abra dbdimp.c file disponível na instalação DBD-SQLite e descubra sqlite3_prepare() função e altere seu terceiro argumento para -1 em vez de 0. Finalmente, instale DBD :: SQLite usando make e fazer make install para resolver o problema.

Operação INSERT

O programa Perl a seguir mostra como criar registros na tabela COMPANY criada no exemplo acima.

#!/usr/bin/perl

use DBI;
use strict;

my $driver = "SQLite"; my $database = "test.db";
my $dsn = "DBI:$driver:dbname=$database"; my $userid = "";
my $password = ""; my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 }) or die $DBI::errstr;
print "Opened database successfully\n";

my $stmt = qq(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Paul', 32, 'California', 20000.00 )); my $rv = $dbh->do($stmt) or die $DBI::errstr; $stmt = qq(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
               VALUES (2, 'Allen', 25, 'Texas', 15000.00 ));
$rv = $dbh->do($stmt) or die $DBI::errstr;

$stmt = qq(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (3, 'Teddy', 23, 'Norway', 20000.00 )); $rv = $dbh->do($stmt) or die $DBI::errstr; $stmt = qq(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
               VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 ););

$rv = $dbh->do($stmt) or die $DBI::errstr;

print "Records created successfully\n";
$dbh->disconnect();

Quando o programa acima for executado, ele criará os registros fornecidos na tabela COMPANY e exibirá as duas linhas a seguir -

Opened database successfully
Records created successfully

SELECT Operação

O programa Perl a seguir mostra como buscar e exibir registros da tabela COMPANY criada no exemplo acima.

#!/usr/bin/perl

use DBI;
use strict;

my $driver = "SQLite";
my $database = "test.db"; my $dsn = "DBI:$driver:dbname=$database";
my $userid = ""; my $password = "";
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 })
   or die $DBI::errstr; print "Opened database successfully\n"; my $stmt = qq(SELECT id, name, address, salary from COMPANY;);
my $sth = $dbh->prepare( $stmt ); my $rv = $sth->execute() or die $DBI::errstr;

if($rv < 0) { print $DBI::errstr;
}

while(my @row = $sth->fetchrow_array()) { print "ID = ". $row[0] . "\n";
      print "NAME = ". $row[1] ."\n"; print "ADDRESS = ". $row[2] ."\n";
      print "SALARY =  ". $row[3] ."\n\n"; } print "Operation done successfully\n"; $dbh->disconnect();

Quando o programa acima for executado, ele produzirá o seguinte resultado.

Opened database successfully
ID = 1
NAME = Paul
ADDRESS = California
SALARY =  20000

ID = 2
NAME = Allen
ADDRESS = Texas
SALARY =  15000

ID = 3
NAME = Teddy
ADDRESS = Norway
SALARY =  20000

ID = 4
NAME = Mark
ADDRESS = Rich-Mond
SALARY =  65000

Operation done successfully

Operação ATUALIZAR

O código Perl a seguir mostra como fazer a instrução UPDATE para atualizar qualquer registro e, em seguida, buscar e exibir os registros atualizados da tabela COMPANY.

#!/usr/bin/perl

use DBI;
use strict;

my $driver = "SQLite"; my $database = "test.db";
my $dsn = "DBI:$driver:dbname=$database"; my $userid = "";
my $password = ""; my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 }) or die $DBI::errstr;
print "Opened database successfully\n";

my $stmt = qq(UPDATE COMPANY set SALARY = 25000.00 where ID=1;); my $rv = $dbh->do($stmt) or die $DBI::errstr; if( $rv < 0 ) {
   print $DBI::errstr; } else { print "Total number of rows updated : $rv\n";
}
$stmt = qq(SELECT id, name, address, salary from COMPANY;); my $sth = $dbh->prepare( $stmt );
$rv = $sth->execute() or die $DBI::errstr; if($rv < 0) {
   print $DBI::errstr; } while(my @row = $sth->fetchrow_array()) {
      print "ID = ". $row[0] . "\n"; print "NAME = ". $row[1] ."\n";
      print "ADDRESS = ". $row[2] ."\n"; print "SALARY = ". $row[3] ."\n\n";
}
print "Operation done successfully\n";
$dbh->disconnect();

Quando o programa acima for executado, ele produzirá o seguinte resultado.

Opened database successfully
Total number of rows updated : 1
ID = 1
NAME = Paul
ADDRESS = California
SALARY =  25000

ID = 2
NAME = Allen
ADDRESS = Texas
SALARY =  15000

ID = 3
NAME = Teddy
ADDRESS = Norway
SALARY =  20000

ID = 4
NAME = Mark
ADDRESS = Rich-Mond
SALARY =  65000

Operation done successfully

Operação DELETE

O código Perl a seguir mostra como usar a instrução DELETE para excluir qualquer registro e, em seguida, buscar e exibir os registros restantes da tabela COMPANY -

#!/usr/bin/perl

use DBI;
use strict;

my $driver   = "SQLite";
my $database = "test.db"; my $dsn = "DBI:$driver:dbname=$database";
my $userid = ""; my $password = "";
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 })
   or die $DBI::errstr; print "Opened database successfully\n"; my $stmt = qq(DELETE from COMPANY where ID = 2;);
my $rv = $dbh->do($stmt) or die $DBI::errstr;

if( $rv < 0 ) { print $DBI::errstr;
} else {
   print "Total number of rows deleted : $rv\n"; } $stmt = qq(SELECT id, name, address, salary from COMPANY;);
my $sth = $dbh->prepare( $stmt ); $rv = $sth->execute() or die $DBI::errstr;

if($rv < 0) { print $DBI::errstr;
}

while(my @row = $sth->fetchrow_array()) { print "ID = ". $row[0] . "\n";
      print "NAME = ". $row[1] ."\n"; print "ADDRESS = ". $row[2] ."\n";
      print "SALARY =  ". $row[3] ."\n\n"; } print "Operation done successfully\n"; $dbh->disconnect();

Quando o programa acima for executado, ele produzirá o seguinte resultado.

Opened database successfully
Total number of rows deleted : 1
ID = 1
NAME = Paul
ADDRESS = California
SALARY = 25000

ID = 3
NAME = Teddy
ADDRESS = Norway
SALARY = 20000

ID = 4
NAME = Mark
ADDRESS = Rich-Mond
SALARY = 65000

Operation done successfully

Neste capítulo, você aprenderá como usar SQLite em programas Python.

Instalação

SQLite3 pode ser integrado com Python usando o módulo sqlite3, que foi escrito por Gerhard Haring. Ele fornece uma interface SQL compatível com a especificação DB-API 2.0 descrita pelo PEP 249. Você não precisa instalar este módulo separadamente porque ele é enviado por padrão junto com o Python versão 2.5.x em diante.

Para usar o módulo sqlite3, você deve primeiro criar um objeto de conexão que represente o banco de dados e, em seguida, opcionalmente, você pode criar um objeto cursor, que o ajudará a executar todas as instruções SQL.

APIs de módulo Python sqlite3

A seguir estão importantes rotinas do módulo sqlite3, que podem ser suficientes para sua necessidade de trabalhar com o banco de dados SQLite do seu programa Python. Se você estiver procurando por um aplicativo mais sofisticado, poderá consultar a documentação oficial do módulo Python sqlite3.

Sr. Não.	API e descrição
1	sqlite3.connect(database [,timeout ,other optional arguments]) Esta API abre uma conexão com o arquivo de banco de dados SQLite. Você pode usar ": memory:" para abrir uma conexão de banco de dados para um banco de dados que reside na RAM em vez de no disco. Se o banco de dados for aberto com sucesso, ele retorna um objeto de conexão. Quando um banco de dados é acessado por várias conexões e um dos processos modifica o banco de dados, o banco de dados SQLite é bloqueado até que a transação seja confirmada. O parâmetro timeout especifica quanto tempo a conexão deve esperar até que o bloqueio desapareça até gerar uma exceção. O padrão para o parâmetro de tempo limite é 5,0 (cinco segundos). Se o nome do banco de dados fornecido não existir, esta chamada criará o banco de dados. Você também pode especificar o nome do arquivo com o caminho necessário se quiser criar um banco de dados em qualquer outro lugar, exceto no diretório atual.
2	connection.cursor([cursorClass]) Esta rotina cria um cursorque será usado em toda a sua programação de banco de dados com Python. Este método aceita um único parâmetro opcional cursorClass. Se fornecido, deve ser uma classe de cursor personalizada que estende sqlite3.Cursor.
3	cursor.execute(sql [, optional parameters]) Esta rotina executa uma instrução SQL. A instrução SQL pode ser parametrizada (ou seja, marcadores em vez de literais SQL). O módulo sqlite3 suporta dois tipos de marcadores de posição: pontos de interrogação e marcadores de posição nomeados (estilo nomeado). For example - cursor.execute ("inserir valores de pessoas (?,?)", (Quem, idade))
4	connection.execute(sql [, optional parameters]) Esta rotina é um atalho do método execute acima fornecido pelo objeto cursor e cria um objeto cursor intermediário chamando o método cursor, a seguir chama o método execute do cursor com os parâmetros fornecidos.
5	cursor.executemany(sql, seq_of_parameters) Esta rotina executa um comando SQL em todas as sequências de parâmetros ou mapeamentos encontrados na sequência sql.
6	connection.executemany(sql[, parameters]) Esta rotina é um atalho que cria um objeto cursor intermediário chamando o método cursor e, em seguida, chama o método cursor.s executemany com os parâmetros fornecidos.
7	cursor.executescript(sql_script) Essa rotina executa várias instruções SQL ao mesmo tempo fornecidas na forma de script. Emite primeiro uma instrução COMMIT e, em seguida, executa o script SQL obtido como parâmetro. Todas as instruções SQL devem ser separadas por ponto e vírgula (;).
8	connection.executescript(sql_script) Essa rotina é um atalho que cria um objeto de cursor intermediário chamando o método do cursor e, em seguida, chama o método executa script do cursor com os parâmetros fornecidos.
9	connection.total_changes() Essa rotina retorna o número total de linhas do banco de dados que foram modificadas, inseridas ou excluídas desde que a conexão com o banco de dados foi aberta.
10	connection.commit() Este método confirma a transação atual. Se você não chamar este método, tudo o que você fez desde a última chamada para commit () não será visível em outras conexões de banco de dados.
11	connection.rollback() Este método reverte quaisquer mudanças no banco de dados desde a última chamada para commit ().
12	connection.close() Este método fecha a conexão com o banco de dados. Observe que isso não chama commit () automaticamente. Se você apenas fechar a conexão com o banco de dados sem chamar commit () primeiro, suas alterações serão perdidas!
13	cursor.fetchone() Este método busca a próxima linha de um conjunto de resultados de consulta, retornando uma única sequência, ou None quando não há mais dados disponíveis.
14	cursor.fetchmany([size = cursor.arraysize]) Essa rotina busca o próximo conjunto de linhas de um resultado de consulta, retornando uma lista. Uma lista vazia é retornada quando não há mais linhas disponíveis. O método tenta buscar quantas linhas forem indicadas pelo parâmetro de tamanho.
15	cursor.fetchall() Esta rotina busca todas as linhas (restantes) de um resultado de consulta, retornando uma lista. Uma lista vazia é retornada quando nenhuma linha está disponível.

Conectar ao banco de dados

O código Python a seguir mostra como se conectar a um banco de dados existente. Se o banco de dados não existir, ele será criado e, finalmente, um objeto de banco de dados será retornado.

#!/usr/bin/python

import sqlite3

conn = sqlite3.connect('test.db')

print "Opened database successfully";

Aqui, você também pode fornecer o nome do banco de dados como o nome especial :memory:para criar um banco de dados na RAM. Agora, vamos executar o programa acima para criar nosso banco de dadostest.dbno diretório atual. Você pode alterar seu caminho de acordo com sua necessidade. Mantenha o código acima no arquivo sqlite.py e execute-o conforme mostrado abaixo. Se o banco de dados for criado com sucesso, ele exibirá a seguinte mensagem.

$chmod +x sqlite.py $./sqlite.py
Open database successfully

Crie uma mesa

O programa Python a seguir será usado para criar uma tabela no banco de dados criado anteriormente.

#!/usr/bin/python

import sqlite3

conn = sqlite3.connect('test.db')
print "Opened database successfully";

conn.execute('''CREATE TABLE COMPANY
         (ID INT PRIMARY KEY     NOT NULL,
         NAME           TEXT    NOT NULL,
         AGE            INT     NOT NULL,
         ADDRESS        CHAR(50),
         SALARY         REAL);''')
print "Table created successfully";

conn.close()

Quando o programa acima for executado, ele criará a tabela COMPANY em seu test.db e exibirá as seguintes mensagens -

Opened database successfully
Table created successfully

Operação INSERT

O programa Python a seguir mostra como criar registros na tabela COMPANY criada no exemplo acima.

#!/usr/bin/python

import sqlite3

conn = sqlite3.connect('test.db')
print "Opened database successfully";

conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (1, 'Paul', 32, 'California', 20000.00 )");

conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (2, 'Allen', 25, 'Texas', 15000.00 )");

conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (3, 'Teddy', 23, 'Norway', 20000.00 )");

conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 )");

conn.commit()
print "Records created successfully";
conn.close()

Quando o programa acima for executado, ele criará os registros fornecidos na tabela COMPANY e exibirá as duas linhas a seguir -

Opened database successfully
Records created successfully

SELECT Operação

O programa Python a seguir mostra como buscar e exibir registros da tabela COMPANY criada no exemplo acima.

#!/usr/bin/python

import sqlite3

conn = sqlite3.connect('test.db')
print "Opened database successfully";

cursor = conn.execute("SELECT id, name, address, salary from COMPANY")
for row in cursor:
   print "ID = ", row[0]
   print "NAME = ", row[1]
   print "ADDRESS = ", row[2]
   print "SALARY = ", row[3], "\n"

print "Operation done successfully";
conn.close()

Quando o programa acima for executado, ele produzirá o seguinte resultado.

Opened database successfully
ID = 1
NAME = Paul
ADDRESS = California
SALARY = 20000.0

ID = 2
NAME = Allen
ADDRESS = Texas
SALARY = 15000.0

ID = 3
NAME = Teddy
ADDRESS = Norway
SALARY = 20000.0

ID = 4
NAME = Mark
ADDRESS = Rich-Mond
SALARY = 65000.0

Operation done successfully

Operação ATUALIZAR

O código Python a seguir mostra como usar a instrução UPDATE para atualizar qualquer registro e, em seguida, buscar e exibir os registros atualizados da tabela COMPANY.

#!/usr/bin/python

import sqlite3

conn = sqlite3.connect('test.db')
print "Opened database successfully";

conn.execute("UPDATE COMPANY set SALARY = 25000.00 where ID = 1")
conn.commit()
print "Total number of rows updated :", conn.total_changes

cursor = conn.execute("SELECT id, name, address, salary from COMPANY")
for row in cursor:
   print "ID = ", row[0]
   print "NAME = ", row[1]
   print "ADDRESS = ", row[2]
   print "SALARY = ", row[3], "\n"

print "Operation done successfully";
conn.close()

Quando o programa acima for executado, ele produzirá o seguinte resultado.

Opened database successfully
Total number of rows updated : 1
ID = 1
NAME = Paul
ADDRESS = California
SALARY = 25000.0

ID = 2
NAME = Allen
ADDRESS = Texas
SALARY = 15000.0

ID = 3
NAME = Teddy
ADDRESS = Norway
SALARY = 20000.0

ID = 4
NAME = Mark
ADDRESS = Rich-Mond
SALARY = 65000.0

Operation done successfully

Operação DELETE

O código Python a seguir mostra como usar a instrução DELETE para excluir qualquer registro e, em seguida, buscar e exibir os registros restantes da tabela COMPANY.

#!/usr/bin/python

import sqlite3

conn = sqlite3.connect('test.db')
print "Opened database successfully";

conn.execute("DELETE from COMPANY where ID = 2;")
conn.commit()
print "Total number of rows deleted :", conn.total_changes

cursor = conn.execute("SELECT id, name, address, salary from COMPANY")
for row in cursor:
   print "ID = ", row[0]
   print "NAME = ", row[1]
   print "ADDRESS = ", row[2]
   print "SALARY = ", row[3], "\n"

print "Operation done successfully";
conn.close()

Quando o programa acima for executado, ele produzirá o seguinte resultado.

Opened database successfully
Total number of rows deleted : 1
ID = 1
NAME = Paul
ADDRESS = California
SALARY = 20000.0

ID = 3
NAME = Teddy
ADDRESS = Norway
SALARY = 20000.0

ID = 4
NAME = Mark
ADDRESS = Rich-Mond
SALARY = 65000.0

Operation done successfully