Important Update
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.
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;
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;
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';
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;
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;
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.
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;
SUM kalkuliert die Summe der Attribute, die in dem jeweiligen Argument enthalten sind.
Select SUM(Salary)FROM Employee WHERE Emp_Age < 30;
Ganz einfach – der Durchschnitt des genannten Attributs.
SELECT AVG(Price)FROM Products;
Diese Abfrage listet alle Views auf, die in diesem Schema enthalten sind.
SELECT * FROM My_Schema.views;
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;
Die Standard Syntax für das Auswählen von Attributen einer Tabelle lässt sich auch auf eine View anwenden.
SELECT * FROM Failing_Students;
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;
Diese Abfrage löscht die View mit dem Namen “V1”.
DROP VIEW V1
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'
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'
Ein einzigartiger Schlüssel erlaubt es einer Spalte sicherzugehen, dass alle ihrer Werte einzigartig sind.
SELECT * FROM Sys.Objects WHERE Type='uq'
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'
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'
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'
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!
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
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
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;
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%'
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’
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.
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.
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
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)
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)
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
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.
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
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
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.
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.
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
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
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;
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!
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.
SELECT COALESCE(NULL,NULL,'ByteScout',NULL,'Byte')
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.
SELECT CONVERT(int, 27.64)
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
“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
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.
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.
SELECT dtno,
eno,
emname,
job,
salary,
LAG(sal, 1, 0) OVER (PARTITION BY dtno ORDER BY salary) AS salary_prev
FROM employee;
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
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
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.
SELECT
prdid, SUM(amount),
PERCENT_RANK() OVER (ORDER BY SUM(amount) DESC) AS percent_rank
FROM sales
GROUP BY prdid
ORDER BY prdid;
PRDID SUM(AMOUNT) PERCENT_RANK
----------- ----------- ------------
1 22623.5 0
2 223927.08 1
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
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
“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.
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) ]
SELECT empid,
name,
dno,
salary,
job,
CORR(SYSDATE - joiningdate, salary) OVER () AS my_corr_val
FROM employee;
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)
SELECT empid,
name,
dno,
salary,
NTILE(6) OVER (ORDER BY salary) AS container_no
FROM employee;
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
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.
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.
MEASURES STRT.tstamp AS initial_tstamp,
LAST(UP.tstamp) AS first_tstamp,
LAST(DOWN.tstamp) AS finished_tstamp
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)
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.
FIRST_VALUE
{ (expr) [NULLS ]
| (expr [NULLS ])
}
OVER (analytic clause)
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.
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;
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
“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.
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 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.
In this digital age, everything and everyone is becoming more dependent on the internet, digital devices, and the cloud. Therefore, the emphasis on internet security...
Since May 2019 started, we already have a splendid UPDATE for our SDK and freeware products! As always, you can trust in ByteScout professional approach...