Datenbankentwicklung

SQL

Definition

SQL (Structured Query Language) ist eine strukturierte Abfragesprache zur Verwaltung und zum Betrieb relationaler Datenbanken. Es ist eine der am weitesten verbreiteten Sprachen in Datenbankverwaltungssystemen.

Hauptfunktionen

Grundgrammatik

- Daten abfragen
SELECT * FROM Tabellenname WHERE Bedingung;

--Daten einfügen
INSERT INTO Tabellenname (Spalte 1, Spalte 2) VALUES (Wert 1, Wert 2);

--Daten aktualisieren
UPDATE-Tabellenname SET-Spalte 1 = Wert 1 WHERE-Bedingung;

-- Daten löschen
DELETE FROM Tabellenname WHERE-Bedingung;

--Tabelle erstellen
CREATE TABLE Tabellenname (
    Spaltenname 1 Datentyp,
    Spaltenname 2 Datentyp
);

Gängige Datentypen

Vorteil

MySQL

relationale Datenbank

MySQL ist ein beliebtes relationales Open-Source-Datenbankverwaltungssystem (RDBMS), das SQL als Abfragesprache verwendet und für kleine, mittlere und große Anwendungen geeignet ist.

Merkmal

Anwendungsbeispiele

mysql -u root -p
CREATE DATABASE example_db;
USE example_db;
CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50), age INT);
INSERT INTO users (name, age) VALUES ('Alice', 30);
SELECT * FROM users;

Anwendbare Szenarien

SQLite

Leichte Datenbank

SQLite ist eine eingebettete Datenbank, die keinen separaten Server für die Verwaltung erfordert und für einfache Anwendungen geeignet ist.

Merkmal

Anwendungsbeispiele

sqlite3 example.db
CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER);
INSERT INTO users (name, age) VALUES ('Alice', 30);
SELECT * FROM users;

Anwendbare Szenarien

PostgreSQL

PostgreSQL (oft als Postgres bezeichnet) ist ein leistungsstarkes objektrelationales Open-Source-Datenbanksystem (ORDBMS). Sie basiert auf mehr als 30 Jahren aktiver Entwicklung und genießt einen guten Ruf für Zuverlässigkeit, funktionale Robustheit und Leistung und gilt als „die fortschrittlichste Open-Source-Datenbank der Welt“.

Kernfunktionen

Technische Architektur

PostgreSQL verwendet eine Client-Server-Architektur und weist jeder Verbindung unabhängige Handler zu, wodurch sichergestellt wird, dass der Absturz einer einzelnen Verbindung nicht die Stabilität des gesamten Datenbankservers beeinträchtigt.

Gängige Erweiterungssätze

Die Leistungsfähigkeit von Postgres beruht größtenteils auf seiner umfangreichen Ökosystemerweiterung:

Gegenüberstellung von Vor- und Nachteilen

Vorteil Mangel
Hohe Datenintegrität und -sicherheit Beim Schreiben großer Datenmengen mit hoher Frequenz kann es durch den VACUUM-Mechanismus zu Leistungsschwankungen kommen.
Unterstützen Sie komplexe Datenanalysen und Geschäftslogik Der Speicherverbrauch ist im Allgemeinen höher als bei MySQL
Die Open-Source-Community ist äußerst aktiv und es gibt keine Herstellerbindung Es müssen viele Parameter eingestellt werden, und Anfänger benötigen eine lange Lernkurve, um die Leistung zu konfigurieren und zu optimieren.

Anwendbare Szenarien

T-SQL

T-SQL, dessen vollständiger Name Transact-SQL ist, ist eine erweiterte Version des von Microsoft und Sybase entwickelten SQL-Standards. Es ist die zentrale Kommunikationssprache für Microsoft SQL Server- und Azure SQL-bezogene Dienste. Im Vergleich zu Standard-SQL bietet T-SQL zusätzliche Programmierfunktionen, die es ihm ermöglichen, nicht nur Daten abzufragen, sondern auch komplexe logische Operationen abzuwickeln.

Sprachmerkmale

Hauptunterschiede zwischen T-SQL und Standard-SQL

Funktionstyp Standard-SQL (ANSI) T-SQL (Microsoft)
String-Verkettung Verwenden Sie doppelte vertikale Balken || Verwenden Sie das Pluszeichen +
Begrenzen Sie die Anzahl der zurückgegebenen Spalten Verwenden Sie FETCH FIRST Verwenden Sie das Schlüsselwort TOP
Datentypkonvertierung CAST CAST und CONVERT (unterstützt Formatierung)
Programmlogik Hauptsächlich grundlegende Anfragen Vollständige prozedurale Programmiersprachenfunktionen

Grundlegendes Grammatikbeispiel

Der folgende Code zeigt, wie Variablen, logische Beurteilungen und Datenabfragen in T-SQL kombiniert werden:

-- Variablen deklarieren und festlegen
DECLARE @Threshold INT = 100;
DECLARE @CurrentStock INT;

-- Rufen Sie den Lagerbestand eines bestimmten Produkts ab
SELECT @CurrentStock = StockQuantity
VON Produkten
WO Produkt-ID = 5;

-- logisches Urteil
IF @CurrentStock < @Threshold
ANFANGEN
    DRUCKEN „Warnung: Der Lagerbestand liegt unter dem voreingestellten Schwellenwert. ';
    -- Nachschublogik ausführen...
ENDE
Sonst
ANFANGEN
    SELECT * FROM Products WHERE ProductID = 5;
ENDE

Anwendungsszenarien

T-SQL wird häufig in den folgenden Bereichen verwendet:

T-SQL-Programmentwicklung

T-SQL erweitert Standard-SQL, um vollständige Programmfunktionen bereitzustellen. Durch Variablen, Logiksteuerung und Fehlerbehandlung können Entwickler komplexe Geschäftslogik auf Datenbankebene schreiben.

Variablendeklaration und -zuweisung

In T-SQL müssen alle benutzerdefinierten Variablen mit dem @-Symbol beginnen. Verwenden Sie DECLARE zur Deklaration und SET oder SELECT zur Zuweisung.

-- Variablen deklarieren
DECLARE @EmployeeCount INT;
DECLARE @DepartmentName NVARCHAR(50);

-- Auftrag
SET @DepartmentName = 'IT-Abteilung';

--Werte aus Abfrageergebnissen zuweisen
SELECT @EmployeeCount = COUNT(*)
VON Mitarbeitern
WHERE Department = @DepartmentName;

- Ergebnisse ausgeben
PRINT @EmployeeCount;

Prozesssteuerung WENN...SONST

IF...ELSE-Anweisungen ermöglichen die Ausführung verschiedener Codeblöcke basierend auf Bedingungen. Wenn ein Block mehrere Anweisungen enthält, müssen diese mit BEGIN...END umschlossen werden.

DECLARE @StockLevel INT;
SET @StockLevel = 10;

IF @StockLevel < 5
ANFANGEN
    DRUCKEN „Der Lagerbestand ist sehr niedrig, bitte füllen Sie ihn sofort auf.“ ';
ENDE
ELSE IF @StockLevel < 20
ANFANGEN
    DRUCKEN „Der Lagerbestand ist niedrig, es wird empfohlen, ihn aufzufüllen.“ ';
ENDE
Sonst
ANFANGEN
    DRUCKEN „Auf Lager. ';
ENDE

Fehlerbehandlung TRY...CATCH

Der von T-SQL bereitgestellte TRY...CATCH-Mechanismus ähnelt modernen Programmiersprachen (wie C# oder Java) und kann Ausnahmen während der Ausführung erfassen, um abnormale Programmunterbrechungen zu verhindern.

Beginnen Sie mit dem Versuch
    – Code, der ausgeführt werden soll
    INSERT INTO Sales (OrderID, ProductID, Quantity)
    WERTE (1001, 'P01', -5); – Gehen Sie davon aus, dass dies einen Einschränkungsfehler auslöst
ENDE VERSUCH
Fangen Sie an
    -- Umgang bei auftretenden Fehlern
    AUSWÄHLEN
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_MESSAGE() AS ErrorMessage,
        ERROR_SEVERITY() AS Severity;
        
    --Rollback-Transaktion
    IF @@TRANCOUNT > 0
        ROLLBACK-TRANSAKTION;
ENDE FANG

Verwenden Sie das Feld „Name“ statt „ID“.

Anwendbare Bedingungen

Vorteile

mögliche Probleme

Anwendbare Beispiele

Zum Beispiel die Profiltyp-Datentabelle:

CREATE TABLE config_types (
  name VARCHAR(50) PRIMARY KEY,
  description TEXT
);

INSERT INTO config_types (name, description) VALUES ('general', 'General settings');
SELECT * FROM config_types WHERE name = 'general';

Vererbungsobjekte für den Entwurf relationaler Datenbanken

Design der Datenbankstruktur

Tierform

In dieser Tabelle werden die gemeinsamen Attribute aller „Tiere“ gespeichert.

Feldname Datentyp veranschaulichen
id INT eindeutige Kennung des Tieres
species VARCHAR(50) Arten von Tieren
age INT Alter des Tieres

Katzentisch

Diese Tabelle erbt die ID der Tiertabelle und speichert die eindeutigen Attribute von „Katze“.

Feldname Datentyp veranschaulichen
id INT Entspricht der ID der Tiertabelle
breed VARCHAR(50) Katzenrasse
favorite_food VARCHAR(50) Das Lieblingsfutter der Katze

SQL-Befehl zum Erstellen einer Tabelle

CREATE TABLE animal (
    id INT PRIMARY KEY AUTO_INCREMENT,
    species VARCHAR(50) NOT NULL,
    age INT NOT NULL
);

CREATE TABLE cat (
    id INT PRIMARY KEY,
    breed VARCHAR(50),
    favorite_food VARCHAR(50),
    FOREIGN KEY (id) REFERENCES animal(id)
);

Beispiel für das Einfügen von Daten

INSERT INTO animal (species, age) VALUES ('Cat', 3);

INSERT INTO cat (id, breed, favorite_food) VALUES (1, 'Siamese', 'Fish');

Beispiel für die Anzeige einer HTML-Tabelle

Tierinformationen

Tierausweis Typ Alter
1 Cat 3

Katzenspezifische Informationen

Tierausweis Vielfalt liebe Essen
1 Siamese Fish

veranschaulichen

In diesem Beispiel,animalDie Tabelle speichert die gemeinsamen Attribute aller Tiere undcatDie Tabelle speichert die einzigartigen Attribute der Katze.catin der Tabelleidist eine Referenzanimaltabellarischid, was darauf hinweist, dass es sich um eine Vererbungsbeziehung handelt.

Informationen zu allen Tieren abfragen

SELECT * FROM animal;

Informationen zu allen Katzen abfragen

Diese Abfrage gibt vollständige Informationen über alle Katzen zurück, einschließlich gemeinsamer Attribute, die aus der Tiertabelle geerbt wurden.

SELECT animal.id, animal.species, animal.age, cat.breed, cat.favorite_food
FROM animal
JOIN cat ON animal.id = cat.id;

Informationen zu einem bestimmten Tier abfragen (z. B. eine bestimmte ID)

SELECT * FROM animal WHERE id = 1;

Informationen zu einer bestimmten Katze (z. B. einer bestimmten Rasse) abfragen

SELECT animal.id, animal.species, animal.age, cat.breed, cat.favorite_food
FROM animal
JOIN cat ON animal.id = cat.id
WHERE cat.breed = 'Siamese';

veranschaulichen

In diesen Abfragebeispielen verwenden wirJOINWilleanimalTisch undcatDie Tabellen werden kombiniert, um vollständige Informationen über die Katze zu erhalten. Dieser Ansatz stellt sicher, dass die Abfrageergebnisse sowohl geerbte als auch eindeutige Eigenschaften enthalten.

FOREIGN KEY

verwenden

FOREIGN KEY (Fremdschlüssel) wird verwendet, um die Zuordnung zwischen zwei Datentabellen herzustellen und die Referenzintegrität der Daten sicherzustellen. Beispielsweise muss ein Feldwert in einer Datentabelle auf einen Primärschlüssel oder einen eindeutigen Wert in einer anderen Datentabelle verweisen.

Grammatik

Untertabelle CREATE TABLE (
  Feldname Datentyp,
  FOREIGN KEY (Fremdschlüsselfeld) REFERENCES übergeordnete Datentabelle (Primärschlüsselfeld)
);

Beispiel

Stellen Sie eine Eins-zu-Viele-Beziehung her, z. B. zwischen Bestellungen und Kunden:

-- übergeordnete Datentabelle erstellen (Kunden)
TABELLE Kunden erstellen (
  customer_id INT PRIMARY KEY,
  Name VARCHAR(50)
);

--Erstellen Sie Untertabellen (Bestellungen) und legen Sie Fremdschlüssel fest
TISCHBestellungen ERSTELLEN (
  order_id INT PRIMARY KEY,
  customer_id INT,
  order_date DATUM,
  AUSLÄNDISCHER SCHLÜSSEL (Kunden-ID) REFERENZEN Kunden (Kunden-ID)
);

Dinge zu beachten

Erweiterte Nutzung

durchgehen kannON DELETEUndON UPDATEGeben Sie das Fremdschlüsselverhalten an:

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  order_date DATE,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
  ON DELETE CASCADE
  ON UPDATE CASCADE
);

Verhaltensoptionen

Fügen Sie Kommentare zu CREATE TABLE hinzu

Verwenden Sie COMMENT, um Feldkommentare festzulegen

In MySQL können Sie verwendenCOMMENTum Notizen zu den Feldern hinzuzufügen.

CREATE TABLE-Benutzer (
  id INT PRIMARY KEY COMMENT 'Eindeutiger Identifikationscode des Benutzers',
  name VARCHAR(50) COMMENT 'Benutzername',
  Alter INT COMMENT 'Alter des Benutzers'
);

Verwenden Sie COMMENT, um Tabellenkommentare festzulegen

CREATE TABLE-Benutzer (
  id INT PRIMÄRSCHLÜSSEL,
  Name VARCHAR(50),
  ageINT
) COMMENT = 'Benutzerinformationstabelle';

Feldnotizen anzeigen

Die Kommentare des Feldes können mit der folgenden Syntax abgefragt werden:

SHOW FULL COLUMNS FROM users;

Feldnotizen ändern

ALTER TABLE-Benutzer MODIFY COLUMN name VARCHAR(50) COMMENT 'Geänderte Bemerkungen';

Anwendungsbereich

Hinweise zu PostgreSQL-Einstellungen

KOMMENTAR ZUR SPALTE „users.name“ IST „Benutzername“;

SQL-Spaltennamen verwenden reservierte Wörter

Wenn bei SQL-Abfragen ein Konflikt zwischen dem Feldnamen und dem reservierten Wort (Schlüsselwort) des Datenbanksystems auftritt, tritt ein Syntaxfehler auf. Die Bewältigung dieses Problems erfordert eine umfassende Betrachtung sowohl unter Präventions- als auch unter Lösungsaspekten.

1. Grundlegende Lösung: Vermeiden Sie die Verwendung reservierter Wörter (Datenbankdesign)

Dies ist der am meisten empfohlene Ansatz und zielt darauf ab, das Problem an der Ursache zu beseitigen.

2. Technische Lösung: Verwenden Sie Anführungszeichen, um Identifikationssymbole einzuschließen (Programmcode-Implementierung).

Wenn die Verwendung reservierter Wörter nicht vermieden werden kann (z. B. beim Arbeiten mit einer vorhandenen Datenbankstruktur), muss der Feldname in entsprechende Anführungszeichen gesetzt werden, um der SQL-Engine klar mitzuteilen, dass es sich um einen Bezeichner und nicht um einen SQL-Befehl handelt.

Datenbanksystem Anführungszeichen Beispiel
MySQL / MariaDB Backtick (`) SELECT `interval`, `time` FROM table;
PostgreSQL / Oracle doppelte Anführungszeichen (") SELECT "interval", "time" FROM table;
SQL Server eckige Klammern ([]) SELECT [interval], [time] FROM table;

3. Programmcode-Automatisierung (SQL dynamisch generieren)

Wenn Sie in Programmiersprachen wie Python dynamisch SQL-Anweisungen generieren müssen, sollten Sie eine Funktion zum automatischen Ausführen von Anführungszeichen implementieren, um sicherzustellen, dass die generierten Anweisungen sicher und korrekt sind.

4. Vorteile des ORM-Frameworks

Wenn Sie ein ORM (wie SQLAlchemy oder Django ORM) verwenden, verarbeitet das Framework automatisch reservierte Wörter und Anführungszeichenunterschiede zwischen verschiedenen Datenbanken und abstrahiert die zugrunde liegende SQL-Ausgabe, was die Entwicklungsarbeit erheblich vereinfacht und die Stabilität des Codes verbessert.

SQL DATETIME-Vergleich

Verwenden Sie TIMESTAMPDIFF

Berechnet den Zeitunterschied in Sekunden zwischen zwei DATETIME-Feldern.

SELECT * FROM table_name
WHERE TIMESTAMPDIFF(SECOND, datetime_column1, datetime_column2) < 5;

Verwenden Sie ABS(TIMESTAMPDIFF)

Stellen Sie sicher, dass es sich bei der Zeitdifferenz um einen absoluten Wert handelt, um Reihenfolgeeffekte zu vermeiden.

SELECT * FROM table_name
WHERE ABS(TIMESTAMPDIFF(SECOND, datetime_column1, datetime_column2)) < 5;

DATEDIFF verwenden (nur für Tagesvergleiche)

Wenn Sie nur vergleichen möchten, ob sie am selben Tag sind, können Sie DATEDIFF verwenden.

SELECT * FROM table_name
WHERE DATEDIFF(datetime_column1, datetime_column2) = 0;

Subtrahieren Sie direkt mit TIMESTAMP

Gilt für Datenbanken, die Zeitstempeloperationen unterstützen, z. B. MySQL.

SELECT * FROM table_name
WHERE ABS(UNIX_TIMESTAMP(datetime_column1) - UNIX_TIMESTAMP(datetime_column2)) < 5;

MySQL verarbeitet Millisekunden-Zeitstempel

Der von Ihnen angegebene Wert1763251200000Ist ein standardmäßiger **Millisekunden-Zeitstempel** (Unix-Epochenzeit in Millisekunden).

Integrierte MySQL-Funktionen (wie zFROM_UNIXTIME) Die Standardverarbeitung ist **Zeitstempel der zweiten Ebene**. Daher müssen Sie Millisekunden-Zeitstempel vor der Verwendung in Sekunden umwandeln.

Korrektur: durch 1000 dividieren

Teilen Sie Ihren Millisekundenwert durch $1000$, um den für die MySQL-Funktion erforderlichen Zeitstempel der zweiten Ebene zu erhalten:

1763251200000 / 1000 = 1763251200

1. Konvertieren Sie in das DATETIME-Format

verwendenFROM_UNIXTIME()Die Funktion konvertiert den Zeitstempel der zweiten Ebene in Standard-MySQLDATETIMEFormat:

SELECT FROM_UNIXTIME(1763251200000 / 1000);

Wenn Ihre MySQL-Version Millisekunden unterstützt und Sie die Präzision in Millisekunden in den Ergebnissen beibehalten möchten, können Sie den zweiten Parameter verwenden:

SELECT FROM_UNIXTIME(1763251200000 / 1000, '%Y-%m-%d %H:%i:%s.%f');

In:

2. Konvertieren in UNIX_TIMESTAMP (Sekunden)

Wenn Sie den Millisekundenwert in einer Ganzzahl speichern möchten (z. B.INToderBIGINT) als Zeitstempel der zweiten Ebene verwenden, ist nur eine einfache Divisionsoperation erforderlich:

SELECT 1763251200000 / 1000;

Oder wenn Ihre Daten in Tabellenfeldern gespeichert sind (z. B. ist das Feld benannt).timestamp_ms）：

SELECT timestamp_ms / 1000 AS unix_timestamp_s FROM your_table;

3. Im Feld DATETIME/TIMESTAMP speichern

Wenn Sie diesen Wert in etwas einfügen, das hatDATETIMEoderTIMESTAMPTypfeld, das Sie eingeben müssenVALUESFühren Sie die Konvertierung teilweise durch:

INSERT INTO your_table (datetime_column) 
VALUES (FROM_UNIXTIME(1763251200000 / 1000));

4. Umgang mit der UTC-Zeitzone

FROM_UNIXTIME()Die Funktion konvertiert den UTC-Zeitstempel in die für Ihren MySQL-Server konfigurierte Zeitzone. Wenn Ihr Zeitstempel auf UTC basiert und Sie möchten, dass das Ergebnis ebenfalls UTC ist, müssen Sie sicherstellen, dass die Zeitzone Ihres Servers oder Ihrer Verbindung korrekt eingestellt ist oder verwendenCONVERT_TZDie Funktion führt eine explizite Zeitzonenbehandlung durch.

Formatieren Sie FLOAT

Verwenden Sie FORMAT()

Verwenden Sie in MySQLFORMAT()um Gleitkommazahlen zu formatieren.

SELECT FORMAT(123.4567, 2); -- Ergebnis: '123,46'

Verwenden Sie ROUND()

ROUND()Wird zum Runden auf eine feste Anzahl von Dezimalstellen verwendet.

SELECT ROUND(123.4567, 2); -- Ergebnis: 123,46

Verwenden Sie CAST() oder CONVERT()

Konvertieren Sie FLOAT in DECIMAL, um eine feste Anzahl von Dezimalstellen beizubehalten.

SELECT CAST(123.4567 AS DECIMAL(10,2)); -- Ergebnis: 123,46
SELECT CONVERT(123.4567, DECIMAL(10,2)); -- Ergebnis: 123,46

Auf Datentabellenfelder anwenden

SELECT id, FORMAT(price, 2) AS formatted_price FROM products;

Felder formatieren

Sie können die Anzahl der Dezimalstellen direkt beim Erstellen einer Datentabelle festlegen.

TABELLE ERSTELLEN Produkte (
  id INT PRIMÄRSCHLÜSSEL,
  Preis DECIMAL(10,2) – zwei Dezimalstellen
);

Gesamtsumme des Abfragefelds

Verwenden Sie MAX()

Ermitteln Sie den Maximalwert des Felds.

SELECT MAX(price) AS max_price FROM products;

Verwenden Sie MIN()

Ermitteln Sie den Mindestwert des Felds.

SELECT MIN(price) AS min_price FROM products;

Verwenden Sie AVG()

Berechnen Sie den Durchschnitt der Felder.

SELECT AVG(price) AS avg_price FROM products;

Fragen Sie gleichzeitig MAX, MIN, AVG ab

SELECT 
  MAX(price) AS max_price, 
  MIN(price) AS min_price, 
  AVG(price) AS avg_price
FROM products;

Berechnet nach Kategorie

SELECT category, 
       MAX(price) AS max_price, 
       MIN(price) AS min_price, 
       AVG(price) AS avg_price
FROM products
GROUP BY category;

Verwenden Sie eine Unterabfrage, um den Maximalwert zu ermitteln

Finden Sie den Maximalwert eines anderen Abfrageergebnisses.

SELECT MAX(price) FROM (SELECT price FROM products WHERE category = 'electronics') AS subquery;

Verwenden Sie ORDER BY + LIMIT

Ermitteln Sie nach dem Sortieren den Maximalwert.

SELECT price FROM products WHERE category = 'electronics' ORDER BY price DESC LIMIT 1;

Verwenden Sie WITH (Common Table Expression, CTE).

WITH filtered_products AS (
  SELECT price FROM products WHERE category = 'electronics'
)
SELECT MAX(price) FROM filtered_products;

geometrische Mittelformel

Berechnungsformel für den geometrischen Mittelwert:

GM = (x1 * x2 * ... * xn)^(1/n)

Verwendung von EXP() und LOG()

In SQL kann der geometrische Mittelwert mithilfe logarithmischer Operationen berechnet werden.

SELECT EXP(AVG(LOG(price))) AS geometric_mean FROM products WHERE price > 0;

Verwenden Sie POWER()

verwendenPOWER()Berechnen Sie die n-te Wurzel:

SELECT POWER(EXP(SUM(LOG(price))), 1 / COUNT(price)) AS geometric_mean 
FROM products WHERE price > 0;

Dinge zu beachten

SELECT EXP(AVG(LOG(price))) AS geometric_mean FROM products WHERE price > 0;

SQL berechnet die Standardabweichung des Feldes

Berechnen Sie die Standardabweichung mit STDDEV()

In MySQL/MariaDB können Sie verwendenSTDDEV()um die Standardabweichung zu berechnen.

Beispiel:

SELECT STDDEV(salary) AS salary_stddev FROM employees;

Unterscheiden Sie zwischen der Stammstandardabweichung und der Stichprobenstandardabweichung

SQL bietet zwei Methoden zur Berechnung der Standardabweichung:

Beispiel:

SELECT 
    STDDEV_POP(salary) AS population_stddev, 
    STDDEV_SAMP(salary) AS sample_stddev
FROM employees;

Berechnen Sie die Standardabweichung manuell

Wenn die SQL-Version dies nicht unterstütztSTDDEV(), können Sie die folgende Formel verwenden:

SELECT SQRT(
    SUM(POW(salary - (SELECT AVG(salary) FROM employees), 2)) / COUNT(salary)
) AS salary_stddev
FROM employees;

abschließend

SQL WHERE-Klausel

SQLWHEREDie Klausel wird zum Extrahieren aus der Tabelle verwendetExtrahieren Sie Datensätze, die bestimmte Bedingungen erfüllen(Spalte/Zeile). Es ist einer der grundlegendsten und wichtigsten Teile der Data Manipulation Language (DML), der zum genauen Filtern der erforderlichen Daten verwendet wird.

Rolle und Zweck

Grundgrammatik

WHEREDie Klausel folgt normalerweiseFROMNachsatz:

SELECT column_name(s)
FROM table_name
WHERE condition;

oder wenn es zum Ändern oder Löschen von Informationen verwendet wird:

UPDATE table_name
SET column1 = value1
WHERE condition;

Häufig verwendete Vergleichs- und logische Operatoren

WHEREOperatoren werden in Klauseln verwendet, um Bedingungen festzulegen. Die häufigsten sind:

Betreibertyp Operator beschreiben
Vergleichsoperator = gleich
Vergleichsoperator >, <, >=, <= Größer als, kleiner als, größer oder gleich, kleiner oder gleich
Vergleichsoperator <>oder!= nicht gleich
Logische Operatoren AND Erfüllen Sie mehrere Bedingungen gleichzeitig
Logische Operatoren OR eine der Bedingungen erfüllen
Logische Operatoren NOT Bedingung ist nicht erfüllt
spezielle Operatoren BETWEEN Innerhalb eines bestimmten Bereichs (einschließlich Grenzen)
spezielle Operatoren LIKE Fuzzy-Matching-String-Muster (matching%oder_）
spezielle Operatoren IN Der Wert ist ein beliebiges Element in der Liste
spezielle Operatoren IS NULL / IS NOT NULL Ob der Feldwert NULL ist

Anwendungsbeispiele

Angenommen, es gibt eine Datei mit dem NamenEmployeesTisch, inklEmployeeID, LastName(Nachname),Salary(Gehalt) undDepartment(Abteilung).

Beispiel 1: Einzelbedingung

Finden Sie alle Mitarbeiter mit der Abteilung „Vertrieb“:

SELECT EmployeeID, LastName
FROM Employees
WHERE Department = 'Sales';

Beispiel 2: Numerischer Vergleich und logisches UND

Finden Sie Mitarbeiter, deren Gehalt mindestens 50.000 beträgt und deren Abteilung nicht „HR“ ist:

SELECT *
FROM Employees
WHERE Salary >= 50000 AND Department != 'HR';

Beispiel 3: Bereich ZWISCHEN

Finden Sie Mitarbeiter mit Gehältern zwischen 60.000 und 80.000 (einschließlich):

SELECT *
FROM Employees
WHERE Salary BETWEEN 60000 AND 80000;

Beispiel 4: Liste IN

Finden Sie Mitarbeiter in den Bereichen „Marketing“ oder „Finanzen“:

SELECT *
FROM Employees
WHERE Department IN ('Marketing', 'Finance');

Dies entspricht der VerwendungWHERE Department = 'Marketing' OR Department = 'Finance'。

SQL GROUP BY-Klausel

SQLGROUP BYKlausel wird verwendet, umSELECTIn einer Anweisung werden Zeilen mit demselben Wert (oder einer Wertekombination für mehrere Felder) in Zusammenfassungsspalten gruppiert. Es wird häufig mit SQL verwendetZusammenfassungsfunktionZusammengenommen wird ein Gesamtwert für jede Gruppe berechnet.

Rolle und Zweck

Grundgrammatik

GROUP BYKlausel muss enthalten seinWHEREKlausel, aber danachHAVINGUndORDER BYVorsatz.

SELECT Spaltenname(n), aggregat_function(column_to_summarize)
VON Tabellenname
WHERE condition_on_rows – (optional) Filtert einzelne Zeilen vor dem Gruppieren
GROUP BY Spaltenname(n) – gibt die Spalte an, die für die Gruppierung verwendet wird
HAVING condition_on_groups – (optional) Filtergruppen nach der Gruppierung
ORDER BY Spaltenname(n);

Wichtige Regeln:alles, was darin vorkommtSELECTin der Liste, aberNEINVon Zusammenfassungsfunktionen umgebene Spalten sindmussenthaltenGROUP BYin-Klausel.

Anwendungsbeispiele

Angenommen, es gibt eine Datei mit dem NamenProductsTisch, inklCategory(Produktkategorie) undPrice(Preis).

Nachfrage: Finden Sie den Durchschnittspreis und die Produktmenge für jede Produktkategorie.

SELECT Category, AVG(Price) AS AveragePrice, COUNT(ProductID) AS TotalProducts
FROM Products
GROUP BY Category;

Beispielbeschreibung

Mehrspaltige Gruppierung

Sie können basierend auf mehreren Feldern gruppieren. Zeilen werden nur dann gruppiert, wenn alle angegebenen Felder denselben Wert haben.

Nachfrage: Ermitteln Sie den Gesamtumsatz für jede Kombination aus Produktkategorie (Kategorie) und Lieferant (Lieferant).

SELECT Category, Supplier, SUM(SalesAmount) AS TotalSales
FROM SalesRecords
GROUP BY Category, Supplier
ORDER BY Category, Supplier;

Dies führt zu einem Ergebnis, bei dem jede Zeile den Gesamtumsatz für eine eindeutige Kombination aus Kategorie und Lieferant darstellt.

Häufig verwendete Zusammenfassungsfunktionen

Funktion Funktion
COUNT() Zählen Sie die Anzahl der Zeilen in einer Gruppe.
SUM() Berechnet die Summe numerischer Felder in einer Gruppe.
AVG() Berechnet den Durchschnitt numerischer Felder in einer Gruppe.
MAX() Ermitteln Sie den Maximalwert eines Felds in einer Gruppe.
MIN() Ermitteln Sie den Mindestwert eines Felds in einer Gruppe.

SQL HAVING-Klausel

In SQL,HAVINGDie Klausel wird zum Filtern von Gruppenergebnissen (Gruppen) verwendet und wird normalerweise mit verwendetGROUP BYKlauseln werden zusammen verwendet.

ObwohlWHEREDie Klausel wird zum Filtern der Zeilen einer einzelnen Spalte (Rows) verwendet, ist jedoch nützlich, wenn gruppenbasierte Zusammenfassungswerte erforderlich sind (z. B.COUNT(), SUM(), AVG()Wenn Sie Gruppen filtern, indem Sie auf das Ergebnis der Zusammenfassungsfunktion warten, müssen Sie verwendenHAVINGKlausel.

Der Unterschied zwischen HABEN und WO

Besonderheit WHERE-Klausel HAVING-Klausel
Zeitpunkt der Ausführung Filtern Sie die Originalzeilen, bevor die Daten gruppiert werden (GROUP BY). Nachdem die Daten gruppiert wurden (GROUP BY), filtern Sie die zusammengefassten Gruppen.
Verfügbare Konditionen Zusammenfassungsfunktionen (wie COUNT, SUM, AVG) können nicht direkt verwendet werden. Zum Festlegen von Filterbedingungen müssen Aggregationsfunktionen verwendet werden.
Anwendungsobjekte Ein einzelner Spaltenwert. Gruppenergebnisse.

Grundgrammatik

SELECT Spaltenname(n), Aggregatfunktion(Spaltenname)
VON Tabellenname
WHERE condition_on_rows – (optional) einzelne Zeilen filtern
GROUP BY Spaltenname(n)
HAVING condition_on_groups – (erforderliche) Filtergruppen
ORDER BY Spaltenname(n);

Anwendungsbeispiele

Angenommen, es gibt eine Datei namensOrdersTisch, inklCustomerID(Kundennummer) undTotalAmount(Gesamtbetrag der Bestellung). wir wollen alles herausfindenDer durchschnittliche Bestellwert übersteigt 500der Kunden.

SQL-Abfrage

SELECT CustomerID, AVG(TotalAmount) AS AverageOrder
FROM Orders
GROUP BY CustomerID
HAVING AVG(TotalAmount) > 500;

Beispielbeschreibung

Beispiel für eine zusammengesetzte Bedingung

Angenommen, wir möchten alle Kunden finden, deren Gesamtbestellanzahl **3** beträgt und deren durchschnittliche Bestellmenge weniger als 1000** beträgt.

SQL-Abfrage

SELECT CustomerID, COUNT(OrderID) AS TotalOrders, AVG(TotalAmount) AS AverageOrder
FROM Orders
GROUP BY CustomerID
HAVING COUNT(OrderID) >= 3 AND AVG(TotalAmount) < 1000;

Beispielbeschreibung

SQL-Referenzalias mit Unterabfrage

Problembeschreibung

existierenSELECTIn der Abfrage, falls definiertexpr1 AS field1, kann es drin seinexpr2verwendet infield1？

Antwort: Nein, aufgrund der Reihenfolge, in der SQL-Abfragen ausgeführt werden

Die Ausführungsreihenfolge von SQL bestimmt, dass Aliase nicht identisch sein könnenSELECTIntern noch einmal zitiert:

SELECT price * 1.1 AS new_price, new_price + 10 AS final_price FROM products; -- Fehler

Fehlermeldung:

Unknown column 'new_price' in 'field list'

Lösung

Methode 1: Verwenden Sie eine Unterabfrage

kann zunächst in der Unterabfrage berechnet werdennew_price, und dann in der äußeren Abfrage referenziert:

SELECT new_price, new_price + 10 AS final_price
FROM (SELECT price * 1.1 AS new_price FROM products) AS subquery;

Methode 2: Verwenden von CTE (WITH-Anweisung)

Verfügbar, wenn SQL Common Table Expressions (CTE) unterstützt.WITHZur Vereinfachung:

WITH cte AS (
    SELECT price * 1.1 AS new_price FROM products
)
SELECT new_price, new_price + 10 AS final_price FROM cte;

Methode 3: Wiederholen Sie den Ausdruck

Wenn es sich nur um eine einfache Operation handelt, können Sie die Berechnung direkt wiederholen (dies wird jedoch nicht empfohlen, da die Lesbarkeit schlecht ist):

SELECT price * 1.1 AS new_price, price * 1.1 + 10 AS final_price FROM products;

abschließend

JOIN

JOIN-Einführung

JOIN wird verwendet, um verwandte Daten aus mehreren Datentabellen zusammenzuführen und eine Beziehung basierend auf einem bestimmten Feld (normalerweise einem Fremdschlüssel) herzustellen.

INNER JOIN

Es werden nur die Daten zurückgegeben, die die Bedingungen in den beiden Datentabellen erfüllen.

SELECT orders.order_id, customers.name
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id;

LEFT JOIN

Gibt alle Daten in der linken Tabelle zurück. Wenn in der rechten Tabelle keine entsprechenden Daten vorhanden sind, werden diese angezeigt.NULL。

SELECT customers.name, orders.order_id
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id;

RIGHT JOIN

Gibt alle Daten in der rechten Tabelle zurück. Wenn in der linken Tabelle keine entsprechenden Daten vorhanden sind, werden diese angezeigt.NULL。

SELECT customers.name, orders.order_id
FROM orders
RIGHT JOIN customers ON orders.customer_id = customers.customer_id;

FULL JOIN

Gibt alle Daten in der linken und rechten Tabelle zurück und zeigt sie an, wenn keine Übereinstimmung vorliegt.NULL。

MySQL unterstützt FULL JOIN nicht, verfügbarLEFT JOINUndRIGHT JOINKombinationssimulation.

SELECT customers.name, orders.order_id
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id
UNION
SELECT customers.name, orders.order_id
FROM customers
RIGHT JOIN orders ON customers.customer_id = orders.customer_id;

CROSS JOIN

Gibt alle möglichen Kombinationen (kartesisches Produkt) zweier Tabellen zurück.

SELECT customers.name, products.product_name
FROM customers
CROSS JOIN products;

SELF JOIN

Wird für Verknüpfungen innerhalb derselben Tabelle verwendet, z. B. Beziehungen zu Vorgesetzten von Mitarbeitern.

SELECT A.name AS employee, B.name AS manager
FROM employees A
JOIN employees B ON A.manager_id = B.employee_id;

Schließen Sie bei der Ausführung von .sql andere .sql-Dateien ein

Verwenden Sie die Direktive SOURCE oder \i

MySQL oder MariaDB

Sie können andere „.sql“-Dateien in Ihre MySQL- oder MariaDB-Skripte einbinden, indem Sie die „SOURCE“-Direktive verwenden:
-- abc.sql
SOURCE other_file.sql;
SOURCE another_file.sql;

PostgreSQL (psql)

In PostgreSQL können Sie andere „.sql“-Dateien mit der „\i“-Direktive einbinden:
-- abc.sql
\i other_file.sql
\i another_file.sql

Mit Batch-Skript ausführen

Wenn der SQL-Client die direkte Dateieinbindung nicht unterstützt, können Sie Batch-Skripte verwenden, um mehrere „.sql“-Dateien nacheinander auszuführen.

Beispiel für eine Linux-Shell

#!/bin/bash
mysql -u user -p database_name < abc.sql
mysql -u user -p database_name < other_file.sql

Fortgeschrittener Weg: SQL-Dateien vorverarbeiten

Wenn Ihr SQL-Client die Dateieinbindung nicht direkt unterstützt, können Sie die Haupt-SQL-Datei mit der referenzierten „.sql“-Datei zusammenführen, bevor Sie sie ausführen.

Dateien mit Shell-Skript zusammenführen

cat abc.sql other_file.sql another_file.sql > combined.sql
mysql -u user -p database_name < combined.sql

Dinge zu beachten

1. **Ausführungsreihenfolge**: Stellen Sie sicher, dass die enthaltenen Dateien in der richtigen Reihenfolge ausgeführt werden, um Abhängigkeitsprobleme mit Datentabellen oder Funktionen zu vermeiden. 2. **Bibliotheksspezifische Syntax**: Die Befehlssyntax verschiedener Bibliotheken kann unterschiedlich sein, bitte beachten Sie die entsprechende Dokumentation. 3. **Dateipfad**: Verwenden Sie korrekt absolute oder relative Pfade, um auf „.sql“-Dateien zu verweisen. 4. **Zugriffsberechtigung**: Stellen Sie sicher, dass der SQL-Client die Berechtigung zum Lesen der enthaltenen Datei hat.

Zusammenfassen

Durch die obige Methode können SQL-Skripte für eine einfache Verwaltung und Wiederverwendung modularisiert werden.

Übergabe von Parametern beim Einbinden anderer SQL-Dateien

MySQL und MariaDB

MySQL und MariaDB unterstützen die Übergabe von Parametern in der `SOURCE`-Direktive nicht direkt, aber Variablen können mit der enthaltenen .sql-Datei verwendet werden. So geht's:

Übergeben Sie Parameter mithilfe von Variablen

1. Legen Sie die Variablen in der Haupt-SQL-Datei fest:
   SET @param1 = 'value1';
   SOURCE other_file.sql;
2. Referenzvariablen in „other_file.sql“:
   SELECT * FROM table WHERE column = @param1;

PostgreSQL (psql)

PostgreSQL unterstützt das Setzen von Variablen über den Befehl „\set“ und deren Übergabe an andere Dateien:

Übergeben Sie Parameter mithilfe von Variablen

1. Legen Sie die Variablen in der Haupt-SQL-Datei fest:
   \set param1 'value1'
   \i other_file.sql
2. Verwenden Sie Variablen in „other_file.sql“:
   SELECT * FROM table WHERE column = :'param1';

Übergeben Sie Parameter mithilfe von Befehlszeilentools

Die Übergabe von Parametern an eine SQL-Datei bei der Ausführung über die Befehlszeile ist eine gängige Methode.

Beispiel für eine MySQL-Befehlszeile

1. Verwenden Sie „sed“ oder andere Tools, um Parameter zur Ausführungszeit zu ersetzen:
   sed "s/{param1}/value1/g" abc.sql | mysql -u user -p database_name
2. Verwenden Sie den Platzhalter „{param1}“ in der SQL-Datei und ersetzen Sie ihn durch das Befehlszeilentool.

Beispiel für eine PostgreSQL-Befehlszeile

1. Parameter direkt im Befehl „psql“ festlegen:
   psql -d database_name -v param1=value1 -f abc.sql
2. Verwenden Sie `:'param1'`, um Variablen in der SQL-Datei darzustellen.

Generieren Sie SQL mithilfe von Code

Eine weitere Lösung ist die dynamische Generierung von SQL über eine Programmiersprache wie Python oder Bash: 1. Erstellen Sie dynamisch eine SQL-Datei mit Parametern im Programm. 2. Führen Sie die generierte SQL-Datei aus.

Dinge zu beachten

1. **Sicherheit**: Vermeiden Sie die direkte Einbettung von vom Benutzer eingegebenen Parametern in SQL und berücksichtigen Sie das Risiko einer SQL-Injection. 2. **Umgebungsvariablen**: Einige Tools unterstützen die Verwendung von Umgebungsvariablen zur Übergabe als Parameter.

Zusammenfassen

Obwohl die Parameterübergabe von SQL-Dateien nicht immer direkt unterstützt wird, kann sie über Variablen, Befehlszeilentools oder Programmiersprachen erreicht werden, wobei die Flexibilität von den Eigenschaften des Tools und der Datenbank abhängt.

gespeicherte Prozedur

Einführung in gespeicherte Prozeduren (vorgespeicherte Routinen)

Gespeicherte Prozeduren (gespeicherte Routinen oder gespeicherte Prozeduren) sind eine Reihe von SQL-Anweisungen, die vorkompiliert und in der Datenbank gespeichert werden und über Aufrufe ausgeführt werden können, um die Effizienz zu verbessern und die Codeduplizierung zu reduzieren.

Erstellen Sie eine gespeicherte Prozedur

DELIMITER //
CREATE PROCEDURE GetAllProducts()
BEGIN
  SELECT * FROM products;
END //
DELIMITER ;

Rufen Sie die gespeicherte Prozedur auf

CALL GetAllProducts();

gespeicherte Prozedur mit Parametern

Eingabeparameter

Suchen Sie nach Produkten in einer bestimmten Kategorie:

DELIMITER //
CREATE PROCEDURE GetProductsByCategory(IN category_name VARCHAR(50))
BEGIN
  SELECT * FROM products WHERE category = category_name;
END //
DELIMITER ;

Anruf:

CALL GetProductsByCategory('electronics');

Ausgabeparameter

Berechnen Sie die Gesamtzahl der Artikel in einer Kategorie:

DELIMITER //
CREATE PROCEDURE GetProductCountByCategory(IN category_name VARCHAR(50), OUT total_count INT)
BEGIN
  SELECT COUNT(*) INTO total_count FROM products WHERE category = category_name;
END //
DELIMITER ;

Anruf:

CALL GetProductCountByCategory('electronics', @count);
SELECT @count;

Speichern Sie Variablen zur Verwendung in Programmen

DELIMITER //
CREATE PROCEDURE CalculateTotalRevenue()
BEGIN
  DECLARE total DECIMAL(10,2);
  SELECT SUM(price) INTO total FROM sales;
  SELECT total AS total_revenue;
END //
DELIMITER ;

Anruf:

CALL CalculateTotalRevenue();

Verwenden Sie die bedingte Kontrolle in gespeicherten Prozeduren

IF-Bedingung

DELIMITER //
CREATE PROCEDURE CheckStock(IN product_id INT, OUT stock_status VARCHAR(20))
BEGIN
  DECLARE stock INT;
  SELECT quantity INTO stock FROM inventory WHERE id = product_id;

  IF stock > 10 THEN
    SET stock_status = 'In Stock';
  ELSEIF stock > 0 THEN
    SET stock_status = 'Low Stock';
  ELSE
    SET stock_status = 'Out of Stock';
  END IF;
END //
DELIMITER ;

Anruf:

CALL CheckStock(1, @status);
SELECT @status;

Verwendung von LOOP innerhalb eines gespeicherten Programms

DELIMITER //
CREATE PROCEDURE CountDown(IN start_num INT)
BEGIN
  DECLARE i INT;
  SET i = start_num;

  loop_label: LOOP
    IF i <= 0 THEN
      LEAVE loop_label;
    END IF;
    SELECT i;
    SET i = i - 1;
  END LOOP;
END //
DELIMITER ;

Anruf:

CALL CountDown(5);

Gespeichertes Programm löschen

DROP PROCEDURE IF EXISTS GetAllProducts;

abschließend

Parameter-Standardwerte für gespeicherte Prozeduren

MySQL/MariaDB unterstützt das direkte Festlegen des Standardwerts von Parametern nicht

In MySQL und MariaDB können gespeicherte Prozedurparameter nicht direkt auf Standardwerte gesetzt werden (im Gegensatz zu SQL Server oder PostgreSQL). Es ist jedoch möglich, es zu verwendenIFBedingte Anweisungen zur Simulation voreingestellter Werte.

Methode 1: Verwenden Sie IF-Bedingungen, um Standardwerte festzulegen

Angenommen, wir möchten eine Abfrage durchführenusersTabelle, wenn Parameteruser_idWenn nicht angegeben, ist die Standardabfrage-ID 1:

DELIMITER //
PROZEDUR ERSTELLEN GetUserById(IN user_id INT)
ANFANGEN
    WENN user_id NULL IST, DANN
        SET user_id = 1; --Standardwert
    ENDE WENN;

    SELECT * FROM Benutzer WHERE id = user_id;
ENDE //
Trennzeichen;

Ausführungsmethode

CALL GetUserById(NULL); -- wird die ID = 1 abfragen
CALL GetUserById(5); – Abfrage-ID = 5

Methode 2: Verwenden Sie COALESCE(), um Standardwerte festzulegen

COALESCE()Gibt den angegebenen Standardwert zurück, wenn der Parameter NULL ist:

DELIMITER //
CREATE PROCEDURE GetUserById(IN user_id INT)
BEGIN
    SELECT * FROM users WHERE id = COALESCE(user_id, 1);
END //
DELIMITER ;

Ausführungsmethode

CALL GetUserById(NULL); -- Standard ist 1
CALL GetUserById(10); – Abfrage-ID = 10

Methode 3: Verwendung optionaler Parameter (Problemumgehung)

Wenn Sie möchten, dass die Parameter optional sind, können Sie mehrere gespeicherte Prozeduren erstellen. Zum Beispiel:

DELIMITER //
CREATE PROCEDURE GetAllUsers()
BEGIN
    SELECT * FROM users;
END //

CREATE PROCEDURE GetUserById(IN user_id INT)
BEGIN
    SELECT * FROM users WHERE id = user_id;
END //
DELIMITER ;

Ausführungsmethode

CALL GetAllUsers(); -- keine Parameter, alle abfragen
CALL GetUserById(3); – Abfrage-ID = 3

abschließend

Rückgabewert der gespeicherten Prozedur

Methode 1: Verwenden Sie den OUT-Parameter, um einen Wert zurückzugeben

Die gespeicherte Prozedur von MySQL/MariaDB wird nicht unterstütztRETURNGibt Abfrageergebnisse zurück, kann aber verwendet werdenOUTParameter geben Werte zurück.

DELIMITER //
CREATE PROCEDURE GetUserCount(OUT user_count INT)
BEGIN
    SELECT COUNT(*) INTO user_count FROM users;
END //
DELIMITER ;

Rufen Sie die gespeicherte Prozedur auf und erhalten Sie den Rückgabewert

CALL GetUserCount(@total);
SELECT @total; -- Zeigt die Anzahl der Benutzer an

Methode 2: Verwenden Sie SELECT, um die Ergebnismenge zurückzugeben

Wenn Sie ein Abfrageergebnis direkt zurückgeben möchtenSELECTDas ist es:

DELIMITER //
CREATE PROCEDURE GetUserById(IN user_id INT)
BEGIN
    SELECT * FROM users WHERE id = user_id;
END //
DELIMITER ;

Rufen Sie die gespeicherte Prozedur auf

CALL GetUserById(5); – Fragen Sie den Benutzer mit der ID 5 ab

Methode 3: Verwenden Sie RETURN, um einen einzelnen Wert zurückzugeben

Obwohl MySQL unterstütztRETURN, kann aber nur einen einzelnen Wert zurückgeben, der normalerweise zur Steuerung des Prozesses verwendet wird:

DELIMITER //
PROZEDUR ERSTELLEN GetMaxSalary()
ANFANGEN
    DECLARE max_salary DECIMAL(10,2);
    SELECT MAX(Gehalt) INTO max_Gehalt FROM Mitarbeiter;
    RETURN max_salary; – Dadurch wird der Wert jedoch nicht direkt in MySQL zurückgegeben
ENDE //
Trennzeichen;

MySQL kann nicht direktCALLUm den RETURN-Wert zu erhalten, wird empfohlen, den OUT-Parameter zu verwenden:

DELIMITER //
CREATE PROCEDURE GetMaxSalary(OUT max_salary DECIMAL(10,2))
BEGIN
    SELECT MAX(salary) INTO max_salary FROM employees;
END //
DELIMITER ;

CALL GetMaxSalary(@max);
SELECT @max; -- Zeigen Sie das Höchstgehalt an

Methode 4: Mehrere Werte zurückgeben

Mehrere verwendenOUTDie Parameter liefern unterschiedliche Berechnungsergebnisse:

DELIMITER //
CREATE PROCEDURE GetUserStats(OUT total_users INT, OUT avg_age DECIMAL(5,2))
BEGIN
    SELECT COUNT(*) INTO total_users FROM users;
    SELECT AVG(age) INTO avg_age FROM users;
END //
DELIMITER ;

Rufen Sie die gespeicherte Prozedur auf und erhalten Sie mehrere Rückgabewerte

CALL GetUserStats(@total, @avg);
SELECT @total, @avg; -- Zeigen Sie die Gesamtzahl und das Durchschnittsalter der Benutzer an

abschließend

Verwendung von Rückgabewerten außerhalb gespeicherter CALL-Prozeduren

Methode 1: OUT-Parameter verwenden und in Variablen speichern

Auf die gespeicherte MySQL-Prozedur kann über zugegriffen werdenOUTParameter geben Werte zurück, die dann außerhalb von CALL verwendet werden könnenSELECTHolen Sie sich diesen Wert.

Erstellen Sie eine gespeicherte Prozedur

DELIMITER //
CREATE PROCEDURE GetTotalUsers(OUT total_users INT)
BEGIN
    SELECT COUNT(*) INTO total_users FROM users;
END //
DELIMITER ;

Rufen Sie die gespeicherte Prozedur auf und verwenden Sie den Rückgabewert

CALL GetTotalUsers(@total);
SELECT @total AS UserCount; -- Rückgabewert außerhalb von CALL verwenden

Methode 2: Verwenden Sie Variablen, um zurückgegebene Abfrageergebnisse zu speichern

Wenn eine gespeicherte Prozedur verwendet wirdSELECTZurückgegebene Ergebnisse können nicht direkt in Variablen gespeichert, aber verwendet werdenINSERT INTO ... SELECT。

Erstellen Sie eine gespeicherte Prozedur

DELIMITER //
CREATE PROCEDURE GetMaxSalary()
BEGIN
    SELECT MAX(salary) AS max_salary FROM employees;
END //
DELIMITER ;

Verwenden von Variablen für den Zugriff auf Abfrageergebnisse

TEMPORÄRE TABELLE ERSTELLEN temp_result (max_salary DECIMAL(10,2));

INSERT INTO temp_result EXECUTE GetMaxSalary();

SELECT max_salary FROM temp_result; – wird außerhalb von CALL verwendet

Methode 3: Verwenden Sie die Prepared-Anweisung, um dynamisch auf Ergebnisse zuzugreifen

Wenn auf die von der gespeicherten Prozedur generierten Ergebnisse in Variablen zugegriffen werden muss, können Sie Folgendes verwendenPREPAREUndEXECUTE。

Erstellen Sie eine gespeicherte Prozedur

DELIMITER //
CREATE PROCEDURE GetUserById(IN user_id INT)
BEGIN
    SELECT name FROM users WHERE id = user_id;
END //
DELIMITER ;

Variablen aufrufen und speichern

SET @sql = 'CALL GetUserById(5)';
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

abschließend

Rückruf der MySQL-Funktion

Problembeschreibung

In MySQL/MariaDB kann eine gespeicherte Funktion (Funktion) nicht zurückgegeben werdenSELECTErgebnismenge, sonst kommt es zu einem Fehler:

ERROR 1415 (0A000): Not allowed to return a result set from a function

Lösung

Methode 1: Verwenden Sie stattdessen die gespeicherte Prozedur

Funktionen können keine Ergebnismengen zurückgeben, gespeicherte Prozeduren jedoch schon.

Falsche Funktion:

DELIMITER //
FUNKTION ERSTELLEN GetUsers()
RÜCKGABETABELLE
ANFANGEN
    RETURN (SELECT * FROM user); – Das ist nicht erlaubt
ENDE //
Trennzeichen;

Richtige gespeicherte Prozedur:

DELIMITER //
CREATE PROCEDURE GetUsers()
BEGIN
    SELECT * FROM users;
END //
DELIMITER ;

Gespeicherte Prozedur aufrufen:

CALL GetUsers();

Methode 2: Verwenden Sie eine Funktion, um einen einzelnen Wert zurückzugeben

Wenn Sie nur einen einzelnen Wert zurückgeben müssen (z. B. eine Anzahl oder einen Maximalwert), können Sie verwendenRETURN。

DELIMITER //
CREATE FUNCTION GetUserCount()
RETURNS INT DETERMINISTIC
BEGIN
    DECLARE total INT;
    SELECT COUNT(*) INTO total FROM users;
    RETURN total;
END //
DELIMITER ;

Funktion verwenden:

SELECT GetUserCount();

Methode 3: Temporäre Tabelle verwenden

Wenn Sie wirklich mehrere Ergebniszeilen innerhalb der Funktion zurückgeben müssen, können Sie die Funktion Daten in die temporäre Tabelle einfügen lassen und diese dann extern abfragen.

DELIMITER //
CREATE FUNCTION PopulateTempUsers()
RETURNS INT DETERMINISTIC
BEGIN
    CREATE TEMPORARY TABLE IF NOT EXISTS temp_users AS (SELECT * FROM users);
    RETURN 1;
END //
DELIMITER ;

Verwenden Sie die temporäre Tabelle, um Daten zu lesen:

SELECT PopulateTempUsers();
SELECT * FROM temp_users;

abschließend

HeidiSQL

Einführung

HeidiSQL ist ein kostenloser Open-Source-SQL-Client, der MySQL, MariaDB, PostgreSQL und MS SQL Server unterstützt. Es bietet eine GUI zum Verwalten von Datenbanken, zum Ausführen von SQL-Abfragen, zum Importieren/Exportieren von Daten usw.

Hauptfunktionen

Herunterladen und installieren

1. Gehen Sie zur offiziellen Website, um HeidiSQL herunterzuladen:https://www.heidisql.com/download.php2. Führen Sie das Installationsprogramm aus und befolgen Sie die Anweisungen, um die Installation abzuschließen
3. Öffnen Sie HeidiSQL und richten Sie eine neue Verbindung ein

Stellen Sie eine Verbindung zu MySQL/MariaDB her

1. Starten Sie HeidiSQL
2. Klicken Sie auf „Hinzufügen“, um eine neue Verbindung zu erstellen
3. Einstellungen:
   - Hostname/IP: 127.0.0.1 oder Remote-Server-IP
   - Benutzername: root oder anderer Benutzer
   - Passwort: das entsprechende Passwort
   - Port: 3306 (MySQL/MariaDB)
4. Klicken Sie auf „Öffnen“, um eine Verbindung zur Datenbank herzustellen

SQL-Abfrage ausführen

Geben Sie die SQL-Anweisung in das HeidiSQL-Abfragefenster ein:

SELECT * FROM users WHERE status = 'active';

Klicken Sie auf die Schaltfläche „Ausführen“, um die Ergebnisse anzuzeigen.

Daten importieren/exportieren

SQL exportieren

1. Klicken Sie mit der rechten Maustaste auf die Datenbank → wählen Sie „SQL exportieren“
2. Wählen Sie die zu exportierende Datentabelle aus
3. Legen Sie das Exportformat fest (.sql, .csv, .json)
4. Klicken Sie auf „Exportieren“

SQL importieren

1. Öffnen Sie HeidiSQL und wählen Sie die Zieldatenbank aus
2. Klicken Sie auf „Extras“ → „SQL-Datei ausführen“.
3. Wählen Sie die .sql-Datei aus und führen Sie sie aus

Benutzerberechtigungen verwalten

1. Geben Sie „Extras“ → „Benutzerberechtigungen verwalten“ ein.
2. Wählen Sie die zu verwaltenden Benutzer aus
3. Datenbankberechtigungen festlegen (SELECT, INSERT, UPDATE, DELETE usw.)
4. Klicken Sie auf „Speichern“

abschließend

HeidiSQL fügt neue gespeicherte Prozeduren hinzu

Schritt 1: Stellen Sie eine Verbindung zur Datenbank her

1. Starten Sie HeidiSQL.
2. Stellen Sie eine Verbindung zum MySQL- oder MariaDB-Server her.
3. Wählen Sie die Zieldatenbank in der Datenbankliste auf der linken Seite aus.

Schritt 2: Erstellen Sie eine neue gespeicherte Prozedur

1. Klicken Sie links mit der rechten Maustaste auf den Datenbanknamen und wählen Sie „Neu erstellen“ → „Programm speichern“.
2. HeidiSQL öffnet ein neues SQL-Bearbeitungsfenster und stellt eine Standardvorlage für gespeicherte Prozeduren bereit.

Schritt 3: Schreiben Sie die gespeicherte Prozedur

Das Folgende ist ein einfaches Beispiel, das alle Daten in der Benutzertabelle zurückgibt:

DELIMITER //
CREATE PROCEDURE GetAllUsers()
BEGIN
    SELECT * FROM users;
END //
DELIMITER ;

Schritt 4: Führen Sie die gespeicherte Prozedur aus

1. Klicken Sie auf die Schaltfläche „Los“ (grüner Blitz).
2. Bei erfolgreicher Ausführung finden Sie das Programm links in der Spalte „Programm speichern“.

Schritt 5: Testen Sie die gespeicherte Prozedur

CALL GetAllUsers();

Erweitert: Gespeicherte Prozeduren mit Parametern

Übergeben Sie Parameter zum Filtern von Daten, z. B. Abfragen basierend auf der Benutzer-ID:

DELIMITER //
CREATE PROCEDURE GetUserById(IN user_id INT)
BEGIN
    SELECT * FROM users WHERE id = user_id;
END //
DELIMITER ;

Rufen Sie eine gespeicherte Prozedur mit Parametern auf:

CALL GetUserById(1);

Gespeichertes Programm löschen

DROP PROCEDURE IF EXISTS GetAllUsers;

abschließend

Lösung zur Entwicklung von Datenbankanwendungen

In der heutigen Softwareentwicklungsumgebung haben sich Datenbankanwendungen von der einfachen Datenspeicherung zu umfassenden Lösungen entwickelt, die KI, Edge Computing sowie automatisierten Betrieb und Wartung integrieren. Im Folgenden sind die aktuellen Mainstream-Entwicklungsmodelle aufgeführt:

KI-Integration und RAG-Architekturlösung

Mit der Popularität großer Sprachmodelle (LLM) ist die Retrieval-Augmented Generation (RAG) zu einem Entwicklungsstandard geworden. Diese Art von Lösung konzentriert sich auf die Vektorisierung und den Abruf unstrukturierter Daten.

Serverlose und Cloud-native Lösungen

Um den Betrieb und die Wartung zu vereinfachen und die Anlaufkosten zu senken, tendieren Entwickler dazu, Datenbankdienste mit automatischer Skalierungsfunktion zu wählen, was besonders bei Webanwendungen beliebt ist.

Komplettlösung für die Entwicklung von Typsicherheit

Im TypeScript-Ökosystem ist die Synchronisierung von Datenbankdefinitionen mit Front-End-Typen der Schlüssel zur Gewährleistung der Systemstabilität.

Verteilte und Edge-Datenbanklösungen

Um den globalen Zugriff zu bewältigen und die Latenz zu reduzieren, ist es zu einem Trend geworden, Daten benutzernah bereitzustellen.

Vergleichstabelle der Entwicklungspläne

Plankategorie Technologie repräsentieren Kernvorteile
KI zuerst pgvector, Milvus Unterstützen Sie die semantische Suche und den Aufbau einer Wissensdatenbank
Serverloser Typ Supabase, Vercel Postgres Belastungsfreier Betrieb und Wartung, Abrechnung nach Volumen
Effizienter Entwicklungstyp Drizzle ORM, Prisma Extrem hohe Typensicherheit und Entwicklungsgeschwindigkeit
Dezentrale Architektur CockroachDB, TiDB Regionsübergreifende Bereitstellung, automatische Fehlertoleranz

Front-End-Schnittstelle für Datenbankanwendungen

Bei der Entwicklung moderner Datenbankanwendungen geht es im Front-End nicht mehr nur um die Darstellung von Daten, sondern auch um die Typsynchronisierung mit der Back-End-Datenbank, die Statusverwaltung und die serverseitige Rendering-Leistung. Im Folgenden sind die aktuellen Mainstream-Optionen aufgeführt:

Reagieren Sie auf Ökosysteme und Meta-Frameworks

Dies ist derzeit die Lösung mit dem höchsten Marktanteil und der umfassendsten Community-Unterstützung und eignet sich besonders für komplexe Datenbankverwaltungssysteme.

Vue-Ökosystemlösung

Es ist für seine einfache Lernkurve und hochintegrierte Toolkette bekannt und erfreut sich bei den Backend-Managementsystemen mittlerer und großer Unternehmen großer Beliebtheit.

Geben Sie Sicherheits- und UI-Komponententools ein

Um die Entwicklungseffizienz zu verbessern und Datenübertragungsfehler zu reduzieren, sind moderne Frontends in hohem Maße auf die folgenden Tools angewiesen:

Low-Code und Inhouse-Tool-Entwicklung

Für Datenbankverwaltungsschnittstellen, die in Unternehmen verwendet werden, entscheiden sich Entwickler häufig für schnellere Integrationslösungen.

Vergleichstabelle zur Auswahl der Front-End-Lösung

Schemaname Anwendbare Szenarien Kernvorteile
Next.js + Tailwind SaaS-Produkte, moderne Webanwendungen SEO-freundlich, ultimative Leistungsoptimierung
Vue + Element Plus Unternehmensinternes Backend- und Managementsystem Reichhaltige Komponenten und extrem schnelle Entwicklung
TanStack Query Anwendungen mit hochfrequenten Datenaktualisierungen Leistungsstarke Cache-Verwaltung und automatische Synchronisierung
Retool Interne Notfallwartungswerkzeuge Drag & Drop, fast kein CSS-Schreiben erforderlich

Datenbank-Web-Benutzeroberfläche

Moderne Datenbank-Webverwaltungstools ermöglichen Teams den direkten Zugriff auf Daten über einen Browser, ohne dass Desktop-Software installiert werden muss. Nach der funktionalen Positionierung werden folgende Kategorien in vier Kategorien eingeteilt:

1. Universelles Verwaltungstool

Diese Art von Tool unterstützt mehrere Datenbankverbindungen (wie MySQL, PostgreSQL, SQL Server) und eignet sich für Entwickler, die verschiedene Umgebungen verwalten.

2. Datenbankspezifische Verwaltungsschnittstelle

Eine Benutzeroberfläche, die vom Originalhersteller oder der Community umfassend für eine bestimmte Datenbank optimiert wurde.

3. Low-Code- und Tabellenkalkulationsschnittstelle

Konvertieren Sie die Datenbank in eine intuitive Benutzeroberfläche ähnlich wie Excel, die für nichttechnisches Personal oder zum schnellen Aufbau eines internen Backends geeignet ist.

4. Native Schnittstelle der Cloud-Plattform

Wenn die Daten bei einem Cloud-Dienstanbieter gehostet werden, weist die native Webkonsole normalerweise den höchsten Integrationsgrad auf.

CloudBeaver

CloudBeaver ist eine webbasierte Open-Source-Datenbankverwaltungslösung, die vom DBeaver-Team entwickelt wurde. Es nutzt die Java-Back-End- und React-Front-End-Architektur, um Benutzern die sichere Verwaltung verschiedener Datenbanken über den Browser zu ermöglichen, was sich sehr gut für Szenarien eignet, die Fernzugriff oder Teamzusammenarbeit erfordern.

Kernfunktionsmerkmale

Unterschiede zwischen Community Edition und Enterprise Edition

CloudBeaver bietet eine Open-Source-Community-Edition (Community) und eine kommerzielle Enterprise-Edition (Enterprise). Der Hauptunterschied liegt in der erweiterten Funktionsunterstützung:

Merkmale Gemeinschaft Unternehmen (Unternehmen)
Unterstützung für SQL-Datenbanken Unterstützt die meisten gängigen SQL-Anweisungen Enthält NoSQL (MongoDB, Redis)
Cloud-Service-Integration Grundverkabelung Native Unterstützung für das Durchsuchen von AWS-, GCP- und Azure-Ressourcen
Authentifizierung Kontopasswort Unterstützt SSO, SAML, LDAP, Kerberos
Erweiterte Tools Grundlegende Abfrage KI-Assistent (SQL-Generierung), visueller Abfrage-Builder

Anwendbare Szenarien

CloudBeaver-Installationsmethode:

Die am meisten empfohlene Installationsmethode für CloudBeaver ist die Verwendung von Docker, da es bereits alle erforderlichen Java-Umgebungen und Treiber paketiert. Es gibt drei Hauptbereitstellungspfade:

1. Verwenden Sie Docker, um schnell zu starten

Dies ist die einfachste Methode. Führen Sie einfach eine Befehlszeile aus, um den Dienst zu starten. Standardmäßig wird Port 8978 überwacht.

docker run --name cloudbeaver -d -p 8978:8978 dbeaver/cloudbeaver:latest

Öffnen Sie nach der Ausführung die Browsereingabehttp://localhost:8978Sie können den Einstellungsassistenten aufrufen.

2. Verwenden Sie Docker Compose (empfohlen für die Produktionsumgebung)

Die Datenpersistenz kann einfach über Compose-Dateien verwaltet werden, um sicherzustellen, dass die Einstellungen nach dem Neustart des Containers nicht verloren gehen.


version: '3'
services:
  cloudbeaver:
    image: dbeaver/cloudbeaver:latest
    container_name: cloudbeaver
    restart: unless-stopped
    ports:
      - "8978:8978"
    volumes:
      - ./cloudbeaver-data:/opt/cloudbeaver/workspace

Speichern Sie den obigen Inhalt unterdocker-compose.yml, und dann ausführendocker-compose up -d。

3. Eigenständige Installation ausführbarer Dateien (Standalone)

Wenn Docker in der Umgebung nicht verwendet werden kann, können Sie die kompilierte Binärdatei herunterladen und manuell installieren:

Ersteinstellungen nach der Installation

Beim ersten Betreten der Weboberfläche führt Sie das System durch die folgende Konfiguration:

email: [email protected]
T:0000
資訊與搜尋 | 回dev首頁
email: Yan Sa [email protected] Line: 阿央
電話: 02-27566655 ,03-5924828
阿央
泱泱科技
捷昱科技泱泱企業