SQL com SQLite: como criar uma tabela, inserir, selecionar e atualizar dados

Os comandos SQL no SQLite que você mais vai usar são quatro: CREATE TABLE cria a tabela, INSERT coloca dados nela, SELECT consulta e UPDATE atualiza.

Se você já usou uma planilha, já entende o básico de um banco SQL. Ele guarda as informações em tabelas, cada coluna é um tipo de informação e cada linha é um registro. SQL quer dizer Structured Query Language, uma linguagem de consulta estruturada, e é com ela que você conversa com o banco. Você pode rodar os exemplos abaixo em qualquer ferramenta que abra um arquivo SQLite, seja o terminal ou um programa com interface, como o Beekeeper Studio.

CREATE TABLE

Para criar uma tabela você escreve CREATE TABLE, o nome da tabela e, entre parênteses, o nome das colunas separados por vírgula. O comando termina com ponto e vírgula.

sqlite
CREATE TABLE cursos (id, nome, aulas);

O nome da tabela pode ser o que você quiser, mas eu recomendo manter em minúsculo e sem caractere especial. Os nomes das colunas também são escolha sua, id, nome e aulas são só os que fazem sentido para uma lista de cursos.

Você pode se perguntar por que as palavras do SQL estão em maiúsculo. É só uma convenção, porque o SQL não diferencia caixa alta de caixa baixa nas palavras-chave, e create table cursos (id, nome, aulas); funciona da mesma forma. Mesmo assim, o padrão no SQL é escrever os comandos em maiúsculo.

Se você rodar o mesmo CREATE TABLE uma segunda vez, o SQLite recusa com o erro table cursos already exists. Ele não cria uma tabela por cima de outra que já tem o mesmo nome, e isso é bom, porque protege os dados que já estão lá.

DROP TABLE

O comando DROP TABLE, seguido do nome da tabela, irá deletar a sua tabela.

sqlite
DROP TABLE cursos;

Ele remove a tabela e todos os dados que estão nela, sem pedir confirmação. Enquanto você está testando, não tem problema nenhum, mas num banco com dados de verdade é o tipo de comando que você roda com muito cuidado, porque não tem volta.

Tipos de Dados

A tabela que criamos funciona, mas as colunas não dizem nada sobre o que elas guardam. Não definimos se id é um número ou se nome é um texto, e também não colocamos nenhuma restrição. Tudo isso é definido no momento da criação da tabela, logo depois do nome de cada coluna. Como a tabela cursos já existe, rode antes o DROP TABLE cursos; e depois crie a tabela de novo, agora com os tipos:

sqlite
CREATE TABLE cursos (
  id INTEGER NOT NULL,
  nome TEXT NOT NULL,
  aulas INTEGER
);

INTEGER é número inteiro e TEXT é texto. O NOT NULL é uma restrição (em inglês, constraint) que significa “não pode ser nulo”. Com essa definição, todo curso inserido precisa ter id e nome, enquanto aulas pode ficar vazio, porque essa coluna não tem o NOT NULL.

Os tipos no SQLite são bem mais flexíveis que em outros bancos. Declarar INTEGER funciona como uma recomendação do tipo da coluna, não como uma trava, e um texto que não parece número ainda pode ser guardado ali.

As quebras de linha e a indentação do exemplo não mudam nada para o SQL. O comando é o mesmo que se estivesse tudo numa linha só, a diferença é que fica muito mais fácil de ler quando a tabela tem várias colunas.

PRAGMA TABLE_INFO

E como saber se a tabela ficou do jeito que você queria? O PRAGMA é uma palavra-chave usada geralmente para configurações do SQLite, e junto com o TABLE_INFO ele mostra a configuração de cada coluna. O nome da tabela vai entre aspas simples, como texto.

sqlite
PRAGMA TABLE_INFO('cursos');

O resultado tem uma linha para cada coluna da tabela:

A coluna type mostra o tipo que você declarou e a notnull mostra 1 quando o NOT NULL está ativo e 0 quando não está, por isso aulas aparece com 0. O dflt_value é o valor padrão da coluna e o pk indica se ela faz parte da chave primária.

INSERT INTO

Com a tabela pronta, o próximo passo é colocar dados nela. Antes, vale conhecer o comando mais básico para ler uma tabela, que vai ser útil para conferir cada inserção.

sqlite
SELECT * FROM cursos;

O comando SELECT irá selecionar dados. O asterisco significa “todas as colunas”, e o FROM diz de qual tabela. Por enquanto ele devolve só o nome das colunas, sem nenhuma linha, porque a tabela está vazia.

Para inserir, você usa INSERT INTO, o nome da tabela, as colunas entre parênteses, a palavra VALUES e os valores, também entre parênteses.

sqlite
INSERT INTO cursos (id, nome, aulas)
VALUES (1, 'HTML', 10);

A ordem das colunas que você escreve é a ordem que os valores precisam seguir. Se você escrevesse (nome, id), os valores teriam que vir como ('HTML', 1). Os números vão sem aspas e o texto vai entre aspas simples.

Agora o SELECT * FROM cursos; mostra uma linha com o curso de HTML. Se você rodar o mesmo INSERT de novo, ele insere outra linha igual, com o mesmo id 1. Mas o id não deveria ser único? Deveria, só que em nenhum momento a tabela foi criada dizendo isso, e o banco não tem como adivinhar. Garantir um id único é outra restrição, que você define na criação da tabela quando aprende sobre chave primária.

A restrição que definimos foi o NOT NULL, e dá para ver ela funcionando tentando inserir um curso sem id:

sqlite
INSERT INTO cursos (nome, aulas)
VALUES ('CSS', 12);

O SQLite recusa e devolve NOT NULL constraint failed: cursos.id. A mensagem diz exatamente qual restrição falhou e em qual coluna, então quando aparecer esse erro, procure a coluna que ficou sem valor.

Também dá para inserir vários registros de uma vez, sem repetir o nome da tabela e das colunas. Basta separar cada grupo de valores com vírgula depois do VALUES.

sqlite
INSERT INTO cursos (id, nome, aulas)
VALUES
  (2, 'CSS', 12),
  (3, 'JavaScript', 15);

Se esquecer a vírgula que separa os dados o banco simplesmente não executa o comando.

Aspas Simples e Aspas Duplas

No SQL as aspas não são intercambiáveis como em muitas linguagens de programação. A aspa simples serve para strings, que são os textos que você está inserindo ou comparando, como 'HTML'. A aspa dupla serve para identificadores, que são os nomes que você deu para as coisas no banco, como o nome da tabela cursos e o nome das colunas id, nome e aulas.

Até agora os identificadores foram escritos sem aspas e nada deu errado, porque as aspas duplas não são obrigatórias. O problema aparece quando o nome que você escolhe é uma palavra-chave do SQL. Tente criar uma tabela chamada insert:

sqlite
-- erro, insert é uma palavra-chave do SQL
CREATE TABLE insert (id);

-- funciona, as aspas duplas dizem que é um identificador
CREATE TABLE "insert" ("id");

O primeiro comando dá erro porque INSERT é a palavra que o SQL usa para inserir dados, e o banco não aceita ela solta como nome. Com aspas duplas a tabela é criada, mas a partir daí todo comando que usar ela precisa das aspas também, inclusive o SELECT * FROM "insert";.

Por isso a boa prática é colocar sempre os identificadores entre aspas duplas, mesmo quando não é obrigatório. A maioria dos editores ainda pinta o identificador com uma cor diferente quando ele está entre aspas, o que ajuda na leitura. Os números continuam sem aspas, porque 1 e '1' são coisas diferentes, um é número e o outro é um texto que tem o caractere 1.

sqlite
INSERT INTO "cursos" ("id", "nome", "aulas")
VALUES (1, 'HTML', 10);

Sobre a forma de quebrar as linhas, ela também é escolha sua, e o que importa é manter a consistência no projeto todo. Algumas ferramentas, como o Beekeeper, têm uma opção de formatar a query que faz essa indentação automaticamente.

SELECT: Colunas, LIMIT e WHERE

Os resultados dos exemplos daqui para baixo contam com uma tabela que tem exatamente três cursos.

sqlite
DROP TABLE "cursos";

CREATE TABLE "cursos" (
  "id" INTEGER NOT NULL,
  "nome" TEXT NOT NULL,
  "aulas" INTEGER
);

INSERT INTO "cursos" ("id", "nome", "aulas")
VALUES
  (1, 'HTML', 10),
  (2, 'CSS', 12),
  (3, 'JavaScript', 15);

Com a tabela de três cursos (HTML com 10 aulas, CSS com 12 e JavaScript com 15), o SELECT * devolve todas as colunas de todas as linhas. O asterisco não vai entre aspas, porque ele não é um identificador, ele faz parte da sintaxe do comando. Quando você não precisa de todas as colunas, o ideal é escrever só as que você deseja, separadas por vírgula.

sqlite
SELECT "id", "nome" FROM "cursos";

Pedir só o que vai usar faz diferença na otimização, porque o banco deixa de trazer dados que ninguém vai ler.

Do jeito que está, o SELECT traz todas as linhas da tabela, e se ela tiver um milhão de linhas ele traz um milhão. Para limitar a consulta existe o LIMIT, seguido da quantidade máxima de linhas.

sqlite
SELECT * FROM "cursos" LIMIT 2;

Eu uso bastante um LIMIT 100 quando quero só dar uma olhada rápida numa tabela grande, sem esperar carregar tudo.

Para filtrar as linhas por uma condição você usa o WHERE. Os sinais de comparação são os mesmos que você já conhece da matemática e da programação: =, >, <, >=, <= e != (diferente de).

sqlite
SELECT * FROM "cursos"
WHERE "id" = 2;
-- 2 | CSS | 12

SELECT * FROM "cursos"
WHERE "nome" = 'JavaScript';
-- 3 | JavaScript | 15

SELECT * FROM "cursos"
WHERE "id" >= 1;
-- todos os três cursos

Na comparação de texto, maiúscula e minúscula fazem diferença. WHERE "nome" = 'CSS' encontra o curso, mas WHERE "nome" = 'css' não retorna nada, porque para o banco os dois textos são diferentes. É o contrário das palavras-chave, que você pode escrever como quiser (não são case sensitive).

AND e OR

Podemos combinar mais de uma condição com AND ou OR, e eles funcionam como o “e” e o “ou” de qualquer linguagem de programação. No AND todas as condições precisam ser verdadeiras para a linha voltar. No OR basta uma delas ser verdadeira.

sqlite
SELECT * FROM "cursos"
WHERE "id" = 1
  AND "aulas" > 9;
-- 1 | HTML | 10

SELECT * FROM "cursos"
WHERE "id" = 1
  AND "aulas" > 11;
-- nenhuma linha

O segundo não retorna nada porque o curso de id 1 tem 10 aulas, e a segunda condição é falsa para ele. Trocando o AND por OR, o resultado muda bastante:

sqlite
SELECT * FROM "cursos"
WHERE "id" = 1
  OR "aulas" > 11;
-- 1 | HTML | 10
-- 2 | CSS | 12
-- 3 | JavaScript | 15

O HTML volta porque o id dele é 1, e isso já é suficiente. O CSS e o JavaScript têm outro id, mas voltam também porque têm mais de 11 aulas. Cada linha precisa passar em pelo menos uma das condições, e as três passam.

O WHERE também se combina com o LIMIT. No exemplo abaixo o banco procura os cursos com id diferente de 1 e, assim que encontra o primeiro, para de procurar e devolve só ele, que é o CSS.

sqlite
SELECT * FROM "cursos"
WHERE "id" != 1
LIMIT 1;
-- 2 | CSS | 12

UPDATE e o cuidado com o WHERE

Para alterar dados que já estão na tabela você usa o UPDATE, o nome da tabela e o SET, com a coluna e o novo valor. Digamos que o curso de HTML passou a se chamar HTML e CSS:

sqlite
UPDATE "cursos"
SET "nome" = 'HTML e CSS'
WHERE "id" = 1;

O WHERE é a parte mais importante desse comando. Sem ele, o UPDATE altera todas as linhas da tabela, e os três cursos passariam a se chamar HTML e CSS. O banco não pergunta se você tem certeza, ele executa de uma vez códigos “destrutivos” como esse.

Para a condição, use uma coluna que identifique um único registro. Daria para escrever WHERE "nome" = 'HTML', mas o id costuma ser mais garantido de ser único, e por isso é ele que eu uso. Como a nossa tabela ainda não tem uma restrição de id único, isso depende de você não repetir ids na hora de inserir.

Para atualizar mais de uma coluna de uma vez, separe cada coluna = valor com vírgula, sem repetir o SET. O exemplo volta o nome para HTML e muda o total de aulas para 14:

sqlite
UPDATE "cursos"
SET "nome" = 'HTML', "aulas" = 14
WHERE "id" = 1;

O UPDATE é um comando simples, e justamente por isso é fácil rodar ele no automático e esquecer o WHERE. O mesmo vale para o DELETE, que apaga linhas e segue a mesma lógica de condição. Quase sempre você quer mexer em um registro só, por isso sempre tenha cuidado com esses dois comandos e confira se o WHERE está lá antes de executar, porque sem ele a tabela inteira muda.

Com esses quatro comandos você já monta uma tabela, enche de dados, consulta e corrige, e o próximo passo natural é dar a ela restrições mais fortes, como a chave primária, para que o banco recuse um id repetido sem depender da sua atenção.