Datenbanksysteme6 - MySQL DML

Datenbanksysteme
Teil 6
MySQL – DML
Die SELECT-Anweisung
Stefan Maihack Dipl. Ing. (FH)
Datum: 28.10.2005
1
DML – SELECT
einfachste SELECT-Anweisung
•
Alle Inhalte einer Tabelle holen:
SELECT * FROM land;
2
DML - SELECT
•
•
Eine SELECT-Abfrage produziert eine Ergebnismenge mit Daten aus einer Tabelle.
Die Syntax mit einem Großteil der Optionen sieht folgendermaßen aus:
SELECT
[DISTINCT]
Auswahlausdruck …
[INTO {OUTFILE | DUMPFILE} ´/Pfad/zu/Dateiname´ Exportoptionen]
[FROM Tabellenverweise]
[WHERE Suchbedingungen]
[GROUP BY {Spaltenname | Spaltenalias}
[ASC , DESC], …]
[HAVING Suchbedingungen]
[ORDER BY {Spaltenname | Spaltenalias}
[ASC | DESC, …]
[LIMIT [Offset,] Zeilenanzahl]
•
Meistens verwendet man allerdings:
SELECT Auswahlausdruck, … [FROM Tabellenname] [WHERE Suchbedingungen]
3
DML – SELECT
Im Detail
•
•
•
•
•
•
•
•
Auswahlausdruck: spezifiziert eine Liste von aufzurufenden oder auszuwertenden Elementen:
Namen von Spalten, Konstanten oder das Ergebnis der Auswertungen von Operatoren und
Funktionen (mehrere Elemente sind jeweils durch Komma zu trennen; optional können
Aliasnamen vergeben werden).
Tabellenverweise: spezifizieren eine Liste von Tabellen, in denen sich alle aufgeführten Spalten
befinden (mehrere Elemente sind jeweils durch Komma zu trennen; optional können Aliasnamen
für Tabellen und/oder Verknüpfungsbedingungen angegeben werden).
Suchbedingungen: geben die Bedingungen an, denen die Zeilen in der Ergebnismenge genügen
müssen.
DISTINCT: legt fest, dass die Ergebnismenge nur eindeutige Zeilen umfasst.
Mit der GROUP BY-Klausel lassen sich die Ergebnisse nach einer durch Spaltenname oder
Spaltenalias angegeben Spalte gruppieren; die Reihenfolge kann aufsteigend (ASC) oder
absteigend (DESC) sein.
Die HAVING-Klausel gibt an, welche zusätzlichen Verarbeitungsschritte auf die Ergebnismenge
anzuwenden sind, bevor sie an den Benutzer gesendet wird.
Mit der ORDER BY-Klausel lässt sich die Ergebnismenge nach einer durch Spaltenname oder
Spaltenalias angegeben Spalte sortieren; die Reihenfolge kann aufsteigend (ASC) oder
absteigend (DESC) sein.
Die Klausel LIMIT schränkt die Ergebnismenge auf Zeilenanzahl Zeilen ein, wobei optional Offset
Zeilen vom Beginn der Ergebnismenge her ausgelassen werden.
4
DML - SELECT
•
•
•
Aus einer SELECT-Abfrage kann man eine normale oder eine temporäre Tabelle erzeugen.
CREATE TEMPORARY TABLE tmp
SELECT abonennten_ID, name
FROM abonennten
WHERE name LIKE ´John%´;
oder mit Aliasnamen
CREATE TEMPORARY TABLE tmp
SELECT abonennten_ID AS id, name
FROM abonennten
WHERE name LIKE ´John%´;
Ergebnis des SELECT´s „SELECT * FROM tmp;“
id
2
name
John Brown
5
DML – SELECT
Wahl der Spalten die ausgegeben werden sollen
•
Meistens wird nur die folgende Form der SELECT-Anweisung verwendet:
SELECT
Auswahlausdruck,…
[FROM Tabellenname]
[WHERE Suchbedingungen]
•
Mit dem Platzhalter * ruft man die Daten aus allen Spalten einer Tabelle ab:
SELECT * FROM artikel;
•
Projektion: Oft will man nicht alle Spalten einer Tabelle ausgeben, da die Ausgabe dann zu
unübersichtlich wird. Hierzu gibt man statt des * die Spaltennamen an, die man ausgeben will.
SELECT artikelname, artikelnummer FROM artikel;
6
DML – SELECT
Die WHERE-Bedingung
•
Mit einer WHERE-Klausel kann man steuern, welche Zeilen abgerufen werden. Zum Beispiel ruft
die folgende Anweisung nur die Artikel mit einer „id“ gleich „102“ ab und zeigt die Spalten
„artikel_name“ und „preis“ an.
SELECT artikel_name, preis FROM artikel WHERE artikel_id=102;
•
Mit der SELECT-Anweisung können auch Ausdrücke ausgewertet werden, ohne dass man sich
auf eine Tabelle beziehen muss.
SELECT 12.50 *7, 1+2, ´hello world´;
7
DML – SELECT
Ausgabe der aktuellen Zeit und Datum
•
Ermittlung der aktuellen Zeit:
SELECT NOW();
8
DML – SELECT
Beispiele
•
Ausgabe aller Leder-Artikel deren Preis unter 80€ liegt und einen berechneten Abschlag von
17.5%.
SELECT artikel_name, preis, preis*(17.5/100)
FROM artikel
WHERE artikel_name LIKE ´Leder%´
AND preis < 30;
9
DML – SELECT
Beispiele
•
Ausgabe aller Zeilen einer Tabelle mit einer WHERE-Bedingung, die für alle Zeilen zutrifft.
SELECT artikel_name, preis, preis*(12.5/100)
FROM artikel WHERE 1;
•
Dies Anweisung ruft alle Zeilen ab (als hätte man die WHERE-Klausel) nicht angegeben), weil 1
die einzigste Suchbedingung ist und MySQL diesen Wert für jede Zeile zu logisch wahr auswertet.
10
DML – SELECT
Projektion
•
Eine Projektion zeigt nur die Spalten an, die in der SELECT-Anweisung angegeben wurden:
SELECT name, einwohner FROM stadt;
11
DML – SELECT
Die Ergebnismenge einer SELECT-Abfrage beeinflussen
Die folgende Aufzählung gibt die Möglichkeiten an, welche man hat, die Ergebnismenge einer
SELECT-Abfrage zu beeinflussen:
•
•
•
•
Nur eindeutige Zeilen abrufen – d.h., in der Ergebnismenge dürfen keine Zeilen mit doppelten
Werten erscheinen.
Die Ergebnismenge sortieren. Die Daten in der Ergebnismenge kann man nach einer festgelegten
Reihenfolge anordnen lassen.
Die Daten in der Ergebnismenge gruppieren.
Nur die ersten n Zeichen der gesamten Ergebnismenge abrufen oder n Zeilen beginnend ab einer
bestimmten Zeilennummer abrufen.
Mit den SQl-Elementen DISTINCT, ORDER BY, GROUP BY und LIMIT nimmt man Einfluss auf die
Ergebnismenge.
12
DML – SELECT
DISTINCT
•
Das Schlüsselwort DISTINCT weist SELECT an, nur eindeutige Zeilen in die Ergebnismenge zu
übernehmen. Doppelte Zeilen erscheinen nur einmal in der Ausgabe.
Beispiel: Obwohl es mehrere Artikel mit dem Preis 80.00 gibt, ist die Zahl 80.00 nur einmal in der
Ergebnismenge enthalten.
13
DML – SELECT
Aggregatsfunktionen
Die Ergebnismenge mit ORDER BY sortieren
•
Häufig möchte man die Ergebnismenge sortiert ausgeben. Die Zeilen kann man nach
numerischen oder Zeichenfolgenwerten und sogar nach mehreren Werten in einer festgelegten
Rangfolge sortieren.
•
Z.B. Alle Artikel nach dem Preis sortiert auflisten lassen:
14
DML – SELECT
Aggregatsfunktionen
Die Ergebnismenge mit ORDER BY sortieren
•
Beispiel: Sortieren nach dem „artikel_name“
15
DML – SELECT
Aggregatsfunktionen
Die Ergebnismenge mit ORDER BY sortieren
•
Beispiel: Sortieren nach Preis und dann nach Artikelname:
16
DML – SELECT
Aggregatsfunktionen
Die Ergebnismenge mit GROUP BY gruppieren
•
•
•
Die GROUP BY Klausel dient dazu, die Daten gruppiert zurück zu geben.
Vor allem sinnvoll bei Aggregatsfunktionen, um Informationen zu jeder einzelnen Gruppe abzurufen, statt von jeder einzelnen Zeile.
Wenn eine SELECT-Anweisung einen GROUP BY Teil aufweist, so dürfen im SELECT-Teil nur Aggregatsfunktionen (count, avg, min, …)
oder Attribute, die in der GROUP BY Anweisung namentlich genannt werden, stehen!! z.B. SELECT count(*), l_id, name FROM stadt;
(Hier kommt es zu einer Fehlermeldung)
Î Welche Zeilen sollen gezählt werden „l_id“ oder „name“? Darum der Fehler.
ACHTUNG: Hier ist die GROUP BY Klausel nicht sehr sinnvoll, da nicht mehr alle Zeilen
dargestellt werden. Für jede Altersgruppe angezeigte Zeile wird zufällig ausgewählt.
17
DML – SELECT
Aggregatsfunktionen
Die Ergebnismenge mit GROUP BY gruppieren
1.
SELECT-Anweisung:
die ersten 10 Zeilen der Tabelle
„stadt“ werden ausgegeben.
2.
SELECT-Anweisung:
Es wird nach der l_id gruppiert
und gleichzeitig die Zeilen
gezählt, wie viele l_id es
gibt.
3.
SELECT-Anweisung:
Hier wird zusätzlich noch das
Attribut „name“ mit ausgegeben.
Der Betrachter könnte meinen
es das die Anzahl (count(*))
auch für die Namen zu trifft.
Î FALSCH!!!
18
DML – SELECT
Aggregatsfunktionen
Die Ergebnismenge mit GROUP BY gruppieren
•
•
•
Hier erscheint nur die gruppierte Spalte in der Ausgabe. Dadurch ist die Ergebnismenge sinvoller.
Sortiertes Ausgeben: SELECT alt FROM artikel GROUP BY alt DESC.
Die GROUP BY Klausel verwendet man vor allem bei Aggreatsfunktionen.
19
DML – SELECT
Aggregatsfunktionen
Funktionen in einer SELECT-Anweisung verwenden
•
•
Die Funktion SUM() bildet die Summe über eine gewählte Spalte. In der Klammer wird der
Spaltenname eingegeben.
Die Funktion COUNT() kann auf die gleiche Weise eingesetzt werden. Das * bewirkt, dass nach
allen vorhandenen Zeilen durchsucht wird und diese dann gezählt werden.
20
DML – SELECT
Aggregatsfunktionen
Funktionen in einer SELECT-Anweisung verwenden
•
Folgende Funktionen können alle in einer GROUP BY-Klausel verwendet werden:
Î AVG() gibt den Mittelwert oder durchschnittlichen Wert der Daten in einer Gruppe zurück.
Î MAX() liefert den Maximalwert.
Î MIN() liefert den Minimalwert
Î STD() gibt die Standardabweichung zurück.
Î COUNT(*) zählt alle Werte innerhalb der Gruppe.
Î SUM() zählt Werte der angegebenen Spalte zusammen.
21
DML – SELECT
Die Ergebnismenge mit LIMIT einschränken
•
Es ist nicht immer notwendig, die gesamte Ergebnismenge einer SELECT-Abfrage an eine
Anwendung zurückzugeben. Manchmal benötigt man nur die erste Zeile oder eine kleinere
Teilmenge der Zeilen. In diesen Fällen ist es effizienter, die SELECT-Abfrage einzschränken.
22
DML – SELECT
SELECT mit HAVING-Klausel
•
•
•
Eine Ergebnismenge kann mit der HAVING-Klausel eingeschränkt werden.
Gelegentlich ist es erforderlich, die Ergebnismenge abhängig von bestimmten Daten der mit
Aggregatsfunktionen zusammengefassten Ergebnisse einzuschränken.
Beispiel: Welche Artikel gehören zu einem Altersbereich, die weniger als 80 € kosten.
SELECT alt, MIN(preis) FROM artikel
GROUP BY alt
HAVING MIN(preis) < 80.00;
•
•
•
•
Ergebnis:
Diese Anweisung gruppiert die Artikel nach Alter, verwendet die Aggregatsfunktion MIN(), um den
kleinsten Preis in jeder Gruppe zu ermitteln, und gibt Ergebnisse zurück, bei denen der kleinste
Preis kleiner als 80 Euro ist.
HAVING verhält sich wie eine zusätzliche WHERE-Klausel, die auf die Ergebnismenge statt auf
die Tabellen angewendet wird.
Die Suchbedingungen der HAVING-Klausel können genauso konstruiert werden wie in einer
WHERE-Klausel.
HAVING muss nach jeder GROUP BY-Klausel und vor jeder ORDER BY-Klausel stehen.
23
DML – SELECT
SELECT mit HAVING-Klausel
•
Das folgende Beispiel zeigt, wie zu realisieren ist, um die Artikel mit einem minimalen
Gruppenpreis kleiner 80.00 Euro zu finden ist:
SELECT alt, MIN(preis) FROM artikel
GROUP BY alt
HAVING MIN(preis) < 80.00;
Die Abfrage gruppiert die Daten nach dem Alter, sucht den kleinsten Preis in jeder Gruppe
und verwendet dann die HAVING-Bedingung auf den minimalen Preis an.
24
DML – SELECT
Unterabfragen
•
•
Eine Unterabfrage ist eine SELECT-Anweisung innerhalb einer SELECT-Anweisung.
Bei einer Unterabfrage kann man eine SELECT-Anweisung auf einer Ergebnismenge ausführen,
die selbst gerade erst von einer anderen SELECT-Abfrage erzeugt worden ist.
•
Beispiel:
SELECT * FROM Kinderartikel
WHERE id IN
(SELECT id FROM sichere_artikel)
•
Erklärung:
Diese Abfrage würde zuerst eine SELECT-Anweisung auf einer Tabelle sichere_artikel ausführen
(um die als >sichere< klassifizierten Artikel zu suchen). Dann startet die in der ersten Zeile
formulierte SELECT-Anweisung und wählt Datensätze aus der Tabelle kinderartikel aus, bei
denen die id in der Ergebnismenge der ersten Abfrage enthalten ist.
25
DML – SELECT
Unterabfragen
•
Beispiel:
Nenne alle EG-Mitglieder aus der TERRA-Datenbank
SELECT name FROM land
WHERE l_id IN (SELECT land FROM ist_mitglied WHERE organisation=`EG`);
Statt IN kann auch NOT IN verwendet werden, um das Ggegenstück hierzu zu
erzeugen.
26
DML – SELECT
Ergebnismengen mit UNION zusammenfassen
•
•
Mit dem Schlüsselwort UNION lassen sich zwei oder mehrere SELECT-Abfragen verbinden.
MySQL führt jede SELECT-Abfrage selbständig aus und fasst die Ergebnismenge zusammen.
SELECT Auswahlabfrage1
UNION ALL
SELECT Auswahlabfrage2
[UNION ALL]
SELECT Auswahlabfrage3
•
Man kann beliebig viele Ergebnismengen verbinden. Nach dieser Syntax lässt sich
Auswahlabfrage1 mit dem Ergebnis von Auswahlabfrage2 verbinden, das seinerseits mit dem
Ergebnis von Auswahlabfrage3 kombiniert wird, usw.
27
DML – SELECT
Ergebnismengen mit UNION zusammenfassen
Beispiel
•
Folgende Frage soll aus der TERRA-Datenbank beantwortet werden:
Welche Gewässer gibt es in der Datenbank: Hierzu sind die Tabellen „See“ und „Fluss“
abzufragen.
SELECT name, ´(See)` FROM see
UNION ALL
SELECT name, `(Fluss)` FROM fluss;
•
Als Ergebnis werden alle Flüsse aus der Tabelle „fluss“ und alle Seen aus der Tabelle „see“
ausgegeben.
•
Normalerweise arbeitet jede SELECT-Abfrage so, als hätte man das Schlüsselwort DISTINCT
angegeben. In der Ergebnismenge erscheinen deshalb nur eindeutige Zeilen.
Wenn man UNION durch das Schlüsselwort ALL ergänzt, liefert die Anweisung alle abgerufenen
Zeilen zurück.
•
28
DML – DELETE
Datensätze löschen
•
•
Die DELETE-Anweisung dient dazu, Datensätze aus einer Tabelle zu löschen:
Syntax:
DELETE [LOW_PRIORITY] [QUICK] FROM tabellenname
[WHERE Suchbedingungen]
[ORDER BY Spaltenliste]
[LIMIT Zeilenanzahl]
•
•
Aufgrund der ähnlichen Syntax zur SELECT-Anweisung, wird die DELETE-Anweisung an dieser
Stelle behandelt. Es gibt eine WHERE-Klausel, deren Suchbedingungen die Löschoperationen
auf die entsprechenden Zeilen einschränken (genau wie bei der SELECT-Anweisung).
Beispiel: Löschen der Datensätze bei denen die id=105 ist:
DELETE FROM artikel WHERE id=105;
•
Beispiel: Löschen der Datensätze, in denen in der Spalte ‚name‘ die Anfangszeichen ‚toy%‘;
vorkommen:
DELETE FROM artikel WHERE name LIKE ´toy%`;
29
Übungen
TERRA-Datenbank benutzen
1.
2.
3.
4.
5.
Schreiben Sie eine SELECT-Abfrage, die Daten (alle Spalten) aus einer Tabelle „stadt“ abruft,
nach dem Städtenamen sortiert und die Ergebniszeilen 31 bis 40 anzeigt.
Schreiben sie eine SELECT-Anweisung, die Städtenamen aus der Tabelle „stadt“ anruft und
die vorhandenen Städtenamen nur jeweils einmal auflistet.
Richtig oder Falsch: SELECT kann nur Daten aus Tabellen abrufen?
Richtig oder Falsch: In der selben SELECT-Anweisung dürfen ORDER BY und GROUP BY
nicht zusammen geschrieben werden.
Richtig oder Falsch: Eine HAVING-Klausel wird nach einer WHERE-Klausel abgearbeitet.
30