Kleine Einführung in SQL c Clab-Team Institut für Computerlinguistik Universität Zürich Dez. 2008 Zusammenfassung Die Structured Query Language, kurz SQL, ist eine Datenbanksprache, die sowohl zur Abfrage, als auch zur Manipulation von Daten in einer relationalen Datenbank dient. Kleine Einführung in SQL 1 Voraussetzungen 1 Perl auf Basis des abstrakten Datenbank-Layers DBI zwar möglich, aufgrund der unterschiedli- Über das grundsätzliche Ziel des Einsatzes von L SQL sollten Sie informiert sein, bevor Sie diese chen SQL-Implementierungen aber nur schwer zu realisieren ist. Lerneinheit benützen, ansonsten werden keine Kenntnisse vorausgesetzt. 2 Lernziel 4 Grundlegendes In einer relationalen Datenbank werden Informationen in Tabellen gespeichert. Jede Tabelle SQL ist relativ leicht zu erlernen. In dieser speichert zu unterschiedlichen Datensätzen (ho- Lerneinheit finden Sie eine kleine Einführung rizontal) diverse Informationen (vertikal). in die Sprache und ihre Verwendung. Die- Ein Beispiel für eine Datentabelle: se Einführung ist knapp und beschränkt sich auf das Wesentliche, ist aber für einen Einstieg ausreichend. Sie stammt von Markus Wolf, Böblingen. Das Original findet sich auf seiner Website unter http://www.perlunity.de/ perl/datenbanken/sql.shtml.Am Schluss Mitarbeiter MID Nachname Vorname Standort Gehalt 123 Wolf Markus Böblingen 30000 456 Simpson Homer Springfield 120000 789 Schmidt Werner Bonn 75000 der Lerneinheit finden Sie eine ILAP, in der Sie das Gelernte vertiefen können. Sie sollten nach Um Daten in diese Tabelle einzugeben, zu Durcharbeiten dieser Lerneinheiten einfache L ändern, zu löschen und abzufragen, kann man Anfragen an ebenso einfache Datenbanken feh- die Structured Query Language, kurz SQL, ver- lerlos in SQL formulieren können. Sie sollten wenden. ausserdem die Probleme erkannt haben, wel- Unser Streifzug durch SQL beginnt bei einfa- che sich beim Umsetzen von natürlichsprachlich chen Abfragen über eine Tabelle, Abfragen mit formulierten Informationsbedürfnissen in SQL Kriterien und schliesslich Abfragen über meh- ergeben. rere Tabellen, so genannte Joins. Abschliessend werden die Daten einer Tabelle manipuliert, d.h. 3 Einleitung Daten werden • hinzugefügt, Die Structured Query Language, kurz SQL, wird verwendet, um relationale Datenbanken anzusprechen. SQL ist sehr umfangreich und • geändert, und • gelöscht. umfasst nicht nur Anweisungen und Befehle zum Abfragen der Daten - wie man aus dem Namen schliessen könnte - sondern auch die Möglichkeiten, Daten zu ändern oder die Datenbank selbst zu beschreiben. Somit kann man mit SQL so ziemlich alle Aufgaben erledigen, die zur Administration und Benutzung von Datenbanken anfallen. Hier sei noch erwähnt, dass es einen SQLStandard gibt, an dem sich die Datenbankher- 5 Abfragen mit SELECT Eine Abfrage dient dem Suchen und Anzeigen von vorhandenen Daten. Dies geschieht in SQL immer mit dem SELECT-Befehl. Wir wollen aus der Beispieltabelle Mitarbeiter den Namen und Standort aller Mitarbeiter erfahren: steller leider nur orientieren, aber sich nicht dar- SELECT Nachname, Vorname, Standort an halten. Dies hat zur Folge, dass eine da- FROM Mitarbeiter; tenbankunabhängige Software-Entwicklung mit So sieht das Ergebnis aus: Kleine Einführung in SQL 2 Nachname Vorname Standort SELECT Nachname Wolf Markus Böblingen FROM Mitarbeiter Simpson Homer Springfield WHERE Gehalt >= 50000; Schmidt Werner Bonn die folgenden Datensätze zurückgeliefert: Was genau haben wir getan: Wir haben uns alle Nachname Mitarbeiter aus der Tabelle Mitarbeiter zur An- Simpson sicht ausgewählt, und zwar deren Namen und Schmidt Standort. Die Mitarbeiter-Nummer (MID) haben wir dabei ausser Acht gelassen. Es wurden also alle Mitarbeiter, deren Gehalt Eine Abfrage funktioniert generell also so: gleich ist oder über 50000 liegt, ausgewählt. Ein anderes Beispiel: SELECT SpaltenName, SpaltenName, ... FROM TabellenName; SELECT Gehalt FROM Mitarbeiter Um alle Informationen, die in einer Tabelle gespeichert sind, zu sehen, benutzt man folgenden Ausdruck: WHERE Nachname = ’Simpson’; Das Resultat: Gehalt SELECT * FROM TabellenName; 6 SELECT mit Abfragekriterien 120000 7 Mehrere Kriterien in einer Abfrage Man kann Kriterien mit logischen OperatoUm eine Abfrage durchzuführen, die nicht ren verknüpfen, und damit sehr detaillier- alle Datensätze in der Tabelle zurückliefert, te und aussagekräftige Abfragen durchführen. benötigen wir Abfragekriterien. Anhand dieser Beispiel: Kriterien werden die Datensätze gefiltert. Die allgemeine Form einer Abfrage mit Kriterien: SELECT Nachname, Vorname FROM Mitarbeiter SELECT SpaltenName,SpaltenName, ... WHERE Gehalt > 50000 AND Standort != ’Springfield’; FROM TabellenName WHERE Kriterium Wie man im Resultat sieht, wäre Homer Der Aufbau des Kriterienausdrucks leitet Simpson aufgrund seines Gehaltes, das grösser sich von der relationalen Algebra ab. Folgende als 50000 ist, im Resultat enthalten, allerdings Operatoren gibt es in SQL, um vergleichende wurde sein Standort Springfield als Ausschlus- Ausdrücke zu bilden: skriterium angegeben: = gleich Nachname Vorname <> oder != nicht gleich Schmidt Werner < kleiner als > grösser als <= kleiner als oder gleich >= grösser als oder gleich Aus der Tabelle Mitarbeiter werden mit der Abfrage Die beiden Kriterien wurden mit AND verknüpft, das entspricht dem logischen UND (∧). Das logische ODER (∨) wird durch OR ausgedrückt. Eine andere Möglichkeit, Aussagen miteinander zu verknüpfen, bieten die Schlüsselwörter Kleine Einführung in SQL 3 IN und BETWEEN. IN wird verwendet, um in Das Resultat dieser Anfrage mit LIKE lautet: Spalten, die Zeichenketten beinhalten, eine L Nachname Menge von möglichen Kriterien anzugeben. Für Schmidt die folgende SQL-Anfrage werden Nachname und Vorname eines Datensatzes zurückgeliefert, Simpson wenn die in der Klammer angegebene Zeichenkette (nach IN) in der Spalte Standort des Datensatzes vorhanden ist. Das Prozentzeichen in der Anfrage ist dabei als Wildcard zu sehen, repräsentiert also alle möglichen Zeichenfolgen. Im Folgenden noch SELECT Nachname, Vorname ein paar Beispiele für den Einsatz von Wild- FROM Mitarbeiter cards: ’Home%’, ’%ome%’, ’%omer’. WHERE Standort IN (’Böblingen’, ’Bonn’); Das Resultat dieser Anfrage lautet also: 8 Schlüssel und Indizes Nachname Vorname Damit man dieselben Daten nicht mehrmals Wolf Markus in unterschiedlichen Tabellen speichern muss, Schmidt Werner werden sie indiziert. Dazu bekommt ein Datensatz in einer Tabelle einen Primärschlüssel Für Zahlenwerte gibt man ein Zahlenintervall (primary key). Dieser besteht aus einer oder mit BETWEEN an: mehreren Spalten. Auf jeden Fall muss der SELECT Nachname, Vorname Primärschlüssel eines Datensatzes in dieser Ta- FROM Mitarbeiter belle eindeutig sein, d.h. es darf keinen anderen WHERE Gehalt BETWEEN 20000 AND 50000; Datensatz mit demselben Schlüssel geben. Man spricht von einem Fremdschlüssel (for- Das Resultat dieser Anfrage mit BETWEEN lau- eign key) bei einer Tabellenspalte, die in einer an- tet: deren Tabelle ein Primärschlüssel ist. Das heisst, Nachname Vorname Wolf Markus Man kann den ganzen Ausdruck mit NOT negieren, es werden also die Datensätze abgefragt, die nicht diesen Kriterien entsprechen: SELECT Nachname, Vorname FROM Mitarbeiter WHERE Standort NOT IN (’Böblingen’, ’Bonn’); Das Resultat dieser Anfrage mit NOT IN lautet: Nachname Vorname Simpson Homer Und damit bekommen wir bei dieser Abfrage wieder Homer Simpson. Ein weiteres Schlüsselwort ist LIKE: SELECT Nachname FROM Mitarbeiter WHERE Vorname LIKE ’H%’; die beiden Tabellen haben miteinander zu tun, ihre Datensätze sind untereinander verknüpft. Für die im nächsten Kapitel behandelten JoinBeispiele gelten folgende Beispieltabellen: Kleine Einführung in SQL 4 Artikel ArtikelNr. Bezeichnung Preis 0010 Kaffeebecher 5 0020 T-Shirt 20 0030 Mütze 10 0040 Sweatshirt 45 Kunden KundenNr. Nachname Vorname Strasse Stadt 99100 Meier Richard Hauptstr. 1 70000 Stuttgart 43689 Banner Werner Moosweg 5 12334 Aachen 32074 Zeller Stefanie Zollstr. 41 09137 Grenzdorf 77077 Schwarz Eugen Bahnhofstr. 13 83990 Fulda BestellNr. KundenNr. ArtikelNr. Menge 1234 99100 0020 5 3645 43689 0010 16 1234 99100 0040 2 9834 32074 0030 9 Bestellungen In diesem Beispiel hat eine Bestellung zwei als Fremdschlüssel gespeichert, und dadurch Fremdschlüssel: zum einen einen Käufer über weiss ich, welcher Kunde welche Bestellung die Kundennummer, zum anderen einen Arti- aufgegeben hat. kel über die Artikelnummer. Wichtig ist, dass in dieser Tabelle kein Primärschlüssel vorhanden ist. Die Bestellnummer kommt doppelt vor, wie man sieht. Mit diesen Tabellen werden wir nun kompliziertere Abfragen durchführen: Joins. Und damit ins Geschehen: wir wollen alle Bestellungen sehen, die Herr Meier aufgegeben hat: SELECT BestNr, ArtikelNr, Menge FROM Bestellungen, Kunden 9 Joins Es gibt zwei Voraussetzungen, um miteinander WHERE Bestellungen.KundenNr = Kunden.KundenNr AND Nachname = ’Meier’; in Verbindung stehende Tabellen optimal einzu- Zu beachten ist in der WHERE-Klausel die No- setzen: tation TabellenName.Spaltenname. Dadurch wer- 1. Redundanzfreiheit: Dieselben Daten dürfen nicht doppelt in unterschiedlichen Tabellen den die Spalten aus den beiden Tabellen nicht verwechselt. Das Ergebnis sieht so aus: Bestellungen gespeichert werden. Es wäre beispielweise überflüssig, in der Tabelle Bestellungen die Da- Bestnr. Artikelnr. Menge ten des Kunden zu speichern, da diese bereits 1234 0020 5 in der Tabelle Kunden erfasst wurden. 1234 0040 2 2. Schlüssel: Woher weiss ich, welcher Kun- Es gibt Kunden, die noch keine Bestellung de zu welcher Bestellung gehört? Seine aufgegeben haben. Die aber, die schon einmal KundenNr, die in der Tabelle Kunden der etwas bestellt haben, sollen einen Bonus bekom- Primärschlüssel ist, wird in der Bestellung men. Dazu wollen wir herausfinden, wer das ist: Kleine Einführung in SQL 5 SELECT DISTINCT Kunden.KundenNr, Nachname, Vorname FROM Bestellungen, Kunden WHERE Bestellungen.KundenNr • COUNT(*): Liefert die Anzahl der Datensätze, die den Abfragekriterien entsprechen Ein paar Beispiele: = Kunden.KundenNr SELECT SUM(Menge), AVG(Menge) ORDER BY Nachname, Vorname; FROM Bestellungen Normalerweise würde Herr Meier im Ergeb- Berechnet die Anzahl der Artikel, die bestellt nis zweimal auftauchen, da er ja zwei Bestellun- wurden, sowie die durchschnittliche Anzahl, die gen aufgegeben hat. Mit dem Schlüsselwort DI- von einem Artikel bestellt wurde. STINCT wird das aber vermieden, er taucht nur einmal auf. DISTINCT filtert also Duplikate un- SELECT MAX(Menge), MIN(Menge) ter den angeforderten Datensätzen heraus. Um FROM Bestellungen noch eins draufzusetzen, haben wir das Ergebnis nach Nachnamen und Vornamen sortieren lassen, und zwar durch ORDER BY. Das Ergebnis dieser Anfrage sieht so aus: Kunden Liefert die Bestellung mit der grössten Menge und diejenige mit der kleinsten Menge von Artikeln . SELECT COUNT(*) FROM Bestellungen Kundennr. Nachname Vorname 43689 Banner Werner 99100 Meier Richard Zählt alle Bestell-Positionen, bei denen mehr als 32074 Zeller Stefanie 10 Stück von einem Artikel bestellt wurden. WHERE Menge > 10 Dies also eine kleine Einführung in die Joins, damit kommt man schon mal weit. Natürlich geht’s aber noch weitaus komplexer.. Wer sich dafür interessiert, bzw. tiefer einsteigen will, findet z.B. bei Wikipedia (http://en. wikipedia.org/wiki/SQL mehr Informationen. 11 Daten manipulieren Mit Abfragen lassen sich die Informationen in einer Datenbank nach Belieben sortieren und anzeigen. Wie kommen die Daten aber in die Datenbank? Wie werden sie geändert und gelöscht? Mit SQL geht das relativ einfach. Als Beispiel gilt die Tabelle Artikel mit den Spalten 10 Funktionen 5 wichtige Funktionen, die in SQL eingebaut sind, sollen hier erläutert werden: ArtikelNr, Bezeichnung und Preis. 11.1 Daten hinzufügen Zuerst fügen wir einen Datensatz in die Tabelle • SUM(SpaltenName): Addiert alle Werte in die- Artikel hinzu: ser Spalte, wenn es Zahlenwerte sind INSERT INTO Artikel • AVG(SpaltenName): Berechnet das arithmeti- VALUES (’0090’,’duschvorhang’,44.95); sche Mittel der Werte dieser Sparte • MAX(SpaltenName): Liefert den grössten Wert dieser Spalte • MIN(SpaltenName): Liefert den kleinsten Wert dieser Spalte In diesem Fall wird ein neuer Datensatz in die Tabelle eingefügt. Man kann die Reihenfolge der Werte auch verändern, dann muss aber auch angegeben werden, welche Spalten man meint. Dabei lassen wir dieses Mal den Preis mal aus, weil er noch nicht feststeht (auslassen kann Kleine Einführung in SQL 6 man alles, ausgenommen die Primärschlüssel, Lerneinheit, die Formulierung relativ einfacher die immer vorhanden sein müssen - in diesem Anfragen in SQL üben. Hier sollen Sie also SQL Fall ist dies die ArtikelNr). “in Isolation” erlernen. (ILAP) Praktische Einführung in SQL INSERT INTO Artikel In der zweiten ILAP wird das Augenmerk (Bezeichnung, ArtikelNr) auf das Verhältnis zwischen natürlichsprachlich VALUES (’duschvorhang’,’0090’); formulierten Informationsbedürfnissen und den entsprechenden Eingaben in SQL gelegt. Hier 11.2 Daten ändern sollen Sie Ihr Gefühl dafür schärfen, wo SQL wir ihn natürlich nachträglich einfügen, indem einfacher ist als Englisch, und wo es sich umgekehrt verhält: wir einen bestehenden Datensatz ändern: (ILAP) SQL und natürliche Sprache Wenn wir den Preis leergelassen haben, können UPDATE Artikel SET Preis = 44.95 WHERE ArtikelNr = ’0090’; Dies ist natürlich auch möglich, wenn die zu ändernden Daten bereits bestehen, und auch, wenn es mehrere sind: UPDATE Artikel SET Preis = 44.95 WHERE Preis = 49.95 Dadurch werden alle Artikel, die bisher 49.95 gekostet haben, nun billiger. 11.3 Daten löschen Zu guter Letzt wollen wir die Daten wieder löschen: DELETE FROM Artikel WHERE ArtikelNr = ’0090’; Damit wird genau ein Datensatz gelöscht, nämlich der Duschvorhang, den wir vorhin eingefügt haben. Um alle Duschvorhänge, die in der TabelleArtikel gespeichert sind, zu löschen, gehen wir wie folgt vor: DELETE FROM Artikel WHERE Bezeichnung = ’duschvorhang’; 12 ILAPs zu SQL Sie finden im folgenden zwei ILAPs. In der ersten werden Sie, anhand der Beispiele in dieser