MySQL und ERM ein Beispiel
Werbung
MySQL und ERM ein Beispiel © Hans-Georg Beckmann 2002 Eine relationale Datenbank mit MYSQL MySQL arbeitet auf der Grundlage von SQL. Das System hat den besonderen Vorteil, dass es kostenlos genutzt werden kann. Zwar hat dieses Datenbanksystem nicht den gleichen Funktionsumfang, wie kommerzielle System ( z.B. ORACLE ) es kann aber in Hinsicht Geschwindigkeit und Datenvolumen durchaus mit den kommerziellen Systemen mithalten. Das Grundprinzip ist die Client - Server - Architektur, was bedeutet, dass der zentrale Server die Datenbanken verwaltet und die Benutzer über eine Clientprogramm ( von denen viele verschiedene existieren ) auf diese Datenbanken zugreifen. Dabei kann der Datenserver irgendwo auf dieser Welt stehen und die Benutzer greifen z.B. per Internet auf die Datenbanken zu. Datenbank-Client Eingabe einer Abfrage Darstellung der Ergebnisse Abfrage QUERY Ergebnis Datenbank-Server Datenbank Managment System DBMS Datenbanken DB Für die nichtkommerzielle Benutzung ist MySQL frei. Die genauen Benutzerbedingungen und auch das Programm selbst erhält man z.B. bei www.mysql.com Als gute Arbeitsgrundlage kann man "Das Einsteigerseminar MySQL" von Rolf Däßler, bhv Verlag 2001, ISBN 3-8266-7021-3 verwenden. In diesem Script soll es darum gehen, eine Datenbank "SCHULE" zu erzeugen, die auf dem erarbeiteten ER-Modell beruht. Es ist also nicht beabsichtigt, eine umfassende und gründliche Einführung in SQL zu geben. Nur die Aktionen und Befehle, die zu unserer Datenbank führen werden genauer dargestellt. Alles andere zu MySQL sollte man sich immer dann antun, wenn es nötig ist. Seite 21 MySQL installieren und starten © Hans-Georg Beckmann 2002 1.Schritt: Wie bekomme ich MySQL auf meinen Rechner ? Vielleicht war der Systemadministrator schon nett und das Programmpaket ist schon lange da ! Wenn das ber nicht der Fall ist oder, wenn man MySQL zu Hause installieren möchte, dann kommt nun ein wenig Arbeit. Man lädt sich das Programmpaket aus dem Interenet auf den heimischen Rechner. Unter www.mysql.com wird man in der Downloadsektion an einen HTTP- Server oder einen FTP-Server durchgereicht ( z.B bei der GWDG in Göttingen ). Dort lädt man die Datei als WINZIP herunter. Das ist durchaus wichtig, weil die Shareware WINZIP die Datei nicht nur entpackt, sondern auch gleich korrekt installiert, was einige andere Entzipper nicht machen. Die aktuelle stabile Version vonMySQL ist 3.23.49. Nach dem Entpacken und Installieren sollte sich auf dem Laufwerk C: ( ja , ja so heißt das bei Windows noch immer ..) ein Verzeichnis mysql befinden. In diesem Verzeichnis sind viele Unterverzeichnisse abgelegt: Das Verzeichnis mysql Seite 22 MySQL starten © Hans-Georg Beckmann 2002 Wichtig sind die folgenden Unterverzeichnisse: bin enthält alle ausführbaren Programme, die man für MySQL braucht. data enthält alle Datenbanken. Es liegen schon zwei Datenbanken vor: mysql und test. docs Dokumentation Alle anderen Verzeichnisse sind eher was für erfahrene Benutzer, die wir ja erst noch werden sollen. 2. Schritt : MySQL zum ersten Male starten Dazu muss man zuerst einen Blick in C:\mysql\bin werfen. Dort findet man eine Reihe wichtiger Programmteile von MySQL: 5 1 2 3 6 7 4 Das Verzeichnis mysql\bin Seite 23 MySQL starten © Hans-Georg Beckmann 2002 1 mysql.exe Datenbankclient und Kommandozeileneditor zur Kommunikation mit der Datenbank. Das hört sich kompliziert an, bedeutet aber nur, dass in diesem Programm ein Teil des DBSM und ein Clientprogramm zusammengefasst sind. 2 mysqladmin.exe Datenbankadministrationsprogramm 3 mysqld.exe Datenbankserver für Windows 95/98 4 mysqld-opt.exe Datenbankserver für Windows 95/98 der für Pentiumprozessoren optimiert wurde ( whow !) 5 mysqlimport.exe Programmm zum Import aus Textdateien ( u.a.) in bestehende Datenbanken 6 7 winmysqladmin.exe Der Versuch eines bentuzerfreundlichen Datenbankasdministrations programms mit grafischer Oberfläche + Hilfesystem. Im Verzeichnis mysql\data findet man für jede Datenbank einen eigenen Ordner. Dessen Name ist auch der Name der ganzen Datenbank. Das ist wichtig, weil es ja passieren kann, dass jemand aus der Windowsebene heraus einen Ordner mal umbenennt. Dann darf man sich nicht wundern, wenn MySQL seine Datenbanken nicht mehr wiederfindet. Das alles kann natürlich nur geschehen, wenn man als Chef des privaten Rechners Zugriff auf solche Möglichkeiten hat. Im Netzwerk wird das wohl kaum passieren. Angenommen, es existiert eine Datenbank SCHULE, die wiederum eine Tabelle ( Relation) enthält, die schueler heißt, dann gibt es: schueler.FRM eine Datei, mit der Tabellenstruktur schueler.MYD eine Datei, die die Datensätze enthält schueler.MYI eine Datei für den Index Nun zum ersten Start . Seite 24 MySQL starten © Hans-Georg Beckmann 2002 Der "klassische" Weg geht über die MS-DOS Kommandozeile. Wählen sie dazu START >> Programme >> MS-DOS Eingabeaufforderung. Es erscheint das DOS-Fenster mit der Meldung: C:\WINDOWS> und wartet auf unsere Eingaben. Nun muss zuerst der Datenbankserver gestartet werden, der dann im Hintergrung weiterläuft. Geben sie ein: C:\mysql\bin\mysqld Merken sie sich jetzt schon einmal, dass man am Ende der Arbeit diesen Server auch wieder korrekt herunterfahren muss: Seite 25 winmysqladmin © Hans-Georg Beckmann 2002 C:\mysql\bin\mysqladmin -u root shutdown Man kann sich das Leben natürlich auch ein wenig einfacher machen, wenn man vorher das passende Verzeichnis wählt. Also: C:\CD:\MYSQL\BIN Danach muss man immer nur noch den letzen Teil der Befehlszeile eingeben. Starten mit : mysqld Beenden mit mysqladmin -u root shutdown Mit dem Befehl mysqlshow wird das Systemaufgefodert, die aktuell vorhandenen Datenbanken zu zeigen. Man erhält das nebenstehene Bild mit den beiden vorhandenen Datenbanken. (Zeigen sie ruhig etwas Begeisterung über die schöne Bildschirmausgabe.) --------------------------------------------------------------------------------------------------------------------------Hier seien einige ketzerischen Bemerkungen erlaubt. Dass MySQL in dieser Form zu uns kommt ist recht traurig. Diese Art der Benutzeroberfläche ist nun seit mehr als 10 Jahren eine Zumutung für alle User, die nicht zu den Experten der Computerbenutzung gehören. Zwar macht es den wahren Könner aus, dass er oder sie sich mit Kommandozeilen in DOS oder UNIX bestens auskennen und allerlei kryptischen Befehle beherrschen ( von denen einige gleich noch kommen ), aber man kann sich des Eindrucks nicht erwehren, dass hier mit Absicht die "dummen" User abgeschreckt werden sollen, damit man sich selbst noch viel mehr als Experten und coolen Hacker verstehen kann. Immer wieder kann man von "Könnern" hören, dass doch diese Art der Bedienung "echt cool" sei, sie ist es aber nicht, und sie erfüllt keinen sichtbaren Zweck. Auch in der OpenSource-Programmierergilde gibt es genug Kompetenz, um gute Bedienugsoberflächen zu programmieren, ohne dass die Leistungsfähigkeit von MySQL leiden würde. Der Versuch von winmysqladmin.exe zeigt das schon ein wenig. Vielleicht sollten sich die Programmierer auch mal auf anderen Rechnersystemen umschauen, um Benutzeroberflächen zu sehen, die ein angenehmes Arbeiten ermöglichen. Seite 26 Datenbank anlegen © Hans-Georg Beckmann 2002 Eine andere Methode, den Datenbankserver zu starten und auch zu beenden besteht durch das schon erwähnte winmysqladmin.exe, das ebenfalls im bin-Verzeichnis liegt und keine MS-DOS Kommandozeile braucht. Starten sie es durch einen Doppelklick und für einen kurzen Moment erscheint ein Fenster, das sich aber gleich wieder automatisch schließt. Es taucht dafür ein kleines Symbol unten rechts auf, eine Ampel, die hoffentlich grün leuchtet. Das zeigt an, dass der Datenbankserver korrekt läuft. Bei entsprechender Installation kann man dieses Programm auch beim Starten von Windows automatisch mitstarten lassen. Klicken sie auf die kleine Ampel und wählen sie aus dem Popupmenü "Show me". Nun erscheint das komplette Fenster in dessen oberer Zeile eine ganze Reihe von Reitern zu sehen ist. Klicken sie auf DATABASES und sehen sie, welche Datenbanken jetzt aktiv sind. Mit einem Klick auf die rechte Maustaste erhält man kontextabhängige Klappmenüs und kann mit "Hide Me" das Fenster auch wieder verstecken. Das WinMySQLAdmin Fenster Seite 27 Datenbank anlegen © Hans-Georg Beckmann 2002 Dieses Programm erlaubt später auch einen Blick auf die innere Struktur der Datenbanken und wir werden es dann auch benutzen. Schließt man das Programm, dann wird auch der Datenbankserver heruntergefahren. 3.Schritt: Erstellen einer neuen Datenbank mit mysql Rufen sie wieder die MS-DOS -Eingabeanforderung auf. Es soll jetzt eine Datenbank mit dem Namen SCHULE eingerichtet werden. C:CD:\mysql\bin und C:\mysql\bin> mysqladmin -u root -p create schule Mit dem Befehl CREATE wird mysqladmin augefordert, die Datenbanbk SCHULE zu erzeugen. Das kann man auch mit winmysqladmin machen, wenn man mit einem Klick auf die rechte Maustaste ein Kontextmenü erscheinen läßt, das als obersten Punkt "Create Database" enthält. Es erscheint dann ein Dialogfenster, in dem man den Namen der Datenbank angeben kann. Welche Methode man auch wählt, MySQL legt eine neue Datenbank an, die noch nichts enthält. Seite 28 Tabellen anlegen © Hans-Georg Beckmann 2002 Wenn man danach im Eingabefenster mysql startet und den Befehl C:\mysql\bin>mysql und dann mysql > use schule; eingibt, bekommt man hoffentlich von MySQL einige Begrüßungszeilen zu sehen und mitgeteilt, dass nun die Datenbank SCHULE in Benutzung ist. In der Datenbank SCHULE sollen jetzt die Tabellen engelegt werden. Das sind ( wie im vorangegangenen Kapitel schon besprochen ) die Tabellen SCHUELER, KURSE und HATKURS. ( Hier nur in GROSSBUCHSTABEN , damit es dem Leser / der Leserin auch auffallen möge, dass dieses Tabellen zu erstellen sind). Für das Anlegen von Tabellen hat MySQL auch einige Anweisungen vorgesehen, die jetzt probiert werden. Durch use schule ist dafür gesorgt, dass die Tabellen der Datenbank SCHULE zugeordnet werden. Der Befehl CREATE TABLE erwartet, dass auch gleich die Attribute mitangegeben werden. Im Prinzip ist folgende Syntax zu beachten: CREATE TABLE name_der_Tabelle ( Attributname1 TYP , Attributname2 TPY ,......); Im obigen Beispiel sind also die Attribute KHJ, KURSTHEMA, KURLEHRERKURZ, KURSART und KURSNUMMER definiert worden, wobei nur das Kurshalbjahr ein Integer ist und alle anderen Attribute Strings aus Buchstaben sind. Wenn alles geklappt hat, erhält man eine Meldung der Art "Query OK" Seite 29 Tabellen anlegen © Hans-Georg Beckmann 2002 Machen sie keinen Tippfehler, sonst darf man alles noch einmal eingeben. Die Pfeile vor den Zeilen macht übrigens das System von selbst, wenn man ein Return eingibt. Es ist ein Zeichen dafür, dass MySQL eine noch nicht fertige Eingabe vorfindet. Erst, wenn ein Semikolon am Ende erscheint, gilt der Befehl als eingegeben. Genauso erzeugt man die Tabelle SCHUELER. (20) Fehlt also noch die Tabelle HATKURS, die genauso erzeugt wird. (20) Seite 30 Tabellenstruktur sehen © Hans-Georg Beckmann 2002 Der Befehl show tables; zeigt dann, welche Tabellen jetzt in der Datenbak enthalten sind: Damit sind die drei Relationen in der Datenbank festgelegt. Natürlich kann man Tabellen auch wieder löschen, das interessiert aber im Moment noch nicht. Die Struktur der Tabellen kann man mit dem EXPLAIN -Befehl anzeigen lassen. Probieren sie folgende Eingabe: explain hatkurs; Nun wird die Tabellenstruktur angezeigt: (20) Genauso geht das mit der Tabelle kurse: Seite 31 Daten importieren © Hans-Georg Beckmann 2002 Auf das Explain für die letzte Tabelle verzichten wird hier. Ein Problem ist aber nun offensichtlich die Dateneingabe. Es ist klar, dasss in MySQL nun auch in die Tabellen Daten direkt eingegeben werden können. Bei dieser Benutzeroberfläche ist das aber kein Vergnügen. Wenn man weiter bedenkt, dass dabei doch nur einige wenige "Spieldaten" in die Tabellen kommen, dann wird einzusehen sein, dass hier ein Datenimport her muss. Zu den Daten muss man aber einige Vorbemerkungen machen. Sie, die sie dieses Script lesen, sollen die Dateien übernehmen können. Dazu gäbe es mehrere Möglichkeiten. 1) Man könnte sie als fertige MySQL-Tabellen zur Verfügung stellen. Das soll aber nicht sein, damit sie den Datenimport selbst probieren können. 2) Man kann die Textdateien zur Verfügung stellen. Genau das ist die Absicht. Die Textdateien sind im Internet auf dem , Server zu finden, auf dem auch dieses Script liegt. Wenn das nicht der Fall sein sollte, dann bitte eine mail an: [email protected] und die Dateien können zugeschickt werden. Die Daten müssen als Textdateien vorliegen, in denen die einzelnen Datensätze duch ein RETURN voneinander getrennt sind und die einzelnen Felder ( Attribute) innerhalb eines Datensatzes durch Tabulatoren. Alle linearen Datenbankprogramme, aber auch TabellekalkulationsProgramme eröffnen die Möglichkeiten, Daten in der angegebenen Form als "Text mit Tab" zu exportieren. Die Daten, die wir gleich importieren wollen, liegen also in drei Textdateien vor und werden in das Verzeichnis kopiert, in dem sich auch die Dateien der Datenbank SCHULE befinden. Die Textdateien heissen: schueler.tab , kurse.tab und hatkurs.tab Die Daten haben etwas besonders an sich : sie sind echt ! Das ist eine Seltenheit und sollte sie nun auch entsprechend beeindrucken. Die Daten stammen aus einer echten Abiturdatei, die in einer einzigen Tabelle zusammengefasst ist. Diese Tabelle enthält alle relvanten Schülerdaten vom 11. Jahrgang bis zur Abiturprüfung. Mit dieser Datei Seite 32 Daten importieren © Hans-Georg Beckmann 2002 werden Zeugnisse und Schulbescheinigungen, Statistikerhebungen und Abiturzulassungen, persönliche Daten und Abiturzeugnisse erstellt und verwaltet. Zusammen hat ein Datensatz mehr als 900 (!) Felder. Attribute der Schülerdatei aus Filemaker Diese Monsterdatei wird mit dem Programm Filemaker verwaltet. Filemaker erlaubt es, bei der Definition von Feldinhalten mit Formeln und Auswertungen zu arbeiten, auf andere Dateien zuzugreifen und die ausgewählten Daten in sogenannten Layouts zu organisieren und darzustellen. Weiterhin kann die Arbeit in der Datenbank automatisiert oder mit einer Scriptsprache programmiert werden. ( Der Umgang mit dieser Datenbank wurde während der Arbeitstagung in Göttingen demonstriert. Das Scriptum dazu liegt als Material vor.) Ein kleiner Aussschnitt ist oben zu sehen und gibt einen Eindruck von der Größe der ursprünglichen Datenbank. Dieses Datenmonstrum ruft danach, normalisiert zu werden. Ein wenig davon wollen wir ja leisten. Seite 33 Daten importieren © Hans-Georg Beckmann 2002 Alle Daten wurden für die Tabelle SCHUELER anonymisiert. Alle Namen wurden verändert, alle Geburtsdaten wurden verändert. Alle Anschriften und sonstigen persönlichen Daten wurden verändert. Geschlecht, Alter, Kurswahlen, Noten und weitere Daten wurden erhalten. In der Tabelle für die KURSE wurden die Lehrernamen und Namenskürzel verändert, Kursnummern und Themen und Noten blieben jedoch erhalten. In der Tabelle HATKURS tauchen ID-Nummer und Kursnummer auf, die so auch in den anderen beiden Tabellen zu sehen sind. In den Textdateien, die man nun importieren will, müssen die Daten so organisiert sein, dass beim Einlesen auch die richtigen Felder den richtigen Attributen der Tabellen zugeordnet werden. Wenn man das sicher gestellt hat, reicht ein MySQL-Befehl aus, um den Datenimport zu realisieren. LOAD DATA INFILE erwartet als Parameter den Namen der Textdatei in Hochkomma (" ") und eine Angabe darüber, in welche Tabelle der Datenbak eingelesen werden soll. Bei der Angabe der Tabelle ist zu beachten, dass der Datenbankname gefogt von einem Punkt gefolgt vom Tabellennamen angegeben wird. Wenn alles geklappt hat, wird man mit einer netten Meldung des Systems belohnt. Dabei sollte man sich nicht irritieren lassen, wenn es auche eine Reihe von Warnungen gegeben hat. Seite 34 Daten anschauen © Hans-Georg Beckmann 2002 Nun bleibt aber die berechtigte Sorge, ob denn auch alle Daten so angekommen sind, wie wir das wollten. Es muss also mal in die Datenbank hineingeschaut werden. Probieren sie mysql> select * from schule.schueler; oder mysql > select * from schueler; Sie sollten nun alle Datensätze zu sehen bekommen: Die Form, in der man den Inhalt der Datei sieht ist hier recht unschön, da die vielen Attribute nicht in eine Zeile passen und daher auf mehrere Zeilen verteilt werden. Versuchen sie auch mysql > select * from kurse; Seite 35 Auswahl mit SELECT © Hans-Georg Beckmann 2002 Das sieht dann schon etwas besser aus, weil nicht ganz so viele Attribute auftauchen. Der Stern (* ) hinter SELECT besagt, dass alle Attribute der Tabelle dargestellt werden. Will man nur einige Attribute sehen, muss man genau angeben, welche: Probieren sie mysql > select Name,Vorname from schueler; Das sieht doch schon ganz anders aus ! Probieren sie nun: mysql > select kursthema,khj from kurse where kurslehrerkurz="Cq"; Hier wird noch eine weitere Einschränkung vorgenommen: Jetzt sollen aus der Tabelle KURSE die Attribute KURSTHEMA und Kurshalbjahr (KHJ ) angezeigt werden, mit der Bedingung, dass das Lehrerkürzel ( KURSLEHRERKURZ ) dem Textstring "Cq" entspricht. Das ist eine Auswahl ,die schon recht übersichtlich ist, wie man sehen kann. "Cq" Seite 36 Auswahl mit SELECT © Hans-Georg Beckmann 2002 Die SELECT - Anweisung erlaubt es also, aus einer Tabelle mit bestimmten Suchbedingungen Datensätze herauszufiltern und von diesen ausgwählte Attribute darzustellen. Weitere Beispiele für SELECT: Man kann die Anzahl von Datensätzen in einer Tabelle mit COUNT(*) festellen lassen. Probieren sie es auch mit den andere Tabellen aus. Man kann Abfragen mit einem logischen UND verbinden. Die Teile der Abfrage stehen dabei in Klammern und natürlich ist es das englische AND, das man verwenden muss. Wir suchen z.B. alle Kurse des zweiten Kurshalbjahres, die vom Lehrer mit dem Kürzel "Cq" angeboten wurden. Es waren zwei Kurse. Genauso sind Verknüpfungen mit OR und NOT möglich. Unscharfe Suche Nicht immer müssen Attributwerte exakt angegeben werden. Der Befehl select * from kurse where kursnummer like M% ; würde aus der Tabelle Kurse alle die Datensätze finden, deren Kursnummer mit einem M be- Seite 37 Auswahl mit SELECT © Hans-Georg Beckmann 2002 ginnt. Das dürften dann Mathematik- und Musikkurse sein. Das % - Zeichen ist hier ein Platzhalter, der die unscharfe Suche möglich macht. Suche in Bereichen Kann man bei einem Attribut einen Bereich angeben, dann ist eine Abfrage wie die folgende möglich: select * from kurse where KHJ between 2 and 4 Hier werden also alle Kurse gesucht, die im zweiten bis vierten Kurshalbjahr angeboten wurden. Das wäre auch mit select * from kurse where (KHJ>1) and (KHJ <=4) ; möglich gewesen. Verknüpfen von Tabellen Bevor wir nun zwei Tabellen miteinander verknüpfen, müssen einige Änderungen vorgenommen werden. Es sollen die Tabellen Schueler und Hatkurs mithilfe der ID_Nummer abgeglichen werden. Dazu muss in der Tabelle das eindeutige Feld ID_Nummer als Primärschlüssel deklariert werden. Das hätte man schon bei dem CREATE-Befehl miteinbauen können. Nun kommt es aber erst jetzt. Im ersten Schritt ist es Voraussetzug, das Feld ID_Nummer als nicht Null ( NOT NULL) festzulegen, denn leere Felder taugen nicht als Schlüsselfeld. Der Befehl, mit dem das geht ist ALTER TABLE: Geben sie ein: alter table schueler modify ID_Nummer char(20) not null; Nun wird der Primärschlüssel erklärt: alter table schueler add primary key(ID_Nummer); Die Tabellen schueler und hatkurs haben das gemeinsame Feld ID_Nummer, mit dem nun gear- Seite 38 Auswahl mit SELECT © Hans-Georg Beckmann 2002 beitet wird. Nun sollen Name und Vorname und Kursnote aller Schülerinnen und Schüler gesucht werden, die im Kurs mit der Kursnummer "Ma 11" sind. Der SELECT - Befehl ist: Hinter SELECT werden durch Komma getrennt alle Attribute angegeben, die man sehen will. Da sie aus verschiedenen Tabelle stammen, wird jeweils die Tabelle mitangegeben - also etwa schueler.vorname und hatkurs.note. Dann kommt das Schlüsselwort FROM und dahinter durch Komma getrennt die beiden beteiligten Tabellen. Dann kommt die WHERE - Bedingung. In der ersten Klammer wird die Verknüpfung hergestellt, wenn es heißt: schueler.id_nummer = hatkurs.id_nummer Die zweite Klammer enthält die Filterbedingung für die Tabelle. Es werden nur die Datensätze genommen, die der Kursnummer Ma 11 gehorchen. Man erhält schon ein nettes Ergebnis, wie zu sehen ist. Hätte man sich die Kursnummer mit ausgeben lassen, hätte man gemerkt, dass sowohl der Matheleistungskurs mit der Nummer Ma 11 als auch der Grundkurs mit der Nummer ma 11 aufgelistet ist. Das hat damit zu tun, das SQL keine Grossund Kleinschreibung unterscheidet. Aber da gab es ja noch ein Feld Kursart, mit dem man weiter filtern könnte... Seite 39 Auswahl mit SELECT © Hans-Georg Beckmann 2002 Die Verknüpfung wurde mit SELECT verwirklicht. Im Allgemeinen sorgt man dafür, das Abfrage und Verknüpfung in der Syntax getrennt werden. Es wird hier der INNER JOIN-Befehl benutzt. Wenn sie die dargestellte Abfrage sehen, fällt sicher auf, dass in der oberen Zeile wieder die Attribute aus den Tabellen angegeben sind. Nach dem FROM kommt dann der Name der Tabelle mit dem Schlüsselfeld, dann mit INNER JOIN die zu verknüpfende Tabelle. Hinter dem ON wird die Bedingung für die Verknüpfung angegeben. Erst danach kommt die Auswahlbedingung nach dem WHERE. Das Ergebnis ist wie auch oben eine Zensurenliste. Aufgaben Suchen sie aus der Tabelle Kurse alle Kurse mit ihren Themen, die von "Kr" unterrichtet werden. Suchen sie alle Kurse des dritte Kurshalbjahres, die von "Db" unterrichtet werden. Suchen sie in der Tabelle Kurse alle Deutschgrundkurse (Tip: Die beginnen alle mit einem dt) Suche alle Schüler, die im Leistungkurs En 11 enthalten sind. Seite 40 Auswahl mit SELECT © Hans-Georg Beckmann 2002 Weitere Beispiele Im Abschnitt "Datenbanken 2" sind SQL-Abfragen behandelt, die ebenfalls auf der Datenbank "Schule" beruhen. Daraus zwei Beispiele: Mit SELECT AVG(Feldname) kann man den Durchschnittswert eines Attributs einer Tabelle ausgeben lassen. Versuchen sie herauszufinden, ob Mädchen im Matheunterricht benachteiligt werden: Bei einer Durchschnittsnote von 7,7 können wir nun streiten. Das aber erst, wenn sie auch untersucht haben, wie es mit der Durchschnittsnote der Jungen aussieht. Schauen sie doch auch mal in den Leistungskursen vorbei: Als Ergänzung ist auf den nächsten Seiten noch einmal das Übungsmaterial von Fabian Meyer zu finden, das weitere Aufgaben un d Beispiele enthält. Seite 41 Übungen zu SQL © Fabian Meyer 2002 MySQL: Select und Join -- Das Handwerkzeug für Abfragen In diesem Abschnitt werden die wichtigsten Methoden für das "Befragen" von MySQL-Datenbanken erläutert. Die ersten Versuche mit SELECT wurden ja bereits in der MySQL-Einleitung (MYSQL.pdf) angestellt. Wir reissen dies hier deshalb nur kurz an und bauen dann darauf auf. SELECT Mittels dieses Befehls lassen sich beliebige Attribute aus beliebigen Tabellen auswählen. Dies kann man natürlich so bedingen, dass man sich genau die Daten geben lässt, welche man auch bekommen will. Man benötigt ja nicht immer gleich die ganze Datenbank. Andernfalls könnte man auch gleich mit einer großen Liste auf Papier arbeiten, der Aufwand wäre ähnlich. Man stelle sich unsere Beispieldatenbank "Schule" mal ausgedruckt vor: alleine die Tabelle "hatkurs" besitzt 3840 Instanzen! Bleiben wir mit unserem ersten Beispiel gleich bei dieser "Monster-Tabelle": SELECT * FROM `hatkurs` WHERE 1 LIMIT 0, 50; und siehe da? Was bekommen wir? Eine Tabelle mit nur 50 Einträgen und nicht 3840! Woran könnte es liegen? Natürlich an der "LIMIT"-Klausel: Diese besagt hier: Selectiere nur die Einträge 0 bis 50. Das "WHERE 1" besagt nur, daß keine weiteren Kriterien die Auswahl einschränken. Hier setzen wir nun an und schränken die Auswahl ein. Wie sieht eigentlich die Tabelle "hatkurs" aus? Da die Befehle für diese Abfragen bereits in der MySQL-Einführung vorgestellt wurden, zeige ich hier nur kurz die Struktur. "hatkurs" enthält: I D_Nummer Kur snummer Note Punkte char (20) char (5) char (2) i nt(11) Also suchen wir jetzt einmal alle Einträge aus "hatkurs", in denen Schüler 15 Punkte bekommen haben: SELECT * FROM hatkurs WHERE Punkte = 15; (Ich habe hier das LIMIT weggelassen, da ich weiß das es nicht allzu viele Datensätze mit 15 Punkten gibt ;-) ) Soweit ist das ganze noch ganz einleuchtend. Wo wir zuvor nicht weiter eingeschränkt haben, lassen wir uns nun nur Seite 42 Übungen zu SQL © Fabian Meyer 2002 diejenigen Datensätze ausgeben, wo die Punktzahl 15 beträgt. Select ist also recht simpel, nicht wahr? Schön und gut, aber was ist mit Joins? Dazu kommen wir jetzt! Wir wollen nun nicht nur diese obskuren Nummern da stehen haben, sondern hätten gerne gleich eine Auflistung welche Schüler hinter diesen Leistungen stecken. Um diese Information zu erhalten, müssen wir aber Daten aus einer zweiten Tabelle mit abfragen, und zwar auch nicht mit einer eigenständigen Anfrage, sondern direkt verknüpft mit unserer Abfrage, wer 15 Punkte hat. Genau dazu ist der "JOIN" gedacht. Mit "JOIN" lässt sich genau das Gewollte erreichen: Wir können zwei oder mehrere Tabellen-Abfragen miteinander verknüpfen. Die einfachste Methode in MySQL einen "JOIN" auszuführen ist mit dem Komma ",". In MySQL steht das Komma synonym für den Befehl JOIN, allerdings ist es mit dem Komma einfacher und leichter zu lesen, was die Abfrage bewirken soll. Zunächst einmal führen wir die Abfrage aus. Ich erkläre dann anhand dieser die Vorgehensweise: SELECT Schueler.Name, Schueler.Vorname, hatkurs.Kursnummer FROM Schueler, hatkurs WHERE Punkte = 15 AND hatkurs.ID_Nummer = Schueler.ID_Nummer; Aha! Das sieht doch schon komplizierter aus! Ist es aber prinzipiell nicht. Was machen wir da? Eigentlich nicht viel neues: Wir selektieren die Attribute "Name" und "Vorname" aus der Tabelle "Schueler" und "Kursnummer" aus "hatkurs", da wir hier mit einem JOIN arbeiten, müssen wir natürlich angeben aus welcher Tabelle die auszugebenden Daten kommen sollen. Die ID_Nummer beispielsweise ist in beiden Tabellen vorhanden, wollten wir diese mit ausgeben wüsste das MySQL-System nicht welche ID_Nummer ausgegeben werden soll (sprich aus welcher Tabelle die Nummer stammen soll). In dem "FROM"-Teil sehen wir dann das Komma als eigentlichen "JOIN"-Operator. Hier wird angegeben welche Tabellen gejoint werden sollen, in unserem Fall natürlich "Schueler" und "hatkurs". Der "WHERE"-Teil ist erstmal klar, "Punkte = 15" kennen wir ja schon, aber was kommt dann? Tja, man kann einfach mehrere Bedingungen in die "WHERE"-Klausel schreiben, indem man ein "AND" verwendet. Das "AND" funktioniert einfach wie ein "AND WHERE", sprich: wir können die Auswahl weiter eingrenzen. In unserem Fall sorgen wir mit der zweiten Bedingung Seite 43 Übungen zu SQL © Fabian Meyer 2002 dafür, daß nur die Schuelerdaten aus der Tabelle "Schueler" ausgegeben werden, deren ID_Nummern auch im Ergebnis unser Abfrage "Wer hat 15 Punkte bekommen?" auftauchen. Da "ID_Nummer" sowohl in "Schueler" als auch in "hatkurs" vorkommt, lassen sich diese Tabellen auf diese sinnvolle Art und Weise joinen. Soweit mit dieser Abfrage... Machen wir es doch nochmal mit einem Join von allen 3 Tabellen! Unsere Fragestellung: "Liste alle Schueler mit Name, Vorname und Geschlecht auf die 15 Punkte in einem Leistungskurs bekommen haben, sowie die Kursnummer und das Thema des Kurses." Das klingt jetzt erstmal sehr schwierig, ist es aber prinzipiell gar nicht. Man muss nur die einzelnen Bedingungen verknüpfen und die richtige Zusammenstellung finden: Bedingung, WHERE-Syntax: Leistungskurs, kurse.kursart = 'L' 15-Punkte, hatkurs.Punkte = 15 Also nicht viel neues, bisher oder? Nun bauen wir die Abfrage: SELECT s.Name, s.Vorname, s.Geschlecht, k.kursnummer, k.kursthema FROM Schueler s, kurse k, hatkurs h WHERE h.Punkte = 15 AND h.ID_Nummer = s.ID_Nummer AND h.kursnummer = k.kursnummer AND k.kursart='L'; So, was ist neu? Eigentlich nichts. Wir haben nur erstmal ein "Alias" für die Tabellen gesetzt: "Schueler s" bedeutet, daß die Tabelle "Schueler" in dieser Abfrage auch als "s" angesprochen werden kann, analog funktioniert das natürlich auch für die anderen Tabellen ("kurse" ist dann "k" und "hatkurs" ist dann "h"). Ansonsten haben wir nur eine dritte Tabelle hinzu genommen und unsere Bedingungen in SQL formuliert und hinzugefügt. Als Ergebnis bekommen wir eine schöne Antwort auf unsere Fragestellung. Es sollte in diesem kurzen Beitrag eigentlich deutlich geworden sein, daß man in SQL mit einfachen Befehlen auch komplizierte Fragestellungen zu lösen vermag. An Theorie soll das genügen, der Rest ist Übungssache. Seite 44
