Datenbanksysteme (2) - Einführung in SQL
Werbung
Grundlagen der Informatik Wintersemester 2005/06 Prof. Bernhard Jung 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, WS 2005/06 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 (2003; www.idc.com) Oracle: 39.8% IBM: 31.3% Microsoft: 12.1% Prof. B. Jung Grundlagen der Informatik, WS 2005/06 ! Prof. B. Jung Grundlagen der Informatik, WS 2005/06 2 ! from http://www.informationweek.com/story/showArticle.jhtml?articleID=23901139 Prof. B. Jung Grundlagen der Informatik, WS 2005/06 "# $ % && ' Assistent: Persnr Name Fachgebiet Boss 3002 3003 3004 3005 3006 3007 Platon Aristoteles Wittgenstein Rhetikus Newton Spinoza Ideenlehre Syllogistik Sprachtheorie Planetenbewegung Keplersche Gesetze Gott und Natur 2125 2125 2126 2127 2127 2134 Student: Professor: Persnr 2125 2126 2127 2133 2134 2136 2137 Name Sokrates Russel Kopernikus Popper Augustinus Curie Kant Prof. B. Jung Rang Raum C4 C4 C3 C3 C3 C4 C4 226 232 310 52 309 36 7 Matrnr Name 24002 25403 26120 26830 27550 28106 29120 29555 Xenokrates Jonas Fichte Aristoxenos Schopenhauer Carnap Theophrastos Feuerbach Sem 18 12 10 8 6 3 2 2 3 "# $ % && ' 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 ( 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) anfragen, einfügen, löschen, ändern von Datensätzen SQL ist eine deklarative Programmiersprache i.Ggs. zu imperativen oder objekt-orientierten Programmiersprachen (wie Python, Java, C++, C#, …) SQL ist eine Implementierung der Relationenalgebra aber kleinere Unterschiede 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, WS 2005/06 4 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 . . . Projektion Relation / Produkt / Join Selektion / Differenz / Durchschnitt Division / Quantifizierung Aggregation Vereinigung Sortierung Prof. B. Jung ! ) 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 Matrnr, Name, Sem from Student where Sem < 5; select distinct SWS from Vorlesung where Leser = 2125 and not TITEL = 'Ethik'; Prof. B. Jung 5 %& * & 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 %& * & 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 6 +& * & Selektion in Relationenalgebra SQL: durch Formulierung von Bedingungen in WHERE-Klausel: Spaltenname, Spaltenname,Konstante Konstante =, =,!=, !=,<>, <>,>, >,>=, >=,<, <,<= <= and, and,or, or,not 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 +& * &$ % & 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, WS 2005/06 7 !) & & 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 Welche Professoren lesen 'Logik'? 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; Welche Studenten hören welche Vorlesung? Welche Studenten hören welche Vorlesung? (alternative Syntax) Prof. B. Jung # ) # ,' # -'& 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, WS 2005/06 8 . &) select * from Assistent where Boss = (select Persnr from Professor where Name = 'Sokrates'); Wer sind die Assistenten von Sokrates? Ergebnistabelle PERSNR --------3002 3003 NAME -------------------Platon Aristoteles FACHGEBIET BOSS ------------------------------ --------Ideenlehre 2125 Syllogistik 2125 Prof. B. Jung . &) select * from prüfen where Note < (select avg (Note) from prüfen); Welche Prüfungen haben mit einer überdurchschnittlichen Note geendet? Ergebnistabelle MATRNR VORLNR PERSNR NOTE --------- --------- --------- --------28106 5001 2126 1 Prof. B. Jung 9 / '% ' 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); Namen von Assistenten und Professoren Prof. B. Jung / '% ' 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 10 / '% ' 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); VORLNR --------5043 5216 4630 Prof. B. Jung 0 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 11 0 Welche Professoren halten (dieses Semester) keine Vorlesung? Sämtliche Informationen über Vorlesungen 5001 bzw 5041 select Name from Professor where not exists (select * from Vorlesung where Leser = Persnr); select * from Vorlesung where Vorlnr in (5001, 5041); Prof. B. Jung . %% count, count,sum, sum,avg, avg,max, max,min min Durchschnittliche Semesterzahl der Studenten ' Ausführen von Operationen auf Tupelmengen 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 12 . %% group groupby bySpalte(n) Spalte(n) Gruppierung der Zeilen der Ergebnistabelle select Leser, sum (Sws) from Vorlesung group by Leser; Summe SWS pro Leser Ergebnistabelle: LESER SUM(SWS) --------- --------2125 10 2126 8 2133 2 2137 10 Prof. B. Jung . %% 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 13 . %% 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 NAME SUM(SWS) ------------------------------ --------Sokrates 10 Russel 8 Kant 10 Prof. B. Jung ' order orderby by Spalte(n) Spalte(n) asc / desc asc / desc Festlegen der Sortierreihenfolge der Ergebnistabelle (aufsteigend bzw. absteigend) Ausgabe der Studierendendatensätze, geordnet nach Matrikelnr 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 14 ' ,' ! -'& # ) select Name, Matrnr from Student order by Matrnr; NAME -----------------Xenokrates Jonas Fichte Aristoxenos Schopenhauer Carnap Theophrastos Feuerbach select Persnr, Name, Rang from Professor order by Rang desc, Name asc; MATRNR --------24002 25403 26120 26830 27550 28106 29120 29555 Prof. B. Jung & PERSNR --------2136 2137 2126 2125 2134 2127 2133 NAME ------------------Curie Kant Russel Sokrates Augustinus Kopernikus Popper RA -C4 C4 C4 C4 C3 C3 C3 Grundlagen der Informatik, WS 2005/06 ' +, +,-,-,*,*,/,/,... ... char_length, char_length,substring, substring,||,||,... ... current_time, current_time,current_date, current_date,+, +,-,-,*,*,... ... Um 1.0 verbesserte Noten für Kurs 4630 Um 1.0 verbesserte Noten für Kurs 4630 Prof. B. Jung arithmetische Funktion Zeichenkettenfunktion Datumsfunktion select Matrnr, Note - 1 from prüfen where Vorlnr = 4630; select Name, Rang || '-Professur' from Professor; NAME -----------------------------Sokrates Russel Kopernikus Popper Augustinus Curie Kant RANG||'-PROF -----------C4-Professur C4-Professur C3-Professur C3-Professur C3-Professur C4-Professur C4-Professur 15 & ' 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 %& ' % 12 # Standard-Änderungsoperationen: insert . . . update . . . delete . . . Einfügen Ändern Löschen Prof. B. Jung 16 ,' insert insertinto intoTabelle Tabelle values valuesTupel; Tupel; 30 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, 'Curie', 'C4, null); Werte für alle Spalten insert into Student (Matrnr, Name) values (25000, 'Goethe'); Werte für benannte Spalten (andere Spalten: Default-Werte) Prof. B. Jung ,' 30 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 17 2 # ,' 30 update updateTabelle Tabelle set setWerteänderung Werteänderung where whereBedingung Bedingung Ändern von Zeilen in einer existierenden Tabelle Alle Vorlesungen auf 2 SWS setzen update Vorlesung set Sws = 2; Semesteranzahl von allen Studenten um 1 erhöhen update Student set Sem = Sem + 1; Prof. Russel zieht in Raum 213 ein update Professor set Raum = 213 where name = 'Russel'; Daten zur Vorlesung 5001 aktualisieren update Vorlesung set Titel = 'Grundlagen', Sws = 3, Leser = 2125 where Vorlnr = 5001; Prof. B. Jung 4 ,' delete deletefrom fromTabelle Tabelle where Bedingung where Bedingung 30 Löschen von Zeilen in einer existierenden Tabelle Alle Prüfungsdatensätze löschen delete from prüfen Datensatz zur Vorlesung 5001 löschen delete from Vorlesung where Vorlnr = 5001; 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); Prof. B. Jung 18 # ' % 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 0 ,' 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)); create table voraussetzen (Vorgänger number (5), Nachfolger number (5)); Prof. B. Jung 19 & Server OSToolbox Datenbank Datenbank "Maschine" "Maschine" SQLDDL Datenbank Datenbank entwurf entwurf Beispiele: Microsoft Access Oracle Ingres MySQL SQLDML AnwendungsAnwendungsProgramm ProgrammBB OS-Toolbox evtl. Netzwerk SQL-DML AnwendungsAnwendungsProgramm ProgrammAA OS-Toolbox evtl. Netzwerk Verwaltung Verwaltung Klient Buchungs Buchungs -system -system Klient Prof. B. Jung 20
