Articles

Introduzione a SQL: Esempi, best practice e insidie

Posted by admin

SQL (Structured Query Language) è stato con noi per più di mezzo secolo e non sta andando via in qualunque momento presto. Popolare sia nei database relazionali tradizionali che nelle nuove tecnologie di database NoSQL, SQL è ampiamente utilizzato per l’analisi dei dati, l’elaborazione di Big Data, i linguaggi di codifica e altro ancora.

Sono un fan di SQL. È un linguaggio potente ma semplice. In questo post, condividerò:

  • La potenza di SQL e perché lo stiamo usando
  • Insidie comuni e best practice

Cos’è SQL e perché dovresti usarlo?

SQL è stato inizialmente sviluppato da IBM nei primi anni 1970, adottato da Oracle più tardi quel decennio, ed è stato utilizzato in database relazionali da allora. Nel 1986, SQL è stato adottato come standard dall’American National Standards Institute (ANSI).

Man mano che il volume dei dati è cresciuto nel tempo, sono emersi nuovi database e nuove tecnologie. Uno di questi è Big Data, per insiemi di dati troppo complessi per le applicazioni di elaborazione dati tradizionali da affrontare. Comune nei Big Data, i database NoSQL non sono basati sul modello relazionale. A volte vengono chiamati Non solo SQL, per sottolineare che possono supportare un linguaggio di query simile a SQL. Alcuni esempi di database e librerie non relazionali più recenti che supportano SQL includono:

  • Apache Spark Analytics Engine
  • Google Big Query – managed analytics Data warehouse
  • Facebook Presto e AWS Athena
  • Elastic search
  • Python Data Analysis Library (pandasql)

SQL è ampiamente utilizzato sia in OLTP (Transaction Processing) che OLAP (Analytic Processing), nonostante i due siano casi d’uso molto diversi:

Snippet 1: pandasql

Elaborazione delle Transazioni Elaborazione Analitica
uso Comune database Relazionali Grande database di Dati
utilizzo SQL Insert, update, e query da molti utenti contemporaneamente query di selezione. Pochi Query alla volta
Scala di calcolo Piccolo Grande intero tavoli
Query durata Veloce Può essere ore o più
Prestazioni relative caratteristiche Indici e chiavi Partizioni, dati ridondanti (per ogni caso d’uso)
funzioni Analitiche di Rado usato Comuni

Prima di tuffarsi in SQL, ecco un esempio di SQL utilizzo al di fuori di basi di dati — Python Analisi dei Dati di Libreria:

Codice Output
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

Come mostrano questi esempi, SQL è vivo e vegeto nelle più recenti e più popolari tecnologie.

Diamo un’occhiata più da vicino al suo potere.

Esempi di funzioni SQL

La parte più forte di SQL sono le sue funzioni. Ecco i tre tipi di funzioni supportate da SQL:

Tipo Ambito Riduzione dei record Esempi
Semplice record No CASE … WHEN 1 THEN column_1 WHEN 2 THEN column_2 ELSE 3 END
GREATEST (column_1, column_2, column_3)
COALESCE (column_1)
Aggregati (group by) Gruppo di record SUM(column_1)
AVG(column_1)
Analitica Gruppo di record No SUM(column_1) OVER (PARTITION BY column_2)
LEAD/LAG - value of a field in the next or previous record

Si consiglia di andare oltre le funzioni del vostro motore delle query di capire le sue capacità. Qui, ad esempio, è possibile trovare la documentazione di Presto su funzioni aggregate e funzioni analitiche.

Convenzioni di codifica SQL consigliate (denominazione e altro)

Per ottenere la potenza di SQL, consigliamo vivamente di utilizzare le convenzioni di codifica. Ciò migliorerà sia la scrittura che la lettura e migliorerà il lavoro di squadra. Ecco una breve lista di quelli più importanti:

Nomi e case Usa i caratteri di sottolineatura in cui includeresti naturalmente uno spazio nel nome (cognome -> last_name)
Maiuscolo per parole chiave e funzioni (SELECT, FROM, WHERE, IN, SUM…)
Minuscolo per tutto il resto: variabili, nomi di tabelle, colonne ecc.
Usa esplicitamente le parole chiave Nei join usa le parole chiave: CROSS, INNER ON ON, LEFT ON ON, RIGHT Evita i join come parte della clausola WHERE: SELEZIONA Ali FROM table_a, table_b DOVE Ali
Aliasing-Includi sempre la parola chiave AS
Evita i moduli nidificati, usa invece CON Evita SELECT FROM FROM(SELECT SELECT)

Usa CON le clausole! In questo modo è possibile evitare query complesse e creare query leggibili passo-passo

Evitare il JOIN IN / NON IN uso.

Interlinea Prima E / O Dopo ogni definizione parola chiave
Includere i commenti I /* e */ di chiusura e sono accettabili
non utilizzare SELECT * Sì, è compatto e facile, ma le nuove colonne aggiunto e incluso il risultato di una query, senza che sia necessario. Scrivi esplicitamente il nome della colonna che ti serve

Esempi di query SQL: Buono & Cattivo

Per mostrarti quanto siano importanti le convenzioni di codifica, guarda questi esempi buoni e cattivi.

Tipo Buona Male
Flusso Due semplici query Una query complessa
parole chiave Maiuscole Minuscolo, come altri di testo
Join Esplicito, facile da capire Implicita, con altri in CUI predicati
Rientro interlinea prima O aiuta a conoscere il suo ambito di applicazione No interlinea prima O. Grande condizione, sarà molto difficile da capire
Usato Non utilizzato

Frammento 4: Cattivo esempio

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

Frammento di 5: Buon esempio

-- 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

Qui c’è un bel strumento online per SQL formattazione che è possibile utilizzare: https://sqlformat.org/

SQL problemi di Risoluzione dei Problemi Errori di & Insidie

Come qualsiasi altra lingua, SQL ha il suo insidie che si dovrebbe essere a conoscenza di risparmiare tempo ed evitare errori nell’applicazione.

La prima causa di errori SQL & bug – valori Null

Frammento 6: NULL risultato della query

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

Per ovviare a questo problema si dovrebbe utilizzare la funzione coalesce ogni volta che c’è un pericolo:

Frammento 7: Superare Null in join

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

SQL Indici e le Partizioni Non Vengono Utilizzati

spendiamo un sacco di tempo a costruire il nostro schema di database aggiungendo indici e dichiarazione di partizioni. Ma non otterremo le prestazioni che vogliamo se ci fermiamo lì-dobbiamo assicurarci che il nostro motore di database li usi.

Per esempio, supponiamo di avere un indice sulla colonna nome:

SELECT first_nameFROM employeeWHERE UPPER(first_name) LIKE 'A%'
Indice non utilizzato
SELECT first_nameFROM employeeWHERE first_name LIKE 'A%' OR first_name LIKE 'a%'
Indice utilizzato

Ci possono essere variazioni da una attuazione a un altro, ma il concetto è valido in qualsiasi implementazione di SQL.

Come ti assicuri che i tuoi indici e le tue partizioni vengano utilizzati? Analizza il tuo piano di esecuzione delle query. In alcune implementazioni SQL, è possibile utilizzare la parola chiave EXPLAIN. In Oracle, ad esempio, si chiama EXPLAIN PLAN.

Funzioni analitiche Insidie nell’ordine di valutazione SQL

Questo è importante se si utilizza una funzione analitica, poiché un malinteso nell’ordine di valutazione può portare a bug. Ad esempio:

Snippet 8: Funzione analitica con filtro

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

Otterremo il numero di dipendenti nel reparto con un cognome che inizia con A, invece di tutti i dipendenti nel reparto. Ricorda che le funzioni analitiche vengono eseguite per ultime.

Standardizzazione SQL

Sintassi di data e ora, NULL, concatenazione di stringhe, sensibilità al caso di confronto e altri aspetti di SQL variano da un fornitore all’altro, rendendo difficile spostare un’applicazione tra i fornitori.

Prova a utilizzare solo SQL standard. Ad esempio, utilizzare COALESCE invece di ISNULL (SQL Server) o NVL(Oracle).

Prevenire attacchi SQL Injection (SQLi)

Una ben nota trappola di sicurezza nelle applicazioni Web è l’attacco SQL injection – una tecnica di iniezione di codice che attacca le applicazioni inserendo istruzioni in un campo per l’esecuzione. Tali attacchi vengono solitamente utilizzati per rubare dati, modificare i dati o modificare le autorizzazioni nel database attaccato.

La prevenzione degli attacchi SQL Injection inizia durante lo sviluppo e dovrebbe essere seguita dall’uso di scanner e firewall per applicazioni Web per proteggere l’applicazione. Si prega di fare riferimento al post sul blog SQL Injection per esempi e modi per prevenire tali attacchi.

Avvolgere

SQL è fantastico. Personalmente preferisco il linguaggio basato su SQL a qualsiasi altro linguaggio di query o API. Tuttavia, è necessario utilizzarlo correttamente per semplificare il lavoro. Inizia costruendo correttamente lo schema e in seguito usa le convenzioni di codifica e altre opzioni, come la parola chiave WITH e le funzioni della finestra.

Non dimenticare di tenere a mente le insidie conosciute. E, se stai scrivendo un’applicazione, proteggila da SQL Injection.

Il post Introduzione a SQL: Esempi, Best Practice e insidie apparso prima sul Blog.

* * * Si tratta di una rete di blogger di sicurezza sindacato blog dal Blog scritto da Ori Nakar. Leggi il post originale a: https://www.imperva.com/blog/introduction-to-sql-examples-best-practices-and-pitfalls/

Related Post

Leave A Comment