Folien zu relationalen Datenbanken

Michaela Weiss | 01. April 2015
Lerneinheit 4:
Relationale Datenbanken
am Beispiel von MySQL
Seite 2
Datenbanken
Allgemeines:
Datenbank(management)systeme ermöglichen die Speicherung großer
Datenmengen
Kennzeichen relationaler Datenbanken:
• Alle Tabellen sind gleichberechtigt
• Spalten (Attribute) werden wie die Tabellen über ihren Namen
angesprochen (es gibt nicht die 2. Spalte)
• Zeilen einer Tabelle (Datensätze) bilden eine Menge, d.h. es gibt keine
zwei identischen Zeilen
• Reihenfolge der Zeilen spielt keine Rolle (es gibt nicht die 5. Zeile)
• Zu jeder Tabelle gibt es mindestens einen Schlüssel und beliebig viele
Nicht-Schlüssel-Attribute
Schlüssel = minimale Spaltenkombination, die eine Zeile eindeutig
identifiziert
• Bei mehreren Schlüsseln wird einer als Primärschlüssel ausgezeichnet
Seite 3
Datenbanken
Kennzeichen relationaler Datenbanken:
• Beziehungen zwischen Tabellen werden über Wertgleichheit in
äquivalenten Spalten hergestellt
Mitarbeiter:
Fremd-
PersNr
Name
Vorname
Titel
AbtNr
…
…
…
…
…
Abteilung:
AbtNr
Name
…
…
Seite 4
Datenbanken
Zugriff auf unsere relationalen Datenbanken:
• Zugriff auf unsere Rechner (z.B. theseus.mathematik.uni-ulm.de):
• von Unix-artigen Systemen (auch Mac OSX) direkt über ein Terminal
• Von Windows über PuTTY
• Wichtige Datenbankbefehle:
• Starten unseres Datenbankmanagementsystems (über ein
spezielles Skript):
/home/demodb/bin/demodb
• Anzeige aller Datenbanken:
SHOW DATABASES;
• Auswahl einer Datenbank:
USE Datenbankname;
• Anzeige aller Tabellen:
SHOW TABLES;
• Detailinformation zu einer Tabelle:
DESCRIBE Tabellenname;
Seite 5
Datenbankabfragen Teil 1
Übungsaufgaben:
1. Alle Daten der CountryLanguage-Tabelle
SELECT * FROM CountryLanguage;
2. Von allen Datensätzen der CountryLanguage-Tabelle: CountryCode,
Language und Percentage
SELECT CountryCode, Language, Percentage
FROM CountryLanguage;
Projektion
3. Name und Einwohnerzahl der Städte in den USA (CountryCode = USA)
SELECT Name, Population FROM City
WHERE CountryCode = “USA“;
Bedingte
Ausgabe
Seite 6
Datenbankabfragen Teil 1
Übungsaufgaben:
4. Name aller Städte, deren Namen mit X beginnt und deren Einwohnerzahl
größer als 500.000 ist
SELECT Name FROM City WHERE Name LIKE “X%“
AND Population > 500000;
5. Name aller gespeicherten deutschen Städte (Germany)
SELECT City.Name FROM City, Country
WHERE City.CountryCode = Country.Code
AND Country.Name = "Germany";
City
Country
ID Name
CountryCode District Population
…
…
…
…
Code Name
Continent Region
…
…
…
…
…
Join
Seite 7
Datenbankabfragen Teil 1
Präsenzaufgaben:
1. Ges.: Liste aller Ländernamen inklusive Name des Staatsoberhaupts
SELECT Name, HeadOfState FROM Country;
2. Ges: Liste aller Länder, deren Name mit D beginnt
(Ausgabe: Code und Name)
SELECT Name FROM Country WHERE Name LIKE “D%"
3. Ges.: Liste aller Städte (Name und Einwohnerzahl), die in den
Vereinigten Staaten (United States) sind und mehr als 500.000
Einwohner haben
SELECT City.Name, City.Population FROM City, Country
WHERE City.CountryCode = Country.Code
AND Country.Name = “ United States“
AND City.Population > 500000;
Seite 8
Datenbankabfragen Teil 2
Übungsaufgaben:
Name und Einwohnerzahl aller deutschen und italienischen Städte
in mehreren Queries:
Select Code FROM Country WHERE Name = “Germany“; (DEU)
Select Code FROM Country WHERE Name = “Italy“; (ITA)
 SELECT Name, Population FROM City WHERE
CountryCode IN (“DEU“, “ITA“);
in einem Query:
a.) Subquery:
SELECT Name, Population FROM City WHERE
CountryCode IN (SELECT Code FROM Country WHERE
Name = “Germany“ OR Name = “Italy“);
b.) Join:
SELECT S.Name, S.Population FROM City S, Country L
WHERE S.CountryCode = L.Code
AND L.Name IN (“Germany“, “Italy“);
Seite 9
Datenbankabfragen Teil 2
Präsenzaufgaben:
1. Name und Einwohnerzahl des Landes, in dem sich die Stadt Riga befindet.
SELECT L.Name, L.Population FROM City S, Country L
WHERE S.CountryCode = L.Code
AND S.Name = “Riga“;
2. Liste aller Ländernamen mit Name der Hauptstadt und (Landes-)Fläche.
SELECT L.Name, S.Name, L.SurfaceArea
FROM Country L, City S
WHERE S.ID = L.Capital;
Seite 10
Datenbankabfragen Teil 2
Präsenzaufgaben:
1. Name und Einwohnerzahl des Landes, in dem sich die Stadt Riga befindet.
SELECT L.Name, L.Population FROM City S, Country L
WHERE S.CountryCode = L.Code
AND S.Name = “Riga“;
2. Liste aller Ländernamen mit Name der Hauptstadt und (Landes-)Fläche.
SELECT L.Name AS Land, S.Name AS Hauptstadt, L.SurfaceArea
FROM Country L, City S
WHERE S.ID = L.Capital;
gewünschtes Aussehen:
Seite 11
Datenbankabfragen Teil 2
Präsenzaufgaben:
3. Stadtinformationen aller Städte, die zu Ländern gehören, welche von
Elisabeth II regiert werden.
a.) mittels Subquery
SELECT * FROM City WHERE CountryCode IN
(SELECT Code FROM Country WHERE HeadOfState = “Elisabeth II“)
ORDER BY Name;
b.) mittels Join
SELECT S.* FROM City S, Country L
WHERE S.CountryCode = L.Code AND L.HeadOfState = “Elisabeth II“
ORDER BY S.Name;
Seite 12
Berechnungen
1. Zählen  COUNT(…)
Bsp.: Wie viele deutsche Städte sind gespeichert?
mysql> SELECT * FROM City WHERE CountryCode = “DEU“;
mysql> SELECT COUNT(*) FROM City WHERE CountryCode = “DEU“;
Seite 13
Berechnungen
2. Summieren  SUM(…)
Bsp: Gesamteinwohnerzahl der gespeicherten deutschen Städte
mysql> SELECT SUM(Population) FROM City
WHERE CountryCode = “DEU“;
Seite 14
Berechnungen
3. Maximum/Minimum/Durchschnitt  MIN(…)/MAX(…)/AVG(…)
Bsp: Größte Einwohnerzahl einer deutschen Stadt
mysql> SELECT MAX(Population) FROM City
WHERE CountryCode = "DEU";
Seite 15
Berechnungen
4. Limitierung der Ausgabe  LIMIT … + ORDER BY (ASC/DESC)
Bsp: Ausgabe aller städtespezifischen Daten der drei deutschen Städte
mit der kleinsten Einwohnerzahl
mysql> SELECT * FROM City WHERE CountryCode = "DEU"
ORDER BY Population LIMIT 3;
Seite 16
Berechnungen
5. Rechnen
Bsp: Ausgabe der Sprachen, die in Belgien gesprochen werden, unter
Angabe der prozentualen und absoluten Anzahl der Sprecher
mysql> SELECT * FROM CountryLanguage WHERE CountryCode = "BEL";
mysql> SELECT Population FROM Country WHERE Code = "BEL";
Seite 17
Berechnungen
5. Rechnen
Bsp: Ausgabe der Sprachen, die in Belgien gesprochen werden, unter
Angabe der prozentualen und absoluten Anzahl der Sprecher
mysql> SELECT * FROM CountryLanguage WHERE CountryCode = "BEL";
mysql> SELECT Population FROM Country WHERE Code = "BEL";
mysql> SELECT L.Language, L.Percentage,
L.Percentage/100*C.Population
FROM CountryLanguage L, Country C
WHERE L.CountryCode = C.Code AND L.CountryCode = "BEL";
Seite 18
Berechnungen
5. Runden  ROUND(…)
Bsp: Ausgabe der Sprachen, die in Belgien gesprochen werden, unter
Angabe der prozentualen und auf 2 Nachkommastellen gerundeten
absoluten Anzahl der Sprecher
mysql> SELECT L.Language, L.Percentage,
ROUND(L.Percentage/100*C.Population,2)
FROM CountryLanguage L, Country C
WHERE L.CountryCode = C.Code AND L.CountryCode = "BEL";
Seite 19
Übungsaufgaben
1. Durchschnittliche Bevölkerungszahl der deutschen Städte (Code: DEU)
auf ganze Zahl gerundet?
mysql> SELECT ROUND(AVG(Population)) FROM City WHERE
CountryCode = "DEU";
2. Anzahl der offiziellen Amtssprachen Belgiens (Code: BEL)?
mysql> SELECT COUNT(*) FROM CountryLanguage WHERE
CountryCode = "BEL" AND IsOfficial = "T";
3. Absolute Anzahl der Deutsch-Sprecher (German) in Belgien?
mysql> SELECT Percentage/100*Population
FROM CountryLanguage, Country
WHERE CountryCode = Code
AND CountryCode = "BEL" AND Language = "GERMAN";