; F_rel_sning SQL
Documents
Resources
Learning Center
Upload
Plans & pricing Sign in
Sign Out
Your Federal Quarterly Tax Payments are due April 15th Get Help Now >>

F_rel_sning SQL

VIEWS: 10 PAGES: 79

  • pg 1
									   IV1351 ht2010
   nikos dimitrakas
   KTH/ICT/SCS




                  Frågespråk och SQL
                                  nikos dimitrakas
                                          nikosd@kth.se
                                            08-162099
                                             rum 6626




Connolly/Begg         (3rd edition) Kapitel 5, 6 (och lite överallt) (4.1, 4.2)
                      (4th edition) Kapitel 5, 6 (och lite överallt) (4.1, 4.2)
                      (5th edition) Kapitel 6, 7, 8.3 (och lite överallt) (5.1, 5.2)



                                                                                       1
IV1351 ht2010
nikos dimitrakas
KTH/ICT/SCS




                                 Frågespråk
   • Ett språk för att ställa frågor mot en relationsdatabas.
   • Ett slutet språk: Ett uttryck tar emot en eller flera tabeller och
     resulterar i en ny tabell.
   • Procedurella vs. Deklarativa frågespråk
             • Procedurella - Man berättar hur, inte vad! (Relationsalgebra)
             • Deklarativa – Man berättad vad, inte hur! (Tuppelkalkyl)




                                                                               2
IV1351 ht2010
nikos dimitrakas
KTH/ICT/SCS




                               Relationsalgebra
   • Procedurellt språk
   • Består av ett antal operatorer/operationer
             • Varje operation tar en eller två källor och returnerar ett resultat
               som kan i sin tur bli källa till en annan operation
             • Operationer exekveras i en följd
             • Exempel på operationer:
                   • Projektion Π
                   • Selektion s
                   • Join q
                   • Union ⋃
   • Dubblettfritt (bygger på mängdläran)



                                                                                     3
IV1351 ht2010
nikos dimitrakas
KTH/ICT/SCS




                   Tuppelkalkyl/Relationskalkyl

• Ett deklarativt språk som bygger på predikatlogik
• Databasen betraktas som en tuppelvärld och man letar
  efter tuppler som kan få ett villkor att bli sant




                                                         4
  IV1351 ht2010
  nikos dimitrakas
  KTH/ICT/SCS
                                   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 & 2006 kom de senaste versionerna. De introducerade en
  hel del XML-funktionalitet.
                                                                       5
IV1351 ht2010
nikos dimitrakas
KTH/ICT/SCS
                                SQL
Uppdelning
• Databeskrivning, SQL-DDL (Data Definition Language)
• Databearbetning, SQL-DML (Data Manipulation Language)
• Behörighet, SQL-DCL (Data Control Language)


Egenskaper
• Case insensitive (nyckelord, tabellnamn, kolumnnamn)
• Relationally complete - kan uttrycka allt som går att uttrycka
  i relationsalgebran
• Standarden består av mycket mer än bara språket och
  språket består av mycket mer än bara ett frågespråk
                                                                   6
 IV1351 ht2010
 nikos dimitrakas
 KTH/ICT/SCS
                             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 PROCEDURE
DROP VIEW, ta bort vy             DROP PROCEDURE


                                                     7
IV1351 ht2010
nikos dimitrakas
KTH/ICT/SCS
                                        SQL-DML


                   •INSERT, lägg till
                   •UPDATE, ändra
                   •DELETE, ta bort
                   •COMMIT, spara
                   •ROLLBACK, återställ
                   •SELECT, utsökning (relationsalgebra)


                                                           8
IV1351 ht2010


                                    SQL-DCL
nikos dimitrakas
KTH/ICT/SCS




                   •CREATE USER, skapa användare
                   •CREATE SCHEMA

                   •DROP USER, ta bort användare
                   •DROP SCHEMA

                   •GRANT, ge behörighet
                   •REVOKE, ta bort behörighet



                                                   9
IV1351 ht2010


                                       Exempel DDL, DML
nikos dimitrakas
KTH/ICT/SCS




                   • 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




                                                                        10
                          Konceptuell modell  Logisk
IV1351 ht2010
nikos dimitrakas
KTH/ICT/SCS




                           modell  Relationsdatabas
                   Hund         jagar               Katt
                                        0..*
        namn 1..1 UNIQUE 0..*
                                               namn 1..1 UNIQUE
        ras 0..1
                                               färg 0..1
        ägare 1..1




                                                                  11
IV1351 ht2010


                   Konceptuell modell  Logisk
nikos dimitrakas
KTH/ICT/SCS




                    modell  Relationsdatabas




                   CREATE TABLE Hund (
                    hid NUMBER PRIMARY KEY,
                    namn STRING NOT NULL UNIQUE,
                    ras STRING,
                    ägare STRING NOT NULL)


                                                   12
IV1351 ht2010


                   Konceptuell modell  Logisk
nikos dimitrakas
KTH/ICT/SCS




                    modell  Relationsdatabas




                     CREATE TABLE Katt (
                      kid NUMBER PRIMARY KEY,
                      namn STRING NOT NULL UNIQUE,
                      färg STRING)


                                                     13
IV1351 ht2010
nikos dimitrakas
KTH/ICT/SCS           Konceptuell modell  Logisk
                       modell  Relationsdatabas




              CREATE TABLE Jakt (
               hundid NUMBER,
               katt NUMBER,
               PRIMARY KEY (hundid, katt),
               FOREIGN KEY (hundid) REFERENCES Hund (hid)
               ON DELETE CASCADE ON UPDATE CASCADE)


                                                            14
IV1351 ht2010
nikos dimitrakas
KTH/ICT/SCS           Konceptuell modell  Logisk
                       modell  Relationsdatabas




                   ALTER TABLE Jakt
                    ADD FOREIGN KEY (katt)
                    REFERENCES Katt
                    ON DELETE RESTRICT
                    ON UPDATE CASCADE

                                                    15
IV1351 ht2010
nikos dimitrakas
KTH/ICT/SCS
                                        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.
                                                                    16
IV1351 ht2010
nikos dimitrakas
KTH/ICT/SCS         Datatyper i SQL
   INTEGER
   SMALLINT             CLOB
   NUMBER                (Character Large OBject)

   DECIMAL(p[,q])       BLOB
   FLOAT                 (Binary Large OBject)

   BOOLEAN
                        XML
   CHAR(n)
   VARCHAR(n)
   STRING(n)
   TEXT
   DATE               Varierar från DMBS till DBMS!
   TIME
   TIMESTAMP
   MONEY
                                                      17
IV1351 ht2010


                                        INSERT
nikos dimitrakas
KTH/ICT/SCS




                   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
                                                                              18
IV1351 ht2010
nikos dimitrakas
KTH/ICT/SCS
                   INSERT med specificerade värden
    Lägg till en rad i tabellen Hund med värden 151,
    ”Woolfy”, ”Pudel”, ”Johan”
    INSERT INTO Hund
    VALUES (151, ’Woolfy’, ’Pudel’, ’Johan’)
    INSERT INTO Hund (ras, hid, namn, ägare)
    VALUES (’Pudel’, 151, ’Woolfy’,’Johan’)
    Lägg till en rad i tabellen Hund med värden 21,
    ”Jumpy”, ”Lisa”. Alltså ingen ras!
    INSERT INTO Hund
    VALUES (21, ’Jumpy’, NULL, ’Lisa’)
    INSERT INTO Hund (namn, hid, ägare)
    VALUES (’Jumpy’, 21, ’Lisa’)                       19
IV1351 ht2010
nikos dimitrakas


                    INSERT med specificerade värden
KTH/ICT/SCS




      Lägg till flera rader med en gång (fungerar inte med alla
      databashanterare, men ingår i standarden):

        INSERT INTO Hund VALUES
        (124, ’Scrapy’, NULL, ’Lisa’),
        (125, ’Lucky’, ’Rottweiler’, ’Nils’),
        (126, ’Ruddy’, ’Pudel’, ’Lisa’)




                                                                  20
IV1351 ht2010
nikos dimitrakas
KTH/ICT/SCS

                        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



                                                                       21
IV1351 ht2010


                     INSERT och främmande nycklar
nikos dimitrakas
KTH/ICT/SCS




   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 22:

              INSERT INTO Jakt (hundid, katt)
              VALUES (125, 22)

   Fungerar förutsatt att det finns en rad i tabellen Hund med id
   = 125 och en rad i tabellen Katt med kid = 22.

                                                                    22
IV1351 ht2010


                                   UPDATE
nikos dimitrakas
KTH/ICT/SCS




        Ä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’




                                                                     23
IV1351 ht2010


                                DELETE
nikos dimitrakas
KTH/ICT/SCS




    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?
                                                               24
      IV1351 ht2010
      nikos dimitrakas
      KTH/ICT/SCS

                                                              Katt
                                              Jakt
                                                              kid    namn      färg
                                              hundid   katt
                                                              11     Kitty     grått
                                              101      12
                                                              12     Smokey    svart
                                              101      14
                                                              13     Misty     vitt
                                              101      17
                                                              14     Tiger     svart
Hund                                          102      17
                                                              16     Patches   svart
hid            namn      ras          ägare   103      16
                                                              17     Fluffy    grått
101            Barky     Dalmatin     Johan   103      17
                                                              18     Muffin    vitt
102            Woolfy    Dalmatin     Johan   103      20
                                                              19     Missy
103            Beasty    Dobermann    Maria   105      13
                                                              20     Lucy      brunt
104            Scrapy                 Lisa    105      18
                                                              21     Lacey
105            Lucky     Foxterrier   Nils    107      12
107            Ruddy     Labrador     Lisa    107      17
108            Grumpy    Pudel        Johan   110      17
110            Slicky    Dalmatin     Peter   111      12
111            Jumpy     Pudel        Lisa    111      13
                                              111      20

                                                                                       25
                   SELECT … FROM … WHERE …
IV1351 ht2010
nikos dimitrakas
KTH/ICT/SCS




 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.

                                                                     26
IV1351 ht2010
nikos dimitrakas
KTH/ICT/SCS
                          SELECT vs Relationsalgebra


           Ta fram namn och ras för alla hundar som ägs av Johan!

            Π      namn, ras   s   ägare = ’Johan’   Hund

             projektion            SELECT namn, ras
                                   FROM Hund
              selektion            WHERE ägare = ’Johan’




                                                                    27
IV1351 ht2010
nikos dimitrakas
KTH/ICT/SCS
                                SELECT vs Tuppelkalkyl


           Ta fram namn och ras för alla hundar som ägs av Johan!
            {t.namn, t.ras | Hund(t)  t.ägare = 'Johan'}




                   resultatet            villkor

             SELECT namn, ras
             FROM Hund
             WHERE ägare = ’Johan’



                                                                    28
IV1351 ht2010


                                  WHERE-klausulen
nikos dimitrakas
KTH/ICT/SCS




                    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
SELECT *
FROM Hund
WHERE (ras = ’Dalmatin’ OR ras = ’Pudel’)
AND ägare <> ’Peter’
AND namn LIKE ’%ky’
                                                                      29
IV1351 ht2010
nikos dimitrakas
KTH/ICT/SCS
                              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

                                                    30
IV1351 ht2010
nikos dimitrakas
KTH/ICT/SCS
                                  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



                                                                        31
IV1351 ht2010
nikos dimitrakas
KTH/ICT/SCS
                                     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’;


                                                 32
  IV1351 ht2010
  nikos dimitrakas
  KTH/ICT/SCS
                             SELECT från flera tabeller
                              SQL vs relationsalgebra
     Ta fram namn för de personer som äger en hund som jagar
     en katt!
       Π        ägare   s   hundid = hid   (Hund x Jakt)
       Π        ägare   (Hund θ      hundid = hid   Jakt)
                               SELECT ägare
projektion                     FROM Hund, Jakt
                               WHERE hundid=hid
join-villkor
selektion                      SELECT Hund.ägare
                               FROM Hund, Jakt
                               WHERE Jakt.hundid = Hund.hid

                                                               33
IV1351 ht2010
nikos dimitrakas
KTH/ICT/SCS
                    SELECT från flera tabeller
                      SQL vs tuppelkalkyl
   Ta fram namn för de personer som äger en hund som jagar
   en katt!
     {t.ägare | Hund(t) ( t2)(Jakt(t2)  t.hid = t2.hundid)



     SELECT Hund.ägare
     FROM Hund, Jakt
     WHERE Jakt.hundid = Hund.hid




                                                                 34
IV1351 ht2010


                                        Alias
nikos dimitrakas
KTH/ICT/SCS




    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


     SELECT h.namn, h.ras
     FROM Hund AS h
     WHERE h.ägare = ’Johan’
    Obs! Hund kan inte användas i SELECT och WHERE,
    bara h


                                                         35
IV1351 ht2010


                                Alias, exempel
nikos dimitrakas
KTH/ICT/SCS




            Vilka två hundar har samma ägare?

            SELECT h1.namn, h2.namn
            FROM Hund AS h1, Hund h2
            WHERE h1.ägare = h2.ägare
            AND h1.hid < h2.hid




                                                 36
IV1351 ht2010
nikos dimitrakas
KTH/ICT/SCS
                               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.hid = j1.hundid
        AND h2.hid = j2.hundid
        AND j1.katt = j2.katt
        AND h1.hid < h2.hid




                                                  37
IV1351 ht2010
nikos dimitrakas
KTH/ICT/SCS
                                    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 12?
               SELECT namn
               FROM Hund
               WHERE hid IN (SELECT hundid
                            FROM Jakt
                            WHERE katt=12)



                                                          38
IV1351 ht2010
nikos dimitrakas
KTH/ICT/SCS
                                IN och EXISTS

              Vilka hundar jagar en katt med id 12?
              SELECT namn
              FROM Hund
              WHERE EXISTS (SELECT hundid
                                 FROM Jakt
                                 WHERE katt=12
                                 AND hundid=hid)




                                                      39
IV1351 ht2010
nikos dimitrakas
KTH/ICT/SCS
                             Ett till exempel


       Vilka två hundar jagar samma katt?

      SELECT h1.namn, h2.namn
      FROM Hund h1, Hund h2
      WHERE h1.hid < h2.hid
      AND h1.hid IN (SELECT hundid
                FROM Jakt
                WHERE katt IN (SELECT katt
                            FROM Jakt
                            WHERE hundid=h2.hid))




                                                    40
IV1351 ht2010
nikos dimitrakas
KTH/ICT/SCS
                                  Division i SQL

              Vilka hundar jagar alla vita katter? (Division)

     SELECT namn
     FROM Hund
     WHERE NOT EXISTS (SELECT *
                    FROM Katt
                    WHERE färg = ’vitt’
                    AND kid NOT IN (SELECT katt
                              FROM Jakt
                              WHERE hundid=hid))




                                                                41
IV1351 ht2010
nikos dimitrakas
KTH/ICT/SCS
                             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).




                                                                    42
IV1351 ht2010
nikos dimitrakas
KTH/ICT/SCS
                      Andra mängdoperationer


      UNION


      EXCEPT (MINUS)


      INTERSECT(ION)




      Kom ihåg unionskompatibilitet!
                                               43
IV1351 ht2010


                         Unionskompatibilitet
nikos dimitrakas
KTH/ICT/SCS




 Mängdoperationer som UNION, DIFFERENCE
 och INTERSECTION kräver att de två mängder
 som ingår i operationen är kompatibla.
 Unionskompatibilitet innebär:
      • Lika många kolumner
      • Kolumner i samma ordning
      • Kolumner av samma datatyper




                                                44
IV1351 ht2010
nikos dimitrakas
KTH/ICT/SCS
                                UNION exempel


              Vilka hundar jagar katt 12 eller katt 17?

                   SELECT namn
                   FROM Hund, Jakt
                   WHERE hid = hundid
                   AND katt = 12
                   UNION
                   SELECT namn
                   FROM Hund, Jakt
                   WHERE hid = hundid
                   AND katt = 17

                                                          45
IV1351 ht2010
nikos dimitrakas
KTH/ICT/SCS
                           INTERSECT exempel

            Vilka hundar jagar både katt 12 och katt 17?

                   SELECT namn
                   FROM Hund, Jakt
                   WHERE hid = hundid
                   AND katt = 12
                   INTERSECT
                   SELECT namn
                   FROM Hund, Jakt
                   WHERE hid = hundid
                   AND katt = 17


                                                           46
IV1351 ht2010


                               EXCEPT exempel
nikos dimitrakas
KTH/ICT/SCS




            Vilka hundar jagar inga katter?
            SELECT namn
            FROM Hund
            EXCEPT
            SELECT namn
            FROM Hund, Jakt
            WHERE hid = hundid

            SELECT namn
            FROM Hund
            WHERE hid NOT IN (SELECT hundid
                             FROM Jakt)

                                                47
IV1351 ht2010
nikos dimitrakas
KTH/ICT/SCS
                       Mängdoperationer

      Mängdoperationer kan i vissa fall uttryckas utan
       mängdoperatorer.
      • Union kan uttryckas med hjälp av OR-villkor
      • Snitt kan uttryckas med hjälp av AND-villkor
      • Differens kan uttryckas med hjälp av NOT-villkor




                                                           48
IV1351 ht2010
nikos dimitrakas
KTH/ICT/SCS
                            Aggregatfunktioner

     COUNT(*)          räknar antalet rader i en tabell
     COUNT(att)        räknar antalet värden i en kolumn/attribut
     MAX(att)          ger det största värdet i en kolumn/attribut
     MIN(att)          ger det minsta värdet i en kolumn/attribut
     Bara numeriska kolumner/attribut:
     SUM(att)          summerar värdena i en kolumn/attribut
     AVG(att)          tar genomsnittet av värdena i en
                       kolumn/attribut
     VARIANCE(att)     räknar ”variationen” av värdena i en
                       kolumn/attribut
     STDEV(att)        räknar standardavvikelse av värdena i en
                       kolumn/attribut


                                                                     49
IV1351 ht2010
nikos dimitrakas
KTH/ICT/SCS
                          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




                                                      50
IV1351 ht2010
nikos dimitrakas
KTH/ICT/SCS
                       Nya kolumnnamn

      Skapar man kolumner med aggregatfunktioner, har de
      ursprungligen inget namn.


    Hur många katter jagas av hund 101 eller hund 103?
     SELECT COUNT(DISTINCT katt) AS kattantal
     FROM Jakt
     WHERE hundid = 101 OR hundid = 103


     Notera att COUNT(DISTINCT) inte stöds i Access.

                                                           51
 IV1351 ht2010
 nikos dimitrakas
 KTH/ICT/SCS
                           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 kolumnerna.
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!
                                                                   52
IV1351 ht2010
nikos dimitrakas
KTH/ICT/SCS
                         GROUP BY Exempel

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




                                                   53
IV1351 ht2010


                                            HAVING
nikos dimitrakas
KTH/ICT/SCS




                   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




                                                                    54
IV1351 ht2010


                                        HAVING
nikos dimitrakas
KTH/ICT/SCS




                   Vilka katter jagas av minst 3 hundar? Visa namnet!

                   SELECT namn
                   FROM Jakt, Katt
                   WHERE katt=kid
                   GROUP BY namn
                   HAVING COUNT(hundid) > 2




                                                                        55
IV1351 ht2010
nikos dimitrakas
KTH/ICT/SCS
                            HAVING Exempel

       Hur många katter av varje färg jagar varje hund? Visa bara
       hundar som jagar minst 3 katter!
   SELECT Hund.namn, färg, COUNT(kid) AS antal
   FROM Jakt, Hund, Katt
   WHERE hundid=hid
   AND katt=kid
   AND hundid IN (SELECT hundid
                FROM Jakt
                GROUP BY hundid
                HAVING COUNT(katt)>2)
   GROUP BY Hund.namn, färg



                                                                    56
IV1351 ht2010
nikos dimitrakas
KTH/ICT/SCS
                   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(hid) AS antal
        FROM Hund
        GROUP BY ägare) AS nytabell
  WHERE antal = 1




                                                            57
IV1351 ht2010


                   Utan COUNT(DISTINCT)
nikos dimitrakas
KTH/ICT/SCS




 COUNT(DISTINCT) utan COUNT(DISTINCT):


Hur många katter jagas av hund 101 eller hund 103?


SELECT COUNT(katt) AS kattantal
FROM (SELECT DISTINCT katt FROM Jakt
      WHERE hundid = 101 OR hundid = 103)




                                                     58
  IV1351 ht2010
  nikos dimitrakas
  KTH/ICT/SCS
                     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(hid) =
      (SELECT MAX(antal)
       FROM (SELECT ägare, COUNT(hid) AS antal
             FROM Hund
             GROUP BY ägare) AS nytabell)




                                                           59
IV1351 ht2010
nikos dimitrakas
KTH/ICT/SCS
                   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 = hid) AS antal
  FROM Hund

  Här blir hela den nästlade satsen ett värde i varje rad i resultatet.
  Det finns ingen WHERE-klausul så alla hundar kommer med.

                                                                          60
IV1351 ht2010
nikos dimitrakas
KTH/ICT/SCS
                                    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(hid)*500 AS hundkostnad
FROM Hund
GROUP BY ägare




                                                                       61
IV1351 ht2010
nikos dimitrakas
KTH/ICT/SCS
                                            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

                                                                                                     62
IV1351 ht2010
nikos dimitrakas
KTH/ICT/SCS
                             INNER JOIN

     Visa alla hundar som jagar katter!
     SELECT namn
     FROM Hund INNER JOIN Jakt
     ON (hid=hundid)




                                          63
IV1351 ht2010
nikos dimitrakas
KTH/ICT/SCS
                              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=hid)

                                                         64
IV1351 ht2010
nikos dimitrakas
KTH/ICT/SCS

                   SELECT-satsens syntax

       SELECT [DISTINCT] <attributlista>
       FROM <tabellista>
       [WHERE <villkorsuttryck>]
       [GROUP BY <kolumnlista>
        [HAVING <villkorsuttryck>]]
       [ORDER BY <kolumnlista>];




                                           65
IV1351 ht2010
nikos dimitrakas
KTH/ICT/SCS
                               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(hid) AS antal
          FROM Hund
          GROUP BY ägare

          SELECT ägare, antal
          FROM Hundägande
          WHERE antal > 1

                                                             66
IV1351 ht2010
nikos dimitrakas


                     Fördelar med VIEWS
KTH/ICT/SCS




   • 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




                                                            67
IV1351 ht2010
nikos dimitrakas
KTH/ICT/SCS
                                           INDEX

             Ett index skapas för att snabbt kunna komma åt data,
             genom att :
                  • begränsa kolumner
                  • sortera om rader

             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)

                                                                    68
IV1351 ht2010
nikos dimitrakas
KTH/ICT/SCS
                                 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!




                                                                        69
 IV1351 ht2010
 nikos dimitrakas
 KTH/ICT/SCS                   Domän Constraint
• CHECK Constraint
      – … kön STRING(1) CHECK (kön IN (’m’, ’k’) ) …
• DOMAIN
      – … kön MK …

     CREATE DOMAIN MK AS STRING(1)
     CHECK VALUE IN (’m’, ’k’)

     CREATE DOMAIN COUNTRYTYPE AS STRING(50)
     CHECK VALUE IN (SELECT namn FROM land)




                                                       70
IV1351 ht2010
nikos dimitrakas
KTH/ICT/SCS           General Constraint

    Assertion

    CREATE ASSERTION könkoll
    CHECK (NOT EXISTS (SELECT kön
                       FROM person
                       WHERE kön NOT IN (’m’, ’k’)))




                                                       71
 IV1351 ht2010
 nikos dimitrakas
 KTH/ICT/SCS         Enterprise Constraint
• ECA
      – Event
      – Condition
      – Action
• Trigger
     CREATE TRIGGER triggernamn
     AFTER/BEFORE händelse ON tabellnamn
     REFERENCING ny & gammal
     FOR EACH ROW/STATEMENT
     WHEN villkor
     logik att utföras



                                             72
 IV1351 ht2010
 nikos dimitrakas
 KTH/ICT/SCS           CREATE TRIGGER
Räkna hur många rottweiler samma ägare äger vid
inmatning av en ny rottweiler och gör något.

CREATE TRIGGER hundantalkoll
BEFORE INSERT ON HUND
REFERENCING NEW AS n
FOR EACH ROW
WHEN n.ras = ’rottweiler’
IF (SELECT COUNT(*)
    FROM Hund
    WHERE ras=’rottweiler’ AND ägare = n.ägare) > 5
THEN
    gör något.


                                                      73
IV1351 ht2010
nikos dimitrakas
KTH/ICT/SCS
                               CREATE / DROP USER

                   CREATE USER – Skapa användare
                   Skapa användaren Kalle!
                    CREATE USER Kalle


                   DROP USER – Ta bort användare
                   Ta bort användaren Kalle!
                   DROP USER Kalle




                                                    74
IV1351 ht2010
nikos dimitrakas
KTH/ICT/SCS                         GRANT
   Beviljar ett subjekt (användare) vissa privilegier
   (läsa/skriva/etc) på ett objekt (tabell/vy/etc)


    Ge Kalle behörighet att titta i tabellen Hund!
    GRANT SELECT
    ON Hund
    TO Kalle
     Förutsätter att man själv har behörighet att ge denna
     behörighet!




                                                             75
 IV1351 ht2010
 nikos dimitrakas
 KTH/ICT/SCS                                 GRANT
     GRANT privilegier
     ON objekt
     TO subjekt
     [WITH GRANT OPTION]

• Privilegier
      – SELECT, INSERT, UPDATE, DELETE, REFERENCES, USAGE, ALL
        PRIVILEGES, roll
      – Specifika kolumner: UPDATE(adress)
• Objekt
      – Tabell, vy, domän, etc
• Subjekt
      – Användare, grupp, roll, PUBLIC
• WITH GRANT OPTION
      – Tillåt subjektet att ge bort samma behörighet vidare
                                                                 76
IV1351 ht2010
nikos dimitrakas
KTH/ICT/SCS                        REVOKE
   Ta bort vissa privilegier (läsa/skriva/etc) från ett subjekt
   (användare)


    Ta bort Kalles behörighet att titta i tabellen Hund!
    REVOKE SELECT
    ON Hund
    TO Kalle

     Man måste ha gett användaren behörigheten för att få ta
     bort den!



                                                                  77
IV1351 ht2010


                            Tumregler
nikos dimitrakas
KTH/ICT/SCS




   • Tänk ut vad ni vill göra innan ni börjar skriva satsen.
   • Tänk ett steg i taget. Dela upp ett komplext problem till
     flera mindre problem som kan lösas var för sig. Använd
     vyer vid behov.
   • Kontrollera vilka kolumner som finns och hur främmande
     nycklar länkar tabellerna. Kvalificera kolumnerna vid
     behov.
   • Tänk på ordningen som villkor utvärderas (OR, AND). Styr
     med parenteser.
   • Ta inte med onödiga tabeller eller kolumner.




                                                                 78
IV1351 ht2010


                          SQL Validator
nikos dimitrakas
KTH/ICT/SCS




    Ni kan kontrollera om era SQL-satser följer
    standarden här:

    http://developer.mimer.com/validator/




                                                  79

								
To top