Folie 1

Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalitt ist gut&schnell kostenfreie MySQL Community Edition Diverse MySQL Server Lizenzmodelle Dem kostenfreien MySQL fehlen jedoch einige Features, die in groen kommerziellen DB-Systemen eine Selbstverstndlichkeit sind

Folie 2

Dr. Heidrun BethgeDatenbanken2 Eigenschaften von MySQL I Client-Server-Architektur: DB-Server + beliebig viele Clients, die mit Server kommunizieren MySQL folgt dem ANSI-SQL Standard, allerdings einige (wesentliche) Einschrnkungen Programmiersprachen: Fr MySQL- Anwendungen gibt es viele APIs (Application Programming Interfaces) bzw. Bibliotheken. Programmierung in C, C++, Java, Perl, PHP, Python, Tcl mglich. ODBC: Durch ODBC-Schnittstelle kann MySQL darber hinaus ber die gngigen Windows- Programmiersprachen angesprochen werden.

Folie 3

Dr. Heidrun BethgeDatenbanken3 Eigenschaften von MySQL II Plattformunabhngig: MySQL luft auf Macintosh, OS/2, Linux, Windows, div. Unix- Varianten Geschwindigkeit: MySQL ist eine sehr schnelles Datenbanksystem. Geschwindigkeitsvorteil jedoch auch durch fehlende Merkmale wie z.B. Transaktionen in MyISAM-Tabellen. MySQL stellt geringe Anforderungen an die Hardware. Untersttzung durch weltweite, groe MySQL-Entwicklergemeinde.

Folie 4

Dr. Heidrun BethgeDatenbanken4 Einschrnkungen von MySQL Sub-Selects/Deletes: SELECT * FROM table1 WHERE x IN (SELECT y FROM table2) geht ab 4.1 Views (ab 5.0) Stored Procedures und Trigger (eingeschrnkt ab 5.1) Transaktionen (eingeschrnkt ab 4.0). keine freien DataWarehouse-Funktionen keine freien Recovery-Mechanismen

Folie 5

Dr. Heidrun BethgeDatenbanken5 Windows oder Unix/Linux? MySQL wurde ursprnglich fr Unix/Linux verwendet und erst spter auf Windows portiert. Auf im Internet ffentlich zugnglichen Servern luft MySQL fast nur unter Linux. -> intensiverer Praxiseinsatz -> intensiver auf diesen Systemen getestet effizienterer Einsatz unter Unix/Linux, da MySQL fr deren Prozess- und Thread- Modelle optimiert ist fr Entwicklungsumgebung egal: hier funktioniert Windows genauso gut

Folie 6

Dr. Heidrun BethgeDatenbanken6 Installation testen starten: mysql/bin/mysql.exe jetzt Befehl status absetzen falls es nicht klappt: Task-Manager. Prozess mysqld-nt.exe sollte laufen. evtl auch aufrufen mit Parametern: mysql h 192.168.0.1 -u username -p

Folie 7

Dr. Heidrun BethgeDatenbanken7 MySQL-Monitor mysql Nicht zu verwechseln mit dem MySQL- Datenbank-Server, der evtl. als Dienst luft. Start ber mysql/bin/mysql.exe evtl auch aufrufen mit Parametern: mysql h localhost -u username p hier sind Administrations- und Wartungsaufgaben interaktiv durchfhrbar; Kommandointerpreter jeder Befehl wird durch Semikolon beendet, Befehle knnen ber mehrere Zeilen gehen Mit Pfeiltasten lassen sich Befehle wiederholen

Folie 8

Dr. Heidrun BethgeDatenbanken8 erstes Beispiel create database test1; show databases; use test1; create table testtab ( id int not null auto_increment, wert tinyint not null, primary key (id)); insert into testtab (wert) values (3); select * from testtab; drop table testtab;

Folie 9

Dr. Heidrun BethgeDatenbanken9

Folie 10

Dr. Heidrun BethgeDatenbanken10 Entwicklungsgeschichte SQL SQL (frherer Name: SEQUEL) ist zunchst Anfragesprache des relationalen DBMS-Prototyps System R Implementierung ab 1975 im IBM Forschungslabor in San Jose SQL wurde ab Anfang der 80er Jahre in verschiedenen DB-Implementierungen verfgbar: IBM, Oracle, DEC,...

Folie 11

Standardisierungen SQL Seit Anfang/Mitte der 80er Jahre Standardisierungsaktivitten am ANSI fr eine relationale DB-Sprache auf Basis von SQL ANSI SQL-86 (SQL1) ANSI SQL-92 (SQL2) ANSI SQL-99 (SQL3) ANSI SQL-2003 ANSI SQL/XML-2006 ANSI SQL-2008 Bis heute ist (vermutlich) noch kein relationales DBMS Full SQL konform. SQL der groen DBMS weitgehend kompatibel. Dr. Heidrun BethgeDatenbanken11

Folie 12

Dr. Heidrun BethgeDatenbanken12 Sprachumfang von SQL DDL Data Definition Language Erstellen von Tabellen, Indices, Views, DQL Data Query Language Abfragen von Daten DML Data Manipulation Language Anlegen, ndern, Lschen von Daten DCL Data Control Language Anlegen von Benutzern, Vergabe von Zugriffsrechten TCL Transaction Control Language Steuerung von Transaktionen

Folie 13

Verwendung von SQL SQL ist Anweisungssprache. SQL ist keine Programmiersprache. Reihenfolge der SQL-Klauseln ist fix. SQL ist nicht case-sensitiv (Ausnahme: Tabellen- und Feldnamen z.B. in bestimmten Oracle-Tabellen). Beliebiges Einfgen von Leerzeichen, Leerzeilen, Tabs mglich. Dr. Heidrun BethgeDatenbanken13

Folie 14

Dr. Heidrun BethgeDatenbanken14 CREATE TABLE CREATE TABLE tabellenname ( feldname1 feldtyp feldoptionen, feldname2 feldtyp feldoptionen... [, index1, index2] );

Folie 15

Dr. Heidrun BethgeDatenbanken15 CREATE TABLE Beispiel CREATE TABLE teilnehmer ( TnNr int(11) NOT NULL PRIMARY KEY, Name varchar(12), Ort varchar(12), tnalter int(11) default NULL ) ; Alternativ: CREATE TABLE teilnehmer ( TnNr int(11) NOT NULL, Name varchar(12), Ort varchar(12), tnalter int(11) default NULL, PRIMARY KEY (TnNr) ) ;

Folie 16

Dr. Heidrun BethgeDatenbanken16 CREATE TABLE: Feldoptionen NOT NULL DEFAULT defaultwert PRIMARY KEY (gleichzeitig NOT NULL erforderlich) AUTO_INCREMENT (nicht ANSI, z.B. in MySQL)

Folie 17

Dr. Heidrun BethgeDatenbanken17 References in MySQL z.B. fr InnoDB-Tabellen implementiert nicht jedoch im Standard-Tabellenformat MyISAM dient der berwachung referentieller Integritt CREATE TABLE parent( parentid INT NOT NULL PRIMARY KEY) ENGINE=INNODB; CREATE TABLE child( childid INT NOT NULL PRIMARY KEY, parentid INT, INDEX par_ind (parentid), FOREIGN KEY (parentid) REFERENCES parent(parentid) ON DELETE SET NULL) ENGINE=INNODB;

Folie 18

Dr. Heidrun BethgeDatenbanken18 Namensgebung mglichst klare Feld- und Tabellennamen einheitliches Schema fr die Benennung von Feldern und Tabellen z.B. einheitlich: Singular title oder Plural titles Feldnamen evtl. Rckschluss auf Tabelle und/oder Datentyp

Folie 19

Dr. Heidrun BethgeDatenbanken19 MySQL-Datentypen Integer Tinyint8-Bit-Integer-128 - +127 Smallint16-Bit-Integer Mediumint24-Bit-Integer Int, Integer32-Bit-Integer Bigint64-Bit-Integer Attribut UNSIGNED: nur positive Zahlen. Ansonsten +- AUTO_INCREMENT (kein ANSI-SQL). Attribut PRIMARY KEY oder UNIQUE mssen gleichzeitig gesetzt sein Es gibt auch Int(x) (x-stellig), dient jedoch nur der Formatierung,keine Stelleneinschrnkung

Folie 20

Dr. Heidrun BethgeDatenbanken20 MySQL Datentypen Fliekomma FLOAT8 Stellen, 4 Byte REAL/DOUBLE16 Stellen, 8 Byte DOUBLE(6,3) hat 6 Stellen vor und 3 Stellen nach dem Dezimalpunkt 123456.789 Anz. Nachkommastellen festlegen bewirkt Rundung DECIMAL(p,s)p: gesamte Stellenanzahl, s: Nachkommazahlen. Festkommazahl. DECIMAL(8,3) 99999.999 bis 99999.999 NUMERIC, DEC Synonyme fr DECIMAL

Folie 21

Dr. Heidrun BethgeDatenbanken21 MySQL Datentypen Datum/Zeit DATE3 Byte '2012-05-10' Bereich 1000-01-01 bis 9999-12-31 TIME3 Byte '23:59:59' Bereich +/- 838:59:59 DATETIME 8 Byte '2012-05-10 23:59:59' YEAR 1 Byte Jahreszahl 1900-2155 TIMESTAMP Datum und Zeit in der Form '20120510235959' zw. 1970 und 2038. Wird bei nderung des Datensatzes automatisch aktualisiert.

Folie 22

Dr. Heidrun BethgeDatenbanken22 MySQL Datentypen Zeichenketten CHAR(n)max. 255 Zeichen, n Byte VARCHAR(n) variable Lnge, max. n Zeichen (

Dr. Heidrun BethgeDatenbanken23 MySQL Datentypen fr Binrdaten nicht ANSI! BLOB: Binary Large Object TINYBLOBBinrdaten mit var. Lnge, max. 255 Byte BLOB (2 16 -1 Byte), MEDIUMBLOB (2 24 -1 Byte), LONGBLOB (2 32 -1 Byte) Vorteil: auch Binrdateien unterliegen DB- Zugriffsschutz eventuell nur Links in der Datenbank abspeichern, Binrdaten in externen Dateien speichern -> schnellerer Datenbankzugriff

Folie 24

Dr. Heidrun BethgeDatenbanken24 MySQL Datentypen fr Aufzhlungen ENUM Auswahl einer von max. 65535 Zeichenketten (1-2 Byte) CREATE TABLE testenum (color ENUM (`red, `green, `blue, `white)) INSERT testenum VALUES (`red) SET Kombination von max. 255 Zeichenketten, Mehrfachauswahl mglich (1-8 Byte) CREATE TABLE testset (fontattr SET (`bold, `italic, `underlined)) INSERT testset VALUES (`bold,italic)

Folie 25

Dr. Heidrun BethgeDatenbanken25 Indizes Beschleunigen Zugriff auf DB verlangsamen nderungen in DB und bentigen Speicherplatz Maximal 16 Indizes pro Tabelle auch fr mehrere Felder gleichzeitig Index sinnlos, wenn Feld sehr viele gleiche Eintrge hat -> Normalisierung! UNIQUE macht Index effizienter, eineindeutig PRIMARY Primrschlssel Beschrnkung der Indexlnge bei CHAR und VARCHAR mglich, bei xxxTEXT und BLOB erforderlich.

Folie 26

Dr. Heidrun BethgeDatenbanken26 Indizes II MySQL kann Indizes in folgenden Fllen nicht nutzen: Ungleichoperationen (WHERE column !=...) Funktionen (WHERE DAY(datum)=...) Primr- und Fremdschlssel haben untersch. Typ LIKE `%abc`

Folie 27

Dr. Heidrun BethgeDatenbanken27 CREATE INDEX Folgende Ausdrcke sind gleichwertig fr die Indexerstellung: CREATE TABLE booktitle ( titleID..., title..., publisher..., year..., PRIMARY KEY..., INDEX idxtitle (title)) CREATE INDEX idxtitle on booktitle (title) ALTER TABLE booktitle ADD INDEX idxtitle (title)

Folie 28

Dr. Heidrun BethgeDatenbanken28 Tabellendateien MySQL tabelle.frm Tabellenschema (Datentypen, Indizes...) tabelle.myd MyISAM-Tabellendaten tabelle.myi MyISAM-Indizes (alle Indizes der Tab.) tabelle.db Berkeley-DB-Tabelle (Daten+Indizes) hier sind auch Transaktionen mglich

Folie 29

Dr. Heidrun BethgeDatenbanken29 ALTER TABLE ALTER TABLE tblname tbloptions ALTER TABLE ADD newcolname coltype coloptions [FIRST|AFTER existingcolumn] ALTER TABLE tblname MODIFY colname coltype coloptions ALTER TABLE tblname CHANGE oldcolname newcolname coltype coloptions ALTER TABLE tblname DROP colname ALTER TABLE tblname DROP INDEX iname ALTER TABLE tblname DROP PRIMARY KEY

Folie 30

Dr. Heidrun BethgeDatenbanken30 INSERT

Folie 31

Dr. Heidrun BethgeDatenbanken31 Beispiel INSERT Fge einen neuen Teilnehmer (TnNr 200) fr Kurs G08 und AngNr 1 in die Gebhren-Relation ein. Die Teilnahmegebhr sei noch nicht bekannt. INSERT INTO gebuehren VALUES (1,G08, 200,NULL) alternativ: INSERT INTO gebuehren (angnr, kursnr, tnnr) VALUES (1,G08, 200,NULL) Fge ein neues Kursangebot (AngNr 3) fr G08 fr den 15.3.2014 in Ulm ein. INSERT INTO angebot VALUES (3,G08,15.03.2014,Ulm)

Folie 32

Dr. Heidrun BethgeDatenbanken32 SELECT

Folie 33

Dr. Heidrun BethgeDatenbanken33 SELECT * Gib die Teilnehmer-Relation komplett aus. SELECT * FROM Teilnehmer;

Folie 34

Dr. Heidrun BethgeDatenbanken34 SELECT FROM Gib aus der Teilnehmer-Relation die Attribute Name, TnNr, Ort aus, und zwar in dieser Reihenfolge. SELECT Name, TnNr, Ort FROM Teilnehmer;

Folie 35

Dr. Heidrun BethgeDatenbanken35 SELECT DISTINCT Gib alle Orte aus, aus denen Teilnehmer kommen. SELECT ortSELECT DISTINCT ortFROM teilnehmer; ohne Duplikateliminierungmit Duplikateliminierung

Folie 36

Dr. Heidrun BethgeDatenbanken36 SELECT FROM WHERE SELECT TnNr, Name, Ort FROM Teilnehmer WHERE Ort = 'Ulm'; SELECT * FROM Teilnehmer WHERE Ort = 'Ulm'; SELECT * FROM Teilnehmer WHERE TnNr >= 100

Folie 37

Dr. Heidrun BethgeDatenbanken37 AND, OR Vergleichs-Ausdrcke knnen mit AND und OR verknpft werden:... TnNr > 100 AND TnNR < 200... Ort = 'Ulm' OR Ort = 'Neu-Ulm'... (Ort = 'Ulm' OR Ort = 'Neu-Ulm') AND TnNr > 100

Folie 38

Dr. Heidrun BethgeDatenbanken38 Vergleichsoperatoren... TnNr BETWEEN 100 AND 200 ist quivalent zu:... TnNr >= 100 AND TnNR

Dr. Heidrun BethgeDatenbanken41 Berechnete Felder Gib aus, bei welcher Kursliteratur ein Unterbestand (Bestand kleiner als Bedarf) vorhanden ist und wie hoch die Beschaffungskosten hierfr sind. SELECT KursNr, Bedarf-Bestand, (Bedarf- Bestand)*Preis FROM KursLit WHERE Bedarf > Bestand; Problem: wenig aussagekrftige Spaltennamen

Folie 42

Dr. Heidrun BethgeDatenbanken42 Felder benennen Abhilfe: Umbenennen der Attribute SELECT KursNr, Bedarf-Bestand AS Minusbestand, (Bedarf-Bestand)*Preis AS Beschaffungskosten FROM KursLit WHERE Bedarf > Bestand;

Folie 43

Dr. Heidrun BethgeDatenbanken43 einige String-Funktionen LCASE(string) string in Kleinbuchstaben UCASE(string) string in Grobuchstaben LENGTH(string) LOCATE(suchstr, string) LEFT(string, anz), RIGHT(string, anz) SUBSTRING(string, start [,lnge]) LTRIM(string), RTRIM(string) Entfernen von Blanks (vorne/hinten) TRIM(string) Entfernen von Blanks vorne+hinten CHAR(num.Wert) CHAR(123)->123 (String)

Folie 44

Dr. Heidrun BethgeDatenbanken44 Datums-Funktionen Gib die Angebotsrelation, sortiert nach Datum, mit separaten Spalten fr Tag, Monat und Jahr aus. SELECT AngNr, KursNr, day(datum) AS Tag, month(datum) AS Monat, year(datum) AS Jahr, Ort FROM Angebot ORDER BY Datum;

Folie 45

Dr. Heidrun BethgeDatenbanken45 ORDER BY Gib alle Kurs-Angebote fr die Kurse 'P13' und 'I09' sortiert nach Datum (das aktuellste zuerst), Angebotsnummer und Ort (jeweils aufsteigend) aus. SELECT * FROM Angebot WHERE KursNr = P13 OR KursNr = I09 ORDER BY Datum DESC, AngNr, Ort; Die ORDER BY-Klausel kann in einem Anfrageausdruck nur einmal und zwar ganz am Schlu auftreten.

Folie 46

Dr. Heidrun BethgeDatenbanken46 Operatoren und Konventionen NOT! OR|| AND&& =, ,!=, =, > +, -, *, / Zeichenkette bzw. Zeichenkette Zahlen: -2002 -123.456

Folie 47

Dr. Heidrun BethgeDatenbanken47 Aggregatfunktionen COUNT(Ausdruck): Anzahl Felder NULL COUNT(*): Anzahl Felder AVG(Ausdruck): Mittelwert MIN(Ausdruck): Minimum MAX(Ausdruck): Maximum SUM(Ausdruck): Summe STD(Ausdruck): Standardabweichung

Folie 48

Dr. Heidrun BethgeDatenbanken48 Beispiele Aggregatfunktionen Gib die Anzahl aller Kursteilnehmer aus 'Ulm' aus. SELECT COUNT(*) FROM Teilnehmer WHERE Ort = Ulm; Gib das durchschnittliche Gehalt aller Kursleiter aus. SELECT AVG(Gehalt) FROM Kursleiter; Wenn eine SELECT-Anweisung eine Aggregatfunktion enthlt, so wird nur max. ein Resultat-Tupel erzeugt.

Folie 49

Dr. Heidrun BethgeDatenbanken49 Verbund alte Form

Folie 50

Dr. Heidrun BethgeDatenbanken50 Beispiel Verbund Gib aus, welche Kursangebote (Ausgabe: KursNr, AngNr) von welchen Kursleitern (Ausgabe: Name) durchgefhrt werden. Bisherige Form: SELECT f.KursNr, f.AngNr, lt.Name FROM Fuehrt_durch f, Kursleiter lt WHERE f.PersNr = lt.PersNr;

Folie 51

Dr. Heidrun BethgeDatenbanken51 JOIN join-condition-j1 kann nur Attribute referenzieren, die im Scope von table1 oder table2 auftreten, nicht jedoch solche aus table3. join-condition-j2 kann auf alle Attribute der zuvor spezifizierten Tabellen (linke Seite) sowie die von table3 zugreifen. Diese Join-Form macht insbesondere dann Sinn, wenn die Join-Spalten verschiedene Namen haben und/oder andere Join-Bedingungen als "=" ausgedrckt werden sollen.

Folie 52

Dr. Heidrun BethgeDatenbanken52 JOIN Beispiel Gib aus, welche Kursangebote (Ausgabe: KursNr, AngNr) von welchen Kursleitern (Ausgabe: Name) durchgefhrt werden. SELECT KursNr, AngNr, Name FROM Fuehrt_durch INNER JOIN Kursleiter ON Kursleiter.PersNr = Fuehrt_durch.PersNr Krzere Schreibweise mit Alias-Namen fr die Tabellen: SELECT f.KursNr, f.AngNr, lt.Name FROM Fuehrt_durch f INNER JOIN Kursleiter lt ON f.PersNr = lt.PersNr

Folie 53

Dr. Heidrun BethgeDatenbanken53 NATURAL JOIN SELECT select-list FROM table1 NATURAL JOIN table2 Das ID-Feld in den beiden Tabellen muss den gleichen Namen haben und vom gleichen Typ sein

Folie 54

Dr. Heidrun BethgeDatenbanken54 OUTER JOIN SELECT select-list FROM table1 LEFT [OUTER] JOIN table2 ON... SELECT select-list FROM table1 RIGHT [OUTER] JOIN table2 ON... nicht in MySQL: SELECT select-list FROM table1 FULL [OUTER] JOIN table2 ON...

Folie 55

Dr. Heidrun BethgeDatenbanken55 LEFT JOIN - Beispiel Gib alle Kurse mit ihren Voraussetzungen aus. SELECT k.KursNr, k.Titel, v.VorNr Voraus_KursNr FROM Kurs k LEFT JOIN Vorauss v ON k.KursNr = v.KursNr;

Folie 56 Angebot -> Fuehrt_durch -> Kursleiter (KursNr) (AngNr,KursNr) (PersNr) Zu 4: Kursleiter">

Dr. Heidrun BethgeDatenbanken56 Vorgehensweise bei Join 1. Auszugebende Werte/Attribute bestimmen 2. Potentiell relevante Entities bestimmen 3. "Join-Pfad" + Join-Attribute bestimmen 4. Optional: "Abwahl" der nicht bentigten Entities Gib aus, welche Kursangebote (Ausgabe: KursNr, Titel, AngNr, Datum, Ort) von welchen Kursleitern (Ausgabe: PersNr) durchgefhrt werden. Zu 1: KursNr, Titel, AngNr, Datum, Ort, PersNr Zu 2: Kurs, Angebot, Kursleiter Zu 3: Kurs -> Angebot -> Fuehrt_durch -> Kursleiter (KursNr) (AngNr,KursNr) (PersNr) Zu 4: Kursleiter

Folie 57

Dr. Heidrun BethgeDatenbanken57 Bsp. Join SELECT k.KursNr, k.Titel, f.AngNr, a.Datum, a.Ort, f.PersNr FROM Kurs k INNER JOIN Angebot a ON k.KursNr = a.KursNr INNER JOIN Fuehrt_durch f ON a.AngNr = f.AngNr AND a.KursNr = f.KursNr; alt: SELECT k.KursNr, k.Titel, f.AngNr, a.Datum, a.Ort, f.PersNr FROM Kurs k, Angebot a, Fuehrt_durch f WHERE k.KursNr = a.KursNr AND a.AngNr = f.AngNr AND a.KursNr = f.KursNr;

Folie 58

Dr. Heidrun BethgeDatenbanken58 Aufgaben Join I 1.Gib aus, welche Teilnehmer (Ausgabe: TnNr) an Kurs 'G08' oder 'G10' (Ausgabe: KursNr, AngNr, Ort) teilnehmen. 2.Gib alle Kursangebote (Ausgabe: alles) zusammen mit den vorliegenden Anmeldungen (TnNr) aus.

Folie 59

Dr. Heidrun BethgeDatenbanken59 Aufgaben Join II 2.Gib alle Kursangebote (Ausgabe: alles) zusammen mit den vorliegenden Anmeldungen (TnNr) aus. 3.Wie Aufgabe 2, aber zustzlich zur Teilnehmernummer soll jeweils auch noch der Teilnehmername ausgegeben werden. 4.Gib alle Kursangebote aus, fr die sich kein Teilnehmer angemeldet hat. Hinweis: WHERE... IS [NOT] NULL

Folie 60

Dr. Heidrun BethgeDatenbanken60 Aufgaben Aggregatfunktionen 1.Gib das hchste, das niedrigste und das durchschnittliche Kursleiter-Gehalt sowie die Summe aller Gehlter aus. 2.Gib die Anzahl der verschiedenen Orte aus, aus denen die Kursteilnehmer kommen.

Folie 61

Dr. Heidrun BethgeDatenbanken61 Gruppierung - Problem Gib auf Basis der Teilnehmer-Relation eine ortsbezogene Teilnehmerstatistik aus. D.h. gib aus, wie viele Teilnehmer aus welchem Ort kommen (Ausgabe: Ortsname, Teilnehmerzahl). SELECT Ort, COUNT... ? FROM Teilnehmer Lsung: Gruppierung SELECT Ort, COUNT(Ort) Anzahl FROM Teilnehmer GROUP BY Ort;

Folie 62

Dr. Heidrun BethgeDatenbanken62 Group by Having Syntax

Folie 63

Dr. Heidrun BethgeDatenbanken63 Group By GROUP BY bewirkt (interne) Teilmengenbildung (Gruppierung) der Ergebnis-Relation entsprechend dem Gruppierungsattribut bzw. den Attributen (-> Menge von Mengen). Aggregationsfunktionen werden jeweils auf Teilmengen (falls vorhanden) angewandt. Eventuelle Joins werden vor Anwendung der Gruppierungsfunktion ausgefhrt, (Reihenfolge jetzt also: FROM - WHERE - GROUP BY- HAVING - ORDER BY - SELECT)

Folie 64

Dr. Heidrun BethgeDatenbanken64 Having Gruppierungsnebenbedingungen werden mittels HAVING-Klausel ausgedrckt (HAVING kann nur in Verbindung mit GROUP BY auftreten). Unterschied zwischen WHERE und HAVING: WHERE eliminiert Zeilen HAVING eliminiert Gruppen

Folie 65

Dr. Heidrun BethgeDatenbanken65 bung Group by - Having 1.Gib fr alle Kursangebote die Anzahl der Anmeldungen aus (Ausgabe: Angebote- Attribute, Anzahl Teilnehmer). 2.Wie vorheriges, aber nur solche Kurse ausgeben, fr die mehr als 2 Anmeldungen vorliegen. 3.Gib alle Kurstypen (KursNr) - mit Ausnahme der Grundlagenkurse ('G08', 'G10') - aus, fr die mehr als 4 Anmeldungen vorliegen.

Folie 66

Unterabfragen Dr. Heidrun BethgeDatenbanken66

Folie 67

Dr. Heidrun BethgeDatenbanken67 Motivation Unterabfragen I Welche Artikel haben einen Preis, der ber dem Durchschnittspreis liegt? SELECT AVG(Einzelpreis) AS avgpreis FROM Artikel; Ergebnis (z.B): avgpreis 134.05 SELECT Artikelname FROM Artikel WHERE Einzelpreis>134.05; zwei Abfragen notwendig

Folie 68

Dr. Heidrun BethgeDatenbanken68 Motivation Unterabfragen II Nur eine Abfrage mittels Unterabfrage (= Sub-Select, Subquery): SELECT Artikelname, Einzelpreis FROM Artikel WHERE Einzelpreis > ( SELECT AVG(Einzelpreis) FROM Artikel); Hier notwendig, dass Unterabfrage nur einen Wert liefert (scalar subquery) und dass die Datentypen der verglichenen Felder zusammenpassen.

Folie 69

Dr. Heidrun BethgeDatenbanken69 scalar subquery Unterabfragen, welche nur einen Wert zurckliefern, werden eingeleitet durch Vergleichsoperatoren (=;; ;>=) weiteres Beispiel: Suche alle Kunden heraus, die im gleichen Ort wohnen wie der Kunde Nr. 123 SELECT kundennr, ort FROM kunde WHERE ort = (SELECT ort FROM kunde WHERE kundennr=123);

Folie 70

Dr. Heidrun BethgeDatenbanken70 table subquery = Unterabfrage, welche eine Tabelle zurckliefert. Mit folgenden Bedingungen werden diese Unterabfragen eingeleitet ( ist Vergleichsoperator (=;; ;>=)): [NOT] INprft, ob ein Wert in der Ergebnismenge der Unterabfrage enthalten ist. [NOT] EXISTS prft, ob die Unterabfrage mindestens eine Zeile erbringt, die der Bedingung gengt. Liefert True/False zurck. ANY SOME prft, ob die Bedingung fr irgendeine Zeile der Unterabfrage zutrifft ALLprft, ob die Bedingung fr alle Zeilen der Unterabfrage zutrifft.

Folie 71

Dr. Heidrun BethgeDatenbanken71 Unterabfrage mit IN Suche alle Kunden heraus, die im gleichen Ort wohnen wie die Kunden Nr. 123 oder 124. SELECT kundennr, ort FROM kunde WHERE ort IN (SELECT ort FROM kunde WHERE kundennr=123 OR kundennr=124);

Folie 72

Dr. Heidrun BethgeDatenbanken72 Unterabfrage mit EXISTS Suche alle Artikelkategorien (Getrnke, Ssswaren...) heraus, denen mindestens ein Artikel zugeordnet ist: SELECT kategoriename FROM kategorie WHERE EXISTS (SELECT * FROM artikel WHERE artikel.kategorienr=kategorie.kategorienr) korrelierte Unterabfrage! obiges ist auch ohne Unterabfrage lsbar

Folie 73

Dr. Heidrun BethgeDatenbanken73 nicht-korrelierte und korrelierte Unterabfragen I

Folie 74

Dr. Heidrun BethgeDatenbanken74 nicht-korrelierte und korrelierte Unterabfragen II In Unterabfragen kann auf Tabellen von ueren Abfrage-Ausdrcken Bezug genommen werden: korrelierte Unterabfragen. Nachteil: Fr jeden Datensatz der aufrufenden Abfrage muss die Unterabfrage einzelnd ausgefhrt werden. Bei semantisch quivalenten Anfrageformulierungen mit und ohne Korrelation knnen sich stark unterschiedliche Antwortzeiten ergeben. Dies ist auch abhngig von Anfragebearbeitungs- Strategie bzw. Gte der Anfrage-Optimierung des verwendeten DBMS

Folie 75

Dr. Heidrun BethgeDatenbanken75 Syntax Unterabfrage mit EXISTS SELECT Attributliste FROM Rel1, Rel2,..., Reln WHERE [ NOT ] EXISTS ( Tabellenausdruck ) Der Subselect-Ausdruck (EXISTS) liefert nur TRUE (Treffer gefunden) oder FALSE (keine Treffer).

Folie 76

Dr. Heidrun BethgeDatenbanken76 Syntax Unterabfrage mit ANY / ALL / IN

Folie 77

Dr. Heidrun BethgeDatenbanken77 weiteres zu Unterabfragen = ANY ist quivalent zu IN, ALL ist quivalent zu NOT IN. Unterabfragen knnen geschachtelt sein, d.h. sie knnen wiederum Unterabfragen enthalten. Hufig knnen Unterabfragen auch ersetzt werden durch join-Abfragen ohne Unterabfragen. Dies geht dann nicht, wenn die Unterabfrage eine Mengenfunktion beinhaltet.

Folie 78

Dr. Heidrun BethgeDatenbanken78 Unterabfrage vs. Join Gib alle Kursangebote aus (Ausgabe: KursNr, AngNr), fr die Teilnehmer aus 'Ulm' gebucht haben. SELECT DISTINCT nt.KursNr, nt.AngNr FROM Nimmt_teil nt INNER JOIN Teilnehmer As t ON nt.TnNr = t.TnNr WHERE t.Ort = Ulm SELECT DISTINCT nt.KursNr, nt.AngNr FROM Nimmt_teil nt WHERE EXISTS (SELECT * FROM Teilnehmer AS t WHERE t.Ort = Ulm AND t.TnNr = nt.TnNr)

Folie 79

Dr. Heidrun BethgeDatenbanken79 Bsp. Unterabfrage mit ANY / IN SELECT DISTINCT nt.Kursnr, nt.AngNr FROM Nimmt_teil nt WHERE nt.TnNr = ANY (SELECT TnNr FROM Teilnehmer WHERE Ort = 'Ulm') SELECT DISTINCT nt.KursNr, nt.AngNr FROM Nimmt_teil nt WHERE nt.TnNr IN (SELECT TnNr FROM Teilnehmer WHERE Ort = 'Ulm')

Folie 80

Paarweise Vergleiche SELECT name, ort FROM teilnehmer WHERE (tnalter, ort) = (SELECT tnalter, ort FROM teilnehmer WHERE name='Schulze') AND name 'Schulze'; Keine paarweisen Vergleiche auf SQL-Server mglich. Dr. Heidrun BethgeDatenbanken80

Folie 81

Dr. Heidrun BethgeDatenbanken81 Unterabfragen umgehen SELECT * FROM tabelle1 WHERE tabelle1.id IN (SELECT tabelle2.id FROM tabelle2); wird ersetzt durch SELECT tabelle1.* FROM tabelle1 INNER JOIN tabelle2 ON tabelle1.id=tabelle2.id; SELECT * FROM tabelle1 WHERE id NOT IN (SELECT id FROM tabelle2); SELECT * FROM tabelle1 WHERE NOT EXISTS (SELECT id FROM tabelle2 WHERE tabelle1.id=tabelle2.id); wird ersetzt durch SELECT tabelle1.* FROM tabelle1 LEFT JOIN tabelle2 ON tabelle1.id=tabelle2.id WHERE tabelle2.id IS NULL;

Folie 82

Dr. Heidrun BethgeDatenbanken82 Aufgaben Unterabfragen 1.Gib die Personalnummer des Kursleiters mit dem niedrigsten Gehalt aus. 2.Wie lautet der Titel des Kurses mit der gnstigsten Kursliteratur? 3.Bestimme alle Kursangebote (Kurstitel und Angebotsnummer), zu denen es noch keine Teilnehmer gibt. Verwenden Sie eine korrelierte und eine nicht- korrelierte Unterabfrage. 4.Gib alle Kursleiter aus, die mindestens zwei Kurse leiten. Ausgabe: Personaldaten, Angebotsdaten, sortiert nach PersNr.

Folie 83

INLINE-VIEW = Unterabfragen im FROM oder JOIN SELECT * FROM (SELECT * FROM tabelle 1) t1 INNER JOIN (SELECT * FROM tabelle2) t2 ON t1.feld1=t2.feld2 Korrelierte Abfragen lassen sich hufig durch Inline- Views umgehen. Auswertungen auf gruppierten Daten mit Aggregaten hiermit mglich. Dr. Heidrun BethgeDatenbanken83

Folie 84

INLINE-VIEW Beispiel 1 Stadt, in der die meisten Teilnehmer wohnen: SELECT max(a.Anzahl) FROM (SELECT ort, count(*) Anzahl FROM teilnehmer GROUP BY ort) a MySQL: bentigt Alias fr Inline-View. SELECT ort, max(a.Anzahl) mglich. Oracle: bentigt keinen Alias, Ausgabe von ort nicht mglich. Dr. Heidrun BethgeDatenbanken84

Folie 85

INLINE-VIEW Beispiel 2 Alle Mitarbeiter, die mehr verdienen als der Durchschnitt ihrer Abteilung SELECT m1.nachname, m1.abteilungsnr FROM mitarbeiter m1 INNER JOIN (SELECT m2.abteilungsnr, AVG(m2.gehalt) AS durchschnitt FROM mitarbeiter m2 GROUP BY m2.abteilungsnr) g ON m1.abteilungsnr=g.abteilungsnr WHERE m1.gehalt > g.durchschnitt; Dr. Heidrun BethgeDatenbanken85

Folie 86

Unterabfragen im HAVING gleiches Vorgehen wie bei Unterabfragen in WHERE SELECT ort, count(*) Anzahl FROM teilnehmer GROUP BY ort HAVING ort NOT IN (SELECT ort FROM angebot); Dr. Heidrun BethgeDatenbanken86

Folie 87

Unterabfragen in CREATE, UPDATE, DELETE CREATE TABLE kopie_kursleiter AS SELECT * FROM kursleiter; UPDATE kopie_kursleiter SET gehalt=gehalt+100 WHERE persnr in (SELECT persnr FROM fuehrt_durch); DELETE FROM kopie_kursleiter WHERE persnr NOT IN (SELECT persnr FROM fuehrt_durch); Dr. Heidrun BethgeDatenbanken87

Folie 88

SELECT feld1, feld2 FROM tabelle1 UNION | UNION ALL | INTERSECT | MINUS/EXCEPT SELECT feld3, feld4 FROM tabelle2 UNION: Vereinigungsmenge ohne Duplikate UNION ALL: mit Duplikaten INTERSECT: Schnittmenge MINUS (Oracle) / EXCEPT (SQL-Server): Differenz INTERSECT und MINUS in MySQL nicht vorhanden SET-Operatoren Dr. Heidrun BethgeDatenbanken88

Folie 89

Beispiele SET-Operatoren Gib alle Orte aus, wo Angebote stattfinden oder Teilnehmer wohnen. SELECT ort FROM angebot UNION SELECT ort FROM teilnehmer; Gib alle Angebotsorte aus, wo gleichzeitig Teilnehmer wohnen. SELECT ort FROM angebot INTERSECT SELECT ort FROM teilnehmer; Gib alle Angebotsorte aus, wo kein Teilnehmer wohnt. SELECT ort FROM angebot MINUS SELECT ort FROM teilnehmer; Dr. Heidrun BethgeDatenbanken89

Folie 90

Regeln fr SET-Operatoren Die erste SELECT-Anweisung gibt vor: Anzahl Felder Feldnamen Feldreihenfolge Felddatentypen Die folgende(n) SELECT-Anweisung(en) mssen bereinstimmen in Anzahl Felder Feldreihenfolge Felddatentypen ORDER BY wird einmal fr alle Datenstze gemeinsam am Ende der Anweisung verwendet Dr. Heidrun BethgeDatenbanken90

Folie 91

Beispiel UNION SELECT 'T' typ, name, ort FROM teilnehmer WHERE name like 'M%' UNION SELECT 'K' typ, name, NULL FROM kursleiter WHERE name like 'M%' ORDER BY name; Dr. Heidrun BethgeDatenbanken91

Folie 92

UNION anstelle FULL OUTER JOIN Nicht jedes DBMS hat die FULL OUTER JOIN-Funktionalitt! Gib alle Lieferanten und alle von ihnen gelieferten Produkte aus. Lieferanten ohne Produkte und Produkte ohne Lieferanten sollen ebenfalls mit ausgegeben werden. SELECT l.firma, p.produktname FROM lieferant l LEFT JOIN produkt p ON l.lieferantid=p.lieferantid UNION SELECT l.firma, p.produktname FROM lieferant l RIGHT JOIN produkt p ON l.lieferantid=p.lieferantid Wie kann dieses Beispiel gelst werden ohne LEFT/RIGHT JOIN? Dr. Heidrun BethgeDatenbanken92

Folie 93

Aufgaben SET-Operatoren Lsen Sie folgende Aufgaben in Oracle auf dem Uni- Schema mittels SET-Operatoren. Lassen sich die Aufgaben auch ohne SET-Operator und ohne Unterabfrage lsen? Warum (nicht)? 1.Alle Studenten-Namen, die bei Sokrates Vorlesungen hren, aber keine Vorlesungen bei Popper hren. 2.Alle Vorlesungen mit 4 Semesterwochenstunden (sws), in der noch kein Student eine Prfung abgelegt hat. 3.Alle Vorlesungen mit 4 Semesterwochenstunden (sws), in der mindestens ein Student eine Prfung abgelegt hat. 4.Alle Professoren, die keine Vorlesung halten, aber einen Assistenten haben. Dr. Heidrun BethgeDatenbanken93