SQL
Werbung
Datenbanksysteme: SQL Prof. Dr. Carl-Christian Kanne Wednesday, November 19, 2008 1 Die Anfragesprache SQL • “Structured Query Language” • Ursprung: SEQUEL (IBM Research) • Standard (ANSI/ISO) • • • • • SQL1 (SQL-86) SQL2 (SQL-92) SQL:1999 (SQL-3) SQL:2003 SQL:2006 Wednesday, November 19, 2008 2 SQL-Teilsprachen • Data Definition Language DDL • Logisches und physisches Schema beschreiben • Data Manipulation Language DML • • Anfragen Updates Wednesday, November 19, 2008 3 Uni-Schema voraussetzen MatrNr Name Semester Vorgänger Studenten N N Name Fachgebiet Wednesday, November 19, 2008 N Titel lesen 1 arbeitenFür VorlNr SWS Vorlesungen prüfen N Assistenten M M M Note PersNr hören N Nachfolger 1 1 Professoren PersNr Rang Raum Name 4 Die relationale Uni-DB PersNr 2125 2126 2127 2133 2134 2136 2137 Professoren Name Rang Sokrates C4 Russel C4 Kopernikus C3 Popper C3 Augustinus C3 Curie C4 Kant C4 Raum 226 232 310 52 309 36 7 voraussetzen Vorgänger Nachfolger 5001 5041 5001 5043 5001 5049 5041 5216 5043 5052 5041 5052 5052 5259 prüfen MatrNr VorlNr PersNr Note 28106 5001 2126 1 25403 5041 2125 2 27550 4630 2137 2 Wednesday, November 19, 2008 Studenten MatrNr Name Semester 24002 Xenokrates 18 25403 Jonas 12 26120 Fichte 10 26830 Aristoxenos 8 27550 Schopenhauer 6 28106 Carnap 29120 Theophrastos 29555 Feuerbach 3 2 2 hören MatrNr VorlNr 26120 5001 27550 5001 27550 4052 28106 5041 28106 5052 28106 5216 28106 5259 29120 5001 29120 5041 29120 5049 29555 5022 25403 5022 Assistenten Persl Nr Name Fachgebiet Boss 3002 Platon Ideenlehre 2125 Syllogistik 2125 3003 Aristoteles 3004 Wittgenstein Sprachtheorie 3005 Rhetikus 3006 Newton 3007 Spinoza 2126 Planeten bewegung Keplersche Gesetze 2127 2127 Gott und Natur 2126 Vorlesungen VorlNr Titel SWS gelesen von 5001 Grundzüge 4 2137 5041 Ethik 4 2125 5043 Erkenntnistheorie 3 2126 5049 Mäeutik 2 2125 4052 5052 5216 Logik Wissenschaftstheorie Bioethik 4 3 2 2125 2126 2126 5259 Der Wiener Kreis 2 2133 5022 Glaube und Wissen 2 2134 4630 Die 3 Kritiken 4 2137 5 SQL-Datenmodell • SQL verwendet keine Relationen, sondern “Multisets” als grundlegenden Kollektionstyp • Duplikate erlaubt • Aber Schlüsselangabe möglich • Reihenfolge aber nicht festgelegt • Bestimmte Anfragekonstrukte erlauben jedoch Tupelordnung (siehe ORDER BY) Wednesday, November 19, 2008 6 SQL-Datenmodell: Domänen • character (n), char (n) • character varying (n), varchar (n) • numeric (p,s), integer • blob oder raw für sehr große binäre Daten • clob für sehr große String-Attribute • date für Datumsangaben • xml für XML-Dokumente Wednesday, November 19, 2008 7 DDL: Tabelle anlegen create table Professoren NULL “character(2) ” (PersNr integer not null, Name varchar (30) not null Rang character (2) ); character(2)NULL Wednesday, November 19, 2008 8 Einfache SQL-Anfrage select PersNr, Name from Professoren where Rang= ´C4´; PersNr 2125 2126 2136 2137 Wednesday, November 19, 2008 Name Sokrates Russel Curie Kant 9 Duplikateliminierung select distinct Rang from Professoren Rang C3 C4 Wednesday, November 19, 2008 10 Verknüpfung von Relationen Wer liest Mäutik? select Name from Professoren,Vorlesungen where PersNr = gelesenVon and Titel = `Grundzüge‘ ; ΠName(σPersNr=gelesenVon∧Titel=’Grundzüge’(Professoren × Vorlesungen)) Wednesday, November 19, 2008 11 FROM-Klausel from Professoren,Vorlesungen Vorlesungen Professoren PersNr Name Rang Raum VorlNr 2125 2126 Sokrates Russel C4 C4 226 232 5001 5041 Grundzüge Ethik 4 4 gelesen von 2137 2125 2127 Kopernikus C3 310 5043 Erkenntnistheorie 3 2126 2133 Popper C3 52 2134 Augustinus C3 309 5049 4052 Mäeutik Logik 2 4 2125 2125 2136 2137 Curie Kant C4 C4 36 7 Titel SWS ... × Wednesday, November 19, 2008 12 gelesen SWS von 4 2137 2 2125 PersNr Name Rang Raum VorlNr Titel 2125 2125 Sokrates Sokrates C4 C4 226 226 5001 5049 Grundzüge Mäeutik 2126 ... Russel ... C4 ... 232 ... 5001 ... Grundzüge ... 4 ... 2137 ... 2127 Kopernikus C3 310 5049 Mäeutik 2 2125 ... ... ... ... ... ... ... ... 2134 Augustinus C3 309 5001 Grundzüge 4 2137 2134 Augustinus C3 309 5041 Ethik 4 2125 ... 2136 ... 2137 ... ... Curie ... Kant ... ... C4 ... C4 ... ... 36 ... 7 ... ... 5041 ... 5049 ... ... Ethik ... Mäeutik ... ... 4 ... 2 ... ... 2125 ... 2125 ... where PersNr = gelesenVon and Titel = `Mäeutik‘ Wednesday, November 19, 2008 13 Projektion auf Ergebnisschema PersNr Name Rang Raum VorlNr Titel 2137 Sokrates C4 226 5001 Grundzüge gelesen SWS von 4 2137 select Name Name Sokrates Wednesday, November 19, 2008 14 Duplikate select Rang from Professoren • Vorsicht: SQL erzeugt ohne distinct immer Duplikate, im relationalen Modell nicht möglich • Korrekte SQL-Seite der Übersetzung also... Wednesday, November 19, 2008 15 Kanonische Übersetzung Allgemein hat eine (ungeschachtelte) SQLAnfrage die Form: Übersetzung in die relationale Algebra: Π A1, ..., An(σP (R1 x ... x Rk )) Π A1, ..., An σP select distinct A1, ..., An x from R1, ..., Rk x where P; x R1 Wednesday, November 19, 2008 Rk R3 R2 16 Namen in FROM-Klausel •Welche Studenten hören welche Vorlesungen? select Name, Titel from Studenten, hören,Vorlesungen where Studenten.MatrNr = hören.MatrNr and hören.VorlNr = Vorlesungen.VorlNr; •(Um-)Benennung der Relationen select s.Name, v.Titel from Studenten s, hören h,Vorlesungen v where s. MatrNr = h. MatrNr and h.VorlNr = v.VorlNr Wednesday, November 19, 2008 17 Mengenoperationen ( select Name from Assistenten ) union ( select Name from Professoren) •union Vereinigung •union all Vereinigung ohne Duplikateleminierung •intersect Durchschnitt •minus Mengendifferenz •Schemata müssen gleich sein! Wednesday, November 19, 2008 18 Aggregatfunktionen select avg (Semester) from Studenten min, max, avg, count, sum,... Wednesday, November 19, 2008 19 Gruppierung select gelesenVon, sum (SWS) from Vorlesungen group by gelesenVon; Wednesday, November 19, 2008 20 Filtern von Gruppen select gelesenVon, Name, sum (SWS) from Vorlesungen, Professoren where gelesenVon = PersNr and Rang = ´C4´ group by gelesenVon, Name having avg (SWS) >= 3; Wednesday, November 19, 2008 21 Besonderheiten bei Aggregatoperationen • • pro Gruppe ein Ergebnistupel alle Attribute in select-Klausel müssen auch in group by-Klausel stehen • stellt eindeutigen Wert pro Gruppe sicher Wednesday, November 19, 2008 22 Beispielauswertung from Professoren,Vorlesungen Vorlesungen Professoren PersNr Name Rang Raum VorlNr 2125 2126 Sokrates Russel C4 C4 226 232 5001 5041 Grundzüge Ethik 4 4 gelesen von 2137 2125 2127 Kopernikus C3 310 5043 Erkenntnistheorie 3 2126 2133 Popper C3 52 2134 Augustinus C3 309 5049 4052 Mäeutik Logik 2 4 2125 2125 2136 2137 Curie Kant C4 C4 36 7 Titel SWS ... × Wednesday, November 19, 2008 23 gelesen SWS von 4 2137 2 2125 PersNr Name Rang Raum VorlNr Titel 2125 2125 Sokrates Sokrates C4 C4 226 226 5001 5049 Grundzüge Mäeutik 2126 ... Russel ... C4 ... 232 ... 5001 ... Grundzüge ... 4 ... 2137 ... 2127 Kopernikus C3 310 5049 Mäeutik 2 2125 ... ... ... ... ... ... ... ... 2134 Augustinus C3 309 5001 Grundzüge 4 2137 2134 Augustinus C3 309 5041 Ethik 4 2125 ... 2136 ... 2137 ... ... Curie ... Kant ... ... C4 ... C4 ... ... 36 ... 7 ... ... 5041 ... 5049 ... ... Ethik ... Mäeutik ... ... 4 ... 2 ... ... 2125 ... 2125 ... where gelesenVon = PersNr and Rang = ´C4´ Wednesday, November 19, 2008 24 Gruppierung VorlN r Titel 5001 Grundzüge 5041 Ethik 5043 Erkenntnistheorie 5049 Mäeutik 4052 Logik 5052 Wissenschaftstheorie 5216 Bioethik 4630 Die 3 Kritiken SWS 4 4 3 2 4 3 2 4 gelesen PersNr von 2137 2125 2126 2125 2125 2126 2126 2137 2137 2125 2126 2125 2125 2126 2126 2137 Name Kant Sokrates Russel Sokrates Sokrates Russel Russel Kant Rang Raum C4 C4 C4 C4 C4 C4 C4 C4 7 226 232 226 226 232 232 7 group by gelesenVon, Name Wednesday, November 19, 2008 25 Gruppen filtern VorlN r Titel SWS 5041 5049 4052 Ethik Mäeutik Logik 4 2 4 2125 2125 2125 2125 2125 2125 Sokrates Sokrates Sokrates C4 C4 C4 226 226 226 5216 Bioethik 5043 Erkenntnistheorie 5052 Wissenschaftstheorie 2 3 3 2126 2126 2126 2126 2126 2126 Russel Russel Russel C4 C4 C4 232 232 232 5001 4630 4 4 2137 2137 2137 2137 Kant Kant C4 C4 7 7 Grundzüge Die 3 Kritiken gelesen PersNr von Name Rang Raum having avg(SWS)>=2 VorlN r gelesen PersNr von Titel SWS 5041 5049 4052 Ethik Mäeutik Logik 4 2 4 2125 2125 2125 2125 2125 2125 Sokrates Sokrates Sokrates C4 C4 C4 226 226 226 5001 4630 Grundzüge Die 3 Kritiken 4 4 2137 2137 2137 2137 Kant Kant C4 C4 7 7 Name Rang Raum select gelesenVon, Name, sum(SWS) Wednesday, November 19, 2008 26 Ergebnis gelesenVon Name sum(SWS) 2125 Sokrates 10 2137 Kant 8 Wednesday, November 19, 2008 27 Sortieren select PersNr, Name, Rang from Professoren order by Rang desc, Name asc; PersNr 2136 2137 2126 2125 2134 2127 2133 Wednesday, November 19, 2008 Name Curie Kant Russel Sokrates Augustinus Kopernikus Popper Rang C4 C4 C4 C4 C3 C3 C3 28 Attributauswahl in SELECT select * from studenten select Titel, (SWS * 1.5) as ECTS from Vorlesungen Wednesday, November 19, 2008 29 Updates in SQL • DML erlaubt auch Veränderungen der Basisrelationen Wednesday, November 19, 2008 30 Einfügen insert into hören select MatrNr,VorlNr from Studenten,Vorlesungen where Titel= `Logik´; insert into Studenten (MatrNr, Name) values (28121, `Archimedes´); Nullwert! Wednesday, November 19, 2008 31 Änderung von Tupeln update Studenten set Semester= Semester + 1 update prüfen set note=6 where MatrNr=25403 Wednesday, November 19, 2008 32 Löschen delete from Studenten where Semester > 13; Wednesday, November 19, 2008 33 Nullwerte in SQL 1. Erweiterung der Operatoren der Domäne D auf • • NULL D Prinzip: NULL bedeutet “unbekannt” 1 + NULL = NULL, concat(NULL,’Hallo’)=NULL,... 2. Erweiterung auf dreiwertige Logik • • • true, false, unknown Vergleich mit NULL ergibt unknown where verlangt nach wie vor “true” 3. NULL bildet eigene Gruppe bei group by 4. Test auf NULL mit is null oder is not null Wednesday, November 19, 2008 34 Wahrheitstabellen X not X true false unknown unknown false true and true unknown true true unknown unknown unknown unknown false false false false false false false or true unknown false true true true true unknown true unknown unknown false true unknown Wednesday, November 19, 2008 false 35 Sichten Sicht 1 Sicht 2 Logische Ebene Sicht 3 Anwendungssichten basieren evtl. auf anderen Schemata als logische Ebene Physische Ebene ...wird von SQL durch View-Konstrukt unterstützt Wednesday, November 19, 2008 36 Erzeugung von SQL-Views create view VVZ as (VNr, Titel, Dozent) select VorlNr, Titel, Name from Vorlesungen, Professoren where gelesenVon=PersNr Wednesday, November 19, 2008 37 Anwendungen • Datenunabhängigkeit • Datenschutz (Zugriffskontrolle) • Modularisierung • Performance-Steigerung Wednesday, November 19, 2008 38 Geschachtelte Anfragen (WHERE) select * from prüfen where Note < ( select avg (Note) from prüfen ); Wednesday, November 19, 2008 39 Elementtest select Name from Professoren where PersNr not in ( select gelesenVon from Vorlesungen ); Wednesday, November 19, 2008 40 Existenzquantor exists select p.Name from Professoren p Korrelation where not exists ( select * from Vorlesungen v where v.gelesenVon = p.PersNr ); Wednesday, November 19, 2008 41 Geschachtelte Anfragen (SELECT) select PersNr, Name, (select sum (SWS) as Lehrbelastung from Vorlesungen Korrelation where gelesenVon=PersNr ) from Professoren; Wednesday, November 19, 2008 42 Geschachtelte Unteranfragen (FROM) select tmp.MatrNr, tmp.Name, tmp.VorlAnzahl from (select s.MatrNr, s.Name, count(*) as VorlAnzahl from Studenten s, hören h where s.MatrNr=h.MatrNr group by s.MatrNr, s.Name) tmp where tmp.VorlAnzahl > 2; Wednesday, November 19, 2008 MatrNr Name VorlAnzahl 28106 Carnap 4 29120 Theophrastos 3 43 Weitere Beispiele für Unteranfragen ( select Name from Assistenten ) union ( select Name from Professoren ); Wednesday, November 19, 2008 44
