1	SQL-kielen perusteet © Jouni Huotari & Tapani Äijänen 1999-2006, viimeisin päivitys 12.9.2006 Kalvot perustuvat osin Ari Hovin SQL-oppaaseen (Docendo 2004)
2	1. Johdanto Huom. Relaatiomalli ym. tietokantojen perusteet on esitetty erillisillä kalvoilla
3	SQL - Structured Query Language SQL on tietokantojen käsittelyyn kehitetty kieli Yleisimmät kielellä hoidettavat toiminnot: Tietokannan rakenteen määrittely ja muuttaminen Kyselyt tietokantaan ja laskenta tietokannan datalla Tietojen ylläpito: lisäykset, muutokset ja poistot Valtuuksien käsittely Tapahtumankäsittelyn ohjaaminen Lisäksi SQL:n käyttö ohjelmoinnissa ”Upotettu” SQL; SQL:n käyttö muiden kielien sisältä API-rajapinnat ohjelmointikieliin
4	SQL SQL on luonteeltaan ei-proseduraalinen kieli, jossa kerrotaan mitä halutaan tehdä (ei miten) SQL perustuu relaatioalgebraan ja joukko-oppiin SQL:stä useita versioita, mm. SQL-92 eli SQL2, SQL'99 eli SQL3 ja SQL:2003 Kielestä on eri valmistajilla toisistaan poikkeavia versioita (murteita) SQL-kieltä voidaan käyttää useissa SQL-tuotteissa vuorovaikutteisesti antamalla SQL-käskyjä omaan ikkunaan, jolloin tulokset saadaan välittömästi (toiseen ikkunaan)
5	SQL-kielen historiaa Alun perin SEQUEL (Structured English QUEry Language) 1. versio kehitettiin IBM:n tutkimuskeskuksessa 1970-luvulla (E.F. Coddin relaatiomalliin pohjautuen) 1. kaupallinen versio esiteltiin v. 1979 (Oracle) SQL:n ANSI-standardi julkistettiin v. 1986 1989 lisättiin viite-eheysmääritykset 1992 liitossyntaksi, tapahtumanhallinta ym. 1999 mm. herättimet, talletetut proseduurit ja oliolaajennuksia + CLI (Call Level Interface) 2003 lisää olio-ominaisuuksia, XML, SQL/OLB ym.
6	Näissä kalvoissa esiintyvät SQL-käskyt Noudattavat pääosin SQL2-standardia Suurin osa on testattu Oraclella, OCELOTilla ja MS Accessilla; näistä vain OCELOT on täysin SQL-standardin (SQL2 ja osin SQL3) mukainen Kalvoissa on mainittu, jos käskystä on jokin standardista poikkeava tuotekohtainen ratkaisu Rakenne (käskyjen esitysjärjestys) on Ari Hovin kirjaa mukaileva
7	DDL ja DML Data Definition Language (DDL): tietokannan rakenteen määrittely Perus-SQL:ssä CREATE, ALTER ja DROP TABLE, CREATE ja DROP VIEW, CREATE ja DROP INDEX Käyttökelpoisia esimerkiksi silloin kun on tarve luoda uudestaan taulut ja sarakkeet Lisäksi mm. auktorisointikäskyt (GRANT ja REVOKE) Data Manipulation Language (DML): tietokannan käsittely Kyselyihin SELECT, koostefunktiot (mm. SUM ja COUNT) ym. Tietojen ylläpitoon UPDATE, DELETE ja INSERT Muutosten vahvistus ja peruutus (COMMIT ja ROLLBACK) Lisäksi mm. Oraclessa on ohjauskäskyt transaktioille, sessiolle ja järjestelmälle, esim. ANALYZE, AUDIT ja COMMENT
8	SQL ohjelmoinnissa ”Upotettu” SQL: SQL-käskyt muiden kielien sisältä SQL-käskyt voidaan laittaa ohjelmiin sellaisenaan ohjelma linkitetään relaatiokantaan API-rajapinnoilla (Application Programming Interface), esim. ODBC ja JDBC Dynaaminen SQL: (jonkin muuttujan arvoksi sijoitettu) SQL-käsky käännetään ja suoritetaan ajoaikana
9	2. Peruskyselyt SELECT-käskyn eri muodot
10	SELECT-käsky SQL-kielen keskeinen toiminto on kyselyiden tekeminen tietokantaan Kyselyissä SQL-kielen perusrakenne käsittää kolme avainsanaa, joista SELECT ja FROM ovat pakollisia: SELECT <attribuuttilista> valitsee listan mukaiset sarakkeet (attribuutit) kyselyyn FROM <taululista> määrittelee haettavat taulut WHERE <ehdot> määrittelee ehdot haettaville riveille (monikoille) SELECT * hakee kaikki sarakkeet SELECT ilman WHERE-ehtoa hakee kaikki rivit
11	SQL-käskyn esitystapa ja rakenne SQL-kyselyn yleinen muoto: SELECT [A1, A2, … AN] -- valittavat sarakkeet FROM [T1, T2 … TM] -- valittavat taulut WHERE [L1] -- kyselyn ehto GROUP BY [L2] -- ryhmittelyehto HAVING [L3] -- ryhmittely hakuehto ORDER BY [L4] -- lajitteluehto Kommentit alkaa yleensä kahdella viivalla: -- (ei toimi Accessissa; vanhassa MySQL:ssä pitää olla välilyönti viivojen jälkeen) /* Tällä tavoin voidaan merkitä pidempi kommentti monissa tuotteissa (mm. MySQL, Oracle, SQL Server) */ Monissa tuotteissa käskyn lopussa on puolipiste, mikä kertoo tuotteelle käskyn päättymisestä
12	SQL-esimerkkejä Hae kaikki henkilöt, jotka ovat syntyneet v. 1975: SELECT * FROM henkilo WHERE syntvuosi = 1975 DISTINCT, mikäli mahdolliset toistot halutaan poistaa: SELECT DISTINCT myyja FROM tuotteet SELECT-lause voi sisältää sarakkeisiin kohdistuvia operaatioita (laskentaa) ja sarakkeelle voidaan antaa nk. Aliasnimi (AS-sidesanalla), esim. SELECT hinta, hinta * 1.22 AS HintaSisALV, nimi FROM tuote Monissa tuotteissa AS-sidesana voidaan jättää pois
13	SQL-hakuesimerkkejä; lajittelu Haetaan kaikki tuotetiedot, lajittelu nimen mukaan: SELECT * FROM tuotteet ORDER BY nimi -- oletus = nouseva eli ASC, laskeva = DESC Kysely, joka lajittelee ensisijaisesti sukunimen mukaan, mutta näyttää kuitenkin ensin etunimen: SELECT etunimi, sukunimi, kunta, tutkinto, palkka FROM henkilo ORDER BY sukunimi, etunimi
14	Rivien valinta: WHERE-ehto WHERE-ehto voi sisältää vertailuoperaattoreita <, >, <>, <=, >=, !=, !>, !<) BETWEEN -määreen IN -määreen LIKE -määreen IS NULL tai IS NOT NULL -ehdon AND, OR ja NOT -operaattoreita Merkkijonohaut: % = mikä tahansa merkkijono (Accessissa *) _ = joku merkki (Accessissa ?) Kirjainkoolla on väliä (paitsi MS:n tuotteissa ja MySQL:ssä)
15	WHERE-esimerkkejä SELECT nimi, hinta FROM tuotteet WHERE hinta BETWEEN 100 AND 1000; SELECT nimi FROM tuotteet WHERE nimi LIKE '%86%'; SELECT nimi, hinta FROM tuotteet WHERE nimi NOT IN ('pentium' , 'K6');
16	Koostefunktiot Viisi standardifunktiota: AVG -- keskiarvo MIN -- pienin arvo MAX -- suurin arvo SUM -- summa COUNT -- lukumäärä Esim. montako henkilöä on henkilo-taulussa: SELECT Count(*) AS Lkm FROM henkilo;
17	Koostefunktioiden käyttö Koostefunktioita käytetään SELECT-lauseen sarakeluettelossa ja HAVING-lauseessa SELECT MAX(hinta), MIN(hinta) -- suurin ja pienin hinta FROM tuote; WHERE-lauseessa koostefunktioita ei voi käyttää (vaan ne on laitettava HAVING-lauseeseen), esim. Haetaan myyjät, joiden myymien tuotteiden hinta yhteensä on yli 5000: SELECT myyja, SUM(hinta) -- tuotteiden hintojen summa FROM tuote; GROUP BY myyja -- ryhmittely myyjän mukaan HAVING SUM(hinta) > 5000; DISTINCT-rajaus toimii muissa tuotteissa paitsi Accessissa: SELECT COUNT(DISTINCT city) -- monestako kaupungista FROM authors; -- kirjailijoita
18	Ryhmittely - Group By GROUP BY -ehdolla tiedot ryhmitellään jonkin sarakkeen tai sarakkeiden mukaan ryhmiin GROUP BY -ehdon ryhmiin kohdistetaan yleensä jokin summafunktio kaikki SELECT-listan lauseet, joissa ei ole funktiota, on lueteltava GROUP BY –lauseessa! Esim. kunkin myyjän tuotteiden hintojen keskiarvo: SELECT myyja, AVG(hinta) FROM tuote GROUP BY myyja; Kunkin myyjän tuotetyyppien määrä SELECT myyja, COUNT(myyja) FROM tuote GROUP BY myyja;
19	Ryhmittely - Having Sekä HAVING- että WHERE-lauseella voidaan ryhmittelyn tulosta rajata: SELECT myyja, COUNT() -- myös NULL kelpaa FROM tuote WHERE tuoteHinta > 100 GROUP BY myyja -- hae myyjät joilla HAVING COUNT() >2; -- vähintään 3 tuotetta Tulosjoukkoa voidaan siis rajata eri tasoilla: rajataan ensiksi ryhmiteltävä joukko WHERE-lausekkeella tästä syntynyt tulos ryhmitellään GROUP BY-määrittelyllä lopuksi ryhmiteltyä joukkoa voidaan vielä rajata HAVING-lauseella
20	Muita funktioita Pyöristys useimmissa tuotteissa ROUND-funktiolla, esim. SELECT palkka, round(palkka*1.1,0) AS uusipalkka FROM emp; Päivämääräfunktiot, ks. Hovi s. 56 – 65 Tuotekohtaiset maa-asetukset huomioitava ANSI-standardissa CURRENT_DATE: tämä päivä Tuotekohtaisista eroista on hyvä yhteenveto: http://troels.arvin.dk/db/rdbms/#functions
21	Merkkijonofunktioita Merkkitiedon käsittely standardissa merkkijono saadaaan funktiolla SUBSTRING(mjono FROM mista FOR pituus) monissa tuotteissa on kuitenkin SUBSTR(mjono,mista,mihin) -funktio (Accessissa Mid-funktio), esim.: SELECT sukunimi, ‘, ‘, SUBSTR(etunimi,1,1), ‘.’ FROM nimet Tuloksena lista: Aaltonen, M. … Muita merkkijonofunktioita: CHAR_LENGTH -- merkkien määrä (Oraclessa LENGTH) UPPER(nimi) -- merkit isoiksi (Accessissa UCase) LOWER(nimi) -- merkit pieniksi (Accessissa LCase) LEFT(nimi, n) -- merkkijono vasemmalta (n merkkiä) RIGHT(nimi, n) -- merkkijono oikealta (n merkkiä)
22	Vielä merkkijonojen käsittelystä Merkkijonojen yhdistäminen (konkatenointi) standardia noudattavat Oracle ja DB2, jossa merkkijonot yhdistetään kahdella pystyviivalla, esim.: SELECT sukunimi \|\| ', ' \|\| etunimi AS Nimi FROM HENKILO; MS:n tuotteissa käytetään + -merkkiä ja MySQL:ssä CONCAT-funktiota Tietotyypin muuntaminen: eksplisiittisesti numeerisen ja merkkimuotoisen välillä, esim. ANSI-standardin CAST-funktiolla tai Oraclessa TO_CHAR (n, [format [,lang]]); muutoin implisiittisesti
23	SQL:2003-standardin uusia funktioita Nk. skalaarifunktiot: LN (), EXP (), POWER (), SQRT (), FLOOR (), CEIL[ING] (), WIDTH_BUCKET() Koostefunktiot: STDDEV_POP (), STDDEV_SAMP (), VAR_POP (), VAR_SAMP () ym. Nk. taulukkofunktiot: RANK () OVER ..., DENSE_RANK () OVER ..., PERCENT_RANK () OVER ..., CUME_DIST () OVER ..., ROW_NUMBER () OVER ... Muitakin on ...
24	Liitokset - haut useaan tauluun Tavallisin liitos on valintaliitos ehtona =, !=, <, <=, >=, > yhtäläisyysliitos (=) yleisin (vrt. Inner join) taulut liitetään toisiinsa yleensä avaimilla (perus- ja viiteavain) Esim. SELECT nimi, tilausnumero FROM asiakas INNER JOIN tilaus ON asiakas.asiakastunnus = tilaus.asiakastunnus; Kvalifiointi: taulun nimi sarakenimen eteen pakollista, jos saman niminen sarake kahdessa taulussa
25	Perinteinen liitos Liitosehto WHERE-lauseessa Esim. hae yrityksen nimi, ko. yrityksen henkilöiden nimet ja palkat yli 2500 tienaavista: SELECT Yrtun, Nimi, -- Hae yrityksen tunnus, nimi Sukunimi, Etunimi, Palkka -- ja henkilön nimi + palkka FROM YRITYS, HENKILO -- näistä tauluista WHERE YRITYS.Yrtun = HENKILO.Yrtun -- Liitosehto! AND Palkka > 7500 -- Hakuehto palkalle ORDER BY Nimi; -- Lajittelu
26	Uusi liitossyntaksi (suositeltavampi vaihtoehto) Liitosehto FROM-lauseessa Hae yrityksen nimi, ko. yrityksen henkilöiden nimet ja palkat yli 2500 tienaavista: SELECT Yrtun, Nimi, -- Hae yrityksen tunnus, nimi Sukunimi, Etunimi, Palkka -- henkilön suku- ja etunimi + palkka FROM YRITYS -- YRITYS –taulusta, joka INNER JOIN HENKILO -- liitetään HENKILO-tauluun ON YRITYS.Yrtun = HENKILO.Yrtun -- Liitosehto WHERE Palkka > 7500 -- Hakuehto palkalle ORDER BY Nimi; -- Lajittelu Standardin mukaan voidaan myös käyttää NATURAL JOIN –liitosta, jos liitossarakkeet ovat samannimiset
27	Liitokset: ulkoliitos (outer join) Outer Join ottaa mukaan myös ne rivit, jotka eivät täytä join-ehtoa; esim.: haetaan myös ne asiakkaat, joille ei löydy tilauksia: SELECT nimi, tilausnumero FROM asiakas LEFT OUTER JOIN tilaus ON asiakas.asiakastunnus = tilaus.asiakastunnus; vanha syntaksi: SELECT nimi, tilausnumero FROM asiakas, tilaus WHERE asiakas.asiakastunnus (+) = tilaus.asiakastunnus; OUTER-sana voidaan jättää pois SQL-standardissa
28	Liitos itseensä Liitos itseensä: tarvitaan korrelaationimeä (alias) Esim. haetaan JAMKin eri yksiköt: SELECT a.nimi FROM asiakas a, asiakas b WHERE a.emoyritystunnus = b.asiakastunnus AND b.nimi = 'JAMK'; Joissakin tuotteissa pitää käyttää AS-sidesanaa korrelaationimen edessä, joissakin pitää olla välilyönti Kun korrelaationimi on otettu käyttöön, ei alkuperäistä nimeä saa käyttää sarakkeen kvalifioinnissa (esim. edellisessä käskyssä SELECT asiakas.nimi antaisi virheilmoituksen
29	Yhdiste (UNION) Kaksi taulua (tai useampia) yhdistetään samantyyppisen tiedon avulla Esim. opettajien ja opiskelijoiden puhelinnumerot: SELECT OpeNimi AS Nimi, PuhNro, ’opettaja’ FROM OPETTAJA UNION SELECT OpiskNimi AS Nimi, PuhNro, ’opiskelija’ FROM OPISKELIJA ORDER BY 1;
30	Alikysely Voidaan käyttää SELECT, INSERT, UPDATE ja DELETE -komennoissa Sisin kysely suoritetaan ensiksi Voi palauttaa vain yhden sarakkeen ja sille yhden tai useamman arvon vertailuoperaattori “=“: alikysely voi palauttaa vain yhden arvon IN-operaattori tai vertailuoperaattori ANY tai ALL -ehdon kanssa sallii alikyselyn palauttavan useita arvoja Oltava aina suluissa ja haettavana saa olla vain yksi sarake Käyttää automaattisesti distinct-määrettä
31	Alikyselyt – esimerkki1: = Hae sen henkilön nimi, joka myy solmioita: SELECT HenkNimi FROM HENKILO WHERE HenkID = (SELECT MyyjaID FROM TUOTE WHERE TuoteNimi = ‘Solmio'); Aika usein alikyselyssä etsitään arvoa koostefunktiolla (palauttaa aina yhden arvon)
32	Alikyselyt – esimerkki 2: IN Hae niiden tuotteiden nimet ja hinnat, joita myy laiteosastolla olevat henkilöt: SELECT TuoteNimi, hinta FROM tuote WHERE myyja IN (SELECT myyja FROM myyjat WHERE osasto = 'laite'); Myös NOT IN on käyttökelpoinen, kun halutaan sulkea pois alikyselyn palauttama tulosjoukko
33	Alikyselyt – esimerkki 3: ANY, ALL ja EXISTS Jos käytetään vertailuoperaattoria > tai <, niin IN ei käy SELECT nimi, hinta, myyja FROM tuote -- hakuehto: WHERE hinta > ANY -- ne myyjät (SELECT hinta -- jotka myyvät FROM tuote -- kalliimpia tuotteita WHERE myyja = ‘Pekka'); -- kuin Pekka ANY: jos alikyselyn mikä tahansa arvo täyttää ehdon ALL: jos alikyselyn jokainen yksittäinen arvo täyttää ehdon EXISTS: tosi, jos alikysely palauttaa ainakin yhden rivin
34	3. Taulujen määrittely ja muuttaminen DDL: Taulujen luonti, muutos ja poisto DML: taulujen tietojen ylläpito Tapahtumien (transaktioiden) hallinta Näkymät, synonyymit ja muut tietokantaobjektit
35	Taulujen perustaminen Taulun perustajalla täytyy olla valtuudet (GRANT) tietokantaan Kullekin taulun sarakkeelle annetaan nimi ja tietotyyppi (+ maksimipituus ja mahdolliset desimaalit) Tarvittaessa määritetään rajoitteita (NOT NULL, PRIMARY KEY, FOREIGN KEY, UNIQUE, DEFAULT ja CHECK)
36	SQL-esimerkki: taulun luonti Taulu luodaan CREATE TABLE –käskyllä: CREATE TABLE YRITYS (YritysID smallint NOT NULL, YritysNimi varchar (80), Toimialakoodi char (10), YrLisaysPvm date, PRIMARY KEY (YritysID)) Viite-eheys REFERENCES-määreellä, ks. Rajoitteet CONSTRAINT fk_toimiala FOREIGN KEY (Toimialakoodi) REFERENCES TOIMIALA (Toimialakoodi)
37	Muutamia SQL-92-standardin tietotyyppejä CHAR [(pit.), kiinteä, oletuspit. 1] VARCHAR [(pit.), vaihtuvanmitt. merkkijono] NUMERIC [(koko pituus [,desimaaliosa])] INTEGER [kokonaisluku, pit. tuotekoht.] DATE [vuosi (0001-9999), kk ja päivä] TIME [(pit.), tunti, minuutti ja sekuntti] BLOB [SQL-99, binary large object]
38	Ocelot-tietokantatuote sis. SQL2:n ja osan SQL3:n tietotyypeistä
39	SQL:2003 – muutokset aikaisempaan standardiin Uudet tietotyypit: BIGINT (tarkkuus tuotekohtainen, kuitenkin aina BIGINT >= INTEGER >= SMALLINT) MULTISET (voidaan luoda ARRAY-tyyppinen kokoelma) Kaksi tietotyyppiä poistettu: BIT BIT VARYING Sequence CREATE TABLE LIKE … Ym.
40	Oraclen tietotyypit CHAR [255 tavua, kiinteäpituuksinen] VARCHAR2 [2 kt (4 kt Oracle 8:ssa)] NUMBER [numeerinen 1x10-¹³⁰ – 9,99x10¹²⁵] LONG [2 Gt] RAW [255 tavua, binaarinen] LONG RAW [2 Gt, binaarinen] DATE [pvm ja aika, kiinteäpituuksinen] ROWID [rivin ID:n muuttujatyyppi]
41	Muita Oraclen tietotyyppejä NCHAR ja NVARCHAR2: kansallisen kielen tietotyyppi CLOB: yksitavuinen, NCLOB: yksi- tai monitavuinen, 4 Gt BLOB: binaarinen, 4 Gt BFILE: osoitin tallennettuun ulkoiseen bin.tiedostoon Oraclen oma (esim. SDO_GEOMETRY for Oracle Spatial) Käyttäjän määrittämä tietotyyppi (voidaan tehdä INTYPE File Assistant -ohjelmalla)
42	SQL-esimerkki: taulun muutos Taulu muutetaan ALTER TABLE –käskyllä, esim. sarakkeen lisäys (loppuun) ALTER TABLE YRITYS ADD COLUMN Maa char (20) ALTER TABLE tuli osaksi SQL-standardia vasta SQL3:ssa Katso tuotekohtaisesti, voidaanko lisätä kerralla useita sarakkeita tai sarake johonkin väliin Sarakkeen muutos: ALTER COLUMN ja poisto: DROP COLUMN, ks. oma tuotteesi, esim. ALTER TABLE YRITYS DROP COLUMN Maa CASCADE
43	Eheysvaatimukset Tietokantaan kohdistuu kolmen tyyppisiä eheysvaatimuksia: Avaineheys: taululla tulee olla perus- eli pääavain (PK) Viite-eheys: viiteavaimelle tulee löytyä arvo viitattavan taulun pääavaimesta Attribuuttieheys: attribuuttien tulee kuulua sallittuun joukkoon Määritetään nk. rajoitteella (ks. seur. kalvo) Palvelin tarkistaa eheysvaatimusten toteutumisen
44	Rajoitteet eli pakotteet (constraints) Määritellään CREATE TABLE tai ALTER TABLE -komennoissa Rajoitteita voidaan määritellä sarakkeille tai tauluille Rajoitteita ovat: avaimet: PRIMARY KEY, FOREIGN KEY ja UNIQUE oletusarvot ja raja-arvotarkistukset: DEFAULT ja CHECK esimerkkejä: ALTER TABLE DEPT ADD CONSTRAINT pk_deptno PRIMARY KEY (deptno), ADD priority SMALLINT DEFAULT 1 ADD CONSTRAINT chk_sukup CHECK (sukupuoli IN (‘M’,’N’))
45	Primary Key, Unique ja Foreign key Primary Key (CONSTRAINT pk_sarake PRIMARY KEY (sarakenimi1 [,sarakenimi2]) Yksiköi taulun rivit Sarakkeiden tulee olla Not Null -tyyppisiä Käytetään hyväksi viite-eheyttä luotaessa Unique (CONSTRAINT sarakenimi UNIQUE) Luo yksilöivän indeksin (oletus on ei klusteroitu) Sarakkeet voivat sisältää Null-arvoja Voi olla useita / taulu Foreign Key (CONSTRAINT fk_sarake FOREIGN KEY (sarakenimi) REFERENCES vanhempi (perusvain) ) Luo viite-eheyden taulujen välille Voi sisältää useita sarakkeita
46	Viiteavaimen lisäys Viite-eheys voidaan määritellä taulua luotaessa tai jälkeenpäin ALTER TABLE -käskyllä Esim. Puhelin-taulu, jonka vanhempi on Henkilo; jos henkilö poistetaan niin myös henkilön puhelimet poistetaan (ON DELETE CASCADE) ALTER TABLE Puhelin ADD CONSTRAINT FK_HenkiloID FOREIGN KEY (HenkiloID) REFERENCES Henkilo (HenkiloID) ON DELETE CASCADE Viisi vaihtoehtoa: CASCADE, SET DEFAULT, SET NULL, NO ACTION, RESTRICT, joista kaksi viimeistä estävät poiston, jos vanhemmalla on lapsia Myös päivitykset voidaan vyöryttää: ON UPDATE CASCADE
47	Laskuri: automaattisesti kasvava sarake Taululle voidaan määritellä sarake, joka on automaattisesti kasvava numero Määrittelyssä annetaan alkuarvo ja lisäys Standardin mukaan laskuri luodaan joko IDENTITY- tai SEQUENCE-käskyllä Ominaisuus voidaan luoda myös CREATE TABLE, ALTER TABLE tai SELECT INTO –komennon yhteydessä Laskuri toimii usein perusavaimena
48	Laskurin luonti Esimerkki 1: CREATE TABLE tuotteet ( TUOTE_ID INTEGER GENERATED ALWAYS AS IDENTITY START WITH 100 INCREMENT 1 MINVALUE 100 NO MAXVALUE NO CYCLE, ... ) Esimerkki 2: CREATE SEQUENCE osanumero AS INTEGER START WITH 1 INCREMENT BY 1 MAXVALUE 100000 MINVALUE 1
49	SQL:2003 - GENERATED ALWAYS Esimerkki: CREATE TABLE EMPLOYEES ( EMP_ID INTEGER, SALARY DECIMAL(7,2), BONUS DECIMAL(7,2), TOTAL_COMP GENERATED ALWAYS AS ( SALARY + BONUS ), HR_CLERK GENERATED ALWAYS AS ( CURRENT_USER ) )
50	Taulun poisto Taulun poisto tapahtuu DROP TABLE -käskyllä, esim. DROP TABLE TEMP; Tällöin vyörytetään poistot, jos CASCADE DELETE on lapsitauluun määritetty, muutoin poisto estetään (jos viite-eheys on asetettu) Samalla poistuu taulussa olevat tiedot (olihan siis backup tallessa?)
51	DML Tietojen lisäys, muutos ja poisto Tapahtumien eli transaktioiden hallinta
52	Tietojen lisääminen INSERT INTO taulu VALUES (arvot); Esim. INSERT INTO prosessorit(cpu, hinta) VALUES ('AMD', 700); Sarakkeiden nimiä ei tarvitse luetella, jos kaikille annetaan arvo tai niillä on oletusarvo tai ne sallivat Null-arvon; on kuitenkin suositeltavaa luetella ne! Taulusta toiseen: INSERT INTO taulu1 SELECT s1, s2 FROM taulu2; MySQL:ssä voidaan antaa useita lisättäviä rivejä yhdessä käskyssä
53	Tietojen muuttaminen Olemassa olevien tietojen päivittäminen UPDATE tuotteet SET hinta = 1200 WHERE tuote_nimi = 'lelu'; Tai esim. 10 %:n hinnan lisäys: UPDATE tuotteet SET hinta = hinta * 1.1 Useaan tauluun perustuva päivitys UPDATE tuotteet SET määrä = 10 FROM tuotteet t, myyjat m WHERE t.myyja = m.myyja;
54	Tietojen poistaminen Rivien poistaminen taulusta DELETE {FROM} [taulun nimi] WHERE [ehto poistettaville riveille]: DELETE FROM tuote WHERE nimi = 'auto'; Poisto alikyselyn avulla DELETE asiakas WHERE asiakastunnus NOT IN (SELECT asiakastunnus FROM TILAUS);
55	Tapahtumien eli transaktioiden hallinta Tapahtuma on yleensä useamman käskyn kokonaisuus, joka suoritetaan joko kokonaan tai ei ollenkaan Esim. maalitilaston päivitys: BEGIN TRANSACTION INSERT INTO ottelu (joukkue1ID, joukkue2ID, kotimaalit, vierasmaalit) VALUES (3, 8, 4, 2); UPDATE joukkue SET maalisaldo = maalisaldo + :kotimaalit - :vierasmaalit WHERE joukkueID = :joukkue1ID; UPDATE joukkue SET maalisaldo = maalisaldo + :vierasmaalit - :kotimaalit WHERE joukkueID = :joukkue2ID; COMMIT; COMMIT hyväksyy, ROLLBACK peruuttaa tapahtuman
56	SQL-92 eristyvyystasot Eristyvyystaso ilmaisee transaktion eristyvyyttä eli riippumattomuutta muista samanaikaisista transaktioista Tavoitteena ehkäistä samanaikaisuuden ongelmia: 1 Hukatun päivityksen ongelma (lost update): päällekirjoitus 2 Keskeneräisen käsittelyn ongelma (uncommitted dependency) a) luetaan peruutettava b) päivitetään peruutettava 3 Ristiriitaisen tulkinnan ongelma (inconsistent analysis) a) eritahtinen päivitys uusien rivien ongelma (phantom rows) Katso esimerkit erillisestä kalvosetistä (J. Rantanen) http://data.hamk.fi/materiaalit/tiedonhallinta2/ccr/eristyvyys.pps
57	Eristyvyystasot Komennolla SET TRANSACTION ISOLATION LEVEL voidaan asettaa neljä eri eristyvyystasoa:
58	Muita tietokantaobjekteja Näkymät, synonyymit, indeksointi, valtuudet ja systeemihakemisto
59	Näkymät Näkymä (view) on looginen näyte tietokannan tauluista tai näkymistä Näkymä ei voi sisältää SELECT INTO, ORDER BY tai COMPUTE -ehtoja Ei voi viitata väliaikaisiin tauluihin Tarvitaan SELECT-oikeus tauluihin Esim: CREATE VIEW Hinnasto AS SELECT nimi, hinta FROM tuote; Käyttö: SELECT * FROM Hinnasto;
60	Näkymän käyttö Näkymän kautta voidaan päivittää tai lisätä tietoja, mutta yleensä vain yhteen tauluun ja silloinkin on useita ehtoja, mm. näkymässä ei saa olla DISTINCT-määrettä, GROUP BY tai HAVING-lausetta eikä alikyselyä Näkymä poistetaan DROP VIEW -komennolla Jos näkymässä on laskettuja sarakkeita, view-määrittelyn nimen jäljessä täytyy luetella sarakkeiden nimet: CREATE VIEW ALVhinnasto(nimi, ALVhinta) AS SELECT nimi, hinta*1.22 FROM TUOTE;
61	Esimerkki laskentaosasto-näkymästä CREATE VIEW Accounting AS SELECT dname, loc, empno, ename, job FROM dept, emp WHERE dept.deptno = emp.deptno AND dept.deptno = 10; -- 10: ACCOUNTING-osasto SELECT * FROM Accounting; DROP VIEW Accounting;
62	Vinkkejä näkymien luontiin Testaa ensin kysely ja luo vasta sitten näkymä Jos taulun nimi muuttuu, luo kysely jossa on alkuperäisen taulun kentät; tällä tavoin sinun ei tarvitse muuttaa olemassa olevia SELECT- ym. käskyjä Näkymillä voit suojata tietoja käyttäjäryhmittäin Voit rakentaa näkymän toisen näkymän päälle, mutta se voi vaikeuttaa ymmärtämistä
63	Synonyymit Helpottavat viittaamista tauluun antamalla tauluviittaukselle jokin kuvaava nimi Esim. jos toinen käyttäjä (Pekka) on antanut SELECT-oikeuden omaan tauluunsa (kurssi), voidaan tiedot hakea komennolla SELECT * FROM Pekka.kurssi Luomalla synonyymi (CREATE SYNONYM kurssit FOR Pekka.kurssi) voidaan em. kysely tehdä käskyllä SELECT * FROM kurssit Tarkista tuki synonyymille omasta tuotteestasi
64	Valtuudet Tavoitteena tietojen suojaus Tiedot voidaan suojata käyttäjä- ja taulukohtaisesti Käyttäjiä luodaan useimmissa tuotteissa CREATE USER –käskyllä ja salasana määritetään samalla, esim. CREATE USER jouni IDENTIFIED BY jh1 Joissain tuotteissa salasana luodaan CREATE PASSWORD -käskyllä Käyttöoikeudet voidaan niputtaa nk. rooliin (voi olla myös yksittäinen henkilö) ja luoda CREATE ROLE –käskyllä, esim. CREATE ROLE opettaja Valtuudet myönnetään GRANT-käskyllä, esim. GRANT SELECT ON kirjat TO jouni tai kaikki oikeudet GRANT ALL -käskyllä Valtuudet poistetaan REVOKE-käskyllä ja käyttäjä DROP USER -käskyllä
65	Indeksit Tauluille voidaan perustaa sarakekohtaisia hakemistoja eli indeksejä Indeksien käytön tarkoituksena on mm. nopeuttaa tietokantaan tapahtuvia hakuja Yksilöivä (unique) indeksi voi toimia myös perusavaimena CREATE UNIQUE INDEX i ON taulu(sarake)
66	Indeksien käyttö Indeksoinnin etuja: hakujen, liitosten, ryhmittelyjen ja lajittelujen nopeutuminen pakottaa yksilöimään rivit Kannattaa indeksoida: perusavaimet ja viiteavaimet sarakkeet, joita haetaan tai järjestetään usein Indeksointi vie levytilaa ja aikaa luotaessa ja muutoksissa Ei kannata indeksoida: harvoin haussa käytettäviä sarakkeita sarakkeita, joiden selektiivisyys on huono
67	Peukalosääntö indeksin luomiselle (nk. 3 tähden indeksi) Ota kaikki WHERE-kohdassa mainitut sarakkeet Lisää ORDER BY –kohdassa mainitut sarakkeet Laita loppuun SELECT-lauseessa olevat sarakenimet Esim. sopiva indeksi ”SELECT a, b, c FROM t WHERE d = ’x’ ORDER BY b” –käskylle olisi CREATE INDEX i_dbac ON t(d,b,a,c) Jos kaikki SELECTissä mainitut sarakkeet on mukana indeksissä, kyse on paksusta indeksistä Indeksi poistetaan DROP INDEX -käskyllä
68	Tietokannan ja tietokantakaavan (”skeeman”) perustaminen + systeemihakemisto Tietokanta luodaan monissa tuotteissa CREATE DATABASE -käskyllä, esim. CREATE DATABASE kirjat Tietokantakaava (schema) on kokoelma tietokanta-objekteja (taulut, näkymät jne.), se vastaa siis käsitettä tietokanta; kaava luodaan käskyllä CREATE SCHEMA Tieto eri tietokantaobjekteista on nk. tieto- eli systeemihakemistossa (catalog, data dictionary); se on standardin mukaan nimeltään INFORMATION_SCHEMA Tietokantakaavan omistaa aina yksi henkilö (rooli)
69	Haut systeemihakemistosta Käskyt vaihtelevat tuotteittain Lista (käyttäjän) tauluista saadaan eri tuotteissa seuraavilla käskyillä: SHOW TABLES (MySQL) SELECT table_name from user_tables (Oracle) SELECT tname FROM tab (Oracle) SELECT table_name FROM information_schema.tables (SQL Server) Lista tietohakemiston sisältämistä tietokannoista ja niiden omistajista (Ocelot): SELECT CATALOG_NAME, SCHEMA_NAME, SCHEMA_OWNER FROM INFORMATION_SCHEMA.SCHEMATA
70	Lisätietoa Relaatiotietokantasanasto: http://www.cs.helsinki.fi/~laine/relaatiosanasto/ Historiaa ym.: http://en.wikipedia.org/wiki/SQL Suomeksi: http://fi.wikipedia.org/wiki/SQL Yhteenveto: http://www.ocelot.ca/glossary.htm SQL-kurssi: http://sqlcourse.com ja http://sqlcourse2.com SQL-standardeista: http://www.jcc.com/sql.htm Tuotteiden erosta: http://troels.arvin.dk/db/rdbms/