SQL: Weitere Funktionen - Bildungsportal Sachsen

SQL: Weitere Funktionen
• Vergleich auf Zeichenketten
– LIKE ist ein Operator mit dem in Zeichenketten andere Zeichenketten gesucht werden; zwei reservierte Zeichen mit besonderer Bedeutung sind hier
werden; zwei reservierte Zeichen mit besonderer Bedeutung sind hier • % (manchmal *) für eine beliebige Zeichenfolge
• _ für ein einzelnes, beliebiges Zeichen
– Beispiel: Adresse sei ein String; suche alle Mitarbeiter aus Freiberg
– Q25: SELECT V_Name, N_Name
FROM Personal
FROM Personal
WHERE Adr LIKE ‘%Freiberg%’
– ACHTUNG: LIKE hebt im Prinzip die Atomarität der Attributwerte auf!
10. Prof. Jasper: Datenbanksysteme
1
SQL: Weitere Funktionen
• Arithmetik
– Übliche arithmetische Operatoren '+', '‐'. '*', and '/' können auf die Ergebniswerte
angewandt werden:
– Q27: SELECT V_Name, N_Name, 1.1*Gehalt
FROM Personal, Projekt, Arbeitet_für
WHERE Arbeitet_für.P_ID=Personal.PID AND Projekt.Proj_ID=Arbeitet_für.Proj_ID
AND P_Name='ProduktX’
• Sortierung
– ORDER BY sortiert die Ergebnistupel einer Anfrage entsprechend der Werte
spezifizierter Attribute (ASC aufsteigen (default) und DESC absteigend).
– Beispiel: Gib
p
alle Abteilungsname, alle
g
,
Mitarbeiternamen und die zugeordneten
g
Projekte sortiert nach Abteilung und nach Nachnamen.
– Q28: SELECT Abt_Name, V_Name, N_Name, P_Name
,
,
_ Ar, Projekt
,
j
Pr
FROM Abteilungg Ab, Personal P, Arbeitet_für
WHERE Ab.Abt_ID=P.Abt_Nr
AND P.P_ID=Ar.P_ID
AND Ar.Proj_ID=Pr.Proj_ID
ORDER BY Abt_Name, N_Name
b
10. Prof. Jasper: Datenbanksysteme
2
SQL: Zusammenfassung SWF‐Klauseln
• Jede Abfrage in SQL kann aus sechs Teilen bestehen, wobei die ersten beiden vorhanden sein müssen. Sie werden in der folgenden Reihenfolge spezifiziert:
Reihenfolge spezifiziert:
– SELECT
FROM
[WHERE
[GROUP BY
[HAVING
[ORDER BY
<attribute list>
<table list>
<condition>]
<grouping attribute(s)>]
<group condition>]
<group condition>]
<attribute list>]
• Jede Anfrage wird in der folgenden Reihenfolge bearbeitet
– 1. WHERE‐Klausel
– 2. GROUP BY und HAVING‐Klausel
– 3. Sortierung
3 S ti
und SELECT‐Klausel
d SELECT Kl
l
10. Prof. Jasper: Datenbanksysteme
3
SQL: Datenmanipulation: INSERT, UPDATE, DELETE
• INSERT
– Fügt ein oder mehrere Tupel zu einer Tabelle hinzu
– Attributwerte sollten dabei in der gleichen Reihenfolge auftreten wie die Att ib t
t
llt d b i i d
l i h R ih f l
ft t
i di
zugehörigen Attribute im CREATE TABLE‐Statement
– Beispiel:
p
– U1: INSERT INTO Personal
VALUES (‘Bernd', 'Müller', '62446929', ‘30‐09‐1966',
'Leipziger
Leipziger Str. 56, 09599 Freiberg
Str 56 09599 Freiberg', 'M'
M , 47600,
47600 '98765421'
98765421 , 4 )
4)
– Alternativ können die Attribute explizit spezifiziert werden, wobei die üblichen Bedingungen eingehalten und NULL‐Werte nicht angegeben werden müssen:
– Beispiel
– U1A: INSERT INTO Personal (V_Name, N_Name, P_ID)
VALUES (‘Bernd', 'Müller', '62446929‘)
10. Prof. Jasper: Datenbanksysteme
4
SQL: Datenmanipulation: INSERT
•
INSERT (Forts.)
– Alle und nur die über DDL definierte Constraints werden automatisch vom DBMS überwacht!
– Mit
i INSERT kann
S
k
auch
h das Ergebnis
d
b i einer
i
Anfrage
f
zu einer
i
Tabelle
b ll hinzugefügt
hi
fü werden.
d
– Wenn z.B. eine (temporäre) Tabelle mit dem Namen, der Anzahl der Mitarbeiter und der
Gehaltssumme einer jeden Abteilung gefüllt werden soll, kann dieses wie folgt durchgeführt
werden:
– U3A: CREATE TABLE Abt_Info
(Abt_Name
VARCHAR(10),
Anz_Mitarb
INTEGER,
Sum_Gehalt
INTEGER);
– U3B
U3B: INSERT INTO
INSERT INTO Abt_Info
Abt I f (Abt_Name, Anz_Mitarb, Sum_Gehalt)
(Abt N
A Mit b S
G h lt)
SELECT Abt_Name, COUNT (*), SUM (Gehalt)
FROM Abteilung, Personal
WHERE Abt_ID=Abt_Nr
GROUP BY Abt_Name;
•
Achtung: In Abt_Info befindet sich der Zustand der DB zum Zeitpunkt der Anfrage!
10. Prof. Jasper: Datenbanksysteme
5
SQL: Datenmanipulation: UPDATE
• UPDATE
Mit UPDATE werden Attributwerte von einem oder mehreren Tupeln geändert
Mit i
Mit einer WHERE‐Klausel werden die entsprechenden Tupel
WHERE Kl
l
d di
t
h d T l selektiert
l kti t
Eine SET‐Klausel spezifiziert das zu ändernde Attribut und dessen neuer Wert
Ein Update wirkt auf genau der einen angegeben Tabelle
Ein Update wirkt auf genau der einen angegeben Tabelle
Beispiel: Ändere Standort und zugeordnete Abteilung für das Projekt „Supertool“:
– U5: UPDATE Projekt
SET Standort= ‘Dresden', Abt = 5
WHERE Projektname=‘Supertool’
–
–
–
–
–
10. Prof. Jasper: Datenbanksysteme
6
SQL: Datenmanipulation: UPDATE
• UPDATE (Forts.)
– Durch Update können auch neue Werte auf der Basis existierender Werte in eine Tabelle eingetragen werden
eine Tabelle eingetragen werden.
– Beispiel: Erhöhe das Gehalt aller Mitabeiter der Forschungsabteilung um 10%:
– U6: UPDATE Personal
SET Gehalt = Gehalt *1.1
WHERE Abt_Nr IN (SELECT Abt_ID
FROM Abteilung
FROM Abteilung
WHERE Abt_Name =‘Forschung')
– Der neue „Gehalt“‐Wert basiert hier auf dem bisher eingetragenen Wert
– Auf der rechten Seite des „=„‐Zeichens wird der alte Wert referenziert
– Auf der linken Seite des „=„‐Zeichens wird der neue Wert referenziert
10. Prof. Jasper: Datenbanksysteme
7
SQL: Datenmanipulation: DELETE
• DELETE
– Löscht Tupel aus einer Tabelle
• Die zu löschenden Tupel
p werden durch eine WHERE‐Klausel bestimmt
• Die Referentielle Integrität wird berücksichtigt
• Tupel werden nur aus einer Tabelle (pro Statement) gelöscht (bis auf die Tupel anderer Tabellen, die durch CASCADE betroffen sind)
• Falls keine WHERE‐Klausel angegeben wird, werden alle Tupel gelöscht: Die Tabelle ist anschließend leer
• Beispiele:
U4A
U4A:
DELETE FROM Personal
DELETE
FROM P
l
WHERE N‐Name=‘Müller’
U4B:
DELETE FROM Personal
WHERE P_ID='123456789’
U4C:
DELETE FROM Personal
WHERE Abt_Nr
Abt Nr IN (SELECT
IN (SELECT Abt_ID
Abt ID
FROM Abteilung
WHERE Abt_Name = ‘Forschung’)
U4D:
DELETE FROM Personal
10. Prof. Jasper: Datenbanksysteme
8
SQL: Fortgeschrittene Konzepte
• Views
– Eine View (Sicht) ist eine virtuelle Tabelle, die aus anderen Tabellen abgeleitet wird
– Auf Sichten können eingeschränkt updates durchgeführt werden
– In Anfragen können Sichten wie alle anderen Tabellen genutzt werden
g
g
– Somit auch als „vordefinierte Standard‐Abfragen“ in DBS nutzbar
– SQL Befehl: CREATE VIEW
• Ein Tabellen (Sichten‐) Name
• Eine optionale Liste von Attributnamen (z. B. um neue Name für Attribute zu vergeben oder Namen für abgeleitete Attribut zu vereinbaren
• Eine SQL‐Abfrage um den Inhalt der Sicht (View) zu definieren
– Beispiel: Erweiterte „Arbeitet_Für“‐Tabelle
– CREATE VIEW Arbeitet_Für_Erw
CREATE VIEW Arbeitet Für Erw AS
SELECT V_Name, N_Name, P_Name, Stunden
FROM Personal Pers, Projekt Pr, Arbeitet_Für A
WHERE Pr.Proj_ID=A.Proj_ID AND Pers.P_ID=Pr.P_ID
10. Prof. Jasper: Datenbanksysteme
9
SQL: Fortgeschrittene Konzepte
• Views (Forts.):
– Die Sicht kann in üblichen Abfragen genutzt werden, Beispiel:
– SELECT V_Name, N_Name
SELECT V N
N N
FROM Arbeitet_Für_Erw
WHERE P_Name = ‚Supertool‘;
– Sobald nicht mehr benötigt, können VIEWs gelöscht werden:
– DROP Arbeitet_Für_Erw;
DROP A b it t Fü E
– Problem:
Problem: Effizienz
Effizienz
– Views als Abfrage: immer wenn auf eine View zugegriffen wird, berechne die Abfrage: Effizienzprobleme bei Abfragen
– Materialisierte Sicht (materialized view): speichere eine physikalische Tabelle und halte sie up‐to‐date: Effizienzprobleme bei Updates (sogenannte Folgeupdates)
10. Prof. Jasper: Datenbanksysteme
10
SQL: Fortgeschrittene Konzepte
• Views (Forts.): Hier (VIEW‐) UPDATES
– Updates auf eine Sicht, die auf einer einzelnen Tabelle ohne Aggregatfunktionen definiert ist: Änderungen können direkt auf die
Aggregatfunktionen definiert ist: Änderungen können direkt auf die Basistabelle ausgeführt werden (falls genügend Informationen vorhanden um Constraints zu erfüllen)
– Updates auf Sichten, die über ein Join gebildet wurden: dieses kann möglich sein, i.a. allerdings nicht möglich
– Auf Views, die Gruppierung und/oder Aggregationen beinhalten, kann kein Auf Views, die Gruppierung und/oder Aggregationen beinhalten, kann kein
Update durchgeführt werden
10. Prof. Jasper: Datenbanksysteme
11
SQL: Zugriff auf SQL‐DB aus Programmen
• Ziel:
– Zugriff auf Daten aus Anwendungsprogrammen (im Gegensatz zum interaktiven SQL)
• Warum?
– Daten werden in Informationssystemen durch ungeübte (bzgl. SQL) Nutzer genutzt • Varianten:
– Eingebettete Befehle
• Eine Programmiersprache wird um SQL und Multimengen (Bags) ergänzt
• Beispiel PASCAL R
– Bibliothek von DB(MS)‐Funktionen
• CALL‐Funktion in Programmiersprache für Bibliotheksfunktionen
• Standard‐Bibliothek für SQL (DB‐API (Application Program Interface)
– Eine Neue „Full‐fledged“ Programmiersprache für DB‐Anwendungen (4th generation languages 4GL)
•
•
•
•
Minimiert den sogenannten „impedance mismatch“ (Unverträglichkeit)
Mengen vs. Tupel (Record) Verarbeitung
Iteratoren notwendig
Keine Mengenoperationen in konventionellen Programmiersprachen
10. Prof. Jasper: Datenbanksysteme
12