4. Structured Query Language (SQL)

4. Structured Query Language
(SQL)
Rückblick
§ Konzeptuelles Modell (ERM) können wir nun in
(wenige) Relationen übersetzen
§ Relationale Algebra gibt uns eine Sprache an die Hand,
mit der wir Anfragen auf Relationen formulieren können
§ Jetzt: Structured Query Language (SQL) als der
Industriestandard zur Schemadefinition,
Datenmanipulation und Anfrageformulierung
Datenbanken / Kapitel 4: Structured Query Language (SQL)
2
SQL vs. Relationales Modell & Relationenalgebra
§ Relationales Modell und relationale Algebra bilden das
theoretische Fundament von SQL
§ Wichtige Unterschiede:
§ Relationales Modell sieht Relationen als Mengen von
Tupeln, und es gibt somit keine Duplikate; per SQL definierte
Tabellen in RDBMS können jedoch Duplikate enthalten
§ Relationale Algebra gibt (implizit) einen Auswertungsplan für
die Anfrage vor; SQL ist rein deklarativ, d.h. das RDBMS
darf selbst entscheiden wie die Anfrage ausgewertet wird
Datenbanken / Kapitel 4: Structured Query Language (SQL)
3
Structured Query Language (SQL)
§ Structured Query Language (SQL)
§ geht zurück auf den in IBM Almaden (San Jose)
entwickelten Prototypen System R
§ ursprünglich: Structured English Query Language (SEQUEL)
§ auf Englisch wird SQL noch immer sequel gesprochen
Datenbanken / Kapitel 4: Structured Query Language (SQL)
4
SQL als Standard
§ SQL standardisiert durch American National Standards
Institute (ANSI) und International Organization for
Standardization (ISO)
§ SQL-86 / SQL-89
§ SQL-92 (z.B. Datentypen für Daten, Mengenoperationen)
§ SQL-99 (z.B. rekursive Anfragen)
§ SQL-2003 (z.B. Unterstützung von XML)
§ SQL-2008 / SQL-2011
§ Oracle, IBM DB2 und Microsoft SQL Server unterstützen
SQL-92 weitgehend und bieten darüber hinaus
proprietäre Funktionalität (z.B. XML-Unterstützung)
Datenbanken / Kapitel 4: Structured Query Language (SQL)
5
SQL Sprachbestandteile
§ Data Definition Language (DDL) zur
Schemadefinition (z.B. Anlegen von Tabellen)
§ Data Query Language (DQL) zum
Anfragen (z.B. Auswahl bestimmer Zeilen)
§ Data Manipulation Language (DML) zur
Datenmanipulation (z.B. Einfügen von Daten in Tabellen)
§ Data Control Language (DCL) zur
Rechteverwaltung (z.B. Sperren des Zugriffs auf Tabelle)
§ Transaction Control Language (TCL) zur
Transaktionsverwaltung (z.B. rückgängig machen)
Datenbanken / Kapitel 4: Structured Query Language (SQL)
6
4.1 Schemadefinition und -veränderung
§ Data Definition Language (DDL) stellt Befehle zur
Schemadefinition und –veränderung bereit, z.B.
§ Anlegen, Ändern und Löschen von Tabellen
§ Anlegen, Ändern und Löschen von Sichten (Kapitel 10)
§ Wie können wir zu Relationen unseres Schema, z.B.
Ó
Ô
Studenten : [ MatrNr : integer, Vorname : string, Name : string, Semester : integer ]
Ó
Ô
Professoren : [ PersNr : integer, Vorname : string, Name : string, Fach : string ]
entsprechende Tabelle anlegen?
Datenbanken / Kapitel 4: Structured Query Language (SQL)
7
Datentypen
§ SQL kennt eine Vielzahl von Datentypen, u.a.
§ int, smallint, bigint für ganze Zahlen
§ float für Gleitkommazahlen
§ char(n) für Zeichenketten mit fixer Länge n
§ varchar(n) für Zeichenkette der maximalen Länge n
§ date, time, datetime für Datums- und Zeitangaben
§ money für Währungsangaben
§ blob / clob für große Binär- bzw. Textdaten
§ …
§ Verfügbarkeit und Benennung der Datentypen
unterscheidet sich (leider) zwischen RDBMSs
Datenbanken / Kapitel 4: Structured Query Language (SQL)
8
Datentypen in SQLite und MS SQL Server
§ MS SQL Server und SQLite als die für unsere Vorlesung
relevanten RDBMSs kennen u.a. folgende Datentypen
§ int / smallint / integer für ganze Zahlen
§ float / real für Gleitkommazahlen
§ char / varchar(n) / text für Zeichenketten
§ varbinary(n) / blob für sehr große Binärdaten (bis 2 GB)
§ datetime / smalldatetime für Datumsangaben
§ money für Währungsangaben
Datenbanken / Kapitel 4: Structured Query Language (SQL)
9
NULL-Werte
§ Weiterer Unterschied zum relationalen Modell:
RDBMSs unterstützen NULL-Werte für alle Datentypen
§ NULL-Wert zeigt an, dass der Wert des Attributs nicht
bekannt ist oder dass das Attribut nicht anwendbar ist
§ NULL-Werte können bei der Schemadefinition (d.h. dem
Anlegen von Tabellen) erlaubt oder untersagt werden
§ Beispiel: Lieferdatum (nicht bekannt) und Bemerkung (nicht
anwendbar) von Bestellungen dürfen NULL sein
Datenbanken / Kapitel 4: Structured Query Language (SQL)
10
Anlegen von Tabellen
§ Tabellen lassen sich mittels CREATE TABLE anlegen
1
2
3
4
5
CREATE TABLE < Name der Tabelle > (
< Name von Attribut 1 > datentyp ( NOT ) NULL ,
< Name von Attribut 2 > datentyp ( NOT ) NULL ,
...
)
§ Beispiel: Tabelle Professoren
1
2
3
4
5
6
CREATE TABLE Professoren (
PersNr int NOT NULL ,
Vorname varchar (30) NOT NULL ,
Name varchar (30) NOT NULL ,
Fach varchar (60) NULL
)
Datenbanken / Kapitel 4: Structured Query Language (SQL)
11
Anlegen von Tabellen
§ NULL-Werte können erlaubt oder untersagt werden
§ NOT NULL lässt keine NULL-Werte zu
§ NULL (oder keine Angabe) lässt NULL-Werte zu
§ Diese Angaben sind Integritätsbedingen, d.h. das
RDBMS stellt sicher, dass sie eingehalten werden
Datenbanken / Kapitel 4: Structured Query Language (SQL)
12
Primärschlüssel
§ Bisher kein Schlüssel definiert, d.h. die Tabelle
Professoren könnte mehrere identische Tupel enthalten
§ Primärschlüssel (mehr dazu in Kapitel 5) ist die Menge
von Attributen, die als Schlüssel für Tabelle gewählt wurde
§ Primärschlüssel wird mittels PRIMARY KEY angegeben
und kann ein oder mehrere Attribute umfassen
Datenbanken / Kapitel 4: Structured Query Language (SQL)
13
Primärschlüssel
§ Beispiel: Tabelle Professoren
1
2
3
4
5
6
7
CREATE TABLE Professoren (
PersNr int ,
Vorname varchar (30) ,
Name varchar (30) ,
Fach varchar (60) ,
PRIMARY KEY ( PersNr )
)
§ Erinnerung: Primärschlüssel stellen eine
Integritätsbedingung dar (mehr dazu in Kapitel 6),
um deren Einhaltung sich das RDBMS nun kümmert,
d.h. wir können nicht mehrere Tupel mit identischer
PersNr in die Tabelle einfügen
Datenbanken / Kapitel 4: Structured Query Language (SQL)
14
Fremdschlüssel
§ Fremdschlüssel sind Gruppen von Attributen, die dem
Primärschlüssel einer anderen Tabelle entsprechen
und damit auf ein Tupel in der anderen Tabelle verweisen
§ Beispiel: Relation Vorlesung, nach Eliminierung des 1:nBeziehungstyps lesen, enthält Attribut PersNr,
welches auf einen Professor verweist
1
2
3
4
5
6
7
CREATE TABLE Vorlesungen (
VorlNr int NOT NULL ,
Bezeichnung varchar (60) NOT NULL ,
SWS varchar (30) NOT NULL ,
PersNr int NULL ,
PRIMARY KEY ( VorlNr )
)
Datenbanken / Kapitel 4: Structured Query Language (SQL)
15
Fremdschlüssel
§ Fremdschlüssel werden mittels FOREIGN KEY ...
REFERENCES angegeben und können jeweils mehrere
Attribute enthalten
§ Eine Tabelle kann höchstens einen Primärschlüssel,
aber mehrere Fremdschlüssel besitzen
1
2
3
4
5
6
7
8
CREATE TABLE Vorlesungen (
VorlNr int NOT NULL ,
Bezeichnung varchar (60) NOT NULL ,
SWS varchar (30) NOT NULL ,
PersNr int NULL ,
FOREIGN KEY ( PersNr ) REFERENCES Professoren ( PersNr ) ,
PRIMARY KEY ( VorlNr )
)
Datenbanken / Kapitel 4: Structured Query Language (SQL)
16
Referentielle Integrität
§ Fremdschlüssel stellen eine Integritätsbedingung dar
§ RDBMS stellt referentielle Integrität der Daten sicher, d.h.
wenn die Attribute A1 ,…, An als Fremdschlüssel auf eine
andere Tabelle T markiert sind, dann
§ müssen alle Ai den Wert NULL haben (sofern erlaubt), oder
§ es muss ein entsprechendes Tupel mit den Werten der
Attribute Ai als Primärschlüssel in der Tabelle T existieren
§ Mehr zur referentiellen Integrität in Kapitel 6
Datenbanken / Kapitel 4: Structured Query Language (SQL)
17
Ändern von Tabellen
§ Tabellen (d.h. ihr Schema, nicht ihr Inhalt) lassen sich
mittels ALTER TABLE verändern
§ Hinzufügen eines Attributs
1
2
ALTER TABLE < Name der Tabelle >
ADD COLUMN < Name des Attributs > datentyp
§ Ändern eines Attributs
1
2
ALTER TABLE < Name der Tabelle >
ALTER COLUMN < Name des Attributs > datentyp
Datenbanken / Kapitel 4: Structured Query Language (SQL)
18
Löschen von Tabellen
§ Tabellen lassen sich mittels DROP TABLE löschen
1
DROP TABLE < Name der Tabelle >
§ Beispiel: Tabelle Professoren
1
DROP TABLE Professoren
Datenbanken / Kapitel 4: Structured Query Language (SQL)
19
SQL-Syntaxdiagramme
§ Dokumentation zu RDBMS veranschaulicht die Syntax
eines SQL Kommandos evtl. mittels Syntaxdiagramm
Quelle: https://www.sqlite.org
Datenbanken / Kapitel 4: Structured Query Language (SQL)
20
SQL-Grammatiken
§ Grammatiken sind eine alternative, zu Syntaxdiagrammen
äquivalente, Darstellung der Syntax von SQL Kommandos
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
(create_definition,...)
[partition_options]
create_definition:
col_name column_definition
| [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)
[index_option] ...
| [CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (index_col_name,...) reference_definition
| CHECK (expr)
column_definition:
data_type [NOT NULL | NULL] [DEFAULT default_value]
[AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
[COMMENT 'string']
[COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}]
[STORAGE {DISK|MEMORY|DEFAULT}]
[reference_definition]
Quelle: http://docs.oracle.com
Datenbanken / Kapitel 4: Structured Query Language (SQL)
21
Data Dictionary
§ RDBMSs verwenden meist selbst eine Datenbank, das
sogenannte Data Dictionary, um Schemainformationen
(z.B. welche Tabellen es gibt) zu verwalten
§ Namen der Tabellen, in denen das Data Dictionary selbst
angelegt ist, sind (leider) systemabhängig
§ RDBMSs bieten teilweise eigene Kommandos, um auf
das Data Dictionary zuzugreifen
Datenbanken / Kapitel 4: Structured Query Language (SQL)
22
Data Dictionary
§ Zugriff auf Data Dictionary in SQLite:
§ .tables zeigt verfügbare Tabellen an
§ .schema zeigt Schema einer Tabelle an
§ Zugrif auf Data Dictionary in MS SQL Server:
§ sp_tables enthält verfügbare Tabellen
§ sp_columns <T> enthält Schema der Tabelle T
Datenbanken / Kapitel 4: Structured Query Language (SQL)
23
4.2 Anfragen auf einer Tabelle
§ Anfragen lassen sich mittels des SELECT Kommandos
formulieren; dieses hat folgende Form
1
2
3
4
SELECT
FROM
WHERE
ORDER BY
< Attribute >
< Tabellen >
< Bedingungen >
< Attribute >
§ Beispiel: Vorname und Name von Professoren in Informatik
1
2
3
SELECT Vorname , Name
FROM Professoren
WHERE Fach = ’ Informatik ’
dies entspricht folgendem Ausdruck der Relationenalgebra
fi [ Vorname, Name ] ( ‡ [ Fach = “Informatik” ] ( Professoren ) )
Datenbanken / Kapitel 4: Structured Query Language (SQL)
24
Duplikateneliminierung
§ Anfrageergebnisse können, im Gegensatz zu
Ergebnisrelationen der Relationanalgebra,
Duplikate enthalten
Professoren
PersNr
Vorname
Name
Fach
101101
231011
300128
478122
600321
Donald
Albert
Alfred
Donald
Carl
Knuth
Einstein
Nobel
Kossmann
Gauss
Informatik
Physik
Chemie
Informatik
Mathematik
Vorname
1
2
SELECT Vorname
FROM Professoren
Datenbanken / Kapitel 4: Structured Query Language (SQL)
Donald
Albert
Alfred
Donald
Carl
25
Duplikateneliminierung
§ Duplikate im Anfrageergebnis können durch Angabe von
DISTINCT unterdrückt werden
Professoren
PersNr
Vorname
Name
Fach
101101
231011
300128
478122
600321
Donald
Albert
Alfred
Donald
Carl
Knuth
Einstein
Nobel
Kossmann
Gauss
Informatik
Physik
Chemie
Informatik
Mathematik
Vorname
1
2
SELECT DISTINCT Vorname
FROM Professoren
Datenbanken / Kapitel 4: Structured Query Language (SQL)
Donald
Albert
Alfred
Carl
26
Sortierung
§ Anfrageergebnisse können, im Gegensatz zu
Ergebnisrelationen der Relationanalgebra,
eine Ordnung haben
§ Sortierung des Anfrageergebnis nach ein oder mehreren
Attributen durch Angabe durch ORDER BY
§ Natürliche Ordnung der Attribute wird gemäß ihres
Datentyps verwendet, d.h. numerische Attribute (z.B. int
und float) werden nach numerischem Wert, textuelle
Attribute (z.B. char und varchar) lexikografisch sortiert
Datenbanken / Kapitel 4: Structured Query Language (SQL)
27
Sortierung
§ Angabe von ASC bzw. DESC bestimmt, ob nach einem
Attribut aufsteigend oder absteigend sortiert wird
§ ASC ist hier Vorgabewert (default) und kann entfallen
§ Beispiel: Vorname, Name und Fach von Professoren,
aufsteigend sortiert nach Nachname und Vorname
1
2
3
4
SELECT
FROM
WHERE
ORDER BY
Datenbanken / Kapitel 4: Structured Query Language (SQL)
Vorname , Name , Fach
Professoren
Fach = ’ Mathematik ’
Name , Vorname
28
Sortierung
§ Beispiel: Vorname und Name von Studenten, absteigend
sortiert nach ihrer Anzahl von Semestern
1
2
3
SELECT Vorname , Name
FROM Studenten
ORDER BY Semester DESC
Datenbanken / Kapitel 4: Structured Query Language (SQL)
29
Formatierung und Namenskonventionen
§ Ähnlich zu Programmiersprachen, gibt es auch für SQL
verschiedene Konvetionen zur Bennenung von
Bezeichnern und Formatierung von Kommandos
§ Schlüsselwörter (CREATE vs. create)
§ Attributnamen (Bestell_Nr vs. BestellNr)
§ Tabellennamen (Kunden vs. Kunde)
§ Formatierung von Kommandos (Klammern und Umbrüche)
§ Letztlich Geschmacksache,
Konsistenz innerhalb eines Projekts ist jedoch wichtig
Datenbanken / Kapitel 4: Structured Query Language (SQL)
30
Anfrageübung Versandhandel
Ó
Ô
Bestellungen : [ BestellNr : integer, Bestelldatum : string, . . . , KundenNr : integer, ]
Ó
Ô
Kunden : [ KundenNr : integer, Vorname : string, Name : string, . . . ]
Ó
Ô
Artikel : [ ArtikelNr : integer, Beschreibung : string, . . . , ME : string ]
Ó
Ô
Mengeneinheiten : [ ME : string, Beschreibung : string, . . . ]
Ó
Ô
Bestellpositionen : [ BestellNr : integer, ArtikelNr : integer , Anzahl : integer ]
§ Kunden aus Mannheim sortiert nach Name und Vorname
§ Bestellungen des Kunden mit der KundenNr 66111019
sortiert nach ihrem Bestelldatum
§ Vornamen von Kunden (ohne Duplikate)
Datenbanken / Kapitel 4: Structured Query Language (SQL)
31
Anfrageübung Versandhandel
§ Kunden aus Mannheim sortiert nach Name und Vorname
SELECT *
FROM Kunden
WHERE Wohnort = ’ Mannheim ’
ORDER BY Name , Vorname
1
2
3
4
§ Bestellungen des Kunden mit der Kunden_Nr 66111019
sortiert nach ihrem Bestelldatum
1
2
3
4
SELECT
FROM
WHERE
ORDER BY
*
Bestellungen
Kunden_Nr = 66111019
Bestelldatum
§ Vornamen von Kunden (ohne Duplikate)
1
2
SELECT DISTINCT Vorname
FROM Kunden
Datenbanken / Kapitel 4: Structured Query Language (SQL)
32
Zusammenfassung
§ Structured Query Language (SQL) als standardisierte
Anfragesprache für relationale Datenbanken
§ Data Definition Language zur Schemadefinition
(z.B. CREATE TABLE zum Anlegen von Tabellen)
§ Data Query Language zum Formulieren von Anfragen
(SELECT ... FROM ... WHERE ... ORDER BY ...)
Datenbanken / Kapitel 4: Structured Query Language (SQL)
33
Literatur
[1]
A. Kemper und A. Eickler: Datenbanksysteme – Eine
Einführung, De Gruyter Oldenbourg, 2015 (Kapitel 4)
[2]
G. Saake, K.-U. Sattler und A. Heuer:
Datenbanken - Konzepte und Sprachen,
mitp Professional, 2013 (Kapitel 7)
Datenbanken / Kapitel 4: Structured Query Language (SQL)
34