Idag
I
SQLs delar
Den del av SQL som vi gått igenom kallas SQL DML (SQL Data Manipulation
Language). En annan viktig del är SQL DDL (SQL Data Definition Language)
som används för att skapa databaser och databastabeller, skapa regler för
vem som får göra vad i databaserna och skapa index för att snabba upp
sökningar.
Hur skapar vi och underhåller en databas?
I
I
I
Hur skapar man tabeller?
Hur får man in data i tabellerna?
Hur ändrar man innehållet i en tabell?
I
Index? Vad är det och varför behövs de?
I
Behöver jag bry mig om index?
DD1370 (Föreläsning 4)
Databasteknik och informationssystem 7,5 hp
Senaste versionen av SQL, SQL 3, är mer komplicerad med möjligheter att
starta program då vissa händelser inträffar (s.k. triggers) och med omfattande
programmeringsmöjligheter
Hösten 2008
1 / 20
Skapa en databas
DD1370 (Föreläsning 4)
Databasteknik och informationssystem 7,5 hp
Hösten 2008
2 / 20
Att utplåna en databas
I många böcker och om man söker på internet används kommandot
CREATE DATABASE databasnamn;
men det är inte standardiserat. Olika databashanterare har olika metoder för att
skapa en databas även om fler och fler tillhandahåller ett SQL-kommando.
I
Är inte heller standardiserat. Ofta kan man tillhandahålla
SQL-kommandot:
DROP DATABASE serafimsdb;
I
Eftersom det inte finns en standard måste man läsa den aktuella
databashanterarens manualer noga. Det förekommer stora skillnader.
I
men lika ofta får man slå i manualer för att hitta rätt kommando.
I
I
I postgresql, som vi använder mycket på KTH, finns dels ett SQL-kommando och
dels ett kommando man kan skriva i ett terminalfönster. Har man utfört
kommandot psql så att man hamnar i postgresql:s kommandotolk så går det
utmärkt att skriva t.ex. CREATE DATABASE serafimsdb;
I
men i ett terminalfönster använder man kommandot createdb serafimsdb
I
Liknande möjligheter finns i alla DBMS men kommandona kan se annorlunda ut
Orsaken till att varken CREATE DATABASE eller DROP DATABASE finns
som standard är att de flesta organisationer som använder databaser har
speciella anställda, s.k. databasadministratörer (DBA) som sköter
företagets bestånd av databaser och att det är bra om ingen får kasta
bort (eller skapa) en databas utan DBA:s medverkan.
På OS-nivå skulle jag kunnat använda postgresql kommandot dropdb
serafimsdb
I
Man kan vara säker på att olika leverantörer har sina egna (ibland väldigt egna)
idéer så det är bra att åtminstone läsa igenom aktuellt DBMS manual.
I
DD1370 (Föreläsning 4)
Databasteknik och informationssystem 7,5 hp
Hösten 2008
I
3 / 20
DD1370 (Föreläsning 4)
Databasteknik och informationssystem 7,5 hp
Hösten 2008
4 / 20
Tabeller (1)
Tabeller . . . (2)
När man väl skapat sin databas är det dags att skapa tabellerna. Det finns
många sätt och kanske det allra mest eleganta är att använda ett grafiskt
gränssnitt i en webbläsare, för Postgresql finns phpPgAdmin som man kan
använda på nestor via URL:en
Den hade egenskapsmatrisen:
http://nestor.nada.kth.se/phpPgAdmin
men det är viktigt att kunna bygga databaser via SQL-kommandon också.
Vi ska titta på hur man gör för att realisera den varuhusdatabas som jag
gjorde en modell till:
Anställning
Anställd
Leverantör
DD1370 (Föreläsning 4)
I-termer
E-termer
Obj
Vara
varunr
typ
Avdelning
avd
våning
Anställd
namn
lön, chef
Leverantör
företag
adress
Försäljning
avd, varunr
volym
Anställd
namn, avd
Lager
företag, avd, varunr
Samb
volym
Vara
Databasteknik och informationssystem 7,5 hp
Hösten 2008
DD1370 (Föreläsning 4)
5 / 20
Tabeller . . . (3)
Databasteknik och informationssystem 7,5 hp
Hösten 2008
6 / 20
Tabeller . . . (4)
I
och vi fick fram en databasstruktur:
I
DD1370 (Föreläsning 4)
Namn
Avdelning
Försäljning
!
!!
!
er!!!
ag!
L!
aa
!!
aa
!
!
aa
!
aa
!!
Typ
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)
Databasteknik och informationssystem 7,5 hp
I
Kommentar: Har man startat postgresql med kommandot psql kan man
använda kommandot \h för att få hjälp med SQL-kommandon
Eftersom jag vill markera alla främmande nycklar så måste jag mata in i
en viss ordning, annars klagar DBMS.
Ordningen bestäms av de främmande nycklarna. Jag börjar med tabeller
som inte har några sådana.
CREATE TABLE vara (
varunr int2 NOT NULL,
typ character varying(20),
PRIMARY KEY (varunr)
);
Hösten 2008
7 / 20
DD1370 (Föreläsning 4)
Databasteknik och informationssystem 7,5 hp
Hösten 2008
8 / 20
Tabeller . . . (5)
Tabeller . . . (6)
CREATE TABLE avdelning (
avd character varying(20) NOT NULL,
våning int2,
PRIMARY KEY (avd)
);
CREATE TABLE anställd (
namn character varying(30) NOT NULL,
lön int4,
chef character varying(30),
avd character varying(20),
PRIMARY KEY (namn),
FOREIGN KEY (chef) REFERENCES anställd,
FOREIGN KEY (avd) REFERENCES avdelning
);
CREATE TABLE leverantör (
företag character varying(30) NOT NULL,
adress character varying(50),
PRIMARY KEY (företag)
);
DD1370 (Föreläsning 4)
Databasteknik och informationssystem 7,5 hp
Hösten 2008
9 / 20
DD1370 (Föreläsning 4)
Databasteknik och informationssystem 7,5 hp
Tabeller . . . (7)
Tabeller . . . (8)
CREATE TABLE försäljning (
avd character varying(20) NOT NULL,
varunr int2 NOT NULL,
volym int4,
PRIMARY KEY (avd, varunr),
FOREIGN KEY (avd) REFERENCES avdelning,
FOREIGN KEY (varunr) REFERENCES vara
);
CREATE TABLE lager (
avd character varying(20) NOT NULL,
varunr int2 NOT NULL,
företag character varying(30) NOT NULL,
volym int4,
PRIMARY KEY (avd, varunr, företag),
FOREIGN KEY (avd) REFERENCES avdelning,
FOREIGN KEY (varunr) REFERENCES vara,
FOREIGN KEY (företag) REFERENCES leverantör
);
DD1370 (Föreläsning 4)
Databasteknik och informationssystem 7,5 hp
Hösten 2008
11 / 20
DD1370 (Föreläsning 4)
Databasteknik och informationssystem 7,5 hp
Hösten 2008
10 / 20
Hösten 2008
12 / 20
Ändra i tabelldefinitioner
Mata in nya data i en tabell
Kommandot INSERT INTO används för att lägga till data till en tabell.
I
Man kan ändra en tabelldefinition när tabellen har skapats
I
ALTER TABLE tillåter att man lägger till eller tar bort kolumner
I
Man kan ta bort en kolumn så länge som kolumnen inte innehåller data
men finns det ett enda värde i en kolumn så kan den inte tas bort
I
Många andra saker kan ställas in samtidigt. Se den långa utskriften vid
kommandot \h ALTER TABLE när man är i postgresqls
kommandogränssnitt
Man kan välja att stoppa in allt som går på en rad. Då slipper man räkna
upp kolumnerna. Ex.:
INSERT INTO anställd
VALUES (’Karlsson K’, 23540, ’Svensson S’, ’sport’);
om man matar in värdena i ”rätt” ordning
I
Annars måste man räkna upp kolumnerna. Ex.:
INSERT INTO anställd (lön, namn, avd)
VALUES (23540, ’Karlsson K’, ’sport’);
varvid utelämnade kolumner fylls på med null i den inmatade raden.
Skulle man ha angivit ett ”defaultvärde” används det i ställer för null
DD1370 (Föreläsning 4)
Databasteknik och informationssystem 7,5 hp
Hösten 2008
DD1370 (Föreläsning 4)
13 / 20
Ta bort rader ur en tabell
Databasteknik och informationssystem 7,5 hp
Hösten 2008
14 / 20
Uppdatera rader
Kommandot DELETE FROM används för att ta bort data från en tabell. Ex.:
I
DELETE FROM anställd WHERE namn = ’Karlsson K’;
I
VARNING!
Kommandot UPDATE används för att ändra data i en tabell. Ex.:
DELETE FROM anställd;
tar bort alla rader från anställd. Inga frågor i stil med ”är du säker?” eller
I
något annat. Alla rader försvinner utom om det finns
främmande-nyckel-beroenden. Då vägrar DBMS.
Lägger man till
I
UPDATE anställd SET lön = 32400 WHERE namn = ’Karlsson K’;
I
UPDATE anställd SET (lön=32400, chef=’Näsström C’) WHERE
namn = ’Karlsson K’;
I
UPDATE anställd SET lön = 2*lön WHERE chef IS NOT NULL;
DELETE FROM anställd CASCADE;
så tas alla rader bort från anställd och också alla rader i andra tabeller
som är beroende av någon av raderna som tagits bort ur anställd
DD1370 (Föreläsning 4)
Databasteknik och informationssystem 7,5 hp
Hösten 2008
15 / 20
DD1370 (Föreläsning 4)
Databasteknik och informationssystem 7,5 hp
Hösten 2008
16 / 20
Ladda data från fil / Spara data på fil
I
I
Index – vad är det?
Man kan hämta kommandon direkt från en fil, t.ex. skapas
varuhusdatabasen genom kommandot \i vhdump
där filen vhdump innehåller alla kommandon för att både bygga tabellerna
och för att fylla dem med data
Ett index är en datastruktur som används för att snabba upp sökningar i
en tabell
Kopiera data till en enskild tabell går också bra på det viset men också
med kommandot COPY. Ex.:
Antag att vi har en tabell med 1000000 rader där raderna är så att vi kan
packa 5 rader/block. Resten av blocket består av en ”header” och lite
oanvänt utrymme
COPY avdelning (avd, våning)
FROM ’/home/serafim/avd.sql’ WITH DELIMITER ’|’
I
Då kommer filen som innehåller tabellen att vara knappt 820 MB. Inte
många primärminnen klarar av att hålla en sådan fil i minnet och ändå ha
utrymme för annat.
Kopiera data till en fil går också bra med kommandot COPY. Ex.:
COPY avdelning TO ’/home/serafim/avd.sql’
I
”dumpa” hela databasen med OS-kommandot pg_dump databasnamn
I
”dumpa” alla databaser med OS-kommandot pg_dumpall
DD1370 (Föreläsning 4)
Databasteknik och informationssystem 7,5 hp
Hösten 2008
Antag att vårt OS hanterar block som är 4096 byte stora. Det betyder att
4096 byte läses i varje enskild läsoperation. Varken mer eller mindre
Antag också att en nyckel är 30 byte och en pekare 8 byte. Då får vi rum
med 100 par <nyckel,pekare> i varje block. En sån fil blir 10000 block stor
(41MB)
17 / 20
Index . . .
DD1370 (Föreläsning 4)
Databasteknik och informationssystem 7,5 hp
Hösten 2008
18 / 20
Index . . .
Det får rum i minnet och om vi använder binärsökning i filen + linjär
sökning i blocken så tar det d2 log 10000e = 14 läsningar i block för att
hitta det rätta blocket
Även om det inte får rum i minnet kan vi behålla några block i minnet hela
tiden. Väljer vi de på avståndet 1/2, 1/4, 3/4, 1/8, 3/8, 5/8, 7/8 . . . av filens
längd, kommer vi att kunna hitta vilken post som helst av de 1000000
med väsentligt färre läsningar från sekundärminnet än 14
Så trots den stora mängden kopior av nycklar (= redundans) så snabbar
det upp systemet avsevärt
DBMS genererar alltid (om man inte stänger av den faciliteten) ett s.k.
”B-träds-index” för alla tabeller som har en primärnyckel angiven
Ibland kan det finnas anledning att göra fler index men oftast inte. Man
kan för det mesta helt enkelt låta DBMS välja när index ska skapas och
vilken sorts index som ska användas
Det finns en mängd olika typer av index, alltid med stora tidsvinster som
bonus
DD1370 (Föreläsning 4)
Databasteknik och informationssystem 7,5 hp
Hösten 2008
19 / 20
DD1370 (Föreläsning 4)
Databasteknik och informationssystem 7,5 hp
Hösten 2008
20 / 20
