Was ist SQL? Die Verkehrssprache der Datenanalyse

Heutzutage ist die strukturierte Abfragesprache das Standardmittel zum Bearbeiten und Abfragen von Daten in relationalen Datenbanken, allerdings mit proprietären Erweiterungen unter den Produkten. Die Leichtigkeit und Allgegenwart von SQL hat sogar dazu geführt, dass die Entwickler vieler „NoSQL“ - oder nicht relationaler Datenspeicher wie Hadoop Teilmengen von SQL übernommen oder eigene SQL-ähnliche Abfragesprachen entwickelt haben.

SQL war jedoch nicht immer die „universelle“ Sprache für relationale Datenbanken. Von Anfang an (ca. 1980) hatte SQL gewisse Streiks dagegen. Viele Forscher und Entwickler zu dieser Zeit, einschließlich mir, dachten, dass der Overhead von SQL verhindern würde, dass es jemals in einer Produktionsdatenbank praktikabel wird.

Wir haben uns eindeutig geirrt. Viele glauben jedoch immer noch, dass der Preis für die Laufzeitleistung bei aller Einfachheit und Zugänglichkeit von SQL oft zu hoch ist.

SQL-Verlauf

Bevor es SQL gab, hatten Datenbanken enge Navigationsprogrammierschnittstellen und wurden normalerweise um ein Netzwerkschema herum entworfen, das als CODASYL-Datenmodell bezeichnet wird. CODASYL (Ausschuss für Datensystemsprachen) war ein Konsortium, das für die Programmiersprache COBOL (ab 1959) und die Erweiterung der Datenbanksprache (ab 10 Jahre später) verantwortlich war.

Wenn Sie für eine CODASYL-Datenbank programmiert haben, haben Sie über Sätze, die Eins-zu-Viele-Beziehungen ausdrücken, zu Datensätzen navigiert. Ältere hierarchische Datenbanken erlauben nur, dass ein Datensatz zu einem Satz gehört. In Netzwerkdatenbanken kann ein Datensatz zu mehreren Gruppen gehören.

Angenommen, Sie möchten die in CS 101 eingeschriebenen Schüler auflisten. Zuerst finden Sie "CS 101"in der CoursesGruppe nach Namen, legen fest, dass Sie als Eigentümer oder Eltern der EnrolleesGruppe das erste Mitglied ( ffm) der EnrolleesGruppe finden, das ein StudentDatensatz ist, und listen es. Dann würden Sie in eine Schleife gehen: Finden Sie das nächste Mitglied ( fnm) und listen Sie es auf. Wenn dies fnmfehlschlägt, verlassen Sie die Schleife.

Das mag für den Datenbankprogrammierer viel Arbeit bedeuten, war aber zur Ausführungszeit sehr effizient. Experten wie Michael Stonebraker von der University of California in Berkeley und Ingres wiesen darauf hin, dass diese Art der Abfrage in einer CODASYL-Datenbank wie IDMS ungefähr die Hälfte der CPU-Zeit und weniger als die Hälfte des Arbeitsspeichers in derselben relationalen Datenbank mit SQL in Anspruch nahm .

Zum Vergleich wäre die entsprechende SQL-Abfrage, mit der alle Schüler in CS 101 zurückgegeben werden, ungefähr so 

SELECT student.name FROM Kurse, Teilnehmer, Studenten WHERE Kursname

Diese Syntax impliziert einen relationalen inneren Join (tatsächlich zwei davon), wie ich weiter unten erläutern werde, und lässt einige wichtige Details aus, wie z. B. die für die Joins verwendeten Felder.

Relationale Datenbanken und SQL

Warum sollten Sie die Verbesserung der Ausführungsgeschwindigkeit und der Speichernutzung um den Faktor zwei aufgeben? Es gab zwei große Gründe: einfache Entwicklung und Portabilität. Ich dachte nicht, dass einer von beiden 1980 im Vergleich zu Leistung und Speicherbedarf eine große Rolle spielte, aber als sich die Computerhardware verbesserte und billiger wurde, kümmerten sich die Leute nicht mehr um Ausführungsgeschwindigkeit und Speicher und machten sich mehr Sorgen um die Entwicklungskosten.

Mit anderen Worten, Moores Gesetz hat CODASYL-Datenbanken zugunsten relationaler Datenbanken getötet. Die Verbesserung der Entwicklungszeit war zwar erheblich, aber die SQL-Portabilität erwies sich als Wunschtraum.

Woher kamen das relationale Modell und SQL? EF „Ted“ Codd war ein Informatiker am IBM San Jose Research Laboratory, der in den 1960er Jahren die Theorie des relationalen Modells erarbeitete und 1970 veröffentlichte. IBM implementierte nur langsam eine relationale Datenbank, um die Einnahmen von zu schützen seine CODASYL-Datenbank IMS / DB. Als IBM schließlich sein System R-Projekt startete, war das Entwicklungsteam (Don Chamberlin und Ray Boyce) nicht unter Codd und ignorierte Codds relationales Alpha-Sprachpapier von 1971, um ihre eigene Sprache, SEQUEL (Structured English Query Language), zu entwerfen. Im Jahr 1979, bevor IBM sein Produkt überhaupt veröffentlicht hatte, nahm Larry Ellison die Sprache in seine Oracle-Datenbank auf (unter Verwendung der SEQUEL-Veröffentlichungen vor dem Start von IBM als Spezifikation). SEQUEL wurde bald zu SQL, um eine internationale Markenverletzung zu vermeiden.

Die „Tom-Toms, die für SQL schlagen“ (wie Michael Stonebraker es ausdrückte) kamen nicht nur von Oracle und IBM, sondern auch von Kunden. Es war nicht einfach, CODASYL-Datenbankdesigner und -programmierer einzustellen oder zu schulen, daher sahen SEQUEL (und SQL) viel attraktiver aus. SQL war in den späten 1980er Jahren so attraktiv, dass viele Datenbankanbieter zum großen Entsetzen von Codd, der der Ansicht war, dass relationale Datenbanken von Grund auf neu entworfen werden mussten, um relational zu sein, im Wesentlichen einen SQL-Abfrageprozessor über ihre CODASYL-Datenbanken hefteten.

Eine reine relationale Datenbank, wie sie von Codd entworfen wurde, basiert auf Tupeln, die in Relationen gruppiert sind und mit der Prädikatenlogik erster Ordnung übereinstimmen. In relationalen Datenbanken der realen Welt gibt es Tabellen, die Felder, Einschränkungen und Trigger enthalten, und Tabellen werden über Fremdschlüssel verknüpft. SQL wird verwendet, um die zurückzugebenden Daten zu deklarieren, und ein SQL-Abfrageprozessor und ein Abfrageoptimierer verwandeln die SQL-Deklaration in einen Abfrageplan, der vom Datenbankmodul ausgeführt wird.

SQL enthält eine Subsprache zum Definieren von Schemas, die Datendefinitionssprache (DDL), sowie eine Subsprache zum Ändern von Daten, die Datenmanipulationssprache (DML). Beide haben Wurzeln in frühen CODASYL-Spezifikationen. Die dritte Subsprache in SQL deklariert Abfragen über die SELECTAnweisung und relationale Verknüpfungen.

SQL-  SELECTAnweisung

Die SELECTAnweisung teilt dem Abfrageoptimierer mit, welche Daten zurückgegeben werden sollen, in welchen Tabellen gesucht werden soll, welche Beziehungen zu befolgen sind und in welcher Reihenfolge die zurückgegebenen Daten festgelegt werden sollen. Das Abfrageoptimierungsprogramm muss selbst herausfinden, welche Indizes verwendet werden sollen, um Brute-Force-Tabellenscans zu vermeiden und eine gute Abfrageleistung zu erzielen, es sei denn, die jeweilige Datenbank unterstützt Indexhinweise.

Ein Teil der Kunst des relationalen Datenbankdesigns hängt von der vernünftigen Verwendung von Indizes ab. Wenn Sie einen Index für eine häufige Abfrage weglassen, kann die gesamte Datenbank bei starker Leselast langsamer werden. Wenn Sie zu viele Indizes haben, kann die gesamte Datenbank unter starken Schreib- und Aktualisierungslasten langsamer werden.

Eine weitere wichtige Kunst ist die Auswahl eines guten, einzigartigen Primärschlüssels für jeden Tisch. Sie müssen nicht nur die Auswirkungen des Primärschlüssels auf allgemeine Abfragen berücksichtigen, sondern auch, wie er in Verknüpfungen abgespielt wird, wenn er als Fremdschlüssel in einer anderen Tabelle angezeigt wird, und wie er sich auf die Referenzlokalität der Daten auswirkt.

Im erweiterten Fall von Datenbanktabellen, die je nach Wert des Primärschlüssels in verschiedene Volumes aufgeteilt werden (horizontales Sharding), müssen Sie auch berücksichtigen, wie sich der Primärschlüssel auf das Sharding auswirkt. Hinweis: Sie möchten, dass die Tabelle gleichmäßig über die Volumes verteilt ist. Dies bedeutet, dass Sie keine Datumsstempel oder aufeinanderfolgende Ganzzahlen als Primärschlüssel verwenden möchten.

Diskussionen über die SELECTAussage mögen einfach beginnen, können aber schnell verwirrend werden. Erwägen:

SELECT * FROM Kunden;

Einfach, richtig? Es werden alle Felder und Zeilen der CustomersTabelle abgefragt. Angenommen, die CustomersTabelle enthält hundert Millionen Zeilen und hundert Felder, und eines der Felder ist ein großes Textfeld für Kommentare. Wie lange dauert es, bis alle Daten über eine Netzwerkverbindung mit 10 Megabit pro Sekunde abgerufen werden, wenn jede Zeile durchschnittlich 1 Kilobyte Daten enthält?

Vielleicht sollten Sie reduzieren, wie viel Sie über den Draht senden. Erwägen:

SELECT TOP 100 Firmenname, lastSaleDate, lastSaleAmount, totalSalesAmount FROM Kunden
WO Staat UND Stadt
ORDER BY lastSaleDate DESCENDING;

Jetzt werden Sie viel weniger Daten abrufen. Sie haben die Datenbank gebeten, nur vier Felder anzugeben, nur die Unternehmen in Cleveland zu berücksichtigen und nur die 100 Unternehmen mit den letzten Verkäufen anzugeben. Um dies auf dem Datenbankserver am effizientesten zu tun, Customersbenötigt die Tabelle jedoch einen Index state+cityfür die WHEREKlausel und einen Index lastSaleDatefür die Klauseln ORDER BYund TOP 100.

Gilt übrigens TOP 100für SQL Server und SQL Azure, nicht jedoch für MySQL oder Oracle. In MySQL würden Sie LIMIT 100nach der WHEREKlausel verwenden. In Oracle würden Sie eine Bindung ROWNUMals Teil der WHEREKlausel verwenden, d WHERE... AND ROWNUM <=100. H. Leider gehen die ANSI / ISO-SQL-Standards (und es gibt bisher neun davon, die sich von 1986 bis 2016 erstrecken) nur so weit, dass jede Datenbank ihre eigenen proprietären Klauseln und Funktionen einführt.

SQL-Joins 

Bisher habe ich die SELECTSyntax für einzelne Tabellen beschrieben. Bevor ich JOINKlauseln erklären kann  , müssen Sie Fremdschlüssel und Beziehungen zwischen Tabellen verstehen. Ich werde dies anhand von Beispielen in DDL unter Verwendung der SQL Server-Syntax erklären.

Die Kurzversion davon ist ziemlich einfach. Jede Tabelle, die Sie in Beziehungen verwenden möchten, sollte eine Primärschlüsseleinschränkung haben. Dies kann entweder ein einzelnes Feld oder eine Kombination von Feldern sein, die durch einen Ausdruck definiert sind. Zum Beispiel:

TABELLE ERSTELLEN Personen (
    PersonID int NICHT NULL PRIMARY KEY,
    PersonName char (80),
    ...

Jede Tabelle, auf die Bezug genommen werden muss, Personssollte ein Feld haben, das dem PersonsPrimärschlüssel entspricht. Um die relationale Integrität zu gewährleisten, sollte dieses Feld eine Fremdschlüsseleinschränkung aufweisen. Zum Beispiel:

TABELLE ERSTELLEN Bestellungen (
    OrderID int NICHT NULL PRIMARY KEY,
    ...
    PersonID int AUSLÄNDISCHE SCHLÜSSELREFERENZEN Personen (PersonID)
);

Es gibt längere Versionen beider Anweisungen, die das CONSTRAINTSchlüsselwort verwenden, mit dem Sie die Einschränkung benennen können. Das ist es, was die meisten Datenbank-Design-Tools generieren.

Primärschlüssel sind immer indiziert und eindeutig (die Feldwerte können nicht dupliziert werden). Andere Felder können optional indiziert werden. Es ist oft nützlich, Indizes für Fremdschlüsselfelder und für Felder zu erstellen, die in WHEREund ORDER BYKlauseln erscheinen, obwohl dies nicht immer der Fall ist, da der Schreib- und Aktualisierungsaufwand potenziell hoch ist.

Wie würden Sie eine Abfrage schreiben, die alle von John Doe erteilten Bestellungen zurückgibt?

SELECT PersonName, OrderID FROM Persons
INNER JOIN Orders ON Persons.PersonID = Orders.PersonID
WO PersonName;

In der Tat gibt es vier Arten von JOIN: INNER, OUTER, LEFT, und RIGHT. Dies INNER JOINist die Standardeinstellung (Sie können das Wort weglassen INNER) und enthält nur Zeilen, die in beiden Tabellen übereinstimmende Werte enthalten. Wenn Sie Personen auflisten möchten, ob sie Bestellungen haben oder nicht, verwenden Sie beispielsweise Folgendes LEFT JOIN:

SELECT PersonName, OrderID FROM Persons
LEFT JOIN Orders ON Persons.PersonID = Orders.PersonID
ORDER BY PersonName;

Wenn Sie Abfragen ausführen, die mehr als zwei Tabellen verknüpfen, Ausdrücke verwenden oder Datentypen erzwingen, kann die Syntax zunächst etwas haarig werden. Glücklicherweise gibt es Datenbankentwicklungstools, mit denen Sie korrekte SQL-Abfragen generieren können, häufig durch Ziehen und Ablegen von Tabellen und Feldern aus dem Schemadiagramm in ein Abfragediagramm.

Gespeicherte SQL-Prozeduren

Manchmal SELECTbringt Sie der deklarative Charakter der Aussage nicht dahin, wohin Sie wollen. Die meisten Datenbanken verfügen über eine Funktion, die als gespeicherte Prozeduren bezeichnet wird. Leider ist dies ein Bereich, in dem fast alle Datenbanken proprietäre Erweiterungen der ANSI / ISO-SQL-Standards verwenden.

In SQL Server war der ursprüngliche Dialekt für gespeicherte Prozeduren (oder gespeicherte Prozesse) Transact-SQL, auch bekannt als T-SQL. in Oracle war es PL-SQL. Beide Datenbanken haben zusätzliche Sprachen für gespeicherte Prozeduren wie C #, Java und R hinzugefügt. Eine einfache gespeicherte T-SQL-Prozedur ist möglicherweise nur eine parametrisierte Version einer SELECTAnweisung. Seine Vorteile sind Benutzerfreundlichkeit und Effizienz. Gespeicherte Prozeduren werden beim Speichern optimiert, nicht jedes Mal, wenn sie ausgeführt werden.

Eine kompliziertere gespeicherte T-SQL-Prozedur verwendet möglicherweise mehrere SQL-Anweisungen, Eingabe- und Ausgabeparameter, lokale Variablen, BEGIN...ENDBlöcke, IF...THEN...ELSEBedingungen, Cursor (zeilenweise Verarbeitung einer Menge), Ausdrücke, temporäre Tabellen und eine ganze Reihe anderer prozedurale Syntax. Wenn die Sprache der gespeicherten Prozedur C #, Java oder R ist, werden Sie natürlich die Funktionen und die Syntax dieser prozeduralen Sprachen verwenden. Mit anderen Worten, trotz der Tatsache, dass die Motivation für SQL darin bestand, standardisierte deklarative Abfragen zu verwenden, sehen Sie in der realen Welt viele datenbankspezifische prozedurale Serverprogrammierungen.

Das führt uns nicht ganz zurück in die schlechten alten Zeiten der CODASYL-Datenbankprogrammierung (obwohl Cursor nahe beieinander liegen), aber es geht von den Ideen zurück, dass SQL-Anweisungen standardisiert werden sollten und dass Leistungsprobleme dem Optimierer für Datenbankabfragen überlassen werden sollten . Am Ende ist eine Verdoppelung der Leistung oft zu viel, um sie auf dem Tisch zu lassen.

Lernen Sie SQL

Die unten aufgeführten Websites können Ihnen beim Erlernen von SQL helfen oder die Macken verschiedener SQL-Dialekte entdecken.