Luento 5

• tietokannat502.mp3 3.2M
• tietokannat503.mp3 3.3M
• tietokannat504.mp3 3.6M
• tietokannat505.mp3 3.2M
• tietokannat506.mp3 4.0M
• tietokannat507.mp3 3.3M

Structured Query Language (SQL)

• standardoitu ja laajimmin käytetty kieli relaatiotietokantojen yhteydessä.
• Ensimmäinen versio vuodelta 1974 (SEQUEL)
• Virallinen standardi 1986
• Standardia korjailtiin 1989
• Seuraava versio vuodelta 1992 (SQL92)
• Uusin versio vuodelta 1999 (SQL99)
• SQL ei ole pelkästään kyselykieli kuten voisi nimestä yrittää päätellä. Kieli sisältää ominaisuudet:
  • tietokannan rakenteen määrittelyyn ja muuttamiseen
  • kyselyjen tekemiseen
  • tietojen lisäämiseen, muuttamiseen ja poistamiseen
  • valtuuksien ja turvallisuuden hoitamiseen
  • tapahtumankäsittelyn ohjaamiseen
  • upotetun SQL:n ja kohdistimien hallitsemiseen
• SQL:ää käytetään monin eri tavoin ja eri yhteyksissä:
  • Vuorovaikutteinen SQL
    käytetään antamalla SQL-käskyjä omassa ikkunassaan ja saadaan vastaukseen suoraan omaan ikkunaansa
  • Upotettu SQL
    SQL-käskyt upotetaan ohjelmointikieleen tai sovellus/raporttikehittimeen. Vastaukset suoraan ohjelmointikielen muuttujiin
  • Dynaaminen SQL
    SQL-käskyjä luodaan dynaamisesti ohjelmakoodissa ja lähetetään generoitu SQL-käsky tietokantajärjestelmälle käännettäväksi ja suoritettavaksi

Taulujen luominen

Taulut luodaan CREATE TABLE komennolla.

CREATE TABLE Tyontekija (
TyontekijaID INTEGER,
Etunimi VARCHAR(32),
Sukunimi VARCHAR(64),
Palkka DOUBLE,
Syntymaaika DATE,
Osasto INTEGER,
)

SQL-92:en tietotyypit

CHAR [(pit)]

Kiinteänmittainen merkkijono. Oletuspituus on 1.

VARCHAR [(pit)]

Vaihtuvanmittainen merkkijono.

NUMERIC [(pit., [desim.osa])]

Tarkka numeerinen arvo, jonka koko pituus on pit ja desim.osa kuvaa desimaaliosan pituutta.

DECIMAL [(pit., [desim.osa])]

Kuten edellä mutta pituus voi ohjelmistokohtaisesti olla pitempikin kuin pit.

INTEGER

Kokonaisluku, jonka pituus on ohjelmistokohtainen. Esim. 32-bittinen kokonaisluku.

SMALLINT

Kokonaisluku, joka on pienempi kuin INTEGER. Sen pituus on ohjelmistokohtainen (puolisana). Esim. 16-bittinen kokonaisluku.

FLOAT [(pit.)]

Liukuluku, jonka pituus on suurempi tai yhtäsuuri kuin pit.

REAL

Liukuluku, jolla on ohjelmistokohtainen pituus.

DOUBLE PRECISION

Kaksoistarkkuudella esitetty liukuluku, jonka pituus on ohjelmistokohtainen ja pitempi kuin REALin pituus.

BIT [(pit.)]

Mielivaltainen bittijono. Oletuspituus on 1.

BIT VARYING (pit.)

Mielivaltainen annetunmittainen bittijono. Pit on annettava.

DATE

Vuosi (0001 - 9999), kuukausi ja päivä.

TIME [(pit.)]

Tunti (00-23), minuutti (0-59) ja sekunti (00-61.9999).

TIMESTAMP [(pit.)]

Vuosi, kuukausi, päivä, sekä tunti, minuutti ja sekunti.

TIME WITH TIME ZONE (pit.)

Sama kuin TIME, mutta lisätietona erotus aikavyöhykkeeseen.

TIMESTAMP WITH TIME ZONE (pit.)

Sama kuin TIMESTAMP, mutta lisätietona erotus aikavyöhykkeeseen.

Kenttien pakollisuus

CREATE TABLE Tyontekija (
TyontekijaID INTEGER NOT NULL,
Etunimi      VARCHAR(32) NOT NULL,
Sukunimi     VARCHAR(64) NOT NULL,
Palkka       DOUBLE NOT NULL,
Syntymaaika  DATE NOT NULL,
Osasto       INTEGER NOT NULL
)

Perusavaimen määrittely

CREATE TABLE Tyontekija (
TyontekijaID INTEGER NOT NULL,
Etunimi      VARCHAR(32) NOT NULL,
Sukunimi     VARCHAR(64) NOT NULL,
Palkka       DOUBLE NOT NULL,
Syntymaaika  DATE NOT NULL,
Osasto       INTEGER NOT NULL,
CONSTRAINT   Tyontekija_PK
PRIMARY KEY  (TyontekijaID)
)

Viiteavaimien määrittely

CREATE TABLE Tyontekija (
TyontekijaID INTEGER NOT NULL,
Etunimi      VARCHAR(32) NOT NULL,
Sukunimi     VARCHAR(64) NOT NULL,
Palkka       DOUBLE NOT NULL,
Syntymaaika  DATE NOT NULL,
Osasto       INTEGER NOT NULL,
CONSTRAINT   Tyontekija_PK
             PRIMARY KEY (TyontekijaID),
CONSTRAINT   T_Osasto
             FOREIGN KEY (Osasto)
             REFERENCES Osasto (OsastoID)
)

Taulut, joihin halutaan viitata, täytyy luoda ennen viittauksia

Viiteavaimien toiminta

CREATE TABLE Tyontekija (
TyontekijaID INTEGER NOT NULL,
Etunimi      VARCHAR(32) NOT NULL,
Sukunimi     VARCHAR(64) NOT NULL,
Palkka       DOUBLE NOT NULL,
Syntymaaika  DATE NOT NULL,
Osasto       INTEGER NOT NULL,
CONSTRAINT   Tyontekija_PK
             PRIMARY KEY (TyontekijaID),
CONSTRAINT   T_Osasto
             FOREIGN KEY (Osasto)
             REFERENCES Osasto (OsastoID)
             ON DELETE NO ACTION
             ON UPDATE CASCADE
)

Oletusarvot

CREATE TABLE Tyontekija (
TyontekijaID INTEGER NOT NULL,
Etunimi      VARCHAR(32) NOT NULL,
Sukunimi     VARCHAR(64) NOT NULL,
Palkka       DOUBLE NOT NULL DEFAULT 10000,
Syntymaaika  DATE NOT NULL DEFAULT '1.1.1970',
Osasto       INTEGER NOT NULL DEFAULT 1,
CONSTRAINT   Tyontekija_PK
             PRIMARY KEY (TyontekijaID),
CONSTRAINT   T_Osasto
             FOREIGN KEY (Osasto)
             REFERENCES Osasto (OsastoID)
             ON DELETE NO ACTION
             ON UPDATE CASCADE
)

Koko tietokannan luominen

CREATE TABLE Osasto (
OsastoID	INTEGER NOT NULL,
OsastoNimi	VARCHAR(32) NOT NULL,
CONSTRAINT Osasto_PK
	PRIMARY KEY (OsastoID)
);

CREATE TABLE Tyontekija (
TyontekijaID	INTEGER NOT NULL,
Etunimi		VARCHAR(32) NOT NULL,
Sukunimi	VARCHAR(64) NOT NULL,
Palkka		DOUBLE NOT NULL DEFAULT 10000,
Syntymaaika	DATE NOT NULL DEFAULT '1.1.1970',
Osasto		INTEGER NOT NULL DEFAULT 1,
CONSTRAINT Tyontekija_PK
	PRIMARY KEY (TyontekijaID),
CONSTRAINT   T_FK_Osasto
	FOREIGN KEY (Osasto)
	REFERENCES Osasto (OsastoID)
		ON DELETE NO ACTION
		ON UPDATE CASCADE
);


CREATE TABLE Puhelinnumero (
Tyontekija	INTEGER NOT NULL,
Puhelinnumero	VARCHAR(32) NOT NULL,
CONSTRAINT Puhelinnumero_PK
	PRIMARY KEY (Tyontekija,Puhelinnumero),
CONSTRAINT   Puh_FK_Tyontekija
	FOREIGN KEY (Tyontekija)
	REFERENCES Tyontekija (TyontekijaID)
		ON DELETE CASCADE
		ON UPDATE CASCADE
);

CREATE TABLE Lapsi (
Huoltaja	INTEGER NOT NULL,
Syntymaaika	DATE NOT NULL,
CONSTRAINT Lapsi_PK
	PRIMARY KEY (Huoltaja, Syntymaaika),
CONSTRAINT   Lapsi_FK_Huoltaja
	FOREIGN KEY (Huoltaja)
	REFERENCES Tyontekija (TyontekijaID)
		ON DELETE CASCADE
		ON UPDATE CASCADE
);

CREATE TABLE Toimittaja (
ToimittajaID	INTEGER NOT NULL,
ToimittajaNimi	VARCHAR(64) NOT NULL,
CONSTRAINT Toimittaja_PK
	PRIMARY KEY (ToimittajaID)
);

CREATE TABLE Osa (
OsaID	INTEGER NOT NULL,
OsaNimi	VARCHAR(64) NOT NULL,
CONSTRAINT Osa_PK
	PRIMARY KEY (OsaID)
);

CREATE TABLE Koostuu (
K_OsaID		INTEGER NOT NULL,
OsaID		INTEGER NOT NULL,
Lukumaara	SMALLINT NOT NULL,
CONSTRAINT Koostuu_PK
	PRIMARY KEY (K_OsaID, OsaID),
CONSTRAINT   Koostuu_FK_1
	FOREIGN KEY (K_OsaID)
	REFERENCES Osa (OsaID)
		ON DELETE CASCADE
		ON UPDATE CASCADE,
CONSTRAINT   Koostuu_FK_2
	FOREIGN KEY (OsaID)
	REFERENCES Osa (OsaID)
		ON DELETE CASCADE
		ON UPDATE CASCADE
);

CREATE TABLE Projekti (
ProjektiID		INTEGER NOT NULL,
ProjektiNimi		VARCHAR(64) NOT NULL,
Projektipaallikko	INTEGER NOT NULL,
CONSTRAINT Projekti_PK
	PRIMARY KEY (ProjektiID),
CONSTRAINT   Projekti_FK_P
	FOREIGN KEY (Projektipaallikko)
	REFERENCES Tyontekija (TyontekijaID)
		ON DELETE NO ACTION
		ON UPDATE CASCADE
);

CREATE TABLE Tekee (
TyontekijaID	INTEGER NOT NULL,
ProjektiID	INTEGER NOT NULL,
CONSTRAINT Tekee_PK
	PRIMARY KEY (TyontekijaID, ProjektiID),
CONSTRAINT   Tekee_FK_1
	FOREIGN KEY (TyontekijaID)
	REFERENCES Tyontekija (TyontekijaID)
		ON DELETE NO ACTION
		ON UPDATE CASCADE,
CONSTRAINT   Tekee_FK_2
	FOREIGN KEY (ProjektiID)
	REFERENCES Projekti (ProjektiID)
		ON DELETE NO ACTION
		ON UPDATE CASCADE
);

CREATE TABLE Toimittaa (
ToimittajaID	INTEGER NOT NULL,
OsaID		INTEGER NOT NULL,
CONSTRAINT Toimittaa_PK
	PRIMARY KEY (ToimittajaID, OsaID),
CONSTRAINT   Toimittaa_FK_1
	FOREIGN KEY (ToimittajaID)
	REFERENCES Toimittaja (ToimittajaID)
		ON DELETE NO ACTION
		ON UPDATE CASCADE,
CONSTRAINT   Tekee_FK_2
	FOREIGN KEY (OsaID)
	REFERENCES Osa (OsaID)
		ON DELETE NO ACTION
		ON UPDATE CASCADE
);

CREATE TABLE Osan_toimitus (
ProjektiID	INTEGER NOT NULL,
ToimittajaID	INTEGER NOT NULL,
OsaID		INTEGER NOT NULL,
Lukumaara	SMALLINT NOT NULL,
CONSTRAINT OsaT_PK
	PRIMARY KEY (ProjektiID, ToimittajaID, OsaID),
CONSTRAINT   OSaT_FK_1
	FOREIGN KEY (ToimittajaID)
	REFERENCES Toimittaja (ToimittajaID)
		ON DELETE NO ACTION
		ON UPDATE CASCADE,
CONSTRAINT   OsaT_FK_2
	FOREIGN KEY (OsaID)
	REFERENCES Osa (OsaID)
		ON DELETE NO ACTION
		ON UPDATE CASCADE,
CONSTRAINT   OsaT_FK_3
	FOREIGN KEY (ProjektiID)
	REFERENCES Projekti (ProjektiID)
		ON DELETE NO ACTION
		ON UPDATE CASCADE
);

Indeksien luominen

CREATE INDEX

CREATE UNIQUE INDEX IDX_Osastonimi ON Osasto (OsastoNimi);
CREATE INDEX IDX_Sukunimi ON Tyontekija (Sukunimi);
CREATE INDEX IDX_Etunimi ON Tyontekija (Etunimi);
CREATE INDEX IDX_Huoltaja ON Lapsi (Huoltaja);
CREATE INDEX IDX_ProjektiNimi ON Projekti (ProjektiNimi);
CREATE INDEX IDX_OsaNimi ON Osa (OsaNimi);
CREATE INDEX IDX_ToimittajaNimi ON Toimittaja (ToimittajaNimi);


DROP INDEX IDX_Osastonimi;
DROP INDEX IDX_Sukunimi;
DROP INDEX IDX_Etunimi;
DROP INDEX IDX_Huoltaja;
DROP INDEX IDX_ProjektiNimi;
DROP INDEX IDX_OsaNimi;
DROP INDEX IDX_ToimittajaNimi;

Taulujen rakenteen muuttaminen

ALTER TABLE

ALTER TABLE Oppilaat
ADD Foobar VARCHAR(10)

ALTER TABLE Oppilaat
ADD CONSTRAINT Oppilaat_postinrofk FOREIGN KEY (postinumero)
  REFERENCES Postinumerot
  ON DELETE NO ACTION
  ON UPDATE CASCADES

ALTER TABLE Oppilaat
DROP CONSTRAINT Oppilaat_postinrofk

Käyttöoikeudet

GRANT

REVOKE

GRANT SELECT ON Oppilaat TO Petri, Esko

GRANT INSERT, UPDATE ON Oppilaat TO Petri

GRANT ALL ON Oppilaat TO Tommi
WITH GRANT OPTION

REVOKE ALL ON Oppilaat FROM Petri, Esko

Transaktiot

COMMIT

ROLLBACK

Herättimet (engl. triggers)

CREATE TRIGGER

Seuraavassa esimerkissä on luotu IBM:n DB2:een herättimillä sama toiminta, kuin saataisiin aikaan normaalisti viite-eheysmäärittelyillä. Vrt. Lapsi-taulun määrittely aiemmin

Käytetty SQL-syntaksi ei noudata standardia vaan on DB2:en oma versio herättimien määrittelystä. Määrittelyn perusperiaate tulee kuitenkin selväksi. Viite-eheysmääritys pitää poistaa ennen kuin voi luoda sen korvaavan herättimen.

ALTER TABLE Lapsi
DROP CONSTRAINT Lapsi_FK_Huoltaja;

CREATE TRIGGER Paivita_Lapsi
AFTER UPDATE ON Tyontekija
REFERENCING NEW AS N OLD AS O
FOR EACH ROW MODE DB2SQL
UPDATE Lapsi SET Huoltaja = N.TyontekijaID WHERE Huoltaja = O.TyontekijaID;

CREATE TRIGGER Poista_Lapsi
AFTER DELETE ON Tyontekija
REFERENCING OLD AS O
FOR EACH ROW MODE DB2SQL
DELETE FROM Lapsi WHERE Huoltaja = O.TyontekijaID;

CREATE TRIGGER Varmista_Lapsi_u
NO CASCADE BEFORE UPDATE ON Lapsi
REFERENCING NEW AS N
FOR EACH ROW MODE DB2SQL
WHEN (N.Huoltaja NOT IN ( SELECT TyontekijaID FROM Tyontekija ) )
	SIGNAL SQLSTATE '75000' ('The update value of the Huoltaja in Lapsi is no equal to any value of the parent key of the parent table');

CREATE TRIGGER Varmista_Lapsi_i
NO CASCADE BEFORE INSERT ON Lapsi
REFERENCING NEW AS N
FOR EACH ROW MODE DB2SQL
WHEN (N.Huoltaja NOT IN ( SELECT TyontekijaID FROM Tyontekija ) )
	SIGNAL SQLSTATE '75001' ('The insert value of the Huoltaja in Lapsi is no equal to any value of the parent key of the parent table');


DROP TRIGGER Paivita_Lapsi;
DROP TRIGGER Poista_Lapsi;
DROP TRIGGER Varmista_Lapsi_u;
DROP TRIGGER Varmista_Lapsi_i;

ALTER TABLE Lapsi
ADD CONSTRAINT Lapsi_FK_Huoltaja
	FOREIGN KEY (Huoltaja)
        REFERENCES Tyontekija (TyontekijaID)
                     ON DELETE CASCADE
                     ON UPDATE CASCADE;

Talletetut proseduurit (engl. stored procedures)

CREATE PROCEDURE