Seminar
Einf ¨uhrung in SQL mit Oracle
von
Prof. Dr. Rainer Schwenkert Hochschule M ¨unchen
Themenbereiche
• SQL-Historie
• Wichtige DDL- und DML-Anweisungen
• Der Select-Befehl
• Spezialthemen
Die standardisierte Sprache SQL
Was ist SQL?
SQL ist eine Abk ¨urzung f ¨ur Structured Query Language.
SQL ist eine Norm-Datenbanksprache (ANSI und ISO) zur Definition, Kontrol- le, Abfrage und Manipulation von Daten in (objekt)relationalen Datenbanksyste- men.
Durch seine Rolle als Quasi-Standard ist SQL sehr stark verbreitet, da eine gewisse Unabh ¨angigkeit vom benutzten Datenbankmanagementsystem erzielt werden kann.
SQL wurde Mitte der 70er Jahre im Rahmen des Projekts “System R“ (ers- te lauff ¨ahige relationale Datenbank) von IBM unter der Abk ¨urzung SEQUEL (Structured English Query Language) entwickelt.
Die standardisierte Sprache SQL
Historische Entwicklung des Standards
• 1987: SQL0 ist erster Standard ¨uberhaupt, ohne Konzepte zur Integrit ¨atssi- cherung;
• 1989: SQL1 mit Integrit ¨atserweiterungen, jedoch viele Schw ¨achen bei pra- xisrelevanten Aufgabenstellungen;
• 1992: SQL2 bzw. SQL-92 genannt, deutliche Verbesserung von SQL1;
• 1999: SQL3 bzw. SQL-99 Erweiterung von SQL2 um prozedurale Elemente und objektorientierte Ans ¨atze;
• 2003: SQL4 bzw. SQL:2003: Integration von XML in SQL;
Die standardisierte Sprache SQL
Der SQL3-Standard Typischer heutiger Dialekt in Produkten:
SQL3 + propriet ¨are Erg ¨anzungen
Der SQL3-Standard trat am 15. November 1999 in Kraft. SQL3 definiert einen Sprachkern, das sog. Core-SQL als minimalen Sprachumfang. Diesen Kern muss jede standardkonforme Implementierung von SQL3 umfassen.
Wir werden in diesem Seminar wesentliche Aspekte von Core-SQL kennen ler- nen, dar ¨uber hinaus einige wichtige Oracle-Besonderheiten.
Die standardisierte Sprache SQL
Unterschied von SQL zu Programmiersprachen SQL ist im Gegensatz zu g ¨angigen Programmiersprachen
• mengenorientiert: das Ergebnis einer Abfrage ist stets eine Menge von Tupeln (diese kann auch leer sein oder aus nur einem Element bestehen), also wieder eine Tabelle;
• deskriptiv: der Anwender formuliert lediglich die gew ¨unschten Eigenschaf- ten der Datens ¨atze (logische Beschreibung) und erh ¨alt eine Antwort ohne Kenntnis der inneren Struktur der Datenbank.
Die standardisierte Sprache SQL
Nachteile von SQL
• Verschiedene DBS-Hersteller verwenden verschiedene Standards und zus ¨atz- lich eigene Erweiterungen.
• Integrit ¨atspr ¨ufungen sind in den meisten SQL-Implementierungen nicht in ausreichendem Umfang realisiert.
• SQL ist redundant, d.h. ein und dieselbe Abfrage l ¨asst sich oft auf mehrere Weisen formulieren, ohne dass der Anwender weiß, welche Art die schnel- lere, effektivere ist.
• SQL l ¨aßt das Auftreten von sog. uneigentlichen Relationen (Multisets), d.h.
Tabellen mit mehrfachen Tupeln (Duplikaten) zu. Dadurch wird die Effizienz der Abfrageoptimierung gemindert.
Die standardisierte Sprache SQL
Vereinbarungen zur Syntax-Beschreibung
Zun ¨achst wird immer die allgemeine Form der Kommandos vorgestellt. Dann bauen wir eine Beispieldatenbank auf. Dabei werden die Befehle strukturiert und
¨ubersichtlich angegeben, obwohl SQL formatfrei ist und nicht zwischen Groß- und Kleinschreibung unterscheidet.
Bei der Syntaxdefinition werden wir Schl ¨usselworte groß schreiben, benutzer- spezifische Angaben hingegen klein.
Optionale Teilausdr ¨ucke sind in eckige Klammern ([. . .]) gesetzt, diese Klam- mern sind kein Bestandteil der SQL-Syntax.
Der senkrechte Strich | wird zur Trennung von Alternativen verwendet.
Die DDL - Data Definition Language
Die DDL - Aufbau einer Datenbank
Wir wollen hier die DDL-Befehlsklasse von SQL, zu der die Befehle
• CREATE/DROP TABLE: Erzeugen/L ¨oschen von Tabellen
• ALTER TABLE: ¨Anderung des Tabellenaufbaus
• CREATE/DROP INDEX: Index f ¨ur Tabellen anlegen/l ¨oschen
• CREATE/DROP VIEW: Erzeugen/L ¨oschen einer virtuellen Tabelle (Sicht) geh ¨oren, vorstellen.
Die DDL - Data Definition Language
Die Beispieldatenbank
ABT
ABTNR NAME
AN Ausland Nord AS Ausland S ¨ud AW Ausland West
ABTL
ABTNR PNR
AN 111
AS 222
AW 333
PERS
PNR NAME VORNAME ABTNR GEHALT
100 Stocker Manfred AN 3
500 M ¨uller Andreas AW 4
111 Meier Stefan AN 5
102 Huber Herbert AS 7
222 Stoer Michael AS 3
333 Wenzel Ludger AW 6
112 J ¨ager Heinz AN 5
444 M ¨uller Stefan AW 6
Die DDL - Data Definition Language
Die Beispieldatenbank
PKFZZ
PNR KFZNR FAEHIGKEIT
100 1 gut
100 2 mittel
100 3 mittel
500 1 schlecht
111 2 mittel
111 3 gut
102 1 schlecht
222 1 gut
333 2 gut
112 3 schlecht
KFZ
KFZNR NAME
1 LKW
2 Kran
3 Bagger
Die DDL - Data Definition Language
Definition von Tabellen
F ¨ur jede in die Datenbank aufzunehmende Tabelle muss nun eine Tabelle er- zeugt werden:
CREATE TABLE Tabellenname
(Spaltenname_1 Datentyp_1, Spaltenname_2 Datentyp_2,
...
Spaltenname_n Datentyp_n);
Die DDL - Data Definition Language
Definition von Tabellen – Datentypen Die wichtigsten standardisierten SQL-Datentypen sind:
ANSI-Datentyp Erl ¨auterung Oracle-Datentyp
SMALLINT ganze Zahl mit Vorzeichen (klein)
INT,INTEGER ganze Zahl mit Vorzeichen NUMBER(38)
DEC[IMAL](m,n) Dezimalzahl mit mindestens m Stellen,
davon n Nachkommastellen NUMBER(n,m) NUMERIC(m,n) Dezimalzahl mit exakt m Stellen,
davon n Nachkommastellen NUMBER(n,m) CHAR[ACTER](n) Zeichenkette (String) mit fester L ¨ange n CHAR(n)
CHAR[ACTER] VARYING(n) Zeichenkette (String) mit variabler L ¨ange bis n VARCHAR2(n)
DATE Datum (JJJJ-MM-TT) DATE
Die DDL - Data Definition Language
Definition der Beispieldatenbank
CREATE TABLE Abt CREATE TABLE Abtl (Abtnr CHAR(2), (Abtnr CHAR(2),
Name VARCHAR2(14)); Pnr NUMBER(4));
CREATE TABLE Pers CREATE TABLE Pkfzz
(Pnr NUMBER(4), (Pnr NUMBER(4),
Name CHAR(14), Kfznr NUMBER(2), Vorname CHAR(10), Faehigkeit CHAR(8));
Abtnr CHAR(2));
CREATE TABLE Kfz
(Kfznr NUMBER(2),
Name VARCHAR2(6));
Die DDL - Data Definition Language
Modifikation von Tabellen ALTER TABLE Tabellenname
ADD|DROP (Spaltenname_1 Datentyp_1, Spaltenname_2 Datentyp_2, ...
Spaltenname_n Datentyp_n);
Unsere Verbesserung lautet nun:
ALTER TABLE Pers
ADD (Gehalt NUMBER(1));
Steht im ALTER-Befehl anstelle der ADD- eine DROP-Klausel, so werden die dort spezifizierten Spalten gel ¨oscht.
Die DDL - Data Definition Language
Modifikation von Tabellen ALTER TABLE Tabellenname
MODIFY (Spaltenname_1 Datentyp_1, ...
Spaltenname_n Datentyp_n);
• Datentyp ¨anderungen (z.B. NUMBER zu CHAR(n)) sind nur erlaubt falls die betreffende Spalte leer ist.
• Anderung der L ¨ange von VARCHAR2-Spalten ist jederzeit m ¨oglich.¨ ALTER TABLE Kfz
MODIFY (Name VARCHAR2(8));
Die DDL - Data Definition Language
Definition eines Index
Ein Index ist eine physikalische Zugriffsstruktur. Er erleichtert den Zugriff auf Zeilen ¨uber Bedingungen, welche die indexierten Attribute betreffen.
CREATE [UNIQUE] INDEX Indexname ON Tabellenname
(Spaltenname_1 [ASC|DESC], ...
Spaltenname_n [ASC|DESC]);
• Das Befehlswort UNIQUE erstellt einen eindeutigen Index. Eindeutigkeit wird stets vom System gepr ¨uft (eventl. werden Einf ¨uge- bzw. ¨Anderungsvorg ¨ange nicht ausgef ¨uhrt.
• ASC bzw. DESC legen die Sortierreihenfolge (auf- bzw. absteigend) fest, keine Angabe entspricht der Standardvorgabe ASC.
Die DDL - Data Definition Language
Definition eines Index
Wir indexieren nun die Personaltabelle mit einem Index “Persnr“ (aufsteigend sortiert):
CREATE UNIQUE INDEX Persnr ON Pers (Pnr ASC);
Da die Pflege eines Index aufwendig ist (Updates der Basisrelation, Speicherbe- darf), sollte man den Index l ¨oschen, sobald er nicht mehr ben ¨otigt wird:
DROP INDEX Persnr;
Die DML - Data Manipulation Language
Die DML - Datenbankabfragen und -manipulationen Zur DML-Befehlsklasse von SQL geh ¨oren die folgenden Befehle:
• SELECT: Tabellen abfragen
• DELETE: Datens ¨atze (Zeilen) einer Tabelle l ¨oschen
• INSERT: Datens ¨atze (Zeilen) in eine Tabelle einf ¨ugen
• UPDATE: Daten (Zeilen, Spaltenwerte) in einer Tabelle ver ¨andern
Beim Abfragen von Tabellen werden nat ¨urlich keine Daten manipuliert. Dennoch z ¨ahlt der SELECT-Befehl zur DML.
Die DML - Data Manipulation Language
Der INSERT-Befehl – Eingabe von Tabellenzeilen INSERT INTO Tabellenname
[(Spaltenname_1, Spaltenname_2, ..., Spaltenname_n)]
VALUES
(Wert_1, Wert_2, ..., Wert_n);
• Wird die bei der Tabellendefinition angegebene Spaltenreihenfolge einge- halten, so kann die Aufz ¨ahlung der Spaltennamen entfallen.
• Die Reihenfolge der Eingabewerte muss der vereinbarten Spaltenreihenfol- ge entsprechen.
• Alphanumerische Daten m ¨ussen in einfache Hochkommata (’. . .’) einge- schlossen werden.
Die DML - Data Manipulation Language
Eingabe von Tabellenzeilen – F ¨ullen der Beispiel-DB INSERT INTO Pers
(Pnr, Vorname, Name, Gehalt, Abtnr) VALUES
(100, ’Manfred’, ’Stocker’, 3, ’AN’);
Bei Einhaltung der Spaltenreihenfolge aus dem CREATE TABLE-Befehl (GE- HALT nach ABTNR) gen ¨ugt auch:
INSERT INTO Pers VALUES
(500, ’M¨uller’, ’Andreas’, ’AW’, 4);
Die DML - Data Manipulation Language
Der INSERT-Befehl – Zweite Form
Sind die in die Tabelle zu ¨ubernehmenden Daten bereits in anderen Tabellen vorhanden, dann kann auch mit dem SELECT-Befehl gearbeitet werden:
INSERT INTO Tabellenname
[(Spaltenname_1, Spaltenname_2, ..., Spaltenname_n)]
SELECT-Befehl;
Beispiele folgen sp ¨ater. Oracle sieht weitere M ¨oglichkeiten zum Datenimport (SQLLoader, CSV-Import) aus externen Dateien vor.
Die DML - Data Manipulation Language
Datenbankabfragen – Der SELECT-Befehl Prinzipielle Funktionsweise des SELECT-Befehls:
FROM WHERE SELECT Was ?
Woher ? Wann ?
<Attributliste>
<Tabellenliste>
<Bedingung(en)>
Resultatsattribute angefragte Tabellen Bedingung(en) an die Resultatstupel
Die DML - Data Manipulation Language
Datenbankabfragen – Der SELECT-Befehl Eine vereinfachte Syntax des SELECT-Befehls lautet:
SELECT [ALL | DISTINCT] Spaltenliste
FROM Tabellenname_1, Tabellenname_2, ..., Tabellenname_n WHERE Bedingung | Subquery
GROUP BY Spaltenliste
HAVING Bedingung | Subquery
ORDER BY Spaltenliste [ASC | DESC];
Die einzelnen Teile des SELECT-Befehls nennt man Klauseln.
Die DML - Data Manipulation Language
Formale Regeln f ¨ur die Reihenfolge der Klauseln
• Die SELECT-Klausel muss am Anfang stehen, danach kommt die FROM- Klausel.
• Alle Klauseln bis auf die SELECT- und FROM-Klausel k ¨onnen weggelassen werden.
• Die WHERE-Klausel hat stets nach der FROM-Klausel zu erscheinen.
• Die HAVING-Klausel darf nur in Verbindung mit einer vorangestellten GROUP BY-Klausel eingesetzt werden.
• Die ORDER BY-Klausel muss am Ende stehen.
Die DML - Data Manipulation Language
Der SELECT-Befehl – Einfache Beispiele
Den gesamten Inhalt einer Tabelle (z.B. der Tabelle PKFZZ) kann man sich mit- tels “∗“ in der SELECT-Klausel anzeigen lassen. M ¨ochte man nur bestimmte Spalten der Tabelle sehen, so gibt man deren Namen an:
SELECT * SELECT Pnr, Kfznr FROM Pkfzz; FROM Pkfzz;
Jede Tabelle hat eine Pseudospalte, die intern vom System vergeben wird. Da- mit ist jede Tabellenzeile eindeutig identifizierbar. Die Spalte sieht man nur durch explizite Angabe des Spaltennamens “ROWID“.
SELECT ROWID, Pnr, Kfznr FROM Pkfzz;
Die DML - Data Manipulation Language
Die WHERE-Klausel an Beispielen
• “Wer arbeitet in der Abteilung AN ?“
SELECT Name, Vorname FROM Pers WHERE Abtnr=’AN’;
• “Suche die Vornamen aller Mitarbeiter, die in AN oder AW arbeiten und nicht Stocker heißen.“
SELECT Vorname FROM Pers
WHERE (Abtnr=’AN’ OR Abtnr=’AW’) AND NOT Name=’Stocker’;
