Articles

Eksempler, bedste praksis og faldgruber

Posted by admin

har været med os i mere end et halvt århundrede, og det går ikke væk når som helst snart. Populær i både traditionelle relationsdatabaser og nyere teknologier til Noskl-databaser, bruges i vid udstrækning til dataanalyse, Big Data-behandling, kodningssprog og mere.

jeg er fan af GL. Det er et stærkt, men alligevel simpelt sprog. I dette indlæg deler jeg:

  • styrken i KVL og hvorfor vi bruger det
  • fælles faldgruber og bedste praksis

hvad er KVL, og hvorfor skal du bruge det?

blev oprindeligt udviklet af IBM i begyndelsen af 1970 ‘ erne, vedtaget af Oracle senere det årti, og har været brugt i relationsdatabaser lige siden. I 1986 blev det vedtaget som en standard af American National Standards Institute (ANSI).

da datamængden er vokset over tid, er der kommet nye databaser og teknologier frem. En af disse er Big Data, for datasæt for komplekse til traditionelle databehandlingsapplikationer at håndtere. Fælles i Big Data, noskl databaser er ikke baseret på den relationelle model. De kaldes undertiden ikke kun for at understrege, at de muligvis understøtter et sprog, der ligner en forespørgsel. Nogle eksempler på nyere ikke-relationelle databaser og biblioteker, der understøtter:

  • Apache Spark Analytics Engine
  • Google Big forespørgsel-managed analytics data lager
  • Facebook Presto og AAU Athena
  • elastisk søgning
  • Python dataanalyse Bibliotek)

det er meget udbredt både i OLTP (transaktionsbehandling) og OLAP (analytisk behandling), på trods af at de to er meget forskellige brugssager:

Uddrag 1: pandaskl

transaktionsbehandling analytisk behandling
almindelig brug relationsdatabaser store datadatabaser
Indsæt, Opdater og forespørgsler fra mange brugere samtidigt Vælg forespørgsler. Få forespørgsler ad gangen
Beregningsskala lille Stor-hele tabeller
Forespørgselsvarighed hurtig kan være timer eller mere
Ydelsesrelaterede funktioner indekser og nøgler partitioner, redundante data (pr. brug-case)
analytiske funktioner sjældent anvendt almindelig

før du dykker ned i CCL, er her et eksempel på CCL-brug uden for databaser — Pythons Dataanalysebibliotek:

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

som disse eksempler viser, er vi i LIVE og godt i de nyeste og mest populære teknologier.

lad os se nærmere på dens magt.

eksempler på KVL-funktioner

den stærkeste del af KVL er dens funktioner. Her er de tre typer funktioner:

Type omfang reduktion af poster eksempler
enkel enkelt rekord Nej CASE … WHEN 1 THEN column_1 WHEN 2 THEN column_2 ELSE 3 END
GREATEST (column_1, column_2, column_3)
COALESCE (column_1)
aggregeret (gruppe efter) gruppe af poster Ja SUM(column_1)
AVG(column_1)
analytisk gruppe af poster Nej SUM(column_1) OVER (PARTITION BY column_2)
LEAD/LAG - value of a field in the next or previous record

Vi anbefaler at gå over funktionerne i din forespørgselsmotor for at forstå dens muligheder. Her kan du for eksempel finde Prestos dokumentation om aggregatfunktioner og analytiske funktioner.

anbefalede KODNINGSKONVENTIONER (navngivning og mere)

for at få kraften i KODNINGSKONVENTIONER anbefaler vi stærkt at bruge kodningskonventioner. Dette vil forbedre både skrivning og læsning og forbedre teamarbejdet. Her er en kort liste over de vigtigste:

navne og sag brug understregninger, hvor du naturligvis vil medtage et mellemrum i navnet (efternavn -> last_name)
store bogstaver for nøgleord og funktioner (Vælg, fra, hvor, I, SUM…)
små bogstaver for alt andet: variabler, tabelnavne, kolonner osv.
brug eksplicit nøgleord i joinforbindelser brug nøgleord: kryds, indre… til, venstre… til, højre… til
undgå sammenføjninger som en del af hvor-klausulen: Vælg … fra table_a, table_b hvor …
Aliasing-Inkluder altid as-nøgleordet
undgå indlejrede formularer, brug med i stedet undgå vælg … fra (Vælg …)

brug med klausuler! På denne måde kan du undgå komplekse forespørgsler og oprette trinvise læsbare forespørgsler

undgå I / ikke i brug deltage.

linjeafstand før og efter hver søgeordsdefinition
Medtag kommentarer både / * og lukning * / og — er acceptable
brug ikke SELECT * Ja, det er kompakt og nemt, men nye kolonner tilføjes og medtages til dit forespørgselsresultat uden behov. Skriv eksplicit det kolonnenavn, du har brug for

gode & dårlige

for at vise dig, hvor vigtige kodningskonventionerne er, skal du se på disse gode og dårlige eksempler.

Type god dårlig
strøm to enkle forespørgsler en kompleks forespørgsel
nøgleord store bogstaver små bogstaver, ligesom anden tekst
Deltag eksplicit-let at forstå Implicit – med andre hvor prædikater
indrykning linjeafstand før eller hjælper med at kende dens omfang ingen linjeafstand før eller. Større tilstand vil være meget svært at forstå
brugt ikke brugt

Snippet 4: dårligt eksempel

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

Snippet 5: godt eksempel

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

her er et godt online værktøj til formatering, som du kan bruge: https://sqlformat.org/

fejl & faldgruber

som ethvert andet sprog har vi sine egne faldgruber, som du bør være opmærksom på for at spare tid og forhindre fejl i din ansøgning.

den første årsag til fejl & bugs – Null værdier

Snippet 6: null forespørgselsresultat

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

for at overvinde dette problem skal du bruge coalesce, når der er fare:

Snippet 7: overvinde Null i joinforbindelser

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

indekser og partitioner bruges ikke

vi bruger meget tid på at opbygge vores databaseskema ved at tilføje indekser og erklære partitioner. Men vi får ikke den ydelse, vi ønsker, hvis vi stopper der — vi skal sørge for, at vores databasemotor bruger dem.

lad os for eksempel antage, at vi har et indeks på kolonnen first_name:

SELECT first_nameFROM employeeWHERE UPPER(first_name) LIKE 'A%'
indeks ikke brugt
SELECT first_nameFROM employeeWHERE first_name LIKE 'A%' OR first_name LIKE 'a%'
indeks anvendt

der kan være ændringer fra en implementering til en anden, men konceptet er gyldigt i enhver implementering.

Hvordan sørger du for, at dine indekser og partitioner bliver brugt? Analyser din forespørgselsudførelsesplan. I nogle af disse implementeringer kan du bruge nøgleordet forklar. I Oracle kaldes det for eksempel forklar PLAN.

analytiske funktioner faldgruber i EVALUERINGSRÆKKEFØLGE

denne er vigtig, hvis du bruger en analytisk funktion, da misforståelse af evalueringsordren kan føre til fejl. For eksempel:

Uddrag 8: analytisk funktion med filter

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

vi får antallet af medarbejdere i afdelingen med et efternavn, der begynder med A, i stedet for alle medarbejdere i afdelingen. Husk at analytiske funktioner udføres sidst.

standardisering

syntaks For dato og klokkeslæt, nuller, strengsammenkædning, sammenligning af sagsfølsomhed og andre aspekter af standardisering varierer fra en leverandør til en anden – hvilket gør det svært at flytte en applikation mellem leverandører.

prøv kun at bruge standard kvm. For eksempel-brug COALESCE i stedet for ISNULL (NVL Server) eller NVL(Oracle).

undgå indsprøjtningsangreb

en velkendt sikkerhedsgrube i internetapplikationer er indsprøjtningsangrebet – en kodeinjektionsteknik, der angriber applikationer ved at indsætte udsagn i et felt til udførelse. Sådanne angreb bruges normalt til at stjæle data, ændre data eller ændre tilladelser i den angrebne database.

forebyggelse af indsprøjtningsangreb starter under udviklingen og bør efterfølges af brugen af scannere og brandvægge til internetapplikation for at beskytte din applikation. Se venligst vores blogpost for eksempler og måder at forhindre sådanne angreb på.

indpakning

kvm er fantastisk. Jeg foretrækker personligt sprog baseret på et hvilket som helst andet forespørgselssprog eller API. Du skal dog bruge det korrekt for at gøre dit arbejde lettere. Start med at opbygge dit skema korrekt, og brug senere kodningskonventioner og andre muligheder, som f.eks.

glem ikke at holde de kendte faldgruber i tankerne. Og hvis du skriver en ansøgning, skal du beskytte den mod injektion.

posten Introduktion til KVL: Eksempler, bedste praksis og faldgruber dukkede først op på bloggen.

*** dette er en sikkerhed bloggere netværk syndikeret blog fra Blog forfattet af Ori Nakar. Læs det oprindelige indlæg på: https://www.imperva.com/blog/introduction-to-sql-examples-best-practices-and-pitfalls/

Related Post

Leave A Comment