SQL: Übersicht - Bildungsportal Sachsen

SQL: Übersicht
• SQL (Structured Query Language) ist ANSI‐ und ISO‐Standard
• 1975 SEQUEL = Structured English Query Language für System R (IBM)
• 1979 Oracle V2 (Relational Software Inc. ) erstes SQL Produkt
• 1986 ANSI
1986 ANSI‐Standard
Standard SQL1
SQL1
• 1987 Internationalen Organisation für Normung (ISO)‐Standard SQL1
• 1989 SQL1 (Version 2) standardisiert
• 1992 SQL2 bzw. SQL‐92 von der ISO verabschiedet
– Alle aktuellen DBMS halten sich im wesentlichen daran
• 1999 SQL:1999 (ISO/IEC 9075:1999, auch SQL3 genannt) – Noch nicht in allen DBMS implementiert, gilt auch für die folgenden:
Noch nicht in allen DBMS implementiert gilt auch für die folgenden:
• 2003 SQL:2003
2008 SQL:2008
• 2008 SQL:2008
9. Prof. Jasper: Datenbanksysteme
1
SQL: Datendefinition
• Datendefinition in SQL mittels CREATE, DROP und ALTER TABLE
• CREATE TABLE
– Definiert eine neue (Basis‐) Tabelle mit Namen und Attributspezifikation
(Name und Datentyp aus INTEGER; FLOAT, DECIMAL(i, j), CHAR(n), (Name und Datentyp
INTEGER; FLOAT DECIMAL(i j) CHAR(n)
VARCHAR(n))
– Ein Constraint NOT NULL kann für Attribute definiert werden
– Beispiel
– CREATE TABLE Abteilung(
Abt il
(
ABT_NAME
VARCHAR(10) NOT NULL,
ABT_ID
INTEGER NOT NULL,
ABT_Leiter
CHAR(9),
ABT_Leiter_seit
CHAR(9) );
9. Prof. Jasper: Datenbanksysteme
2
SQL: Datendefinition
• CREATE TABLE (Forts.)
– In SQL2 können auch Primärschlüssel, Fremdschlüssel und Constraints für die referentielle Integrität spezifiziert werden
– Primärschlüssel über die Schlüsselworte PRIMARY KEY und UNIQUE
– Beispiel
p
CREATE TABLE Abteilung(
ABT NAME
ABT_NAME
VARCHAR(10) NOT NULL
NULL,
ABT_ID
INTEGER NOT NULL,
ABT_Leiter
CHAR(9),
CHAR(9) );
ABT_Leiter_seit
PRIMARY KEY (ABT_ID),
UNIQUE (ABT_NAME),
FOREIGN KEY (ABT
(ABT_Leiter)
Leiter) REFERENCES Personal);
9. Prof. Jasper: Datenbanksysteme
3
SQL: Datendefinition
• DROP TABLE:
–
–
–
–
Löscht eine Tabelle und ihre Definition
Di T b ll kann
Die Tabelle
k
nie
i mehr
h genutzt
t t werden!
d !
Beispiel:
DROP TABLE Personal;
• ALTER TABLE:
– Fügt ein weiteres Attribut zu einer Relation hinzu
– Nach Ausführung der Anweisung haben alle Tupel den Wert NULL für diese
Att ib t
Attribut
– Somit ist NOT NULL‐Constraint hier nicht erlaubt
– Beispiel:
– ALTER TABLE Personal ADD Stellenbezeichung
VARCHAR(12);
– Der Nutzer muss für jedes Tupel einen Wert explizit eintragen
9. Prof. Jasper: Datenbanksysteme
4
SQL: Datendefinition
• Weitere DDL‐Statements in SQL2 und SQL:1999
– CREATE SCHEMA
• Ein
Ei DB‐Schema wird
DB S h
i d explizit
li it über
üb einen
i
N
Namen
vereinbart
i b t
– Unterstützung der referentiellen Integrität
• RESTRICT, CASCADE, SET NULL, SET DEFAULT
• Beispiel:
CREATE TABLE Abteilung(
ABT_NAME
VARCHAR(10) NOT NULL,
ABT_ID
INTEGER NOT NULL,
CHAR(9),
( ),
ABT_Leiter
ABT_Leiter_seit
CHAR(9) );
PRIMARY KEY (ABT_ID),
UNIQUE
Q
(
(ABT_NAME),
),
FOREIGN KEY (ABT_Leiter) REFERENCES Personal
ON DELETE SET DEFAULT ON UPDATE CASCADE);
9. Prof. Jasper: Datenbanksysteme
5
SQL: Datendefinition
• Referentielle Integrität (Forts.)
– CREATE TABLE Personal(
Name
VARCHAR(30) NOT NULL,
P_ID
CHAR(9), NOT NULL
Geb_Dat
DATE,
ABT_ID
INTEGER DEFAULT 1,
Vorgesetzter CHAR(9),
PRIMARY KEY (P_ID),
FOREIGN KEY (ABT_ID) REFERENCES DEPT
ON DELETE SET NULL ON UPDATE CASCADE
CASCADE,
FOREIGN KEY (Vorgesetzter) REFERENCES Personal
ON DELETE SET NULL ON UPDATE CASCADE);
9. Prof. Jasper: Datenbanksysteme
6
SQL: Datendefinition
• Weitere Datentypen in SQL2 bzw‐ SQL:1999
DATE: Datums mit unterschiedlichen Formaten, Standard YYYY‐MM‐DD
TIME Z it in Stunden, Minuten
TIME: Zeit
i St d
Mi t und Sekunden, Standard hh:mm:ss
dS k d
St d d hh
TIME(i): Zeit mit Sekundenbruchteilen (i‐stellig) hh:mm:ss:ii...i
TIMESTAMP: besteht aus DATE und TIME
TIMESTAMP: besteht aus DATE und TIME
INTERVAL: Relative Angabe von Zeit im Vergleich zu einer absoluten Angabe („2008‐10‐25 plus 3 WEEKS“)
– CLOB: Text ohne interne Struktur
– BLOB: Digitales Objekt
– …
–
–
–
–
–
9. Prof. Jasper: Datenbanksysteme
7
SQL: Anfragen (Queries)
• SQ
SQL hat eine
h
i Anweisung
i
fü Anfragen: das SELECT‐Statement
für
f
d S C S
• Wichtiger Unterschied zwischen dem formalen relationalen Modell
und dem “SQL‐Datenmodell”: SQL arbeitet
Q
Q
mit Multimengen
g ((Bags), g ),
d.h. es können identische Tupel in einer Tabelle existieren. Beispiel: {A, B, C, A} ist ein Bag; {A, B, C} ist sowohl Bag als auch Menge
• Die Grundform
Die Grundform einer SQL SELECT Anweisung
SQL SELECT Anweisung ist eine Abbildung, Abbildung
auch SFW‐Klausel (SELECT‐FROM‐WHERE‐Block) genannt
SELECT
<attribute list>
FROM <table list>
WHERE<condition>
– <attribute list> ist die Menge der Attributnamen, deren Werte das Ergebnis der Anfrage bilden
– <table list> spezifiziert alle Tabellen (Relationen) zur Beantwortung der
Anfrage
– <condition> ist eine ((boolesche) Bedingung, die jedes
)
g g,
j
Tupel
p spezifiziert, p
,
das in der Antwortmenge ist.
9. Prof. Jasper: Datenbanksysteme
8
SQL: Anfragen (Queries)
• Beispiels‐DB‐Schema:
Personal
V Name
V_Name
N Name
N_Name
P ID
P_ID
Geb Dat
Geb_Dat
Adr
M W
M_W
Gehalt
Vorg
Abt Nr
Abt_Nr
Abteilung
Abt_Name
Abt_ID
Abt_Leiter
Abt_Leiter_Seit
Standort
Abt_ID
Standort
Projekt
P_Name
Proj_ID
Standort
Abt
A b it t fü
Arbeitet_für
P_ID
Proj_ID
Stunden
Angehörige
P_Id
An_Name
M_W
Geb_Dat
Beziehung
9. Prof. Jasper: Datenbanksysteme
9
SQL: Anfragen (Queries)
• Einfache Anfragen:
– Entsprechen den Ausdrücken Selektion, Projektion und Join der RA
– Anfrage auf einer Tabelle: • “Geburtsdatum und Adresse von Mitarbeiter Bernd Müller”
• Q0: SELECT Geb_Dat, Adr
FROM Personal
WHERE V Name=‘Bernd'
WHERE V_Name
Bernd AND N_Name
AND N Name=‘Müller’
Müller
• Entspricht einer Selektion (WHERE‐Klausel) und Projektion (SELECT‐Klausel)
• Resultat kann Duplikate enthalten
– Anfrage auf zwei Tabellen:
– “Name
Name und Adresse
und Adresse aller Mitabeiter der “Forschungs”‐Abteilung”
Forschungs Abteilung
• Q1: SELECT V_Name, N_Name, Adr
FROM Personal, Abteilung
WHERE Abt Name=‘Forschung'
WHERE Abt_Name=
Forschung AND Abt_ID=Abt_Nr
AND Abt ID=Abt Nr
• Entspricht einer Kombination von Selekton, Projektion und Join der RA
9. Prof. Jasper: Datenbanksysteme
10
SQL: Anfragen (Queries)
• Einfache Anfragen (Forts.)
Ei f h A f
(F
)
– „Gib mir die Projekt‐Id, die zugehörige Abteilungs_Id und Nachname, Adresse und Geburtsdatum des zuständigen Abteilungsleiters für jedes Projekt in Freiberg“
• Q2
Q2: SELECT Proj_ID, Abt_Id, N_Name, Adr, Geb_Dat
SELECT
P j ID Abt Id N N
Ad G b D t
FROM
Projekt, Abteilung, Personal
WHERE Abt=Abt_ID AND Abt_Leiter = P_ID AND Standort= 'Freiberg';
• IN Q2 gibt es zwei Joins
– Aliase:
• Attributnamen müssen in SQL‐Statements eindeutig sein (bzgl. Relationen)
Falls Attribute mit gleichem Namen aus unterschiedlichen Tabellen in einer
in einer Anweisung
• Falls Attribute mit
benötigt werden, müssen diese qulifiziert werden: Tabellennamen als Präfix
• Beispiele: Personal.Geb_Dat, Projekt.Standort
• In einigen Anfragen benötigt man eine Tabelle mehrfach; in diesem Fall (und für
g
p
abkürzende Schreibweisen werden Aliase vergeben: Beispiel
• Q8: “Gib mir Name und Name des direkten Vorgesetzten eines Mitarbeiters”
Q8: SELECT P.V_Name, P.N_Name, V.V_Name, V.N_Name
FROM Personal (AS) P V
( )
WHERE P.Vorg=V.P_ID
• In Q8 sind die Tabellennamen P und V Aliase oder auch Tupelvariablen für die ;
p
unterschiedliche Kopien
p P die Personal‐Tabelle; P und V repräsentieren
Untergebenen, V die Vorgesetzten, AS ist optional
9. Prof. Jasper: Datenbanksysteme
11
SQL: Anfragen (Queries)
• Einfache Anfragen (Forts.)
– leere WHERE‐Klausel:
• FFalls keine
ll k i Bedingung
B di
(WHERE Kl
(WHERE‐Klausel) existiert, sind
l) i ti t i d alle
ll Tupel
T l für
fü das Ergebnis
d E b i
qualifiziert
• Entspricht WHERE TRUE
• Beispiel: “ Gib mit die P_IDs aller Mitarbeiter”
• Q
Q9: SELECT P_ID
FROM Personal;
• Falls
Falls mehrere
mehrere Tabellen in der
in der FROM‐Klausel
FROM Klausel spezifiziert sind (ohne WHERE‐Klausel) WHERE Klausel)
wird das kartesische Produkt ausgegeben
• Beispiel:
• Q10: SELECT P_ID, Abt_Name
Q10 S L C
I Ab
FROM Personal, Abteilung;
• Problem: Sehr große Antwortmengen möglich!
9. Prof. Jasper: Datenbanksysteme
12
SQL: Anfragen (Queries)
• Einfache Anfragen (Forts.)
– *:
• Um alle
Um alle Attribute der
Attribute der spezifizierten Tabellen auszugeben wird * benutzt:
benutzt:
• Beispiele:
• Q1C: SELECT *
FROM Personal
WHERE Abt_Nr=5;
• Q1D: SELECT *
FROM Personal, Abteilung
WHERE Abt_Name=‘Forschung' AND Abt_Nr=Abt_ID;
– DISTINCT:
• SQL behandelt
Q
Tabellen nicht als Mengen; Duplikate
g ; p
können auftreten
• Um Duplikate in der Antwort zu vermeiden wird DISTINCT genutzt: Beispiele
mit/ohne Duplikate:
• Q11: SELECT Gehalt
FROM Personal
•
Q11A: SELECT DISTINCT Gehalt
FROM Personal
9. Prof. Jasper: Datenbanksysteme
13
SQL: Anfragen (Queries)
• Mengenoperationen
SQL hat Mengenoperationen eingebaut
UNION (und ein einigen Versionen von SQL MINUS und INTERSECT)
( d i i i
V i
SQL MINUS d INTERSECT)
Ergebnisse sind immer Mengen, Duplikate werden eliminiert
Mengenoperationen nur auf vereinigungsverträglichen Tabellen: hier
Mengenoperationen nur auf vereinigungsverträglichen Tabellen: hier identische Attribute und diese in der gleichen Reihenfolge spezifiziert!
– Beispiel: „Suche alle Projektnummern der Projekte, in denen ein Mitarbeiter mit Nachnamen Müller mitarbeitet oder die zu einer Abteilung gehören, die it N h
Müll
it b it t d di
i
Abt il
hö
di
von einem Mitarbeiter Müller geleitet werden“
– Q4: (SELECT P_Name
FROM Projekt, Abteilung, Personal
WHERE Abt=Abt_ID AND Abt_Leiter=P_ID AND N_Name = ‘Müller’)
UNION
(SELECT P_Name
FROM Projekt P, Arbeitet für A, Personal Pers
WHERE P Proj ID=A
WHERE P.Proj_ID
A.Proj_ID
Proj ID AND P.P_ID AND P P ID = Pers.P_ID
Pers P ID AND
N_Name = ‘Müller’)
–
–
–
–
9. Prof. Jasper: Datenbanksysteme
14
SQL: Anfragen (Queries)
• Geschachtelte Anfragen:
G h h l A f
– Ein SELECT‐Statement kann als geschachtelte Subanfrage in einer WHERE‐Klausel der sogenannten äußeren Anfrage genutzt werden
– Schachtelung ist oft eine Alternative zu Anfragen; Beispiel
S h ht l
i t ft i Alt
ti
A f
B i i l
– Q1: SELECT
V_Name, N_Name, Adr
FROM Personal
WHERE
Abt Nr IN (SELECT Abt_ID
Abt_Nr
IN (SELECT Abt ID
FROM Abteilung
WHERE Abt_Name=‘Forschung' )
– IN entspricht dem Element‐Operator erweitert auf Multimengen
– Anfragen können beliebig tief geschachtelt werden
– Attribute werden möglichst früh (am weitesten innen) zugeordnet
– Korrellierte (Sub‐)Anfragen sind dadurch definiert, dass Attribute über Anfragegrenzen hinweg genutzt werden
– Beispiel: “Name der Mitarbeiter mit Angehörigen mit identischem Vornamen
– Q12: SELECT P.V_Name, P.N_Name
FROM P
FROM Personal AS P
l AS P
WHERE P.P_ID IN (SELECT P_ID
FROM Angehörige
_
_
_
_
)
WHERE P_ID = P.P_ID AND P.V_Name=An_Name)
9. Prof. Jasper: Datenbanksysteme
15
SQL: Anfragen (Queries)
• Geschachtelte Anfragen (Forts.):
– Jede geschachtelte Anfrage, die nur IN oder „=„ Operatoren in der Schachtelung nutzt kann durch eine einfache (nicht geschachtelte) SWF
Schachtelung nutzt kann durch eine einfache (nicht geschachtelte) SWF‐
Klausel ausgedrückt werden
– Beispiel (s.o.):
– Q12A: SELECT P.V_Name, P.N_Name
FROM Personal P, Angehörige A
WHERE P.P ID=A.P
WHERE P.P_ID
A.P_ID AND P.V_Name
ID AND P.V Name=A.An
A.An_Name;
Name;
– EXISTS prüft, ob das Ergebnis einer korrelierten Subanfrage leer ist oder nicht
– Beispiel (s.o.):
– Q12B : SELECT P.V_Name, P.N_Name
FROM Personal P
FROM Personal P
WHERE EXISTS (SELECT *
FROM Angehörige
WHERE P ID P P ID AND PV N
WHERE P_ID=P.P_ID AND P.V_Name=An_Name)
A N
)
9. Prof. Jasper: Datenbanksysteme
16
SQL: Anfragen (Queries)
• Geschachtelte Anfragen (Forts.):
– „Namen der Mitabeiter ohne Angehörige“
– Q6: SELECT V_Name, N_Name
Q6 SELECT V N
N N
FROM Personal P
WHERE NOT EXISTS (SELECT *
FROM Angehörige
WHERE P_ID=P.P_ID)
– EXISTS ist
EXISTS ist notwendig für die Ausdrucksmächtigkeit
die Ausdrucksmächtigkeit von SQL!
von SQL!
– Explizite
p
Mengen
g ((Aufzählungen)
g )
– Statt Schachtelung können auch explizite Aufzählungen (Mengen) in einer WHERE‐Klausel genutzt werden:
– Q13: SELECT DISTINCT P_ID
FROM Arbeitet_für
WHERE
WH
R Proj_I
Proj ID IN
IN (1, 2, 3)
( , , 3)
9. Prof. Jasper: Datenbanksysteme
17
SQL: Anfragen (Queries)
• Anfragen und NULL
– In SQL‐Anfragen kann auf NULL‐Wert geprüft werden
– Hier ist IS
Hi i t IS oder
d IS NOT
IS NOT als
l Operator zu
O
t
nutzen
t
(NULL W t sind
(NULL‐Werte
i d
unterschiedlich, “=“ geht nicht!)
– Beispiel: “Alle
p
Mitarbeiter ohne Vorgesetzten”
g
– Q14: SELECT V_Name, N_Name
FROM Personal
WHERE Vorg IS NULL
WHERE Vorg
IS NULL
– Achtung: Bei Join‐Bedingungen werden NULL‐Werte nicht berücksichtigt!
9. Prof. Jasper: Datenbanksysteme
18
SQL: Anfragen (Queries)
• Joins
J i
– In der FROM‐Klausel kann ein JOIN definiert werden
– Sieht wie jede andere Tabelle aus
– Verschieden Joins können spezifiziert werden ("theta" JOIN, NATURAL JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, CROSS JOIN, etc)
– Beispiele:
• Q8
Q8: SELECT
SELECT
PV N
P.V_Name, P.N_Name, V.V_Name, V.N_Name
PN N
VV N
VN N
FROM Personal P V
WHERE P.Vorg=V.P_ID
kann geschrieben werden als
• Q8: SELECT
P.V_Name, P.N_Name, V.V_Name, V.N_Name
FROM (Personal P LEFT OUTER JOIN Personal V ON P.Vorg=V.P_ID)
• Q1
Q1 (s.o.)
(s o )
• Q1: SELECT
FROM WHERE
oder
d als
l
• Q1: SELECT
FROM WHERE
V_Name, N_Name, Adr
(Personal JOIN Abteilung ON Abt_Nr=Abt_ID)
Abt_Name=‘Forschung’
V_Name, N_Name, Adr
(Personal NATURAL JOIN Abteilung AS Abteilung(Name, Abt_Nr, AL, ALS)
Name=‘Forschung’
9. Prof. Jasper: Datenbanksysteme
19
SQL: Datendefinition
• Aggregationen COUNT, SUM, MAX, MIN, und AVG
– “Maximales, minimales und durchschnittliches Gehalt der Mitarbeiter”
– Q15: SELECT MAX(SALARY), MIN(SALARY), AVG(SALARY)
FROM Personal
FROM Personal
– In machen DBMS nur eine Aggregationsfunktion nach SELECT erlaubt!
– Anzahl aller Mitarbeiter und Anzahl der Mitarbeiter in der
Forschungsabteilung:
– Q17: SELECT COUNT (*)
Q17:
SELECT COUNT (*)
FROM Personal
– Q18: SELECT COUNT (*)
FROM Personal, Abteilung
WHERE Abt_Nr=Abt_ID AND Abt_Name=‘Forschung’
9. Prof. Jasper: Datenbanksysteme
20
SQL: Datendefinition
• Gruppierung
G
i
– Vielfach möchte man die Aggregationsfunktion auf Untergruppen der Tupelmenge
anwenden
– Jede Untergruppe wird definiert über identische Werte bezüglich eines oder J d U t
i d d fi i t üb id ti h W t b ü li h i
d
mehrerer Gruppierungsattribute
– Gruppierung geht über GROUP BY auf Attribute die auch selektiert werden müssen
– Beispiel: Gib mir Nummer, Anzahl Mitarbeiter und deren Durchschnittsgehalt für j
jede Abteilung
g
– Q20: SELECT Abt_Nr , COUNT (*), AVG (Gehalt)
FROM Personal
GROUP BY Abt_Nr
Gr ppen bilden sich
Gruppen
si h her aufgrund
her a f r nd identischer
identis her Abt_Nr
Abt Nr
– Beispiel: Gib mir für jedes Projekt dessen Nr und Name sowie die Anzahl
Mitarbeiter im Projekt
– Q21: SELECT P.Proj_ID, P_Name, COUNT (*)
FROM Projekt P, Arbeitet_für A
j_
j_
WHERE P.Proj_ID=A.Proj_ID
GROUP BY P.Proj_ID, P_Name
9. Prof. Jasper: Datenbanksysteme
21
SQL: Datendefinition
• Group by having
– Ergänzend können Gruppen, über die aggregiert werden soll, durch
Bedingungen ausgewählt werden
– Die Having‐Klausel selektiert Gruppen (statt individuelle Tupel)
– Beispiel: Für jedes Projekt mit mehr als zwei Mitarbeitern gib mir dessen Nr und Name sowie die Anzahl Mitarbeiter im Projekt
– Q21: SELECT P.Proj_ID, P_Name, COUNT (*)
Q21
SELECT
P P j ID P N
COUNT (*)
FROM Projekt P, Arbeitet_für A
WHERE P.Proj_ID=A.Proj_ID
GROUP BY P.Proj_ID, P_Name
HAVING COUNT(*) >2
9. Prof. Jasper: Datenbanksysteme
22