Što je SQL? Lingua franca analize podataka

Danas je strukturirani jezik upita standardno sredstvo za manipulaciju i ispitivanje podataka u relacijskim bazama podataka, iako s vlasničkim proširenjima među proizvodima. Lakoća i sveprisutnost SQL-a čak su naveli tvorce mnogih "NoSQL" ili nerelacijskih pohrana podataka, poput Hadoop-a, da usvoje podskupove SQL-a ili osmisle svoje jezike upita sličnih SQL-u.

Ali SQL nije uvijek bio "univerzalni" jezik za relacijske baze podataka. Od početka (oko 1980.) SQL je protiv njega imao određene udare. Mnogi istraživači i programeri u to vrijeme, uključujući mene, mislili su da će režijski troškovi SQL-a spriječiti da on ikad bude praktičan u produkcijskoj bazi podataka.

Jasno je da smo pogriješili. Ali mnogi i dalje vjeruju da je, zbog sve jednostavnosti i dostupnosti SQL-a, cijena koja se zahtijeva u vrijeme izvođenja često previsoka.

Povijest SQL-a

Prije postojanja SQL-a, baze podataka imale su uska sučelja za navigacijsko programiranje i obično su bile dizajnirane oko mrežne sheme koja se naziva CODASYL podatkovni model. CODASYL (Odbor za jezike podatkovnih sustava) bio je konzorcij koji je bio odgovoran za programski jezik COBOL (počevši od 1959.) i proširenja jezika baza podataka (počevši 10 godina kasnije).

Kada ste programirali prema bazi podataka CODASYL, kretali ste se prema zapisima kroz skupove koji izražavaju odnose jedan-prema-više. Starije hijerarhijske baze podataka omogućuju da zapis pripada samo jednom skupu. Mrežne baze podataka omogućuju zapisu da pripada više skupova.

Recimo da želite navesti studente upisane u CS 101. Prvo biste "CS 101"u Coursesskupu pronašli ime, postavili ga kao vlasnika ili roditelja Enrolleesskupa, pronašli prvog člana ( ffm) Enrolleesskupa, koji je Studentzapis, i popis to. Tada biste ušli u petlju: Pronađite sljedećeg člana ( fnm) i navedite ga. U slučaju fnmneuspjeha, izašli biste iz petlje.

To se možda čini kao mnogo probnog rada za programera baze podataka, ali bilo je vrlo učinkovito u vrijeme izvršenja. Stručnjaci poput Michaela Stonebrakera sa Kalifornijskog sveučilišta u Berkeleyu i Ingresa istaknuli su da je izvođenje takve vrste upita u bazi podataka CODASYL, kao što je IDMS, uzimalo otprilike pola CPU-a i manje od polovine memorije kao isti upit u relacijskoj bazi podataka koristeći SQL .

Za usporedbu, ekvivalentni SQL upit za vraćanje svih učenika u CS 101 bio bi nešto slično 

ODABERITE ime studenta IZ tečajeva, upisanih, studenti GDJE naziv kursa

Ta sintaksa podrazumijeva relacijsko unutarnje spajanje (zapravo dva od njih), kao što ću objasniti u nastavku, i ostavlja neke važne detalje, poput polja koja se koriste za spajanja.

Relacijske baze podataka i SQL

Zašto biste se odrekli dva puta poboljšanja brzine izvršavanja i upotrebe memorije? Bila su dva velika razloga: lakoća razvoja i prenosivost. Nisam mislio da je ijedan od njih 1980. bio važan u usporedbi s performansama i memorijskim zahtjevima, ali kako se računalni hardver poboljšao i pojeftinio, ljudi su prestali brinuti o brzini izvršavanja i memoriji te su se više brinuli o troškovima razvoja.

Drugim riječima, Mooreov zakon ubio je baze podataka CODASYL u korist relacijskih baza podataka. Kao što se dogodilo, poboljšanje vremena razvoja bilo je značajno, ali pokazalo se da je prenosivost SQL-a glavni san.

Odakle relacijski model i SQL? EF “Ted” Codd bio je informatičar u IBM-ovom istraživačkom laboratoriju u San Joseu koji je razvio teoriju relacijskog modela 1960-ih i objavio je 1970. IBM je sporo implementirao relacijsku bazu podataka nastojeći zaštititi prihode njegova baza podataka CODASYL IMS / DB. Kad je IBM napokon započeo svoj projekt System R, razvojni tim (Don Chamberlin i Ray Boyce) nije bio pod Coddom, a ignorirali su Codd-ov referativni jezični referentni jezik iz 1971. godine kako bi dizajnirali svoj vlastiti jezik, SEQUEL (Structured English Query Language). 1979., prije nego što je IBM uopće izdao svoj proizvod, Larry Ellison je jezik uključio u svoju Oracle bazu podataka (koristeći IBM-ove publikacije SEQUEL prije pokretanja kao njegovu specifikaciju). SEQUEL je ubrzo postao SQL kako bi izbjegao međunarodno kršenje zaštitnog znaka.

"Tom-tomovi koji tuku za SQL" (kako je rekao Michael Stonebraker) nisu dolazili samo od Oraclea i IBM-a, već i od kupaca. Nije bilo lako unajmiti ili osposobiti dizajnere baze podataka i programera CODASYL-a, pa je SEQUEL (i SQL) izgledao puno atraktivnije. SQL je bio toliko atraktivan u kasnim 1980-ima da su mnogi dobavljači baza podataka u osnovi heftali procesor SQL upita povrh svojih baza podataka CODASYL, na veliko zgražanje Codda, koji je smatrao da relacijske baze podataka moraju biti dizajnirane od nule da bi bile relacijske.

Čista relacijska baza podataka, kako ju je dizajnirao Codd, izgrađena je na korpicama grupiranim u relacije, u skladu s logikom predikata prvog reda. Stvarne relacijske baze podataka imaju tablice koje sadrže polja, ograničenja i okidače, a tablice su povezane preko stranih ključeva. SQL se koristi za deklariranje podataka koji se vraćaju, a SQL procesor upita i optimizator upita pretvaraju SQL deklaraciju u plan upita koji izvršava mehanizam baze podataka.

SQL uključuje podjezik za definiranje shema, jezik definicije podataka (DDL), zajedno s podjezikom za izmjenu podataka, jezikom za manipulaciju podacima (DML). Oboje imaju korijene u ranim CODASYL specifikacijama. Treći podjezik u SQL-u deklarira upite putem SELECTizraza i relacijskih spajanja.

SQL  SELECTizraz

SELECTIzjava govori upita optimizator o podacima za povratak, što tablica pogledati u, što odnosi slijediti, a što bi se nametnuti vraćene podatke. Alat za optimizaciju upita mora sam otkriti koje indekse koristiti kako bi se izbjeglo skeniranje tablice grube sile i postigla dobra izvedba upita, osim ako određena baza podataka ne podržava savjete o indeksu.

Dio umjetnosti relacijskog dizajna baze podataka ovisi o razumnoj upotrebi indeksa. Ako izostavite indeks za čest upit, cijela baza podataka može se usporiti pod velikim opterećenjima čitanja. Ako imate previše indeksa, cijela baza podataka može se usporiti pod velikim opterećenjima upisivanja i ažuriranja.

Još jedna važna umjetnost je odabir dobrog, jedinstvenog primarnog ključa za svaki stol. Ne samo da morate uzeti u obzir utjecaj primarnog ključa na uobičajene upite, već kako će se on reproducirati u pridruživanjima kad se u drugoj tablici pojavi kao strani ključ i kako će utjecati na referentnu lokaciju podataka.

U naprednom slučaju tablica baze podataka koje su podijeljene u različite volumene, ovisno o vrijednosti primarnog ključa, koje se naziva vodoravno oštrina, također morate uzeti u obzir kako će primarni ključ utjecati na osipanje. Savjet: Želite da se tablica ravnomjerno rasporedi po volumenima, što sugerira da ne želite koristiti datumske žigove ili uzastopne cijele brojeve kao primarne ključeve.

Rasprave o SELECTizjavi mogu započeti jednostavno, ali brzo mogu postati zbunjujuće. Smatrati:

ODABERITE * OD KUPCA;

Jednostavno, zar ne? Traži sva polja i sve redove Customerstablice. Pretpostavimo, međutim, da Customerstablica ima stotinu milijuna redaka i stotinu polja, a jedno od polja je veliko tekstualno polje za komentare. Koliko će trebati uklanjanje svih tih podataka preko mrežne veze od 10 megabita u sekundi ako svaki redak sadrži prosječno 1 kilobajt podataka?

Možda biste trebali smanjiti koliko šaljete putem žice. Smatrati:

ODABERITE TOP 100 companyName, lastSaleDate, lastSaleAmount, totalSalesAmount OD KUPACA

GDJE država I grad

NARUČI PO PRODANOJ DOLASCI;

Sad ćete izvući puno manje podataka. Zatražili ste od baze podataka da vam daju samo četiri polja, da uzmete u obzir samo tvrtke u Clevelandu i da vam date samo 100 tvrtki s najnovijom prodajom. Da bi to najučinkovitije na poslužitelj baze podataka, međutim, Customerstablica treba indeksa na state+cityza WHEREklauzuli i indeks na lastSaleDateza ORDER BYi TOP 100odredbama.

Inače, TOP 100vrijedi za SQL Server i SQL Azure, ali ne i za MySQL ili Oracle. U MySQL-u biste koristili LIMIT 100nakon WHEREklauzule. U Oracleu biste koristili bound on ROWNUMkao dio WHEREklauzule, tj WHERE... AND ROWNUM <=100. Nažalost, ANSI / ISO SQL standardi (a do danas ih je devet, koji se protežu od 1986. do 2016.) idu samo toliko daleko, nakon čega svaka baza podataka uvodi vlastite vlasničke klauzule i značajke.

SQL se pridružuje 

Do sada sam opisao SELECTsintaksu za pojedinačne tablice. Prije nego što uspijem objasniti  JOINklauzule, morate razumjeti strane ključeve i odnose između tablica. Objasnit ću to primjenom primjera u DDL-u, koristeći sintaksu SQL poslužitelja.

Kratka verzija ovoga prilično je jednostavna. Svaka tablica koju želite koristiti u relacijama trebala bi imati ograničenje primarnog ključa; to može biti jedno polje ili kombinacija polja definiranih izrazom. Na primjer:

STVORI TABELU Osobe (

    PersonID int NIJE NULL PRIMARNI KLJUČ,

    Char Ime osobe (80),

    ...

Svaka tablica na koju se treba povezati Personstrebala bi imati polje koje odgovara Personsprimarnom ključu, a za očuvanje relacijskog integriteta to polje trebalo bi imati ograničenje stranog ključa. Na primjer:

IZRADI NARUDŽBU (

    OrderID int NIJE NULL PRIMARNI KLJUČ,

    ...

    PersonID int STRANE KLJUČNE REFERENCE Osobe (PersonID)

);

Postoje duže verzije obje izjave koje koriste CONSTRAINTključnu riječ, što vam omogućuje imenovanje ograničenja. To generira većina alata za dizajn baze podataka.

Primarni ključevi su uvijek indeksirani i jedinstveni (vrijednosti polja ne mogu se duplicirati). Ostala polja se po želji mogu indeksirati. Često je korisno stvoriti indekse za polja s inozemnim ključem i za polja koja se pojavljuju u WHEREi ORDER BYklauzulama, iako ne uvijek, zbog potencijalnih dodatnih troškova zbog upisa i ažuriranja.

Kako biste napisali upit koji vraća sve narudžbe John Doea?

ODABIRI Ime osobe, ID NARUDŽBE OD Osoba

INNER JOIN Nalozi za osobe.PersonID = Narudžbe.PersonID

GDJE Ime osobe;

U stvari, postoje četiri vrste JOIN: INNER, OUTER, LEFT, i RIGHT. INNER JOINJe zadana (možete izostaviti riječ INNER), a to je onaj koji uključuje samo retke koji sadrže odgovarajuće vrijednosti u obje tablice. Ako želite navesti osobe bez obzira imaju li narudžbe, upotrijebite LEFT JOIN, na primjer:

ODABIRI Ime osobe, ID NARUDŽBE OD Osoba

LIJEVO PRIDRUŽITE Naruke NA Persons.PersonID = Nalozi.PersonID

NARUČITE PO IME LICA;

Kada počnete raditi upite koji spajaju više od dvije tablice, koji koriste izraze ili koji prisiljavaju tipove podataka, sintaksa se u početku može malo dlakavati. Srećom, postoje alati za razvoj baze podataka koji mogu generirati ispravne SQL upite za vas, često povlačenjem i ispuštanjem tablica i polja iz sheme sheme u dijagram upita.

SQL pohranjene procedure

Ponekad vas deklarativna priroda SELECTizjave ne vodi tamo gdje želite ići. Većina baza podataka ima mogućnost koja se naziva pohranjene procedure; nažalost ovo je područje u kojem gotovo sve baze podataka koriste vlasnička proširenja za ANSI / ISO SQL standarde.

U SQL Serveru, početni dijalekt za pohranjene procedure (ili pohranjene procs) bio je Transact-SQL, zvani T-SQL; u Oracleu je to bio PL-SQL. Obje baze podataka dodale su dodatne jezike za pohranjene procedure, kao što su C #, Java i R. Jednostavna pohranjena procedura T-SQL može biti samo parametrizirana verzija SELECTizraza. Njegove su prednosti jednostavnost upotrebe i učinkovitost. Pohranjeni postupci optimiziraju se kad se spreme, a ne svaki put kada se izvrše.

Kompliciraniji T-SQL pohranjeni postupak može koristiti više SQL izraza, ulazne i izlazne parametre, lokalne varijable, BEGIN...ENDblokove, IF...THEN...ELSEuvjete, pokazivače (obrada retka po redu skupa), izraze, privremene tablice i čitav niz drugih proceduralna sintaksa. Očito je ako je pohranjeni jezik procedura C #, Java ili R, koristit ćete funkcije i sintaksu tih proceduralnih jezika. Drugim riječima, usprkos činjenici da je motivacija za SQL bila korištenje standardiziranih deklarativnih upita, u stvarnom svijetu vidite puno proceduralnih programiranja poslužitelja specifičnih za bazu podataka.

To nas ne vraća baš u loša stara vremena programiranja baze podataka CODASYL (premda se pokazivači približavaju), ali se vraća od ideja da bi SQL izrazi trebali biti standardizirani i da bi brigu o performansama trebalo prepustiti optimizatoru upita baze podataka. . Na kraju, udvostručavanje performansi često je previše da bi se ostavilo na stolu.

Naučite SQL

Dolje navedena mjesta mogu vam pomoći da naučite SQL ili otkriti hirove različitih SQL dijalekata.