Articles

Introduction à SQL: Exemples, Meilleures pratiques et pièges

Posted by admin

SQL (Langage de requête structuré) est avec nous depuis plus d’un demi-siècle et il ne disparaîtra pas de sitôt. Populaire dans les bases de données relationnelles traditionnelles et les nouvelles technologies de bases de données NoSQL, SQL est largement utilisé pour l’analyse de données, le traitement de Big Data, les langages de codage, etc.

Je suis un fan de SQL. C’est un langage puissant mais simple. Dans ce post, je vais partager:

  • La puissance de SQL et pourquoi nous l’utilisons
  • Pièges courants et meilleures pratiques

Qu’est-ce que SQL et pourquoi devriez-vous l’utiliser ?

SQL a été initialement développé par IBM au début des années 1970, adopté par Oracle plus tard dans la décennie, et a été utilisé dans les bases de données relationnelles depuis. En 1986, SQL a été adopté comme norme par l’American National Standards Institute (ANSI).

À mesure que le volume de données a augmenté au fil du temps, de nouvelles bases de données et technologies ont vu le jour. L’un d’eux est le Big Data, pour des ensembles de données trop complexes pour les applications de traitement de données traditionnelles. Courantes dans le Big Data, les bases de données NoSQL ne sont pas basées sur le modèle relationnel. Ils sont parfois appelés Non seulement SQL, pour souligner qu’ils peuvent prendre en charge un langage de requête de type SQL. Voici quelques exemples de bases de données et de bibliothèques non relationnelles plus récentes prenant en charge SQL ::

  • Moteur d’analyse Apache Spark
  • Entrepôt de données Google Big Query–Managed analytics
  • Facebook Presto et AWS Athena
  • Recherche élastique
  • Bibliothèque d’analyse de données Python (pandasql)

SQL est largement utilisé à la fois dans OLTP (Traitement des transactions) et OLAP (Traitement Analytique), bien que les deux soient des cas d’utilisation très différents :

Extrait 1: NFDN

Traitement des transactions Traitement analytique
Utilisation courante Bases de données relationnelles Bases de données Big Data
Utilisation SQL Insertion, mise à jour et requêtes simultanées par de nombreux utilisateurs Sélectionnez les requêtes. Peu de requêtes à la fois
Échelle de calcul Petite Grande – tables entières
Durée de la requête Rapide Peut être de plusieurs heures ou plus
Caractéristiques liées aux performances Index et clés Partitions, données redondantes (par cas d’utilisation)
Fonctions analytiques Rarement utilisées Communes

Avant de plonger dans SQL, voici un exemple d’utilisation de SQL en dehors des bases de données — la bibliothèque d’analyse de données de Python:

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

Comme le montrent ces exemples, SQL est bien vivant dans les technologies les plus récentes et les plus populaires.

Regardons de plus près sa puissance.

Exemples de fonctions SQL

La partie la plus forte de SQL est ses fonctions. Voici les trois types de fonctions prises en charge par SQL:

Type Portée Réduction des enregistrements Exemples
Simple Enregistrement unique Non CASE … WHEN 1 THEN column_1 WHEN 2 THEN column_2 ELSE 3 END
GREATEST (column_1, column_2, column_3)
COALESCE (column_1)
Agrégées (groupées par) Groupe d’enregistrements Oui SUM(column_1)
AVG(column_1)
Analyse Groupe d’enregistrements Non SUM(column_1) OVER (PARTITION BY column_2)
LEAD/LAG - value of a field in the next or previous record

Nous vous recommandons de passer en revue les fonctions de votre moteur de requête pour comprendre ses capacités. Ici, par exemple, vous pouvez trouver la documentation de Presto sur les fonctions d’agrégation et les fonctions analytiques.

Conventions de codage SQL recommandées (Nommage et plus)

Pour profiter de la puissance de SQL, nous vous recommandons fortement d’utiliser des conventions de codage. Cela améliorera à la fois l’écriture et la lecture et améliorera le travail d’équipe. Voici une courte liste des plus importants:

Noms et casse Utilisez des traits de soulignement où vous inclurez naturellement un espace dans le nom (nom de famille – > nom de famille)
Majuscules pour les mots-clés et les fonctions (SELECT, FROM, WHERE, IN, SUM))
Minuscules pour tout le reste: variables, noms de table, colonnes, etc.
Utilisez explicitement des mots clés Dans les jointures utilisez des mots clés: CROSS, INNER ON ON, LEFT ON ON, RIGHT ON ON
Évitez les jointures dans le cadre de la clause WHERE: SÉLECTIONNEZ table FROM table_a, table_b OÙAli
Aliasing – Incluez toujours le mot-clé AS
Évitez les formulaires imbriqués, utilisez AVEC à la place Évitez SELECT FROMFROM (SELECT …)

Utilisez AVEC les clauses! De cette façon, vous pouvez éviter les requêtes complexes et créer des requêtes lisibles étape par étape

Éviter la JOINTURE IN / NOT IN–Use.

Interligne Avant ET, OU Après chaque définition de mot clé
Inclure les commentaires À la fois /* et closing */ et — sont acceptables
N’utilisez pas SELECT * Oui, c’est compact et facile, mais de nouvelles colonnes sont ajoutées et incluses dans le résultat de votre requête sans avoir besoin. Écrivez explicitement le nom de colonne dont vous avez besoin

Exemples de requêtes SQL: Bon & Mauvais

Pour vous montrer l’importance des conventions de codage, veuillez regarder ces bons et mauvais exemples.

Type Bon Mauvais
Flux Deux requêtes simples Une requête complexe
Mots-clés Majuscules Minuscules, comme les autres textes
Join Explicite – facile à comprendre Implicite – avec d’autres prédicats WHERE
Indentation Interligne avant OU aide à connaître sa portée Pas d’interligne avant OU. Une condition plus grande sera beaucoup plus difficile à comprendre
Utilisé Non utilisé

Extrait 4: Mauvais exemple

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

Extrait 5: Bon exemple

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

Voici un bon outil en ligne pour le formatage SQL que vous pouvez utiliser: https://sqlformat.org/

Dépannage des problèmes SQL – Erreurs & Pièges

Comme tout autre langage, SQL a ses propres pièges dont vous devez être conscient pour gagner du temps et éviter les bogues dans votre application.

La première cause des erreurs SQL & bugs – Valeurs nulles

Extrait 6: Résultat de la requête NULL

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

Pour résoudre ce problème, vous devez utiliser coalesce chaque fois qu’il y a un danger:

Extrait 7: Surmonter Null dans les jointures

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

Les index et partitions SQL sont Non utilisé

Nous passons beaucoup de temps à construire notre schéma de base de données en ajoutant des index et en déclarant des partitions. Mais nous n’obtiendrons pas les performances souhaitées si nous nous arrêtons là — nous devons nous assurer que notre moteur de base de données les utilise.

Par exemple, supposons que nous ayons un index sur la colonne first_name:

SELECT first_nameFROM employeeWHERE UPPER(first_name) LIKE 'A%'
Index non utilisé
SELECT first_nameFROM employeeWHERE first_name LIKE 'A%' OR first_name LIKE 'a%'
Index utilisé

Il peut y avoir des changements d’une implémentation à l’autre, mais le concept est valide dans n’importe quelle implémentation SQL.

Comment vous assurez-vous que vos index et partitions sont utilisés? Analysez votre plan d’exécution des requêtes. Dans certaines implémentations SQL, vous pouvez utiliser le mot clé EXPLAIN. Dans Oracle, par exemple, cela s’appelle EXPLIQUER LE PLAN.

Pièges des fonctions analytiques dans l’ordre d’évaluation SQL

Celui-ci est important si vous utilisez une fonction analytique, car une mauvaise compréhension de l’ordre d’évaluation peut entraîner des bogues. Par exemple :

Extrait 8: Fonction analytique avec filtre

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

Nous obtiendrons le nombre d’employés dans le département avec un nom de famille commençant par Un, au lieu de tous les employés du département. Rappelez-vous que les fonctions analytiques sont exécutées en dernier.

Normalisation SQL

La syntaxe de date et d’heure, les valeurs nulles, la concaténation de chaînes, la sensibilité à la casse de comparaison et d’autres aspects de SQL varient d’un fournisseur à l’autre, ce qui rend difficile le déplacement d’une application entre les fournisseurs.

Essayez d’utiliser SQL standard uniquement. Par exemple, utilisez COALESCE au lieu d’ISNULL (SQL Server) ou NVL (Oracle).

Prévenir les attaques par injection SQL (SQLi)

Un écueil de sécurité bien connu dans les applications Web est l’attaque par injection SQL – une technique d’injection de code qui attaque les applications en insérant des instructions dans un champ pour exécution. Ces attaques sont généralement utilisées pour voler des données, modifier des données ou modifier les autorisations de la base de données attaquée.

La prévention des attaques par injection SQL commence pendant le développement et doit être suivie de l’utilisation de scanners et de pare-feu d’application Web pour protéger votre application. Veuillez vous référer à l’article de blog sur l’injection SQL pour des exemples et des moyens de prévenir de telles attaques.

Conclure

SQL est génial. Personnellement, je préfère le langage basé sur SQL à tout autre langage de requête ou API. Cependant, vous devez l’utiliser correctement pour faciliter votre travail. Commencez par construire votre schéma correctement et utilisez plus tard des conventions de codage et d’autres options, comme les fonctions AVEC mot-clé et fenêtre.

N’oubliez pas de garder à l’esprit les pièges connus. Et, si vous écrivez une application, protégez-la de l’injection SQL.

L’article Introduction à SQL: Exemples, Meilleures Pratiques et pièges sont apparus en premier sur le Blog.

*** Ceci est un blog syndiqué du réseau de blogueurs de sécurité à partir du blog écrit par Ori Nakar. Lisez le message original à: https://www.imperva.com/blog/introduction-to-sql-examples-best-practices-and-pitfalls/

Related Post

Leave A Comment