Eine Einführung in die Structured Query Language (SQL)

Eine Einführung und Sammlung wichtiger Befehle der Datenbankabfragesprache SQL am Beispiel einer Übungsdatenbank, inkl. einiger Auswertungsbeispiele. Für Neueinsteiger und Anfänger geeignet.

Dieses Tutorial ist eine kostenlose stichwortartige Zusammenfassung wichtiger Fakten, Fachbegriffe, Grundlagen und Beispiele für Anfänger! Es ersetzt auf keinen Fall eine professionelle Schulung, ein Seminar oder ein Lehrbuch. Als Ergänzung zu Ihrem Lernprozess ist diese Zusammenfassung, als kleines Nachschlagewerk, jedoch ein geeignetes Mittel.

Inhaltsverzeichnis

Grundbegriffe und Einführung
Data Definition Language - DDL
Data Manipulation Language – DML
Komplexere SELECT Statements - DML
SQL Aggregats- und Skalarfunktionen - DML
JOINS – Mehrtabellenabfragen - DML
UNIONS – Zusammenführung von mehreren Tabellen - DML
Übungen zu SELECT Statements mit unserer Übungsdatenbank - DML
Eigene Datenbankbenutzer anlegen - DDL
Data Control Language – DCL
Der Vollständigkeit halber: Die Übungsdatenbank aufräumen - DDL
Download der SQL-Beispiel-Statements
Schlusswort

Grundbegriffe und Einführung

Die Structured Query Language kurz SQL, auch in einigen Tabellenbüchern als Standard Query Language bezeichnet, ist eine sogenannte Abfragesprache für relationale Datenbanken. In diesem Dokument verwenden wir MySQL und PhpMyAdmin für die Aufgaben und Übungen sowie die Syntax-Spezifika. Die Installation und Verwendung einer MySQL Datenbank sowie der zugehörigen PhpMyAdmin Oberfläche wird für dieses Tutorial vorausgesetzt. Alternativ können natürlich auch andere Datenbanksoftwareprodukte verwendet werden. In anderen Datenbankmanagementsystemen kann es jedoch zu Abweichungen in der Syntax kommen. Es werden ebenfalls Kenntnisse der theoretischen Datenbankmodellierung vorausgesetzt.

Bestandteile der Sprache SQL

Die Sprache SQL besteht grundsätzlich aus drei Bestandteilen:
1) Definition und Aufbau: DDL = Data Definition Language (z.B. CREATE, ALTER)
2) Daten-Manipulation: DML = Data Manipulation Language (z.B. INSERT, UPDATE, SELECT, DELETE)
3) Administration, Kontrolle: DCL = Data Control Language (z.B. GRANT, REVOKE)

Begrifflichkeiten

Zuerst möchte ich ein paar Begrifflichkeiten ansprechen, denn oft gibt es mehrere Begrifflichkeiten für ein- und dieselbe Sache.

Ein Datenbankmanagementsystem ist die Software für die Verwaltung und Verwendung von Datenbanken. Ein Datenbankmanagementsystem kann mehrere Datenbanken beinhalten.

Unter einer Datenbank versteht man theoretisch einen zusammenhängenden Datenbestand. Eine relationale Datenbank besteht aus einer oder mehreren Tabellen (in relationalen Modellen auch oft als Relation bezeichnet) und/oder Views (Sichten), die mit einer oder mehreren Beziehungen (Relationen) miteinander verknüpft sind. Der Aufbau der Datenbank wird als Datenbankmodell bezeichnet.

Eine Tabelle enthält Datensätze oder auch Zeilen. Ein Datensatz besteht aus einer bestimmten Anzahl von Felder oder Spalten. Die Tabellenspalten werden auch als Attribute bezeichnet. Ein Datensatz wird auch Tupel genannt.

Eine Tabellenzelle oder Zellenwert wird auch als Attributwert bezeichnet. Ist ein Attributwert nicht vorhanden also komplett leer spricht man vom Inhalt NULL. Wichtig: Eine leere Zeichenkette ist nicht gleich NULL!

Führen wir eine SQL Abfrage auf einer Datenbank aus, erhalten wir das Abfrageergebnis, auch Dynaset genannt, in tabellarischer Form zurück.

Etwas grafisch visualisiert verwenden wir folgende Begriffe:

Data Definition Language - DDL

In diesem Tutorial wollen wir mit einer leeren Datenbank starten. Somit beginnen wir mit der DDL. Befehlsübersicht der DDL

Befehl	Beschreibung
CREATE DATABASE …	Erstellt eine leere Datenbank.
DROP DATABASE …	Löscht eine bestehende Datenbank.
CREATE TABLE …	Erstellt eine Tabelle innerhalb der aktuellen Datenbank.
ALTER TABLE …	Verändert die Struktur inkl. Datentypen einer Tabelle innerhalb der aktuellen Datenbank.
DROP TABLE …	Löscht eine Tabelle innerhalb der aktuellen Datenbank.
TRUNCATE TABLE …	Löscht den Inhalt einer Tabelle innerhalb der aktuellen Datenbank und setzt ggf. automatisch generierte Primärschlüssel auf den Standardwert (meistens 1) zurück.
CREATE INDEX …	Erstellt einen Datenbankindex auf der aktuellen Datenbank. Ein Datenbankindex ist eine von der Datenstruktur getrennte Indexstruktur innerhalb einer Datenbank, welche die Suche und das Sortieren nach bestimmten Attributen beschleunigen kann.
DROP INDEX …	Entfernt einen Datenbankindex von der aktuellen Datenbank.
CREATE USER …	Erstellt einen neuen Datenbankbenutzer auf dem Datenbankserver. (Rechte werden in der DCL besprochen)
DROP USER …	Entfernt einen Datenbankbenutzer auf dem Datenbankserver.
CREATE VIEW …	Speichert eine SQL Abfrage als Sicht (View).
DROP VIEW …	Entfernt eine gespeicherte Sicht (View).

Die wichtigsten Datentypen

Art	Datentyp
Ganzzahlen	TINYINT (8 Bit)
	SMALLINT (16 Bit)
	MEDIUMINT (24 Bit)
	INT (32 Bit)
	BIGINT (64 Bit)
Fließkommazahlen	FLOAT (4 Byte)
	DOUBLE (8 Byte)
Binärdaten (z.B. für Dateien)	TINYBLOB (bis zu 255 Byte)
	BLOB (bis zu 65.535 Byte)
	MEDIUMBLOB (bis zu ca. 16,7 Mio. Byte)
	LONGBLOB (bis zu ca. 4 Milliarden Byte)
Zeichenketten und Text	CHAR(x) (bei x <= 255; x steht für die Anzahl Zeichen – Achtung feste Zeichenlänge!)
	VARCHAR(x) (bei x <= 255 jedoch variable Zeichenlänge innerhalb der Felder möglich)
	TINYTEXT (Abkürzung für VARCHAR(255))
	TEXT (bis zu 65.535 Zeichen)
	MEDIUMTEXT (bis zu ca. 16,7 Mio. Zeichen)
	LONGTEXT (bis zu ca. 4 Milliarden Zeichen)
Datumsangaben / Zeitangaben	DATE (Format YYYY-MM-TT)
	TIME (Format HH:MM:SS)
	DATETIME (Datum inkl. Uhrzeit)
	TIMESTAMP (Trennzeichenlose Zeichenfolge: z.B.: 20150102111300)
	YEAR (Jahreszahl Format YYYY)

Hinweis: In dieser Dokumentation werden alle Tabellen und Attributnamen absichtlich mit kleinen Anfangsbuchstaben geschrieben. In einigen Unternehmen gelten solche Namenskonventionen um falsche Schreibweisen prinzipiell vermeiden zu können.

Beispiel DDL Statements mit Erläuterung:

Befehl	Erläuterung
CREATE TABLE mitarbeiter ( ma_id INT NOT NULL PRIMARY KEY, name VARCHAR(150) NOT NULL, aktiv INT DEFAULT 1 );	Erstellt die Tabelle „mitarbeiter“ inkl. dem Feld „ma_id“, welches nicht NULL sein darf sowie der Primärschlüssel ist, und dem Feld „name“, welches ebenfalls nicht NULL sein darf und vom Datentyp VARCHAR(150) ist, und dem Feld „aktiv“ mit einem Standard Wert von 1 (INT).
ALTER TABLE mitarbeiter ADD vorname VARCHAR(50);	Aktualisiert bzw. verändert die Tabelle „mitarbeiter“ und fügt ein neues Feld / Attribut „vorname“ hinzu, welches vom Datentyp VARCHAR(50) ist.
DROP TABLE mitarbeiter;	Löscht und entfernt die Tabelle „mitarbeiter“.
CREATE INDEX index_name ON mitarbeiter (name);	Erstellt einen Datenbankindex mit der Bezeichnung „index_name“ auf der Tabelle „mitarbeiter“ und dem Feld „name“.
DROP INDEX index_name;	Entfernt den Datenbankindex mit der Bezeichnung “index_name”.
CREATE VIEW sqlmyview1 AS SELECT * FROM mitarbeiter;	Erstellt eine Sicht auf die Tabelle mitarbeiter unter dem Namen sqlmyview1.
DROP VIEW sqlmyview1;	Löscht die soeben erstellte Sicht (View) mit dem Namen sqlmyview1.

Unsere Übungsdatenbank – Teil 1
Um später mit SQL Statements Auswertungen machen zu können, benötigen wir eine Übungsdatenbank. Diese erstellen wir in diesen Teilkapiteln Schritt-für-Schritt. Teil 1: Aufbau der Datenbank mittels DDL Statements.

Geplant ist folgende einfache Übungsdatenbank - selbstverständlich sehr stark vereinfacht für Übungszwecke:

Starten wir mit einer neuen leeren Datenbank mit der Bezeichnung „db_sqluebung1“ (Unter der Verwendung des Root-Benutzers, später erzeugen wir einen eigenen Benutzer):

CREATE DATABASE db_sqluebung1;

Mit Angabe des Zeichensatzes:
CREATE DATABASE db_sqluebung1 CHARACTER SET utf8 COLLATE utf8_general_ci;

In der MySQL Konsole benötigt man zudem einen Befehl, der klar macht, dass wir eine bestimmte Datenbank für die anschließend folgenden Befehle verwenden möchten. Dieser Befehl lautet: USE. Achtung - hier kein Semikolon notwendig. Innerhalb PhpMyAdmin kann man die Datenbank mittels einfachem Mausklick auswählen und dort SQL Statements gezielt absetzen. Dort wird der USE Befehl also nicht unbedingt benötigt. Der Vollständigkeit halber:

USE db_sqluebung1

Starten wir nun mit der ersten Tabelle „kunden“ und einem Standardwert für die Spalte „ktyp“:

CREATE TABLE kunden (
kid INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
ktyp CHAR(1) NOT NULL DEFAULT ‘g’,
firma VARCHAR(200),
name VARCHAR(200) NOT NULL,
vorname VARCHAR(100),
strasse VARCHAR(200),
hausnr INT,
plz CHAR(5),
ort VARCHAR(150)
);

Als nächstes erstellen wir die Tabelle „bestellungen“:

CREATE TABLE bestellungen (
bid INT NOT NULL AUTO_INCREMENT,
kunden_id INT NOT NULL,
bdatum DATE,
PRIMARY KEY (bid),
FOREIGN KEY (kunden_id) REFERENCES kunden(kid)
);

Hier sehen wir eine alternative Schreibweise um einen Primärschlüssel zu definieren. Zusätzlich enthält diese Tabelle auch die Kundennummer als Fremdschlüssel (FOREIGN KEY).

Für die nächste Tabelle „b_positionen“ – unseren Bestellpositionen – benötigen wir zuerst die Artikeltabelle, da der dort enthaltene Primärschlüssel als Fremdschlüssel vorkommt.

Wichtige Regel zur Erstellung von Tabellenstrukturen: Es müssen zuerst immer die Tabellen mit den Primärschlüsseln erstellt werden bevor Tabellen erstellt werden, welche diese als Fremdschlüssel verwenden.

Erstellen wir also zuerst die Artikeltabelle mit der Bezeichnung „artikelstamm“:

CREATE TABLE artikelstamm (
aid INT NOT NULL AUTO_INCREMENT,
a_bez VARCHAR(255),
a_preis DOUBLE NOT NULL,
PRIMARY KEY (aid)
);

Und zu guter Letzt die Zwischentabelle „b_positionen“, welche die Artikel zu den Bestellungen unter Angabe der Menge zuordnen wird:

CREATE TABLE b_positionen (
bpid INT NOT NULL AUTO_INCREMENT,
bestell_id INT NOT NULL,
artikel_id INT NOT NULL,
menge DOUBLE NOT NULL,
PRIMARY KEY (bpid),
FOREIGN KEY (bestell_id) REFERENCES bestellungen(bid),
FOREIGN KEY (artikel_id) REFERENCES artikelstamm(aid)
);

Somit haben wir unsere Übungsdatenbank von einer leeren Datenbank aus strukturiert aufgebaut.

Im nächsten Schritt geht es an die Füllung der Datenbank mit Testdaten und den dazugehörigen Auswertungen.

Data Manipulation Language – DML

Die DML beinhaltet Abfrage-Befehle für die Datenmanipulation. So können wir Daten hinzufügen, verändern und löschen sowie Auswertungen vornehmen und somit Daten selektieren.

Grundbefehlsübersicht der DML

Befehl	Beschreibung
INSERT INTO …	Fügt neue Daten in eine Tabelle ein.
UPDATE …	Aktualisiert bzw. ändert bestehende Daten.
DELETE …	Löscht Datensätze.
SELECT (DISCTINCT) …	Selektiert Datensätze oder auch nur Teile von Datensätzen. DISTINCT = ohne Duplikate!
… WHERE	Selektionsbedingung (Kann mit logischen Operatoren ergänzt / verknüpft werden AND, OR)
… GROUP BY	Gruppierung von Abfrageergebnissen (zusätzliche Verwendung von Aggregatsfunktionen möglich)
… ORDBER BY	Sortieren einer Tabelle nach einem Attribut (Spalte) (ASC = Aufsteigend, DESC = Absteigend)
… HAVING	Umgangssprachlich: Die WHERE Bedingung für GROUP BY Statements
… LIKE	Vergleichsoperator für Zeichenketten
… JOIN	Mehrtabellenabfragen, Verbindung von zwei oder mehreren Tabellen zu einer Ergebnistabelle
… UNION	Zusammenführen von Tabellen zu einer Tabelle (Achtung Spaltenzahl muss identisch sein!)

Beispiel DML Statements mit Erläuterung:

Befehl	Beschreibung
INSERT INTO mitarbeiter VALUES ( 1, ‘Max Mustermann‘, 0 );	Fügt in die Tabelle „mitarbeiter“ einen neuen Datensatz ein. Die Werte innerhalb der Klammer müssen genau zu den in der Tabellendefinition festgelegten Spaltenreihenfolge passen inkl. den entsprechenden Datentypen.
INSERT INTO mitarbeiter ( name, aktiv ) VALUES ( ‘Max Mustermann‘, 0 );	Fügt in die Tabelle „mitarbeiter“ einen neuen spezifischen Datensatz mit einer Auswahl an zu füllenden Feldern ein. Dieser erhält eine automatische ID sowie die Werte „Max Mustermann“ für die Spalte „name“ und den Wert 0 für die Spalte „aktiv“. Zeichenketten werden immer in Anführungszeichen geschrieben.
UPDATE mitarbeiter SET vorname = ‘Max‘, name = ‘Mustermann‘ WHERE ma_id = 1;	Aktualisiert bzw. verändert in der Tabelle „mitarbeiter“ die Spalten „vorname“ und „nachname“ auf die Werte „Max“ und „Mustermann“ für die Datensätze (WHERE) bei denen die Spalte „ma_id“ den Wert 1 besitzt. ACHTUNG: UPDATE Statements ohne WHERE Bedingungen ändern die gesamte Tabellenspalte!
DELETE FROM mitarbeiter WHERE ma_id = 1;	Löscht und entfernt den Datensatz aus der Tabelle „mitarbeiter“ bei dem die Spalte „ma_id“ den Wert 1 besitzt. ACHTUNG: DELETE Statements ohne WHERE Bedingungen löschen die gesamten Tabelleninhalte!
SELECT name FROM mitarbeiter;	Einfache Selektion einer oder mehrerer Spalten (auch Projektion genannt) aus einer Tabelle. In diesem Fall die Spalte „name“ aus der Tabelle „mitarbeiter“.
SELECT name FROM mitarbeiter WHERE ma_id >= 10;	Einfache Selektion einer oder mehrerer Spalten sowie einzelner oder mehrerer Datensätze einer Tabelle. In diesem Beispiel wird die Spalte „name“ aller Datensätze der Tabelle „mitarbeiter“ angezeigt, welche eine ma_id >= 10 haben.
SELECT name, vorname FROM mitarbeiter ORDER BY name ASC, vorname ASC;	Selektiert die Spalten „name“ und „vorname“ aller Datensätze der Tabelle „mitarbeiter“ und sortiert diese aufsteigend nach der Spalte „name“ und anschließend aufsteigend nach der Spalte „vorname“.

Unsere Übungsdatenbank – Teil 2
Nun ist es an der Zeit unsere Übungsdatenbank mit „Inhalt“ zu füttern.

Hier ein Beispiel für einen Kundendatensatz:

INSERT INTO kunden (ktyp, firma, name, vorname, strasse, hausnr, plz, ort)
VALUES ( ‘p‘, ‘‘, ‘Mustermann‘, ‘Max‘, ‘Musteralle‘, ‘123‘, ‘12345‘, ‘Musterhausen’ );

Die Spalte „kid“ können wir ignorieren, da wir für die Primärschlüsselspalte den Auto_Increment Modus verwendet haben. Diese Spalte füllt das Datenbankmanagementsystem für uns. Wird dieser Modus nicht verwendet, muss man auch die ID Spalte explizit einfügen.

Der Vollständigkeit halber die Schreibvariante ohne die Feldliste:

INSERT INTO kunden VALUES ( 1, ‘p‘, ‘‘, ‘Mustermann‘, ‘Max‘, ‘Musteralle‘, ‘123‘, ‘12345‘, ‘Musterhausen’ );

Hierbei müssen wir jedoch auch den Wert für die Primärschlüsselspalte mit angeben!

Auch bei der Befüllung der Tabellen müssen wir nach der Regel vorgehen, dass wir die Tabellen zuerst füllen, welche Referenzen auf den Primärschlüssel in anderen Tabellen als Fremdschlüssel enthalten.

Ein Beispiel für einen Artikel:

INSERT INTO artikelstamm ( a_bez, a_preis ) VALUES ( 'Schrauben Set - 20 St.', 2.99 );

Die Zahlenangabe Menge (DOUBLE) erfolgt mit der Schreibweise Punkt statt Komma!

Und eine Bestellung inkl. Bestell-Positionen:

INSERT INTO bestellungen ( kunden_id, bdatum ) VALUES ( 1, '2015-01-05' );
INSERT INTO b_positionen ( bestell_id, artikel_id, menge ) VALUES ( 1, 1, 3 );

Die Datumsangabe wird im entsprechenden Datumsformat (DATE = YYYY-MM-DD) und Anführungszeichen eingegeben!

Und noch ein paar weitere Beispieldatensätze (Kurzform des INSERT INTO Statements), damit die Auswertungen später auch etwas mehr Spaß machen!

Einfügen von 9 weitere Kunden:
INSERT INTO kunden VALUES ( 2, 'p', '', 'Mustermann', 'Mareike', 'Musteralle', '123', '12345', 'Musterhausen' );
INSERT INTO kunden VALUES ( 3, 'g', 'Beispiel Montage GmbH', 'Beispiel', 'Berta', 'Beispielweg', '5', '33215', 'Beispielstadt' );
INSERT INTO kunden VALUES ( 4, 'g', 'Malermeister Pinsel', 'Pinsel', 'Peter', 'Malerstrasse', '1', '12225', 'Malerort' );
INSERT INTO kunden VALUES ( 5, 'p', '', 'Beispiel', 'Berta', 'Beispielweg', '5', '33215', 'Beispielstadt' );
INSERT INTO kunden VALUES ( 6, 'p', '', 'Hubertus', 'Dr. Hubert', 'Hubertusweg', '7', '77345', 'Hubertusgemeinde' );
INSERT INTO kunden VALUES ( 7, 'g', 'Firma Heinzelmann', 'Heinzelmann', 'Heinz', 'Hubertusweg', '55', '77345', 'Hubertusgemeinde' );
INSERT INTO kunden VALUES ( 8, 'g', 'Rohrfrei AG', 'Roland', 'Rene', 'Musteralle', '12', '12345', 'Musterhausen' );
INSERT INTO kunden VALUES ( 9, 'g', 'Finanzwelt AG', 'Fiskus', 'Friedolin', 'Musteralle', '1', '12345', 'Musterhausen' );
INSERT INTO kunden VALUES ( 10, 'g', 'Gartenspezi GmbH', 'Gruener', 'Gert', 'Beispielweg', '3', '33215', 'Beispielstadt' );

Einfügen von 14 weiteren Artikeln:
INSERT INTO artikelstamm VALUES ( 2, 'Schrauben Set - 30 St.', 3.99 );
INSERT INTO artikelstamm VALUES ( 3, 'Schrauben Set - 40 St.', 4.99 );
INSERT INTO artikelstamm VALUES ( 4, 'Werkzeug Sammlung Profi', 55.99 );
INSERT INTO artikelstamm VALUES ( 5, 'Spezial Klebeband 50m Breite: 10cm', 5.99 );
INSERT INTO artikelstamm VALUES ( 6, 'Rechter Winkel - Aluminium', 1.99 );
INSERT INTO artikelstamm VALUES ( 7, 'Rechter Winkel - Stahl', 1.99 );
INSERT INTO artikelstamm VALUES ( 8, 'Schraubenzieher, Kreuz', 2.99 );
INSERT INTO artikelstamm VALUES ( 9, 'Holz-Schrauben Box, 150 St.', 2.99 );
INSERT INTO artikelstamm VALUES ( 10, 'Hammer mit Holzstil', 3.99 );
INSERT INTO artikelstamm VALUES ( 11, 'Regalsystem', 113.99 );
INSERT INTO artikelstamm VALUES ( 12, 'Schreibtisch aus Glas', 224.99 );
INSERT INTO artikelstamm VALUES ( 13, 'Gartenstuhl, Plastik, wetterfest', 18.99 );
INSERT INTO artikelstamm VALUES ( 14, 'Gartentisch, Plastik, wetterfest', 49.99 );
INSERT INTO artikelstamm VALUES ( 15, 'Gartentisch mit Schirm, Plastik, wetterfest', 89.99 );

Einfügen von 19 weiteren Bestellungen:

INSERT INTO bestellungen VALUES ( 2, 2, '2015-01-05' );
INSERT INTO bestellungen VALUES ( 3, 3, '2014-12-06' );
INSERT INTO bestellungen VALUES ( 4, 4, '2015-01-02' );
INSERT INTO bestellungen VALUES ( 5, 5, '2015-01-02' );
INSERT INTO bestellungen VALUES ( 6, 6, '2014-11-02' );
INSERT INTO bestellungen VALUES ( 7, 7, '2014-11-05' );
INSERT INTO bestellungen VALUES ( 8, 9, '2014-11-02' );
INSERT INTO bestellungen VALUES ( 9, 9, '2014-11-05' );
INSERT INTO bestellungen VALUES ( 10, 10, '2014-12-07' );
INSERT INTO bestellungen VALUES ( 11, 2, '2014-12-06' );
INSERT INTO bestellungen VALUES ( 12, 2, '2014-12-05' );
INSERT INTO bestellungen VALUES ( 13, 3, '2014-10-15' );
INSERT INTO bestellungen VALUES ( 14, 4, '2014-10-13' );
INSERT INTO bestellungen VALUES ( 15, 5, '2014-10-05' );
INSERT INTO bestellungen VALUES ( 16, 6, '2014-09-12' );
INSERT INTO bestellungen VALUES ( 17, 7, '2014-09-06' );
INSERT INTO bestellungen VALUES ( 18, 9, '2014-09-05' );
INSERT INTO bestellungen VALUES ( 19, 1, '2014-09-05' );
INSERT INTO bestellungen VALUES ( 20, 10, '2014-09-22' );

Einfügen von 49 weiteren Bestell-Positionen:

INSERT INTO b_positionen VALUES ( 2, 1, 1, 3 );
INSERT INTO b_positionen VALUES ( 3, 1, 2, 4 );
INSERT INTO b_positionen VALUES ( 4, 2, 3, 5 );
INSERT INTO b_positionen VALUES ( 5, 2, 4, 6 );
INSERT INTO b_positionen VALUES ( 6, 3, 5, 7 );
INSERT INTO b_positionen VALUES ( 7, 3, 6, 1 );
INSERT INTO b_positionen VALUES ( 8, 3, 7, 2 );
INSERT INTO b_positionen VALUES ( 9, 4, 8, 3 );
INSERT INTO b_positionen VALUES ( 10, 5, 10, 4 );
INSERT INTO b_positionen VALUES ( 11, 5, 13, 5 );
INSERT INTO b_positionen VALUES ( 12, 6, 10, 6 );
INSERT INTO b_positionen VALUES ( 13, 6, 12, 7 );
INSERT INTO b_positionen VALUES ( 14, 6, 13, 1 );
INSERT INTO b_positionen VALUES ( 15, 6, 14, 2 );
INSERT INTO b_positionen VALUES ( 16, 7, 15, 3 );
INSERT INTO b_positionen VALUES ( 17, 8, 1, 4 );
INSERT INTO b_positionen VALUES ( 18, 9, 2, 5 );
INSERT INTO b_positionen VALUES ( 19, 10, 3, 6 );
INSERT INTO b_positionen VALUES ( 20, 10, 4, 1 );
INSERT INTO b_positionen VALUES ( 21, 10, 5, 2 );
INSERT INTO b_positionen VALUES ( 22, 11, 6, 3 );
INSERT INTO b_positionen VALUES ( 23, 12, 7, 3 );
INSERT INTO b_positionen VALUES ( 24, 12, 1, 3 );
INSERT INTO b_positionen VALUES ( 25, 12, 2, 4 );
INSERT INTO b_positionen VALUES ( 26, 13, 1, 4 );
INSERT INTO b_positionen VALUES ( 27, 13, 2, 4 );
INSERT INTO b_positionen VALUES ( 28, 14, 4, 1 );
INSERT INTO b_positionen VALUES ( 29, 14, 3, 2 );
INSERT INTO b_positionen VALUES ( 30, 15, 10, 3 );
INSERT INTO b_positionen VALUES ( 31, 15, 13, 4 );
INSERT INTO b_positionen VALUES ( 32, 15, 14, 1 );
INSERT INTO b_positionen VALUES ( 33, 15, 15, 2 );
INSERT INTO b_positionen VALUES ( 34, 16, 10, 3 );
INSERT INTO b_positionen VALUES ( 35, 17, 1, 4 );
INSERT INTO b_positionen VALUES ( 36, 18, 1, 5 );
INSERT INTO b_positionen VALUES ( 37, 19, 13, 3 );
INSERT INTO b_positionen VALUES ( 38, 19, 1, 2 );
INSERT INTO b_positionen VALUES ( 39, 19, 2, 2 );
INSERT INTO b_positionen VALUES ( 40, 19, 3, 2 );
INSERT INTO b_positionen VALUES ( 41, 19, 4, 1 );
INSERT INTO b_positionen VALUES ( 42, 20, 3, 1 );
INSERT INTO b_positionen VALUES ( 43, 20, 4, 1 );
INSERT INTO b_positionen VALUES ( 44, 20, 6, 1 );
INSERT INTO b_positionen VALUES ( 45, 1, 3, 2 );
INSERT INTO b_positionen VALUES ( 46, 2, 15, 1 );
INSERT INTO b_positionen VALUES ( 47, 3, 15, 1 );
INSERT INTO b_positionen VALUES ( 48, 4, 15, 1 );
INSERT INTO b_positionen VALUES ( 49, 5, 15, 1 );
INSERT INTO b_positionen VALUES ( 50, 6, 15, 1 );

Insgesamt haben wir nun folgende Übungsdaten in unserer Datenbank:
10 Kunden
15 Artikel
20 Bestellungen mit insgesamt 50 Bestell-Positionen

Komplexere SELECT Statements - DML

Hinweis: Für die folgenden SELECT Statements der nächsten Kapitel verwenden wir immer unsere Übungsdatenbankstruktur.

Befehl	Erläuterung / Was wird selektiert?
SELECT * FROM kunden;	Alle Datensätze und Spalten (*) der Tabelle „kunden“
SELECT name FROM kunden;	Die Spalte „name“ der Tabelle „kunden“
SELECT DISTINCT ort FROM kunden;	Die Spalte „ort” der Tabelle „kunden”, jedoch ohne Duplikate
SELECT name FROM kunden ORDER BY name ASC;	Die Spalte „name“ der Tabelle „kunden“ aufsteigend sortiert
SELECT name FROM kunden ORDER BY name DESC;	Die Spalte „name“ der Tabelle „kunden“ absteigend sortiert
SELECT * FROM kunden WHERE kid = 1;	Selektiert alle Spalten des Datensatzes der Tabelle „kunden”, welcher in der Spalte “kid” den Wert 1 besitzt.
SELECT * FROM kunden WHERE ktyp = ‘g’;	Alle Datensätze und alle Spalten (*) der Tabelle „kunden”, welche in der Spalte ktyp den Wert ‘g’ besitzen
SELECT * FROM kunden WHERE ktyp = ‘g’ ORDER BY name ASC;	Alle Datensätze und alle Spalten (*) der Tabelle „kunden”, welche in der Spalte ktyp den Wert ‘g’ besitzen, aufsteigend sortiert nach der Spalte „name“ Ab jetzt wird fachlich beschrieben, was die Suchabfragen erzeugen bzw. filtern…
SELECT * FROM kunden WHERE name LIKE ‘m%’;	Alle Kunden, deren Name mit einem „m“ beginnen
SELECT * FROM kunden WHERE name LIKE ‘m_s%’;	Alle Kunden, deren Name mit einem „m“ beginnen sowie an der dritten Stelle ein „s“ haben
SELECT * FROM kunden WHERE ort LIKE '[bm]%';	Alle Kunden, deren Ort mit “b” oder “m” beginnen (Die Angabe von Charlists […] funktionieren bei einigen MySQL Datenbanken leider nicht. Hier muss ggf. die Funktion REGEXP verwendet werden.)
SELECT * FROM kunden WHERE name LIKE '[a-m]%';	Alle Kunden, deren Namen mit “a” bis “m” beginnen (Die Angabe von Charlists […] funktionieren bei einigen MySQL Datenbanken leider nicht. Hier muss ggf. die Funktion REGEXP verwendet werden.)
SELECT * FROM kunden WHERE name LIKE ‘m%’ AND ktyp=’g’;	Alle Kunden, die im Kundentyp ein “g” stehen haben UND deren Name mit “m” beginnt
SELECT * FROM kunden WHERE name LIKE ‘m%’ OR name LIKE ‘b%’;	Alle Kunden, deren Name mit “m” ODER deren Name mit “b” beginnt
SELECT * FROM kunden WHERE ort IN ('Musterhausen','Beispielort');	Alle Kunden, deren Ort entweder “Musterhausen” oder “Beispielort” beinhaltet
SELECT * FROM artikelstamm WHERE a_preis BETWEEN 5 AND 20;	Alle Preise aus den Artikeln, deren Preis ZWISCHEN 5 und 20 liegen
SELECT * FROM artikelstamm WHERE a_preis NOT BETWEEN 5 AND 20;	Alle Preise aus den Artikeln, deren Preis NICHT ZWISCHEN 5 und 20 liegen
SELECT firma AS Firmenbezeichnung FROM kunden;	Alias Namen erzeugen, die Spalte „firma“ wird als „Firmenbezeichnung“ im Abfrageergebnis angezeigt
SELECT firma, CONCAT(strasse,' ',hausnr,', ',plz,' ',ort) AS Anschrift FROM kunden;	Alias Namen mit zusammengesetzten Zeichenketten erzeugen mittels Funktion (CONCAT).

Wichtige Hinweise: Je nach Einstellungen der Datenbank sind ggf. die LIKE und Wildcart-Vergleiche Case-Sensitive. Bedeutet ein kleines „m“ ist ungleich einem großen „M“. Die Hochkommata können je nach Zeichensatz von MySQL nicht anerkannt werden. Alternativ können auch die Anführungszeichen “ (Shift+2) verwendet werden oder das einzelne Hochkommata auf der # Taste der Tastatur (Shift+#).

Übersicht der Operatoren der WHERE Klausel

Operator	Erläuterung
=	Gleich / Identischer Inhalt
<>	Ungleich
>	Größer als…
>=	Größer oder gleich…
<	Kleiner als…
<=	Kleiner oder gleich…
BETWEEN	Zwischen (Bereich, Intervallgrenzen)
LIKE	Such-Zeichenkette inkl. „Wildcarts“: % (mehrere Zeichen), _ (ein einzelnes Zeichen) und [] (Char-Liste)
IN	Mehrfache Attributinhalte (Liste von gültigen Werten)

SQL Aggregats- und Skalarfunktionen - DML

SQL Aggregatsfunktionen

Funktion	Erläuterung
GROUP BY …	Aggregatsfunktionen benötigen oft eine Gruppierung nach bestimmten Spalten. Dafür wird dieser Befehl verwendet.
HAVING …	Die WHERE Klausel wird bei GROUP BY Statements durch HAVING ersetzt um sozusagen eine WHERE Klausel auf ein gruppiertes Attribut zu setzen.
AVG()	Funktion: Ermittelt den Durchschnitt
COUNT()	Funktion: Ermittelt die Anzahl
FIRST()	Funktion: Ermittelt den ersten Datensatz der Tabelle – Workaround MySQL: LIMIT 1; am Ende einsetzen
LAST()	Funktion: Ermittelt den letzten Datensatz der Tabelle
MAX()	Funktion: Ermittelt den maximalen Wert
MIN()	Funktion: Ermittelt den minimalen Wert
SUM()	Funktion: Ermittelt die Summe

SQL Skalar-Funktionen

Funktion	Erläuterung
UCASE()	Funktion: Wandelt alle Zeichen in Großbuchstaben um
LCASE()	Funktion: Wandelt alle Zeichen in Kleinbuchstaben um
MID()	Funktion: Ermittelt aus Zeichenketten eine bestimmte Anzahl von Zeichen ab einer bestimmten Position.
	Syntax: SELECT MID ( Spaltenname, Startposition, Anzahlzeichen ) AS Aliasname FROM Tabellenname;
LEN()	Funktion: Gibt die Länge einer Zeichenkette zurück
ROUND()	Funktion: Rundet numerische Werte auf eine gewünschte Anzahl von Dezimalstellen.
	Syntax: SELECT ROUND ( Spaltenname, Dezimalstellen ) FROM Tabellenname;
NOW()	Funktion: Gibt die aktuelle Systemzeit inkl. Datum zurück
DATE_FORMAT()	Funktion: Formatiert die Ausgabe eines Datums-Feldes

Beispiele für die Verwendung

Befehl	Erläuterung
SELECT MID(ort,1,4) AS kurzort FROM kunden;	Liefert die ersten 4 Zeichen der Spalte „ort“.
SELECT UCASE(name) AS namegross, ort FROM kunden;	Liefert die “namen” als Großbuchstaben und die Spalte “ort”. Dabei bekommt die Spalte “name” einen Aliasnamen “namegross”.
SELECT a_bez, ROUND(a_preis,0) AS gerundeterpreis FROM artikelstamm;	Liefert den Preis der Artikel auf ganze Zahlen gerundet, sprich keine Dezimalstellen.
SELECT a_bez, a_preis, NOW() AS tagesdatum FROM artikelstamm;	Liefert eine Preisliste mit aktuellem Tagesdatum und aktueller Systemzeit.
SELECT a_bez, a_preis, DATE_FORMAT(NOW(),'%d.%m.%Y') AS tagesdatum FROM artikelstamm;	Liefert eine Preisliste mit aktuellem Tagesdatum formatiert nach der Regel DD.MM.YYYY.
SELECT AVG(a_preis) AS durchschnitt FROM artikelstamm;	Liefert den absoluten Durchschnittspreis aller Artikel.
SELECT a_bez, a_preis FROM artikelstamm WHERE a_preis > ( SELECT AVG(a_preis) FROM artikelstamm );	Liefert alle Artikel, deren Preis höher als der absolute Durchschnittspreis aller Artikel ist.

Beispiele für die Verwendung mit GROUP BY und HAVING

Befehl	Was wird selektiert?
SELECT ort, COUNT(kid) AS anzahlkundenausort FROM kunden GROUP BY ort;	Ermittelt die Kundenanzahl aus allen Orten. Wichtiger Hinweis: Die GROUP BY Attribute müssen Teil der Aggregatsfunktion sein! (Kurz gesagt: Die GROUP BY Felder müssen im SELECT Teil enthalten sein.)
SELECT ort, COUNT(kid) AS anzahlkundenausort FROM kunden GROUP BY ort HAVING ort LIKE ‘m%’;	Ermittelt die Kundenanzahl aller Orte, welche mit einem “m” beginnen. HAVING ist sozusagen die WHERE Klausel für die GROUP BY Attribute.
SELECT ort, COUNT(kid) AS anzahlkundenausort FROM kunden WHERE ktyp = ‘g’ GROUP BY ort HAVING ort LIKE ‘m%’;	Ermittelt die Kundenanzahl aller Orte, welche mit einem “m” beginnen. Zusätzlich muss es sich um einen Geschäftskunden (ktyp=“g“) handeln. Hier wird WHERE und HAVING verwendet!

JOINS – Mehrtabellenabfragen - DML

Eine Verknüpfung (JOIN) ist eine Verbindung von Tabellen, die das Abfrageergebnis (DYNASET) bestimmt. Ein JOIN ist somit nicht identisch mit einer Beziehung!

Es gibt vier Arten von JOINS:
1) INNER JOIN
2) LEFT JOIN
3) RIGHT JOIN
4) FULL OUTER JOIN

Betrachten wir die JOIN-Arten etwas genauer.

INNER JOIN

Der INNER JOIN gibt alle Datensätze von zwei oder mehreren Tabellen zurück, bei denen die Bedingungsspalten übereinstimmen.

Um dieses Mengenprinzip der JOINS zu verstehen, möchte ich ein ganz einfaches Beispiel verwenden.
Nehmen wir zwei Tabellen, von denen die erste Tabelle eine Mitarbeiterliste einer kleinen Abteilung darstellt und die zweite Tabelle alle Seminarteilnehmer des SQL Workshops enthält.

Schauen wir uns ein INNER JOIN Statement an:

SELECT * FROM mitarbeiter
INNER JOIN seminar
ON mitarbeiter.name = seminar.name;

Das Ergebnis (Dynaset) dieser Abfrage lautet:

Es wird eine Schnittmenge gebildet.
Max Mustermann hat die Nummer (ID) 2 in der Tabelle 1
und die Nummer (ID2) 1 in der Tabelle 2.

Eine abgekürzte Schreibweise kann mit dem NATURAL JOIN vorgenommen werden. Hier werden alle Spalten, die den gleichen Namen haben, automatisch in Beziehung gesetzt:
SELECT * FROM mitarbeiter NATURAL JOIN seminar;

Alternativ kann man statt ON auch USING verwenden, wenn die Beziehungsspalte in beiden Tabellen gleich benannt ist:
SELECT * FROM mitarbeiter INNER JOIN seminar USING (name);

LEFT JOIN

Beim LEFT JOIN wird die zuerst angegebene Tabelle vollständig im Dynaset aufgenommen und ergänzt anschließend die passenden Datensätze aus der Tabelle 2.

Nehmen wir wieder ein konkretes einfaches Beispiel.

Schauen wir uns ein LEFT JOIN Statement an:

SELECT * FROM mitarbeiter
LEFT JOIN seminar
ON mitarbeiter.name = seminar.name;

Das Ergebnis (Dynaset) dieser Abfrage lautet:

RIGHT JOIN

Beim RIGHT JOIN verhält es sich genau umgekehrt zum LEFT JOIN. Der RIGHT JOIN nimmt die zweite (rechte) angegebene Tabelle vollständig im Dynaset auf und ergänzt die passenden Datensätze aus der Tabelle 1.

FULL OUTER JOIN

Beim FULL OUTER JOIN werden theoretisch alle Kombinationen der Verbindung aufgelistet. Vereinfacht gesagt handelt es sich um eine Kombination des LEFT JOIN mit dem RIGHT JOIN.

Für ein Beispiel verändern wir die Seminartabelle etwas um die Arbeitsweise besser erkennen zu können.

Schauen wir uns ein entsprechendes Statement an:

SELECT * FROM mitarbeiter
FULL OUTER JOIN seminar
ON mitarbeiter.name = seminar.name;

Das Ergebnis (Dynaset) dieser Abfrage lautet:

Das Ergebnis sieht in der Tat so aus, als hätte man den LEFT JOIN und den RIGHT JOIN nacheinander ausgeführt und die Duplikate entfernt.

UNIONS – Zusammenführung von mehreren Tabellen - DML

Neben den JOINS gibt es die UNION Befehle um Dynasets miteinander zu kombinieren.

Ein abstraktes Beispiel verdeutlicht den UNION Befehl.

Schauen wir uns ein entsprechendes Statement an:

SELECT name FROM abteilung_a
UNION
SELECT name FROM abteilung_b;

Hinweis: Die zu kombinierenden Dynasets müssen immer die gleiche Spaltenanzahl haben! Zusätzlich müssen die zu kombinierenden Spalten auch identische Datentypen haben, da es sonst nicht zusammen passen würde!

Das Ergebnis (Dynaset) dieser Abfrage lautet:

In unserem Beispiel gibt es Max Mustermann jedoch in beiden Abteilungen. Der UNION Befehl erzeugt keine Duplikate. Verwendet man den UNION ALL Befehl, werden auch Duplikate angezeigt:

SELECT name FROM abteilung_a
UNION ALL
SELECT name FROM abteilung_b;

Ergebnis:

Übungen zu SELECT Statements mit unserer Übungsdatenbank - DML

Befehl	Aufgabenstellung / Was soll ermittelt werden?
SELECT ort, COUNT(ort) AS anzahlkunden FROM kunden GROUP BY ort HAVING COUNT(ort) >= 2;	Anzahl Kunden aus demselben Ort, wird erst ab mindestens 2 Kunden im Ort angezeigt
SELECT k.firma, k.name, COUNT(k.kid) AS anzahlbestellung FROM kunden AS k INNER JOIN bestellungen AS b ON k.kid = b.kunden_id GROUP BY k.firma, k.name ORDER BY k.firma ASC, k.name ASC;	Alle Kunden (Firma, Name) und deren Anzahl von Bestellungen, aufsteigend sortiert nach Firma und Name
SELECT k.firma, SUM(bp.menge) AS anzahlartikel FROM kunden AS k INNER JOIN bestellungen AS b ON k.kid = b.kunden_id INNER JOIN b_positionen AS bp ON bp.bestell_id = b.bid WHERE k.ktyp=“G“ GROUP BY k.firma ORDER BY anzahlartikel DESC;	Anzahl der bestellten Artikel aller Bestellungen pro Geschäftskunde (ktyp = „g“), absteigend nach der Anzahl sortiert
SELECT SUM(a.a_preis * bp.menge) AS umsatz FROM b_positionen AS bp INNER JOIN artikelstamm AS a ON bp.artikel_id = a.aid;	Der Gesamtumsatz aller Kunden
SELECT k.ktyp, SUM(a.a_preis * bp.menge) AS umsatz FROM kunden AS k INNER JOIN bestellungen AS b ON k.kid = b.kunden_id INNER JOIN b_positionen AS bp ON b.bid = bp.bestell_id INNER JOIN artikelstamm AS a ON bp.artikel_id = a.aid GROUP BY k.ktyp;	Der Gesamtumsatz aller Geschäfts- und Privatkunden
SELECT k.firma, SUM(a.a_preis * bp.menge) AS umsatz FROM kunden AS k INNER JOIN bestellungen AS b ON k.kid = b.kunden_id INNER JOIN b_positionen AS bp ON b.bid = bp.bestell_id INNER JOIN artikelstamm AS a ON bp.artikel_id = a.aid WHERE k.ktyp = "g" GROUP BY k.firma HAVING umsatz > 100;	Der Gesamtumsatz aller Geschäftskunden ab einem Umsatz größer 100
UPDATE kunden SET firma = NULL WHERE firma = ""; SELECT * FROM kunden WHERE firma IS NULL;	1) Zuerst eine kleine UPDATE Anweisung: Alle Attribute „firma“, deren Inhalt ‘‘ ist, sollen mit NULL gefüllt werden 2) Alle Einträge der Kundentabelle, wo „firma“ NULL ist, auflisten
SELECT bestell_id, SUM(a_preis * menge) AS bestellsumme FROM b_positionen AS b INNER JOIN artikelstamm AS a ON b.artikel_id = a.aid GROUP BY bestell_id ORDER BY bestellsumme DESC LIMIT 5;	Ermitteln der TOP 5 aller Bestellungen und deren Gesamtbetrag absteigend nach Gesamtbetrag der Bestellung
SELECT bst.bestellnummer, MAX(bestellsumme) FROM ( SELECT bp.bestell_id AS bestellnummer, SUM(a.a_preis * bp.menge) AS bestellsumme FROM b_positionen AS bp INNER JOIN artikelstamm AS a ON bp.artikel_id = a.aid GROUP BY bp.bestell_id ) AS bst;	Ermitteln der höchsten Bestellung (Verschachteltes SELECT)
SELECT k.vorname, k.name, SUM(a.a_preis * bp.menge) AS umsatz FROM kunden AS k INNER JOIN bestellungen AS b ON k.kid = b.kunden_id INNER JOIN b_positionen AS bp ON b.bid = bp.bestell_id INNER JOIN artikelstamm AS a ON bp.artikel_id = a.aid WHERE k.ktyp="p" GROUP BY k.vorname, k.name HAVING umsatz >= 200;	Alle Privatkunden, die gleich oder mehr als 200 im Endpreis bestellt haben

Eigene Datenbankbenutzer anlegen - DDL

Befehl	Was bewirkt der Befehl?
CREATE USER usr_sqluebung@localhost IDENTIFIED BY ‘MeinGeheimesPasswort‘;	Erzeugt einen neuen Benutzer „usr_sqluebung“ auf dem Rechner / Server „localhost“ und setzt dessen Passwort auf „MeinGeheimesPasswort“.
DROP USER usr_sqluebung@localhost;	Entfernt den Benutzer „usr_sqluebung“ auf dem Rechner / Server „localhost“.

Data Control Language – DCL

Die Data Control Language wurde früher auch als DAL – Data Administration Language – bezeichnet. Mit den DCL Statements können z.B. Datenbankrechte administriert werden.

Befehl	Was bewirkt der Befehl?
GRANT ALL ON db_sqluebung1.* TO usr_sqluebung@localhost;	Gewährt alle Rechte auf unsere Übungsdatenbank db_sqluebung1 für den Benutzer „usr_sqluebung“ auf dem Rechner / Server „localhost“.
GRANT SELECT ON db_sqluebung1.kunden TO usr_sqluebung@localhost;	Gewährt SELECT Rechte auf unsere Übungsdatenbank db_sqluebung1 und dort spezifisch auf die Tabelle kunden für den Benutzer „usr_sqluebung“ auf dem Rechner / Server „localhost“.
REVOKE ALL ON . FROM usr_sqluebung@localhost;	Entfernt ALLE Rechte des Users „usr_sqluebung“ auf dem Rechner / Server „localhost“.

Der Vollständigkeit halber: Die Übungsdatenbank aufräumen - DDL

Damit wir bestehende Referenzen nicht zerstören und evtl. Fehler verursachen, löschen wir die Tabellen inkl. Inhalte in umgekehrter Reihenfolge zur Erstellung. Sozusagen löschen wir „rückwärts“.

DROP TABLE b_positionen;
DROP TABLE bestellungen;
DROP TABLE kunden;

DROP USER usr_sqluebung@localhost; -- Falls er noch vorhanden ist. ---

DROP DATABASE db_sqluebung1;

Download der SQL-Beispiel-Statements

Alle SQL Statements zur Übungsdatenbank (SQLUEBUNG1.sql) finden Sie hier in einer Text-Datei ohne Formatierung für einfaches Copy & Paste zusammengestellt. So sparen Sie sich das "Abtippen".

Download

Dateiformat: Textdatei (.SQL) / Dateigröße: ca. 14 KiloBytes / Autoren: Marc Wershoven und Anika Muschner / Version 1.3 (15.02.2015)

Schlusswort

Ich hoffe, wir konnten Ihnen mit dieser Zusammenfassung und den vielen Beispielen beim Erlernen der Programmiersprache SQL oder als Nachschlagemöglichkeit hilfreich zur Seite stehen.

Wir wünschen Ihnen Viel Erfolg und Spass mit SQL!

Autoren: Marc Wershoven, Anika Muschner (2015)