93
Dr. Heidrun Bethge Datenbanken 1 MySQL relationales Datenbanksystem als Open-Source Internet- Datenbank sehr weit verbreitet, vorhandene Funktionalität ist gut&schnell kostenfreie MySQL Community Edition Diverse MySQL Server Lizenzmodelle Dem kostenfreien MySQL fehlen jedoch einige Features, die

Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalität ist

Embed Size (px)

Citation preview

Page 1: Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalität ist

Dr. Heidrun Bethge Datenbanken 1

MySQL• relationales Datenbanksystem• als Open-Source Internet-Datenbank

sehr weit verbreitet, vorhandene Funktionalität ist gut&schnell

• kostenfreie MySQL Community Edition• Diverse MySQL Server Lizenzmodelle• Dem kostenfreien MySQL fehlen jedoch

einige Features, die in großen kommerziellen DB-Systemen eine Selbstverständlichkeit sind

Page 2: Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalität ist

Dr. Heidrun Bethge Datenbanken 2

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) Einschränkungen

• Programmiersprachen: Für MySQL-Anwendungen gibt es viele APIs (Application Programming Interfaces) bzw. Bibliotheken. Programmierung in C, C++, Java, Perl, PHP, Python, Tcl möglich.

• ODBC: Durch ODBC-Schnittstelle kann MySQL darüber hinaus über die gängigen Windows-Programmiersprachen angesprochen werden.

Page 3: Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalität ist

Dr. Heidrun Bethge Datenbanken 3

Eigenschaften von MySQL II• Plattformunabhängig: MySQL läuft 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.

• Unterstützung durch weltweite, große MySQL-Entwicklergemeinde.

Page 4: Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalität ist

Dr. Heidrun Bethge Datenbanken 4

Einschränkungen 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

(eingeschränkt ab 5.1)• Transaktionen (eingeschränkt ab 4.0).• keine freien DataWarehouse-Funktionen• keine freien Recovery-Mechanismen

Page 5: Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalität ist

Dr. Heidrun Bethge Datenbanken 5

Windows oder Unix/Linux? • MySQL wurde ursprünglich für Unix/Linux

verwendet und erst später auf Windows portiert.

• Auf im Internet öffentlich zugänglichen Servern läuft MySQL fast nur unter Linux. -> intensiverer Praxiseinsatz -> intensiver auf diesen Systemen getestet

• effizienterer Einsatz unter Unix/Linux, da MySQL für deren Prozess- und Thread-Modelle optimiert ist

• für Entwicklungsumgebung egal: hier funktioniert Windows genauso gut

Page 6: Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalität ist

Dr. Heidrun Bethge Datenbanken 6

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

Page 7: Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalität ist

Dr. Heidrun Bethge Datenbanken 7

MySQL-Monitor mysql• Nicht zu verwechseln mit dem MySQL-

Datenbank-Server, der evtl. als Dienst läuft.• Start über mysql/bin/mysql.exe• evtl auch aufrufen mit Parametern:

mysql –h localhost -u username –p• hier sind Administrations- und

Wartungsaufgaben interaktiv durchführbar; Kommandointerpreter

• jeder Befehl wird durch Semikolon beendet, Befehle können über mehrere Zeilen gehen

• Mit Pfeiltasten lassen sich Befehle wiederholen

Page 8: Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalität ist

Dr. Heidrun Bethge Datenbanken 8

erstes Beispielcreate 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;

Page 9: Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalität ist

Dr. Heidrun Bethge Datenbanken 9

Page 10: Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalität ist

Dr. Heidrun Bethge Datenbanken 10

Entwicklungsgeschichte SQL• SQL (früherer Name: SEQUEL) ist

zunächst 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 verfügbar: IBM, Oracle, DEC, ...

Page 11: Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalität ist

Standardisierungen SQL• Seit Anfang/Mitte der 80er Jahre

Standardisierungsaktivitäten am ANSI für 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 großen DBMS weitgehend kompatibel.

Dr. Heidrun Bethge Datenbanken 11

Page 12: Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalität ist

Dr. Heidrun Bethge Datenbanken 12

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, Löschen von Daten• DCL Data Control Language

Anlegen von Benutzern, Vergabe von Zugriffsrechten

• TCL Transaction Control LanguageSteuerung von Transaktionen

Page 13: Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalität ist

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 Einfügen von Leerzeichen, Leerzeilen, Tabs möglich.

Dr. Heidrun Bethge Datenbanken 13

Page 14: Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalität ist

Dr. Heidrun Bethge Datenbanken 14

CREATE TABLE

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

Page 15: Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalität ist

Dr. Heidrun Bethge Datenbanken 15

CREATE TABLE BeispielCREATE 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)) ;

Page 16: Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalität ist

Dr. Heidrun Bethge Datenbanken 16

CREATE TABLE: Feldoptionen

• NOT NULL • DEFAULT defaultwert• PRIMARY KEY (gleichzeitig NOT NULL

erforderlich)• AUTO_INCREMENT

(nicht ANSI, z.B. in MySQL)

Page 17: Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalität ist

Dr. Heidrun Bethge Datenbanken 17

References– in MySQL z.B. für InnoDB-Tabellen implementiert– nicht jedoch im Standard-Tabellenformat MyISAM– dient der Überwachung referentieller Integrität

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;

Page 18: Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalität ist

Dr. Heidrun Bethge Datenbanken 18

Namensgebung

• möglichst klare Feld- und Tabellennamen

• einheitliches Schema für die Benennung von Feldern und Tabellen

• z.B. einheitlich: Singular title oder Plural titles

• Feldnamen evtl. Rückschluss auf Tabelle und/oder Datentyp

Page 19: Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalität ist

Dr. Heidrun Bethge Datenbanken 19

MySQL-Datentypen Integer• Tinyint 8-Bit-Integer -128 -

+127• Smallint 16-Bit-Integer• Mediumint 24-Bit-Integer• Int, Integer 32-Bit-Integer• Bigint 64-Bit-Integer• Attribut UNSIGNED: nur positive Zahlen.

Ansonsten +-• AUTO_INCREMENT (kein ANSI-SQL).

Attribut PRIMARY KEY oder UNIQUE müssen gleichzeitig gesetzt sein

• Es gibt auch Int(x) (x-stellig), dient jedoch nur der Formatierung,keine Stelleneinschränkung

Page 20: Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalität ist

Dr. Heidrun Bethge Datenbanken 20

MySQL Datentypen Fließkomma• FLOAT 8 Stellen, 4 Byte• REAL/DOUBLE 16 Stellen, 8 Byte• DOUBLE(6,3) hat 6 Stellen vor und 3 Stellen

nach dem Dezimalpunkt123456.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 für DECIMAL

Page 21: Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalität ist

Dr. Heidrun Bethge Datenbanken 21

MySQL Datentypen Datum/Zeit• DATE 3 Byte '2012-05-10'

Bereich 1000-01-01 bis 9999-12-31• TIME 3 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.

Page 22: Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalität ist

Dr. Heidrun Bethge Datenbanken 22

MySQL Datentypen Zeichenketten• CHAR(n) max. 255 Zeichen, n Byte• VARCHAR(n) variable Länge, max. n Zeichen

(<256). Speicherbedarf: Tatsächliche Länge+1Byte

• CHAR und VARCHAR: Leerzeichen am Beginn & Ende werden gelöscht

• TINYTEXT variable Länge, max. 255 Zeichen, (n+1 Byte)

• TEXTmax. 216-1 Zeichen (n+2 Byte)• MEDIUMTEXT max. 224-1 Zeichen (n+3 Byte)• LONGTEXT max. 232-1 Zeichen (n+4 Byte)• xxxTEXT-Datentypen sind kein ANSI

Page 23: Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalität ist

Dr. Heidrun Bethge Datenbanken 23

MySQL Datentypen für Binärdaten• nicht ANSI!• BLOB: Binary Large Object• TINYBLOB Binärdaten mit var. Länge, max.

255 Byte• BLOB (216-1 Byte), MEDIUMBLOB (224-1

Byte), LONGBLOB (232-1 Byte)• Vorteil: auch Binärdateien unterliegen DB-

Zugriffsschutz• eventuell nur Links in der Datenbank

abspeichern, Binärdaten in externen Dateien speichern -> schnellerer Datenbankzugriff

Page 24: Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalität ist

Dr. Heidrun Bethge Datenbanken 24

MySQL Datentypen für Aufzählungen

• 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 möglich(1-8 Byte)

• CREATE TABLE testset (fontattr SET (`bold‘, `italic‘, `underlined‘))INSERT testset VALUES (`bold,italic‘)

Page 25: Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalität ist

Dr. Heidrun Bethge Datenbanken 25

Indizes• Beschleunigen Zugriff auf DB• verlangsamen Änderungen in DB und benötigen

Speicherplatz• Maximal 16 Indizes pro Tabelle• auch für mehrere Felder gleichzeitig• Index sinnlos, wenn Feld sehr viele gleiche Einträge

hat -> Normalisierung!• UNIQUE macht Index effizienter, eineindeutig• PRIMARY Primärschlüssel• Beschränkung der Indexlänge bei CHAR und

VARCHAR möglich, bei xxxTEXT und BLOB erforderlich.

Page 26: Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalität ist

Dr. Heidrun Bethge Datenbanken 26

Indizes II

MySQL kann Indizes in folgenden Fällen nicht nutzen:

• Ungleichoperationen (WHERE column != ...)• Funktionen (WHERE DAY(datum)=...)• Primär- und Fremdschlüssel haben untersch. Typ• LIKE `%abc`

Page 27: Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalität ist

Dr. Heidrun Bethge Datenbanken 27

CREATE INDEX

Folgende Ausdrücke sind gleichwertig für 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)

Page 28: Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalität ist

Dr. Heidrun Bethge Datenbanken 28

Tabellendateien MySQL• tabelle.frm

Tabellenschema (Datentypen, Indizes...)

• tabelle.mydMyISAM-Tabellendaten

• tabelle.myiMyISAM-Indizes (alle Indizes der Tab.)

• tabelle.dbBerkeley-DB-Tabelle (Daten+Indizes)hier sind auch Transaktionen möglich

Page 29: Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalität ist

Dr. Heidrun Bethge Datenbanken 29

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

Page 30: Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalität ist

Dr. Heidrun Bethge Datenbanken 30

INSERT

Page 31: Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalität ist

Dr. Heidrun Bethge Datenbanken 31

Beispiel INSERTFüge einen neuen Teilnehmer (TnNr 200) für Kurs G08 und AngNr 1 in die Gebühren-Relation ein. Die Teilnahmegebühr sei noch nicht bekannt.

INSERT INTO gebuehren VALUES (1,‘G08‘, 200,NULL)

alternativ:INSERT INTO gebuehren (angnr, kursnr, tnnr)

VALUES (1,‘G08‘, 200,NULL)Füge ein neues Kursangebot (AngNr 3) für G08 für den 15.3.2014 in Ulm ein.INSERT INTO angebot VALUES (3,‘G08‘,‘15.03.2014‘,‘Ulm‘)

Page 32: Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalität ist

Dr. Heidrun Bethge Datenbanken 32

SELECT

Page 33: Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalität ist

Dr. Heidrun Bethge Datenbanken 33

SELECT *

Gib die Teilnehmer-Relation komplett aus.

SELECT *

FROM Teilnehmer;

Page 34: Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalität ist

Dr. Heidrun Bethge Datenbanken 34

SELECT FROM

Gib aus der Teilnehmer-Relation die Attribute Name, TnNr, Ort aus, und zwar in dieser Reihenfolge.

SELECT Name, TnNr, Ort

FROM Teilnehmer;

Page 35: Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalität ist

Dr. Heidrun Bethge Datenbanken 35

SELECT DISTINCT

Gib alle Orte aus, aus denen Teilnehmer kommen.

SELECT ort SELECT DISTINCT ort

FROM teilnehmer; FROM teilnehmer;

ohne Duplikateliminierung mit Duplikateliminierung

Page 36: Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalität ist

Dr. Heidrun Bethge Datenbanken 36

SELECT FROM WHERE

SELECT TnNr, Name, Ort

FROM Teilnehmer

WHERE Ort = 'Ulm';

SELECT *

FROM Teilnehmer

WHERE Ort = 'Ulm';

SELECT *

FROM Teilnehmer

WHERE TnNr >= 100

Page 37: Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalität ist

Dr. Heidrun Bethge Datenbanken 37

AND, OR

• Vergleichs-Ausdrücke können mit AND und OR verknüpft werden:

• ... TnNr > 100 AND TnNR < 200• ... Ort = 'Ulm' OR Ort = 'Neu-Ulm'• ... (Ort = 'Ulm' OR Ort = 'Neu-Ulm')

AND TnNr > 100

Page 38: Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalität ist

Dr. Heidrun Bethge Datenbanken 38

Vergleichsoperatoren

• ... TnNr BETWEEN 100 AND 200ist äquivalent zu: ... TnNr >= 100 AND TnNR <= 200

• ... TnNr NOT BETWEEN 100 AND 200• ... TnNr IN (100, 200, 130, 400)• ... TnNr NOT IN (100, 200, 130, 400)

Page 39: Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalität ist

Dr. Heidrun Bethge Datenbanken 39

LIKE Beispiel

Page 40: Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalität ist

Dr. Heidrun Bethge Datenbanken 40

LIKE Verwendung• LIKE ist ein spezieller Vergleichsoperator für

CHAR-Attribute. Der Attributwert wird als Ganzes von links nach rechts zeichenweise mit dem angegebenen Muster verglichen.

• Maskierungszeichen („wild cards“):– "%" : an der durch "%" markierten Stelle können

beliebig viele (auch keine!) zu ignorierende Zeichen stehen.

– "_" : an der durch "_" (Unterstrich) markierten Stelle wird genau ein Zeichen ignoriert.

Page 41: Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalität ist

Dr. Heidrun Bethge Datenbanken 41

Berechnete Felder

„Gib aus, bei welcher Kursliteratur ein Unterbestand (Bestand kleiner als Bedarf) vorhanden ist und wie hoch die Beschaffungskosten hierfür sind.“

SELECT KursNr, Bedarf-Bestand, (Bedarf-Bestand)*PreisFROM KursLitWHERE Bedarf > Bestand;

Problem: wenig aussagekräftige Spaltennamen

Page 42: Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalität ist

Dr. Heidrun Bethge Datenbanken 42

Felder benennenAbhilfe: Umbenennen der Attribute

SELECT KursNr, Bedarf-Bestand AS Minusbestand,(Bedarf-Bestand)*Preis AS BeschaffungskostenFROM KursLitWHERE Bedarf > Bestand;

Page 43: Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalität ist

Dr. Heidrun Bethge Datenbanken 43

einige String-Funktionen• LCASE(string) string in Kleinbuchstaben• UCASE(string) string in Großbuchstaben• LENGTH(string)• LOCATE(suchstr, string)• LEFT(string, anz), RIGHT(string, anz)• SUBSTRING(string, start [,länge])• LTRIM(string), RTRIM(string) Entfernen von

Blanks (vorne/hinten)• TRIM(string) Entfernen von Blanks

vorne+hinten• CHAR(num.Wert) CHAR(123)->’123’ (String)

Page 44: Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalität ist

Dr. Heidrun Bethge Datenbanken 44

Datums-Funktionen„Gib die Angebotsrelation, sortiert nach Datum, mit separaten Spalten für Tag, Monat und Jahr aus.“

SELECT AngNr, KursNr, day(datum) AS Tag,month(datum) AS Monat, year(datum) AS Jahr, OrtFROM AngebotORDER BY Datum;

Page 45: Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalität ist

Dr. Heidrun Bethge Datenbanken 45

ORDER BY„Gib alle Kurs-Angebote für die Kurse 'P13' und 'I09' sortiert nach Datum (das aktuellste zuerst), Angebotsnummer und Ort (jeweils aufsteigend) aus.“SELECT *FROM AngebotWHERE 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.

Page 46: Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalität ist

Dr. Heidrun Bethge Datenbanken 46

Operatoren und Konventionen

• NOT!• OR ||• AND&&• =, <>,!= , <=, <, >=, >• +, -, *, /• ‘Zeichenkette‘ bzw. “Zeichenkette“• Zahlen: -2002 -123.456

Page 47: Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalität ist

Dr. Heidrun Bethge Datenbanken 47

Aggregatfunktionen

• COUNT(Ausdruck): Anzahl Felder <> NULL

• COUNT(*): Anzahl Felder• AVG(Ausdruck): Mittelwert• MIN(Ausdruck): Minimum• MAX(Ausdruck): Maximum• SUM(Ausdruck): Summe• STD(Ausdruck): Standardabweichung

Page 48: Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalität ist

Dr. Heidrun Bethge Datenbanken 48

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 enthält, so wird nur max. ein Resultat-Tupel erzeugt.

Page 49: Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalität ist

Dr. Heidrun Bethge Datenbanken 49

Verbund – alte Form

Page 50: Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalität ist

Dr. Heidrun Bethge Datenbanken 50

Beispiel Verbund

„Gib aus, welche Kursangebote (Ausgabe: KursNr, AngNr) von welchen Kursleitern (Ausgabe: Name) durchgeführt werden.“

Bisherige Form:

SELECT f.KursNr, f.AngNr, lt.NameFROM Fuehrt_durch f, Kursleiter ltWHERE f.PersNr = lt.PersNr;

Page 51: Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalität ist

Dr. Heidrun Bethge Datenbanken 51

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 "=" ausgedrückt werden sollen.

Page 52: Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalität ist

Dr. Heidrun Bethge Datenbanken 52

JOIN BeispielGib aus, welche Kursangebote (Ausgabe: KursNr, AngNr) von welchen Kursleitern (Ausgabe: Name) durchgeführt werden.

SELECT KursNr, AngNr, NameFROM Fuehrt_durch INNER JOIN Kursleiter ON Kursleiter.PersNr =

Fuehrt_durch.PersNr

Kürzere Schreibweise mit Alias-Namen für die Tabellen:

SELECT f.KursNr, f.AngNr, lt.NameFROM Fuehrt_durch fINNER JOIN Kursleiter lt ON f.PersNr = lt.PersNr

Page 53: Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalität ist

Dr. Heidrun Bethge Datenbanken 53

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

Page 54: Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalität ist

Dr. Heidrun Bethge Datenbanken 54

OUTER JOINSELECT select-listFROM table1 LEFT [OUTER] JOIN table2 ON ...

SELECT select-listFROM table1 RIGHT [OUTER] JOIN table2 ON ...

nicht in MySQL:SELECT select-listFROM table1 FULL [OUTER] JOIN table2 ON ...

Page 55: Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalität ist

Dr. Heidrun Bethge Datenbanken 55

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;

Page 56: Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalität ist

Dr. Heidrun Bethge Datenbanken 56

Vorgehensweise bei Join1. Auszugebende Werte/Attribute bestimmen

2. Potentiell relevante Entities bestimmen

3. "Join-Pfad" + Join-Attribute bestimmen

4. Optional: "Abwahl" der nicht benötigten Entities

„Gib aus, welche Kursangebote (Ausgabe: KursNr, Titel, AngNr, Datum, Ort) von welchen Kursleitern (Ausgabe: PersNr) durchgeführt 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

Page 57: Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalität ist

Dr. Heidrun Bethge Datenbanken 57

Bsp. JoinSELECT k.KursNr, k.Titel, f.AngNr, a.Datum, a.Ort, f.PersNrFROM Kurs kINNER JOIN Angebot a ON k.KursNr = a.KursNrINNER JOIN Fuehrt_durch f ON a.AngNr = f.AngNrAND a.KursNr = f.KursNr;

alt:SELECT k.KursNr, k.Titel, f.AngNr, a.Datum, a.Ort, f.PersNrFROM Kurs k, Angebot a, Fuehrt_durch fWHERE k.KursNr = a.KursNr AND a.AngNr = f.AngNr AND a.KursNr = f.KursNr;

Page 58: Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalität ist

Dr. Heidrun Bethge Datenbanken 58

Aufgaben Join I1. 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.

Page 59: Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalität ist

Dr. Heidrun Bethge Datenbanken 59

Aufgaben Join II

2. Gib alle Kursangebote (Ausgabe: alles) zusammen mit den vorliegenden Anmeldungen (TnNr) aus.

3. Wie Aufgabe 2, aber zusätzlich zur Teilnehmernummer soll jeweils auch noch der Teilnehmername ausgegeben werden.

4. Gib alle Kursangebote aus, für die sich kein Teilnehmer angemeldet hat.Hinweis: WHERE ... IS [NOT] NULL

Page 60: Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalität ist

Dr. Heidrun Bethge Datenbanken 60

Aufgaben Aggregatfunktionen

1. Gib das höchste, das niedrigste und das durchschnittliche Kursleiter-Gehalt sowie die Summe aller Gehälter aus.

2. Gib die Anzahl der verschiedenen Orte aus, aus denen die Kursteilnehmer kommen.

Page 61: Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalität ist

Dr. Heidrun Bethge Datenbanken 61

Gruppierung - ProblemGib 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

Lösung: GruppierungSELECT Ort, COUNT(Ort) AnzahlFROM TeilnehmerGROUP BY Ort;

Page 62: Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalität ist

Dr. Heidrun Bethge Datenbanken 62

Group by – Having Syntax

Page 63: Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalität ist

Dr. Heidrun Bethge Datenbanken 63

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 ausgeführt, (Reihenfolge jetzt also: FROM - WHERE -GROUP BY- HAVING - ORDER BY - SELECT)

Page 64: Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalität ist

Dr. Heidrun Bethge Datenbanken 64

Having

• Gruppierungsnebenbedingungen werden mittels HAVING-Klausel ausgedrückt (HAVING kann nur in Verbindung mit GROUP BY auftreten).

• Unterschied zwischen WHERE und HAVING:– WHERE eliminiert Zeilen– HAVING eliminiert Gruppen

Page 65: Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalität ist

Dr. Heidrun Bethge Datenbanken 65

Übung Group by - Having

1. Gib für alle Kursangebote die Anzahl der Anmeldungen aus (Ausgabe: Angebote-Attribute, Anzahl Teilnehmer).

2. Wie vorheriges, aber nur solche Kurse ausgeben, für die mehr als 2 Anmeldungen vorliegen.

3. Gib alle Kurstypen (KursNr) - mit Ausnahme der Grundlagenkurse ('G08', 'G10') - aus, für die mehr als 4 Anmeldungen vorliegen.

Page 66: Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalität ist

Unterabfragen

Dr. Heidrun Bethge Datenbanken 66

Page 67: Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalität ist

Dr. Heidrun Bethge Datenbanken 67

Motivation Unterabfragen IWelche 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

Page 68: Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalität ist

Dr. Heidrun Bethge Datenbanken 68

Motivation Unterabfragen IINur eine Abfrage mittels Unterabfrage (= Sub-Select, Subquery):

SELECT Artikelname, EinzelpreisFROM ArtikelWHERE Einzelpreis > ( SELECT AVG(Einzelpreis) FROM Artikel);

Hier notwendig, dass Unterabfrage nur einen Wert liefert („scalar subquery“) und dass die Datentypen der verglichenen Felder zusammenpassen.

Page 69: Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalität ist

Dr. Heidrun Bethge Datenbanken 69

scalar subqueryUnterabfragen, welche nur einen Wert zurückliefern, werden eingeleitet durch Vergleichsoperatoren (=;<>;<;<=;>;>=)

weiteres Beispiel: Suche alle Kunden heraus, die im gleichen Ort wohnen wie der Kunde Nr. 123

SELECT kundennr, ort FROM kundeWHERE ort = (SELECT ort FROM kunde

WHERE kundennr=123);

Page 70: Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalität ist

Dr. Heidrun Bethge Datenbanken 70

table subquery= Unterabfrage, welche eine Tabelle zurückliefert. Mit folgenden Bedingungen werden diese Unterabfragen eingeleitet (§ ist Vergleichsoperator (=;<>;<;<=;>;>=)):

[NOT] IN prüft, ob ein Wert in der Ergebnismenge der Unterabfrage enthalten ist.

[NOT] EXISTS

prüft, ob die Unterabfrage mindestens eine Zeile erbringt, die der Bedingung genügt. Liefert True/False zurück.

§ ANY § SOME

prüft, ob die Bedingung für irgendeine Zeile der Unterabfrage zutrifft

§ ALL prüft, ob die Bedingung für alle Zeilen der Unterabfrage zutrifft.

Page 71: Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalität ist

Dr. Heidrun Bethge Datenbanken 71

Unterabfrage mit IN

Suche alle Kunden heraus, die im gleichen Ort wohnen wie die Kunden Nr. 123 oder 124.

SELECT kundennr, ort FROM kundeWHERE ort IN (SELECT ort FROM kunde

WHERE kundennr=123 OR kundennr=124);

Page 72: Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalität ist

Dr. Heidrun Bethge Datenbanken 72

Unterabfrage mit EXISTSSuche alle Artikelkategorien (Getränke, Süsswaren...) heraus, denen mindestens ein Artikel zugeordnet ist:

SELECT kategoriename FROM kategorieWHERE EXISTS (SELECT * FROM artikel

WHERE artikel.kategorienr=kategorie.kategorienr)

korrelierte Unterabfrage!

obiges ist auch ohne Unterabfrage lösbar

Page 73: Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalität ist

Dr. Heidrun Bethge Datenbanken 73

nicht-korrelierte und korrelierte Unterabfragen I

Page 74: Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalität ist

Dr. Heidrun Bethge Datenbanken 74

nicht-korrelierte und korrelierte Unterabfragen II

• In Unterabfragen kann auf Tabellen von äußeren Abfrage-Ausdrücken Bezug genommen werden: korrelierte Unterabfragen.

• Nachteil: Für jeden Datensatz der aufrufenden Abfrage muss die Unterabfrage einzelnd ausgeführt werden.

• Bei semantisch äquivalenten Anfrageformulierungen mit und ohne Korrelation können sich stark unterschiedliche Antwortzeiten ergeben.

• Dies ist auch abhängig von Anfragebearbeitungs-Strategie bzw. Güte der Anfrage-Optimierung des verwendeten DBMS

Page 75: Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalität ist

Dr. Heidrun Bethge Datenbanken 75

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).

Page 76: Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalität ist

Dr. Heidrun Bethge Datenbanken 76

Syntax Unterabfrage mit ANY / ALL / IN

Page 77: Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalität ist

Dr. Heidrun Bethge Datenbanken 77

weiteres zu Unterabfragen• = ANY ist äquivalent zu IN,

<> ALL ist äquivalent zu NOT IN.• Unterabfragen können geschachtelt sein, d.h.

sie können wiederum Unterabfragen enthalten.

• Häufig können Unterabfragen auch ersetzt werden durch join-Abfragen ohne Unterabfragen. Dies geht dann nicht, wenn die Unterabfrage eine Mengenfunktion beinhaltet.

Page 78: Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalität ist

Dr. Heidrun Bethge Datenbanken 78

Unterabfrage vs. JoinGib alle Kursangebote aus (Ausgabe: KursNr, AngNr), für die Teilnehmer aus 'Ulm' gebucht haben.

SELECT DISTINCT nt.KursNr, nt.AngNrFROM Nimmt_teil nt INNER JOIN Teilnehmer As t ON nt.TnNr = t.TnNrWHERE t.Ort = ’Ulm’

SELECT DISTINCT nt.KursNr, nt.AngNrFROM Nimmt_teil ntWHERE EXISTS

(SELECT *FROM Teilnehmer AS tWHERE t.Ort = ’Ulm’ AND t.TnNr = nt.TnNr)

Page 79: Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalität ist

Dr. Heidrun Bethge Datenbanken 79

Bsp. Unterabfrage mit ANY / INSELECT DISTINCT nt.Kursnr, nt.AngNrFROM Nimmt_teil ntWHERE nt.TnNr = ANY

(SELECT TnNrFROM TeilnehmerWHERE Ort = 'Ulm')

SELECT DISTINCT nt.KursNr, nt.AngNrFROM Nimmt_teil ntWHERE nt.TnNr IN

(SELECT TnNrFROM TeilnehmerWHERE Ort = 'Ulm')

Page 80: Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalität ist

Paarweise VergleicheSELECT name, ort

FROM teilnehmer

WHERE (tnalter, ort) = ( SELECT tnalter, ort

FROM teilnehmer

WHERE name='Schulze')

AND name <>'Schulze';

Keine paarweisen Vergleiche auf SQL-Server möglich.

Dr. Heidrun Bethge Datenbanken 80

Page 81: Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalität ist

Dr. Heidrun Bethge Datenbanken 81

Unterabfragen umgehenSELECT * FROM tabelle1 WHERE tabelle1.id IN (SELECT tabelle2.id FROM tabelle2); wird ersetzt durchSELECT tabelle1.* FROM tabelle1INNER 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 durchSELECT tabelle1.* FROM tabelle1 LEFT JOIN tabelle2 ON tabelle1.id=tabelle2.id WHERE tabelle2.id IS NULL;

Page 82: Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalität ist

Dr. Heidrun Bethge Datenbanken 82

Aufgaben Unterabfragen1. Gib die Personalnummer des Kursleiters mit

dem niedrigsten Gehalt aus.

2. Wie lautet der Titel des Kurses mit der günstigsten 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.

Page 83: Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalität ist

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 häufig durch Inline-Views umgehen.

• Auswertungen auf gruppierten Daten mit Aggregaten hiermit möglich.

Dr. Heidrun Bethge Datenbanken 83

Page 84: Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalität ist

INLINE-VIEW Beispiel 1Stadt, in der die meisten Teilnehmer wohnen:

SELECT max(a.Anzahl)

FROM (SELECT ort, count(*) Anzahl

FROM teilnehmer

GROUP BY ort) a

MySQL: benötigt Alias für Inline-View. SELECT ort, max(a.Anzahl) möglich.

Oracle: benötigt keinen Alias, Ausgabe von ort nicht möglich.

Dr. Heidrun Bethge Datenbanken 84

Page 85: Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalität ist

INLINE-VIEW Beispiel 2Alle 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 Bethge Datenbanken 85

Page 86: Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalität ist

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 Bethge Datenbanken 86

Page 87: Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalität ist

Unterabfragen in CREATE, UPDATE, DELETE

• CREATE TABLE kopie_kursleiter ASSELECT * FROM kursleiter;

• UPDATE kopie_kursleiter SET gehalt=gehalt+100WHERE persnr in (SELECT persnr

FROM fuehrt_durch);

• DELETE FROM kopie_kursleiter WHERE persnr NOT IN (SELECT persnr

FROM fuehrt_durch);Dr. Heidrun Bethge Datenbanken 87

Page 88: Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalität ist

SELECT feld1, feld2FROM tabelle1UNION | UNION ALL | INTERSECT | MINUS/EXCEPTSELECT feld3, feld4FROM 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 Bethge Datenbanken 88

Page 89: Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalität ist

Beispiele SET-OperatorenGib 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 Bethge Datenbanken 89

Page 90: Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalität ist

Regeln für SET-Operatoren• Die erste SELECT-Anweisung gibt vor:

– Anzahl Felder– Feldnamen– Feldreihenfolge– Felddatentypen

• Die folgende(n) SELECT-Anweisung(en) müssen übereinstimmen in– Anzahl Felder– Feldreihenfolge– Felddatentypen

• ORDER BY wird einmal für alle Datensätze gemeinsam am Ende der Anweisung verwendet

Dr. Heidrun Bethge Datenbanken 90

Page 91: Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalität ist

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 Bethge Datenbanken 91

Page 92: Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalität ist

UNION anstelle FULL OUTER JOINNicht jedes DBMS hat die FULL OUTER JOIN-Funktionalität!

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 gelöst werden ohne LEFT/RIGHT JOIN?

Dr. Heidrun Bethge Datenbanken 92

Page 93: Dr. Heidrun BethgeDatenbanken1 MySQL relationales Datenbanksystem als Open-Source Internet-Datenbank sehr weit verbreitet, vorhandene Funktionalität ist

Aufgaben SET-Operatoren

Lösen Sie folgende Aufgaben in Oracle auf dem Uni-Schema mittels SET-Operatoren. Lassen sich die Aufgaben auch ohne SET-Operator und ohne Unterabfrage lösen? Warum (nicht)?

1. Alle Studenten-Namen, die bei Sokrates Vorlesungen hören, aber keine Vorlesungen bei Popper hören.

2. Alle Vorlesungen mit 4 Semesterwochenstunden (sws), in der noch kein Student eine Prüfung abgelegt hat.

3. Alle Vorlesungen mit 4 Semesterwochenstunden (sws), in der mindestens ein Student eine Prüfung abgelegt hat.

4. Alle Professoren, die keine Vorlesung halten, aber einen Assistenten haben.

Dr. Heidrun Bethge Datenbanken 93