Articles

Úvod do SQL: příklady, osvědčené postupy a úskalí

Posted by admin

SQL (Structured Query Language) je s námi již více než půl století a v dohledné době nezmizí. Populární jak v tradičních relačních databázích, tak v novějších technologiích NoSQL databází, SQL je široce používán pro analýzu dat, zpracování velkých dat, kódovací jazyky a další.

jsem fanoušek SQL. Je to silný, ale jednoduchý jazyk. V tomto příspěvku, budu sdílet:

  • síla SQL a proč ji používáme
  • časté úskalí a osvědčené postupy

co je SQL a proč byste jej měli používat?

SQL byl původně vyvinut společností IBM na počátku 1970. let, přijat společností Oracle později v tomto desetiletí a od té doby se používá v relačních databázích. V roce 1986 byl SQL přijat jako standard americkým Národním normalizačním Institutem (ANSI).

vzhledem k tomu, že objem dat v průběhu času rostl, objevily se nové databáze a technologie. Jedním z nich jsou velká Data, pro datové sady příliš složité pro tradiční aplikace pro zpracování dat. Databáze NoSQL, které jsou běžné ve velkých datech, nejsou založeny na relačním modelu. Někdy se jim říká nejen SQL, aby zdůraznili, že mohou podporovat dotazovací jazyk podobný SQL. Některé příklady novějších nerelačních databází a knihoven, které podporují SQL, zahrnují:

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

SQL je široce používán jak v OLTP (Transaction Processing), tak v OLAP (Analytic Processing), přestože oba jsou velmi odlišné případy použití:

Snippet 1: pandy

transakční zpracování analytické zpracování
běžné použití relační databáze velké datové databáze
použití SQL vložení, aktualizace a dotazy mnoha uživatelů současně vyberte dotazy. Několik dotazů najednou
měřítko výpočtu malé Velké-celé tabulky
Délka dotazu rychlý může být hodin nebo více
funkce související s výkonem indexy a klíče oddíly, redundantní data (na případ použití)
analytické funkce zřídka používané časté

než se ponoříte do SQL, zde je příklad využití SQL mimo databáze-Knihovna analýzy dat Pythonu:

kód výstup
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

jak ukazují tyto příklady, SQL je naživu a dobře v nejnovějších a nejpopulárnějších technologiích.

podívejme se blíže na jeho sílu.

SQL Funkce příklady

nejsilnější částí SQL jsou jeho funkce. Zde jsou tři typy funkcí, které SQL podporuje:

Typ rozsah redukce záznamů příklady
jednoduchý jediný záznam ne CASE … WHEN 1 THEN column_1 WHEN 2 THEN column_2 ELSE 3 END
GREATEST (column_1, column_2, column_3)
COALESCE (column_1)
agregovaná (skupina podle) skupina záznamů Ano SUM(column_1)
AVG(column_1)
analytické skupina záznamů ne SUM(column_1) OVER (PARTITION BY column_2)
LEAD/LAG - value of a field in the next or previous record

doporučujeme projít si funkce vašeho dotazu, abyste porozuměli jeho schopnostem. Zde najdete například dokumentaci Presto o agregovaných funkcích a analytických funkcích.

doporučené konvence kódování SQL (pojmenování a další)

Chcete-li získat sílu SQL, důrazně doporučujeme používat konvence kódování. Tím se zlepší psaní i čtení a zlepší se týmová práce. Zde je krátký seznam těch důležitějších:

jména a písmena použijte podtržítka, kde byste přirozeně zahrnuli mezeru v názvu (příjmení -> last_name)
velká písmena pro klíčová slova a funkce (Vyberte, z, kde, v, součet…)
malá písmena pro všechno ostatní: proměnné, názvy tabulek, sloupce atd.
explicitně použijte klíčová slova v joinech použijte klíčová slova: kříž, vnitřní … na, vlevo… na, vpravo… na
Vyhněte se spojům jako součást klauzule WHERE: Vyberte… z table_a, table_b kde …
Aliasing – vždy obsahovat klíčové slovo AS
Vyhněte se vnořeným formulářům, použijte místo toho Vyhněte se výběru … z (vyberte …)

použijte s klauzulemi! Tímto způsobem se můžete vyhnout složitým dotazům a vytvářet krok za krokem čitelné dotazy

vyhnout se / nepoužívat se připojit.

řádkování před a nebo po každé definici klíčového slova
zahrnout Komentáře oba / * a uzavření * / a-jsou přijatelné
nepoužívejte SELECT * Ano, Je to kompaktní a snadné, ale nové sloupce jsou přidány a zahrnuty do výsledku dotazu bez potřeby. Napište explicitně název sloupce, který potřebujete

příklady dotazů SQL: dobrý & špatný

Chcete-li ukázat, jak důležité jsou konvence kódování, podívejte se na tyto dobré a špatné příklady.

Typ dobrý špatný
Tok dva jednoduché dotazy jeden komplexní dotaz
klíčová slova velká písmena malá písmena, like other text
připojit explicitní-snadno pochopitelné implicitní – s jinými, kde predikáty
odsazení řádkování před nebo pomáhá znát jeho rozsah žádné řádkování před nebo. Větší stav bude mnohem těžké pochopit
Používá se nepoužívá se

Snippet 4: špatný příklad

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: dobrý příklad

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

zde je pěkný online nástroj pro formátování SQL, který můžete použít: https://sqlformat.org/

SQL problémy Řešení problémů-chyby & úskalí

stejně jako jakýkoli jiný jazyk, SQL má své vlastní úskalí, které byste měli být vědomi ušetřit čas a zabránit chybám ve vaší aplikaci.

první příčina chyb SQL & bugs – Null values

Snippet 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

Chcete-li tento problém překonat, měli byste použít coalesce, kdykoli existuje nebezpečí:

Snippet 7: překonat Null v připojeních

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

SQL indexy a oddíly se nepoužívají

trávíme spoustu času budováním našeho databázového schématu přidáním indexů a deklarováním diskových oddílů. Ale nedostaneme výkon, který chceme, pokud se tam zastavíme — musíme se ujistit, že je náš databázový stroj používá.

Předpokládejme například, že máme index ve sloupci first_name:

SELECT first_nameFROM employeeWHERE UPPER(first_name) LIKE 'A%'
Index se nepoužívá
SELECT first_nameFROM employeeWHERE first_name LIKE 'A%' OR first_name LIKE 'a%'
použitý Index

může dojít ke změnám z jedné implementace do druhé, ale koncept je platný v jakékoli implementaci SQL.

jak se ujistíte, že jsou používány vaše indexy a oddíly? Analyzujte plán provádění dotazů. V některých implementacích SQL můžete použít klíčové slovo vysvětlit. Například v Oracle se tomu říká vysvětlit plán.

analytické funkce úskalí v SQL pořadí hodnocení

Toto je důležité, pokud používáte analytickou funkci, protože nedorozumění pořadí hodnocení může vést k chybám. Například:

Snippet 8: analytická funkce s filtrem

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

dostaneme počet zaměstnanců v oddělení s příjmením začínajícím na A, místo všech zaměstnanců v oddělení. Nezapomeňte, že analytické funkce jsou prováděny jako poslední.

standardizace SQL

syntaxe data a času, nuly, zřetězení řetězců, citlivost porovnávacích případů a další aspekty SQL se liší od jednoho dodavatele k druhému – což ztěžuje Přesun aplikace mezi dodavateli.

zkuste použít pouze standardní SQL. Například-použijte COALESCE místo ISNULL (SQL Server) nebo NVL (Oracle).

zabraňte útokům SQL Injection (SQLi)

známým bezpečnostním úskalím ve webových aplikacích je SQL injection attack – technika vstřikování kódu, která napadá aplikace vložením příkazů do pole pro provedení. Takové útoky se obvykle používají k odcizení dat, změně dat nebo změně oprávnění v napadené databázi.

prevence útoků SQL Injection začíná během vývoje a mělo by následovat použití skenerů a firewallů webových aplikací k ochraně vaší aplikace. Příklady a způsoby, jak těmto útokům zabránit, naleznete v blogu SQL Injection.

zabalení

SQL je skvělé. Osobně preferuji jazyk založený na SQL před jakýmkoli jiným dotazovacím jazykem nebo API. Musíte ji však používat správně, abyste usnadnili práci. Začněte vytvořením schématu správně a později použijte konvence kódování a další možnosti, jako je funkce s klíčovým slovem a oknem.

nezapomeňte mít na paměti známá úskalí. A pokud píšete aplikaci, chráňte ji před injekcí SQL.

příspěvek Úvod do SQL: Příklady, osvědčené postupy a úskalí se objevily jako první na blogu.

* * * Toto je syndikovaný blog blogerů z blogu, jehož autorem je Ori Nakar. Přečtěte si původní příspěvek na adrese: https://www.imperva.com/blog/introduction-to-sql-examples-best-practices-and-pitfalls/

Related Post