Einführung

SQL steht für „Structured Query Language“ - eine Datenbanksprache

• zur Definition von Datenstrukturen in relationalen Datenbanken sowie
• zum Bearbeiten (Einfügen, Verändern, Löschen) und
• Abfragen von darauf basierenden Datenbeständen.

SQL ist der relevante Standard für Datenbanken. Bei so gut wie allen Client-Server-Systemen wird die Kommunikation zwischen Clients und Server über SQL abgewickelt und auch Desktop- bzw. Personal-Datenbanksysteme wie beispielsweise Access oder dBase lassen sich per SQL-Anweisung steuern. Wenn Sie mit Datenbanken zu tun haben, kommen Sie an SQL kaum vorbei.

Eigenschaften von SQL

SQL ist

• Hersteller unabhängig und portable zwischen verschiedenen Computer- und DB-Systemen
• unterstützt
  • von IBM, Oracle, Informix
  • durch Microsoft ODBC (Open Database Connectivity)
  • durch JDBC (Java Database Connectivity)

Kategorien der SQL-Befehle

SQL-Befehle lassen sich in 4 Kategorien unterteilen:

1. Data Definition Language (DDL) – Befehle zur Definition des Datenbankschemas (Erzeugen, Ändern, Löschen von Datenbanktabellen, Definition von Primärschlüsseln und Fremdschlüsseln)
2. Data Manipulation Language (DML) – Befehle zur Datenmanipulation (Ändern, Einfügen, Löschen von Datensätzen) und lesendem Zugriff
3. Data Query Language (QL) – Befehle zur Abfrage und Aufbereitung der gesuchten Informationen, wird auch als Untermenge der DML klassifiziert
4. Data Control Language (DCL) – Befehle für die Rechteverwaltung

Während DML-Befehle zum Bearbeiten, Einfügen oder Löschen von Daten, QL-Befehle für den lesenden Zugriff auf die Datenbank vorgesehen sind, lassen sich die DDL-Befehle für die Definition des Schemas einer Datenbank verwenden. DCL-Befehle schließlich dienen dazu, einzelne Rechte zu verwalten oder Transaktionen zu kontrollieren.

Als zusätzliche Kategorie gilt

1. Transaction Control Language (TCL) – Befehle für die Transaktionskontrolle

Constraints

Constraints (deutsch „Einschränkung“) definieren Bedingungen, die beim Einfügen, Ändern und Löschen von Datensätzen in der Datenbank erfüllt werden müssen. Ein Constraints ist so etwas wie eine Regel für eine Spalte bezogen auf die Gesamtsicht der Tabelle.

Für die meisten Tabellen werden Sie wahrscheinlich eine PRIMARY KEY-Einschränkung definieren, die besagt, dass der Skalar einzigartig sein muss und nicht NULL sein kann.

Doch es können auch andere Arten von Constraints wie UNIQUE, FOREIGN KEY, CHECK oder DEFAULT erforderlich sein.

Kombischlüssel: Man kann mit mind. 2 Feldern einen Kombischlüssel festlegen. Beachten Sie aber, dass pro CREATE-TABLE-Anweisung nur einmal eine PRIMARY KEY-Einschränkung verwendet werden darf.

Es gibt folgende Typen:

• NOT NULL
• UNIQUE
• PRIMARY KEY
• FOREIGN KEY

NOT NULL

Wert darf nicht lehr gelassen werde.

CREATE TABLE tabelle1 (
name Datentyp NOT NULL
);

UNIQUE

Wert muss eindeutig sein

CREATE TABLE tabelle1 (
name Datentyp UNIQUE,
);

PRIMARY KEY

CREATE TABLE tabelle1 (
name INT PRIMARY KEY,
);

FOREIGN KEY

CREATE TABLE tabelle1 (
name INT FOREIGN KEY REFERENCES Tabelle (Spalte),
);

Kombischlüssel

CREATE TABLE tabelle1 (
PRIMARY KEY(name1, name2)
);

Kartesische Produkt

Das kartesische Produkt (auch Kreuzprodukt) ist eine Operation der Mengenlehre, bei der zwei oder mehr Mengen miteinander verknüpft werden. Im relationalen Datenbankmodell kommt das kartesische Produkt zum Einsatz, um Tupel-Mengen in Form von Tabellen miteinander zu verbinden. Das Ergebnis dieser Operation ist wiederum eine Menge geordneter Tupel, bei der jedes Tupel aus einem Element jeder Ausgangsmenge besteht.

Als Operator für das kartesische Produkt kommt in der relationalen Algebra das Multiplikationszeichen (×) zum Einsatz.

Dazu ein Beispiel:

Das kartesische Produkt A × B der beiden Mengen A = {x, y, z} und B = {1, 2, 3} ist:

A × B = {(x,1), (x,2), (x,3), (y,1), (y,2), (y,3), (z,1), (z,2), (z,3)}

Veranschaulichen lässt sich die Rechnung anhand folgender Grafik:

Zu beachten ist dabei die Reihenfolge der Paarbildung. Das kartesische Produkt A × B entspricht beispielsweise nicht derselben Menge wie das kartesische Produkt von B × A.

A × B = {(x,1), (x,2), (x,3), (y,1), (y,2), (y,3), (z,1), (z,2), (z,3)}

B× A = {(1,x), (1,y), (1,z), (2,x), (2,y), (2,z), (3,x), (3,y), (3,z)}

In der SQL-Terminologie wird eine Operation, bei der das kartesische Produkt aus zwei Datenbanktabellen gebildet wird, als CROSS JOIN bezeichnet. In der Praxis kommen CROSS JOINs aufgrund der ungefilterten Ergebnismenge nur selten zum Einsatz.

T-SQL

Was ist T-SQL?

Die Datenbanksprache Transact-SQL ist die Sprache des Microsoft SQL Servers und wurde als Erweiterung des SQL-Standards entwickelt. Transact-SQL  erweitert den SQL-Standard um das Transaktionsverhalten, die Fehlerbehandlung und der Einsatz von lokalen Variablen und gespeicherten Prozeduren, die im Datenbankmanagementsystem hinterlegt werden können.

T-SQL Syntaxkonventionen

• Transact-SQL-Schlüsselwörter und -Befehle werden mit GROSSBUCHSTABEN geschrieben.
• T-SQL Kommentare
  • In der Datenbanksprache T-SQL können Kommentare auf zwei Arten hinterlegt werden: Einzeilige und mehrzeilige Kommentare (Blockkommentare).

T-SQL Datentypen

MySQL/MariaDB

MySQL/MariaDB-Syntaxkonventionen

Kommentare Der MySQL Server unterstützt drei Kommentarstile:

• Vom Zeichen ‘#’ bis zum Zeilenende.
• Von der Sequenz ‘– ’ bis zum Zeilenende. Bei MySQL erfordert der Kommentarstil ‘– ’ (doppelter Bindestrich) mindestens ein nachfolgendes Whitespace- oder Steuerzeichen (z. B. ein Leerzeichen, einen Tabulator, einen Zeilenwechsel usw.).
• Von der Sequenz /* bis zur folgenden Sequenz */, wie man es von der Programmiersprache C her kennt. Diese Syntax ermöglicht Kommentare, die sich über mehrere Zeilen erstrecken, da öffnende und schließende Sequenz nicht in derselben Zeile stehen müssen.

Syntax

Referentielle Integrität / Einschränkungen (de)aktiviert

Bevor die Tabellen mit Daten gefüllt werden, sollte man sich darüber Gedanken machen, in welcher Reihenfolge die Tabellen zu füllen sind. Wegen der referentiellen Integritätsbeziehungen zwischen den Tabellen ist diese nämlich nicht beliebig. So kann man z.B. die Tabelle B mit Fremdschlüsseln nicht vor der Tabelle A mit Primärschlüsseln füllen.

Man kann die referentielle Integrität allerdings, wie andere Einschränkungen auch, vor dem Einfügen von Datensätzen mit dem Befehl ausser Kraft setzen:

ALTER TABLE Artikel NOCHECK CONSTRAINT ALL

Nach dem Einfügen der Datensätze sollten die Einschränkungen wieder aktiviert werden:

ALTER TABLE Artikel CHECK CONSTRAINT ALL

DDL: Data Definition Language

Mit den SQL DDL-Befehlen kann ein vorgegebenes Datenmodell wie folgt implementiert werden:

1. Datenbank erzeugen
2. Tabellen erzeugen
3. Index erstellen (falls nötig)

Datenbank erstellen:

CREATE DATABASE [IF NOT EXIST] name
;

Datenbank löschen

DROP DATABASE [IF EXIST] name
;

Eine Datenbank auswählen

USE name
;

Tabellen erstellen:

CREATE TABLE  [IF NOT EXIST] Tabelle (
Spaltenname DATENTYP [CONSTRAINTS]
);

Beispiel:

CREATE TABLE tabelle1 (
MNR INT PRIMARY KEY,
MName VARCHAR(25) NOT NULL,
Vorname VARCHAR(25) NOT NULL,
Adresse VARCHAR(25),
Geburtstag DATETIME,
Gehalt SMALLMONEY
);

CREATE TABLE tabelle2 (
WZNR INT PRIMARY KEY,
Art VARCHAR(25) UNIQUE,
Kaufwert SMALLMONEY,
Besitzer INT FOREIGN KEY REFERENCES Mitarbeiter (MNR),
AnzahlWerkzeug INT,
Gewicht NUMERIC(4,2)
);

Tabelle löschen

DROP TABLE [IF EXIST] name
;

Spalte hinzufügen

ALTER TABLE tabelle
ADD Spaltenname DATENTYP
;

Spalte löschen

ALTER TABLE tabelle
DROP COLUMN Spaltenname
;

Datentyp ändern

MSSQL
ALTER TABLE tabelle
ALTER COLUMN Spaltenname DATENTYP
;

MySQL
ALTER TABLE tbTabelle
MODIFY COLUMN Spaltenname Datentyp
;

DML: Data Manipulation Language

Tabelle füllen

INSERT INTO tabelle (Spaltenname, Spaltenname, Spaltenname, Spaltenname)
VALUES (Wert, Wert, Wert, Wert)
;

Beispiel:
INSERT INTO Auftrag(KundenNr, ArtikelNr, Anzahl, Datum)
VALUES (001, 002, 3.25, '2.5.2003')
;

Beachten Sie folgendes:

• Ist ein Wert für den Datentyp einer Spalte zu gross, wird die Anweisung nicht ausgeführt!
• Die Werte teilweise in einfachen Anführungszeichen stehen. Diese braucht der SQL-Server, um den Wert als nicht-numerisch zu erkennen.
• Um numerische Werte mit Dezimalstellen einzufügen, müssen Sie diese mit einem Punkt versehen, da ein Komma den Anfang eines neuen Wertes bezeichnet.
• Wird ein Datensatz vollständig hinzugefügt, können Sie die Liste mit den Spaltenbezeichnungen und die Kommas nach dem Tabellennamen weglassen

Daten aktualisieren

UPDATE tb_Kunden
SET Strasse = "Zürcherstrasse"
WHERE KVorname = "Elina" AND KName = 'Meier'
;

WICHTIG: Lässt man die WHERE-Klausel weg, werden alle Datensätze der Tabelle geändert.

Daten löschen

DELETE FROM tb_Kunden
WHERE KNr = 001
;

WICHTIG: Lässt man die WHERE-Klausel weg, werden alle Datensätze der Tabelle gelöscht.

QL: Query Language

Abfrage

SELECT *
FROM tabelle
WHERE Spaltenname = Wert
;

“Stern” = Alle

….oder nur bestimmte Spalten

SELECT Spaltenname, Spaltenname
FROM tabelle
;

Mithilfe des DISTINCT-Befehls werden Redundanzen, die in einer Tabellen auftreten können, eliminiert und die Werte werden jeweils nur einmal angezeigt. Der DISTINCT Befehl wird in einer SQL-SELECT-Abfrage direkt hinter dem Select platziert.

SELECT DISTINCT Spaltenname
FROM tabelle
;

Vergleichsoperatoren

Miteinander zu vergleichende Werte müssen demselben Datentyp angehören. Dabei sind SMALLINT, INTEGER, DOUBLE und FLOAT allerdings miteinander vergleichbar.

Jokerzeichen

Zudem gibt es in SQL auch Jokerzeichen. Mit Jokerzeichen können Ähnlichkeiten abgefragt werden. Dabei wird der Vergleichsoperator durch LIKE ersetzt:

Gruppenfunktionen

Gruppenfunktionen erlauben das Filtern der Daten nach einfachen Kriterien. Sie können z. B. den Durchschnittswert, die Anzahl Daten, den höchsten oder tiefsten Wert und die Summe einer Spalte mit folgenden Ausdrücken ermitteln:

• AVG( pre) Berechnet den Durchschnittswert einer Spalte, die durch den Ausdruck bestimmt wird
• COUNT(*) Zählt alle Zeilen in der angegebenen Tabelle
• MIN( ausdruck) Findet den kleinsten Wert in der durch den Ausdruck angegebenen Spalte
• MAX( ausdruck) Findet den grössten Wert in der durch den Ausdruck angegebenen Spalte
• SUM( ausdruck) Berechnet die Summe der durch den Ausdruck angegebenen Spaltenwerte

Zeilen gruppieren und sortieren

Mit der GROUP BY- Klausel werden in der Ergebnistabelle jeweils alle Zeilen zusammengefasst, die in einer bestimmten Spalte den gleichen Wert aufweisen. Jede Gruppe wird zu einer Zeile zusammengefasst.

Die Gruppierungsklausel wird meist bei der Verwendung von Gruppenfunktionen auf Spalten nötig.

Jeder der hinter GROUP BY angegebenen Spaltenbezeichner muss vor der FROM- Anweisung ebenfalls aufgeführt sein:

Beispiel:

SELECT COUNT (Ort) AS Anzahl
FROM Kunde
GROUP BY Ort
HAVING Ort ='Zürich'
;

Durch Hinzufügen einer ORDER BY- Klausel lassen sich die Zeilen die angezeigt werden sollen, in einer bestimmten Reihenfolge geordnet werden.

Beispiel:

SELECT SchülerNr, ModulNr, Modul
FROM Belegung
WHERE ModulNr = ‘106’
ORDER BY SchülerNr ASC
;

Durch Hinzufügen einer ORDER BY- Klausel lassen sich die Zeilen die angezeigt werden sollen, in einer bestimmten Reihenfolge geordnet werden.

Das Ergebnis lässt sich mit ASC bzw. DESC Auf- und Absteigend sortieren.

Beispiel:

SELECT SchülerNr, ModulNr, Modul
FROM Belegung
WHERE ModulNr = ‘106’
ORDER BY Modul ASC
;

QL 2: JOIN

Mit einem SQL-Join kann man abfragen über mehrere Tabellen machen. Die Verbindung zwischen den Tabellen wird mit einer ähnlichen Spalte hergestellt.

TikTok

Verschiedene Typen von JOIN-Abfragen

• (INNER) JOIN: Gibt Datensätze zurück, die übereinstimmende Werte in beiden Tabellen haben
• LEFT (OUTER) JOIN: Gibt alle Datensätze aus der linken Tabelle und die übereinstimmenden Datensätze aus der rechten Tabelle zurück
• RIGHT (OUTER) JOIN: Gibt alle Datensätze aus der rechten Tabelle und die übereinstimmenden Datensätze aus der linken Tabelle zurück
• FULL (OUTER) JOIN: Gibt alle Datensätze zurück, wenn es eine Übereinstimmung in der linken oder rechten Tabelle gibt

QL 3: Unterabfragen

Eine Unterabfrage liegt vor, wenn in eine SELECT-Anfrage (die Hauptabfrage) eine weitere SELECT-Anfrage (die Unterabfrage) eingebaut wird. Die Unterabfrage ist immer Bestandteil der WHERE-Bedingung und kann sich auf dieselbe Tabelle wie die Hauptanfrage oder auf eine andere beziehen.

Die Unterabfrage schränkt die Auswahl der Abfrage ein. Mit diesem kann eine Komplexere Bedingung für die WHERE-Klausel gemacht werden.

“=” = Vergleich mit einer Zahl

IN = Vergleich mit einem String

DCL: Data Control Language

Die Datenkontroll-Sprache (DCL) kontrolliert die Sicherheit und die Zugriffsrechte für Objekte oder Teile eines Datenbanksystems. Ein sehr fein unterteiltes Rechtesystem hilft Ihnen dabei, die Zugriffsberechtigungen von unterschiedlichen Benutzern zu steuern.

Die wichtigsten Befehle sind:

• GRANT – vergibt Zugriffsrechte
• DENY – verweigert Zugriffsrechte
• REVOKE – löscht vorher vergebene oder verweigerte Zugriffsrechte

Erstellen Benutzer

CREATE USER 'benutzer'@'localhost' IDENTIFIED BY 'passwort';

Host einschränken

CREATE USER 'maria'@'247.150.130.0/255.255.255.0';

oder

CREATE USER 'marco'@'%';

Delete User

DROP USER 'username'@'localhost';

Show Permissions

SHOW GRANTS FOR 'username'@'host';

Revoke Permission

REVOKE type_of_permission ON database_name.table_name FROM 'username'@'host';

Grant Permissions

GRANT PRIVILEGE ON database.table TO 'username'@'host';

Beispie:

GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, REFERENCES, RELOAD on * . * TO 'sammy'@'localhost' WITH GRANT OPTION;

Admins/Root

GRANT ALL PRIVILEGES ON *.* TO 'sammy'@'localhost' WITH GRANT OPTION;

Weitere Infos

Using Roles

TCL: Transaction Control Language

Einen Sonderfall bilden die Sprachelemente zur Arbeit mit Transaktionen, die uneinheitlich entweder der DML, der DCL oder einer eigenen Kategorie Transaction Control Language (TCL) zugeordnet werden.

Was ist eine Transaktion?

Stellen Sie sich vor, ein Geldinstitut verfügt über ein Zahlungssystem mit Datenbank-Anbindung. Bei Überweisungen werden die Beiträge wie folgt von einem Konto auf das andere Konto gebucht:

1. Beim ersten Konto wird der Geldbetrag abgebucht.
2. Beim zweiten Konto wird der Geldbetrag gutgeschrieben.

Stellen Sie sich nun vor, der Datenbankserver stürzt genau in dem Moment ab, nachdem der erste Befehl ausgeführt wurde und bevor der zweite Befehl ausgeführt werden kann. Nach dem Hochfahren des Zahlungssystems sind die Daten der Datenbank inkonsistent, denn der betreffende Geldbetrag wurde einerseits abgebucht, andererseits aber nirgends gutgeschrieben. Der Absturz hat quasi Geld «vernichtet». Das Problem in diesem Beispiel besteht darin, dass ein konsistenter Zustand der Datenbank erst dann erreicht nachdem mehrere Befehle hintereinander ausgeführt worden sind.

Dieses Problem kann gelöst werden, indem die fraglichen Befehle zu einer Einheit zusammenfasst werden. Diese Einheit nennt man Transaktion. Eine Transaktion wird entweder ganz oder gar nicht ausgeführt. Beim Hochfahren des Systems nach einen Absturz erkennt eine Datenbank, dass nicht alle Befehle der Transaktion ausgeführt worden sind, und macht sie wieder rückgängig.

Bemerkungen

Eine Transaktion ist eine logische Arbeitseinheit, die einen oder mehrere Schritte enthält, von denen jeder erfolgreich abgeschlossen werden muss, damit die Transaktion an die Datenbank übergeben werden kann. Wenn Fehler auftreten, werden alle Datenänderungen gelöscht und die Datenbank wird zu Beginn der Transaktion in ihren ursprünglichen Zustand zurückgesetzt. Einfache Transaktion

BEGIN TRANSACTION
    INSERT INTO DeletedEmployees(EmployeeID, DateDeleted, User)
        (SELECT 123, GetDate(), CURRENT_USER);
    DELETE FROM Employees WHERE EmployeeID = 123;
COMMIT TRANSACTION

Rollback-Transaktion

Wenn in Ihrem Transaktionscode ein Fehler auftritt und Sie ihn rückgängig machen möchten, können Sie Ihre Transaktion rückgängig machen:

BEGIN TRY
    BEGIN TRANSACTION
        INSERT INTO Users(ID, Name, Age)
        VALUES(1, 'Bob', 24)

        DELETE FROM Users WHERE Name = 'Todd'
   COMMIT TRANSACTION
END TRY
BEGIN CATCH
   ROLLBACK TRANSACTION
END CATCH