SQL

• Umfangreiche Datenmengen werden üblicherweise in
relationalen Datenbank-Systemen (RDBMS) gespeichert
• Logische Struktur der Datenbank wird mittels
Entity/Realtionship-Diagrammen
structured query dargestellt
language
SQL
• Manipulation und Abfrage der Daten erfolgt mittels SQL
Michael Kamleitner,
(Structured Query Language)
17.3.2003
•
•
Entwickelt anfang der 70er von E.F. Codd (IBM)
Aktueller Standard: ANSI-SQL-92 (bzw. –99)
• Am Markt etablierte Datenbanken
•
•
Oracle, Microsoft SQL-Server, IBM DB2...
...implementieren mehr oder weniger ANSI-SQL-92
• Umfangreiche Datenmengen werden üblicherweise in
Einleitung
E/R Diagramm
Hypersonic
SQL
Beispiel
relationalen Datenbank-Systemen (RDBMS) gespeichert
• Logische Struktur der Datenbank wird mittels
Entity/Realtionship-Diagrammen dargestellt
• Manipulation und Abfrage der Daten erfolgt mittels SQL
(Structured Query Language)
•
•
Entwickelt anfang der 70er von E.F. Codd (IBM)
Aktueller Standard: ANSI-SQL-92 (bzw. –99)
• Am Markt etablierte Datenbanken
•
•
Oracle, Microsoft SQL-Server, IBM DB2...
...implementieren mehr oder weniger ANSI-SQL-92
• Datenbanken bestehen aus Tabellen
Einleitung
E/R Diagramm
Hypersonic
SQL
Beispiel
•
Spalten→ Attribute, Felder
•
•
Zeilen → Datensätze
Primärschlüssel: ermöglicht eindeutige Identifikation eines
Datensatzes, darf also in einer Tabelle niemals doppelt
vorkommen
ID (Primärschlüssel)
1
2
3
4
Hersteller
VW
VW
Opel
Audi
Modell
Käfer
Golf 2
Manta
A4
Preis
9.100,11.200,12.300,21.400,-
• Kritik am Beispiel: entspricht nicht der Normalform!
•
Feld Hersteller ist nicht funktional abhängig vom Primärschlüssel
•
Folge: Ineffiziente Speicherung, Redundanz
•
Lösung: Aufteilen in zwei separate Tabellen
• Verknüpfung von Tabellen mittels Fremdschlüssel
Einleitung
E/R Diagramm
Hypersonic
Hersteller-ID
(Primärschlüssel)
1
2
3
Name
VW
Opel
Audi
SQL
Automobil-ID
1
2
1
1
{
Beispiel
Hersteller-ID
(Fremdschl.)
1
1
2
3
Primärschlüssel
Name
Käfer
Golf 2
Manta
A4
Preis
9.100,11.200,12.300,21.400,-
• Darstellung mittels Entity/Relationship Diagramm
Einleitung
Automobil
Hersteller
E/R-Diagramm
Hersteller-ID
Name
Hypersonic
SQL
Beispiel
1 : n
Automobil-ID
Hersteller-ID (FK)
Modellname
Preis
• Hypersonic-SQL
Einleitung
E/R-Diagramm
Hypersonic
•
einfach in Betrieb zu nehmende SQL-Datenbank
•
frei verfügbare Demo -Version
•
kommt im Orionserver zum Einsatz
•
Nachteil: Daten werden in der Demo-Version nicht dauerhaft
gespeichert und sind nach Beenden des Programms verloren!
SQL
Beispiel
• Freie (bzw. günstige) Alternativen: MySQL, postgreSQL,
Microsoft Access
• Installation:
Einleitung
•
http://javaboutique.internet.com/HypersonicSQL
E/R-Diagramm
Hypersonic
SQL
Beispiel
Download der Datei hsql_421.zip von
•
Enpacken der Datei bspw. in C:\HSQL
•
Aufruf von C:\HSQL\hypersonicsql\index.html
•
Klick auf „In-Memory Database Manager“
• Anlegen von Tabellen
Einleitung
•
Vor der ersten Verwendung muss die Struktur (Felder,
Datentypen) und die Beziehungen der Tabellen definiert werden
E/R-Diagramm
•
Syntax:
Hypersonic
SQL
CREATE TABLE Tabellenname
(
Feldname1
Feldtyp2 [Not Null],
Feldname1
Feldtyp1 [Not Null], ...
CONSTRAINT Schlüsselname1 PRIMARY KEY (Feldname1,...)
CONSTRAINT Schlüsselname2 FOREIGN KEY
(Feldname d. aktuellen Tab.)
REFERENCES Fremdtabelle
(Feldname d. Fremdtab.)
Beispiel
);
•
Einfache Feldtypen:
•
•
•
•
INTEGER
DOUBLE
VARCHAR (n)
DATE
... Ganze Zahlen
... Dezimalzahlen
... Zeichenkette der Länge n
... Datum
• Beispiel: Tabelle „Hersteller“
Einleitung
E/R-Diagramm
Hypersonic
SQL
Beispiel
Hersteller-ID
(Primärschlüssel)
1
2
3
Name
VW
Opel
Audi
CREATE TABLE Hersteller
(
HerstellerID
INTEGER
Name
VARCHAR(100)
NOT NULL,
NOT NULL,
CONSTRAINT HerstellerPK PRIMARY KEY (HerstellerID)
);
• Beispiel: Tabelle „Automobil“
Einleitung
E/R-Diagramm
Hypersonic
Hersteller-ID
(Fremdschl.)
1
1
2
3
Automobil-ID
1
2
1
1
Name
Käfer
Golf 2
Manta
A4
Preis
9.100,11.200,12.300,21.400,-
SQL
Beispiel
CREATE TABLE Automobil
(
HerstellerID
INTEGER
AutomobilID
INTEGER
Name
VARCHAR(100)
Preis
DOUBLE,
NOT NULL,
NOT NULL,
NOT NULL,
CONSTRAINT AutomobilPK PRIMARY KEY (HerstellerID,
AutomobilID),
CONSTRAINT HerstellerFK FOREIGN KEY (HerstellerID)
REFERENCES Hersteller
(HerstellerID)
);
• Einfügen von Datensätzen
Einleitung
•
Beim Einfügen werden die Datentypen sowie evt. vorhandene
Constraints von der Datenbank überprüft
E/R-Diagramm
•
Syntax:
Hypersonic
INSERT INTO Tabellenname VALUES (Feld1, Feld2,...);
•
SQL
Beispiel
Beispiele:
INSERT INTO Hersteller VALUES (1,'VW');
INSERT INTO Hersteller VALUES (2,'Opel');
INSERT INTO Hersteller VALUES (3,'Audi');
INSERT
INSERT
INSERT
INSERT
INTO
INTO
INTO
INTO
Automobil
Automobil
Automobil
Automobil
VALUES
VALUES
VALUES
VALUES
(1,1,'Käfer', 9100);
(1,2,'Golf 2', 11200);
(2,1,'Manta', 12300);
(3,1,'A4', 21400);
INSERT INTO Hersteller VALUES (2,'Mercedes');
INSERT INTO Automobil VALUES (5,1,'Civic', 13400);
• Einfache Abfragen
Einleitung
•
Dienen der Selektion eines Ausschnitts aus der Datenbank nach
bestimmten Kriterien
E/R-Diagramm
•
Syntax:
Hypersonic
SQL
Beispiel
SELECT Feldname1, Feldname2, ...
FROM Tabellenname
WHERE Bedingung1... AND|OR Bedingung2...;
•
Beispiele:
SELECT Name, Preis FROM Automobil;
SELECT * FROM Automobil;
SELECT Name, Preis FROM Automobil
WHERE HerstellerID = 1
AND Preis > 10000;
• Ändern von Datensätzen
Einleitung
•
Manipuliert den Inhalt von beliebig vielen Felder in einem
definierten Ausschnitt einer Tabelle
E/R-Diagramm
•
Syntax:
Hypersonic
SQL
Beispiel
UPDATE Tabellenname
SET Feldname1 = Wert, Feldname2 = Wert,...
WHERE Bedingung1...;
•
Beispiele:
UPDATE Automobil SET Preis = Preis * 2;
UPDATE Automobil SET Name = 'Golf'
WHERE HerstellerID = 1 AND AutomobilID = 1;
• Löschen von Datensätzen
Einleitung
E/R-Diagramm
Hypersonic
SQL
Beispiel
•
Löscht beliebige Zeilen aus der Tabelle
•
Syntax:
DELETE FROM Tabellenname
WHERE Bedingung1,...;
•
Beispiele:
DELETE FROM Automobil WHERE HerstellerID = 1;
DELETE FROM Automobil;
• Abfragen mit Verknüpfung (Join)
Einleitung
•
E/R-Diagramm
•
Hypersonic
•
SQL
SELECT Feldname1, Feldname2,...
FROM Tabellenname1, Tabellenname2,...
WHERE Verknüpfungsbedingung,...;
Bildet das Kreuzprodukt von 2 oder mehreren Tabellen
(„kombiniert jede Zeile aus Tab.1 mit jeder Zeile aus Tab. 2“)
Das Kreuzprodukt wird mittels WHERE auf übereinstimmende
Schlüsselpaare eingeschränkt
Syntax:
Beispiel
•
Beispiele:
SELECT *
FROM Hersteller, Automobil;
SELECT *
FROM Hersteller, Automobil
WHERE Hersteller.HerstellerID = Automobil.HerstellerID;
• Abfragen mit Sortierung (order by)
Einleitung
•
Das Abfrageergebnis kann beliebig aufsteigend (ASC[ending])
oder absteigend (DESC[ending]) sortiert werden
E/R-Diagramm
•
Syntax:
Hypersonic
SQL
Beispiel
SELECT ... ORDER BY Feldname1 ASC|DESC,
Feldname2 ASC|DESC,...;
•
Beispiele:
SELECT * FROM Automobil
ORDER BY Preis ASC;
• Abfragen mit Gruppierung (group by)
Einleitung
E/R-Diagramm
•
Anwendung mathematischer Funktionen auf Felder
•
Syntax:
SELECT
Hypersonic
SQL
Beispiel
SUM(Feldname)
AVG(Feldname)
MAX(Feldname)
MIN(Feldname)
COUNT(Feldname) FROM Tabellenname;
•
Ohne group by-Anweisung: Anwendung auf die ganze Tabelle
•
Mit der group by-Anweisung: Gruppierung nach einem Feld
SELECT Gruppierungsspalte, Math. Funktion
FROM Tabellenname GROUP BY Gruppierungsspalte;
•
Beispiel: Gesucht ist das teuerstes Modell jedes Herstellers
SELECT HerstellerID,MAX(Preis)
FROM Automobil GROUP BY HerstellerID;
• Weitere SQL-Befehle
Einleitung
E/R-Diagramm
Hypersonic
SQL
Beispiel
DROP TABLE Tabellenname;
ALTER TABLE Tabellenname ADD COLUMN Feldname Feldtyp;
Datenbank zur Personalverwaltung
Einleitung
•
Zu jedem Mitarbeiter sollen Name, Anschrift, PLZ und Gehalt in
geeigneten Datentypen gespeichert werden
•
Die Mitarbeiter sind in Abteilungen organisiert, die jeweils durch
einen Namen und eine Kostenstellennummer beschrieben werden
•
Jeder Mitarbeiter ist genau einer Abteilung zugeteilt
E/R-Diagramm
Hypersonic
SQL
Mitarbeiter
Beispiel
MitarbeiterID
Name
Anschrift
PLZ
Gehalt
AbteilungsID (FK)
Abteilungen
AbteilungID
Bezeichnung
Kostenstelle
Anlegen der Tabellen
Einleitung
E/R-Diagramm
Hypersonic
CREATE TABLE Abteilungen
(
AbteilungID
INTEGER
NOT NULL,
Name
VARCHAR(50) NOT NULL,
Kostenstelle
INTEGER,
CONSTRAINT AbteilungenPK PRIMARY KEY (AbteilungID)
SQL
Beispiel
);
CREATE TABLE Mitarbeiter
(
MitarbeiterID INTEGER
NOT
Name
VARCHAR(50),
Anschrift
VARCHAR(50),
PLZ
INTEGER
NOT
Gehalt
DOUBLE
NOT
AbteilungID
INTEGER
NOT
NULL,
NULL,
NULL,
NULL,
CONSTRAINT MitarbeiterPK PRIMARY KEY (MitarbeiterID),
CONSTRAINT AbteilungFK
FOREIGN KEY (AbteilungID)
REFERENCES Abteilungen (AbteilungID)
);
Anlegen einiger Testdatensätze:
Einleitung
•
4 Abteilungen (Geschäftsführung, Rechnungswesen, Verkauf,
Service)
•
Mehrere Mitarbeiter pro Abteilung
E/R-Diagramm
Hypersonic
SQL
Beispiel
Anlegen einiger Testdatensätze:
Einleitung
E/R-Diagramm
Hypersonic
SQL
Beispiel
INSERT
INSERT
INSERT
INSERT
INTO
INTO
INTO
INTO
Abteilungen
Abteilungen
Abteilungen
Abteilungen
VALUES
VALUES
VALUES
VALUES
(1,'Geschäftsführung',1000);
(2,'Rechnungswesen',2200);
(3,'Verkauf',3400);
(4,'Service',4000);
INSERT INTO Mitarbeiter
VALUES (1,'Müller','Heuberggasse 42-50',1170,20000.0,1);
INSERT INTO Mitarbeiter
VALUES (2,'Meyer','Margarethengürtel 15',1050,1400.50,2);
INSERT INTO Mitarbeiter
VALUES (3,'Huber','Bergsteiggasse 1',1170,1200.50,2);
INSERT INTO Mitarbeiter
VALUES (4,'Berger','Litfaßstrasse 12',1030,1300,3);
INSERT INTO Mitarbeiter
VALUES (5,'Naerr','Mengergasse 12',1210,1000.3,3);
INSERT INTO Mitarbeiter
VALUES (6,'Stocker','Hernalser Hauptstr. 112',1170,1500.0,4);
INSERT INTO Mitarbeiter
VALUES (7,'Niedermesser','Geblergasse 10',1170,1200.30,4);
INSERT INTO Mitarbeiter
VALUES (8,'Rat','Arndtstrasse 48',1120,1400,4);
Einige Abfragen
Einleitung
•
SELECT COUNT(*) FROM Mitarbeiter;
E/R-Diagramm
Hypersonic
•
Liste aller Mitarbeiter-Namen inkl. der jeweiligen
Abteilungsbezeichnung
SELECT Mitarbeiter.Name,Abteilungen.Name
FROM Mitarbeiter,Abteilungen
WHERE Mitarbeiter.AbteilungID = Abteilungen.AbteilungID;
SQL
Beispiel
Anzahl der Mitarbeiter
•
Liste aller Abteilungs-Bezeichnugnen mit den jeweiligen GesamtGehaltskosten
SELECT
FROM
WHERE
GROUP
•
Abteilungen.Name, SUM(Gehalt)
Abteilungen,Mitarbeiter
Mitarbeiter.AbteilungID = Abteilungen.AbteilungID
BY Abteilungen.AbteilungID;
Löschen der Abteilung 4
DELETE FROM Mitarbeiter WHERE AbteilungID = 4;
DELETE FROM Abteilungen WHERE AbteilungID = 4;