Ein Einführung in die Nutzung von relationalen Datenbanken und SQL

Ein Einführung in die Nutzung von
relationalen Datenbanken und SQL
Nils Schmeißer I Software & Services I https://www.hzdr.de/FWCS
Inhalt
▪ Daten und Tabellen
▪ Normalisierung, Beziehungen, Datenmodell
▪ SQL - Structured Query Language
▪ Anlegen von Tabellen
▪ Datentypen (Spalten), Regeln, Beziehungen
▪ Tabellen (Objekt) Beschreibung
▪ Daten (Zeilen)
▪ einfüge, auswählen, ändern und löschen
▪ das Transaktionsmodell
▪ View
▪ Funktionen
▪ Zugang zu RDMBS
Seite 2
Mitglied der Helmholtz-Gemeinschaft
Nils Schmeißer I Software & Services I https://www.hzdr.de/FWCS
wichtige Begriffe
▪ DBMS: Datenbank Management System
▪ Software, die Datenhaltung und Logik organisiert
▪ DBMS Instanz: laufender Prozeß eines DBMS
▪ Tablespace: physikalische Ablage von Daten in einer
DBMS Instanz
▪ Tablespaces und Instanzen sind für den DBMS Nutzer
typischerweise nicht sichtbar
▪ Schema = Datenbank
▪ logische Ablage von Daten
▪ enthält alle Objekte einer Datenbank (Tabellen, ...)
Seite 3
Mitglied der Helmholtz-Gemeinschaft
Nils Schmeißer I Software & Services I https://www.hzdr.de/FWCS
Tabelle
▪ Aufbewahrungsort von Daten ist eine Tabelle
▪ Tabelle hat einen Namen und Spalten
▪ die Menge der Spalten ist festgelegt
▪ die Datensätze werden in Zeilen abgelegt
▪ die Anzahl der Zeilen spielt keine Rolle!
Name Vorname PLZ Ort Strasse Name Vorname PLZ Ort Strasse Mustermann Manfred 1234 Musterstadt Straße 1
Mustermann Manfred 1234 Musterstadt Straße 1
Mustermann Brunhild 1234 Musterstadt Straße 1
Mustermann Brunhild 1234 Musterstadt Straße 1
Zeile
Seite 4
Spalte
Mitglied der Helmholtz-Gemeinschaft
Nils Schmeißer I Software & Services I https://www.hzdr.de/FWCS
Daten und Tabellen - ein Beispiel
Name Vorname PLZ Ort Straße Name Vorname PLZ Ort Straße Mustermann Manfred 1234 Musterstadt Straße 1
Mustermann Manfred 1234 Musterstadt Straße 1
Mustermann Brunhild Msterstadt Straße 1
Mustermann Brunhild Msterstadt Straße 1
▪ Probleme:
▪ Redundanzen
▪ Inkonsistenzen
▪ Lösung: “Normalisierung”
▪ eine Tabelle hat eine feste “Breite”,
die Spaltenwerte sind atomar
▪ eine Tabelle enthält nur die Daten eines
Themengebietes (Definitionsbereich)
Seite 5
Mitglied der Helmholtz-Gemeinschaft
Nils Schmeißer I Software & Services I https://www.hzdr.de/FWCS
Daten und Tabellen - Normalisierung
Name
Name
Mustermann
Mustermann
Mustermann
Mustermann
Vorname
Vorname Ort
Ort
Manfred
Manfred 11
Brunhild
Brunhild11
ID
ID
1
1
PLZ OrtStraße
PLZ OrtStraße
1234 Musterstadt
1234 Musterstadt
Straße 1
Straße 1
▪ Aufspalten der Daten in Namen und Orte
▪ Zuordnung von Orten zu Namen
▪ Frage: wie erfolgt die Zuordnung?
→ Schlüssel und Beziehung (Relation)
Seite 6
Mitglied der Helmholtz-Gemeinschaft
Nils Schmeißer I Software & Services I https://www.hzdr.de/FWCS
Datenmodell
▪ Vorraussetzung für den Entwurf einer Datenbank
▪ Einhaltung der Normalisierungsregeln
▪ Festlegen von Definitionsbereichen, Regeln,
Schlüsseln
▪ Darstellung im sog. entity-relationship Diagramm
Seite 7
Mitglied der Helmholtz-Gemeinschaft
Nils Schmeißer I Software & Services I https://www.hzdr.de/FWCS
SQL - Structured Query Language
▪ deklarative Datenbanksprache für relationale DB-Systeme
▪ 1986 ANSI, 1999 ISO/IEC 9075:1999
▪ DDL: Data Definition Language; Befehle zur Definition des
Datenbankschemas z.B. create table
▪ DML: Data Manipulation Language; Befehle zur
Datenmanipulation und Abfrage
▪ DCL: Data Control Language; Befehle zur Rechteverwaltung
▪ relationale Datenbankmanagementsysteme (RDBMS)
▪ DB2, Informix, Microsoft SQL Server, MySQL, Oracle,
PostgreSQL, Sybase
▪ andere Modelle: OODB
Seite 8
Mitglied der Helmholtz-Gemeinschaft
Nils Schmeißer I Software & Services I https://www.hzdr.de/FWCS
DDL - Anlegen von Tabellen
▪ Tabelle hat Name und Spalten
▪ create table <tabellenname> ( <spalten> );
▪ Spalten haben Name, Typ und optional Regeln
▪ <spaltenname> <spaltentyp> [ <regel> ]
▪ Beispiel:
create table ort (
id number primary key,
name varchar(4000)
)
/ ← Kennzeichen für Ende DDL Anweisung
▪ Tip: immer mehr Platz vorsehen
▪ Tabellen- und Spaltennamen dürfen keine Sonderzeichen und
Leerzeichen enthalten
Seite 9
Mitglied der Helmholtz-Gemeinschaft
Nils Schmeißer I Software & Services I https://www.hzdr.de/FWCS
DDL - Tabellen und Datentypen
▪ smallint, int, bigint: ganze Zahlen
▪ float, double: rationale Zahlen
▪ number: ganze und rationale Zahlen (Oracle spezifisch)
▪ varchar2(n): String (maximale Länge Oracle: 4000 Byte !)
▪ Werte werden in Hochkomma (') eingeschlossen (Bsp.: 'hallo')
▪ varchar2 ist Oracle spezifisch, SQL-Standard: varchar
▪ date: Zeitstempel (Datum und Uhrzeit)
▪ blob: binary large object für Objekte größer als 4 kByte
▪ kann beliebige Binärdaten enthalten
▪ clob: große Strings
▪ boolean: logische Werte (true und false)
▪ null-Wert: der nicht-existente Wert (<> leerer String)
Seite 10
Mitglied der Helmholtz-Gemeinschaft
Nils Schmeißer I Software & Services I https://www.hzdr.de/FWCS
DDL - Tabellen, Spalteneigenschaften
▪ uniq: Wertemenge enthält jeden Wert höchstens einmal
▪ not null: keine null-Werte zulässig
▪ primary key: Primärschlüssel (uniq not null)
▪ ein Primärschlüssel kann über mehrere Spalten erklärt
werden
▪ default <wert>: Wert wird automatisch erzeugt
▪ Beispiel: create table t (
pk number primary key,
dt date not null,
vv number default 3.14159
)
Seite 11
Mitglied der Helmholtz-Gemeinschaft
Nils Schmeißer I Software & Services I https://www.hzdr.de/FWCS
DDL - Tabellen und Beziehungen
▪ Fremdschlüssel: Werte eine Spalte müssen in der
referenzierten Spalte enthalten sein
▪ references <tabelle> ( <spalte> )
▪ die referenzierte Spalte muß die Eigenschaft uniq not
null oder primary key haben
▪ alternative Regeldarstellung:
▪ constraint <cname> primary key ( <sname> )
▪ foreign key ( <sname> ) references <tname> ( <rsname> ) [ on delete cascade ]
Seite 12
Mitglied der Helmholtz-Gemeinschaft
Nils Schmeißer I Software & Services I https://www.hzdr.de/FWCS
DDL - Tabellen, komplettes Beispiel
create table orte (
create table kontakte (
id number primary key,
id number primary key,
name varchar2(4000) not null,
name varchar2(4000) not null,
plz number not null,
vname varchar2(4000) not null,
strasse varchar2(4000) not null
ort number references orte(id)
)
)
/
/
create table orte (
create table kontakte (
id number,
id number primary key,
name varchar2(4000) not null,
name varchar2(4000) not null,
plz number not null,
vname varchar2(4000) not null,
strasse varchar2(4000) not null,
ort number,
constraint pk_orte primary key ( id )
constraint fk_orte foreign key )
(ort) references orte (id)
/
)
Seite 13
Mitglied der Helmholtz-Gemeinschaft
Nils Schmeißer I Software & Services I https://www.hzdr.de/FWCS
DDL - Tabellen beschreiben
▪ describe <objektname>;
▪ zeigt die Beschreibung eines Objektes an
▪ Beispiel: describe orte;
Name
Null?
Typ
--------- -------- ----------ID
NOT NULL NUMBER
NAME
VARCHAR2(4000)
PLZ
NUMBER
STRASSE
VARCHAR2(4000)
Seite 14
Mitglied der Helmholtz-Gemeinschaft
Nils Schmeißer I Software & Services I https://www.hzdr.de/FWCS
DDL - Tabellen/Objekte löschen
▪ drop <otype> <objektname>;
▪ Löschen eines Objektes
▪ table, view, function
▪ trigger
▪ Beispiel: drop table orte;
▪ enthält die Tabelle referenzierte Daten,
verhindert das DBMS die Löschung
Seite 15
Mitglied der Helmholtz-Gemeinschaft
Nils Schmeißer I Software & Services I https://www.hzdr.de/FWCS
DML - Datenzeilen einfügen
▪ insert into <tname> (<spaltenliste>) values (<wertliste>) ;
▪ die Angabe der Spaltenliste kann entfallen, wenn alle Spalten mit Werten befüllt
werden; Reihefolge beachten !
▪ Beispiel:
insert into orte ( id, plz, name, strasse )
values ( 1, 1234, 'Musterstadt', 'Strasse 1');
insert into orte
values ( 2, 'Dresden', 1328,'Strasse 3');
▪ was passiert bei folgenden Anweisungen:
insert into orte values ( 3, 4567, 'Pirna','Strasse 4');
insert into orte values ( 1, 'Pirna', 4567, 'Strasse 4');
insert into orte (id,plz,strasse)
values ( 3, 4567, 'Strasse 4');
Seite 16
Mitglied der Helmholtz-Gemeinschaft
Nils Schmeißer I Software & Services I https://www.hzdr.de/FWCS
DML - Datenzeilen einfügen
▪ beim Einfügen werden die Referenzregeln
geprüft
▪ DBMS verhindert
▪ doppelte Werte in uniq-Spalten
▪ Wert, die in referenzierten Spalten nicht existieren
▪ null-Werte bei not null-Spalten
Seite 17
Mitglied der Helmholtz-Gemeinschaft
Nils Schmeißer I Software & Services I https://www.hzdr.de/FWCS
DML - Datenzeilen auswählen
▪ ein DBMS zeigt generell keine Daten an,
Daten werden ausgewählt → Ergebnismenge
▪ select <spaltenliste> from
<tabellenliste> [ where <bedingung> ] ;
▪ select ermittelt eine → Menge von Datensätzen
▪ die Bedingung ist ein bool'scher Term (true oder false),
z.B. 1=2 oder <spalte>=<wert>
▪ Beispiel:
select * from orte;
select name,strasse from orte where plz=1234;
Seite 18
Mitglied der Helmholtz-Gemeinschaft
Nils Schmeißer I Software & Services I https://www.hzdr.de/FWCS
DML - Datenzeilen auswählen
▪ distinct: voneinander verschiedene Werte
▪ ermittle voneinander verschiedene Nachnamen:
select distinct name from kontakte;
▪ group by: ermittle gleichartige Werte
▪ Typische Fragestellung „Wieviel von einer Sorte?“
select name,count(name)
from kontakte group by name;
Seite 19
Mitglied der Helmholtz-Gemeinschaft
Nils Schmeißer I Software & Services I https://www.hzdr.de/FWCS
DML - Datenzeilen auswählen, Join
▪ Join: Abfrage über mehrere Tabellen
select kontakte.name,vorname,plz,orte.name,strasse
from kontakte,orte where kontakte.ort=orte.id;
Name Vorname Ort
Name Vorname Ort
Mustermann Manfred 1
Mustermann Manfred 1
Mustermann Brunhild 1
Mustermann Brunhild 1
ID PLZ Ort Straße
ID PLZ Ort Straße
1 1234 Musterstadt Straße 1
1 1234 Musterstadt Straße 1
Name Vorname PLZ Ort Straße
Name Vorname PLZ Ort Straße
Mustermann Manfred 1234 Musterstadt Straße 1
Mustermann Manfred 1234 Musterstadt Straße 1
Mustermann Brunhild 1234 Musterstadt Straße 1
Mustermann Brunhild 1234 Musterstadt Straße 1
▪ führt Informationen aus mehreren Tabellen zusammen
▪ „passende“ Spalten müsssen nicht zwangsweise eine
Relation haben
Seite 20
Mitglied der Helmholtz-Gemeinschaft
Nils Schmeißer I Software & Services I https://www.hzdr.de/FWCS
DML - Datenzeilen auswählen, Join
▪ bei Abfragen über mehrere Tabellen müssen
nicht eindeutige Spaltennamen qualifiziert
werden
▪ Beispiel:
select kontakte.name,orte.name
from kontakte,orte
where kontakte.ort=orte.id;
Seite 21
Mitglied der Helmholtz-Gemeinschaft
Nils Schmeißer I Software & Services I https://www.hzdr.de/FWCS
DML - Mengenoperationen
▪ auf Auswahlmengen können Mengenoperationen
angewandt werden
▪ union, union all: Vereinigungsmenge
▪ union ermittelt nur unterschiedliche Elemente
▪ select name from t1 union select name from t2;
▪ intersect: Schnittmenge
▪ minus: entfernt aus der ersten alle Elemente der
zweiten Menge
Seite 22
Mitglied der Helmholtz-Gemeinschaft
Nils Schmeißer I Software & Services I https://www.hzdr.de/FWCS
DML - Subabfrage
▪ ermitteln von Datensätz basierend auf der
Auswahl einer Menge in einer anderen Tabelle
▪ Beispiel: alle Namen von Personen, die in Orten
mit einer Postleitzahl < 2000 wohnen:
select name from kontakte where
ort in (select id from orte where plz<2000);
Seite 23
Mitglied der Helmholtz-Gemeinschaft
Nils Schmeißer I Software & Services I https://www.hzdr.de/FWCS
DML - Datenzeilen ändern
▪ update <tabellenname> set
<zuweisungsliste> [ where <bedingung> ] ;
▪ die Zuweisungsliste besteht aus Termen der Form
<spaltenname>=<Wert>
▪ Beispiel:
update orte set plz=9999 where id=1;
update kontakte set ort=2;
▪ DBMS verhindert das Setzen von illegalen Werten
▪ DBMS verhindert das Ändern von referenzierten Werten
Seite 24
Mitglied der Helmholtz-Gemeinschaft
Nils Schmeißer I Software & Services I https://www.hzdr.de/FWCS
DML - Datenzeilen löschen
▪ delete from <tabellennname>
[ where <bedingung> ];
▪ Beispiel:
delete from kontakte where
name='Mustermann';
delete from orte;
▪ DBMS verhindert das Löschen von Datensätzen, die
referenziert werden
Seite 25
Mitglied der Helmholtz-Gemeinschaft
Nils Schmeißer I Software & Services I https://www.hzdr.de/FWCS
Das Transaktionsmodell
▪ Beispiel:
Q: 10:05
R: 10:08
Q: 10:06
R: 10:09
▪ Lösung: eine Transaktionsfolge muß bestätigt werden, bevor sie
persistent (dauerhaft) in die Datenbank geschrieben wird.
▪ commit; macht eine Transaktionsfolge persistent
▪ rollback; macht eine Transaktionsfolge ungültig
▪ eine neue Transaktionsfolge beginnt nach jedem commit oder rollback
bzw. beim Anmelden an der Datenbank
Seite 26
Mitglied der Helmholtz-Gemeinschaft
Nils Schmeißer I Software & Services I https://www.hzdr.de/FWCS
Views
Name
Name
Mustermann
Mustermann
Mustermann
Mustermann
Vorname
Vorname Ort
Ort
Manfred
1
Manfred
1
Brunhild
1
Brunhild
1
ID
ID
11
Name
Vorname
Name
Vorname PLZ
PLZ
Mustermann
Manfred
1234
Mustermann Manfred
1234
Mustermann
Brunhild
1234
Mustermann Brunhild
1234
PLZ
PLZ
1234
1234
Ort
Ort
Musterstadt
Musterstadt
Musterstadt
Musterstadt
Ort
Ort
Musterstadt
Musterstadt
Straße
Straße
Straße
Straße11
Straße
Straße
Straße
Straße11
Straße
Straße11
▪ spezifische Sicht auf Daten
▪ wird wie Tabelle behandelt
▪ Datenmanipulation nur, wenn View über einer
Tabelle
Seite 27
Mitglied der Helmholtz-Gemeinschaft
Nils Schmeißer I Software & Services I https://www.hzdr.de/FWCS
Views
▪ Definition:
create or replace view vname
( spaltenliste ) as select-statement
▪ Beispiel:
create view adressen ( name,vorname,plz,ort,strasse ) as select kontakte.name,vorname,plz,orte.name,strasse
from kontakte,orte where kontakte.ort=orte.id
/
▪ eine View „speichert“ eine Anfrage
▪ Views können wieder in Views verwendet werden
Seite 28
Mitglied der Helmholtz-Gemeinschaft
Nils Schmeißer I Software & Services I https://www.hzdr.de/FWCS
Funktionen
▪ Funktionen berechnen Ausdrücke
▪ können in Abfragen verwendet werden
Beispiel: select max(id) from orte;
▪ sind Bestandteil von SQL, die Definition erfolgt jedoch spezifisch
▪ Beispiel:
create function ABS(x in number) return number is
begin
if x<0 then
return ­x;
end if;
return x;
end;
/
Seite 29
Mitglied der Helmholtz-Gemeinschaft
Nils Schmeißer I Software & Services I https://www.hzdr.de/FWCS
Funktionen - Gruppenfunktionen
▪ min, max: von Werten
▪ select min(t) from temperatur;
▪ sum: Summe von Werten
▪ select sum(preis*n) from warenkorb;
▪ count: Anzahl von Werten
▪ select count(*) from bestand;
▪ weitere „klassische“ Funktionen
▪ sin, cos, abs, ...
Seite 30
Mitglied der Helmholtz-Gemeinschaft
Nils Schmeißer I Software & Services I https://www.hzdr.de/FWCS
Funktionen - String Funktionen
▪ Stringverknüpfung: s1 || s2,
hängt s2 an s1 an
▪ upper(s), lower(s): konvertiert Strings in
Groß- bzw. Kleinbuchstaben
▪ substr(s,i,l): ermittelt den Substring am
dem i-ten Zeichen in s der Länge l
▪ instr(str,sstr): ermittelt die Position von
sstr in str falls vorhanden
▪ to_char: konvertiert nach String
Seite 31
Mitglied der Helmholtz-Gemeinschaft
Nils Schmeißer I Software & Services I https://www.hzdr.de/FWCS
Funktionen - String Funktionen
▪ s1 like s2
▪ prüft auf Ähnlichkeit von Strings
▪ Beispiel: ... where name like 'Schmei%'
▪ soundex(s1) = soundex(s2)
▪ Oracle spezifische Funktion
▪ prüft, ob s1 in der aktuellen Spracheinstellung
ähnlich zu s2 „klingt“, Hash-Wert
▪ Beispiel: ... where soundex(name) like soundex('Dresde');
Seite 32
Mitglied der Helmholtz-Gemeinschaft
Nils Schmeißer I Software & Services I https://www.hzdr.de/FWCS
DCL - Berechtigungen
▪ innerhalb eines Schemas kann zunächst nur auf
die eigenen Objekte zugegriffen werden
▪ auf Objekte, anderer Schemata, die sich in der
selben Instanz befinden können Rechte erteilt
werden:
▪ grant select on DB1.TAB1 to DB2;
▪ select, insert, update, delete, execute Recht
▪ können nicht weitergegeben werden, außer:
grant ... on ... to .. with grant option;
Seite 33
Mitglied der Helmholtz-Gemeinschaft
Nils Schmeißer I Software & Services I https://www.hzdr.de/FWCS
DCL - Berechtigungen
▪ Objekte in anderem Schema müssen qualifiziert
werden
▪ Beispiel: im Schema DB2
select id from DB1.TAB1;
▪ gilt auch für Funktionen
Seite 34
Mitglied der Helmholtz-Gemeinschaft
Nils Schmeißer I Software & Services I https://www.hzdr.de/FWCS
Arbeiten mit RDBMS
▪ es gibt keine generischen Oberflächen!
▪ Kommandprozessoren (sqlplus, psql, mysql, ...)
▪ Programmierschnittstellen
▪ ODBC - MS typisches Verfahren
▪ JDBC - Java
▪ spezifische Bibliotheken, meist in C
▪ OCI (Oracle Call Interface), libpq (PostgreSQL), ...
Seite 35
Mitglied der Helmholtz-Gemeinschaft
Nils Schmeißer I Software & Services I https://www.hzdr.de/FWCS
Oracle - sqlplus
▪ sqlplus dbname@dbhost:port:iname
▪ port ist typischerweise 1521
▪ iname (Instanzname) meist ORA0
▪ Verwaltungs-GUI's
▪ JDeveloper, Oracle Enterprise Manager
▪ Hora
▪ Eclipse
▪ dbvis
▪ Zugang nur von speziellen Rechnern
Seite 36
Mitglied der Helmholtz-Gemeinschaft
Nils Schmeißer I Software & Services I https://www.hzdr.de/FWCS
Oracle - sqlplus
▪ direktes Senden von Anweisungen an RDBMS
▪ Anzeige von selektierten Wertmengen
▪ erzeugen von Protokolldateien
▪ spool Dateiname;
Anweisungsfolge
spool off;
▪ Abarbeitung von Befehlsdateien (Skripte)
▪ @Dateiname
Seite 37
Mitglied der Helmholtz-Gemeinschaft
Nils Schmeißer I Software & Services I https://www.hzdr.de/FWCS