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

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

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:

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:

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.

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

SELECT first_nameFROM employeeWHERE first_name LIKE 'A%' OR first_name LIKE 'a%'

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/