Datenbanksysteme (2) - Einführung in SQL
Werbung
Termin 12: Datenbanksysteme (2) Einführung in SQL Grundlagen der Informatik Sommersemester 2006 Prof. Bernhard Jung Übersicht 1. 2. 3. 4. Einführung SQL Datenbankanfragen mit SQL (select … from … where) Erzeugen, Ändern und Löschen von Tabelleninhalten mit SQL Erzeugen, Ändern und Löschen von Tabellen mit SQL (DDL) Hauptlernziele • Die Umsetzung der Relationenalgebra in SQL verstehen • Fähigkeit, einfachere Datenbankabfragen u. andere Datenbankoperationen in SQL zu formulieren Literatur Gumm & Sommer. Einführung in die Informatik. Oldenbourg. 2004. Lehrhilfe SQL. Institut für Informatik, TU Bergakademie Freiberg. - erhältlich im Institut für Informatik, Fr. Schüttauf, für 1,00 € Prof. B. Jung Grundlagen der Informatik, SS 2006 1 Datenbanksysteme - Statistiken (1) Markt für Datenbanksysteme (nach www.idc.com) 2003: $13.6 Milliarden 2008: ca $20 Milliarden Linux / Open Source Databanken 2003: $299 Millionen Markt-Anteile (2004; IDC / Gartner Dataquest) Oracle: 41.3% IBM: 30.6% Microsoft: 13.4% Prof. B. Jung / 33.9% / 34.2 % / 20.0% Grundlagen der Informatik, SS 2006 Datenbanksysteme – Statistiken (2) Prof. B. Jung Grundlagen der Informatik, SS 2006 2 Datenbanksysteme – Statistiken (3) from http://www.informationweek.com/story/showArticle.jhtml?articleID=23901139 Prof. B. Jung Grundlagen der Informatik, SS 2006 Wdh - Beispielrelationen 1 Assistent: Persnr Name Fachgebiet Boss 3002 3003 3004 3005 3006 3007 Ideenlehre Syllogistik Sprachtheorie Planetenbewegung Keplersche Gesetze Gott und Natur 2125 2125 2126 2127 2127 2134 Platon Aristoteles Wittgenstein Rhetikus Newton Spinoza Student: Professor: Persnr 2125 2126 2127 2133 2134 2136 2137 Name Sokrates Russel Kopernikus Popper Augustinus Curie Kant Prof. B. Jung Matrnr Name 24002 25403 26120 26830 27550 28106 29120 29555 Xenokrates Jonas Fichte Aristoxenos Schopenhauer Carnap Theophrastos Feuerbach Sem Rang Raum C4 C4 C3 C3 C3 C4 C4 226 232 310 52 309 36 7 18 12 10 8 6 3 2 2 Grundlagen der Informatik, SS 2006 3 Wdh - Beispielrelationen 2 Vorlesung: voraussetzen: Vorlnr Titel SWS 5001 5041 5043 5049 4052 5052 5216 5259 5022 4630 4 4 3 2 4 3 2 2 2 4 Grundzüge Ethik Erkenntnistheorie Gewäsch Logik Wissenschaftstheorie Bioethik Der Wiener Kreis Glaube und Wissen Die 3 Kritiken Leser 2137 2125 2126 2125 2125 2126 2126 2133 2134 2137 hören: Vorgänger Nachfolger Matrnr Vorlnr 5001 5001 5001 5041 5043 5041 5052 5041 5043 5049 5216 5052 5052 5259 26120 27550 27550 28106 28106 28106 28106 29120 29120 29120 29555 25403 29555 5001 5001 4052 5041 5052 5216 5259 5001 5041 5049 5022 5022 5001 prüfen: Prof. B. Jung Matrnr Vorlnr Persnr Note 28106 25403 27550 5001 5041 4630 2126 2125 2137 1 2 2 Grundlagen der Informatik, SS 2006 Was ist SQL? SQL = Structured Query Language SQL ist Standardsprache für relationale Datenbankmanagementsysteme (DBMS) SQL stellt Möglichkeiten bereit zur Definition der Struktur von Datenbanken (Datendefinitionssprache; DDL) Manipulation der Datenbankinhalte (Datenmanipulationssprache; DML) i.Ggs. zu imperativen oder objekt-orientierten Programmiersprachen (wie Python, Java, C++, C#, …) SQL ist eine Implementierung der Relationenalgebra aber kleinere Unterschiede anfragen, einfügen, löschen, ändern von Datensätzen SQL ist eine deklarative Programmiersprache z.B. Ordnung der Tabellenzeilen in SQL z.B. Duplikate von Tabellenzeilen in SQL erlaubt Bekannte auf SQL basierende DBMS: Oracle, IBM DB2, Microsoft SQL Server, Access, Ingres, MySQL, … Prof. B. Jung Grundlagen der Informatik, SS 2006 4 SQL Historie ca. 1975 1981 1986 1987 SEQUEL, Vorläufer von SQL wird für das Projekt System R von IBM entwickelt. SQL gelangt mit SQL/Data Systems erstmals durch IBM auf den Markt. SQL1 wird von ANSI als Standard verabschiedet. SQL1 wird jetzt auch von ISO als Standard verabschiedet und 1989 nochmals überarbeitet. 1992 1999 2003 Der SQL2 bzw. SQL-92 Standard wird von der ISO verabschiedet. SQL3 bzw. SQL:1999 wird verabschiedet. SQL:2003 wird von der ISO als Nachfolger des SQL:1999 Standards verabschiedet. Prof. B. Jung Grundlagen der Informatik, SS 2006 Relationale Datenbanksprache SQL Datenbanksprache (DL): - Datendefinitionssprache (DDL) - Datenmanipulationssprache (DML): - Änderungssprache - Anfragesprache Datenmanipulationssprache / Anfrage Standardanfrageschema: select . . . from . . . where . . . all / any / in / exists group by . . . union . . . order by . . . Prof. B. Jung ... Projektion Relation / Produkt / Join Selektion / Differenz / Durchschnitt Division / Quantifizierung Aggregation Vereinigung Sortierung Grundlagen der Informatik, SS 2006 5 SQL – Einfache Anfragen Einfache Anfragen: select Spalte(n) from Tabelle(n) where Bedingung; Anzeigen von Spalte(n) der Zeilen aus Tabelle(n), die einer Bedingung genügen Umfasst Projektion, Selektion und Join der Relationenalgebra! Anfragen über einer Tabelle: select * from Assistent; select distinct SWS from Vorlesung where Leser = 2125 and not TITEL = 'Ethik'; select Matrnr, Name, Sem from Student where Sem < 5; Prof. B. Jung Grundlagen der Informatik, SS 2006 Spaltenauswahl Projektion in Relationenalgebra Spaltenname, Spaltenname,Konstante Konstante ** distinct, distinct,all all SQL> select Boss from Assistent; BOSS --------2125 2125 2126 2127 2127 2134 Auswahl von Spalten, Konstanten Auswahl aller Spalten Duplikatebeseitigung SQL> select distinct Boss from Assistent; BOSS --------2125 2126 2127 2134 gleiches Ergebnis wie bei: select all Boss from Assistent; Prof. B. Jung Grundlagen der Informatik, SS 2006 6 Spaltenauswahl Spaltenname, Spaltenname,Konstante Konstante ** distinct, distinct,all all Auswahl von Spalten, Konstanten Auswahl aller Spalten Duplikatebeseitigung SQL> select 'Guten Tag, ', Name from Assistent; 'GUTENTAG ----------Guten Tag, Guten Tag, Guten Tag, Guten Tag, Guten Tag, Guten Tag, NAME -----------------------------Platon Aristoteles Wittgenstein Rhetikus Newton Spinoza Prof. B. Jung Grundlagen der Informatik, SS 2006 Zeilenauswahl Selektion in Relationenalgebra SQL: durch Formulierung von Bedingungen in WHERE-Klausel: Spaltenname, Spaltenname,Konstante Konstante =, =,!=, !=,<>, <>,>, >,>=, >=,<, <,<= <= and, or, not and, or, not between between... ...and and like like’...%..._...’ ’...%..._...’ is isnull, null,is isnot notnull null Angabe von Spalten und Konstanten Vergleichsoperator logische Verknüpfung Bereichsangabe Zeichenmuster Leerwert select Titel from Vorlesung where Leser = 2125 and not TITEL = 'Ethik'; Prof. B. Jung Grundlagen der Informatik, SS 2006 7 Zeilenauswahl - Beispiele SQL> select * from Vorlesung where Leser = 2125; VORLNR --------5041 5049 4052 TITEL SWS LESER ------------------------------- --------- --------Ethik 4 2125 Gewäsch 2 2125 Logik 4 2125 SQL> select Titel from Vorlesung where Leser = 2125; TITEL --------------------------Ethik Gewäsch Logik Prof. B. Jung SQL> select Titel from Vorlesung where Leser = 2125 and not Titel = 'Ethik'; TITEL ------------------------------Gewäsch Logik Grundlagen der Informatik, SS 2006 SQL – Anfragen über mehrere Tabellen Join in Relationenalgebra in Beispielen jeweils Equi-Join (d.h. Gleichheit von Attributwerten): select Name, Titel from Professor, Vorlesung where Persnr = Leser and Titel = 'Logik'; Join select Name, Titel from Student, hören, Vorlesung where Student.Matrnr = hören.Matrnr and hören.Vorlnr = Vorlesung.Vorlnr; select s.Name, v.Titel from Student s, hören h, Vorlesung v where s.Matrnr = h.Matrnr and h.Vorlnr = v.Vorlnr; Prof. B. Jung Welche Professoren lesen 'Logik'? Welche Studenten hören welche Vorlesung? Welche Studenten hören welche Vorlesung? (alternative Syntax) Grundlagen der Informatik, SS 2006 8 Ergebnisse der Anfragen der vorangehenden Folie NAME TITEL ------------------------------ -----------------------Sokrates Logik NAME -----------------------------Jonas Fichte Schopenhauer Schopenhauer Carnap Carnap Carnap Theophrastos Theophrastos Theophrastos Feuerbach Feuerbach Prof. B. Jung TITEL ------------------------------Glaube und Wissen Grundzüge Logik Grundzüge Ethik Wissenschaftstheorie Der Wiener Kreis Grundzüge Ethik Gewäsch Grundzüge Glaube und Wissen Grundlagen der Informatik, SS 2006 Geschachtelte Anfragen select * from Assistent where Boss = (select Persnr from Professor where Name = 'Sokrates'); Wer sind die Assistenten von Sokrates? Ergebnistabelle PERSNR --------3002 3003 Prof. B. Jung NAME -------------------Platon Aristoteles FACHGEBIET BOSS ------------------------------ --------Ideenlehre 2125 Syllogistik 2125 Grundlagen der Informatik, SS 2006 9 Geschachtelte Anfragen Welche Prüfungen haben mit einer überdurchschnittlichen Note geendet? select * from prüfen where Note < (select avg (Note) from prüfen); Ergebnistabelle MATRNR VORLNR PERSNR NOTE --------- --------- --------- --------28106 5001 2126 1 Prof. B. Jung Grundlagen der Informatik, SS 2006 Mengenoperationen Voraussetzung: Gleichförmigkeit der verknüpften Tabellen gleiche Anzahl und gleicher Typ der Attribute union union (intersect, (intersect,except) except) Vereinigung von Tabellen (Durchschnitt, Differenz) (select Name from Assistent) union (select Name from Professor); Prof. B. Jung Namen von Assistenten und Professoren Grundlagen der Informatik, SS 2006 10 Mengenoperationen Welche Vorlesungen werden von Studenten gehört? (select Vorlnr from Vorlesung) intersect (select Vorlnr from hören); Ergebnistabelle: VORLNR --------4052 5001 5022 5041 5049 5052 5259 Prof. B. Jung Grundlagen der Informatik, SS 2006 Mengenoperationen Welche Vorlesungen werden von keinem Studenten gehört? oder: Für welche Vorlesungen sind keine Studierenden registriert? Nach SQL Standard (klappt nicht in Oracle 8): (select Vorlnr from Vorlesung) except (select Vorlnr from hören); In Oracle 8: Ergebnistabelle: select Vorlnr from Vorlesung where Vorlnr not in (select Vorlnr from hören); Prof. B. Jung VORLNR --------5043 5216 4630 Grundlagen der Informatik, SS 2006 11 Quantifizierung all, all,any, any,some some in, in,not notin in exists, exists,not notexists exists für alle, für ein Element von, nicht Element von es existiert ein, es existiert kein Welche Studenten studieren am längsten? select Name from Student where Sem >= all (select Sem from Student); (Æ Xenokrates mit 18 Semestern) Prof. B. Jung Grundlagen der Informatik, SS 2006 Quantifizierung Welche Professoren halten (dieses Semester) keine Vorlesung? Sämtliche Informationen über Vorlesungen 5001 bzw 5041 Prof. B. Jung select Name from Professor where not exists (select * from Vorlesung where Leser = Persnr); select * from Vorlesung where Vorlnr in (5001, 5041); Grundlagen der Informatik, SS 2006 12 Gruppierungsfunktion count, count,sum, sum,avg, avg,max, max,min min Ausführen von Operationen auf Tupelmengen Durchschnittliche Semesterzahl der Studenten select avg (Sem) from Student; Beste bzw. schlechteste Note in Kursen 5001 oder 5041 select min (Note), max (Note) from prüfen where Vorlnr = 5001 or Vorlnr = 5041; Anzahl der Tupel der Tabelle hören; d.h. Anzahl der registrierten Hörer für alle Vorlesungen select count (*) from hören; Prof. B. Jung Grundlagen der Informatik, SS 2006 Gruppierung group groupby bySpalte(n) Spalte(n) select Leser, sum (Sws) from Vorlesung group by Leser; Summe SWS pro Leser Ergebnistabelle: Prof. B. Jung Gruppierung der Zeilen der Ergebnistabelle LESER SUM(SWS) --------- --------2125 10 2126 8 2133 2 2137 10 Grundlagen der Informatik, SS 2006 13 Gruppierung group groupby bySpalte(n) Spalte(n) having havingBedingung Bedingung Gruppierung der Zeilen der Ergebnistabelle und Ausführung von Gruppierungsfunktionen Summe SWS pro Leser -aber nur für Leser, deren Veranstaltungen insgesamt einen Umfang von mindestens 6 SWS haben select Leser, sum (Sws) from Vorlesung group by Leser having sum (Sws) >= 6; Ergebnistabelle: LESER SUM(SWS) --------- --------2125 10 2126 8 2137 10 Prof. B. Jung Grundlagen der Informatik, SS 2006 Gruppierung Pesrnr, Name, Summe SWS der C4-Professoren, die im Umfang von mindestens 8 SWS Vorlesungen geben select Leser, Name, sum (Sws) from Vorlesung, Professor where Leser = Persnr and Rang = 'C4' group by Leser, Name having sum(Sws) >= 8; Ergebnistabelle: LESER --------2125 2126 2137 Prof. B. Jung NAME SUM(SWS) ------------------------------ --------Sokrates 10 Russel 8 Kant 10 Grundlagen der Informatik, SS 2006 14 Sortierung order orderby by Spalte(n) Spalte(n) asc / desc asc / desc Ausgabe der Studierendendatensätze, geordnet nach Matrikelnr Festlegen der Sortierreihenfolge der Ergebnistabelle (aufsteigend bzw. absteigend) select Name, Matrnr from Student order by Matrnr; Auflistung der Professoren, geordnet nach Rang (lexikalisch absteigend – d.h. C4 vor C3), dann alphabetisch select Persnr, Name, Rang from Professor order by Rang desc, Name asc; Prof. B. Jung Grundlagen der Informatik, SS 2006 Sortierung – Ergebnisse der Anfragen auf voriger Folie select Name, Matrnr from Student order by Matrnr; select Persnr, Name, Rang from Professor order by Rang desc, Name asc; NAME -----------------Xenokrates Jonas Fichte Aristoxenos Schopenhauer Carnap Theophrastos Feuerbach PERSNR --------2136 2137 2126 2125 2134 2127 2133 Prof. B. Jung MATRNR --------24002 25403 26120 26830 27550 28106 29120 29555 NAME ------------------Curie Kant Russel Sokrates Augustinus Kopernikus Popper RA -C4 C4 C4 C4 C3 C3 C3 Grundlagen der Informatik, SS 2006 15 Elementfunktion +, +,-,-,*,*,/,/,... ... char_length, char_length,substring, substring,||,||,...... current_time, current_date, current_time, current_date,+, +,-,-,*,*,... ... arithmetische Funktion Zeichenkettenfunktion Datumsfunktion select Matrnr, Note - 1 from prüfen where Vorlnr = 4630; Um 1.0 verbesserte Noten für Kurs 4630 select Name, Rang || '-Professur' from Professor; Prof. B. Jung NAME RANG||'-PROF ------------------------------ -----------Sokrates C4-Professur Russel C4-Professur Kopernikus C3-Professur Popper C3-Professur Augustinus C3-Professur Curie C4-Professur Kant C4-Professur Grundlagen der Informatik, SS 2006 Elementfunktion Ausgabe von Studentendaten mit Zeitpunkt des Zugriffs auf Datensätze select Matrnr, Sem, current_time from Student; MATRNR SEM CURRENT_T --------- --------- --------24002 18 00:34:50 25403 12 00:34:50 26120 10 00:34:50 26830 8 00:34:50 27550 6 00:34:50 28106 3 00:34:50 29120 2 00:34:50 29555 2 00:34:50 Prof. B. Jung Grundlagen der Informatik, SS 2006 16 Datenmanipulationssprache / Änderung Standard-Änderungsoperationen: insert . . . update . . . delete . . . Prof. B. Jung Einfügen Ändern Löschen Grundlagen der Informatik, SS 2006 Einfügen von Datensätzen insert insertinto intoTabelle Tabelle values valuesTupel; Tupel; Einfügen von Zeilen in eine existierende Tabelle insert insertinto intoTabelle Tabelle Anfrage; Anfrage; Beispiele: Einfügen neuer Datensätze insert into Professor values (2136, 'Einstein', 'C4', null); insert into Student (Matrnr, Name) values (25000, 'Goethe'); Prof. B. Jung Werte für alle Spalten Werte für benannte Spalten (andere Spalten: Default-Werte) Grundlagen der Informatik, SS 2006 17 Einfügen von Datensätzen Alle Studenten mit mehr als 15 Semestern Studienzeit zu Assistenten befördern insert into Assistent (Persnr, Name) select Matrnr, Name from Student where Sem > 15; Prof. B. Jung Grundlagen der Informatik, SS 2006 Ändern von Datensätzen update updateTabelle Tabelle set setWerteänderung Werteänderung where whereBedingung Bedingung Ändern von Zeilen in einer existierenden Tabelle Alle Vorlesungen auf 2 SWS setzen Semesteranzahl von allen Studenten um 1 erhöhen Prof. Russel zieht in Raum 213 ein Daten zur Vorlesung 5001 aktualisieren update Vorlesung set Sws = 2; update Student set Sem = Sem + 1; update Professor set Raum = 213 where name = 'Russel'; update Vorlesung set Titel = 'Grundlagen', Sws = 3, Leser = 2125 where Vorlnr = 5001; Prof. B. Jung Grundlagen der Informatik, SS 2006 18 Löschen von Datensätzen delete deletefrom fromTabelle Tabelle where whereBedingung Bedingung Löschen von Zeilen in einer existierenden Tabelle Alle Prüfungsdatensätze löschen Datensatz zur Vorlesung 5001 löschen Datensätze der Studenten über 15. Semester löschen delete from Student where Sem >= 15; Vorlesungen, die andere Vorlesungen voraussetzen, sollen ihrerseits keine Voraussetzung für weitere Vorlesungen sein delete from voraussetzen where Vorgänger in (select Nachfolger from voraussetzen); delete from prüfen delete from Vorlesung where Vorlnr = 5001; Prof. B. Jung Grundlagen der Informatik, SS 2006 Datendefinitionssprache (DDL) Standarddefinitionsoperationen: Erzeugen (create), Ändern (alter), Löschen (drop) für: Tabelle (table), Sicht (view), ... und Erteilung (grant) und Entzug (revoke) von Rechten Datentypen: char(n), varchar(n), number(p), number(p,s), date, long, blob, . . . Namen: 1. Zeichen Buchstabe, dann Buchstaben, Ziffern, _ oder $ Prof. B. Jung Grundlagen der Informatik, SS 2006 19 DDL: Erzeugen von Tabellen create createtable tableTabellen-Name Tabellen-Name (Spalten-Name (Spalten-NameTyp, Typ, Spalten-Name Spalten-NameTyp Typnot notnull, null, Spalten-Name Typ not Spalten-Name Typ notnull nullunique, unique, . .. ..).) Definition einer Tabelle mit Spalten und Integritätsbedingungen Beispiele: create table Professor (persnr number (5) not null unique, Name varchar (20) not null, Rang char (2), Raum number (4)); Prof. B. Jung create table voraussetzen (Vorgänger number (5), Nachfolger number (5)); Grundlagen der Informatik, SS 2006 Systemüberblick Server OSToolbox Datenbank Datenbank "Maschine" "Maschine" SQLDDL Datenbank Datenbank entwurf entwurf Beispiele: Microsoft Access Oracle Ingres MySQL Prof. B. Jung SQLDML AnwendungsAnwendungsProgramm ProgrammBB OS-Toolbox evtl. Netzwerk SQL-DML AnwendungsAnwendungsProgramm ProgrammAA OS-Toolbox evtl. Netzwerk Verwaltung Verwaltung Klient Buchungs Buchungs -system -system Klient Grundlagen der Informatik, SS 2006 20
