Tiedon haku eli SELECT-lause ...................................... 3 Distinct-määre ........................................................ 6 Lasketut kentät ....................................................... 7 Sarakkeen uudelleen nimeäminen .............................. 8 SQL:n funktioita ...................................................... 9
Merkkijonofunktiot ................................................ 9 Päivämääräfunktiot ............................................. 10 Muita funktioita .................................................. 11
Rivien valinta eli WHERE -ehto ................................ 12 Vertailu ............................................................. 12 Loogiset operaattorit: .......................................... 13 Hahmontunnistus ................................................ 15 Joukkoon kuuluminen .......................................... 17 Arvoväli ............................................................. 18 Vertailu tyhjä-arvoon ........................................... 19
Lajittelu eli ORDER BY -osa ..................................... 20
HAAGA-HELIA – TIKO / Digi-15 2 (21) Tieto ja tiedon varastointi J.Rantanen, O.Virkki
swd03_sql_dml_1 17.8.2018 16:29:00
SQL / DML ~ Data Manipulation language ~ Tietojen käsittely INSERT tiedon (rivien) lisäys tauluun UPDATE taulussa olevan tiedon muuttaminen / päivitys DELETE tiedon (rivien) poisto taulusta SELECT kysely tietokannasta Materiaalissa käytetyt esimerkit liittyvät Conolly, Begg. Database Systems -kirjan esimerkkiin asunnon vuokraus -tietokannasta
HAAGA-HELIA – TIKO / Digi-15 3 (21) Tieto ja tiedon varastointi J.Rantanen, O.Virkki
swd03_sql_dml_1 17.8.2018 16:29:00
Tiedon haku eli SELECT-lause ~ SELECT-lause etsii ja näyttää tietoja
yhdestä tai useammasta tietokannan taulusta SELECT [DISTINCT|ALL]
{ * | [column_expression [AS new_name]] [,...]}
FROM table_name [alias] [,...]
[WHERE condition]
[GROUP BY comlumn_list] [HAVING condition]
[ORDER BY column_list]
HAAGA-HELIA – TIKO / Digi-15 4 (21) Tieto ja tiedon varastointi J.Rantanen, O.Virkki
swd03_sql_dml_1 17.8.2018 16:29:00
Esim. Kaikki sarakkeet & kaikki rivit Hae kaikkien työntekijöiden kaikki tiedot SELECT * FROM staff; tai SELECT no, fname, lname, address, tel_no, position, sex, dob, salary, nin, bno FROM staff;
sno fname lname address tel_no position sex dob salary nin bno SL21 John White Manager 30000.00 B5 SG37 Ann Beech Snr Asst 12000.00 B3 SA9 David Ford Deputy 18000.00 B3 SA9 Mary Howe Assistant 9000.00 B7 SG5 Susan Brand Manager 24000.00 B3 SL41 Julie Lee Assistent 9000.00 B5
Älä käytä valmiissa ohjelmakohdassa koskaan
'SELECT *' –vaihtoehtoa; se lisää ohjelman tietoriippuvuutta ja heikentää samalla ylläpidettävyyttä
HAAGA-HELIA – TIKO / Digi-15 5 (21) Tieto ja tiedon varastointi J.Rantanen, O.Virkki
swd03_sql_dml_1 17.8.2018 16:29:00
Esim. Tietyt sarakkeet, kaikki rivit Hae kaikkien henkilöiden numerot, nimet ja palkat. SELECT sno, fname, lname, salary FROM staff;
sno fname lname salary
SL21 John White 30000.00
SG37 Ann Beech 12000.00
SA9 David Ford 18000.00
SA9 Mary Howe 9000.00
SG5 Susan Brand 24000.00
SL41 Julie Lee 9000.00
HAAGA-HELIA – TIKO / Digi-15 6 (21) Tieto ja tiedon varastointi J.Rantanen, O.Virkki
swd03_sql_dml_1 17.8.2018 16:29:00
Distinct-määre ~ valitaan vain yksi rivi yhtä sarakkeen arvoa kohti
(duplikaatit jää pois) DISTINCT:n vastakohta on ALL
(ALL on oletus; ei siis tarvitse kirjoittaa) Esim. Hae kohdenumerot vuokrattavien kohteiden näyttötiedosta SELECT pno FROM viewing;
pno PA14 PG4 PG4
PA14 PG36
SELECT DISTINCT pno FROM viewing;
pno PA14
PG4 PG36
HAAGA-HELIA – TIKO / Digi-15 7 (21) Tieto ja tiedon varastointi J.Rantanen, O.Virkki
swd03_sql_dml_1 17.8.2018 16:29:00
Lasketut kentät SELECT-lauseessa voidaan käyttää myös
laskutoimituksia numeeristen kenttien yhteydessä Esim: Hae henkilöiden numerot, nimet ja palkat se. henkilön palkka tulostetaan kuukausipalkkana SELECT sno, fname, lname, salary/12 FROM staff;
sno fname lname col4
SL21 John White 2500.00
SG37 Ann Beech 1000.00
SA9 David Ford 1500.00
SA9 Mary Howe 750.00
SG5 Susan Brand 2000.00
SL41 Julie Lee 750.00
HAAGA-HELIA – TIKO / Digi-15 8 (21) Tieto ja tiedon varastointi J.Rantanen, O.Virkki
swd03_sql_dml_1 17.8.2018 16:29:00
Sarakkeen uudelleen nimeäminen Kentän uusi nimi annetaan varatun sanan AS
jälkeen Esim: Hae henkilöiden numerot, nimet ja palkat se. henkilön palkka tulostetaan kuukausipalkkana SELECT sno, fname, lname, salary/12 AS monthly_salary FROM staff;
sno fname lname monthly_salary
SL21 John White 2500.00
SG37 Ann Beech 1000.00
SA9 David Ford 1500.00
SA9 Mary Howe 750.00
SG5 Susan Brand 2000.00
HAAGA-HELIA – TIKO / Digi-15 9 (21) Tieto ja tiedon varastointi J.Rantanen, O.Virkki
swd03_sql_dml_1 17.8.2018 16:29:00
SQL:n funktioita
Merkkijonofunktiot || yhdistää kaksi merkki- tai bittijonoa
UPPER muuttaa kirjaimet suuriksi
LOWER muuttaa kirjaimet pieniksi
CHAR_LENGTH palauttaa merkkijonon pituuden
SUBSTRING palauttaa osan merkkijonosta
POSITION palauttaa merkkijonon position (toisesta merkkijonosta)
TRIM poistaa merkkijonosta johtavat (LEADING), päättävät (TRAILING) tai molemmat (BOTH) merkit
Esim. fname || lname CHAR_LENGTH(‘Beech’) palauttaa 5 SUBSTRING(‘Beech’ FROM 1 to 3) palauttaa ‘Bee’ POSITION (‘ee’ IN ‘Beech’) palauttaa 2 TRIM (BOTH ‘*’ FROM ‘*** Hello World ***’) palauttaa ‘Hello World’
HAAGA-HELIA – TIKO / Digi-15 10 (21) Tieto ja tiedon varastointi J.Rantanen, O.Virkki
swd03_sql_dml_1 17.8.2018 16:29:00
Päivämääräfunktiot • päivämäärätiedot tallentuvat muodossa
yyyy-mm-dd HH:mi:ss CURRENT_DATE nykyinen päivämäärä
CURRENT_TIME nykyinen ajanhetki
CURRENT_TIME_STAMP nykyinen päivämäärä ja aika
EXTRACT päivämäärän osa Esim. CURRENT_TIME(2) palauttaa ajan sadasosasekunnin tarkkuudella CURRENT_TIME_STAMP(0) palauttaa pvm ja aika sekunnin tarkkuudella SELECT EXTRACT (YEAR FROM showdate) FROM viewing WHERE comment IS NULL; Samoin toimivat päivä (DAY) ja kuukausi (MONTH)
HAAGA-HELIA – TIKO / Digi-15 11 (21) Tieto ja tiedon varastointi J.Rantanen, O.Virkki
swd03_sql_dml_1 17.8.2018 16:29:00
Muita funktioita CAST konvertoi lausekkeen arvon toiseksi
tietotyypiksi CURRENT_USER
USER
nykyisen käyttäjän tunnus
SESSION_USER istunnon hallitsijan tunnus
SYSTEM_USER systeemikäyttäjän tunnus Esim. CAST(5E3 AS INTEGER)
Ehdollinen palautus, CASE CASE type WHEN ‘House’ THEN 1 WHEN ‘Flat’ THEN 2 ELSE 0 END
HAAGA-HELIA – TIKO / Digi-15 12 (21) Tieto ja tiedon varastointi J.Rantanen, O.Virkki
swd03_sql_dml_1 17.8.2018 16:29:00
Rivien valinta eli WHERE -ehto
Vertailu ~ comparison ~ vertaa lauseketta toiseen
Vertauluoperaattorit: = , < , > , <= , >= , <> erisuuruus myös != (ei ISO) Esim. Listaa kaikki henkilöt, joiden palkka on suurempi kuin 10 000 £. SELECT sno, fname, lname, salary FROM staff WHERE salary > 10000;
sno fname lname salary
SL21 John White 30000.00
SG37 Ann Beech 12000.00
SA9 David Ford 18000.00
SG5 Susan Brand 24000.00
HAAGA-HELIA – TIKO / Digi-15 13 (21) Tieto ja tiedon varastointi J.Rantanen, O.Virkki
swd03_sql_dml_1 17.8.2018 16:29:00
Loogiset operaattorit: OR , AND , NOT 1. prioriteettijärjestys: NOT , AND , OR 2. suluissa oleva evaluoidaan ensin 3. lausekkeen evaluointi vasemmalta oikealla Esim. Tulosta henkilönumerot henkilöiltä, joiden nimi on John White SELECT sno FROM staff WHERE fname = ‘John’ AND lname = ‘White’;
sno
SL21
HAAGA-HELIA – TIKO / Digi-15 14 (21) Tieto ja tiedon varastointi J.Rantanen, O.Virkki
swd03_sql_dml_1 17.8.2018 16:29:00
Esim. Listaa sellaisten haarakonttoreiden osoitteet, jotka sijaitsevat Lontoossa tai Glasgow:ssa. SELECT bno, street, area, city, pcode FROM branch WHERE city = ‘London’ OR city = ‘Glasgow’; BNO STREET AREA CITY PCODE B5 22m Deer Rd Sidcup London SW1 4EH B3 163 Main St Patrick Glasgow G11 0QX B2 56 Clover Dr London NW10 6EU
HAAGA-HELIA – TIKO / Digi-15 15 (21) Tieto ja tiedon varastointi J.Rantanen, O.Virkki
swd03_sql_dml_1 17.8.2018 16:29:00
Hahmontunnistus ~ pattern match ~ noudattaako merkkijono tiettyä hahmoa LIKE (NOT LIKE) % mikä tahansa merkkijono _ mikä tahansa merkki villimerkit SQL:ssä ovat erilaisia kuin 'yleensä' ! Esim. sarake LIKE ‘H%’ sarakkeen arvo alkaa H:lla sarake LIKE ‘H_ _ _ _’ sarakkeen arvo alkaa H:lla ja
sitä pitää seurata 4 merkkiä sarake LIKE ‘%e’ Sarakkeen arvo päättyy e:hen sarake NOT LIKE ‘H%’ Sarakkeen arvo ei ala H:lla sarake ‘15#%’ ESCAPE ‘#’ sarakkeen arvo 15%
HAAGA-HELIA – TIKO / Digi-15 16 (21) Tieto ja tiedon varastointi J.Rantanen, O.Virkki
swd03_sql_dml_1 17.8.2018 16:29:00
Esim. Etsi kaikki henkilöt, joiden osoitekentässä on Glasgow’-merkkijono SELECT sno, fname, lname,address FROM staff WHERE address LIKE ‘%Glasgow%’;
sno fname lname Address
SG37 Ann Beech 81 George St,Glasgow PA1 2JR
SA9 David Ford 63 Ashby St, Patrick,Glasgow G11
SG5 Susan Brand 5 Gt Western Rd,Glasgow G12
HAAGA-HELIA – TIKO / Digi-15 17 (21) Tieto ja tiedon varastointi J.Rantanen, O.Virkki
swd03_sql_dml_1 17.8.2018 16:29:00
Joukkoon kuuluminen ~ set membership ~ kuuluuko lausekkeen arvo johonkin arvojoukkoon IN (NOT IN) Esim. Listaa kaikki “managerit“ ja “deputyt“. SELECT sno, fname, lname, position FROM staff WHERE position IN (‘Manager’, ‘Deputy’);
sno fname lname position
SL21 John White Manager
SG5 Susan Brand Manager SG14 David Ford Deputy
tai SELECT sno, fname, lname, position FROM staff WHERE position = ‘Manager’ OR position = ‘Deputy’;
HAAGA-HELIA – TIKO / Digi-15 18 (21) Tieto ja tiedon varastointi J.Rantanen, O.Virkki
swd03_sql_dml_1 17.8.2018 16:29:00
Arvoväli ~ range ~ kuluuko lausekkeen arvo tiettyjen arvojen väliin BETWEEN … AND (NOT BETWEEN … AND) Esim. Listaa sellaisten henkilöiden tiedot, joiden palkka on välillä 20 000 £ – 30 000 £ SELECT sno, fname, lname, position, salary FROM staff WHERE salary BETWEEN 20000 AND 30000;
sno fname lname position salary
SL21 John White Manager 30000.00
SG5 Susan Brand Manager 24000.00
tai SELECT sno, fname, lname, position, salary FROM staff WHERE salary >=20000 AND salary <=30000;
HAAGA-HELIA – TIKO / Digi-15 19 (21) Tieto ja tiedon varastointi J.Rantanen, O.Virkki
swd03_sql_dml_1 17.8.2018 16:29:00
Vertailu tyhjä-arvoon ~ testaa onko sarakkeen arvo tyhjä IS NULL (IS NOT NULL) Esim. Etsi sellaisten näyttöjen katsojien numerot, kohteiden numerot ja päivämäärät, joihin ei liity kommentteja SELECT rno, pno, date FROM viewing WHERE comment IS NULL;
Rno Pno date CR56 PG4 26-May-95
CR56 PG36 28-Apr-95
HAAGA-HELIA – TIKO / Digi-15 20 (21) Tieto ja tiedon varastointi J.Rantanen, O.Virkki
swd03_sql_dml_1 17.8.2018 16:29:00
Lajittelu eli ORDER BY -osa • ORDER BY -osaan liitetään niiden sarakkeiden
nimet, joiden mukaan lajitellaan
• Jos lajittelusarakkeita useita, erotetaan ne toisistaan pilkulla
• lajittelu voi olla nouseva (ASCENDING, ASC , oletus) tai laskeva (DESCENDING, DESC)
Esim. Listaa henkilöstö aakkosjärjestyksessä nimen mukaan SELECT sno, fname, lname, salary FROM staff ORDER BY lname, fname;
sno fname lname salary
SG37 Ann Beech 12000.00
SG5 Susan Brand 24000.00
SA9 David Ford 18000.00
SA9 Mary Howe 9000.00
SL41 Julie Lee 9000.00
SL21 John White 30000.00
HAAGA-HELIA – TIKO / Digi-15 21 (21) Tieto ja tiedon varastointi J.Rantanen, O.Virkki
swd03_sql_dml_1 17.8.2018 16:29:00
Esim. Listaa vuokrattavat huoneiston tyypin mukaisessa järjestyksessä SELECT pno, type, rooms, rent FROM property_for_rent ORDER BY type;
Pno Type Room Rent PL94 Flat 4 400
PG4 Flat 3 350
PG36 Flat 3 375
PG16 Flat 4 450
PA14 House 6 650
PG21 House 5 600
Esim. Listaa vuokrattavat huoneiston tyypin mukaisessa järjestyksessä ja kalleimmat ensin SELECT pno, type, rooms, rent FROM property_for_rent ORDER BY type, rent DESC;
