Articles

Introdução ao SQL: exemplos, melhores práticas e armadilhas

Posted by admin

SQL (Structured Query Language) está conosco há mais de meio século e não vai embora tão cedo. Popular tanto em bancos de dados relacionais tradicionais quanto em novas tecnologias de bancos de dados NoSQL, o SQL é amplamente utilizado para análise de dados, processamento de Big Data, linguagens de codificação e muito mais.

eu sou um fã de SQL. É uma linguagem poderosa, mas simples. Neste post, vou compartilhar:

  • o poder do SQL e por que estamos usando
  • armadilhas comuns e melhores práticas

o que é SQL e por que você deve usá-lo?SQL foi inicialmente desenvolvido pela IBM no início dos anos 1970, adotado pela Oracle mais tarde naquela década, e tem sido usado em bancos de dados relacionais desde então. Em 1986, o SQL foi adotado como padrão pelo American National Standards Institute (ANSI).

à medida que o volume de dados cresceu ao longo do tempo, novos bancos de dados e tecnologias surgiram. Um deles é o Big Data, para conjuntos de dados muito complexos para os aplicativos tradicionais de processamento de dados lidarem. Comum em big Data, os bancos de dados NoSQL não são baseados no modelo relacional. Às vezes, eles são chamados não apenas de SQL, para enfatizar que podem suportar uma linguagem de consulta semelhante a SQL. Alguns exemplos de bancos de dados e bibliotecas não relacionais mais recentes que suportam SQL incluem:

  • Apache Faísca Mecanismo de análise
  • Google Grandes Consulta gerenciados analytics armazém de dados
  • Facebook Presto e AWS Athena
  • Elástico de pesquisa
  • Python de Análise de Dados (Biblioteca de pandasql)

SQL é utilizada tanto em OLTP (Processamento de Transações) e OLAP (Processamento Analítico), apesar de os dois serem muito diferentes casos de uso:

Trecho 1: pandasql

Processamento de Transações Processamento Analítico
uso Comum bancos de dados Relacionais Dados Grandes bancos de dados
a utilização de SQL Insert, update, e consultas por muitos usuários simultaneamente consultas Select. Algumas Consultas de cada vez
Escala de computação Pequeno Grande – tabelas inteiras
duração de Consulta Fast Pode ser horas ou mais
O desempenho de funcionalidades relacionadas com Índices e chaves Partições de dados redundantes (por caso de uso)
funções Analíticas usados com pouca frequência Comum

Antes de mergulhar em SQL, aqui está um exemplo do uso de SQL fora das bases de dados do Python Análise de Dados da Biblioteca:

Código Saída
import pandas as pdimport pandasql as psdata_frame = pd.DataFrame(, , , ], columns=)sql = """SELECT name, COUNT(DISTINCT id) FROM data_frame GROUP BY NAME """print(ps.sqldf(sql, locals()))
 Name COUNT(DISTINCT ID)0 James 11 John 22 Ted 1

Como mostram esses exemplos, o SQL está vivo e bem em mais recentes e mais populares tecnologias.

vamos dar uma olhada mais de perto em seu poder.

SQL Functions Examples

a parte mais forte do SQL são suas funções. Aqui estão os três tipos de funções que o SQL suporta:

Tipo Escopo Redução de registros Exemplos
Simples registro Único Não CASE … WHEN 1 THEN column_1 WHEN 2 THEN column_2 ELSE 3 END
GREATEST (column_1, column_2, column_3)
COALESCE (column_1)
Agregados (grupo) Grupo de registros Sim SUM(column_1)
AVG(column_1)
Analítico Grupo de registros Não SUM(column_1) OVER (PARTITION BY column_2)
LEAD/LAG - value of a field in the next or previous record

recomendamos indo sobre as funções de seu mecanismo de consulta para entender as suas capacidades. Aqui, por exemplo, você pode encontrar a documentação do Presto sobre funções agregadas e funções analíticas.

Convenções de codificação SQL recomendadas (nomenclatura e muito mais)

para obter o poder do SQL, recomendamos o uso de Convenções de codificação. Isso melhorará a escrita e a leitura e melhorará o trabalho em equipe. Aqui está uma pequena lista dos mais importantes:

Nomes e caso Use sublinhados onde você iria, naturalmente incluem um espaço no nome (sobrenome) > last_name)
maiúsculas para palavras-chave e funções (SELECT, FROM, WHERE, EM SOMA,…)
minúsculas para tudo o resto: variáveis, nomes de tabelas, colunas, etc.
use explicitamente palavras-chave em junções use palavras-chave: CROSS, INNER … ON, LEFT … ON, RIGHT… ON
evite junções como parte da cláusula WHERE: Selecione … de table_a, table_b onde …
Aliasing – sempre inclua a palavra-chave AS
evite formulários aninhados, use com evite selecionar … de (selecionar …)

Use com cláusulas! Dessa forma, você pode evitar consultas complexas e criar consultas legíveis passo a passo

evitar a junção IN / NOT in-Use.

Espaçamento de Linha Antes E OU Após cada definição de palavra-chave
Incluir comentários Ambos /* e fechamento */ e — são aceitáveis
não use SELECT * Sim, ele é compacto e fácil mas novas colunas são adicionadas e incluído o resultado da consulta, sem necessidade. Escreva explicitamente o nome da coluna que você precisa

exemplos de consulta SQL: bom & Ruim

para mostrar a importância das Convenções de codificação, observe esses exemplos bons e ruins.

Tipo Bom Ruim
o Fluxo de Duas consultas simples Uma consulta complexa
Palavras-chave Maiúsculas Minúsculas, como outros de texto
Junte-se Explícito – fácil de entender Implícito – com outros predicados
Indentação espaçamento antes OU ajuda a conhecer o seu âmbito de aplicação Nenhum espaçamento antes OU. Maior condição será muito difícil de entender
Usado Não utilizado

Fragmento 4: Mau exemplo

select emp.department, sum(exp.amount) expensesfrom employees emp, expenses expwhere emp.department in (select departmentfrom employeeswhere department like '%development%' or department like '%management%'group by departmenthaving count(distinct id) > 50) and emp.id = exp.employee_idgroup by emp.department

Fragmento 5: exemplo de Bom

-- filter: Development and Management departments with more than 50 employeesWITH dept AS (SELECT departmentFROM employeesWHERE department LIKE '%Development%'OR department LIKE '%Management%'GROUP BY departmentHAVING Count(DISTINCT id) > 50)-- find expenses for each of the filtered departmentsSELECT emp.department,SUM(exp.amount) AS expensesFROM employees AS empINNER JOIN deptON emp.department = dept.departmentINNER JOIN expenses AS expON emp.id = exp.employee_idGROUP BY emp.department

Aqui está uma boa ferramenta online para o SQL formatação que você pode usar: https://sqlformat.org/

SQL Solução de Problemas de Erros & Armadilhas

Como qualquer outra língua, SQL tem suas próprias armadilhas que você deve estar atento para poupar tempo e evitar erros na sua aplicação.

A primeira causa de erros de SQL & erros – valores Null

Fragmento 6: NULL resultado da consulta

SELECT NULL = NULL --> NULLSELECT NULL != NULL --> NULLSELECT NULL IS NULL --> trueSELECT 1 + NULL --> NULL SELECT SUM(num_column) WHERE FALSE --> NULL

Para superar esse problema, você deve usar aglutinam sempre que há um perigo:

Fragmento 7: Superar Nulo em associações

FROM t1INNER JOIN t2 ON COALESCE(t1.name, '') = COALESCE(t2.name, '')

SQL Índices e Partições Não São Usados

Nós gastamos um monte de tempo a construir o nosso esquema de banco de dados adicionando índices e declarando partições. Mas não obteremos o desempenho que queremos Se pararmos por aí-temos que garantir que nosso mecanismo de banco de dados os use.

Por exemplo, vamos supor que temos um índice na coluna nome:

SELECT first_nameFROM employeeWHERE UPPER(first_name) LIKE 'A%'
o Índice não é usado
SELECT first_nameFROM employeeWHERE first_name LIKE 'A%' OR first_name LIKE 'a%'
Índice usado

pode haver alterações de uma implementação para outra, mas o conceito é válido em qualquer implementação de SQL.

como você garante que seus índices e partições estejam sendo usados? Analise seu plano de execução de consulta. Em algumas implementações SQL, você pode usar a palavra-chave EXPLAIN. No Oracle, por exemplo, é chamado explicar plano.

funções analíticas armadilhas na ordem de avaliação SQL

este é importante se você estiver usando uma função analítica, pois mal-entendido a ordem de avaliação pode levar a bugs. Por exemplo:

Snippet 8: função analítica com filtro

SELECT first_name, last_name, department, COUNT(1) OVER (PARTITION BY department) AS size_of_departmentFROM employeesWHERE last_name LIKE 'A%'

obteremos o número de funcionários no departamento com um sobrenome começando com A, em vez de todos os funcionários do Departamento. Lembre-se de que as funções analíticas são executadas por último.

padronização SQL

sintaxe de data e hora, nulos, concatenação de string, sensibilidade de caso de comparação e outros aspectos do SQL variam de um fornecedor para outro – dificultando a movimentação de um aplicativo entre fornecedores.

tente usar apenas SQL padrão. Por exemplo – use COALESCE em vez de ISNULL (SQL Server) ou NVL(Oracle).

Prevent SQL Injection (SQLi) Attacks

uma Armadilha de segurança bem conhecida em aplicativos da web é o SQL injection attack – uma técnica de injeção de código que ataca aplicativos inserindo instruções em um campo para execução. Esses ataques geralmente são usados para roubar dados, Alterar dados ou alterar permissões no banco de dados atacado.

a prevenção de ataques de injeção SQL começa durante o desenvolvimento e deve ser seguida pelo uso de scanners e firewalls de aplicativos da web para proteger seu aplicativo. Consulte a postagem do blog SQL Injection para obter exemplos e maneiras de evitar tais ataques.

encerrar

SQL é ótimo. Eu pessoalmente Prefiro linguagem baseada em SQL a qualquer outra linguagem de consulta ou API. No entanto, você precisa usá-lo corretamente para facilitar seu trabalho. Comece construindo seu esquema corretamente e depois use Convenções de codificação e outras opções, como as funções com palavra-chave e janela.

não se esqueça de manter as armadilhas conhecidas em mente. E, se você estiver escrevendo um aplicativo, proteja-o da injeção SQL.

o post Introdução ao SQL: Exemplos, melhores práticas e armadilhas apareceram primeiro no Blog.

*** este é um blog Sindicado de rede de blogueiros de segurança do Blog de autoria de Ori Nakar. Leia o post original em: https://www.imperva.com/blog/introduction-to-sql-examples-best-practices-and-pitfalls/

Related Post

Leave A Comment