Articles

Johdatus SQL: esimerkkejä, parhaita käytäntöjä ja sudenkuoppia

Posted by admin

SQL (strukturoitu kyselykieli) on ollut meillä jo yli puoli vuosisataa, eikä se katoa lähiaikoina. Suosittu sekä perinteisissä relaatiotietokannoissa että uudemmissa NoSQL-tietokantatekniikoissa, SQL: ää käytetään laajalti data-analytiikkaan, Big Data processingiin, koodauskieliin ja muuhun.

olen SQL – fani. Se on voimakas, mutta yksinkertainen kieli. Tässä viestissä Jaan:

  • SQL: n voima ja miksi sitä käytetään
  • yleiset sudenkuopat ja parhaat käytännöt

mikä on SQL ja miksi sitä kannattaa käyttää?

SQL: n kehitti alun perin IBM 1970-luvun alussa, Oracle myöhemmin samalla vuosikymmenellä, ja sitä on käytetty relaatiotietokannoissa siitä lähtien. Vuonna 1986 American National Standards Institute (ANSI) hyväksyi SQL: n standardiksi.

tiedon määrän kasvaessa ajan myötä on syntynyt uusia tietokantoja ja teknologioita. Yksi näistä on Big Data, sillä tietoaineistot ovat liian monimutkaisia perinteisten tietojenkäsittelysovellusten käsiteltäviksi. Yleistä Big datassa, NoSQL-tietokannat eivät perustu relaatiomalliin. Niitä kutsutaan joskus SQL: n lisäksi korostaakseen, että ne voivat tukea SQL-kaltaista kyselykieltä. Joitakin esimerkkejä uudemmista ei-relaatiotietokannoista ja kirjastoista, jotka tukevat SQL: ää ovat:

  • Apache Spark Analytics Engine
  • Google Big Query-managed analytics data warehouse
  • Facebook Presto and AWS Athene
  • Elastinen haku
  • Python Data Analysis Library (pandasql)

SQL on laajalti käytössä sekä OLTP: ssä (Transaktiokäsittely) että OLAP: ssa (analyyttinen käsittely), vaikka nämä kaksi ovat hyvin erilaisia käyttötapauksia:

pätkä 1: pandasql

tapahtumakäsittely analyyttinen käsittely
yleinen käyttö relaatiotietokannat Big Data-tietokannat
SQL: n käyttö Lisää, päivitä ja monien käyttäjien kyselyt samanaikaisesti valitse kyselyt. Muutama kysely kerrallaan
laskuasteikko pieni Suuri-kokonaiset taulukot
kyselyn kesto nopea voi olla tuntia tai enemmän
suorituskykyyn liittyvät ominaisuudet indeksit ja avaimet osiot, tarpeettomat tiedot (käyttötapauskohtaisesti)
analyyttiset funktiot harvoin käytetyt Yleiset

ennen SQL: ään sukeltamista, tässä on esimerkki SQL: n käytöstä tietokantojen ulkopuolella-Pythonin Data Analysis Library:

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

kuten nämä esimerkit osoittavat, SQL on elossa ja hyvin uusin ja suosituin teknologioita.

katsotaanpa tarkemmin sen voimaa.

SQL-funktiot esimerkit

SQL: n vahvin osa on sen funktiot. Tässä on kolme funktiotyyppiä, joita SQL tukee:

Tyyppi soveltamisala tietueiden vähentäminen esimerkkejä
yksinkertainen yksittäinen levy Ei CASE … WHEN 1 THEN column_1 WHEN 2 THEN column_2 ELSE 3 END
GREATEST (column_1, column_2, column_3)
COALESCE (column_1)
kootut (ryhmä) tietoryhmä Kyllä SUM(column_1)
AVG(column_1)
analyyttinen tietoryhmä Ei SUM(column_1) OVER (PARTITION BY column_2)
LEAD/LAG - value of a field in the next or previous record

suosittelemme käymään läpi kyselymoottorin toiminnot ymmärtääksesi sen ominaisuudet. Täältä löytyy esimerkiksi Preston dokumentaatio aggregaattifunktioista ja analyyttisistä funktioista.

suositellut SQL-Koodauskonventiot (nimeäminen ja muuta)

SQL: n vallan saamiseksi suosittelemme koodauskonventioiden käyttöä. Tämä parantaa sekä kirjoittamista että lukemista ja parantaa tiimityötä. Tässä lyhyt lista tärkeämmistä:

nimet ja tapaus käytä alaviivoja, joissa nimeen lisätään luonnollisesti välilyönti (sukunimi -> last_nimi)
suuraakkoset avainsanoille ja funktioille (valitse, mistä, missä, summa…)
pienaakkoset kaikelle muulle: muuttujat, taulukkojen nimet, sarakkeet jne.
käytä nimenomaisesti avainsanoja liitoksissa käytä avainsanoja: risti, sisempi … ON, vasen… on, oikea… on
Vältä liittymistä osana WHERE-lauseketta: Valitse … kohteesta table_a, table_b, jossa …
Aliasing-sisällytä aina as-avainsana
Vältä sisäkkäisiä muotoja, käytä sen sijaan Vältä valitse … kohdasta (Valitse …)

käytä lausekkeiden kanssa! Näin voit välttää monimutkaiset kyselyt ja luoda vaiheittain luettavia kyselyitä

Vältä IN / NOT IN – Use JOIN.

Riviväli ennen ja jälkeen kunkin avainsanan määritelmän
Sisällytä Kommentit sekä / * että sulkeminen * / ja — ovat hyväksyttäviä
älä käytä SELECT * kyllä, se on kompakti ja helppo, mutta kyselytulokseesi lisätään ja lisätään uusia sarakkeita ilman tarvetta. Kirjoita nimenomaisesti sarakkeen nimi tarvitset

SQL Query Examples: Good & Bad

to show you how important the coding conventions are, please look these good and bad examples.

Tyyppi hyvä huono
virtaus kaksi yksinkertaista kyselyä yksi monimutkainen kysely
avainsanat isot kirjaimet pienet kirjaimet, kuten muukin teksti
liity eksplisiittinen-helppo ymmärtää implisiittinen-muiden missä predikaatit
sisennys Riviväli ennen tai auttaa tietämään sen laajuuden ei riviväliä ennen tai. Suurempi kunto on paljon vaikea ymmärtää
käytetty ei käytössä

pätkä 4: Bad example

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

pätkä 5: Good example

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

Here ’ s a nice online tool for SQL formating that you can use: https://sqlformat.org/

SQL Problems Troubleshooting-Mistakes & sudenkuopat

kuten millä tahansa muulla kielellä, SQL: llä on omat sudenkuoppansa, joista sinun tulisi olla tietoinen säästääksesi aikaa ja ehkäistäksesi bugeja sovelluksessasi.

SQL – virheiden ensimmäinen syy & bugs-Null-arvot

pätkä 6: null-kyselytulos

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

tämän ongelman ratkaisemiseksi kannattaa käyttää yhteenliittymiä aina kun on vaara:

pätkä 7: Overcome Null in joins

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

SQL-indeksit ja osioita ei käytetä

käytämme paljon aikaa tietokannan skeeman rakentamiseen lisäämällä indeksejä ja julistamalla osioita. Mutta emme saa haluamaamme suorituskykyä, jos pysähdymme siihen-meidän on varmistettava, että tietokantamoottorimme käyttää niitä.

Oletetaan esimerkiksi, että meillä on hakemisto ykkös_nimi-sarakkeessa:

SELECT first_nameFROM employeeWHERE UPPER(first_name) LIKE 'A%'
indeksiä ei käytetä
SELECT first_nameFROM employeeWHERE first_name LIKE 'A%' OR first_name LIKE 'a%'
käytetty indeksi

toteutuksesta toiseen voi tulla muutoksia, mutta konsepti on voimassa missä tahansa SQL-toteutuksessa.

Miten varmistat, että indeksejäsi ja osioitasi käytetään? Analysoi kyselyn suoritussuunnitelma. Joissakin SQL-toteutuksissa voit käyttää selitä-avainsanaa. Esimerkiksi Oraclessa sitä kutsutaan SELITYSSUUNNITELMAKSI.

analyyttisten funktioiden sudenkuopat SQL-Arviointijärjestyksessä

tämä on tärkeä, jos käytät analyyttistä funktiota, sillä arviointijärjestyksen väärinymmärrys voi johtaa bugeihin. Esimerkiksi:

pätkä 8: analyyttinen funktio suodattimella

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

saamme kaikkien osaston työntekijöiden sijaan osaston työntekijöiden määrän, jonka sukunimi alkaa A: lla. Muista, että analyyttiset toiminnot suoritetaan viimeisenä.

SQL-standardointi

päivämäärän ja kellonajan syntaksi, Nullit, merkkijonojen tiivistäminen, vertailutapauksen herkkyys ja muut SQL: n näkökohdat vaihtelevat toimittajalta toiselle – mikä vaikeuttaa sovelluksen siirtämistä toimittajien välillä.

yritä käyttää vain standard SQL: ää. Esimerkiksi-käytä COALESCE sijasta ISNULL(SQL Server) tai NVL (Oracle).

Prevent SQL Injection (SQLi) Attacks

tunnettu tietoturvaloukku verkkosovelluksissa on SQL injection attack – koodinsyöttötekniikka, joka hyökkää sovelluksia vastaan lisäämällä lausekkeita kenttään suoritusta varten. Tällaisia hyökkäyksiä käytetään yleensä tietojen varastamiseen, tietojen muuttamiseen tai käyttöoikeuksien muuttamiseen hyökätyssä tietokannassa.

SQL-Injektiohyökkäysten estäminen alkaa kehityksen aikana, minkä jälkeen tulee käyttää skannereita ja verkkosovelluksen palomuureja sovelluksen suojaamiseksi. Katso SQL Injection blogikirjoitus esimerkkejä ja tapoja estää tällaisia hyökkäyksiä.

paketointi

SQL on hienoa. Itse pidän enemmän SQL-pohjaisesta kielestä kuin mistään muusta kyselykielestä tai API: sta. Sinun on kuitenkin käytettävä sitä oikein, jotta työsi olisi helpompaa. Aloita rakentamalla skeema oikein ja käytä myöhemmin koodaustapoja ja muita vaihtoehtoja, kuten avainsanalla ja ikkunatoimintoja.

älä unohda tunnettuja sudenkuoppia. Ja, jos kirjoitat hakemuksen, suojaa se SQL injektio.

the post Introduction to SQL: Esimerkkejä, parhaita käytäntöjä ja sudenkuoppia appeared first on Blog.

* * * tämä on Tietoturvabloggaajien verkoston syndikoitu blogi Ori Nakarin kirjoittamasta blogista. Lue alkuperäinen viesti osoitteesta: https://www.imperva.com/blog/introduction-to-sql-examples-best-practices-and-pitfalls/

Related Post

Leave A Comment