|
|
Eine Datenbank ist eine Sammlung von einzelnen Datentabellen, die intelligent miteinander verknüpft sind. Die Art der Verknüpfung entscheidet über den Typ der Datenbank und meistens haben wir es mit s.g. relationalen Datenbanken zu tun.
Nehmen wir das Beispiel einer Fluggesellschaft. Sie muss einen Überblick haben über Flugzeuge, Personal, Flüge, Passagiere (und vieles mehr). Nehmen wir einmal nur zwei dieser Kategorien (beim Programmieren würden wir sagen: Klassen). Wie speichern wir diese Daten?
1. Möglichkeit: Grosse Tabelle mit allem
Hier nehmen wir uns als Einheit jeden Passagierflug, d.h. jeder Passagier enthält für jeden Flug einen Eintrag. (Nebenbemerkung: Wenn jemand oder etwas im Datenbankbereich etwas "macht" und das dann aufgezeichnet wird - in diesem Fall die Buchung eines Fluges - dann nennt man das "Transaktion". Wir haben hier also eine Transaktionstabelle.)
Nehmen wir nun an, wir müssten über das Flugzeug auch noch Informationen speichern. Dann müssen wir die Informationen doch auch noch hier eintragen? Und dann fliegen auf dem Flug ja noch Piloten und Stewardessen mit. Also müssen wir noch diese Info auch noch dazu speichern. Aber das wäre irgendwann blöd: Wir würden zu jeder Transaktion immer wieder eine ganze Latte an Informationen speichern - Flugdaten, Flugzeugdaten, Personaldaten, die doch ganz viele Transaktionen gemeinsam haben. Das heisst, wir tragen sie immer und wieder ein, obwohl sie identisch sind.
2. Möglichkeit: Kleine Tabellen mit Keys
In dieser Tabelle befinden sich nur Nummern und Datumsangaben. "ID" gibt dabei eine eindeutige Nummer für den Passagier an, "FID" eine eindeutige Nummer für den Flug. Mit FID, ID und Datumsangabe ist die Transaktion dann eindeutig identifzierbar. In zwei weiteren kleinen Tabellen müssen wir nur noch jeweils einzeln die Daten für die Passagiere und Flüge eintragen:
Auf diese Art und Weise können wir nun zwei verschiedene Art von Auswertungen machen: Wir können auswerten lassen, welche Flüge ein bestimmter Passagier gebucht hat und wir können auswerten lassen, welche Passagiere mit einem bestimmten Flug fliegen.
Welche Flüge hat Herr Schmidt gebucht?
Welche Passagiere haben den Flug Paris-Shanghai gebucht?
Das Funktionsprinzip haben wir damit verstanden. Jetzt packen wir noch ein paar Begriffe oben drauf.
Die Verbindung zwischen zwei Tabellen, genauer, die Beziehung, in der zwei Tabellen zueinander stehen, nennt man eine "Relation". Meist kann man sich darunter die Zuordnung über die Zeiger, die "Keys" vorstellen. Aber es gibt auch andere Relationen. Z.B. Ähnlichkeitsrelationen: Datensätze aus 2 Tabellen werden einander zugeordnet, die sich in einer Variablen lediglich ähnlich sind. Oder deren Koordinatenvariablen zeigen, dass die Orte, die hinter den Datensätzen stehen, nicht zu weit auseinanderliegen. Da gibt es viele Möglichkeiten. Allgemein kann man sagen: Eine Relation ist eine "Verbindungsvorschrift" zwischen zwei Tabellen.
Die Verknüpfung mehrer Tabellen über Relationen nennt man "Joinen". Wir wollen uns gleich einmal ansehen, wie mächtig das Joinen sein kann.
Eine echte Airline-Datenbank ist natürlich nicht so klein. Sie hat wahrscheinlich mehrere tausend Tabellen und die meisten dieser Tabellen haben mehr als 1 Million Zeilen, die ein oder andere vielleicht auch mehr als 1 Milliarde. Wir wollen im Folgenden unser Beispiel etwas erweitern und annehmen, dass es folgende Tabellen gibt:
Bei einer Routine-Untersuchung stellt sich heraus, dass ein Bauteil des Fahrweks fehlerhaft gefertigt wird. Alle Flugzeuge, die dieses Bauteil enthalten, müssen sofort stillgelegt werden. Die Airline muss nun sofort kalkulieren, welche Passagiere davon betroffen sind, wo noch Zimmer frei sind und wieviel sie die Hotelbuchungen kosten werden. Das Ganze erscheint nicht ganz einfach, denn das Bauteil wird in zwei verschiedenen Fahrwerkstypen eingebaut, die in verschiedenen Generationen verschiedener Flugzeugtypen verbaut wurden und die entsprechenden Flugzeuge sind über die ganze Welt verstreut.
Macht nichts. Mit einer relationalen Datenbank ist die Suche ein Klacks.
Wir sehen, dass wir mit einer relationalen Datenbank ganze komplexe Systeme durchwandern können. Wir müssen uns unter einer Tabelle eine Liste von Objekten derselben Klasse vorstellen. Und diese Objekte sind vielfältig mit Objekten anderer Klassen (anderen Typs) verknüpft. Betrachten wir z.B. eine Stadt mit Häusern, Strassen, Menschen, den Kleidern, Autos, Kameras, Schmuckstücken der Menschen, dann ist klar, dass das ganze System durch eine Vielfalt von Klassen strukturiert ist. Aber es gibt auch viele gleichartige Objekte - viele Menschen, viele Kleidungsstücke, viele Strassen usw. und alle diese Objekte der gleichen Klasse führen wir in Tabellen. Den Bezug zwischen den Objekten stellen wir durch Relationen her.
Es gibt Programme, die sind darauf spezialisiert, Klassenstrukturen automatisch in Datenbankstrukturen abzubilden. Sie nennt man "Objektrelationale Mapper". Und es gibt Datenbanksysteme, die besonders stark im Bereich soller Mappings sind - z.B. PostgreSQL.
Für einen speziellen Zweck lässt sich ein RDBMS relativ leicht mit Freebasic herstellen. Alles dazu Notwendige haben wir schon gelernt. Allem voran das gedankliche Konzept der Zeiger, allerdings in Form von Zeigern auf Dateipositionen, nicht auf RAM-Adressen. Letzteres haben wir in Teil 2 (Abschnitt "Random Access") einmal gelernt. Der ganze Witz besteht darin, spezielle Tabellen anzulegen, in denen die Datei-(Tabellen-)position unter einem bestimmten Schlüsselwort, der ID, gespeichert ist. Dann kann das Programm blitzschnell die Position einer bestimmten ID nachschlagen und den Inhalt der Zeile aus der Tabelle suchen. Das ist der technische Kern eines RDBMS.
Übung 1: Schreiben Sie ein kleines RDBMS für die oben genannten drei Tabellen. Die Lib soll SUB's bereitstellt, mit denen man die Tabellen beladen kann ("insert row") und mit denen man spezielle Abfragen durchführen kann: SUB trx_tab(result() as ttrx, nresult as integer, passenger_lastname as string, date_from as string, date_to as string) könnte z.B. alle Buchungen von Passagieren mit dem Nachnamen lastname zwischen date_from und date_to abfragen und in das Array result() stellen.
Übung 2:Testen Sie Ihr RDBMS: Schafft es, bei 1 Million Buchungen und 100.000 Passagiere, die Leistung, die Informationen binnen 1 min. zurückzuliefern?
Heutige RDBMS stellen dem Benutzer eine Abfragesprache zur Verfügung, mit deren Hilfe man in beliebigen Datenbanken auf sehr mächtige Art und Weise Informationen abfragen kann. Je grösser die Datenbank, je mehr Tabellen und Verknüpfungen, desto deutlicher wird die Mächtigkeit dieser Sprache. Für eine DB-basierte Webpage sieht dies in der Regel noch recht harmlos aus, befinden Sie sich in der operativen Datenbank eines grossen Konzerns, sieht die Sache schon deutlich anders aus: Hier benötigen Sie Jahre, um richtig gute Abfragen zu generieren und ohne SQL ginge es wahrscheinlich gar nicht.
SQL heisst "Simple Query Language", "Query" ist das englische Wort für "Suche", "Untersuchung" oder eben "Abfrage". Tatsächlich ist die Abfrage das Kernstück von SQL, aber man kann auch Tabellen generieren, Zeilen einfügen, löschen oder modifzieren, Spalten einfügen, Tabellen umbenennen, Rechte setzen usw.
Wir werden hier ein bisschen etwas von SQL kennenlernen. Eine umfangreiche Einführung kann das aber nicht sein. Es gibt aber ohnehin schon eine Vielzahl guter Tutorials zu SQL, stellvertretend sei hier nur eins verlinkt.
Um eine erste Ahnung zu bekommen, was man mit SQL macht, möchte ich hier anhand des Airline-8-Tabellen-DB-Beispiels von oben ein paar einfache Beispiel-Abfragen notieren, aber die Formulierung in SQL noch nicht hinschreiben.
SQL-Selects geben ihre Ergebnisse immer in Tabellen zurück.
Das sind so ein paar der ganz typischen Abfragen, die aus Anwendersicht auf eine solche Datenbank erfolgen können.
Bei der Suche nach Datenbanken, die Freebasic ansteuern kann, trifft man auf SQLite und MySQL. SQLite ist ein sehr schlankes, kleines RDBMS, das hauptsächlich dann eingesetzt wird, wenn programmeigene Daten anstatt in Dateien in einem RDBMS verwaltet werden soll. SQLite ist weniger für Netzwerk- und Multiuserbetrieb konzipiert.
MySQL dürfte auf der anderen Seite eines der bekanntesten RDBMS überhaupt sein. So gut wie jeder Web-Provider stellt MySQL dem User zur Verfügung. Sehr viele Websites arbeiten mit einer Kombination aus MySQL und der Programmiersprache PHP. MySQL ist kostenlos und steht auf allen Betriebsyystemen zur Verfügung. So lag es nahe, sich damit erstmal zu beschäftigen - das Gelernte ist ebenso für Webdesign und Webapplikationen zu gebrauchen.
Geben Sie in Google "MySQL Download" ein und Sie landen über die erste Fundstelle auf den blau-gelben Seiten von Sun - MySQL. Aktuelle stabile Version zur Redaktionszeit (Anfang 2010) von MySQL ist Version 5.1. Hier heisst die Datenbank-Software seit neuester Zeit "Community Server" - man könnte das Ding ja sonst zu schnell finden. Stellen Sie Ihr Betriebssystem ein und laden Sie sich die Dateien (im Linux-Fall eine gz-Datei, im Windows-Fall am Besten den msi-Installer) herunter.
Hier wird jetzt der Verlauf der Windows-Installation beschrieben.
EIn Klick auf "Execute", das Abarbeiten der Schritte, der Service wird gestartet - und es passiert gar nichts. Nach der Erfolgsmeldung taucht nichts auf dem Bildschirm auf. FUBAR? (Fucked Up Beyond Repair?) Nein. Alles in Ordnung.
Als erstes brauchen wir einen Zugang zu unserem tollen neuen System. Der kommt extrem nüchtern daher. In Programme -> MySQL -> MySQL Server 5.1 finden wir ein CommandLine-Tool. Das starten wir mal. Dann geben wir unser root-Passwort ein. Und da sind wir.
Als erstes geben wir dort mal "help" ein. Das kann nicht schaden. Es erscheint eine überschaubare Liste von Kommandos. Das sind allerdings längst nicht alle, die hier verstanden werden.
Tippen wir einmal ein:
Das sind Datenbanken, die MySQL schon mitbringt. Besonders test sollten wir uns vielleicht einmal ansehen.
create table passagier (
id int(10),
firstname varchar(50),
lastname varchar(50)
);
Wir speichern die Datei so ab, dass wir den Pfad leicht angeben können. Am Besten mit der Endung .sql. Wenn der Pfad C:\freebasic\test\t1.sql heisst, dann geben wir in der MySQL-Kommandozeile ein:
\. C:\freebasic\test\t1.sql + ENTER (also kein Semikolon)Wenn alles gut geht, dann kommt "Query OK, 0 rows affected." Das klingt nicht so erfolgsversprechend, ist aber richtig: Schliesslich ist die Tabelle noch leer, hat also 0 Zeilen. Geben wir nun nochmal "show tables" ein, dann sieht die Sache anders aus: Wir haben eine Tabelle "passagier"!
/*
create table passagier (
id int(10),
firstname varchar(50),
lastname varchar(50)
);
*/
insert into passagier values (0,'Alwin','Müller');
insert into passagier values (1,'Barbara','Schneider');
insert into passagier values (2,'Christian','Huber');
insert into passagier values (3,'Daniela','Klein');
insert into passagier values (4,'Egon','Balder');
insert into passagier values (5,'Fritz','Wepper');
mysql> select * from passagier; +------+-----------+-----------+ | id | firstname | lastname | +------+-----------+-----------+ | 0 | Alwin | M³ller | | 1 | Barbara | Schneider | | 2 | Christian | Huber | | 3 | Daniela | Klein | | 4 | Egon | Balder | | 5 | Fritz | Wepper | +------+-----------+-----------+ 6 rows in set (0.00 sec) mysql>Das sieht schon mal nicht so schlecht aus.
mysql> select * from passagier where substr(lastname,1,2)='Ba'; +------+-----------+----------+ | id | firstname | lastname | +------+-----------+----------+ | 4 | Egon | Balder | +------+-----------+----------+ 1 row in set (0.23 sec) mysql> select * from passagier where substr(lastname,1,2)='Ba';
Damit haben wir MySQL in Betrieb genommen und ein paar erste Funktionstest durchgeführt.
In den nächsten Abschnitten lernen wir ein wenig SQL. Zunächst Befehle, um Tabellen zu bauen und Daten einzulesen. Im nächsten Abschnitt beschäftigen wir uns dann mit dem select-Befehl und wie man Abfragen programmiert.
Zum Nachschlagen gibt im WWW reichlich Quellen. Falls man gerade online ist, kann man die Referenzdokumentation von Sun (einschliesslich kleinem Tutorial zu MySQL) benutzen. Irgendwo im Web habe ich auch eine offline-Dokumentation gefunden.
create database <database_name> |
Es gibt zwei Arten, wie man neue Tabellen anlegen ein: Explizite Definition über die Syntax
create table <name> (varname1 as vartype1, ...); |
create table <name> as select .... from <oldtable> |
Wir haben vor, nun eine ganze Test-Datenbank mit 8 Tabellen einzulesen. Ein Teil der Arbeit wird sein, die Tabellen zu deklarieren - das geschieht explizit. Welche Variablentypen können wir verwenden? Nähere Referenz dazu in der Dokumentation - Teil Datentypen. Hier seien nur ein paar wichtige genannt:
Damit können wir die 8 Tabellen deklarieren. Daten einlesen können wir mit
load data local infile <path> into table <tabname> lines terminated by '\r\n'; |
Der String "lines terminated" muss an das jeweilige Betriebssystem angepasst werden. Bei Linux wäre es nur ein '\n'.
Hier können Sie sich die Definition und den Inhalt der 8 Tabellen herunterladen. Die Textdatei hat die Form des folgenden Beispiels:
$Flughäfen name airport id int(5) cityname varchar(20) x int(5) y int(5) tempwint double(2,1) tempsomm double(2,1) %% id cityname x y TempWint TempSomm 0 München 200 -300 6,00 26,00 1 Düsseldorf -50 150 4,50 24,50 2 Frankfurt 0 0 5,00 25,00
Das heisst, zuerst kommt hinter einem $-Zeichen der interne Tabellenname. Dann kommen alle Angaben, die man für das create-table-Kommando braucht. Ab %% kommt zuerst eine Kopfzeile (die man für das SQL nicht braucht) und dann der Inhalt der Tabellen. Hier Vorsicht: MySQL erwartet wie Freebasic auch als Dezimalpunkt einen Punkt, kein Komma.
Irgendwann kommt dann wieder ein $-Zeichen, das den Anfang der nächsten Tabelle bezeichnet.
Übung: Schreiben Sie ein Freebasic-Programm, das
Führen Sie das SQL aus und schauen Sie sich dann mit "show tables" und "select * from <table>" die Tabellen an.
Anschliessend sollte das Ergebnis so aussehen:
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| airport |
| booking |
| customer |
| flight |
| jets |
| jettype |
| origdest |
| passagier |
+----------------+
8 rows in set (0.00 sec)
mysql> select * from airport
-> ;
+------+--------------+--------+--------+----------+----------+
| id | cityname | x | y | tempwint | tempsomm |
+------+--------------+--------+--------+----------+----------+
| 0 | München | 200 | -300 | 6.0 | 9.9 |
| 1 | Düsseldorf | -50 | 150 | 4.5 | 9.9 |
| 2 | Frankfurt | 0 | 0 | 5.0 | 9.9 |
| 3 | Hamburg | 50 | 300 | 4.0 | 9.9 |
| 4 | Berlin Tegel | 100 | 300 | 4.0 | 9.9 |
(...)
Wenn's mit dem Programmschreiben nicht ganz geklappt haben sollte, können Sie sich eine Lösung hier herunterladen.
Wir haben schon gelernt, dass man sich den Inhalt der Tabellen mit select * from < mytab > anschauen kann. Allerdings scrollt Mysql dann den gesamten Tabelleninhalt durch, was bei 100.000 Zeilen nicht lustig ist. Man sollte das auch möglichst nicht mit Ctrl-C versuchen - das killt den ganzen Client. Die Lösung ist:
select * from <tabname> LIMIT <Zeilenzahl> |
Betrachten wir ersteinmal den vorderen Teil eines select-Kommandos. In allgemeiner Form sieht es so aus:
select ausdruck1 as varname1, ausdruck2 as varname2,... from tabelle1;
Wir können also unsere Variablen im select-Kommando bilden.
mysql> select *,seats_fc+seats_bc+seats_ec as totalseats from jettype; +------+------+----------+-------+----------+----------+----------+------------+ | id | type | velocity | class | seats_fc | seats_bc | seats_ec | totalseats | +------+------+----------+-------+----------+----------+----------+------------+ | 0 | 0 | 737 | 800 | 1 | 0 | 30 | 31 | | 1 | 0 | 747 | 900 | 3 | 20 | 100 | 123 |
Wichtige Operatoren und Funktionen auf einer Zeile:
Kleine Übung 1:
Nehmen Sie die flight-Tabelle und wandeln Sie DAY, MONTH und YEAR, die ja hier als einzelne Zahlvariablen vorliegen, in ein Stringformat der Form 'DD.MM.YYYY' um, also z.B. '01.04.2010'.
Datumsfunktionen
: Es ist ratsam, Datumsangaben im enstprechenden DATE-Format zu speichern. Dann kann man leicht Zeitdifferenzen ausrechnen, auf Monate und Jahre aggregieren usw. Das Casting erfolgt mit der str_to_date()-Funktion. Meist in der Form str_to_date('01.01.2010','%d.%m.%Y').
Kleine Übung 2:
Lassen Sie sich die Zeitdifferenz zwischen Abflug und Ist-Zeit in der flight-Tabelle ausgeben.
Neue Variablen bilden mit create table und drop table
Die Funktionen bieten sich an, neue nützliche Variablen in die Tabellen einzuführen, damit sie man nicht jedes Mal ad hoc berechnen muss, wenn man sie braucht. Dazu gibt es zwei mögliche Methoden. Die eine ist, mit einem create table as eine neue Tabelle zu erzeugen und im folgenden select die neue Variablen zu bilden. Dann können wir die alte Tabelle löschen. Das macht man mit dem Kommando
drop table <tabname> ; |
Dann können wir die neue Tabelle wieder in die alte umbennen. Das geht mit
alter table <tabname> rename to <newnname> ; |
Ein SQL zum Hinzufügen einer neuen Variablen "bookingtime" könnte also so ausschauen:
drop table flight2; create table flight2 as select a.*, str_to_date(concat(format(day,0),".",format(month,0),".",replace(format(year,0),',','')),'%d.%m.%Y') as bookingtime from flight a; select * from flight2 limit 10;
Dann schauen wir uns das Ergebnis an und prüfen, ob das Erzeugen von "bookingtime" gut geklappt hat. Anschliessend können wir die alte Tabelle droppen und die neue umbenennen:
drop table flight; alter table flight2 rename to flight2;
"Aggregieren" heisst "auf einen Haufen bringen", also "zusammenklumpen", "zusammenfassen". Im Fall Datenbanken heisst das, dass wir die Informationen mehrer Tabellenzeilen in eine einzige Zeile bringen. Das geht durch einfache statistische Funktionen: Anzahl, Summe, Mittelwert, Maximum, Minimum usw.
| count(varname) | Zählt die Anzahl Zeilen, in der die Variable "varname" nicht NULL ist. Bsp.: select count(day) from flight--> Zählt die Anzahl Flüge in der Tabelle, in der day nicht NULL ist. |
| count(*) | Zählt überhaupt die Anzahl Zeilen |
| sum() | Summe der Variablen über die ganze Tabelle |
| min(), max() | Minimum oder Maximum über die ganze Tabelle |
| avg() | Mittelwert über die ganze Tabelle |
| std() | Standardabweichung über die ganze Tabelle |
Beispiel:
select count(*) as flights, min(bookingtime), max(bookingtime) from flight +---------+------------------+------------------+ | flights | min(bookingtime) | max(bookingtime) | +---------+------------------+------------------+ | 1159 | 2010-01-01 | 2013-10-25 | +---------+------------------+------------------+ 1 row in set (0.00 sec) mysql> select sum(km)/1000,avg(km)/1000,min(km)/1000,max(km)/100 from jets; +--------------+---------------+--------------+-------------+ | sum(km)/1000 | avg(km)/1000 | min(km)/1000 | max(km)/100 | +--------------+---------------+--------------+-------------+ | 537000.0000 | 6102.27272727 | 600.0000 | 198000.0000 | +--------------+---------------+--------------+-------------+ 1 row in set (0.03 sec)
Die oben eingeführten Aggregierungsfunktionen sind zwar nett, machen aber meist über die ganze Tabelle keinen Sinn. Man kann aber die Tabelle in Teiltabellen gruppieren und sich dann die Aggregatswerte für jede Gruppe anzeigen lassen.
select varname1,sum(<varname2>) from <tabname> group by <varname1>Teilt die Tabelle in Teiltabellen nach Werten von varname1 auf. Achtung! varname1 muss natürlich eine kategoriale Variable sind (integer mit überschaubarer Zahl von Ausprägungen), sonst kommt am Ende eine Riesentabelle raus. sum() steht hier nur stellvertretend für eine beliebige Aggregatfunktion. |
Beispiel:
select year_c,format(avg(km),0) as km from jets group by year_c; +--------+------------+ | year_c | km | +--------+------------+ | 1998 | 7,200,000 | | 1999 | 13,200,000 | | 2000 | 6,857,143 | | 2001 | 7,061,538 | | 2002 | 8,000,000 | | 2003 | 7,466,667 | | 2004 | 6,300,000 | | 2005 | 7,285,714 | | 2006 | 5,600,000 | | 2007 | 4,114,286 | | 2008 | 2,400,000 | | 2009 | 1,542,857 | +--------+------------+ 12 rows in set (0.00 sec)
Hier sehen wir schon, wie die km-Leistung der Jets mit dem Alter ansteigt - unser erster kleiner Report.
select class,count(*) from booking group by class; +-------+----------+ | class | count(*) | +-------+----------+ | 1 | 2387 | | 2 | 8982 | | 3 | 6577 | +-------+----------+ 3 rows in set (0.11 sec)
Hier sehen wir die Anzahl der Buchungen nach Klasse.
In der vorhergehenden Beispielen wurden die Tabellen zufällig sortiert nach der Gruppierungsvariablen ausgegeben. Aber das passiert nicht immer - wenn wir nicht extra etwas dafür tun, nämlich die Ergebnistabelle sortieren. Das machen wir mit
select * from <tabname> order by <varname> ASC oder DESCASC sortiert dabei (numerisch oder alphabetisch) aufsteigend, DESC absteigend. |
select class,count(*) from booking group by class order by class desc; +-------+----------+ | class | count(*) | +-------+----------+ | 3 | 6577 | | 2 | 8982 | | 1 | 2387 | +-------+----------+ 3 rows in set (0.08 sec)sortiert die Reihenfolge also andersherum.
Wichtig:
"order by" orientiert sich an den Variablennamen Ergebnistabelle, also an denen, die im Zweifelsfall hinter dem "as" kommen. "group by" orientiert sich an den Variablennamen und Ausdrücken der Ausgangstabellen. Die Variablennamen der Ergebnistabelle kennt group by nicht!select class,count(*) as n from booking group by class order by n desc; +-------+------+ | class | n | +-------+------+ | 2 | 8982 | | 3 | 6577 | | 1 | 2387 | +-------+------+ 3 rows in set (0.05 sec)Das geht!
Manchmal hat man eine Gruppierungsvariable, aber sie ist nicht so gruppiert, wie man das gerne hätte. Dann kann man mit der Funktion case when die Kodierung modifzieren. Die Rekodierungsfunktion zusammen mit den Aggegatfunktionen ist ein sehr mächtiges Instrument!
case
when <condition1> then <value1>
when <condition2> then <value2>
else <value_else>
end as ...
Beispiel:
select
case
when x=0 then 'a'
when x=1 then 'b'
when x=2 then 'c'
else 'not defined'
end as x2 from mytab;
|
Im Beispiel wird die Variable x umkodiert. Wenn x den Wert 0 hat, soll x2 den Wert 'a' haben, wenn 1, dann den Wert 'b' usw. und falls der Wert nicht in der Liste links vom Komma ist, dann den Wert 'not defined'.
Kleine Übung:
Wir wollen in der Tabelle "Jets" den Flugzeughersteller unserer Flugzeuge identifizieren. Laut Tabelle "jettype" sind die Typen 0 bis 3 von Boeing, der Rest ist von Airbus. Welche Jets haben im Mittel die höhere km-Leistung? Die von Airbus oder die von Boeing?
Die Lösung könnte ziemlich einfach sein, wenn da nicht eine SQL-Falle wäre, die oben schon angesprochen wurde:
select
case
when typeid>=0 and typeid<=3 then 'Boeing'
else 'Airbus' end as Hersteller,
avg(km) as avg_km
from
jets
group by
case
when typeid>=0 and typeid<=3 then 'Boeing'
else 'Airbus' end
+------------+--------------+
| Hersteller | avg_km |
+------------+--------------+
| Airbus | 5290909.0909 |
| Boeing | 6913636.3636 |
+------------+--------------+
2 rows in set (0.01 sec)
Und siehe da: Die Boeings sind anscheinend schon mehr geflogen... Die SQL-Falle ist der group by-Ausdruck. Hier müssen wir nochmal den ganzen case when-Ausdruck reinkopieren. Das ist unübersichtlich und eine Syntax-Schwachstelle von SQL, aber im Moment ist es eben so.
Bis jetzt war das Ganze gut und schön, hatte aber noch nichts mit relationen Datenbanken zu tun - deren Witz ja darin besteht, Informationen aus verschiedenen Tabellen zusammenzutragen und zu aggregieren. Also fangen wir jetzt mal damit an.
Um die nächsten Beispiele übersichtlich zu halten, basteln wir uns mal ganz kleine Testtabellen. Das können wir mit reinem SQL machen, ohne externe Dateien erzeugen zu müssen.
create table testtab1 ( id int(5), name1 varchar(20) ); insert testtab1 values (1,'1Anton'); insert testtab1 values (2,'2Berta'); insert testtab1 values (NULL,'3empty'); create table testtab2 ( id int(5), name2 varchar(20) ); insert testtab2 values (1,'2Alfons'); insert testtab2 values (2,'2Bimbo'); insert testtab2 values (3,'3Caesar'); mysql> select * from testtab1; +------+--------+ | id | name1 | +------+--------+ | 1 | 1Anton | | 2 | 2Berta | | NULL | 3empty | +------+--------+ 3 rows in set (0.00 sec) mysql> select * from testtab2; +------+---------+ | id | name2 | +------+---------+ | 1 | 2Alfons | | 2 | 2Bimbo | | 3 | 3Caesar | +------+---------+ 3 rows in set (0.00 sec)
Und nun machen wir unseren ersten Join:
mysql> select * from testtab1,testtab2; +------+--------+------+---------+ | id | name1 | id | name2 | +------+--------+------+---------+ | 1 | 1Anton | 1 | 2Alfons | | 2 | 2Berta | 1 | 2Alfons | | NULL | 3empty | 1 | 2Alfons | | 1 | 1Anton | 2 | 2Bimbo | | 2 | 2Berta | 2 | 2Bimbo | | NULL | 3empty | 2 | 2Bimbo | | 1 | 1Anton | 3 | 3Caesar | | 2 | 2Berta | 3 | 3Caesar | | NULL | 3empty | 3 | 3Caesar | +------+--------+------+---------+ 9 rows in set (0.00 sec)
Öha. Jede Zeile der ersten Tabelle wurde mit jeder der zweiten Tabelle kombiniert. Macht eine Ergebnistabelle mit 3 x 3 = 9 Zeilen. Das nennt man ein kartesisches Produkt. (Kommt aus der Mengenlehre). Kartesische Produkte sind die Grundlage jedes Joins. Nennen wir zwei oder mehr Tabellen hinter dem FROM, dann bildet die DB-Engine erstmal (logisch) das kartesische Produkt. Und nun können wir immer noch entscheiden, welche Zeilen wir daraus haben wollen. Das ist die Zweistufenlogik von relationalen Joins.
Welche Zeilen wollen wir denn gerne haben? Na ja, vielleicht die, deren id übereinstimmt. Es könnte ja z.B. sein, dass name1 den Vornamen und name2 den Nachnamen speichert. OK, also:
mysql> select * from testtab1 a,testtab2 b where a.id=b.id; +------+--------+------+---------+ | id | name1 | id | name2 | +------+--------+------+---------+ | 1 | 1Anton | 1 | 2Alfons | | 2 | 2Berta | 2 | 2Bimbo | +------+--------+------+---------+ 2 rows in set (0.00 sec)
Wir haben hinter die Tabellennamen noch Buchstaben geschrieben, "a" und "b". Das nennt man Aliasse. Sie braucht man, um bei mehreren Tabellen, aber gleichen Variablennamen innerhalb der Tabellen zu kennzeichnen, welche Variable man denn eigentlich haben möchte - die aus Tabelle 1 oder die aus Tabelle 2.
Jetzt haben wir uns aus dem kartesischen Produkt zwei Zeilen rausgeholt. Zwei? wollten wir nicht eigentlich drei haben? Ja, aber wir haben dummerweise übersehen, dass wir für eine Zeile in Tabelle1 keine id haben. Dann wird diese Zeile durch das WHERE rausgefiltert, da das WHERE zwei gültige id's braucht, um seine Bedingung zu kontrollieren.
Wir nennen solch einen Join, bei der zu einer Key-Variablen zwingend in beiden Tabellen Zeilen da sein müssen, einen inner join. Für solch einen Inner-Join gibt's auch noch eine andere, neuere Syntax:
select * from tabname1 a innerjoin tabname 2 b on a.id=b.id |
Die neuere Syntax hat hier keine Vorteile, aber wir werden gleich auf Fälle kommen, wo sie sehr vorteilhaft ist.
Mittels der Aliasse können wir nun auch individuelle Variablenlisten zusammenstellen lassen, also z.B.
mysql> select a.id,a.name1 as firstname,b.name2 as lastname from testtab1 a,testtab2 b where a.id=b.id; +------+-----------+----------+ | id | firstname | lastname | +------+-----------+----------+ | 1 | 1Anton | 2Alfons | | 2 | 2Berta | 2Bimbo | +------+-----------+----------+ 2 rows in set (0.05 sec)
Übungen:
Lösungen:
| Lösung zu Übung 1 |
select a.id as id, a.year_c as baujahr, case when b.class=1 then '1-Kurzstrecke' when b.class=2 then '2-Mittelstrecke' when b.class=3 then '3-Langstrecke' end as reichweite, b.seats_fc+ b.seats_bc+ b.seats_ec as seats from jets a, jettype b where a.typeid=b.id order by baujahr,reichweite +------+---------+-----------------+-------+ | id | baujahr | reichweite | seats | +------+---------+-----------------+-------+ | 57 | 1998 | 1-Kurzstrecke | 140 | | 58 | 1998 | 1-Kurzstrecke | 140 | | 55 | 1998 | 1-Kurzstrecke | 140 | | 48 | 1999 | 1-Kurzstrecke | 140 | | 15 | 1999 | 3-Langstrecke | 400 | | 49 | 2000 | 1-Kurzstrecke | 140 | | 0 | 2000 | 1-Kurzstrecke | 130 | | 64 | 2000 | 1-Kurzstrecke | 140 | | 1 | 2000 | 1-Kurzstrecke | 130 | |
| Lösung zu Übung 2 |
select b.customerid, d1.cityname as wohnort, d2.cityname as startflugh, d3.cityname as zielflugh, c.bookingtime as flugdatum from customer a, booking b, flight c, airport d1, airport d2, airport d3 where b.customerid=a.id and b.flightid=c.id and a.cityid=d1.id and c.orig_id=d2.id and c.dest_id=d3.id and b.customerid in (0,100,200) order by customerid; +------------+------------+------------+-----------+------------+ | customerid | wohnort | startflugh | zielflugh | flugdatum | +------------+------------+------------+-----------+------------+ | 0 | Frankfurt | Frankfurt | München | 2011-10-01 | | 0 | Frankfurt | Frankfurt | München | 2010-01-01 | | 100 | Düsseldorf | Düsseldorf | München | 2012-05-10 | | 200 | Hamburg | Hamburg | München | 2011-05-01 | | 200 | Hamburg | Hamburg | München | 2011-10-10 | +------------+------------+------------+-----------+------------+ 5 rows in set (1 min 51.63 sec)Wir sehen hier, dass die Ausführung ziemlich lange gedauert hat. Das liegt an dem grossen Join, der gebildet werden musste. Hätten die Tabellen nicht nur max. 10.000 Zeilen, sondern 100.000 gehabt, hätten wir leicht Stunden gewartet. Das lässt sich allerdings stark beschleunigen. Mit Hilfe von s.g. Indizes und primary keys. Dazu werden wir noch kommen. |
| Lösung zu Übung 3 |
select b.constr as hersteller, b.type as typ, sum(seats_ec) as seats from jets a, jettype b where a.typeid=b.id group by b.constr, b.type; +------------+------+-------+ | hersteller | typ | seats | +------------+------+-------+ | Airbus | 320 | 2300 | | Airbus | 330 | 1140 | | Airbus | 340 | 1210 | | Airbus | 380 | 1400 | | Boeing | 737 | 1500 | | Boeing | 747 | 1680 | | Boeing | 757 | 2660 | | Boeing | 767 | 990 | +------------+------+-------+ 8 rows in set (0.05 sec) |
| Lösung zu Übung 4 |
select c.constr as hersteller, c.type as typ, count(*) as n from flight a, jets b, jettype c, airport d where a.jet_id=b.id and b.typeid=c.id and a.orig_id=d.id and year(a.bookingtime)=2010 and lower(d.cityname) like '%düsseldorf%' group by c.constr, c.type; +------------+------+-------------+----+ | hersteller | typ | ziel | n | +------------+------+-------------+----+ | Airbus | 320 | Warschau | 91 | | Airbus | 330 | Madrid | 7 | | Airbus | 340 | Delhi | 23 | | Airbus | 380 | Los Angeles | 5 | | Boeing | 737 | München | 59 | | Boeing | 747 | Shanghai | 9 | | Boeing | 757 | Madrid | 21 | | Boeing | 767 | Shanghai | 19 | +------------+------+-------------+----+ 8 rows in set (2.27 sec)Antwort: 59 - und die gehen alle nach München. |
Der Inner Join, wie oben beschrieben, wird sehr häufig benutzt, hat aber auch einen brisanten Stolperstrick eingebaut. Zu der id, die wir joinen, muss in beiden beteiligten Tabellen eine Information vorhanden sein, sonst fällt die ganze id raus. Denn das WHERE setzt immer voraus, dass hinter dem WHERE stehenden Variablen nicht NULL sind. Ist eine NULL, dann trifft die WHERE-Bedingung automatisch nicht zu und die Kombination fällt raus. Das gilt sogar dann, wenn wir "WHERE (b.id is null) or ... " schreiben. Die Bedingung ist paradoxerweise auch dann false, wenn b.id NULL ist.
Bei einem left outer-Join gilt: Nimm die Zeile aus dem Kreuzprodukt in die Ergebnistabelle mit rein, falls die id der linken Tabelle da ist. Setze dann die Variablen der rechten Tabelle im Ergebnis auf NULL.
select a.var1,b.var2 from tab1 a left outer join tab2 b where a.id=b.id; |
mysql> select a.name1,b.name2 from testtab1 a, testtab2 b where a.id=b.id; +--------+---------+ | name1 | name2 | +--------+---------+ | 1Anton | 2Alfons | | 2Berta | 2Bimbo | +--------+---------+ 2 rows in set (0.00 sec) mysql> /* Left outer join - neue Notation - empfohlene Variante: */ select a.name1,b.name2 from testtab1 a left outer join testtab2 b on b.id=a.id; +--------+---------+ | name1 | name2 | +--------+---------+ | 1Anton | 2Alfons | | 2Berta | 2Bimbo | | 3empty | NULL | +--------+---------+ 3 rows in set (0.00 sec)
Wir haben beim inner join die s.g. alte SQL-Notation (vor 1999) angegeben, einfach deshalb, weil sie auch heute noch die gebräuchlichste Notation für inner joins ist und weil bei ihr am einfachsten klar wird, was eigentlich passiert: Bildung der Kreuztabelle - Auswahl mittels WHERE. Beim left-join verwenden wir nun die neue SQL99-Notation. Obwohl es auch dafür eine alte Notation gäbe. Die ging so:
/* Left outer join - alte Notation - funktioniert mit MySQL nicht: */ select a.name1,b.name2 from testtab1 a, testtab2 b where b.id=a.id(+);Ich habe sie hier nur aus zwei Gründen erwähnt: Erstens, weil diese Notation noch mit grossen RDBMS wie SQL-Server oder Oracle 11 nicht nur funktioniert, sondern auch sehr intensiv von SQL-Programmierern benutzt wird - und zweitens weil dann der Übergang zur neuen Notation nicht ganz so unmotiviert ist.
Wir haben bei left-outer-Joins eigentlich an zwei Stellen where-Bedingungen. Neben der üblichen where-Stelle auch beim Join hinter dem Schlüsselwort ON.
mysql> select a.name1,b.name2 from testtab1 a left outer join testtab2 b on b.id=a.id and substr(name2,2,1)='B'; +--------+--------+ | name1 | name2 | +--------+--------+ | 1Anton | NULL | | 2Berta | 2Bimbo | | 3empty | NULL | +--------+--------+ 3 rows in set (0.00 sec)Wir sehen, dass die Bedingung hinter dem ON auf keinen Fall die Zahl der Zeilen einschränkt, sondern im Zweifelsfall nur ein NULL in der entsprechenden Variablen produziert. Anders, wenn wir das Ganze in die WHERE-Bedingung stellen:
mysql> select a.name1,b.name2 from testtab1 a left outer join testtab2 b on b.id=a.id where substr(name2,2,1)='B'; +--------+--------+ | name1 | name2 | +--------+--------+ | 2Berta | 2Bimbo | +--------+--------+ 1 row in set (0.00 sec)
Mit dem Schlüsselwort DISTINCT in einem Ausdruck hinter SELECT werden nur Zeilen berücksichtigt, die sich bezüglich des Ausdrucks unterscheiden.
distinct Variablenliste:
select distinct <a,b,c...> from mytab |
distinct innerhalb von Aggregatfunktionen:
select count(distinct <ausdruck>) from mytab |
So können wir relativ einfach herausfinden, welche verschiedenen Baujahre es bei unsern Fliegern gibt:
mysql> select distinct year_c from jets order by 1; +--------+ | year_c | +--------+ | 1998 | | 1999 | | 2000 | | 2001 | | 2002 | | 2003 | | 2004 | | 2005 | | 2006 | | 2007 | | 2008 | | 2009 | +--------+ 12 rows in set (0.00 sec)
Hätten wir natürlich auch hingekriegt, wenn wir irgendetwas über eine nach Baujahr gruppierten Tabelle gezählt hätten. Aber oft ist distinct sehr nützlich, um schlicht Zeilenduplikate zu vermeiden. Solche Zeilenduplikate sind etwas, was in SQL sehr leicht entstehen kann, wenn die Join-Bedingung mehr Zeilen zurücklässt, als man eigentlich wünscht.
select b.constr, case when a.km<=5000000 then 'new' else 'used' end as usage1, count(*) as n from jets a, jettype b where /* Hier simulieren wir einen ungenauen Join: */ abs(a.typeid-b.id)<=2 group by b.constr, case when a.km<=5000000 then 'new' else 'used' end +--------+--------+-----+ | constr | usage1 | n | +--------+--------+-----+ | Airbus | new | 75 | | Airbus | used | 106 | | Boeing | new | 75 | | Boeing | used | 129 | +--------+--------+-----+ 4 rows in set (0.00 sec)
OK, prima Ergebnis: Knapp die Hälfte unserer Flieger ist neu. Aber Moment mal - haben wir denn wirklich ca. 300 Flieger? Nachzählen hilft:
mysql> select count(*) from jets; +----------+ | count(*) | +----------+ | 88 | +----------+ 1 row in set (0.00 sec)
Nicht die Spur! Aber das hätten wir, auch ohne den Join auf Herz und Nieren zu prüfen, sicherer abfragen können:
select b.constr, case when a.km<=5000000 then 'new' else 'used' end as usage1, count(distinct a.id) as n from jets a, jettype b where /* Hier simulieren wir einen ungenauen Join: */ abs(a.typeid-b.id)<=2 group by b.constr, case when a.km<=5000000 then 'new' else 'used' end +--------+--------+----+ | constr | usage1 | n | +--------+--------+----+ | Airbus | new | 28 | | Airbus | used | 39 | | Boeing | new | 30 | | Boeing | used | 43 | +--------+--------+----+ 4 rows in set (0.14 sec)
Jetzt stimmt's. So eine Absicherung, dass keine Duplikate gezählt werden, ist meist sehr ratsam.
Übung:
Passagiere, die irgendeinen Flug in einer Boeing 747 gebucht haben, sind besondere Passagiere. Wieviel dieser Passagiere haben in 2011 von Frankfurt ausgehende Flüge (egal in welchem Flugzeug) gebucht und welches Jahresbudget haben sie dabei ausgegeben? Wir rechnen den geflogenen km mit 30 Cent.
Manche Queries sind so kompliziert, dass sie nicht in einem Schritt durchgeführt werden können. Das ist aber auch nicht schlimm. Dann machen wir's halt in mehreren Schritten. Dazu erzeugen wir eine temporäre Tabelle, die dann im zweiten Schritt wieder benutzt wird.
/* Hilfstabelle */ drop table temp1; create table temp1 as select distinct a.customerid from booking a, flight b, jets c, jettype d where a.flightid=b.id and b.jet_id=c.id and c.typeid=d.id and d.constr='Boeing' and d.type=747; /* Auswertung - diese Tabelle rechnet in dieser Form ca. 45 min. auf einem Netbook! */ select count(distinct c.customerid) as passagiere747_2011_frankf, sum(e.dist*0.3) as budget747_2011_frankf from temp1 c, airport d, origdest e, flight b, booking a where c.customerid=a.customerid and a.flightid=b.id and b.year=2011 and b.orig_id=d.id and d.cityname='Frankfurt' and b.orig_id=e.origid and b.dest_id=e.destid; drop table temp1 +---------------------------+-----------------------+ | passagiere747_2011_frankf | budget747_2011_frankf | +---------------------------+-----------------------+ | 269 | 1488392.1 | +---------------------------+-----------------------+ 1 row in set (43 min 15.91 sec)
D.h., wir haben 269 747-Passiere, die in 2011 1,5 Mio EUR bei unserer Airline lassen - auf allen Flügen, nicht nur auf 747-Flügen.
Wir werden später noch sehen, dass man die Tabellenzugriffe beschleunigen kann, z.B. indem man s.g. Indizes bildet und benutzt. Aber nun zu der Frage: Könnten wir das auch in einem SQL abhandeln? Ja, das geht. Und zwar mit einem s.g. Subselect. Hier schreiben wir an die Stelle hinter dem FROM, an der eigentlich ein Tabellenname (z.B. "temp1") kommen sollte, einen kompletten weiteren Select:
select <varliste1> from (select <id> from tabname1 where ....) a,tabname2 b where a.id=b.id |
Das sieht in unserem Fall dann so aus:
select count(distinct c.customerid) as passagiere747_2011_frankf, sum(e.dist*0.3) as budget747_2011_frankf from ( select distinct a.customerid from booking a, flight b, jets c, jettype d where a.flightid=b.id and b.jet_id=c.id and c.typeid=d.id and d.constr='Boeing' and d.type=747) c, airport d, origdest e, flight b, booking a where c.customerid=a.customerid and a.flightid=b.id and b.year=2011 and b.orig_id=d.id and d.cityname='Frankfurt' and b.orig_id=e.origid and b.dest_id=e.destid;
Die Frage ist, wann man mit einer expliziten Hilfstabelle und wann mit einem Subselect arbeiten sollte. Zumindest meine Erfahrung ist, dass man eher zur expliziten Hilfstabelle greifen sollte und nur in einem Fall, in dem man sich über den subselect sehr sehr sicher ist oder dieser sehr kurz läuft, diesen verwenden sollte. Meistens ist es ja doch so, dass man erstmal den Subselect in mehrfachem Anlauf ausprobieren muss und wenn man das dann immer mit dem Rahmen-Select zusammen abschiesst, wird das Debugging kompliziert und unschön. Explizite Hilfstabellen kann man sich anschauen und man kann sie hinterher wieder droppen.
Nun kommen wir noch zum letzten zentralen Element eines SQL's. Allerdings das, das ich z.B. am wenigsten benutze. Es ist nicht nur, wie Subselects auch, durch einen separaten Select gut ersetzbar, es kostet meist auch noch ziemlich Rechenzeit. HAVING ist eine Art WHERE-Clause, die wir nicht auf die Zwischen-Kreuztabelle loslassen, sondern auf die ERGEBNIStabelle.
select <groupvar> count(*) from <tabname> where <Bedingung> group by <groupvar> having count(*)>100 |
Wobei "count(*)>100" durch irgendeine andere Bedingung mit irgendeiner Aggregatfunktion (min, max, sum, avg, ...) ersetzt werden kann.
Übung:
Erstelle eine Tabelle mit allen Passagieren, die mehr als 5 Buchungen haben
| mysql> create table temp2 as
-> select
-> customerid,
-> count(*) as nbooking
-> from
-> booking
-> group by
-> customerid
-> having count(*)>5;
Query OK, 1624 rows affected (0.25 sec)
Records: 1624 Duplicates: 0 Warnings: 0
mysql> select * from temp2 limit 10;
+------------+----------+
| customerid | nbooking |
+------------+----------+
| 1 | 7 |
| 4 | 7 |
| 6 | 7 |
| 9 | 7 |
| 10 | 7 |
| 17 | 7 |
| 25 | 7 |
| 28 | 7 |
| 29 | 7 |
| 30 | 7 |
+------------+----------+
10 rows in set (0.00 sec)
Manchmal wollen wir das Ergebnis zweier Queries einfach hintereinanderhängen. Das klappt mit einem normalen Select schlecht, da er drauf abzielt, die Variablen verschiedener Tabellen in einer Teile nebeneinander zu stellen, aber nicht hintereinander.
select * from < tabname1> union all select * from < tabname2> |
Beispiel:
create table temp3 as select customerid, '0 Wenig Buchungen' as buch, count(*) as nbooking from booking group by customerid having count(*)<5 union all select customerid, '1 Viel Buchungen' as buch, count(*) as nbooking from booking group by customerid having count(*)>5; select buch,count(*) as n from temp3 group by buch^; Query OK, 6788 rows affected (0.38 sec) Records: 6788 Duplicates: 0 Warnings: 0 +-------------------+------+ | buch | n | +-------------------+------+ | 0 Wenig Buchungen | 5164 | | 1 Viel Buchungen | 1624 | +-------------------+------+ 2 rows in set (0.03 sec)
Im vorstehenden Beispiel hätten wir das Ergebnis natürlich auch anders erzielen können. Aber häufig wollen wir die gleiche Query auf ganz verschiedene Tabellen laufen lassen (allerdings müssen alle Tabellen genau den gleichen Aufbau haben!). Dann ist union all unverzichtbar.
Das einfach "union" wird man seltener brauchen. Es hängt die Zeilen der Ergebnistabellen nicht nur einfach hintereinander, sondern macht auch noch einen Vergleich und lässt nur die "distinct"-Zeilen stehen, also die Zeilen, die nicht doppelt vorkommen. Vorsicht Rechenzeit! union's (ohne all) sind kostenintensiv!
|
Eigentlich doch ganz easy, oder? ...;-) Keine Sorge: Die Wissenschaft, gute Queries zu schreiben, wird selbst unter Profis sehr unterschiedlich gut beherrscht. Für den Laien reicht es meistens, die Grundstruktur SELECT FROM WHERE und in manchen Fällen noch die Aggregatform SELECT FROM WHERE GROUP BY einigermassen zu durchschauen.
Der SELECT-Befehl ist der bei weitestem mächtigste und komplizierteste Befehl von SQL. Es gibt aber noch eine ganze Reihe anderer Befehle, die man oft braucht.
| Befehl | Bedeutung | Syntax und Beispiel |
| update | Verändert Werte in einer Tabelle | update <tabname> set <var1>=<value> where <Bedingung> Beispiel: update jettype set velocity=855 where constr='Boeing' and type=737; mysql> select * from jettype; +------+--------+------+----------+-------+----------+----------+----------+ | id | constr | type | velocity | class | seats_fc | seats_bc | seats_ec | +------+--------+------+----------+-------+----------+----------+----------+ | 0 | Boeing | 737 | 855 | 1 | 0 | 30 | 100 | |
| insert | Fügt neue Zeilen in eine Tabelle ein.$
Syntax1 eignet sich, um einzelne Werte per SQL einzufügen.$
Syntax2 eignet sich, um eine SELECT-Ergebnistabelle in eine andere Tabelle einzufügen. | Syntax1: insert into <tabname> values (<valuelist>); Syntax2: insert into <tabname> select .... Beispiel: mysql> insert into jets values (88,0,2010,100000); Query OK, 1 row affected (0.06 sec) mysql> select * from jets where id>80; +------+--------+--------+---------+ | id | typeid | year_c | km | +------+--------+--------+---------+ | 81 | 6 | 2005 | 9000000 | | 82 | 6 | 2006 | 7200000 | | 83 | 6 | 2007 | 5400000 | | 84 | 7 | 2009 | 1800000 | | 85 | 7 | 2009 | 1800000 | | 86 | 7 | 2009 | 1800000 | | 87 | 7 | 2009 | 1800000 | | 88 | 0 | 2010 | 100000 | +------+--------+--------+---------+ 8 rows in set (0.03 sec) mysql> insert into jets select id+100 as id,typeid,year_c,km from jets where id<10; Query OK, 10 rows affected (0.09 sec) Records: 10 Duplicates: 0 Warnings: 0 mysql> select * from jets where id>80; +------+--------+--------+---------+ | id | typeid | year_c | km | +------+--------+--------+---------+ | 81 | 6 | 2005 | 9000000 | | 82 | 6 | 2006 | 7200000 | | 83 | 6 | 2007 | 5400000 | | 84 | 7 | 2009 | 1800000 | | 85 | 7 | 2009 | 1800000 | | 86 | 7 | 2009 | 1800000 | | 87 | 7 | 2009 | 1800000 | | 88 | 0 | 2010 | 100000 | | 100 | 0 | 2000 | 6000000 | | 101 | 0 | 2000 | 6000000 | | 102 | 0 | 2000 | 6000000 | | 103 | 0 | 2001 | 5400000 | | 104 | 0 | 2001 | 5400000 | | 105 | 0 | 2001 | 5400000 | | 106 | 0 | 2001 | 5400000 | | 107 | 0 | 2001 | 5400000 | | 108 | 0 | 2001 | 5400000 | | 109 | 0 | 2002 | 4800000 | +------+--------+--------+---------+ 18 rows in set (0.00 sec) |
| delete | Lösche Zeilen | Syntax: delete from <tabname> where ... Beispiel: mysql> delete from jets where id>87; Query OK, 11 rows affected (0.05 sec) mysql> select * from jets where id>80; +------+--------+--------+---------+ | id | typeid | year_c | km | +------+--------+--------+---------+ | 81 | 6 | 2005 | 9000000 | | 82 | 6 | 2006 | 7200000 | | 83 | 6 | 2007 | 5400000 | | 84 | 7 | 2009 | 1800000 | | 85 | 7 | 2009 | 1800000 | | 86 | 7 | 2009 | 1800000 | | 87 | 7 | 2009 | 1800000 | +------+--------+--------+---------+ 7 rows in set (0.00 sec) |
| alter table drop column | Lösche Spalte | Syntax: alter table <tabname> drop column <varname> Beispiel: mysql> create table temp4 as select * from jets where id<10; Query OK, 10 rows affected (0.11 sec) Records: 10 Duplicates: 0 Warnings: 0 mysql> alter table temp4 drop column km; Query OK, 10 rows affected (0.14 sec) Records: 10 Duplicates: 0 Warnings: 0 mysql> select * from temp4; +------+--------+--------+ | id | typeid | year_c | +------+--------+--------+ | 0 | 0 | 2000 | | 1 | 0 | 2000 | | 2 | 0 | 2000 | | 3 | 0 | 2001 | | 4 | 0 | 2001 | | 5 | 0 | 2001 | | 6 | 0 | 2001 | | 7 | 0 | 2001 | | 8 | 0 | 2001 | | 9 | 0 | 2002 | +------+--------+--------+ 10 rows in set (0.00 sec) |
| alter table rename to | Tabelle umbenennen | Syntax: alter table <tabname> rename to <newtabname> ; |
Ein grosser Teil der Kunst der Datenbanksystemadministration (das ist immerhin ein Beruf!) besteht darin, das System schneller zu machen. Dazu gibt es viele Mittel. Die vielleicht drei wichtigsten sind:
Verwendung von Primary Keys
Man muss nicht zu grossen Mitteln greifen, um eine einigermassen schnelle Abfrage zu bekommen. Eine gute Sache ist die Verwendung von Primary Keys. Fügen wir bei der Deklaration der Tabelle dieses Schlüsselwort in die Zeile für den primären Schlüssel ein:
drop table flight; create table flight ( id int(8) primary key, day int(5), month int(5), year int(5), weekday int(5), hour int(5), orig_id int(5), dest_id int(5), jet_id int(5)); load data local infile 'Y:/mysql/flight.dat' into table flight lines terminated by '\r\n'; drop table booking; create table booking ( id int(8) primary key, customerid int(8), flightid int(5), class int(5)); load data local infile 'Y:/mysql/booking.dat' into table booking lines terminated by '\r\n';
Anschliessend benötigt eine Abfrage wie
drop table temp1; create table temp1 as select distinct a.customerid from booking a, flight b, jets c, jettype d where a.flightid=b.id and b.jet_id=c.id and c.typeid=d.id and d.constr='Boeing' and d.type=747;
nur noch ein Drittel der Zeit. MySQL bildet über die primary key-Tabellen automatisch einen Index.
Der 2x2-Trick
Am meisten gewinnt man bei unindizierten Joins, wenn man einen Join von 5 Tabellen in 4 Abfragen mit einem Join von nur jweils 2 Tabellen zerlegt. Das bringt fast immer etwas. Ein Join mit 5 Tabellen ist, falls alle N Zeilen haben, ein NxNxNxNxN-Problem. Zerlegt in 4 2er-Abfragen ist es nur noch ein 4xNxN-Problem.
Die Möglichkeiten mit MySQL werden natürlich erst dann richtig ausgefahren, wenn wir die Mächtigkeit einer nativen Programmiersprache wie Freebasic mit einem RDBMS und SQL verbinden. Let's start.
Als aller erstes müssen wir dafür sorgen, dass Freebasic die libmysql.dll findet. Sie findet sich im MySQL-Programmverzeichnis unter lib (oder lib/opt). Aber da findet sie Freebasic nicht. Einfach nach windows\system32 kopieren.
Nun zur Interaktion mit Freebasic selbst, d.h. zu den notwendigen Befehlen der MySQL-API, um einen SQL abzuschicken und das Ergebnis nach Freebasic zu bekommen.
| Befehl | Bedeutung |
| db = mysql_init( NULL ) | Initialisiere den API-Zugriff. db ist vom Typ MYSQL ptr und ein Handle auf das verwendete DB-System. |
| result=mysql_real_connect( db, NULL, "root", "a", dbname, MYSQL_PORT, NULL, 0 ) | Verbindet mit einer Datenbank dbname unter dem User "root", dem Passwort "a". Das zweite Argument wäre eigentlich die Adresse, (z.B. eine IP-Adresse), wo das Programm das RDBMS findet. Bei NULL sucht es auf dem eigenen Rechner (unter "localhost"). result ist vom Typ Integer und ist ungleich null, wenn alles geklappt hat. |
| result=mysql_select_db( db, dbname ) | Auswahl der Datenbank. (Entspricht "use dbname;" innerhalb des MYSQL-Clients). |
| error=mysql_query(db,sql_query_string) | Absetzen eines SQL-Befehls. error ist vom Typ Integer. Ist error=0, ist alles in Ordnung. sql_query_string kann eine String-Konstante sein, also z.B. "select * from jettype;" oder eine zstring-Variable. |
| restab=mysql_store_result(db) | Veranlasst die API, das Ergebnis der letzten Query in einer Struktur vom Typ MYSQL_RES ptr abzuspeichern. |
| nrow=mysql_num_rows(restab) | Die Frage ist, wie lang die Ergebnistabelle denn geworden ist. mysql_num_rows(restab) gibt Antwort darauf. nrow ist vom Typ integer. (Tabellen > 2 Mrd. Zeilen sind tunlichst zu vermeiden...) |
| ncol = mysql_num_fields(restab) | Meistens werden wir wissen, wieviele Spalten unsere Tabelle hat. Aber sicher ist sicher. ncol ist vom Typ integer. |
| row = mysql_fetch_row( restab ) | Hier bekommen wir nun endlich die Daten der nächsten Zeile. row[] ist ein Array von zstrings, also vom Typ zstring ptr ptr. |
| mysql_free_result(restab) | Gibt alle Resourcen der Ergebnistabelle frei. |
| mysql_close( db ) | Schliesst den RDBMS-Zugriff. |
Das Ganze können wir in einem zusammenfassenden kleinen Beispielprogramm betrachten, in dem wir den Inhalt der jettype-Tabelle auf dem Bildschirm ausgeben:
#INCLUDE ONCE "mysql\mysql.bi"
#define NULL 0
SUB connect1
DIM db AS MYSQL PTR
'Initialisiere die API. db zeigt auf das MySQL-System.
db = mysql_init( NULL )
DIM dbname AS STRING
dbname = "test"
'Verbinde dich mit dem MySQL-System.
'2. Argument: Adresse des Servers. NULL = Localhost.
IF( mysql_real_connect( db, NULL, "root", "a", dbname, MYSQL_PORT, NULL, 0 ) = 0 ) THEN
PRINT "Can't connect to the mysql server on port"; MYSQL_PORT
mysql_close( db )
END 1
END IF
'Waehle die Datenbank aus.
IF( mysql_select_db( db, dbname ) ) THEN
PRINT "Can't select the "; dbname; color=teal>" database !"
mysql_close( db )
END 1
END IF
'Gib ein Informationen zum RDBMS-Host und zur Datenbank aus.
PRINT "Client info: "; *mysql_get_client_info()
PRINT "Host info: "; *mysql_get_host_info( db )
PRINT "Server info: "; *mysql_get_server_info( db )
'Schicke ein SQL los.
DIM res AS INTEGER
res=mysql_query(db,"select * from jettype;")
'Deklariere einen Zeiger auf die Ergebnistabelle
DIM restab AS mysql_res PTR
IF res=0 THEN
'Hole den Zeiger auf die Ergebnistabelle
restab=mysql_store_result(db)
IF restab>0 THEN
'Wieviel Zeilen hat die Ergebnistabelle?
nrow=mysql_num_rows(restab)
? nrow
IF nrow>0 THEN
DIM AS INTEGER i,j,ncol
DIM AS mysql_row row
'Wieviel Spalten hat die Ergebnistabelle?
ncol = mysql_num_fields(restab)
? ncol
'Lies die Ergebnistabelle aus.
FOR i=0 TO nrow-1
'Hole die naechste Zeile der Ergebnistabelle
row = mysql_fetch_row( restab )
? i,
'Gib alle Spalten der aktuellen Zeile auf dem Bildschirm aus.
FOR j=0 TO ncol-1
DIM s AS STRING: s=*row[j]
? s;" - ";
NEXT j
PRINT
NEXT i
SLEEP
END IF
END IF
'Mache den Speicherplatz der Ergebnistabelle frei.
mysql_free_result(restab)
ELSE
? "sql query error"
END IF
'Schliesse den API-Zugang.
mysql_close( db )
END 0
END SUB
connect1
Unsere bisherige Übungsdatenbank soll nun ein Client-Programm erhalten, mit dem wir
können.
Unsere bisherigen (Simulations-)daten sind auf Kundenseite noch ein bisschen ärmlich: Ausser der Cityid haben wir keine Information. Daher wollen wir das Ganze noch etwas aufpeppen und uns Kundenadressdaten "beschaffen".
Anbei finden Sie eine Tabelle mit je 31 Vornamen, Nachnamen und Strassennamen (alle frei erfunden und zusammengestellt).
Andreas Müller Appenzellerstr. Anton Meier Graubündnerstr. Bettina Maier Neurotherstr. Barbara Schneider Heinestr. Anna Becker Goethestr. Christoph Süsskind Hessestr. Christian Steiger Ahornweg Christine Ortlieb Eichstr. Christina Orner Buchenstr. Denis Shiu Rosenweg Dora Pfister Lilienstr. Dorothee Anders Max-Planck-Str. Eduard Rohrer Ludwigstr. Eveline Reusch Friedrichstr. Eva Redwig Ottostr. Esther Ludwig Wilhelmstr. Felix Lehmann Marxstr. Markus Weber Georgenstr. Lukas Schmidt Hansastr. Johannes Schmalz Pariser Platz Maximilian Jäger Schatzmüllerbogen Maria Berner Arminstr. Vivienne Bohrmüller Brentanostr. Julia Seemüller Eichendorffstr. Sonja Wendel Schubertstr. Michael Schoner Mozartstr. Olaf Hartlieb Beethovenstr. Marianne Wetzel Bach-Allee Inga Ungerer Nürnbergerstr. Georg Eichbaum Frankfurterstr. Alwin Schleissheimer Stuttgarterstr.
Falls Sie beim Erstellen der Testdatenbank die Spur verloren haben, können Sie sich hier in Ihrem MySQL die Datenbank komplett reinladen.
Dann werden alle Tabellen in die Datenbank "test" geladen.
Die Aufgabe klingt popelig einfach: Schreiben Sie ein einfaches Programm, mit der ein Reisebüromitarbeiter neue Kunden anlegen und neue Buchungen eingeben kann (und Kundendaten und Buchungsdaten modifizieren kann).
Wie Sie das optisch umsetzen, ob mit einer einfachen textorientierten Oberfläche oder richtig superschick mit Designgrafik, oder sowas dazwischen mit GTK+, aber dann schon Buttons und Menüs usw., das bleibt Ihnen überlassen.
Wenn Sie allerdings schon mal Ihre eigenen Flugreisen im Internet gebucht haben, dann wissen Sie, dass solche Eingabeprogrammme nicht nur stumpfsinnig Daten entgegennehmen und dann anzeigen. Sie bieten Auswahlmenüs an und in diesen Auswahlmenüs steht im intelligenten Fall nur das, was respektive der anderen schon getroffenen Auswahlen sinnvoll und auch verfügbar ist. Wir wollen also ein intelligentes Eingabeprogramm haben!
Hier zur Leistungsbeschreibung:
Das ist die Mini-Ausführung einer vergleichbaren Leistungsbeschreibung in der realen Business-Welt.
Also, dann an den Start! Viel Spass!
Meine Lösung hat rund 1.700 Zeilen und 45K Code. Das ist die kompakteste Lösung, die mir eingefallen ist, die die Funktionalität von oben implementiert und gut bedienbar ist. So kompakt konnte sie nur mit einer gewissen Neigung zur Faulheit und einer daraus resultierenden Ausnutzung von OOP-Prinzipien werden, soweit das der derzeitige FB-Standard (0.20b) zulässt. Es lohnt sich vielleicht, das Klassenmodell mal kurz anzuschauen.
Selbst ein Netzwerkspiel programmieren? Möglicherweise eine Internetspiel? Und das mit Freebasic? Überhaupt kein Problem mehr.
Ein einfaches LAN-Spiel hätten wir auch vorher schon zustandegebracht. Die Kommunikation hätte dann über Files stattgefunden. Aber das ist langsam und fehlerträchtig. MySQL bringt wie jede solide Datenbank einen eigenen Netzwerk-Zugang mit. Läuft es als Service und ist es entsprechend konfiguriert, dann kann man sich unter Angabe des Hostnames, Benutzer und Passwort ins MySQL eines entfernten Systems einloggen. Da MySQL fast immer über TCPIP kommuniziert (falls es nicht in exotischer Weise anders konfiguriert ist), klappt das auch mit MySQL-Datenbanken von Providern. D.h., wir können MySQL als Netzwerkserver für ein Internetspiel nutzen. Oder unser eigenes Spezial-Chat-Programm damit bauen etc.
Mit ein paar Handgriffen kann man die laufende Instanz von MySQL netzwerkfähig machen. Dazu startet man den "MySQL Server Instance Config Wizard" (unter Windows aus dem Startmenü). Dort klickt man sich bis zu den Networking-Options durch. Das sollte so aussehen:
Wichtig ist dann noch, dass zwei Dialogfenster weiter "Enable root access from remote machines" aktiviert ist. Da wir nur unwichtige und temporäre Spieldaten zu speichern gedenken, ist das nicht weiters bedenklich.
Und fertig. Testen können Sie die Sache natürlich nur, wenn Sie ein LAN und mind. zwei PC's zur Verfügung haben. Nennen wir sie hier "Client" und "Server". Der Hostname vom Server hiesse "omaserver". Auf dem Server sollte MySQL gestartet sein und als Service laufen. Starten Sie dann auf dem Client von cmd aus mit
mysql -h omaserver -u root -p
Falls der Server nicht gefunden werden sollte, prüfen Sie erstmal, ob der Servercomputer selbst überhaupt erreichbar ist. Unter Windows geht das mit
net view \\omaserver
Wenn Nein, dann ist klar, wo das Problem nicht liegt: Bei MySQL. Wenn Ja, dann checken Sie nochmals mit Config Wizard die Einstellungen, insbesondere Port, max. Anzahl Verbindungen und remote access by root.
wird es einen kleinen Exkurs, so ne Art Jump-and-Run-Spiel über's Netz, geben, in dem die bisherigen Techniken OOP, GTK und MySQL zusammengeführt werden.