Luento 5
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 Oppilaat (
Sotu CHAR(11),
Sukunimi VARCHAR(100),
Katuosoite VARCHAR(100),
Postinumero INTEGER,
Puhelin VARCHAR(20),
Osastonro SMALLINT,
Vkurssinro SMALLINT,
Aloitusvuosi SMALLINT
) 

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 Oppilaat (
Sotu CHAR(11) NOT NULL,
Sukunimi VARCHAR(50) NOT NULL,
Katuosoite VARCHAR(50) NOT NULL,
Postinumero INTEGER NOT NULL,
Puhelin VARCHAR(20) NULL,
Osastonro SMALLINT NOT NULL,
Vkurssinro SMALLINT NOT NULL,
Aloitusvuosi SMALLINT NOT NULL
) 

Perusavaimen määrittely

CREATE TABLE Oppilaat (
Sotu CHAR(11) NOT NULL,
Sukunimi VARCHAR(50) NOT NULL,
Katuosoite VARCHAR(50) NOT NULL,
Postinumero CHAR(5) NOT NULL ,
Puhelin VARCHAR(20) NULL,
Osastonro SMALLINT NOT NULL,
Vkurssinro SMALLINT NOT NULL,
Aloitusvuosi SMALLINT NOT NULL,
CONSTRAINT Oppilaat_PrimaryKey 
	PRIMARY KEY (Sotu)
) 

Viiteavaimien määrittely

CREATE TABLE Oppilaat (
Sotu CHAR(11) NOT NULL,
Sukunimi VARCHAR(50) NOT NULL,
Katuosoite VARCHAR(50) NOT NULL,
Postinumero CHAR(5) NOT NULL ,
Puhelin VARCHAR(20) NULL,
Osastonro SMALLINT NOT NULL,
Vkurssinro SMALLINT NOT NULL,
Aloitusvuosi SMALLINT NOT NULL,
CONSTRAINT Oppilaat_PrimaryKey 
	PRIMARY KEY (Sotu),
CONSTRAINT Oppilaat_Postinumero
	FOREIGN KEY (postinumero) 
	REFERENCES Postinumerot (postinumero),
CONSTRAINT Oppilaat_Osastonro
	FOREIGN KEY (Osastonro) 
	REFERENCES Osastot (Osastonro),
CONSTRAINT Oppilaat_Vkurssinro
	FOREIGN KEY (Vkurssinro) 
	REFERENCES Vkurssit (Vkurssinro)

Viiteavaimien toiminta

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

CREATE TABLE Osastot (
Osastonro SMALLINT NOT NULL,
Osastonimi VARCHAR(100) NOT NULL,
CONSTRAINT Osastot_PrimaryKey
	PRIMARY KEY (Osastonro)
)

CREATE TABLE Vkurssit (
Vkurssinro SMALLINT NOT NULL,
Vkurssinimi VARCHAR(100) NOT NULL,
CONSTRAINT Vkurssit_PrimaryKey
	PRIMARY KEY (Vkurssinro)
)


CREATE TABLE Postinumerot (
Postinumero CHAR(5) NOT NULL,
Paikkakunta VARCHAR(100) NOT NULL,
CONSTRAINT Postinumerot_PrimaryKey 
	PRIMARY KEY (postinumero)
)



CREATE TABLE Oppilaat (
Sotu CHAR(11) NOT NULL,
Sukunimi VARCHAR(50) NOT NULL,
Katuosoite VARCHAR(50) NOT NULL,
Postinumero CHAR(5) NOT NULL ,
Puhelin VARCHAR(20) NULL,
Osastonro SMALLINT NOT NULL,
Vkurssinro SMALLINT NOT NULL,
Aloitusvuosi SMALLINT NOT NULL,
CONSTRAINT Oppilaat_PrimaryKey 
	PRIMARY KEY (Sotu),
CONSTRAINT Oppilaat_Postinumero
	FOREIGN KEY (postinumero) 
	REFERENCES Postinumerot (postinumero)
		ON DELETE NO ACTION
		ON UPDATE CASCADES,
CONSTRAINT Oppilaat_Osastonro
	FOREIGN KEY (Osastonro) 
	REFERENCES Osastot (Osastonro)
		ON DELETE NO ACTION
		ON UPDATE CASCADES,
CONSTRAINT Oppilaat_Vkurssinro
	FOREIGN KEY (Vkurssinro) 
	REFERENCES Vkurssit (Vkurssinro)
		ON DELETE NO ACTION
		ON UPDATE CASCADES,

Oletusarvot

CREATE TABLE Oppilaat (
Sotu CHAR(11) NOT NULL,
Sukunimi VARCHAR(50) NOT NULL,
Katuosoite VARCHAR(50) NOT NULL,
Postinumero CHAR(5) NOT NULL ,
Puhelin VARCHAR(20) DEFAULT 'ei numeroa',
Osastonro SMALLINT DEFAULT 1,
Vkurssinro SMALLINT DEFAULT 1,
Aloitusvuosi SMALLINT DEFAULT 2000,
CONSTRAINT Oppilaat_PrimaryKey 
	PRIMARY KEY (Sotu),
CONSTRAINT Oppilaat_Postinumero
	FOREIGN KEY (postinumero) 
	REFERENCES Postinumerot (postinumero)
		ON DELETE NO ACTION
		ON UPDATE CASCADES,
CONSTRAINT Oppilaat_Osastonro
	FOREIGN KEY (Osastonro) 
	REFERENCES Osastot (Osastonro)
		ON DELETE NO ACTION
		ON UPDATE CASCADES,
CONSTRAINT Oppilaat_Vkurssinro
	FOREIGN KEY (Vkurssinro) 
	REFERENCES Vkurssit (Vkurssinro)
		ON DELETE NO ACTION
		ON UPDATE CASCADES,

Loput taulut esimerkkitietokannasta

CREATE TABLE Kurssit (
KurssiID SMALLINT NOT NULL,
Kurssinnimi VARCHAR(100) NOT NULL,
Laajuus NUMERIC NOT NULL,
CONSTRAINT Kurssit_PrimaryKey
	PRIMARY KEY (Kurssitunnus)
)

CREATE TABLE Tentit (
Sotu CHAR(11) NOT NULL,
KurssiID SMALLINT NOT NULL,
Pvm DATE NOT NULL,
Arvosana NUMERIC NOT NULL,
CONSTRAINT Tentit_PrimaryKey
	PRIMARY KEY (Sotu, Kurssitunnus, pvm)
CONSTRAINT Tentit_Sotu
	FOREIGN KEY (Sotu) 
	REFERENCES Oppilaat (Sotu)
		ON DELETE CASCADES
		ON UPDATE CASCADES,
CONSTRAINT Tentit_kurssiID
	FOREIGN KEY (KurssiID) 
	REFERENCES Kurssit (KurssiID)
		ON DELETE NO ACTION
		ON UPDATE CASCADES,

)

Indeksien luominen

CREATE INDEX

CREATE INDEX Oppilaat_sukunimi ON Oppilaat(sukunimi)

CREATE UNIQUE INDEX Oppilaat_sotu ON Oppilaat(sotu)

DROP INDEX Oppilaat_sukunimi

DROP INDEX Oppilaat_sotu

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

Talletetut proseduurit (engl. stored procedures)

CREATE PROCEDURE