SQL

1. SQL nikos dimitrakas nikos@dsv.su.se 08-162099 rum 6626 Connolly/Begg (3rd & 4th edition) kapitel 5, 6 (och lite överallt)

2. 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. SQL Structured Query Language

3. databeskrivning, SQL-DDL (Data Definition Language) databearbetning, SQL-DML (Data Manipulation Language) • behörighet, SQL-DCL (Data Control Language) Uppdelning av SQL SQL kan uttrycka allt som går att uttrycka i relationsalgebran - språket är "relationally complete"

4. CREATE TABLE, skapa tabell ALTER TABLE, ändra tabell DROP TABLE, ta bort tabell CREATE INDEX, skapa index DROP INDEX, ta bort index CREATE VIEW, skapa vy DROP VIEW, ta bort vy SQL - DDL CREATE SCHEMA CREATE DATABASE (även CATALOG) CREATE TRIGGER DROP TRIGGER CREATE PROCEDURE DROP PROCEDURE

5. INSERT, lägg till UPDATE, ändra DELETE, ta bort COMMIT, spara ROLLBACK, återställ SELECT, utsökning SQL-DML

6. CREATE USER, skapa användare CREATE SCHEMA DROP USER, ta bort användare DROP SCHEMA GRANT, ge behörighet REVOKE, ta bort behörighet SQL-DCL

7. 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 Exempel DDL, DML

8. Konceptuell modell  Logisk modell  Relationsdatabas

9. CREATE TABLE Hund ( id NUMBER PRIMARY KEY, namn STRING NOT NULL UNIQUE, ras STRING, ägare STRING NOT NULL) Konceptuell modell  Logisk modell  Relationsdatabas DDL

10. CREATE TABLE Katt ( kid NUMBER PRIMARY KEY, färg STRING) Konceptuell modell  Logisk modell  Relationsdatabas DDL

11. CREATE TABLE Jakt ( hundid NUMBER, katt NUMBER, PRIMARY KEY (hundid, katt), FOREIGN KEY (hundid) REFERENCES Hund (id) ON DELETE CASCADE ON UPDATE CASCADE) Konceptuell modell  Logisk modell  Relationsdatabas DDL

12. ALTER TABLE Jakt ADD FOREIGN KEY (katt) REFERENCES Katt ON DELETE RESTRICT ON UPDATE CASCADE Konceptuell modell  Logisk modell  Relationsdatabas DDL

13. Tar bort en tabell Ta bort tabellen Bil! DROP TABLE DROP TABLE Bil Ta bort tabellen Bil och alla referenser! Tar bort tabellen bil samt andra databasobjekt som beror på tabellen bil. DROP TABLE Bil CASCADE DDL

14. Datatyper i SQL INTEGER SMALLINT NUMBER DECIMAL(p[,q]) FLOAT BOOLEAN CHAR(n) VARCHAR(n) STRING(n) TEXT DATE TIME TIMESTAMP MONEY CLOB (Character Large OBject) BLOB (Binary Large OBject) XML

15. 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 INSERT DML

16. Lägg till en rad i tabellen Hund med värden 125, ”Woolfy”, ”Coley”, ”Johan” DML INSERT med specificerade värden INSERT INTO HundVALUES (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 HundVALUES (3342, ’Jumpy’, NULL, ’Lisa’); INSERT INTO Hund (namn, id, ägare) VALUES (’Jumpy’, 3342, ’Lisa’);

17. Lägg till flera rader med en gång (fungerar inte med alla databashanterare, men ingår i standarden): INSERT med specificerade värden INSERT INTO Hund VALUES (1233, ’Scrapy’, NULL, ’Lisa’), (1555, ’Lucky’, ’Tax’, ’Nils’), (2334, ’Ruddy’, ’Foxhound’, ’Lisa’); DML

18. INSERT INTO temp(hund, antaljakter) SELECT-sats INSERT via SELECT DML 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.

19. 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: Fungerar förutsatt att det finns en rad i tabellen Hund med id = 125 och en rad i tabellen Katt med kid = 122. INSERT och främmande nycklar INSERT INTO Jakt (hundid, katt) VALUES (125, 122); DML

20. Ändrar innehållet i ett eller flera attribut på noll eller flera rader Ändra färgen för alla vita katter till brunt! UPDATE DML UPDATE Katt SET färg = ’brunt’ WHERE färg = ’vitt’;

21. DELETE FROM Katt WHERE färg = ’grått’; DELETE DML 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 Vad händer med främmande nycklarna?

22. Katt Jakt Exempeldata Hund

23. SELECT namn, ras FROM Hund WHERE ägare = ’Johan’; 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! Går igenom tabellen rad för rad och tar med i resultatet de rader som uppfyller villkoret. DML

24. SELECT vs Relationsalgebra Ta fram namn och ras för alla hundar som ägs av Johan! Π namn, rasσ ägare = ’Johan’Hund SELECT namn, ras FROM Hund WHERE ägare = ’Johan’; projektion selektion

25. Kan innehålla: jämförelseoperatorerna =, <>, >, >=, <, <= logiska operatorerna t ex AND, OR och NOT parenteser för att styra utvärderingen BETWEEN för att testa intervall LIKE för att matcha mönster % _ (eller * ?) IN och EXISTS för att hantera mängder WHERE-klausulen SELECT *FROM HundWHERE (ras = ’Terrier’ OR ras = ’Coley’) AND ägare <> ’Johan’AND namn LIKE ’B%’

26. Används för att sortera resultatet i stigande ordning: ASC (default) i fallande ordning: DESC ORDER BY klausulen SELECT ras, namnFROM HundWHERE ägare <> ’Johan’ORDER BY ras DESC, namn ASC

27. Används för att ta bort dubbletter från resultatet SELECT DISTINCT Ta fram alla hundraser i stigande ordning! SELECT DISTINCT rasFROM HundORDER BY ras

28. Hur skall NULL tolkas? Visa alla vita katter! NULL SELECT kid FROM Katt WHERE färg = ’vitt’; Visa alla icke-vita katter! SELECT kid FROM Katt WHERE färg <> ’vitt’;

29. SELECT ägare FROM Hund, Jakt WHERE hundid=id 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 = idJakt) projektion join-villkor selektion SELECT Hund.ägare FROM Hund, Jakt WHERE Jakt.hundid = Hund.id

30. SELECT h1.namn, h2.namn FROM Hund AS h1, Hund h2 WHERE h1.ägare = h2.ägare AND h1.id > h2.id 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?

31. 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 Ett till exempel Vilka två hundar jagar samma katt?

32. SELECT namn FROM Hund WHERE id IN (SELECT hundid FROM Jakt WHERE katt=122) 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?

33. SELECT namn FROM Hund WHERE EXISTS (SELECT hundid FROM Jakt WHERE katt=122 AND hundid=id) IN och EXISTS Vilka hundar jagar en katt med id 122?

34. 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)) Ett till exempel Vilka två hundar jagar samma katt?

35. 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)) Division i SQL Vilka hundar jagar alla vita katter? (Division) VitKatt(katt) Π kidσ färg = ’vitt’ Katt Π namn (Hund θ id = hundid (Jakt ÷ VitKatt))

36. 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).

37. Andra mängdoperationer UNION EXCEPT (MINUS) INTERSECT(ION) Kom ihåg Union-kompatibilitet!

38. SELECT namn FROM Hund, JaktWHERE id = hundidAND katt = 122 UNION SELECT namn FROM Hund, JaktWHERE id = hundidAND katt = 42 UNION exempel Vilka hundar jagar antingen katt 122 eller katt 42?

39. SELECT namn FROM Hund, JaktWHERE id = hundidAND katt = 122 INTERSECT SELECT namn FROM Hund, JaktWHERE id = hundidAND katt = 42 INTERSECT exempel Vilka hundar jagar både katt 122 och katt 42?

40. SELECT namn FROM Hund EXCEPT SELECT namn FROM Hund, JaktWHERE id = hundid EXCEPT exempel Vilka hundar jagar inga katter? SELECT namn FROM Hund WHERE id NOT IN (SELECT hundid FROM Jakt)

41. 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 Aggregatfunktioner

42. SELECT COUNT(*) FROM Jakt Exempel på funktioner Hur många jakter finns det i databasen? Hur många katter jagas? SELECT COUNT(DISTINCT katt) FROM Jakt

43. 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.

44. 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! GROUP BY 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!

45. SELECT namn, COUNT(katt) AS antal FROM Jakt, Hund WHERE hundid=id GROUP BY hundid, namn ORDER BY antal DESC, namn ASC GROUP BY Exempel Hur många katter jagas av varje hund?

46. Används för att sätta villkor på resultatet av aggregatfunktioner (villkor på grupper) Vilka katter jagas av minst 3 hundar? HAVING SELECT katt FROM Jakt GROUP BY katt HAVING COUNT(hundid) > 2

47. 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 HAVING Exempel Hur många katter av varje färg jagar varje hund? Visa bara hundar som jagar minst 3 katter!

48. 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

49. 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)

50. 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)