visão global

Vamos aprender o que é SQL, para que podemos usá-lo e, então, começaremos a escrever algumas consultas básicas para selecionar e filtrar dados de um banco de dados.

Nota: Na verdade, não trabalharemos com um banco de dados SQL hoje, estaremos usando Python para criar um dataframe e então usaremos uma biblioteca chamada pandasql para consultá-lo. Se você não está familiarizado com Python, ainda sugiro que siga o tutorial e preste atenção aos comandos SQL e suas saídas.

O que é SQL?

Os dados estão em todos os lugares e mais deles são criados a cada minuto. Costumávamos armazenar esses dados em papel em arquivos gigantes, mas agora os armazenamos digitalmente em coisas chamadas de bancos de dados. Agora, como podemos extrair facilmente os dados que queremos deste banco de dados digital? É para isso que serve o SQL! SQL (Structured Query Language) é uma linguagem que usamos para nos comunicar com nossos bancos de dados. Se quiser extrair, editar ou adicionar dados a um banco de dados, você pode usar o SQL para fazer isso. Os bancos de dados podem ser criados em uma variedade de arquiteturas (lagos de dados gigantes, esquemas simples de 1 tabela), escritos em uma variedade de linguagens (C ++, Java), mas SQL é o terreno comum que permite que qualquer pessoa acesse esses dados usando sintaxe universal.

Sabemos que podemos editar e adicionar dados a um banco de dados usando SQL, mas o SQL se torna mais útil quando queremos extrair dados existentes por causa de sua capacidade de filtrar registros massivos e retornar apenas as entradas exatas que estamos procurando. Há um número quase infinito de métodos SQL, cada um com sua própria sintaxe exclusiva e localização necessária dentro do comando. Mais sobre isso a seguir.

Sintaxe e melhores práticas

O uso de um comando SQL para extrair dados de um banco de dados é chamado de consulta e, no SQL, existem algumas regras de sintaxe comuns que podem fazer com que seu código seja interrompido se não for seguido. As consultas às vezes podem ser muito longas e extremamente complicadas, portanto, algumas práticas recomendadas foram desenvolvidas ao longo dos anos para garantir que as consultas sejam organizadas e legíveis. Vamos examinar os fundamentos da sintaxe SQL e algumas regras de práticas recomendadas comuns que devemos seguir ao escrever consultas SQL.

Aqui está um comando SQL muito básico que simplesmente retorna todas as linhas e colunas de um banco de dados chamado store_sales. (Lembre-se de que esta é uma das consultas mais simples que podemos escrever em SQL, as consultas raramente serão tão curtas)

''' 
SELECT * FROM store_sales;
'''

• ’’’…’’’- As consultas SQL são sempre strings e, ao usar aspas triplas, podemos contornar o fato de que a maioria das consultas requer várias linhas
• SELECT - este é o comando usado para extrair dados de um banco de dados, sempre seguido por uma indicação de quais colunas acessar para extrair dados, sempre o primeiro comando na consulta
• * - este é o idioma SQL para "todos", selecionará cada linha e cada coluna (nota: isso pode ser outra coisa dependendo de quais colunas queremos acessar)
• FROM - é assim que especificamos qual fonte de dados usar, sempre precede o comando SELECT e tudo o que vier depois será o alvo do comando
• store_sales - este é o nome de nosso banco de dados imaginário, isso será diferente dependendo de qual banco de dados você está usando
• ; - isso diz ao nosso banco de dados que terminamos o nosso comando, sempre terminamos uma consulta com ;

Conforme mencionado anteriormente, cada comando em SQL tem sua própria sintaxe e posicionamento na consulta. Aqui está uma folha de referência que eu realmente gosto para verificar rapidamente a sintaxe e os casos de uso: https://www.w3schools.com/sql/sql_ref_keywords.asp . Basta clicar no nome de um método para obter mais informações sobre como usá-lo, incluindo alguns comandos de amostra.

Noções básicas de seleção de dados

No restante deste artigo, vamos nos concentrar nos métodos básicos de seleção e filtragem de dados. Vamos contornar esse cenário imaginário: somos professores de uma escola com uma turma de 20 crianças. É o fim do trimestre. No final de cada semana do período de 10 semanas, os alunos fazem um teste sobre a matéria da semana anterior. As pontuações desses testes são armazenadas em um banco de dados SQL no sistema de nossa escola, portanto, vamos escrever algumas consultas para obter algumas informações sobre nossos alunos e suas pontuações nos testes.

Como mencionado antes, vamos usar Python para criar os dados que vamos usar (pandas DataFrames) e vamos usar uma biblioteca chamada pandasqlpara consultá-los. pandasqlusa SQLite, que é um tipo bastante comum de SQL para armazenamento local / cliente em aplicativos.

Importações e criação de dados

Se você deseja apenas mergulhar nas consultas, pule para a seção ONDE. Se você não estiver familiarizado com Python, sugiro que o faça.

Aqui está o código que usei para gerar nossos dados de pontuação de teste aleatório. Não vou entrar em muitos detalhes, há alguns comentários para ajudá-lo a entender o que está acontecendo. Nosso DataFrame final do pandas será armazenado em uma variável para scoresa qual escreveremos nossas consultas SQL.

Se você rolar até o final do código, verá as primeiras 5 linhas de nossos dados impressas.

ONDE

O WHEREargumento é usado como argumento de filtragem de base. Ele sempre vem após sua FROM …declaração e sempre é seguido pela condição que você deseja filtrar. As condições podem ser para dados numéricos ou de string, as colunas não podem conter os dois tipos de dados. WHEREoperadores funcionam praticamente da mesma forma que qualquer outra linguagem de programação, mas aqui está uma breve visão geral:

Agora, digamos que nós, como professores, queremos saber quem em nossa classe não se saiu tão bem no teste da primeira semana. Vamos dar uma olhada na aparência da consulta para isso (usando uma WHEREcláusula). Quantificaremos “não foi tão quente” como uma pontuação de menos de 77%.

Ok, então o que aconteceu aqui? A primeira coisa que você notará é que usamos em SELECT name, test_1_scorevez de SELECT *. Essas são as duas colunas em nossa scorestabela e, ao indicá-las na SELECTinstrução, estamos dizendo para retornar apenas informações nessas duas colunas.

Em seguida, estamos dizendo que queremos selecionar FROM scores. Em pandasql, os objetos DataFrame atuais do pandas em nosso ambiente de trabalho são tratados como tabelas SQL acessíveis. Sendo esse o caso, scoresé a nossa fonte de dados, e indicamos como tal na FROMcláusula.

Também dissemos que queríamos apenas discos WHERE test_1_score <77. Como nossos dados são numéricos, podemos usar um operador matemático para dizer que queremos todos os valores menos, graças a _blank_

Vamos tentar outro. Desta vez, queremos ver quais alunos se saíram particularmente bem (90% ou melhor) no teste final semanal (teste 10).

Impressionante! Simplesmente indicamos que queríamos aplicar a filtragem aos valores em test_10_score, em vez de test_1_scoree, em seguida, viramos nosso sinal do operador para 'maior que'.

Entre dois valores

E se quiséssemos encontrar alunos que entendessem claramente o material, mas talvez não estudassem tão bem quanto deveriam? Vamos supor que foi isso que aconteceu com os alunos que pontuaram acima de 80%, mas abaixo de 90%.

Você pode pensar que a consulta seria parecida com isto, onde simplesmente 'empilham' nossos operadores.

Mas, como podemos ver, estamos obtendo todos os tipos de valores que estão fora do intervalo de 80-90.

Isso ocorre porque há um operador SQL integrado que devemos usar em seu lugar. Você consegue adivinhar qual é o nome desse método? Você adivinhou, é BETWEEN. BETWEENé tecnicamente um operador lógico, portanto, sempre segue o comando WHERE. Ele pede dois valores - o início e o fim do intervalo desejado, separados por AND- indicando um intervalo de números. Uma nota aqui: SQL BETWEENé inclusivo (equivalente a 80<=test_10_score<=90).

Agora podemos ver que temos uma lista muito menor, contendo apenas nomes e pontuações para alunos que pontuaram entre 80-90% inclusive.

E / OU

Até agora, especificamos apenas uma única condição em nossas WHEREinstruções. Na realidade, podemos ter quantas condições quisermos, tornando o SQL realmente útil para filtragem complexa. Existem duas maneiras de indicar várias condições após um WHEREcomando:

• AND - usado quando queremos retornar entradas onde ambas / todas as condições são verdadeiras (por exemplo, queremos alunos com pontuação acima de 90% nos testes 9 e 10)
• OR - usado quando queremos retornar entradas onde qualquer uma das condições é verdadeira, retornará todos os registros que correspondam a qualquer número de condições na cadeia OR

Ótimo! Agora, digamos que as semanas 1 e 2 do semestre cobriram o mesmo material e queremos obter uma lista de alunos que compreenderam pelo menos uma boa parte desse material. Para isso, queremos retornar os nomes dos alunos que pontuaram melhor do que 85% no teste 1 ou no teste 2. Isso se adequa perfeitamente a um ORoperador.

OK fixe. Parece que, na maior parte do tempo, nossa classe entendeu uma boa parte do material das duas primeiras semanas

A grande vantagem do ANDe ORé que você pode encadear quantos deles quiser. Você pode até misturar e combinar!

Por exemplo, se nosso teste final (teste 10) foi um teste final em todo o material das semanas 1–9, podemos ver uma lista de alunos que se saíram mal no início do semestre e também se saíram mal no o fim do prazo. Vamos olhar para alunos que não se saíram tão bem nos testes nas semanas 1 ou 2 e que também não reservaram um tempo para estudá-lo durante o semestre e acabaram tendo um desempenho ruim no teste final (teste 10 ) Dê uma olhada na consulta SQL a seguir e veja se você sabe o que ela está fazendo.

Desta vez, dissemos que queremos retornar apenas os nomes dos alunos que pontuaram abaixo de 75% no teste 1 ou no teste 2 e também pontuaram abaixo de 75% no teste 10. Usamos ORprimeiro porque não nos importamos com qual teste eles fez mal, nós apenas se importava se eles fizeram mal em qualquer deles. Em seguida, costumávamos ANDindicar que precisávamos absolutamente que a última condição fosse verdadeira. Se as entradas não corresponderem a uma das duas primeiras condições e à última condição, elas não serão incluídas na devolução.

ORDENAR POR

Às vezes, ainda pode ser difícil filtrar um longo retorno de consulta e pode ser útil organizar e filtrar ainda mais as linhas na saída. ORDER BYé um método muito comum e útil para fazer isso e funciona em praticamente todos os tipos de dados (string, série temporal, numérico, etc.). ORDER BYe seus vários modificadores devem sempre ser colocados perto do final e sempre vir após suas cláusulas condicionais (por exemplo WHERE).

Vamos criar uma nova coluna em nossa tabela que é uma média geral da pontuação do teste para cada aluno nas últimas 10 semanas. Vamos chamar esta coluna test_avg.

Agora podemos obter uma lista ordenada de nossos alunos armazenada por seu avg. pontuação do teste. Essa consulta seria mais ou menos assim, mas dê uma olhada em nossa saída.

Obtemos uma lista perfeitamente organizada, mas essa lista está em ordem crescente . Esta é a configuração padrão no SQL ORDER BY. Se quisermos que uma lista seja classificada em ordem decrescente , o SQL torna isso muito fácil; apenas adicionamos a DESCtag após nosso indicador de coluna.

Agora temos nossos valores mais altos no início de nossa tabela de retorno.

Também podemos ordenar nossas devoluções por mais de uma coluna. Você faz isso separando por vírgula os valores da coluna após seu ORDER BYargumento. Por exemplo, a consulta a seguir retorna name, test_avge test_10_scoreclassificada por test_10_scoreprimeiro e, em seguida, classificada por test_avg. É sutil, mas veja se você consegue dizer onde essa segunda camada de classificação está acontecendo.

Se olharmos atentamente (verifique as linhas 1 e 2, 3 e 4 e 12 e 13), podemos ver essa ordem de duas camadas em ação.

Essa dupla ordem não é necessariamente muito útil para essa situação, mas veja o exemplo a seguir. Imagine trabalhar para uma concessionária de automóveis e precisar classificar dezenas de milhares de registros para encontrar uma venda específica de uma marca e modelo específicos para um trabalho de reparo. Agora, classificar por dois valores (digamos yeare então car_model) torna-se extremamente necessário e o SQL torna isso mais fácil para nós.

GRUPO POR

O último método importante sobre o qual falaremos aqui é o GROUP BYmétodo. Ele faz exatamente o que parece, agrupa linhas pela coluna especificada, mas o retorno de a GROUP BYparece um pouco diferente das consultas que vimos no passado. GROUP BYagrega linhas com base em valores comuns e é normalmente usado para colunas que contêm variáveis ​​categóricas. Semelhante a ORDER BY, sempre vai no final da consulta. É comum usar GROUP BYna mesma consulta que uma ORDER BYcláusula, neste caso, sempre colocamos por GROUP BYúltimo.

Partindo do nosso exemplo de pontuação de professor / teste, poderíamos usar esta função para agrupar alunos por letra-série. E é isso que vamos fazer. Primeiro, vamos criar uma nova coluna em nossa tabela. Chamaremos esta coluna gradee a preencheremos com notas de letras com base nas seguintes condições de pontuação média de teste:

Se você olhar totalmente para a direita, verá nossa nova coluna.

Agora podemos escrever algumas consultas muito legais. Dê uma olhada na consulta a seguir e veja se você pode dizer o que está acontecendo (há um método que não abordamos ainda).

A primeira coisa que você provavelmente notou na consulta é o COUNT(name)argumento. Está em nossa SELECTcláusula, então sabemos que é algum tipo de informação que estamos extraindo de nossa mesa. COUNT()é um método integrado realmente útil em SQL que retorna um único valor que representa o número de entradas no retorno, agrupadas da maneira que você especificar. Optamos por contar o número de nomes também conhecidos como alunos em cada grupo. Os grupos são especificados por nosso GROUP BY gradeargumento, que agrega nossas muitas linhas em algumas linhas que representam todos os valores atuais na gradecoluna.

Outro método SQL integrado muito útil é AVG(). Ele funciona da mesma forma que COUNT(), retornando um único valor que representa a média da coluna fornecida, agrupado da maneira que você especificar. Na consulta a seguir, estamos retornando a pontuação média do teste de todos os nossos alunos em cada grupo de notas de letras.

Observe as novas colunas criadas por COUNT()e AVG(). Eles são nomeados intuitivamente, descrevendo exatamente o que a coluna contém, mas também parece um pouco confuso. Podemos corrigir isso usando o ASargumento SQL . Este é um argumento muito útil para ter em seu conjunto de ferramentas, especialmente quando você começa a agregar muitas categorias diferentes, pode ser muito útil ter uma convenção de nomenclatura familiar e personalizável.

Ajudantes extras: AS e AVG ()

A consulta a seguir usa uma ASinstrução para retornar um único valor, renomeado test_1_avg. Como você provavelmente pode imaginar, esse valor será uma média de pontuação da classe para o teste 1.

Podemos ver o resultado imediatamente em nossa saída é uma test_1_avgcoluna recém-dublada contendo um único valor.

AS é colocado imediatamente após o objeto que você deseja renomear e pode ser usado em qualquer lugar em que você esteja introduzindo uma coluna ou um novo valor em sua consulta.

Conclusão

Isso é o suficiente para esta edição do SQL Crash Course, fique atento para o próximo artigo desta série, onde veremos como criar tabelas e bancos de dados SQL - a arquitetura SQL usada para armazenar tabelas.

Para resumir o que aprendemos, primeiro aprendemos que SQL (Structured Query Language) é a linguagem que usamos para falar com bancos de dados e que nos permite adicionar, excluir, editar e recuperar dados de bancos de dados. Em seguida, aprendemos vários métodos para recuperar dados e aprendemos sobre as melhores práticas e sintaxe de SQL. Aprendemos que os argumentos têm certas localizações em que precisam estar dentro de uma consulta, aprendemos que as consultas precisam de um ponto e vírgula no final e aprendemos que existem toneladas de métodos integrados para agregar linhas e colunas de dados.

Obrigado pela leitura, sintonize na próxima vez e feliz SQLing!