Kerschensteiner Schule Wachtmann
BBSII Delmenhorst: Wittenberg
Technische Assistenten Informatik
Datenbanken – Informationen zum Thema SQL
2004
SQL mit MySQL
1. Allgemeines
2. Informationen zur Architektur von MySQL
3. Anfragesprache SQL
07.12.2011 WT/WG Seite 1 von 25
Kerschensteiner Schule Wachtmann
BBSII Delmenhorst: Wittenberg
Technische Assistenten Informatik
Datenbanken – Informationen zum Thema SQL
2004
1. Allgemeines
Was ist ein Datenbanksystem
Datenbank: Eine Datenbank ist die Zusammenfassung von Datenbeständen,
zu deren einzelnen Elementen nach unterschiedlichen Kriterien
zugegriffen werden kann.
Datenbanksystem: Ein Datenbanksystem ist die Gesamtheit aller Programme, die
für den Aufbau und die Verwaltung der Datenbestände sowie
die Sicherung und Wiedergewinnung der Daten erforderlich
sind.
Ein DBS (DBMS) enthält:
Operationen zur Datenbankverwaltung
(CREATE, DROP)
Operationen zur Datenpflege
(DELETE, INSERT, UPDATE)
Abfragesprache (SELECT)
Sicherungsroutinen / Protokollierung
Recover - Verfahren
Unterschiedliche Architekturen
Hierarchische
Datenbanken: Strenge Hierarchie der Satztypen (Baumförmig).
Nur 1:n Beziehungen sind möglich (inflexibel)
Netzwerkdatenbanken Speicherung der Daten in Netzform.
Nachteil: Der Anwender muß genau den Suchpfad bei Abfragen
vorgeben (Programmierkenntnisse erforderlich).
Relationale Datenbanken Speicherung der Daten in Tabellen.
Anwenderfreundlich (einfach vorstellbar)
mächtige Anfragesprache SQL
07.12.2011 WT/WG Seite 2 von 25
Kerschensteiner Schule Wachtmann
BBSII Delmenhorst: Wittenberg
Technische Assistenten Informatik
Datenbanken – Informationen zum Thema SQL
2004
Hier einige große Datenbanksysteme
DB2
INFORMIX
ORACLE
SQL SERVER
Außerdem sei noch SAPDB (früher ADABAS) und MySQL genannt.
Datenbank-Anfragesprache SQL
Allgemeines zu SQL (Structured Query Language)
entwickelt in den 70er Jahren von E.F. Codd bei IBM
Vorteile
Mathematisch fundiert
beschreibende Sprache, keine Informationen über die Speicherung der Objekte nötig
leicht erlernbare Anfragesprache (keine Programmierkenntnisse erforderlich)
Alle Ergebnisse von SQL-Abfragen sind wieder Tabellen
Standard (ANSI - SQL)
07.12.2011 WT/WG Seite 3 von 25
Kerschensteiner Schule Wachtmann
BBSII Delmenhorst: Wittenberg
Technische Assistenten Informatik
Datenbanken – Informationen zum Thema SQL
2004
SQL dient zur
Datenbeschreibung (DDL – Data Definition Language)
- Datenbank erstellen, löschen und selektieren
- Tabellen erstellen, löschen und modifizieren
- Indizes erstellen, löschen und modifizieren
- - Statusinformationen über den Zustand der Datenbank erstellen
Datenmanipulation
- Einfügen, Ändern und Löschen von Daten(DML – Data Manipulation Language)
- Anzeigen von Daten aus der Datenbank (DQL – Data Query Language)
Datensicherheit
- Zugriffberechtigungen (Data Control Language)
- Ein-Befehls-Prinzip: Transaktionen
- Protokollierung aller Aktivitäten
- Wiederherstellung einer Datenbank nach z.B. einem Systemabsturz
oder einem Plattenausfall
07.12.2011 WT/WG Seite 4 von 25
Kerschensteiner Schule Wachtmann
BBSII Delmenhorst: Wittenberg
Technische Assistenten Informatik
Datenbanken – Informationen zum Thema SQL
2004
2. Informationen zur Architektur von MySQL
MySQL - Produkte
Backend Produkte
Dienen zur Speicherung und Verwaltung der Datenbank
mysqld-nt (Standard)
Frontend Produkte
Anwendermodule. Diese kommunizieren mit dem Backend.
Beispiele:
mysql.exe DOS Kommandozeilenprogramm (MySQL Monitor)
MySQL Control Center Grafisches Verwaltungswerkzeug
PhpMyAdmin auf PHP basierender Web-Client für einen MySQL
Server
Weitere Informationen zur Installation der Komponenten sind aus den Arbeitsblättern zu
entnehmen.
Anlegen einer Datenbank
CREATE DATABASE ;
Selektieren einer Datenbank
USE ;
Löschen einer Datenbank
DROP ;
07.12.2011 WT/WG Seite 5 von 25
Kerschensteiner Schule Wachtmann
BBSII Delmenhorst: Wittenberg
Technische Assistenten Informatik
Datenbanken – Informationen zum Thema SQL
2004
Anlegen einer Tabelle
CREATE TABLE
(Struktur);
Zur Struktur gehört die Definition der Spalten einer Tabelle inkl. Datentyp
Mögliche Datentypen:
Typ Beschreibung
TINYINT -128 .. 127
TINYINT UNSIGNED 0 .. 255
SMALLINT UNSIGNED Integer-Zahl von -0 bis 65535 (= 216 - 1)
SMALLINT Integer-Zahl von –32768 (= -215) bis +32767 (= 215 - 1)
INT -2.147.483.648 .. 2.147.483.647
INT UNSIGNED 0 .. 4.294.967.295
BIGINT -3402823e+31 .. 3402823e+31
DECIMAL(length,dec) Kommazahl der Länge "length" und mit "dec" Dezimalstellen; die
Länge beträgt: Stellen vor dem Komma + 1 Stelle für Komma +
Stellen nach dem Komma
Achtung: Steht so in der Beschreibung, jedoch lässt z. B.
DECIMAL(5,2) werte bis 9999.99 zu.
VARCHAR(NUM) Zeichenkette mit max "NUM" Stellen (1 ungleich
> größer
= größer gleich
= 15 (vergl1b)
b) ausdruck [ NOT ] BETWEEN ausdruck1 AND ausdruck2
WHERE pfl_hoehe between 10 AND 20 (vergl2)
07.12.2011 WT/WG Seite 16 von 25
Kerschensteiner Schule Wachtmann
BBSII Delmenhorst: Wittenberg
Technische Assistenten Informatik
Datenbanken – Informationen zum Thema SQL
2004
c) ausdruck [ NOT ] IN ( wert1, wert2, ...)
WHERE pfl_preis IN (1.8, 1.25, 2) (vergl3)
Achtung: Dezimalpunkte müssen als Punkt aufgeführt werden
(amerikanische Schreibweise)
d) ausdruck [ NOT ] LIKE ”zeichenkette”
Joker: % steht für kein oder mehrere Zeichen
_ steht für genau ein Zeichen
WHERE pfl_name LIKE ”A%” (vergl4a)
WHERE pfl_name NOT LIKE ”A%” (vergl4b)
WHERE pfl_name LIKE ”Aster” (vergl4c)
WHERE pfl_name LIKE ”Aste_” (vergl4d)
e) ausdruck IS [ NOT ] NULL
WHERE pfl_farbe IS NULL (vergl6a)
WHERE pfl_farbe IS NOT NULL (vergl6b)
07.12.2011 WT/WG Seite 17 von 25
Kerschensteiner Schule Wachtmann
BBSII Delmenhorst: Wittenberg
Technische Assistenten Informatik
Datenbanken – Informationen zum Thema SQL
2004
Group by - Klausel (optional)
Gruppierung der Ergebniszeilen nach bestimmten Spalten der Ergebnismenge.
Syntax: GROUP BY ( spalten_name [ ASC | DESC ] , ... )
Bemerkungen:
Alle Spalten der Select - Klausel müssen in der Group by - Klausel aufgeführt werden.
Alle Spalten der Group by - Klausel müssen als Spalten der Select - Klausel aufgeführt
werden.
Statt der Spaltennamen können auch entsprechende Zahlen in der Group by - Klausel
aufgeführt werden.
Beispiele:
Welche Wohnorte treten in der Lieferantentabelle auf ?
SELECT lfr_ort (group1)
FROM lieferanten
=> doppelte Wohnorte
SELECT lfr_ort (group2)
FROM lieferanten
GROUP BY lfr_ort
(alternativ mit DISTINCT)
Vorteile von GROUP BY:
Zu jeder so definierten Gruppe können die Aggregatsfunktionen zu weiteren Auswertungen
benutzt werden.
Welche Wohnorte treten wie oft in der Lieferantentabelle auf ?
SELECT (group3)
FROM lieferanten
GROUP BY
07.12.2011 WT/WG Seite 18 von 25
Kerschensteiner Schule Wachtmann
BBSII Delmenhorst: Wittenberg
Technische Assistenten Informatik
Datenbanken – Informationen zum Thema SQL
2004
Für die Sorten "Baum", "Heide" und "Ranke", unterschieden jeweils nach deren Farben, soll
der Minimalpreis ermittelt werden.
SELECT (group4)
FROM pflanzen
WHERE AND
GROUP BY
ORDER BY
Zusätzlich sollen nun noch der Maximalpreis und die Differenz (Maximalpreis -
Minimalpreis) sortiert nach der Differenz (absteigend) angezeigt werden.
SELECT (group5)
FROM pflanzen
WHERE AND
GROUP BY
ORDER BY
07.12.2011 WT/WG Seite 19 von 25
Kerschensteiner Schule Wachtmann
BBSII Delmenhorst: Wittenberg
Technische Assistenten Informatik
Datenbanken – Informationen zum Thema SQL
2004
Having - Klausel (optional)
Die Having - Klausel bezieht sich auf eine vorangegangende Group by - Klausel und erlaubt,
die Ergebnisdaten weiter einzuschränken (bezogen auf die Aggregatsfunktionen).
Syntax: HAVING bedingung
Dabei dürfen als Bedingung nur Aggregatsfunktionen verwendet werden.
Beispiele:
Welche Wohnorte treten in der Lieferantentabelle mindestens zweimal auf ?
SELECT lfr_ort, COUNT (*) (having1)
FROM lieferanten
GROUP BY lfr_ort
HAVING COUNT (*) >= 2
Wie bereits oben (group4) soll für die Pflanzensorten "Baum", "Heide" und "Ranke" bzw.
deren Farben der Minimalpreis berechnet werden. Zusätzlich interessieren nur die
Minimalpreise, die zwischen 3 EURO und 11 EURO liegen.
SELECT (group4)
FROM pflanzen
WHERE AND
GROUP BY
HAVING
ORDER BY
07.12.2011 WT/WG Seite 20 von 25
Kerschensteiner Schule Wachtmann
BBSII Delmenhorst: Wittenberg
Technische Assistenten Informatik
Datenbanken – Informationen zum Thema SQL
2004
Into temp - Klausel (optional) Nicht bei MySQL
Mittels der Into temp - Klausel werden die Resultate einer Suche in einer temporären Tabelle
gespeichert.
Syntax: INTO TEMP
Beispiele:
SELECT DISTINCT ang_art_nr (into)
FROM angebote
INTO TEMP angebote_art_nr
Eine Alternative zu INTO bietet die folgende Syntax:
CREATE TABLE AS SELECT ……
CREATE TABLE angebote_art_nr (as_for_into)
AS SELECT DISTINCT ang_art_nr
FROM angebote;
07.12.2011 WT/WG Seite 21 von 25
Kerschensteiner Schule Wachtmann
BBSII Delmenhorst: Wittenberg
Technische Assistenten Informatik
Datenbanken – Informationen zum Thema SQL
2004
Tabellenverknüpfungen
Eine Verknüpfung verbindet zwei oder mehrere Tabellen miteinander. Diese Bedingung wird
daher auch Verbundoperation oder JOIN genannt.
Beispiel:
Tabelle Lieferanten:
Lief-Nr Name
5 Meier
9 Müller
Tabelle Bestellungen:
Bestell-Nr. Lief-Nr. Bestelldatum
47 5 01.04.2003
120 9 11.11.2003
34 5 28.02.2003
JOIN dieser zwei Tabellen (kartesisches Produkt) ohne JOIN-Bedigung:
Bestell-Nr. Lief-Nr. Bestelldatum Lief-Nr Name
47 5 01.04.2003 5 Meier
47 5 01.04.2003 9 Müller
120 9 11.11.2003 5 Meier
120 9 11.11.2003 9 Müller
34 5 28.02.2003 5 Meier
34 5 28.02.2003 9 Müller
Gewünscht sind aber natürlich nur die Datensätze mit gleichen Lieferantennummern.
Ergebnis:
Bestell-Nr. Lief-Nr. Bestelldatum Lief-Nr Name
47 5 01.04.2003 5 Meier
120 9 11.11.2003 9 Müller
34 5 28.02.2003 5 Meier
07.12.2011 WT/WG Seite 22 von 25
Kerschensteiner Schule Wachtmann
BBSII Delmenhorst: Wittenberg
Technische Assistenten Informatik
Datenbanken – Informationen zum Thema SQL
2004
Syntax: SELECT select-klausel FROM tab1, [ OUTER ] tab2, ...
WHERE bedingung
Er gelten die gleichen Operationen wie oben.
Beispiele:
Für welche Pflanzen existieren Angebote?
SELECT * (join1)
FROM pflanzen, angebote
WHERE pfl_art_nr = ang_art_nr
Für welche Pflanzen existieren Angebote? Suchen sie die Pflanzennamen und die
Lieferantennummer aus dem Angebot.
SELECT pfl_name, ang_lfr_nr (join2)
FROM
WHERE
SELECT pfl_name, ang_lfr_nr (join2a)
FROM pflanzen p, angebote a
WHERE
Für welche Pflanzen, für die der Angebotspreis über 8.50 EURO liegt, existieren Angebote?
SELECT (join3)
FROM
WHERE AND
Welche Pflanzen, die weniger als 1 EURO kosten, sind als niedrig einzustufen?
SELECT (join4)
FROM
WHERE AND AND
07.12.2011 WT/WG Seite 23 von 25
Kerschensteiner Schule Wachtmann
BBSII Delmenhorst: Wittenberg
Technische Assistenten Informatik
Datenbanken – Informationen zum Thema SQL
2004
In wievielen Angeboten werden Astern angeboten?
SELECT (join5)
FROM
WHERE
Welche Lieferanten liefern die Wasserpflanze "Froschbiss"?
SELECT (join6)
FROM
WHERE
INNER/Outer Join:
Syntax: SELECT select-klausel FROM tab1 INNER JOIN tab2, ...
ON tab1.attribut = tab2.attribut
Syntax: SELECT select-klausel FROM tab1 LEFT [OUTER] JOIN tab2, ...
ON tab1.attribut = tab2.attribut
Aufgabe:
Darstellung aller Lieferantenangebote plus, falls vorhanden, die Daten möglicher
Bestellungen. Der Angebotspreis soll zwischen 1.00 EURO und 1.50 EURO liegen.
Falsch:
SELECT (join7a)
FROM
WHERE
SELECT (join7b)
FROM INNER JOIN
ON
Problem: Es werden nur die Angebote angezeigt, für die eine Bestellung vorliegt, alle anderen
Angebote ohne Bestellung nicht.
Richtig:
SELECT (join8)
FROM
ON
07.12.2011 WT/WG Seite 24 von 25
Kerschensteiner Schule Wachtmann
BBSII Delmenhorst: Wittenberg
Technische Assistenten Informatik
Datenbanken – Informationen zum Thema SQL
2004
Datenmanipulation mit SQL
Daten einfügen:
Syntax: INSERT INTO tabname [ (spalten_name, ...) ]
{ VALUES (wert1, wert2, ...) | SELECT - Anweisung }
Beispiele:
INSERT INTO pflanzen (insert1)
VALUES (4712, "Rose", "Blume", "rot", 45, 6, 9, 2.45)
INSERT INTO pflanzen (pfl_art_nr, pfl_name) (insert2)
VALUES (4713, "Tulpe")
Daten löschen:
Syntax: DELETE FROM tabname [ WHERE-Bedingung ]
Beispiele:
DELETE FROM angebote_art_nr (delete1)
DELETE FROM pflanzen (delete2)
WHERE pfl_art_nr = ‘4712’
Daten ändern:
Syntax: UPDATE tabname
SET spalten_name = Ausdruck
{, spalten_name = Ausdruck, ……}
[ WHERE-Bedingung ]
Beispiele:
UPDATE pflanzen (update1)
SET pfl_preis = 1.50,
pfl_hoehe = 25
WHERE pfl_name = "Aster"
07.12.2011 WT/WG Seite 25 von 25