Articles

Bevezetés Az SQL-be: példák, bevált gyakorlatok és buktatók

Posted by admin

az SQL (strukturált lekérdezési nyelv) több mint fél évszázada velünk van, és nem fog egyhamar eltűnni. Népszerű mind a hagyományos relációs adatbázisokban, mind az újabb NoSQL adatbázis-technológiákban, az SQL-t széles körben használják adatelemzéshez, nagy adatfeldolgozáshoz, kódolási nyelvekhez stb.

az SQL rajongója vagyok. Ez egy erőteljes, mégis egyszerű nyelv. Ebben a bejegyzésben, megosztom:

  • az SQL ereje és miért használjuk
  • gyakori buktatók és bevált gyakorlatok

mi az SQL és miért érdemes használni?

az SQL-t eredetileg az IBM fejlesztette ki az 1970-es évek elején, az Oracle pedig még abban az évtizedben átvette, és azóta is használják relációs adatbázisokban. 1986 – ban az SQL-t szabványként fogadta el az amerikai Nemzeti Szabványügyi Intézet (ANSI).

ahogy az adatok mennyisége az idő múlásával nőtt, új adatbázisok és technológiák jelentek meg. Ezek egyike a Big Data, mivel az adatkészletek túl bonyolultak a hagyományos adatfeldolgozó alkalmazások kezeléséhez. A nagy adatokban gyakori, hogy a NoSQL adatbázisok nem a relációs modellen alapulnak. Néha nem csak SQL-nek hívják őket, hogy hangsúlyozzák, hogy támogathatnak egy SQL-szerű lekérdezési nyelvet. Néhány példa az SQL-t támogató újabb nem relációs adatbázisokra és könyvtárakra:

  • Apache Spark Analytics Engine
  • Google Big Query-managed analytics adattárház
  • Facebook Presto és AWS Athena
  • Elastic search
  • Python adatelemző Könyvtár (pandasql)

az SQL-t széles körben használják mind az OLTP-ben (Tranzakciófeldolgozás) , mind az OLAP-ban (analitikus feldolgozás), annak ellenére, hogy a kettő nagyon eltérő felhasználási eset:

1. részlet: pandasql

tranzakciós feldolgozás analitikus feldolgozás
közös használat relációs adatbázisok Big Data adatbázisok
SQL-használat sok felhasználó beszúrása, frissítése és lekérdezése egyidejűleg lekérdezések kiválasztása. Kevés lekérdezés egy időben
számítási skála kicsi nagy-teljes asztalok
a lekérdezés időtartama gyors lehet óra vagy több
teljesítményhez kapcsolódó jellemzők indexek és kulcsok partíciók, redundáns adatok (Használati esetenként)
analitikai funkciók ritkán használt gyakori

mielőtt belemerülne az SQL-be, íme egy példa az SQL használatára az adatbázisokon kívül — a Python adatelemző Könyvtára:

Kód kimenet
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

amint ezek a példák mutatják, az SQL él és jól van a legújabb és legnépszerűbb technológiákban.

nézzük meg közelebbről a hatalmát.

SQL függvények példák

az SQL legerősebb része a funkciói. Íme az SQL által támogatott három típusú funkció:

Típus hatókör rekordok csökkentése példák
egyszerű Egyetlen felvétel nem CASE … WHEN 1 THEN column_1 WHEN 2 THEN column_2 ELSE 3 END
GREATEST (column_1, column_2, column_3)
COALESCE (column_1)
összesített (csoport szerint) rekordok csoportja Igen SUM(column_1)
AVG(column_1)
analitikus rekordok csoportja nem SUM(column_1) OVER (PARTITION BY column_2)
LEAD/LAG - value of a field in the next or previous record

javasoljuk, hogy nézze át a lekérdező motor funkcióit, hogy megértse annak képességeit. Itt található például a Presto dokumentációja az aggregált függvényekről és az analitikus függvényekről.

ajánlott SQL kódolási konvenciók (elnevezés és egyebek)

az SQL erejének megszerzéséhez javasoljuk a kódolási konvenciók használatát. Ez javítja mind az írást, mind az olvasást, és javítja a csapatmunkát. Itt van egy rövid lista a fontosabbakról:

nevek és kisbetűk használjon aláhúzásokat, ahol természetesen szóközt tartalmaz a névben (vezetéknév -> last_name)
nagybetűk kulcsszavakhoz és függvényekhez (SELECT, FROM, WHERE, IN, SUM…)
kisbetűk minden máshoz: változók, táblázatnevek, oszlopok stb.
expliciten használja a kulcsszavakat az illesztésekben használja a kulcsszavakat: kereszt, belső… be, bal… be, jobb… tovább
kerülje az illesztéseket a WHERE záradék részeként: Válassza a … lehetőséget a table_a-ból, table_b-ből, ahol …
Aliasing-mindig tartalmazza az AS kulcsszót
kerülje a beágyazott űrlapokat, használja helyette kerülje a választást … tól től (válasszon …)

használja a záradékokkal! Így elkerülheti az összetett lekérdezéseket, és lépésről lépésre olvasható lekérdezéseket hozhat létre

Kerülje el a használatban lévő / nem használt csatlakozást.

sorköz Minden kulcsszó meghatározása előtt és után
Megjegyzések mind a/*, mind a záró * / és — elfogadhatók
ne használja SELECT * Igen, ez a kompakt és egyszerű, de az új oszlopok hozzá, és tartalmazza a lekérdezés eredménye szükség nélkül. Írja be kifejezetten a szükséges oszlopnevet

SQL lekérdezési példák: jó & rossz

hogy megmutassa, mennyire fontosak a kódolási konvenciók, kérjük, nézze meg ezeket a jó és rossz példákat.

Típus rossz
Flow két egyszerű lekérdezés egy összetett lekérdezés
kulcsszavak nagybetűk kisbetűk, mint más szöveg
Csatlakozz Explicit-könnyen érthető Implicit-más, ahol predikátumok
behúzás sorköz előtt, vagy segít megismerni annak hatókörét nincs sorköz előtt vagy előtt. A nagyobb állapotot sokkal nehéz megérteni
használt nem használt

4. kódrészlet: rossz példa

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

5. kódrészlet: jó példa

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

itt van egy szép online eszköz az SQL formázáshoz, amelyet használhat: https://sqlformat.org/

SQL problémák hibaelhárítás-hibák & buktatók

mint minden más nyelvnek, az SQL-nek is vannak saját buktatói, amelyekkel tisztában kell lennie, hogy időt takarítson meg és megakadályozza az alkalmazás hibáit.

az SQL hibák első oka & bugs – Null értékek

6.kódrészlet: NULL lekérdezés eredménye

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

a probléma leküzdéséhez használja a coalesce-t, amikor veszély áll fenn:

7. kódrészlet: A null leküzdése a joins-ban

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

az SQL indexek és partíciók not used

sok időt töltünk adatbázis-sémánk felépítésével indexek hozzáadásával és partíciók deklarálásával. De nem kapjuk meg a kívánt teljesítményt, ha itt megállunk — meg kell győződnünk arról, hogy Adatbázis-motorunk használja őket.

tegyük fel például, hogy van egy indexünk a first_name oszlopban:

SELECT first_nameFROM employeeWHERE UPPER(first_name) LIKE 'A%'
nem használt Index
SELECT first_nameFROM employeeWHERE first_name LIKE 'A%' OR first_name LIKE 'a%'
használt Index

lehet, hogy változások történnek egyik megvalósításról a másikra, de a koncepció minden SQL megvalósításban érvényes.

hogyan győződhet meg arról, hogy az indexeket és partíciókat használják? Elemezze a lekérdezés végrehajtási tervét. Néhány SQL implementációban használhatja az EXPLAIN kulcsszót. Az Oracle – ben például magyarázó tervnek hívják.

analitikai függvények buktatók az SQL – értékelési sorrendben

ez fontos, ha analitikus függvényt használ, mivel az értékelési sorrend félreértése hibákat okozhat. Például:

8.kódrészlet: analitikus függvény szűrővel

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

az osztály összes alkalmazottja helyett a vezetéknévvel kezdődő alkalmazottak számát kapjuk az osztályon. Ne feledje, hogy az analitikus függvények utoljára kerülnek végrehajtásra.

SQL szabványosítás

dátum és idő szintaxis, nullák, karakterlánc – összefűzés, összehasonlító esetérzékenység és az SQL egyéb szempontjai szállítónként változnak-ami megnehezíti az alkalmazások mozgatását a gyártók között.

próbáljon csak szabványos SQL-t használni. Például-használja a COALESCE-t ISNULL (SQL Server) vagy NVL(Oracle) helyett.

az SQL Injection (Sqli) támadások megelőzése

a webes alkalmazások jól ismert biztonsági buktatója az SQL injection attack – egy kódinjekciós technika, amely megtámadja az alkalmazásokat azáltal, hogy utasításokat illeszt be egy mezőbe végrehajtás céljából. Az ilyen támadásokat általában adatok ellopására, adatok megváltoztatására vagy engedélyek megváltoztatására használják a megtámadott adatbázisban.

az SQL injekciós támadások megelőzése a fejlesztés során kezdődik, és ezt szkennerek és webes alkalmazások tűzfalainak használata követi az alkalmazás védelme érdekében. Kérjük, olvassa el az SQL Injection blogbejegyzést az ilyen támadások megelőzésének példáiról és módjairól.

csomagolás

az SQL nagyszerű. Én személy szerint jobban szeretem az SQL-alapú nyelvet, mint bármely más lekérdezési nyelvet vagy API-t. A munka megkönnyítése érdekében azonban helyesen kell használnia. Kezdje a séma helyes felépítésével, majd később használja a kódolási konvenciókat és más lehetőségeket, például a kulcsszóval és az ablak funkcióval.

ne felejtsd el szem előtt tartani az ismert buktatókat. Ha pedig alkalmazást ír, akkor védje meg az SQL injekciótól.

az SQL bevezetése: Példák, legjobb gyakorlatok és buktatók jelentek meg először a blogon.

*** ez egy biztonsági bloggerek hálózat szindikált blog Blog szerzője Ori Nakar. Olvassa el az eredeti bejegyzést: https://www.imperva.com/blog/introduction-to-sql-examples-best-practices-and-pitfalls/

Related Post

Leave A Comment