TOP-40 Wichtigsten SQL Abfragen - ByteScout
  • Home
  • /
  • Blog
  • /
  • TOP-40 Wichtigsten SQL Abfragen

TOP-40 Wichtigsten SQL Abfragen

SQL ist 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 31. SQL Anweisungen für Datenbank Management
2. Abfrage um Spalten einer Tabelle auszuwählen 32. Neue Tabellen zu unserer neuen DB hinzufügen
3. Abfrage um Daten auszugeben mit Hilfe eines Constraints 33. Tabellen mit SQL modifizieren und löschen
4. Abfrage um sortierte Daten auszugeben mit Hilfe von “Order By” 34. Der Schlüssel zu einer erfolgreichen Indizierung
5. Abfrage um sortierte Daten auszugeben mit Hilfe von “Group By” 35. Fortgeschrittene Konzepte um die Leistung zu verbessern
6. Daten Manipulation mit Hilfe von COUNT 36. Konditionale Unterabfragen Resultate
7. Daten Manipulation mit Hilfe von SUM 37. Auswahl von Tabelle zu Tabelle kopieren
8. Daten Manipulation mit Hilfe von AVG 38. NULL Resultate einfangen
9. Abfrage um alle Views aufzulisten 39. HAVING kann Erleichterung bringen!
10. Abfrage um eine View zu erstellen 40. Mach den Sack zu mit Strings!
11. Abfrage um eine View wiederzugeben 41. Nutze COALESCE zur Rückgabe des ersten Ausdrucks ungleich Null
12. Abfrage um eine View zu aktualisieren 42. Nutze Convert, um einen beliebigen Wert in einen bestimmten Datentyp umzuwandeln
13. Abfrage um eine View zu löschen 43. Die DENSE_RANK() Analyse-Abfrage
14. Abfrage um Benutzer Tabellen anzuzeigen 44. Die “Query_partition” Klausel
15. Abfrage um Primärschlüssel anzuzeigen 45. Die letzten fünf Datensätze einer Tabelle finden
16. Abfrage um einzigartige Schlüssel anzuzeigen 46. LAG
17. Fremdschlüssel anzeigen 47. LEAD
18. Trigger anzeigen 48. PERCENT_RANK
19. Interne Tabellen anzeigen 49. MIN
20. Eine Liste der Prozesse anzeigen 50. MAX
21. Die Werte zweier Spalten einer Tabelle tauschen 51. “Top- N” Abfragen
22. Eine Spalte mit einzigartigen Werten wiedergeben 52. Die “CORR” Analyse-Abfrage
23. Eine Top 25 erstellen mit der SELECT TOP Klausel 53. Die “NTILE” Analyse-Abfrage
24. Nach SQL Tabellen suchen mit Hilfe von Wildcards 54. Die VARIANCE, VAR_POP und VAR_SAMP Abfragen
25. Zwischen Montag and Dienstag 55. STDDEV, STDDEV_POP und STDDEV_SAMP Abfragen
26. Die Schnittmenge zwischen zwei Tabellen finden 56. Pattern Matching
27. Mit Hilfe von UNION die Kräfte verdoppeln 57. FIRST_VALUE
28. Spalten Labels leserlicher gestalten 58. LAST_VALUE
29. Immer und überall! 59. Die “PREDICTION” Funktion
30. Entwicklerfreundliches SQL schreiben 60. CLUSTER_SET

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;

2. 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.

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.

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.

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.

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.

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

7. Daten Manipulation mit Hilfe von SUM

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

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

8. Daten Manipulation mit Hilfe von AVG

Ganz einfach – der Durchschnitt des genannten Attributs.

SELECT AVG(Price)FROM Products;

9. Abfrage um alle Views aufzulisten

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

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.

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.

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.

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”.

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.

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.

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.

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.

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.

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.

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:

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:

UPDATE Customers SET Zip=Phone, Phone=Zip

22. 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:

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:

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:

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:

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

26. 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:

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:

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.

28. 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:

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.

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:

/*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:

CREATE DATABASE AllSales

32. 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:

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.

33. 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:

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:

DROP TABLE table_name

34. 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:

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:

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:

SELECT * FROM Customers

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

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:

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.

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:

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:

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:

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

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

<a href="https://bytescout.com">www.bytescout.com</a>

..und 20 weitere hilfreiche SQL-Abfragen-Beispiele!

41. Nutze COALESCE zur Rückgabe des ersten Ausdrucks ungleich Null

Das SQL-Coalesce wird zum Verwalten der NULL-Werte der Datenbank genutzt. Bei dieser Methode werden die NULL-Werte mit dem benutzerdefinierten Wert ersetzt. Die SQL-Coalesce-Funktion beurteilt die Parameter in Reihe und liefert immer den ersten Wert ungleich Null aus dem angegebenen Datensatz von Argumenten.

Syntax

SELECT COALESCE(NULL,NULL,'ByteScout',NULL,'Byte')

Ausgabe

ByteScout

42. Nutze Convert, um einen beliebigen Wert in einen bestimmten Datentyp umzuwandeln

Es wird verwendet, um einen Wert in einen definierten Datentyp zu konvertieren. Zum Beispiel, wenn man einen bestimmten Wert in einen Integer Datentyp konvertieren möchte, kann dafür die “Convert” Funktion genutzt werden.

Syntax

SELECT CONVERT(int, 27.64)

Ausgabe

27

43. Die DENSE_RANK() Analyse-Abfrage

Es ist eine analytische Abfrage, die den Rang einer Zeile in einer angeordneten Sammlung von Zeilen berechnet. Ein Ausgaberang ist eine Zahl ab 1. DENSE_RANK ist eine der wichtigsten analytischen SQL-Abfragen. Sie gibt Rangeinstellungen als fortlaufende Zahlen zurück. Im Falle einer Beziehung steigt der Rang nicht. Beispielsweise wird die folgende Abfrage die sequentiellen Ränge einem Mitarbeiter geben.

SELECT eno,
dno,
salary,
DENSE_RANK() OVER (PARTITION BY dno ORDER BY salary) AS ranking
FROM employee;

ENO  DNO SALARY RANKING
---------- ---------- ---------- ----------
7933  10 1500   1
7788  10 2650   2
7831  10 6000   3
7362  20 900    1
7870  20 1200   2
7564  20 2575   3
7784  20 4000   4
7903  20 4000   4
7901  30 550    1
7655  30 1450   2
7522  30 1450   2
7844  30 1700   3
7493  30 1500   4
7698  30 2850   5

44. Die “Query_partition” Klausel

“query_partition_clause” unterteilt den Ausgabesatz in Verteilungen oder Sammlungen von Daten. Die Entwicklung der analytischen Abfrage wird von den Grenzen beschränkt, die durch die sich auf den Prozess beziehenden Partitionen erzwungen werden. Eine “GROUP BY” Klausel ändert die Leistung einer Aggregatfunktion. Wird das “query_partition_clause” entfernt, wird die gesamte Ausgabesammlung als separate Partition interpretiert.

Die folgende Abfrage verwendet eine OVER-Klausel, damit der angezeigte Durchschnitt auf die Datensätze des Ausgabesatzes basiert ist.

SELECT eno, dno, salary,
AVG(salary) OVER () AS avg_sal
FROM employee;

EO  DNO SALARY AVG_SAL
---------- ---------- ---------- ----------
7364 20 900 2173.21428
7494 30 1700 2173.21428
7522 30 1350 2173.21428
7567 20 3075 2173.21428
7652 30 1350 2173.21428
7699 30 2950 2173.21428
7783 10 2550 2173.21428
7789 20 3100 2173.21428
7838 10 5100 2173.21428
7845 30 1600 2173.21428
7877 20 1200 2173.21428
7901 30 1050 2173.21428
7903 20 3100 2173.21428
7935 10 1400 2173.21428

45. Die letzten fünf Datensätze einer Tabelle finden

Nun, möchte man die letzten acht Datensätze aus einer Tabelle abrufen, ist es immer schwierig solche Daten abzurufen, wenn die Tabelle umfangreiche Informationen enthält. Zum Beispiel, möchte man die letzten acht Datensätze aus der Tabelle “employee” abrufen, können “rownum” und eine “Union” Klausel genutzt werden. Rownum ist in SQL temporär.

Beispielsweise,

Select * from Employee A where rownum <=8
union
select * from (Select * from Employee A order by rowid desc) where rownum <=8;

Die obige SQL-Abfrage wird die letzten acht Datensätze der Tabelle “employee” angeben, wobei “rownum” eine Pseudospalte ist. Es indiziert die Daten in einem Ausgabesatz.

46. LAG

LAG wird zum Abrufen von Daten aus einer vorherigen Zeile angewendet. Es ist eine analytische Funktion. Zum Beispiel gibt die folgende Abfrage das Gehalt aus der vorherigen Zeile an, um die Differenz zwischen dem Gehalt der aktuellen Zeile und dem der vorherigen Zeile zu berechnen. Bei dieser Abfrage wurde das ORDER BY der LAG-Funktion angewendet. Der Standardwert ist eins, wenn kein Versatz definiert wird.
Die willkürliche Standardbedingung wird angegeben, wenn der Versatz den Fensterbereich überschreitet. Der Standardwert ist null, wenn kein Standardwert definiert wird.

Syntax

SELECT dtno,
       eno,
       emname,
       job,
       salary,
       LAG(sal, 1, 0) OVER (PARTITION BY dtno ORDER BY salary) AS salary_prev
FROM   employee;

Ausgabe

DTNO ENO ENAME JOB SAL SAL_PREV
---------- ---------- ---------- --------- ---------- ----------
10 7931 STEVE CLERK 1300 0
10 7783 JOHN MANAGER 2450 1300
10 7834 KING PRESIDENT 5000 2450
20 7364 ROBIN CLERK 800 0
20 7876 BRIAN CLERK 1100 800
20 7567 SHANE MANAGER 2975 1100
20 7784 SCOTT ANALYST 3000 2975
20 7908 KANE ANALYST 3000 3000
30 7900 JAMES CLERK 950 0
30 7651 CONNER SALESMAN 1250 950
30 7522 MATTHEW SALESMAN 1250 1250
30 7843 VIVIAN SALESMAN 1500 1250
30 7494 ALLEN SALESMAN 1600 1500
30 7695 GLEN MANAGER 2850 1600

47. LEAD

LEAD ist auch eine analytische Abfrage, die zum Abrufen von Daten aus Zeilen angewendet wird, die sich zusätzlich im Ausgabesatz befinden. Die folgende Abfrage gibt das Gehalt aus der nächsten Zeile an, um die Abweichung zwischen der vorherrschenden Zeile und der anschließenden Zeile zu berechnen. Der Standardwert ist 1, wenn kein Versatz definiert wird. Die willkürliche Standardbedingung wird angegeben, wenn der Versatz den Fensterbereich überschreitet. Der Standardwert ist null, wenn kein Standardwert definiert wird.

SELECT eno,
       empname,
       job,
       salary,
       LEAD(salary, 1, 0) OVER (ORDER BY salary) AS salary_next,
       LEAD(salary, 1, 0) OVER (ORDER BY salary) - salary AS salary_diff
FROM   employee;

ENO EMPNAME JOB SALARY SALARY_NEXT SALARY_DIFF
---------- ---------- --------- ---------- ---------- ----------
7369 STEVE CLERK 800 950 150
7900 JEFF CLERK 950 1100 150
7876 ADAMS CLERK 1100 1250 150
7521 JOHN SALESMAN 1250 1250 0
7654 MARK SALESMAN 1250 1300 50
7934 TANTO CLERK 1300 1500 200
7844 MATT SALESMAN 1500 1600 100
7499 ALEX SALESMAN 1600 2450 850
7782 BOON MANAGER 2450 2850 400
7698 BLAKE MANAGER 2850 2975 125
7566 JONES MANAGER 2975 3000 25
7788 SCOTT ANALYST 3000 3000 0
7902 FORD ANALYST 3000 5000 2000
7839 KING PRESIDENT 5000 0 -5000

48. PERCENT_RANK

Die analytische “PERCENT_RANK” Abfrage. Die “ORDER BY” Klausel ist für diese Abfrage notwendig. Durch das Ausschließen einer Partitionierungsklausel von der “OVER” Klausel wird festgelegt, dass der ganze Ausgabesatz als eine separate Partition interpretiert wird. Die erste Zeile des standardisierten Satzes wird als 0 angegeben und die letzte Zeile des Satzes als 1. Zum Beispiel gibt die angezeigte SQL-Abfrage die folgende Ausgabe an.

Syntax

SELECT
     prdid, SUM(amount),
     PERCENT_RANK() OVER (ORDER BY SUM(amount) DESC) AS percent_rank
     FROM sales
     GROUP BY prdid
     ORDER BY prdid;

Ausgabe

PRDID        SUM(AMOUNT)  PERCENT_RANK
----------- ----------- ------------
          1    22623.5            0
          2   223927.08           1

49. MIN

Durch die Verwendung einer leeren “OVER” Klausel wird MIN in eine Analysefunktion konvertiert. Es ist eine weitere analytische Abfrage. Dabei wird der ganze Ergebnissatz als eine einzelne Partition interpretiert. Sie gibt das Minimalgehalt für alle Mitarbeiter und ihre ursprünglichen Daten an. Zum Beispiel zeigt die folgende Abfrage die Verwendung von MIN in der Auswahlabfrage an.

SELECT eno,
       empname,
       dtno,
       salary,
       MIN(salary) OVER (PARTITION BY dtno) AS min_result
FROM   employee;

     ENO   EMPNAME          DTNO     SALARY MIN_RESULT
---------- ---------- ---------- ---------- ---------------
      7782 CLARK              10       2450            1300
      7839 KING               10       5000            1300
      7934 MILLER             10       1300            1300
      7566 JONES              20       2975             800
      7902 FORD               20       3000             800
      7876 ADAMS              20       1100             800
      7369 SMITH              20        800             800
      7788 SCOTT              20       3000             800
      7521 WARD               30       1250             950
      7844 TURNER             30       1500             950
      7499 ALLEN              30       1600             950
      7900 JAMES              30        950             950
      7698 BLAKE              30       2850             950
      7654 MARTIN             30       1250             950

50. MAX

Durch Verwendung einer “OVER” Klausel in einer leeren Zeile wird MAX in eine Analysefunktion konvertiert. Der Mangel an einer Partitionierungsklausel zeigt an, dass der gesamte Ausgabesatz als eine separate Partition interpretiert wird. Das gibt das Maximalgehalt für alle Mitarbeiter und ihre ursprünglichen Daten an. Zum Bespiel zeigt die folgende Abfrage die Verwendung von MAX in der select-Abfrage.

SELECT eno,
       empname,
       dtno,
       salary,
       MAX(salary) OVER () AS max_result
FROM   employee;

     ENO   EMPNAME          DTNO       SALARY    MAX_RESULT
---------- ---------- ---------- ---------- ----------
      7369 SMITH              20        800       3000
      7499 ALLEN              30       1600       3000
      7521 WARD               30       1250       3000
      7566 JONES              20       2975       3000
      7654 MARTIN             30       1250       3000
      7698 BLAKE              30       2850       3000
      7782 CLARK              10       2450       3000
      7788 SCOTT              20       3000       3000
      7839 KING               10       5000       3000
      7844 TURNER             30       1500       3000
      7876 ADAMS              20       1100       3000
      7900 JAMES              30        950       3000
      7902 FORD               20       3000       3000
      7934 MILLER             10       1300       3000

51. “Top- N” Abfragen

“Top-N” Abfragen bieten einen Prozess zum Einschränken der Anzahl von Zeilen an, die von organisierten Datensammlungen geliefert werden. Sie sind bemerkenswert vorteilhaft, wenn Nutzer die obere oder die untere Anzahl an Zeilen aus einer Tabelle angeben möchten.

Die folgende Abfrage stellt beispielsweise die zwanzig Zeilen mit zehn unterschiedlichen Werte dar:

SELECT price
FROM   sales_order
ORDER BY price;

PRICE
----------
100
100
200
200
300
300
400
400
500
500
600

PRICE
----------
600
700
700
800
800
900
900
1000
1000

20 rows selected.

52. Die “CORR” Analyse-Abfrage

Die “CORR” Analysefunktion wird zur Bestimmung der Korrelationskoeffizient verwendet. Diese Abfrage wird genutzt, um die Korrelationskoeffizient von Pearson zu berechnen. Die Funktion berechnet das Folgende für Zeilen in der Tabelle ohne Nullwerte. Diese Abfrage gibt die Werte zwischen +1 und -1 zurück, die Folgendes beschreiben:
Syntax: CORR(exp1, exp2) [ OVER (analytic_clause) ]

Beispiel

SELECT empid,
       name,
       dno,
       salary,
       job,
       CORR(SYSDATE - joiningdate, salary) OVER () AS my_corr_val
FROM   employee;

53. Die “NTILE” Analyse-Abfrage

Die “NTILE” Abfrage gibt den Benutzern die Möglichkeit, einen Sequenzsatz in eine detaillierte Anzahl relativ ähnlicher Gruppen oder Containern von Sanktionszeilen aufzuteilen. Ist die Anzahl an Zeilen in der Sammlung kleiner als die Anzahl an definierten Containern, wird die Anzahl an Containern verringert. Die Basis-Syntax wurde unten angewandt:

NTILE(exp) OVER ([ partition_clause ] order_by)

Beispiel

SELECT empid,
       name,
       dno,
       salary,
       NTILE(6) OVER (ORDER BY salary) AS container_no
FROM   employee;

54. Die VARIANCE, VAR_POP und VAR_SAMP Abfragen

Die VARIANCE, VAR_POP und VAR_SAMP sind Aggregatfunktionen. Sie werden individuell angewandt, um die Varianz, die Gruppenvarianz oder die Stichprobenvarianz einer Datensammlung zu berechnen. Als Aggregatabfragen verringern sie die Anzahl an Zeilen, daher der Ausdruck “aggregat”. Wenn die Daten nicht angeordnet sind, ändert man die Gesamtzahl der Zeilen in der “Employee” Tabelle in eine separate Zeile mit aggregierten Werten. Die folgende Abfrage zeigt ein Beispiel für die Verwendung dieser Funktionen.

SELECT VARIANCE(salary) AS var_salary,
       VAR_POP(salary) AS pop_salary,
       VAR_SAMP(salary) AS samp_salary
FROM   employee;

VAR_SALARY   POP_SALARY   SAMP_SALARY
------------ ----------- ------------
1479414.97  1588574.81   1388717.27

55. STDDEV, STDDEV_POP und STDDEV_SAMP Abfragen

STDDEV, STDDEV_POP und STDDEV_SAMP Abfragen werden angewandt, um individuell die Standardabweichung, Bevölkerungsstandardabweichung und die kumulative Standardabweichung der Stichprobe zu berechnen. Als Aggregatabfragen verringern sie die Anzahl an Zeilen, daher der Ausdruck “aggregat”. Wenn die Daten nicht angeordnet sind, wandelt man alle Zeilen in der “EMPLOYEE” Tabelle in eine separate Zeile um. Die folgende Abfrage stellt ein Beispiel für die Verwendung aller diesen Funktionen dar:

SELECT STDDEV(salary) AS stddev_salary,
       STDDEV_POP(salary) AS pop_salary,
       STDDEV_SAMP(salary) AS samp_salary
FROM   employee;

STDDEV_SALARY POP_SALARY SAMP_SALARY
---------- -------------- ---------------
1193.50     1159.588      1193.603

Gibt es mehr als ein Konto nach Entfernen der Nullen, zeigt die “STDDEV” Funktion das Resultat von “STDDEV-SAMP” an. Durch die Verwendung einer leeren “OVER” Klausel wird das Resultat der “STDDEV” Abfrage in eine Analyse-Abfrage umgewandelt. Die Abwesenheit einer Partitionierung zeigt, dass der ganze Ausgabesatz als eine bestimmte Partition interpretiert wird, deswegen werden die Standardabweichung des Gehalts und die Primärdaten akzeptiert.

56. Pattern Matching

Die Pattern Matching-Syntax fügt verschiedene Alternativen hinzu. Daten müssen präzis und formgerecht behandelt werden. Die “PARTITION BY” und “ORDER BY” Bedingungen aller analytischen SQL-Abfragen werden angewandt, um Daten in Ansammlungen und in jeder Gruppe aufzuteilen. Werden keine Partitionen festgelegt, wird der ganze Sequenzsatz als eine riesige Partition betrachtet.

Zum Beispiel,

Die “MEASURES” Klausel legt das Spaltenresultat für jede Übereinstimmung, die bereitgestellt wird, fest.

Syntax

MEASURES  STRT.tstamp AS initial_tstamp,
          LAST(UP.tstamp) AS first_tstamp,
          LAST(DOWN.tstamp) AS finished_tstamp

Beispiel

DEFINE
  UP AS UP.products_sold > PREV(UP.products_sold),
  FLAT AS FLAT.products_sold = PREV(FLAT.products_sold),
  DOWN AS DOWN.products_sold < PREV(DOWN.products_sold)

57. FIRST_VALUE

Die einfachste Weise, die analytischen Funktionen zu verstehen, ist mit dem Studieren der Aggregatfunktionen zu beginnen. Eine Aggregatfunktion sammelt Daten aus zahlreichen Zeilen in eine einzigartige resultierende Zeile. Zum Beispiel können Nutzer die “AVG” Funktion anwenden, um einen Durchschnitt aller Gehälter aus der “EMPLOYEE” Tabelle zu erhalten. Lass uns mal sehen, wie First-Value (Erster Wert) genutzt werden kann. Die hauptsächliche Erläuterung für die “First-Value” Analysefunktion wurde unten angezeigt.

Syntax:

FIRST_VALUE 
  { (expr) [NULLS ]
  | (expr [NULLS ])
  }
  OVER (analytic clause)

Beispiel

SELECT eno,
       dno,
       salary,
       FIRST_VALUE(salary) IGNORE NULLS 
         OVER (PARTITION BY dno ORDER BY salary) AS lowest_salary_in_dept
FROM   employee;

The above query will ignore null values.

58. LAST_VALUE

Die hauptsächliche Erläuterung für die “Last-Value” (Letzter Wert) Analysefunktion wurde unten angezeigt.

Syntax: LAST_VALUE
  { (expr) [ { NULLS ]
  | (expr [ NULLS ])
  OVER (analytic clause)

Die “Last_Value” Analysefunktion bezieht sich auf die “LAST” Analysefunktion. Die Funktion macht es möglich, dass Benutzer die letzte Ausgabe von einer organisierten Spalte bekommen. Durch die Verwendung der Standard-Fensterverwaltung kann die Ausgabe überraschend sein. Zum Beispiel,

SELECT eno,
       dno,
       salary,
       LAST_VALUE(salary) IGNORE NULLS
         OVER (PARTITION BY dno ORDER BY salary) AS highest_salary_in_dept
FROM   employee;

59. Die “PREDICTION” Funktion

Das Entwurfsbeispiel sagt das Geschlecht und das Alter der Kunden vorher, von denen am meisten erwartet wird, dass sie eine Einverständniskarte annehmen (Ziel = 1). Die “PREDICTION” Funktion verwendet die Preismatrix, in einer Korrelation mit dem Design, und gilt für den Familienstand, und die Hausgröße als Vorhersager. Die Syntax der “PREDICTION” Funktion kann auch einen Teil beliebiger “GROUPING” Information, wenn ein partitioniertes Modell abgerufen wird.

SELECT client_gender, COUNT(*) AS ct, ROUND(AVG(age)) AS average_age
   FROM mining_data_shop
   WHERE PREDICTION(sample COST MODEL
      USING client_marital_status, house_size) = 1
   GROUP BY client_gender
   ORDER BY client_gender;
   
CUST_GENDER         CNT    AVG_AGE
------------ ---------- ----------
F                   270         40
M                   585         41

60. CLUSTER_SET

“CLUSTER_SET” kann Daten durch einen der folgenden Schritten abrufen: Es kann ein Mining-Objekt für die Informationen verwenden, oder kann es Daten durch die Ausführung einer Analyse-Klausel abbauen, die ein oder mehr sich bewegende Mining-Muster erstellt und nutzt.

Dieses Beispiel listet die Eigenschaften auf, die den größten Einfluss auf die Clusterverteilung für die Client-ID 1000 haben. Die Abfrage fordert die Funktionen CLUSTER_DETAILS und CLUSTER_SET an, die das Clustering-Modell “my_sample” verwenden.

Beispiel

SELECT S.cluster_id, prob,
       CLUSTER_DETAILS(my_sample, S.cluster_id, 7 USING T.*) kset
FROM
  (SELECT v.*, CLUSTER_SET(my_sample, USING *) nset
    FROM mining_data
   WHERE client_id = 1000) T,
  TABLE(T.nset) Q
ORDER BY 2 DESC;  

Ein Cluster besteht aus gruppierten Tabellen, die die entsprechenden Datenblöcke verteilen, d.h. alle Tabellen sind eigentlich zusammengebaut. Ein Beispiel dafür sind die Tabellen EMPLOYEE und DEPARTMENT, die durch die “DNO” Spalte verbunden sind. Wenn man ein Cluster davon machen möchte, werden tatsächlich alle Spalten in den gleichen Datenblöcken speichern.

   

About the Author

ByteScout Team ByteScout Team of Writers ByteScout has a team of professional writers proficient in different technical topics. We select the best writers to cover interesting and trending topics for our readers. We love developers and we hope our articles help you learn about programming and programmers.  
prev
next