Relationsdatabaser og SQL

1
Relationsdatabaser og SQL

• Del 3 af 4
• Data Manipulation Language (DML) i SQL
• Aalborg Universitet, d. 6. september 2006

2
Tegn-forklaring

• Denne skrifttype er lig kode
• STORE BOGSTAVER er lig Oracle kommandoer
• lttekstgt betyder at tekst inkl. større-end og
  mindre-end tegn skal erstattes med den faktiske
  tekst/værdi.
• tekst betyder at tekst er valgfri og kan
  udelades. De firkantede parenteser skal aldrig
  skrives i koden.
• tekst1tekst2 betyder at en af teksterne
  adskilt af de lodrette streger skal vælges. De
  krøllede parenteser skal aldrig skrives i koden.

3
Elementer i en tabel
Postnumre
Postnumre
4
SQL til tabeldata

• Data Manipulation Language (DML)
• INSERT
• UPDATE
• DELETE
• SELECT
• (MERGE)
• (TRUNCATE)
• Vedrører de faktiske data i tabeller

5
Insert

• INSERT INTO lttabelnavngt
• (ltkolonnergt) VALUES (ltværdiergt)
• INSERT INTO kunder VALUES (115,
  'Føtex', null, '13-08-2006')
• INSERT INTO kunder (id, kunde)
  VALUES (116, 'Netto')

Kunder
Bemærk, tomt felt
6
Insert data fra anden tabel

• INSERT INTO lttabelnavngt
• (ltkolonnergt)
• SELECT ltkolonnenavnegt FROM lttabelnavngt
• INSERT INTO kunder_kopi
• (id, kunde)
• SELECT (id, kunde) FROM kunder
• Bemærk at nøgleordet VALUES udelades ved
  indsætning af data fra en anden tabel

7
Regler for (indsættelse af) data

• Værdierne adskilles med komma ,
• Enkelt anførselstegn ' ' omkring værdier der
  indsættes (ikke krav ved NUMBER datatype)
• Der skal (naturligvis) tages hensyn til store og
  små bogstaver
• Ved manuel dataindsættelse kan kun én række
  oprettes for hver INSERT kommando
• NULL indtastes for et blankt felt.
• Der skal altid indtastes data i kolonner med NOT
  NULL betingelse

8
Regler for (indsættelser af) datoer

• Da datoer kan antage mange forskellige formater,
  er det nødvendigt at definere det anvendte
  datoformat.
• Direkte i INSERT kommandoen vha. TO_DATE
  funktionen
• INSERT INTO kunder VALUES (115, 'Føtex',
  null, TO_DATE('13-08-2006', 'DD-MM-YYYY'))
• Eller ved at definere et datoformat for hele
  sessionen.
• ALTER SESSION
• SET NLS_DATE_FORMAT 'DD-MM-YYYY'
• INSERT INTO kunder VALUES (115, 'Føtex',
  null, '13-08-2006')
• Funktionen SYSDATE indsætter dags dato i
  kolonnen.
• INSERT INTO kunder VALUES (115, 'Føtex',
  null, SYSDATE)

9
Sekvenser fortløbende numre

• Sekvenser anvendes hovedsageligt til at danne
  indholdet i syntetiske primærnøgler
• CREATE SEQUENCE ltsekvensnavngt INCREMENT BY
  ltantalgt START WITH ltnummergt
• DROP SEQUENCE ltsekvensnavngt
• SELECT FROM user_sequences
• CREATE SEQUENCE kunder_seq INCREMENT BY 1
  START WITH 1000
• INSERT INTO kunder (id, kunde) VALUES
  (kunder_seq.nextval, 'Føtex')

10
Opdatering af data

• UPDATE lttabelnavngt SET ltkolonnenavngt
  ltværdigt WHERE ltbetingelsegt
• UPDATE kunder SET kunde 'Bilka',
• postnr 9000 WHERE kunde 'A-Z'
• Hvis WHERE-sektionen udelades opdateres alle
  rækker i tabellen!

11
Sletning af data

• DELETE FROM lttabelnavngt WHERE
  ltbetingelsegt
• DELETE FROM kunder WHERE kunde 'Føtex'
• TRUNCATE TABLE kunder
• TRUNCATE sletter uden mulighed for at foretage
  rollback/fortryde, men er til gengæld langt
  hurtigere end DELETE ved større datasæt.

12
Gemme og fortryde

• Når der arbejdes med data i tabeller gemmes
  ændringer ikke automatisk.
• COMMIT
• Gemmer ændringer
• SAVEPOINT ltnavn på savepointgt
• Opretter midlertidigt punkt der kan fortrydes
  tilbage til
• ROLLBACK TO ltnavn på savepointgt
• Fortryd ændringer siden sidste commit eller
  bestemt savepoint

13
Udtrække data

• SELECT - Hvilke data vil jeg have?
• FROM - Hvor skal data komme fra?
• WHERE - Hvilke betingelser skal være
  gældende?
• GROUP BY - Hvordan skal data grupperes?
• HAVING - Hvilke betingelser skal være
  gældende for grupper?
• ORDER BY - Hvordan skal data sorteres?

14
Komplet udtræk
Postnumre

• SELECT
• FROM
• postnumre
• SELECT
• postnr, bynavn
• FROM
• postnumre

Resultat
15
Udtræk på delmængde af kolonner - projection
Postnumre

• SELECT
• bynavn
• FROM
• postnumre

Resultat
16
Sortering af output stigende (asc) eller
faldende (desc)
Postnumre

• SELECT
• FROM
• postnumre
• ORDER BY
• postnr ASC

Resultat
17
Udtræk på delmængde af rækker - restrict
Postnumre

• SELECT
• FROM
• postnumre
• WHERE
• postnr gt 8000

Resultat
18
Betingelser i WHERE

• Grundlæggende typer af betingelser
• Sammenligning
• Interval
• Mængde medlemskab
• Mønster-match
• Test for NULL
• Joins, der fortæller hvordan data fra flere
  tabeller hænger sammen kan også foretages i
  WHERE-sektionen, men generelt anbefales det at
  foretage joins direkte i FROM-sektionen.

19
Sammenligning

• Følgende type sammenligninger kan foretages
  mellem kolonner og/eller værdier
• Lig med
• lt Mindre end
• gt Større end
• lt Mindre end eller lig med
• gt Større end eller lig med
• ! Forskellig fra
• ltgt Forskellig fra

20
Interval og mængde medlemskab

• Interval inkl. start- og slutværdi (BETWEEN)
• SELECT ltkolonnenavnegt FROM lttabelnavngt
• WHERE ltkolonnenavngt
• BETWEEN ltkolonne1 eller værdi1gt AND ltkolonne2
  eller værdi2gt
• Mængde medlemsskab (IN)
• SELECT ltkolonnenavngt FROM lttabelnavngt
• WHERE ltkolonnenavngt
• IN (ltkolonne1 eller værdi1gt, ltkolonne2 eller
  værdi2gt, )

21
Mønster-match

• Mønster-match - jokertegn
• hvis den præcise værdi ikke kendes
• eller værdier med bestemte karakteristika ønskes
• WHERE ltkolonnenavngt LIKE 'lt_gt'
• _ præcis én vilkårlig karakter
• 0 til mange vilkårlige karakterer
• Eks
• SELECT FROM medarbejdere
• WHERE fornavn LIKE 'Ann_'
• AND efternavn LIKE 'ristnsen'
• SELECT FROM medarbejdere
• WHERE email like '_at_'

22
Test for NULL

• Test om feltet er tomt eller ej
• WHERE ltkolonnenavngt IS NULL
• WHERE ltkolonnenavngt IS NOT NULL
• Eks
• SELECT kunde_id, kunde
• FROM kunder
• WHERE tlf IS NOT NULL

23
Bemærk forskellene i betingelserne!

• WHERE Kunde 'Bilka
• Finder rækker hvor kundenavnet er præcis Bilka.
• WHERE Kunde 'Bil
• Finder rækker hvor kundenavnet er præcis Bil.
• WHERE Kunde 'null
• Finder rækker hvor kundenavnet er præcis null.
• WHERE Kunde like 'Bil
• Finder rækker hvor kundenavnet starter med
  Bil.
• WHERE Kunde is null
• Finder rækker hvor kundenavnet er tomt.

24
Flere samtidige betingelser og negation af
betingelser

• Flere betingelser kan kombineres ved anvendelse
  af AND og OR i WHERE-sektionen
• NOT kan anvendes til at negere en betingelse
  (opnå det modsatte resultat)
• Evalueringsrækkefølge
• Generelt fra venstre mod højre
• Dog evalueres parenteser først
• Og NOT evalueres før AND, der evalueres før OR
• Select FROM kunder WHERE (postnr gt 9000)
  AND (kunde 'Føtex') OR NOT(kunde
  ! 'Bilka')

25
Joins

• Anvendes til søgning på data fra flere tabeller
• Tabellerne samles (join'es) via kolonner med
  ens indhold
• ofte kolonner med fremmed- og primærnøgler
• Join kan ske i FROM- eller WHERE-delen
• Typer af joins
• Naturligt join
• Inner join
• Outer join (left, right, full)
• Kartesisk produkt

26
Simpelt gammeldags join i WHERE
Postnumre

• SELECT k.kunde, k.postnr, p.bynavn FROM
  postnumre p, kunder k WHERE p.postnr
  k.postnr
• Når samme kolonnenavn forekommer i begge
  tabeller skal kolonnen præciseres med
  tabelnavnet

Kunder
27
Naturligt join på alle enslydende kolonner
Postnumre

• SELECT k.kunde, postnr, p.bynavn FROM
  postnumre p NATURAL JOIN kunder k
• Tabelnavn må IKKE stå foran
• join-kolonnen i et naturligt join

Kunder
28
Inner Join på delmængde af enslydende kolonnenavne
Postnumre

• SELECT k.kunde, postnr, p.bynavn FROM
  postnumre p JOIN kunder k USING (postnr)
• Tabelnavn må IKKE stå foran
• join-kolonnen ved USING

Kunder
29
Inner Join på kolonner med forskellige navne
Postnumre

• SELECT k.kunde, k.postnr, p.bynavn FROM
  postnumre p JOIN kunder k ON (p.nr
  k.postnr)

Kunder
30
Leftrightfull outer join
Postnumre

• SELECT k.kunde, postnr, p.bynavn FROM
  postnumre p NATURAL RIGHT OUTER JOIN
  kunder k
• LEFTRIGHTFULL OUTER kan
• tilføjes til alle typer FROM-joins

Kunder
31
Det kartesiske produkt
Postnumre

• SELECT k.kunde, k.postnr, p.bynavn FROM
  postnumre p CROSS JOIN kunder k
• Resultatet er lig alle kombinationer
• af rækkerne fra kildetabellerne

Kunder
32
Beregninger på kolonner
Postnumre

• Alle almindelige regneoperationer
• (plus, minus, gange og division)
• kan foretages på tal-kolonner
• Plus og minus kan også foretages
• på datoer for at lægge dage til
• eller trække dage fra en dato.
• SELECT
• postnr 1000 AS nyt_postnr
• FROM
• postnumre

Resultat
33
Funktioner

• Concatenation sammentrækker data fra flere felter
  til et felt
• ltkolonnenavn1 eller værdi1gt ltkolonnenavn2
  eller værdi2gt
• CONCAT(lt kolonnenavn1 eller værdi1 gt,
  ltkolonnenavn2 eller værdi2gt)
• CONCAT virker kun med to kolonner/værdier af
  gangen
• Eks
• SELECT 'Navn ' kunde AS kundenavn FROM
  kunder

34
Funktioner

• Udtræk en delmængde af teksten i et felt. Tallet
  m siger hvorfra der skal klippes, mens n er antal
  tegn fra m der klippes ud
• SUBSTR (ltkolonnenavngt, m,n)
• SELECT SUBSTR(kunde, 1,3) FROM kunder
• Viser de første 3 bogstaver i hver kundes navn
• Udskift tomme / null værdier med egen tekst/tal
  eller indholdet fra en anden kolonne.
• NVL(ltkolonnenavngt, ltkolonnenavn eller værdigt)
• SELECT NVL(kunde, 'Ukendt') FROM kunder

35
Funktioner

• Find/vis længden af hver enkelt værdi i en
  kolonne
• LENGTH(ltkolonnenavngt)
• Fjern decimaler, så der kun er m antal tilbage
• TRUNC (ltkolonnenavngt, m)
• Afrund, så der kun er m antal decimaler tilbage
• ROUND (ltkolonnenavngt, m)
• Fjern foran- og/eller bagvedstillede karakterer
• TRIM (LEADING ltkaraktergt FROM ltkolonnegt)
• TRIM (TRAILING ltkaraktergt FROM ltkolonnegt)
• TRIM (BOTH ltkaraktergt FROM ltkolonnegt)
• TRIM (ltkolonnegt)

36
Funktioner

• Konvertering af dataformat
• TO_CHAR (ltkolonnenavngt)
• TO_NUMBER (ltkolonnenavngt)
• TO_DATE (ltkolonnenavngt,'datoformat')
• Datoformat f.eks. lig 'DD-MM-YYYY' for
  '15-06-2004'
• Ændring af tekst til STORE / små bogstaver
• UPPER (ltkolonnenavngt)
• LOWER (ltkolonnenavngt)

37
Aggregeringsfunktioner og GROUP BY

• SELECT
• COUNT(ltkolonnenavngt eller )
• MIN (ltkolonnenavngt)
• MAX (ltkolonnenavngt)
• AVG (ltkolonnenavngt)
• SUM (ltkolonnenavngt)
• Ved hjælp af GROUP BY kan der laves aggregeringer
  gruppevis i stedet for alle valgte rækker.
• HAVING kan anvendes med GROUP BY til at begrænse
  de grupper, der skal vises.
• Kan sammenlignes med WHERE

38
Aggregeringsfunktioner - eksempler

• SELECT COUNT() FROM kunder
• SELECT MIN (postnr), MAX (postnr)
  FROM kunder
• SELECT postnr, COUNT () AS antal
  FROM kunder GROUP BY postnr HAVING
  COUNT() gt 10 ORDER BY postnr

39
Distinct

• DISTINCT finder de unikke værdier i en eller
  flere kolonne
• DISTINCT ltkolonnenavn(e)gt
• SELECT DISTINCT postnr FROM KUNDER
• SELECT DISTINCT kunde, postnr FROM KUNDER
• DISTINCT kan anvendes i aggregerings-funktioner,
  hvor den mest interessante typisk er COUNT
• SELECT COUNT(DISTINCT postnr) FROM KUNDER
• Hvis man vil tælle unikke værdier over flere
  kolonner skal de først sammentrækkes til et felt

40
CASE

• CASE anvendes til at danne nye værdier, hver gang
  der er et match på eksisterende værdier.
• (CASE ltkolonnenavngt WHEN
  ltoriginalværdigt THEN ltny værdigt
  ELSE ltdefault_værdigt END)
  AS ltny kolonnegt
• (CASE WHEN ltbetingelsegt THEN
  ltny værdigt ELSE
  ltdefault_værdigt END) AS ltny kolonnegt

41
Case - eksempler

• SELECT kunde, (CASE postnr WHEN 9000
  THEN 'Aalborg WHEN 9220 THEN 'Aalborg
  Øst ELSE 'Ukendt by END) AS
  Bynavn FROM kunder
• SELECT postnr, bynavn, (CASE WHEN
  postnr lt 5000 THEN Sjælland WHEN postnr gt
  6000 THEN Jylland ELSE Fyn END) AS
  region FROM postnumre

42
Sæt operatorer

• Sæt operatorer kombinere resultatet (rækkerne)
  fra forskellige select-sætninger
• UNION (Alle rækker minus dubletter)
• UNION ALL (Alle rækker inkl. dubletter)
• INTERSECT (Alle dubletter)
• MINUS (Unikke rækker fra første Select)

JOIN
UNION/INTERSECT/MINUS
43
Sæt operator - eksempel

• Alle unikke samarbejdspartnere og postnr
• SELECT kunde AS navn, postnr FROM
  kunder UNION SELECT lev_navn,
  postnr FROM leverandoerer
• Alle samarbejdspartnere, der både er kunder og
  leverandører fra samme postnr
• SELECT kunde AS navn, postnr FROM
  kunder INTERSECT SELECT lev_navn,
  postnr FROM leverandoerer

44
Subqueries

• Anvendelse af resultatet fra en (indre)
  SELECT-sætning som input i en anden (ydre)
  SELECT-sætning.
• Eks
• SELECT ltkolonnenavngt FROM lttabelnavngt
  WHERE ltkolonnenavngt IN (SELECT)
• SELECT ltkolonnenavngt FROM (SELECT)
  WHERE

45
Subqueries eksempler

• Find navnet på byen der har det største postnr
• SELECT postnr, bynavn FROM postnumre WHERE
  postnr ( SELECT max (postnr) FROM
  postnr )
• Banalt eks. hvor en forespørgsel anvendes som den
  komplette kilde til en ny forespørgsel
• SELECT id, kunde FROM ( SELECT
  FROM kunder WHERE postnr gt 8000
• ) ORDER BY kunde

46
Eksempel på hierarkisk forespørgsel
Medarbejdere

• Hierarkier kan gemmes i
• tabeller ved at have far/parent-
• kolonne, der viser den over-
• ordnede række.
• SELECT id, navn,
• PRIOR navn AS chef, LEVEL
• FROM medarbejdere
• CONNECT BY PRIOR id chef_id
• START WITH chef_id IS NULL

Resultat
47
View

• Virtuel tabel, der baseres på en gemt SQL-sætning
• I databasen gemmes kun sql-sætningen
• Formål
• Forenkle komplicerede forespørgsler
• Implementering af sikkerhed
• Give forskellige brugere forskellige måder at se
  de samme data
• Logisk uafhængighed af ændringer i
  tabelstrukturer
• Alle SELECT-forespørgsler kan foretages mod et
  view
• Begrænsede muligheder for INSERT, UPDATE og
  DELETE afhængig af konstruktionen

48
Oprettelsen af et view

• Oprettelsen af et view
• CREATE OR REPLACE VIEW ltviewnavngt AS SELECT
  ltkolonnergt FROM lttabelnavnegt WHERE
• Forespørgsel mod et view
• SELECT FROM ltviewnavngt
• Sletning af et view
• DROP VIEW ltviewnavngt
• Vis definitionerne af alle views
• SELECT FROM user_views

49
DUAL tabel i Oracle

• DUAL tabellen er en dummy tabel, der ikke har
  noget indhold, men som kan anvendes ved
  SELECT-sætninger, hvor der reelt ikke er en
  kildetabel
• Eks
• SELECT 510 FROM DUAL
• SELECT SYSDATE FROM DUAL
• SELECT USER FROM DUAL

50
Trigger

• En trigger er et lille program, der ved en
  bruger-defineret aktivitet udfører en bestemt
  handling.
• CREATE OR REPLACE TRIGGER lttrigger-navngt
• BEFOREAFTER INSERTUPDATEDELETE ON
  lttabel-navngt
• REFERENCING NEW AS ltnavn for ny rækkegt OLD
  AS ltnavn for gammel rækkegt
• FOR EACH ROW WHEN (lttrigger-betingelsegt)
• lttrigger-handlinggt

51
Trigger der automatisk henter næste værdi fra en
sekvens ind i en tabel

• CREATE OR REPLACE TRIGGER kunde_seq_trigger
  BEFORE INSERT ON kunder FOR EACH ROW
• BEGIN
• IF ( NEW.kunde_id IS NULL ) THEN
• SELECT kunde_seq.NEXTVAL
• INTO NEW.kunde_id
• FROM DUAL
• END IF
• END
• /

52
Kommentarer - Comments

• Kommentarer der ikke registreres i databasen
• -- hvis kommentaren kun fylder én linie
• / / hvis kommentaren fylder flere linier
• Kommentarer der registreres i databasen
• COMMENT ON TABLE lttabelnavngt
• IS 'ltkommentarergt'
• COMMENT ON COLUMN lttabelnavn.kolonnenavngt
• IS 'ltkommentarergt'
• SELECT table_name, comments
• FROM user_tab_comments
• WHERE table_name 'lttabelnavngt'

53
Access Rights (DCL)

• Brugerstyring og hvilke brugere, der har adgang
  til hvilke data.
• GRANT ltaccess_rightgt ON lttabelnavngt TO ltuser_idgt
• REVOKE ltaccess_rightgt ON lttabelnavngt FROM
  ltuser_idgt
• Eks
• GRANT SELECT ON kunder TO bmm
• REVOKE SELECT ON kunder FROM bmm

54
Brugerstyring med SQL

• Opret bruger
• CREATE USER ltbrugernavngt IDENTIFIED BY
  ltpasswordgt
• Slet bruger
• DROP USER ltbrugernavngt CASCADE
• Rettigheder til bruger
• GRANT CONNECT, RESOURCE, CREATE VIEW TO
  ltbrugernavngt
• Ændre password
• ALTER USER ltbrugernavngt IDENTIFIED BY ltnyt
  passwordgt
• Log på med bruger
• CONNECT brugernavngt/ltpasswordgt_at_ltdatabasenavngt

55
Indeksering

• Hvorfor?
• Større hastighed
• Uden et indeks må databasen søge sekventielt
• Tilsvarende at skulle finde oplysninger i en bog
• Syntaks
• CREATE UNIQUE INDEX ltindeksnavngt
• ON lttabelnavngt (ltkolonnenavngt DESC)
• Hvilke kolonner skal man så indeksere?
• Primære og unikke nøgler indekseres automatisk
• Fremmednøgler
• Felter der ofte søges på

56
Data dictionary

• Databasens systemtabeller (ejes af sys)
• Tabeldefinitioner
• Integrity constraints
• Sikkerhedsinformation
• Definition af indeks, views, mv.

57
Data dictionary

• Nyttige views i Oracle's data dictionary
• user_tables
• user_constraints
• user_cons_columns (Constraints kolonner)
• user_views
• user_sequences
• user_synonyms
• user_indexes
• user_ind_columns (Indeks kolonner)

58
Scripts

• Et script er en eller flere sql-sætninger
  placeret i en tekstfil typisk med endelsen
  .sql.
• Kan eksekveres fra SQL-prompten med
• _at_ltsti og filnavngt
• Eksempel
• _at_C\scripts\create_tables.sql

59
?