Folienadaption HS, 4/00

Relationen sind Tabellen!

Relationales Modell = Tabellarische Repräsentation der Daten+ „assoziative“ Anfragesprache

Datenbank enthält auch Beschreibung der Daten: Datenbankschema ( also Metadaten)

Wie kommt man zu einem DB-Schema?

1. Datenmodellierungtypisch: Entity-Relationship-Modell (datenorientiert) zunehmend mit den Operationen (Unified Modeling Language, UML)

Lies z.B: einem Kunde (customer) werden Lieferungen (shipments) gesandt, keine, eine oder mehrere (1:M) Kardinalitäten sind Invarianten: eine Lieferung kann nie an mehrere

Kunden gehen

Oft andere Notation!

Beispiel zu Entity-Relationship-Modellierung

Kunden (AntiqueOwners)

Antiquität

bestellen (Orders)

N

M

KDnr (ownerID) First name Nachname

(item

Handelstransaktion (Antiques)

kauft

verkauft

Was?

(siehe Tutorial S. 6 u. 7)

Schlüssel (key)Allgemein: eine identifizierende Folge von Attributen. (Invanrianzeigenschaft!)

Wie kommt man zum DB-Schema: Datendefinition

2. Datenbankschema definierenim RDM heißt das: Tabellenstruktur festlegen,Bezeichner, Attribute und deren Typ, ggf. Invarianten

CREATE TABLE ORDERS(OWNERID INTEGER NOT NULL FOREIGN KEY REFERENCES AntiqueOwners (OWNERID), ITEMDESIRED CHAR(40) NOT NULL);

CREATE TABLE AntiqueOwners(OWNERID INTEGER NOT NULL, OwnerLastname CHAR(40) NOT NULL, OwnerFirstName CHAR(30));

CREATE TABLE Antiques(SellerID INTEGER NOT NULL FOREIGN KEY REFERENCES AntiqueOwners (OWNERID),.....);

Das Datenbänkchen...

... benutzen wir später!

Eine zweite DB für einfache SQL-Anfragen

Leider hat dieseTabelle des Tutorials herzlichwenig mit der ersten zu tun!Was fehlt da?

Einfache SQL-Anfragen

Grundstruktur (eine beteiligte Tabelle):

SELECT [<attributBezeicher>] | * FROM <tabellenBezeichner >[WHERE <prädikat>] ;

EBNF-SyntaxMetasymbole!

Ein SQL-Ausdruck braucht also kein (Selektions-)prädikat

Ergebnis einer Anfrage ist immer eine Tabelle!... auf der wieder eine Anfrage... -> Algebra auf Relationen als theoretische Basis. Relationenalgebra: eine applikative Sprache

SELECT STATEFROM EmployeeAddressTable;

So sollte Ergebnis aussehen, (leider) werden Duplikate nicht entfernt

Beispiele

SELECT FirstName, LastName, Address, City, StateFROM EmployeeAddressTable;

SELECT LastName, Address, City, StateFROM EmployeeAddressTableWHERE State = ´Ohio´;

Projektion

Selektion

Zeichenkettenwertein Hochkommata

Selektionsprädikate (1)

<prädikat> This is a boolean expression which each row must satisfy. Operators which may be used include AND, OR, NOT, >, >=, =, <, <= The LIKE operator permits strings to be compared using 'wild cards'. The symbols _ and % are used to represent a single character or a sequence of characters. The IN operator allows an item to be tested against a list of values. There is a BETWEEN operator for checking ranges.

Engl. <condition_list>

Einfache Anfragen (1)

SELECT EMPLOYEEIDNOFROM EMPLOYEESTATISTICSTABLEWHERE SALARY < 40000 AND POSITION = ´Programmer´;

Programmierer, die weniger als 4000 (was??) verdienen?

Angestellte mit Gehalt > 40000 oder Bonus (benefits) von mehr als 10000?

SELECT EMPLOYEEIDNOFROM EMPLOYEESTATISTICSTABLEWHERE SALARY < 40000 OR BENEFITS > 10000;

Kann hier auch OR stehen?

Einfache Anfragen (2)

Manager mit Gehalt > 40000 oder Bonus (benefits) von mehr als 10000?

SELECT EMPLOYEEIDNOFROM EMPLOYEESTATISTICSTABLEWHERE POSITION = ´Manager´ AND (SALARY < 40000 OR BENEFITS > 10000;

Manager, Programmierer, Stabsmitglieder oder Ingenieure!

SELECT EMPLOYEEIDNOFROM EMPLOYEESTATISTICSTABLEWHERE POSITION IN ( ´Manager´, ´Programmer´, ´Staff´, ´Engineer´) ;

Vermeidet lästiges OR,was bedeutet: NOT IN ?

Einfache Anfragen (3)

Manager mit Gehalt < Bonus (benefits) ?

SELECT EMPLOYEEIDNOFROM EMPLOYEESTATISTICSTABLEWHERE POSITION = ´Manager´ AND SALARY < BENEFITS ; Natürlich nur, wenn die

Attribute gleichen Typ haben!

Manager mit Gehalt zwischen 50000000 und 100000000 ?

SELECT EMPLOYEEIDNOFROM EMPLOYEESTATISTICSTABLEWHERE POSITION = ´MANAGER´ AND SALARY BETWEEN 50000000 AND 100000000 ;

Einfache Anfragen (4)

Manager mit Gehalt zwischen 50000000 und 100000000 ?

SELECT EMPLOYEEIDNOFROM EMPLOYEESTATISTICSTABLEWHERE POSITION = ´MANAGER´ AND SALARY BETWEEN 50000000 AND 100000000 ;

Etwas ´verbose´ !

Angestellte mit Anfangsbuchstabe ´S´

SELECT EMPLOYEEIDNOFROM EMPLOYEESTATISTICSTABLEWHERE LASTNAME LIKE ´S%´;

In regulären Ausdrücken ist das der *

Einfache Subqueries

Personen mit Vornamen, die auch als Nachnamen auftauchen

SELECT EMPLOYEEIDNOFROM EMPLOYEESTATISTICSTABLEWHERE FIRSTNAME IN (SELECT LASTNAME FROM EMPLOYEESTATISTICSTABLE) ;

Beachte Unterschied zu ´Vorname = Nachname´

Diese Subqueries sindunabhängig voneinander

Mehrere Tabellen

Annahme: EmployeeAddressTable besitzt zusätzlich Spalte EmployeeNo

Anfrage: Namen der Manager, die mehr als 1000000 verdienen.

Problem: Verbindung zwischen der Information in verschiedenen Tabellen kann nicht hergestellt werden.

Aliasmanchmal nötig,immer nützlich

SELECT LASTNAME FROM EMPLOYEESTATISTICSTABLE ES, EMPLOYEEADDRESSTABLE EAWHERE POSITION = ´MANAGER´ AND SALARY > 1000000 AND EA.EMPLOYEENO = ES.EMPLOYEENO;

Verbund (genauer: Gleichheitsverbund)

Andere Jointypen

Voriges Beispiel: Join-Prädikat Teil der WHERE-Klausel: muss nicht so sein.

SELECT LASTNAME FROM EMPLOYEESTATISTICSTABLE ES JOIN EMPLOYEEADDRESSTABLE EA ON EA.EMPLOYEENO = ES.EMPLOYEENO WHERE POSITION = ´MANAGER´ AND SALARY > 1000000;

Unterschied:

Gültigkeitsbereich der Bezeichner in WHERE - Klausel

Natürlicher Verbund

SELECT LASTNAME FROM EMPLOYEESTATISTICSTABLE ES NATURAL JOIN EMPLOYEEADDRESSTABLE EAWHERE POSITION = ´MANAGER´ AND SALARY > 1000000;

Gleichverbund (Equijoin) auf gleich benannten Attributen verschiedener Tabellen.

Äusserer Verbund

010

400

ID

Erstelle Liste aller Namen und Adressen und (ggf.) des Gehalts

Wo bleibt die Adresse der nicht angestellten Personen?

SELECT EA.LASTNAME, EA.ADRESS,ES.SALARY FROM EMPLOYEESTATISTICSTABLE ES RIGHT OUTER JOIN EMPLOYEEADDRESSTABLE EA ON EA.EMPLOYEENO = ES.EMPLOYEENO ;

Right Outer Join: auch die Tupel des rechten Relationsarguments ohne Korrespondenzwert im ersten gehören zum Ergebnis!

Duplikate eliminieren

SELECT STATEFROM EmployeeAddressTable;

liefert ....

Mehrfach auftretende Werte (Duplikate) unterdrückendurch Schlüsselwort DISTINCT .

SELECT DISTINCT STATEFROM EmployeeAddressTable ORDER BY State;

hier sogar sortiert

Aggregierungsfunktionen

· () , SUM gives the total of all the rows satisfying , any conditions

, of the given column where the given .column is numeric· () .AVG gives the average of the given column· () MAX gives the largest figure in the given

.column· () MIN gives the smallest figure in the given

.column· (*) COUNT gives the number of rows satisfying

the .conditionsSELECT SUM(SALARY), AVG(SALARY)

FROM EMPLOYEESTATISTICSTABLE;

Bsp: Gehaltssumme und -durchschnitt aller Mitarbeiter!

Arithmetisch

Zielliste und Prädikate können Arithmetik enthalten

Bsp: Angestellte, die das Doppelte des Durchschnitts verdienen

SELECT EMPLOYEEIDFROM EMPLOYEESTATISTICSTABLEWHERE SALARY > (SELECT AVG(SALARY) FROM EMPLOYEESTATISTICSTABLE);

Gruppierung

Maximaler Verdienst aller Berufsgruppen

SELECT POSITION MAX(SALARY) FROM EMPLOYEESTATISTICSTABLE GROUP BY POSITION;

Maximaler Verdienst aller Berufsgruppen mit Durchschnittsgehalt > 50000

SELECT POSITION MAX(SALARY) FROM EMPLOYEESTATISTICSTABLE GROUP BY POSITIONHAVING AVG(SALARY) > 50000;

Daten Ändern

Adding Data

To insert rows into a table, do the following:

INSERT INTO ANTIQUES VALUES (21, 01, 'Ottoman', 200.00);

This inserts the data into the table, as a new row, column-by-column, in the pre-defined order. Instead, let's changethe order and leave Price blank:

INSERT INTO ANTIQUES (BUYERID, SELLERID, ITEM)VALUES (01, 21, 'Ottoman');

Ändern und Löschen

Ändere Preis für alle Positionen ´chair´ auf 500.

UPDATE ANTIQUES SET PRICE = 500.00 WHERE ITEM = 'Chair';

Lösche alle Ottomanen

DELETE FROM ANTIQUESWHERE ITEM = 'Ottoman';

Beliebige Prädikate möglich

Nochmal: Datendefinition

Mit Hilfe von Anfragen können Tabellen definiert werden, die künftig(fast) wie Basistabellen behandelt werden.Diese Tabellen heißen VIEW . Sie werden normalerweise nicht materialisiert.

CREATE VIEW MONEYMAKERS AS SELECT LASTNAME, CITY, STATE FROM EMPLOYEESTATISTICSTABLE ES, EMPLOYEEADDRESSTABLE EA WHERE SALARY > 1000000 AND EA.EMPLOYEENO = ES.EMPLOYEENO;

SELECT COUNT(LASTNAME)FROM MONEYMAKERSGROUP BY CITY, STATE;

Anfrage auf VIEW

Schemaänderungen erleichtern Rapid Prototyping

Hinzufügen des Attributs (der Spalte) EMPLOYEENO zur EMPLOYEEADDRESSTABLE

ALTER TABLE EMPLOYEEADDRESSTABLE ADD (EMPLOYEENO INTEGER NULL);

Nullwert != 0 NULL : undefiniert

Nicht behandelt: Mengenoperationen, physikalisches Schema,

Einbettung in Pogrammiersprachen.