SQL

Datum: September 2020
Lesedauer: 9 Minuten

Referenzmodul: M105 - Informatiker EFZ Applikationsentwicklung 

Dieser Blogpost zeigt die essenziellen Befehle von SQL.

Überblick

SQL ist eine Query-Language. Es ist also eine Sprache, um strukturierte Daten von einer Datenbank zu manipulieren und abzufragen.
Z. B. HeidiSQL und MySQL Workbench ermöglichen das Schreiben von SQL.

Um das in diesem Blogpost erlernte Wissen zu testen, empfehle ich den Online-Editor:
https://sqliteonline.com/ 

Teilbereiche

SQL wird in fünf Bereiche unterteilt. Hier ein kleiner Überblick:

Praxis

Nun zeige ich, wie man die oben genannten Befehle richtig verwendet.

Ich empfehle, das Erlernte jeweils im Online-Editor (https://sqliteonline.com/ ) zu testen. Dabei ist noch wichtig, dass die Gross-/Kleinschreibung nicht beachtet wird.

So sieht die Online-IDE aus. Links sieht man die Tabellen. Klappt man diese auf, so sieht man die einzelnen Spalten. Rechts stehen nützliche Befehle. Man kann auch auf History klicken, um die letzten Befehle sichtbar zu machen.
Der mittlere Teil ist am wichtigsten. Oben kann man die Befehle schreiben. Im unteren Bereich sieht man die Ausgabe.

Grundlagen

Kommentare

Kommentare kann man auf zwei Arten schreiben.

-- Kommentar über eine Zeile

/*
Kommentar über mehrere Zeilen
*/

Semikolon

Ein Semikolon wird benötigt, wenn die Anweisung fertig ist.

Datenbank verwenden

Damit man in der gewünschten Datenbank arbeiten kann, muss man diese festlegen.

use baseName;

DDL (Data Definition Language)

DDL-Befehle verwendet man dann, wenn man verschiedene Objekte einer Datenbank erstellen oder bearbeiten will.

CREATE

Um etwas zu erstellen, verwendet man CREATE.

DATABASE

CREATE DATABASE baseName;

Beim Erstellen einer Datenbank kann man auch noch den Zeichensatz mitgeben. Dies wird in diesem Beispiel nicht gemacht, um es simpel zu halten. Zudem kann man zuerst Datenbanken mit demselben Namen löschen, um Konflikte zu vermeiden. Dazu kommen wir aber später.

TABLE

Neben Datenbanken können auch Tabellen erstellt werden.

CREATE TABLE kategorie (
    kategorie_id INT PRIMARY KEY AUTO_INCREMENT,
    kategoriename VARCHAR(255)
);

Bei dieser Tabelle werden zwei Attribute hinzugefügt. Eines davon ist ein Primärschlüssel. Dieser ist in Form einer Zahl. Daher wird er als INT deklariert. Dahinter schreibt man noch PRIMARY KEY, um ihn als Primärschlüssel zu verwenden. Zum Schluss schreibt man AUTO_INCREMENT. Damit sagt man, dass die Zahl automatisch erhöht wird.

Beim Erstellen einer Tabelle gibt man jeweils den verlangten Datentyp an. In dieser Tabelle sind die grundlegenden dargestellt:

Dies sind die wichtigsten. Jedoch gibt es noch viel mehr.

ALTER

Mit ALTER kann man die Struktur einer Tabelle ändern.

Einfügen

Es ist möglich, neue Spalten einzufügen.

ALTER TABLE ort ADD landesbezeichnung CHAR(3);

In diesem Beispiel wurde in der Tabelle Ort eine Landesbezeichnung hinzugefügt.

Umbenennen

Nun könnte man auch etwas umbenennen. In diesem Fall ändert man den Namen von Landesbezeichnung auf Bundesland.

ALTER TABLE ort CHANGE landesbezeichnung bundesland char(3);

Default-Werte

Auch Default-Werte können mit ALTER verändert werden.

ALTER TABLE ort ALTER vorwahl SET DEFAULT '00000';

Löschen

Auch das Löschen ist mit ALTER möglich.

ALTER TABLE DROP ort;

DROP

Beim letzten Beispiel hat man DROP bereits verwendet. Mit DROP kann man Verschiedenes löschen:

• Spalten
• Nutzer
• Datenbanken

Beispiel

Oft wird vor dem Erstellen einer Datenbank geprüft, ob schon eine mit diesem Namen existiert. Falls dies der Fall ist, wird sie gelöscht.

DROP DATABASE IF EXISTS dataName;
CREATE DATABASE dataName;

RENAME

In Kombination mit ALTER ist es möglich, Tabellen umzubenennen.

ALTER TABLE oldName
RENAME TO newName;

TRUNCATE

Mit TRUNCATE löscht man Daten aus einer Tabelle, ohne dabei die Tabelle zu löschen.

TRUNCATE TABLE kategorien;

DQL (Data Query Language)

DQL wird verwendet, um die benötigten Daten abzufragen.

SELECT

Mit SELECT sagt man, dass man etwas auswählen will. Danach gibt man an, was ausgewählt werden soll. In diesem Beispiel ist das alles (*). Es ist jedoch auch möglich, nur bestimmte Spalten zu selektieren.

Danach muss man angeben, von welcher Tabelle man die Attribute will.

SELECT * FROM "tabellenName";

WHERE

Es kommt oft vor, dass man nur gewisse Zeilen benötigt, die eine bestimmte Bedingung erfüllen.

SELECT "spaltenName" FROM "tabellenName" WHERE "Bedingung";

Folgende Tabelle zeigt, wie überprüft werden kann.

Platzhalter

Platzhalter werden verwendet, wenn man z. B. alle Vornamen will, die mit einem bestimmten Buchstaben beginnen.

Testet man den Wert mit einem Platzhalter, so muss man LIKE anstelle von = verwenden:

SELECT preis FROM produkt WHERE beschreibung LIKE '%cola%';

Aggregatfunktionen

Diese Funktionen werden verwendet, um bestimmte Felder/Werte zu erhalten.

SELECT MIN(feld) FROM Tabelle; -- der kleinste Wert
SELECT MAX(feld) FROM Tabelle; -- der grösste Wert
SELECT SUM(feld) FROM Tabelle; -- die Summe aller Werte
SELECT AVG(feld) FROM Tabelle; -- der Durchschnitt
SELECT COUNT(feld) FROM Tabelle; -- die Anzahl der Werte

HAVING

Sobald man Aggregatfunktionen verwendet, filtert man nicht mehr mit WHERE, sondern mit HAVING. HAVING ermöglicht es auch, nach dem Gruppieren zu filtern.

ORDER

Mit Order kann man die Datensätze sortieren.

SELECT "spaltenName" FROM "tabellenName" ORDER BY "spaltenName";

Standardmässig wird aufsteigend (ASC) sortiert. Jedoch kann man die Sortierung mit DESC umkehren.

ORDER BY "spalte1" [ASC, DESC], "spalte2" [ASC, DESC]

GROUP

GROUP BY wird verwendet, um zu gruppieren. Oft macht man das unter Verwendung der Aggregatfunktionen.

SELECT COUNT(customerID), country
FROM customers
GROUP BY country;

AS

Mit AS kann man den Namen einer Tabelle oder Spalte für die Ausgabe ändern. Der Name wird also nicht überschrieben, sondern für die Ausgabe geändert.

SELECT marktwert as preis FROM players;

In diesem Beispiel wird die Spalte “Marktwert” unter dem Namen “Preis” ausgegeben.
Es gibt noch weitere Möglichkeiten zu selektieren. Auf die, meiner Meinung nach, fortgeschritteneren Dinge wie DISTINCT oder UNION gehe ich jetzt aber nicht ein.

DML (Data Manipulation Language)

Um die Daten einer Datenbank zu manipulieren, muss man sich der DML-Befehle bedienen.

INSERT

Mit einem INSERT kann man Daten in eine Tabelle einfügen.

CREATE table tableName (
	vorname varchar(30),
	nachname varchar(30)
);
INSERT tableName VALUES('john', 'doe');

Man muss die Daten einer Tabelle nicht zwingend manuell einfügen, wenn man sie schon in einer anderen Tabelle hat.

CREATE TABLE person AS
SELECT vorname, nachname
FROM customers;

Es gibt zwei Methoden, um Datensätze in eine Tabelle einzufügen:

Standard-Insert

Bei dieser Variante schreibt man auf jeder Zeile einen INSERT-Befehl. Dies erfordert mehr Zeit, kann aber übersichtlicher sein.

INSERT INTO customers (customerName, city, country) VALUES ('A', 'Tokio', 'Japan');
INSERT INTO customers (customerName, city, country) VALUES ('B', 'Hongkong', 'China');

Bulk-Insert

Ich bevorzuge den Bulk-Insert, da er schneller geht. Man braucht lediglich ein INSERT-Statement zu schreiben. Danach kann man mehrere Datensätze einfügen.

INSERT INTO 'adressen' ('id', 'name', 'fk_plz')
VALUES (1, 'urs', 9000), (2, 'hans', 9000), (3, 'anna', NULL), (4, 'Kurt', 9035);

UPDATE

Mit UPDATE können bestimmte Werte verändert werden.

UPDATE customers
SET contactName = 'Alfred Schmidt', City = 'Frankfurt' WHERE customerID = 1;

DELETE

Mit DELETE kann ich bestimmte Datensätze löschen. Wie bei einer SELECT-Abfrage kann ich mit WHERE gewisse Daten herausfiltern.

DELETE from klasse WHERE bezeichnung = INA1a;

Manueller Datenimport

Wenn ich die Daten schon in einem File habe, muss ich nicht alle Datensätze von Hand in eine Tabelle eintragen. Ich muss nur das File laden.

LOAD DATA INFILE 'abc.csv' INTO TABLE abc

DCL (Data Control Language)

Will man Rechte vergeben, benötigt man diese Befehle.

Um Rechte zu erteilen, muss man Benutzer anlegen.

CREATE User mustermann identified BY "1234";

Ein Nutzer kann auch wieder gelöscht werden.

DROP User muster;

Um die Users anzuzeigen, schreibt man folgende SELECT-Abfrage.

SELECT * from mysql.user;

Hat man Benutzer erstellt, so kann man auch Benutzerrollen erstellen.

Tipp: Man kann auch Gruppen erstellen und diesen die benötigten Rechte verleihen.

GRANT

Mit GRANT kann man Rechte verteilen.

CREATE USER urs@192.168.13.149 IDENTIFIED BY '1234'; -- User mit Passwort 1234 wird erstellt
GRANT SELECT, UPDATE ON artikel.artikel TO urs@192.168.13.149; -- User darf in der Tabelle Artikel SELECT und UPDATE Befehle ausführen

REVOKE

Mit REVOKE kann man Rechte entziehen.

REVOKE SELECT ON artikel.artikel FROM urs@'%'; -- Urs darf keine SELECT-Abfragen ausführen
REVOKE ALL PRIVILEGES ON artikel.artikel FROM urs@'%'; -- Urs hat nun keinerlei Rechte

TCL (Transaction Control Language)

Mit TCL-Befehlen kann man sichere Änderungen einer Datenbank durchführen. Dies ist z. B. wichtig auf einer Bank. Das Geld soll immer komplett oder gar nicht überwiesen werden. Mit TCL-Befehlen kann man solche Änderungen sicher durchführen.

Ich gehe hier nicht auf die einzelnen Befehle ein, da dies den Rahmen sprengen würde. Unter diesem Link findet man aber eine gute Beschreibung:
https://www.studytonight.com/dbms/tcl-command.php#:~:text=Commit%2C%20Rollback%20and%20Savepoint%20SQL,grouped%20together%20into%20logical%20transactions .

Hilfe!

Die Reihenfolge der einzelnen Befehle muss stimmen. Ansonsten kommt es zu Fehlermeldungen. Diese Grafik kann dabei helfen.

Ich hoffe, dieser Blogpost hat geholfen. Es ist klar, dass man nicht alles versteht/weiss, wenn man ihn das erste Mal durchliest. Um SQL wirklich auch zu können, muss man üben.

Ich empfehle dazu zwei Quellen:

• SQL spielerisch erlernen: https://sql-island.informatik.uni-kl.de/ 
• SQL ausprobieren: https://sqliteonline.com/