Articles

Inleiding tot SQL: voorbeelden, Best Practices en valkuilen

Posted by admin

SQL (Structured Query Language) is al meer dan een halve eeuw bij ons en het zal niet snel verdwijnen. Populair in zowel traditionele relationele databases en nieuwere NoSQL databases technologieën, SQL wordt veel gebruikt voor data-analyse, Big Data-verwerking, codering talen, en meer.

ik ben een fan van SQL. Het is een krachtige, maar eenvoudige taal. In deze post, Ik zal delen:

  • de kracht van SQL en waarom we het gebruiken
  • gemeenschappelijke valkuilen en best practices

Wat is SQL en waarom zou u het moeten gebruiken?

SQL werd in eerste instantie ontwikkeld door IBM in de vroege jaren 1970, later dat decennium overgenomen door Oracle, en wordt sindsdien gebruikt in relationele databases. In 1986 werd SQL als standaard aangenomen door het American National Standards Institute (ANSI).

naarmate de hoeveelheid gegevens in de loop van de tijd is toegenomen, zijn nieuwe databases en technologieën ontstaan. Een daarvan is Big Data, voor datasets die te complex zijn voor traditionele gegevensverwerkingstoepassingen. Vaak in Big Data, NoSQL databases zijn niet gebaseerd op het relationele model. Ze worden soms niet alleen SQL genoemd, om te benadrukken dat ze een SQL-achtige query taal kunnen ondersteunen. Enkele voorbeelden van nieuwere niet-relationele databases en bibliotheken die SQL ondersteunen zijn::

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

SQL wordt veel gebruikt zowel in OLTP (transactieverwerking) en OLAP (analytische verwerking), ondanks dat de twee zeer verschillende use cases:

Snippet 1: pandasql

transactieverwerking analytische verwerking
algemeen gebruik relationele databases Big Data databases
SQL-gebruik invoegen, bijwerken en query ’s door veel gebruikers tegelijkertijd SELECT query’ s. Enkele Vragen op een moment
de Schaal van de berekening Klein Groot – volledige tabellen
Query duur Snel Kan het uren of meer
Performance gerelateerde functies Indices en de toetsen Partities, redundante gegevens (per use-case)
Analytische functies Zelden gebruikt Algemeen

duik in SQL, hier is een voorbeeld van SQL-gebruik buiten van databases — Python ‘ s Data-Analyse Bibliotheek:

Code 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

zoals deze voorbeelden laten zien, SQL is springlevend en goed in de nieuwste en meest populaire technologieën.

laten we eens een kijkje nemen op de kracht ervan.

SQL-functies voorbeelden

het sterkste deel van SQL is zijn functies. Hier zijn de drie soorten functies SQL ondersteunt:

Type Omvang Vermindering van records Voorbeelden
Eenvoudige Één record Geen CASE … WHEN 1 THEN column_1 WHEN 2 THEN column_2 ELSE 3 END
GREATEST (column_1, column_2, column_3)
COALESCE (column_1)
Geaggregeerd (group by) Groep records Ja SUM(column_1)
AVG(column_1)
Analyse Groep records Geen SUM(column_1) OVER (PARTITION BY column_2)
LEAD/LAG - value of a field in the next or previous record

Wij raden gaan over de functies van uw query-engine om zijn mogelijkheden te begrijpen. Hier vindt u bijvoorbeeld de documentatie van Presto over geaggregeerde functies en analytische functies.

aanbevolen SQL-Coderingsconventies (naamgeving en meer)

om de kracht van SQL te verkrijgen, raden we het gebruik van coderingsconventies ten zeerste aan. Dit zal zowel het schrijven als het lezen verbeteren en het teamwerk verbeteren. Hier is een korte lijst van de belangrijkste:

namen en hoofdletters gebruik underscores waarbij u natuurlijk een spatie in de naam (achternaam – > last_name)
hoofdletters voor sleutelwoorden en functies (SELECT, FROM, WHERE, in, SUM…)
kleine letters voor al het andere: variabelen, tabelnamen, kolommen enz.
gebruik expliciet trefwoorden in joins gebruik trefwoorden: CROSS, INNER… ON, LEFT… ON, RIGHT… ON
vermijd joins als onderdeel van de WHERE-clausule: Selecteer … uit table_a, table_b waar …
Aliasing-voeg altijd het sleutelwoord als toe
vermijd geneste formulieren, gebruik in plaats daarvan vermijd SELECT … FROM (SELECT …)

gebruik met clausules! Op deze manier kunt u complexe query ’s vermijden en stap-voor-stap leesbare query’ s

vermijden in / niet in – Use JOIN.

regelafstand Voor en, of na elke trefwoorddefinitie
opmerkingen zowel / * als slotopmerkingen * / en-zijn aanvaardbaar
gebruik SELECT * Ja, Het is compact en eenvoudig, maar nieuwe kolommen worden toegevoegd en toegevoegd aan uw zoekresultaat zonder een noodzaak. Schrijf expliciet de kolomnaam die u nodig hebt

voorbeelden van SQL-Query ‘ s: goed & slecht

om u te laten zien hoe belangrijk de coderingsconventies zijn, kijk naar deze goede en slechte voorbeelden.

Type Goede Slecht
Flow Twee eenvoudige query ‘ s Een complexe query
Trefwoorden Hoofdletters Kleine letters, net als andere tekst
Join Expliciete – gemakkelijk te begrijpen Impliciet – met andere WAAR predikaten
Inspringen regelafstand voor OF helpt bij het kennen van de werkingssfeer Geen regelafstand voor OF. Grotere voorwaarde zal veel moeilijk te begrijpen
Gebruikt Niet gebruikt

Fragment 4: Slecht voorbeeld

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

Fragment 5: Goed voorbeeld

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

Hier is een mooie online tool voor SQL opmaak die u kunt gebruiken: https://sqlformat.org/

SQL-Problemen Oplossen – Fouten & Valkuilen

Als elke andere taal SQL heeft zijn eigen valkuilen die u moet zich bewust zijn van bespaar jezelf de tijd en het voorkomen van fouten in uw aanvraag.

de eerste oorzaak voor SQL – fouten & bugs-Null-waarden

fragment 6: NULL query result

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

om dit probleem op te lossen moet u coalesce gebruiken wanneer er een gevaar is:

fragment 7: null overwinnen in joins

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

SQL-Indices en partities worden niet gebruikt

we besteden veel tijd aan het bouwen van ons databaseschema door indices toe te voegen en partities te declareren. Maar we zullen niet de prestaties krijgen die we willen als we daar stoppen — we moeten ervoor zorgen dat onze database engine ze gebruikt.

bijvoorbeeld, laten we aannemen dat we een index hebben op de kolom voornaam:

SELECT first_nameFROM employeeWHERE UPPER(first_name) LIKE 'A%'
Index niet gebruikt
SELECT first_nameFROM employeeWHERE first_name LIKE 'A%' OR first_name LIKE 'a%'
gebruikte Index

er kunnen veranderingen zijn van de ene implementatie naar de andere, maar het concept is geldig in elke SQL implementatie.

hoe zorgt u ervoor dat uw indices en partities worden gebruikt? Analyseer uw query uitvoeringsplan. In sommige SQL-implementaties kunt u het sleutelwoord EXPLAIN gebruiken. In Oracle, bijvoorbeeld, heet het plan uitleggen.

analytische functies valkuilen in SQL – Evaluatievolgorde

deze is belangrijk als u een analytische functie gebruikt, omdat het verkeerd begrijpen van de evaluatievolgorde tot bugs kan leiden. Bijvoorbeeld:

fragment 8: analytische functie met filter

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

we krijgen het aantal werknemers in de afdeling met een achternaam die begint met een, in plaats van alle werknemers in de afdeling. Vergeet niet dat analytische functies als laatste worden uitgevoerd.

SQL standaardisatie

datum en tijd syntaxis, NULLs, string concatenation, vergelijking hoofdlettergevoeligheid en andere aspecten van SQL variëren van de ene leverancier tot de andere – waardoor het moeilijk is om een toepassing tussen leveranciers te verplaatsen.

probeer alleen standaard SQL te gebruiken. Bijvoorbeeld-gebruik COALESCE in plaats van ISNULL (SQL Server) of NVL(Oracle).

Prevent SQL Injection (SQLi) Attacks

een bekende valkuil voor beveiliging in webtoepassingen is de SQL injection attack – een code-injectietechniek die toepassingen aanvalt door verklaringen in een veld in te voegen voor uitvoering. Dergelijke aanvallen worden meestal gebruikt om gegevens te stelen, gegevens te wijzigen of machtigingen in de aangevallen database te wijzigen.

het voorkomen van SQL-injectieaanvallen begint tijdens de ontwikkeling en moet worden gevolgd door het gebruik van scanners en firewalls voor webtoepassingen om uw toepassing te beschermen. Raadpleeg de SQL Injection blogpost voor voorbeelden en manieren om dergelijke aanvallen te voorkomen.

het inpakken van

SQL is groot. Persoonlijk geef ik de voorkeur aan SQL-gebaseerde taal boven elke andere query taal of API. U moet het echter correct gebruiken om uw werk gemakkelijker te maken. Begin met het bouwen van uw schema correct en later gebruik maken van codering conventies en andere opties, zoals de met trefwoord en venster functies.

vergeet niet de bekende valkuilen in gedachten te houden. En als u een toepassing schrijft, bescherm deze dan tegen SQL-injectie.

de post introductie tot SQL: Voorbeelden, Best Practices en valkuilen appeared first on Blog.

* * * This is a Security Bloggers Network syndicated blog from Blog author by Ori Nakar. Lees het oorspronkelijke bericht op: https://www.imperva.com/blog/introduction-to-sql-examples-best-practices-and-pitfalls/

Related Post

Leave A Comment