Primeiros passos
O projeto SQLite fornece um programa de linha de comando simples denominado sqlite3 (ou sqlite3.exe no Windows) que permite ao usuário inserir e executar manualmente instruções SQL em um banco de dados SQLite ou em um arquivo ZIP . Este documento fornece uma breve introdução sobre como usar o programa sqlite3 .
Inicie o programa sqlite3 digitando "sqlite3" no prompt de comando, opcionalmente seguido pelo nome do arquivo que contém o banco de dados SQLite (ou arquivo ZIP ). Se o arquivo nomeado não existir, um novo arquivo de banco de dados com o nome fornecido será criado automaticamente. Se nenhum arquivo de banco de dados for especificado na linha de comando, um banco de dados temporário será criado e excluído quando o programa "sqlite3" for encerrado.
Na inicialização, o programa sqlite3 mostrará uma breve mensagem de banner e solicitará que você insira o SQL. Digite as instruções SQL (terminadas por ponto-e-vírgula), pressione "Enter" e o SQL será executado.
Por exemplo, para criar um novo banco de dados SQLite denominado "ex1" com uma única tabela denominada "tbl1", você pode fazer o seguinte:
$ sqlite3 ex1
SQLite version 3.28.0 2019-03-02 15:25:24
Enter ".help" for usage hints.
sqlite> create table tbl1(one varchar(10), two smallint);
sqlite> insert into tbl1 values('hello!',10);
sqlite> insert into tbl1 values('goodbye', 20);
sqlite> select * from tbl1;
hello!|10
goodbye|20
sqlite>
Encerre o programa sqlite3 digitando o caractere End-Of-File do sistema (geralmente um Control-D). Use o caractere de interrupção (geralmente um Control-C) para interromper uma instrução SQL de longa execução.
Certifique-se de digitar um ponto-e-vírgula no final de cada comando SQL! O programa sqlite3 procura um ponto-e-vírgula para saber quando o comando SQL está completo. Se você omitir o ponto-e-vírgula, o sqlite3 apresentará um prompt de continuação e aguardará que você insira mais texto a ser adicionado ao comando SQL atual. Este recurso permite inserir comandos SQL que abrangem várias linhas. Por exemplo:
sqlite> CREATE TABLE tbl2 (
...> f1 varchar(30) primary key,
...> f2 text,
...> f3 real
...> );
sqlite>
Clique duas vezes em Inicializar no Windows
Os usuários do Windows podem clicar duas vezes no ícone sqlite3.exe para fazer com que o shell da linha de comando apareça uma janela de terminal executando SQLite.
No entanto, como o clique duplo inicia o sqlite3.exe sem argumentos de linha de comando, nenhum arquivo de banco de dados terá sido especificado, portanto, o SQLite usará um banco de dados temporário que é excluído quando a sessão termina. Para usar um arquivo de disco permanente como banco de dados, digite o comando ".open" imediatamente após a inicialização da janela do terminal:
SQLite version 3.28.0 2019-03-02 15:25:24
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .open ex1.db
sqlite>
O exemplo acima faz com que o arquivo de banco de dados denominado "ex1.db" seja aberto e usado. O arquivo "ex1.db" é criado se não existir anteriormente. Você pode querer usar um nome de caminho completo para garantir que o arquivo esteja no diretório em que você pensa que está. Use barras como o caractere separador de diretório. Em outras palavras, use "c: /work/ex1.db", não "c: \ work \ ex1.db".
Como alternativa, você pode criar um novo banco de dados usando o armazenamento temporário padrão e, em seguida, salvar esse banco de dados em um arquivo de disco usando o comando ".save":
SQLite version 3.28.0 2019-03-02 15:25:24
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> ... many SQL commands omitted ...
sqlite> .save ex1.db
sqlite>
Tenha cuidado ao usar o comando ".save", pois ele sobrescreverá quaisquer arquivos de banco de dados preexistentes com o mesmo nome sem solicitar confirmação. Tal como acontece com o comando ".open", você pode querer usar um nome de caminho completo com separadores de diretório de barra para evitar ambigüidade.
3. Comandos especiais para sqlite3 (comandos de ponto)
Na maioria das vezes, sqlite3 apenas lê linhas de entrada e as passa para a biblioteca SQLite para execução. Mas as linhas de entrada que começam com um ponto (".") São interceptadas e interpretadas pelo próprio programa sqlite3. Esses "comandos de ponto" são normalmente usados para alterar o formato de saída de consultas ou para executar certas instruções de consulta predefinidas. Originalmente, havia apenas alguns comandos de ponto, mas ao longo dos anos muitos novos recursos foram acumulados, de modo que hoje existem mais de 60.
Para obter uma lista dos comandos de ponto disponíveis, você pode inserir ".help" sem argumentos. Ou digite ".help TOPIC" para obter informações detalhadas sobre TOPIC. A lista de comandos de ponto disponíveis é a seguinte:
sqlite> .help
.archive ... Manage SQL archives
.auth ON|OFF Show authorizer callbacks
.backup ?DB? FILE Backup DB (default "main") to FILE
.bail on|off Stop after hitting an error. Default OFF
.binary on|off Turn binary output on or off. Default OFF
.cd DIRECTORY Change the working directory to DIRECTORY
.changes on|off Show number of rows changed by SQL
.check GLOB Fail if output since .testcase does not match
.clone NEWDB Clone data into NEWDB from the existing database
.databases List names and files of attached databases
.dbconfig ?op? ?val? List or change sqlite3_db_config() options
.dbinfo ?DB? Show status information about the database
.dump ?OBJECTS? Render database content as SQL
.echo on|off Turn command echo on or off
.eqp on|off|full|... Enable or disable automatic EXPLAIN QUERY PLAN
.excel Display the output of next command in spreadsheet
.exit ?CODE? Exit this program with return-code CODE
.expert EXPERIMENTAL. Suggest indexes for queries
.explain ?on|off|auto? Change the EXPLAIN formatting mode. Default: auto
.filectrl CMD ... Run various sqlite3_file_control() operations
.fullschema ?--indent? Show schema and the content of sqlite_stat tables
.headers on|off Turn display of headers on or off
.help ?-all? ?PATTERN? Show help text for PATTERN
.import FILE TABLE Import data from FILE into TABLE
.imposter INDEX TABLE Create imposter table TABLE on index INDEX
.indexes ?TABLE? Show names of indexes
.limit ?LIMIT? ?VAL? Display or change the value of an SQLITE_LIMIT
.lint OPTIONS Report potential schema issues.
.load FILE ?ENTRY? Load an extension library
.log FILE|off Turn logging on or off. FILE can be stderr/stdout
.mode MODE ?TABLE? Set output mode
.nullvalue STRING Use STRING in place of NULL values
.once ?OPTIONS? ?FILE? Output for the next SQL command only to FILE
.open ?OPTIONS? ?FILE? Close existing database and reopen FILE
.output ?FILE? Send output to FILE or stdout if FILE is omitted
.parameter CMD ... Manage SQL parameter bindings
.print STRING... Print literal STRING
.progress N Invoke progress handler after every N opcodes
.prompt MAIN CONTINUE Replace the standard prompts
.quit Exit this program
.read FILE Read input from FILE
.recover Recover as much data as possible from corrupt db.
.restore ?DB? FILE Restore content of DB (default "main") from FILE
.save FILE Write in-memory database into FILE
.scanstats on|off Turn sqlite3_stmt_scanstatus() metrics on or off
.schema ?PATTERN? Show the CREATE statements matching PATTERN
.selftest ?OPTIONS? Run tests defined in the SELFTEST table
.separator COL ?ROW? Change the column and row separators
.session ?NAME? CMD ... Create or control sessions
.sha3sum ... Compute a SHA3 hash of database content
.shell CMD ARGS... Run CMD ARGS... in a system shell
.show Show the current values for various settings
.stats ?ARG? Show stats or turn stats on or off
.system CMD ARGS... Run CMD ARGS... in a system shell
.tables ?TABLE? List names of tables matching LIKE pattern TABLE
.testcase NAME Begin redirecting output to 'testcase-out.txt'
.testctrl CMD ... Run various sqlite3_test_control() operations
.timeout MS Try opening locked tables for MS milliseconds
.timer on|off Turn SQL timer on or off
.trace ?OPTIONS? Output each SQL statement as it is run
.vfsinfo ?AUX? Information about the top-level VFS
.vfslist List all available VFSes
.vfsname ?AUX? Print the name of the VFS stack
.width NUM1 NUM2 ... Set minimum column widths for columnar output
sqlite>
4. Regras para "comandos de ponto"
As instruções SQL comuns têm formato livre e podem ser espalhadas por várias linhas e podem ter espaços em branco e comentários em qualquer lugar. Comandos de ponto são mais restritivos:
- Um comando de ponto deve começar com "." na margem esquerda sem espaço em branco anterior.
- O comando dot deve estar inteiramente contido em uma única linha de entrada.
- Um comando de ponto não pode ocorrer no meio de uma instrução SQL comum. Em outras palavras, um comando de ponto não pode ocorrer em um prompt de continuação.
- Comandos de ponto não reconhecem comentários.
Os argumentos passados para comandos de ponto são analisados a partir da cauda do comando, de acordo com estas regras: Os argumentos são delimitados por espaços em branco (excluindo nova linha); O texto delimitado por um par de aspas simples ou duplas é tratado como um único argumento, sem as aspas; e, dentro de um argumento entre aspas duplas, é feita a tradução da sequência de escape tradicional da string C com barra invertida.
Os comandos de ponto são interpretados pelo programa de linha de comando sqlite3.exe, não pelo próprio SQLite. Portanto, nenhum dos comandos de ponto funcionará como um argumento para interfaces SQLite como sqlite3_prepare () ou sqlite3_exec () .
O programa sqlite3 é capaz de mostrar os resultados de uma consulta em 14 formatos diferentes:
- ascii
- box
- csv
- column
- html
- insert
- json
- line
- list
- markdown
- quote
- table
- tabs
- tcl
Você pode usar o comando ponto ".mode" para alternar entre esses formatos de saída. O modo de saída padrão é "lista". No modo de lista, cada linha de um resultado de consulta é gravada em uma linha de saída e cada coluna dentro dessa linha é separada por uma string separadora específica. O separador padrão é um símbolo de barra vertical ("|"). O modo de lista é especialmente útil quando você vai enviar a saída de uma consulta para outro programa (como AWK) para processamento adicional.
sqlite> .mode list
sqlite> select * from tbl1;
hello!|10
goodbye|20
sqlite>
Use o comando de ponto ".separator" para alterar o separador. Por exemplo, para alterar o separador para uma vírgula e um espaço, você pode fazer o seguinte:
sqlite> .separator ", "
sqlite> select * from tbl1;
hello!, 10
goodbye, 20
sqlite>
O próximo comando ".mode" irá redefinir o ".separator" de volta ao seu padrão. Portanto, você precisará repetir o comando ".separator" sempre que alterar os modos, se quiser continuar usando um separador não padrão.
No modo "aspas", a saída é formatada como literais SQL. As strings são colocadas entre aspas simples e as aspas simples internas são evitadas por duplicação. Blobs são exibidos em notação literal de blob hexadecimal (Ex: x'abcd '). Os números são exibidos como texto ASCII e os valores NULL são exibidos como "NULL". Todas as colunas são separadas umas das outras por uma vírgula (ou qualquer caractere alternativo selecionado usando ".separator").
sqlite> .mode quote
sqlite> select * from tbl1;
'hello',10
'goodbye',20
sqlite>
No modo "linha", cada coluna em uma linha do banco de dados é mostrada em uma linha sozinha. Cada linha consiste no nome da coluna, um sinal de igual e nos dados da coluna. Os registros sucessivos são separados por uma linha em branco. Aqui está um exemplo de saída de modo de linha:
sqlite> .mode line
sqlite> select * from tbl1;
one = hello
two = 10
one = goodbye
two = 20
sqlite>
No modo coluna, cada registro é mostrado em uma linha separada com os dados alinhados em colunas. Por exemplo:
sqlite> .mode column
sqlite> select * from tbl1;
one two
-------- ---
hello 10
goodbye 20
sqlite>
No modo "coluna" (e também nos modos "caixa", "tabela" e "redução") a largura das colunas se ajusta automaticamente. Mas você pode sobrescrever isso, fornecendo uma largura mínima para cada coluna usando o comando ".width". Os argumentos para ".width" são inteiros que são o número mínimo de espaços a serem dedicados a cada coluna. Números negativos significam justificados à direita. Assim:
sqlite> .width 12 -6
sqlite> select * from tbl1;
one two
------------ ------
hello! 10
goodbye 20
sqlite>
Uma largura de 0 significa que a largura da coluna é escolhida automaticamente. As larguras das colunas não especificadas tornam-se zero. Conseqüentemente, o comando ".width" sem argumentos redefine todas as larguras das colunas para zero e, portanto, faz com que todas as larguras das colunas sejam determinadas automaticamente.
O modo "coluna" é um formato de saída tabular. Outros formatos de saída tabular são "box", "markdown" e "table":
sqlite> .width
sqlite> .mode markdown
sqlite> select * from tbl1;
| one | two |
|---------|-----|
| hello! | 10 |
| goodbye | 20 |
sqlite> .mode table
sqlite> select * from tbl1;
+---------+-----+
| one | two |
+---------+-----+
| hello! | 10 |
| goodbye | 20 |
+---------+-----+
sqlite> .mode box
sqlite> select * from tbl1;
┌─────────┬─────┐
│ one │ two │
├─────────┼─────┤
│ hello! │ 10 │
│ goodbye │ 20 │
└─────────┴─────┘
sqlite>
Outro modo de saída útil é "inserir". No modo de inserção, a saída é formatada para se parecer com instruções SQL INSERT. Use o modo de inserção para gerar texto que pode ser usado posteriormente para inserir dados em um banco de dados diferente.
Ao especificar o modo de inserção, você deve fornecer um argumento extra que é o nome da tabela a ser inserida. Por exemplo:
sqlite> .mode insert new_table
sqlite> select * from tbl1;
INSERT INTO "new_table" VALUES('hello',10);
INSERT INTO "new_table" VALUES('goodbye',20);
sqlite>
Outros modos de saída incluem "html", "json" e "tcl". Experimente você mesmo para ver o que eles fazem.
6. Consultando o esquema do banco de dados
O programa sqlite3 fornece vários comandos convenientes que são úteis para examinar o esquema do banco de dados. Não há nada que esses comandos façam que não possa ser feito por algum outro meio. Esses comandos são fornecidos puramente como um atalho.
Por exemplo, para ver uma lista das tabelas no banco de dados, você pode inserir ".tables".
sqlite> .tables
tbl1 tbl2
sqlite>
O comando ".tables" é semelhante a definir o modo de lista e, em seguida, executar a seguinte consulta:
SELECT name FROM sqlite_schema
WHERE type IN ('table','view') AND name NOT LIKE 'sqlite_%'
ORDER BY 1
Mas o comando ".tables" faz mais. Ele consulta a tabela sqlite_schema para todos os bancos de dados anexados , não apenas o banco de dados primário. E organiza sua saída em colunas organizadas.
O comando ".indexes" funciona de maneira semelhante para listar todos os índices. Se o comando ".indexes" receber um argumento que é o nome de uma tabela, ele mostrará apenas os índices dessa tabela.
O comando ".schema" mostra o esquema completo para o banco de dados ou para uma única tabela se um argumento tablename opcional for fornecido:
sqlite> .schema
create table tbl1(one varchar(10), two smallint)
CREATE TABLE tbl2 (
f1 varchar(30) primary key,
f2 text,
f3 real
);
sqlite> .schema tbl2
CREATE TABLE tbl2 (
f1 varchar(30) primary key,
f2 text,
f3 real
);
sqlite>
O comando ".schema" é praticamente o mesmo que definir o modo de lista e, em seguida, inserir a seguinte consulta:
SELECT sql FROM sqlite_schema
ORDER BY tbl_name, type DESC, name
Tal como acontece com ".tables", o comando ".schema" mostra o esquema para todos os bancos de dados anexados . Se quiser ver apenas o esquema de um único banco de dados (talvez "principal"), você pode adicionar um argumento a ".schema" para restringir sua saída:
O comando ".schema" pode ser aumentado com a opção "--indent", caso em que tenta reformatar as várias instruções CREATE do esquema para que sejam mais facilmente legíveis por humanos.
O comando ".databases" mostra uma lista de todos os bancos de dados abertos na conexão atual. Sempre haverá pelo menos 2. O primeiro é "principal", o banco de dados original aberto. O segundo é "temp", o banco de dados usado para tabelas temporárias. Pode haver bancos de dados adicionais listados para bancos de dados anexados usando a instrução ATTACH. A primeira coluna de saída é o nome ao qual o banco de dados está anexado e a segunda coluna de resultado é o nome do arquivo externo. Pode haver uma terceira coluna de resultado que será "'r / o'" ou "'r / w'" dependendo se o arquivo de banco de dados é somente leitura ou leitura-gravação. E pode haver uma quarta coluna de resultado mostrando o resultado de sqlite3_txn_state () para aquele arquivo de banco de dados.
O comando de ponto ".fullschema" funciona como o comando ".schema", pois exibe todo o esquema do banco de dados. Mas ".fullschema" também inclui dumps das tabelas de estatísticas "sqlite_stat1", "sqlite_stat3" e "sqlite_stat4", se existirem. O comando ".fullschema" normalmente fornece todas as informações necessárias para recriar exatamente um plano de consulta para uma consulta específica. Ao relatar problemas suspeitos com o planejador de consulta SQLite para a equipe de desenvolvimento do SQLite, os desenvolvedores são solicitados a fornecer a saída ".fullschema" completa como parte do relatório de problemas. Observe que as tabelas sqlite_stat3 e sqlite_stat4 contêm amostras de entradas de índice e, portanto, podem conter dados confidenciais, portanto, não envie o ".fullschema"
7. Redirecionado o I/O
7.1. Gravando resultados em um arquivo
Por padrão, o sqlite3 envia os resultados da consulta para a saída padrão. Você pode alterar isso usando os comandos ".output" e ".once". Basta colocar o nome de um arquivo de saída como um argumento para .output e todos os resultados da consulta subsequente serão gravados nesse arquivo. Ou use o comando .once em vez de .output e a saída será redirecionada apenas para o próximo comando único antes de reverter para o console. Use .output sem argumentos para começar a escrever na saída padrão novamente. Por exemplo:
sqlite> .mode list
sqlite> .separator |
sqlite> .output test_file_1.txt
sqlite> select * from tbl1;
sqlite> .exit
$ cat test_file_1.txt
hello|10
goodbye|20
$
Se o primeiro caractere do nome do arquivo ".output" ou ".once" for um símbolo de barra vertical ("|"), os caracteres restantes serão tratados como um comando e a saída será enviada para esse comando. Isso torna mais fácil canalizar os resultados de uma consulta para algum outro processo. Por exemplo, o comando "open -f" em um Mac abre um editor de texto para exibir o conteúdo que lê da entrada padrão. Portanto, para ver os resultados de uma consulta em um editor de texto, pode-se digitar:
sqlite> .once '|open -f'
sqlite> SELECT * FROM bigTable;
Se os comandos ".output" ou ".once" tiverem um argumento "-e", a saída será coletada em um arquivo temporário e o editor de texto do sistema será chamado nesse arquivo de texto. Assim, o comando ".once -e" obtém o mesmo resultado que ".once '| open -f'", mas com o benefício de ser portátil em todos os sistemas.
Se os comandos ".output" ou ".once" tiverem um argumento "-x", isso fará com que eles acumulem saída como valores separados por vírgulas (CSV) em um arquivo temporário, então invoque o utilitário de sistema padrão para visualizar arquivos CSV (geralmente um programa de planilha) no resultado. Esta é uma maneira rápida de enviar o resultado de uma consulta a uma planilha para facilitar a visualização:
sqlite> .once -x
sqlite> SELECT * FROM bigTable;
7,2 Lendo SQL de um arquivo
No modo interativo, o sqlite3 lê o texto de entrada (instruções SQL ou comandos de ponto ) do teclado. Você também pode redirecionar a entrada de um arquivo ao iniciar o sqlite3, é claro, mas não terá a capacidade de interagir com o programa. Às vezes, é útil executar um script SQL contido em um arquivo, inserindo outros comandos na linha de comando. Para isso, o comando ponto ".read" é fornecido.
O comando ".read" recebe um único argumento que é (normalmente) o nome de um arquivo a partir do qual se lê o texto de entrada.
sqlite> .read myscript.sql
O comando ".read" interrompe temporariamente a leitura do teclado e, em vez disso, obtém sua entrada do arquivo nomeado. Ao chegar ao final do arquivo, a entrada volta para o teclado. O arquivo de script pode conter comandos de ponto, como a entrada interativa comum.
Se o argumento para ".read" começar com "|" então, em vez de abrir o argumento como um arquivo, ele executa o argumento (sem o "|" inicial) como um comando e usa a saída desse comando como sua entrada. Portanto, se você tiver um script que gera SQL, poderá executá-lo diretamente usando um comando semelhante ao seguinte:
sqlite> .read '|myscript.bat'
Tradução livre de: https://www.sqlite.org/cli.html
Retirado em 24/08/2021
