SQL - Teil 2

Cognitive Interaction Technology Center of Excellence
Modifikation der Datenbank
• Löschen
• Einfügen
• Änderungen
• Änderungen von Sichten
71
Cognitive Interaction Technology Center of Excellence
Löschen
• Wir haben bereits gesehen, dass wir den gesamten Inhalt
einer Tabelle r löschen können durch das Kommando:
delete from r
Wir können aber auch selektiv Tupel (Zeilen) löschen, die
eine bestimmte Bedingung P erfüllen:
delete from r where P
72
Cognitive Interaction Technology Center of Excellence
Beispiele Löschen
• Löschen aller Konten bei der „Perryridge“ Filiale:
delete from account
where branch_name=‘Perryridge‘
• Löschen aller Kredite zwischen $1300 und $1500:
delete from loan
where amount between 1300 and 1500
• Löschen aller Konten von Filialen in Brooklyn:
delete from account
where branch_name in (select branch_name
from branch
where branch_city=‘Brooklyn‘)
73
Cognitive Interaction Technology Center of Excellence
Bemerkung zu delete
• Beim Löschen von Tupeln ist es wichtig, dass zuerst alle Tupel
ermittelt werden welche die Bedingung erfüllen und dann erst
alle Tupel gelöscht werden.
74
Cognitive Interaction Technology Center of Excellence
Einfügen
• Wir können mit SQL Tupel direkt einfügen durch das
„insert into“ Kommando:
insert into account values(‘A-9732‘,‘Perryridge‘,1200)
Hierbei müssen wir allerdings die Reihenfolge der Attribute
kennen!
75
Cognitive Interaction Technology Center of Excellence
Einfügen
Man kann allerdings auch die Attributsnamen explizit
angeben, dann kann die Reihenfolge variiert werden:
insert into account
(branch_name,account_number,balance)
values(‘Perryridge‘,‘A-9732‘,1200)
76
Cognitive Interaction Technology Center of Excellence
Einfügen durch Select-Ausdrücke
• Wir können auch Tupel einfügen, die wir aus anderen Tabellen
selektiert haben.
• Bsp: Wir erzeugen ein Konto mit 200$ Startguthaben für jeden
Besitzer eines Kredites an der Perryridge Filiale.
insert into account
select loan_number, branch_name, 200
from loan
where branch_name=‘Perryridge‘
77
Cognitive Interaction Technology Center of Excellence
Einfügen durch Select-Statements
• Auch hier ist es wichtig, dass der „select“- Teil
vollkommen ausgewertet wird bevor die Tupel eingefügt
werden.
• Bsp:
insert into account
select *
from account
78
Cognitive Interaction Technology Center of Excellence
Änderung bestimmter Werte
• In manchen Fällen wollen wir den Wert eines Attributs
ändern ohne die anderen Werte zu modifizieren. Das geht
mit dem update-Kommando:
update account
set balance = balance*1.05
79
Cognitive Interaction Technology Center of Excellence
Änderungen mit update
• Änderungen mit update können wir auch selektiv für
bestimmte Tupel machen:
update account
set balance = balance*1.05
where balance >= 1000
80
Cognitive Interaction Technology Center of Excellence
Änderungen in Sichten
• Sichten stellen ein sehr wichtiges und praktisches
Werkzeug dar; allerdings ist es problematisch, Sichten zu
ändern.
• Betrachten wir z.B. folgende Sicht:
create view loan_info as
select customer_id, amount
from borrower, loan
where borrower.loan_number=loan.loan_number
81
Cognitive Interaction Technology Center of Excellence
Änderungen in Sichten
Was passiert nun wenn wir versuchen, in die Sicht Zeilen
einzufügen?
insert into loan_info values (‘Johnson‘,1900).
Loan_info
82
Cognitive Interaction Technology Center of Excellence
Änderungen in Sichten
• Problem: diese Änderung hat nicht den erwünschten Effekt
(warum?)
83
Cognitive Interaction Technology Center of Excellence
Änderbare Sichten (updatable)
• Auf Grund der Probleme, die mit der Modifikation von
Sichten einhergehen, verbieten einige Datenbanksysteme
schlicht und einfach die Modifikation von Sichten.
• Im Allgemeinen können wir aber festlegen, dass eine Sicht
änderbar (updatable) ist wenn folgende Bedingungen
erfüllt sind:
84
-Der from-Teil bezieht sich auf nur eine Tabelle
-Der select-Teil beinhaltet nur Attribute der Tabelle aber keine
komplexen Ausdrücke oder Aggregate
-Jedes Attribut welches nicht im select-Teil vorkommt kann auf „null“
gesetzt werden.
-Die Query verwendet nicht die Konstrukte group by oder having.
Cognitive Interaction Technology Center of Excellence
Änderbare Sichten
• Auch änderbare Sichten garantieren nicht, dass die
eingefügte Zeile dann auch wirklich in der Sicht enthalten
ist.
• Man kann in SQL dann eine Sicht definieren als „with
check option“. Dann wird ein Tupel nur eingefügt wenn
auch sichergestellt ist, dass das Tupel in der Sicht auch
enthalten ist.
85
create view loan_info as
select customer_id, amount
from borrower, loan
where borrower.loan_number=loan.loan_number
with check option
Cognitive Interaction Technology Center of Excellence
Weitere Datentypen in SQL
• Zusätzlich zu den Datentypen, die wir bisher
kennengelernt haben, unterstützt SQL auch folgende
Datentypen:
-Date: stellt einen Kalendertag dar, beschrieben durch ein Jahr (4
Ziffern), einen Monat (2 Ziffern) und einen Tag (2 Ziffern).
-Time: repräsentiert eine Tageszeit mit Angabe der Stunde, Minute
und Sekunde.
-Timestamp: eine Kombination aus Date und Time, um einen
Zeitpunkt eindeutig zu charakterisieren.
86
Cognitive Interaction Technology Center of Excellence
Datentypen in SQL
• SQL stellt uns auch Vergleichsoperatoren sowie
arithmetische Operatoren für diese Datentypen bereit:
• 2009-10-22 > 2009-10-15 = true
• 2009-10-22 – 2009-10-15 = Intervall von sieben Tagen
• 2009-10-22 + Intervall von 7 Tagen = 2009-10-29
87
Cognitive Interaction Technology Center of Excellence
User-definierte Datentypen
• In SQL können eigene Datentypen definiert werden, z.B.
create type Euros as numeric(12,2) final
Diese Typen können wir dann bei der Definition eines
Schemas natürlich verwenden:
88
create table account
(account_number char(10),
branch_name char(15),
balance
Euros)
Cognitive Interaction Technology Center of Excellence
Integritätsbedingungen
• Integritätsbedingungen auf einer einzelnen Relation
-Not Null
-Eindeutigkeitsbedingung
-Der Check-Konstrukt
• Referentielle Integrität
89
Cognitive Interaction Technology Center of Excellence
Integritätsbedingungen
• Beispiele für Integritätsbedingungen sind folgende:
-Ein Kontostand kann nicht „null“ sein.
-Konten müssen unterschiedliche Kontonummern haben.
-Jedes Konto in der depositor-Relation muss auch einen
entsprechenden Eintrag in der account-Relation haben.
90
Cognitive Interaction Technology Center of Excellence
Integritätsbedingungen
• Integritätsbedingungen werden meistens bei der
Erzeugung des Schemas einer Tabelle spezifiziert. Wir
haben bisher dadurch Primärschlüssel von Relationen
angegeben:
create table customer
(customer_name
char(20),
customer_street
char(20),
customer_city
char(30),
primary key (customer_name))
91
Cognitive Interaction Technology Center of Excellence
„Not null“-Bedingung
• In manchen Fällen macht es keinen Sinn, „null“-values
zuzulassen (z.B. beim Stand eines Kontos).
• Mit dem „not null“ Ausdruck können wir „null“-values
explizit verbieten:
create table account
( account_number char(15) not null,
branch_name
char(3),
balance
numeric(16,2) not null,
primary key (account_number))
92
Cognitive Interaction Technology Center of Excellence
Eindeutigkeitsbedingung
• Man kann in SQL nicht nur den Primärschlüssel
spezifizieren, sondern auch festlegen, dass keine zwei
Tupel in der Datenbank die selben Werte für zwei Attribute
haben sollen.
• Das geht mit dem unique-Konstrukt:
create table account
( account_number char(15) not null unique,
branch_name
char(3),
balance
numeric(16,2) not null
)
93
Cognitive Interaction Technology Center of Excellence
Weitere Bedingungen
• Es können weitere Bedingungen an eine Relation geknüpft
werden. Das erfolgt durch das Keyword „check“.
• Bsp: Der Kontostand muss immer positiv sein:
create table account
(account_number
char(10) not null,
branch_name
char(15),
balance
numeric(12,2) not null,
primary key (account_number),
check (balance >=0))
94
Cognitive Interaction Technology Center of Excellence
Weitere Bedingungen
• Bsp: Die Branche eines Kontos muss in der branch-Tabelle
enthalten sein:
create table account
(account_number
char(10) not null,
branch_name
char(15),
balance
numeric(12,2) not null,
primary key (account_number),
check (balance >=0),
check branch_name in (select branch_name from branch))
95
Cognitive Interaction Technology Center of Excellence
Referentielle Integrität
• Wir können auch mit Hilfe der DDL von SQL foreign keys
definieren.
• Formale Definition eines foreign keys:
-Gegeben zwei Relationen r1 und r2 mit Schemas R1(r1) und R2(r2) und
Primärschlüsseln K1 und K2 jeweils.
-Eine Teilmenge r´ der Attribute in R1 nennen wir Fremdschlüssel
(oder foreign key) mit Referenz auf R2 wenn für jedes Tupel t1 in r1
ein Tupel t2 in r2 existiert so dass t1[r‘]=t2[K2].
-Wir können diese Bedingung auch äquivalent schreiben als:
96
Cognitive Interaction Technology Center of Excellence
Spezifikation von Fremdschlüssel-Verweisen
• Wir können Fremdschlüssel durch das Schlüsselwort
„references“ einführen:
create table account
(account_number
char(10) not null,
branch_name
char(15) references branch,
balance
numeric(12,2) not null,
primary key (account_number),
check (balance >=0))
97
Cognitive Interaction Technology Center of Excellence
Spezifikation von Fremdschlüssel-Verweisen
• Oder alternativ:
create table account
(account_number
char(10) not null,
branch_name
char(15)
balance
numeric(12,2) not null,
primary key (account_number),
foreign key (branch_name) references branch
check (balance >=0))
Damit müssen wir in der account-Tabelle einen Filialennamen
(branch_name) angeben, der in der branch-Tabelle auch
existiert.
98
Cognitive Interaction Technology Center of Excellence
Änderungen in der referenzierten Tabelle
• Frage: Was passiert wenn Branchen gelöscht werden?
• SQL stellt uns verschiedene Strategien zur Verfügung, wie
bei der Löschung von referenzierten Tupeln verfahren
werden soll.
• Verhalten im Löschen-Fall:
-„on delete cascade“: wenn Tupel in der referenzierten Tabelle
gelöscht werden, dann lösche alle Tupel in der referenzierenden
Tabelle, die das gelöschte Tupel referenzieren.
-„on delete set null“: wenn Tupel in der referenzierten Tabelle
gelöscht werden dann setze die entsprechende FremdschlüsselReferenz in der referenzierenden Tabelle auf „null“.
99
Cognitive Interaction Technology Center of Excellence
Änderungen in der referenzierten Tabelle
• Frage: Was passiert wenn der Name einer Branche sich ändert?
• SQL stellt uns verschiedene Strategien zur Verfügung, wie bei
einer Änderung der referenzierten Tupel verfahren werden soll.
• Verhalten im Fall einer Änderung
-„on update cascade“: wenn Tupel in der referenzierten Tabelle geändert
werden, dann ändere entsprechend alle Tupel in der referenzierenden
Tabelle, die das Tupel referenzieren.
-„on update set null“: wenn Tupel in der referenzierten Tabelle geändert
werden (Primärschlüssel), dann setze die Referenzen in den Tupeln der
referenzierenden Tabelle auf „null“.
100
Cognitive Interaction Technology Center of Excellence
Probleme mit referentieller Integrität
• Nehmen wir an, wir haben eine Tabelle person, die wie folgt aussieht:
create table person
(firstname
char(10) not null,
secondname
char(15) not null,
middlename
char(10),
birthday
date,
address
char(50),
marriedToPersonWithFirstname
char(10),
marriedToPersonWithSecondname
char(15),
marriedToPersonWithBirthday
date
primary key (firstname,secondname,birthday),
foreign key(marriedToPersonWithFirstname,
marriedToPersonWithSecondName,marriedToPersonWithBirthday)
references person (firstname,secondname,birthday)
)
101
Cognitive Interaction Technology Center of Excellence
Aufschiebbar oder nicht aufschiebbar?
• Was ist nun wenn wir zwei Personen hinzufügen wollen,
die verheiratet sind (z.B. Mia und Vincent)?
• Unabhängig davon welche Person zuerst angelegt wird,
werden wir die referentielle Integrität verletzen!
• In SQL kann man angeben, dass die Überprüfung
bestimmter Integritätsbedingungen aufgeschoben werden
kann (deferred) bis zum Ende einer Transaktion.
• In einigen Datenbanken (PostgreSQL, ORACLE) sagt
initially deferred deferrable, dass der
Fremdschlüsselcheck erst nach der Transaktion
durchgeführt werden soll.
102
Cognitive Interaction Technology Center of Excellence
An unserem Beispiel
create table person
(firstname
char(10) not null,
secondname
char (15) not null,
middlename
char(10),
birthday
date not null,
address
char(50),
marriedToPersonWithFirstname
char(10),
marriedToPersonWithSecondname
char(15),
marriedToPersonWithBirthday
date
primary key (firstname,secondname,birthday),
foreign key(marriedToPersonWithFirstname, marriedToPersonWithSecondName,
marriedToPersonWithBirthday) references people (firstname,secondname,birthday)
on delete set null
on update cascade
initially deferred deferrable (PostgreSQL, ORACLE, aber nicht MySQL)
103
Cognitive Interaction Technology Center of Excellence
Zugriffsrechte
• In SQL kann man für jede Tabelle folgende Rechte vergeben:
-Leserecht
-das Recht, Daten einzufügen
-das Recht, Daten zu ändern
-das Recht, Daten zu löschen
Diese Rechte werden in SQL „privileges“ genannt. SQL unterscheidet
folgende „privileges“:
-select
-insert
-update
-delete
104
Cognitive Interaction Technology Center of Excellence
Vergabe von Rechten (Authorisierung)
• Die Vergabe von Rechten auf einer Tabelle erfolgt durch
den Befehl (DDL):
grant <Privilege_List> on <Table> to <User>
Bsp:
grant select on account to John, Mary
grant select, update(amount) on loan to John, Mary
105
Cognitive Interaction Technology Center of Excellence
Vergabe von Rechten
• Es können auch alle Rechte auf einer Tabelle mit dem
keyword „all“ vergeben werden:
grant all on account to Philipp
• Rechte können natürlich auch wieder entzogen werden:
revoke insert on account from Philipp
106
Cognitive Interaction Technology Center of Excellence
Embedded SQL und JDBC
• Oft will man auf eine Datenbank von der Programmiersprache
aus zugreifen, in der man eine Anwendung entwickelt.
• Wenn SQL-Ausdrücke in eine Programmiersprache eingebettet
sind, so spricht man von „Embedded SQL“.
• Für die Einbettung von SQL in eine Programmiersprache (z.B.
Java) benötigt man Software (einen Connector) der zwischen
dem Programm und der Datenbank „vermittelt“.
• Für Java gibt es z.B. die JDBC API (Java Database
Connectivity).
• Für C gibt es die Open Database Connectivity (ODBC) API.
• Alle unsere Beispiele werden sich auf die JDBC API beziehen.
107
Cognitive Interaction Technology Center of Excellence
Wichtige Schritte um SQL in Java einzubetten
1. Eine Library importieren, die JDBC implementiert (z.B.
von MySQL), siehe http://dev.mysql.com/downloads/
connector/j/3.1.html
2. Den richtigen Treiber laden.
3. Eine Verbindung zur Datenbank herstellen.
4. Los geht‘s! (Anfragen, Löschen, Änderungen etc.)
108
Cognitive Interaction Technology Center of Excellence
Imports in Java (siehe Example1.java)
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;
109
Cognitive Interaction Technology Center of Excellence
Treiber laden (siehe Example1.java)
public class Example1 {
public static void main(String[] args) {
Connection conn=null;
Statement stmt;
ResultSet rs;
// load the driver
System.out.println("Loading driver...");
try{
Class.forName("com.mysql.jdbc.Driver").newInstance();
} catch(Exception e) {
System.out.println("Class strife. " + e);
}
System.out.println("Driver loaded!");
110
...
}
Cognitive Interaction Technology Center of Excellence
Verbindung zur Datenbank herstellen (siehe
Example1.java)
try{
conn = DriverManager.getConnection
("jdbc:mysql://localhost/Banking?user=cimiano");
} catch (SQLException ex) {}
111
Cognitive Interaction Technology Center of Excellence
Syntax für URL in DriverManager.getConnection()
jdbc:mysql://[hostname][:port]/dbname[?
param1=value1][&param2=value2]...
String url = “jdbc:mysql://localhost/Banking";
Connection con = DriverManager.getConnection(url,
“cimiano", "password");
112
Cognitive Interaction Technology Center of Excellence
localhost
• Der Port wird per Default auf 3306 gesetzt (wenn nichts
anderes angegeben wird!).
• Dementsprechend sind folgende Statements äquivalent:
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/
Banking?user=cimiano&password=pass");
conn = DriverManager.getConnection("jdbc:mysql://localhost/
Banking?user=cimiano&password=pass");
conn = DriverManager.getConnection("jdbc:mysql://localhost/
Banking,”cimiano”,”pass”);
113
Cognitive Interaction Technology Center of Excellence
Anfragen (siehe Example1.java)
try {
stmt = conn.createStatement();
rs = stmt.executeQuery("SELECT * from account");
int count=0;
while (rs.next ())
{
String account_no = rs.getString ("account_number");
String balance = rs.getString ("balance");
System.out.println ("account number = " + account_no+ ", balance = " + balance);
count++;
}
rs.close ();
System.out.println (count + " rows were retrieved");
}
114
Cognitive Interaction Technology Center of Excellence
Updates (siehe Example2.java)
stmt.executeUpdate("insert into customer values
('447-77-0000','John','5th Av.','New York')");
Siehe Example2.java auf http://philipp.cimiano.org/
teaching/datenbanken_WS09/
115
Cognitive Interaction Technology Center of Excellence
Anfragen von Metadaten mit JDBC (siehe
Example3.java)
• Metadaten sind „Daten über Daten“.
• Man kann z.B. zu den Ergebnissen einer Anfrage die
Namen der Spalten (als Metadaten) besorgen (siehe
Example3.java):
stmt = conn.createStatement();
116
rs = stmt.executeQuery("SELECT * from account");
String column;
int count=0;
rsmd = (ResultSetMetaData) rs.getMetaData();
for (int i=1; i <= rsmd.getColumnCount(); i++)
{
column = rsmd.getColumnName(i);
System.out.print(column+"\t");
}