Ohjelmointi

Mikä on SQL? Tietojen analysoinnin kieli

Nykyään strukturoitu kyselykieli on tavallinen tapa käsitellä ja kysellä tietoja relaatiotietokannoissa, vaikka tuotteilla on omia laajennuksia. SQL: n helppous ja läsnäolo ovat jopa johtaneet monien “NoSQL” -tietojen tai muiden kuin relaatioon liittyvien tietovarastojen, kuten Hadoopin, luojat ottamaan käyttöön SQL-osajoukot tai keksimään omat SQL-tyyppiset kyselykielensä.

SQL ei kuitenkaan aina ollut relaatiotietokantojen "universaali" kieli. Alusta alkaen (noin 1980) SQL: llä oli tiettyjä lakkoja sitä vastaan. Monet tuolloin tutkijat ja kehittäjät, myös minä, ajattelivat, että SQL: n yleiskustannukset estäisivät sitä koskaan olemasta käytännöllinen tuotantotietokannassa.

Olimme selvästi väärässä. Mutta monet uskovat silti, että SQL: n helppokäyttöisyyden ja helppokäyttöisyyden vuoksi ajonaikaisessa suorituskyvyssä vaadittu hinta on usein liian korkea.

SQL-historia

Ennen SQL: n olemassaoloa tietokannoissa oli tiukat, navigointiohjelmointirajapinnat, ja ne suunniteltiin tyypillisesti CODASYL-tietomalliksi kutsutun verkkomallin ympärille. CODASYL (komitea tietojärjestelmäkielistä) oli konsortio, joka vastasi COBOL-ohjelmointikielestä (alkoi vuonna 1959) ja tietokannan kielen laajennuksista (alkoi 10 vuotta myöhemmin).

Kun ohjelmoit CODASYL-tietokantaan, navigoit tietueisiin sarjoilla, jotka ilmaisevat yhdestä moneen-suhteet. Vanhemmat hierarkkiset tietokannat sallivat tietueen kuulumisen vain yhteen sarjaan. Verkkotietokannat sallivat tietueen kuulumisen useisiin sarjoihin.

Sano, että haluat listata CS 101: een ilmoittautuneet opiskelijat. Löydät ensin "CS 101" että Kurssit aseta nimellä, aseta se omistajaksi tai vanhemmaksi Ilmoittautuneet joukko, etsi ensimmäinen jäsen (ffm) Ilmoittautuneet sarja, joka on a Opiskelija tallentaa ja luetteloida se. Sitten menisit silmukkaan: Etsi seuraava jäsen (fnm) ja luetella se. Kun fnm epäonnistui, poistut silmukasta.

Se voi tuntua paljon tietojenkäsittelyohjelman ohjelmoijalle suoritetulta työltä, mutta se oli erittäin tehokas suorituksen aikana. Asiantuntijat, kuten Michael Stonebraker Kalifornian yliopistosta Berkeleyssä ja Ingres, huomauttivat, että tällaisen kyselyn tekeminen CODASYL-tietokannassa, kuten IDMS, kesti noin puolet suorittimen ajasta ja alle puolet muistista kuin sama kysely relaatiotietokannassa SQL: n avulla .

Vertailun vuoksi vastaava SQL-kysely kaikkien CS 101: n opiskelijoiden palauttamiseksi olisi jotain 

VALITSE opiskelijan.nimi kursseilta, ilmoittautuneilta, opiskelijoilta WHERE kurssin.nimi

Tämä syntakse merkitsee sisäistä relaatioliitäntää (oikeastaan ​​kaksi niistä), kuten selitän alla, ja jättää pois joitain tärkeitä yksityiskohtia, kuten liitoksiin käytettävät kentät.

Relaatiotietokannat ja SQL

Miksi luopuisit kahdesta parannuksesta suorituksen nopeudessa ja muistin käytössä? Syitä oli kaksi: kehityksen helppous ja siirrettävyys. En uskonut, että jommallakummalla oli merkitystä vuonna 1980 verrattuna suorituskykyyn ja muistivaatimuksiin, mutta tietokonelaitteiston kehittyessä ja haluttaessa ihmiset eivät enää välittäneet suorituksen nopeudesta ja muistista ja olivat huolissaan enemmän kehityskustannuksista.

Toisin sanoen, Mooren laki tappoi CODASYL-tietokannat relaatiotietokantojen hyväksi. Kuten tapahtui, kehitysajan parantuminen oli merkittävää, mutta SQL-siirrettävyys osoittautui putken unelmaksi.

Mistä relaatiomalli ja SQL tulivat? EF “Ted” Codd oli IBM San Jose -tutkimuslaboratorion tietojenkäsittelytieteen tutkija, joka laati relaatiomallin teorian 1960-luvulla ja julkaisi sen vuonna 1970. IBM oli hidas toteuttamaan relaatiotietokantaa pyrkiessään suojelemaan sen CODASYL-tietokanta IMS / DB. Kun IBM aloitti lopulta System R -projektinsa, kehitystiimi (Don Chamberlin ja Ray Boyce) ei ollut Coddin alaisuudessa, ja he jättivät huomiotta Coddin vuoden 1971 Alpha-relaatiokielipaperin suunnitellakseen omaa kieltään, SEQUEL (Structured English Query Language). Vuonna 1979, ennen kuin IBM oli edes julkaissut tuotettaan, Larry Ellison sisällytti kielen Oracle-tietokantaansa (käyttäen IBM: n julkaisua edeltäviä SEQUEL-julkaisuja teknisenä tuotteena). SEQUEListä tuli pian SQL välttääkseen kansainväliset tavaramerkkirikkomukset.

"Tom-tomit, jotka hakevat SQL: ää" (kuten Michael Stonebraker sanoi) eivät tulleet vain Oraclen ja IBM: n, mutta myös asiakkailta. CODASYL-tietokannan suunnittelijoita ja ohjelmoijia ei ollut helppo palkata tai kouluttaa, joten SEQUEL (ja SQL) näytti paljon houkuttelevammalta. SQL oli niin houkutteleva 1980-luvun lopulla, että monet tietokantatoimittajat olennaisesti nitovat SQL-kyselyprosessorin CODASYL-tietokantojensa päälle, Coddin suureksi tyrmistykseksi, joka katsoi, että relaatiotietokannat oli suunniteltava alusta alkaen relaatioon.

Coddin suunnittelema puhdas relaatiotietokanta on rakennettu suhteisiin ryhmiteltyihin ryhmiin ensimmäisen asteen predikaattilogiikan mukaisesti. Reaalimaailman relaatiotietokannoissa on taulukoita, jotka sisältävät kenttiä, rajoituksia ja laukaisijoita, ja taulukot liittyvät toisiinsa vieraisiin avaimiin. SQL: ää käytetään palautettavien tietojen ilmoittamiseen, ja SQL-kyselyprosessori ja kyselyn optimoija tekevät SQL-ilmoituksesta kyselysuunnitelman, jonka tietokantamoottori suorittaa.

SQL sisältää alikielen kaavojen määrittelemiseksi, datan määrityskielen (DDL) sekä alikielen tietojen muokkaamiseksi, datankäsittelykielen (DML). Molemmilla on juuret varhaisissa CODASYL-spesifikaatioissa. SQL: n kolmas kieli ilmoittaa kyselyt VALITSE lausunto ja relaatioliitännät.

SQLVALITSE lausunto

VALITSE lause kertoo kyselyn optimoijalle, mitä tietoja palautetaan, mitä taulukoita on tarkasteltava, mitä suhteita on noudatettava ja mikä järjestys palautettaville tiedoille. Kyselyn optimoijan on itse selvitettävä, mitä indeksejä käytetään, jotta vältetään raa'an voiman taulukon skannaus ja saavutetaan hyvä kyselyn suorituskyky, ellei tietty tietokanta tue indeksivihjeitä.

Osa relaatiotietokantojen suunnittelusta riippuu indeksien harkitusta käytöstä. Jos jätät hakemiston toistuvalle kyselylle, koko tietokanta voi hidastua suurten lukukuormitusten aikana. Jos sinulla on liian monta hakemistoa, koko tietokanta voi hidastua raskaissa kirjoitus- ja päivityskuormissa.

Toinen tärkeä taide on valita hyvä, ainutlaatuinen ensisijainen avain jokaiselle pöydälle. Sinun ei tarvitse vain pohtia ensisijaisen avaimen vaikutusta yleisiin kyselyihin, vaan myös sen, kuinka se toimii liittymisissä, kun se näkyy vieraanä avaimena toisessa taulukossa, ja miten se vaikuttaa tietojen viittauspaikkaan.

Edistyneessä tapauksessa tietokantataulukot, jotka on jaettu erilaisiin volyymeihin riippuen ensisijaisen avaimen arvosta, jota kutsutaan vaakasuoraksi sirpaloitumiseksi, on myös harkittava, kuinka ensisijainen avain vaikuttaa sirpoutumiseen. Vinkki: Haluat taulukon jakautuvan tasaisesti eri osiin, mikä viittaa siihen, ettet halua käyttää päivämääräleimoja tai peräkkäisiä kokonaislukuja ensisijaisina avaimina.

Keskustelut VALITSE lausunto voi alkaa yksinkertaisesti, mutta voi nopeasti tulla hämmentäväksi. Harkitse:

VALITSE * ASIAKASTA;

Yksinkertainen, eikö? Se kysyy kaikki kentät ja kaikki rivit Asiakkaat pöytä. Oletetaan kuitenkin, että Asiakkaat taulukossa on sata miljoonaa riviä ja sata kenttää, ja yksi kentistä on suuri kommenttikentti. Kuinka kauan kestää kaiken tiedon vetäminen 10 megabitin sekunnin verkkoyhteydellä, jos jokaisella rivillä on keskimäärin 1 kilotavu dataa?

Ehkä sinun pitäisi vähentää kuinka paljon lähetät langan yli. Harkitse:

VALITSE TOP 100 yrityksen nimi, lastSaleDate, lastSaleAmount, totalSalesAmount FROM Asiakkaat

Missä valtio ja kaupunki

TILAUS lastSaleDate LASKEVA;

Nyt aiot vetää paljon vähemmän tietoja. Olet pyytänyt tietokantaa antamaan sinulle vain neljä kenttää, ottamaan huomioon vain Clevelandin yritykset ja antamaan sinulle vain 100 yritystä, joilla on viimeisimmät myynnit. Voit tehdä sen tehokkaimmin tietokantapalvelimella kuitenkin Asiakkaat taulukko tarvitsee hakemiston osavaltio + kaupunki varten MISSÄ lauseke ja hakemisto lastSaleDate varten TILAA ja TOP 100 lausekkeet.

Muuten, TOP 100 on voimassa SQL Serverissä ja SQL Azuressa, mutta ei MySQL: ssä tai Oraclessa. MySQL: ssä haluat käyttää RAJA 100 jälkeen MISSÄ lauseke. Oraclessa haluat käyttää sidottua ROWNUM osana MISSÄ lauseke, ts. Missä ... JA ROWNUM <= 100. Valitettavasti ANSI / ISO SQL -standardit (ja niitä on tähän mennessä yhdeksän, jotka ulottuvat vuodesta 1986 vuoteen 2016) menevät vain niin pitkälle, että sen jälkeen kukin tietokanta esittelee omat lausekkeensa ja ominaisuutensa.

SQL liittyy

Toistaiseksi olen kuvannut VALITSE syntaksi yksittäisille taulukoille. Ennen kuin voin selittääLIITTYÄ SEURAAN lausekkeista, sinun on ymmärrettävä vieraat avaimet ja taulukoiden väliset suhteet. Selitän tämän käyttämällä esimerkkejä DDL: stä ja SQL Serverin syntaksia.

Lyhyt versio tästä on melko yksinkertainen. Jokaisella taulukossa, jota haluat käyttää suhteissa, tulisi olla ensisijainen avainrajoitus; tämä voi olla joko yksi kenttä tai lausekkeen määrittelemä kenttien yhdistelmä. Esimerkiksi:

LUO TAULUKKO Henkilöt (

PersonID int EI NULL PRIMARY KEY,

Henkilönimi char (80),

    ...

Jokainen taulukko, johon on liityttävä Henkilöt pitäisi olla kenttä, joka vastaa Henkilöt ensisijaisen avaimen ja suhteellisen eheyden säilyttämiseksi tällä kentällä tulisi olla vieraan avaimen rajoitus. Esimerkiksi:

LUO TAULUKON Tilaukset (

OrderID int EI NULL PRIMARY KEY,

    ...

PersonID int ULKOPUOLISET AVAINLÄHTEET Henkilöt (PersonID)

);

Molemmista lauseista on pidempi versio, joka käyttää RAJOITE avainsana, jonka avulla voit nimetä rajoituksen. Sitä useimmat tietokannan suunnittelutyökalut tuottavat.

Ensisijaiset avaimet ovat aina indeksoituja ja yksilöllisiä (kentän arvoja ei voida kopioida). Muut kentät voidaan vaihtoehtoisesti indeksoida. Usein on hyödyllistä luoda hakemistoja ulkomaisille avainkentille ja niissä näkyville kentille MISSÄ ja TILAA lausekkeet, vaikkakaan ei aina, kirjoitusten ja päivitysten mahdollisten yleiskustannusten vuoksi.

Kuinka kirjoitat kyselyn, joka palauttaa kaikki John Doen tekemät tilaukset?

Valitse Henkilönimi, Tilausnumero Henkilöiltä

INNER JOIN -tilaukset henkilöt.PersonID = Tilaukset.PersonID

WHERE Henkilönimi;

Itse asiassa on olemassa neljää erilaista LIITTYÄ SEURAAN: SISÄINEN, ULKOINEN, VASENja OIKEA. SISÄINEN LIITTYMINEN on oletusarvo (voit jättää sanan pois SISÄINEN), ja se sisältää vain rivit, jotka sisältävät vastaavia arvoja molemmissa taulukoissa. Jos haluat luetella henkilöitä riippumatta siitä, onko heillä tilauksia, käytä a VASEN LIITTYMINEN, esimerkiksi:

Valitse Henkilönimi, Tilausnumero Henkilöiltä

LEFT JOIN Tilaukset PERSON.PersonID = Tilaukset.PersonID

TILAA Henkilönimi;

Kun aloitat kyselyjä, jotka yhdistävät useamman kuin kaksi taulukkoa, jotka käyttävät lausekkeita tai pakottavat tietotyyppejä, syntaksista voi aluksi tulla hieman karvainen. Onneksi on olemassa tietokantakehitystyökaluja, jotka voivat luoda oikeat SQL-kyselyt sinulle usein vetämällä ja pudottamalla taulukoita ja kenttiä kaaviosta kyselykaavioon.

SQL-tallennetut menettelyt

Joskus VALITSE lausunto ei vie sinua sinne, minne haluat mennä. Useimmissa tietokannoissa on toiminto, jota kutsutaan tallennetuiksi menettelyiksi; valitettavasti tämä on alue, jolla melkein kaikki tietokannat käyttävät omia laajennuksia ANSI / ISO SQL -standardeihin.

SQL Serverissä tallennettujen menettelyjen (tai tallennettujen prosessien) alkuperäinen murre oli Transact-SQL, alias T-SQL; Oraclessa se oli PL-SQL. Molemmat tietokannat ovat lisänneet lisäkieliä tallennettuihin menettelyihin, kuten C #, Java ja R. Yksinkertainen T-SQL-tallennettu menettely voi olla vain parametroitu versio VALITSE lausunto. Sen etuja ovat helppokäyttöisyys ja tehokkuus. Tallennetut menettelyt optimoidaan, kun ne tallennetaan, ei aina, kun ne suoritetaan.

Monimutkaisempi T-SQL-tallennettu menettely voi käyttää useita SQL-käskyjä, syöttö- ja lähtöparametreja, paikallisia muuttujia, ALOITA ... LOPPU lohkot, JOS ... Sitten ... MUUTA ehdot, kohdistimet (sarjan rivi riviltä käsittely), lausekkeet, väliaikaiset taulukot ja koko joukko muuta menettelysyntaksi. Ilmeisesti, jos tallennettu menettelykieli on C #, Java tai R, aiot käyttää näiden menettelykielien toimintoja ja syntaksia. Toisin sanoen, huolimatta siitä, että SQL: n motivaatio oli käyttää standardoituja deklaratiivisia kyselyitä, todellisessa maailmassa näet paljon tietokantakohtaisia ​​proseduuripalvelinohjelmointia.

Se ei vie meitä takaisin CODASYL-tietokantaohjelmoinnin vanhaan huonoon aikaan (vaikka kohdistimet ovat lähellä), mutta se palaa takaisin ajatuksiin siitä, että SQL-käskyt olisi standardoitava ja että suorituskykyongelmat olisi jätettävä tietokantakyselyjen optimoijalle . Loppujen lopuksi kaksinkertainen suorituskyky on usein liikaa jättää pöydälle.

Opi SQL: ää

Alla luetellut sivustot voivat auttaa sinua oppimaan SQL: n tai tutustumaan erilaisten SQL-murteiden oivalluksiin.