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

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

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:

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:

Esempi di query SQL: Buono & Cattivo

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

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

SELECT first_nameFROM employeeWHERE first_name LIKE 'A%' OR first_name LIKE 'a%'

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/