Articles

Introduksjon til SQL: Eksempler, Beste Praksis og Fallgruver

Posted by admin

SQL (Structured Query Language) har vært med oss i mer enn et halvt århundre, og det går ikke bort når som helst snart. Populær i både tradisjonelle relasjonsdatabaser og nyere nosql-databaseteknologier, ER SQL mye brukt til dataanalyse, stor Databehandling, kodingsspråk og mer.

jeg er en fan AV SQL. Det er et kraftig, men likevel enkelt språk. I denne artikkelen vil jeg dele:

  • KRAFTEN I SQL og hvorfor vi bruker DEN
  • Vanlige fallgruver og beste praksis

HVA ER SQL og Hvorfor Du Bør Bruke DET?

SQL ble opprinnelig utviklet av IBM tidlig på 1970-tallet, adoptert av Oracle senere det tiåret, og har blitt brukt i relasjonsdatabaser siden den gang. I 1986 BLE SQL vedtatt som en standard Av American National Standards Institute (ANSI).

etter hvert som datamengden har vokst over tid, har nye databaser og teknologier oppstått. En Av Disse Er Big Data, for datasett som er for komplekse for tradisjonelle databehandlingsprogrammer å håndtere. Vanlig I Big Data, NoSQL databaser er ikke basert på relasjonsmodellen. DE kalles Noen GANGER IKKE BARE SQL, for å understreke at de kan støtte ET SQL-lignende spørrespråk. Noen eksempler på nyere ikke-relasjonsdatabaser og biblioteker som støtter SQL inkluderer:

  • Apache Spark Analytics Engine
  • google Stor Spørring – administrert analyse datalager
  • Facebook Presto og AWS Athena
  • Elastisk søk
  • Python Data Analysis Library (pandasql)

SQL er mye brukt både I Oltp (Transaction Processing) og OLAP (Analytic Processing), til tross for at de to er svært forskjellige brukstilfeller:

Snippet 1: pandasql

Transaksjonsbehandling Analytisk Behandling
Vanlig bruk Relasjonsdatabaser big Data databaser
SQL-bruk Sett inn, oppdater og spørringer av mange brukere samtidig Velg spørringer. Få Spørsmål om gangen
Beregningsskala Liten Stor-hele tabeller
Spørringsvarighet Fast kan være timer eller mer
Ytelsesrelaterte funksjoner Indekser og nøkler Partisjoner, redundante data (per brukstilfelle)
Analytiske funksjoner Sjelden brukt Vanlige

Før du dykker INN I SQL, er det et eksempel på SQL-bruk utenfor databaser-Pythons Data Analysis Library:

Kode Utgang
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 eksemplene viser, ER SQL levende og godt i de nyeste og mest populære teknologiene.

La oss ta en nærmere titt på sin makt.

EKSEMPLER PÅ SQL-Funksjoner

den sterkeste delen AV SQL er FUNKSJONENE. HER er de tre typer FUNKSJONER SQL støtter:

Type Omfang Reduksjon av poster Eksempler
Enkel Enkel post Nei CASE … WHEN 1 THEN column_1 WHEN 2 THEN column_2 ELSE 3 END
GREATEST (column_1, column_2, column_3)
COALESCE (column_1)
Samlet (gruppe etter) gruppe med poster Ja SUM(column_1)
AVG(column_1)
Analytisk gruppe poster Nei SUM(column_1) OVER (PARTITION BY column_2)
LEAD/LAG - value of a field in the next or previous record

Vi anbefaler å gå over funksjonene til spørring motoren for å forstå sine evner. Her kan Du for Eksempel Finne Prestos dokumentasjon på aggregerte funksjoner og analysefunksjoner.

Anbefalte SQL-Kodingskonvensjoner(Navngivning og Mer)

for Å få KRAFTEN TIL SQL anbefaler vi sterkt å bruke kodingskonvensjoner. Dette vil forbedre både skriving og lesing, og forbedre teamarbeid. Her er en kort liste over de viktigste:

Navn og sak bruk understrek hvor du naturlig vil inkludere et mellomrom i navnet (etternavn -> last_name)
Store bokstaver for søkeord og funksjoner (VELG, FRA, HVOR, I, SUM…)
Små bokstaver for alt annet: variabler, tabellnavn, kolonner etc.
Eksplisitt bruk søkeord i sammenføyninger bruk søkeord: KRYSS, INDRE … PÅ, VENSTRE … PÅ, HØYRE … på
Unngå sammenføyninger som en del AV where-klausulen: VELG … FRA table_a, table_b HVOR …
Aliasing – inkluder alltid as-nøkkelordet
Unngå nestede skjemaer, bruk med i stedet Unngå VELG … FRA (VELG …)

Bruk med klausuler! På denne måten kan du unngå komplekse spørringer, og opprette trinnvise lesbare spørringer

Unngå I / IKKE I Bruk BLI MED.

Linjeavstand Før og, Eller ETTER hvert søkeord definisjon
Inkluder kommentarer både /* og lukking */ og — er akseptable
Ikke bruk VELG * ja, det er kompakt og enkelt, men nye kolonner legges til og inkluderes i spørringsresultatet uten behov. Skriv eksplisitt kolonnenavnet du trenger

SQL Query Eksempler: God & Dårlig

for å vise deg hvor viktig kodingskonvensjonene er, vennligst se på disse gode og dårlige eksemplene.

Type Bra Dårlig
Flow To enkle spørringer en kompleks spørring
Nøkkelord Store Bokstaver Små Bokstaver, som annen tekst
Bli Med Eksplisitt-lett å forstå Implisitt – med andre hvor predikater
Innrykk Linjeavstand før Eller hjelper med å vite omfanget Ingen linjeavstand før ELLER. Større tilstand vil være mye vanskelig å forstå
Brukt Ikke brukt

Snippet 4: Dårlig 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 en fin online verktøy FOR SQL formatering som du kan bruke: https://sqlformat.org/

SQL Problemer Feilsøking-Feil & Fallgruver

SOM alle andre språk, HAR SQL sine egne fallgruver som du bør være klar over for å spare tid og hindre feil i søknaden din.

den første årsaken TIL SQL – feil & feil-Nullverdier

Kodebiten 6: NULLSPØRRINGSRESULTAT

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

HVIS du vil overvinne dette problemet, bør du bruke koalesce når det er fare:

Kodebiten 7: Overvinne Null i sammenføyninger

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

SQL-Indekser og Partisjoner Er ikke brukt

vi bruker mye tid på å bygge vårt databaseskjema Ved Å Legge til indekser og erklære partisjoner. Men vi får ikke den ytelsen vi vil ha hvis vi stopper der-vi må sørge for at databasemotoren bruker dem.

la oss for eksempel anta at vi har en indeks i kolonnen first_name:

SELECT first_nameFROM employeeWHERE UPPER(first_name) LIKE 'A%'
Indeks ikke brukt
SELECT first_nameFROM employeeWHERE first_name LIKE 'A%' OR first_name LIKE 'a%'
Indeks brukt

det kan være endringer fra en implementering til en annen, men konseptet er gyldig i ENHVER SQL-implementering.

hvordan sørger du for at indeksene og partisjonene dine blir brukt? Analyser utførelsesplanen for spørringen. I NOEN SQL-implementeringer kan du bruke FORKLARINGSORDET. I Oracle, for Eksempel, kalles DET FORKLAR PLAN.

Analytiske Funksjoner Fallgruver I SQL-Evalueringsordre

Denne er viktig hvis du bruker en analytisk funksjon, da misforståelse av evalueringsordren kan føre til feil. For eksempel:

Utdrag 8: Analytisk funksjon 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 antall ansatte i avdelingen med et etternavn som begynner Med A, i stedet for alle ansatte i avdelingen. Husk at analytiske funksjoner utføres sist.

SQL – Standardisering

syntaks For Dato Og klokkeslett, NULLs, strengkjeding, sammenligning av saksfølsomhet og andre aspekter av SQL varierer fra en leverandør til EN annen-noe som gjør det vanskelig å flytte et program mellom leverandører.

Prøv bare å bruke standard SQL. For eksempel-bruk COALESCE i stedet FOR ISNULL(SQL Server) eller NVL (Oracle).

Forhindre SQL Injection (Sqli) – Angrep

EN velkjent sikkerhetsgruve i webapplikasjoner er SQL injection attack – en kodeinjeksjonsteknikk som angriper applikasjoner ved å sette inn setninger i et felt for utførelse. Slike angrep brukes vanligvis til å stjele data, endre data eller endre tillatelser i den angrep databasen.

Forebygging AV SQL-Injeksjonsangrep starter under utvikling, og bør følges av bruk av skannere og webapplikasjonsbrannmurer for å beskytte programmet. Se BLOGGINNLEGGET FOR SQL Injection for eksempler og måter å forhindre slike angrep på.

Innpakning

SQL er flott. Jeg personlig foretrekker SQL-basert språk til et annet spørrespråk eller API. Du må imidlertid bruke den riktig for å gjøre arbeidet enklere. Start med å bygge skjemaet riktig og senere bruke koding konvensjoner og andre alternativer, som med søkeord og vindu funksjoner.

ikke glem å holde de kjente fallgruvene i tankene. Og hvis du skriver et program, så beskytt det mot SQL-Injeksjon.

innlegget Introduksjon TIL SQL: Eksempler, Beste Praksis og Fallgruver dukket opp først På Bloggen.

*** Dette Er En Sikkerhet Bloggere Nettverk syndikert blogg Fra Blogg forfattet Av Ori Nakar. Les det opprinnelige innlegget på: https://www.imperva.com/blog/introduction-to-sql-examples-best-practices-and-pitfalls/

Related Post

Leave A Comment