Structured Query Language (SQL)

• tietokannat0502.mp3 3.4M
• tietokannat0503.mp3 3.6M
• tietokannat0504.mp3 3.6M
• tietokannat0505.mp3 3.6M
• tietokannat0506.mp3 3.4M

• 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

Data Definition Language (DDL)

Taulut luodaan CREATE TABLE komennolla.

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

Tauluja poistetaan DROP TABLE komennolla.

DROP TABLE Tyontekija;

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 PRECISION 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 PRECISION 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:

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

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 PRECISION NOT NULL DEFAULT 10000,
Syntymaaika  DATE NOT NULL DEFAULT '1970-1-23',
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
)

Tarkistukset

CREATE TABLE Tyontekija (
TyontekijaID	INTEGER NOT NULL,
Etunimi		VARCHAR(32) NOT NULL,
Sukunimi	VARCHAR(64) NOT NULL,
Palkka		DOUBLE PRECISION NOT NULL DEFAULT 10000 CHECK ( palkka > 0 ),
Syntymaaika	DATE NOT NULL DEFAULT '1970-1-23' CHECK (syntymaaika > '1900-1-1'),
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 UNIQUE,
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 Tyontekija (
TyontekijaID	INTEGER NOT NULL,
Etunimi		VARCHAR(32) NOT NULL,
Sukunimi	VARCHAR(64) NOT NULL,
Palkka		DOUBLE PRECISION NOT NULL DEFAULT 10000,
Syntymaaika	DATE NOT NULL DEFAULT '1970-1-23',
Osasto		INTEGER NOT NULL DEFAULT 1,
CONSTRAINT Tyontekija_Palkka
	CHECK ( palkka > 0 ),
CONSTRAINT Tyontekija_Syntymaaika
	CHECK (syntymaaika > '1900-1-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_Puhnro
	UNIQUE ( Puhelinnumero ),

CONSTRAINT   Puh_FK_Tyontekija
	FOREIGN KEY (Tyontekija)
	REFERENCES Tyontekija (TyontekijaID)
		ON DELETE CASCADE
		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 PRECISION NOT NULL DEFAULT 10000 CHECK ( palkka > 0 ),
Syntymaaika	DATE NOT NULL DEFAULT '1970-1-23' CHECK (syntymaaika > '1900-1-1'),
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 UNIQUE,
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
); 

Varmistaaksesi ettei tietokannassa ole valmiiksi mitään tauluja niin kannattaa ennen CREATE TABLE -lauseita poistaa jo olemassaolevat taulut.

DROP TABLE Osan_toimitus;
DROP TABLE Toimittaa;
DROP TABLE Tekee;
DROP TABLE Projekti;
DROP TABLE Koostuu;
DROP TABLE Osa;
DROP TABLE Toimittaja;
DROP TABLE Lapsi;
DROP TABLE Puhelinnumero;
DROP TABLE Tyontekija;
DROP TABLE Osasto;

Indeksien luominen

Indeksejä voidaan luoda joko uniikkeja joissa ei sallita samoja arvoja (esim. osaston nimi) tai tavallisia indeksejä joissa sallitaan useampi sama arvo (esim. sukunimi)

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

Taulun luomisen jälkeen voi vielä yrittää muuttaa taulun rakennetta. Tämä ei kuitenkaan yleensä ole kovin suositeltavaa.

ALTER TABLE

ALTER TABLE Tyontekija
ADD CONSTRAINT T_FK_Osasto
	FOREIGN KEY (Osasto)
	REFERENCES Osasto (OsastoID)
	ON UPDATE CASCADE
	ON DELETE RESTRICT;

ALTER TABLE Tyontekija
DROP CONSTRAINT T_FK_Osasto RESTRICT;

ALTER TABLE Tyontekija
DROP COLUMN Osasto RESTRICT;

Käyttöoikeudet

GRANT

REVOKE

GRANT SELECT ON Tyontekija TO peheinon;

GRANT INSERT, UPDATE ON Tyontekija TO peheinon;

GRANT ALL ON Tyontekija TO peheinon WITH GRANT OPTION;

REVOKE ALL ON Tyontekija FROM peheinon;

Transaktiot

SET TRANSACTION

COMMIT

ROLLBACK

SET TRANSACTION 
	READ WRITE	
	ISOLATION LEVEL SERIALIZABLE;

	...
	...
	...

COMMIT;