MODB vt2007
nikos dimitrakas
SQL
nikos dimitrakas
nikos@dsv.su.se
08-162099
rum 6626
Connolly/Begg (3rd & 4th edition) kapitel 5, 6 (och lite överallt)
1
MODB vt2007
nikos dimitrakas
SQL
Structured Query Language
•olika förslag till databasspråk för relations-DBMS har
förekommit, QUEL, SQL
•SQL fick tidigt dominerande ställning, IBM
•internationella standarden för data sub-language för
relations-DBMS kallas SQL, togs 1986.
•1992 publicerade ISO en standard som kallas SQL2 (eller
SQL92)
•standarden överensstämmer till stora delar med aktuella
SQL-dialekter för de vanligaste DBMS
•1999 publicerade ISO en ny version av standarden, SQL1999
(eller SQL3). Denna standard innehåller en del
objektorienterade funktionalitet.
•2003 kom den senaste versionen kallad SQL2003.
2
MODB vt2007
nikos dimitrakas
Uppdelning av SQL
• databeskrivning, SQL-DDL (Data Definition
Language)
• databearbetning, SQL-DML (Data Manipulation
Language)
• behörighet, SQL-DCL (Data Control Language)
SQL kan uttrycka allt som går att uttrycka i
relationsalgebran - språket är "relationally complete"
3
MODB vt2007
nikos dimitrakas
SQL - DDL
CREATE TABLE, skapa tabell CREATE SCHEMA
ALTER TABLE, ändra tabell CREATE DATABASE
DROP TABLE, ta bort tabell (även CATALOG)
CREATE INDEX, skapa index CREATE TRIGGER
DROP INDEX, ta bort index DROP TRIGGER
CREATE VIEW, skapa vy CREATE
DROP VIEW, ta bort vy PROCEDURE
DROP PROCEDURE
4
MODB vt2007
nikos dimitrakas
SQL-DML
•INSERT, lägg till
•UPDATE, ändra
•DELETE, ta bort
•COMMIT, spara
•ROLLBACK, återställ
•SELECT, utsökning
5
MODB vt2007
nikos dimitrakas
SQL-DCL
•CREATE USER, skapa
användare
•CREATE SCHEMA
•DROP USER, ta bort användare
•DROP SCHEMA
•GRANT, ge behörighet
•REVOKE, ta bort behörighet
6
MODB vt2007
nikos dimitrakas
Exempel DDL, DML
• Skapa en databas
– från konceptuell modell till färdiga tabeller
• Fylla på med data
– mata in data
– ändra data
– ta bort data
• Ställa frågor mot databasen
– enkla frågor
– aggregerade frågor
7
MODB vt2007
Konceptuell modell Logisk
nikos dimitrakas
modell Relationsdatabas
Hund
jagar Katt
namn 1..1 UNIQUE
ras 0..1 ID 1..1 UNIQUE
ägare 1..1 0..* 0..* färg 0..1
8
MODB vt2007
nikos dimitrakas
Konceptuell modell Logisk
modell Relationsdatabas
CREATE TABLE Hund (
id NUMBER PRIMARY KEY,
namn STRING NOT NULL UNIQUE,
ras STRING,
ägare STRING NOT NULL)
9
MODB vt2007
nikos dimitrakas
Konceptuell modell Logisk
modell Relationsdatabas
CREATE TABLE Katt (
kid NUMBER PRIMARY KEY,
färg STRING)
10
MODB vt2007
nikos dimitrakas Konceptuell modell Logisk
modell Relationsdatabas
CREATE TABLE Jakt (
hundid NUMBER, katt NUMBER,
PRIMARY KEY (hundid, katt),
FOREIGN KEY (hundid) REFERENCES Hund (id)
ON DELETE CASCADE ON UPDATE CASCADE)
11
MODB vt2007
nikos dimitrakas Konceptuell modell Logisk
modell Relationsdatabas
ALTER TABLE Jakt
ADD FOREIGN KEY (katt)
REFERENCES Katt
ON DELETE RESTRICT
ON UPDATE CASCADE 12
MODB vt2007
nikos dimitrakas
DROP TABLE
Tar bort en tabell
Ta bort tabellen Bil!
DROP TABLE Bil
Ta bort tabellen Bil och alla referenser!
DROP TABLE Bil CASCADE
Tar bort tabellen bil samt andra
databasobjekt som beror på tabellen bil.
13
MODB vt2007
nikos dimitrakas
Datatyper i SQL
INTEGER
CLOB
SMALLINT
(Character Large OBject)
NUMBER
BLOB
DECIMAL(p[,q])
(Binary Large OBject)
FLOAT
BOOLEAN XML
CHAR(n)
VARCHAR(n)
STRING(n)
TEXT
DATE
TIME
TIMESTAMP
MONEY
14
MODB vt2007
nikos dimitrakas
INSERT
Används för att lägga till rader i en existerande
tabell
Det finns två sätt
• med specificerade värden
• via SELECT
Man kan ange ett värde för varje kolumn i den
förbestämda ordningen
Man kan ange värden endast för vissa kolumner i
valfri ordning
Man kan lägga till en eller flera rader med ett
kommando
15
MODB vt2007
nikos dimitrakas
INSERT med specificerade värden
Lägg till en rad i tabellen Hund med värden 125,
”Woolfy”, ”Coley”, ”Johan”
INSERT INTO Hund
VALUES (125, ’Woolfy’, ’coley’, ’Johan’);
INSERT INTO Hund (ras, id, namn, ägare)
VALUES (’coley’, 125, ’Woolfy’,’Johan’);
Lägg till en rad i tabellen Hund med värden 3342,
”Jumpy”, ”Lisa”. Alltså ingen ras!
INSERT INTO Hund
VALUES (3342, ’Jumpy’, NULL, ’Lisa’);
INSERT INTO Hund (namn, id, ägare)
VALUES (’Jumpy’, 3342, ’Lisa’); 16
MODB vt2007
nikos dimitrakas
INSERT med specificerade värden
Lägg till flera rader med en gång (fungerar inte med
alla databashanterare, men ingår i standarden):
INSERT INTO Hund VALUES
(1233, ’Scrapy’, NULL, ’Lisa’),
(1555, ’Lucky’, ’Tax’, ’Nils’),
(2334, ’Ruddy’, ’Foxhound’, ’Lisa’);
17
MODB vt2007
nikos dimitrakas
INSERT via SELECT
Antag att vi har en tabell temp(hund, antaljakter)
där vi vill lägga in hund-id och totalt antal jagade
katter. Vi kan då räkna fram innehållet till tabellen
temp med en SELECT-sats. Vi skall se hur man
skriver sådana satser snart!
SELECT-satsens resultat blir nya rader i tabellen
temp.
INSERT INTO temp(hund, antaljakter)
SELECT-sats
18
MODB vt2007
nikos dimitrakas
INSERT och främmande nycklar
Lägger man in ett värde i en kolumn som är främmande
nyckel måste detta värde finnas i den relaterade
tabellen. Annars misslyckas inmatningen.
Lägg till en rad i tabellen Jakt med värden 125 och 122:
INSERT INTO Jakt (hundid, katt)
VALUES (125, 122);
Fungerar förutsatt att det finns en rad i tabellen Hund
med id = 125 och en rad i tabellen Katt med kid = 122.
19
MODB vt2007
nikos dimitrakas
UPDATE
Ändrar innehållet i ett eller flera attribut på noll eller
flera rader
Ändra färgen för alla vita katter till brunt!
UPDATE Katt
SET färg = ’brunt’
WHERE färg = ’vitt’;
20
MODB vt2007
nikos dimitrakas
DELETE
Används för att ta bort en eller flera rader i en tabell
Ta bort alla hundar som ägs av Johan
DELETE
FROM Hund
WHERE ägare = ’Johan’;
Ta bort alla gråa katter
DELETE
FROM Katt
WHERE färg = ’grått’;
Vad händer med främmande nycklarna?
21
MODB vt2007
nikos dimitrakas
Katt
Jakt
kid färg
hundid katt
122 grått
130 122
111 svart
130 106
106 vitt
130 42
33 svart
Hund 135 122
43 svart
id namn ras ägare 431 33
45 grått
130 Barky Terrier Johan 431 45
42 vitt
135 Woolfy Coley Johan 431 42
55
431 Beasty Tax Maria 431 111
99 brunt
1233 Scrapy Lisa 1555 111
100
1555 Lucky Tax Nils 1555 42
2334 Ruddy Foxhound Lisa 2343 122
2343 Grumpy Golden Retriever Johan 2345 42
2345 Slicky Peter 3342 42
3342 Jumpy Lisa 3342 111
22
MODB vt2007
nikos dimitrakas
SELECT … FROM … WHERE …
Väljer ut data från en eller flera tabeller när alla villkor blir
uppfyllda.
Ta fram namn och ras för alla hundar som ägs av Johan!
SELECT namn, ras
FROM Hund
WHERE ägare = ’Johan’;
Går igenom tabellen rad för rad och tar med i resultatet
de rader som uppfyller villkoret.
23
MODB vt2007
nikos dimitrakas
SELECT vs Relationsalgebra
Ta fram namn och ras för alla hundar som
ägs av Johan!
Π namn, ras σ ägare = ’Johan’ Hund
projektion SELECT namn, ras
FROM Hund
selektion WHERE ägare = ’Johan’;
24
MODB vt2007
nikos dimitrakas
WHERE-klausulen
Kan innehålla:
• jämförelseoperatorerna =, , >, >=, ’Johan’
AND namn LIKE ’B%’
25
MODB vt2007
nikos dimitrakas
ORDER BY klausulen
Används för att sortera resultatet
i stigande ordning: ASC (default)
i fallande ordning: DESC
SELECT ras, namn
FROM Hund
WHERE ägare ’Johan’
ORDER BY ras DESC, namn ASC
26
MODB vt2007
nikos dimitrakas
SELECT DISTINCT
Används för att ta bort dubbletter från
resultatet
Ta fram alla hundraser i stigande ordning!
SELECT DISTINCT ras
FROM Hund
ORDER BY ras
27
MODB vt2007
nikos dimitrakas
NULL
Hur skall NULL tolkas?
Visa alla vita katter!
SELECT kid
FROM Katt
WHERE färg = ’vitt’;
Visa alla icke-vita katter!
SELECT kid
FROM Katt
WHERE färg ’vitt’;
28
MODB vt2007
nikos dimitrakas
SELECT från flera tabeller
Ta fram namn för de personer som äger en
hund som jagar en katt!
Π ägare σ hundid = id (Hund x Jakt)
Π ägare (Hund θ hundid = id Jakt)
SELECT ägare
projektion
FROM Hund, Jakt
join-villkor WHERE hundid=id
selektion SELECT Hund.ägare
FROM Hund, Jakt
WHERE Jakt.hundid = Hund.id
29
MODB vt2007
nikos dimitrakas
Alias
Använd ett alias istället för tabellnamnet för att
• undvika skriva långa tabellnamn om och om igen
• skilja mellan olika instanser av samma tabell
Vilka två hundar har samma ägare?
SELECT h1.namn, h2.namn
FROM Hund AS h1, Hund h2
WHERE h1.ägare = h2.ägare
AND h1.id > h2.id
30
MODB vt2007
nikos dimitrakas
Ett till exempel
Vilka två hundar jagar samma katt?
SELECT DISTINCT h1.namn, h2.namn
FROM Hund h1, Hund h2, Jakt j1, Jakt j2
WHERE h1.id = j1.hundid
AND h2.id = j2.hundid
AND j1.katt = j2.katt
AND h1.id > h2.id
31
MODB vt2007
nikos dimitrakas
IN och EXISTS
Används för att jobba med mängder (oftast
genererade med nästlade SELECT-satser)
Vilka hundar jagar en katt med id 122?
SELECT namn
FROM Hund
WHERE id IN (SELECT hundid
FROM Jakt
WHERE katt=122)
32
MODB vt2007
nikos dimitrakas
IN och EXISTS
Vilka hundar jagar en katt med id 122?
SELECT namn
FROM Hund
WHERE EXISTS (SELECT hundid
FROM Jakt
WHERE katt=122
AND hundid=id)
33
MODB vt2007
nikos dimitrakas
Ett till exempel
Vilka två hundar jagar samma katt?
SELECT h1.namn, h2.namn
FROM Hund h1, Hund h2
WHERE h1.id > h2.id
AND h1.id IN (SELECT hundid
FROM Jakt
WHERE katt IN (SELECT katt
FROM Jakt
WHERE hundid=h2.id))
34
MODB vt2007
nikos dimitrakas
Division i SQL
Vilka hundar jagar alla vita katter? (Division)
VitKatt(katt) Π kid σ färg = ’vitt’ Katt
Π namn (Hund θ id = hundid (Jakt ÷ VitKatt))
SELECT namn
FROM Hund
WHERE NOT EXISTS (SELECT *
FROM katt
WHERE färg = ’vitt’
AND KID NOT IN (SELECT katt
FROM Jakt
WHERE hundid=id))
35
MODB vt2007
nikos dimitrakas
Division i SQL
Vad har man i de tre nivåerna?
Nivå 1 – Det som vi vill ha i resultatet. Inget annat.
Nivå 2 – Det som utgör alla. Har ingen direkt
koppling till nivå 1.
Nivå 3 – Det som kombinerar det vi letar efter (som
finns i nivå 1) och det som utgör alla (som finns i
nivå 2).
36
MODB vt2007
nikos dimitrakas
Andra mängdoperationer
UNION
EXCEPT (MINUS)
INTERSECT(ION)
Kom ihåg Union-kompatibilitet!
37
MODB vt2007
nikos dimitrakas
UNION exempel
Vilka hundar jagar antingen katt 122 eller katt 42?
SELECT namn
FROM Hund, Jakt
WHERE id = hundid
AND katt = 122
UNION
SELECT namn
FROM Hund, Jakt
WHERE id = hundid
AND katt = 42
38
MODB vt2007
nikos dimitrakas
INTERSECT exempel
Vilka hundar jagar både katt 122 och katt 42?
SELECT namn
FROM Hund, Jakt
WHERE id = hundid
AND katt = 122
INTERSECT
SELECT namn
FROM Hund, Jakt
WHERE id = hundid
AND katt = 42
39
MODB vt2007
nikos dimitrakas
EXCEPT exempel
Vilka hundar jagar inga katter?
SELECT namn
FROM Hund
EXCEPT
SELECT namn
FROM Hund, Jakt
WHERE id = hundid
SELECT namn
FROM Hund
WHERE id NOT IN (SELECT hundid
FROM Jakt)
40
MODB vt2007
nikos dimitrakas
Aggregatfunktioner
COUNT(*) räknar antalet rader i en tabell
COUNT(att) räknar antalet värden i en kolumn
MAX(att) ger det största värdet i en kolumn
MIN(att) ger det minsta värdet i en kolumn
Bara aritmetiska kolumner:
SUM(att) summerar värdena i en kolumn
AVG(att) tar genomsnittet av värdena i en
kolumn
VARIANCE(att) räknar ”variationen” av värdena i en
kolumn
STDEV(att) räknar standard avvikelse av
värdena i en kolumn
41
MODB vt2007
nikos dimitrakas
Exempel på funktioner
Hur många jakter finns det i databasen?
SELECT COUNT(*)
FROM Jakt
Hur många katter jagas?
SELECT COUNT(DISTINCT katt)
FROM Jakt
42
MODB vt2007
nikos dimitrakas
Nya kolumnnamn
Skapar man kolumner med aggregatfunktioner, har de
ursprungligen inget namn.
Hur många katter jagas av hund 130 eller hund 431?
SELECT COUNT(DISTINCT katt) AS kattantal
FROM Jakt
WHERE hundid = 130 OR hundid = 431
Notera att COUNT(DISTINCT) inte stöds i Access.
43
MODB vt2007
nikos dimitrakas
GROUP BY
Grupperar rader på angivna kolumner.
Rader grupperas i mindre grupper så att inom varje grupp
alla rader har samma värden på de angivna kolumner.
Kombineras oftast med aggregatfunktionerna.
Räkna antal katter av varje färg!
SELECT färg, COUNT(kid) AS antal
FROM Katt
GROUP BY färg;
Alla icke aggregerade kolumner som förekommer i
SELECT klausulen måste finnas med i GROUP BY
klausulen! 44
MODB vt2007
nikos dimitrakas
GROUP BY Exempel
Hur många katter jagas av varje hund?
SELECT namn, COUNT(katt) AS antal
FROM Jakt, Hund
WHERE hundid=id
GROUP BY hundid, namn
ORDER BY antal DESC, namn ASC
45
MODB vt2007
nikos dimitrakas
HAVING
Används för att sätta villkor på resultatet av
aggregatfunktioner (villkor på grupper)
Vilka katter jagas av minst 3 hundar?
SELECT katt
FROM Jakt
GROUP BY katt
HAVING COUNT(hundid) > 2
46
MODB vt2007
nikos dimitrakas
HAVING Exempel
Hur många katter av varje färg jagar varje hund? Visa
bara hundar som jagar minst 3 katter!
SELECT namn, färg, COUNT(kid) as antal
FROM Jakt, Hund, Katt
WHERE hundid=id
AND katt=kid
AND hundid IN (SELECT hundid
FROM Jakt
GROUP BY hundid
HAVING COUNT(katt)>2)
GROUP BY namn, färg
47
MODB vt2007
nikos dimitrakas
Nästlade SELECT-satser
Det är också möjligt att nästla satser i FROM-klausulen
Ta fram alla personer som äger endast 1 hund! (utan
att använda HAVING, EXISTS eller IN)
SELECT ägare
FROM (SELECT ägare, COUNT(id) AS antal
FROM Hund
GROUP BY ägare) AS nytabell
WHERE antal = 1
48
MODB vt2007
nikos dimitrakas
Utan COUNT(DISTINCT)
COUNT(DISTINCT) utan COUNT(DISTINCT):
Hur många katter jagas av hund 130 eller hund 431?
SELECT COUNT(katt) AS kattantal
FROM (SELECT DISTINCT katt FROM Jakt
WHERE hundid = 130 OR hundid = 431)
49
MODB vt2007
nikos dimitrakas
Ett till exempel på nästlade satser
Ta fram den eller de personer som äger de flesta hundar!
SELECT ägare
FROM Hund
GROUP BY ägare
HAVING COUNT(id) =
(SELECT MAX(antal)
FROM (SELECT ägare, COUNT(id) AS antal
FROM Hund
GROUP BY ägare) AS nytabell)
50
MODB vt2007
nikos dimitrakas
Mera nästlade SELECT satser
Det är också möjligt att nästla satser i SELECT-klausulen
under förutsättningen att de endast producerar en rad
och en kolumn.
Ta fram alla hundar och antalet katter de jagar! Ta med
samtliga hundar!
SELECT namn, (SELECT COUNT(*)
FROM Jakt
WHERE hundid = id) AS antal
FROM Hund
Här blir hela den nästlade satsen ett värde i varje rad
i resultatet.
51
MODB vt2007
nikos dimitrakas
Aritmetiska uttryck
+, -, *, / kan användas direkt i SELECT-satser
Om varje hund kostar 500 kronor per månad,
vad är då kostnaden för varje hundägare?
SELECT ägare, COUNT(id)*500 AS hundkostnad
FROM Hund
GROUP BY ägare
52
MODB vt2007
nikos dimitrakas
JOIN -operationer
• INNER JOIN
– Tar med alla kombinationer av rader från båda tabellerna då
villkoret stämmer
• LEFT OUTER JOIN
– Tar med alla rader från tabellen till vänster och de rader från
tabellen till höger som uppfyller villkoret
• RIGHT OUTER JOIN
– Tar med alla rader från tabellen till höger och de rader från tabellen
till vänster som uppfyller villkoret
• FULL OUTER JOIN
– Tar med alla rader från båda tabeller
• CROSS JOIN
• UNION JOIN
53
MODB vt2007
nikos dimitrakas
INNER JOIN
Visa alla hundar som jagar katter!
SELECT namn
FROM Hund INNER JOIN Jakt
ON (id=hundid)
Om tabellerna skall joinas på kolumner
med samma namn kan man skriva istället:
SELECT namn
FROM Hund INNER JOIN Jakt
USING kolumnnamn
54
MODB vt2007
nikos dimitrakas
OUTER JOIN
Visa alla katter och vilka hundar som jagar dem!
SELECT kid, hundid
FROM Katt LEFT OUTER JOIN Jakt
ON (kid=katt)
Visa alla katter och alla hundar och alla
jaktförhållanden!
SELECT kid, hundid
FROM Katt LEFT OUTER JOIN Jakt
ON (kid=katt) FULL OUTER JOIN Hund
ON (hundid=id)
55
MODB vt2007
nikos dimitrakas
SELECT-satsens format
SELECT [DISTINCT]
FROM
[WHERE ]
[GROUP BY
[HAVING ]]
[ORDER BY ];
56
MODB vt2007
nikos dimitrakas
VIEWS
En vy (view) är en virtuell tabell som finns för
användarna, men skapas med data från andra
tabeller
Skapa en vy med alla hundägare och antalet hundar
de äger!
CREATE VIEW hundägande
AS SELECT ägare, COUNT(id) AS antal
FROM Hund
GROUP BY ägare
57
MODB vt2007
nikos dimitrakas
Fördelar med VIEWS
•samma data kan betraktas av olika användare
på olika sätt (på samma gång)
•användaren kan få en förenklad bild av
databasen - mindre komplex DML
•flexibel behörighetskontroll
•optimering (materialized views)
•återanvändning
58
MODB vt2007
nikos dimitrakas
INDEX
Ett index skapas för att snabbt kunna komma åt
data med SELECT satser, genom att :
• begränsa kolumner
• optimera inför JOIN
• optimera inför selektion
• optimera inför sortering/gruppering
Skapa ett index på kattfärgerna!
CREATE INDEX kattfärg
ON Katt(färg, kid)
Skapa ett index på hundägarna och ras!
CREATE INDEX hundägare
ON Hund(ägare, ras)
59
MODB vt2007
nikos dimitrakas
VIEWS & INDEX
Tänk på att dessa tar plats och kräver tid för att
hålla uppdaterade!
Skapa endast de som verkligen behövs!
60
MODB vt2007
nikos dimitrakas
SQL Validator
Ni kan kontrollera om era SQL-satser följer
standarden här:
http://developer.mimer.com/validator/
61