SQL

S

SQL Bedeutung

T

Tabellen erstellen, bearbeiten, löschen

D

Daten einfügen, bearbeiten und löschen

S

SELECT

A

Aufgaben zu SQL

SQL Bedeutung

SQL ist eine Programmiersprache zum Abfragen und manipulieren von Daten. Es ist die Abkürzung für "structured query language". Die Befehle werden in folgende 4 Bereiche eingeteilt:

(DQL) zur Abfrage und Aufbereitung der gesuchten Informationen (z.B. SELECT)
(DML) zur Datenmanipulation (Ändern, Einfügen, Löschen) und lesendem Zugriff (z.B. UPDATE)
(DDL) zur Definition des Datenbankschemas (z.B. CREATE TABLE)
(DCL) für die Rechteverwaltung und Transaktionskontrolle

Ein wichtiger Bestandteil sind die Schlüssel (Primary key, Foreign key), welche eine eindeutige Zuordnung möglich machen, um Daten miteinander zu verbinden und gezielt anzusteuern (z.B. für UPDATE).
Redundanz beschreibt die Existenz von überflüssigen / doppelten Informationen und ist nicht erwünscht.
Die referentielle Integrität (Beziehungsintegrität) beschreibt die Bedingung, dass Informationen miteinander verbunden sind. So muss es für einen Fremdschlüssel den jeweiligen Primärschlüssel geben.

Bemerkung: In den folgenden Artikeln nutze ich den Syntax, den wir in der Schule lernen und weder eindeutig Oracle, MySQL oder SQL-Server zuzuordnen ist und schreibe alle Keywords groß auch wenn dies nicht zwingend erforderlich ist.

Tabellen erstellen, bearbeiten, löschen

Erklärung

Mit CREATE DATABASE name wird eine neue Datenbank erstellt. Jetzt können Tabellen angelegt werden. Dazu nutzt man:

CREATE TABLE table_name (
  column_name1 data_type(size) optional_property,
  column_name2 data_type(size) optional_property,
  ...
 )

Die wichtigsten Datentypen

int kurz für Intiger. Das sind ganzzahlige Werte von -2 Mrd. bis 2Mrd.
decimal(x,n) speichert Dezimalzahlen. x: all digits; n: all digits after the decimal point
varchar(X) kurz für variable characters und halt X Buchstaben/Zahlen/Symbole...
text für lange Zeichenketten also Text
datetime speichert ein Datum in diesem Format: YYYY-MM-DD hh:mm:ss

Eigenschaften

NOT NULL das Feld darf nicht leer bleiben
PRIMARY KEY kombiniert UNIQUE und NOT NULL und darf nur einmal in einer Tabelle vorkommen
FOREIGN KEY verhindert das Eingeben von Werten, die nicht in der anderen Tabelle in der Primärschlüsselspalte stehen
DEFAULT wenn kein Wert festgelegt wird, wird dieser Wert eingefügt anstatt NULL
CHECK der Wert in der Zelle muss diese Bedinung erfüllen
AUTO_INCREMENT erzeugt automatisch eine einzigartige Zahl
UNIQUE die Werte dürfen nur einmal in der Spalte vorkommen
COMMENT ein Kommentar zur Spalte, der auch in der Datenbank gespeichert wird.

Wenn die Tabellenstruktur bereits erstellt wurde, kann ALTER TABLE name genutzt werden, um einzelne Spalten zu ändern, hinzufügen, oder zu löschen:

ALTER TABLE table_name
 ADD column_nameX data_type(size) optional_property,
 MODIFY column_name data_type(size) optional_property,
 DROP column_name,
 ADD CONSTRAINT name property,
 ADD property;

Wird eine Tabelle und ihr ganzer Inhalt nicht mehr gebraucht, kann die Tabelle mit DROP TABLE name gelöcht werden. Achtung das ist sowie jeder Befehl in SQL nicht rückgängig machbar!

Beispiele

CREATE TABLE users (
 id int(11) PRIMARY KEY AUTO_INCREMENT,
 forename varchar(50) NOT NULL,
 sirname varchar(50),
 gender varchar(6) CHECK (gender='female' OR gender='male' OR gender='diverse'),
 usename varchar(10) NOT NULL UNIQUE,
 unimportant decimal(6,2) COMMENT 'this column will be removed',
 getMails bit DEFAULT false /* can be 0 or 1 as an equivalent for true and false */
);

Mit dem Befehl wird eine Tabelle mit dem Namen 'users' und folgenden Feldern erstellt:

id: ist der Primärschlüssel und wird automatisch hochgezählt
forename: muss ausgefüllt werden und kann 50 Zeichen halten
sirname: wenn nicht ausgefüllt, ist es NULL sonst kann es 50 Zeichen halten
gender: lässt nur "female", "male" oder "diverse" zu und ist auf 6 Zeichen beschränkt
usename: muss ausgefüllt werden und kann 10 Zeichen fassen, muss sich von den anderen Werten in der Spalte unterscheiden
unimportant: kann bis zu 9.999,99 speichern und wurde mit "this column will be removed" kommentiert
getmail: kann nur 0 oder 1 speichern, wird nichts eingetragen, ist der Wert automatisch 0

Vielleicht wurde schon bemerkt, dass ich mich bei einer Spalte verschrieben habe und auch sonst einiges hinzugefügt, bzw. geändert oder gelöscht werden sollte.

ALTER TABLE users
 MODIFY sirname varchar(50) NOT NULL,
 CHANGE usename username varchar(40),
 DROP unimportant;

Der Spalte 'sirname' wurde 'NOT NULL' hinzugefügt, 'usename' zu 'username' umbenannt, der Datentyp von 'username' auf 'varchar(40)' geändert und die Spalte 'unimportant' gelöscht.

DROP TABLE users
weiter zu: Daten

Daten einfügen, bearbeiten und löschen

Erklärung

Nachdem im letzten Artikel das Gerüst also die Tabellenstruktur erstellt wurde, können jetzt Daten eingefügt werden.

INSERT INTO name (cloumn1, column2,...) VALUES
 (value1_1, value2_1, ...),
 (value1_2, value2_2, ...);

Wenn in alle Spalten ein Wert eingefügt wird kann die Klammer in der ersten Zeile weggelassen werden. Daten werden geändert mit:

UPDATE name
  SET column1 = new_value1, column2 = new_value2, ...
  WHERE condition;

Wenn keine Bedingung gesetzt wird, werden alle Reihen geändert. Üblicherweise ist die Bedingung der Primärschlüssel, um nur eine Zeile zu bearbeiten. Daten werden gelöscht:

DELETE FROM name WHERE condition;

Beispiel

INSERT INTO users (forename, sirname, gender, username) VALUES
  ('Max', 'Mustermann', 'male', 'Maxi21'),
  ('Jane', 'Doe', 'female', 'JaneDoe'),
  ('Marie', 'Curie', 'female', 'radioactiv');

UPDATE users SET username='Maxchen' WHERE id=1;

DELETE FROM users WHERE true=true;

SELECT

Erklärung

SELECT [DISTINCT] column1, column2, ... FROM tablename [WHERE condition] [HAVING condition] [GROUP BY column] [ORDER BY column ASC | DESC]

Die gespeicherten Daten können mit dem Befehl angezeigt werden.

DISTINCT lässt nur unterschiedliche Werte zu. So wird "DE, EN, US" angezeigt anstatt "DE, DE, EN, DE, US, EN, US, DE".
Mit ORDER BY werden die Ergebnisse nach der Spalte mit entweder aufsteigend (ASC) oder absteigend (DESC)
Mit GROUP BY werden Zeilen mit dem selben Wert zusammengefasst.
HAVING wurde für Funktionen eingefügt, da WHERE keine Möglichkeit dafür bietet
Bedinungen können mit AND und OR verknüpft werden.
Nutzt man BETWEEN value AND value, werden die alle Werte dazwischen ausgegeben, falls vorhanden. Mit NOT kann dies umgekehrt werden.
IN (value, value, value) gibt Zeilen mit diesen Werten, wieder. Es kann mit NOT umgekehrt werden oder auch als Langform mit OR geschrieben werden.

Funktionen

Sie können nicht in dem "WHERE"-Statement genutzt werden. Dazu wird eine extra Abfrage geschrieben (siehe Bsp.).

MIN() gibt den kleinsten Wert an
AVG() zeigt den Mittelwert aus allen Daten
MAX() bringt das Maximum zum Vorschein
SUM() addiert alle Werte
COUNT() zählt alle Werte

mehrere Tabellen

Daten aus mehreren Tabellen werden über die Primärschlüssel miteinander verbunden, um Informationen aus der einen Tabelle, mit Informationen aus der anderen Tabelle zu verknüpfen. Die bis jetzt bekannte schreibweise lautet: ... WHERE table1.ID=table2.ID.

Beispiele

Beispiel für eine Abfrage bei der jeder Begriff nur einmal auftauchen soll

 SELECT DISTINCT Bezeichnung
  FROM artikel;

Beispiel für eine Abfrage mit mehreren Werten

 SELECT *
  FROM artikel
  WHERE bezeichnung IN ('Herrenhose', 'Sommerkleid', 'T-Shirt')

Beispiel für eine Abfrage mit zwei Tabellen und einer Funktion

 SELECT a.ArtNr, Bezeichnung, (verkaufspreis * Absatz_2019) as `Umsatzstärkster`
  FROM artikel as a, verkauf as v
  WHERE a.ArtNr = v.ArtNr AND (verkaufspreis * Absatz_2019)=(
   SELECT max(verkaufspreis * Absatz_2019)
    FROM artikel as a, verkauf as v
    WHERE a.ArtNr = v.ArtNr
  );

Aufgaben zu SQL

Aufgabe 1

Beantworte folgende Fragen:

Wofür steht SQL?
In welche 4 Bereiche lassen sich die Befehle einordnen?
Und was ist die "referentielle Integrität"?

Lösung

SQL steht für "structured query language" und wird in:

(DQL) zur Abfrage und Aufbereitung der gesuchten Informationen
(DML) zur Datenmanipulation und lesendem Zugriff
(DDL) zur Definition des Datenbankschemas
(DCL) für die Rechteverwaltung und Transaktionskontrolle

Bereich eingeteilt. Die referentielle Integrität beschreibt die Bedingung, dass Informationen miteinander verbunden sind. So muss es für einen Fremdschlüssel den jeweiligen Primärschlüssel geben.

Aufgabe 2

Erstelle die Datenbank "JS-buildings" mit folgenden Tabellen.

"Employees": Jeder Angestellte hat eine fortlaufende ID, Vor- und Nachnamen mit jeweils maximal 20 Zeichen, bekommt einen ganzzahligen Lohn, der wenn nicht angegeben 450 beträgt und hat maximal ein optionales Hobby
"sold_buildings": Hier sind alle Gebäude verzeichnet, die von den Mitarbeitern gemanagt werden. Jeder Mitarbeiter kümmert sich um ein Gebäude. Die Gebäude haben eine Bezeichnung und verursachen Kosten im 4 stelligen Bereich

Ändere den ganzzahligen Lohn zu einem mit 2 Nachkommastellen im 4 stelligen Bereich und entferne die Spalte Hobby. Die Gebäude erwirtschaften auch Erlöse im 5 stelligen Bereich.

Lösung

 CREATE DATABASE `JS-buildings` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
 
 CREATE TABLE `employees` (
  id int(11) PRIMARY KEY AUTO_INCREMENT,
  forename varchar(20) NOT NULL,
  sirname varchar(20) NOT NULL,
  salary int(5) NOT NULL DEFAULT 450,
  hobby varchar(50) NULL
 );
 
 CREATE TABLE `sold_buildings` (
  id int(11) PRIMARY KEY AUTO_INCREMENT,
  b_name varchar(30) NOT NULL,
  costs decimal(6,2) NOT NULL
 );
 
 ALTER TABLE employees
  MODIFY salary decimal(6,2),
  DROP hobby;
 
 ALTER TABLE sold_buildings
  ADD revenues decimal(7,2);

Aufgabe 3

Übertrage die Werte in die Tabellen.

Angestellte

1	Max	Peters	450
2	Mila	Neumeier	2048
3	Alexa	Finke	900,24
4	Julian	Schönling	450
5	Fino	Neumeier	7030,50
6	Heino	Schmidt	1040,20
7	Lisa	Neumeier	675
8	Adrian	Peters	3090

Gebäude

1	STRG+C-Schule	8250,90	11300
2	Gib-aus-Bank	1760,45	20500
3	Sammelkiosk	3140	13050
4	Luxuswohnung	9990,01	35900
5	STRG+V-Kirche	560	09000
6	Obama-Tower	6500	30001,50
7	Kammer des Fürchtens	2000,60	02500
8	Museum der Toten	900	15670,50
9	Lisa Feller	450	0

Lösung

 INSERT INTO employees VALUES
  (1, "Max", "Peters", 450),
  (2, "Mila", "Neumeier", 2048),
  (3, "Alexa", "Finke", 900.24),
  (4, "Julian", "Schönling", 450),
  (5, "Fino", "Neumeier", 7030.5),
  (6, "Heino", "Schmidt", 1040.20),
  (7, "Lisa", "Neumeier", 675),
  (8, "Adrian", "Peters", 3090);
 
 INSERT INTO sold_buildings (b_name, costs, revenues) VALUES
  ("STRG+C-Schule", 8250.9, 11300),
  ("Gib-aus-Bank", 1760.45, 20500),
  ("Sammelkiosk", 3140, 13050),
  ("Luxuswohnung", 9990.1, 35900),
  ("STRG+V-Kirche", 560, 9000),
  ("Obama-Tower", 6500, 39991.5),
  ("Kammer des Fürchtens", 2000.6, 2500),
  ("Museum der Toten", 900, 15670.5),
  ("Lisa Feller", 450, 0);

Aufgabe 4

Ändere die Werte:

"" um die Tastenkombinationen
Erlöse um 5% verringern
lösche Lisa Feller

Lösung

 UPDATE sold_buildings
  SET b_name='"STRG+C-Schule"'
  WHERE id=1;
 UPDATE sold_buildings
  SET b_name='"STRG+V-Kirche"'
  WHERE id=5;
 
 UPDATE sold_buildings
  SET revenues=revenues*.95;
 
 DELETE FROM sold_buildings
  WHERE b_name="Lisa Feller";

Aufgabe 5

einfache Abfragen

Angestellte (forename, id) aus der Familie Neumeier und Peters
Mitarbeiter, die weniger als 3090 und nicht 675 oder 900,24 verdienen
Alle Familien (Spalte als "Families") die bei JS-buildings angestellt sind
Löhne, die zwischen 450 und 2000 liegen

komplexere Abfragen

Welche Gebäude betreuen Adrian und Julian?
Welches Gebäude erwirtschaftet (auch nach Abzug der Lohnkosten) am meisten und wie viel?
Was verdienen die Angestellten im Durchschnitt

Lösung

 SELECT id, forename
  FROM employees
  WHERE sirname IN ('Neumeier', 'Peters'); -- a)
 
 SELECT *
  FROM employees
  WHERE salary < 3090 AND salary NOT IN (675, 900.24); -- b)
 
 SELECT DISTINCT sirname as famalies
  FROM employees; -- c)
 
 SELECT salary
  FROM employees
  WHERE salary BETWEEN 450 AND 2000; -- d)

Informatik

SQL

S

T

D

S

A

Erklärung

Die wichtigsten Datentypen

Eigenschaften

Beispiele

Erklärung

Beispiel

Erklärung

Funktionen

mehrere Tabellen

Beispiele

Aufgabe 1

Lösung

Aufgabe 2

Lösung

Aufgabe 3

Angestellte

Gebäude

Lösung

Aufgabe 4

Lösung

Aufgabe 5

einfache Abfragen

komplexere Abfragen

Lösung