Articles

Introducere în SQL: Exemple, cele mai bune practici și capcane

Posted by admin

SQL (Structured Query Language) a fost cu noi pentru mai mult de o jumătate de secol și nu va dispărea oricând în curând. Popular atât în bazele de date relaționale tradiționale, cât și în noile tehnologii de baze de date NoSQL, SQL este utilizat pe scară largă pentru analiza datelor, procesarea datelor mari, limbaje de codare și multe altele.

sunt un fan al SQL. Este un limbaj puternic, dar simplu. În acest post, voi împărtăși:

  • puterea SQL și de ce îl folosim
  • capcane comune și cele mai bune practici

ce este SQL și de ce ar trebui să-l utilizați?

SQL a fost inițial dezvoltat de IBM la începutul anilor 1970, adoptat de Oracle mai târziu în acel deceniu și a fost folosit în bazele de date relaționale de atunci. În 1986, SQL a fost adoptat ca standard de către American National Standards Institute (ANSI).

pe măsură ce volumul de date a crescut în timp, au apărut noi baze de date și tehnologii. Unul dintre acestea este Big Data, pentru seturi de date prea complexe pentru aplicațiile tradiționale de prelucrare a datelor. Frecvente în Big Data, bazele de date NoSQL nu se bazează pe modelul relațional. Uneori sunt numite nu numai SQL, pentru a sublinia faptul că pot suporta un limbaj de interogare asemănător SQL. Câteva exemple de baze de date și biblioteci non-relaționale mai noi care acceptă SQL includ:

  • Apache Spark Analytics Engine
  • Google mare interogare-gestionate de analiză Data warehouse
  • Facebook Presto și AWS Athena
  • căutare Elastic
  • Python Data Analysis Library (pandasql)

SQL este utilizat pe scară largă atât în OLTP (procesarea tranzacțiilor), cât și în OLAP( procesarea analitică), în ciuda faptului că cele două sunt cazuri de utilizare foarte diferite:

fragment 1: pandasql

procesarea tranzacțiilor procesarea analitică
utilizare comună baze de date relaționale baze de date Big Data
utilizare SQL inserare, actualizare și interogări de către mulți utilizatori simultan Selectați interogări. Puține întrebări la un moment dat
scară de calcul mic tabele întregi mari
durata interogării Fast poate fi de ore sau mai mult
caracteristici legate de performanță indici și chei partiții, date redundante (pe caz de utilizare)
Funcții analitice utilizate rar frecvente

înainte de a vă scufunda în SQL, iată un exemplu de utilizare SQL în afara bazelor de date-Biblioteca de analiză a datelor Python:

Cod ieșire
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

după cum arată aceste exemple, SQL este viu și bine în cele mai noi și mai populare tehnologii.

să aruncăm o privire mai atentă asupra puterii sale.

Exemple de funcții SQL

cea mai puternică parte a SQL este funcțiile sale. Aici sunt cele trei tipuri de funcții SQL sprijină:

Tip domeniul de aplicare reducerea înregistrărilor Exemple
simplu înregistrare unică nu CASE … WHEN 1 THEN column_1 WHEN 2 THEN column_2 ELSE 3 END
GREATEST (column_1, column_2, column_3)
COALESCE (column_1)
agregat (grup după) grup de înregistrări Da SUM(column_1)
AVG(column_1)
analitic grup de înregistrări nu SUM(column_1) OVER (PARTITION BY column_2)
LEAD/LAG - value of a field in the next or previous record

vă recomandăm să treceți peste funcțiile motorului dvs. de interogare pentru a înțelege capacitățile acestuia. Aici, de exemplu, puteți găsi documentația Presto privind funcțiile agregate și funcțiile analitice.

convenții de codificare SQL recomandate (denumire și multe altele)

pentru a obține puterea SQL, vă recomandăm să utilizați convenții de codare. Acest lucru va îmbunătăți atât scrierea, cât și citirea și va îmbunătăți munca în echipă. Iată o scurtă listă a celor mai importante:

nume și majuscule utilizați subliniere în care ați include în mod natural un spațiu în nume (nume de familie- >last_name)
majuscule pentru cuvinte cheie și funcții (selectați, de la, unde, în, sumă…)
minuscule pentru orice altceva: variabile, nume de tabele, coloane etc.
utilizați în mod explicit cuvinte cheie În se alătură utilizați cuvinte cheie: CROSS, INNER … ON, LEFT … ON, RIGHT… ON
evitați se alătură ca parte a clauzei WHERE: Selectați … din table_a, table_b unde …
Aliasing-includeți întotdeauna cuvântul cheie AS
evitați formularele imbricate, utilizați cu evitați selectați … din (Selectați …)

utilizați cu clauze! În acest fel puteți evita interogările complexe și puteți crea interogări lizibile pas cu pas

evitați alăturarea în / nu în utilizare.

spațiere între linii înainte și sau după fiecare definiție a cuvântului cheie
Include comentarii ambele / * și de închidere * / și – sunt acceptabile
nu utilizați Selectați * da, este compact și ușor, dar noi coloane sunt adăugate și incluse la rezultatul interogării fără a fi nevoie. Scrieți Explicit numele coloanei de care aveți nevoie

Exemple de interogare SQL: bun & rău

pentru a vă arăta cât de importante sunt convențiile de codificare, vă rugăm să priviți aceste exemple bune și rele.

Tip bun rău
Flux două interogări simple o interogare complexă
cuvinte cheie majuscule minuscule, ca și alte texte
Alăturați-vă Explicit-ușor de înțeles Implicit-cu alte unde predicate
indentare spațiere înainte sau ajută la cunoașterea domeniului său de aplicare fără spațiere înainte sau. Condiție mai mare va fi mult mai greu de înțeles
folosit neutilizat

fragment 4: exemplu rău

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

fragment 5: exemplu bun

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

Iată un instrument Online frumos pentru formatarea SQL pe care îl puteți utiliza: https://sqlformat.org/

SQL probleme depanare-Greșeli & capcanele

ca orice altă limbă, SQL are propriile capcane pe care ar trebui să fie conștienți de a economisi timp și pentru a preveni bug-uri în cererea dumneavoastră.

prima cauză pentru erori SQL & bugs – valori Null

fragment 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

pentru a depăși această problemă, ar trebui să utilizați coalesce ori de câte ori există un pericol:

fragment 7: depăși Null În se alătură

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

indicii SQL și partiții sunt neutilizat

petrecem mult timp construind schema bazei noastre de date adăugând indici și declarând partiții. Dar nu vom obține performanța pe care o dorim dacă ne oprim aici — trebuie să ne asigurăm că motorul nostru de baze de date le folosește.

de exemplu, să presupunem că avem un index pe coloana first_name:

SELECT first_nameFROM employeeWHERE UPPER(first_name) LIKE 'A%'
indicele nu este utilizat
SELECT first_nameFROM employeeWHERE first_name LIKE 'A%' OR first_name LIKE 'a%'
Index utilizat

pot exista modificări de la o implementare la alta, dar conceptul este valabil în orice implementare SQL.

cum vă asigurați că indicii și partițiile dvs. sunt utilizate? Analizați planul de execuție a interogării. În unele implementări SQL, puteți utiliza cuvântul cheie EXPLAIN. În Oracle, de exemplu, se numește explică planul.

Funcții analitice capcane în SQL – evaluare ordine

aceasta este importantă dacă utilizați o funcție analitică, ca neînțelegere ordinea de evaluare poate duce la bug-uri. De exemplu:

fragment 8: funcție analitică cu filtru

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

vom obține numărul de angajați din departament cu un nume de familie care începe cu A, în loc de toți angajații din departament. Amintiți-vă că funcțiile analitice sunt executate ultima dată.

standardizare SQL

data și ora sintaxă, NULLs, concatenare șir, sensibilitate caz comparație și alte aspecte ale SQL variază de la un Furnizor la altul – ceea ce face greu pentru a muta o aplicație între furnizori.

încercați să utilizați numai SQL standard. De exemplu-utilizați COALESCE în loc de ISNULL(SQL Server) sau NVL (Oracle).

Prevenirea atacurilor SQL Injection (SQLi)

o capcană de securitate bine cunoscută în aplicațiile web este atacul SQL injection – o tehnică de injectare a codului care atacă aplicațiile prin inserarea declarațiilor într-un câmp pentru execuție. Astfel de atacuri sunt de obicei folosite pentru a fura date, a schimba date sau a schimba permisiunile din Baza de date atacată.

Prevenirea atacurilor de injecție SQL începe în timpul dezvoltării și ar trebui urmată de utilizarea scanerelor și a firewall-urilor pentru aplicații web pentru a vă proteja aplicația. Vă rugăm să consultați SQL Injection blog post pentru exemple și modalități de a preveni astfel de atacuri.

ambalaj până

SQL este mare. Eu personal prefer limbaj bazat pe SQL la orice alt limbaj de interogare sau API. Cu toate acestea, trebuie să o utilizați corect pentru a vă ușura munca. Începeți prin construirea corectă a schemei și utilizați ulterior convenții de codare și alte opțiuni, cum ar fi cu funcții de cuvinte cheie și ferestre.

nu uitați să țineți cont de capcanele cunoscute. Și, dacă scrieți o aplicație, apoi proteja de SQL Injection.

post Introducere în SQL: Exemple, cele mai bune practici și capcane au apărut pentru prima dată pe Blog.

*** acesta este un blog de rețea de bloggeri de securitate sindicalizat de la Blog-ul scris de Ori Nakar. Citiți postarea originală la: https://www.imperva.com/blog/introduction-to-sql-examples-best-practices-and-pitfalls/

Related Post

Leave A Comment