Introdução SQL
SQL (Structured Query Language - Linguagem de Busca Estruturada) é a linguagem que usamos para manipular dados de um banco (existem outros tipo de bancos de dados que não usam SQL). Com o SQL pdemos extrair/adicionar informações dos bancos de dados.
Neste capítulo aprenderemos alguns comandos básicos do SQL para
consulta (também chamada de QUERY). Para isso usaremos o banco de dados
(já preenchido) longlist.db
BAIXAR.
Esse BD armazena se refere a livros, em particular, livros que
concorreram ao prêmio internacional “The Booker Prizes”. Esse é um
prêmio para livros de ficção do mundo inteiro, que ocorre anualmente.
Todo ano um comitê escolhe 13 livros para concorrerem ao prêmio. O banco
de dados contém uma lista com 5 anos desses livros selecionados. O banco
é mostrado abaixo:
1 Acessando o banco de dados
Existem diversas formas de acessar o banco de dados por meio do SQLite, todas são equivalentes. Em todas precisamos abrir o arquivo antes de começar a fazer as consultas. Algumas opções para isso são:
1.1 Usando o terminal
Para acessar o banco usando o terminal, faça:
Abra o terminaL: na pasta em que o banco de dados está localizado, botão direito do mouse e “Abrir no terminal, essa opção já abre o terminal na pasta atual. Se não houver essa opção, em”Pesquisa” digite cmd, em seguida
cd caminho_da_pasta_do_banco, isso vai direcionar o terminal para a pasta do banco.Digite
sqlite3 longlist.dbpara acessar o banco. Para acessar outros bancos basta substituir o nome do mesmo.Para confirmar a qual banco de dados o sqlite está conectado, digite
.databasesPara deixar o sqlite digite
.quit.
Aqui já é possível executar as queries de busca no banco.
1.2 Usando o SQLiteStudio
O SQLiteStudio (https://sqlitestudio.pl/) é um programa do próprio SQLite, que fornece uma interface gráfica intuitiva para se conectar aos bancos de dados. Ele facilita a visualização das tabelas e dos dados nelas contidas.
Baixe o programa
Database-> Connect to database. Selecione o banco.
Para executar queries abra a aba “Open SQL editor” e digite as queries.
Execute a seguinte query para testar:
SELECT * FROM "longlist";Acessando pelo terminal, a seguinte tabela é para ser mostrada (que já é o retorno de uma query no banco de dados):
No entanto, no restante desse material os outputs serão mostrado da seguinte forma, para facilitar a visualização:
| isbn | title | author | translator | format | pages | publisher | published | year | votes | rating |
|---|---|---|---|---|---|---|---|---|---|---|
| 9788439736967 | Boulder | Eva Baltasar | Nicole d’Amonville Alegría | paperback | 112 | Literatura Random House | 2022-08-02 | 2023 | 2779 | 3.77 |
| 9781628971538 | Whale | Cheon Myeong-Kwan | Jae Won Chung | paperback | 368 | Europa Editions | 2023-01-19 | 2023 | 175 | 3.97 |
| 9781642861181 | The Gospel According to the New World | Maryse Condé | Richard Philcox | paperback | 184 | World Editions | 2023-03-07 | 2023 | 114 | 3.05 |
| 9781529414431 | Standing Heavy | Gauz | Frank Wynne | paperback | 252 | MacLehose Press | 2022-05-26 | 2023 | 322 | 3.57 |
| 9781474623025 | Time Shelter | Georgi Gospodinov | Angela Rodel | hardcover | 304 | W&N | 2022-04-21 | 2023 | 3142 | 4.05 |
| 9781839764318 | Is Mother Dead | Vigdis Hjorth | Charlotte Barslund | paperback | 330 | Verso Fiction | 2022-09-27 | 2023 | 2455 | 3.76 |
| 9781529427820 | Jimi Hendrix Live in Lviv | Andrey Kurkov | Reuben Woolley | hardcover | 416 | MacLehose Press | 2023-04-27 | 2023 | 167 | 3.64 |
| 9781945492655 | The Birthday Party | Laurent Mauvignier | Daniel Levin Becker | paperback | 454 | Transit Books | 2023-01-24 | 2023 | 529 | 3.83 |
| 9781804270288 | While We Were Dreaming | Clemens Meyer | Katy Derbyshire | paperback | 528 | Fitzcarraldo Editions | 2023-03-30 | 2023 | 705 | 4.00 |
| 9781782278627 | Pyre | Perumal Murugan | Aniruddhan Vasudevan | paperback | 224 | Pushkin Press | 2022-04-08 | 2023 | 1302 | 4.04 |
2 Comandos básicos SQL
Agora veremos uma série de comandos para realizarmos buscas, ou seja, as queries no banco de dados.
2.1 SELECT
O comando SELECT é usado para selecionar dados de um banco. O comando precisa de pelo menos duas informações: o que queremos selecionar, e de onde (qual tabela) queremos essa informação. O comando abaixo seleciona todos os dados (pelo asterisco *) da tabela chamada ‘longlist’:
SELECT * FROM "longlist";Ou seja, da forma como está o comando seleciona tudo que há no banco de dados.
Podemos usar o comando SELECT para buscar os dados de uma única coluna, basta substituirmos o * pelo nome da coluna. O comando abaixo busca todos os registros da coluna ‘titles’:
SELECT "title" FROM "longlist";| title |
|---|
| Boulder |
| Whale |
| The Gospel According to the New World |
| Standing Heavy |
| Time Shelter |
| Is Mother Dead |
| Jimi Hendrix Live in Lviv |
| The Birthday Party |
| While We Were Dreaming |
| Pyre |
Podemos selecionar mais de uma coluna de uma única vez, bastando separar os nomes das colunas por vírgulas:
SELECT "title","authors" FROM "longlist";| title | “authors” |
|---|---|
| Boulder | authors |
| Whale | authors |
| The Gospel According to the New World | authors |
| Standing Heavy | authors |
| Time Shelter | authors |
| Is Mother Dead | authors |
| Jimi Hendrix Live in Lviv | authors |
| The Birthday Party | authors |
| While We Were Dreaming | authors |
| Pyre | authors |
2.2 LIMIT
Podemos limitar o número de registros que serão retornados, usando a palavra LIMIT com o número de elementos que queremos retornar.
SELECT "title","authors" FROM "longlist" LIMIT 5;| title | “authors” |
|---|---|
| Boulder | authors |
| Whale | authors |
| The Gospel According to the New World | authors |
| Standing Heavy | authors |
| Time Shelter | authors |
2.3 WHERE
O comando WHERE é usado para retornar somente os registros em que uma determinada condição é satisfeita. Por exemplo, selecionando os dados de autores (“authors”) e títulos (“titles”) em que o ano é 2023:
SELECT "title","authors" FROM "longlist" WHERE "year" = 2023;| title | “authors” |
|---|---|
| Boulder | authors |
| Whale | authors |
| The Gospel According to the New World | authors |
| Standing Heavy | authors |
| Time Shelter | authors |
| Is Mother Dead | authors |
| Jimi Hendrix Live in Lviv | authors |
| The Birthday Party | authors |
| While We Were Dreaming | authors |
| Pyre | authors |
OBSERVAÇÃO: o ano 2023 não foi escrito com aspas pois está armazenado com um número no BD.
Usamos o sinal de igualdade “=” para criar a condição. Existem 2 outros sinais que podemos usar com o WHERE:
| Sinal | Uso |
|---|---|
| = | Igualdade |
| != | Diferente de |
| <> | Diferente de |
Temos uma coluna que específica se o livro é de capa-dura ou não (coluna “format”, pode ser “paperback” ou “hardcover”). Vamos selecionar todos os nomes de livros que não são capa dura:
SELECT "title","format" FROM "longlist" WHERE "format" != 'hardcover';| title | format |
|---|---|
| Boulder | paperback |
| Whale | paperback |
| The Gospel According to the New World | paperback |
| Standing Heavy | paperback |
| Is Mother Dead | paperback |
| The Birthday Party | paperback |
| While We Were Dreaming | paperback |
| Pyre | paperback |
| Still Born | paperback |
| A System So Magnificent It Is Blinding | paperback |
OBSERVAÇÃO: hardcover foi escrito com aspas simples pois é uma string do BD. Embora funcione usando tanto aspas simples quanto duplas, é uma boa prática usar:
A. Aspas duplas: Nomes de tabelas e colunas.
B. Aspas simples: Nomes de strings do BD.
2.4 NOT
A palavra NOT é usada para fazer a negação de uma sentença. Por exemplo, poderíamos fazer a mesma query anterior, buscando os livros que são capa dura, e negando essa sentença:
SELECT "title","format" FROM "longlist" WHERE NOT "format" = 'hardcover';2.5 AND, OR, ()
Podemos usar as palavras AND e [ OR]{style=“color:blue” para criar sentenças mais complexas em uma query (como em uma linguagem de progrmação). A query abaixo seleciona os livros de 2022 ou 2023.
SELECT "title","author","year" FROM "longlist" WHERE "year" = 2022 OR "year" = 2023;Agora vamos buscar todos os livros de 2022 e 2023 em que o formato da capa não é dura. Note que temos duas condições: uma em relação aos anos e outra em relação aos tipos de capas. Para juntar as duas vamos separar cada uma por parentêses, e cada par de parenteses pela palavra AND:
SELECT "title","year","format" FROM "longlist" WHERE ("year" = 2022 OR "year" = 2023) AND ("format" != 'hardcover');2.6 NULL
O SQL tem uma palavra específica para campos nulos (não preenchidos) - NULL. Podemos usar nas queries para verificar se um determinado valor é nulo (IS NULL) ou se não é (IS NOT NULL). No banco de dados de livros, alguns livros foram traduzidos para outros idiomas, portanto a coluna ‘translator’ indica justamente o tradutor. Alguns livros no entanto não foram traduzidos, de forma que o campo fica vazio.
A query abaixo busca todos os títulos de livros que não possuem tradutores:
SELECT "title","translator" FROM "longlist" WHERE "translator" IS NULL;E para buscar todos os livros que possuem tradutores:
SELECT "title","translator" FROM "longlist" WHERE "translator" IS NOT NULL;2.7 LIKE, %, _
O operador LIKE é usado para buscas parciais em uma string. Por exemplo, todos os títulos de livros que possuam a palavra ‘love’ contida em alguma parte.
Usamos o operadores % e _ junto com LIKE.
Vamos buscar os títulos de livro que contém a palavra ‘love’.
SELECT "title" FROM "longlist" WHERE "title" LIKE '%love%';As % podem ser colocadas antes e depois da string de busca. Antes significa que qualquer string que tenha texto ANTES de love será mostrada, e depois significa que qualquer string em que love apareça, e depois tenha mais texto també, será mostrada.
Note o que ocorre quando deixamos somente a % depois de love (ou seja, queremos todas as strings que não possuam nada antes de love, porém podem possuir depois).
SELECT "title" FROM "longlist" WHERE "title" LIKE 'love%';Vamos selecionar todos os livros que começam com “The”:
SELECT "title" FROM "longlist" WHERE "title" LIKE 'The%';OBSERVAÇÃO: A query acima poderia selecionar títulos que tenham palavras que começam com “The”, por exemplo “Therefore”. Para corrigir isso usamos a seguinte query (adicionamos um espaço após ‘The’):
SELECT "title" FROM "longlist" WHERE "title" LIKE 'The %';É possível usar quantos % quisermos, com strings maiores. Considere a query que busca títulos que devem começar com “The” e em qualquer lugar depois ter a palavras que começam com a letra “p”:
SELECT "title" FROM "longlist" WHERE "title" LIKE 'The % p%';Usamos o underline _ quando não sabemos a string completa que queremos buscar. Por exemplo, existe um livro que é chamado “Pyre”, porém não sabemos a escrita correta, se é “Pyre” ou “Pire”. Podemos realizar a seguinte query:
SELECT "title" FROM "longlist" WHERE "title" LIKE 'P_re';Neste outro exemplo buscamos um título que comece com T e tenha mais 3 letras em seu nome, porém não sabemos quais letras:
SELECT "title" FROM "longlist" WHERE "title" LIKE 'T___';2.8 CONSTRUINDO RANGES (BETWEEN … AND …)
Considere agora que desejamos buscar os livros cujos anos na competição foram 2019, 2020, 2021 ou 2022. Uma possibilidade é usar a query WHERE junto com OR da seguinte maneira:
SELECT "title","year" FROM "longlist" WHERE ("year" = 2019) OR ("year" = 2020) OR ("year" = 2021) OR ("year" = 2022);Com certeza a query pode ser mais enxuta. Podemos usar os seguintes operadores para construir ranges:
| Sinal | Uso |
|---|---|
| > | Maior que |
| < | Menor que |
| >= | Maior ou igual a |
| <= | Menor ou igual a |
A query abaixo busca os títulos em que as data são >= 2019 e <= 2022.
SELECT "title","year" FROM "longlist" WHERE ("year" >= 2019) AND ("year" <= 2022);Uma outra possibilidade para criação de ranges é a utilização das palavras chave BETWEEN junto com AND para construir ranges. Veja como fica o exemplo usando essa sintaxe:
SELECT "title","year" FROM "longlist" WHERE "year" BETWEEN 2019 AND 2022;Vamos buscar os livros com menos do que 300 pg:
SELECT "title","pages" FROM "longlist" WHERE "pages" < 300;2.9 ORDENANDO ELEMENTOS (ORDER BY)
Também podemos montar queries que ordenam os resultados buscados por algum critério. Considere a coluna “rating” do banco de dados, que fornece a nota dada ao livro. Vamos tentar selecionar os 10 livros com as melhores notas.
SELECT "title","rating" FROM "longlist" ORDER BY "RATING" LIMIT 10;OBSERVAÇÃO: Note que as notas estão ordenadas de forma crescente, este é o default do ORDER BY. Para ordenar em ordem decrescente podemos usar a palavra chave DESC, o mesmo para ascendente ASC.
A query fica então:
SELECT "title","rating" FROM "longlist" ORDER BY "rating" DESC LIMIT 10;Note que existem empates nas notas dos livros, por exemplo “Still Born” e “When We Cease to Understand the World”, ambos com notas 4.14. Podemos então fazer uma segunda ordenação para desempate, pelo número de votos que um livro recebeu. Os votos estão na coluna “votes”, podemos adicionar a segunda ordenação simplesmente por meio de uma vírgula após a primeira. A query fica então:
SELECT "title","rating", "votes" FROM "longlist" ORDER BY "rating" DESC, "votes" DESC LIMIT 10;Vamos tentar ordenar o banco de dados por títulos:
SELECT "title" FROM "longlist" ORDER BY "title" ASC LIMIT 10;2.10 FUNÇÕES DE AGREGAÇÃO
Além de retornar um conjunto de elementos de acordo com um query, podemos extrair informações do conjunto de forma agregada. Por exemplo, qual seria a média de rating dos livros do ano 2023? Qual o maior e o menor rating? As palavras chave abaixo podem ser usadas para isso, e são chamadas de FUNÇÕES DE AGREGAÇÃO:
| Função | Executa |
|---|---|
| COUNT | Conta o número de elementos |
| AVG | Calcula a média dos elementos |
| MIN | Retorna o menor elemento |
| MAX | Retorna o maior elemento |
| SUM | Calcula a soma dos elementos |
Vamos calcular a média de rating do banco de dados. Como esses operadores são funções, precisamos colocar entre parentêses os argumentos que queremos calcular. A query fica então:
SELECT AVG("rating") FROM "longlist";Podemos limitar o número de casas decimais de um resultado por meio de outra função, chamada ROUND():. A query abaixo calcula a média, limitando a 2 casas decimais a resposta.
SELECT ROUND(AVG("rating"),2) FROM "longlist";Note que o nome dado para a soluna de resposta é a própria função da query SELECT ROUND(AVG(“rating”),2). Para uma apresentação mais amigável, podemos alterar o nome da coluna mostrada usando a palavra chave AS. Vamos alterar o nome para Média de rating com a seguinte query:
SELECT ROUND(AVG("rating"),2) AS "Média de rating" FROM "longlist";Selecionando o menor rating dos livros:
SELECT MIN("rating") AS "Minimo rating" FROM "longlist";Vamos contar o número de títulos do banco de dados:
SELECT COUNT("title") AS "Total de titulos" FROM "longlist";Para determinar o número de elementos do banco de dados (número de linhas), podemos usar o asterisco:
SELECT COUNT(*) AS "Total de linhas" FROM "longlist";Vamos contar o número de “publishers”:
SELECT COUNT("publisher") AS "Total de publishers" FROM "longlist";Será que isso quer dizer que existem 78 “publishers” diferentes no banco de dados? NÃO, a query retorna o número de linhas em que a coluna “publisher” não possui valor nulo. Como podemos fazer para encontrar o número de “publishers” diferentes (não repetidos)? Para isso usamos a palavra chave DISTINCT, para selecionar somente os “publishers” sem repetição:
SELECT DISTINCT "publisher" FROM "longlist";E agora, para encontrarmos a número de “publishers” diferentes basta usarmos a função COUNT na query anterior:
SELECT COUNT(DISTINCT "publisher") FROM "longlist";2.11 EXPORTANDO QUERIES
Até agora executamos todas as queries e as visualizamos no próprio terminal de comando. Para facilitar a verificação, podemos exportar os dados em um arquivo de texto externo. Para isso usamos o comando:
.output "caminho do arquivo de saída";Por exemplo, supondo que queremos exportar as queries para um arquivo chamado “saida.txt” que está localizado na pasta “C:”, o comando fica:
.output "C:\CURSO\SQl\saida.txt";Com esse comando todos os resultados das queries serão redirecionados para o arquivo. Para retornar os outputs para o prompt de comando basta digitar o comando sem nenhum caminho:
.output;