select - Softwarelabor (SWL)

Rückblick
§ Structured Query Language (SQL) als standardisierte
Anfragesprache für relationale Datenbanken
§ Data Definition Language zur Schemadefinition
(z.B. CREATE TABLE zum Anlegen von Tabellen)
§ Data Query Language zum Formulieren von Anfragen
(SELECT ... FROM ... WHERE ... ORDER BY ...)
Datenbanken / Kapitel 4: Structured Query Language (SQL)
35
Mathematische Funktionen
§ SQL unterstützt die mathematischen Operatoren (+, *, -, /)
und eine Vielzahl mathematischer Funktionen, z.B.
§ ABS(A): Betrag
§ SIGN(A): Vorzeichen
§ SQRT(A): Quadratwurzel
§ FLOOR(A): Abrunden auf ganze Zahl
§ CEIL(A): Aufrunden auf ganze Zahl
§ MAX(A,B): Maximum der Attribute A und B
§ MIN(A,B): Minimum der Attribute A und B
§ …
Datenbanken / Kapitel 4: Structured Query Language (SQL)
36
Sonstige Funktionen
§ SQL unterstützt zahlreiche Funktionen für
nichtnumerische Datentypen, etwa für Zeichenketten
§ LOWER(A): Zeichenkette in Kleinbuchstaben
§ UPPER(A): Zeichenkette in Großbuchstaben
§ LENGTH(A): Länge der Zeichenkette
§ SUBSTRING(A, start, end): Ausschnitt der Zeichenkette
§ TRIM(A): Zeichenkette ohne umgebende Leerzeichen
Datenbanken / Kapitel 4: Structured Query Language (SQL)
37
Aggregatfunktionen
§ SQL unterstützt eine Vielzahl von Aggregatfunktionen,
die auf die Werte eines Attributs in mehreren Tupeln
angewendet werden können, z.B.:
§ MIN(A): minimaler Wert für Attribut A
§ MAX(A): maximaler Wert für A
§ AVG(A): durchschnittlicher Wert für A
§ SUM(A): Summe der Werte für A
§ COUNT(*): Anzahl Tupel
§ COUNT(A): Anzahl Tupel mit Wert ungleich NULL für A
§ COUNT(DISTINCT A): Anzahl Werte ungleich NULL für A
Datenbanken / Kapitel 4: Structured Query Language (SQL)
38
Aggregatfunktionen
§ Beispiel:
§ MIN(A) = 1
§ MAX(A) = 3
§ AVG(A) = 8 / 4 = 2
§ COUNT(*) = 5
§ COUNT(A) = 4
A
1
2
NULL
2
3
§ COUNT(DISTINCT A) = 3
Datenbanken / Kapitel 4: Structured Query Language (SQL)
39
Aggregatfunktionen
§ Beispiel: Maximale, minimale und durchschnittliche
Semesteranzahl innerhalb Studenten der Physik
1
2
3
4
5
SELECT MAX ( Semester ) ,
MIN ( Semester ) ,
AVG ( Semester )
FROM Studenten
WHERE Fach = ’ Physik ’
Datenbanken / Kapitel 4: Structured Query Language (SQL)
40
Selektionsbedingungen
§ Selektionsbedingungen, in WHERE-Klausel, erlauben
mehr als Vergleich zwischen Attribut und Konstante
§ Vergleichsoperatoren (=, <> oder !=, >, <, >=, <=)
1
2
3
SELECT Vorname , Name
FROM Studenten
WHERE Semester > 10
§ Vergleiche zwischen Attributen
1
2
3
SELECT *
FROM Bestellungen
WHERE Bestelldatum = Lieferdatum
Datenbanken / Kapitel 4: Structured Query Language (SQL)
41
Selektionsbedingungen
§ Boole‘sche Operatoren (NOT, OR, AND) mit der Rangfolge
NOT vor AND vor OR und ggf. Klammerung
§ Beispiel: Studenten weder von Informatik noch Physik
1
2
3
SELECT *
FROM Studenten
WHERE Fach != ’ Informatik ’ AND Fach != ’ Physik ’
oder durch Anwendung von De Morgan
1
2
3
SELECT *
FROM Studenten
WHERE NOT ( Fach = ’ Informatik ’ OR Fach = ’ Physik ’)
Datenbanken / Kapitel 4: Structured Query Language (SQL)
42
Mustervergleiche in Zeichenketten
§ Textuelle Attribute kann man mittels LIKE gegen ein
Muster vergleichen; hierbei dient % als Platzhalter
(wildcard) und repräsentiert kein oder mehrere Zeichen
§ Beispiel: Vorlesungen mit „Grundlagen“ im Titel
1
2
3
SELECT *
FROM Vorlesungen
WHERE Titel LIKE ’% Grundlagen % ’
wenn auch „Informatikgrundlagen“ gefunden werden soll
1
2
3
SELECT *
FROM Vorlesungen
WHERE LOWER ( Titel ) LIKE ’% grundlagen % ’
Datenbanken / Kapitel 4: Structured Query Language (SQL)
43
Wertemengen
§ Mittels des Kommandos IN kann überprüft werden, ob der
Wert eines Attributs in einer gegebenen Wertemenge liegt
§ Beispiel: Studenten mit Vornamen Max oder Moritz
1
2
3
SELECT *
FROM Studenten
WHERE Vorname IN ( ’ Max ’ , ’ Moritz ’)
§ Die Wertemenge darf auch mittels einer
SQL-Unterabfrage bestimmt werden,
dazu später mehr
Datenbanken / Kapitel 4: Structured Query Language (SQL)
44
Überprüfung auf NULL-Werte
§ Mittels der Kommandos IS NULL und IS NOT NULL kann
überprüft werden, ob der Wert eines Attributs NULL ist
§ Beispiel: Professoren mit unbekanntem Fach
1
2
3
SELECT *
FROM Professoren
WHERE Fach IS NULL
§ MS SQL Server bietet zudem die Funktion ISNULL(a,b),
welche den Wert b annimmt, wenn a NULL ist und
andernfalls den Wert a belässt
§ SQLite kennt eine entsprechende Funktion IFNULL(a,b)
Datenbanken / Kapitel 4: Structured Query Language (SQL)
45
4.3 Anfragen über mehrere Tabellen
§ SQL unterstützt verschiedene Kommandos, um zwei oder
mehr Tabellen miteinander zu verknüpfen; hierzu
werden mehrere Tabellen in der FROM-Klausel erwähnt und
Joinprädikate in der WHERE-Klausel angegeben
§ SQL kennt zudem eine alternative Syntax, die dazu dient,
Joinprädikate von Selektionsprädikaten zu trennen
Datenbanken / Kapitel 4: Structured Query Language (SQL)
46
Kartesisches Produkt
§ Kartesisches Produkt zweier Tabellen lässt sich durch
deren Nennung in der FROM-Klausel oder durch
Verwendung des CROSS JOIN Kommandos ermitteln
§ Beispiel: Kombinationen von Studenten und Professoren
1
2
SELECT *
FROM Studenten , Professoren
oder
1
2
SELECT *
FROM Studenten CROSS JOIN Professoren
Datenbanken / Kapitel 4: Structured Query Language (SQL)
47
Natürlicher Join
§ Natürlicher Join zweier Tabellen lässt sich durch deren
Nennung in der FROM-Klausel und Angabe der
gemeinsamen Attribute in der WHERE-Klausel oder durch
Verwendung des NATURAL JOIN Kommandos ermitteln
§ Beispiel: Vorlesungen mit zugehörigen Professoren
SELECT *
FROM Vorlesungen , Professoren
WHERE Vorlesungen . PersNr = Professoren . PersNr
1
2
3
oder
1
2
SELECT *
FROM Vorlesungen NATURAL JOIN Professoren
Datenbanken / Kapitel 4: Structured Query Language (SQL)
48
Allgemeiner Join
§ Allgemeiner Join zweier Tabellen lässt sich durch deren
Nennung in der FROM-Klausel und Angabe des
Joinprädikats in der WHERE-Klausel oder durch
Verwendung des JOIN ON Kommandos ermitteln
§ Beispiel: Professoren und Studenten gleichen Namens
1
2
3
SELECT *
FROM Professoren , Studenten
WHERE Professoren . Name = Studenten . Name
oder
1
2
3
4
SELECT
FROM
JOIN
ON
*
Professoren
Studenten
Professoren . Name = Studenten . Name
Datenbanken / Kapitel 4: Structured Query Language (SQL)
49
Mengenoperationen
§ Die Kommandos UNION, INTERSECT und EXCEPT
bilden die Mengenoperationen , ∩ und ab und
dürfen nur auf Tabellen mit kompatiblen Schemata
angewendet werden
§ Beispiel: Namen, die nur bei Studenten, aber nicht bei
Professoren vorkommen
1
2
3
4
5
SELECT
FROM
EXCEPT
SELECT
FROM
Datenbanken / Kapitel 4: Structured Query Language (SQL)
Name
Studenten
Name
Professoren
50
Umbenennung
§ Taucht die gleiche Tabelle mehrfach in einem SQL
Kommando auf, können die einzelnen Auftreten
benannt werden
§ Beispiel: Paare von Studenten mit gleichem Fach
1
2
3
SELECT *
FROM Studenten s1 , Studenten s2
WHERE s1 . Fach = s2 . Fach
Datenbanken / Kapitel 4: Structured Query Language (SQL)
51
4.4 Datenmanipulation
§ Data Manipulation Language (DML) stellt Befehle zum
Einfügen, Ändern und Löschen von Tupeln bereit
§ RDBMS überprüft beim Einfügen, Ändern und Löschen
bestehende Integritätsbedingungen (z.B. definiert durch
Primärschlüssel oder Fremdschlüssel) und
verweigert bei Verletzung die Ausführung
Datenbanken / Kapitel 4: Structured Query Language (SQL)
52
Einfügen von Daten
§ Tupel lassen sich mittels des INSERT INTO Kommandos
in eine Tabelle einfügen
§ Beispiel: Füge Carla Columna als Professorin
für Literatur mit PersNr 198606 hinzu
1
2
INSERT INTO Professoren
VALUES (198606 , ’ Carla ’ , ’ Columna ’ , ’ Literatur ’)
mit Reihenfolge der Attribute gemäß Schema, alternativ
1
2
INSERT INTO Professoren ( Name , Fach , Vorname , PersNr )
VALUES ( ’ Columna ’ , ’ Literatur ’ , ’ Carla ’ , 198606)
mit Angabe der Attributreihenfolge
Datenbanken / Kapitel 4: Structured Query Language (SQL)
53
Ändern von Daten
§ Bereits existierende Tupel lassen sich mittels des UPDATE
Kommandos verändern; dieses hat folgende Form
1
2
3
UPDATE < Tabelle >
SET < Attribut > = < Wert > , ...
WHERE < Bedingungen >
§ Beispiel: Ändere Name von Professor 198606 nach Cole
1
2
3
UPDATE Professoren
SET Name = ’ Cole ’
WHERE PersNr = 198606
§ Beispiel: Erhöhe Semester aller Studenten
1
2
UPDATE Studenten
SET Semester = Semester + 1
Datenbanken / Kapitel 4: Structured Query Language (SQL)
54
Löschen von Daten
§ Bereits existierende Tupel lassen sich mittels des DELETE
Kommandos löschen; dies hat folgende Form
1
2
DELETE FROM < Tabelle >
WHERE < Bedingungen >
§ Beispiel: Lösche Studenten der Datenverarbeitung
1
2
DELETE FROM Studenten
WHERE Fach = ’ Da tenver arbeit ung ’
Datenbanken / Kapitel 4: Structured Query Language (SQL)
55
4.5 SQLite
§ Im Softwarelabor verwenden wir MS SQL Server; zum
Üben zu Hause stellen wir unsere Beispieldatenbank
für SQLite zur Verfügung
§ Datenbanken und Software für Windows, Mac und Linux
stehen zum Download auf der Vorlesungswebseite bereit
Datenbanken / Kapitel 4: Structured Query Language (SQL)
56
SQLite Kommandos
§ Starten von SQLite mit der Beispieldatenbank
§ Mac / Linux: ./sqlite3 dbs-unix.sqlite
§ Windows: ./sqlite3.exe dbs-windows.sqlite
§ Anzeigen verfügbarer Tabellen
§ .tables
§ Anzeigen des Schemas einer Tabelle
§ .schema <Tabelle>
Datenbanken / Kapitel 4: Structured Query Language (SQL)
57
SQLite Kommandos
§ Anzeige von Attributnamen an-/ausschalten
§ .headers on|off
§ Trennzeichen für Spalten ändern
§ .separator ‘|‘ (trennt Spalten durch Vertikalstrich)
§ Hilfe anzeigen
§ .help
§ Beenden
§ .quit
Datenbanken / Kapitel 4: Structured Query Language (SQL)
58
SQLite Kommandos
§ SQL Kommandos in SQLite durch Semikolon beendet
§ SELECT * FROM Kunden;
Datenbanken / Kapitel 4: Structured Query Language (SQL)
59
Anfrageübung Versandhandel
§ Welche Artikel sind gemäß Ihrer Beschreibung ein Bausatz
§ Welche Bestellungen sind von Kunden namens Becker
§ Welche Kunden namens Gates wohnen nicht in Homburg
§ Wieviele Artikel haben Kunden namens Schmidt bestellt
§ Welche Paare von Artikeln wurde je zusammen bestellt
Datenbanken / Kapitel 4: Structured Query Language (SQL)
60
Anfrageübung Versandhandel
§ Welche Artikel sind gemäß Ihrer Beschreibung ein Bausatz
SELECT *
FROM Artikel
WHERE LOWER ( Bezeichnung ) LIKE ’% bausatz % ’
1
2
3
§ Welche Bestellungen sind von Kunden namens Becker
1
2
3
4
SELECT
FROM
WHERE
AND
1
2
3
*
Bestellungen , Kunden
Bestellungen . Kunden_Nr = Kunden . Kunden_Nr
Kunden . Name = ’ Becker ’
SELECT *
FROM Bestellungen NATURAL JOIN Kunden
WHERE Kunden . Name = ’ Becker ’
Datenbanken / Kapitel 4: Structured Query Language (SQL)
61
Anfrageübung Versandhandel
§ Welche Kunden namens Gates wohnen nicht in Homburg
1
2
3
SELECT *
FROM Kunden
WHERE Name = ’ Gates ’ AND Wohnort <> ’ Homburg ’
§ Wieviele Artikel haben Kunden namens Schmidt bestellt
1
2
3
4
5
SELECT SUM ( Anzahl )
FROM Bestellpositionen , Bestellungen , Kunden
WHERE Bestellpositionen . Bestell_Nr = Bestellungen . Bestell_Nr
AND Bestellungen . Kunden_Nr = Kunden . Kunden_Nr
AND Kunden . Name = ’ Schmidt ’
Datenbanken / Kapitel 4: Structured Query Language (SQL)
62
Anfrageübung Versandhandel
§ Welche Paare von Artikeln wurden je zusammen bestellt
1
2
3
4
5
6
7
8
9
SELECT DISTINCT a1 . Artikel_Nr , a2 . Artikel_Nr
FROM Artikel a1 ,
Bestellposition en b1 ,
Bestellposition en b2 ,
Artikel a2
WHERE a1 . Artikel_Nr = b1 . Artikel_Nr
AND b1 . Bestell_Nr = b2 . Bestell_Nr
AND b2 . Artikel_Nr = a2 . Artikel_Nr
AND a1 . Artikel_Nr < a2 . Artikel_Nr
Datenbanken / Kapitel 4: Structured Query Language (SQL)
63
Zusammenfassung
§ SQL bietet viele Möglichkeiten zur Anfrageformulierung
§ mathematische Funktionen (z.B. ABS(A) und SIGN(A))
§ Aggregatfunktionen (z.B. MIN(A) und SUM(A))
§ Boole‘sche Operatoren (AND, OR, EXCEPT)
§ Verknüpfungen von mehreren Tabellen (JOINs)
§ Data Manipulation Language (DML) zum Einfügen,
Ändern und Löschen von Tupeln in Tabellen
§ SQLite als RDBMS zum Üben zu Hause
Datenbanken / Kapitel 4: Structured Query Language (SQL)
64
Literatur
[1]
A. Kemper und A. Eickler: Datenbanksysteme – Eine
Einführung, De Gruyter Oldenbourg, 2013 (Kapitel 4)
[2]
G. Saake, K.-U. Sattler und A. Heuer:
Datenbanken - Konzepte und Sprachen,
mitp Professional, 2013 (Kapitel 7)
Datenbanken / Kapitel 4: Structured Query Language (SQL)
65