Relationen und SQL

Einführung in die
Informatik II
Die Structured Query Language SQL
Prof. Dr. Nikolaus Wulff
SQL
• Das E/R-Modell lässt sich eins zu eins auf ein Tabellenschema abbilden. Benötigt wird eine Syntax, um
Tabellen zu definieren und zu manipulieren.
• Relationale Datenbanken bieten hierzu SQL als
Sprache an, die unterschiedliche Aspekte vereint:
– Die Data Definition Language (DDL), definiert die
Schemata per create/drop Tabelle.
– Die Data Manipulation Language (DML) verändert
Tabelleneinträge per insert- und update- und sucht
Einträge per select-Anweisung.
– Die Data Control Language (DCL) vergibt Rechte und
Rollen an Benutzer/Gruppen per grant-Befehl.
Prof. Dr. Nikolaus Wulff
Informatik II
2
Befehlsübersicht (Ausschnitt)
• CREATE TABLE relation (Attribut Typ Liste)
– Erzeugt das Relationsschema als Tabelle
• INSERT INTO relation (Attribut Liste) VALUES...
– Fügt einen Satz in die Tabelle ein
• UPDATE relation SET attribut=value ...
– Verändert einen oder mehrere Sätze der Tabelle
• SELECT * FROM relation
– Liefert alle Elemente der Relationsmenge
• SELECT (Attribut Typ Liste) FROM relation
– Liefert die Projektion T der Relationsmenge
• SELECT * FROM relation WHERE (Bedingung)
– Liefert per Selektion  Θ eine Teilmenge
Prof. Dr. Nikolaus Wulff
Informatik II
3
E/R-Modell und Codierung
• In modernen Anwendungen entwickelt sich das E/RModell meistens parallel zum Quellcode.
• C-Struts, Pascal-Records oder Java Klassen dienen als
Schablone für das Tabellenschema. Zu jedem Attribut
des Struktur/Klasse gehört ein Attribut des Tabellen /
Relationenschema:
create table Person
id
integer not null,
name
varchar(30),
gewicht float,
schuhe integer,
primary key(id);
Prof. Dr. Nikolaus Wulff
Informatik II
Erzeugung der
Relation „Person“
als Tabelle
4
Datenbank Sitzung
• Alle Datenbankhersteller bieten Werkzeuge zum
Administrieren der Datenbank. Im einfachsten Fall als
Konsolenanwendung in einer DOS/Unix-Shell:
mysql> show tables;
Empty set (0.00 sec)
mysql> create table person (id integer not null,
name varchar(30), gewicht float, primary key(id));
Query OK, 0 rows affected (0.05 sec)
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| person
|
+----------------+
Attribut „Schuhe“
1 row in set (0.00 sec)
wurde vergessen...
mysql>
Prof. Dr. Nikolaus Wulff
Informatik II
5
Anzeigen des Schemas
• Überprüfung des neu angelegten Schema für die
Person Tabelle per „describe“-Befehl...
Prof. Dr. Nikolaus Wulff
Informatik II
6
MySQL Control Center
• Komfortabler ist ein graphisches Werkzeug:
Das Schema
der Tabelle
Die
Personen
Tabelle
Prof. Dr. Nikolaus Wulff
Informatik II
7
Tabelle füllen
• Noch ist die Tabelle leer, mit dem insert Befehl lassen
sich neue Datensätze anlegen:
insert into Person (id,
values (1,'Tom',
insert into Person (id,
values (2,'Bob',
Prof. Dr. Nikolaus Wulff
Informatik II
name, gewicht)
78.6);
name, gewicht)
82);
8
Tabelle anzeigen
• Mit dem select Befehl lässt sich der Inhalt/die Menge
einer Tabelle ausgeben:
– select * from Person
– Es werden alle Elemente mit allen Attributen ermittelt.
• Eine Projektion T wird durch die Angabe des
Schemas T erzielt:
– select (name,gewicht) from Person
– Liefert von allen Elementen nur Name und Gewicht
• Eine Selektion  Θ erfolgt durch Angabe einer
Bedingung in einer where-Klausel:
select * from Person where name like '%o%'
– Liefert die Daten von Tom und Bob...
Prof. Dr. Nikolaus Wulff
Informatik II
9
Eindeutiger Primary Key
• Das Primary Key Feld (hier id) muss eindeutig sein,
kein Datensatz mit dem selben Schlüssel lässt sich ein
zweites Mal einfügen:
• Ein leeres Feld (hier gewicht) ist erlaubt, allerdings
nicht beim Primary Key!
Prof. Dr. Nikolaus Wulff
Informatik II
10
Update Befehl
• Existierende Datensätze lassen sich nachträglich per
update-Befehl verändern:
update person set gewicht=80;
• Dummerweise wurden so alle Gewichte verändert!
Update ist eine Mengenoperation...
Prof. Dr. Nikolaus Wulff
Informatik II
11
Update - where
• Um gezielt eine Teilmenge zu manipulieren, muss
diese mit einer where-Bedingung selektiert werden:
update person set ... where name='Kai'
• Es wird erst die Treffermenge mit where selektiert
und anschließend darauf update ausgeführt.
Prof. Dr. Nikolaus Wulff
Informatik II
12
Join / Kartesisches Produkt
• Tabellen lassen sich vereinen, hier person⊗groesse:
Beachte:
select p.name, ..., g.groesse
from person as p,
groesse as g
• Die Projektion π =(name,gewicht,groesse) wurde
angewandt, jedoch die Selektion σ fehlt.
• Mit person as p werden kurze Alias Namen vergeben.
Prof. Dr. Nikolaus Wulff
Informatik II
13
θ - Join
• Mit der where Bedingung p.id = g.id und
anschließender Projektion erfolgt die richtige
Zuordnung:
Selektion σ ohne
Projektion π ...
Selektion σ mit
Projektion π.
Prof. Dr. Nikolaus Wulff
Informatik II
14
Views
• Kreuzprodukte, Projektionen und Selektionen können
als eine bestimmte Sicht auf die Relationen der Datenbank angesehen werden.
• In Form einer View lässt sich dieses Konzept direkt
als eine „pseudo Tabelle/Relation“ innerhalb einer DB
realisieren:
create view vPerson as select p.id, p.name, p.gewicht, g.groesse
from person as p, groesse as g where p.id = g.id;
• Diese Anweisung erzeugt eine View auf die per select
ausgewählte Untermenge der Relationen P und G.
Prof. Dr. Nikolaus Wulff
Informatik II
15
Views sind auch Relationen
• Views als spezielle Form
einer Relation lassen
sich per select anzeigen.
• Die where Bedingung
der select Definition
wird berücksichtigt:
– Tim gehört nicht mit zur
View Relation...
• Auf der View lassen sich
Selektion, Projektion
und Kreuzprodukt
anwenden...
Prof. Dr. Nikolaus Wulff
Informatik II
16
Views Pro und Contra
• Schwieriger werden insert und update Methoden bei
Views, es müssten in dem Beispiel zwei Tabellen
geändert werden.
• Es z.B. nicht klar, ob bei der insert/update-Methode
das Attribut name bei P oder G gesetzt werden soll...
• Es müssen also beide Tabellen der View explizit
geändert werden, um Konsistenz zu erreichen.
• Views eignen sich zum Anzeigen und Selektieren,
sind aber nicht für insert und update geeignet.
• Mit Views können für Benutzergruppen „Nur-Lese“
Zugriffe einfach ermöglicht werden.
Prof. Dr. Nikolaus Wulff
Informatik II
17
Suchen und Index
• Meistens werden Daten nicht nur gespeichert, sondern
sie müssen auch schnell wieder gefunden werden.
• Hierzu bietet die SQL where-Klausel geeignete
Operatoren an:
– where id=5, where name like 'B%', where gewicht < 60,...
• Damit diese Suche möglichst effizient erfolgt werden
häufig benötigte Attributspalten mit einem Index
versehen:
create index name_idx on person(name);
• Datenbanksysteme können intern einen „Bind-Plan“
erstellen, um Zugriffe zu optimieren.
Prof. Dr. Nikolaus Wulff
Informatik II
18
SQL Funktionen
• SQL bietet Funktionen
über den Relationen.
• Statistische Funktionen:
– min/max/avg/median
• Kardinalität: count
• Summierung: sum
Rundungsfehler
des Typen float...
Prof. Dr. Nikolaus Wulff
Informatik II
19
SQL in Programmiersprachen
• Natürlich lässt sich eine DB nicht nur per SQL von
der Konsole aus verwenden.
• Fast alle Programmiersprachen bieten Möglichkeiten
SQL direkt in den Quellcode einzubetten.
• Java sieht hierzu das Paket java.sql vor, es gibt dort:
– Eine Datenbankverbindung: Connection.
– Eine SQL-Anweisung: Statement
• dieses lässt sich als insert, update, where-Klausel etc
parametriesieren.
– Eine SQL-Ergebnismenge: ResultSet
• über diese Menge kann, wie bei einer Collection, in einer Schleife
iteriert werden, um Java Objekte aus zu instanziieren.
Prof. Dr. Nikolaus Wulff
Informatik II
20
Jdbc SQL API
Prof. Dr. Nikolaus Wulff
Informatik II
21
Book insert per SQL
protected void insert(Book obj) throws SQLException {
StringBuffer sql = new StringBuffer(INSERT);
sql.append(getTableName());
sql.append(" (id,isbn,author,title,description,price)");
sql.append(" values(");
sql.append(obj.getId());
sql.append(", '");
sql.append(obj.getIsbn());
sql.append("','");
sql.append(obj.getAuthor());
sql.append("','");
sql.append(obj.getTitle());
sql.append("','");
sql.append(obj.getDescription());
sql.append("',");
sql.append(obj.getPrice());
sql.append(")");
executeSQL(sql.toString());
}
Prof. Dr. Nikolaus Wulff
Informatik II
22
Eine generische SQL Query
public Collection<T> executeQuery(String where)
throws SQLException {
Connection con = getConnection();
ResultSet set; Statement stm;
ArrayList<T> result = new ArrayList<T>();
String sql = "SELECT * FROM " + getTableName()+ where;
try {
stm = con.createStatement();
set = stm.executeQuery(sql);
while (set.next()) {
T obj = mappSetToInstance(con, set);
result.add(obj);
}
} catch (SQLException e) {
handleError(e);
} finally {
releaseResources(false, con, stm, set);
}
return result;
}
Prof. Dr. Nikolaus Wulff
Informatik II
23
Zusammenfassung
• Datenbanken bieten dank SQL eine einheitliche
Schnittstelle für die Benutzer und den Anwendungsentwickler.
• Datenbanksysteme eignen sich für große Datenmengen und erlauben dank Indizierung effiziente
Suchabfragen.
• SQL lässt sich gut in Programme einbetten und liefert
performante Antwortszeiten.
• Viele Objektorientierte Sprachen bieten OR-Mapper,
die vom objektorientierten UML-Modell auf das
relationale E/R-Modell transformieren.
Prof. Dr. Nikolaus Wulff
Informatik II
24