Datenbank - Teil 3 Ziele dieser Übung: Eine Datenbank anlegen mit SQL Daten eingeben mit SQL Abfragen stellen mit SQL und PHP © IAS Datenbanken-Teil 3 1 Datenbank - Teil 3 Datenbankserver Entwickelt von der schwedischen Aktiengesellschaft MySQL Unter GNU General Public License (GPL) kostenlos verfügbar Weltweit der populärste Open-Source-Datenbankserver mit mehr als 6 Millionen Installationen http://www.mysql.de/ XAMPP Komplettpaket für Windows: © IAS Apache 2.4.4, MySQL 5.5.32, PHP 5.4.16, phpMyAdmin 4.0.4, FileZilla FTP Server 0.9.41, Tomcat 7.0.41 (with mod_proxy_ajp as connector), Strawberry Perl 5.16.3.1 Portable, XAMPP Control Panel 3.2.1 (from hackattack142) stand: Juni 2013 Datenbanken-Teil 3 2 1. Starten und Beenden XAMPP wird standardmäßig in c:\xampp installiert! Die Dienste „Apache“ und „MySQL“ starten „Stop“ beendet die Dienste © IAS Datenbanken-Teil 3 3 1. Starten und Beenden MySQL verfügt über GUI zur Verwaltung (z.B. phpMyAdmin) und über einen MySQL-Monitor (Kommandozeile). Im Eingabeaufforderung-Fenster eingeben: c:\xampp\mysql\bin\mysql.exe –u root © IAS Datenbanken-Teil 3 4 2. Neue Datenbank anlegen Im Rahmen der Übungen soll eine Bibliotheks-Datenbank angelegt werden, die Bücher (inkl. Sachgebiet und Ort), Benutzer und Ausleihvorgänge verwaltet. Mit der SQL-Anweisung CREATE DATABASE dbname wird eine neue Datenbank angelegt: CREATE DATABASE bib; SQL-Anweisung immer mit Strichpunkt (;) abschließen SQL-Anweisungen beziehen sich immer auf eine bestimmte Datenbank. Damit diese nicht bei jeder Anweisung angegeben werden muss, kann zu der betreffenden Datenbank mit USE dbname „gewechselt“ werden. USE bib; © IAS Datenbanken-Teil 3 5 3. Tabellen anlegen Tabellen werden mit der SQL-Anweisung „CREATE TABLE tbname (fname datentyp null-Werte, default, …)“ angelegt. Zuerst soll eine Tabelle für die Bücher mit den Feldern Jahr, Buchnummer, Verweis auf Sachgebiet, Signatur, Autoren, Titel, Verlag und Erscheinungsjahr erstellt werden. CREATE TABLE buecher (jahr DECIMAL (4,0) NOT NULL, bunr DECIMAL (4,0) NOT NULL, sgk DECIMAL (4,0) NOT NULL, sgnr DECIMAL (4,0) NOT NULL, aut1 VARCHAR (20) NULL, aut2 VARCHAR (20) NULL, aut3 VARCHAR (20) NULL, titel VARCHAR (20) NULL, verlag VARCHAR (20) NULL, esj DECIMAL (4,0) NULL, PRIMARY KEY(jahr, bunr)); © IAS Datenbanken-Teil 3 6 3. Tabellen anlegen Weitere Tabellen enthalten die Sachgebiete und die Benutzer (mit Mitgliedsnummer, Name, PLZ, Straße und Aufnahmedatum) CREATE TABLE sachgebiet (sgk DECIMAL (4,0) NOT NULL, sachgb VARCHAR (30) NOT NULL, PRIMARY KEY (sgk)); CREATE TABLE benutzer (mitnr DECIMAL (4,0) NOT NULL, name VARCHAR (30) NOT NULL, plz DECIMAL (5,0) NULL, stra VARCHAR (30) NULL, datm DATE NOT NULL, PRIMARY KEY(mitnr)); © IAS Datenbanken-Teil 3 7 3. Tabellen anlegen Weitere Tabellen enthalten die Orte (PLZ, Ort) und die Ausleihvorgänge CREATE TABLE orte (plz DECIMAL (5,0) NOT NULL, ort VARCHAR (40) NOT NULL, PRIMARY KEY (plz)); CREATE TABLE entleihung (jahr DECIMAL (4,0) NOT NULL, bunr DECIMAL (4,0) NOT NULL, mitnr DECIMAL (4,0) NOT NULL, date DATE NOT NULL, PRIMARY KEY (jahr, bunr)); © IAS Datenbanken-Teil 3 8 4. Daten eingeben Daten werden mit der SQL-Anweisung INSERT INTO tbname (fname, …) VALUES (wert, …) eingegeben Auf Grund der Fremdschlüssel (auch wenn MySQL keine referentielle Integrität sicherstellt) zuerst für die Tabelle Sachgebiete: INSERT INTO sachgebiet (sgk, sachgb) VALUES (1, 'Datenbanken'), (2, 'Web-Technologien'); © IAS Datenbanken-Teil 3 9 4. Daten eingeben Dann für die Tabelle Bücher: INSERT INTO buecher (jahr,bunr, sgk, sgnr, aut1, aut2, aut3, titel, verlag, esj) VALUES (2012, 1, 1, 1, 'konnertz', 'strobel',NULL, 'MySQL', 'IAS', 2011), (2012, 2, 1, 2, 'malz', NULL, NULL, 'PHP', 'IAS', 010), (2010, 1, 2, 1, ‚beck',‘maga',NULL,'Fussball', 'DFB', 2009); © IAS Datenbanken-Teil 3 10 4. Daten eingeben Die Tabelle Orte: INSERT INTO orte(plz,ort)VALUES(70550,'Stuttgart'),(70569, 'Vaihingen'); Die Tabelle Benutzer: INSERT INTO benutzer (mitnr, name, plz, stra, datm) VALUES (1, 'student1', 70550, 'Pfaffenwaldring 47', '2012-01-01'),(2, 'student2', 70569,'Pfaffenwaldring 32', '2012-01-02'); © IAS Datenbanken-Teil 3 11 4. Daten eingeben Die Tabelle Einleihung: INSERT INTO entleihung (jahr, bunr, mitnr, date) VALUES (2010, 1, 1, '2012-03-01'),(2012, 1, 2, '2012-03-03'), (2012, 2, 2, '2012-04-05'); © IAS Datenbanken-Teil 3 12 5. Daten abfragen Daten werden mit der SQL-Anweisung SELECT projektion FROM join WHERE selektion ausgegeben. Abfrage: Welche Bücher sind in der Datenbank? SELECT jahr, bunr, aut1, titel FROM buecher; © IAS Datenbanken-Teil 3 13 5. Daten abfragen Abfrage: Welcher Benutzer kommt aus Vaihingen? SELECT benutzer.name FROM benutzer, orte WHERE benutzer.PLZ = orte.PLZ AND orte.ort = 'Vaihingen'; © IAS Datenbanken-Teil 3 14 5. Daten abfragen Abfrage: Welche Bücher hat das Mitglied mit dem Namen „student2“ ausgeliehen? SELECT buecher.titel FROM buecher, entleihung, benutzer WHERE buecher.jahr = entleihung.jahr AND buecher.bunr = entleihung.bunr AND entleihung.mitnr = benutzer.mitnr AND benutzer.name = 'student1'; © IAS Datenbanken-Teil 3 15 5. Daten abfragen Abfrage: Wie viele Bücher haben die Benutzer jeweils ausgeliehen? SELECT benutzer.name, count(entleihung.bunr) AS anzahl FROM benutzer, entleihung WHERE benutzer.mitnr = entleihung.mitnr GROUP BY benutzer.name; MySQL-Monitor verlassen, d. h. Im Eingabeaufforderung-Fenster eingeben: quit © IAS Datenbanken-Teil 3 16 MySQL-Monitor verlassen, d. h. Im Eingabeaufforderung-Fenster eingeben: quit; Die DB „bib“ wurde nun im Verzeichnis „C:\xampp\mysql\data“ angelegt. Das Eingabeaufforderung-Fenster wird für die weitere Teilaufgabe nicht mehr benötigt. © IAS Datenbanken-Teil 3 17 6. Anzeigen von DB-Daten in HTML-Seiten Web-Server Apache wurde bereits gestartet. Einen Browser (z. B. Internet Explorer) über das Startmenü starten und folgende URL eingeben: http://localhost/ XAMPP Startseite soll erscheinen © IAS Datenbanken-Teil 3 18 6. Anzeigen von DB-Daten in HTML-Seiten Die HTML- und PHP-Dateien liegen im Verzeichnis c:\xampp\htdocs Dort erstellen Sie sich die Datei „bib.php“, die im Rahmen der Übung modifiziert werden soll (s. weitere Folien) © IAS Datenbanken-Teil 3 19 6. Anzeigen von DB-Daten in HTML-Seiten Datei „bib.php“ bearbeiten Sie können die PHP-Datei modifizien (im Editor öffnen), um die vorher im MySQL-Monitor ausgeführten Abfragen auf der HTML-Seite anzeigen zu lassen. <HTML> <HEAD> <TITLE>Uebung ST2: PHP Abfrage für MySQL</TITLE> </HEAD> <BODY> Uebung ST2: PHP Abfrage fuer MySQL<br><br> 1/3 <SCRIPT LANGUAGE="php"> // zuerst erfolgt der Aufbau einer Verbindung zur Datenbank // Dabei wird der Server (Localhost) angegeben. Normalerweise // muss man hier auch Benutzername und Passwort mit angeben. $connection=mysql_connect ('localhost', 'root') or die ("Keine Verbindung zum Server"); // bei einem Fehler © IAS Datenbanken-Teil 3 20 6. Anzeigen von DB-Daten in HTML-Seiten 2/3 // Danach wird die zu verwendende Datenbank (bib) ausgewählt. $db=mysql_select_db ("bib") or die ("Keine Verbindung zur Datenbank"); // bei Fehler $abfrage = "SELECT * FROM buecher"; echo "Folgende Abfrage wird ausgefuehrt: $abfrage<br /><br />"; // Abfrage wird durchgeführt $ergebnis = mysql_query ($abfrage) or die ("SQL-Fehler"); // bei einem Fehler // HTML-Tabelle zur Darstellung des Ergebnisses echo "<table border=\"1\">"; echo "<tr>"; echo "<th>Jahr</th>"; echo "<th>Buchnr.</th>"; echo "<th>Titel</th>"; echo "</tr>"; © IAS Datenbanken-Teil 3 21 6. Anzeigen von DB-Daten in HTML-Seiten // Alle gefundenen Zeilen der Abfrage werden durchlaufen while ($zeile = mysql_fetch_array($ergebnis)) { // Neue Zeile in der HTML-Tabelle echo "<tr>"; // Attribute der gelesenen Zeile in Variablen speichern $jahr=$zeile['jahr']; $bunr=$zeile['bunr']; $titel=$zeile['titel']; echo "<td>$jahr</td>"; echo "<td>$bunr</td>"; echo "<td>$titel</td>"; echo "</tr>"; } // Am Ende wird die Verbindung zur Datenbank wieder getrennt mysql_close (); // HTML-Tabelle abschließen echo "</table>"; </SCRIPT> </BODY> </HTML> © IAS Datenbanken-Teil 3 3/3 22 6. Anzeigen von DB-Daten in HTML-Seiten Führen Sie diese Datei aus, indem Sie folgende URL im Browser Eingeben: http://localhost/bib.php © IAS Datenbanken-Teil 3 23