SQL Kochbuch, 2. Auflage

Kapitel 1. Aufzeichnungen abrufen

Diese Arbeit wurde mithilfe von KI übersetzt. Wir freuen uns über dein Feedback und deine Kommentare: [e-mail]

Dieses Kapitel konzentriert sich auf die grundlegenden SELECT-Anweisungen. Es ist wichtig, ein solides Verständnis der Grundlagen zu haben, da viele der hier behandelten Themen nicht nur in schwierigeren Rezepten vorkommen, sondern auch im alltäglichen SQL zu finden sind.

1.1 Alle Zeilen und Spalten aus einer Tabelle abrufen

Problem

Du hast eine Tabelle und möchtest alle Daten darin sehen.

Lösung

Verwende das Sonderzeichen * und gib ein SELECT auf die Tabelle aus:

1 select *
2   from emp

Diskussion

Das Zeichen * hat in SQL eine besondere Bedeutung. Wenn du es verwendest, wird jede Spalte der angegebenen Tabelle zurückgegeben. Da keine WHERE-Klausel angegeben ist, wird auch jede Zeile zurückgegeben. Die Alternative wäre, jede Spalte einzeln aufzulisten:

select empno,ename,job,sal,mgr,hiredate,comm,deptno
  from emp

Bei Ad-hoc-Abfragen, die du interaktiv ausführst, ist es einfacher, SELECT * zu verwenden. Wenn du jedoch Programmcode schreibst, ist es besser, jede Spalte einzeln anzugeben. Die Leistung ist dieselbe, aber durch die explizite Angabe weißt du immer, welche Spalten du von der Abfrage zurückgibst. Außerdem sind solche Abfragen für andere Personen leichter zu verstehen (die möglicherweise nicht alle Spalten der Tabellen in der Abfrage kennen). Probleme mit SELECT * können auch auftreten, wenn deine Abfrage im Code enthalten ist und das Programm eine andere Menge von Spalten aus der Abfrage erhält als erwartet. Wenn du alle Spalten angibst und eine oder mehrere fehlen, ist es zumindest wahrscheinlicher, dass ein Fehler auf die fehlende(n) Spalte(n) zurückgeführt werden kann.

1.2 Abrufen einer Teilmenge von Zeilen aus einer Tabelle

Problem

Du hast eine Tabelle und möchtest nur Zeilen sehen, die eine bestimmte Bedingung erfüllen.

Lösung

Verwende die WHERE-Klausel, um anzugeben, welche Zeilen beibehalten werden sollen. Beispiel: Du möchtest alle Mitarbeiter anzeigen, die der Abteilung 10 zugeordnet sind:

1 select *
2   from emp
3  where deptno = 10

Diskussion

Mit der WHERE-Klausel kannst du nur die Zeilen abrufen, an denen du interessiert bist. Wenn der Ausdruck in der WHERE-Klausel für eine beliebige Zeile wahr ist, wird diese Zeile zurückgegeben.

Die meisten Anbieter unterstützen gängige Operatoren wie =, <, >, <=, >=, ! und <>. Außerdem möchtest du vielleicht Zeilen haben, die mehrere Bedingungen erfüllen. Das kannst du erreichen, indem du AND, OR und Klammern angibst, wie im nächsten Rezept gezeigt.

1.3 Suche nach Zeilen, die mehrere Bedingungen erfüllen

Problem

Du willst Zeilen zurückgeben, die mehrere Bedingungen erfüllen.

Lösung

Verwende die WHERE-Klausel zusammen mit den OR- und AND-Klauseln. Wenn du z.B. alle Mitarbeiter in Abteilung 10, alle Mitarbeiter, die eine Provision verdienen, und alle Mitarbeiter in Abteilung 20, die höchstens 2.000 $ verdienen, finden möchtest:

1 select *
2   from emp
3  where deptno = 10
4     or comm is not null
5     or sal <= 2000 and deptno=20

Diskussion

Du kannst eine Kombination aus AND, OR und Klammern verwenden, um Zeilen zurückzugeben, die mehrere Bedingungen erfüllen. Im Lösungsbeispiel findet die WHERE-Klausel solche Zeilen, die:

• Die DEPTNO ist 10

• Der COMM ist nicht NULL

• Das Gehalt eines Angestellten in DEPTNO 20 beträgt 2.000 $ oder weniger.

Das Vorhandensein von Klammern bewirkt, dass die Bedingungen innerhalb der Klammern gemeinsam ausgewertet werden.

Sieh dir zum Beispiel an, wie sich die Ergebnismenge ändert, wenn die Abfrage wie hier gezeigt mit Klammern geschrieben wurde:

select *
 from emp
where (     deptno = 10
        or comm is not null
        or sal <= 2000
      )
  and deptno=20

EMPNO ENAME  JOB     MGR  HIREDATE      SAL       COMM  DEPTNO
----- ------ ----- -----  ----------- ----- ----------  ------
 7369 SMITH  CLERK  7902  17-DEC-1980   800                 20
 7876 ADAMS  CLERK  7788  12-JAN-1983  1100                 20

1.4 Abrufen einer Teilmenge von Spalten aus einer Tabelle

Problem

Du hast eine Tabelle und möchtest die Werte für bestimmte Spalten und nicht für alle Spalten sehen.

Lösung

Gib die Spalten an, an denen du interessiert bist. Du möchtest zum Beispiel nur den Namen, die Abteilungsnummer und das Gehalt der Mitarbeiter sehen:

1 select ename,deptno,sal
2   from emp

Diskussion

Indem du die Spalten in der SELECT-Klausel angibst, stellst du sicher, dass keine überflüssigen Daten zurückgegeben werden. Das kann besonders wichtig sein, wenn du Daten über ein Netzwerk abrufst, da du so keine Zeit verschwendest, wenn du Daten abrufst, die du nicht brauchst.

1.5 Sinnvolle Namen für Spalten vergeben

Problem

Du möchtest die Namen der Spalten, die von deiner Abfrage zurückgegeben werden, ändern, damit sie besser lesbar und verständlich sind. Betrachte diese Abfrage, die die Gehälter und Provisionen für jeden Mitarbeiter zurückgibt:

1 select sal,comm
2   from emp

Was ist SAL? Ist es die Abkürzung für Sale? Ist es der Name von jemandem? Was ist COMM? Heißt es Kommunikation? Du möchtest, dass die Ergebnisse aussagekräftigere Bezeichnungen haben.

Lösung

Um die Namen der Abfrageergebnisse zu ändern, verwenden Sie das Schlüsselwort AS in der Formoriginal_name AS new_name. Einige Datenbanken verlangen kein AS, aber alle akzeptieren es:

 1 select sal as salary, comm as commission
  2   from emp

SALARY   COMMISSION
-------  ----------
    800
   1600         300
   1250         500
   2975
   1250        1400
   2850
   2450
   3000
   5000
   1500           0
   1100
    950
   3000
   1300

Diskussion

Wenn du das Schlüsselwort AS verwendest, um den von deiner Abfrage zurückgegebenen Spalten neue Namen zu geben, nennt man das Aliasing dieser Spalten. Die neuen Namen, die du vergibst, werden als Aliasnamen bezeichnet. Gute Aliasnamen können viel dazu beitragen, dass eine Abfrage und ihre Ergebnisse für andere verständlich sind.

1.6 Verweis auf eine Aliased-Spalte in der WHERE-Klausel

Problem

Du hast Aliasnamen verwendet, um aussagekräftigere Spaltennamen für deine Ergebnismenge zu erhalten, und möchtest einige der Zeilen mit der WHERE-Klausel ausschließen. Dein Versuch, in der WHERE-Klausel auf Aliasnamen zu verweisen, schlägt jedoch fehl:

select sal as salary, comm as commission
  from emp
 where salary < 5000

Lösung

Wenn du deine Abfrage als Inline-View verpackst, kannst du auf die Aliased-Spalten verweisen:

1 select *
2   from (
3 select sal as salary, comm as commission
4   from emp
5        ) x
6  where salary < 5000

Diskussion

In diesem einfachen Beispiel kannst du die Inline-Ansicht umgehen und COMM oder SAL direkt in der WHERE-Klausel referenzieren, um das gleiche Ergebnis zu erzielen. Mit dieser Lösung erfährst du, was du tun musst, wenn du in einer WHERE-Klausel auf eines der folgenden Elemente verweisen willst:

• Aggregatfunktionen

• Skalare Unterabfragen

• Fensterfunktionen

• Aliasnamen

Wenn du deine Abfrage, die dir Aliase gibt, in einer Inline-Ansicht platzierst, kannst du in deiner äußeren Abfrage auf die Aliasspalten verweisen. Warum musst du das tun? Die WHERE-Klausel wird vor der SELECT-Abfrage ausgewertet; daher sind SALARY und COMMISSION noch nicht vorhanden, wenn die WHERE-Klausel der "Problem"-Abfrage ausgewertet wird. Diese Aliasnamen werden erst nach der Verarbeitung der WHERE-Klausel angewendet. Die FROM-Klausel wird jedoch vor der WHERE-Klausel ausgewertet. Wenn du die ursprüngliche Abfrage in eine FROM-Klausel einfügst, werden die Ergebnisse dieser Abfrage vor der äußersten WHERE-Klausel generiert, und deine äußerste WHERE-Klausel "sieht" die Alias-Namen. Diese Technik ist besonders nützlich, wenn die Spalten in einer Tabelle nicht besonders gut benannt sind.

Tipp

Die Inline-View in dieser Lösung ist ein Aliasing X. Nicht alle Datenbanken verlangen, dass eine Inline-View explizit mit einem Aliasing versehen wird, aber einige schon. Alle akzeptieren sie.

1.7 Verkettung von Spaltenwerten

Problem

Du möchtest Werte in mehreren Spalten als eine Spalte zurückgeben. Du möchtest zum Beispiel diese Ergebnismenge aus einer Abfrage der Tabelle EMP erzeugen:

CLARK WORKS AS A MANAGER
KING WORKS AS A PRESIDENT
MILLER WORKS AS A CLERK

Die Daten, die du für die Erstellung dieser Ergebnismenge brauchst, stammen jedoch aus zwei verschiedenen Spalten, den Spalten ENAME und JOB in der Tabelle EMP:

 select ename, job
    from emp
    where deptno = 10

ENAME      JOB
---------- ---------
CLARK      MANAGER
KING       PRESIDENT
MILLER     CLERK

Lösung

Finde die integrierte Funktion deines DBMS und verwende sie, um Werte aus mehreren Spalten zu verketten.

DB2, Oracle, PostgreSQL

Diese Datenbanken verwenden den doppelten vertikalen Balken als Verkettungsoperator:

1 select ename||' WORKS AS A '||job as msg
2   from emp
3  where deptno=10

MySQL

Diese Datenbank unterstützt eine Funktion namens CONCAT:

1 select concat(ename, ' WORKS AS A ',job) as msg
2   from emp
3  where deptno=10

SQL Server

Verwende den + Operator für die Verkettung:

1 select ename + ' WORKS AS A ' + job as msg
2   from emp
3  where deptno=10

Diskussion

Verwende die CONCAT-Funktion, um Werte aus mehreren Spalten zu verketten. Der || ist eine Abkürzung für die CONCAT-Funktion in DB2, Oracle und PostgreSQL, während + die Abkürzung für SQL Server ist.

1.8 Verwendung der bedingten Logik in einer SELECT-Anweisung

Problem

Du möchtest IF-ELSE-Operationen für Werte in deiner SELECT-Anweisung durchführen. Du möchtest zum Beispiel eine Ergebnismenge erzeugen, die bei einem Gehalt von 2.000 USD oder weniger die Meldung "UNTERBEZAHLT" ausgibt, bei einem Gehalt von 4.000 USD oder mehr die Meldung "ÜBERBEZAHLT" und bei einem Gehalt dazwischen die Meldung "OK" ausgibt. Die Ergebnismenge sollte wie folgt aussehen:

 ENAME            SAL  STATUS
---------- ----------  ---------
SMITH             800  UNDERPAID
ALLEN            1600  UNDERPAID
WARD             1250  UNDERPAID
JONES            2975  OK
MARTIN           1250  UNDERPAID
BLAKE            2850  OK
CLARK            2450  OK
SCOTT            3000  OK
KING             5000  OVERPAID
TURNER           1500  UNDERPAID
ADAMS            1100  UNDERPAID
JAMES             950  UNDERPAID
FORD             3000  OK
MILLER           1300  UNDERPAID

Lösung

Verwende den CASE-Ausdruck, um eine bedingte Logik direkt in deinerSELECT-Anweisung auszuführen:

1 select ename,sal,
2        case when sal <= 2000 then 'UNDERPAID'
3             when sal >= 4000 then 'OVERPAID'
4             else 'OK'
5        end as status
6   from emp

Diskussion

Mit dem CASE-Ausdruck kannst du eine Bedingungslogik auf die von einer Abfrage zurückgegebenen Werte anwenden. Du kannst einen Alias für einen CASE-Ausdruck angeben, um eine besser lesbare Ergebnismenge zu erhalten. In der Lösung siehst du den Alias STATUS für das Ergebnis des CASE-Ausdrucks. Die ELSE-Klausel ist optional. Wenn du die ELSE-Klausel weglässt, gibt der CASE-Ausdruck für jede Zeile, die die Testbedingung nicht erfüllt, NULL zurück.

1.9 Begrenzung der Anzahl der zurückgegebenen Zeilen

Problem

Du möchtest die Anzahl der Zeilen begrenzen, die in deiner Abfrage zurückgegeben werden. Die Reihenfolge spielt dabei keine Rolle; n beliebige Zeilen sind ausreichend.

Lösung

Verwende die integrierte Funktion deiner Datenbank, um die Anzahl der zurückgegebenen Zeilen zu steuern.

DB2

In DB2 verwendest du die FETCH FIRST-Klausel:

1 select *
2   from emp fetch first 5 rows only

MySQL und PostgreSQL

In MySQL und PostgreSQL kannst du dasselbe mit LIMIT machen:

1 select *
2   from emp limit 5

Oracle

In Oracle schränkst du die Anzahl der zurückgegebenen Zeilen ein, indem du ROWNUM in der WHERE-Klausel einschränkst:

1 select *
2   from emp
3  where rownum <= 5

SQL Server

Verwende das Schlüsselwort TOP, um die Anzahl der zurückgegebenen Zeilen zu begrenzen:

1 select top 5 *
2   from emp

Diskussion

Viele Anbieter bieten Klauseln wie FETCH FIRST und LIMIT, mit denen du die Anzahl der Zeilen festlegen kannst, die von einer Abfrage zurückgegeben werden sollen. Bei Oracle musst du eine Funktion namens ROWNUM verwenden, die für jede zurückgegebene Zeile eine Zahl zurückgibt (einen aufsteigenden Wert, der bei eins beginnt).

So sieht es aus, wenn du ROWNUM <= 5 verwendest, um die ersten fünf Zeilen zurückzugeben:

1. Oracle führt deine Abfrage aus.

2. Oracle holt die erste Zeile und nennt sie Zeile Nummer eins.

3. Haben wir die Zeile Nummer fünf schon hinter uns gelassen? Wenn nein, dann gibt Oracle die Zeile zurück, weil sie das Kriterium erfüllt, dass sie kleiner oder gleich fünf ist. Wenn ja, dann gibt Oracle die Zeile nicht zurück.

4. Oracle holt die nächste Zeile und erhöht die Zeilennummer (auf zwei, dann auf drei, dann auf vier und so weiter).

5. Gehe zu Schritt 3.

Wie dieser Prozess zeigt, werden die Werte von Oracles ROWNUM zugewiesen , nachdem jede Zeile abgerufen wurde. Das ist ein wichtiger und entscheidender Punkt. Viele Oracle-Entwickler versuchen, z. B. nur die fünfte Zeile einer Abfrage zurückzugeben, indem sie ROWNUM = 5 angeben.

Die Verwendung einer Gleichheitsbedingung in Verbindung mit ROWNUM ist eine schlechte Idee. Das passiert, wenn du versuchst, z. B. die fünfte Zeile mit ROWNUM = 5 zurückzugeben:

1. Oracle führt deine Abfrage aus.

2. Oracle holt die erste Zeile und nennt sie Zeile Nummer eins.

3. Sind wir schon bei Zeile Nummer fünf angelangt? Wenn nein, dann verwirft Oracle die Zeile, weil sie die Kriterien nicht erfüllt. Wenn ja, dann gibt Oracle die Zeile zurück. Aber die Antwort wird niemals "Ja" sein!

4. Oracle holt die nächste Zeile und nennt sie Zeile Nummer eins. Das liegt daran, dass die erste Zeile, die von der Abfrage zurückgegeben wird, die Nummer eins haben muss.

5. Gehe zu Schritt 3.

Wenn du dir diesen Prozess genau ansiehst, kannst du sehen, warum die Verwendung von ROWNUM = 5 zur Rückgabe der fünften Zeile fehlschlägt. Du kannst keine fünfte Zeile haben, wenn du nicht zuerst die Zeilen eins bis vier zurückgibst!

Du wirst feststellen, dass ROWNUM = 1 tatsächlich die erste Zeile zurückgibt, was der bisherigen Erklärung zu widersprechen scheint. Der Grund dafür, dass ROWNUM = 1 die erste Zeile zurückgibt, ist, dass Oracle mindestens einmal versuchen muss, eine Zeile aus der Tabelle zu holen, um festzustellen, ob sie überhaupt vorhanden ist. Lies dir den vorangegangenen Vorgang genau durch und ersetze eine durch fünf, dann verstehst du, warum es in Ordnung ist, ROWNUM = 1 als Bedingung (für die Rückgabe einer Zeile) anzugeben.

1.10 Rückgabe von n zufälligen Datensätzen aus einer Tabelle

Problem

Du möchtest eine bestimmte Anzahl von zufälligen Datensätzen aus einer Tabelle zurückgeben. Du möchtest die folgende Anweisung so abändern, dass aufeinanderfolgende Ausführungen einen anderen Satz von fünf Zeilen ergeben:

select ename, job
  from emp

Lösung

Nimm eine beliebige integrierte Funktion, die von deinem DBMS unterstützt wird, um Zufallswerte zurückzugeben. Verwende diese Funktion in einer ORDER BY-Klausel, um Zeilen nach dem Zufallsprinzip zu sortieren. Verwende dann die Technik aus dem vorherigen Rezept, um die Anzahl der zufällig sortierten Zeilen zu begrenzen.

DB2

Verwende die eingebaute Funktion RAND in Verbindung mit ORDER BY und FETCH:

1 select ename,job
2   from emp
3  order by rand() fetch first 5 rows only

MySQL

Verwende die integrierte RAND-Funktion in Verbindung mit LIMIT und ORDER BY:

1 select ename,job
2   from emp
3  order by rand() limit 5

PostgreSQL

Verwende die eingebaute Funktion RANDOM in Verbindung mit LIMIT und ORDER BY:

 1 select ename,job
2   from emp
3  order by random() limit 5

Oracle

Verwende die eingebaute Funktion VALUE, die du im eingebauten Paket DBMS_RANDOM findest, in Verbindung mit ORDER BY und der eingebauten Funktion ROWNUM:

1 select *
2   from (
3  select ename, job
4    from emp
6   order by dbms_random.value()
7        )
8   where rownum <= 5

SQL Server

Verwende die eingebaute Funktion NEWID in Verbindung mit TOP und ORDER BY, um eine zufällige Ergebnismenge zu erhalten:

1 select top 5 ename,job
2   from emp
3  order by newid()

Diskussion

Die ORDER BY-Klausel kann den Rückgabewert einer Funktion akzeptieren und ihn verwenden, um die Reihenfolge der Ergebnismenge zu ändern. Diese Lösungen schränken alle die Anzahl der Zeilen ein, die nach der Ausführung der Funktion in der ORDER BY-Klausel zurückgegeben werden. Für Nicht-Oracle-Benutzer ist es vielleicht hilfreich, sich die Oracle-Lösung anzuschauen, da sie (konzeptionell) zeigt, was unter der Oberfläche der anderen Lösungen passiert.

Es ist wichtig, dass du die Verwendung einer Funktion in der ORDER BY-Klausel nicht mit der Verwendung einer numerischen Konstante verwechselst. Wenn du eine numerische Konstante in der ORDER BY-Klausel angibst, verlangst du, dass die Sortierung nach der Spalte an der entsprechenden Position in der SELECT-Liste erfolgt. Wenn du eine Funktion in der ORDER BY-Klausel angibst, wird die Sortierung nach dem Ergebnis der Funktion durchgeführt, wenn sie für jede Zeile ausgewertet wird.

1.11 Suche nach Nullwerten

Problem

Du willst alle Zeilen finden, die für eine bestimmte Spalte null sind.

Lösung

Um festzustellen, ob ein Wert null ist, musst du IS NULL verwenden:

1 select *
2   from emp
3  where comm is null

Diskussion

NULL ist niemals gleich/nicht gleich, nicht einmal sich selbst; daher kannst du nicht mit = oder != testen, ob eine Spalte NULL ist. Um festzustellen, ob eine Zeile NULL-Werte hat, musst du IS NULL verwenden. Du kannst auch IS NOT NULL verwenden, um Zeilen zu finden, die keine Nullwerte in einer bestimmten Spalte enthalten.

1.12 Nullen in reale Werte umwandeln

Problem

Du hast Zeilen, die Nullen enthalten und möchtest Nicht-Null-Werte anstelle dieser Nullen zurückgeben.

Lösung

Verwende die Funktion COALESCE, um reelle Werte durch Nullen zu ersetzen:

1 select coalesce(comm,0)
2   from emp

Diskussion

Die Funktion COALESCE nimmt einen oder mehrere Werte als Argumente an. Die Funktion gibt den ersten Wert in der Liste zurück, der nicht null ist. In der Lösung wird der Wert von COMM zurückgegeben, wenn COMM nicht null ist. Andernfalls wird eine Null zurückgegeben.

Wenn du mit Nullen arbeitest, ist es am besten, die integrierten Funktionen deines DBMS zu nutzen; in vielen Fällen wirst du feststellen, dass mehrere Funktionen für diese Aufgabe genauso gut funktionieren. COALESCE funktioniert zufällig bei allen DBMSs. Auch CASE kann für alle DBMS verwendet werden:

select case
       when comm is not null then comm
       else 0
       end
  from emp

Du kannst zwar CASE verwenden, um Nullen in Werte umzuwandeln, aber du wirst sehen, dass es viel einfacher und prägnanter ist, COALESCE zu verwenden.

1.13 Suche nach Mustern

Problem

Du möchtest Zeilen zurückgeben, die mit einem bestimmten Teilstring oder Muster übereinstimmen. Betrachte die folgende Abfrage und Ergebnismenge:

select ename, job
  from emp
 where deptno in (10,20)

ENAME       JOB
----------  ---------
SMITH       CLERK
JONES       MANAGER
CLARK       MANAGER
SCOTT       ANALYST
KING        PRESIDENT
ADAMS       CLERK
FORD        ANALYST
MILLER      CLERK

Von den Beschäftigten in den Abteilungen 10 und 20 willst du nur die zurückgeben, die entweder ein "I" in ihrem Namen haben oder eine Stellenbezeichnung, die auf "ER" endet:

ENAME       JOB
----------  ---------
SMITH       CLERK
JONES       MANAGER
CLARK       MANAGER
KING        PRESIDENT
MILLER      CLERK

Lösung

Verwende den LIKE-Operator in Verbindung mit dem SQL-Platzhalter (%):

1 select ename, job
2   from emp
3  where deptno in (10,20)
4    and (ename like '%I%' or job like '%ER')

Diskussion

Wenn er in einer LIKE-Mustererkennung verwendet wird, passt der Prozentoperator (%) auf eine beliebige Folge von Zeichen. Die meisten SQL-Implementierungen bieten auch den Unterstrich-Operator ("_"), um ein einzelnes Zeichen zu finden. Wenn du das Suchmuster "I" mit %-Operatoren einschließt, wird jede Zeichenkette zurückgegeben, die ein "I" (an einer beliebigen Position) enthält. Wenn du das Suchmuster nicht mit % einschließt, hat die Position des Operators Auswirkungen auf die Ergebnisse der Abfrage. Wenn du z. B. Aufträge finden willst, die auf "ER" enden, stellst du den%-Operator vor "ER"; wenn du nach allen Aufträgen suchen willst, die mit "ER" beginnen, fügst du den %-Operator an "ER" an.

1.14 Resümee

Diese Rezepte mögen einfach sein, aber sie sind auch grundlegend. Die Informationsbeschaffung ist das Herzstück der Datenbankabfrage, und das bedeutet, dass diese Rezepte das Herzstück von praktisch allem sind, was im Rest des Buches besprochen wird.