Cognitive Interaction Technology Center of Excellence

Datenbanken I - SQL – 5.11.2009

Philipp Cimiano AG Semantische Datenbanken und Wissensverarbeitung

1

Cognitive Interaction Technology Center of Excellence

SQL

2

• SQL steht für „Structured Query Language“. • Als Vorgänger der SQL Sprache wurde Anfang der 70er die Sprache „Sequel“ im Kontext des „R“-Systems von IBM eingeführt. • SQL ist heutzutage der „de facto“ Standard im Hinblick auf Anfragesprachen für relationale Datenbanken. • Es gab im Laufe der Zeit veschiedene SQL Versionen: • SQL-86 • SQL-89 • SQL-92 • SQL-99 • SQL-2003 • SQL-2006 • SQL-2008

Cognitive Interaction Technology Center of Excellence

Komponenten von SQL

• SQL gliedert sich in folgende Komponenten: -Data Definition Language: umfasst Befehle um Schemas zu definieren oder zu ändern, Tabellen zu löschen, etc. -Data-Manipulation Language -Integrity Constraints -View Definition -Transaction Control -Embedded/Dynamic SQL -Authorization

3

Cognitive Interaction Technology Center of Excellence

Komponenten von SQL

• SQL gliedert sich in folgende Komponenten: -Data Definition Language -Data-Manipulation Language: umfasst die Query Language von SQL, die zum größten Teil auf der relationalen Algebra basiert. Sie beinhaltet auch Befehle um Zeilen in eine Tabelle einzufügen, Zeilen zu löschen, zu ändern usw. -Integrity Constraints -View Definition -Transaction Control -Embedded/Dynamic SQL -Authorization 4

Cognitive Interaction Technology Center of Excellence

Komponenten von SQL

• SQL gliedert sich in folgende Komponenten: -Data Definition Language -Data-Manipulation Language -Integrity Constraints: die DDL beinhaltet auch Befehle zur Spezifikation von Bedingungen, die in allen Instanzen der Datenbank erfüllt sein müssen. Updates. die diesen Bedingung zuwiderlaufen, werden nicht erlaubt. -View Definition -Transaction Control -Embedded/Dynamic SQL -Authorization 5

Cognitive Interaction Technology Center of Excellence

Komponenten von SQL

• SQL gliedert sich in folgende Komponenten: -Data Definition Language -Data-Manipulation Language -Integrity Constraints -View Definition: die DDL erlaubt uns auch die Spezifikation von Sichten auf die Datenbank. -Transaction Control -Embedded/Dynamic SQL -Authorization

6

Cognitive Interaction Technology Center of Excellence

Komponenten von SQL

• SQL gliedert sich in folgende Komponenten: -Data Definition Language -Data-Manipulation Language -Integrity Constraints -View Definition -Transaction Control: SQL stellt uns Befehle zur Verfügung, um Transaktionen zu definieren (Beginn/Ende) -Embedded/Dynamic SQL -Authorization

7

Cognitive Interaction Technology Center of Excellence

Komponenten von SQL

• SQL gliedert sich in folgende Komponenten: -Data Definition Language -Data-Manipulation Language -Integrity Constraints -View Definition -Transaction Control -Embedded/Dynamic SQL: SQL Befehle können in diverse Programmiersprachen eingebettet werden (C,C++,Java,...) -Authorization

8

Cognitive Interaction Technology Center of Excellence

Komponenten von SQL

• SQL gliedert sich in folgende Komponenten: -Data Definition Language -Data-Manipulation Language -Integrity Constraints -View Definition -Transaction Control -Embedded/Dynamic SQL -Authorization: Die DDL beinhaltet Befehle, um Zugangsrechte für Tabellen zu spezifizieren.

9

Cognitive Interaction Technology Center of Excellence

Schema Definition

create table r(A1 D1, A2 D2,...,An Dn ... ) Wobei: • r der Name der Relation ist • A1,...,An die Attribute mit ihren entsprechenden Domänen D1,...,Dn sind. 10

Cognitive Interaction Technology Center of Excellence

Constraints

• Als Constraints betrachten wir vorerst nur die Spezifikation von Primärschlüsseln. • Mit „primary key (A1,...,An)“ bringt man zum Ausdruck, dass die Attribute A1,..,An den Primärschlüssel einer Relation darstellen.

11

Cognitive Interaction Technology Center of Excellence

Standard Domänen

• char(n)/character: string mit einer Länge von genau n Zeichen • varchar(n): string mit einer variable Länge von bis zu n Zeichen • int/integer: eine ganze Zahl (maschinenabhängig) • smallint: eine „kleine“ ganze Zahl (maschinenabhängig) • numeric(p,d): eine Zahl mit p Ziffern (zusätzlich zu einem Vorzeichen), wobei d Ziffern „rechts vom Komma“ sind. Mit numeric(3,1) können wir z.B. 44.5 speichern aber weder 444.5 noch 0.32 darstellen. • real: Fließkommazahlen (maschinenabhängig) • float(n): eine Fließkommazahl mit mindestens n Ziffern 12

Cognitive Interaction Technology Center of Excellence

Beispiel customer

• create table customer (customer_name char(20), customer_street char(20), customer_city char(30), primary key (customer_name))

13

Cognitive Interaction Technology Center of Excellence

Beispiel branch

• create table branch (branch_name char(15), branch_city char(30), assets numeric(16,2), primary key (branch_name))

14

Cognitive Interaction Technology Center of Excellence

Beispiel account

• create table account (account_number char(10), branch_name char(15), balance numeric(12,2), primary key (account_number))

15

Cognitive Interaction Technology Center of Excellence

Beispiel depositor

• create table depositor (customer_name char(20), account_number char(10), primary key (customer_name,account_number))

16

Cognitive Interaction Technology Center of Excellence

Hinzufügen von Zeilen (Tupeln)

• Um Zeilen einer Tabelle hinzuzufügen verwenden wir den insert-Befehl: insert into account values (´A-9732´,´Perryridge´,1200)

17

Cognitive Interaction Technology Center of Excellence

Löschen

• Löschen von allen Einträgen in einer Tabelle: delete from r Es können dann noch Tupel hinzugefügt werden. • Löschen der gesamten Tabelle mit dem Schema:

18

drop table r Es können keine Tupel mehr hinzugefügt werden; die Tabelle ist „weg“.

Cognitive Interaction Technology Center of Excellence

Hinzufügen/Löschen von Attributen

• Hinzufügen von Attribut A mit Domäne D zu Tabelle r: alter table r add A D „Null“-values werden für dieses Attribut eingefügt. • Löschen von Attribut A mit Domäne D: alter table r drop A Alle Werte dieser „Spalte“ werden gelöscht.

19

Cognitive Interaction Technology Center of Excellence

SQL Anfragesprache

• Eine SQL Anfrage hat typischerweise die folgende Form: select A1,A2,...,An from r1,r2,...,rm where P wobei: • der select-Teil einer Projektion entspricht. Man spezifiziert damit die Attribute, die man gerne als Ergebnis zurückgeliefert haben möchte. • der from-Teil dem kartesischen Produkt der Relationen r1,r2,...,rm enrspricht. • Der where-einer Selektionsoperation mit Bedingung P entspricht. 20

Cognitive Interaction Technology Center of Excellence

Interpretation einer SQL Anfrage in Bezug zur relationalen Algebra

select A1,A2,...,An from r1,r2,...,rm where P

21

Cognitive Interaction Technology Center of Excellence

Der select-Teil • Bsp: Finde die Namen aller Branchen in der Tabelle loan.

22

Cognitive Interaction Technology Center of Excellence

select branch_name from loan

Wichtig: SQL eliminiert keine Duplikate (das ist teuer!) außer man gibt es explizit an! 23

Cognitive Interaction Technology Center of Excellence

select distinct

• Man kann die Entfernung von Duplikaten erfordern wenn man das keyword „distinct“ nach „select“ verwendet: select distinct branch_name from loan

24

Cognitive Interaction Technology Center of Excellence

Arithmetische Ausdrücke

• Wie in der relationalen Algebra auch können wir im selectTeil (Projektion in der rel. Algebra) auch arithmetische Ausrücke angeben: select loan_number,branch_name,amount*100 from loan

25

Cognitive Interaction Technology Center of Excellence

Der where-Teil

• Der where-Teil dient zur Spezifikation einer Bedingung, die von den Ergebnissen erfüllt sein muss. Bsp: Finde alle Kreditnummern (loan_number) von der Perryridge Filiale mit einem Kreditbetrag von mindestens 1200 Euro: select loan_number from loan where branch_name =‘Perryridge‘ and amount > 1200 26

Cognitive Interaction Technology Center of Excellence

„From“-Teil

• Der From-Teil dient zur Spezifikation eines kartesischen Produktes. Er gibt also die Tabellen an, die kombiniert werden sollen. • Es ist relativ einfach, in SQL einen Natural Join zu durchzuführen: select customer_name, borrower.loan_number,amount from borrower, loan where borrower.loan_number=loan.loan_number 27

Cognitive Interaction Technology Center of Excellence

Rename in SQL

• SQL erlaubt natürlich auch die Umbenennung von Tabellen und Attributen. • Das funktioniert wie folgt: alterName as neuerName

• „as“ kann sowohl in dem „select“-Teil als auch im „from“-Teil auftreten.

28

Cognitive Interaction Technology Center of Excellence

Beispiel „as“ im select-Teil

select customer_name, borrower.loan_number as loan_id, amount from borrower, loan where borrower.loan_number=loan.loan_number

29

Cognitive Interaction Technology Center of Excellence

Tupelvariablen

• SQL erlaubt es uns, Tupelvariablen für Relationen einzuführen durch das keyword ‚as‘ , z.B. im ‚from‘-Teil:

select customer_name, T.loan_number, S.amount from borrower as T, loan as S where T.loan_number = S.loan_number 30

Cognitive Interaction Technology Center of Excellence

Tupelvariablen

• Tupelvariablen sind besonders nützlich wenn wir Zeilen innerhalb einer Tabelle vergleichen wollen:

select distinct T.branch_name from branch as T, branch as S where T.assets > S.assets and S.branch_city =‘Brooklyn‘ 31

Cognitive Interaction Technology Center of Excellence

String-Operationen

• SQL erlaubt es uns in Anfragen Stringmuster zu verwenden. • Muster bestehen aus zwei besonderen Symbolen: • % kann für eine beliebige Anzahl an Zeichen stehen • _ kann für ein beliebiges Zeichen stehen

• Man kann diese Symbole mit einem ‚like‘-Ausdruck verwenden: select customer_name from customer where customer_street like %Main%

32

Cognitive Interaction Technology Center of Excellence

Anordnung der Tupel

• Man kann in SQL auch die Ergebnisse sortieren nach bestimmten Attributen, aufsteigend oder absteigend:

select distinct customer_name from borrower, loan where borrower.loan_number=loan.loan_number and branch_name=‘Perryridge‘ order by customer_name

33

Wenn nichts anderes angegeben ist, ordnet „order by“ die Ergebnisse aufsteigend an. Bei einem „order by“ statement muss SQL sortieren (das ist teuer!)

Cognitive Interaction Technology Center of Excellence

Mengen-Operatoren

• Union • Intersect • Except

34

Cognitive Interaction Technology Center of Excellence

Union-Operator

• Der Union Operator in SQL entspricht dem -Operator in der relationalen Algebra. • Bsp: Wir wollen alle Kunden finden, die ein Girokonto oder einen Kredit besitzen: (select customer_id from depositor) union (select customer_id from borrower) 35

Cognitive Interaction Technology Center of Excellence

Union-Operator

• Wichtig: Die Union-Operation eliminiert automatisch doppelte Tupel (Duplikate). • Man kann das aber durch Verwendung von ‚all‘ unterbinden: (select customer_name from depositor) union all (select customer_name from borrower) 36

Cognitive Interaction Technology Center of Excellence

Intersect-Operator

• Der SQL intersect-Operator entsprich dem der relationalen Algebra. (select distinct customer_name from depositor) intersect (select customer_name from borrower) 37

-Operator

Cognitive Interaction Technology Center of Excellence

Intersect-Operation

• Die intersect-Operation eliminiert ebenfalls doppelte Tupel. Man kann das ebenfalls unterbinden:

(select distinct customer_name from depositor) intersect all (select customer_name from borrower)

38

Die Anzahl der „Kopien“ die für ein Tupel zurückgegeben werden entspricht dem Minimum der „Kopien“ des Tupels von den beiden Tabellen. Bemerkung: intersect gibt es in MySQL nicht! (Übung)

Cognitive Interaction Technology Center of Excellence

Except-Operator • Der except-Operator entspricht dem set-difference (-) Operator der relationalen Algebra. • Der folgende SQL Ausdruck gibt uns alle Kunden, die ein Girokonto aber kein Kredit haben: (select distinct customer_name from depositor) except (select customer_name from borrower) 39

Bemerkung: except gibt es in MySQL nicht! (siehe Folie 54)

Cognitive Interaction Technology Center of Excellence

Except-Operator

• Der except-Operator eliminiert ebenfalls doppelte Tupel. • Auch das können wir durch das keyword ‚all‘ unterbinden. (select distinct customer_name from depositor except all (select customer_name

40

from borrower) Die Anzahl der Kopien eines Tupels in der Ergebnismenge entspricht der Anzahl der Kopien in der ersten Tabelle abzüglich der Anzahl der Kopien dieses Tupels in der zweiten Tabelle (wenn die Differenz positiv ist).

Cognitive Interaction Technology Center of Excellence

Aggregationsfunktionen

• Durchschnitt: avg • Minimum: min • Maximum: max • Summe: sum • Anzahl: count Die Eingabe von sum und avg müssen Mengen von numerischen Werten sein; min, max und count können auch mit Strings arbeiten. 41

Cognitive Interaction Technology Center of Excellence

Beispiel

• Bsp: Wir wollen den durchschnittlichen Kontostand in der Filiale in Perryridge haben: select avg (balance) from account where branch_name=‘Perryridge‘

42

Cognitive Interaction Technology Center of Excellence

Gruppierung und Aggregation

• Auch in SQL können wir (wie in der relationalen Algebra) gruppieren vor dem aggregieren: select branch_name, avg(balance) from account group by branch_name Beim Durchschnitt werden Duplikate natürlich nicht eliminiert. 43

Cognitive Interaction Technology Center of Excellence

Gruppierung und Aggregation

• In einigen Fällen wollen wir aber, dass Duplikate eliminiert werden. • Bsp: Finde die Anzahl von Kunden pro Filiale select branch_name, count (distinct customer_name) from depositor, account where depositor.account_number=account.account_number group by branch_name 44

Cognitive Interaction Technology Center of Excellence

Gruppierung und Aggregation

• In manchen Fällen möchten wir auch Bedingungen knüpfen an die Gruppen, deren Werte aggregiert werden. Das geht mit Hilfe des ‘having‘-Operators:

select branch_name, avg(balance) from account group by branch_name having avg(balance) > 500 45

Cognitive Interaction Technology Center of Excellence

Zusammenspiel von having, group by und where

• Wenn wir in einem SQL Ausdruck (Anfrage) sowohl having, group by als auch where verwenden, dann ist die Abarbeitungsreihenfolge wie folgt: 1.  Zuerst wird die Bedingung in der where-Clause verifiziert. 2.  Die Tupel, welche die where-Bedingung erfüllen, werden gruppiert. 3.  Es werden die Gruppen rausgefiltert, welche die havingBedingung nicht erfüllen. 4.  Dann wird der entsprechende Menge von Werten aggregiert. 46

Cognitive Interaction Technology Center of Excellence

„Null“-Werte

• SQL erlaubt auch die Verwendung von „null“-Werten. Sie werden im Wesentlichen wie bei der relationalen Algebra behandelt. • Wir können zusätzlich explizit auf „null“-Werte testen: select loan_number from loan where amount is null

47

Cognitive Interaction Technology Center of Excellence

Auswertung von SQL Ausdrücken

• Für einen SQL Ausdruck select ... from R1,...,Rn where P liefert SQL nur die Tupel zurück, die auf der Bedingung P „true“ zurückliefern. Solche Tupel, die für die Bedingung zu „false“ oder „unknown“ ausgewertet werden, werden nicht zurückgeliefert. 48

Cognitive Interaction Technology Center of Excellence

Behandlung von null/unknown

• Bei Vergleichen mit einem „null“-Wert wird der Wert „unknown“ zurückgeliefert. • Bool‘sche Operatoren: -„and“: • true and unknown = unknown • false and unknown = false • unknown and unknown = unknown

-„or“: • true or unknown=true • false or unknown=unknown • unknown or unknown = unknown

-„not“: • not unknown = unknown 49

Cognitive Interaction Technology Center of Excellence

Aggregationsfunktionen und „null“-Werte

• Faustregel: Alle Aggregationsfunktionen ausser count ignorieren „null“-Werte. • Konsequenz: Die Menge der Werte die aggregiert werden kann leer sein. Dann ist das Ergebnis der Aggregationsfunktion „null“. • Die Anzahl der Tupel (count) in einer leeren Liste ist natürlich „0“.

50

Cognitive Interaction Technology Center of Excellence

Geschachtelte SQL Anfragen

• Es gibt verschiedene Arten und Weisen, in SQL komplexere Anfragen zu stellen bzw. Anfragen ineinander zu schachteln: -Set Membership / Comparison Queries -Complex Queries -With-Clause Queries -Views

51

Cognitive Interaction Technology Center of Excellence

Set Membership

• Wir können das keyword „in“ verwenden, um zu testen, ob bestimmte Tupel in einer Menge enthalten (oder nicht enthalten) sind: select distinct customer_name from borrower where customer_name in (select customer_name from depositor) Frage: Was berechnet diese Query? 52

Cognitive Interaction Technology Center of Excellence

Komplexeres Beispiel für Set Membership Queries

select distinct customer_name from borrower, loan where borrower.loan_number = loan.loan_number and branch_name=‘Perryridge‘ and (branch_name,customer_name) in (select branch_name, customer_name from depositor, account where depositor.account_number= account.account_number) 53

Cognitive Interaction Technology Center of Excellence

EXCEPT als „NOT IN“

(select distinct customer_id from depositor) except (select customer_id from borrower) können wir dann auch schreiben als: select distinct customer_id from depositor where customer_id not in (select customer_id from borrower) 54

Cognitive Interaction Technology Center of Excellence

Vergleichsoperationen auf Mengen

• Nochmal zu unserer Anfrage: „Finde die Namen aller Filialen, die höhere Anlagewerte als (mindestens) eine Filiale in Brooklyn haben.“ • Bisher hatten wir das so gemacht: select distinct T.branch_name from branch as T, branch as S where T.assets > S.assets and S.branch_city=‘Brooklyn‘ Das können wir auch durch Vergleichsoperationen auf Menge realisieren. 55

Cognitive Interaction Technology Center of Excellence

Vergleichsoperationen auf Mengen

select branch_name from branch where assets > some (select assets from branch where branch_city=‘Brooklyn‘)

56

Diese Query ist durchaus näher an der natürlichsprachlichen Formulierung der Anfrage. Wir können in SQL folgende Keywords für Vergleiche mit Menge verwenden: < some, some, >=some, = some und some. (nicht das gleich wie not in!)

Cognitive Interaction Technology Center of Excellence

Vergleichsoperationen mit Mengen (weitere Beispiele)

• Bsp: Finde alle Filialen, die höhere Anlagewerte als alle Filialen in Brooklyn haben. select branch_name from branch where assets > all (select assets from branch where branch_city=‘Brooklyn‘) SQL stellt zusätzlich folgende Vergleichsoperatoren auf Mengen bereit: < all, = all, all (identisch zu not in). 57

Cognitive Interaction Technology Center of Excellence

Vergleichsoperatoren auf Mengen (weitere Beispiele)

• Bsp: „Finde die Filiale mit dem höchsten durchschnittlichen Kontostand“. • Problem: Wir können in SQL Aggregationsfunktionen nicht schachteln: select branch, max(avg(balance)) from branch

58

Cognitive Interaction Technology Center of Excellence

Vergleichsoperatoren auf Mengen (weitere Beispiele)

• Bsp: „Finde die Filiale mit dem höchsten durchschnittlichen Kontostand“. select branch_name from account group by branch_name having avg (balance) >= all (select avg(balance) from account group by branch_name) 59

Cognitive Interaction Technology Center of Excellence

Test auf leere Relationen

• Wir können die keywords „exists“ (bzw. „not exists“) verwenden um zu prüfen, dass es Elemente (bzw. keine Elemente) gibt, die eine bestimmte Bedingung erfüllen. select customer_name from borrower where exists (select * from depositor where depositor.customer_name= borrower.customer_name) 60

Cognitive Interaction Technology Center of Excellence

Enthaltensein von Mengen

• Interessanterweise können wir „not exists“ verwenden, um das Enthaltensein von einer Menge in einer anderen Menge zu prüfen.

61

Cognitive Interaction Technology Center of Excellence

Enthaltensein von Mengen

• Bsp: Finde alle Kunden, die Konten bei allen Filialen von Brooklyn haben. • Für jeden Kunden K gibt es zwei relevante Mengen: -A(K): die Menge der Filialen bei denen dieser Kunde ein Konto hat. -B: die Menge aller Filialen in Brooklyn.

• Idee: liefere alle K zurück, so dass B eine Teilmenge von A (K) ist. (Übungsaufgabe)

62

Cognitive Interaction Technology Center of Excellence

Complex Queries

• Wir können in SQL nicht nur Teil-Ausdrücke in Form von SetMembership Konstrukten im „where“ oder „having“-Teil einbetten, sondern auch in dem „from“-Teil. • Bsp: Finde den Durchschnitts-Saldo von allen Filialen deren Saldo im Durchschnitt größer als 500 ist: Select name, avg from (select branch_name as name, avg(balance) as avg from account group by branch_name) as avg_balance where avg > 500 63

Cognitive Interaction Technology Center of Excellence

Complex Queries

Bsp: Finde das Maximum der Summe aller Saldos für alle Branchen: select max(tot_balance) from (select branch_name, sum(balance) as tot_balance from account group by branch_name) as branch_total

64

Cognitive Interaction Technology Center of Excellence

Sichten (Views)

• Um das Verfassen von komplexeren Queries zu erleichtern können in SQL sogenannte „Sichten“ (Views) definiert werden. Das sind im Wesentlichen „benamte“ Query-Audrücke, die in anderen Query-Ausdrücken verwendet werden können. Eine Sicht wird in SQL wie folgt erzeugt: create view as

65

Cognitive Interaction Technology Center of Excellence

Beispiel Sichten

• Nehmen wir an, ein Mitarbeiter der Bank muss Briefe an alle Kunden der Bank schicken. Er braucht eine Liste aller Kunden (unabhängig ob die Kunden ein Girokonto oder einen Kredit besitzen) pro Filiale. Wir können hierfür eine Sicht wie folgt definieren: create view all_customer as (select branch_name, customer_id from depositor,account where depositor.account_number=account.account_number) union (select branch_name, customer_id from borrower, loan where borrower.loan_number=loan.loan_number) 66

Cognitive Interaction Technology Center of Excellence

Sichten

• Sichten können nun ganz normal als Relation verwendet werden: select customer_name from all_customer where branch_name=‘Perryridge‘

67

Cognitive Interaction Technology Center of Excellence

Bemerkungen zu Sichten

• Die Ausdrücke, die Sichten zu Grunde liegen, werden dynamisch ausgewertet zu dem Zeitpunkt zu welchem der einbettende Ausdruck ausgewertet wird. • Aus Effizienzgründen können Sichten auch „ausmaterialisert“ werden, d.h. die Ergebnisse können explizit gespeichert werden. Man spricht dann von einer ausmaterialisierten Sicht (materialized view). Dann muss man allerdings immer dafür sorgen, dass bei Änderung der entsprechenden Tabellen die ausmaterialisierten Sichten neu berechnet werden (view maintenance). 68

Cognitive Interaction Technology Center of Excellence

Komplexe Anfragen mit „with“

69

• Anstatt Sichten global zu definieren, kann man das Schreiben von Anfragen auch dadurch erleichtern, dass man „lokale“ oder „temporäre Sichten“ mit Hilfe des keywords „with“ einführt: with max_balance (value) as select max(balance) from account select account_number from account, max_balance where account.balance = max_balance.value Bemerkung: „with“-Ausdrücke werden nicht von MySQL unterstützt!

Cognitive Interaction Technology Center of Excellence

Komplexe Anfragen mit „with“ (Weiteres Beispiel)

70

• Bsp: Wir wollen alle Filialen finden deren gesamtes Saldo höher ist als der Durchschnitt des gesamtes Saldos über alle Filialen (vgl. Folie 59). with branch_total (branch_name,value) as select branch_name, sum(balance) from account group by branch_name with branch_total_avg(value) as select avg(value) from branch_total select branch_name from branch_total, branch_total_avg where branch_total.value >= branch_total_avg.value