Kas yra SQL? Duomenų analizės lingua franca

Šiandien Struktūrizuota užklausų kalba yra standartinė priemonė manipuliuoti ir pateikti užklausas reliacinėse duomenų bazėse, nors ir su nuosavais plėtiniais tarp produktų. SQL paprastumas ir visuotinumas netgi paskatino daugelio „NoSQL“ ar nesusijusių duomenų saugyklų, tokių kaip „Hadoop“, kūrėjus priimti SQL potinklius arba sugalvoti savo SQL tipo užklausų kalbas.

Tačiau SQL ne visada buvo „universali“ reliacinių duomenų bazių kalba. Nuo pat pradžių (apie 1980 m.) SQL turėjo tam tikrų streikų. Daugelis to meto tyrinėtojų ir kūrėjų, tarp jų ir aš, manė, kad pridėtinė SQL neleis jos niekada praktiškai naudoti gamybos duomenų bazėje.

Aišku, mes klydome. Tačiau daugelis vis dar mano, kad norint palengvinti SQL prieinamumą, vykdymo metu reikalaujama kaina dažnai būna per didelė.

SQL istorija

Kol dar nebuvo SQL, duomenų bazės turėjo griežtas navigacinio programavimo sąsajas ir paprastai buvo sukurtos pagal tinklo schemą, vadinamą CODASYL duomenų modeliu. „CODASYL“ (Duomenų sistemų kalbų komitetas) buvo konsorciumas, kuris buvo atsakingas už COBOL programavimo kalbą (pradedant nuo 1959 m.) Ir duomenų bazės kalbų išplėtimą (prasidėjo po 10 metų).

Kai programavote pagal „CODASYL“ duomenų bazę, naršėte prie įrašų per rinkinius, kurie išreiškia santykius „vienas su daugeliu“. Senesnėse hierarchinėse duomenų bazėse įrašas gali priklausyti tik vienam rinkiniui. Tinklo duomenų bazės leidžia įrašui priklausyti keliems rinkiniams.

Tarkime, kad norite išvardyti studentus, užsiregistravusius CS 101. Pirmiausia rasite „CS 101“ viduje konors Kursai nustatykite pagal pavadinimą, nustatykite tai kaip Įstojusieji nustatyti, suraskite pirmąjį narį (ffm) iš Įstojusieji rinkinys, kuris yra a Studentas įrašykite ir išvardykite. Tada jūs eitumėte į ciklą: raskite kitą narį (fnm) ir išvardykite. Kada fnm nepavyko, jūs išeitumėte iš ciklo.

Tai gali atrodyti, kad duomenų bazės programuotojui tenka daug nuveikti, tačiau vykdymo metu jis buvo labai efektyvus. Ekspertai, tokie kaip Michaelas Stonebrakeris iš Kalifornijos universiteto Berkeley ir Ingresas, nurodė, kad tokio tipo užklausos atlikimas CODASYL duomenų bazėje, tokioje kaip IDMS, užtruko maždaug pusę procesoriaus laiko ir mažiau nei pusę atminties kaip ta pati užklausa reliacinėje duomenų bazėje naudojant SQL .

Palyginimui, lygiavertė SQL užklausa grąžinti visus CS 101 studentus būtų kažkas panašaus 

PASIRINKITE studento vardą iš kursų, besimokančiųjų, studentų KUR kurso pavadinimas

Ta sintaksė reiškia santykinį vidinį sujungimą (iš tikrųjų du iš jų), kaip paaiškinsiu žemiau, ir palieka keletą svarbių detalių, pavyzdžiui, sujungimams naudojamus laukus.

Reliacinės duomenų bazės ir SQL

Kodėl atsisakytumėte veiksnio, kuris pagerintų vykdymo greitį ir atminties naudojimą? Buvo dvi didelės priežastys: paprastas kūrimas ir perkeliamumas. Nemaniau, kad 1980 m. Vienas iš jų turėjo daug reikšmės, palyginti su našumu ir atminties reikalavimais, tačiau tobulėjant ir pigstant kompiuterio aparatinei įrangai žmonės nustojo rūpintis vykdymo greičiu ir atmintimi ir labiau jaudinosi dėl kūrimo išlaidų.

Kitaip tariant, Moore'o įstatymas užmušė CODASYL duomenų bazes, naudodamasis reliacinėmis duomenų bazėmis. Kaip atsitiko, kūrimo laiko pagerėjimas buvo reikšmingas, tačiau SQL perkeliamumas pasirodė esąs svajonė.

Iš kur atsirado reliacinis modelis ir SQL? EF „Tedas“ Coddas buvo IBM San Chosė tyrimų laboratorijos informatikas, 1960-aisiais parengęs reliacinio modelio teoriją ir paskelbęs ją 1970 m. IBM, siekdama apsaugoti pajamų iš savo CODASYL duomenų bazę IMS / DB. Kai IBM pagaliau pradėjo savo „System R“ projektą, kūrėjų komandos (Don Chamberlin ir Ray Boyce) nebuvo Codd, ir jie nepaisė Codd 1971 m. „Alpha“ reliacinės kalbos darbo, norėdami sukurti savo kalbą „SEQUEL“ („Structured English Query Language“). 1979 m., Dar net IBM neišleidus savo produkto, Larry Ellisonas įtraukė kalbą į savo „Oracle“ duomenų bazę (kaip savo specifikaciją naudodamas IBM prieš paleidimą pateiktus SEQUEL leidinius). „SEQUEL“ netruko tapti „SQL“, kad išvengtų tarptautinio prekės ženklo pažeidimo.

„Tomai, mušantys dėl SQL“ (kaip teigė Michaelas Stonebrakeris), ateidavo ne tik iš „Oracle“ ir IBM, bet ir iš klientų. Samdyti ar mokyti CODASYL duomenų bazių kūrėjus ir programuotojus nebuvo lengva, todėl SEQUEL (ir SQL) atrodė daug patraukliau. Aštuntajame dešimtmetyje SQL buvo toks patrauklus, kad daugelis duomenų bazių tiekėjų iš esmės sukabino SQL užklausų procesorių ant savo CODASYL duomenų bazių, dėl didelio Coddo nusivylimo, kuris manė, kad reliacinės duomenų bazės turi būti kuriamos nuo nulio, kad jos būtų reliacinės.

Gryna reliacinė duomenų bazė, kurią sukūrė Codd, yra pastatyta į grupes, sugrupuotas į santykius, atitinkančius pirmos eilės predikatų logiką. Realaus pasaulio reliacinėse duomenų bazėse yra lentelės, kuriose yra laukai, apribojimai ir aktyvikliai, o lentelės yra susijusios per užsienio raktus. SQL naudojamas deklaruoti grąžintinus duomenis, o SQL užklausų procesorius ir užklausų optimizavimo priemonė SQL deklaraciją paverčia užklausos planu, kurį vykdo duomenų bazės variklis.

SQL apima antrinę kalbą schemoms apibrėžti, duomenų apibrėžimo kalbą (DDL), taip pat duomenų keitimo antrinę kalbą, duomenų manipuliavimo kalbą (DML). Abi šios šaknys yra ankstyvose CODASYL specifikacijose. Trečioji SQL antrinė kalba deklaruoja užklausas per PASIRINKTI teiginys ir reliaciniai sujungimai.

SQLPASIRINKTI pareiškimas

PASIRINKTI teiginyje užklausos optimizatoriui nurodoma, kokius duomenis reikia grąžinti, į kokias lenteles žiūrėti, kokius santykius sekti ir kokią tvarką taikyti grąžintiems duomenims. Užklausos optimizavimo priemonė turi pati išsiaiškinti, kokius indeksus naudoti, kad būtų išvengta žiaurios jėgos lentelės nuskaitymo ir pasiektas geras užklausos efektyvumas, nebent tam tikroje duomenų bazėje nepalaikomos indekso užuominos.

Dalis reliacinių duomenų bazių kūrimo meno priklauso nuo protingo indeksų naudojimo. Jei praleisite dažnos užklausos indeksą, visa duomenų bazė gali sulėtėti esant dideliems skaitymo apkrovimams. Jei turite per daug indeksų, visa duomenų bazė gali sulėtėti esant sunkiam rašymui ir atnaujinimui.

Kitas svarbus menas yra gero, unikalaus kiekvieno stalo pagrindinio rakto pasirinkimas. Turite ne tik atsižvelgti į pirminio rakto įtaką įprastoms užklausoms, bet ir į tai, kaip jis veiks prisijungiant, kai kitoje lentelėje pasirodys kaip užsienio raktas, ir kaip tai paveiks duomenų nuorodos lokalumą.

Išplėstiniu atveju duomenų bazių lentelės, kurios yra suskirstytos į skirtingus tomus, atsižvelgiant į pirminio rakto, vadinamo horizontaliu dalijimu, vertę, taip pat turite apsvarstyti, kaip pagrindinis raktas paveiks skaidymą. Patarimas: norite, kad lentelė būtų tolygiai paskirstyta tūriuose, o tai rodo, kad nenorite naudoti datos antspaudų ar iš eilės einančių sveikųjų skaičių kaip pagrindinius raktus.

Diskusijos PASIRINKTI pareiškimas gali prasidėti paprastai, bet gali greitai tapti painus. Apsvarstykite:

PASIRINKTI * IŠ klientų;

Paprasta, tiesa? Čia prašoma visų laukų ir visų eilučių Klientai stalo. Tarkime, kad vis dėlto Klientai lentelėje yra šimtas milijonų eilučių ir šimtas laukų, o vienas iš laukų yra didelis komentarų teksto laukas. Kiek laiko užtruks visi tie duomenys per 10 megabitų per sekundę tinklo ryšį, jei kiekvienoje eilutėje yra vidutiniškai 1 kilobaitas duomenų?

Galbūt turėtumėte sumažinti, kiek siunčiate per laidą. Apsvarstykite:

PASIRINKTI TOP 100 įmonės pavadinimą, paskutinįPardavimoDatą, paskutinįPardavimoAmount, totalSalesAmount FROM klientų
KUR valstija IR miestas
UŽSAKYTI PAGAL lastSaleDate MAŽĖJANTI;

Dabar ketinate surinkti daug mažiau duomenų. Jūs paprašėte duomenų bazės pateikti tik keturis laukus, atsižvelgti tik į Klivlando įmones ir pateikti tik 100 bendrovių, kurių pardavimai buvo paskutiniai. Norėdami tai padaryti efektyviausiai duomenų bazės serveryje, Klientai lentelėje reikia rodyklės valstija + miestas už KUR sąlyga ir rodyklė lastSaleDate už RŪŠIUOTI PAGAL ir TOP 100 sąlygos.

Beje, TOP 100 galioja „SQL Server“ ir „SQL Azure“, bet ne „MySQL“ ar „Oracle“. „MySQL“ naudosite RIBOTI 100 po to, kai KUR sąlyga. „Oracle“ naudosite įrištą ROWNUM kaip dalis KUR sąlyga, t.y. KUR ... IR SUMAŽUOTI <= 100. Deja, ANSI / ISO SQL standartai (o jų iki šiol yra devyni, tęsiasi nuo 1986 m. Iki 2016 m.) Eina tik taip toli, per kuriuos kiekviena duomenų bazė įveda savo nuosavybės sąlygas ir funkcijas.

SQL prisijungia

Iki šiol aš aprašiau PASIRINKTI atskirų lentelių sintaksė. Kol aš negaliu paaiškintiPRISIJUNGTI išlygas, turite suprasti svetimus raktus ir ryšius tarp lentelių. Aš tai paaiškinsiu naudodamasis pavyzdžiais DDL, naudodamasis SQL serverio sintakse.

Trumpa to versija yra gana paprasta. Kiekvienoje lentelėje, kurią norite naudoti santykiuose, turėtų būti pagrindinis rakto apribojimas; tai gali būti vienas laukas arba laukų derinys, apibrėžtas išraiška. Pavyzdžiui:

SUKURTI LENTEL Pers Asmenys (
    „PersonID int“ NĖRA VISO PAGRINDINIO RAKTO,
    „PersonName char“ (80),
    ...

Kiekviena lentelė, su kuria reikia susieti Asmenys turėtų turėti lauką, atitinkantį Asmenys pirminis raktas ir norint išsaugoti santykių vientisumą, tas laukas turėtų turėti svetimo rakto apribojimą. Pavyzdžiui:

SUKURTI LENTELĖS užsakymus (
    „OrderID int“ NĖRA PAGRINDINIS RAKTAS,
    ...
    PersonID int UŽSIENIO PAGRINDINĖS NUORODOS Asmenys (PersonID)
);

Yra ilgesnių abiejų teiginių versijų, kuriose naudojamas SUSITARIMAS raktinis žodis, leidžiantis įvardyti apribojimą. Tai sukuria dauguma duomenų bazių projektavimo įrankių.

Pirminiai raktai visada yra indeksuoti ir unikalūs (lauko reikšmių negalima dubliuoti). Kitus laukus galima pasirinktinai indeksuoti. Dažnai naudinga kurti užsienio raktų laukų ir laukų, kuriuose rodoma, indeksus KUR ir RŪŠIUOTI PAGAL išlygos, nors ir ne visada, dėl galimų rašymo ir atnaujinimo pridėtinių išlaidų.

Kaip parašytumėte užklausą, kurioje būtų pateikti visi John Doe užsakymai?

PASIRINKITE Asmens vardas, Užsakymo ID iš asmenų
„INNER JOIN“ užsakymai asmenims. PERSONID = Užsakymai.PersonID
WHERE asmenvardis;

Iš tikrųjų yra keturių rūšių PRISIJUNGTI: VIDAUS, IŠORINIS, Kairėir TEISĖ. VIDINIS PRISIJUNGIMAS yra numatytasis (galite praleisti žodį VIDAUS), ir į ją įtraukiamos tik eilutės, kuriose abiejose lentelėse yra atitinkančios vertės. Jei norite išvardyti asmenis, neatsižvelgiant į tai, ar jie turi užsakymų, ar ne KAIRĖS PRISIJUNGTI, pavyzdžiui:

PASIRINKITE Asmens vardas, Užsakymo ID iš asmenų
„LEFT JOIN“ užsakymai asmenims. PERSONID = Užsakymai.PersonID
UŽSAKYTI PAGAL asmenvardį;

Pradėjus vykdyti užklausas, jungiančias daugiau nei dvi lenteles, kuriose vartojamos išraiškos arba kurios priverčia naudoti duomenų tipus, iš pradžių sintaksė gali būti šiek tiek plaukuota. Laimei, yra duomenų bazių kūrimo įrankių, kurie gali sugeneruoti teisingas SQL užklausas, dažnai nuvilkdami lenteles ir laukus iš schemos diagramos į užklausos diagramą.

SQL saugomos procedūros

Kartais deklaratyvus programos pobūdis PASIRINKTI pareiškimas nepatenka ten, kur norite eiti. Daugelyje duomenų bazių yra galimybė, vadinama saugomomis procedūromis; deja, tai yra sritis, kurioje beveik visose duomenų bazėse naudojami patentuoti ANSI / ISO SQL standartų plėtiniai.

SQL serveryje pradinė saugomų procedūrų (arba saugomų procesų) tarmė buvo „Transact-SQL“, dar žinomas kaip „T-SQL“; „Oracle“ tai buvo PL-SQL. Abi duomenų bazės pridėjo papildomų kalbų saugomoms procedūroms, tokioms kaip C #, Java ir R. Paprasta T-SQL saugoma procedūra gali būti tik parametruojama PASIRINKTI pareiškimas. Jo pranašumai yra paprastas naudojimas ir efektyvumas. Saugomos procedūros optimizuojamos jas išsaugant, o ne kiekvieną kartą jas vykdant.

Sudėtingesnėje T-SQL saugomoje procedūroje gali būti naudojami keli SQL sakiniai, įvesties ir išvesties parametrai, vietiniai kintamieji, PRADŽIA ... PABAIGA blokai, JEI ... TADA ... KITA sąlygos, žymekliai (rinkinio apdorojimas eilutėmis po eilutės), išraiškos, laikinos lentelės ir visa eilė kitų procesinių sintaksių. Akivaizdu, kad jei saugoma procedūrų kalba yra C #, Java arba R, jūs naudosite tų procedūrinių kalbų funkcijas ir sintaksę. Kitaip tariant, nepaisant to, kad SQL motyvacija buvo naudoti standartizuotas deklaratyvias užklausas, realiame pasaulyje matote daug konkrečiai duomenų bazei skirtų procedūrinių serverių programavimo.

Tai ne visai grąžina mus į senus blogus „CODASYL“ duomenų bazių programavimo laikus (nors žymekliai yra arti), tačiau grįžta iš minčių, kad SQL sakiniai turėtų būti standartizuoti ir kad našumo problemos turėtų būti paliktos duomenų bazės užklausų optimizavimo priemonei . Galų gale dvigubo pasirodymo dažnai būna per daug, kad paliktum ant stalo.

Sužinokite SQL

Žemiau išvardytos svetainės gali padėti išmokti SQL arba atrasti įvairių SQL tarmių keistenybių.