A “tabela”, formada por linhas e colunas, é a unidade padrão de informação em um banco de dados relacional e a estrutura do comando para a criação de uma tabela é:

CREATE TABLE nome_tabela ( nome_coluna domínio, ..., [, restrições] );

O comando CREATE TABLE necessita de um nome para a tabela (nome_tabela) e os nomes das colunas (nome_coluna), separados por vírgulas, que vão compor a tabela seguidos dos “domínios”.

O domínio significa o “tipo” do dado que será armazenado na coluna que pode ser: integer, real, text, blob ou null. (Fonte: Datatypes In SQLite)

E finalmente uma lista de “restrições” (constraints) também separada por vírgulas que visa manter a consistência (ou integridade) dos dados. As restrições podem ser: (Fonte: O que é uma Constraint?)

Exemplo de criação de uma tabela:

A coluna “id” é declarada para ter o tipo integer e a restrição de ser uma chave primária. Uma chave primária (PRIMARY KEY) com o tipo inteiro (integer) transforma a coluna em uma coluna de “autoincremento”.

A restrição PRIMARY KEY é uma combinação das restrições UNIQUE e NOT NULL.

A coluna nome é declarada como sendo do tipo text e tem duas restrições: NOT NULL (não nulo), COLLATE e NOCASE.

A restrição COLLATE permite definir como os campos do tipo text são comparados ao longo de uma coluna. E a associação com a restrição NOCASE permite impedir nomes semelhantes em uma mesma coluna. Por exemplo, se for inserido o contato 'Jerry' pela linha de comando no SQLite:

Não será possível inserir outro contato com o nome 'JERRY':

  sqlite> insert into agenda (nome, fone) values ('JERRY', '555-1212');
insert into agenda (nome, fone) values ('JERRY', '555-1212');
Error: UNIQUE constraint failed: agenda.nome, agenda.fone

Mas a inserção de 'JERRY' seria possível se não fosse inserido a restrição NOCASE no campo nome.

E a coluna fone é do tipo text e tem duas restrições: NOT NULL e DEFAULT. Isso significa que esse campo não pode ficar “vazio” (NULL) e se não for inserido um dado para esse campo o banco de dados insere automaticamente a string 'UNKNOWN'.

Depois disso, há uma restrição UNIQUE a qual define que nenhum valor repetido poderá ser fornecido para os campos nome e fone.

Para visualizar a estrutura da tabela basta digitar o comando .schema:

sqlite> .schema
.schema
CREATE TABLE agenda ( id integer PRIMARY KEY,
nome text NOT NULL COLLATE NOCASE,
fone text NOT NULL DEFAULT 'UNKNOWN',
UNIQUE (nome,fone) );

Para alterar a estrutura da tabela e inserir novos campos existe o comando alter table:

sqlite> ALTER TABLE agenda 
   ...> ADD COLUMN email text NOT NULL DEFAULT '' COLLATE NOCASE;

sqlite> .schema

CREATE TABLE agenda ( id integer PRIMARY KEY,
nome text NOT NULL COLLATE NOCASE,
fone text NOT NULL DEFAULT 'UNKNOWN', email text NOT NULL DEFAULT '' COLLATE NOCASE,
UNIQUE (nome,fone) );

Agora que criamos as tabelas vamos analisar como inserir dados em uma tabela com o comando INSERT.

  CREATE TABLE foods(
  id integer PRIMARY KEY,
  type_id integer,
  name text );

sqlite> INSERT INTO foods (name, type_id) VALUES ('Cinnamon Bobka', 1);
sqlite> SELECT * FROM foods WHERE name='Cinnamon Bobka';
id          type_id     name          
----------  ----------  --------------
13          1           Cinnamon Bobka
413         1           Cinnamon Bobka

sqlite> INSERT INTO foods (name) VALUES ('Cinnamon Bobka');
sqlite> SELECT * FROM foods WHERE name='Cinnamon Bobka';
id          type_id     name          
----------  ----------  --------------
13          1           Cinnamon Bobka
413         1           Cinnamon Bobka
414         NULL        Cinnamon Bobka

sqlite> INSERT INTO foods VALUES(NULL, 1, 'Chocolate Bobka');
sqlite> SELECT * FROM foods WHERE name like '%Bobka';
id          type_id     name           
----------  ----------  ---------------
10          1           Chocolate Bobka
13          1           Cinnamon Bobka 
413         1           Cinnamon Bobka 
414         NULL        Cinnamon Bobka 
415         1           Chocolate Bobka

  sqlite> .schema foods
  CREATE TABLE foods(
  id integer PRIMARY KEY,
  type_id integer,
  name text );

  sqlite> SELECT * FROM foods WHERE type_id=NULL;
  sqlite>

  sqlite> select max(id) from foods;
  max(id)   
  ----------
  415       
  sqlite> select last_insert_rowid();
  last_insert_rowid()
  -------------------
  415

sqlite> INSERT INTO foods
   ...> VALUES (NULL,
   ...> (SELECT id FROM food_types WHERE name='Bakery'),
   ...> 'Blackberry Bobka');

sqlite> SELECT * FROM foods WHERE name like '%Bobka';
id          type_id     name           
----------  ----------  ---------------
10          1           Chocolate Bobka
13          1           Cinnamon Bobka 
413         1           Cinnamon Bobka 
414         NULL        Cinnamon Bobka 
415         1           Chocolate Bobka
416         1           Blackberry Bobk

sqlite> INSERT INTO foods
   ...> SELECT null, type_id, name FROM foods
   ...> WHERE name LIKE '%Bobka';

sqlite> SELECT * FROM foods WHERE name like '%Bobka';
id          type_id     name           
----------  ----------  ---------------
10          1           Chocolate Bobka
13          1           Cinnamon Bobka 
413         1           Cinnamon Bobka 
414         NULL        Cinnamon Bobka 
415         1           Chocolate Bobka
416         1           Blackberry Bobk
417         1           Chocolate Bobka
418         1           Cinnamon Bobka 
419         1           Cinnamon Bobka 
420         NULL        Cinnamon Bobka 
421         1           Chocolate Bobka
422         1           Blackberry Bobk

sqlite> CREATE TABLE foods_copy (id int, type_id int, name text);
sqlite> INSERT INTO foods_copy SELECT * FROM foods;
sqlite> SELECT count(*) FROM foods;
count(*)  
----------
422       
sqlite> SELECT count(*) FROM foods_copy;
count(*)  
----------
422 

Como dissemos anteriormente o objetivo de um banco de dados é armazenar dados e disponibilizar ferramentas para a consulta “seletiva” de dados com base em “critérios específicos de filtragem”.

A linguagem SQL possui o comando SELECT para a realização de consultas, o qual permite: coletar, combinar, comparar e filtrar os dados organizados nas tabelas de um banco de dados.

O comando SELECT em sua forma mais simples tem a seguinte sintaxe:

SELECT nome_da_coluna[, ...] FROM nome_da_tabela

Figura P.3. Exemplo de uso do comando SELECT (Fonte: SQL WHERE Clause - Things beginners must know)

A variável nome_da_coluna define a(s) coluna(s) da tabela nome_da_tabela.

Por exemplo, a tabela foods possui 3 colunas (id, type_id, e name):

  sqlite> .schema foods
  CREATE TABLE foods(
  id integer primary key,
  type_id integer,
  name text );

O comando SELECT na declaração seguinte filtra apenas as colunas id e name da tabela foods:

sqlite> SELECT id, name FROM foods;
id          name      
----------  ----------
1           Bagels    
2           Bagels, ra
3           Bavarian C
4           Bear Claws
5           Black and 
6           Bread (wit
7           Butterfing
8           Carrot Cak
9           Chips Ahoy
10          Chocolate
...

Essa declaração gera uma saída com 422 linhas mas exibimos apenas as 10 primeiras.

Para selecionar “todas” as colunas da tabela basta substituir o(s) nome(s) da(s) coluna(s) pelo caracter “*”:

  sqlite> SELECT * FROM foods;
id          type_id     name      
----------  ----------  ----------
1           1           Bagels    
2           1           Bagels, ra
3           1           Bavarian C
4           1           Bear Claws
5           1           Black and 
6           1           Bread (wit
7           1           Butterfing
8           1           Carrot Cak
9           1           Chips Ahoy
10          1           Chocolate 
...

A declaração “SELECT nome_da_coluna[, ...] FROM nome_da_tabela” permite filtrar “colunas” de uma tabela.

Figura P.4. Seleção da coluna “id” de uma tabela com 5 linhas.

E como podemos filtrar “linhas”?

Essa operação pode ser feita com a cláusula restritiva WHERE com a seguinte sintaxe:

SELECT nome_da_coluna[, ...] FROM nome_da_tabela [WHERE expressão_de_restrição]

Figura P.5. Seleção da linha onde name=“Sopa”.

Figura P.6. Retorno da consulta.

A SQLite aplica a cláusula WHERE a cada linha da tabela que é obtida pela aplicação da cláusula FROM.

Explicando de outra forma.

O argumento de WHERE é um “predicado lógico”. Um predicado, no sentido mais simples, é apenas uma afirmação sobre algo. Considere a seguinte declaração:

A flor (sujeito) é amarela e tem pétalas pequenas (predicado).

A flor é o sujeito, e o predicado é formado por duas afirmações (a cor é amarela e as pétalas são pequenas) que podem ser verdardeiras ou falsas.

O sujeito em uma cláusula WHERE é uma linha da tabela. A linha é o sujeito lógico, e a cláusula WHERE é o predicado lógico.

Cada linha na qual a afirmação é avaliada como verdadeira é incluída (ou selecionada) como parte do resultado, e cada linha em que a afirmação é falsa é excluída. Assim, a proposição sobre a flor traduzida para uma declaração relacional ficaria:

SELECT * FROM flores WHERE cor='amarela' and pétala='pequena';

O banco de dados aplica em cada linha da tabela flores (o sujeito) a cláusula WHERE (predicado) para formar uma proposição lógica:

Esta linha tem cor='amarela' e pétala='pequena'.

Se for verdadeira será incluída na tabela resultante, senão será excluída.

Como já dito anteriormente, o comando SELECT inclui a cláusula WHERE que implementa uma operação de “restrição”:

SELECT nome_da_coluna[, ...] FROM nome_da_tabela [WHERE expressão_de_restrição]

Uma “expressão” é uma frase do código que usa um operador para modificar uma constante, o nome da coluna ou o produto de uma outra expressão para calcular um valor que o programa usa para controlar a seleção dos dados de um banco de dados.

Vamos fazer alguns comentários sobre dois elementos que compõem uma expressão: as constantes e os operadores.

  sqlite> SELECT date();
date()    
----------
2017-02-06
sqlite> SELECT time();
time()    
----------
15:52:32
qlite> SELECT datetime();
datetime()         
-------------------
2017-02-06 16:06:19
sqlite> SELECT current_date;
current_date
------------
2017-02-06  
sqlite> SELECT current_time;
current_time
------------
15:52:58    
sqlite> SELECT current_timestamp;
current_timestamp  
-------------------
2017-02-06 16:07:12

sqlite> .headers on
sqlite> .mode column
sqlite> .nullvalue NULL
sqlite> CREATE TABLE data_hora ( id int,
   ...> data NOT NULL DEFAULT current_date,
   ...> hora NOT NULL DEFAULT current_time,
   ...> data_hora NOT NULL DEFAULT current_timestamp );
sqlite> INSERT INTO data_hora (id) values (1);
sqlite> INSERT INTO data_hora (id) values (2);
sqlite> SELECT * FROM data_hora;
id          data        hora        data_hora          
----------  ----------  ----------  -------------------
1           2017-02-07  11:28:51    2017-02-07 11:28:51
2           2017-02-07  11:29:10    2017-02-07 11:29:10
sqlite> 

P.1.4.2. Operadores

Um Operador recebe um ou mais valores de entrada e produz um valor de saída. Ou seja, executa uma operação produzindo algum tipo de resultado.

Quando operam sobre apenas um valor de entrada (ou operando) são chamados de “unários”. Os operadores “binários” recebem dois valores de entrada, e os operadores ternários recebem três operandos.

Os operadores podem ser encadeados em expressões onde a saída de um é a entrada de outro conforme o diagrama da figura P.1.

Figura P.7. Encadeamente de operadores unário, binário e ternário formando uma expressão. (Fonte: The Definitive Guide to SQLite, 2010)

Os operadores são usados para especificar condições em uma declaração SQLite e servir como conjunção para “encadear” várias condições em uma declaração. (Fonte: What is an Operator in SQLite?)

Basicamente podem ser de dois tipos: aritméticos e lógicos. Um operador aritmético produz uma saída numérica, ao passo que um operador lógico produz um resultado lógico (TRUE, FALSE ou NULL) e permitem formar “expressões lógicas”.

Operadores unários suportados pela SQLite: “-”, “+”, “~” e “NOT”.

Operadores binários suportados pela SQLite em ordem decrescente de precedência: (Fonte: SQL As Understood By SQLite)

• || (concatenação de string)

• “*” (operador aritmético multiplicação), “/” (operador aritmético divisão) e “%” (operador aritmético módulo - resto da divisão)

• “+” (operador aritmético soma) e “–” (operador aritmético subtração)

• “≪” (operador bit-a-bit(bitwise) deslocamento à esquerda) , “≫” (operador bit-a-bit(bitwise) deslocamento à direita), “&” (operador lógico and) e “|” (operador lógico ou)

• “<” (operador relacional menor que), “<=” (operador relacional menor ou igual que), “>” (operador relacional maior que) e “>=” (operador relacional maior ou igual que)

• “=” (operador relacional igual), “==” (operador relacional igual), “!=” (operador relacional não igual (diferente)), “<>” (operador relacional não igual (diferente)), “IS” (operador lógico igual), “IS NOT” (operador lógico desigual), “IN” (operador lógico em), “LIKE” (operador relacional correspondência de string) e “GLOB” (operador relacional correspondência de nome de arquivo)

• “AND” (operador lógico and)

• “OR” (operador lógico ou)

Como já dito anteriormente, um operador aritmético produz uma saída numérica, ao passo que um operador lógico produz um resultado lógico (TRUE, FALSE ou NULL) e permitem formar “expressões lógicas”.

No SQLite FALSE é representado pelo número “0” enquanto TRUE é representado por qualquer outro número diferente de 0.

sqlite> SELECT 1 > 2;
1 > 2     
----------
0         
sqlite> SELECT 1 < 2;
1 < 2     
----------
1         
sqlite> SELECT 1 = 2;
1 = 2     
----------
0         

Exemplo de uso do operador lógico:

  qlite> SELECT * FROM foods WHERE name='JujyFruit' AND type_id=9;
id          type_id     name      
----------  ----------  ----------
244         9           JujyFruit 
sqlite> SELECT * FROM foods WHERE id >= 1 AND id <= 10;
id          type_id     name      
----------  ----------  ----------
1           1           Bagels    
2           1           Bagels, ra
3           1           Bavarian C
4           1           Bear Claws
5           1           Black and 
6           1           Bread (wit
7           1           Butterfing
8           1           Carrot Cak
9           1           Chips Ahoy
10          1           Chocolate 

O operador relacional LIKE é usado apenas com strings e serve para verificar se uma string coincide com um determinado padrão de strings. Por exemplo, para selecionar todas as linhas em alimentos cujos nomes começam com a letra J, você pode fazer o seguinte:

sqlite> SELECT id, name FROM foods WHERE name LIKE 'J%';
id          name      
----------  ----------
156         Juice box 
236         Jucyfruit 
243         Jello with
244         JujyFruit 
245         Junior Min
370         Jambalaya 

O caracter curinga “%” no padrão corresponde a qualquer sequência de zero ou mais caracteres na string.

O SQLite reconhece dois caracteres curingas:

• % - coincide zero ou mais caracteres

• _ - coincide com qualquer caractere único na string

Dica

Comparando com o shell do Linux o “%” é equivalente ao “*” e o “_” é equivalente ao “?”.

O operador relacional GLOB é muito semelhante em comportamento ao operador LIKE. Sua principal diferença é que ele se comporta de forma semelhante à semântica do “globbing” no Linux. Isso significa que ele usa os caracteres universais associados ao globbing de arquivos, como “*” e “?” (wildcards) e discrimina entre maiúsculas e minúsculas.

O exemplo a seguir mostra o uso do glob:

sqlite> SELECT id, name FROM foods
   ...> WHERE name GLOB 'Pine*';
id          name      
----------  ----------
205         Pineapple 
258         Pineapple

O operador BETWEEN é logicamente equivalente a um par de comparações. "x BETWEEN y AND z" é equivalente a "x >= y AND x <= z", exceto que com BETWEEN, a expressão x só é avaliada uma vez.

sqlite> SELECT * FROM foods WHERE id BETWEEN 10 AND 20;
id          type_id     name           
----------  ----------  ---------------
10          1           Chocolate Bobka
11          1           Chocolate Eclai
12          1           Chocolate Cream
13          1           Cinnamon Bobka 
14          1           Cinnamon Swirls
15          1           Cookie         
16          1           Crackers       
17          1           Cupcake        
18          1           Cupcakes       
19          1           Devils Food Cak
20          1           Dinky Donuts 

O operador IN verifica se o valor de uma coluna coincide com qualquer valor em uma lista.

Exemplo de uso:

 sqlite> SELECT * FROM foods
   ...> WHERE name IN ('Brocolli', 'Carrots', 'Oranges');
id          type_id     name      
----------  ----------  ----------
202         8           Oranges   
385         15          Brocolli  
387         15          Carrots

O operador IS não é exatamente um operador, mas serve especificamente para verificar se uma determinada coluna está definida para NULL. O NULL não é um valor, mas sim um “flag” (marca) que indica a ausência de um valor. Este operador verifica se uma variável é ou não NULL retornando TRUE ou FALSE conforme o caso.

Exemplo de uso:

sqlite> SELECT id, type_id, name FROM foods
   ...> WHERE type_id IS NULL;
id          type_id     name          
----------  ----------  --------------
414         NULL        Cinnamon Bobka
420         NULL        Cinnamon Bobka

Até o momento os dados selecionados a partir do banco de dados apareceram na ordem em que foram inseridos. Porém o comando SELECT inclui uma cláusula ORDER BY com a qual é possível classificar a saída de uma seleção na ordem crescente ou decrescente, com a sintaxe:

Exemplo de uso:

sqlite> .width 5 5 25
sqlite> SELECT * FROM foods
   ...> WHERE name like 'Ba%'
   ...> ORDER BY type_id DESC;
id     type_  name                     
-----  -----  -------------------------
382    15     Baked Beans              
383    15     Baked Potato w/Sour Cream
327    12     Bacon Club (no turkey)   
274    10     Bacon                    
192    8      Banana                   
108    5      Banana Yogurt            
87     4      Barbeque Sauce           
1      1      Bagels                   
2      1      Bagels, raisin           
3      1      Bavarian Cream Pie

A clausula GROUP BY permite organizar a saída de um comando SELECT em grupos de dados idênticos.

A sintaxe consiste em colocar após “GROUP BY” os nomes de uma ou mais colunas separadas por vírgulas. O interpretador do SQLite irá agrupar a saída segundo os valores das colunas, na ordem em que foram nomeadas.

Exemplo de uso:

qlite> SELECT type_id FROM foods GROUP BY type_id;
type_id   
----------
NULL      
1         
2         
3         
.
.
.
15

GROUP BY é usada entre a cláusula WHERE e o comando SELECT, e divide a saída de WHERE em grupos de linhas que possuem um valor (ou valores) em comum para uma coluna (ou colunas) específica. E esses grupos de linhas são então repassados para o comando SELECT.

No exemplo anterior, existem 15 diferentes tipos de alimentos (type_id varia de 1 a 15) e por isso GROUP BY organiza todas as linhas da tabela foods em 15 grupos segundo o type_id. Em seguida o comando SELECT recebe cada grupo, extrai o valor comum de type_id e coloca em uma linha separada gerando por isso uma saída de 15 linhas, uma para cada grupo conforme o diagrama da figura P.8.

Figura P.8. Estrutura de funcionamento da cláusula GROUP BY para o exemplo anterior. (Fonte: The Definitive Guide to SQLite, 2010)

Exibindo o número de elementos (linhas) em cada grupo:

sqlite> SELECT type_id, count(*) FROM foods GROUP BY type_id;
type_id     count(*)  
----------  ----------
NULL        2         
1           55        
2           15        
3           23        
4           22        
5           17        
6           4         
7           60        
8           23        
9           61        
10          36        
11          16        
12          23        
13          14        
14          19        
15          32

Nesse último exemplo a função count(*) foi usada 15 vezes (uma para cada grupo), como mostra a figura P.9.

Figura P.9. Aplicação da função count(*) associada à cláusula GROUP BY. (Fonte: The Definitive Guide to SQLite, 2010)

Sem o uso do GROUP BY essa informação poderia ser obtida fazendo 15 consultas:

qlite> SELECT count(*) FROM foods WHERE type_id=null;
count(*)  
----------
0         
sqlite> SELECT count(*) FROM foods WHERE type_id IS null;
count(*)  
----------
2         
sqlite> SELECT count(*) FROM foods WHERE type_id=1;
count(*)  
----------
55        
sqlite> SELECT count(*) FROM foods WHERE type_id=2;
count(*)  
----------
15
.
.
.
sqlite> SELECT count(*) FROM foods WHERE type_id=15;
count(*)  
----------
32  

A cláusula GROUP BY faz o agrupamento dos registros e transfere o resultado para o comando SELECT, mas também é possível fazer uma “filtragem” desses grupos antes de transferir para o comando SELECT.

Isso pode ser feito com o predicado HAVING.

Por exemplo, vamos refazer a consulta do exemplo anterior mas dessa vez vamos selecionar apenas os grupos de alimentos com menos de 20 itens:

sqlite> SELECT type_id, count(*) FROM foods
   ...> GROUP BY type_id HAVING count(*) < 20;
type_id     count(*)  
----------  ----------
NULL        2         
2           15        
5           17        
6           4         
11          16        
13          14        
14          19

O predicado HAVING é aplicado ao resultado da cláusula GROUP BY e faz uma filtragem análoga á filtragem da cláusula WHERE sobre a saída da cláusuala FROM.

A diferença é que o predicado da cláusula WHERE é expresso em termos de valores individuais de cada linha enquanto que o predicado de HAVING é expresso em termos de valores “agregados”.

Neste exemplo HAVING aplica o predicado count(*)<20 a todos os grupos, e somente os grupos que satisfazem a essa condição são passados para o comando SELECT conforme o diagrama da figura P.10.

Figura P.10. Aplicação da função count(*) associada à cláusula GROUP BY e HAVING. (Fonte: The Definitive Guide to SQLite, 2010)

Resumindo, a sintaxe de uma consulta usando GROUP BY, HAVING e ORDER BY é: (Fonte: SQLite - HAVING Clause)

Até o momento as consultas foram feitas a partir de uma única tabela, mas uma grande vantagem do comando SELECT é a possibilidade de usar os dados de duas ou mais tabelas para construir uma nova tabela.

Para usar mais de uma tabela em uma instrução SELECT, temos que seguir a seguinte sintaxe:

Por exemplo, a tabela foods tem uma coluna food_types, e os valores dessa coluna correspondem aos valores da coluna id na tabela food_types.

sqlite> .schema
CREATE TABLE episodes (
  id integer primary key,
  season int,
  name text );
CREATE TABLE foods(
  id integer primary key,
  type_id integer,
  name text );
CREATE TABLE foods_episodes(
  food_id integer,
  episode_id integer );
CREATE TABLE food_types(
  id integer primary key,
  name text );
CREATE TABLE foods_copy (id int, type_id int, name text);

Portanto existe um “relacionamento” entre as duas tabelas. Qualquer valor na coluna foods.type_id deve ter um valor correspondente na coluna food_types.id, que por sinal é a “CHAVE PRIMÁRIA” (PRIMARY KEY) da tabela food_types.

Devido a essa correspondência a coluna food_types.id é chamada de “CHAVE ESTRANGEIRA” (FOREIGN KEY), ou seja, contém (ou se refere) a uma “CHAVE PRIMÁRIA” (PRIMARY KEY) de outra tabela.

Uma “CHAVE ESTRANGEIRA” (FOREIGN KEY) em uma tabela aponta para uma “CHAVE PRIMÁRIA” (PRIMARY KEY) em outra tabela.

Esta correspondência pode ser usada para combinar as tabelas foods e food_types e produzir uma nova tabela com informações mais detalhadas tais como o nome do tipo de comida (food_types.name) para cada alimento na tabela foods.

Isso pode ser feito com os comandos:

sqlite> SELECT foods.name, food_types.name
   ...> FROM foods, food_types
   ...> WHERE foods.type_id = food_types.id;
name        name      
----------  ----------
Bagels      Bakery    
Bagels, ra  Bakery    
.
.
.
Veggie bur  Vegetables
Wax Beans   Vegetables
Cinnamon B  Bakery    
Chocolate   Bakery    
Blackberry  Bakery    
Chocolate   Bakery    
Cinnamon B  Bakery    
Cinnamon B  Bakery    
Chocolate   Bakery    
Blackberry  Bakery    

Para limitar a exibição a 10 registros:

sqlite> .width 25 10 --Para definir a largura das colunas
sqlite> SELECT foods.name, food_types.name
   ...> FROM foods, food_types
   ...> WHERE foods.type_id = food_types.id LIMIT 10;
name                       name      
-------------------------  ----------
Bagels                     Bakery    
Bagels, raisin             Bakery    
Bavarian Cream Pie         Bakery    
Bear Claws                 Bakery    
Black and White cookies    Bakery    
Bread (with nuts)          Bakery    
Butterfingers              Bakery    
Carrot Cake                Bakery    
Chips Ahoy Cookies         Bakery    
Chocolate Bobka            Bakery  

A cláusula “foods.type_id = food_type.id” é a “expressão de junção” que especifica como as tabelas serão reunidas.

Para fazer a junção o banco de dados localiza as linhas correspondentes e para cada linha na primeira tabela o banco de dados localiza todas as linhas na segunda tabela para incluir na nova tabela. Portanto, neste exemplo a cláusula FROM criou uma nova tabela pela junção das linhas das duas tabelas originais conforme o diagrama da figura P.11.

Figura P.11. Diagrama esquemático da junção de duas tabelas. (Fonte: The Definitive Guide to SQLite, 2010)

O banco de dados SQLite suporta seis diferentes tipos junções (ou uniões): junção interna, junção externa, junção de produto cartesiano, left outer join, right outer join e full outer join. O exemplo anterior ilustra a “junção interna”, que é a mais comum.

Uma junção interna utiliza uma operação da teoria dos conjuntos chamada “interseção” para identificar os elementos que existem em ambos os conjuntos.

A figura P.12 mostra que a interseção do conjunto {1, 2, 8, 9} com o conjunto {1, 3, 5, 8} é o conjunto {1, 8}.

Figura P.12. Diagrama esquemático da operação de interseção entre dois conjuntos. (Fonte: The Definitive Guide to SQLite, 2010)

Esse é o processo envolvido em uma junção interna, mas os conjuntos em uma junção são elementos comuns das colunas relacionadas. Considere que o conjunto da esquerda na figura P.12 representa os valores na coluna foods.type_id e o conjunto da direita representa os valores na coluna food_types.id. Após definidas as colunas correspondentes, uma junção interna encontra as linhas de ambos os lados que contêm valores semelhantes e as combina para formar as linhas da tabela resultante como mostra o diagrama da figura P.13.

Figura P.13. Diagrama esquemático da operação de junçao interna. (Fonte: The Definitive Guide to SQLite, 2010)

A atualização dos registros é feita com o comando UPDATE, o qual permite a modificação de uma ou mais colunas dentro de uma ou mais linhas em uma tabela.

Sintaxe do comando UPDATE:

UPDATE nome_da_tabela SET nome_da_coluna = expressão[, nome_da_coluna = expressão] [WHERE expressão]

A cláusula WHERE usa uma “expressão” (ou predicado) para identificar as linhas que devem ser modificadas.

Exemplo de uso:

sqlite> .width 5 5 25
sqlite> SELECT * FROM foods WHERE name LIKE 'Chocolate%';
id     type_  name                     
-----  -----  -------------------------
10     1      Chocolate Bobka          
11     1      Chocolate Eclairs        
12     1      Chocolate Cream Pie      
222    9      Chocolates, box of       
223    9      Chocolate Chip Mint      
224    9      Chocolate Covered Cherrie
415    1      Chocolate Bobka          
417    1      Chocolate Bobka          
421    1      Chocolate Bobka          
sqlite> UPDATE foods SET name='CHOCOLATE BOBKA'
   ...> WHERE name = 'Chocolate Bobka';
sqlite> SELECT * FROM foods WHERE name LIKE 'Chocolate%';
id     type_  name                     
-----  -----  -------------------------
10     1      CHOCOLATE BOBKA          
11     1      Chocolate Eclairs        
12     1      Chocolate Cream Pie      
222    9      Chocolates, box of       
223    9      Chocolate Chip Mint      
224    9      Chocolate Covered Cherrie
415    1      CHOCOLATE BOBKA          
417    1      CHOCOLATE BOBKA          
421    1      CHOCOLATE BOBKA          
sqlite> 

Se a cláusula WHERE não for usada, o comando UPDATE modificará cada linha da tabela, desde que não viole nenhuma cláusula de integridade referencial fornecida durante a definição da tabela.

Por exemplo o comando seguinte gera um erro:

sqlite> UPDATE foods SET id = 11 WHERE name = 'CHOCOLATE BOBKA';
Error: UNIQUE constraint failed: foods.id

O banco de dados não faz a atualização do campo id pois apresenta a “restrição” (constraints) de ser uma PRIMARY KEY, e portanto deve ser única.

O comando DELETE, como seu nome indica, apaga as linhas de uma tabela com a seguinte sintaxe:

DELETE FROM nome_da_tabela [WHERE expressão]

A cláusula FROM nomeia a tabela a partir da qual as linhas serão apagadas. E a cláusula WHERE permite identificar as linhas que serão apagadas.

Por exemplo a declaração seguinte irá remover a linha com o id=420:

sqlite>  SELECT * FROM foods; --Verificando os últimos registros
 .
 .
 .
415    1      CHOCOLATE BOBKA          
416    1      Blackberry Bobka         
417    1      CHOCOLATE BOBKA          
418    1      Cinnamon Bobka           
419    1      Cinnamon Bobka           
420    NULL   Cinnamon Bobka           
421    1      CHOCOLATE BOBKA          
422    1      Blackberry Bobka  
sqlite> DELETE FROM foods WHERE id=420; --Removendo o registro com id=420
sqlite> SELECT * FROM foods; --Verificando os últimos registros
.
.
.
415    1      CHOCOLATE BOBKA          
416    1      Blackberry Bobka         
417    1      CHOCOLATE BOBKA          
418    1      Cinnamon Bobka           
419    1      Cinnamon Bobka           
421    1      CHOCOLATE BOBKA          
422    1      Blackberry Bobka

Removendo as linhas com name = 'CHOCOLATE BOBKA':

sqlite> SELECT * FROM foods WHERE name LIKE 'Chocolate%';
id     type_  name                     
-----  -----  -------------------------
10     1      CHOCOLATE BOBKA          
11     1      Chocolate Eclairs        
12     1      Chocolate Cream Pie      
222    9      Chocolates, box of       
223    9      Chocolate Chip Mint      
224    9      Chocolate Covered Cherrie
415    1      CHOCOLATE BOBKA          
417    1      CHOCOLATE BOBKA          
421    1      CHOCOLATE BOBKA          
sqlite> DELETE FROM foods WHERE name='CHOCOLATE BOBKA';
sqlite> SELECT * FROM foods WHERE name LIKE 'Chocolate%';
id     type_  name                     
-----  -----  -------------------------
11     1      Chocolate Eclairs        
12     1      Chocolate Cream Pie      
222    9      Chocolates, box of       
223    9      Chocolate Chip Mint      
224    9      Chocolate Covered Cherrie

Uma transação trata uma série de modificações no banco de dados como se fosse uma única modificação. Ou seja, todas as modificações acontecem ou nenhuma acontece e se elas acontecerem, deverá ser de uma só vez.

Isso visa manter a “integridade do banco de dados” e é chamado de “princípio atômico de integridade do banco de dados.”

Um exemplo clássico que ilustra a importância de uma transação é uma transferência de dinheiro entre contas bancárias. Considere que um programa bancário está transferindo dinheiro de uma conta (1) para outra conta (2). O programa de transferência pode realizar essa operação de duas maneiras:

Em ambos os casos a transferência é um processo em “duas etapas”: uma inserção seguida de uma remoção, ou uma remoção seguida de uma inserção.

Agora imagine se durante a transferência o sistema rodando o banco de dados travar ou faltar energia e a segunda etapa da operação não se realizar?

No primeiro caso o dinheiro vai estar presente em ambas as contas, e no segundo caso o dinheiro vai desaparecer. Ou seja alguém vai sair perdendo em ambos os casos, e o banco de dados vai ficar em um estado “inconsistente”.

A questão fundamental neste caso é que: ambas as operações devem acontecer ou nenhuma delas deve acontecer. Essa é o princípio fundamental de uma transação.

O “escopo de uma transação” é definido pelo uso de 3 comandos: BEGIN, COMMIT e ROLLBACK.

O comando BEGIN inicia uma transação e todas as operações que seguem o comando BEGIN podem ser potencialmente desfeitas e serão efetivamente desfeitas se não for executado o comando COMMIT antes do fim da “seção”.

O comando COMMIT realiza efetivamente as ações pendentes deste o início da transação.

E da mesma forma o comando ROLLBACK cancela todas as operações pendentes desde o início da transação.

Uma transação é um escopo no qual “todas” as operaçoes são realizadas ou canceladas, “juntas”.

Exemplo de uma transação:

sqlite> BEGIN;
sqlite> DELETE FROM foods;
sqlite> ROLLBACK;
sqlite> SELECT count(*) FROM foods;
count(*)  
----------
417      

Neste exemplo a transação foi iniciada com BEGIN, em seguida todas as linhas da tabela foods foram deletadas, mas em seguida essa operação foi cancelada com o comando ROLLBACK. E o comando SELECT mostra que a remoção não foi realizada.

O SQLite executa automaticamente cada comando em sua própria transação e se não houver falhas são automaticamente implementadas (committed).

www.sqlitetutorial.net/

SQLite Tutorial - Mike Chirico

Guia rápido de comandos SQLite3

Gerenciando banco de dados SQLite3 com Python - Parte 1

Introdução ao SQL

Exemplo de uso de SQL na análise de dados: