Articles

Introduktion till SQL: exempel, bästa praxis och fallgropar

Posted by admin

SQL (Structured Query Language) har varit med oss i mer än ett halvt sekel och det går inte bort när som helst snart. Populär i både traditionella relationsdatabaser och nyare NoSQL-databasteknologier, SQL används ofta för dataanalys, stor databehandling, kodningsspråk och mer.

jag är ett fan av SQL. Det är ett kraftfullt men ändå enkelt språk. I det här inlägget delar jag:

  • kraften i SQL och varför vi använder det
  • vanliga fallgropar och bästa praxis

vad är SQL och varför ska du använda det?

SQL utvecklades ursprungligen av IBM i början av 1970-talet, antogs av Oracle senare samma årtionde, och har använts i relationsdatabaser sedan dess. 1986 antogs SQL som standard av American National Standards Institute (ANSI).

när datamängden har vuxit över tiden har nya databaser och tekniker uppstått. En av dessa är Big Data, för datamängder som är för komplexa för traditionella databehandlingsapplikationer att hantera. Vanligt i Big Data är NoSQL-databaser inte baserade på relationsmodellen. De kallas ibland inte bara SQL, för att betona att de kan stödja ett SQL-liknande frågespråk. Några exempel på nyare icke-relationella databaser och bibliotek som stöder SQL inkluderar:

  • Apache Spark Analytics Engine
  • Google stora frågehanterade analytics data warehouse
  • Facebook Presto och AWS Athena
  • elastisk sökning
  • Python Data Analysis Library (pandasql)

SQL används ofta både i OLTP (transaktionsbehandling) och OLAP (analytisk bearbetning), trots att de två är mycket olika användningsfall:

Utdrag 1: pandasql

transaktionsbehandling analytisk bearbetning
gemensam användning relationsdatabaser big data-databaser
SQL-användning infoga, uppdatera och frågor av många användare samtidigt Välj frågor. Få frågor åt gången
Beräkningsskala liten Stor – hela tabeller
Frågelängd snabb kan vara timmar eller mer
prestandarelaterade funktioner index och nycklar partitioner, redundanta data( per användningsfall)
analytiska funktioner används sällan vanliga

innan du dyker in i SQL, här är ett exempel på SQL-användning utanför databaser-Pythons Dataanalysbibliotek:

kod utgång
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 dessa exempel visar är SQL levande och bra i den senaste och mest populära tekniken.

Låt oss titta närmare på dess kraft.

SQL-funktioner exempel

den starkaste delen av SQL är dess funktioner. Här är de tre typerna av funktioner SQL stöder:

Typ omfattning minskning av poster exempel
enkel enda post 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)
aggregerad (grupp efter) grupp av poster Ja SUM(column_1)
AVG(column_1)
analytisk grupp av poster Nej SUM(column_1) OVER (PARTITION BY column_2)
LEAD/LAG - value of a field in the next or previous record

vi rekommenderar att du går igenom funktionerna i din frågemotor för att förstå dess kapacitet. Här kan du till exempel hitta Prestos dokumentation om aggregerade funktioner och analytiska funktioner.

rekommenderade SQL-Kodningskonventioner (namngivning och mer)

för att få kraften i SQL rekommenderar vi starkt att du använder kodningskonventioner. Detta kommer att förbättra både skrivning och läsning och förbättra lagarbetet. Här är en kort lista över de viktigaste:

namn och fall använd understreck där du naturligtvis skulle inkludera ett mellanslag i namnet (efternamn -> efternamn)
versaler för nyckelord och funktioner (Välj, från, var, i, summa…)
gemener för allt annat: variabler, tabellnamn, kolumner etc.
använd uttryckligen nyckelord i kopplingar Använd nyckelord: kors, inre … på, vänster … på, höger… på
Undvik kopplingar som en del av where-klausulen: Välj … från table_a, table_b där …
Aliasing-inkludera alltid as-nyckelordet
Undvik kapslade formulär, använd istället Undvik välj … från (Välj …)

använd med klausuler! På så sätt kan du undvika komplexa frågor och skapa steg-för-steg läsbara frågor

Undvik i / inte i bruk gå.

radavstånd före och, eller efter varje sökordsdefinition
inkludera kommentarer både / * och stängning * / och-är acceptabla
använd inte SELECT * Ja, det är kompakt och enkelt, men nya kolumner läggs till och ingår i ditt frågeresultat utan behov. Skriv uttryckligen det kolumnnamn du behöver

SQL-frågeexempel: bra & dåligt

för att visa dig hur viktiga kodningskonventionerna är, titta på dessa bra och dåliga exempel.

Typ bra dåligt
flöde två enkla frågor en komplex fråga
nyckelord versaler gemener, som annan text
gå med Explicit-lätt att förstå Implicit-med andra där predikat
indrag radavstånd före eller hjälper till att känna till dess omfattning inget radavstånd före eller. Större tillstånd kommer att vara mycket svårt att förstå
används används inte

Utdrag 4: dåligt exempel

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

Utdrag 5: bra exempel

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

här är ett trevligt onlineverktyg för SQL-formatering som du kan använda: https://sqlformat.org/

SQL problem felsökning-misstag & fallgropar

precis som alla andra språk, SQL har sina egna fallgropar som du bör vara medveten om att spara tid och förhindra buggar i din ansökan.

den första orsaken till SQL – fel & buggar-null-värden

Utdrag 6: NULL-frågeresultat

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

för att lösa problemet bör du använda coalesce när det finns en fara:

Utdrag 7: övervinna Null i kopplingar

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

SQL-index och partitioner är används inte

vi spenderar mycket tid på att bygga vårt databasschema genom att lägga till index och förklara partitioner. Men vi får inte den prestanda vi vill ha om vi slutar där — vi måste se till att vår databasmotor använder dem.

låt oss till exempel anta att vi har ett index på kolumnen first_name:

SELECT first_nameFROM employeeWHERE UPPER(first_name) LIKE 'A%'
Index används inte
SELECT first_nameFROM employeeWHERE first_name LIKE 'A%' OR first_name LIKE 'a%'
Index används

det kan finnas ändringar från en implementering till en annan, men konceptet är giltigt i alla SQL-implementeringar.

Hur ser du till att dina index och partitioner används? Analysera din exekveringsplan för frågor. I vissa SQL-implementeringar kan du använda nyckelordet förklara. I Oracle kallas det till exempel förklara PLAN.

analytiska funktioner fallgropar i SQL-Utvärderingsorder

den här är viktig om du använder en analytisk funktion, eftersom missförstånd av utvärderingsordningen kan leda till fel. Till exempel:

Utdrag 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 antalet anställda på avdelningen med ett efternamn som börjar med A, istället för alla anställda på avdelningen. Kom ihåg att analytiska funktioner körs sist.

SQL standardisering

datum och tid syntax, nollor, sträng sammanlänkning, jämförelse skiftlägeskänslighet och andra aspekter av SQL varierar från en leverantör till en annan – vilket gör det svårt att flytta ett program mellan leverantörer.

Försök bara använda standard SQL. Till exempel-använd COALESCE istället för ISNULL (SQL Server) eller NVL(Oracle).

förhindra SQL Injection (SQLi) attacker

en välkänd säkerhetsgrop i webbapplikationer är SQL injection attack – en kodinjektionsteknik som attackerar applikationer genom att infoga uttalanden i ett fält för körning. Sådana attacker används vanligtvis för att stjäla data, ändra data eller ändra behörigheter i den attackerade databasen.

förhindra SQL injektion attacker startar under utveckling, och bör följas av användning av skannrar och webbapplikation brandväggar för att skydda din ansökan. Se SQL Injection blogginlägg för exempel och sätt att förhindra sådana attacker.

inslagning upp

SQL är stor. Jag föredrar personligen SQL-baserat språk till något annat frågespråk eller API. Du måste dock använda den korrekt för att göra ditt arbete enklare. Börja med att bygga ditt schema korrekt och använd senare kodningskonventioner och andra alternativ, som med nyckelord och fönsterfunktioner.

glöm inte att hålla de kända fallgroparna i åtanke. Och, om du skriver ett program, sedan skydda den från SQL-injektion.

inlägget introduktion till SQL: Exempel, bästa praxis och fallgropar dök upp först på bloggen.

*** Detta är en säkerhet bloggare nätverk syndikerade blogg Från blogg författad av Ori Nakar. Läs det ursprungliga inlägget på: https://www.imperva.com/blog/introduction-to-sql-examples-best-practices-and-pitfalls/

Related Post

Leave A Comment