SQL von Kopf bis Fuß
Werbung
SQL von Kopf bis Fuß Wäre es nicht wunderbar, wenn es ein Buch gäbe, das SQL so erklärt, dass ich mir nicht wünschen würde, eine einsame Insel im Pazifik zu bewohnen, auf der es keine Datenbanken gibt? Aber wahrscheinlich ist das nichts als Träumerei... Lynn Beighley Deutsche Übersetzung von Lars Schulten ULB Darmstadt O'REILLY 16697974 Beijing • Cambridge • Köln • Paris • Sebastopol • Taipei • Tokyo Inhaltsverzeichnis Der Inhalt (in der Dbersicht) Einfuhrung 1 Daten und Tabellen: Ein Ortfiir alle Dinge 2 Die SELECT-Anweisung: Geschenkter Datenabruf 3 DELETE und UPDATE: Veränderung tut gut 4 Cleveres Tabellen-Design: Warum normal sein? 5 ALTER: Die Vergangenheit umschreiben 6 Fortgeschrittenes SELECT: Ihre Daten mit neuen Augen sehen xxiii 1 53 119 159 197 235 7 Eine Datenbank, viele Tabellen: Aus Ihrer Tabelle herauswachsen 8 Joins und Mehr-Tabellen-Operationen: Vereinigen wir uns! 281 9 Unterabfragen: Abfragen in Abfragen 10 Outer-Joins, Self-Joins und Unions: Neue Manöver 11 Constraints, Views und Transaktionen: Viele Köche verderben die Datenbank 455 12 Sicherheit: Ihre Anlagen schützen A Die Top Ten der Themen, die wir nicht behandelt haben 343 379 417 493 525 B Probieren Sie es selbst 543 C All Ihre SQL-Werkzeuge 551 Index 559 Der Inhalt (jetzt awsMirM) Einführung I h r G e h i r n U n d S Q L . Sie versuchen, etwas zu lernen, und Ihr Hirn tut sein Bestes, damit das Gelernte nicht hängen bleibt. Es denkt nämlich: »Wir sollten lieber ordentlich Platz für wichtigere Dinge lassen, z.B. für das Wissen darüber, welche Tiere einem gefährlich werden könnten oder dass es eine ganz schlechte Idee ist, nackt Snowboard zu fahren.« Tja, wie schaffen wir es nun, Ihr Gehirn davon zu überzeugen, dass Ihr Leben davon abhängt, etwas über SQL zu wissen? Für wen ist dieses Buch? xxvi Wir wissen, was Ihr Gehirn denkt xxv Metakognition xxvii Machen Sie sich Ihr Hirn Untertan xxix lies mich xxx Die Fachgutachter xxxii Danksagungen xxxiii VII Inhaltsverzeichnis Dutten und Tabellen Ein Ort für alle Dinge 1 Hassen Sie es auch, Dinge zu verlieren? Egal ob es ihre Autoschlüssel, der 25%-Gutschein von Globetrotter oder Ihre Anwendungsdaten sind: Es gibt nichts Schlimmeres, als nicht dazu in der Lage zu sein, das, was man braucht, zur Hand zu haben ... wenn man es braucht. Und wenn es um Ihre Anwendungen geht, gibt es keinen besseren Ort, Ihre wichtigen Informationen zu speichern, als eine Tabelle. Blättern Sie also um, treten Sie ein und machen Sie einen Spaziergang durch die Welt der relationalen Datenbanken. j Ihre Datenbank durch die Röntgenbrille viii . Daten definieren 2 Betrachten Sie Daten in Kategorien 7 Was in einer Datenbank steckt 8 Ihre Datenbank durch die Röntgenbrille 10 Datenbanken enthalten verwandte Daten 12 Tabellen unter der Lupe 13 Übernehmen Sie das Kommando! 17 Die Tabelle einrichten: die CREATE TABLE-Anweisung 19 Eine kompliziertere Tabelle erstellen 20 Wie einfach das Schreiben von SQL doch ist 21 Endlich die Tabelle meine_kontakte erstellen 22 Ihre Tabelle ist fertig 23 Machen Sie Bekanntschaft mit ein paar Datentypen 24 Eine Beschreibung Ihrer Tabelle 28 Eine Datenbank oder Tabelle kann nicht neu erstellt werden! 30 Raus mit der alten Tabelle, rein mit der neuen 32 Daten einfügen mit der INSERT-Anweisung 34 Die INSERTjAnweisung erstellen 37 Variationen zu einer INSERT-Anweisung 41 Spalten ohne Werte 42 Blicken Sie mit der SELECT-Anweisung in Ihre Tabelle 43 SQL im Gespräch: Geständnis einer NULL 44 Ihre innere NULL beherrschen 45 NOT NULL erscheint in DESC 47 Die Lücken mit DEFAULT füllen 48 Ihr SQL-Werkzeugkasten 50 Inhaltsverzeichnis Die SELECT-AnWefsimg 2 Geschenkter Datenabruf Ist Geben wirklich besser denn Nehmen? Bei Datenbanken ist es nicht unwahrscheinlich, dass Sie genauso oft Daten abrufen wie einfügen müssen. An dieser Stelle betritt dieses Kapitel die Bühne: Sie treffen die mächtige SELECT-Anweisung und lernen, wie man Zugang zu den wichtigen Informationen erhält, die Sie in Ihre Tabellen gesteckt haben. Sie werden sogar lernen, wie man mit WHERE, AND und OR Daten selektiv abruft und so vermeidet, Daten anzuzeigen, die man nicht braucht. , . Ein Date oder kein Date? 54 Ein besseres SELECT 57 Was ist dieses *? 58 Wie Sie Ihre Datentypen abfragen 64 Mehr Interpunktionsprobleme 65 Ungebundene Anführungszeichen 66 Einfache Anführungszeichen sind Sonderzeichen 67 Daten mit einfachen Anführungszeichen einfügen 68 Bestimmte Spalten auswählen, um das Ergebnis einzugrenzen 73 Schnellere Ergebnisse mit ausgewählten Spalten 73 Ihre Abfragen kombinieren 80 Numerische Werte finden 83 Vergleichsoperatoren Mit Vergleichsoperatoren numerische Daten Mit Vergleichsoperatoren Textdaten einfangen 86 finden 88 91 Sein ODER nicht sein 93 Der Unterschied zwischen AND und OR 96 NULLen finden mit IS NULL 99 Zeit sparen mit einem Schlüsselwort: LIKE 101 Der Joker sticht 101 Bereiche wählen mit AND und Vergleichsoperatoren 105 Aber es gibt einen besseren Weg 106 Nach dem Date ist man entweder IN ... 109 ...oder NOT IN 110 Mehr NOT 111 Ihr SQL-Werkzeugkasten 116 IX Inhaltsverzeichnis 3 DELETE und UPDATE Veränderung tut gut Sie ändern häufig Ihre Meinung? Das ist ab jetzt in O r d n u n g ! Mit den Befehlen, die Sie im Folgenden lernen werden - DELETE und UPDATE - , sind Sie nicht mehr an Entscheidungen gebunden, die Sie vor sechs Monaten gefällt haben, als Sie die Daten darüber eingefügt hatten, dass die Dauerwelle demnächst wieder in sei. Mit UPDATE können Sie Daten ändern, und mit DELETE werden Sie Daten los, die Sie nicht mehr brauchen. Aber wir geben Ihnen nicht nur die Werkzeuge. In diesem Kapitel lernen Sie auch, wie Sie gezielt mit Ihren neuen Kräften umgehen und vermeiden, Daten zu vernichten, die Sie noch brauchen. Clowns sind gruselig Clowns festhalten Die Clowns sind unterwegs Wie Ihre Clown-Daten eingefügt werden Bonzo, wir haben ein Problem Mit DELETE einen Datensatz loswerden Unsere neue DELETE-Anweisung verwenden DELETE-Regeln Der INSERT-DELETE-Doppelschritt Passen Sie bei Ihren DELETEs auf Der Ärger mit ungenauen DELETEs Daten ändern mit UPDATE UPDATE-Regeln UPDATE ist das neue INSERT/DELETE UPDATE im Einsatz Die Bewegungen der Clowns aktualisieren AktualisiererfSie Ihre Preise Wir brauchen nur ein UPDATE Ihr SQL-Werkzeugkasten 120 121 122 126 128 129 131 132 135 140 144 146 147 148 149 150 154 156 158 Inhaltsverzeichnis CleVeres Tabellen-Design 4 Warum normal sein? Sie haben Tabellen erzeugt, ohne ihnen viele Gedanken ZU S c h e n k e n . Das war in Ordnung. Sie haben funktioniert. Sie können auf ihnen SELECTs, INSERTs, DELETEs und UPDATES ausführen. Aber wenn Ihre Datenmenge wächst, beginnen Sie, auf Dinge zu stoßen, bei denen Sie sich wünschten, Sie hätte sie anders gemacht, damit jetzt Ihre WHERE-Klauseln einfacher wären. Und dazu müssen Sie Ihre Tabellen normal machen. Zwei fischige Tabellen Bei einer Tabelle geht es um Beziehungen Atomare Daten Atomare Daten und Ihre Tabellen Regeln für atomare Daten Gründe, normal zu sein Die Vorteile normalisierter Tabellen Clowns sind nicht normal Auf halbem Weg zu INF PRIMÄRSCHLÜSSEL-Regeln Sich der NORMALITÄT nähern Gregors Tabelle in Ordnung bringen Unsere alte CREATE TABLE-Anweisung Zeig mir die T£»«le Zeitsparende Anweisung CREATE TABLE mit einem PRIMARY KEY 1, 2, 3 ... und alles automatisch Einer Tabelle einen PRIMARY KEY hinzufügen n Moment. Ich habe eine Tabelle, die vol Daten ist. Sie können nicht ernsthaft erwarti ich wie in Kapitel 1 den Befehl bROP TABLE c und das ganze Zeug dann neu eintippe, nur ur Primärschlüssel für jeden Datensatz zu erzeugen | ALTER TÄBLE und ein neuer PRIMARY KEY )lhr SQL-Werkzeugkasten 160 164 168 170 171 174 175 176 177 178 181 182 183 184 185 186 188 192 193 194 Inhaltsverzeichnis ALTER 5 Die Vergangenheit umschreiben Haben Sie sich jemals gewünscht, die Fehler der Vergangenheit korrigieren ZU können? Jetzt haben Sie Gelegenheit dazu. Mit der ALTER-Anweisung können Sie all die Lektionen, die Sie gelernt haben, auf Tabellen zur Anwendung bringen, die Sie vor Tagen, Monaten oder sogar Jahren entworfen haben. Besser noch: Sie können es sogar tun, ohne dass das Einfluss auf Ihre Daten hat. Wenn Sie mit dem Folgenden durch sind, wissen Sie, was normal wirklich bedeutet, und können es auf alle Ihre Tabellen, alte und neue, anwenden. Wir müssen Änderungen vornehmen Tabellenveränderungen Extre m-Tabellenumbau Die Tabelle umbenennen Wir müssen einige Pläne machen Ihre Spalten umfunktionieren Strukturelle Veränderungen ALTER und CHANGE Zwei Spalten mit nur einer SQL-Anweisung ändern Schnell! Löschen Sie diese Spalte Ein genauer Blick auf die nichtatomare Spalte wohnort Nach Mustern suchen Ein paar praktische String-Funktionen st an der Zeit, Ih müde, alte Gebrauchtwagen-Tabelle in einen Kunden-Magneten zu verwandeln und sie auf ein Tabellenniveau zu heben, von dem Sie nicht einmal geträumt haben. XII Eine neue Spalte mit einer aktuellen füllen Wie unsere UPDATE/SET-Kombo funktioniert Ihr SQL-Werkzeugkasten 198 203 204 205 207 208 209 210 211 215 222 223 224 229 230 232 Inhaltsverzeichnis Fortgescirittenes SELECT 6 Ihre Daten mit neuen Augen sehen Es ist an der Zeit, Ihrem Werkzeugkasten etwas Finesse hinzuz u f ü g e n . Sie wissen bereits, wie man mit SELECT Daten auswählt und WHERE-Klauseln einsetzt. Aber manchmal benötigt man mehr Genauigkeit, als sie SELECT und WHERE bieten. In diesem Kapitel werden Sie lernen, wie Sie Ihre Daten ordnen und gruppieren und wie Sie auf Ihren Ergebnissen mathematische Operationen durchführen. Die Videothek Datenhausen baut um Probleme mit unseren aktuellen Tabellen Die bestehenden Daten zuordnen Die neue Spalte füllen UPDATE mit einem CASE-Ausdruck Es scheint, als hätten wir ein Problem Tabellen können unordentlich werden Die ausgewählten Daten sortieren Probieren Sie ein kleines ORDER BY Eine einzelne Spalte ordnen ORDER mit zwei Spalten VIDEOTHEK DATENHAUSEN ORDER mit mehreren Spalten Eine sortierte film_tabelle Die Reihenfolge mit DESC umkehren Das Pfadfinderinnen-Keksverkaufsproblem SUM kann für uns addieren Mit GROUP BY alle auf einmal addieren AVG mit GROUP BY MIN und MAX Mit COUNT die Tage zählen SELECT DISTINCT Die Anzahl an Ergebnissen limitieren Auf nur den zweiten Platz eingrenzen Ihr SQL-Werkzeugkasten 236 237 238 239 242 244 249 250 253 254 257 258 259 261 263 265 266 267 268 269 271 274 275 278 xiii Inhaltsverzeichnis Eine Datenbank, Viele Tabellen Aus Ihrer Tabelle herauswachsen Manchmal reicht eine einzige Tabelle einfach nicht mehr a u s . Ihre Daten sind komplexer geworden, und die eine Tabelle, mit der Sie gearbeitet haben, schafft es nicht mehr. Ihre Tabelle steckt voller redundanter Daten, die Platz verschwenden und Ihre Abfragen verlangsamen. Sie sind Ihrer einen Tabelle treu geblieben, solange es möglich war. Aber die Welt ist groß und weit, und manchmal braucht man mehrere Tabellen, um seine Daten festzuhalten, zu steuern und die eigene Datenbank unter Kontrolle zu halten. Ein Date für Norbert Alles ist verloren ... doch, Moment Über eine Tabelle hinausdenken Die Mehr-Tabellen-Clown-Datenbank Das clown_info-Datenbankschema Aus einer Tabelle zwei machen Ihre Tabellen verknüpfen Ihren Fremdschlüssel beschränken Warum Fremdschlüssel? Eine Tabelle mit einem Fremdschlüssel erstellen Beziehungen zwischen Tabellen Datenmuster: 1:1 Datenmuster: Verwendungszwecke für l:l-Tabellen Datenmuster: eins-zu-viele Info orl «i r wann ortjd 0—* Datenmuster: nun die m:n-Beziehungen Datenmuster: Wir brauchen eine Verbindungstabelle Ort Datenmuster: m:n Endlich 1NF> Schlüssel auf mehreren Spalten Kurzform-Notationen Partielle funktioneile Abhängigkeit Transitive funktioneile Abhängigkeit 2. Normalform Die 3. Normalform (endlich) Glücklich bis ans Ende ihrer Tage Ihr SQL-Werkzeugkasten XIV 282 293 294 295 296 298 303 305 306 307 309 309 310 311 312 315 316 321 322 324 325 326 330 336 339 340 Inhaltsverzeichnis Joins und 8 Vereinigen wir uns! Willkommen in der Mehr-Tabellen-Welt. Es ist wunderbar, dass Sie jetzt mehrere Tabellen in Ihrer Datenbank haben, aber Sie müssen ein paar neue Werkzeuge und Techniken kennenlernen, damit Sie mit ihnen auch arbeiten können. Mehrere Tabellen führen schnell zu Verwirrungen, deswegen benötigen Sie Aliase, um Ihre Tabellen auseinanderzuhalten. Und Joins helfen Ihnen, Ihre Tabellen zu verknüpfen, damit Sie wieder an all die Daten herankommen, die wir im letzten Kapitel verteilt haben. Machen Sie sich fertig, es ist Zeit, die Steuerung der Datenbank wieder zu übernehmen. Immer diese Wiederholungen ... Ihre Tabellen vorab füllen Die »Schwer zu normalisieren«-Misere Die besonderen Interessen (Werte) Interessiert bleiben Alle Interessen aktualisieren Alle Interessen abfragen Viele Wege fuhren nach Rom CREATE, SELECT und INSERT (fast) gleichzeitig CREATE, SELECT und INSERT gleichzeitig Was das AS macht... Spaltenaliase Wer braucht schon Tabellenaliase? Alles, was Sie schon immer über Joins wissen wollten . Der Kartesische Join O^ .. und do kommen die kleinen Ergebnistabellen wirklich her! Ihren Inneren Join befreien Der Inner-Join im Einsatz: der Equi-Join Der Innejj-Join im Einsatz: der Nicht-Equi-Join Der letzte Inner: der Natural-Join Vereinte Abfragen? Tabellen- und Spaltenaliase im Gespräch Ihr SQL-Werkzeugkasten 344 345 347 348 349 350 351 352 352 353 354 355 356 357 358 363 364 367 368 375 376 377 XV Inhaltsverzeichnis Abfragen in Abfragen Ja, Hans, mir wäre eine zweiteilige Frage recht. Joins sind wunderbar, aber manchmal müssen Sie Ihrer Datenbank mehrere Fragen stellen. Oder das Ergebnis einer Abfrage nehmen und als Eingabe für eine andere Abfrage verwenden. An dem Punkt kommen Unterabfragen ins Spiel. Sie helfen Ihnen, doppelte Daten zu vermeiden, und machen Ihre Abfragen dynamischer. Sie verschaffen Ihnen sogar Zugang zu diesen exklusiven Backstage-Partys. (Na, nicht wirklich, aber zwei von drei ist ja keine so schlechte Quote!) j AUSSEHE Abfrage : INNERE Abfrage . Gregor wird Jobvermittler 380 Gregors Liste erhält mehr Tabellen 381 Gregor nutzt einen Inner-Join 382 Aber er möchte noch andere Abfragen probieren 384 Unterabfragen 386 Zwei Abfragen zu einer kombinieren 387 Als wäre eine Abfrage nicht genug: die Unterabfrage 388 Eine Unterabfrage im Einsatz 389 Regeln für Unterabfragen 391 Unterabfragenaufbauschnellkurs 394 Eine Unterabfrage als SELECT-Spalte 397 Eine Unterabfrage mit einem Natural-Join 398 Eine nicht-korrelierte Unterabfrage 399 SQL im Gespräch: Die beste Form einer Abfrage finden 400 Nicht-korrelierte Unterabfragen mit mehreren Werten 403 Korrelierte Unterabfragen 408 Eine (nützliche) korrelierte Unterabfrage mit NOT EXISTS 409 EXISTS unrLNOT EXISTS 410 Gregors Jobvermittlung öffnet die Türen 412 Auf dem Weg zur Party ... 413 Ihr SQL-Werkzeugkasten 414 SELECT eine_Spalte, andere_Spalte FROM Tabelle WHERE Spalte = (SELECT Spalte FROM Tabelle) XVI Inhaltsverzeichnis 10 OuterJoins, Sei? Joins und Unlgns Neue Manöver Noch kennen Sie die Join-Geschichte nur halb, sie wissen, dass Cross-Joins alle Zeilen beider Tabellen liefern und Inner-Joins die Zeilen, die einer Bedingung genügen. Was Sie noch nicht kennen, sind Outer-Joins, die Ihnen Zeilen liefern, zu denen es keine passenden Gegenstücke in der anderen Tabelle gibt, Self-Joins, die (was ziemlich seltsam erscheint) eine Tabelle mit sich selbst verknüpfen, und Unions, die die Ergebnisse Ihrer Abfragen kombinieren. Haben Sie das alles einmal gelernt, werden Sie Ihre Daten immer so abrufen können, wie Sie sie gerade benötigen. (Und auch die Frage nach der Wahrheit von Unterabfragen haben wir nicht vergessen!) Alte Daten aufräumen Hier geht's links und rechts Hier ist ein Left-Outer-Join Outer-Joins und mehrere Treffer Der Right-Outer-Join Mit UNION kam Während Sie mit Outer-Joins gespielt haben . fytyr die Wir könnten eine neue Tabelle erstellen fciratbnisse diesw * * separaten AV> Wie sich die neue Tabelle einfugt Ein selbstreferentieller Fremdschlüssel hnunjen Eine Tabelle mit sich selbst verknüpfen Wir brauchen einen Self-Join Mehr-Tabellen-Informationen, 2. Weg Sie können eine UNION verwenden UNION ist beschränkt g üssen in beiden Tabellen vorhanden sein. UNION-Regeln im Einsatz UNION ALL Aus einer UNION eine Tabelle erstellen INTERSECT und EXCEPT Mit Joins sind wir fertig, Zeit weiterzugehen . Unterabfragen und Joins im Vergleich Eine Unterabfrage in einen Join umwandeln Ein Self-Join als Unterabfrage A BezeitVinunjen, die m beiden Tabellen sind, werden aus den Ergebnissen ausjestWossen Gregors Unternehmen wacht Ihr SQL-Werkzeugkasten 418 419 420 425 426 429 430 431 432 433 435 436 437 438 439 440 441 442 443 443 444 449 450 452 XVII Inhaltsverzeichnis Constralnts, Views und Transaktionen Zu viele Köche verderben die Datenbank 11 Ihre Datenbank ist gewachsen und muss jetzt auch von anderen V e r w e n d e t W e r d e n . Das Problem ist, dass sich einige von ihnen mit SQL vielleicht nicht so gut auskennen wie Sie. Sie brauchen Möglichkeiten zu verhindern, dass sie fal- sche Daten eingeben, Techniken, die erlauben, ihnen nur Teile der Daten zu präsentieren, und Wege, sie davon abzuhalten, sich gegenseitig auf die Füße zu treten, wenn sie parallel Daten eingeben. In diesem Kapitel beginnen wir, unsere Daten vor den Fehlern anderer zu schützen. Willkommen zu Datenbanken in der Defensive, Teil 1. I BANKHAUS HELLER & PFENNIG ' xviii " • — I Gregor hat Hilfskräfte eingestellt 456 Tims erster Tag: Einen neuen Kunden einfügen 457 Tim vermeidet eine NULL 458 Drei Monate später 459 Einen CHECK-Constraint einfügen 460 Das Geschlecht einschränken 461 Franks Job wird lästig 463 Einen View erstellen 465 Views betrachten 466 Was der View tatsächlich macht 467 Was ein View ist 468 Einfügen, Aktualisieren und Löschen mit Views 471 Vorgeben, dass der View eine richtige Tabelle ist 472 Ein View mit CHECK OPTION 475 Ihr View ist aktualisierbar, wenn ... 476 Wenn Sie Ihre Views nicht mehr brauchen 477 Wenn guten Datenbanken Böses widerfährt 478 Was im Geldautomaten abläuft 479 Mehr Ärger am Geldautomaten 480 Es ist kein Traum, es ist eine Transaktion 482 Der klassische ACID-Test 483 SQL hilft Ihnen, Transaktionen zu steuern 484 Was im Geldautomaten hätte passieren sollen 485 Unter MySQL mit Transaktionen arbeiten 486 Und jetzt sind Sie dran 487 Ihr SQL-Werkzeugkasten 490 Inhaltsverzeichnis Sicfcerfceft 12 Ihre Anlagen schützen Sie haben eine ungeheure Menge an Zeit und Energie in die Erstellung Ihrer Datenbank gesteckt. Und Sie wären erschüttert, wenn dieser etwas passieren würde. Aber Sie mussten auch anderen Personen Zugriff auf Ihre Daten geben und sorgen sich jetzt, dass diese etwas falsch einfügen oder aktualisieren könnten oder - noch schlimmer - die falschen Daten löschen könnten. Jetzt werden Sie lernen, wie Datenbanken und die Objekte darin sicherer gemacht werden und wie Sie die vollständige Steuerung darüber erhalten, wer was mit Ihren Daten machen darf. Anwenderprobleme 494 Fehler in der Clown-Datenbank vermeiden 495 Das Benutzerkonto root schützen 497 Einen neuen Benutzer hinzufügen 498 Entscheiden, was genau ein Benutzer braucht 499 Eine einfache GRANT-Anweisung 500 GRANT-Variante 503 Berechtigungen mit REVOKE widerrufen 504 Eine verwendete GRANT OPTION widerrufen 505 REVOKE mit Präzision 506 Das Problem mit geteilten Konten 510 Die Rolle verwenden 512 Rollen löschen 512 Eine Rolle WITH ADMIN OPTION verwenden 514 CREATE USER und GRANT kombinieren 519 Gregors Liste wird international 520 Ihr SQL-Werkzeugkasten 522 Wie wäre es njjt Gregors liste in Ihrer Stadt? 524 SQL in Ihren eigenen Projekten 524 n root traege 1 froh muede krank XIX
