MySQL-Zertifizierung, Kapitel 9 ohne 9.6

9.1 Using SELECT to Retrieve Date

Der SELECT-Befehl (das ist wahrscheinlich für 99% der Leser nichts Neues) wird vor allem dazu benutzt, um Datensätze aus den Tabellen zu lesen und anzuzeigen. Hier eine Übersicht der möglichen Optionen für den SELECT-Befehl:

SELECT spalte1, spalte2, spalte3
FROM tbl_1
WHERE id <=500
GROUP BY gruppe
HAVING ausdruck
ORDER BY spalte1
LIMIT 10;

9.2 Specifying Which Columns To Receive

Viele glauben, dass der SELECT-Befehl ausschließlich dazu benutzt wird, um Datensätze aus Tabellen zu lesen. Das ist aber falsch, denn SELECT kann viel mehr. Grundsätzlich ist es die FROM Option nicht zwingend erforderlich, um Datensätze einer Tabelle auszulesen. Folgendes Beispiel zeigt grundlegende Funktionen von SELECT:

mysql> SELECT 1+10, REPEAT("Hallo", 2), 3*9;
+------+--------------------+-----+
| 1+10 | REPEAT("Hallo", 2) | 3*9 |
+------+--------------------+-----+
| 11 | HalloHallo | 27 |
+------+--------------------+-----+

In der 1. Spalte sieht man das Ergebnis aus 1+10. Die zweite Spalte zeigt den Begriff „Hallo“ zwei Mal wiederholt. Die dritte Spalte zeigt das Ergebnis aus der Multiplikation aus 3 mal 9.


Eine einfache Abfrage aus einer Tabelle sieht folgendermaßen aus:

mysql> SELECT Chip_id, Chiptakt, Speichertakt, Speicher FROM gpu LIMIT 1;
+---------+----------+--------------+----------+
| Chip_id | Chiptakt | Speichertakt | Speicher |
+---------+----------+--------------+----------+
| 68 | 612 | 2160 | 768 |
+---------+----------+--------------+----------+

In diesem Fall werden nur bestimmte Spalten ausgelesen. LIMIT bedeutet, dass maximal 1 Datensatz (Zeile) ausgelesen wird. Um alle Spalten auszulesen, kann man das * verwenden:

mysql> SELECT * FROM gpu LIMIT 1;

9.2.1 Renaming Retrieved Columns

Standardmäßig wird bei einer SELECT-Abfrage der Name der Spalte angezeigt. Diesen Namen kann man mithilfe von AS umbenennen:

# Spalte umbenennen
mysql> SELECT Speicher AS 'RAM In MB' FROM gpu LIMIT 1;
+-----------+
| RAM In MB |
+-----------+
| 768 |
+-----------+

# Spalte umbenennen (Kurzform ohne AS)
mysql> SELECT Speicher 'RAM In MB' FROM gpu LIMIT 1;

Das Schlüsselwort AS muss nicht angegeben werden.

9.2.2 Identifying the Database Containing a Table

Wenn man nicht mit dem Befehl USE database eine Verbindung zu einer Datenbank aufgebaut hat, kann MySQL logischerweise nicht die richtige Tabelle finden. Dazu kann man Datenbank zusätzlich angeben:

mysql> SELECT * FROM db001.gpu LIMIT 1;

Es ist zwar möglich, eine Standard-Datenbank anzugeben, aber wenn man ohne Datenbank-Verbindung eine Tabelle aus einer anderen Datenbank ansprechen möchte, muss man oben gezeigtes Prinzip trotzdem anwenden.

9.3 Specifying Which Rows to Retrieve

Mit dem Schlüsselwort WHERE kann man Kriterien festlegen, welche Datensätze ausgelesen werden sollen. Die wichtigsten Ausdrücke sind = (gleich), < (kleiner) und > (größer), welche auch kombiniert werden können: <= (kleiner gleich) und >= (größer gleich). Des Weiteren kann man die Suchkriterien mit AND und OR genauer eingrenzen. Folgendes Beispiel soll dies näher erläutern.

mysql> SELECT Chip_id, Speichertakt FROM gpu WHERE Speichertakt >= 100 AND Speichertakt <= 115 OR Speichertakt > 2000;
+---------+--------------+
| Chip_id | Speichertakt |
+---------+--------------+
| 68 | 2160 |
| 45 | 2020 |
| 25 | 110 |
| 32 | 103 |
| 10 | 100 |
| 8 | 115 |
| 7 | 100 |
| 30 | 100 |
| 18 | 100 |
| 29 | 100 |
| 30 | 100 |
| 29 | 100 |
| 44 | 2200 |
+---------+--------------+

Die Datensätze müssen folgende Kriterien erfüllen:


1.) Speichertakt muss größer gleich 100 und kleiner gleich 115 sein
2.) Oder der Speichertakt ist größer als 2000


Die Kriterien kann man mit der runden Klammer gruppieren. Um beispielsweise Grafikkarten anzeigen zu lassen, dessen Speichertakt zwischen 100-200 und 400-500 liegt, muss man die SELECT-Anweisung folgendermaßen schreiben:

mysql> SELECT Chip_id, Speichertakt FROM gpu 
-> WHERE (Speichertakt >= 100 AND Speichertakt <= 115)
-> OR (Speichertakt >= 400 AND Speichertakt <= 500);

9.3.1 Using ORDER BY to Sort Query results

Mit dem Schlüsselwort ORDER BY kann man festlegen, nach welcher Spalte die Datensätze sortiert werden sollen. Außerdem können ASC (aufsteigend, „von klein nach groß“) und DESC (absteigend, von „groß nach klein“) benutzt werden. Standardmäßig verwendet MySQL ASC, sortiert als „von klein nach groß“.

# Sortieren nach vorname, dann nach nachname
SELECT id, vorname, nachname FROM tbl ORDER BY vorname, nachname;

# vorname absteigend, nachname aufsteigend sortieren
SELECT id, vorname, nachname FROM tbl ORDER BY vorname DESC, nachname ASC;

Wie wendet man ORDER BY an, wenn man die Spalte bei der Ausgabe umbenennt? Man nimmt einfach den Alias oder gibt die Spaltenzahl an:

# Sortieren in Verbindung mit Alias-Spalten
SELECT vorname AS abc, nachname AS xyz FROM tbl ORDER BY abc, xyz;

# 1 steht für erste, 2 für zweite Spalte
SELECT vorname AS abc, nachname AS xyz FROM tbl ORDER BY 1, 2;

9.3.2 The Natural Sort Order of Data Types

Das in Punkt 9.3.1 beschriebene Schlüsselwort ORDER BY wird dazu verwendet, um die Spalte zu definieren, nach welcher sortiert werden soll. Außerdem kann man die Richtung festlegen (aufsteigend, absteigend).


In diesem Abschnitt wird die natürliche Sortierreihenfolge beschrieben und diese sich auf die Ausgabe auswirkt. Kurzgefasst: nach welchen Kriterien wird überhaupt sortiert? Wie bereits besprochen ist die Sortierreihenfolge vom jeweiligen Datentyp abhängig, die der Spalte zugewiesen wurde. Es gibt beispielsweise nicht-binäre und binäre Datentypen, die MySQL-intern unterschiedlich sortieren.


Man kann natürliche Sortierreihenfolge auf folgende Datentypen festlegen:


- Numerische Spalten: werden nach dem Zahlenwert sortiert
- Tempöräre Spalten: werden nach Zeit sortiert
- Spalten mit Zeichenketten (string): Hier ist die Reihenfolge abhängig, ob es sich um einen binären oder nicht binären Datentyp handelt. Außerdem ist die festgelegte Collation (Sortierreihenfolge) entscheidend.


Beispiel: CHAR-Spalte, non-case-sensitiv

SELECT spalte1 FROM tbl ORDER BY spalte1;

# Ausgabe in Reihenfolge: aAaaBbbB

In diesem Beispiel wird die Groß- und Kleinschreibung ignoriert. Anders sieht das Ergebnis aus, wenn man eine case-sensitive Sortierreihenfolge:

SELECT spalte1 FROM tbl ORDER BY spalte1 COLLATION latin1_general_cs;

# Ausgabe in Reihenfolge: AaaaBBbb

Nun wird auch die Groß- und Kleinschreibung beachtet. Was passiert aber, wenn man eine binäre Sortierreihenfolge verwendet?

SELECT spalte1 FROM tbl ORDER BY spalte1 COLLATION latin1_bin;

# Ausgabe in Reihenfolge: ABBaaabb

Bei der Ausgabe werden zuerst die Groß-, dann die Kleinbuchstaben angezeigt. Das ist folgendermaßen zu erklären. Bei einem Binär-Datentyp wird jedem Zeichen intern ein Wert zugewiesen. In diesem Bsp. hat jeder Buchstabe einen internen, individuellen Wert, über den dieser angesprochen wird. Das könnte intern ungefäh so aussehen: A=1, B=2, C=3,...,a=27,b=28,c=29, usw... Die Ausgabe wird nun entsprechend nach den Werten sortiert.


Sortierreihenfolge bei ENUM

Bei einer eigens angelegten Aufzählung mit dem Typ ENUM ist die Sortierreihenfolge bestimmt durch die Reihenfolge, wie sie angelegt wurden:

mysql> CREATE TABLE monate 
->(
-> spalte1 ENUM('Jan','Feb','Mar','Apr','Mai','Jun','Jul','Aug') -> );

mysql> SELECT * FROM monate ORDER BY spalte1;

Ausgabe: Jan, Feb, Mar, Apr, Mai, Jun, Jul, Aug

Die Ausgabe mit SELECT wird nach der Reihenfolge sortiert, wie die Datensätze angelegt wurden. Um die Ausgabe wiederum alphabetisch zu sortieren, kann man CAST() benutzen:

mysql> SELECT * FROM monate ORDER BY CAST(spalte1 AS CHAR);

Ausgabe: Apr, Aug, Feb, Jul, Jun, Mar, Mai

9.3.3 Limiting a Selection Using LIMIT

Mit dem Schlüsselwort LIMIT kann man die Anzahl der auszugebenden Datensätze einschränken.

# Ausgabe der ersten 20 Zeilen
SELECT * FROM tbl01 LIMIT 20;

Dieser Befehl bedeutet, dass nur die ersten 20 Datensätze angezeigt werden. LIMIT kann aber noch einen weiteren Zahlenwert enthalten. Wenn zwei Werte angegeben werden, so bedeutet der erste Wert „Ignoriere die Datensätze bis X“ und der zweite Wert „lese die nächsten X Zeilen aus“.

# Ausgabe 30 Zeilen beginnend bei dem 1000. Datensatz
SELECT * FROM tbl01 LIMIT 1000,30;

9.3.4 Using DISTINCT to Eliminate Duplicates

Das Schlüsselwort DISTINCT() wird dazu verwendet, um doppelte Werte in einer Spalte zusammenzufassen. Daher ähnelt DISTINCT der Funktion GROUP BY. Folgendes Beispiel soll die Funktionsweise verdeutlichen

mysql> SELECT name FROM multiindex;
+-------+
| name |
+-------+
| Anja |
| Anja |
| Anja |
| Peter |
| Peter |
+-------+

mysql> SELECT DISTINCT(name) FROM multiindex;
+-------+
| name |
+-------+
| Anja |
| Peter |
+-------+

mysql> SELECT name FROM multiindex GROUP BY name;
+-------+
| name |
+-------+
| Anja |
| Peter |
+-------+

9.4 Aggregating Results

MySQL bietet es an, die Ergebnisse zusammenzufassen und Operationen anzuwenden. MySQL kann zählen, Durchschnittswerte errechnen, Minimal- uind Maximalwerte ermitteln oder Ergebnisse summieren.


In folgenden Kapiteln werden due Funktionen MIN(), MAX(), SUM(), AVG(), COUNT() und GROUP_CONCAT() näher erläutert.

9.4.1 The MIN() und MAX() Aggregate Funktions

Die Funktionen MIN() und MAX() werden dazu benutzt, um den kleinsten bzw. größten Wert aus einer Spalte ausfindig zu machen:

mysql> SELECT MAX(TRANSISTOREN), MIN(TRANSISTOREN) FROM gpu;
+-------------------+-------------------+
| MAX(TRANSISTOREN) | MIN(TRANSISTOREN) |
+-------------------+-------------------+
| 754 | 1 |
+-------------------+-------------------+

MIN() und MAX() kann man auch auf Spalten anwenden, die Zeichenketten (Strings) enthalten:

mysql> SELECT MAX(hersteller), MIN(hersteller) FROM gpu_hrst;
+-----------------+-----------------+
| MAX(hersteller) | MIN(hersteller) |
+-----------------+-----------------+
| XGI | 3dfx |
+-----------------+-----------------+

3dfx ist der kleinste Wert, da mit einer Zahl beginnend. Der Buchstabe X ist ziemlich am Ende des Alphabets, daher ist XGI der größte Wert. Auch hier ist wieder entscheidend, ob es sich um einen binären oder nicht-binären Datentyp handelt.

9.4.2 The SUM() and AVG() Aggregate Functions

Ähnlich wie MIN() und MAX() funktionieren auch die Funktionen SUM() und AVG(). Sie berechnen aber die Summe und den Durchschnitt der Spaltenwerte:

mysql> SELECT SUM(TRANSISTOREN), AVG(TRANSISTOREN) FROM gpu;
+-------------------+-------------------+
| SUM(TRANSISTOREN) | AVG(TRANSISTOREN) |
+-------------------+-------------------+
| 22393 | 142.6306 |
+-------------------+-------------------+

SUM() und AVG() können auch auf String-Datentypen angewendet werden, bringen aber keine sinnvolle Ergebnisse.

9.4.3 The COUNT() Aggregate Function

Mit der Funktion COUNT() können zum einen Zeilen und zum anderen Werte gezählt werden:

# Zählen aller Zeilen in einer Tabelle
mysql> SELECT COUNT(*) FROM bench;
+----------+
| COUNT(*) |
+----------+
| 884 |
+----------+

Neben der Anzahl der Zeilen kann COUNT() auch Werte in einer Spalte zählen. Hierbei werden alle Werte beachtet, die nicht NULL sind. In folgendem Beispiel sind 80 Null-Werte enthalten (884 - 804 = 80):

# Zählen von Werten in einer Spalte
mysql> SELECT COUNT(hdd) FROM bench;
+------------+
| COUNT(hdd) |
+------------+
| 804 |
+------------+

In Verbindung mit DISTINCT können die unterschiedlichen Werte gezählt werden. Beispiel:

# Wie viele unterschiedliche Namen wurden im Jahr 2008 vergeben?
mysql> SELECT COUNT(DISTINCT vorname) FROM geburten WHERE jahr=2008;
+-------------------------+
| COUNT(DISTINCT vorname) |
+-------------------------+
| 62 |
+-------------------------+

Man kann diesen Befehl auch erweitern. Gibt man bei COUNT die Spalten an, so werden die Kombinationen gezählt. Um beispielsweise Namensvetter auszulesen, schreibt man folgendes:

# Wie viele Namensvetter gab es im Jahr 2008?
mysql> SELECT COUNT(DISTINCT vorname, nachname) FROM geburten WHERE jahr=2008;
+-----------------------------------+
| COUNT(DISTINCT vorname, nachname) |
+-----------------------------------+
| 2 |
+-----------------------------------+

Hinweis: In Verbindung mit GROUP_BY zählt COUNT, wie oft ein Wert vorkommt (Siehe 9.5).

9.4.4 The GROUP_CONCAT() Function

Mit der Funktion GROUP_CONCAT() können die Zeilenwerte in einer einzigen Zelle angezeigt werden. Anstatt jeder Wert in einer neuen Zeile erscheint, werden die Ergebnisse komma-separiert in einer Zelle ausgegeben:

mysql> SELECT GROUP_CONCAT(hersteller) FROM gpu_hrst;
+--------------------------------------------------------+
| GROUP_CONCAT(hersteller) |
+--------------------------------------------------------+
| ATI,Nvidia,XGI,Matrox,PowerVR,3dfx,Intel,S3,3DLabs,ITG |
+--------------------------------------------------------+

Das Trennzeichen für die Ausgabe kann ebenfalls geändert werden. Dazu verwendet man das Schlüsselwort SEPARATOR:

mysql> SELECT GROUP_CONCAT(hersteller SEPARATOR '-') FROM gpu_hrst;
+--------------------------------------------------------+
| GROUP_CONCAT(hersteller SEPARATOR '-') |
+--------------------------------------------------------+
| ATI-Nvidia-XGI-Matrox-PowerVR-3dfx-Intel-S3-3DLabs-ITG |
+--------------------------------------------------------+

9.4.5 Aggregation for NULL Values or Empty Sets

Alle in Punkt 9.4 aufgeführten Funktionen ignorieren NULL-Werte. Ausnahme ist COUNT():


- COUNT(*) zählt Zeilen, so dass NULL-Werte nicht ignoriert werden

- COUNT(ausdruck) ignoriert NULL-Werte wiederum!

9.5 Grouping Results

Das Schlüsselwort GROUP_BY wird dazu verwendet, mehrfach vorkommende Werte zusammenzufassen. Daher ähnelt dieses Schlüsselwort der Funktion DISTINCT.


In Verbindung mit GROUP_BY() kann man COUNT() dazu verwenden, um diese mehrfach vorkommende Werte zu zählen:

# Gruppieren nach Benchmark-Rubriken
mysql> SELECT section_id, COUNT(*) FROM bench GROUP BY section_id;
+------------+----------+
| section_id | COUNT(*) |
+------------+----------+
| 0 | 3 |
| 1 | 3 |
| 2 | 225 |
| 3 | 53 |
| 4 | 158 |
+------------+----------+

In diesem Beispiel gibt es insgesamt 225 Datensätze, welche als section_id den Wert 2 haben. Man kann auch mehrere Spalte gruppieren.

# Zählen, wieviele Personen welche Grafikkarte besitzen
mysql> SELECT section_id, vga, COUNT(*) FROM bench GROUP BY section_id, vga WHERE section_id=1;
+------------+----------------------------+----------+
| section_id | vga | COUNT(*) |
+------------+----------------------------+----------+
| 1 | ASUS GeForce 6800 | 1 |
| 1 | ATI Radeon 9550 | 1 |
| 1 | ATI Radeon 9800 Pro | 2 |
+------------+----------------------------+----------+

In diesem Beispiel gibt es zwei Anwender, die den Benchmark mit der section_id und der Grafikkarte Radeon 9800 Pro durchlaufen haben.


Sehr praktisch ist auch die Möglichkeit, die bekannten Rechnungsfunktionen (MIN, MAX, etc) einzusetzen. Folgendes Beispiel soll den schlechtesten, besten und durchschnittlichen Wert der Benchmarkergebnisse ermitteln:

# Schlechteste, beste und durchschnittliche Werte ermitteln
mysql> SELECT section_id, MIN(points_result), MAX(points_result), AVG(points_result)
-> FROM bench
-> GROUP BY section_id;
+------------+--------------------+--------------------+--------------------+
| section_id | MIN(points_result) | MAX(points_result) | AVG(points_result) |
+------------+--------------------+--------------------+--------------------+
| 0 | 0 | 9999 | 2856.8571 |
| 1 | 749 | 17520 | 5584.6915 |
| 2 | 109 | 40818 | 8238.8622 |
| 3 | 414 | 24225 | 11528.2453 |
| 4 | 125 | 52033 | 11815.9241 |
+------------+--------------------+--------------------+--------------------+

9.5.2 Selecting Groups with HAVING

HAVING ist ein zusätzliches Schlüsselwort, um Ergebnisse einzugrenzen. Es kommt dann zum Einsatz, wenn man in Verbindung mit GROUP BY das Schlüsselwort WHERE benutzt. Beispiel:

mysql> SELECT section_id, vga, COUNT(*)
-> FROM bench
-> WHERE section_id > 3 OR section_id < 10
-> GROUP BY section_id, vga;

In diesem Beispiel werden nun zahlreiche Datensätze aus der Tabelle gelesen, denn alle Benchmarkergebnisse mit einer ID zwischen 4 und 9 werden angezeigt. Um nun hier nur bestimmte Datensätze herauszulesen, setzt man HAVING ein.

mysql> SELECT section_id, vga, COUNT(*)
-> FROM bench
-> WHERE section_id < 3 OR section_id > 10
-> GROUP BY section_id, vga
-> HAVING vga = 'Radeon X1950 Pro';

+------------+------------------+----------+
| section_id | vga | COUNT(*) |
+------------+------------------+----------+
| 4 | Radeon X1950 Pro | 2 |
| 8 | Radeon X1950 Pro | 1 |
+------------+------------------+----------+

In diesem Beispiel werden nur die Ergebnisse angezeigt, bei der eine Radeon X1950 Pro Grafikkarte eingesetzt wurde.

9.5.3 Using GROUP BY and WITH ROLLUP

Folgende Beschreibung ist auf www.mysql.de zu lesen:


„Die GROUP BY-Klausel gestattet die Verwendung eines Modifizierers WITH ROLLUP, der bewirkt, dass zusätzliche Datensätze zur zusammenfassenden Ausgabe hinzugefügt werden. Diese Datensätze stellen Zusammenfassungsoperationen einer höherer Ebene (so genannte Superaggregate) dar.“

mysql> SELECT jahr, SUM(profit) FROM sales GROUP BY year WITH ROLLUP;
+------+-------------+
| year | SUM(profit) |
+------+-------------+
| 2000 | 8525 |
| 2001 | 8010 |
| NULL | 16535 |
+------+-------------+

Mit dem Schlüsselwort WITH ROLLUP werden die Werte addiert. Die Super-Aggregat-Zeile ist durch NULL identifzierbar.