TOP-40 Wichtigsten SQL Abfragen

SQList unglaublich mächtig und wie jedes gut gemachte Entwicklungstool verfügt es über ein paar Befehle, über die jeder gute Entwickler Bescheid wissen sollte. Hier sind einige der wichtigsten Befehle – jede dieser Abfragen ist für so gut wie jedes System erforderlich, das mit einer SQL Datenbank arbeitet.

1. Abfrage um Tabellen wiederzugeben

Diese Abfrage kann ausgeführt werden, um eine Liste von den Tabellen einer Datenbank zu erzeugen, in denen die Datenbank „My_Schema” lautet.

SELECT * FROM My_Schema.Tables;

Abfrage um Spalten einer Tabelle auszuwählen

Dies ist vielleicht die am Meisten genutzte SQL Abfrage. In dem unteren Beispiel extrahieren wir die „Student_ID” Spalte oder Attribute von der Tabelle „STUDENT”.

SELECT Student_ID FROM STUDENT;

Falls du alle Attribute einer bestimmten Tabelle anzeigen willst, dann ist dies die richtige Abfrage:

SELECT * FROM STUDENT;

3. Abfrage um Daten auszugeben mit Hilfe eines Constraints

Diese Abfrage gibt bestimmte Attribute einer Tabelle auf dem Constraint Employee ID =0000 aus.

1
SELECT EMP_ID, NAME FROM EMPLOYEE_TBL WHERE EMP_ID = '0000';

4. Abfrage um sortierte Daten auszugeben mit Hilfe von “Order By”

Diese Abfrage ordnet die Ergebnisse anhand der Attribute, welches mit “Order By” spezifiziert wird – falls das Attribut also einen Integer Datentyp hat, so werden die Ergebnisse entweder in aufsteigender, oder absteigender Reihenfolge ausgegeben. Falls der Datentyp ein String ist, werden die Resultate in alphabetischer Reihenfolge ausgegeben.

1
2
SELECT EMP_ID, LAST_NAME FROM EMPLOYEE
WHERE CITY = 'Seattle' ORDER BY EMP_ID;

Die Reihenfolge des Ergebnisses kann auch manuell kontrolliert werden, indem du „asc” für aufsteigend und „desc” für absteigend benutzt.

1
2
SELECT EMP_ID, LAST_NAME FROM EMPLOYEE_TBL
WHERE CITY = 'INDIANAPOLIS' ORDER BY EMP_ID asc;

5. Abfrage um sortierte Daten auszugeben mit Hilfe von “Group By”

Die “Group By“ Funktion gruppiert die ausgegebenen Daten anhand eines spezifizierten Attributs.

1
2
SELECT Name, Age FROM Patients WHERE Age > 40
GROUP BY Age ORDER BY Name;

Abfragen um Daten zu manipulieren mit Hilfe von mathematischen Funktionen

Es existieren eine Menge von eingebauten mathematischen Funktionen wie COUNT und AVG, welche grundlegende Funktionalitäten bieten, um die Anzahl der Resultate zu zählen und deren Durchschnitt zu berechnen.

6. Daten Manipulation mit Hilfe von COUNT

Diese Abfrage zeigt die komplette Anzahl der Kunden an, indem sie jede einzelne Customer ID zählt. Zusätzlich gruppiert sie die Ergebnisse anhand des Landes jedes Kunden.

1
SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country;

Daten Manipulation mit Hilfe von SUM

SUM kalkuliert die Summe der Attribute, die in dem jeweiligen Argument enthalten sind.

1
Select SUM(Salary)FROM Employee WHERE Emp_Age < 30;

8. Daten Manipulation mit Hilfe von AVG

Ganz einfach – der Durchschnitt des genannten Attributs.

1
SELECT AVG(Price)FROM Products;

9. Abfrage um alle Views aufzulisten

Diese Abfrage listet alle Views auf, die in diesem Schema enthalten sind.

1
SELECT * FROM My_Schema.views;

10. Abfrage um eine View zu erstellen

Eine View (=Sicht) ist eine maßgeschneiderte Tabelle, die durch die Ergebnisse einer Abfrage generiert wurde. Sie hat Tabellen und Zeilen wie fast jede andere Tabelle. Üblicherweise ist es eine gute Idee, Abfragen als unabhängige Views laufen zu lassen, da es dadurch möglich wird, sie später aufzurufen, um die Ergebnisse einer Abfrage zu sehen. Die Alternative wäre es die gleiche Abfrage für eine bestimmte Ausgabemenge immer wieder ausführen zu müssen.

1
2
3
4
CREATE VIEW Failing_Students AS
SELECT S_NAME, Student_ID
FROM STUDENT
WHERE GPA > 40;

11. Abfrage um eine View wiederzugeben

Die Standard Syntax für das Auswählen von Attributen einer Tabelle lässt sich auch auf eine View anwenden.

1
SELECT * FROM Failing_Students;

12. Abfrage um eine View zu aktualisieren

Diese Abfrage aktualisiert die View mit dem Namen „Product List“ – und falls diese View nicht existiert, dann wird die „Product List“ – View genauso generiert, wie es in der Abfrage spezifiziert wurde.

1
2
3
4
CREATE OR REPLACE VIEW [ Product List] AS
SELECT ProductID, ProductName, Category
FROM Products
WHERE Discontinued = No;

13. Abfrage um eine View zu löschen

Diese Abfrage löscht die View mit dem Namen “V1”.

1
DROP VIEW V1

14. Abfrage um Benutzer Tabellen anzuzeigen

Eine Benutzer Tabelle ist die Repräsentation von definierten Informationen in einer Tabelle. Sie kann als Argument für einen Prozess genutzt werden oder als Benutzer Funktion. Da sie so nützlich sind, empfiehlt es sich sie mit Hilfe der folgenden Abfrage im Auge zu behalten.

1
SELECT * FROM Sys.objects WHERE Type='u'

15. Abfrage um Primärschlüssel anzuzeigen

Ein Primärschlüssel identifiziert alle einzigartigen Werte in einer Tabelle. Die folgende Abfrage listet alle Felder, die als Primärschlüssel markiert sind.

1
SELECT * from Sys.Objects WHERE Type='PK'

16. Abfrage um einzigartige Schlüssel anzuzeigen

Ein einzigartiger Schlüssel erlaubt es einer Spalte sicherzugehen, dass alle ihrer Werte einzigartig sind.

1
SELECT * FROM Sys.Objects WHERE Type='uq'

17. Fremdschlüssel anzeigen

Fremdschlüssel verbinden Tabellen miteinander – sie sind diejenigen Attribute einer Tabelle, die sich auf den Primärschlüssel einer anderen Tabelle beziehen.

1
SELECT * FROM Sys.Objects WHERE Type='f'

18. Trigger anzeigen

Ein Trigger ist so etwas wie ein “Event Aufpasser”– er ist also ein vorbestimmter Block von Anweisungen, die ausgeführt werden, wenn ein bestimmtes Ereignis eintritt.  Die Liste der definierten Trigger kann durch die Anwendung der folgenden Abfrage betrachtet werden.

1
SELECT * FROM Sys.Objects WHERE Type='tr'

19. Interne Tabellen anzeigen

Interne Tabellen werden als Zusatzprodukt von Benutzer-Aktionen generiert und können in der Regel nicht aufgerufen werden. Die Daten in internen Tabellen können nicht manipuliert werden. Allerdings können die Metadaten von internen Tabellen mit der folgenden Abfrage betrachtet werden.

1
SELECT * FROM Sys.Objects WHERE Type='it'

20. Eine Liste der Prozesse anzeigen

Ein gespeicherter Prozess ist eine Gruppe von SQL Abfragen, die eine einzelne logische Einheit formen und eine bestimmte Aufgabe ausführen. Durch die folgende Abfrage kannst du sie im Auge behalten:

1
SELECT * FROM Sys.Objects WHERE Type='p'

.. und ZWANZIG Weitere SQL Abfragen für Fortgeschrittene!

21. Die Werte zweier Spalten einer Tabelle tauschen

In diesem und den weiteren Beispielen werden wir eine übliche Unternehmensdatenbank nutzen, inklusive einiger Tabellen, die sehr einfach aufgebaut sind. Unsere Übungsdatenbank wird eine Kundentabelle (=Customer) und eine Bestellungentabelle (=Order) beinhalten. In der Kundentabelle werden offensichtliche Spalten wie ID, Name, Adresse, PLZ (=zip), E-Mail und Weitere enthalten sein. Wir nehmen jetzt einfach mal an, dass der Primärschlüssel fürs Indizieren das Customer_ID Feld ist.

Während wir das im Hinterkopf haben, können wir uns ganz einfach eine Bestellungentabelle vorstellen, die ebenfalls das indizierte Customer_ID Feld besitzt und weitere Informationen zu jeder Bestellung, die vom Kunden aufgegeben wurde. Diese Tabelle wird die Felder Nummer (=Number), Menge (=Quantity), Datum (=Date), Position (=Item), und Preis (=Price) beinhalten. Stelle dir in unserem ersten SQL Beispiel vor, dass alle Phone und Zip Felder vertauscht wurden und alle Telefonnummern fälschlicherweise in das Zip-Feld eingegeben wurden. Mit dem folgenden SQL Befehl können wir dieses Problem ganz einfach lösen:

1
UPDATE Customers SET Zip=Phone, Phone=Zip

Eine Spalte mit einzigartigen Werten wiedergeben

Nun stelle dir vor, dass unser Dateneingeber dieselben Kundendaten mehrmals in die gleiche Kundentabelle eingetragen hat. Wie du weißt, kann eine richtige Indizierung nur dann erfolgen, wenn die Schlüsselfelder einzigartige Werte aufweisen. Um dieses Problem zu lösen, werden wir den SELECT DISTINCT Befehl nutzen um eine indizierbare Liste von einzigartigen Kunden zu erzeugen:

1
SELECT DISTINCT ID FROM Customers

23. Eine Top 25 erstellen mit der SELECT TOP Klausel

Als Nächstes stell dir vor, unsere Kundentabelle ist um einige tausend Beiträge gewachsen. Wir wollen uns allerdings nur 25 dieser Beiträge ansehen, um die Spaltenüberschriften zu demonstrieren. Die SELECT TOP Klausel erlaubt es uns, eine bestimmte Anzahl von Einträgen auszugeben, wie zum Beispiel die Top 25 Liste. In diesem Beispiel werden uns die Top 25 der Kundentabelle ausgegeben:

1
SELECT TOP 25 FROM Customers WHERE Customer_ID<>NULL;

24. Nach SQL Tabellen suchen mit Hilfe von Wildcards

Wildcard Zeichen oder Operatoren wie „%” machen es einfach, bestimmte Strings in einer großen Tabelle mit tausenden Einträgen zu finden. Nehmen wir an, wir wollen alle Kunden finden, deren Namen mit „Herb” beginnen, wie etwa Herberts und Herbertson. Das % Wildcard Symbol kann benutzt werden, um ein solches Suchergebnis zu erzielen. Der folgende SQL Befehl wird alle Zeilen der Kundentabelle wiedergeben, bei denen das Customer_name Feld mit „Herb” beginnt:

1
SELECT * From Customers WHERE Name LIKE 'Herb%'

25. Zwischen Montag and Dienstag

Heute ist Mittwoch, wir kommen auf der Arbeit an und merken, dass unser neuer Angestellter für die Datenpflege alle Bestellungen für Montag und Dienstag falsch eingegeben hat. Wir wollen unserem neuen Azubi beibringen, wie er all die falschen Einträge finden und korrigieren kann. Was ist der einfachste Weg, um alle Einträge der Bestellungentabelle aufzurufen, die am Montag und Dienstag eingegeben wurden?  Die BETWEEN Klausel macht so etwas kinderleicht:

1
2
SELECT ID FROM Orders WHERE
Date BETWEEN ‘01/12/2018’ AND ‘01/13/2018’

Die Schnittmenge zwischen zwei Tabellen finden

Ohne Zweifel ist der Grund, warum Relationale Datenbanken überhaupt existieren, dass sich passende Einträge in zwei Tabellen finden lassen! Der JOIN Befehl ermöglicht diese Kernfunktion von SQL und macht diese Aufgabe ein Leichtes. Hier holen wir uns eine Liste aller Einträge, die sowohl auf die Kunden- wie die Bestellungentabelle zutreffen:

1
2
SELECT ID FROM Customers INNER
JOIN Orders ON Customers.ID = Orders.ID

Die Motivation hinter INNER JOIN ist es in diesem Fall alle Einträge in der Kundentabelle auszuwählen, die passende Customer ID Werte in der Bestellungen Tabelle aufweisen und nur diese wiederzugeben. Natürlich gibt es jede Menge Arten von JOIN, zum Beispiel FULL, SELF, und LEFT. Lass es uns für den Anfang aber unkompliziert belassen und lass uns noch mehr und vielfältigere Abfragen anschauen.

27. Mit Hilfe von UNION die Kräfte verdoppeln

Wir können die Ergebnisse zweier SQL Abfragen ganz natürlich mit dem UNION Schlüsselwort in eine kombinieren. Nehmen wir an, wir möchten eine neue Tabelle erstellen, in dem wir „Customer_name“ und „phone“ von der Kundentabelle mit einer Liste der letzten Aufträge dieses Kunden kombinieren, sodass wir Muster erkennen können und eventuelle zukünftige Einkäufe vorhersehen können. Hier ist ein schneller Weg um diese Aufgabe auszuführen:

1
2
SELECT phone FROM Customers
UNION SELECT item FROM Orders

Das UNION Schlüsselwort macht es möglich JOINS und andere Kriterien zu kombinieren um ein sehr mächtiges Potential der Tabellengenerierung zu erreichen.

Spalten Labels leserlicher gestalten

Das Vergeben von Pseudonymen bei Spaltenlabels macht es uns einfach Spaltenlabels in etwas leichter Lesbares umzubenennen.  Es gibt einen Kompromiss, den man beim Benennen von Spalten eingeht, um sie kurz und knapp zu machen. Das resultiert im täglichen Gebrauch oft in reduzierter Lesbarkeit. In unserer Bestellungentabelle enthält die Item Spalte die Beschreibung der eingekauften Produkte. Lass uns mal anschauen wie wir die Item Spalte temporär umbenennen können, damit sie für den Nutzer einfach zu lesen ist:

1
SELECT Item AS item_description FROM Orders

29. Immer und überall!

Wäre es nicht toll, wenn es einen Bedingungssatz gäbe, auf den du dich immer verlassen kannst?  Die SQL Befehle, die ANY und ALL benutzen, können diesen Traum zur Wirklichkeit werden lassen! Lass uns mal anschauen, wie das ALL Schlüsselwort benutzt wird, um nur dann Einträge mit einzubeziehen, wenn der Bedingungssatz auf ALLE Einträge zutrifft. In dem folgenden Beispiel werden wir Einträge aus der Bestellungentabelle ausgeben, bei denen es die Idee ist, eine Liste mit einem großen Volumen von Bestellungen für eine gewisse Position zu bekommen. In diesem Fall für Kunden, die mehr als 50 dieses Produkts bestellt haben.

1
2
3
4
SELECT Item FROM Orders
WHERE id = ALL
(SELECT ID FROM Orders
WHERE quantity > 50)

30. Entwicklerfreundliches SQL schreiben

Ein oft übersehenes aber trotzdem sehr wichtiges Element des SQL Skripts ist es Kommentare zu einem Abfragenskript hinzuzufügen, welches erklärt, was die Abfrage erzielt. Das macht es zukünftigen Entwicklern leichter, die alte Abfragen überarbeiten und aktualisieren wollen.
Die — Einzeiler- und die /* .. */ Mehrzeilenbegrenzer ermöglichen es uns, nützliche Kommentare zu unserem Skript hinzuzufügen. Dies kann aber auf einem anderen sehr wertvollen Weg erreicht werden. Manchmal wird ein Teil des Codes nicht genutzt. Wir möchten ihn aber nicht löschen, da wir ihn vielleicht noch einmal gebrauchen können. Hier können wir einfach einen Kommentarbegrenzer hinzufügen um den Code für den Moment zu deaktiveren:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
/*Die untere Abfrage wird nicht ausgeführt, da sie mit Kommentar versehen ist*/
/*
SELECT item FROM Orders
WHERE date ALL = (SELECT Order_ID FROM Orders
WHERE quantity > 50)
*/
 
/* die sql Abfrage wird ausgeführt
der Text nach “--" wird ignoriert
*/
 
SELECT item -- single comment
FROM Orders -- another single comment
WHERE id
ALL = (SELECT ID FROM Orders
WHERE quantity > 25)

31. SQL Anweisungen für Datenbank Management

Bis jetzt haben wir SQL Abfragenbefehle erkundet, um Tabellen abzufragen und Einträge von mehreren Abfragen zu kombinieren. Jetzt wird es Zeit einen Schritt weiter zu gehen und uns die Datenbank auf einem strukturellen Level anzuschauen. Fangen wir mal mit dem einfachsten SQL Befehl überhaupt an, nämlich dem, der eine neue Datenbank erstellt. Hier werden wir eine neue Datenbank als Behälter für unsere Kunden- und Bestellungentabelle verwenden, welche wir in unseren zehn letzten Beispielen bereits kennengelernt haben:

1
CREATE DATABASE AllSales

Neue Tabellen zu unserer neuen DB hinzufügen

Als Nächstes werden wir die Kundentabelle hinzufügen, die wir in unseren vorherigen Beispielen bereits genutzt haben und fügen die Spaltennamen hinzu, mit denen wir uns schon bekannt gemacht haben:

1
2
3
4
5
6
CREATE TABLE Customers (
ID varchar(80),
Name varchar(80),
Phone varchar(20),
....
);

Obwohl die meisten Datenbanken mit Programmen wie Access oder OpenOffice erstellt werden, ist es wichtig zu wissen, wie man Datenbanken und Tabellen auf Code-Ebene erstellt und löscht mit Hilfe von SQL Befehlen. Dies ist besonders der Fall, wenn man neue Webanwendungen installiert und die Benutzeroberfläche neue Nutzer darum bittet, die Namen der Datenbanken einzugeben, die während der Installation hinzugefügt werden sollen.

Tabellen mit SQL modifizieren und löschen

Stell dir vor, du möchtest eine Geburtstagskarte an deine Kunden schicken, um deine Dankbarkeit für die Geschäfte mit ihnen auszudrücken. Du willst also ein Geburtstagsfeld zu deiner Kundentabelle hinzufügen. SQL macht es dir einfach existierende Tabellen mit dem ALTER zu modifizieren:

1
ALTER TABLE Customers ADD Birthday varchar(80)

Falls eine Tabelle durch falsche Daten beschädigt wurde, kannst du sie wie folgt auch ganz einfach löschen:

1
DROP TABLE table_name

Der Schlüssel zu einer erfolgreichen Indizierung

Akkurates Indizieren erfordert, dass die Primärschlüsselspalte nur einzigartige Werte enthält, aus genau diesem Grund. Das garantiert, dass JOIN Befehle die Integrität waren und valide Treffer ausgeben. Lass uns noch einmal unsere Kundentabelle erstellen und die ID Spalte als Primärschlüssel festlegen:

1
2
3
4
5
CREATE TABLE Customers (
ID int NOT NULL,
Name varchar(80) NOT NULL,
PRIMARY KEY (ID)
);

Wir können die Funktionalität des Primärschlüssels ausbauen, sodass er automatisch von einer Basis hochzählt. Ändere den ID Eintrag von oben, um das AUTO_INCREMENT Schlüsselwort wie im folgenden Befehl hinzuzufügen:

1
ID int NOT NULL AUTO_INCREMENT

35. Fortgeschrittene Konzepte um die Leistung zu verbessern

Auch wenn es praktisch erscheint, ist es besser die Spaltennamen in einen SELECT Befehl zu schreiben, als den * Begrenzer als Wildcard zu benutzen und alle Spalten auszuwählen. Der SQL Server muss eine Suche durchführen, um all die Spalten in deiner Tabelle zu finden und sie für dich in einen Befehl zu schreiben (für jedes Mal, in dem der SELECT Befehl ausgeführt wird). Zum Beispiel:

1
SELECT * FROM Customers

würde in der Tat wesentlich schneller in unserer Datenbank ausgeführt werden, wenn du es so formulierst:

1
2
SELECT Name, Birthday, Phone,
Address, Zip FROM Customers

Leistungsabbrüche können auf viele verschiedene Arten umgangen werden. Zum Beispiel: Verschwende keine Zeit damit den SQL Server dazu zu zwingen, die System/Master Datenbank jedes Mal zu prüfen, indem du nur gespeicherte Prozessnamen nutzt und setze nie das Präfix SP_. Das Einsetzen von NOCOUNT ON reduziert ebenfalls die Zeit, die der SQL Server benötigt, um Zeilen zu zählen, die von INSERT, DELETE und anderen Befehlen betroffen sind. Das Verwenden von INNER JOIN mit einer Bedingung ist wesentlich schneller, als WHERE Klauseln, gepaart mit einer Bedingung. Wir raten Entwicklern dazu ihr Wissen über SQL Serverabfragen daher auf ein fortgeschrittenes Niveau zu bringen, genau aus diesem Grund. Aus Produktionsgründen können diese Tipps essentiell für eine adäquate Leistung sein. Falls du es noch nicht bemerkt hast, unsere Beispiele favorisieren INNER JOIN.

36. Konditionale Unterabfragen Resultate

Der SQL Operator EXISTS prüft die Existenz von Einträgen in Unterabfragen und gibt den Wert TRUE zurück, wenn eine Unterabfrage einen oder mehrere Einträge zurückgibt. Schau dir einmal diese Abfrage mit einer Unterabfrage Bedingung an:

1
2
3
SELECT Name FROM Customers WHERE EXISTS
(SELECT Item FROM Orders
WHERE Customers.ID = Orders.ID AND Price > 50)

In dem Beispiel oben gibt SELECT einen Wert TRUE wieder, wenn die Bestellungen eines Kunden weniger als 50$ betragen.

37. Auswahl von Tabelle zu Tabelle kopieren

Es gibt unzählige Verwendungsmöglichkeiten für dieses SQL Werkzeug. Nehmen wir an, du willst die jährliche Bestellungentabelle in einer großen Archivtabelle abspeichern. Das nächste Beispiel zeigt dir, wie das funktioniert.

1
2
3
INSERT INTO Yearly_Orders
SELECT * FROM Orders
WHERE Date<=1/1/2018

Dieses Beispiel wird alle Einträge des Jahres 2018 zu dem Archiv hinzufügen

38. NULL Resultate einfangen

In Fällen, bei denen NULL Werte in einem Feld erlaubt sind, werden Kalkulationen auf diesen Werten ebenfalls NULL als Ergebnis herausgeben. Das kann umgangen werden indem man den IFNULL Operator nutzt. In diesem nächsten Beispiel wird ein Wert von Null wiedergegeben, im Gegensatz zu dem Wert NULL, falls die Kalkulation auf ein Feld mit einem NULL Wert trifft:

1
2
3
SELECT Item, Price *
(QtyInStock + IFNULL(QtyOnOrder, 0))
FROM Orders

39. HAVING kann Erleichterung bringen!

Das Problem war, dass die SQL WHERE Klausel nicht auf zusammengefügten Funktionen agieren konnte. Dieses Problem wurde durch die HAVING Klausel gelöst. Zur Veranschaulichung gibt diese nächste Abfrage eine Liste von Kunden wieder, gruppiert nach Region, bei denen mindestens ein Kunde pro Region gespeichert ist:

1
2
3
4
SELECT COUNT(ID), Region
FROM Customers
GROUP BY Region
HAVING COUNT(ID) > 0;

40. Mach den Sack zu mit Strings!

Lass uns einen Blick auf das Verarbeiten der Inhalte von Felddaten mit Hilfe von Funktionen werfen. Substring ist wahrscheinlich die wertvollste all dieser eingebauten Funktionen. Sie gibt dir fast alle der Möglichkeiten von Regex, ist aber lange nicht so kompliziert. Nehmen wir an, du möchtest den Substring links von den Punkten in einer Web-Adresse finden. So schaffst du das mit einem SQL SELECT Befehl:

1
SELECT SUBSTRING_INDEX("www.bytescout.com", ".", 2);

Diese Zeile wird alles links nach dem zweiten Vorkommen von “.” wiedergeben. In unserem Fall wäre das:

https://example.com

Deprecated: Directive 'allow_url_include' is deprecated in Unknown on line 0