SQLvalmennus Alikyselyt alikyselyt yleisesti milloin alikyselylle ei ole

  • Slides: 24
Download presentation
SQL-valmennus Alikyselyt - alikyselyt yleisesti - milloin alikyselylle ei ole vaihtoehtoa - korreloimattomat alikyselyt

SQL-valmennus Alikyselyt - alikyselyt yleisesti - milloin alikyselylle ei ole vaihtoehtoa - korreloimattomat alikyselyt - korreloidut alikyselyt - tehokkuusnäkökulma NULL-arvo - minkälaiseksi informaatioksi puuttuva arvo tulkitaan - miksi Null ja erityisesti miksi EI - käsittely (vertailu ja laskenta null-arvolla) Tavallisimpien funktioiden käyttö - mm. merkkijonofunktiot - päivämääräfunktiot ja päivämääristä yleisesti

Kertausta WHERE-lauseessa = -merkin ja like-määreen ero koostefunktioiden kanssa pitää muistaa, ettei detaljitietoja voi

Kertausta WHERE-lauseessa = -merkin ja like-määreen ero koostefunktioiden kanssa pitää muistaa, ettei detaljitietoja voi esittää sarakeluettelossa, ellei detaljitietoa ole mainittu GROUP BY-määreessä minkälaisesta kyselystä tulee vastukseksi täsmälleen yksi rivi mistä kyselystä voi tulla vastaukseksi o – n riviä miten haetaan vaikkapa asiakkaiden lukumäärä

Alikyselyt alikyselyitä voi käyttää SELECT, INSERT, UPDATE ja DELETEkomennoissa alikysely on aina suluissa kyselyjä

Alikyselyt alikyselyitä voi käyttää SELECT, INSERT, UPDATE ja DELETEkomennoissa alikysely on aina suluissa kyselyjä voi olla useita sisäkkäin sisin alikysely suoritetaan ensin käytetään automaattisesti distinct-määrettä (poistaa tuplat) SELECT huoneno, asno FROM Majoitus WHERE hinta = (SELECT max(hinta) FROM Majoitus)

ALIKYSELYN PERUSMALLIT select. . . from. . . where jokusarake = (alikysely, joka tuottaa

ALIKYSELYN PERUSMALLIT select. . . from. . . where jokusarake = (alikysely, joka tuottaa täsmälleen yhden arvon) where asno = 25 where jokusarake in (alikysely, joka tuottaa 0 -n arvoa) where asno in 1, 25, 27

Yhden tuloksen tuottavat kyselyt yksi tulos tarkoittaa yhtä riviä ja yhtä saraketta SELECT MAX(hinta)

Yhden tuloksen tuottavat kyselyt yksi tulos tarkoittaa yhtä riviä ja yhtä saraketta SELECT MAX(hinta) FROM Majoitus WHERE-lauseessa tällaisen alikyselyn kanssa voidaan käyttää vertailuoperaattoreita =, <, > ja < > < = Ø= erisuuri kuin (myös != joissain tuotteissa) pienempi tai yhtäsuuri kuin suurempi tai yhtäsuuri kuin WHERE hinta = (SELECT MAX(hinta). . . )

Monta riviä tuottavat kyselyt jos tuloksena monta riviä ja yksi sarake, voidaan WHERE-lauseessa käyttää

Monta riviä tuottavat kyselyt jos tuloksena monta riviä ja yksi sarake, voidaan WHERE-lauseessa käyttää IN-määrettä (jos arvo on jokin listassa oleva) WHERE-lauseessa tällaisen alikyselyn kanssa voidaan käyttää vertailuoperaattoreita >, < vain jos lisäksi SOME / ANY, mikä tahansa alikyselyn arvo ALL, alikyselyn jokainen arvo täyttää ehdon

Mitä kysely siis hakee? SELECT asno, huoneno FROM Majoitus WHERE hinta > ALL (SELECT

Mitä kysely siis hakee? SELECT asno, huoneno FROM Majoitus WHERE hinta > ALL (SELECT hinta FROM Majoitus WHERE asno = 1)

Alikysely voidaan sijoittaa WHERE-määreeseen, sarakeluetteloon tai FROM_määreeseen. SELECT asno, Myynti. Asiakkaittain FROM (SELECT asno,

Alikysely voidaan sijoittaa WHERE-määreeseen, sarakeluetteloon tai FROM_määreeseen. SELECT asno, Myynti. Asiakkaittain FROM (SELECT asno, avg(hinta) AS Myynti. Asiakkaittain FROM Majoitus GROUP BY asno) tai SELECT asno, hinta, (SELECT max(hinta) FROM Majoitus) AS Suurin. Hinta FROM Majoitus

 Alikyselyä ei voi korvata liitoksella, jos alikyselyssä on koostefunktio Kyselyn tulee hakea asiakastaulusta

Alikyselyä ei voi korvata liitoksella, jos alikyselyssä on koostefunktio Kyselyn tulee hakea asiakastaulusta sen asiakkaan nimitiedot, jonka asiakasnumero on suurin. SELECT sukunimi, etunimi FROM Asiakas WHERE asno = (SELECT max(asno) FROM Asiakas)

 Alikyselyä ei voi korvata liitoksella, jos • alikysely on muotoa NOT IN tai

Alikyselyä ei voi korvata liitoksella, jos • alikysely on muotoa NOT IN tai NOT EXISTS Kyselyn tulee hakea asiakastaulusta sen asiakkaan nimitiedot, jonka majoitus eivät ole maksaneet yli 2000 euroa SELECT sukunimi, etunimi FROM Asiakas WHERE asno NOT IN ( SELECT asno FROM Majoitus WHERE hinta > 2000)

 Alikyselyä ei voi korvata liitoksella, jos • alikysely on UPDATE- tai DELETE-käskyssä UPDATE

Alikyselyä ei voi korvata liitoksella, jos • alikysely on UPDATE- tai DELETE-käskyssä UPDATE Asiakas SET bonus = yes WHERE asno in (SELECT asno FROM Majoitus GROUP BY asno HAVING count(*) > 2)

Korreloidut alikyselyt (riippuva, kytketty) WHERE-osan alikyselyssä viitataan pääkyselyn sarakkeeseen, puhutaan kytketyistä alikyselyistä pääkyselyn saraketta

Korreloidut alikyselyt (riippuva, kytketty) WHERE-osan alikyselyssä viitataan pääkyselyn sarakkeeseen, puhutaan kytketyistä alikyselyistä pääkyselyn saraketta sanotaan kytketyksi sarakkeeksi kysely suoritetaan uudestaan jokaista pääkyselyn riviä kohden alikyselyn kytketty sarake saa joka kierroksella arvon vuorossa olevalta pääkyselyn riviltä

Miten voisi hakea sen asiakkaan / niiden asiakkaiden nimitiedot, joilla on täsmälleen 4 majoitusta?

Miten voisi hakea sen asiakkaan / niiden asiakkaiden nimitiedot, joilla on täsmälleen 4 majoitusta? SELECT sukunimi, etunimi FROM Asiakas WHERE 4 = (select count(*) FROM Majoitus WHERE Asiakas. asno = Majoitus. asno) Alikyselyssä viitataan ulompaan kyselyyn.

SELECT sukunimi, etunimi FROM Asiakas WHERE asno IN (SELECT Asiakas. asno FROM Asiakas, Majoitus

SELECT sukunimi, etunimi FROM Asiakas WHERE asno IN (SELECT Asiakas. asno FROM Asiakas, Majoitus WHERE Asiakas. asno = Majoitus. asno GROUP By Asiakas. asno HAVING count(*) = 4 )

Alikyselyn pohdintaa alikysely voi olla hidas, yleensä liitos on tehokkaampi, koska alikyselystä tulee helposti

Alikyselyn pohdintaa alikysely voi olla hidas, yleensä liitos on tehokkaampi, koska alikyselystä tulee helposti väliaikainen taulu SELECT tuottaa vastaukseksi tulosjoukon eli vastaulun, monien tuotteiden optimoijat osaavat sisäisesti muuttaa alikyselyn liitokseksi ennen suoritusta alikysely on usein selkeämpi kuin monimutkainen liitos Korreloidun alikyselyn pohdintaa korreloidut alikyselyt voivat olla todella hitaita lue pääkyselyn rivi, toteuta alikysely, . . .

Null puuttuva ei tyhjämerkki ei nolla kuten ääretön, ei voi vertailla

Null puuttuva ei tyhjämerkki ei nolla kuten ääretön, ei voi vertailla

Minkälaiseksi informaatioksi puuttuva arvo tulkitaan Codd: Relaatiokannoissa on järjestelmällinen null-arvon tuki — Tietokanta tuottaa

Minkälaiseksi informaatioksi puuttuva arvo tulkitaan Codd: Relaatiokannoissa on järjestelmällinen null-arvon tuki — Tietokanta tuottaa täydellisen tuen null-arvoille erotuksena muista arvoista. Null-arvo tarkoittaa puuttuvaa arvoa tai tuntematonta arvoa. Se ei ole sama kuin nolla tai tyhjämerkki tai mikään muukaan arvo. Sitä ei ’voi’ verrata muuhun tietoon, ei edes toiseen nullarvoon. Tietokantaa suunniteltaessa on syytä valita kohdealueen tilanteeseen sopiva oletusarvo sen sijaan, että sallittaisiin nullarvojen käyttö.

Null-arvo laskennassa Jos matemaattisessa lausekkeessa käytetään saraketta, jonka sisältö on null, saadaan tulokseksi null.

Null-arvo laskennassa Jos matemaattisessa lausekkeessa käytetään saraketta, jonka sisältö on null, saadaan tulokseksi null. Keskiarvojen laskemisessa on pohdittava, miten null-arvoja halutaan käsitellä. Null-arvo vertailussa Jos A: n arvo on null (riippumatta siitä, mikä B: n arvo on), relaatiotietokannan ANSI SQL: n mukainen vertailu tuottaa tulokseksi Tuntematon. Tämä on kuitenkin välinekohtaista. Yleisesti voi sanoa, että null-arvojen salliminen tietokannoissa aiheuttaa enemmän haittaa kuin hyötyä.

Hyödyllisiä funktioita merkkijonofunktiot: täyttö, liimaus, korvaus, muunnos, etsintä, . . . numeeriset funktiot pyöristys,

Hyödyllisiä funktioita merkkijonofunktiot: täyttö, liimaus, korvaus, muunnos, etsintä, . . . numeeriset funktiot pyöristys, itseisarvo, . . . päiväysfunktiot päivän tai kuukauden nimi, kuukauden päästä, kuun viimeinen päivä, . . . yleiset funktiot null-arvon vaihtaminen nollaksi järjestelmäfunktiot käyttäjätunnus, nykyhetki, . . . Funktioiden nimet ja valikoima vaihtelee järjestelmästä riippuen.

Esimerkkejä merkkijonoille käyvistä funktioista length (Sukunimi) merkkijonon pituus substring(Sukunimi, 1, 3) tai substr (Sukunimi,

Esimerkkejä merkkijonoille käyvistä funktioista length (Sukunimi) merkkijonon pituus substring(Sukunimi, 1, 3) tai substr (Sukunimi, 1, 3) merkkijonosta 3 mrk osamerkkijono alkaen 1 merkistä if. Null(Lähiosoite, ”ei ole”) tai COALESCE(Lähiosoite, ”ei ole”) korvataan Sukunimi-sarakkeen null-arvo replace(Sukunimi, ”korvattava”, ”korvaus”) korvataan merkkijono toisella upper(Sukunimi) / lower(Sukunimi) muuttaa ISOILLA tai pienillä kirjaimilla kirjoitetuksi ltrim(Sukunimi) / rtrim(Sukunimi) poistaa välilyönnit alusta / lopusta

Esimerkkejä numeroille käyvistä funktioista round(luku, 1) pyöristää yhden desimaalin tarkkuudella ceil(luku) pyöristää ylöspäin kokonaisluvuksi

Esimerkkejä numeroille käyvistä funktioista round(luku, 1) pyöristää yhden desimaalin tarkkuudella ceil(luku) pyöristää ylöspäin kokonaisluvuksi floor(luku) pyöristää alaspäin kokonaisluvuksi greatest(luku 1, luku 2, luku 3, . . . ) least(luku 1, luku 2, luku 3, . . . ) suurin/pienin lukusarjasta sign(luku) palauttaa -1 neg. ja 1 pos. luvuille

Päivämääräfunktioista Päivämäärille on joukko funktioita, jotka monet ovat tuotekohtaisia päivämäärien erotus esim. months_between(alkupvm, loppupvm)

Päivämääräfunktioista Päivämäärille on joukko funktioita, jotka monet ovat tuotekohtaisia päivämäärien erotus esim. months_between(alkupvm, loppupvm) vuoden, kuukauden tai päivän hakeminen year(päiväys) month(päiväys) day(päiväys) kuukauden viimeisen päivän hakeminen last_day(päiväys) lisää kuukausia päiväykseen add_months(alkupvm, lukumäärä)

Päivämäärän esittäminen päivämäärien esittäminen riippuu välineestä yleensä päivämääräesitellään ’ ’ –merkkien sisällä WHERE myyntipvm

Päivämäärän esittäminen päivämäärien esittäminen riippuu välineestä yleensä päivämääräesitellään ’ ’ –merkkien sisällä WHERE myyntipvm > ’ 31. 12. 2001’ Accessin SQL-tulkille päivämäärä on kirjoitettava ## merkkien väliin WHERE myyntipvm > #31 -12 -2000# Accessissa päivät-kuukaudet-vuodet on erotettava – tai / -merkillä käyttöjärjestelmän maa-asetukset voivat myös vaikuttaa päivämäärän esittelyyn (piste saattaa kelvata erotinmerkiksi)

Esimerkki päiväysfunktioista Oraclessa SELECT ’Tänään on ’ || lower( rtrim( to_char(sysdate, ’DAY’) ) ||

Esimerkki päiväysfunktioista Oraclessa SELECT ’Tänään on ’ || lower( rtrim( to_char(sysdate, ’DAY’) ) || to_char(sysdate, ’DD’) ) || ’ ’ || rtrim( to_char(sysdate, ’MONTH’) ) || ’ta’ from taulu