Garten - Daten – Bank Was ist das?

Garten - Daten – Bank
Was ist das?
Dr. Karsten Tolle – PRG2 – SS 2013
Inhalt heute …
• Kurz: Motivation und Begriffe
• SQL (am Beispiel MySQL und Workbench)
– create table (Tabelle erzeugen)
– insert into (Einfügen)
– select (Anfragen)
•
•
•
•
Struktur
Aggregatfunktionen
where-Bedingungen (inklusive like-Operator)
Anfrage über mehrere Tabellen
Dr. Karsten Tolle – PRG2 – SS 2013
4
Daten
Definition (Duden):
1. Plural von Datum
2. [Zahlen]werte, Angaben,
formulierbare Befunde
3. (EDV) elektronisch
gespeicherte Zeichen,
Angaben,
Informationen
Dr. Karsten Tolle – PRG2 – SS 2013
Bank
•
•
•
•
Bank zum sitzen
Bank als Geldinstitut
Sandbank
…
5
Datenbank
Definition (Duden):
Elektronisches System, in dem große Mengen
an Daten zentral gespeichert werden können.
Datenbank
Was ist der Unterschied zu einem USB-Stick oder einer Festplatte?
Dr. Karsten Tolle – PRG2 – SS 2013
6
• Datenbankmanagementsystem (DBMS)
• Datenbank (DB)
• Datenbanksystem (DBS)
Dr. Karsten Tolle – PRG2 – SS 2013
7
Vorteile DBS
• Redundanz und Inkonsistenz
Werden durch die zentrale
Datenverwaltung und Datenhaltung
vermieden.
• Sicherheit gegen Datenmissbrauch
Durch die zentrale Benutzerverwaltung
können Zugriffsrechte gut kontrolliert
werden.
• Datenkonsistenz auch bei Ausfall
Durch Recovery-Strategien wird sicher
gestellt, dass auch nach einem
unerwartetem Ausfall die Daten konsistent
bleiben (Transaktionen).
• … und weitere!
Dr. Karsten Tolle – PRG2 – SS 2013
8
Fragen um herauszufinden, ob
man ein DBMS benötigt:
1. Liegt eine große Datenmenge vor, die schwer zu managen
ist?
–
–
Braucht es lange das Dokument zu öffnen?
Ist das Betrachten der Daten schwierig, muss man viel scrollen
und ist es schwer die gesuchten Daten zu finden?
2. Arbeiten verschiedene Personen/Anwendungen mit den
Daten?
3. Gibt es weitere Daten, die mit den gegebenen Daten in
Relation stehen und ebenfalls gespeichert werden?
–
Ziehen Änderungen an einer Stellen Änderungen an anderen
Stellen nach sich?
4. Werden die gleichen Daten an unterschiedlichen Orten
verwendet?
Dr. Karsten Tolle – PRG2 – SS 2013
9
Erstellung einer Datenbank:
Erster Schritt …
• Was sind die Anforderungen?
• Was sind die Ziele?
• Ist bekannt was gespeichert werden soll?
Design des Datenmodels
Dr. Karsten Tolle – PRG2 – SS 2013
10
Geschäftswissen
Kunde
Bank
Geld
SoftwareIngenieur
Konto
Anforderungen
1. ....
2. ....
3. ....
BankExperte
Datenbank
Dr. Karsten Tolle – PRG2 – SS 2013
Schema
11
Entity-Relationship-Modell
(ER-Modell)
•
Siehe auch: C. Maria Keet: A formal comparison of
conceptual datamodeling languages
(http://ftp.informatik.rwth-aachen.de/Publications/CEUR-WS/Vol337/paper3.pdf)
Picture by: Frank Roeing
Dr. Karsten Tolle – PRG2 – SS 2013
12
Erstellung einer Datenbank:
Zweiter Schritt …
• Welches DBMS wird genutzt?
• Wer soll wie auf die Daten zugreifen?
Umsetzung des Datenmodels im DBMS
• Wir verwenden in PRG2:
– relationales DBMS – insb. MySQL
– Zugriff über die Anfragesprache SQL
Dr. Karsten Tolle – PRG2 – SS 2013
13
Relationales Datenbankmodell
E.F. Codd, 1970 (Grundbegriffe)
• Tabellen mit Zeilen und Spalten um die Daten
darzustellen.
Attribute
Employee
Tupel
EMPNO
FIRSTNME
LASTNME
PHONENO
SALARY
001
Jon
Lucas
2983
2000
003
Jon
Smith
2980
3588
103
Lucas
Jon
4444
3980
999
Jon
Smith
3987
1500
Schema bzw. Relationenschema:
Employee (EMPNO, FIRSTNME, LASTNME, PHONENO, SALARY)
Dr. Karsten Tolle – PRG2 – SS 2013
14
MySQL
• http://dev.mysql.com/downloads/mysql/
Dr. Karsten Tolle – PRG2 – SS 2013
15
http://dev.mysql.com/doc/refman/5.6/en/
… für Version 5.1 gibt es das Ref. Manual auch in Deutsch!
Dr. Karsten Tolle – PRG2 – SS 2013
16
MySQL - Command Line Client
• Wichtige Befehle:
–
–
–
–
–
–
show databases; -- zeigt die Datenbanken an
create database <database_name>; -- erzeugt eine leere DB
drop database <database_name>; -- löscht eine DB
use <database_name>; -- erzeugt eine Verbindung zur Datenbank
show tables; -- zeigt die Tabellen der Datenbank an
explain <table_name>; -- gibt Informationen über die Tabelle
Dr. Karsten Tolle – PRG2 – SS 2013
17
Dr. Karsten Tolle – PRG2 – SS 2013
18
Dr. Karsten Tolle – PRG2 – SS 2013
19
DBS – Sprachen
Das DBS stellt als Schnittstelle eine
Datenbanksprache(n) für die folgenden Zwecke
zur Verfügung:
– Datenabfrage und -manipulation – Data
Manipulation Language (DML)
– Verwaltung der Datenbank – Data Definition
Language (DDL)
– Berechtigungssteuerung – Data Control Language
(DCL)
z.B. SQL
Dr. Karsten Tolle – PRG2 – SS 2013
20
SQL (ein erster Einblick)
Structured Query Language
Structured Query Language
• SQL ist für Relationale Datenbanksysteme!
Standards:
• SQL-1 von 1986 bzw. 1989 (ca. 120 Seiten)
• SQL-2 (SQL92) von 1992 (ca. 580 Seiten)
http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt
• SQL-3 (SQL99) von 2000 (ca. 1200 Seiten)
http://www.cse.iitb.ac.in/dbms/Data/Papers-Other/SQL1999/ansi-iso-9075-2-1999.pdf
• SQL 2003 - ISO/IEC 9075:2003
• SQL:2006 - ISO/IEC 9075-14:2006 (SQL/XML)
• …
Dr. Karsten Tolle – PRG2 – SS 2013
22
Heute …
• Erstellen
• Einfügen
• Anfragen
-
create
insert
select
… später
• update, delete, alter und drop
Dr. Karsten Tolle – PRG2 – SS 2013
23
Tabellen erstellen
Eine Tabelle wird im Minimalfall mit ihrem
eindeutigen Namen sowie der Liste der
zugehörigen Attribute samt Domänen nach
folgendem Schema definiert:
create table Relations-Name (
Attribut-Name Domäne { , Attribut-Name
Domäne}∗
);
z.B.: create table test (id int);
Dr. Karsten Tolle – PRG2 – SS 2013
24
die wichtigsten-SQL-Datentypen
•
•
•
•
•
•
•
•
integer/int
char(n)
varchar(n)
double
date
time
datetime
timestamp
• clob(n)
• blob(n)
• …
Siehe: http://dev.mysql.com/doc/refman/5.6/en/data-types.html
Dr. Karsten Tolle – PRG2 – SS 2013
25
create table Konto (
KtoNr integer,
KundenName varchar(25),
FilialName varchar(25),
Saldo real
Aus real macht MySQL intern double!
);
create table Kunde (
Name varchar(25),
Vorname varchar(25),
Strasse varchar(25),
Stadt varchar(25),
GebDatum date
);
Dr. Karsten Tolle – PRG2 – SS 2013
26
Einfügen von Tupeln I
Um Daten einzufügen, spezifiziert man das Tupel,
welches eingefügt werden soll …
Die Werte für die Attribute der Tupel müssen aus der
Domäne (Definitionsbereich) der Attribute sein.
insert into Kunde
values
('Otto', 'Hans', 'Bäckerweg 12', 'Frankfurt', '1970-1201') ;
Der Kunde "Hans Otto" wird eingefügt.
Dr. Karsten Tolle – PRG2 – SS 2013
27
Einfügen von Tupeln II
... unbekannte Werte können mit NULL-Values
befüllt werden:
insert into Kunde
values
('Otto', 'Hans', null, 'Frankfurt', '1970-12-01') ;
Der Kunde "Hans Otto" wird eingefügt.
Dr. Karsten Tolle – PRG2 – SS 2013
28
Einfügen von Tupeln III
... alternativ kann man die Attribute angeben, die
gesetzt werden sollen:
insert into Kunde (Vorname, Name, GebDatum)
values ('Hans', 'Otto', '1970-12-01') ;
Der Kunde "Hans Otto" wird eingefügt.
Dr. Karsten Tolle – PRG2 – SS 2013
29
Einfügen von Tupeln IV
... es können auch mehrere Datensätze mit einem
SQL-Statement eingefügt werden:
insert into Kunde (Vorname, Name, GebDatum)
values
('Hans', 'Otto', '1970-12-01'),
('Hans', 'Otto', '1925-11-24'),
('Hans', 'Otto', ‚2012-12-24') ;
Der Kunde "Hans Otto" wird 3-mal eingefügt.
Dr. Karsten Tolle – PRG2 – SS 2013
30
Primärschlüssel
Mittels der Klausel primary key kann eine
Attributfolgen einer Relation als Primärschlüssel
ausgezeichnet werden. Hierdurch wird
verhindert, dass ein Wert mehr als einmal
eingetragen wird.
create table Konto (
KtoNr integer primary key not null,
KundenName varchar(25),
FilialName varchar(25) not null,
Saldo double ) ;
abhängig vom DBMS, ob „not null“
nötig ist – MySQL nicht nötig.
Dr. Karsten Tolle – PRG2 – SS 2013
31
Primärschlüssel
Wenn mehr als ein Attribut als Primärschlüssel
definiert werden sollen, wird die Klausel in der
Form primary key (Attributnamen-Liste)
verwendet.
create table Transaktion (
vonKtoNr integer not null,
anKtoNr integer not null,
Datum date not null,
Betrag double,
primary key (vonKtoNr, anKtoNr, Datum) ) ;
Dr. Karsten Tolle – PRG2 – SS 2013
32
Zeitangaben
Datentyp
„Nullwert“
DATETIME
'0000-00-00 00:00:00'
DATE
'0000-00-00'
TIMESTAMP
'0000-00-00 00:00:00'
TIME
'00:00:00'
YEAR
0000
Unterschied DATETIME und TIMESTAMP?
Was ist das mit dem Jahr ‘23‘ gemeint?
Dr. Karsten Tolle – PRG2 – SS 2013
33
Unterschiede der DBMS
Datenbanksystem
Datentyp
Geltungsbereich
Genauigkeit
MS-SQL Server 2005
datetime
01.01.1753 bis
31.12.9999
3,33 Millisekunden
smalldatetime
01.01.1900 bis
06.06.2079
1 Minute
date
01.01.0001 bis
31.12.9999
1 Tag
time
00:00:00.0000000 bis
23:59:59.9999999
100 Nanosekunden
datetime
01.01.0001 bis
31.12.9999
3,33 Millisekunden
smalldatetime
01.01.1900 bis
06.06.2079
1 Minute
DATE
01.01.0100 bis
29.02.32768
1 Tag
TIME
00:00 bis 23:59.9999
6,67 Millisekunden
DATETIME
01.01.1000 00:00:00 bis
31.12.9999 23:59:59
1 Sekunde
DATE
01.01.1000 bis
31.12.9999
1 Tag
TIME
–838:59:59 bis 838:59:59 1 Sekunde
YEAR
1901 bis 2055
MS-SQL Server 2008
Firebird
MySQL 5.x
Dr. Karsten Tolle – PRG2 – SS 2013
1 Jahr
Quelle: http://de.wikibooks.org/wiki/Einf%C3%BChrung_in_SQL:_Datentypen
34
Syntaktische Grundform der
SQL-Anfrage
select
A1,A2, ... ,An
from
R1, R2, ... ,Rm
[where
conditions]
[group by clause]
[having
clause]
[order by clause];
Dr. Karsten Tolle – PRG2 – SS 2013
35
Anfragen ohne Bedingungen
Filiale ( Name Leiter Stadt Einlagen )
select Name, Leiter from Filiale;
select Leiter, Name from Filiale;
select Stadt from Filiale;
select * from Filiale;
Dr. Karsten Tolle – PRG2 – SS 2013
36
SQL verwirklicht das Prinzip der
„Vielfachmenge“ (engl. multiset). In den
Ergebnismengen können demnach Duplikate
auftreten.
Sind keine Duplikate erwünscht, müssen sie
explizit durch den Zusatz distinct entfernt
werden.
select distinct Stadt from Filiale;
Dr. Karsten Tolle – PRG2 – SS 2013
37
Aggregatfunktionen
Die sog. Aggregatfunktionen können in der
select-Klausel anstelle von einzelnen
Attributen angegeben werden.
Ergebnis einer Aggregatfunktion ist ein Wert,
kein Tupel.
select count(∗) as AnzahlKonten
from Konto;
Dr. Karsten Tolle – PRG2 – SS 2013
38
Aggregatfunktionen
• min( A ) zur Berechnung des Minimalwerts aller Tupel unter dem
Attribut A.
• max( A ) zur Berechnung des Maximalwerts aller Tupel unter dem
Attribut A.
• avg( [ distinct ] A ) zur Berechnung des Durchschnittswerts aller
Tupel unter dem Attribut A, wobei unter Angabe von distinct
mehrfach gleiche Werte nur einmal in die Berechnung eingehen.
• sum( [ distinct ] A ) zur Berechnung der Summe aller Tupel unter
dem Attribut A, wobei unter Angabe von distinct mehrfach gleiche
Werte nur einmal in die Berechnung eingehen.
• count( ∗ ) zum Zählen der Tupel der betrachteten Relation.
• count( [ distinct ] A) zum Zählen der Tupel der betrachteten
Relation, wobei zunächst eine Duplikateneliminierung bezogen auf
Werte unter dem Attribut A stattfindet.
Dr. Karsten Tolle – PRG2 – SS 2013
39
where-Klausel
Bezüglich der Bedingung sind Vergleiche mit den
üblichen Operatoren, den logischen
Verknüpfungen and und or sowie beliebige
Klammerungen gestattet.
Konto ( KontoNr KundenNr FilialName Saldo )
select * from Konto
where Saldo > 5000 or KundenNr <= 100;
Dr. Karsten Tolle – PRG2 – SS 2013
40
Textvergleiche - LIKE
Kunde ( KundenNr Name Vorname Straße Stadt )
select KundenNr
from Kunde
where Name like 'To%' and
Vorname not like '_arste%';
Bem.:
% für beliebige Zeichenfolgen
_ für genau ein Zeichen
Dr. Karsten Tolle – PRG2 – SS 2013
41
Anfrage über mehrere Relationen
Werden in der from-Klausel mehrere Relationen
spezifiziert, so erfolgt die Berechnung des
kartesischen Produktes.
Konto ( KontoNr KundenNr FilialName Saldo )
Filiale ( Name Leiter Stadt Einlagen )
select * from Filiale, Konto;
Dr. Karsten Tolle – PRG2 – SS 2013
… im Ergebnis wird jeder DS auf Filiale
mit jedem DS aus Konto verbunden!
42
SQL-Online-Tutorial
• http://sqlzoo.net/ (evt.: http://old.sqlzoo.net)
Dr. Karsten Tolle – PRG2 – SS 2013
43
SQL Online Tutorial
• Anfragen können interaktiv ausgeführt werden.
Dr. Karsten Tolle – PRG2 – SS 2013
44
SQL – Web Links
• SQL Tutorial (deutsch): http://www.sqltutorial.de/
• SQL Tutorial (englisch):
http://www.intermedia.net/support/sql/sqltut.asp
• SQL Online ausprobieren: http://sqlzoo.net/
Dr. Karsten Tolle – PRG2 – SS 2013
45
Literatur – eBooks allgemein
• http://www.ub.unifrankfurt.de/datenbanken/ebooks_gesamt.html
Dr. Karsten Tolle – PRG2 – SS 2013
46