Share
Es ist wichtig, dass jeder Webentwickler mit Datenbankinteraktionen vertraut ist. Im zweiten Teil der Serie werden wir die SQL-Sprache weiter untersuchen und das, was wir in einer MySQL-Datenbank gelernt haben, anwenden. Wir lernen Indexe, Datentypen und komplexere Abfragestrukturen kennen.
Bitte lesen Sie den Abschnitt "Was Sie brauchen" im ersten Artikel hier: SQL für Anfänger (Teil 1).
Wenn Sie die Beispiele in diesem Artikel auf Ihrem eigenen Entwicklungsserver ausführen möchten, gehen Sie wie folgt vor:
Indizes (oder Schlüssel) werden hauptsächlich zur Verbesserung der Geschwindigkeit von Datenabrufoperationen (z. B. SELECT) für Tabellen verwendet.
Sie sind ein so wichtiger Teil eines guten Datenbankdesigns. Es ist schwer, sie als "Optimierung" zu klassifizieren. In den meisten Fällen sind sie im ursprünglichen Entwurf enthalten, sie können jedoch auch später mit einer ALTER TABLE-Abfrage hinzugefügt werden.
Die häufigsten Gründe für die Indizierung von Datenbankspalten sind:
Fast jede Tabelle sollte einen PRIMARY KEY haben, meistens als INT mit der Option AUTO_INCREMET.
Wenn Sie sich an den ersten Artikel erinnern, haben wir ein 'user_id'-Feld in der Benutzertabelle erstellt, und es war ein PRIMARY KEY. Auf diese Weise können wir in einer Webanwendung über ihre ID-Nummer auf alle Benutzer verweisen.
Die in einer PRIMARY KEY-Spalte gespeicherten Werte müssen eindeutig sein. Es kann auch nicht mehr als einen PRIMARY KEY pro Tabelle geben.
Sehen wir uns eine Beispielabfrage an, die eine Tabelle für die Liste der USA-Staaten erstellt:
CREATE TABLE-Zustände (id INT AUTO_INCREMENT PRIMARY KEY, Name VARCHAR (20));
Es kann auch so geschrieben werden:
CREATE TABLE-Zustände (id INT AUTO_INCREMENT, Name VARCHAR (20), PRIMARY KEY (id));
Da wir davon ausgehen, dass der Zustandsname ein eindeutiger Wert ist, sollten wir das vorherige Abfragebeispiel ein wenig ändern:
CREATE TABLE-Zustände (id INT AUTO_INCREMENT, Name VARCHAR (20), PRIMARY KEY (ID), UNIQUE (Name));
Standardmäßig wird der Index nach dem Spaltennamen benannt. Wenn Sie möchten, können Sie ihm einen anderen Namen zuweisen:
CREATE TABLE-Zustände (ID INT AUTO_INCREMENT, Name VARCHAR (20), PRIMARY KEY (ID), UNIQUE Statusname (Name));
Jetzt heißt der Index 'state_name' anstelle von 'name'.
Nehmen wir an, wir möchten eine Spalte hinzufügen, die das Jahr darstellt, in dem jeder Staat beigetreten ist.
CREATE TABLE-Zustände (id INT AUTO_INCREMENT, Name VARCHAR (20), join_year INT, PRIMARY KEY (id), UNIQUE (name), INDEX (join_year));
Ich habe gerade die join_year-Spalte hinzugefügt und sie indiziert. Dieser Indextyp hat keine Einschränkung für die Eindeutigkeit.
Sie können es auch KEY anstelle von INDEX nennen.
CREATE TABLE-Zustände (id INT AUTO_INCREMENT, Name VARCHAR (20), join_year INT, PRIMARY KEY (id), UNIQUE (Name), KEY (join_year));
Durch das Hinzufügen eines Indexes wird die Leistung von INSERT- und UPDATE-Abfragen verringert. Da jedes Mal, wenn neue Daten zu der Tabelle hinzugefügt werden, die Indexdaten automatisch aktualisiert werden, ist zusätzliche Arbeit erforderlich. Die Leistungssteigerungen bei SELECT-Abfragen überwiegen dies bei weitem. Fügen Sie jedoch nicht einfach nur Indizes für jede einzelne Tabellenspalte hinzu, ohne über die Abfragen nachzudenken, die Sie ausführen.
Bevor wir mit weiteren Abfragen fortfahren, möchte ich eine Beispieltabelle mit einigen Daten erstellen.
Dies wird eine Liste der US-Bundesstaaten mit ihren Beitrittsdaten (dem Datum, an dem der Staat die Verfassung der Vereinigten Staaten ratifiziert hat oder in die Union aufgenommen wurde) und ihrer aktuellen Bevölkerung sein. Sie können Folgendes kopieren und in Ihre MySQL-Konsole einfügen:
CREATE TABLE-Zustände (id INT AUTO_INCREMENT, Name VARCHAR (20), join_year INT, Bevölkerung INT, PRIMARY KEY (id), UNIQUE (name), KEY (join_year)); INSERT INTO Staaten (1, "Alabama", 1819, 4661900), (2, "Alaska", 1959, 686293), (3, "Arizona", 1912, 6500180), (4, "Arkansas", 1836, 2855390) ), (5, "California", 1850, 36756666), (6, "Colorado", 1876, 4939456), (7, "Connecticut", 1788, 3501252), (8, "Delaware", 1787, 873092), (9, "Florida", 1845, 18328340), (10, "Georgia", 1788, 9685744), (11, "Hawaii", 1959, 1288198), (12, "Idaho", 1890, 1523816), (13 "Illinois", 1818, 12901563), (14, "Indiana", 1816, 6376792), (15, "Iowa", 1846, 3002555), (16, "Kansas", 1861, 2802134), (17, ") Kentucky ', 1792, 4269245), (18, "Louisiana", 1812, 4410796), (19, "Maine", 1820, 1316456), (20, "Maryland", 1788, 5633597), (21, "Massachusetts") 1788, 6497967), (22, "Michigan", 1837, 10003422), (23, "Minnesota", 1858, 5220393), (24, "Mississippi", 1817, 2938618), (25, "Missouri", 1821 , 5911605), (26, "Montana", 1889, 967440), (27, "Nebraska", 1867, 1783432), (28, "Nevada", 1864, 2600167), (29, "New Hampshire", 1788, 1315809), (30, "New Jersey", 1787, 8682) 661), (31, "New Mexico", 1912, 1984356), (32, "New York", 1788, 19490297), (33, "North Carolina", 1789, 9222414), (34, "North Dakota"), 1889, 641481), (35, "Ohio", 1803, 11485910), (36, "Oklahoma", 1907, 3642361), (37, "Oregon", 1859, 3790060), (38, "Pennsylvania", 1787, 12448279), (39, "Rhode Island", 1790, 1050788), (40, "South Carolina", 1788, 4479800), (41, "South Dakota", 1889, 804194), (42, "Tennessee", 1796) 6214888), (43, "Texas", 1845, 24326974), (44, "Utah", 1896, 2736424), (45, "Vermont", 1791, 621270), (46, "Virginia", 1788, 7769089) (47, "Washington", 1889, 6549224), (48, "West Virginia", 1863, 1814468), (49, "Wisconsin", 1848, 5627967), (50, "Wyoming", 1890, 532668) ;
Die GROUP BY-Klausel gruppiert die resultierenden Datenzeilen in Gruppen. Hier ist ein Beispiel:
Was ist also passiert? Wir haben 50 Zeilen in der Tabelle, aber 34 Ergebnisse wurden von dieser Abfrage zurückgegeben. Dies liegt daran, dass die Ergebnisse in der Spalte 'join_year' gruppiert wurden. Mit anderen Worten, wir sehen nur eine Zeile für jeden unterschiedlichen Wert von join_year. Da einige Bundesstaaten das gleiche join_year haben, haben wir weniger als 50 Ergebnisse.
Zum Beispiel gab es nur eine Zeile für das Jahr 1787, aber es gibt drei Staaten in dieser Gruppe:
Es gibt also drei Zustände hier, aber nur der Name von Delaware wurde früher nach der GROUP BY-Abfrage angezeigt. Eigentlich könnte es einer der drei Zustände gewesen sein, und wir können uns nicht auf diese Daten verlassen. Was ist der Sinn der Verwendung der GROUP BY-Klausel??
Ohne eine Aggregatfunktion wie COUNT () wäre es meist nutzlos. Mal sehen, was einige dieser Funktionen bewirken und wie sie uns nützliche Daten liefern können.
Dies ist möglicherweise die am häufigsten verwendete Funktion zusammen mit GROUP BY-Abfragen. Es gibt die Anzahl der Zeilen in jeder Gruppe zurück.
Zum Beispiel können wir damit die Anzahl der Zustände für jedes join_year anzeigen:
Wenn Sie eine GROUP BY-Aggregatfunktion verwenden und keine GROUP BY-Klausel angeben, werden die gesamten Ergebnisse in einer einzelnen Gruppe zusammengefasst.
Anzahl aller Zeilen in der Tabelle:
Anzahl der Zeilen, die eine WHERE-Klausel erfüllen:
Diese Funktionen geben die Minimal-, Maximal- und Durchschnittswerte zurück:
Diese Funktion verkettet alle Werte in der Gruppe mit einem bestimmten Trennzeichen in einer einzelnen Zeichenfolge.
Im ersten GROUP BY-Abfragebeispiel wurde nur ein Zustandsname pro Jahr angezeigt. Sie können diese Funktion verwenden, um alle Namen in jeder Gruppe anzuzeigen:
Wenn das verkleinerte Bild schwer zu lesen ist, ist dies die Abfrage:
SELECT GROUP_CONCAT (Name SEPARATOR ','), join_year FROM-Status GROUP BY join_year;
Sie können dies verwenden, um die numerischen Werte zusammenzurechnen.
Ähnlich wie andere Programmiersprachen unterstützt SQL den Kontrollfluss.
Dies ist eine Funktion, die drei Argumente benötigt. Das erste Argument ist die Bedingung, das zweite Argument wird verwendet, wenn die Bedingung wahr ist, und das dritte Argument, wenn die Bedingung falsch ist.
Hier ist ein praktischeres Beispiel, in dem wir es mit der Funktion SUM () verwenden:
SELECT SUM (IF (Bevölkerung> 5000000, 1, 0)) AS big_states, SUM (IF (Bevölkerung) <= 5000000, 1, 0) ) AS small_states FROM states;
Der erste SUM () - Aufruf zählt die Anzahl der großen Staaten (Bevölkerung über 5 Millionen) und der zweite Anruf zählt die Anzahl der kleinen Staaten. Der Aufruf von IF () innerhalb dieser Aufrufe von SUM () gibt je nach Bedingung entweder 1 oder 0 zurück.
Hier ist das Ergebnis:
Dies funktioniert ähnlich wie die Anweisungen des Switch-Case, die Sie möglicherweise aus der Programmierung kennen.
Angenommen, wir möchten jeden Staat in eine von drei möglichen Kategorien einordnen.
SELECT COUNT (*), CASE BEISPIEL BEISPIEL BEISPIELE BEISPIEL 1: 5000000 DANN 'groß' WENN BEISPIEL> 1000000 DANN 'mittel' ELSE 'klein' END AS state_size FROM Zustände GROUP BY state_size;
Wie Sie sehen, können wir GROUP BY tatsächlich den von der CASE-Anweisung zurückgegebenen Wert verwenden. Folgendes passiert:
Mit der HAVING-Klausel können wir Bedingungen auf "ausgeblendete" Felder anwenden, z. B. auf die zurückgegebenen Ergebnisse von Aggregatfunktionen. Daher wird es normalerweise zusammen mit GROUP BY verwendet.
Schauen wir uns zum Beispiel die Abfrage an, die wir zum Zählen der Anzahl von Status nach Join-Jahr verwendet haben:
SELECT COUNT (*), join_year FROM-Zustände GROUP BY join_year;
Das Ergebnis waren 34 Zeilen.
Nehmen wir jedoch an, wir sind nur an Zeilen interessiert, deren Zählwert höher als 1 ist. Wir können die WHERE-Klausel nicht dafür verwenden:
Hier wird HAVING nützlich:
Beachten Sie, dass diese Funktion möglicherweise nicht in allen Datenbanksystemen verfügbar ist.
Es ist möglich, die Ergebnisse einer Abfrage abzurufen und für eine andere Abfrage zu verwenden.
In diesem Beispiel erhalten wir den Staat mit der höchsten Bevölkerung:
SELECT * FROM-Zustände WHERE Population = (SELECT MAX (Population) FROM-Zustände);
Die innere Abfrage gibt die höchste Bevölkerung aller Staaten zurück. Die äußere Abfrage durchsucht die Tabelle erneut mit diesem Wert.
Sie denken vielleicht, dass dies ein schlechtes Beispiel war, und ich stimme etwas zu. Die gleiche Abfrage könnte folgendermaßen effizienter geschrieben werden:
SELECT * FROM States ORDER BY Bevölkerung DESC LIMIT 1;
Die Ergebnisse sind in diesem Fall die gleichen, jedoch gibt es einen wichtigen Unterschied zwischen diesen beiden Arten von Abfragen. Vielleicht zeigt ein anderes Beispiel das besser.
In diesem Beispiel erhalten wir die letzten Staaten, die der Union beigetreten sind:
SELECT * FROM-Zustände WHERE join_year = (SELECT MAX (join_year) FROM-Zustände);
Dieses Mal gibt es zwei Reihen in den Ergebnissen. Wenn wir hier den Abfragetyp ORDER BY… LIMIT 1 verwendet hätten, hätten wir nicht dasselbe Ergebnis erhalten.
Manchmal möchten Sie möglicherweise mehrere Ergebnisse verwenden, die von der inneren Abfrage zurückgegeben werden.
Die folgende Abfrage ermittelt die Jahre, in denen mehrere Staaten der Union beigetreten sind, und gibt die Liste dieser Staaten zurück:
SELECT * FROM-Zustände WHERE join_year IN (SELECT join_year FROM-Zustände GROUP BY join_year HAVING COUNT (*)> 1) ORDER BY join_year;
Unterabfragen können sehr komplex werden, daher werde ich in diesem Artikel nicht weiter darauf eingehen. Wenn Sie mehr darüber erfahren möchten, lesen Sie das MySQL-Handbuch.
Es ist auch erwähnenswert, dass Unterabfragen manchmal eine schlechte Leistung aufweisen können. Daher sollten sie mit Vorsicht verwendet werden.
Mit einer UNION-Abfrage können wir die Ergebnisse mehrerer SELECT-Abfragen kombinieren.
Dieses Beispiel kombiniert Zustände, die mit dem Buchstaben 'N' beginnen, und Zustände mit großen Populationen:
(SELECT * FROM-Staaten, deren Name LIKE "n%" ist) UNION (SELECT * FROM-Staaten, deren Bevölkerung> 10000000 ist);
Beachten Sie, dass New York groß ist und der Name mit dem Buchstaben 'N' beginnt. Es wird jedoch nur einmal angezeigt, da doppelte Zeilen automatisch aus den Ergebnissen entfernt werden.
Eine weitere schöne Sache bei UNION ist, dass Sie Abfragen in verschiedenen Tabellen kombinieren können.
Nehmen wir an, wir haben Tabellen für Mitarbeiter, Manager und Kunden. Und jede Tabelle hat ein E-Mail-Feld. Wenn Sie alle E-Mails mit einer einzigen Abfrage abrufen möchten, können Sie Folgendes ausführen:
(E-Mail von Mitarbeitern auswählen) UNION (E-Mail von Managern auswählen) UNION (E-Mail von Kunden auswählen WHERE abonniert = 1);
Es würde alle E-Mails aller Angestellten und Manager abrufen, jedoch nur die E-Mails der Kunden, die den Erhalt von E-Mails abonniert haben.
Wir haben bereits im letzten Artikel über die INSERT-Abfrage gesprochen. Nun, da wir uns heute mit Datenbankindizes befasst haben, können wir über erweiterte Funktionen der INSERT-Abfrage sprechen.
Dies ist fast wie eine bedingte Aussage. Die Abfrage versucht zuerst, eine bestimmte INSERT-Anweisung auszuführen. Wenn sie aufgrund eines doppelten Werts für einen PRIMARY KEY oder UNIQUE KEY fehlschlägt, führt sie stattdessen ein UPDATE aus.
Erstellen wir zuerst eine Testtabelle.
Es ist ein Tisch für Produkte. Die Spalte "Vorrat" gibt die Anzahl der Produkte an, die wir auf Lager haben.
Versuchen Sie nun, einen doppelten Wert einzufügen und zu sehen, was passiert.
Wir haben wie erwartet einen Fehler erhalten.
Nehmen wir an, wir haben einen neuen Brotbackautomat erhalten und möchten die Datenbank aktualisieren, und wir wissen nicht, ob bereits ein Datensatz dafür vorhanden ist. Wir könnten nach vorhandenen Datensätzen suchen und darauf basierend eine weitere Abfrage durchführen. Oder wir könnten alles in einer einfachen Abfrage erledigen:
Dies funktioniert genau wie INSERT mit einer wichtigen Ausnahme. Wenn eine doppelte Zeile gefunden wird, löscht sie diese zuerst und führt dann die INSERT-Anweisung aus, sodass keine Fehlermeldungen angezeigt werden.
Beachten Sie, dass die ID inkrementiert wurde, da es sich tatsächlich um eine völlig neue Zeile handelt.
Auf diese Weise können doppelte Fehler unterdrückt werden, um zu verhindern, dass die Anwendung beschädigt wird. Manchmal möchten Sie vielleicht versuchen, eine neue Zeile einzufügen und es ohne Beanstandung fehlschlagen zu lassen, falls ein Duplikat gefunden wird.
Es wurden keine Fehler zurückgegeben und keine Zeilen wurden aktualisiert.
Jede Tabellenspalte muss einen Datentyp haben. Bisher haben wir die Typen INT, VARCHAR und DATE verwendet, aber wir haben nicht ausführlich darüber gesprochen. Es gibt auch einige andere Datentypen, die wir untersuchen sollten.
Beginnen wir mit den numerischen Datentypen. Ich mag es, sie in zwei getrennte Gruppen zu unterteilen: Ganzzahlen vs. Nicht-Ganzzahlen.
Eine ganzzahlige Spalte kann nur natürliche Zahlen (keine Dezimalzahlen) enthalten. Standardmäßig können sie negative oder positive Zahlen sein. Ist die Option UNSIGNED jedoch gesetzt, können nur positive Zahlen gespeichert werden.
MySQL unterstützt 5 Arten von Ganzzahlen mit verschiedenen Größen und Bereichen:
Diese Datentypen können Dezimalzahlen enthalten: FLOAT, DOUBLE und DECIMAL.
FLOAT besteht aus 4 Bytes, DOUBLE aus 8 Bytes und sie funktionieren ähnlich. DOUBLE hat jedoch eine bessere Genauigkeit.
DECIMAL (M, N) hat je nach Genauigkeitsgrad eine unterschiedliche Größe, die angepasst werden kann. M ist die maximale Anzahl von Ziffern und N ist die Anzahl von Ziffern rechts vom Dezimalpunkt.
Zum Beispiel hat DECIMAL (13,4) maximal 9 Ganzzahlen und 4 Nachkommastellen.
Wie der Name schon sagt, können wir in diesen Datentypspalten Zeichenketten speichern.
CHAR (N) kann bis zu N Zeichen enthalten und hat eine feste Größe. Zum Beispiel benötigt CHAR (50) unabhängig von der Größe der Zeichenfolge immer 50 Zeichen pro Zeile. Das absolute Maximum beträgt 255 Zeichen
VARCHAR (N) funktioniert genauso, aber die Speichergröße ist nicht festgelegt. N wird nur für die maximale Größe verwendet. Wenn eine Zeichenfolge gespeichert wird, die kürzer als N Zeichen ist, wird auf der Festplatte viel weniger Speicherplatz benötigt. Die absolute Maximalgröße beträgt 65535 Zeichen.
Variationen des Datentyps TEXT eignen sich eher für lange Zeichenfolgen. TEXT hat ein Limit von 65535 Zeichen, MEDIUMTEXT 16,7 Millionen Zeichen und LONGTEXT 4,3 Milliarden Zeichen. MySQL speichert sie normalerweise an unterschiedlichen Stellen auf dem Server, sodass der Hauptspeicher für die Tabelle relativ klein und schnell bleibt.
DATE speichert Datumsangaben und zeigt sie in diesem Format 'JJJJ-MM-TT' an, enthält jedoch keine Zeitangaben. Es hat einen Bereich von 1001-01-01 bis 9999-12-31.
DATETIME enthält sowohl Datum als auch Uhrzeit und wird in diesem Format angezeigt: JJJJ-MM-TT HH: MM: SS. Es hat einen Bereich von '1000-01-01 00:00:00' bis '9999-12-31 23:59:59'. Es benötigt 8 Byte Speicherplatz.
TIMESTAMP funktioniert mit wenigen Ausnahmen wie DATETIME. Es dauert nur 4 Byte Speicherplatz und der Bereich ist '1970-01-01 00:00:01' UTC bis '2038-01-19 03:14:07' UTC. Zum Beispiel kann es nicht gut sein, um Geburtsdaten zu speichern.
TIME speichert nur die Uhrzeit und YEAR speichert nur das Jahr.
Es gibt einige andere Datentypen, die von MySQL unterstützt werden. Sie können hier eine Liste davon sehen. Hier sollten Sie auch die Speichergrößen der einzelnen Datentypen überprüfen.
Vielen Dank, dass Sie den Artikel gelesen haben. SQL ist eine wichtige Sprache und ein Werkzeug im Arsenal der Webentwickler.
Bitte hinterlassen Sie Ihre Kommentare und Fragen und haben Sie einen schönen Tag!
Sind Sie bereit, Ihre Fähigkeiten auf die nächste Stufe zu bringen und von Ihren Skripten und Komponenten zu profitieren? Besuchen Sie unseren Schwestermarkt CodeCanyon.
Accentsconagua