Idag. En enkel modell över ett varuhus. Strukturen motsvaras av tabeller. Motsvarande databasstruktur. Anställning. 1.

(1)

Idag

1. En enkel databas

2. Prata med databaser (frågepsråket SQL)

DD1370 (Föreläsning 2) Databasteknik och informationssystem 7,5 hp Hösten 2011 1 / 17

En enkel modell över ett varuhus

Anställd

Leverantör

Avdelning

Vara Anställning

Lager

Försäljning

!!!!!!!!!!!!!!!! aa

aa aa

aa

Motsvarande databasstruktur

Vara (varunr, typ) Avdelning (avd, våning)

Anställd (namn, lön, chef, avd) Leverantör (företag, adress)

Lager (företag, avd, varunr, volym) Försäljning (avd, varunr, volym)

Strukturen motsvaras av tabeller

Vara Försäljning Avdelning

varunr typ avd varunr volym avd våning

1 blomma blommor 1 201 bad 8

4 krukväxt blommor 4 87 barnkläder 5

9 blomma blommor 9 86 blommor 1

22 toalettpapper damkläder 254 55 bröd 1

27 hushållspapper matvaror 60 2345 damkläder 6

33 diskmedel sport 153 743 matvaror 1

60 läskedryck tekniskaart 22 1203 möbler 8

130 skiftnyckel tekniskaart 27 944 skor 4

134 skruvmejsel tekniskaart 33 321 sport 2

153 träningsoverall verktyg 130 456 tekniskaart 1

225 handväska möbler 336 95 leksaker 2

254 kjol verktyg 134 886 verktyg 2

336 stol papper 347 5643 möbler 8

347 penna väskor 336 87665 papper 7

358 bok leksaker 347 243 väskor 4

(2)

”Prata med databaser”

SQL – Structured query language

I Förr tvingades man skriva program för varje frågeställning

I Tidsödande och krävde specialister

I Därför utvecklades en mängd olika språk som skulle ha vissa egenskaper

I De skulle vara deklarativa (specificera resultatets egenskaper och låt datorn generera metoden för att hitta resultatet)

I De skulle vara optimerande så att även en idiotiskt formulerad fråga utfördes på rimlig tid

I De skulle vara ”intuitiva”, vilket kom att betyda ”så nära talad engelska som möjligt” eller ”grafiska” (programmera med exempel eller gester)

I SQL har så småningom blivit en ”de facto” standard. Några udda språk finns kvar med för en tynande tillvaro

I Till SQL har utvecklats möjligheter att bädda in SQL-sater i program skrivna i vanliga programspråk och ta hand om och hantera svaren från databasen

I SQL används även för att skapa databaser och databastabeller (och förstöra dem också)

SQL – Vad måste man kunna göra med ett sådant språk?

I Skapa en databas (inte standardiserat)

I Kasta bort en databas drop database <databasnamn>

I Skapa tabeller create table <tabellnamn> . . . I Ta bort tabeller drop table <tabellnamn>

I Lägga till data i tabeller insert . . . into <tabellnamn>

I Ta bort data ur tabeller delete from <tabellnamn> . . . I Uppdatera data i tabeller update <tabellnamn> . . . I Hämta data från tabeller Kommer . . .

Eftersom språket kan göra så många saker på så många sätt är det bäst att titta efter vid behov (men vi ska gå igenom några exempel) Omfattande hjälp finns i databashanteringsystemet

SQL – Hämta data

Vad krävs för att ”kunna göra allt”? Vi måste

I kunna dela upp tabeller,

I plocka enstaka rader

I eller kolumner

I eller båda (= plocka enstaka värden)

I sätta ihop resultaten till meningsfull information

I Kort sagt: Vi måste kunna plocka isär och sätta ihop efter eget behag

I Men då tillåts vi också göra bort oss (ibland ganska kapitalt)

I Extra ”krydda(??)”. Vi vet inte alltid om ett tomt svar är korrekt eller beror på felformulerad fråga

SQL – Grunden

Frågor till databasen ställs med

select [distinct] lista-1-med-kolumner from lista-med-tabeller

where lista-1-med-villkor

[group by lista-2-med-kolumner [having lista-2-med-villkor]]

[order by del-av-lista-1-med-kolumner]

Varje operation ger en tabell som resultat (tabellen är den enda grundläggande datatypen)

Vi ska se på en del exempel och kommentera dem

(3)

SQL – Plocka rader

I select * from Anställd;

skriv ut tabellen anställd

I select * from Anställd where lön > 9000;

skriv ut de anställda som tjänar mer än 9000

I select * from Anställd

where lön > 9000 and chef = ’Näsström C’;

where lön > 9000 or chef = ’Näsström C’;

where lön > 9000 and not chef = ’Näsström C’;

where lön > 9000 and chef <> ’Näsström C’;

SQL – Plocka kolumner

I select Företag from Leverantör;

I select Lön from Anställd;

I select Namn,Lön from Anställd

where lön > 9000 and chef <> ’Näsström C’;

plocka kolumner ur vissa rader (eller enstaka värden)

I Test på mängdtillhörighet

Vad tjänar chefen/cheferna på skoavdelningen?

select lön from Anställd where namn in

(select chef from Anställd where avd = ’skor’);

I Även kostanta mängder kan användas

select lön from Anställd where avd in

(select avd from Avdelning where våning in (4, 5))

I och godtyckligt djup på ”nästling”

Vad tjänar cheferna på fjärde våningen?

select lön from Anställd where namn in (select chef from Anställd where avd in

(select avd from Avdelning where våning = 4));

SQL – Relationsvariabler . . .

I create view tmp as select * from Anställd where chef = ’Näsström C’;

skapar ett SQL-uttryck som lagras undan för senare användning . . . ;

I create view resultat as select namn from tmp where lön > 9000;

som fungerar som vilken tabell som helst, rätt kolumnnamn ”ärvs”

I create view resultat(anst, månadslön) as select namn, lön from Anställd;

Men man kan namnge kolumnerna också . . . ger stora möjligheter att hämta vilka data som helst och bygga ihop i stort sett vilka tabeller som helst

SQL – Mängdoperationer

Man bygger språken på mängdmatematik (mängdalgebra och predikatlogik)

where lön > 9000 and chef = ’Näsström C’

union

select * from Anställd

where lön > 10000 and chef = ’Hovander T’;

I select * from Anställd where chef = ’Näsström C’

except

select * from Anställd where lön <= 9000;

I select * from Anställd where chef = ’Näsström C’

intersect

select * from Anställd where lön > 9000;

(4)

SQL – bygga ihop tabeller

Här har man tillgång till ’Join’ som slår ihop två rader och ’Naturlig join’ som slår ihop rader där värdena överensstämmer i i kolumner med samma namn i båda tabellerna

I select * from Anställd, Avdelning where våning = 2;

I select * from Anställd, Avdelning where Avdelning.våning = 2;

I select * from Anställd, Avdelning where Anställd.avd <> Avdelning.avd;

I select * from Anställd, Avdelning where Avdelning.avd <> Anställd.avd;

I select * from Anställd join Avdelning on Anställd.avd <> Avdelning.avd;

I select * from Anställd natural join Avdelning;

SQL – Aggregat

Man kan utföra vissa enkla beräkningar på innehållet i en tabell eller en tabellkolumn.

SUM(kol), AVG(kol), MAX(kol), MIN(kol), COUNT(distinct kol eller *)

I select SUM(lön) from Anställd;

Finn totala lönekostnaden för alla anställda, ignorera NULL-värden

I select SUM(lön) from Anställd where avd=’skor’;

Finn lönekostnaden för skoavdelningen, ignorera NULL-värden

I select SUM(lön), COUNT(*) from Anställd where avd=’skor’;

Finn lönekostnaden och antalet anställda på skoavdelningen, ignorera NULL-värden

I select avd, SUM(lön), COUNT(*) from Anställd group by avd;

Finn lönekostnad och antalet anställda för alla avdelningar, ignorera NULL-värden

I create view result(avd, avglön) as

select avd, AVG(lön) from Anställd group by avd;

select avd from result

where avglön = (select MAX(avglön) from result;

Vilken avdelning har högst medellön?

SQL – Alias (tupelvariabler)

Man kan använda variabler som är kapabla att ”hålla i” en tupel (tabellrad).

Bra om man behöver läsa i samma tabell två gånger i samma fråga eller man vill referera mellan nivåerna i en nästlad fråga.

I select distinct a.varunr from lager a, lager b where a.varunr=b.varunr and a.avd != b.avd ; Vilka varor (varunummer) finns på fler än ett lager?

I select * from Anställd U where chef = ’Näsström C’ and exists

(select * from Anställd where lön > 9000 and namn = U.namn and lön = U.lön and chef = U.chef and avd = U.avd)

ett annat sätt att uttrycka skärning

SQL – Lite udda men användbara saker

I Man kan jämföra ett värde med en mängd som innehåller endast ett värde

I select namn from Anställd L where lön >

(select max(lön) from Anställd where avd = ’fisk’);

Vilka tjänar mer än den som tjänar mest på fiskavdelningen?

I select namn from Anställd L where lön >

(select min(lön) from Anställd where avd = ’fisk’);

Vilka tjänar åtminstone inte sämre än den som tjänar sämst på fiskavdelningen?

I Man kan också jämföra med en mängd värden

I select namn from Anställd L where lön > all (select lön from Anställd where avd = ’fisk’);

Vilka tjänar mer än den som tjänar mest på fiskavdelningen? (igen)

I select namn from Anställd L where lön > any (select lön from Anställd where avd = ’fisk’);

Vilka tjänar mer än den som tjänar minst på fiskavdelningen? (igen)

(5)

SQL – Textmatchning

Like och Matches kan användas vid matchning av texter

select namn from Anställd L where namn like ”A%”;

select namn from Anställd L where namn matches ”A??”;

Like: % följd av tecken _ ett tecken

\ speciell mening med nästa tecken Matches: * följd av tecken

? ett tecken

[ ] ett tecken ut ett intervall

\ speciell mening med nästa tecken