HOWTO zur MySQL-Nutzung (Entwickler oder Anwender) (C) 2006-2018 T.Birnthaler/H.Gottschalk OSTC Open Source Training and Consulting GmbH http://www.ostc.de $Id: mysql-user-HOWTO.txt,v 1.297 2020/02/24 06:36:18 tsbirn Exp $ Dieses Dokument beschreibt den MySQL-Einsatz auf Entwickler/Anwenderseite. HINWEIS: MySQL-Spezialitäten sind durch "MY!" oder "MY!N.M" gekennzeichnet (falls sie ab MySQL Version N.M (nicht mehr) verfügbar sind). HINWEIS: Der Begriff "Datenbank" wird häufig "schwammig" verwendet. MySQL ist ein "Datenbank-Managementsystem" (DBMS), das die Verwaltung vieler "Datenbanken" gleichzeitig erlaubt. Jede Datenbank besteht aus Tabellen und weiteren Datenbank-Objekten, die zur Abbildung eines konkreten Sachverhalts verwendet werden. Statt der (langen) Begriffe "Datenbank-Managementsystem" oder (abgekürzt) "Datenbank-System" wird gerne der kurze Begriff "Datenbank" verwendet. Ein besserer Begriff für eine eigentliche "Datenbank" ist daher "Schema", so besteht keine Verwechslungsgefahr, was gemeint ist. Das DBMS Oracle z.B. vermeidet den Begriff "Datenbank" und verwendet "Schema". Für "Datenbank" wird in diesem Skript "DB" oder als Abkürzung verwendet. ________________________________________________________________________________ INHALTSVERZEICHNIS ================== 1) MySQL-Clients 1a) Zusammenspiel der MySQL-Komponenten 1b) MySQL Web-Client "phpMyAdmin" einrichten und aufrufen 1c) MySQL-Clients: Konfigurations-Dateien 1d) MySQL-Clients: Allgemeine Optionen 1e) MySQL-Clients: Verbindung zum Server (SSL) 1f) Client "mysql" starten (Verbindung/Session) 1g) Client "mysql": Befehlsein- und ausgabe (Prompt, Strg-C, TEE) 1h) Client "mysql" effizient bedienen (USE, History, Strg-L, TAB, Hilfe, SOURCE) 1i) Client "mysql" verlassen 1j) Client "mysql" auf Kommandozeile nutzen (Batch-Modus) 1k) Client "mysql": Spezielle Optionen 1l) Client "mysql": Ausgabeformat-Optionen 1m) Client "mysql": Sonstige Optionen 1n) Client "mysql": Umgebungsvariablen 1o) Client "mysql": Interne Befehle (Escape-Sequenzen) 1p) Client "mysql": Hilfe anzeigen 1q) Client "mysql": Prompt-Definition 1r) Grafische MySQL-Programme (GUI) 2) Syntax 2a) Leerraum und Formatierung 2b) Zeichenketten (Strings) und Quotierung 2c) Escape-Sequenzen 2d) Zahlen 2e) Datenbank-Objekte 2f) Identifier (Bezeichner) 2g) GROSS/kleinschreibung 2h) Kommentare 3) Typische MySQL-Datenbankbefehle (Tutorial) 4) MySQL-Datentypen 4a) Datentyp-Optimierung (Performance/Speicherplatz) 4b) Fixe/Variable Record-Länge (Row-Format) 4c) AUTO_INCREMENT 5) MySQL-Operatoren 6) Boolesche Logik 7) Der Wert NULL 7a) Eigenschaften von NULL 7b) Prüfung auf Wert NULL 7c) Vergleiche mit NULL 7d) Boolesche Logik mit NULL (ternär/dreiwertig) 8) Reguläre Ausdrücke in MySQL (RegEx) 9) MySQL-Funktionen 9a) Aggregatfunktionen und Gruppierung (Aggregation) 10) Schlüssel (Key) und Index 10a) Eigenschaften 10b) Erstellen/entfernen 10c) FULLTEXT-Index 10d) SPATIAL-Index 10e) Index-Nutzung 10f) Index-Optimierung 10g) Fremdschlüssel (Foreign Keys) und Referenzielle Integrität 11) Joins 12) Mengen-Operationen 13) Unterabfragen (Subqueries/Subselect) 14) Transaktionen 15) Locking 16) Views (Sichten) 17) Variablen 18) Prepared Statements (Vorbereitete Anweisungen) 19) Stored Routines (Stored Procedures/Functions) 19a) Lokale Variablen 19b) Wertzuweisung an Variable 19c) Ausgabe von Variablen oder Daten 19d) Kontrollstrukturen (Block/Compound Statement) 19e) Kontrollstrukturen (Verzweigungen) 19f) Kontrollstrukturen (Schleifen) 19g) Prozeduren 19h) Funktionen 20) Trigger 21) Condition Handler (Error/Warning) 22) Cursor (Zeiger) 23) Table Handler 24) Events (Ereignisse) 25) Signale 25a) MySQL-Fehlercode / SQL-Status 26) Begrenzungen von MySQL (Limits) 27) MySQL testen -------------------------------------------------------------------------------- ________________________________________________________________________________ 1) MySQL-Clients ---------------- 1a) Zusammenspiel der MySQL-Komponenten --------------------------------------- Architektur: * N MySQL-Clients + 1 MySQL-Server (auf anderem oder gleichem Rechner) + Client: mysql (Kommandoschnittstelle, eine von vielen) + Server: mysqld (Daemon, Instanz) * MySQL-Server-Schichten + Oben: Benutzer-Schnittstelle (API, SQL, DB-Management) + Mitte: Storage Engine + Unten: Dateisystem Server "mysqld" #=================================# X +------+ +------+ +------+ X X |Key- | |Temp- | |Table-| X X |buffer| |tables| |cache | X X +------+ +------+ +------+ X X +------+ +------+ +------+ X X |Buffer| |Sort- | |Join- | X X |-pool | |buffer| |buffer| X X +------+ +------+ +------+ X X............................^....X X +--------------------+ | X Storage Clients X | | | X Engines Dateisystem +-------+ Netz X +--v---+ +------+ +-+-v-+ X +--------+ +---------+ | mysql |<------->| Conn | |Query | |Execu|<---+->| InnoDB |<->| | +-------+ werk X | ecti +-->|Cache | |-tor | X | +--------+ | Dateien | : X | ion | +------+ +--^--+ X | : | Verz. | : X | / | ^ Plan | X +-> : <->| / | : X |Thread| | +--+--+ X | : | Raw | +-------+ Soc- X | Hand | +--+---+ |Opti-| X | +--------+ | Devices | | mysql |<------->| ling +-->|Parser+-->|mizer| X +->| MyISAM |<->| | +-------+ ket X +------+ +------+ +-----+ X +--------+ +---------+ #=================================# Die Verbindung zwischen MySQL-Client und -Server erfolgt per Socket, Named Pipe, Memory oder TCP/IP (siehe Optionen "-h/--host" und "--protocol"). 1b) MySQL Web-Client "phpMyAdmin" einrichten und aufrufen --------------------------------------------------------- A) Einrichtung per Paket "phpmyadmin" (Administrator "phpmyadmin"): sudo apt-get install phpmyadmin # Paket + abhängig. Pakete install. /etc/phpmyadmin/config.inc.php # Konfiguration /usr/share/phpmyadmin/* # PHP-Code /usr/share/doc/phpmyadmin/* # Dokumentation http://localhost/phpmyadmin/index.php # Anmeldung an Web-Oberfläche B) Apache Webserver mit PHP-Unterstützung installieren und phpMyAdmin-Quellcode kopieren nach: /srv/www/htdocs/phpMyAdmin # Variante A /var/www/htdocs/phpMyAdmin # Variante B Dazu vorher in Konfig-Datei (inc=include) /srv/www/htdocs/phpMyAdmin/config.inc.php # Variante A /var/www/htdocs/phpMyAdmin/config.inc.php # Variante B einen gültigen MySQL-Benutzer mit gültigem Passwort eintragen, der MySQL-Administrationsrechte (d.h. ALL PRIVILEGES + GRANT OPTION) besitzt: $cfg['Servers'][$i]['auth_type'] = 'config'; # Auth.meth. (config/http/cookie) $cfg['Servers'][$i]['user'] = 'root'; # MySQL Benutzer (Administrator) $cfg['Servers'][$i]['password'] = 'GEHEIM'; # MySQL Passwort (wg. 'config') Den Apache-Webserver starten: /etc/init.d/apache2 start # Temporär (init) service apache2 start # Temporär (systemd) rcapache2 start # Temporär (OpenSUSE) chkconfig -a apache2 # Permanent (OpenSUSE) Im Web-Browser dann über folgende URL die Oberfläche "phpMyAdmin" aufrufen und mit den in "config.inc.php" eingetragenen Benutzer-Daten anmelden: http://localhost/phpMyAdmin/index.php 1c) MySQL-Clients: Konfigurations-Dateien ----------------------------------------- Die Einstellung der MySQL-Variablen und -Optionen stammen aus dem MySQL-Kommando selbst sowie einigen Konfig-Dateien in der hier angegebenen Reihenfolge (sofern vorhanden, die Einstellungen der zuletzt angegebenen überschreiben die Einstellungen der ersten): +---+-------------------+------------------------------------+ | N | Quelle (Datei) | Bedeutung | +---+-------------------+------------------------------------+ | 0a| Client-Programm | Global (zentral, fest eingebaut) | | | 0b| Server "mysqld" | Global (zentral, fest eingebaut) | | +---+-------------------+------------------------------------+ | | 1 | /etc/my.cnf | Global (zentral) | | | 2 | /etc/mysql/my.cnf | Global (zentral) | | steigender | 3 | /usr/etc/my.cnf | Global (zentral) | | Vorrang +---+-------------------+------------------------------------+ | | 4 | ~/.my.cnf | Lokal (benutzerbezogen) | | | 5 | Umgebung | Lokal (Umgebungsvariablen) | | | 6 | Kommandozeile | Lokal (direkt beim Kommandoaufruf) | v +---+-------------------+------------------------------------+ HINWEIS: Optionen auf der Kommandozeile haben immer das "letzte Wort"! Konfig-Dateien sind durch in eckige Klammern gesetzte Programmnamen in "GRUPPEN" (Abschnitte) eingeteilt. Berücksichtigt werden für den "mysql"-Client Optionen folgender Gruppen (--> mysql-admin-HOWTO.txt --> 1a) Grundlegendes): [mysql] # Programmname [client] # Programmtyp TIP: Mit folgenden Optionen wird das Einlesen der Konfig-Dateien verändert bzw. der sich aus den Konfig-Dateien ergebende Default-Wert der Optionen ausgegeben. Die ersten drei Optionen sind nur als 1. Argument nach dem Kommandonamen (z.B. "mysql") erlaubt, --print-defaults muss direkt danach stehen: +------------------------------+---------------------------------------------+ | Option | Bedeutung | +------------------------------+---------------------------------------------+ | --no-defaults | Keine Konf.dateien lesen | | --defaults-file= | Default-Optionen NUR aus Datei lesen | | --defaults-extra-file= | Datei ZUSÄTZL. nach glob. Konf.dat. | +------------------------------+---------------------------------------------+ | --print-defaults | Programmargumente ausgeben und Abbruch | +------------------------------+---------------------------------------------+ D.h. die in das Server-Programm "mysqld" bzw. das Client-Programm "mysql" eingebauten Einstellungen erhält man per: mysqld --no-defaults --print-defaults # Server mysql --no-defaults --print-defaults # Client Die Einstellungen gemäß Konfig.Dateien von Server und Client erhält man per: mysqld --print-defaults # Server mysql --print-defaults # Client 1d) MySQL-Clients: Allgemeine Optionen -------------------------------------- Die Optionen der MySQL-Kommandozeilen-Programme sind in Kurzform "-X" sowie in Langform "--XXX" angebbar, bei der Kurzform ist die GROSS/kleinschreibung relevant, bei der Langform nicht. ALLE MySQL-Programme kennen folgende Optionen: +----------+-----------+----------------------------------------------+ | Kurzform | Langform | Bedeutung | +----------+-----------+----------------------------------------------+ | -v | --verbose | Mehr Info ausgeben (mehrfach erlaubt) | | -s | --silent | Weniger Info ausgeben (mehrfach erlaubt) | +----------+-----------+----------------------------------------------+ | -V | --version | Versioninformation ausgeben | | -? | --help | Hilfe zum Aufruf ausgeben (Syntax, Optionen) | +----------+-----------+----------------------------------------------+ ALLE MySQL-Kommandozeilen-Clients mit einer Verbindung zum MySQL-Server kennen folgende Optionen (legen Art und Weise der Verbindung fest): +------------+---------------------+----------------------------------------+ | Kurzform | Langform | Bedeutung | +------------+---------------------+----------------------------------------+ | -h | --host= | Ziel-Host (STD: localhost) | | -u | --user= | Benutzer festlegen (STD: OS-Anmeldung) | | -p[] | --password[=] | Passwort abfragen bzw. direkt angeben | | -D | --database= | Datenbank auswählen (STD: keine) | +------------+---------------------+----------------------------------------+ | | --protocol= | Verb.protokoll (tcp/socket/pipe/memory)| | -P | --port= | Ziel-Port (STD: 3306) | | -S | --socket= | Socket (STD: /var/lib/mysql/mysql.sock | | | | bzw. /var/run/mysqld/mysqld.sock) | +------------+---------------------+----------------------------------------+ Bedeutung von "" bei Option --protocol: +---------+-------------------------------------------------+--------------+ | | Verbindung Client <-> Server erzwingen ... | Art | +---------+-------------------------------------------------+--------------+ | tcp | ... per TCP (STD: Port 3306) | lokal+remote | | socket | ... per Socket (STD: /var/lib/mysql/mysql.sock) | nur lokal | | pipe | ... per Named Pipe (STD: | memory | ... per Shared Memory | nur lokal | +---------+-------------------------------------------------+--------------+ HINWEIS: Standardmäßig wird lokal per "socket" und remote per "tcp" verbunden. HINWEIS: Bei der Kurzform -X darf der Wert auch per Leerzeichen getrennt von der Option angegeben werden. AUSNAME: Das Passwort ist DIREKT NACH der Option "-p" (ohne Leerzeichen dazwischen) anzugeben! HINWEIS: Ohne Option "-p" wird eine Anmeldung OHNE Passwort versucht. 1e) MySQL-Clients: Verbindung zum Server (SSL) ---------------------------------------------- Die MySQL-Clients versuchen je nach Aufruf auf folgende Arten mit dem MySQL-Server Verbindung aufzunehmen: * Lokale Verbindung per Socket/Named Pipe/Shared Memory: Option "-h/--host" nicht oder mit Wert "localhost/127.0.0.1/::1" angegeben und Option "--protocol" nicht angegeben oder nicht auf Wert "tcp" gesetzt. MySQL-Client und -Server müssen dann natürlich auf dem gleichen Host laufen oder per VPN verbunden sein. * Netzwerk-Verbindung per TCP/IP: Option "-h/--host" angegeben und bezeichnet fremden Rechner/fremde IP-Adresse oder Option "--protocol" auf Wert "tcp" gesetzt. Auch dann, wenn rein lokale Verbindung möglich wäre! Für eine per SSL verschlüsselte Verbindung zwischen Client und Server sind folgende Optionen anzugeben: +--------------------------+------------------------------------------------+ | Option (nur Langform) | Bedeutung | +--------------------------+------------------------------------------------+ | --ssl | SSL für Verbindung wz. Cl. und S. einschalten | | | (automatisch von anderen --ssl-Opt. aktiviert) | | --skip-ssl | Abschalten der SSL-Verbindungsverschlüsselung | +--------------------------+------------------------------------------------+ | --ssl-ca= | CA-Datei im PEM-Format (siehe OpenSSL-Doku) | | --ssl-capath= | CA-Verz. (siehe OpenSSL-Doku) | | --ssl-cert= | X509-Zertifikat in PEM-Format | | --ssl-cipher= | SSL-Verschlüsselungstyp (...) | | --ssl-key= | X509-Schlüssel im PEM-Format | +--------------------------+------------------------------------------------+ | --ssl-verify-server-cert | "Common Name" des Servers in seinem Zertifikat | | | mit dem beim Verb.aufbau benutzten Hostnamen | | | vergleichen (STD: aus) | +--------------------------+------------------------------------------------+ Ob ein Verbindung per SSL verschlüssel werden MUSS, wird pro Benutzer beim Anlegen durch Angabe der Option REQUIRE festgelegt: +------------------+---------------------------------------------------------+ | Option | Bedeutung | +------------------+---------------------------------------------------------+ | NONE | Unverschlüss. Verbindung erlaubt (STD), verschl. auch | | SSL | Nur SSL-verschlüsselte Verbindungen erlaubt | | X509 | Gültiges Zertifikat notwendig, Issuer + Subject egal | | CIPHER | Verschlüsselung und Schlüssel müssen best. Bed. genügen | | | (z.B. CIPHER "EDH-RSA-DES-CBC3-SHA") | | ISSUER | Zertifikat muss von CA ausgestellt sein | | SUBJECT | Zertifikat muss Subject enthalten | +------------------+---------------------------------------------------------+ 1f) Client "mysql" starten (Verbindung/Session) ----------------------------------------------- Der Client "mysql" bietet eine einfache KOMMANDOZEILENBASIERTE Schnittstelle zum Absetzen von SQL-Anweisungen an einen MySQL-Server und zur Ausgabe ihrer Ergebnisse (MySQL-"Terminal/Monitor"). Trotzdem ist er sehr leistungsfähig, weil MySQL (nahezu) vollständig über SQL-Anweisungen steuerbar ist. Um diese Schnittstelle nutzen zu können, sind allerdings gute Kenntnisse der SQL-Anweisungen und ihrer Syntax notwendig. Auf der Kommandozeile wird zunächst per Client "mysql" eine VERBINDUNG mit dem MySQL-Server "mysqld" aufgenommen (eine SESSION gestartet), indem eine Anmeldung mit gültigem Benutzernamen + Passwort durchgeführt wird (-u=user, -p=password, -h=host, -D=database, -P=Port, -S=Socket). Anschließend können solange SQL-Anweisungen abgesetzt werden, bis durch Verlassen des "mysql"-Clients die Verbindung zum MySQL-Server "mysqld" wieder beendet wird: mysql -utom -pgeheim Geht die Verbindung zum MySQL-Server verloren (z.B. durch Timeout), dann öffnet "mysql" beim nächsten Kommando AUTOMATISCH eine neue Verbindung (reconnect). Da dies fast unmerklich geschieht und beim Verbindungsabbruch Sitzungsdaten wie lokale Variablen und sonstige Einstellungen verloren gehen, ist dies auch verhinderbar durch: mysql --skip-reconnect ... Typische Aufrufe des "mysql"-Clients: +-------------------------------+--------------------------------------------+ | Kommando | Bedeutung | +-------------------------------+--------------------------------------------+ | mysql | Als angemeldeter OS-Benutzer aufrufen | | mysql -utom | Benutzer "tom" ohne Passwort (unsinnig!) | | mysql -utom -p | Benutzer "tom" (Passwort interaktiv eingeb)| | mysql -utom -pgeheim | Passwort "geheim" gleich mitgeben (ungut!) | | mysql -utom -pgeheim -htest | Mit Rechner "test" verbinden (host) | | mysql -utom -pgeheim -P1234 | Mit Port "1234" verbinden (STD: 3306) | | mysql -utom -pgeheim -S/tmp/x | Mit Socket verbinden | | mysql -utom -pgeheim -Dprod | Datenbank "prod" auswählen (Variante A) | | mysql -utom -pgeheim prod | Datenbank "prod" auswählen (Variante B) | +-------------------------------+--------------------------------------------+ Hilfe zum Aufruf des "mysql"-Client anzeigen (Optionen, Reihenfolge der Konfig-Dateien, Werte von Variablen und Optionen): mysql --help mysql -? Weitere Einstellungen zur Verbindung: !!! --connect-timeout= # Sek. bis autom. Verbindungstrenn. (STD: 0=nie) --max-allowed-packet= # Max. Länge transf. Befehle/Ergebnisse (STD: 16MB) --net-buffer-length= # Puffergröße TCP/IP+Socket-Komm. (STD: 16KB) --skip-reconnect # Keine automat. Neuverbindung nach Trennung Durch Setzen des Timeout wird der Client bei längerer Nichtbenutzung automatisch verlassen (STD: 0 = kein Timeout): TIP: Aliase für "mysql"-Anmeldung + Umschalten auf aktuell relevante Datenbank definieren und in "~/.alias" oder "~/.bash_aliases" ablegen (-D=Standard-DB, -t=ASCII-Rahmen um Ausgaben zeichnen, -e=Befehl ausführen): alias my="mysql -utom -pgeheim -Dtest -t" # Interaktiv/Batch per Umlenk. alias mye="mysql -utom -pgeheim -Dtest -t -e" # SQL direkt auf Kommandozeile Aufruf durch: my # Interaktiv-Modus starten my < # Batch-Datei mit SQL-Anweisungen einlesen mye 'SELECT * FROM pers' # SQL-Anweisung direkt ausführen 1g) Client "mysql": Befehlsein- und ausgabe (Prompt, Strg-C, TEE) ----------------------------------------------------------------- Nach der Anmeldung am "mysql-Client" erscheint ein PROMPT folgender Form, der die interaktive Eingabe beliebiger SQL-Anweisungen (Statements) erwartet. Abschluss der Eingabe per übergibt diese dem MySQL-Server zur Ausführung und zeigt das Ergebnis sowie statistische Informationen an. mysql> ... # Wartet auf Kommando-Eingabe + HINWEIS: Echte SQL-Anweisungen (werden an MySQL-Server geschickt) sind mit ";" abzuschließen! Client-spezifische Befehle ("USE " oder "QUIT") dürfen ohne abschließenden ";" geschrieben werden (besser angeben). Um z.B. alle Datenbanken anzuzeigen, ist folgender Dialog zu führen (die SQL-Anweisung ist hier GROSS geschrieben, GROSS/kleinschreibung ist aber eigentlich egal, siehe auch --> 2g) GROSS/kleinschreibung): mysql> SHOW DATABASES; # Prompt + Benutzer-EINGABE (";" notwendig) +------------------------------+ # ASCII-Rahmen (AUSGABE) | Database | # Spaltenüberschrift (AUSGABE) +------------------------------+ # ASCII-Rahmen (AUSGABE) | INFORMATION_SCHEMA | # 1. Ergebniszeile (AUSGABE) | mysql | # 2. Ergebniszeile (AUSGABE) | PERFORMANCE_SCHEMA | # 3. Ergebniszeile (AUSGABE) | phpmyadmin | # 4. Ergebniszeile (AUSGABE) | ... | # ... (AUSGABE) +------------------------------+ # ASCII-Rahmen (AUSGABE) 15 rows in set (0.01 sec) # Statistik (15 Ergeb.zeilen + Laufzeit) # (Leerzeile) mysql> # Prompt nächste Benutzer-EINGABE oder (leeres Ergebnis): mysql> SHOW DATABASES LIKE "xyz"; # Prompt + Benutzer-EINGABE + Empty set (0.00 sec) # Statistik (leeres Ergebnis + Laufzeit) # (Leerzeile) mysql> # Prompt nächste Benutzer-EINGABE oder (Fehler): mysql> SHOW TABLES; # Prompt + Benutzer-EINGABE ERROR 1046 (3D000): No database selected # Fehlermeldung mysql> # Prompt nächste Benutzer-EINGABE Fehlermeldungen, Warnungen und Bemerkungen (Notes) nach einer SQL-Anweisung ausgeben (bezieht sich immer auf die vorhergehende SQL-Anweisung): SHOW ERRORS; # Nur Fehler ausgeben SHOW WARNINGS; # Fehler, Warnungen, Bemerkungen (Notes) ausgeben Nach dem sendet der Client "mysql" das Kommando an den MySQL-Server "mysqld", mit dem die Verbindung besteht, wartet auf das Ergebnis und gibt es auf dem Bildschirm aus. Die Ausgabe erfolgt in tabellarischer Form (Zeilen + Spalten). Die erste Zeile enthält die Spaltenüberschrift. Am Ende wird die Anzahl der erhaltenen Datensätze (N rows) und die Dauer der Abfrage (N.NN sec) ausgegeben. Anschließend wird erneut der Prompt angezeigt und auf die Eingabe des nächsten Kommandos gewartet. Hier noch ein Beispiel: mysql> SELECT SIN(PI()/2), (4+1)*5; # Prompt + Benutzer-Eingabe +-------------+---------+ # Ausgabe (ASCII-Rahmen) | SIN(PI()/2) | (4+1)*5 | # Ausgabe (Überschrift) +-------------+---------+ # Ausgabe (ASCII-Rahmen) | 1 | 25 | # Ausgabe (1. Datensatz) +-------------+---------+ # Ausgabe (ASCII-Rahmen) 1 row in set (0.00 sec) # Statistik (eine Ergeb.zl. + Laufzeit) # (Leerzeile) mysql> # Prompt nächste Benutzer-EINGABE Fortsetzungs-Prompt "->": SQL-Kmdos müssen nicht auf einer Zeile stehen, sie können mehrere Zeilen verteilt sein. MySQL erkennt das Kommando-Ende am abschließenden ";" und gibt bis zum abschließenden ";" den Fortsetzungs-Prompt "->" aus: mysql> SELECT user, # Kmdo-Beginn -> password, # Kmdo-Teil -> host # Kmdo-Teil -> FROM mysql.user # Kmdo-Teil -> ; # Kmdo-Ende ";" Unvollständige SQL-Anweisungen (z.B. wird der ";" gerne vergessen) führen zur Anzeige eines der folgenden Fortsetzungs-Prompts (dann einfach restliche Kommandoteile oder fehlendes ";" tippen und drücken): +--------+------------+------------------------------------------+ | Prompt | Element | Bedeutung | +--------+------------+------------------------------------------+ | -> | Kommando | Unvollständig bzw. ";" vergessen | | "> | String | "..." begonnen aber noch nicht beendet | | '> | String | '...' begonnen aber noch nicht beendet | | `> | Bezeichner | `...` begonnen aber noch nicht beendet | | /*> | Kommentar | /*...*/ begonnen aber noch nicht beendet | +--------+------------+------------------------------------------+ Ausgaben des "mysql"-Client in einer Datei aufzeichnen: mysql ... --tee= Die Ausgabe auf Datei kann auch interaktiv im "mysql"-Client aktiviert und wieder deaktiviert werden durch: mysql> TEE # Aufzeichnung starten mysql> ... # Ergebnisse aufzeichnen mysql> NOTEE # Aufzeichnung beenden mysql> ... # Ergebnisse NICHT aufzeichnen mysql> \T # Aufzeichnung starten (Escape-Sequenz) mysql> ... # Ergebnisse aufzeichnen mysql> \t # Aufzeichnung beenden (Escape-Sequenz) HINWEIS: Vorteil dieser Art der Ausgabesteuerung ist, dass die Ausgabe sowohl auf dem Bildschirm als auch in eine Datei erfolgt. 1h) Client "mysql" effizient bedienen (USE, History, Strg-L, TAB, Hilfe, SOURCE) -------------------------------------------------------------------------------- Standard-Datenbank per USE: Als 1. Kommando immer folgendes verwenden, um eine der vorhandenen Datenbanken als DEFAULT/STANDARD-DATENBANK auszuwählen. Die Objekte darin sind dann leicht über ihre Namen (ohne Qualifizierung mit dem Datenbanknamen) erreichbar. Der ";" am Ende darf bei "USE" weggelassen werden (besser erst gar nicht daran gewöhnen!): USE # Variante A USE ; # Variante B (besser) Name der Standard-Datenbank ausgeben ("NULL" falls noch keine ausgewählt): SELECT DATABASE(); # Variante A SELECT SCHEMA(); # Variante B MySQL-History: Alte Befehle können per Cursortasten durchgeblättert, editiert und erneut mit ausgeführt werden. Mehrzeilig Befehle werden dabei zu einer (langen) Zeile zusammengezogen, die evtl. schwer zu editieren ist. Die Befehle stehen auch nach Verlassen und erneutem Aufrufen des Clients "mysql" wieder zur Verfügung, da sie PRO BENUTZER in seinem Heimatverz. in folgender Datei gespeichert werden (d.h. root <-> normaler Benutzer getrennt): ~/.mysql_history Da ALLE im "mysql"-Client eingegebenen Befehle in der MySQL-History landen, können darin evtl. Passworte oder andere sicherheitsrelevante Daten stehen. Die Zugriffsrechte der Datei sollten daher 600 lauten ("rw" nur für Besitzer). Um den Datei-Inhalt schnell zu leeren, folgendes Kommando absetzen: > ~/.mysql_history # Leere Datei umlenken Um die MySQL-History NICHT aufzuzeichen, folgendes einstellen: export MYSQL_HISTFILE="/dev/null" # Variante A rm ~/.mysql_histfile && ln -s /dev/null ~/.mysql_histfile # Variante B Befehle oder Befehlsteile können auch per MAUS (linke Taste selektiert + mittlere Taste kopiert) in die Kommandozeile des "mysql"-Clients kopiert werden. Wird bis zum rechten Terminalrand markiert, ist der Befehlsabschluss gleich mit in der Kopie enthalten (unter Linux). TAB-Completion: Datenbank-, Tabellen- und Spalten-Namen (aber keine anderen SQL-Syntax-Elemente) können per -Taste AUTOMATISCH VERVOLLSTÄNDIGT werden (Tab-Completion). Dazu per "-D " oder "USE ;" eine Default/Standard-Datenbank auswählen und beim Aufruf von "mysql" die Option "--auto-rehash" angeben oder in "mysql" den Befehl "REHASH" eingeben (verlangsamt den Start etwas), um den Datenbankinhalt einzulesen. mysql -utom -pgeheim --auto-rehash mysql # Variante A mysql -utom -pgeheim --auto-rehash -D mysql # Variante B mysql -utom -pgeheim # Variante C USE mysql; # Variante C REHASH # Variante C Bildschirm LEEREN im "mysql"-Client (Prompt "mysql> " steht dann ganz oben): # TIP: L=Leeren (eigentlich Steuerzeichen FF=FormFeed ;-) HILFE zu Kommandos im "mysql"-Client durch folgende Befehle anzeigen (die Hilfetexte stehen direkt in der Datenbank in den Tabellen "mysql.help_..."): +------------------+---------------------------------------------------------+ | Befehl | Bedeutung | +------------------+---------------------------------------------------------+ | HELP | Liste der internen "mysql"-Client-Befehle ausgeben | | HELP HELP | Hilfe zur Hilfe selbst ausgeben | | HELP ... | Hilfe zu SQL-Anweisung ausgeben (eindeut. Präfix langt) | | HELP CONTENTS | Liste der Hilfe-Themen ausgeben | | HELP | Hilfe zu einem Thema aus Themenliste ausgeben | +------------------+---------------------------------------------------------+ TIP: UPDATE- und DELETE-Anweisungen gegen versehentliches Weglassen der WHERE- oder LIMIT-Klausel schützen (d.h. versehentliches Ändern/Löschen ALLER Datensätze verhindern) und Anzahl der Ergebniszeilen bzw. Verknüpfungszeilen beschränken: +----+--------------------------+--------------------------------------------+ | | Langform | Bedeutung | +----+--------------------------+--------------------------------------------+ | -U | --safe-updates | UPDATE- und DELETE-Anweisungen gegen | | | --i-am-a-dummy | Weglassen von WHERE und LIMIT schützen | +----+--------------------------+--------------------------------------------+ | | --select-limit= | Abbruch Erg.ausgabe ab N Zl. (STD: 1000) | | | --max-join-size= | Abbruch Erg.ausg. ab N Zl.komb. (STD: 1Mio)| +----+--------------------------+--------------------------------------------+ TIP: SQL-Anweisungen aus externer Datei einlesen (Include): SOURCE ; # Kein "..." um , vollst. Pfad! 1i) Client "mysql" verlassen (Strg-C) ------------------------------------- Durch folgende Eingaben kann der "mysql"-Client verlassen werden: +--------+------------------------------------------------------+ | Befehl | Bedeutung | +--------+------------------------------------------------------+ | QUIT | Quit-Befehl | | EXIT | Exit-Befehl | | \q | Quit (Escape-Sequenz) | +--------+------------------------------------------------------+ | Strg-C | Abbruch (Cancel, deaktivieren mit "--sigint-ignore") | | Strg-D | D=Dateiende (nur Linux) | | Strg-Z | Z=Dateiende (nur Windows) | +--------+------------------------------------------------------+ Drücken von Strg-C zum Abbrechen der aktuellen SQL-Anweisung sollte man sich abgewöhnen. In der Shell-Kommandozeile ist man daran gewöhnt, dass die Shell dabei nicht abgebrochen und man nicht abgemeldet wird. Aus dem Client "mysql" fliegt man hingegen sofort raus und muss sich mühsam wieder anmelden. Ruft man den "mysql"-Client mit Option "--sigint-ignore" auf, so beendet Strg-C nur noch den aktuellen Befehl, bricht aber "mysql" nicht mehr ab: mysql -utom -pgeheim --sigint-ignore -Dtest TIP: Am besten in Konfig-Datei "~/.my.cnf" eintragen. 1j) Client "mysql" auf Kommandozeile nutzen (Batch-Modus) --------------------------------------------------------- Neben der interaktiven Nutzung kann der Client "mysql" auch im Batch-Modus benutzt werden. Dazu die SQL-Anweisung direkt nach der Option "-e/--execute" angeben. Mehrere SQL-Anweisungen durch ";" trennen, der letzte ";" darf fehlen (d.h. einzelne SQL-Anweisung muss nicht mit ";" abgeschlossen werden): mysql -utom -pgeheim -e "USE mysql; SELECT user, host, password FROM user" Alternativ können die SQL-Anweisungen per Eingabe-Umleitung aus einer Datei eingelesen werden (analog dem Kommando "SOURCE" im "mysql"-Client). Ebenso können Ergebnisse von SQL-Anweisungen statt auf dem Bildschirm per Ausgabe-Umleitung auch auf eine Datei ausgegeben werden. +------------------------------------------+------------------------------+ | Kommando | Bedeutung | +------------------------------------------+------------------------------+ | mysql -u -p -e "" | execute | | ... -e "USE first; SELECT * FROM pers" | execute | | ... -e "USE first; | execute (mehrere Kommandos | | INSERT INTO pers VALUES | durch ";" trennen!) | | (100, 'Hans', 'Dampf'); | (Statement in "..." setzen, | | INSERT INTO pers VALUES | Strings darin in '...') | | (101, 'Hänschen', 'Klein')" | | +------------------------------------------+------------------------------+ | mysql -u -p < | Eingabe von Datei | | ..... -u -p -D < | (USE nicht vergessen!) | +------------------------------------------+------------------------------+ | ..... -u -p -D <; | SQL-Statement | | ... | ... | | EOF | bis EOF (End Of File) | +------------------------------------------+------------------------------+ | mysql ... -e "SOURCE " | Eingabe von Datei | | mysql ... > | Ausgabe auf Datei | +------------------------------------------+------------------------------+ Die Eingabedatei wird oft manuell erstellt und kann neben der Definition von Tabellen auch Daten zum Füllen der Tabellen enthalten. Häufig entsteht die Eingabedatei auch durch einen teilweisen/vollständigen "Dump" einer MySQL-Datenbank per Kommando "mysqldump". Es erzeugt SQL-Anweisungen, die mit dem "mysql"-Client ausgeführt werden können, um Struktur + Inhalt der Datenbank wieder zu erstellen: mysqldump -utom -pgeheim test > test-dump.sql # Dump von DB "test" erzeugen mysql -utom -pgeheim -e "DROP DATABASE test" # DB "test" löschen (Inhalt!) mysql -utom -pgeheim -e "CREATE DATABASE test" # DB "test" anlegen (leer) mysql -utom -pgeheim -Dtest < test-dump.sql # DB-Dump "test" einspielen Alternativ (Dump-Datei editieren) folgende Anweisungen am Anfang der Dumpdatei "test-dump.sql" hinzufügen: mysqldump -utom -pgeheim test > test-dump.sql # Dump von DB "test" erzeugen edit test-dump.sql # SQL-Dumpdatei editieren: DROP DATABASE test; # - DB "test" löschen (Inhalt! CREATE DATABASE IF NOT EXISTS test; # - DB "test" anlegen (leer) USE test; # - Auf DB "test" umschalten ... # - Restliche SQL-Anweisungen Und dann die Sicherung der Datenbank einspielen: mysql -utom -pgeheim < test-dump.sql HINWEIS: Bei einem SQL-Syntaxfehler wird die Verarbeitung sofort abgebrochen (außer Option "-f/--force" ist gesetzt). 1k) Client "mysql": Spezielle Optionen -------------------------------------- Die Optionen des "mysql"-Clients sind in Kurzform "-X" sowie in Langform "--XXX" angebbar, bei der Kurzform ist die GROSS/kleinschreibung relevant, bei der Langform nicht. Folgende Optionen des "mysql"-Client wurden in den vorherigen Abschnitten schon beschrieben: +----+--------------------------+--------------------------------------------+ | Opt| Langform | Bedeutung | +----+--------------------------+--------------------------------------------+ | -e | --execute="" | SQL-Anweisung ausführen | +----+--------------------------+--------------------------------------------+ | | --auto-rehash | Tab.+Spaltennamen vervollst. (nach USE) | | -A | --no-auto-rehash | Tab.+Spaltennamen NICHT vervollst. (alt) | | -A | --skip-auto-rehash | Tab.+Spaltennamen NICHT vervollst. (alt) | | -A | --disable-auto-rehash | Tab.+Spaltennamen NICHT vervollst. (neu) | +----+--------------------------+--------------------------------------------+ | | --sigint-ignore | Signal INT (Strg-C) ignorieren | +----+--------------------------+--------------------------------------------+ | | --connect-timeout= | Sek. bis autom. Verbindungstrenn. (STD: 0) | | | --reconnect | Bei Verb.verlust diese autom. wieder aufb. | | | --skip-reconnect | Bei Verb.verlust diese NICHT autom. aufb. | +----+--------------------------+--------------------------------------------+ | | --max-allowed-packet= | Max. Länge transf. Bef./Ergeb. (STD: 16MB) | | | --net-buffer-length= | Puffergröße TCP/IP+Socket-Komm. (STD: 16KB)| +----+--------------------------+--------------------------------------------+ | -U | --safe-updates | UPDATE- und DELETE-Anweisungen gegen | | | --i-am-a-dummy | Weglassen von WHERE und LIMIT schützen | +----+--------------------------+--------------------------------------------+ | | --select-limit= | Abbruch Erg.ausgabe ab N Zl. (STD: 1000) | | | --max-join-size= | Abbruch Erg.ausg. ab N Zl.komb. (STD: 1Mio)| +----+--------------------------+--------------------------------------------+ Folgende Optionen legen das Verhalten bei fehlerhaften SQL-Anweisungen fest (STD: Bei SQL-Syntaxfehler Verarbeitung der restlichen Anw. abbrechen): +----------+-----------------+-------------------------------------------+ | Kurzform | Langform | Bedeutung | +----------+-----------------+-------------------------------------------+ | -f | --force | Bei einem SQL-Fehler nicht abbrechen | | | --show-warnings | Warnungen nach jeder Anweisung anzeigen | +----------+-----------------+-------------------------------------------+ 1l) Client "mysql": Ausgabeformat-Optionen ------------------------------------------ Folgende Optionen legen das AUSGABEFORMAT fest: +----------+------------+-------------------------------------------+ | Kurzform | Langform | Bedeutung | +----------+------------+-------------------------------------------+ | -t | --table | ASCII-Rahmen (STD im Interaktiv-Modus) | | -B | --batch | Spalten TAB-getrennt (STD im Batch-Modus) | | -E | --vertical | vErtikale-Ausgabe (nützlich!) | | -H | --html | HTML-Format (1 Zeile ohne NL!) | | -X | --xml | XML-Format (mehrzeilig + eingerückt) | +----------+------------+-------------------------------------------+ | -r | --raw | Ohne Escape-Umwandlung ausgeben (*) | +----------+------------+-------------------------------------------+ (*) HINWEIS: Der Client "mysql" wandelt beim Einlesen Escape-Sequenzen wie \n \r \t \b \a \0 \\ \" \' in echte (Steuer-)Zeichen um. Bei der Ausgabe erfolgt die umgekehrte Umwandlung Steuerzeichen nach Escape-Sequenzen nur dann, wenn KEINE der Optionen -t, -X, -E, --raw angegeben wird. Standard-Ausgabeformate: * Interaktiver Modus (Eingabe-Gerät ist EIN Terminal) --> Option -t/--table Werte jeder Ausgabespalte werden auf grösste notwendige Breite formatiert und Ausgabe wird mit einem "ASCII-Rahmen" versehen. * Batch-Modus (Eingabe-Gerät ist KEIN Terminal) --> Option -B/--batch Werte jeder Ausgabespalte werden nicht auf einheitliche Breite formatiert, sondern durch je einen TABULATOR getrennt. TIP: Für das menschliche Auge ist das 1. Format angenehmer zu lesen, für den Computer ist das 2. Format besser zu verarbeiten. Bei breiten Tabellen ist das per Option "-E" aktivierte "vErtikale Format" sinnvoll. Die Spaltenwerte eines Datensatzes werden dann zeilenweise mit den Spaltennamen als Präfix ausgegeben und jeder Datensatz eingeleitet durch eine Zeile "*** N. row ***". *************************** 1. row *************************** nr: 77 vorname: heinz name: bayer *************************** 2. row *************************** nr: 88 vorname: Andrea name: Bayer *************************** 3. row *************************** nr: 99 vorname: Richard name: Seiler 3 rows in set (0.00 sec) TIP: Im "mysql"-Client durch Stmt-Abschluss "\G" (go) statt ";" auslösen. 1m) Client "mysql": Sonstige Optionen ------------------------------------- +----+-----------------------------+----------------------------------------+ | Opt| Langform | Bedeutung | +----+-----------------------------+----------------------------------------+ | | --column-names | Spaltennamen-Überschrift anzeig. (STD) | | -N | --skip-column-names | Spaltennamen-Überschrift weglassen | | -m | --column-type-info | Spaltentyp anzeigen (Metadaten) | +----+-----------------------------+----------------------------------------+ | -C | --compress | Datenübertr. zw. Cl. + Server komprim. | +----+-----------------------------+----------------------------------------+ | -# | --debug= | Debuglogfile gemäß schreiben | !!! | -T | --debug-info | Einige Debuginfo am Programmende ausg. | | | --debug-check | Einige Debuginfo am Programmende ausg. | +----+-----------------------------+----------------------------------------+ | | --default-character-set= | Standard-Zeichensatz | | | --character-sets-dir= | Verz. der Zeichensätze | +----+-----------------------------+----------------------------------------+ | -L | --line-numbers | Zeilennummer bei Fehler ausgeben (STD) | | | --skip-line-numbers | Zeilennummer bei Fehler NICHT ausgeb. | +----+-----------------------------+----------------------------------------+ | | --tee= | Ausgabe auf Datei zusätzlich | | | --no-tee | Ausgabe auf Datei wieder abschalten | | -n | --unbuffered | Ausgabe nach jeder Abfrage leeren | +----+-----------------------------+----------------------------------------+ | -w | --wait | Warten+neu versuch. Verb. aufzubauen | | | --reconnect | Bei Verb.verlust diese wieder aufbauen | | | --skip-reconnect | Bei Verb.verlust Client verlassen | +----+-----------------------------+----------------------------------------+ | -c | --comments | Kommentare zum Server senden (erhalten)| | | --skip-comments | Kommentare NICHT zum Server send (STD) | +----+-----------------------------+----------------------------------------+ | -G | --named-commands | Interne Client-Kmdos überall erlaubt | | -g | --disable-named-commands | Interne Client-Kmdos nur in 1.Zl erl. | -g | | --no-named-commands | Analog (veraltet) | +----+-----------------------------+----------------------------------------+ | | --delimiter= | SQL-Kmdo-Begrenzer def. (STD: ";") | | -i | --ignore-spaces | Leerzeichen nach Fkt.name ignorieren | +----+-----------------------------+----------------------------------------+ | | --pager= | Seitenweises Blätterkmdo. | | | --disable-pager | Kein seitenweises Blättern | | | --no-pager | Kein seitenweises Blättern (veraltet) | | | --prompt= | Prompt-Definition (STD: "mysql> ") | +----+-----------------------------+----------------------------------------+ | -o | --one-database | Nur -D zählt, USE ignoriert | +----+-----------------------------+----------------------------------------+ | -q | --quick | Abfrageergebnisse nicht cachen | +----+-----------------------------+----------------------------------------+ | | --secure-auth | Altes Protokoll (vor 4.1.1) verhindern | +----+-----------------------------+----------------------------------------+ 1n) Client "mysql": Umgebungsvariablen -------------------------------------- Folgende Umgebungsvariablen werden vom Client "mysql" ausgewertet: +--------------------+------------------------------------------------------+ | Umgebungsvariable | Bedeutung | +--------------------+------------------------------------------------------+ | MYSQL_DEBUG | Debug-Trace-Optionen | | MYSQL_HISTFILE | MySQL-History-Datei (STD: "$HOME/.mysql_history") | | MYSQL_HISTIGNORE | MySQL-History-Datei ignorieren | | MYSQL_HOME | Server-spezifische "my.cnf"-Datei | | MYSQL_HOST | Hostname | | MYSQL_PS1 | Client-Prompt-String (STD: "mysql> ") | | MYSQL_PWD | Passwort (unsicher!) | | MYSQL_TCP_PORT | TCP/IP-Port (STD: 3306) | | MYSQL_UNIX_PORT | Socket (bei "localhost" benutzt) | | MYSQL_GROUP_SUFFIX | | | DATA_VAR_MYSQL | | +--------------------+------------------------------------------------------+ | PAGER | Programm zum seitenweisen Blättern (--pager) | | EDITOR | Editor für interaktives Bearbeiten (1. Wahl) | | VISUAL | Editor für interaktives Bearbeiten (2. Wahl) | +--------------------+------------------------------------------------------+ | USER | Benutzer | | HOME | Heimatverzeichnis des Benutzers | | PATH | Shell-Suchpfad für Programme | | LD_RUN_PATH | Suchpfad für Bibliothek "libmysqlclient.so" | +--------------------+------------------------------------------------------+ | UMASK | Maske für neu angelegte Dateien | | UMASK_DIR | Maske für neu angelegte Verz. | | TZ | Lokale Zeitzone | +--------------------+------------------------------------------------------+ 1o) Client "mysql": Interne Befehle (Escape-Sequenzen) ------------------------------------------------------ Neben SQL-Anweisungen versteht der "mysql"-Client folgende INTERNEN BEFEHLE bzw. ihre Abkürzung in Form einer zweibuchstabigen Escape-Sequenz (ein ";" als Abschluss ist hier nicht notwendig, schadet aber auch nicht): +---------------+----+-------------------------------------------------------+ | Befehl | ESC| Bedeutung | +---------------+----+-------------------------------------------------------+ | ? [] | \? | Synonym für "help" (Argument ) | | CHARSET | \C | Zeichensatz einschalten (für binlog-Verarbeitung) | | CLEAR | \c | SQL-Anweisung abbrechen | | CONNECT [D H] | \r | Serververbindung neu aufbauen (Opt: und ) | | DELIMITER | \d | SQL-Kmdo-Begrenzer def. (Zeilenrest, STD: ";") | | EDIT | \e | Letzte SQL-Anweisung per $EDITOR/$VISUAL bearbeiten | | EGO | \G | SQL-Anweisung ausführen (vErtikale Anzeige) | | EXIT | \q | "mysql"-Client verlassen (auch Strg-C, Strg-D) | | GO | \g | SQL-Anweisung ausführen (STD: ASCII-Tabellen Anz.) | | HELP | \h | Diese Hilfe anzeigen (auch \?) | | NOPAGER | \n | Seitenweise Anzeige ausschalten (stdout) | | NOTEE | \t | Nicht mehr in Ausgabedatei schreiben | | NOWARNING | \w | Keine Warnungen nach jeder Anweisung anzeigen (STD) | | PAGER [] | \P | Seitenweise Anzeige ein (STD: $PAGER, z.B. "less") | | PRINT | \p | Aktuelle SQL-Anweisung ausgeben | | PROMPT []| \R | "mysql"-Client-Prompt festleg. (STD: "mysql> ") | | QUIT | \q | "mysql"-Client verlassen (auch Strg-C, Strg-D) | | REHASH | \# | TAB-Completion-Hash aufbauen | |RESETCONNECTION| \x | Verb. neu aufbauen (ab MY!5.7.3) | | SOURCE | \. | SQL-Datei einlesen und ausführen (Include) | | STATUS | \s | Server-Status anzeigen | | SYSTEM | \! | System-Kommando ausführen (Shell-Escape) | | TEE | \T | Ausgabedatei setzen (alles zusätzlich dorthin) | | USE | \u | Auf Default/Standard-Datenbank umschalten | | WARNING | \W | Warnungen nach jeder Anweisung anzeigen | +---------------+----+-------------------------------------------------------+ 1p) Client "mysql": Hilfe anzeigen ---------------------------------- Im Client "mysql" ist zu den Client-internen Befehlen und den SQL-Anweisungen jederzeit Hilfe anzeigbar (die Hilfetexte stehen in der internen Verwaltungs-Datenbank "mysql" in den Tabellen "mysql.help_..."). +----------------------------+------------------------------------+ | Hilfe-Kommando | Bedeutung | +----------------------------+------------------------------------+ | ? | Liste der Client-internen Befehle | | \? | Liste der Client-internen Befehle | | \h | Liste der Client-internen Befehle | | HELP | Liste der Client-internen Befehle | +----------------------------+------------------------------------+ | HELP HELP | Hilfe zur Hilfe | | HELP | Hilfe zu SQL-Anweisung (z.B. JOIN) | +----------------------------+------------------------------------+ | HELP CONTENTS | Hilfethemenliste (Topics) | | HELP | Hilfe zu Thema aus Themenliste | | ... Account Management | Hilfethema | | ... Administration | " | | ... Compound Statements | " | | ... Data Definition | " | | ... Data Manipulation | " | | ... Data Types | " | | ... Functions | " | | ... Functions and Modifiers for Use with GROUP BY " | | ... Geographic Features | " | | ... Help Metadata | " | | ... Language Structure | " | | ... Plugins | " | | ... Procedures | " | | ... Storage Engines | " | | ... Table Maintenance | " | | ... Transactions | " | | ... Triggers | " | | ... User-Defined Functions | " | | ... Utility | " | +----------------------------+------------------------------------+ 1q) Client "mysql": Prompt-Definition ------------------------------------- Das Aussehen des Prompts kann definiert werden per (STD: "mysql> "): PROMPT # Escape-Sequenzen im erlaubt (ohne Quotierung "...") PROMPT # Rückkehr zum STD.-Prompt "mysql> " (Leerz. am Ende!) Beispiel (mit Escape-Sequenzen \h=host, \u=user, \d=database): PROMPT \u@\h:\d>\ # Leerzeichen am Ende! "Escape-Sequenzen" um spezielle Zeichen oder variable Werte anzuzeigen: +----+-----------+---------------------------------------------------+ | Esc| Name | Beschreibung | +----+-----------+---------------------------------------------------+ | \ | | Leerzeichen (dem Backslash folgt ein Leerzeichen) | | \_ | | Leerzeichen (Unterstrich!) | | \" | | Gänsefüßchen | | \' | | Hochkomma | | \\ | | Backslash-Zeichen "\" | +----+-----------+---------------------------------------------------+ | \S | Semicolon | Semikolon ";" | | \t | tabulator | Tabulator-Zeichen | | \n | newline | Zeilenvorschub | +----+-----------+---------------------------------------------------+ | \c | count | Anweisungszähler (pro Anweisung um 1 erhöht) | | \l | delimiter | Aktueller Kmdo-Begrenzer (STD: ";", MY!5.0.25) | +----+-----------+---------------------------------------------------+ | \C | connection| Aktuelle Verbindungs-ID (ab MY!5.7.6) | | \d | database | Default-Datenbank | | \h | host | MySQL-Server | | \p | port | Aktueller TCP/IP-Port oder Socket-Datei | | \u | user | Benutzername | | \U | User | Vollständiger Benutzername "user_name@host_name" | | \v | version | MySQL-Server Version | +----+-----------+---------------------------------------------------+ | \D | date | Aktuelles Datum "DD.MM.YYYY" | | \O | mOnth | Aktueller Monat 3 Zeichen (Jan, Feb, ...) | | \o | month | Aktueller Monat in numerischer Form (1 ... 12) | | \w | weekday | Aktueller Wochentag 3 Zeichen (Mon, Tue, ...) | | \Y | Year | Aktuelles Jahr (4 Ziffern) | | \y | year | Aktuelles Jahr (2 Ziffern) | +----+-----------+---------------------------------------------------+ | \m | minutes | Minuten der aktuellen Zeit (00-59) | | \P | PM | Vormittag/Nachmittag der aktuellen Zeit (AM/PM) | | \R | Realtime | Stunde der aktuellen Zeit (00-23) | | \r | realtime | Stunde der aktuellen Zeit (00-12) | | \s | seconds | Sekunden der aktuellen Zeit (00-59) | +----+-----------+---------------------------------------------------+ | \Z | | Zeichen "Z" für jedes hier nicht aufgeführte Z. | +----+-----------+---------------------------------------------------+ TIP: Prompt in benutzerspez. Konfig-Datei definieren (Leerzeichen am Ende!): [mysql] prompt = "\u@\h:\d>\_" TIP: Prompt auf der Kmdo-Zeile definieren: mysql --prompt="\u@\h:\d>\_" ... 1r) Grafische MySQL-Programme (GUI) ----------------------------------- Neben den bedienungstechnisch relativ einfachen aber trotzdem leistungsfähigen Kommandozeilenprogrammen sind noch eine Reihe GUI-basierter MySQL-Clients verfügbar, die per Maus bedienbar sind. Diese Programme müssen getrennt installiert werden, sie sind nicht Bestandteil des MySQL-Servers oder -Clients: +---------------------------+------------------------------------------------+ | GUI-Programm | Beschreibung | +---------------------------+------------------------------------------------+ | MySQL Workbench | GUI-Client (frei, inkl. ER-Designer) | | MySQL GUI Tools | Zusammenfassung von: | | MySQL Administrator | Server-Konfiguration, -Verwaltung, -Wartung | | MySQL Query Browser | Grafischer SQL-Client | | MySQL Migration Toolkit | Schemata + Daten aus anderen DB importieren | +---------------------------+------------------------------------------------+ | phpMyAdmin | Webbasiertes DB-Management (Webserver nötig) | | mysql-admin | Webbasiertes DB-Management (Webserver nötig) | | Adminer | Webbasiertes DB-Management (Webserver nötig) | +---------------------------+------------------------------------------------+ | winMySQLadmin | GUI-Client (Windows, STD., MUSS lokal laufen!) | | SQL-Front | GUI-Client (Windows, kommerziell) | | SQLyog | GUI-Client (Windows, kommerziell) | | MySQLManager | GUI-Client (Windows, kommerziell) | | Navicat for MySQL | GUI-Client (Windows, kommerziell) | | EMS MySQL Manager | GUI-Client (Windows, kommerziell) | | MyAdmin | GUI-Client (Windows, kommerziell) | | HeidiSQL (MySQL-Front) | GUI-Client (Windows, Linux/MacOS per Wine) | | SQuirrel | GUI-Client (Java: Windows, MacOS, Linux) | | TOAD | GUI-Client (Windows, kommerziell, viele DB) | +---------------------------+------------------------------------------------+ | Knoda | GUI-Client analog Access (Linux) | | Rekall | GUI-Client (Linux) | | Ksql | GUI-Client (Linux) | | KMySQLadmin | GUI-Client (Linux) | | Emma | GUI-Client (Linux) | +---------------------------+------------------------------------------------+ | MySQL Control Center | Weiterentw. von MySQLGUI (mysqlcc, veraltet) | | MySQLGUI | Grafischer SQL-Client (veraltet) | | mysql-navigator | Grafischer SQL-Client (veraltet) | +---------------------------+------------------------------------------------+ HINWEIS: Diese Liste erhebt keinen Anspruch auf Aktualität und Vollständigkeit. 2) Syntax --------- 2a) Leerraum und Formatierung ----------------------------- Leerraum + Zeilenvorschübe + GROSS/kleinschreibung der SQL-Schlüsselworte ist in SQL-Anweisungen (SQL-Statements) irrelevant. Allerdings empfiehlt sich die übliche Konvention: SQL-Schlüsselworte GROSS schreiben! Erst ";" oder "\g" (go) bzw. "\G" (ego = Option -E = --vertical = vErtikale Ausgabe) schließen eine SQL-Anweisung ab. Ausnahme: Interne "mysql"-Client Befehle wie "USE " (besser nicht daran gewöhnen ;-). Es ist sinnvoll, SQL-Anweisungen per Einrückung und Zeilenvorschübe übersichtlich zu formatieren, um eine optimale Lesbarkeit zu erreichen. Die Übersetzungs- und Ausführungsgeschwindigkeit der Anweisung verringert sich dadurch nicht. Insbesondere lange SQL-Anweisungen der Übersicht halber sinnvoll auf mehrere Zeilen umbrechen und einrücken, z.B.: UPDATE TABLE pers SET nr = 7, vorname = "Heinz", name = "Bayer"; besser so formatieren: UPDATE TABLE pers SET nr = 7, vorname = "Heinz", name = "Bayer"; 2b) Zeichenketten (Strings) und Quotierung ------------------------------------------ Alle konstanten Werte außer Zahlen (d.h. Text-, Blob-, Bit-, Set-, Enum-, Datum- und Zeitwerte) sind in "..." oder '...' einzuschließen (zu QUOTIEREN). Prinzipiell sind aber auch Zahlen so darstellbar, d.h. ALLE Werte dürfen quotiert werden (einheitliches Prinzip). Um in "..." das Zeichen " und in '...' das Zeichen ' einzutragen, dieses einfach verdoppeln oder mit "\" quotieren: SELECT "Hallo", 'Welt'; # --> Hallo Welt SELECT Hallo, Welt; # --> FEHLER! SELECT 1, 23, 456; # --> 1 23 456 SELECT '1', "23", '456'; # --> 1 23 456 SELECT 'Don''t'; # --> Don't SELECT 'Don\'t'; # --> Don't SELECT "Don't"; # --> Don't SELECT "Er sagte: ""..."""; # --> Er sagte: "..." SELECT "Er sagte: \"...\""; # --> Er sagte: "..." SELECT 'Er sagte: "..."'; # --> Er sagte: "..." HINWEIS: Zwischen Zeichenketten in "..." und '...' gibt es KEINEN Unterschied (wie in anderen Programmiersprachen z.B. bei Escape-Sequenzen \C), allerdings ist nur '...' ANSI-SQL-kompatibel ("..." wird dort verwendet, um Bezeichner mit Sonderzeichen zu quotieren, in MySQL erfolgt dies durch Backquotes `...`). 2c) Escape-Sequenzen -------------------- Escape-Sequenzen (Maskierungszeichen) \C zur Darstellung von Sonderzeichen in Zeichenketten der Form "..." und '...': +-----+-----------------------------------------------------+ | ESC | Bedeutung | +-----+-----------------------------------------------------+ | \0 | ASCII 0-Zeichen (NUL) | | \' | Einfaches Hochkomma in '...' (auch '' in '...') | | \" | Doppeltes Hochkomma in "..." (auch "" in "...") | | \\ | Backslash (\) | +-----+-----------------------------------------------------+ | \a | Alert (Ton) | | \b | Backspace | | \f | Formfeed (Seitenvorschub beim Drucker) | | \n | Newline (Linefeed) | | \r | Carriage Return | | \t | Tabulator | | \v | Vertikaler Tabulator | | \Z | ASCII 26 (Control-Z, unter Windows evtl. notwendig) | +-----+-----------------------------------------------------+ | \% | %-Zeichen (Zeichen "%" selbst in LIKE) | | \_ | _-Zeichen (Zeichen "_" selbst in LIKE) | +-----+-----------------------------------------------------+ 2d) Zahlen ---------- Wird eine Zeichenkette in einem Zahlenzusammenhang verwendet, dann wird das max. Anfangsstück (PRÄFIX), das noch wie eine Zahl aussieht, AUTOMATISCH in diese Zahl KONVERTIERT (und eine Warnung ausgegeben). ACHTUNG: Sieht eine Zeichenkette überhaupt nicht wie eine Zahl aus, dann entspricht sie der Zahl 0 (Null). SELECT 123 + 0, -5 + 0, 1e+5 + 0; # --> 123, -5, 100000 SELECT "123" + 0, "-5" + 0, "1e+5" + 0; # --> 123, -5, 100000 SELECT "123def" + 0, "-5ghi" + 0, "1e+5kjl" + 0; # --> 123, -5, 100000 SELECT "def" + 0, 100 + "ghi"; # --> 0, 100 Fließkommazahlen sind (unabhängig von Sprach- und Collation-Einstellungen) immer mit DEZIMALPUNKT zu schreiben (Dezimalkomma und Tausendertrennzeichen sind nicht erlaubt). Die Ausgabe von Dezimalkommas ist per FORMAT(, ) erreichbar. Rundung auf feste Anzahl Nachkommastellen oder ganze Zahl ist mit ROUND(, ) erreichbar. SELECT 123.456 + 0, 123,456 + 0; # --> 123.456, 123, 456 SELECT FORMAT(123.456, 2), FORMAT(123.456, 0); # --> 123.46, 123 SELECT ROUND(123.456, 2), ROUND(123.456, 0); # --> 123.46, 123 Hexadezimalzahlen bzw. -bytes folgendermaßen schreiben: 0xaffe... # Variante A (0X1010 nicht erlaubt!) X'affe...' # Variante B (x"1010" nicht erlaubt!) x'affe...' # Variante C (X"1010" nicht erlaubt!) Binärzahlen und Bitfelder folgendermaßen schreiben: 0b1010... # Variante A (0B1010 nicht erlaubt!) b'1010...' # Variante B (b"1010" nicht erlaubt!) B'1010...' # Variante C (B"1010" nicht erlaubt!) Zahlen mit führender 0 sind Dezimalzahlen (keine Oktaldarstellung): SELECT 0123 + 0, 0815 + 0; # --> 123, 815 2e) Datenbank-Objekte --------------------- MySQL kennt folgende "Datenbank-Objekte" (oder kurz "Objekte") und "Syntax-Elemente". Die Platzhalter für BEZEICHNER dieser Objekte stehen in diesem Skript in SQL-Anweisungen immer in spitzen Klammern <...> und müssen durch einen konkreten Bezeichner ersetzt werden: +--------------------+-------------+----------------------------------------+ | Objekt | Platzhalter | Bedeutung | +--------------------+-------------+----------------------------------------+ | Alias | | Weiterer Name für ein Datenbank-Objekt | | Column | | Tabellenspalte | | Database | | Datenbank (Schema) | | Event | | Ereignis (einmalig oder periodisch) | | Index | | Index einer Tabelle | | Log File | | Logdatei | | Partition | | Horizontale Zerlegung einer Tabelle | | Prepared Statement | | Vorkompilierte Anweisung | | Privilege | | Benutzerrecht | | Statement | | SQL-Anweisung | | Stored Function | | Funktion (Rückgabewert) | | Stored Procedure | | Prozedur (kein Rückgabewert) | | Table | | Tabelle | | Tablespace | | Datei für Tab.daten (analog Partition) | | Trigger | | Trigger einer Tabelle | | User | | Benutzeraccount | | View | | Sicht (vordef. gespeicherte Abfrage) | +--------------------+-------------+----------------------------------------+ | Datatype | | Datentyp (z.B. INT, CHAR, VARCHAR, ...)| | Character Set | | Zeichensatz (z.B. latin1, utf8, ...) | | Collation | | Sortierregel (z.B. latin1_german_ic) | | Directory Path | | Verz.pfad (absolut) | | Transcodierung | | Zeichensatzcodierung | +--------------------+-------------+----------------------------------------+ 2f) Identifier (Bezeichner) --------------------------- Bezeichner dürfen standardmäßig aus den Zeichen A-Z, a-z, 0-9, _ und $ bestehen (d.h. insbesondere KEINE Leerzeichen!), führende Ziffern sind nicht erlaubt. Mit der Schreibweise `...` (QUOTIERUNG mit Backquotes, NICHT '...' oder "...") sind auch beliebige andere Zeichen verwendbar (NICHT empfohlen!). SELECT Name FROM test; # OK (STD-Form) SELECT `Name` FROM test; # OK (STD-Form) SELECT _Name_ FROM test; # OK (STD-Form) SELECT $Name FROM test; # OK (STD-Form) SELECT Und_Ein_Name$ FROM test; # OK (STD-Form) SELECT `Name mit Leer zeichen` FROM test; # OK (Sonderzeichen) SELECT `Name mit Sonderzeichen äöüß` FROM test; # OK (Sonderzeichen) Kollidiert ein BEZEICHNER (Objektname) mit einem SQL-Schlüsselwort, so kann er in `...` (Backquotes!) gesetzt dennoch verwendet werden (QUOTIERUNG). Die normale String-Quotierung mit "..." oder '...' funktioniert hier nicht! CREATE TABLE alter (...); # FALSCH (Kollision)! CREATE TABLE `alter` (...); # OK (Quotes notwendig) SELECT user, host FROM mysql.user; # OK (STD-Form ohne Quotes) SELECT `user`, `host` FROM `mysql`.`user`; # OK (Quotes überflüssig) SELECT `user`, `host` FROM `mysql.user`; # FALSCH (Namen einzeln quot.)! SELECT "user", "host" FROM "mysql"."user"; # FALSCH (String)! SELECT 'user', 'host' FROM 'mysql'.'user'; # FALSCH (String)! HINWEIS: In generierten SQL-Anweisungen (z.B. per "mysqldump") werden ALLE BEZEICHNER grundsätzlich prophylaktisch in `...` gesetzt (auch wenn das gar nicht notwendig wäre). Maximal erlaubte Bezeichner-Länge von Datenbank-Objekten und Namen: +-----+------------+ | Max | Bezeichner | +-----+------------+ | 64 | Datenbank | | 64 | Tabelle | | 64 | Spalte | | 64 | Routine | | 255 | Alias | +-----+------------+ | 60 | Host | | 16 | Benutzer | | 41 | Passwort | +-----+------------+ Zugriff auf die Objekte Datenbank, Tabelle, Spalte und Stored Procedure erfolgt durch "relative" oder "vollqualifizierte" (full qualified) Bezeichner (im "mysql"-Client per -Taste = TAB-Completion automatisch vervollständigbar): +--------------------+-----------------------------------------------------+ | Bezeichner | Bedeutung | +--------------------+-----------------------------------------------------+ | . | A) Vollständiger Pfad (immer OK!) | | .. | Analog | | ..* | Analog (ALLE Spalten der Tabelle) | +--------------------+-----------------------------------------------------+ | | B) Relativ zu Default/Standard-Datenbank (USE ) | | . | Analog | | .* | Analog (ALLE Spalten der Tabelle) | +--------------------+-----------------------------------------------------+ | | C) Analog B) in INSERT/SELECT/UPDATE/DELETE | | | Relativ zu EINER Tabelle immer OK! | | | Bei MEHREREN verknüpften Tabelle nur bei | | | pro Tabelle eindeutigen Spaltennamen OK. | | | (mit eindeutigen Spalten-Aliasen auch OK) | | * | Analog (ALLE Spalten einer Tabelle) | +--------------------+-----------------------------------------------------+ | . | D) Vollständiger Pfad (immer OK!) | | | E) Relativ zu Default/Standard-Datenbank (USE ) | +--------------------+-----------------------------------------------------+ SELECT test.pers.name FROM test.pers; # A) OK (Datenbank "test") USE test; # Default-DB "test" auswählen SELECT pers.name FROM pers; # B) OK (Datenbank "test") SELECT name FROM pers; # C) OK bei EINER Tabelle (DB "test") SELECT name FROM pers, age; # C) Problem bei Sp.namen-Kollision SELECT pers.name, age.name FROM pers, age; # C) OK (auch bei Sp.Kollision) SELECT p.name, a.name FROM pers p, age a; # C) OK (Aliase auch bei Sp.Koll.) ACHTUNG: Wird im Fall C) zu einer beteiligten Tabelle nachträglich eine Spalte hinzugefügt, die GLEICHNAMIG zu einer Spalte einer anderen beteiligten Tabelle ist, führt das in bereits vorhandenen SQL-Anweisungen zu Syntaxfehlern, wenn darin auf diese Spalte zugegriffen wird. Daher am besten mit Variante B) arbeiten und zur Abkürzung ALIASE einführen. HINWEIS: Die Bestandteile eines relativen oder vollqualifizierten Bezeichners müssen GETRENNT mit `...` quotiert werden: SELECT DISTINCT `mysql`.`user`.`host` FROM `mysql`.`user`; # OK SELECT DISTINCT `mysql.user.host` FROM `mysql.user`; # FALSCH! SELECT DISTINCT mysql.user.host FROM `mysql`.`user`; # OK Analog MÜSSEN die beiden Bestandteile "User" und "Host" eines Benutzernamens "User@Host" GETRENNT quotiert werden (allerdings mit "..." oder '...', da es sich nicht um Bezeichner, sondern um externe Namen handelt): CREATE USER "hans"@"localhost"; # OK CREATE USER 'hans'@'localhost'; # OK CREATE USER `hans`@`localhost`; # FALSCH CREATE USER "hans@localhost"; # FALSCH! CREATE USER 'hans@localhost'; # FALSCH! CREATE USER `hans@localhost`; # FALSCH! CREATE USER hans@localhost; # FALSCH! 2g) GROSS/kleinschreibung ------------------------- Beim Vergleichen und beim Sortieren von Daten in Tabellen ignoriert MySQL normalerweise die GROSS/kleinschreibung außer in folgenden Fällen: * BINARY vor einem String in den Vergleichen = != <> <=> < <= > >=, BETWEEN, IN, LIKE, RLIKE/REGEX und in ORDER BY erzwingt die Beachtung der GROSS/kleinschreibung bei Vergleichen / Sortieren. * Die Datentypen BINARY, VARBINARY, TINYBLOB, BLOB, MEDIUMBLOB und LONGBLOB erzwingen Beachtung der GROSS/kleinschreibung beim Vergleichen / Sortieren. Die GROSS/kleinschreibung in SQL-Anweisungen ist meist egal AUSSER bei den Bezeichnern von Datenbanken, Tabellen, Views und Logfilegroups unter einem Betriebssystem mit case-sensitivem Dateisystem (da als Verz./Datei abgelegt). Die Namen von Triggern, Labeln (Marken) und Benutzern sowie Passwörter sind immer "case-sensitive". Es gibt daher gewisse KONVENTIONEN bgzl. GROSS/kleinschreibung, an die man sich halten sollte. +--------------------------+-----------------------------------+-------------+ | Sprachelement | GROSS/kleinschreibung relevant | Konvention | +--------------------------+-----------------------------------+-------------+ | Datenbank-Bezeichner | JA (Linux), NEIN (Windows) | klein | | Tabellen-Bezeichner | JA (Linux), NEIN (Windows) | klein | | View-Bezeichner | JA (Linux), NEIN (Windows) | klein | | Dateiname/pfad | JA (Linux), NEIN (Windows) | klein | | Logfilegroup-Name | JA (Linux), NEIN (Windows) | klein | | Tablespace-Name | JA (Linux), NEIN (Windows) | klein | +--------------------------+-----------------------------------+-------------+ | Host-Name | NEIN | klein | | Benutzer-Name | JA | klein | | Passwort | JA | --- | +--------------------------+-----------------------------------+-------------+ | Trigger-Bezeichner | JA | klein | | Label (Marke) | JA | GROSS | +--------------------------+-----------------------------------+-------------+ | SQL-Schlüsselwort | NEIN | GROSS | | SQL-Funktions-Bezeichner | NEIN | GROSS | | Spalten-Bezeichner | NEIN | klein | | Index-Bezeichner | NEIN | klein | | Variablen-Bezeichner | NEIN (ab MY!5.0), JA (bis MY!4.1) | klein | | Prepared-Stmt-Bezeichner | NEIN | klein | | Partitions-Bezeichner | NEIN | klein | | Prozedur-Bezeichner | NEIN | klein | | Funktions-Bezeichner | NEIN | klein | | Event-Bezeichner | NEIN | klein | +--------------------------+-----------------------------------+-------------+ | String-Vergleich | JA/NEIN (abhängig von Collation) | --- | | RegEx-Vergleich | JA/NEIN (abhängig von Collation) | --- | +--------------------------+-----------------------------------+-------------+ | Tabellen-Alias | NEIN | klein | | Spalten-Alias | NEIN | klein | +--------------------------+-----------------------------------+-------------+ TIP: SQL-Schlüsselworte aus Gründen der besseren Lesbarkeit und der einfacheren Suchmöglichkeit IMMER GROSS schreiben (Konvention): select * from pers order by nr asc; # Schlecht lesbar SELECT * FROM pers ORDER BY nr ASC; # Gut lesbar In diesem Skript werden daher alle SQL-Schlüsselworte GROSS, hingegen alle Datenbank-, Tabellen-, Spalten- und sonstige Bezeichner klein geschrieben. 2h) Kommentare -------------- Es gibt folgende Möglichkeiten, Kommentare in den SQL-Quelltext einzubauen: +---------------+----------+-------------------------------------------------+ | Syntax | Typ | Beschreibung | +---------------+----------+-------------------------------------------------+ | -- ... | ANSI-SQL | Bis Zeilenende (Leerz. nach "--" nötig!, MY!) | | #... | Shell | Bis Zeilenende (MY!) | | /*...*/ | C | Beliebig viele Zeilen | +---------------+----------+-------------------------------------------------+ | /*! ...*/ | C | Inhalt NUR von MySQL ausgeführt (MY!) | | /*!NXXYY ...*/| C | Inhalt ab MySQL-Version N.XX.YY ausgeführt (MY!)| +---------------+----------+-------------------------------------------------+ | --... | ANSI-SQL | NICHT möglich (Leerz. nach "--" nötig!, MY!) | | //... | C++ | NICHT möglich! | +---------------+----------+-------------------------------------------------+ Die ersten 3 Kommentartypen werden vom "mysql"-Client VOR dem Transfer einer SQL-Anweisung zum MySQL-Server ENTFERNT. Sollen sie doch mit übertragen und somit in den Logdateien abgelegt werden, dann den Option "--comments" beim Aufruf des "mysql"-Client angeben (STD: --skip-comments). Kommentare der Form /*!...*/ werden grundsätzlich an den MySQL-Server übertragen und stehen somit immer in den Logdateien. Beispiele: SHOW TABLES; # Kommentar bis zum Zeilenende # OK SHOW TABLES; #Kommentar bis zum Zeilenende # OK SHOW TABLES; -- Kommentar bis zum Zeilenende # OK SHOW TABLES; --Kommentar bis zum Zeilenende # FEHLER (Leerz. fehlt!) SHOW TABLES; // Dies ist KEIN Kommentar! # FEHLER (nicht erlaubt) SHOW /* mehrzeiliger # OK (mehrzeiliger Kommentar) Kommentar # OK (mehrzeiliger Kommentar) */ TABLES; # OK (mehrzeiliger Kommentar) SELECT * FROM user /*! STRAIGHT_JOIN */ host # Von MySQL ausgeführt, ON user.Host = host.Host; # von anderen DB nicht CREATE /*!32302 TEMPORARY */ TABLE t1 (a INT); # Ab MY!3.23.02 ausgeführt CREATE /*!99999 Kommentar */ TABLE t2 (a INT); # NIE ausgef. aber erhalten 3) Typische MySQL-Datenbankbefehle (Tutorial) --------------------------------------------- Dieser (lange) Abschnitt bietet eine Einführung in die wichtigsten SQL- Kommandos von MySQL anhand von Beispielen. Verwendet werden darin die beiden Datenbanken "first" und "test" sowie mehrere Tabellen (z.B. "pers" und "age"). Eine ausführliche Beschreibung dieser und anderer SQL-Anweisungen ist in den folgenden Kapiteln und in --> mysql-admin-HOWTO.txt zu finden. HINWEIS: SQL-Anweisungen dürfen auf mehrere Zeilen umbrochen werden und sind immer mit ";" (oder "\g" bzw. "\G") abzuschließen. TIP: Zu jeder neuen Datenbank einen EIGENEN MySQL-Benutzer (hier: "tom") anlegen, ihm ein Passwort (hier: "geheim") sowie geeignete Zugriffsrechte (hier: GRANT ALL PRIVILEGES = sämtliche Rechte) geben. Er darf dann nur mit dieser Datenbank arbeiten (sie muss dazu noch gar nicht existieren!): CREATE USER "tom"@"localhost"; # --> Leeres Passwort, keine Zugriffsrechte # GRANT ALL PRIVILEGES # Alle Zugriffsrechte... ON first.* # ...auf alle Objekte der Datenbank "first" TO "tom"@"localhost" # ...für Benutzer "tom@localhost" IDENTIFIED BY "geheim" # ...mit Passwort "geheim" WITH GRANT OPTION; # ...mit Rechteweitergabe (optional) Passwort nachträglich ändern: SET PASSWORD FOR "tom"@"localhost" = PASSWORD("geheim"); HINWEIS: Seit MY!5.6 ist wg. dem Plugin "validate_password" standardmäßig ein Passwort mit folgenden Eigenschaften nötig: * Mindestlänge 8 Zeichen * Mindestens ein Grossbuchstabe * Mindestens ein Kleinbuchstabe * Mindestens eine Ziffer * Mindestens ein Sonderzeichen (außer Buchstabe und Ziffer) TIP: Vorher "CREATE USER" ist nicht unbedingt notwendig, schadet aber auch nicht, da das GRANT-Statement den Benutzer automatisch anlegt, sofern er noch nicht existiert (solange SQL-Modus "NO_AUTO_CREATE_USER" nicht gesetzt ist). TIP: Username == Datenbankname != Tabellenname != Spaltenname wählen, sonst besteht Verwechslungsgefahr (oder mit Präfix "u_" = User, "d_" = Database, "t_" = Table, "c_" = Column arbeiten). HINWEIS: Manche DB-Systeme legen zu jeder Datenbank automatisch einen Benutzer gleichen Namens an, der alle Rechte bzgl. dieser Datenbank zugewiesen bekommt (PostgreSQL, Oracle). MySQL kennt dieses Standard-Verhalten nicht. Datenbank (Schema) "first" anlegen (meist als MySQL-Administrator "root"): CREATE DATABASE first; # Fehler falls schon existent CREATE SCHEMA first; # (analog) CREATE DATABASE IF NOT EXISTS first; # Nur falls noch nicht existent (MY!) CREATE SCHEMA IF NOT EXISTS first; # (analog MY!) Datenbank (Schema) umbenennen ("root" oder Besitzer, nur von MY!5.1.7 bis MY!5.1.23 verfügbar da zu gefährlich!): RENAME DATABASE first TO new; # "first" --> "new" umbenennen RENAME SCHEMA new TO first; # (analog umgekehrt) Benutzer "tom" nimmt per "mysql"-Client Verbindung mit dem MySQL-Server (lokal) auf und wählt beim Verbindungsaufbau "first" als Default/Standard-Datenbank aus: mysql -utom -p # Password interaktiv abgefragt (sicher!) mysql -utom -pgeheim # Keine Default-Datenbank --> "USE first" mysql -utom -pgeheim -Dfirst # Default-Datenbank --> "first" mysql -utom -pgeheim first # Default-Datenbank --> "first" mysql -utom -pgeheim -hglasgow # MySQL-Server auf Rechner "glasgow" Abfrage der Verbindungsdaten (Klammern "()" notwendig, da Funktionsaufrufe): SELECT DATABASE(); # Standard-Datenbank (oder "NONE") SELECT SCHEMA(); # (analog) SELECT USER(); # Benutzer-Anmeldung @ != SELECT CURRENT_USER(); # Angemeld. Benutzer (evtl. anonym) SELECT SESSION_USER(); # (analog) SELECT SYSTEM_USER(); # (analog) SELECT CONNECTION_ID(); # Sitzungs-ID (Thread, Prozess) SELECT VERSION(); # MySQL-Server Version Abfrage des Server-Status (Database, User, Server Version, Connection-ID, Character set, Protocol Version, Connection type, UNIX Socket, Uptime, ...): STATUS # Nur im "mysql"-Kommandozeilenclient vorhanden \s # (analog) Vorhandene Datenbanken auflisten: SHOW DATABASES; # Alle Datenbanknamen auflisten SHOW SCHEMAS; # (analog) SHOW DATABASES LIKE "my%"; # Nur DB-Namen mit "my" am Anfang SHOW SCHEMAS LIKE "my%"; # (analog) Als Default/Standard-Datenbank (Schema) "first" auswählen. Da es sich um einen internen "mysql"-Clientbefehl handelt, darf der abschließende ";" weggelassen werden (besser gar nicht erst daran gewöhnen!). Alle Bezeichner (Objektnamen) ohne weitere Qualifizierung beziehen sich dann auf diese Datenbank (dieses Schema) --> 2e) Datenbank-Objekte USE first # Variante A (ohne ";") USE first; # Variante B (mit ";") Tabellen einer Datenbank auflisten: SHOW TABLES; # Alle aus Standard-DB "first" SHOW TABLES FROM mysql; # Alle aus Verwaltungs-DB "mysql" SHOW TABLES LIKE "a%"; # Mit Name "a..." aus Standard-DB "first" SHOW TABLES FROM mysql LIKE "%a%"; # Mit Name "...a..." aus DB "mysql" Tabelle "pers" (bedingt wenn sie noch nicht existiert) in Default-DB anlegen (3 Spalten, im 2. Fall mit einer anderen Engine statt Standard-Engine "InnoDB", Spalten ohne/mit Defaultwert und mit NULL erlaubt/nicht erlaubt): CREATE TABLE pers ( # STD-Engine = InnoDB nr INT NOT NULL, # Komma ( NULL verboten) vorname VARCHAR(30), # Komma (STD: NULL erlaubt) name VARCHAR(30) # KEIN Komma! (STD: NULL erlaubt) ) COMMENT = "Personen"; # Kommentar zur Tabelle CREATE TABLE IF NOT EXISTS pers ( # Kein Fehler falls schon existent nr INT NOT NULL, # NULL verboten vorname VARCHAR(30) DEFAULT "", # Defaultwert leere Zeichenkette name VARCHAR(30) NULL # NULL explizit erlaubt (sowieso STD) ) ENGINE = "MyISAM"; # Engine MyISAM (alt: TYPE statt ENGINE) HINWEIS: Jedes DB-Objekt hat automatisch einen "Besitzer", nämlich den User, der es anlegt. Dieser User hat automatisch alle Rechte daran und kann es z.B. umbenennen und auch wieder löschen. HINWEIS: Die Spalten-REIHENFOLGE einer Tabelle ist nur relevant bei: * Abfragen mit "*" * Speichern mit INSERT INTO VALUES (...) ohne Liste von Spaltennamen (irrelevant bei INSERT INTO (, ...) VALUES (...)) * Mehr als einer TIMESTAMP-Spalten in einer Tabelle (ERSTE TIMESTAMP-Spalte wird bei jeder Datensatz-Änderung aktualisiert). Temporäre Tabelle anlegen: CREATE TEMPORARY TABLE pers ( # nr INT DEFAULT 0 # vorname VARCHAR(30), # name VARCHAR(30) # ) CHARACTER SET latin1; # Standard-Zeichensatz für Textspalten HINWEIS: Temporäre Tabellen * Dürfen genauso heißen wie echte Tabellen. * ÜBERDECKEN evtl. vorhandene gleichnamige echte Tabelle, bis sie wieder gelöscht werden. * Sind SITZUNGSBEZOGEN (d.h. pro Sitzung gleicher Name verwendbar, ohne dass es zu Kollisionen kommt). * Werden am Ende einer Sitzung automatisch entfernt, sofern nicht vorher manuell gelöscht (per DROP TEMPORARY ). --> mysql-admin-HOWTO.txt --> 8a) Temporäre Tabellen Tabellenstruktur anzeigen (Spalten + Datentypen): DESCRIBE pers; # Variante A DESCRIBE pers name; # Variante B (nur Spalte "name") DESC pers; # Variante C DESC pers name; # Variante D (nur Spalte "name") EXPLAIN pers; # Variante E SHOW COLUMNS FROM pers; # Variante F SHOW TABLE pers; # FEHLER: Gibt es nicht! Vollständige Tabellen-Definition anzeigen (mit Engine und allen Einstellungen): SHOW CREATE TABLE pers; # Ausgabe horizontal SHOW CREATE TABLE pers\g # Ausgabe horizontal (go) SHOW CREATE TABLE pers\G # Ausgabe vertikal (ego, Option -E) SHOW TABLE pers; # FEHLER: Gibt es nicht! Alle Datensätze einer Tabelle anzeigen (aktuell noch leer): SELECT * FROM pers; # Alle Spalten in Def.reihenfolge SELECT nr, vorname, name FROM pers; # (analog) SELECT vorname, nr, name FROM pers; # Alle Spalten in anderer Reihenfolge SELECT vorname, name FROM pers; # Nur ausgewählte Spalten SELECT nr FROM pers; # Nur ausgewählte Spalte Datensätze in Tabelle "pers" einfügen (nicht angegebene Spalten werden mit ihrem Defaultwert oder NULL gemäß Angabe in Tabellen-Definition belegt): INSERT INTO pers (nr, vorname, name) VALUES (1, "Thomas", "Birnthaler"); INSERT INTO pers (nr, vorname, name) VALUES (2, "Markus", "Mueller"); INSERT INTO pers (nr, vorname, name) VALUES (8, "Andrea", "Bayer"); Bei vollständigen Datensätzen kann auf die Angabe der Spaltennamen verzichtet werden. Dann müssen aber REIHENFOLGE und TYP der einzufügenden Daten exakt mit der Spaltenreihenfolge in der Tabellen-Definition übereinstimmen (Vorsicht!): INSERT INTO pers VALUES (7, "Heinz", "Bayer"); INSERT INTO pers VALUES (NULL, "Hans", "Dampf"); INSERT INTO pers VALUES (NULL, NULL, "Unbekannt"); Andere Einfüge-Syntax: INSERT INTO pers SET nr = 7, vorname = "Heinz", name = "Bayer"; Einfügen von mehr als einem Datensatz gleichzeitig (sehr effizient, MY!): ACHTUNG: Server-Variable "max_allowed_packet" muss gross genug sein, um Statement vollständig zum Server übertragen zu können): INSERT INTO pers (nr, vorname, name) # Nur 1x Spaltennamen! VALUES (1, "Thomas", "Birnthaler"), # Nur 1x VALUES! (2, "Markus", "Mueller"), # "," zw. Datensätzen! (8, "Andrea", "Bayer"), # (9, "Richard", "Seiler"), # (7, "Heinz", "Bayer"), # (5, "Hans", "Dampf"), # (3, NULL, "Unbekannt"); # Anweisungsende Laden der Daten aus einer externen CSV-Datei (Comma Separated Values, enthält pro Datensatz eine Zeile abgeschlossen durch (Unix), (Windows) oder (MacOS). Die Datensatz-Elemente müssen in SPALTENREIHENFOLGE im Datensatz stehen und durch ein Tabulatorzeichen getrennt sein. Bei CSV-Dateien notwendige 1. Zeile mit Spaltennamen kann ignoriert werden. Die Daten können auf Client ("LOCAL") oder Server (kein "LOCAL") liegen. LOAD DATA LOCAL INFILE "/path/to/pdata.txt" INTO TABLE pers; # Linux A LOAD DATA LOCAL INFILE "/path/to/pdata.txt" INTO TABLE pers # Linux B LINES TERMINATED BY "\n" # IGNORE 1 LINES; # 1.Zl ign. LOAD DATA LOCAL INFILE "D:/path/to/pdata.txt" INTO TABLE pers # Windows LINES TERMINATED BY "\r\n" # IGNORE 1 LINES; # 1.Zl ign. LOAD DATA INFILE "D:\\path\\to\\pdata.txt" INTO TABLE pers # Windows LINES TERMINATED BY "\r\n" # IGNORE 1 LINES; # 1.Zl ign. LOAD DATA INFILE "/path/to/pdata.txt" INTO TABLE pers # MacOS FIELDS TERMINATED BY "\t" # Tabs LINES TERMINATED BY "\r" # IGNORE 1 LINES # 1.Zl ign. (nr, vorname) # Spalten in Reihenfolge laden (sonst alle) SET name = ''; # Spalten fix füllen ACHTUNG: Voraussetzungen: * Damit LOAD LOCAL INFILE funktioniert, muss die Server-Variable "local_infile" bei Server UND Client auf "ON" gesetzt sein. * Damit LOAD DATA INFILE ... (sowie LOAD_FILE() und SELECT ... INTO OUTFILE ...) funktionieren, muss das Recht "FILE" für den Benutzer gesetzt sein. * Die Server-Variable "secure_file_priv" legt evtl. Verz. fest, in dem export. und importierten Dateien liegen MÜSSEN (z.B. "/var/lib/mysql-files"). Inhalt der Datei "pdata.txt" (NULL-Wert durch "\N" codiert!): +-------------------------------+ # |NrVornameName | # 1. Zeile mit Spaltennamen --> Ignorieren +-------------------------------+ # |1ThomasBirnthaler| # 1. Datensatz |2MarkusMueller | # 2. Datensatz |8AndreaBayer | # 3. Datensatz |9RichardSeiler | # 4. Datensatz |7HeinzBayer | # 5. Datensatz |\NHansDampf | # 6. Datensatz (\N = NULL-Wert) |\N\NUnbekannt | # 7. Datensatz (\N = NULL-Wert) +-------------------------------+ # Um eine CSV-Datei zu erzeugen, folgende SELECT-Syntax verwenden (kein LOCAL, d.h. Dateiablage nur auf Server möglich, NICHT auf Client!): SELECT ... INTO OUTFILE [] FROM ...; Als sind möglich (in dieser Reihenfolge anzugeben!, "FIELDS" und LINES nur 1x!, alle Elemente können fehlen, ebenso die Spalten) FIELDS TERMINATED BY # STD: "\t" = Tabulator [OPTIONALLY] ENCLOSED BY # STD: "" = keines ESCAPED BY # STD: "\\" = \ LINES STARTING BY # STD: "" = leer TERMINATED BY # STD: "\n" = \n Kopieren einer Tabelle: Struktur und/oder Indices und/oder Datensätze. ACHTUNG: Primary Key und Indices werden NICHT mitkopiert (ausser bei LIKE!): # Struktur Indices Daten CREATE TABLE copy SELECT * FROM pers; # OK Nein OK CREATE TABLE copy AS SELECT * FROM pers; # OK Nein OK CREATE TABLE copy SELECT * FROM pers WHERE 0; # OK Nein Nein (TRICK!) CREATE TABLE copy AS SELECT * FROM pers WHERE 0; # OK Nein Nein CREATE TABLE copy LIKE pers; # OK OK Nein (MY!5.0) INSERT INTO copy SELECT * FROM pers; # Nein Nein OK Um eine Tabelle VOLLSTÄNDIG zu kopieren (Struktur + Indices + Datensätze), die beiden folgenden Anweisungen kombinieren: # Struktur Indices Daten CREATE TABLE copy LIKE pers; # OK OK Nein (MY!5.0) INSERT INTO copy SELECT * FROM pers; # Nein Nein OK Kopieren bestimmter Spalten der Datensätze aus einer Tabelle in eine andere Tabelle (Anzahl Spalten von Quelle und Ziel MUSS übereinstimmen!): INSERT INTO copy (nr, vorname, name) # "vorname" und "name" vertauschen! SELECT nr, name, vorname # FROM pers; # Tabelleninhalt abfragen (ALLE Datensätze mit ALLEN Spalten in ihrer Definitionsreihenfolge bzw. falls Primary Key vorhanden sortiert nach diesem): SELECT * FROM copy; # SELECT ALL * FROM copy; # (analog, ALL ist STD) Tabelleninhalt abfragen (ALLE Datensätze mit bestimmten Spalten in der angegebenen Reihenfolge): SELECT name, vorname FROM pers; Tabelleninhalt abfragen (IDENTISCHE Datensätze nur 1x anzeigen, analog GROUP BY über gewählte Spalten, DISTINCT und DISTINCTROW verhalten sich identisch): SELECT DISTINCT * FROM copy; # Alle Spalten SELECT DISTINCTROW * FROM copy; # (analog) SELECT DISTINCT name FROM copy; # Eine Spalte SELECT DISTINCT name, vorname FROM copy; # Zwei Spalten Spezifische Spaltentexte (Aliase) für Ausgabe als Überschrift festlegen, Standard ist der Spaltenname laut Tabellen-Definition (bei Spalten-Aliasen ist GROSS/kleinschreibung nicht relevant, "AS" ist auch weglassbar): SELECT vorname "Vorname", name "Familienname" FROM pers; # Ohne AS SELECT vorname AS "Vorname", # Mit AS name AS "Familienname" # (besser lesbar) FROM pers; Konstanten und Ergebnis einer Rechnung mit spezifischer Überschrift ausgeben (STD: Konstante, Ergebnis oder Formel auch als Überschrift verwendet): SELECT 2010 AS "Jahr", "Ergebnis = " AS "Text", 5 * 4 - 3 / 2 AS "Formel"; Beispiel: CREATE TABLE IF NOT EXISTS artikel ( nr INT, name VARCHAR(50), preis NUMERIC(10,2), anz INT ); INSERT INTO artikel VALUES (1, "Locher", 4.95, 18), (2, "Hefter", 9.90, 12), (3, "Papier", 5.49, 123); SELECT name AS `Artikel`, # Warum OK? preis AS "Nettopreis", # round(preis * 0.19, 2) AS 'MwSt', # Rundung auf 2 NkSt round(preis * 1.19, 2) AS "Bruttopreis" # Rundung auf 2 NkSt FROM artikel; Tabelleninhalt nach Spalte "name" AUFsteigend sortiert abfragen (ohne GROSS/kleinschreibung zu berücksichtigen; STD: ASC = ascending = aufsteigend, der Deutlichkeit halber TROTZDEM hinschreiben!). Neben SpaltenNAMEN sind auch SpaltenNUMMERN in ORDER BY erlaubt. Diese Nummern beziehen sich auf die Reihenfolge der selektierten Spalten (bei "*" ist das die Reihenfolge der Spalten in der Tabellen-Definition): SELECT * FROM pers ORDER BY name; # GROSS/kleinschr. ignorieren SELECT * FROM pers ORDER BY name ASC; # (analog, besser!) SELECT * FROM pers ORDER BY BINARY name ASC; # GROSS/kleinschr. beachten SELECT * FROM pers ORDER BY 3, 2; # Nach Spalte 3+2 sortieren SELECT * FROM pers ORDER BY name, vorname; # (analog, Sp.name statt nr) Tabelleninhalt nach Spalte "nr" ABsteigend sortiert abfragen (DESC = descending, BINARY = GROSS/kleinschreibung beachten): SELECT * FROM pers ORDER BY nr DESC; # GROSS/kleinschr. ignorieren SELECT * FROM pers ORDER BY BINARY nr DESC; # GROSS/kleinschr. beachten Sortierung des Tabelleninhalts nach Spalte "name" AUFsteigend, bei gleichem Nachnamen nach Spalte "vorname" AUFsteigend und bei gleichem Vor+Nachnamen nach Spalte "nr" ABsteigend (ohne GROSS/kleinschreibung zu berücksichtigen): SELECT * FROM pers ORDER BY name ASC, # AUFsteigend (ASC besser immer hinschreiben!) vorname ASC, # AUFsteigend nr DESC; # ABsteigend Teil der Datensätze einer Tabelle über eine Bedingung abfragen * Sogenannte "WHERE-Klausel/clause" * Textvergleiche ignorieren GROSS/kleinschreibung (außer BINARY vor Operator) * LIKE = Unscharfe Suche mit Wildcards % (beliebig viele belieb. Z.) _ (1 beliebiges Zeichen) * REGEX/RLIKE = Unscharfe Suche mit Regulären Ausdrücken (s.u.) * i/- = Index nutzbar/NICHT nutzbar (beschleunigt evtl. Suche) SELECT nr # i = Index nutzbar FROM pers # - = Index NICHT nutzbar WHERE name > "Andrea" AND name < "Thomas"; # i Ränder weglassen WHERE name >= "Andrea" AND name <= "Thomas"; # i Ränder einschließen (A) ... WHERE name BETWEEN "Andrea" AND "Thomas"; # i Ränder einschließen (B) ... WHERE name NOT BETWEEN "Andrea" AND "Thomas"; # i Ränder weglassen ... WHERE name IS NULL; # i Spalte LEER ... WHERE name IS NOT NULL; # i Spalte NICHT LEER ... WHERE name = NULL; # - FALSCH! immer NULL --> FALSE! ... WHERE name <> NULL; # - FALSCH! immer NULL --> FALSE! ... WHERE name = "Andrea" OR name = "Thomas"; # i Werteliste (A) ... WHERE name IN ("Andrea", "Thomas"); # i Werteliste (B) ... WHERE name NOT IN ("Andrea", "Thomas"); # i Ausschlussliste ... WHERE name LIKE "a%"; # i "a" am Anfang ... WHERE BINARY name LIKE "%a%"; # - "a" irgendwo ... WHERE name NOT LIKE "%a"; # - KEIN "a" am Ende ... WHERE name NOT LIKE "__a%"; # - KEIN "a" an 3. Z.pos. ... WHERE name REGEXP "abc"; # - "abc" enthalten ... WHERE name RLIKE "abc"; # - "abc" enthalten ... WHERE name NOT REGEXP "abc"; # - KEIN "abc" enthalten ... WHERE name NOT RLIKE "abc"; # - KEIN "abc" enthalten Weitere Tabelle anlegen und füllen ("age" statt "alter", da "ALTER" eine SQL-Anweisung ist, durch Einschließen in Backquotes `alter` wäre "alter" doch als Tabellenname möglich, "..." oder '...' funktioniert nicht!): CREATE TABLE age ( nr INT NOT NULL, geburtsdatum DATE, jahre INT, geschlecht CHAR(1) ); # Mehrere Sätze einfügen INSERT INTO age VALUES (1, "1971-1-8", 39, "m"), # Nur möglich, da Werte (2, "2001-5-13", 9, "m"), # ALLER Spalten in der (8, "1969-12-1", 41, "w"), # korrekten Reihenfolge (9, "1975-7-3", 35, "m"); # angegeben werden! INSERT INTO age # Andere Schreibweise SET nr = 7, geburtsdatum = "1966-1-1", jahre = 44, geschlecht = "m"; Zwei (oder mehr) Tabellen gemeinsam abfragen und alle Kombinationen aller Datensätze ausgeben (Kreuzprodukt = "CROSS JOIN" von "pers" und "age"). Die Liste der Tabellen ist durch "," getrennt hintereinander anzugeben: SELECT * # CROSS JOIN (alle Kombinationen) FROM pers, age; # Kreuzprodukt von zwei (oder mehr) Tabellen durchführen und Ergebnismenge über gleiche Werte in der Spalte "nr" einschränken ("INNER JOIN" von "pers" und "age"). Die mehrdeutige Spalte "nr" wird durch vorangestellte Tabellennamen "pers" und "age" qualifiziert (genau spezifiziert): SELECT * # INNER JOIN mit WHERE (implizit) FROM pers, age # WHERE pers.nr = age.nr; # Alternativ "Aliase" (eindeutige Kurznamen, GROSS/kleinschreibung relevant) "p" und "a" für die Tabellen "pers" und "age" einführen (besser lesbar und kürzer): SELECT * # INNER JOIN mit WHERE (implizit) FROM pers AS p, age AS a # Auch ... FROM pers p, age a ... WHERE p.nr = a.nr; # Gleiche Abfrage in expliziter JOIN-Syntax (Variante B+C ist nur möglich, falls die Verknüpfungsspalte in beiden Tabellen gleich heißt, wie hier "nr"): SELECT * # A) INNER JOIN mit ON FROM pers JOIN age # (Spalte "nr" doppelt) ON pers.nr = age.nr; # Allgemeine Syntax SELECT * # B) INNER JOIN mit USING FROM pers JOIN age # (Spalte "nr" in beiden Tab. vorhanden) USING (nr); SELECT * # C) NATURAL JOIN (nur möglich, falls FROM pers NATURAL JOIN age; # "nr" einzige gleichnamige Spalte ist) TIP: Index oder Primary Key auf diversen Spalten anlegen (damit laufen obige SELECT-Abfragen mit Verknüpfung beider Tabellen SEHR VIEL SCHNELLER!). Nur Teil der Datensätze ab bestimmter Position holen ( Datensätze ab Datensatz , STD: alle ab 0), die Datensätze sind beginnend mit "0" nummeriert --- MY!). SELECT * FROM pers LIMIT 10; # 10 Sätze ab Datensatz 0 SELECT * FROM pers LIMIT 3, 5; # 5 Sätze ab Datensatz 3 SELECT * FROM pers LIMIT 5 OFFSET 3; # 5 Sätze ab Datensatz 3 TIP: Abfrage SORTIEREN, sonst ist die Reihenfolge der Datensätze zufällig gemäß ihrer "physikalischen" Engine-Reihenfolge. SELECT * FROM pers ORDER BY name, vorname LIMIT 10; TIP: Immer einen Primärschlüssel anlegen, der jeden Datensatz eindeutig identifiziert. Falls dies aus den Daten heraus nicht möglich ist, dafür eine Spalte mit einem "künstlichen Schlüssel" (eindeutige Nummer) anlegen. Index gleich beim Erstellen einer Tabelle mit anlegen (Variante A): CREATE TABLE pers ( nr INT NOT NULL, # NOT NULL bei PRIMARY KEY! ... # PRIMARY KEY ON (nr), # Primärschlüssel UNIQUE INDEX idx2 ON (name), # Eindeutige Spalte INDEX idx3 ON (name, vorname), # 2 Spalten verkettet FULLTEXT INDEX idx4 ON (vorname), # Volltextsuche SPATIAL INDEX idx4 ON (nr) # Geometriedaten ); Index nach Erstellen einer Tabelle hinzufügen (Variante B, jederzeit möglich): CREATE PRIMARY KEY ON pers (nr); # FALSCH! --> ALTER TABLE CREATE UNIQUE INDEX idx2 ON age (nr); # Eindeutige Spalte(n) CREATE INDEX idx3 ON pers (name, vorname); # 2 Spalten verkettet CREATE FULLTEXT INDEX idx4 ON age (name); # Volltextsuche CREATE SPATIAL INDEX idx5 ON age (nr); # Geometriedaten Index / Primary Key zu Tabelle hinzufügen (Variante C, jederzeit möglich): ALTER TABLE pers ADD PRIMARY KEY (nr); # Name unnötig ("PRIMARY") ALTER TABLE age ADD UNIQUE INDEX idx2 (nr); # Eindeutige Spalte(n) ALTER TABLE pers ADD INDEX idx3 (name, vorname); # 2 Spalten verk. ALTER TABLE age ADD FULLTEXT INDEX idx4 (name); # Volltextsuche ALTER TABLE age ADD SPATIAL INDEX idx5 (nr); # Geometriedaten Indices zu einer Tabelle anzeigen: SHOW INDEX FROM pers; SHOW INDEX FROM age; Index oder Primary Key einer Tabelle entfernen (jederzeit möglich): ALTER TABLE pers DROP PRIMARY KEY; # Kein Name nötig ALTER TABLE pers DROP INDEX idx2; # Name "idx2" nötig, UNIQUE weglassen! DROP INDEX idx3 ON pers; # Name "idx3" nötig TIP: Index auf Präfix von Spalten bzw. AUF/ABsteigend erzeugen (STD: ASC), für Tuningzwecke zur Platzersparnis interessant (MY!). CREATE INDEX idx8 ON pers (name(5), vorname(5)); # Platz sparen! CREATE INDEX idx9 ON pers (name ASC, vorname DESC); # Auf+Absteigend Alle Datensätze ändern (VORSICHT: keine WHERE-Bedingung --> ALLE Datensätze!): UPDATE pers SET nr = nr + 100; Bestimmte Datensätze ändern: UPDATE pers # UPDATE FROM geht in MySQL nicht! SET nr = 111, # name = DEFAULT # DEFAULT-Wert aus Tab.definition einsetzen WHERE vorname = "Thomas"; # Datensätze ersetzen oder einfügen: Analog INSERT falls Datensatz mit diesem Primärschlüssel/UNIQUE INDEX noch nicht vorhanden ist, sonst DELETE des alten + INSERT des neuen Datensatzes durchführen (Primärschlüssel notwendig, DELETE-Recht notwendig, AUTO_INCREMENT-Feld erhöht sich --- MY!): REPLACE INTO copy (nr, vorname, name) # (INTO optional) VALUES (1, "Thomas", "Birnthaler"), # Mehrere Datensätze erlaubt (2, "Hans", "Dampf"); # # REPLACE INTO copy (nr, vorname, name) # (INTO optional) SELECT nr, vorname, name # FROM pers, # WHERE nr < 100; # # REPLACE pers # (INTO optional) SET nr = 7, # vorname = "Heinz", # name = "Beier"; # Datensätze ersetzen oder einfügen: Analog INSERT falls Datensatz mit diesem Primärschlüssel/UNIQUE INDEX noch nicht vorhanden, sonst UPDATE des alten Datensatzes durchführen (bessere Variante, Primärschlüssel notwendig, AUTO_INCREMENT-Feld bleibt gleich --- MY!): INSERT INTO pers (nr, vorname, name) VALUES (1, "Thomas", "Birnthaler") ON DUPLICATE KEY UPDATE vorname = "Thomas", name = "Birnthaler"; HINWEIS: REPLACE ändert AUTO_INCREMENT-Wert bereits vorhandener Datensätze, INSERT INTO ON DUPLICATE KEYS UPDATE macht das nicht (schwierigere Syntax)! Bestimmte Datensätze löschen: DELETE FROM pers WHERE vorname = "Markus" OR nr >= 9; Alle Datensätze löschen, Tabelle belassen (VORSICHT: keine WHERE-Bedingung!): DELETE FROM pers; # Langsam (Datensätze einzeln löschen = Transaktion!) TRUNCATE TABLE pers; # Schnell (Tabelle löschen + neu anlegen, nicht in TA!) TRUNCATE pers; # (analog) HINWEIS: Auch Lösch- und Update-Operation sind per LIMIT auf eine bestimmte Anzahl Datensätze beschränkbar (ob das sinnvoll ist, sei dahingestellt!). Dabei sollten die Datensätze mit ORDER BY sortiert werden, um sie nicht zufällig gemäß ihrer "physikalischen" Reihenfolge zu löschen oder zu ändern: DELETE FROM pers # Max. 2 Datensätze löschen WHERE vorname = "Markus" # ORDER BY name # Wichtig! LIMIT 2; # UPDATE pers # Max. 3 Datensätze ändern SET nr = nr + 1000 # WHERE nr < 1000 # ORDER BY name # Wichtig! LIMIT 3; # TIP: Mit der "mysql"-Client-Option --i-am-a-dummy / --safe-updates / -U sind UPDATE- und DELETE-Anweisungen gegen Weglassen einer WHERE- oder LIMIT-Klausel geschützt (d.h. versehentliches Ändern/Löschen ALLER Datensätze --- außer per TRUNCATE --- wird verhindert). Anzahl Datensätze, Spaltenwerte oder Häufigkeit verschiedener Spaltenwerte in einer Tabelle ermitteln (Gruppieren bzw. Aggregieren): SELECT COUNT(*) FROM pers; # Anz. Datensätze in Tab. (inkl. NULL!) SELECT COUNT(nr) FROM pers; # Anz. Werte von Sp. "nr" (ohne NULL!) SELECT COUNT(DISTINCT nr) FROM pers; # Anz. versch. Werte von Sp. "nr" (ohne NULL!) SELECT name, COUNT(name) FROM pers # Häufigkeit der Werte in Sp. "name" GROUP BY name; # SELECT name, COUNT(name) FROM pers # (analog, nur Werte mit Häuf. > 3) GROUP BY name # HAVING COUNT(name) > 3; # SELECT name, COUNT(name) FROM pers # (analog + Gesamtsumme (NULL als Wert!) GROUP BY name # WITH ROLLUP; # (MY!) ACHTUNG: HAVING wirkt darauf! Weitere typische Aggregatfunktionen analog COUNT (arbeiten auf der Menge aller von einer Abfrage gefundenen Datensätze): SELECT SUM(nr), # Summe aller Werte von Spalte "nr" SUM(DISTINCT nr), # Summe aller verschiedenen Werte von Spalte "nr" AVG(nr), # Durchschnitt aller Werte von Spalte "nr" MIN(nr), # Minimum aller Werte von Spalte "nr" MAX(nr) # Maximum aller Werte von Spalte "nr" FROM pers; Datensatz mit höchster Nummer: SELECT DISTINCT * FROM pers WHERE nr = MAX(nr); # FALSCH! SELECT DISTINCT * FROM pers # A) Subselect für Maximum WHERE nr = (SELECT MAX(nr) FROM pers); # SET @max := (SELECT MAX(nr) FROM pers); # B) Variable @max füllen SET @max = (SELECT MAX(nr) FROM pers); # Variable @max füllen SELECT * FROM pers WHERE nr = @max; # (:= / = ist Zuweisung) SELECT * FROM pers ORDER BY nr DESC LIMIT 1; # C) Abbruch per LIMIT SELECT p1.* FROM pers p1 LEFT JOIN pers p2 # D) FALSCH! ON p1.nr <= p2.nr WHERE p2.nr IS NULL; # Datensatz mit niedrigster oder höchster Nummer: SELECT * FROM pers WHERE nr = MIN(nr) OR nr = MAX(nr); # A) FALSCH! SELECT * FROM pers WHERE nr IN (MIN(nr), MAX(nr)); # B) FALSCH! SELECT * FROM pers WHERE nr IN ( # C) Subselect (SELECT MIN(nr) FROM pers), # (SELECT MAX(nr) FROM pers) # ); # SELECT @min := MIN(nr), @max := MAX(nr) FROM pers; # D) Variablen + ... SELECT MIN(nr), MAX(nr) INTO @min, @max; # (analog MY!) SELECT * FROM pers WHERE nr = @min OR nr = @max; # D1) ... log. Bed SELECT * FROM pers WHERE nr IN (@min, @max); # D2) ... Menge SELECT * FROM pers WHERE nr = @min # D3) ... Union UNION ALL SELECT * FROM pers WHERE nr = @max; Allgemeines SELECT-Statement (alle Möglichkeiten): SELECT , ... # Spaltenauswahl FROM , ... # Tabellenauswahl [WHERE ] # Bedingung auf Spalten (Condition) [GROUP BY , ... # Aggregation auf Spalten (Datensätze zusammenfassen) [HAVING ]] # Bedingung auf Aggregation (nur bei GROUP BY!) [ORDER BY ...] # Sortierung nach Spalten (Name oder Nummer!) [LIMIT ...] # Anz. Datensätze + Startpos. begrenzen (MY!) Beispiel: SELECT nr, COUNT(name) AS "Anz", name AS "Name" FROM pers WHERE nr > 2 GROUP BY name ORDER BY nr DESC LIMIT 2; HINWEIS: MySQL erlaubt SELECT-Anweisungen ohne Tabellen-Bezug, um z.B. das Ergebnis von Ausdrücken zu berechnen (in Oracle ist dazu das Anhängen von "FROM dual" notwendig!). Die bei MySQL (und Oracle) grundsätzlich vorhandene "Dummy"-Tabelle "dual" (enthält EINEN Datensatz ohne Spalten) gibt es daher nur aus Kompatibilitätsgründen zu Oracle: SELECT SQRT(2); # In MySQL erlaubt (in Oracle nicht!) SELECT SQRT(2) FROM dual; # In MySQL und Oracle erlaubt (Ergebnis gleich) Tabellenstruktur und -name ändern: * Mehrere Änderungen gleichzeitig durch Komma getrennt angebbar * Dabei wird KOPIE mit Änderungen angelegt (dauert bei vielen Daten lange!) und anschließend das ORIGINAL durch die KOPIE ersetzt * Tabelle wird GESPERRT, d.h. weitere Anfragen an sie müssen solange warten * Reines Umbenennen einer Tabelle wird ohne Kopie erledigt * Verschieben einer Tabelle in andere DB nur im gleichen Dateisystem möglich * Beim Spaltentyp ändern werden Spaltendaten so weit möglich erhalten (evtl. abgeschnitten oder mit Leerzeichen aufgefüllt) * Tabelle/Engine ändern NICHT auf Systemtabellen in DB "mysql" anwenden! Änderungs-Operationen auf Tabellen (mehrere gleichzeitig erlaubt, COLUMN, TO und CONSTRAINT dürfen weggelassen werden): +--------------------+-------------------------------------------------------+ | Operation | Bedeutung | +--------------------+-------------------------------------------------------+ | ADD [COLUMN] | Spalte einfügen (FIRST, AFTER , STD: hinten) | | DROP [COLUMN] | Spalte entfernen (mit Daten + Indices darauf) | | MODIFY [COLUMN] | Sp.typ ändern (Sp.name bleibt) oder Sp. verschieben | | CHANGE [COLUMN] | Spalte neu definieren (Name + Typ, d.h. auch umben.) | | ALTER [COLUMN]... | Defaultwert... | | ...SET DEFAULT | ...ändern | | ...DROP DEFAULT | ...entfernen | +--------------------+-------------------------------------------------------+ | RENAME [TO] | Tabellenname ändern, Tabelle in andere DB verschieben | | ORDER BY | Datensätze für schnelle Abfrage sortieren (MY!) | | ENGINE | Andere Datenbank-Engine verwenden (MY!) | | TYPE | Andere Datenbank-Engine verwenden (veraltet, MY!) | | IMPORT TABLESPACE | (MY!) | | DISCARD TABLESPACE | (MY!) | +--------------------+-------------------------------------------------------+ | ADD PRIMARY KEY | Primärschlüssel hinzu (muss NOT NULL UNIQUE sein) | | ADD UNIQUE INDEX | Sekundärschlüssel hinzu (muss NOT NULL UNIQUE sein) | | ADD {INDEX | KEY} | Index hinzu (muss nicht NOT NULL oder UNIQUE sein) | | DROP PRIMARY KEY | Primärschlüssel entfernen | | DROP {INDEX | KEY} | Index entfernen | | DISABLE KEYS | Alle Indices der Tabelle abschalten (nur Non-UNIQUE!) | | ENABLE KEYS | Alle Indices der Tabelle aktivieren (nur Non-UNIQUE!) | +--------------------+-------------------------------------------------------+ | ADD FOREIGN KEY | Fremdschlüsselbezug hinzufügen | | DROP FOREIGN KEY | Fremdschlüsselbezug entfernen | | ADD CONSTRAINT | Spalten-Beschränkung hinzufügen (ignoriert MY!) | | DROP CONSTRAINT | Spalten-Beschränkung entfernen (nicht verfügbar MY!) | +--------------------+---------+--------------------------------------------+ | CONVERT TO CHARACTER SET ... | Zeichensatz konvertieren | | COLLATE ... | | | [DEFAULT] CHARACTER SET ... | Standard-Zeichensatz festlegen | | COLLATE ... | | +------------------------------+--------------------------------------------+ Beispiele: ALTER TABLE pers ... ... ADD COLUMN preis DECIMAL(10,2); # STD: Spalte hinten anfügen ... ADD COLUMN (strasse CHAR(30), plz LONG, ort CHAR(30)); ... ... FIRST; # Als 1. Spalte einfügen ... ... AFTER nr; # Nach Spalte "nr" einfügen ... DROP COLUMN name; # Spalte entfernen (inkl. Daten) ... MODIFY COLUMN vorname VARCHAR(50); # Typ ändern (Daten mögl. erhalten) ... CHANGE COLUMN name name VARCHAR(20); # (analog) ... ... FIRST; # Als 1. Spalte verschieben ... ... AFTER nr; # Nach Spalte "nr" verschieben ... CHANGE COLUMN vorname name CHAR(20); # Name+Typ änd. (Daten mögl. erh.) ... CHANGE COLUMN name vorname CHAR(20); # Name änd. (Typ identisch wählen!) ... ALTER COLUMN vorname SET DEFAULT ""; # Default änd.: Leere Zeichenkette ... ALTER COLUMN vorname DROP DEFAULT; # Default änd.: NULL # ... RENAME TO pers_old; # Tabelle umbenennen ... RENAME TO test2.pers; # Tabelle in andere DB verschieben ... ORDER BY vorname DESC, name ASC; # Datensätze sortieren ... ENGINE = MyISAM; # Engine ändern (neu!) ... TYPE = MyISAM; # Engine ändern (veraltet!) # ... ADD PRIMARY KEY (nr); # Primärschlüssel hinzufügen ... DROP PRIMARY KEY; # Primärschlüssel entfernen ... INDEX idx1 (name, vorname); # Index hinzufügen ... DROP INDEX idx1; # Index entfernen ... UNIQUE INDEX idx2 (name, vorname); # Sekundärschlüssel hinzufügen ... DROP UNIQUE INDEX idx2; # Sekundärschlüssel entfernen ... DISABLE KEYS; # Alle Indices abschalten (nur Non-UNIQUE) ... ENABLE KEYS; # Alle Indices aktivieren (nur Non-UNIQUE) # ... ADD FOREIGN KEY age(nr) # Fremdschlüssel hinzufügen REFERENCES pers(nr); # ... DROP FOREIGN KEY age; # Fremdschlüssel entfernen ... ADD CONSTRAINT pruef CHECK (nr >= 1); # Spalten-Beschränkung hinzufügen ... DROP CONSTRAINT pruef; # Nicht verfügbar! # ... CONVERT TO CHARACTER SET "latin1" # Zeichensatz aller Sp. konvertieren COLLATE "latin1_german_ic"; # ... [DEFAULT] CARACTER SET = "latin1" # Zeichensatz aller Sp. konvertieren COLLATE = "latin1_german_ic"; # Tabelle(n) umbenennen (auch in andere Datenbank verschieben, wenn die beiden Datenbanken auf dem gleichen Dateisystem liegen). Stellt eine "atomare" Operation dar, auch bei gleichzeitiger Umbenennung mehrerer Tabellen. Läuft sehr schnell ab, da keine Kopie erstellt wird: RENAME TABLE pers TO new [, ...]; # Nicht für temp. Tabelle ALTER TABLE new RENAME TO pers; # Auch für temp. Tabelle RENAME TABLE pers TO copy, # Zwei Tabellenamen vertauschen (FALSCH!) copy TO pers; # (-> ERROR: Table 'copy' already exists) RENAME TABLE pers TO tmp, # Zwei Tabellenamen vertauschen (OK!) copy TO pers, # tmp TO copy; # Tabelle (bedingt) löschen (inkl. aller Daten und Indices!): DROP TABLE pers; # Fehler falls nicht existent DROP TABLE IF EXISTS pers; # Kein Fehler falls nicht existent DROP TEMPORARY TABLE pers; # Temporäre Tabelle löschen (nicht echte!) Datenbank (bedingt) löschen (alle Tabellen mit allen Daten!): DROP DATABASE first; # Fehler falls nicht existent DROP SCHEMA first; # (analog) DROP DATABASE IF EXISTS first; # Kein Fehler falls nicht existent DROP SCHEMA IF EXISTS first; # (analog) Abfrage von Informationen zur vorhergehenden SQL-Anweisung (pro Sitzung): SELECT ROW_COUNT(); # Anz. verarb. DS (INSERT, UPDATE, DELETE, REPLACE) SELECT SQL_CALC_FOUND_ROWS ...; # Vor Einsatz von FOUND_ROWS() notwendig! SELECT FOUND_ROWS(); # Gesamtanz. Datensätze bei SELECT mit LIMIT SELECT LAST_INSERT_ID(); # Letzter AUTO_INCREMENT-Wert bei INSERT Fehlermeldungen, Warnungen und Bemerkungen (Notes) zur vorhergehenden SQL-Anweisung ausgeben (pro Sitzung): SHOW ERRORS; # Nur Fehler ausgeben SHOW WARNINGS; # Fehler, Warnungen, Bemerkungen (Notes) ausgeben SET max_error_count = 0; # Aufzeichnung der Warnungen/Fehler ausschalten SET max_error_count = 64; # Max. Anz. aufgezeichneter Warnungen/Fehler (STD) SET sql_notes = 1; # Bemerkungen (Notes) 1=aufzeichnen/0=unterdrücken SET sql_warnings = 1; # INSERT-Warnung 1=aufzeichnen/0=unterdrücken SELECT @@error_count; # Anzahl gemerkter Fehler SELECT @@warning_count; # Anzahl gemerkter Warnungen 4) MySQL-Datentypen ------------------- Im Prinzip könnten man die Spaltenwerte einer Tabelle immer in Form von Zeichenketten abspeichern. MySQL würde diese automatisch beim Zugriff in andere Datentypen wie Ganz/Fließkommazahlen, Datums/Zeitwerte sowie Boolesche Werte umwandeln. Der Konvertierungsaufwand wäre aber für jeden Datensatz bei jedem Zugriff durchzuführen und daher zu aufwendig. Daher sollte abgestimmt auf die zu speichernden Werte für jede Tabellenspalte einer der folgenden MySQL-Datentypen ausgewählt werden. Diese Auswahl ist nicht immer eindeutig (z.B. könnte ein Jahr als INT, FLOAT, DECIMAL oder YEAR gespeichert werden). Bei sinnvoller Auswahl des Datentyps gilt: * Erlaubter Wertebereich sinnvoll eingeschränkt (ungültige Daten abgelehnt) (neben dem Spaltennamen ein wichtiger Teil der Dokumentation) * Platzbedarf minimiert * Zugriffsgeschwindigkeit maximiert * Indices effizient nutzbar für Zugriff und Sortierung * Typspezifische MySQL-Funktionen und -Operatoren einsetzbar TIP: Auch an evtl. zukünftige Erweiterungen denken und z.B. nicht für das Speichern einer Jahreszahl nur 2 Stellen ("Y2K-Problem") oder einer IP-Adresse oder Netzwerk-Maske nur 4 Byte (IPv4) vorsehen (IPv6 benötigt 16 Byte). +---------------------------+--------------+-------+-------------------------+ | Datentyp | Typname | Byte | Wertebereich | +---------------------------+--------------+-------+-------------------------+ | Ganzzahl (mit Vorzeichen) | TINYINT | 1 | -127..128 | | | SMALLINT | 2 | -32768..32767 | | | MEDIUMINT | 3 | -8388608..8388607 | | | INT | 4 | -2147483648..2147483647 | | | BIGINT | 8 | -9223372036854775808.. | | | | | ..9223372036854775807 | +---------------------+-----+--------------+-------+-------------------------+ | Ganzzahl (ohne Vz.) | TINYINT UNSIGNED | 1 | 0..255 | | | SMALLINT UNSIGNED | 2 | 0..65535 | | | MEDIUMINT UNSIGNED | 3 | 0..16777215 (16 Mio) | | | INT UNSIGNED | 4 | 0..4294967295 (4 Mrd) | | | BIGINT UNSIGNED | 8 | 0..18446744073709551615 | +---------------------+-----+--------------+-------+-------------------------+ | Fließkommazahl | FLOAT | 4 | 10^38 (7 Dez.stellen) | | (technisch, nicht kaufm.) | DOUBLE | 8 | 10^308 (15 Dez.stellen) | +------------------------+--+--------------+-------+-------------------------+ | Fließkommazahl | FLOAT UNSIGNED | 4 | 10^38 (7 Dez.stellen) | | (ohne Vorz.) | DOUBLE UNSIGNED | 8 | 10^308 (15 Dez.stellen) | +------------------------+--+--------------+-------+-------------------------+ | Festkommazahl (L max. 65) | DECIMAL(L,N) | L+2 | Länge L mit N Nkst. | | (vor MY!5.0 als String) | | L/9*4 | (ab MY!5.0!) | +---------------------------+--------------+-------+-------------------------+ | Festkommazahl (ohne Vorz.)| DECIMAL(L,N) | L+2 | Länge L mit N Nkst. | | | UNSIGNED | L/9*4 | (ab MY!5.0!) | +---------------------------+--------------+-------+-------------------------+ | Boolean | BOOL | 1 | 0/1 FALSE=0/TRUE=1 | | Bitfeld | BIT(N) |(N+7)/8| N Bits (1..64) | +---------------------------+--------------+-------+-------------------------+ | Zeichenkette (Länge fest) | CHAR(L) | L | L=0..255 | | (Länge fest) | BINARY(L) | L | L=0..255 | | (Länge variabel) | VARCHAR(L) | L+1/2 | L=0..255/65535 (MY!5.1) | | (Länge variabel) | VARBINARY(L) | L+1/2 | L=0..255/65535 (MY!5.1) | +---------------------------+--------------+-------+-------------------------+ | Datum + Zeit | YEAR | 1 | JJJJ/JJ (1900-2155) | | | DATE | 3 | JJJJ-MM-TT (1000-9999) | | | TIME | 3 | hh:mm:ss | | | DATETIME | 8 | JJJJ-MM-TT hh:mm:ss | | (Sek. seit 1.1.1970 00:01)| TIMESTAMP | 4 | JJJJMMTThhmmss | +---------------------------+--------------+-------+-------------------------+ | Mikrosekunden 0..6 | TIME(Fsp) | 3+0-3 | hh:mm:ss.ffffff fff | | (Fractional seconds part) | DATETIME(Fsp)| 5+0-3 | JJJJ-MM-TT hh:mm:ss.fff | | seit MY!5.6.4 (0..3 Byte) |TIMESTAMP(Fsp)| 4+0-3 | JJJJMMTThhmmss.ffffff | +---------------------------+--------------+-------+-------------------------+ | Text (Sonderzeichen | TINYTEXT | L+1 | 0-255 | | interpretiert) | TEXT | L+2 | 0-65535 | | | MEDIUMTEXT | L+3 | 0-16777216 (16 Mio) | | | LONGTEXT | L+4 | 0-4294967296 (4 Mrd) | +---------------------------+--------------+-------+-------------------------+ | Binary large object BLOB | TINYBLOB | L+1 | 0-255 | | (binäre Daten, | BLOB | L+2 | 0-65535 | | keine Interpretation | MEDIUMBLOB | L+3 | 0-16777216 (16 Mio) | | von Sonderzeichen) | LONGBLOB | L+4 | 0-4294967296 (4 Mrd) | +---------------------------+--------------+-------+-------------------------+ | Aufzählung (Single-Value) | ENUM(V1, ...)| 1/2 | 1..255, 2=256-65535 | | Menge (Multi-Value) | SET(V1, ...) | 1-4/8 | 1..8/16/24/32/64 Elem. | +-------------------+-------+--------------+-------+-------------------------+ | Unicodestring | NCHAR(L) | L | Synonym für CHAR | | | NATIONAL VARCHAR(L) | L+1 | Synonym für VARCHAR | +-------------------+----------------------+-------+-------------------------+ Mögliche Attribute zu allen Datentypen (danach anzugeben): +---------------+-----------------------------------------------------+ | Attribut | Bedeutung | +---------------+-----------------------------------------------------+ | DEFAULT | Defaultwert setzen, falls KEIN Wert angegeben | | NOT NULL | Wert MUSS angegeben sein (evtl. per Defaultwert) | | NULL | Wert darf weggelassen werden ("undefiniert", STD) | +---------------+-----------------------------------------------------+ HINWEIS: Der Defaultwert muss konstant sein, Funktionen sind unzulässig. Mögliche Attribute zu numerischen Datentypen (Ganzzahl, Fließkomma, Festkomma): +----------------+---------------------------------------------------------+ | Attribut | Bedeutung | +----------------+---------------------------------------------------------+ | () | Ausgabebreite (INT) | | (, ) | Ausgabebreite N und Nkst. M (FLOAT, DOUBLE, DECIMAL) | | UNSIGNED | Wert immer positiv | | ZEROFILL | Ausgabe mit führenden Nullen gemäß Breite (nicht DEC!) | +----------------+---------------------------------------------------------+ | AUTO_INCREMENT | Pro NEU eingefügtem Datensatz automatisch hochzählen | !!! | | (künstlicher Key, nur 1x pro Tabelle, NICHT DECIMAL!) | +----------------+---------------------------------------------------------+ | PRIMARY KEY | Spalte ist Primärschlüssel (--> NOT NULL) | +----------------+--------+------------------------------------------------+ | CHARACTER SET | Zeichensatz und | | COLLATE | Sortierordnung | +-------------------------+------------------------------------------------+ Mögliche Attribute zu TIMESTAMP: +-----------------------------+---------------------------------------------+ | Attribut | Bedeutung | +-----------------------------+---------------------------------------------+ | DEFAULT CURRENT_TIMESTAMP | Neuer Datensatz --> aktuelle Zeit speichern | | ON UPDATE CURRENT_TIMESTAMP | Datensatz geändert --> akt. Zeit speichern | +-----------------------------+---------------------------------------------+ Beispiel: DROP TABLE IF EXISTS data; CREATE TABLE data ( nr INT NOT NULL AUTO_INCREMENT, #0 Automatisch füllen anz TINYINT(3) UNSIGNED, #1 Ausgabebreite 3 grad FLOAT(7,1), #2 Ausgabeformat 12345.6 betrag DECIMAL(9,2), #3 Ausgabeformat 123456.78 vorname CHAR(30), #4 Zeichen (mit Leerz. aufgef.) name VARCHAR(30) NOT NULL, #5 Zeichen binaer1 BINARY(10), #6 Byte (mit 0-Byte aufgefüllt) binaer2 VARBINARY(8), #7 Byte ts1 TIMESTAMP, #8 Automatisch gefüllt (1.Sp.) ts2 TIMESTAMP(3), #9 NICHT " (2.Sp., Milli-Sek.) ts3 TIMESTAMP(6), #10 NICHT " (3.Sp., Mikro-Sek.) datum DATE DEFAULT '2000-01-01', #11 uhrzeit TIME DEFAULT '12:00:00', #12 datumzeit DATETIME, #13 JJJJ-MM-TT hh:mm:ss jahr YEAR(4), #14 wahr BOOL, #15 TRUE/FALSE flags BIT(8), #16 Bitmuster 8 Bit = 1 Byte dokument TEXT, #17 antwort ENUM('Ja', 'Nein', 'Vielleicht'), #18 Wert aus Liste menge SET('gross','reich','maechtig'), #19 Menge aus Listenwerten datei BLOB, #20 PRIMARY KEY (nr) # Kein Komma, notwendig wg. AUTO_INCREMENT von Spalte nr ); INSERT INTO data VALUES ( NULL, #0 INT: Nächste freie Nr. wg. AUTO_INCREMENT 123, #1 TINYINT(3): füllen 12345.6, #2 FLOAT(7,1): füllen 876543.21, #3 DECIMAL(9,2): füllen "Thomas", #4 CHAR(30): füllen (mit Leerz. hinten auffüllen) "Birnthaler", #5 VARCHAR(30): füllen 0x4142434445464748494A, #6 BINARY(10): füllen (mit 0-Byte hinten auffüllen) x'6162636465666768', #7 VARBINARY(8): füllen NULL, #8 TIMESTAMP: Aktuellen Zeitp. autom. eintragen "20120907112233.987654", #9 TIMESTAMP: füllen "2012-09-07 11:22:33.123456", #10 TIMESTAMP:füllen ("7.9.2012 11:22:33" NICHT OK!) "2012-08-03", #11 DATE: füllen "23:59:59", #12 TIME: füllen "2012-08-03 23:59:59", #13 DATETIME: füllen ("3.8.2012 23:59:59" NICHT OK!) 2012, #14 YEAR(4): füllen TRUE, #15 BOOL: füllen b'01000001', #16 BIT(8): füllen ASCII-Code 65 = Zeichen 'A' # "Dokument(text)", #17 TEXT: füllen (direkt) oder # LOAD_FILE("/etc/my.cnf"), #17 TEXT: laden aus externer Datei LOAD_FILE("C:/ProgramData/MySQL/MySQL Server 5.6/my.ini"), #17 Windows "Vielleicht", #18 ENUM: Aufz.wert als String (EINER!) "gross,reich,maechtig", #19 SET: Elem. per "," getr. als String # "Blobdaten(binaer)" #20 BLOB: füllen (direkt) oder # LOAD_FILE("/etc/my.cnf") #20 BLOB: laden aus externer Datei LOAD_FILE("C:/ProgramData/MySQL/MySQL Server 5.6/my.ini") #20 Windows # 0x4142434445464748494A, #20 BLOB: füllen (mit 0-Byte hinten) # x'6162636465666768', #20 BLOB: füllen ); SELECT * FROM data\G # Ergibt (Spalten vertikal anzeigen) *************************** 1. row *************************** nr: 1 #0 INT anz: 123 #1 TINYINT(3) grad: 12345.6 #2 FLOAT(7,1) betrag: 876543.21 #3 DECIMAL(9,2) vorname: Thomas #4 CHAR(30) name: Birnthaler #5 VARCHAR(30) binaer1: ABCDEFGHIJ #6 BINARY(10): Zeichen zu Code 41 ... 4A binaer2: abcdefgh #7 VARBINARY(8): Zeichen zu Code 61 ... 68 ts1: 2012-09-11 18:07:08 #8 TIMESTAMP ts2: 2012-09-07 11:22:33 #9 TIMESTAMP ts3: 2012-09-07 11:22:33 #10 TIMESTAMP datum: 2012-08-03 #11 DATE uhrzeit: 23:59:59 #12 TIME datumzeit: 2012-08-03 23:59:59 #13 DATETIME jahr: 2012 #14 YEAR(4) wahr: 1 #15 BOOL flags: A #16 BIT(8) dokument: Dokument(text) #17 TEXT antwort: Vielleicht #18 ENUM menge: gross,reich,maechtig #19 SET datei: Blobdaten(binaer) #20 BLOB 1 row in set (0.00 sec) Alternative Namen für einige Datentypen: +-----------+-----------------------------------+ | Name | Alternativer Name | +-----------+-----------------------------------+ | BOOL | BOOLEAN, TINYINT(1) | | CHAR | CHARACTER | | DECIMAL | DEC, NUMERIC | | DOUBLE | REAL, DOUBLE PRECISION | | INT | INTEGER | | NCHAR | NATIONAL CHAR, NATIONAL CHARACTER | | VARCHAR | CHARACTER VARYING | +-----------+-----------------------------------+ Beispiele für die Angabe von Konstanten (Literalen) für die Datentypen (Q = Quotieren des Wertes mit "..." oder '...' notwendig): +-----------+---+-----------------------------------------------------+ | Datentyp | Q | Beispiele für Konstanten/Literale | +-----------+---+-----------------------------------------------------+ | INT | - | 0 123 -456 +7899123 0x10EF x'10EF' b'11010' | | FLOAT | - | 0.0 1.23 -0.456 1.23e14 -12E-34 | | DECIMAL | - | 0.0 -123.000 +456789.00 | +-----------+---+-----------------------------------------------------+ | BOOL | - | TRUE FALSE 0 1 "aaa" "" | | BIT | x | b'10101100' 0x10EF x'10EF' | +-----------+---+-----------------------------------------------------+ | CHAR | x | "Hallo" 'Hallo' "" '' | | VARCHAR | x | "Hallo" 'Hallo' "" '' | | BINARY | x | 0x10EF x'10EF' _utf8'uc' "" '' | | VARBINARY | x | 0x10EF x'10EF' _utf8'uc' "" '' | +-----------+---+-----------------------------------------------------+ | YEAR | - | 69 99 13 1972 2010 2100 | | DATE | x | "2009-10-18" "0000-00-00" '2009-10-00' | | TIME | x | "23:59:59" '09:45' "00:00:00" | | DATETIME | x | "2009-10-18 22:30:00" YYYYMMDDhhmmss | | | x | "2009-10-18 22:30:00.123456" YYYYMMDDhhmmss.nnnnnn | | TIMESTAMP |x/-| "2009-10-18 22:30:00" 20091018223000 (Zahl, sic!) | | | | "2009-10-18 22:30:00.123456" | | | | 20091018223000.12345 (Zahl, sic!) | +-----------+---+-----------------------------------------------------+ | TEXT | x | "Hallo" 'Hallo' "" '' _utf8'uc' | | BLOB | x | 0x10EF x'10EF' b'11010' "Daten..." 'Daten...' | +-----------+---+-----------------------------------------------------+ | ENUM | x | "1.Wert" '5.Wert' | | SET | x | "1.Wert,5.Wert,8.Wert" "5.Wert" "" (leer) | +-----------+---+-----------------------------------------------------+ Defaultwert und Defaultattribut NULL/NOT NULL für Datentypen: +-----------+--------------------------------------------------+-------------+ | Datentyp | Defaultwert | Defaultfall | +-----------+--------------------------------------------------+-------------+ | INT | 0 | NULL | | FLOAT | 0.0 | NULL | | DECIMAL | 0.0 | NULL | +-----------+--------------------------------------------------+-------------+ | BIT | b'' (kein Bit gesetzt) | NULL | | BOOL | 0 (FALSE) | NULL | +-----------+--------------------------------------------------+-------------+ | CHAR | "" (leerer String) | NULL | | VARCHAR | "" (leerer String) | NULL | | BINARY | "" (leere Bytefolge) | NULL | | VARBINARY | "" (leere Bytefolge) | NULL | +-----------+--------------------------------------------------+-------------+ | YEAR | 00/0000 | NULL | | DATE | "0000-00-00" | NULL | | TIME | "00:00:00" | NULL | | DATETIME | "0000-00-00 00:00:00" | NULL | | TIMESTAMP | CURRENT_TIMESTAMP() (1.Spalte, akt. Datum+Zeit)| NOT NULL(!) | | | "0000-00-00 00:00:00" (restliche Spalten) | NULL | +-----------+--------------------------------------------------+-------------+ | TEXT | "" (leerer String) | NULL | | BLOB | "" (leerer String) | NULL | +-----------+--------------------------------------------------+-------------+ | ENUM | 0 (falls NULL), "1. Wert" (falls NOT NULL) | NULL | | SET | "" (leere Menge) | NULL | +-----------+--------------------------------------------------+-------------+ HINWEISE: * Datentyp-Konvertierung erfolgt entweder automatisch oder explizit per (BINARY entspricht CAST( AS BINARY)): BINARY # GROSS/kleinschreibung berücksichtigen CAST( AS ) # Datentyp von in umwandeln CONVERT( AS ) # Datentyp von in umwandeln CONVERT( USING ) # Zeichensatz von konvertieren * Zieldatentyp einer Datentyp-Konvertierung kann sein: +--------------------+------------------------------------------+ | BINARY[(n)] | Max. n Byte nutzen (mit 0x00 auffüllen) | | CHAR[(n)] | Max. n Byte nutzen (mit Space auffüllen) | | DATE | | | DATETIME | | | DECIMAL[(m[,d])] | | | SIGNED [INTEGER] | | | TIME | | | UNSIGNED [INTEGER] | | +--------------------+------------------------------------------+ * Folgende Datentyp-Konvertierungen sind möglich: # +----------------------+-------------------------------------+ # | Von | Nach | # +----------------------+-------------------------------------+ # | DECIMAL | CHAR VARCHAR DATE TIME DATETIME | # | CHAR VARCHAR | DECIMAL DATE TIME DATETIME | # | DATE TIME DATETIME | CHAR VARCHAR | | BLOB TEXT | (keine!) | +----------------------+-------------------------------------+ # * INT/FLOAT/DOUBLE: Bei Ganzzahlen ist in Klammern eine "Darstellungsgröße" (1-255) angebbar, bei Fließkommazahlen die "Darstellungsgröße" und die "Anzahl der Nachkommastellen" (DSG >= NKST + 2). Sie bestimmen nicht die Länge oder Genauigkeit der gespeicherten Zahl, sondern legen fest, mit welcher Breite und welcher Nachkommstellenanzahl die AUSGABE erfolgen soll. * FLOAT/DOUBLE: Rundungsfehler beim Rechnen möglich, besser DECIMAL nehmen, wenn es um finanzmathematische Rechnungen geht (z.B. Buchhaltung). * DECIMAL: Zahlen dieses Typs sind vor MY!5.0 in Wirklichkeit Strings mit je einem Zeichen pro Ziffer, für das Komma und für das Vorzeichen (STD: 10.0) und werden bei Rechnungen nach DOUBLE konvertiert (max. 15 Stellen genau). Ab MY!5.0 wird dafür "Precision Math" verwendet, d.h. eine kompaktere (9 Ziffern pro 4 Byte + Restziffern) und genauere Darstellung (max. 64 St.) benutzt und bei Rechnungen nicht mehr nach DOUBLE umgewandelt. Hat ähnliches Verhalten wie BCD-Format (Binary Coded Digits) für kaufmännisches Rechnen. Kaufmännische oder finanzmathematische Daten wie Preise, Umsatz, ... mit DECIMAL darstellen (vermeidet Rundungsfehler von FLOAT/DOUBLE) oder statt in Euro mit 2 Nkst besser in Cent ohne Nkst in INT speichern. ACHTUNG: Dezimalkomma in Zahl (z.B. 1,50) ist (meist) ein Syntaxfehler, Dezimalkomma in String (z.B. "1,50") schneidet Rest nach Komma ab, falls als Zahl interpretiert (ignoriert). * Zahlen können keine führenden 0-en speichern + Falls das notwendig ist, CHAR/VARCHAR verwenden - Damit kann man trotzdem rechnen - Preis: Konvertierung String --> Zahl zur Ausführungszeit (langsam)! + Alternativ ZEROFILL (feste Gesamtlänge abhängig von Darstellungsbreite) * BOOL: Kennt die Konstanten TRUE (Wahr, Wert 1) und FALSE (Falsch, Wert 0). Jeder Wert außer 0, FALSE (und NULL) ist TRUE (Wahr)! * YEAR: Deckt folgende Bereiche ab (d.h. nur 255 Jahre darstellbar) YEAR(2): 1970-2069 (00-69 --> 20XX, 70- 99 --> 19XX) YEAR(4): 1900-2155 (00-99 --> 19XX, 100-255 --> 2XXX) ACHTUNG: Die Abbildungsregeln YY --> YYYY unterscheiden sich! YEAR(2) wird seit MY!5.X.X automatisch auf YEAR(4) abgebildet (deprecated) * DATE: Werte decken die Jahre 01.01.1000-31.12.9999 ab. Jeder Teil (Tag, Monat, Jahr) ist auf "00" setzbar, um auszudrücken, dass ein Datumteil unbekannt ist. Derartige Teile werden bei Sortierung als 1. Wert einsortiert. Ein insgesamt ungültiger Datumswert wird als "0000-00-00" gespeichert. * Per SQL-Modus ist steuerbar, welche ungültigen Datumswerte akzeptiert werden: +---------------------+----------------------------------------------------+ | sql_mode = "..." | Bedeutung | +---------------------+----------------------------------------------------+ | ALLOW_INVALID_DATES | Beliebige Komb. von Mon=1..12 + Tag=1..31 erlaubt | | NO_ZERO_DATE | 0000-00-00 verboten (aber YYYY-01-00 / YYYY-00-01) | | NO_ZERO_IN_DATE | Jahr=00 oder Monat=00 oder Tag=00 nicht erlaubt | +---------------------+----------------------------------------------------+ Beispiel ("_" und "-" in Option und Konfig-Datei möglich): --sql_mode="ALLOW_INVALID_DATES,NO_ZERO_DATE" # "mysqld"-Start sql_mode = "" # In "my.cnf" SET GLOBAL sql_mode = "ALLOW_INVALID_DATES"; # Server-Global SET SESSION sql_mode = "NO_ZERO_DATE,NO_ZERO_IN_DATE"; # Sitzungsbezogen SELECT @@GLOBAL.sql_mode; # Globaler Wert SELECT @@SESSION.sql_mode; # Sitzungsbez. Wert * TIME: Werte decken Zeitbereich -838:59:59.000000 bis 838:59:59.999999 ab + Ab MY!5.6.4 auch auf Mikrosekunden .nnnnnn genau: TIME() (Fsp = Fractional seconds part = 0-6 Stellen als zusätzliche Angabe) * DATETIME: Zusammenfassung von DATE und TIME. Abhängig vom SQL-Modus auch ungültige Datumswerte (z.B. "00.00.0000" oder Jahr/Tag/Monat="0") speicherbar. + Ab MY!5.6.4 auch auf Mikrosekunden .nnnnnn genau: DATETIME() (Fsp = Fractional seconds part = 0-6 Stellen als zusätzliche Angabe) * TIMESTAMP: Wert als Anzahl Sekunden seit 1.1.1970 00:00:00 gespeichert, + Zeitraum: 01.01.1970 00:00:01 ... 19.01.2038 03:14:07 (UTC) + UNIX-Zeitrechnung mit 32-Bit Zahl mit Vorzeichen --> Y2K-Problem! + Unabhängig vom SQL-Modus nur gültige Datumswerte möglich (kein "00.00.0000" oder Tag/Monat="0"). + Ab MY!5.6.4 auch auf Mikrosekunden .nnnnnn genau: TIMESTAMP() (Fsp = Fractional seconds part = 0-6 Stellen als zusätzliche Angabe) Eine TIMESTAMP-Angabe hat die Form YYYYMMDDhhmmss.nnnnnn (Zahl oder "String") oder "YYYY-MM-DD hh:mm:ss.nnnnnn" ("String"), eine TIMESTAMP-Ausgabe hat je nach MySQL-Version unterschiedliche Form: YYYYMMDDhhmmss.nnnnnn # Alt (vor MY!5.4) "YYYY-MM-DD hh:mm:ss.nnnnnn" # Neu (seit MY!5.4) ACHTUNG: Der Wert der 1. TIMESTAMP-Spalte in einer Tabelle wird bei JEDER Änderung eines Datensatzes auf aktuelle(s) Datum + Uhrzeit CURRENT_TIMESTAMP() gesetzt (weitere TIMESTAMP-Spalten bleiben unverändert). Soll diese Spalte bei Änderungen des Datensatzes unverändert bleiben, ist ihr explizit der aktuelle Wert zuzuweisen: UPDATE ... SET ..., ts = ts; ACHTUNG: Ohne Angabe des Attributs NULL/NOT NULL erlauben alle Datentypen den Wert NULL außer dem Datentyp TIMESTAMP. Hier ist explizit das Attribut NULL anzugeben, wenn der NULL-Wert erlaubt sein soll. Lässt man Teile am Ende der Zeitangabe YYYYMMDDhhmmss / "YYYY-MM-DD hh:mm:ss" weg, dann wird der KLEINSTE mögliche Wert dafür eingesetzt. Die Bedingungen (a) bis (f) lassen also am rechten Rand des Zeitraums einiges weg, was man zunächst so nicht erwartet. Erst Bedingung (7) umfasst den beabsichtigten Zeitraum vollständig: # 1.1.12 00:00:00 .. ts BETWEEN 2012 AND 2013 # .. 1.1.13 00:00:00 (a) ts BETWEEN 201201 AND 201301 # .. 1.1.13 00:00:00 (b) ts BETWEEN 20120101 AND 20130131 # .. 31.1.13 00:00:00 (c) ts BETWEEN 20120101000000 AND 20130131000000 # .. 31.1.13 00:00:00 (d) ts BETWEEN 2012010100 AND 2013013123 # .. 31.1.13 23:00:00 (e) ts BETWEEN 201201010000 AND 201301312359 # .. 31.1.13 23:59:00 (f) ts BETWEEN 20120101000000 AND 20130131235959 # .. 31.1.13 23:59:59 (g) HINWEIS: Alle Elemente in Zeitangaben der Form YYYYMMDDhhmmss / "YYYY-MM-DD hh:mm:ss" sind mit 4 (Jahr) bzw. 2 Ziffern (sonst) anzugeben. Es ist unzulässig, die führende Null bei einstelligem Tag, Monat, Stunde, Minute und Sekunde wegzulassen. * CHAR(n) speichert n Zeichen mit Leerzeichen aufgefüllt (SPACE-padded), beim Lesen werden die Leerzeichen ENTFERNT (stripped). BINARY(n) speichert n Byte mit NUL-Bytes aufgefüllt (NUL-padded), beim Lesen werden die NUL-Bytes NICHT ENTFERNT (not stripped). * VARCHAR(n) speichert 0-n Zeichen PLUS eine Länge (1/2 Byte). VARBINARY(n) speichert 0-n Byte PLUS eine Länge (1/2 Byte). * ACHTUNG: CHAR, VARCHAR und TEXT ignorieren GROSS/kleinschreibung beim Vergleichen und Sortieren (case-INsensitive). BINARY, VARBINARY und BLOB berücksichtigen GROSS/kleinschreibung beim Vergleichen und Sortieren (case-sensitive). * TEXT/BLOB: Ein BLOB ist ein "Binary Large OBject" der Länge 0-4 Mrd Byte. Nicht im Datensatz gespeichert, dort steht nur "Zeiger" der Größe L Byte. Die Daten selber stehen pro Objekt an einer anderen Stelle. + TEXT: Textdaten, Vergleich und Sortierung erfolgt anhand Zeichensatz und Collation (ohne Berücksichtigung der GROSS/kleinschreibung). + BLOB: Binärdaten, Vergleich und Sortierung erfolgt anhand der Byte-Codes. Entsprechen VARCHAR/VARBINARY mit folgenden Unterschieden: + TEXT/BLOB-Spalten in Indices MÜSSEN eine Präfix-Längenangabe haben. + TEXT/BLOB-Spalten können KEINE Defaultwerte haben. + Speicherung erfolgt nicht im Datensatz sondern außerhalb, im Datensatz steht nur ein Zeiger. * ENUM: Wie Datentyp (VAR)CHAR verwendbar, der String wird aber als ZAHL gespeichert. Als Werte sind nur Elemente einer vordefinierten Werteliste erlaubt, anstelle des Elements wird platzsparend seine Nummer gespeichert. Ein String, der nicht in der Werteliste enthalten ist, entspricht NULL. Auch Index 1..N gemäß Werteliste speicherbar (1="Elem1", 2="Elem2", ...). * SET: Wie Datentyp (VAR)CHAR verwendbar, eine Kombination von Strings wird als BITMUSTER gespeichert. Als Wert ist eine beliebige Kombination ("Menge") der Werte aus einer vordefinierten Werteliste (max. 64 Werte) erlaubt (auch die leere Menge ""). Anstelle der Element-Kombination wird platzsparend ein Bitmuster gespeichert, jede Bitposition entspricht einem Element der Menge. + Bit 1 an Bitposition N heißt, das N-te Elem. befindet sich in der Menge, + Bit 0 an Bitposition N heißt, das N-te Elem. befindet sich NICHT in der M. * BIT: Kann eine Menge von 1..64 Bit (1-8 Byte) aufnehmen. Jedes Bit ist entweder gesetzt (Wert 1) oder nicht gesetzt (Wert 0). 4a) Datentyp-Optimierung (Performance/Speicherplatz) ---------------------------------------------------- * Kleinstmöglichen Datentyp nutzen (z.B. MEDIUMINT statt INT) --> Weniger Festplattenplatz belegt --> Weniger Platten-I/O --> Schneller! ABER: Zukunftssicher auslegen (Y2K-Problem, IPv4 --> IPv6, UNIX-Timestamp endet am 19.01.2038)! * Spalten möglichst "NOT NULL" deklarieren! --> Weniger Platzverbrauch (NULL benötigt zusätzl. Byte pro Spalte) --> Index schneller! * Mind. eine VARCHAR/VARBINARY/TEXT/BLOB-Spalte --> VARIABLE Record-Länge Nur CHAR/BINARY-Spalten --> FIXE Record-Länge --> 4b) Fixe/Variable Record-Länge (Row-Format) * In SELECT keine Spalten-Konvertierung in der WHERE-Bedingung durchführen --> Index sonst nicht nutzbar --> Für jeden Satz notwendig --> langsam! ACHTUNG: Findet implizit statt, falls mit Textspalte GERECHNET wird! * In JOIN verwendete Spalten sollten exakt gleichen Datentyp + Länge haben, --> Index nutzbar --> Bei (VAR)CHAR/(VAR)BINARY/TEXT/BLOB unterschiedliche Länge toleriert * MySQL-Optimierer macht selbständig folgende Änderungen bei CREATE/ALTER TABLE (Speicherplatz minimal, Tabellenstruktur optimiert, autom. Konvertierung) +-----------------+--------------------------------------------------------+ | Datentyp | Automatische Änderung | +-----------------+--------------------------------------------------------+ | VARCHAR(N<=4) | CHAR(N) | | VARBINARY(N<=4) | BINARY(N) | | CHAR(N>4) | VARCHAR(N) (falls mind. eine VARCHAR-Spalte vorhanden) | | TIMESTAMP(N) | TIMESTAMP (2 <= N geradzahlig <= 14) | | PRIMARY KEY | Spalten erhalten Attribut NOT NULL | | YEAR(2) | YEAR(4) | +-----------------+--------------------------------------------------------+ 4b) Fixe/Variable Record-Länge (Row-Format) ------------------------------------------- * Datensätze (Rows) können max. 65535 Byte lang sein (abhängig von Engine auch weniger) --> 26) Begrenzungen von MySQL (Limits) * FIXES Rowformat (feste Länge): ALLE Spalten NICHT von folgenden 4 Datentypen. VARIABLES Rowformat (unterschiedl. Länge): Mind. EINE Spalte dieses Datentyps: VARCHAR > 4 Zeichen VARBINARY > 4 Zeichen ...BLOB ...TEXT * FIXES Rowformat hat Vorteile bei der Adressierung der Datensätze, nutzt aber evtl. Plattenplatz schlechter (wg. Füllbyte) * VARIABLES Rowformat nutzt Plattenplatz besser aus, benötigt aber weitere Bytes (<=255 --> 1, >255 --> 2) zur Längeninformation * Bei vielen Lösch/Einfüge-Operationen in Tabelle führt variables Rowformat leichter zur "Fragmentierung" (Zersplitterung) der Tabellendaten --> OPTIMIZE TABLE durchführen * Für Table Scan und Zugriff per Index ist fixes/variables Rowformat egal! * Besondere Einstellungen bei Tabellen-Definition + MyISAM: ROW_FORMAT=FIXED/DYNAMIC (myisampack + myisamchk -rq --> COMPRESSED) + InnoDB: ROW_FORMAT=REDUNDANT/COMPACT (20% weniger Platz, CPU erhöht) * TIP: Breite Tabelle in mehrere Teile aufsplitten (fixer + variabler Anteil) 4c) AUTO_INCREMENT ------------------ * Jedem Datensatz in Tabelle automatisch eindeutigen Key zuordnen + "Künstlicher" Schlüssel (von Datenbank generiert) + Eindeutige positive Zahl (negativ --> Große positive Zahl) - Startwert beim Anlegen der Tabelle wählbar (STD: 1): CREATE TABLE ... (...) ... AUTO_INCREMENT = ... ALTER TABLE ... AUTO_INCREMENT = ; - Alternativ Zeile mit Wert N-1 einfügen und wieder löschen * Nur in EINER Spalte pro Tabelle erlaubt + Spalten-Datentyp muss INT, FLOAT oder DOUBLE sein (DECIMAL nicht möglich, UNSIGNED schon) + Index MUSS darauf liegen (nicht unbedingt UNIQUE, aber sinnvoll; PRIMARY KEY am sinnvollsten) + Keine DEFAULT-Angabe möglich * Einfügen von NULL/0 in diese Spalte setzt nächsten noch nicht benutzten Wert + SQL-Modus "NO_AUTO_VALUE_ON_ZERO": 0 als Wert möglich (nicht empfohlen!) + Spaltenwert angegeben - Schon vorhanden --> Einfügen wird verweigert - Noch nicht vorhanden --> Eingefügt + nächster automatisch ermittelter um 1 höher (Lücken entstehen!) * Letzten verwendeten AUTO_INCREMENT-Wert direkt nach INSERT abfragen (nur in derselben Sitzung/Transaktion): SELECT LAST_INSERT_ID(); * HINWEIS: Damit Master-Master-Replikation möglich ist (2 gegenseitige Master oder Ring aus mehreren Mastern), müssen AUTO_INCREMENT-Spalten auf allen Mastern garantiert unterschiedliche Werte erzeugen. Dazu gibt es im MySQL-Server folgende Optionen: auto_increment_offset = # Startwert von AUTO_INCREMENT-Spalten auto_increment_increment = # Schrittweite von AUTO_INCREMENT-Spalten Ist die Anzahl der sich gegenseitig replizierenden Master z.B. 5, dann sollten die einzelnen Master verschiedene Offsets 1, 2, 3, 4, 5 erhalten und bei allen Mastern die Schrittweite auf 5 gesetzt werden. * Pro Tabelle ist ein eigener Startwert für AUTO_INCREMENT möglich, aber keine Schrittweite (diese ist nur pro Server einstellbar). * HINWEISE: + In PostgreSQL dafür Datentyp "SERIAL" verwenden + In Oracle durch "Sequence"-Objekt nachbilden (deutlich komplexer) Beispiel: CREATE TABLE pers ( nr INT NOT NULL AUTO_INCREMENT, # Spalte "nr" automatisch füllen vorname VARCHAR(30), # name VARCHAR(30), # PRIMARY KEY (nr) # Wg. AUTO_INCREMENT nötig ) AUTO_INCREMENT = 10; # Werte 1..9 überspringen # INSERT INTO pers (nr, vorname, name) # VALUES (NULL, "Thomas", "Birnthaler"), # --> nr=10 (Lücke von 1..9) (20, "Markus", "Mueller"); # --> nr=20 (Lücke von 11..19) # INSERT INTO pers (vorname, name) # VALUES ("Andrea", "Bayer"), # --> nr=21 ("Richard", "Seiler"), # --> nr=22 ("Heinz", "Bayer"); # --> nr=23 # INSERT INTO pers (nr, vorname, name) # VALUES (20, "Hans", "Birnthaler"); # --> verweigert 5) MySQL-Operatoren ------------------- Folgende Operatoren sind als Verknüpfung in Ausdrücken (Expressions) verwendbar: +------------+------------------------------------+--------------------------+ | Typ | Beispiele | Bemerkung | +------------+------------------------------------+--------------------------+ | Arithmetik | + - * / DIV % MOD | MOD/% = Modulo (Div.rest)| +------------+------------------------------------+--------------------------+ | Vergleich | < <= > >= | Nicht => und <=! | | | = != <> <=> | <=> ist NULL-sicheres = | | | BETWEEN AND | Ränder einschließen | | | NOT BETWEEN AND | Ränder nicht einschl. | | | IN (, , ...) | In Liste enthalten | | | NOT IN (, , ...) | Nicht in Liste enthalten | | | LIKE [ESCAPE ]| Wildcards "_" "%" (s.u.) | | | NOT LIKE | Wildcards "_" "%" (s.u.) | | | REGEXP/RLIKE | Regulärer Ausdruck (s.u.)| | | NOT REGEXP/RLIKE | Regulärer Ausdruck (s.u.)| | | SOUNDS LIKE | SOUNDEX(v1) = SOUNDEX(v2)| | | IS NULL/UNKNOWN | Gleich NULL/Unbekannt | | | IS NOT NULL/UNKNOWN | Ungleich NULL/Unbekannt | | | IS TRUE/FALSE | Gleich TRUE/FALSE | | | IS NOT TRUE/FALSE | Ungleich TRUE/FALSE | +------------+------------------------------------+--------------------------+ | Logisch | NOT ! | Erg: Nur TRUE/FALSE | | | OR || | Erg: Nur TRUE/FALSE | | | AND && | Erg: Nur TRUE/FALSE | | | XOR | Erg: Nur TRUE/FALSE | +------------+------------------------------------+--------------------------+ | Bit | | & ^ ~ << >> | OR AND XOR INV L/R-SHIFT | +------------+------------------------------------+--------------------------+ | Zeichen | BINARY ... | String binär interpret. | | | _CHARSET | String-Zeichensatz | | | COLLATE | String-Sortierordnung | +------------+------------------------------------+--------------------------+ Beispiele (Rechnen): SELECT 1 + 2 * 3 / 4 - 5; # --> -2.5 SELECT 17 / 3, 17 DIV 3, 17 % 3, 17 MOD 3; # --> 5.6667 5 2 2 Beispiele (Vergleich): SELECT 10 < 4, 2 <= 5, 1 = 3, 1 <> 3; # --> 0 1 0 1 SELECT 10 > 4, 2 >= 5, 1 <=> 3, 1 != 3; # --> 1 0 0 1 SELECT 0 <=> 0, 0 <=> NULL, NULL <=> 0, NULL <=> NULL; # --> 1 0 0 1 SELECT 10 BETWEEN 0 AND 12, 10 NOT BETWEEN 0 AND 12; # --> 1 0 SELECT 10 IN (1, 2, 10), 10 NOT IN (1, 2, 10); # --> 1 0 SELECT "abc" LIKE "%b%", "abc" NOT LIKE "%b%"; # --> 1 0 SELECT "abc" RLIKE "b", "abc" NOT RLIKE "b"; # --> 1 0 SELECT "abc" SOUNDS LIKE "ebz"; # --> 0 SELECT NULL IS NULL, NULL IS NOT NULL; # --> 1 0 SELECT TRUE IS TRUE, TRUE IS FALSE; # --> 1 0 SELECT TRUE IS NOT TRUE, TRUE IS NOT FALSE; # --> 0 1 Beispiele (Logische Verknüpfung): SELECT TRUE AND FALSE OR FALSE AND NOT TRUE; # --> 0 SELECT TRUE && FALSE || FALSE && ! TRUE; # --> 0 SELECT TRUE XOR FALSE, TRUE XOR TRUE, FALSE XOR TRUE; # --> 1 0 1 Beispiele (Bitweise Verknüpfung): SELECT BIN(b'11110001' | b'00111100'); # --> 11111101 (253) SELECT BIN(b'11110001' & b'00111100'); # --> 00110000 (48) SELECT BIN(b'11110001' ^ b'00111100'); # --> 11001101 (205) SELECT BIN(~ b'00110101'); # --> 11..1001010 SELECT BIN(~ b'00110101' & b'11111111'); # --> 11001010 (202) SELECT BIN(b'00110101' >> 3); # --> 00000110 (6) SELECT BIN(b'00110101' << 2); # --> 11010100 (212) SELECT BIN(~(b'00110101' & b'00001111')); # --> 11..111010 SELECT BIN(~(b'00110101' | b'00001111') ^ b'00110011'); # --> 11..110011 Hinweise: * Division "/" ist immer Fließkommadivision, Division "DIV" schneidet den Divisionsrest ab. * Standardmäßig gibt es keinen Stringverkettungs-Operator (z.B. & + . ||) --> CONCAT verwenden, damit sind beliebig viele Strings verkettbar (SET GLOBAL sql_mode = 'PIPES_AS_CONCAT' oder SET GLOBAL sql_mode = 'ANSI' --> Operator "||" verkettet Strings) CONCAT("abc", , "xyz") # Immer möglich "abc" || || "xyz" # Falls sql_mode='PIPES_AS_CONCAT/ANSI' * Falls in Stringverkettung NULL vorkommen kann --> Auf "" abbilden: CONCAT("abc", IF(ISNULL(), "", ), "xyz") CONCAT("abc", IFNULL(, ""), "xyz") * BINARY vor String in Vergleichen = != <> <=> < <= > >= BETWEEN...AND IN LIKE REGEX und in ORDER BY vor Spalte erzwingt Berück. der GROSS/kleinschreibung: SELECT * FROM pers WHERE BINARY name = "abc"; SELECT * FROM pers WHERE BINARY name != "abc"; SELECT * FROM pers WHERE BINARY name > "abc"; SELECT * FROM pers WHERE BINARY name <= "abc"; SELECT * FROM pers WHERE BINARY name BETWEEN "abc" AND "def"; SELECT * FROM pers WHERE BINARY name LIKE "abc%"; SELECT * FROM pers WHERE BINARY name REGEX "^abc$"; SELECT * FROM pers ORDER BY BINARY name ASC; * LIKE-Wildcards für beliebige/beliebig viele Zeichen (): "_" = GENAU EIN beliebiges Zeichen "%" = Beliebig viele beliebige Zeichen (auch 0!) * ESCAPE am Ende von LIKE setzt Entwertungs-Zeichen von "%" und "_" auf das Zeichen (STD: "\"). D.h. um mit LIKE nach den Zeichen "%" und "_" selbst zu suchen, muss man dieses Escape-Zeichen davor setzen: SELECT * FROM test WHERE name LIKE "\_\%"; # Standard "\" SELECT * FROM test WHERE name LIKE "@_@%" ESCAPE "@"; # Escape = "@" Hinweise zu NULL: * NULL kommt in Ausdruck vor --> Ergebnis NULL ("schwarzes Loch") * NULL kommt in Vergleich vor --> Ergebnis NULL (außer bei <=>) * Logische Ausdrücke verkürzt ausgewertet (Short-Cut/Circuit Evaluation): + FALSE AND ... --> sofort FALSE (ohne Auswertung von ...) + TRUE OR ... --> sofort TRUE (ohne Auswertung von ...) D.h. hat ... Wert NULL, ist das Gesamtergebnis trotzdem FALSE bzw. TRUE! * Ist NULL das Ergebnis einer WHERE-Bedingung, entspricht dies dem Wert FALSE. * IS UNKNOWN analog IS NULL (andere Schreibweise, bei Bool. Werten üblich): SELECT 0 IS UNKNOWN, 1 IS UNKNOWN, NULL IS UNKNOWN; # --> 0 0 1 SELECT 0 IS NULL, 1 IS NULL, NULL IS NULL; # --> 0 0 1 * "<=>" ist NULL-sicherer Vergleich auf gleichen Wert (Ersatz für "="): SELECT NULL <=> NULL; # --> 1 (bei "=" --> NULL) SELECT NULL <=> TRUE; # --> 0 (bei "=" --> NULL) SELECT NULL <=> FALSE; # --> 0 (bei "=" --> NULL) SELECT NULL <=> "abc"; # --> 0 (bei "=" --> NULL) SELECT NULL <=> ""; # --> 0 (bei "=" --> NULL) SELECT NULL <=> "NULL"; # --> 0 (bei "=" --> NULL) Verhalten identisch zu "=" falls NULL nicht als Verknüpfungswert vorkommt. Rangfolge/Priorität der Operatoren (durch Klammerung "(...)" ändern): +----+---------------------------------+-------------------------------------+ | Nr | Typ | Bemerkung | +----+---------------------------------+-------------------------------------+ | 1 | BINARY | GROSS/kleinschreibung beachten | | | _CHARSET COLLATE | Zeichensatz + Sortierung anpassen | | 2 | ! (NOT) | Negation (falls HIGH_NOT_PRECEDENCE)| | 3 | - ~ | Unäres Minus, Bitweise Invertierung | | 4 | || | Stringverkettung (falls | | | | sql_mode='PIPES_AS_CONCAT/ANSI') | | 5 | ^ | Bitweise XOR | | 6 | * / DIV % MOD | MOD/% = Modulo, DIV = ganzz. Div. | | 7 | + - | Addition/Subtraktion | | 9 | << >> | Bitweise Shift links/rechts | | 9 | & | Bitweise AND | | 10 | | | Bitweise OR | | 11 | = != <> <=> < <= > >= | Vergleich | | | IN IS LIKE REGEXP | Vergleich | | 12 | BETWEEN ... AND ... | Bereich | | | CASE ... WHEN ... THEN ... ELSE | Fallunterscheidung | | 13 | NOT | Logische Negation | | | | (ab MY!5.0.2 vom Vorrang her hier!) | | 14 | AND && | Logisch AND | | 15 | XOR | Logisch XOR | | 16 | OR || | Logisch OR (falls sql_mode="...") | | 17 | := | Zuweisung | +----+---------------------------------+-------------------------------------+ HINWEIS: Der Operator NOT hatte vor MY!5.0.2 die gleiche (hohe) Priorität wie der Operator !, seither hat er eine Priorität zwischen BETWEEN und AND. Die alte (hohe) Priorität erhält man mit folgendem SQL-Modus: SET GLOBAL sql_mode = 'HIGH_NOT_PRECENDENCE'; HINWEIS: Falls der OR-Operator "||" Strings verketten soll (ANSI-SQL Standard): SET GLOBAL sql_mode = 'PIPES_AS_CONCAT'; # Alternative 1 SET GLOBAL sql_mode = 'ANSI'; # Alternative 2 6) Boolesche Logik ------------------ In Boolescher Logik gibt es nur die beiden Konstanten TRUE und FALSE mit dem numerischen Wert 1 und 0. Umgekehrt werden alle numerischen Werte ungleich 0 auf TRUE abgebildet und der numerische Wert 0 auf FALSE. Strings im Booleschen Kontext werden in Zahlen umgewandelt, d.h. alle Strings mit Ergebnis "0" sind FALSE, die anderen sind TRUE: Logische Ausdrücke ergeben 1 für TRUE und 0 für FALSE SELECT TRUE, FALSE, NOT TRUE, NOT FALSE; # --> 1 0 0 1 SELECT NOT 0, NOT 1, NOT 123, NOT -456, NOT 7.89; # --> 1 0 0 0 0 SELECT NOT "", NOT "abc", NOT "123def", NOT "0def"; # --> 1 1 0 1 Rechnen mit TRUE/FALSE entspricht Rechnen mit 0/1: SELECT TRUE / FALSE; # --> NULL (Division durch 0!) SELECT FALSE / TRUE; # --> 0.0000 SELECT TRUE * FALSE; # --> 0 SELECT TRUE + FALSE; # --> 1 SELECT TRUE - FALSE; # --> 1 SELECT FALSE - TRUE; # --> -1 Das Ergebnis eines Booleschen Ausdrucks kann bereits bei Betrachtung eines TEILS der damit verknüpften Ausdrücke feststehen, egal welchen Wert die restlichen verknüpften Ausdrücke haben (Short-Cut/Short-Circuit Evaluation = Verkürzte Auswertung): AND # nur ausgewertet, falls TRUE ergibt OR # nur ausgewertet, falls FALSE ergibt Beispiel (SQRT(-1) ist nicht definiert): SELECT 0 AND SQRT(-1); # --> 0 (obwohl SQRT(-1) undefiniert) SELECT 1 AND SQRT(-1); # --> NULL (da SQRT(-1) undefiniert) SELECT 1 OR SQRT(-1); # --> 1 (obwohl SQRT(-1) undefiniert) SELECT 0 OR SQRT(-1); # --> NULL (da SQRT(-1) undefiniert) 7) Der Wert NULL ---------------- 7a) Eigenschaften von NULL -------------------------- * NULL steht für Wert "unbekannt" oder "undefiniert" * NULL ist weder die Zahl 0 noch der leere String "", noch TRUE oder FALSE, sondern etwas völlig anderes. * Fast alle Operationen mit NULL ergeben NULL ("Schwarzes Loch") + Ausnahmen: <=>, AND, OR, IS NULL, IS NOT NULL, ISNULL(), IFNULL(), NULLIF(), COALESCE() SUM(), AVG(), COUNT(), ... * Benötigt extra Speicherplatz in Tabelle (pro NULL-Spalte 1 Byte oder 1 Bit) --> Spalten möglichst mit NOT NULL DEFAULT kennzeichnen (STD: NULL) * NULL-sicheren Vergleich mit <=> statt = durchfuehren (MY!) SELECT 1 <=> NULL, NULL <=> NULL, 0 <=> NULL, "" <=> NULL; # --> 0 1 0 0 SELECT 1 = NULL, NULL = NULL, 0 = NULL, "" = NULL; # --> 4x NULL SELECT NULL IS NULL, 0 IS NULL, 1 IS NULL, "" IS NULL; # --> 1 0 0 0 SELECT NULL IS NOT NULL, 0 IS NOT NULL, 1 IS NOT NULL, # "" IS NOT NULL; # --> 0 1 1 1 SELECT ISNULL(NULL), ISNULL(0), ISNULL(1), ISNULL(""); # --> 1 0 0 0 Kommt Wert NULL in einem Ausdruck vor, so hat in der Regel der GESAMTE Ausdruck als Ergebnis den Wert NULL: SELECT 3 * 4 - 1 + NULL AS "Wert"; # --> NULL SELECT 1 = NULL, 1 != NULL, 1 < NULL, 1 > NULL; # --> 4x NULL SELECT NULL = 1, NULL = NULL, 0 = NULL; # --> 3x NULL SELECT NULL != 1, NULL != NULL, 0 != NULL; # --> 3x NULL SELECT CONCAT(NULL, "abc", "def", NULL, "123"); # --> NULL SELECT CONCAT("abc", "def", NULL, "123"); # --> NULL 7b) Prüfung auf Wert NULL ------------------------- Zur expliziten Prüfung auf den Wert NULL gibt es folgende Möglichkeiten: +---------------------------------+------------------------------------------+ | Ausdruck | Bedeutung | +---------------------------------+------------------------------------------+ | IS NULL | In WHERE-Bedingung | | IS NOT NULL | In WHERE-Bedingung | | IS NULL | In Ausdruck | | IS NOT NULL | In Ausdruck | | ISNULL() | 1 wenn = NULL, sonst 0 | | IFNULL(, ) | wenn nicht NULL, sonst | | IF(ISNULL(), , )) | (analog) | | NULLIF(, ) | NULL wenn =, sonst | | COALESCE(, , ...) | Erster Ausdruck != NULL | +---------------------------------+------------------------------------------+ Beispiel: SELECT NULL IS NULL, NULL IS NOT NULL; # --> 1 0 SELECT 0 IS NULL, 0 IS NOT NULL; # --> 0 1 SELECT 1 IS NULL, 1 IS NOT NULL; # --> 0 1 SELECT "" IS NULL, "" IS NOT NULL; # --> 0 1 SELECT ISNULL(NULL), ISNULL(0), ISNULL(1), ISNULL(""); # --> 1 0 0 0 SELECT IFNULL("eins", NULL), IFNULL(NULL, "zwei"); # --> eins zwei SELECT NULLIF("eins", "eins"), NULLIF("eins", "zwei"); # --> NULL eins SELECT COALESCE(NULL, NULL, "abc", NULL, 123); # --> abc 7c) Vergleiche mit NULL ----------------------- Vergleiche ergeben einen Booleschen Wert außer mit NULL wird verglichen, dann resultiert der Wert NULL als Ergebnis (außer bei "<=>"). Der normale Vergleich "=" liefert daher: +-------+-------+-------+-------+ | = | TRUE | FALSE | NULL | # Ein NULL bei "=" ergibt insgesamt NULL +-------+-------+-------+-------+ | TRUE | TRUE | FALSE | NULL | | FALSE | FALSE | TRUE | NULL | | NULL | NULL | NULL | NULL | +-------+-------+-------+-------+ Ebenso gilt für den Vergleich "!=" (oder "<>"): +-------+-------+-------+-------+ | != | TRUE | FALSE | NULL | # Ein NULL bei "!=" ergibt insgesamt NULL +-------+-------+-------+-------+ | TRUE | FALSE | TRUE | NULL | | FALSE | TRUE | FALSE | NULL | | NULL | NULL | NULL | NULL | +-------+-------+-------+-------+ Die anderen Vergleiche ("<", "<=", ">" und ">=") ergeben: +-------+------------+-------+ | | !NULL | NULL | # Ein NULL bei ergibt insgesamt NULL +-------+------------+-------+ | !NULL | TRUE/FALSE | NULL | | NULL | NULL | NULL | +-------+------------+-------+ Der NULL-sichere Vergleich "<=>" liefert hingegen (MY!): +-------+-------+-------+-------+ | <=> | TRUE | FALSE | NULL | +-------+-------+-------+-------+ | TRUE | TRUE | FALSE | FALSE | # NULL <=> TRUE ergibt FALSE | FALSE | FALSE | TRUE | FALSE | # NULL <=> FALSE ergibt FALSE | NULL | FALSE | FALSE | TRUE | # NULL <=> NULL ergibt TRUE +-------+-------+-------+-------+ 7d) Boolesche Logik mit NULL (ternär/dreiwertig) ------------------------------------------------ Die Booleschen Operatoren AND, &&, OR, ||, XOR, NOT und ! werden auf die Verknüpfung mit NULL-Werten erweitert (ternäre oder dreiwertige Logik). Aufgrund der "verkürzten Auswertung" von AND und OR gilt: * FALSE links von AND ergibt sofort FALSE (egal was rechts davon steht) * TRUE links von OR ergibt sofort TRUE (egal was rechts davon steht) * FALSE in AND-Verknüpfung ergibt FALSE (egal ob NULL-Werte verknüpft) * TRUE in OR-Verknüpfung ergibt TRUE (egal ob NULL-Werte verknüpft) * NULL in XOR-Verknüpfung ergibt NULL (egal was sonst verknüpft) Hier die dreiwertigen Auswertungstabellen der Booleschen Operatoren: +-------+-------+-------+-------+ | AND &&| TRUE | FALSE | NULL | # Ein FALSE bei AND ergibt insgesamt FALSE! +-------+-------+-------+-------+ | TRUE | TRUE | FALSE | NULL | | FALSE | FALSE | FALSE | FALSE | | NULL | NULL | FALSE | NULL | +-------+-------+-------+-------+ +-------+-------+-------+-------+ | OR || | TRUE | FALSE | NULL | # Ein TRUE bei OR ergibt insgesamt TRUE! +-------+-------+-------+-------+ | TRUE | TRUE | TRUE | TRUE | | FALSE | TRUE | FALSE | NULL | | NULL | TRUE | NULL | NULL | +-------+-------+-------+-------+ +-------+-------+-------+-------+ | XOR | TRUE | FALSE | NULL | # Ein NULL bei XOR ergibt insgesamt NULL! +-------+-------+-------+-------+ | TRUE | FALSE | TRUE | NULL | | FALSE | TRUE | FALSE | NULL | | NULL | NULL | NULL | NULL | +-------+-------+-------+-------+ +-------+-------+ | NOT ! | | # NULL negiert ergibt NULL! +-------+-------+ | TRUE | FALSE | | FALSE | FALSE | | NULL | NULL | +-------+-------+ Beispiele: SELECT TRUE AND NULL, NULL AND TRUE; # --> NULL NULL SELECT FALSE AND NULL, NULL AND FALSE; # --> 0 0 SELECT TRUE && NULL, NULL && TRUE; # --> NULL NULL SELECT FALSE && NULL, NULL && FALSE; # --> 0 0 SELECT TRUE OR NULL, NULL OR TRUE; # --> 1 1 SELECT FALSE OR NULL, NULL OR FALSE; # --> NULL NULL SELECT TRUE || NULL, NULL || TRUE; # --> 1 1 SELECT FALSE || NULL, NULL || FALSE; # --> NULL NULL SELECT TRUE XOR NULL, NULL XOR TRUE; # --> NULL NULL SELECT FALSE XOR NULL, NULL XOR FALSE; # --> NULL NULL SELECT NOT NULL, ! NULL; # --> NULL NULL 8) Reguläre Ausdrücke in MySQL (RegEx) -------------------------------------- Eigenschaften: * Reguläre Ausdrücke nennt man auch "RegEx" oder "Pattern" (Muster) (von englisch "Regular Expressions") * Vergleichen wie LIKE/NOT LIKE Texte mit einem Muster --> TRUE/FALSE (stark erweiterte Fähigkeiten gegenüber LIKE/NOT LIKE) * Kennen wesentlich mehr "Metazeichen" als LIKE (kennt nur "%" und "_"), um ein Muster auszudrücken, das mit dem Text verglichen wird + Wildcard "_" <=> RegEx "." + Wildcard "%" <=> RegEx ".*" * Operatoren: REGEX RLIKE NOT REGEX NOT RLIKE * Vergleiche mit Regulären Ausdrücken können KEINEN Index nutzen! * RegEx Muss IRGENDWO in Zeichenkette passen, damit TRUE (außer bei Verwendung von ^ bzw. $, dann am Anfang bzw. Ende) +--------------+-----------------------------------------------------------+ | Metazeichen | Bedeutung | +--------------+-----------------------------------------------------------+ | X | Zeichen X steht für sich selbst (sofern kein Metazeichen) | | . | 1 beliebiges Zeichen | | [...] | 1 Zeichen aus Zeichenmenge ... (z.B. [A-Z]) | | [^...] | 1 Zeichen NICHT aus Zeichenmenge ... (z.B. [^0-9]) | | [[:CLASS:]] | Zeichenklasse (siehe unten, [[ und ]] sind doppelt!) | +--------------+-----------------------------------------------------------+ | ...* | 0-N mal Zeichen/geklammerter Ausdruck ... davor (= {0,}) | | ...+ | 1-N mal Zeichen/geklammerter Ausdruck ... davor (= {1,}) | | ...? | 0,1 mal Zeichen/geklammerter Ausdruck ... davor (= {0,1}) | | ...{N} | N mal Zeichen/geklammerter Ausdruck ... davor | | ...{N,M} | N-M mal Zeichen/geklammerter Ausdruck ... davor | +--------------+-----------------------------------------------------------+ | ^ | Stringanfang | | $ | Stringende | | [[:<:]] | Wortanfang (Wort = Folge von alnum + "_") | | [[:>:]] | Wortende (Wort = Folge von alnum + "_") | +--------------+-----------------------------------------------------------+ | (...) | Ausdruck ... gruppieren (für | * + ? {...}) | | ...|... | Alternation/Alternative (ODER) | | \X | Metazeichen X quotieren für ^ $ . [ ] | * + ? { } ( ) \ | | \n \r \t ... | Escape-Sequenz (Steuerzeichen) | +--------------+-----------------------------------------------------------+ Zeichenklassen fassen Zeichen mit einer bestimmten Eigenschaft zusammen (genauer Inhalt hängt von der locale-Einstellung per CHARACTER SET ab): +--------+-----------------------------------------------------------------+ | Klasse | Bedeutung | +--------+-----------------------------------------------------------------+ | alnum | Buchstaben und Ziffern (alpha + digit) | | alpha | Buchstaben (upper + lower) | | cntrl | Steuerzeichen (Code 0-31) | | blank | Leerzeichen (Space + Tabulator) | | digit | Ziffern (0-9) | | empty | Leerzeichen (Space + Tabulator) | | graph | Druckbare Zeichen (print ohne Leerzeichen) | | lower | Kleine Buchstaben (a-z) | | print | Druckbare Zeichen (mit Leerzeichen) | | punct | Interpunktionszeichen (print -- space -- alnum) | | space | Leerraum (Space, FormFeed, Newline, Carriage Return, Tabulator) | | upper | Große Buchstaben (A-Z) | | xdigit | Hexadezimalziffern (0-9 + a-f/A-F) | +--------+-----------------------------------------------------------------+ Beispiele (keine Indices verwendbar!): SELECT nr, name FROM pers WHERE name REGEXP "^abc$"; # Genau "abc" ... WHERE name = "abc"; # (analog) ... WHERE name REGEXP "abc$"; # "abc" am Ende ... WHERE name LIKE "%abc"; # (analog) ... WHERE name REGEXP "abc"; # "abc" enthalten ... WHERE name LIKE "%abc%"; # (analog) ... WHERE name NOT REGEXP "abc"; # KEIN "abc" enthalten ... WHERE name NOT LIKE "%abc%"; # (analog) ... WHERE name RLIKE "^a*$"; # "", "a", "aa", "aaa", ... ... WHERE name = "" OR name = "a" OR ... # (analog) ... WHERE name RLIKE "^a+$"; # "a", "aa", "aaa", ... ... WHERE name = "a" OR name = "aa" OR ... # (analog) ... WHERE name RLIKE "thomas|hans"; # Enth. "thomas" oder "hans" ... WHERE name = "thomas" OR name = "hans"; # (analog) ... WHERE name RLIKE "ab{1,8}a"; # "aba","abba", ...max. 8 "b" ... WHERE name = "aba" OR name = "abba" OR ...; # (analog) Mit LIKE, AND, OR nicht mehr formulierbar: ... WHERE name RLIKE "^[0-9]+$"; # Nur Zahl akzept. (mind. 1 Ziffer) ... WHERE name RLIKE "^[[:digit:]]+$"; # Nur Zahl akzept. (mind. 1 Ziffer) 9) MySQL-Funktionen ------------------- MySQL kennt eine große Menge eingebauter Funktionen, die beliebig in SELECT-Anweisungen zum Konvertieren/Verknüpfen von Spalten sowie in WHERE-Bedingungen einsetzbar sind (auf diesen Spalten liegende Indices sind in so einem Fall nicht nutzbar). Beispiele für den Aufruf von in MySQL eingebauten Funktionen: SELECT CONCAT(vorname, " ", name) AS "Name" FROM pers; SELECT (YEAR(CURDATE()) - YEAR(geburtsdatum)) AS "Alter" FROM age; ### SELECT NOW(), ADDDATE(NOW(), INTERVAL 14 DAY); Folgende Funktionen sind in MySQL vordefiniert. Diese Liste ist erweiterbar durch Laden von externen User Defined Functions (UDF) und durch in SQL programmierte benutzerdefinierte Funktionen (--> 19) Stored Routines): +---------------+-----------------------------------------------------------+ | Bereich | Eingebaute Funktionen + ihre Parameter | +---------------+-----------------------------------------------------------+ | Datenbank | USER() SESSION_USER() SYSTEM_USER() # Anmeldungsuser | | | CURRENT_USER() # Interner User | | | DATABASE() SCHEMA() | | | CONNECTION_ID() # Sitzungs-ID | | | VERSION() # MySQL-Server | +---------------+-----------------------------------------------------------+ | Queryergebnis | FOUND_ROWS() # Bei LIMIT mit SQL_CALC_FOUND_ROWS | | | LAST_INSERT_ID() # Bei AUTO_INCREMENT | | | ROW_COUNT() # Bei INSERT, UPDATE, DELETE, REPLACE | +---------------+-----------------------------------------------------------+ | Arithmetik | ABS(zahl) # Betrag | | | GREATEST(zahl1, zahl2, ...) # Größte Zahl | | | LEAST(zahl1, zahl2, ...) # Kleinste Zahl | | | MOD(x, y) # Divisionsrest x / y | | | RAND([seed]) # Zufallszahl 0 <= z < 1 | | | SIGN(zahl) # Vorzeichen | +---------------+-----------------------------------------------------------+ | Potenzierung | EXP(zahl) | | | POW(num, exp) POWER(num, exp) | | | SQRT(zahl) | +---------------+-----------------------------------------------------------+ | Logarithmus | LN(zahl) | | | LOG(zahl [, basis]) | | | LOG2(zahl) | | | LOG10(zahl) | +---------------+-----------------------------------------------------------+ | Trigonometrie | ACOS(zahl) | | | ASIN(zahl) | | | ATAN(zahl) | | | ATAN2(x, y) | | | COS(zahl) | | | COT(zahl) | | | DEGREES(rad) | | | PI() | | | RADIANS(dec) | | | SIN(zahl) | | | TAN(zahl) | +---------------+-----------------------------------------------------------+ | Rundung | CEILING(zahl) CEIL(zahl) | | | FLOOR(zahl) | | | FORMAT(zahl, dezstellen [, locale]) | | | ROUND(zahl [, dezstellen]) | | | TRUNCATE(zahl, dezstellen) | +---------------+-----------------------------------------------------------+ | Bit | BIT_COUNT(zahl) | | | BIT_LENGTH(str) | | | EXPORT_SET(zahl, ein, aus [,trennzeichen, [numbits]]) | | | MAKE_SET(bits, str1, str2, ...) | +---------------+-----------------------------------------------------------+ | String | COERCIBILITY(str) | | | COLLATION(str) | | | INSERT(str, pos, len, neu) | | | WEIGHT_STRING(str [AS {CHAR|BINARY} (n)] [LEVEL n] [,opt])| +---------------+-----------------------------------------------------------+ | Stringlänge | CHAR_LENGTH(str) CHARACTER_LENGTH(str) | | | LENGTH(str) CHAR/CHARACTER/OCTET_LENGTH(str) | +---------------+-----------------------------------------------------------+ | String- | CONCAT(str1, str2, ...) | | verkettung | CONCAT_WS(trennzeichen, str1, str2, ...) | +---------------+-----------------------------------------------------------+ | String- | REPEAT(str, anz) | | wiederholung| SPACE(anz) | +---------------+-----------------------------------------------------------+ | Stringteile | LEFT(str, len) | | extrahieren | MID(str, pos, len) --> SUBSTR(...) | | | RIGHT(str, len) | | | SUBSTR(str, pos) SUBSTRING(...) | | | SUBSTR(str, pos, len) SUBSTRING(...) | | | SUBSTR(str FROM pos FOR len) SUBSTRING(...) | | | SUBSTR(str FROM len) SUBSTRING(...) | | | SUBSTR_INDEX(str, zeichen, anz) SUBSTRING_INDEX)(...) | +---------------+-----------------------------------------------------------+ | String | LPAD(str, len, fuellstr) | | verkürzen + | LTRIM(str) | | auffüllen | RPAD(str, len, fuellstr) | | | RTRIM(str) | | | TRIM([BOTH | LEADING | TRAILING] [zeichen] [FROM] str) | +---------------+-----------------------------------------------------------+ | String- | LCASE(str) LOWER(str) | | umwandlung | UCASE(str) UPPER(str) | | | REVERSE(str) | +---------------+-----------------------------------------------------------+ | Stringsuche + | INSTR(str, teil) | | ersatz | REPLACE(str, alt, neu) | | | SOUNDEX(str) | +---------------+-----------------------------------------------------------+ | Stringvergl. | FIELD(str, str1, str2, ...) # --> ELT(...) | | | FIND_IN_SET(str, menge) | | | LOCATE(teil, str [, zahl]) | | | MATCH (spalte1, ...) AGAINST (str [modifier]) | | | POSITION(teil, str) --> LOCATE | | | STRCMP(str1, str2) | +---------------+-----------------------------------------------------------+ | Datum | CURDATE() CURRENT_DATE() # --> Datum YYYY-MM-DD | | | DATE(datum/zeit) | | | DATE_FORMAT(datum, format) | | | EXTRACT(zeitabschnitt FROM datetime) | | | FROM_DAYS(tage) | | | LAST_DAY(datum) | | | MAKEDATE(jahr, tag) | | | STR_TO_DATE(str, formatmuster) | | | UTC_DATE() | +---------------+-----------------------------------------------------------+ | Datumteile | DAYNAME(datum) | | | DAYOFMONTH(datum) DAY(datum) | | | DAYOFWEEK(datum) | | | DAYOFYEAR(datum) | | | MONTH(datum) | | | MONTHNAME(datum) | | | QUARTER(datum) | | | WEEK(datum) | | | WEEKDAY(datum) --> Mo=0, Di=1, ..., Sa=5, So=6 | | | WEEKOFYEAR(datum) | | | YEAR(datum) | | | YEARWEEK(datum [, modus]) | +---------------+-----------------------------------------------------------+ | Datumrechnung | ADDDATE(datum, INTERVAL anz typ) --> DATE_ADD(...) | | | DATE_ADD(datum, INTERVAL zeitspanne typ) | | | DATE_SUB(datum, INVERVAL zeitspanne typ) | | | DATEDIFF(neu_datum, alt_datum) # --> TAGE | | | PERIOD_ADD(datum, monate) | | | PERIOD_DIFF(datum1, datum2) | | | SUBDATE(datum, INVERVAL anz typ) --> DATE_SUB(...) | | | TO_DAYS(datum) --> Anz. Tage seit 01.01.0000 | | | TO_SECONDS(datum) --> Anz. Sekunden seit 01.01.0000 | +---------------+-----------------------------------------------------------+ | Zeit | CONVERT_TZ(datum/zeit, zeitzone, zeitzone) | | | CURTIME() CURRENT_TIME() | | | LOCALTIME() | | | MAKETIME(stunde, minute, sekunde) | | | SEC_TO_TIME(sek) | | | TIME_FORMAT(zeit, format) | | | TIME_TO_SEC(zeit) | | | UTC_TIME() | +---------------+-----------------------------------------------------------+ | Zeitteile | HOUR(zeit) | | | MINUTE(zeit) | | | MICROSECOND(zeit) | | | SECOND(zeit) | +---------------+-----------------------------------------------------------+ | Zeitrechnung | ADDTIME(zeit, zeit) | | | SUBTIME(datum/zeit, datum/zeit) | | | TIMEDIFF(zeit, zeit) | +---------------+-----------------------------------------------------------+ | Timestamp | FROM_UNIXTIME(sek [,format]) # --> Datum | | | LOCALTIMESTAMP() | | | NOW([stellen]) CURRENT_TIMESTAMP([stellen]) | | | TIMESTAMP(datum, zeit) | | | TIMESTAMPADD(intervall, wert, datum/zeit) | | | TIMESTAMPDIFF(intervall, wert, datum/zeit) | | | UNIX_TIMESTAMP([datum]) # --> Sekunden seit 1.1.1970 | | | UTC_TIMESTAMP() | +---------------+-----------------------------------------------------------+ | Bedingung | CASE wert WHEN auswahl THEN wert ... ELSE wert END | | | ELT(zahl, str1, str2, ...) # --> FIELD(...) | | | IF(test, wert1, wert2) | | | INTERVAL(x, grenze1, grenze2, ...) | +---------------+-----------------------------------------------------------+ | NULL-Vergleich| COALESCE(wert1, wert2, ...) | | | IFNULL(wert1, wert2) | | | ISNULL(ausdruck) | | | NULLIF(wert1, wert2) | +---------------+-----------------------------------------------------------+ | Konvertierung | ASCII(zeichen) | | | BIN(dezimalzahl) | | | CAST(ausdruck AS typ) CONVERT(ausdruck, typ) | | | CHAR(num1 [,num2, ...]) [USING zeichensatz] | | | CONV(zahl, basis1, basis2) | | | CONVERT(ausdruck USING zeichensatz) | | | GET_FORMAT(datentyp, formattyp) | | | HEX(dezimalzahl) | | | OCT(dezimalzahl) | | | ORD(str) | | | QUOTE(str) | | | UNHEX(str) | | | FROM_BASE64(str) | | | TO_BASE64(str) | +---------------+-----------------------------------------------------------+ | Zeichen- | CHARSET() | | codierung + | COERCIBILITY() | | sortierung | COLLATION() | +---------------+-----------------------------------------------------------+ | Advisory | GET_LOCK(name, timeout) | | Locking | IS_FREE_LOCK(name) | | (User Level) | IS_USED_LOCK(name) | | | RELEASE_LOCK(name) | +---------------+-----------------------------------------------------------+ | Komprimierung | COMPRESS(str) | | | UNCOMPRESS(str) | | | UNCOMPRESSED_LENGTH(str) | +---------------+-----------------------------------------------------------+ | Verschlüsseln | AES_DECRYPT(str, password) | | Entschlüsseln | AES_ENCRYPT(str, password) | | | DES_DECRYPT(str [, password]) | | | DES_ENCRYPT(str [, password]) | | | DECODE(blob, passwort) | | | ENCODE(blob, passwort) | | | ENCRYPT(str, salt) | | | OLD_PASSWORD(str) | | | PASSWORD(str) | +---------------+-----------------------------------------------------------+ | Hashing | CRC32(str) | | | MD5(str) | | | SHA(str) | | | SHA1(str) | +---------------+-----------------------------------------------------------+ | Eindeutige ID | UUID() # --> 128-Bit Zahl | | | UUID_SHORT() # --> 64-Bit Zahl | +---------------+-----------------------------------------------------------+ | Netzwerk | INET_ATON(adresse) | | | INET_NTOA(zahl) | +---------------+-----------------------------------------------------------+ | Sonstige | BENCHMARK(anz, funktion) | | | LOAD_FILE(dateiname) # Datei als String einlesen | | | MASTER_POS_WAIT(dateiname, pos [, timeout]) | | | SLEEP(sek) # Optional .microsec | +---------------+-----------------------------------------------------------+ 9a) Aggregatfunktionen und Gruppierung (Aggregation) ---------------------------------------------------- Aggregatfunktionen fassen in einer SELECT-Anweisung die Werte einer Spalte über ALLE Datensätze zusammen. Kommt eine GROUP-BY-Klausel bezüglich einer (anderen) Spalte vor, erfolgt die Zusammenfassung PRO unterschiedlichem Wert dieser Spalte (mehrere GROUP-BY-Spalten sind möglich). MySQL kennt folgende Aggregatfunktionen: +-----------------------+-------------------------------------------------+ | Funktion | Beschreibung | +-----------------------+-------------------------------------------------+ | COUNT(*) | Anzahl ALLER Datensätze (auch NULL!) | | COUNT() | Anzahl aller Werte ungleich NULL | | COUNT(DISTINCT ) | Anzahl aller verschiedenen Werte ungleich NULL | +-----------------------+-------------------------------------------------+ | SUM() | Summe aller Werte | | SUM(DISTINCT ) | Summe aller verschiedenen Werte (MY!5.1) | | AVG() | Durchschnitt aller Werte | | AVG(DISTINCT ) | Durchschnitt aller verschiedenen Werte (MY!5.1) | | MIN() | Minimum aller Werte | | MAX() | Maximum aller Werte | +-----------------------+-------------------------------------------------+ | STD() | Standardabweichung Werte ungleich NULL (MY!) | | STDDEV() | Standardabweichung Werte ungleich NULL (MY!) | | STDDEV_POP() | Standardabweichung Werte ungleich NULL | | STDDEV_SAMP() | Standardabweichung aller Werte (auch NULL!) | +-----------------------+-------------------------------------------------+ | VARIANCE() | Varianz aller Werte ungleich NULL (MY!) | | VAR_POP() | Varianz aller Werte ungleich NULL | | VAR_SAMP() | Varianz aller Werte (auch NULL!) | +-----------------------+-------------------------------------------------+ | BIT_AND() | Bitweise UND-Verknüpfung aller Werte (MY!) | | BIT_OR() | Bitweise ODER-Verknüpfung aller Werte (MY!) | | BIT_XOR() | Bitweise XOR-Verknüpfung aller Werte (MY!) | +-----------------------+-------------------------------------------------+ | GROUP_CONCAT() | Verkettung aller ermittelten Gruppenwerte (MY!) | +-----------------------+-------------------------------------------------+ HINWEIS: Je nachdem ob eine Spalte NULL-Werte enthält oder nicht gilt: COUNT(*) = COUNT() # Kein NULL-Wert in COUNT(*) > COUNT() # Mind. ein NULL-Wert in Beispiele: CREATE TABLE bestellung ( nr INT, anz INT, preis FLOAT, summe FLOAT, rabatt FLOAT ); INSERT INTO bestellung VALUES (1, 2, 123.45, 2 * 123.45, 10), (2, 5, 10.45, 5 * 10.45, 0), (3, 1, 67.00, 1 * 67.00, 5); SELECT COUNT(name) AS "Artikelanzahl" FROM bestellung; SELECT AVG(preis) AS "Durchschnittspreis" FROM bestellung; SELECT SUM(preis * anz) AS "Gesamtpreis" FROM bestellung; SELECT MIN(preis) AS "Billig", MAX(preis) AS "Teuer" FROM bestellung; Mittels der Klausel GROUP BY lassen sich Datensätze basierend auf GLEICHEN Werten einer oder mehrerer Spalten GRUPPIEREN (zusammenfassen). Über die obigen Aggregatfunktionen entstehen dabei aus den Einzelwerten der RESTLICHEN Spalten der jeweils zusammengefassten Datensätze Gesamtwerte. Eigenschaften: * Pro VERSCHIEDENEM Wert einer GROUP BY Spalte entsteht EINE Ergebniszeile * Datensätze mit NULL-Wert in gruppierter Spalte werden (meist) ignoriert * Die gruppierten Spaltenwerte sind sortierbar: + aufsteigend (ASC, STD) + absteigend (DESC) * Gesamtzeile bzgl. gruppierter Spalte mit Zusatz "WITH ROLLUP" möglich + Spaltenwert dieses Datensatzes ist "NULL" + Werttext für Gesamtzeile per IFNULL festlegen IFNULL(, "") AS + Nicht zusammen mit Sortierung verwendbar + NULL nicht in HAVING als Bedingung verwendbar Syntax: SELECT ... FROM ... GROUP BY [ASC|DESC] [WITH ROLLUP] {, ...} [FOR UPDATE | LOCK IN SHARE MODE | PROCEDURE (...) | PROCEDURE ANALYSE([[, ]]) ] Beispiele: SELECT SUM(summe) AS "Gesamt" FROM bestellung GROUP BY nr # STD: ASC (aufsteigend) HAVING Gesamt > 100; SELECT nr, AVG(preis) AS "Durchschnittspreis" FROM artikel GROUP BY nr DESC # STD: ASC (aufsteigend) HAVING COUNT(nr) > 1; CREATE TABLE verkaeufe ( jahr INT NOT NULL, land VARCHAR(20) NOT NULL, produkt VARCHAR(32) NOT NULL, gewinn INT ); INSERT INTO verkaeufe (jahr, land, produkt, gewinn) VALUES (2009, "Deutschland", "Mixer", 11.3), (2010, "Deutschland", "Mixer", 10.7), (2009, "England", "Mixer", 11.0), (2010, "England", "Mixer", 9.5), (2009, "Frankreich", "Mixer", 12.1), (2010, "Frankreich", "Mixer", 10.2), (2009, "Deutschland", "Toaster", 21.3), (2010, "Deutschland", "Toaster", 20.7), (2009, "England", "Toaster", 21.0), (2010, "England", "Toaster", 19.5), (2009, "Frankreich", "Toaster", 22.1), (2010, "Frankreich", "Toaster", 20.2), (2009, "Deutschland", "Kocher", 1.3), (2010, "Deutschland", "Kocher", 0.7), (2009, "England", "Kocher", 1.0), (2010, "England", "Kocher", 0.5), (2009, "Frankreich", "Kocher", 2.1), (2010, "Frankreich", "Kocher", 0.2); SELECT jahr, SUM(gewinn) # --> jahr SUM(gewinn) FROM verkaeufe # 2009 102 GROUP BY jahr; # 2010 94 SELECT jahr, SUM(gewinn) # --> jahr SUM(gewinn) FROM verkaeufe # 2009 102 GROUP BY jahr # 2010 94 WITH ROLLUP; # NULL 196 SELECT IFNULL(jahr, "GESAMT"), SUM(gewinn) # --> jahr SUM(gewinn) FROM verkaeufe # 2009 102 GROUP BY jahr # 2010 94 WITH ROLLUP; # GESAMT 196 SELECT jahr, land, SUM(gewinn) # Mehrere Gruppen FROM verkaeufe # GROUP BY jahr ASC, land DESC; # SELECT jahr, land, SUM(gewinn) # FROM verkaeufe # GROUP BY jahr, land # Mehrere Gruppen WITH ROLLUP; # + Gesamt SELECT IFNULL(jahr, "JAHRE") AS jahr, # Gesamtwert umbenennen IFNULL(land, "LAENDER") AS land, # Gesamtwert umbenennen SUM(gewinn) # FROM verkaeufe # GROUP BY jahr, land # Mehrere Gruppen WITH ROLLUP; # + Gesamt 10) Schlüssel (Key) und Index ----------------------------- 10a) Eigenschaften ------------------ "Schlüssel" (Key) und "Index" haben zunächst nichts miteinander zu tun: * Ein "Schlüssel" adressiert jeden Datensatz einer Datenbanktabelle EINDEUTIG + Aus einer Spalte oder Kombination mehrerer Spalten gebildet + Nutzen: Beziehung zwischen Tabellen herstellen + NUR EIN (primärer) Schlüssel pro Tabelle möglich (PRIMAY KEY) * Ein "Index" wird für eine Spalte oder Kombination von Spalten angelegt, nach denen häufig gesucht oder sortiert wird + Ähnlich einem Inhaltsverz. zu einem Buch: - Suche nach Datensätzen beschleunigen (WHERE) - Sortierung von Datensätzen beschleunigen (ORDER BY) - Eindeutigkeit prüfen (UNIQUE KEY) - Beziehung herstellen (FOREIGN KEY) + MEHRERE Indices pro Tabelle möglich + Muss nicht unbedingt eindeutig (UNIQUE) sein * Zusammenhang: + Zu jeden Schlüssel einer Datenbank meist ein Index erstellt (kein MUSS) Unterscheidung: * Primärschlüssel (PRIMARY KEY) + Kennzeichnet JEDEN Datensatz EINDEUTIG (z.B. Abteilungsnummer) + Meist (abstrakte) fortlaufende Nummer + Häufig automatisch beim Anlegen eines Datensatzes erzeugt + NUR EIN Primärschlüssel pro Tabelle möglich und notwendig + Eine Primärschlüsselspalte darf NIEMALS LEER sein (NOT NULL) + Komplexe/lange Primärschlüssel verlangsamen Datenbank-Operationen (am besten nur Spalte vom Typ "kleine ganze Zahl" verwenden) * Sekundärschlüssel + Kennzeichnet ebenfalls Datensatz eindeutig (z.B. Abteilungsname) + MEHRERE Sekundärschlüssel pro Tabelle möglich + Sekundärschlüssel darf LEER sein (NULL) * Fremdschlüssel (FOREIGN KEY) + Bezeichnet die Übereinstimmung einer/mehrerer Spalten in einer Tabelle mit der/den Primärschlüsselspalte(n) einer anderen Tabelle ("Verweise") + MEHRERE Fremdschlüssel pro Tabelle möglich + KEINE ZIRKULÄREN Referenzen auf Fremdschlüssel erstellen! + Häufig in "Lookup-Tabelle" verwendet, die 2 oder mehr Tabellen verknüpft + 1:1-Beziehung, 1:N-Beziehung, N:M-Beziehung * Index + Erleichtern Suche nach Datensätzen vor allem in großen Tabellen + Einfügen, Ändern, Löschen von Datensätzen erfordert Index-Aktualisierung --> Kann viel Zeit kosten + TIP: Indices großer Tabellen erst NACH dem Füllen anlegen Hinweise: * Nur 1 Primärschlüssel pro Tabelle erlaubt + Automatisch UNIQUE + Automatisch NOT NULL + Automatisch auch ein Index + Darf mehr als 1 Spalte enthalten! * Mehrere UNIQUE Indices anlegbar + Automatisch Sekundärschlüssel + NULL-Werte darin erlaubt * Index auf Spaltenpräfix beschränkbar (z.B. name(5), vorname(5)) (nicht auf Suffix --> TRICK: Daten per REVERSE() umgedreht speichern) - Sollte selektiv genug sein - Spart Platz - Bei Spaltentyp TEXT/BLOB notwendig * Tabelleninhalt wird durch Index nicht verändert * Index = "Zeiger" auf Datensätze in sortierter Reihenfolge * Index belegt zusätzlichen Plattenplatz * Index enthält KEINE zusätzlichen Daten, sondern erlauben nur schnelleren Lese-Zugriff + Index daher jederzeit löschbar + Index daher jederzeit aus Tabellendaten wieder herstellbar (in Backup nicht aufnehmen) + Beschleunigt nur Zugriff über indizierte Spalten * Beim Erzeugen + Ändern von Datensätzen kosten Indices zusätzlichen Aufwand, da jeder Schreibvorgang auf Datensätzen die Indices mitpflegen muss - Nachträgliche Index-Erzeugung --> Tabelle für Schreiben gesperrt! HINWEIS: Manche DB-Systeme fordern zu jeder Tabelle einen Primärschlüssel, MySQL kann darauf auch verzichten (nur in Ausnahmefällen wirklich sinnvoll). 10b) Erstellen/entfernen ------------------------ Primärschlüssel erstellen/entfernen (automatisch/muss NOT NULL + UNIQUE sein): * Beim Erzeugen einer Tabelle (1.Form mehrspaltig, 1+2.Form = einspaltig) CREATE TABLE ( CREATE TABLE ( nr INT NOT NULL AUTO_INCREMENT, nr INT AUTO_INCREMENT PRIMARY KEY, ..., ..., PRIMARY KEY (nr) ... ); ); * Nachträglich erzeugen - Nur möglich, wenn Spalte "nr" NOT NULL + UNIQUE (KEINE doppelten Werte)! ALTER TABLE ADD PRIMARY KEY (nr); # OK CREATE PRIMARY KEY ON (nr); # FALSCH! --> ALTER TABLE! * Entfernen ALTER TABLE DROP PRIMARY KEY; Sekundärschlüssel erstellen/entfernen: * Beim Erzeugen einer Tabelle CREATE TABLE pers ( nr INT NOT NULL AUTO_INCREMENT, name CHAR(30) NOT NULL, vorname CHAR(30) NOT NULL, PRIMARY KEY (nr), UNIQUE INDEX idx1 (name, vorname) # Oder nur UNIQUE ohne INDEX ); * Nachträglich erzeugen + Nur möglich, wenn Spalte "nr" UNIQUE (KEINE doppelten Werte enthält!), + NULL-Werte sind nicht erlaubt ALTER TABLE pers ADD UNIQUE INDEX idx1 (name, vorname); # INDEX weglassbar CREATE UNIQUE INDEX idx1 ON pers (name, vorname); * Entfernen ALTER TABLE pers DROP INDEX idx1; # Nur INDEX (ohne UNIQUE, ...) Index erstellen, entfernen, (de)aktivieren und anzeigen * Allgemeine Syntax: CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX [USING {BTREE | HASH | RTREE}] ON ( [()] [ASC | DESC], ...); + Indexname nötig, um Index gezielt entfernen zu können --> MUSS pro Tabelle eindeutig sein + Index-Art wählbar (UNIQUE, FULLTEXT, SPATIAL) CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX ...; + Indexsortierung wählbar (in MySQL derzeit ignoriert, STD: ASC) ... ON ( ASC, DESC, ...); + Für Index benutzte Spaltenpräfix-Länge wählbar ... ON ((), (), ...); + Index-Struktur wählbar (BTREE, HASH, RTREE) - MyISAM: BTREE, RTREE - InnoDB: BTREE - MEMORY: BTREE, HASH - NDB: BTREE, HASH CREATE INDEX [USING {BTREE | HASH | RTREE}] ...; * Beim Erzeugen einer Tabelle CREATE TABLE ( nr INT NOT NULL, vorname CHAR(30) NOT NULL, name CHAR(30) NOT NULL, ..., INDEX idx1 (nr), INDEX idx2 (name, vorname) ); * Nachträglich erzeugen CREATE INDEX ON (, ...); ALTER TABLE CREATE INDEX (, ...); * Entfernen DROP INDEX ON ; ALTER TABLE DROP INDEX ; * Aktivieren/deaktivieren ALTER TABLE DISABLE KEYS; # Alle Non-UNIQUE Indices abschalten ALTER TABLE ENABLE KEYS; # Alle Non-UNIQUE Indices wieder aufbauen ALTER INDEX ACTIVE; # In MySQL NICHT möglich (MY!) ALTER INDEX INACTIVE; # In MySQL NICHT möglich (MY!) * Indices einer Tabelle anzeigen lassen (mit Kardinalitätswert N/S) SHOW INDEX FROM ; * Indices einer Datenbank anzeigen lassen (NICHT unter MySQL, MY!) SHOW INDICES; 10c) FULLTEXT-Index ------------------- Eigenschaften: * Nur bei Engine "MyISAM" möglich! * Nur auf Spaltentypen CHAR, VARCHAR, TEXT möglich * Suche nach einzelnen Worten und beliebig langen Texten möglich * Nach einigen englischen "Stopwords" wir NICHT gesucht (z.B. "and", "or") * Suche NICHT mit LIKE/REGEX möglich * Spezielle Suchsyntax: MATCH (, ...) AGAINST ( []) mit IN BOOLEAN MODE # Syntax +xxx -yyy ... IN NATURAL LANGUAGE MODE # Ab MY!5.1.7 IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION # Ab MY!5.1.7 WITH QUERY EXPANSION # 10d) SPATIAL-Index ------------------ Eigenschaften: * Nur bei Engine "MyISAM" möglich! * Nur auf Geometrie-Spaltentypen möglich * Basiert auf dem OpenGIS Geometrie-Modell 10e) Index-Nutzung ------------------ * Indices verwendet bei SELECT (UPDATE, DELETE, REPLACE), ORDER BY und JOIN. * Datenbank sucht zu jeder Abfrage SELBSTÄNDIG sinnvoll einzusetzende Indices (mit Hilfe des "Query Optimizer") * Tabellen/Indices enthalten Informationen über Selektivität und statistische Verteilung der indizierten Daten (Histogramm) + Bsp: Index auf Spalte "Geschlecht" ist wenig nützlich, da immer etwa auf die Hälfte der Daten zugegriffen wird + Bsp: Index auf Schlüsselspalte "nr" ist sehr nützlich (da eindeutig) * Index kann eine/mehrere Spalten einer Tabelle umfassen ("Composite Index") + Umfasst er mehrere Spalten, dann sind alle Spalten-Kombinationen von links nach rechts ebenfalls enthalten (Präfixe)! + Ein mehrspaltiger Index ist immer dann nutzbar, wenn ALLE Spalten von links nach rechts in WHERE-Klausel vorkommen + Beispiel: CREATE INDEX multi ON adresse (name, vorname, strasse); Folgende Abfragen nutzen diesen Index: SELECT * FROM adresse WHERE name LIKE "A%"; SELECT * FROM adresse WHERE name LIKE "A%" AND vorname LIKE "B%" SELECT * FROM adresse WHERE name LIKE "A%" AND vorname LIKE "B%" AND strasse LIKE "C%"; Folgende Abfragen können diesen Index NICHT nutzen: SELECT * FROM adresse WHERE vorname LIKE "B%"; SELECT * FROM adresse WHERE strasse LIKE "B%"; SELECT * FROM adresse WHERE vorname LIKE "B%" AND strasse LIKE "C%"; * Werden nur Datenspalten gelesen, die in EINEM Index enthalten sind: + Index enthält bereits alle benötigten Daten + Zugriff auf Datentabelle nicht nötig + "Covering Index" + Beschleunigung + Beispiel: SELECT name FROM adresse WHERE name LIKE "A%" AND vorname LIKE "B%" * Index bei folgenden Vergleichen verwendbar: <=> # NULL-sicheres "=" = # Gleich < # Kleiner <= # Kleiner oder gleich > # Größer >= # Größer oder gleich LIKE "abc%" # Platzhalter "%" hinten LIKE "abc__" # Platzhalter "_" hinten BETWEEN AND # Bereich einschließlich Grenzen * KEIN Index bei folgenden Vergleichen verwendbar: != # Ungleich <> # Ungleich LIKE "%abc" # Platzhalter "%" vorne LIKE "__abc" # Platzhalter "_" vorne RLIKE "abc" # Regulärer Ausdruck REGEX "abc" # Regulärer Ausdruck NOT RLIKE "abc" # Regulärer Ausdruck NOT REGEX "abc" # Regulärer Ausdruck NOT BETWEEN AND # Nicht in Bereich einschließlich Grenzen * Werden mehrere Bedingungen per AND/OR verknüpft, dann wird meist der Index zu den Spalten einer Bedingung verwendet, der am SELEKTIVSTEN ist, d.h. die wenigsten Datensätze selektiert. Diese Datensätze werden alle gelesen und die restlichen Bedingungen dagegen geprüft * Index auch bei Sortierung ORDER BY und Gruppierung GROUP BY verwendbar * Schlüsselworte "KEY" und "INDEX" sind synonym Indices werden verwendet bei: * Auswahl Datensätze gemäß WHERE-Klausel * Datensätze weglassen * Doppelte Einträge rauswerfen (DISTINCT) * JOIN: Typ und Länge müssen gleich sein (Konvertierung verhindert Index!) * MIN/MAX für bestimmte Spalte * ORDER BY/GROUP BY falls ausgeführt auf Präfix eines Index * Daten + Bedingung durch Index befriedigt und numerisch * = > >= < <= BETWEEN LIKE (mit fixem Präfix) * IS NULL wenn Index auf * Index muss in jeder AND-Gruppe der WHERE-Klausel verwendbar sein (kann auch 1-elementig ohne AND sein!) * LIMIT Indices werden NICHT verwendet bei: * Lese-Operationen, die großen Prozentsatz einer Tabelle lesen * OR auf Spalten in verschiedenen Indices --> IN, UNION [ALL]! * Regulären Ausdrücken 10f) Index-Optimierung ---------------------- Richtlinien für das Erstellen von Indices: * Primär-, Sekundär- und Fremdschlüssel erhalten automatisch einen Index + PRIMARY KEY so kurz wie möglich und eindeutig + Nur im absoluten Ausnahmefall keinen Primärschlüssel definieren (Datensätze dann nicht eindeutig identifizierbar) * Spalten mit wenigen unterschiedlichen Werten (z.B. Anrede, Geschlecht) bringen mit Index KEINEN Geschwindigkeitsgewinn (Kardinalität zu gering) * Indices auf Tabellen einsetzen, die oft gefiltert/gruppiert/sortiert werden + Indices auf Tab. mit wenig Datensätzen (<=12) bringt KEINEN Geschw.gewinn + Mehr als 10% Datensätze einer Tab. ständig gelesen --> Index nicht sinnvoll * Zwei Indices notwendig für auf- + absteigendes Sortieren (ASC, DESC) * Sortierkriterium an letzter Stelle im Index --> (teuren) Filesort sparen * Aggregatfunktionen nutzen keinen Index + COUNT, SUM, AVG, STDDEV, ... + Da alle Datensätze durchlaufen werden müssen + MIN/MAX für bestimmte Spalte schon * Ohne Indices wäre immer ein "Full Table Scan" notwendig + Beim (fast) vollständigen Durchlesen einer Tabelle KEINEN Index benutzen! --> Evtl. Cursor, Handler besser * MySQL: Datensätze und Indices IMMER in getrennten Dateien (InnoDB?) + Kostet zusätzlichen READ bei Zugriff Index --> Daten + Evtl. "Full Table Scan" schneller --> Index lesen unnötig + Evtl. "Covering Index" ausreichend --> Daten lesen unnötig + Nur 1x Speicherplatz für Indexwert bei Datensätzen mit gleichem Indexwert + Delete-Operationen "degenerieren" Tabelle nicht + Caching getrennt möglich * Nur unbedingt notwendige Indices nutzen (Schreibperformance sonst schlecht) * Am häufigsten benutzte Spalten(kombination) indizieren + Mehrere Spalten nach abnehmender Häufigkeit sortieren + Zusammengesetzter Index nutzbar in mehreren Fälle: 1.Sp, 1+2.Sp, ... + Spalte mit mehr doppelten Werten vorne (Kardinalität) * Nur ausreichend lange Präfixe indizieren (Platz, Hits) + Für BLOB/TEXT ein MUSS! + Kardinalität reduziert + TRICK: REVERSE oder SUBSTR verwenden, falls Anfang statisch Eigenschaften von Indices: * Automatisch Präfix- und Längen-komprimiert (MY!) * MySQL verwendet pro Tabelle in einer Query maximal EINEN Index + Normalerweise Index mit kleinster Treffermenge (Anz. passender Datensätze) + Evtl. mehrspaltiger Index sinnvoll! Verschiedene Indexverfahren möglich (MY!): * BTREE: Fast immer möglich * RTREE: Für geometrische Daten * HASH: Bei MEMORY-Tabellen + Einschränkung des HASH-Index - NUR für = und <=> - Nicht für != <> < <= > >= verwendbar - Nicht für ORDER BY und GROUP BY verwendbar - Nur vollständiger Index nutzbar, keine Präfixe Index-Hint: Manuell Hinweis zur Indexnutzung in SELECT pro Tab. angebbar (MY!): * Nach FROM : USE INDEX = NUR diese Indices benutzen ("Full Table Scan" möglich) FORCE INDEX = Analog USE INDEX ("Full Table Scan" NUR wenn unmöglich) IGNORE INDEX = Diese Indices NICHT benutzen * Name des Primären Index ist "PRIMARY" * USE INDEX Liste darf auch leer sein --> Keine Indices benutzen! * Auswahl für welche Operation (fehlt --> Für alle 3 Fälle gültig): FOR JOIN = Für Tabellen-Join FOR ORDER BY = Für Sortierung FOR GROUP BY = Für Gruppierung +--------------------------------------------------------------------------+ | FROM USE INDEX [FOR {JOIN | ORDER BY | GROUP BY}] (, ...) | | FORCE INDEX [FOR {JOIN | ORDER BY | GROUP BY}] (, ...) | | IGNORE INDEX [FOR {JOIN | ORDER BY | GROUP BY}] (, ...) | +--------------------------------------------------------------------------+ Beispiel: SELECT * FROM table1 USE INDEX (idx1, idx2) WHERE col1 = 1 AND col2 = 2 AND col3 = 3; SELECT * FROM table1 IGNORE INDEX (idx3) WHERE col1 = 1 AND col2 = 2 AND col3 = 3; SELECT * FROM t1 USE INDEX (i1) IGNORE INDEX FOR ORDER BY (i2), t2 FORCE INDEX (i3), WHERE t1.id = t2.id ORDER BY a; Indices reorganisieren: * Sinnvoll nach dem Laden von Daten * Cardinality = Durchschnittliche Datensatzanz. mit gleichem Wert berechnen ANALYZE TABLE ; OPTIMIZE TABLE ; myisamchk --analyze / -a SHOW INDEX FROM ; myisamchk --description --verbose * Evtl. Index UND Daten gemäß einem Index sortieren (für sortierten Zugriff auf alle Daten gemäß UNIQUE Index) (kann beim 1. Mal sehr lange dauern) myisamchk --sort-index / -S # Indices sortieren myisamchk --sort-records=N / -R N # Gemäß Index N sortieren 10g) Fremdschlüssel (Foreign Keys) und Referenzielle Integrität --------------------------------------------------------------- Grund für Fremdschlüssel: * Dokumentieren Beziehungen zwischen Tabellen (Master/Detail, Vater/Kind) * Verhindern Einfügen von Inkonsistenzen in DB durch Programmierer + "Referenzielle Integrität" (Referential Integrity) erhalten + Reihenfolge von Einfüge/Änderungs/Lösch-Operationen nicht zu beachten (verhindert "verwaiste Kinder" = orphaned childs) + Fehlerbehandlung bei Unterbrechung * Zentrale Constraint-Prüfung (in Anwendung verzichtbar + einheitlich) * Kaskadierende UPDATEs/DELETEs vereinfachen Anwendungscode Zusammenhang zwischen Tabellen = Beziehung zw. Primär- und Fremdschlüsseln: * Beim UPDATE/DELETE eines Datensatzes aus einer (Eltern)Tabelle werden alle damit über den gleichen Fremdschlüssel verknüpften Datensätze in zugehörigen (Kind)Tabellen ebenfalls AUTOMATISCH von der Datenbank gelöscht/geändert * Beim INSERT eines Datensatzes in eine (Kind)Tabelle mit Fremdschlüssel wird geprüft, ob eine Entsprechung in referenzierter (Eltern)Tabelle vorhanden ist. Wenn nein: Fehlermeldung + Datensatz nicht eingefügt Eigenschaften: * Grundsätzlich bei Tabellen-Definition angebbar + Bei allen Engines außer "InnoDB" syntaktischer Zucker (reine Doku) + Nur bei "InnoDB" gespeichert (und "PBXT") + Speicherung und Implementierung für "MyISAM" geplant * Workaround falls nur ON DELETE nötig: + Multi-Table DELETE * Erzeugen zusätzlichen Overhead + Besser selber machen oder an DB delegieren? Hängt von Anwendung ab (1x implementieren statt mehrmals) * Restore individueller Tabellen von Backup erschwert --> Foreign Keys + Constraints + Trigger temporär abschalten! * Sinnvoll für Cascading Updates/Deletes * Nicht sinnvoll für Constraints --> Trigger besser --> Noch besser ENUM-Typ Bedingungen: * Bezugstabelle muss vorhanden sein * Datentyp und Datengröße korrespondierender Spalten MUSS identisch sein! (bei CHAR/VARCHAR darf Länge verschieden sein) * Auf korrespondierenden Spalten muss ein Index liegen * Verknüpfte Spalten müssen NOT NULL sein und UNIQUE Index haben (wird aber nicht erzwungen) * Bei temporären Tabellen NICHT erlaubt Vorteile/Nachteile: * Nicht umsonst (zusätzlicher Lookup bei jeder Änderung) * Arbeitet "Zeile für Zeile" (auch bei multiplem INSERT/UPDATE/DELETE) * Index notwendig (evtl. groß und zu sonst nichts nutze) Erzeugen einer (Kind)Tabelle mit Fremdschlüsseln einer/mehrere (Eltern)Tabellen ("CONSTRAINT " optional): CREATE TABLE ( # Kindtabelle ma_id INT NOT NULL, # Fremdschlüssel aus Elterntabelle pr_id INT NOT NULL, # Fremdschlüssel aus Elterntabelle ..., FOREIGN KEY (ma_id) REFERENCES mitarbeiter (id) # Bezug zur Elterntabelle ON UPDATE CASCADE # E-Aktion --> K-Aktion ON DELETE CASCADE, # E-Aktion --> K-Aktion CONSTRAINT cpj FOREIGN KEY (pr_id) REFERENCES projekt (id) # Bezug z.E. ON UPDATE CASCADE # E-Aktion --> K-Aktion ON DELETE CASCADE # E-Aktion --> K-Aktion ) ENGINE = "InnoDB"; Fremdschlüssel nachträglich erzeugen (nur möglich, wenn verknüpfte Tabellen ex. sowie verknüpfte Spalten NOT NULL sind und KEINE doppelten Werte enthalten): ALTER TABLE ADD [CONSTRAINT ] # Kindtabelle FOREIGN KEY (, ...) # Spalte in Kindtabelle REFERENCES (, ...) # Spalten in Elterntabelle [MATCH ] # Match-Klausel: Weglassen! [ON UPDATE ] # STD: NO ACTION [ON DELETE ] # STD: NO ACTION ...; Verhalten eines Fremdschlüssels steuern: * MATCH-Klausel definiert Behandlung von NULL-Werten in mehrspaltigen Fremdschlüsseln beim Vergleich mit dem Primärschlüssel (derzeit ignoriert, d.h. immer SIMPLE) MATCH SIMPLE # Mehrsp. Fremdschl. darf teilw./ganz NULL sein (STD) MATCH PARTIAL # Mehrsp. Fremdschl. darf nicht vollständig NULL sein MATCH FULL # Mehrsp. Fremdschl. muss vollständig ungl. NULL sein * Verhalten beim Einfügen/Löschen steuern: ON DELETE # Beim Löschen von Zeilen in Elterntabelle ON UPDATE # Beim Ändern von Zeilen in Elterntabelle * Referenz-Optionen (STD: Anweisung wird abgebrochen) NO ACTION # Abbruch der Änderung in Elterntabelle (STD) RESTRICT # (analog NO ACTION) CASCADE # Löschen/Ändern transitiv auf Kindtabelle ausdehnen SET NULL # Ref. Sp. in Kindtab. auf NULL setzen SET DEFAULT # Ref. Sp. in Kindtab. auf Default setzen (nicht MY!) Fremdschlüssel aus Kindtabelle entfernen: ALTER TABLE DROP FOREIGN KEY ; Fremdschlüssel in Kindtabelle anzeigen: SHOW CREATE TABLE ; SHOW TABLE STATUS FROM LIKE ; Fremdschlüssel überprüfen vor Daten laden aus- und danach wieder einschalten: SET foreign_key_checks = 0; # Prüfung ausschalten SOURCE ; # Kein "..." um ! SET foreign_key_checks = 1; # Prüfung einschalten 11) Joins --------- Ein Join ist eine Verknüpfung von zwei Tabellen zu einer Gesamttabelle über eine Abhängigkeit zwischen den Tabellen (Primärschlüssel + Fremdschlüssel). Das Ergebnis ist wieder eine (temporäre) Tabelle (und wieder ein JOIN mit einer anderen Tabelle durchgeführt werden). MySQL unterstützt folgende JOIN-Arten: +---------------+------------------------------------------------------------+ | Typ | Bedeutung | +---------------+------------------------------------------------------------+ | [CROSS] JOIN | Kombination ALLER Datensätze der 1. Tabelle mit ALLEN | | | der 2. Tabelle ("Kreuzprodukt", "Karthesisches Produkt") | | | (KEINE Beziehung, andere Schreibweise: , ). | +---------------+------------------------------------------------------------+ | [INNER] JOIN | Kombin. Datens. aus 1. Tab. mit PASSENDEN aus 2. Tab. bzgl.| | | einer/mehrerer Spalten. Datensätze beider Tab. weglassen, | | | die in Kombinationsspalte(n) keinen passend. Wert enthalten| +---------------+------------------------------------------------------------+ | {LEFT | RIGHT}| Analog [INNER] JOIN, aber JEDER Datensatz aus LEFT=linker | | [OUTER] JOIN| bzw. RIGHT=rechter Tabelle ist im Ergebnis vorhanden | | | (Spaltenwerte der anderen Tabelle evtl. alle NULL) | +---------------+---------+--------------------------------------------------+ | FULL [OUTER] | Kombination aus LEFT + RIGHT JOIN, d.h. JEDER Datensatz der| | JOIN | linken + rechten Tabelle ist mind. 1x im Ergebnis vorhanden| | | (erst ab MY!5.1 verfügbar!) | +---------------+------------------------------------------------------------+ | STRAIGHT_JOIN | MySQL-Optimierung ignorieren und Daten benutzergesteuert in| | | Reihenfolge der JOINs in FROM-Clause zusammenfügen (MY!) | | | (linke Tabelle immer zuerst VOR rechter Tab. gelesen), | | | entspricht JOIN (d.h "Kreuzprodukt"). | +---------------+---------+--------------------------------------------------+ | NATURAL [{LEFT | RIGHT} | Automatisch verknüpfen über ALLE gemeinsamen | | [OUTER]] JOIN | Spaltennamen (USING ... unnötig) | +-------------------------+--------------------------------------------------+ Hinweise: * MySQL-Optimizer macht immer LEFT JOIN von links nach rechts (d.h. sortiert Tabellen in Zugriffsreihenfolge von links nach rechts) * Schlüsselworte CROSS, INNER und OUTER dürfen weggelassen werden (aus Gründen der klareren Ausdrucks besser verwenden!) * RIGHT-Join wird in LEFT-Join umgewandelt durch Vertauschen der Seiten (MY!) * RIGHT-Join aus Portabilitätsgründen besser als LEFT-Join formulieren (MY!) Weitere spezielle Arten von Joins: +------------+-------------------------------------------------------------+ | Begriff | Bedeutung | +------------+-------------------------------------------------------------+ | Self-Join | Verknüpfung einer Tabelle mit sich selbst (rekursiv) | | Equi-Join | Verknüpfung über "="-Relation | | Theta-Join | Verknüpfung über andere Rel. als "=" (!= <> < <= > >=) | | Semi-Join | Analog Natural Join, dann Reduktion auf Spalten der 1. Tab. | +------------+-------------------------------------------------------------+ Syntax: = [, ] ... = | = [[AS] ] []] | [AS] | () | {OJ LEFT OUTER JOIN # ODBC-Syntax, {...} hinschreiben ON } = [INNER | CROSS] JOIN [] | STRAIGHT_JOIN [ON ] | {LEFT | RIGHT} [OUTER] JOIN | NATURAL [{LEFT | RIGHT} [OUTER]] JOIN = ON | USING (, ...) = USE {INDEX | KEY} [FOR JOIN] (, ...) | FORCE {INDEX | KEY} [FOR JOIN] (, ...) | IGNORE {INDEX | KEY} [FOR JOIN] (, ...) Beispiele: SELECT a.name, a.preis, h.name # INNER JOIN FROM artikel a INNER JOIN hersteller h ON a.name = h.name # Möglich: Spalten verschiedennamig WHERE a.preis > 200; SELECT a.name, h.name # LEFT OUTER JOIN FROM artikel a LEFT OUTER JOIN hersteller h USING (name); # Notwendig: Spalten gleichnamig SELECT * # NATURAL JOIN FROM pers NATURAL JOIN age; # Notwendig: Spalten gleichnamig 12) Mengen-Operationen --------------------- Mengen-Operationen erlauben die Verknüpfung von zwei oder mehr Selektionen (Tabellen) zu einer Gesamtselektion: +------------------+------------------------------------------------------+ | Mengenoperation | Bedeutung | +------------------+------------------------------------------------------+ | UNION [DISTINCT] | Vereinigung (STD: doppelte Datensätze weglassen) | | UNION ALL | Vereinigung (inkl. doppelte Datensätze) | | INTERSECT | Durchschnitt = gemeinsame Datensätze (NICHT in MY!) | | EXCEPT/MINUS | Differenz = 1. minus 2. Selektion (NICHT in MY!) | +------------------+------------------------------------------------------+ Eigenschaften: * Spaltenanzahl der verknüpften Selektionen muss gleich sein * Datentypen der Spalten müssen positionsweise kompatibel sein + Zeichenkette + Zahl + Datum + Zeit + ... * Spaltennamen des Ergebnisses = Spaltennamen der ersten Selektion * Doppelte Datensätze werden standardmäßig weggelassen (Menge!), außer UNION ALL wird verwendet (STD: DISTINCT) * Sortierung der Daten wird zerstört (möglich: Gesamtergebnis sortieren) Beispiel: SELECT name, vorname FROM pers # Selektion 1 UNION # STD: DISTINCT = doppelte weglassen SELECT name, vorname FROM copy; # Selektion 2 # SELECT name, vorname FROM pers # Selektion 1 UNION ALL # Doppelte beibehalten SELECT name, vorname FROM copy; # Selektion 2 13) Unterabfragen (Subqueries/Subselect) ---------------------------------------- Eigenschaften: * SELECT-Statement eingebettet ("nested") in anderem Statement + "Outer query", "Outer Statement" + "Inner query", "Subquery" * Seit MY!4.01 alles gemäß SQL-Standard erlaubt + einige Erweiterungen * IMMER in Klammern (...) zu setzen! * Verschachtelungstiefe beliebig * Ganz außen muss SELECT, INSERT, UPDATE, DELETE stehen * Tabelle kann nicht gleichzeitig modifiziert und gelesen werden * Als Ersatz für JOIN und UNION einsetzbar * Vorsicht bei NULL-Werten und leeren Tabellen! Syntax: # ---- Outer Query ---- --- Inner Query --- SELECT * FROM t1 WHERE col1 = (SELECT col1 FROM t2); SELECT * FROM t1 a WHERE a.col1 = (SELECT b.col1 FROM t2 b); SELECT * FROM t1 WHERE t1.col1 = (SELECT t2.col1 FROM t2); # -- Outer Statement -- ----- Subquery ---- Ergebnis einer Subquery kann sein: * EIN Skalar (einzelner Wert): = () <> () > () < () >= () <= () * EIN Datensatz (ROW-Subquery mit "Row Constructor"): ("wert1", "wert2", ...) = () ROW("wert1", "wert2", ...) = () EXISTS () # TRUE, wenn mind. 1 Datensatz zurück NOT EXISTS () # TRUE, wenn kein Datensatz zurück * EINE Datenspalte (Synonym für ALL ist SOME): ... = ANY () # Mind. EIN Wert aus gleich ... <> ANY () # Mind. EIN Wert aus ungleich ... > ANY () # Mind. EIN Wert aus kleiner ... < ANY () # Mind. EIN Wert aus größer ... <= ANY () # Mind. EIN Wert aus kleiner gleich ... >= ANY () # Mind. EIN Wert aus größer gleich ... IN () # Entspricht "= ANY" ... = ALL () # ALLE Werte aus gleich ... <> ALL () # ALLE Werte aus ungleich ... > ALL () # ALLE Werte aus größer ... < ALL () # ALLE Werte aus kleiner ... <= ALL () # ALLE Werte aus kleiner gleich ... >= ALL () # ALLE Werte aus größer gleich ... NOT IN () # Entspricht "<> ALL" * EINE Tabelle (mehrere Datensätze mit mehreren Datenspalten) ... JOIN... () AS ... # Verbundanweisung (beliebigen Typs) Row Constructor: ("tom", 2) = (SELECT sp1, sp2 FROM t3 WHERE ...) # Syntax 1 ROW("tom", 2) = (SELECT sp1, sp2 FROM t3 WHERE ...) # Syntax 2 Fast identisch (bzw. ganz identisch bei UNIQUE Index Spalte in WHERE ...) mit: "tom" = (SELECT sp1 FROM t3 WHERE ...) AND 2 = (SELECT sp2 FROM t3 WHERE ...) Beispiel: SELECT name, geburtsdatum FROM pers, age WHERE geburtsdatum = (SELECT MAX(geburtsdatum) FROM age) AND pers.nr = age.nr; SELECT name, geburtsdatum FROM pers, age WHERE geburtsdatum >= ALL (SELECT geburtsdatum FROM age) AND pers.nr = age.nr; SELECT name, preis FROM artikel WHERE preis = (SELECT MIN(preis) FROM artikel) OR preis = (SELECT MAX(preis) FROM artikel); SELECT name, preis FROM artikel WHERE preis <= ANY (SELECT preis FROM artikel); DELETE FROM t1 WHERE s11 > ANY (SELECT COUNT(*) FROM t2 # Subquery 1 WHERE NOT EXISTS (SELECT * FROM t3 # Subquery 2 WHERE ROW(5 * t2.s1, 77) = (SELECT 50, 11 * s1 FROM t4 # Subquery 3 UNION SELECT 50, 77 FROM # Subquery 4 (SELECT * FROM t5) AS t5))); # Subquery 5 14) Transaktionen ----------------- Ein DBMS soll die Datenkonsistenz sichern bei: * Gleichzeitigem (Schreib)Zugriff mehrerer Benutzer ("Concurrency") * Server-Absturz (Stromausfall) * Hardwaredefekten * Programmierfehlern * Netzwerkfehlern (Verbindungsabbruch) * Zugriffsrechte-Problemen Transaktion (TA) = Gruppe von zusammengehörenden SQL-Anweisungen A) Entweder gemeinsam VOLLSTÄNDIG ausgeführt B) Oder GAR NICHT ausgeführt (in Ausgangszustand zurückversetzt) + Bei Widerspruch + Bei Fehler + Bei Zugriffsverletzung Nachteil: Längere Ausführungszeit Gegenseitige Behinderung Deadlocks (Über-Kreuz-Exklusiv-Zugriffe) möglich ACID-Eigenschaften einer Transaktion TA: +--------------+-------------------------------------------------------+ | Eigenschaft | Beschreibung | +--------------+-------------------------------------------------------+ | A)tomicity | TA entweder vollständig oder gar nicht durchgeführt | | C)onsistency | DB vor+nach TA konsistent (nicht unbedingt während!) | | I)solation | Gleichzeitig ablaufende TAs beeinflussen sich nicht | | D)urability | Ergebnis einer erfolgreichen TA steht dauerhaft in DB | +--------------+-------------------------------------------------------+ Transaktionen über mehrere SQL-Anweisungen: * NUR mit Engines "InnoDB" + "BDB" (MY!) * Bei allen anderen Engines ist jede einzelne SQL-Anweisung eine TA (sofern der MySQL-Server nicht abstürzt) * Mit "MyISAM" nur Locken kompletter Tabellen möglich (LOCK/UNLOCK) * Engine pro Tabelle wählbar --> Optimale Kombination auswählen * TA-Locking-Verhalten + InnoDB: Row-Level + BDB: Page-Level (evtl. mehrere "benachbarte" Rows auch gesperrt) Standardmäßig ist bei MySQL "Autocommit"-Modus eingeschaltet (MY!): * D.h. JEDE Anweisung für sich stellt Transaktion dar * Startet eine Transaktion mit "BEGIN", wird Autocommit-Modus abgeschalten (d.h. COMMIT/ROLLBACK zum Abschluss/Abbruch der Transaktion notwendig) * Deaktivieren sorgt dafür, dass "BEGIN" nicht mehr notwendig ist und alles bis zum nächsten "COMMIT/ROLLBACK" automatisch eine Transaktion darstellt * Ändern des Autocommit-Modus durch: SET AUTOCOMMIT = 1; # Jede Aktion ist TA, BEGIN startet TA bis COMMIT SET AUTOCOMMIT = 0; # Ständig TA, COMMIT/ROLLBACK beendet+startet neue Transaktionen manuell einleiten und mit COMMIT abschließen bzw. mit ROLLBACK abbrechen (AUTOCOMMIT von BEGIN automatisch deaktiviert): START TRANSACTION; # TA beginnen (auch BEGIN [WORK]) ... # SAVEPOINT ; # Marke setzen (Label) ... # RELEASE SAVEPOINT ; # Savepoint freigeben ... # ROLLBACK [WORK] TO SAVEPOINT ; # Bis Marke zurücknehmen ... # COMMIT [WORK]; # TA abschließen (ALLES!) ROLLBACK [WORK]; # TA abbrechen (ALLES!) Folgende Anweisungen (meist DDL = Data Definition Language) beenden Transaktion ebenfalls (neben COMMIT/ROLLBACK), d.h. führen automatisch "COMMIT" durch: +---------------------+---------------------------------------------------+ | CREATE DATABASE ... | Datenbank anlegen | | DROP DATABASE ... | Datenbank löschen (inkl. Tabellen) | | CREATE TABLE ... | Tabelle anlegen | | DROP TABLE ... | Tabelle löschen (inkl. Struktur) | | TRUNCATE TABLE ... | Tabellendaten löschen (da DROP + CREATE TABLE) | | | (DELETE FROM ; ohne WHERE beendet TA nicht!) | | ALTER TABLE ... | Tabellen-Struktur ändern | | RENAME TABLE ... | Tabelle umbenennen | | CREATE INDEX ... | Index anlegen | | DROP INDEX ... | Index löschen | | LOCK TABLES ... | Tabellen sperren (bis UNLOCK TABLES) | | BEGIN ... | TA sind nicht verschachtelbar! | | FLUSH LOGS ... | Alle/einige Caches auf Platte schreiben | +---------------------+---------------------------------------------------+ Hinweise: * Transaktionen sind nicht verschachtelbar (MY!) * Statt "BEGIN [WORK]" auch "START TRANSACTION" möglich (in Prozeduren verwenden, da "BEGIN" dort einen Block einleitet!) * Tritt KEIN EINZIGER Fehler auf, werden ALLE Änderungen in TA durchgeführt * Tritt EIN Fehler auf, werden ALLE bisherigen Änderungen in TA widerrufen (und die noch folgenden Anweisungen nicht mehr durchgeführt) * Bei Anwendung einer Transaktion auf mind. 1 transaktions-UN-sichere Tabelle wird jede Änderung sofort durchgeführt (MyISAM, entspricht AUTOCOMMIT=1) * "ROLLBACK" auf nicht transaktionssicherer Tabelle führt zu Fehlermeldung * Protokolldateien + ASCII: Zeichnet "ROLLBACK" auf + Binär: Zeichnet "ROLLBACK" NICHT auf Transaktions-Level (Isolations-Ebene) einstellen (STD: 2 = REPEATABLE READ): * SESSION = Für AKTUELLE Sitzung GLOBAL = Für ALLE danach neu gestartete Sitzungen (nicht für bestehende!) Sonst = Für NÄCHSTE Transaktion (1x!) * Beim Start des MySQL-Servers festlegen durch: --transaction-isolation = # STD: 2 SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL ; +------------------+---+------------+-------------------------------------+ | | | | F e h l e r m ö g l i c h k e i t | | Level | N | Datensatz- +-------+-------+-----------+---------+ | | r | sperre | Lost | Dirty | Nonrepeat | Phantom | | | | | Update| Read | able Read | Read | +------------------+---+------------+-------+-------+-----------+---------+ | -- | - | -- | ja | ja | ja | ja | | READ UNCOMMITTED | 0 | -- | -- | ja | ja | ja | | READ COMMITTED | 1 | write | -- | -- | ja | ja | Oracle-STD | REPEATABLE READ | 2 | read/write | -- | -- | -- | ja | Mysql-STD | SERIALIZABLE | 3 | read/write | -- | -- | -- | -- | +------------------+---+------------+-------+-------+-----------+---------+ Hinweise: * Standard von MySQL ist Level 2 (REPEATABLE READ) * Oracle + Standard ist Level 1 (READ COMMITTED) + Kennt nicht Level 0 (READ UNCOMMITTED) und Level 2 (REPEATABLE READ) + Alternative Level-Namen bei Oracle READ UNCOMMITTED = SNAPSHOT REPEATABLE READ = SNAPSHOT TABLE STABILITY + Weitere Angaben nach TRANSACTION möglich: READ WRITE | READ ONLY # Daten ändern (STD) / nur lesen WAIT | NO WAIT # Auf Abschluss anderer TA mit Zugriff auf gl. # Tab. warten (STD), sonst TA sofort abbrechen Beispiel: @ueberweisung = 1000; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; BEGIN; UPDATE konto SET betrag = betrag - @ueberweisung WHERE nr = 123456789; UPDATE konto SET betrag = betrag + @ueberweisung WHERE nr = 987654321; COMMIT; 15) Locking ----------- Tabellen/View-Lock: * Tabellen/Views für exklusiven Zugriff sperren + wieder freigeben * Differenzierung: Schreib-Lock oder Lese/Schreib-Lock * Alle angegebenen Tabellen werden vollständig gesperrt * Funktioniert bei jeder Engine! * WRITE hat höhere Priorität als READ LOCK TABLES # Sperren mehrerer Tabellen gleichzeitig: table1 WRITE, # Lese+Schreibschutz für alle anderen table1 AS alias1 READ, # Schreibschutz (Lesen für alle erlaubt) table2 AS alias2 READ LOCAL, # INSERT zulassen solange kein Konflikt table3 LOW_PRIORITY WRITE; # Erst sperren wenn kein READ-Lock (warten) ... # Operationen auf gelockten Tabellen ... UNLOCK TABLES; # Freigeben aller Locks +--------------------+-------------------------------------------------------+ | Lock-Typ | Bedeutung | +--------------------+-------------------------------------------------------+ | WRITE | Lese- und Schreibschutz für alle außer Anforderer | | READ | Schreibschutz für alle außer Anf. (Lesen für alle OK) | | READ LOCAL | Schreibschutz, aber INSERT OK solange kein Konflikt | | LOW_PRIORITY WRITE | Erst sperren wenn kein READ-Lock (warten) | +--------------------+-------------------------------------------------------+ Tabellen gegen Zugriffe durch andere Threads sperren (alle benötigten Tabellen gleichzeitig!): * Wartet, bis alle anderen Zugriffe auf die Tabellen beendet sind LOCK TABLES , ...; * IN SHARE MODE # IN SHARE MODE NOWAIT # IN EXCLUSIVE MODE # IN EXCLUSIVE MODE NOWAIT # Beispiel: LOCK TABLES pers WRITE; ALTER TABLE pers DISABLE KEYS; INSERT INTO pers (nr, vorname, name) VALUES (1, "Thomas", "Birnthaler"), (2, "Markus", "Mueller"), (8, "Andrea", "Bayer"); ALTER TABLE pers ENABLE KEYS; UNLOCK TABLES; Hinweise: * LOCK/UNLOCK beziehen sich auf aktuelle Sitzung * LOCK führt implizites COMMIT durch * LOCK (und Transaktions-Beginn) führt implizites UNLOCK durch * Wartet, bis ALLE Tabellen gemeinsam gelockt werden können * Deadlock-frei (Reihenfolge der Locks durch Datenbank gewählt) * Lock auf temp. Tabelle erlaubt aber ignoriert (sowieso sitzungsbezogen) * LOCK auf View lockt alle ihre Basis-Tabellen! * Nach LOCK nur auf gelockte Tabellen zugreifbar (auf andere nicht)! * Mehrfachzugriff per Tabellen-Alias braucht Mehrfachlock mit diesen Aliasen! * Transaktion-Simulation für MyISAM (und andere nicht TA-fähige Engines) * Tabelle löschen nach LOCK möglich, aber nicht Tabelle anlegen oder TRUNCATE Advisory Lock (anwendungsbezogen): * Frei definierbare Locks (per Name) * Anwendungen müssen Lock selbst anfordern * Anwendungen, die sich nicht daran beteiligen, werden nicht mit einbezogen (können machen was sie wollen) * Deadlock möglich (DB kümmert sich nicht um ihre Auflösung) +-------------------------+------------------------------------------------+ | Funktion | Bedeutung | +-------------------------+------------------------------------------------+ | GET_LOCK(, ) | Lock beantragen (Timeout nach Sek.| | | (Erg: 1=erhalten, 0=nicht erh., NULL=Fehler) | | IS_FREE_LOCK() | Lock frei? (Erg: 1=ja, 0=nein) | | IS_USED_LOCK() | Lock belegt? (Erg: 1=ja, 0=nein) | | RELEASE_LOCK() | Lock freigeben | +-------------------------+------------------------------------------------+ 16) Views (Sichten) ------------------- Views (Sichten) sind vordefinierte gespeicherte benannte Abfragen (SELECT- Anweisungen), sie werden auch "virtuelle" Tabellen (derived table) genannt. Zweck: * Tabellen und Spalten umbenennen (um Änderungen nach Außen zu verbergen) * Zusätzliche berechnete Spalten einführen * Zugriffsbeschränkung (per Sichtbarkeit von Spalten und Datenzeilen) * Zugriff vereinfachen (Verknüpfung mehrerer Tab. sieht wie eine Tab. aus) * Verbergen konkrete Tabellenstruktur (änderungsfreundlich) * Schrittweises "Refactoring" eines Schemas (alte Tab. wg. Alt-SW beibehalten) + Migration/Umprogrammierung von Applikationen ermöglichen Eigenschaften: * Ableitung aus einer oder mehreren Basis-Tabellen oder anderen (Unter-)Views auf der Basis von Joins, Unions und Unterabfragen + Spalten oder Ausdrücke mit Funktionen, Konstanten, Spalten, Operatoren... + ORDER BY ist möglich (aber evtl. bei weiterem ORDER BY ignoriert) * Verwendbar wie eine normale Tabelle (Pseudotabelle) + Tabellen und Views teilen sich selben Namensraum in einer Datenbank * Spaltennamen + Weglassen --> Spaltennamen automatisch durch SELECT gebildet + Angeben --> Anzahl Spalten von View und SELECT MUSS gleich sein! * Über Views möglich: + Daten abfragen: Immer + Daten ändern: Unter bestimmten Bedingungen + Daten einfügen: Unter bestimmten Bedingungen + Daten löschen: Unter bestimmten Bedingungen * View wird zum Definitionszeitpunkt "eingefrohren" + "Updatability Flag" wird erstellt (UPDATE/INSERT prinzipiell möglich) + Änderungen an Unter-Tabellen oder -Views wirken sich nicht aus + Löschen von Unter-Tab/Views --> Fehlermeldung erst bei View-Verwendung * Beschränkung der Einfüge-Daten auf Erfüllung der View-WHERE-Klausel möglich (WITH CHECK OPTION --> Prüft, ob Daten mit WHERE-Bed. der View ausgefiltert) Beschränkungen: * Nur dann aktualisierbar ("Updatable View", d.h. INSERT/UPDATE/DELETE), wenn 1:1-Beziehung zwischen Datensätzen in View und Basis-Tabellen vorhanden + Nicht möglich bei Verwendung von Aggregat-Funktionen SUM, MIN, MAX, COUNT, sowie DISTINCT, GROUP BY, HAVING, UNION, UNION ALL, Subqueries, ... + Per Ausdruck abgeleitete View-Spalten sind nicht updatebar + Alle zu ändernden Spalten müssen in EINER Basis-Tabelle liegen + Nicht möglich bei Realisierung der View per temp. Tabelle (TEMPTABLE) * Neue Sätze einfügbar ("Insertable", d.h. INSERT) wenn (zusätzlich zu oben) + Kein Basis-Spaltenname doppelt verwendet + Alle Spalten der Basis-Tabelle ohne Defaultwert in View enthalten + Keine per Ausdruck abgeleiteten View-Spalten + Nur EINE Basis-Tabelle ist betroffen * Kein Index auf Views möglich + Ausnutzung von Indices bei MERGE möglich, bei TEMPTABLE nicht * Keine temporäre Tabelle als Basis möglich * Kein Trigger mit View assozierbar (manche DB erlauben "INSTEAD OF" Trigger) * Ableitungs-Algorithmus MERGE/TEMPTABLE ist Eigenschaft der View-Definition und unabhängig von der darauf auszuführenden SQL-Anweisung (MY!) + Führen evtl. Entwickler in die Irre (sieht einfach aus obwohl sehr komplex) + Eigene Optimierung (getrennt von Tabellen) --> Nicht so gut ausgetestet + Schlecht editierbar da Originaltext verloren geht (MY!) --> S281 (Roland Bouman) * In Liste der Tabellen mit enthalten --> Mit Präfix "v_" und "t_" unterscheidbar machen "Materialized" View (nicht in MY!): + Unsichtbare Tabelle die periodisch upgedated wird * Kostet Speicherplatz * Wird nicht bei jeder Veränderung der Basis-Tabellen aktualisiert * Verhält sich wie statische Tabelle --> Schneller als normale View * Für häufige View-Abfragen, deren Basis-Tabellen sich selten ändern * Simulierbar per EVENT/TRIGGER, der View asynchron/synchron als echte Tabelle erstellt (MY!) * "Permanente temporäre" Tabelle Definition von Views: CREATE [OR REPLACE] # REPLACE=View ersetzen [ALGORITHM = (MERGE | TEMPTABLE | UNDEFINED)] # (STD: UNDEFINED, MY!) [DEFINER = { | CURRENT_USER}] # [SQL SECURITY {DEFINER | INVOKER}] # VIEW [(, ...)] # Name + opt. Spaltennamen AS [WITH [CASCADED | LOCAL] CHECK OPTION]; View(s) umbenennen (aber nicht in andere Datenbank verschieben!, stellt eine "atomare" Operation dar, auch bei Umbenennung mehrerer Views): RENAME TABLE TO [, ...]; ALTER TABLE RENAME TO ; View löschen: DROP VIEW ; DROP VIEW IF EXISTS ; # Kein Fehler falls nicht existent Algorithmus ermitteln den View benutzt: EXPLAIN SELECT * FROM ; # "TEMPTABLE" falls "DERIVED" in Ausgabe EXPLAIN EXTENDED SELECT * FROM ; # "TEMPTABLE" falls "DERIVED" in Ausgabe Beispiele für Views: #----------------------------------------------------------------------------- # View 1 #----------------------------------------------------------------------------- DROP TABLE IF EXISTS t1; CREATE TABLE t1 ( anz INT, preis INT ); INSERT INTO t1 VALUES (3, 50), (7, 80), (9, 99), (1, 12); DROP VIEW IF EXISTS v1; CREATE VIEW v1 AS SELECT anz, preis, anz * preis AS "wert" FROM t1; SELECT * FROM v1; DROP VIEW v1; DROP TABLE t1; #----------------------------------------------------------------------------- # View 2 #----------------------------------------------------------------------------- DROP TABLE IF EXISTS t2; CREATE TABLE t2 ( nr INT, name CHAR(30) ); INSERT INTO t2 VALUES (1, "a"), (2, "abcde"), (5, "test"), (4, "text"); DROP VIEW IF EXISTS v2; CREATE VIEW v2 AS SELECT *, "konstant" FROM t2 WHERE nr = LENGTH(name); SELECT * FROM v2; DROP VIEW v2; DROP TABLE t2; #----------------------------------------------------------------------------- # View 3 #----------------------------------------------------------------------------- DROP VIEW IF EXISTS v3; CREATE VIEW v3 (Artikel, Hersteller) AS SELECT a.name, h.name FROM artikel AS a, hersteller AS h WHERE a.name = h.name; #----------------------------------------------------------------------------- # View 4 #----------------------------------------------------------------------------- DROP VIEW IF EXISTS v4; CREATE VIEW v4 (Name, Artikel) AS SELECT p.name, a.name FROM artikel a, bestellung b, pers p WHERE a.nr = b.nr AND p.nr = b.nr AND p.name = "Müller" AND p.vorname = "Oskar"; #----------------------------------------------------------------------------- # View 5 #----------------------------------------------------------------------------- DROP VIEW IF EXISTS v5; CREATE VIEW v5 AS SELECT * FROM artikel # ALLE Spaltennamen übernommen WHERE preis > 200 # Bedingung an Datensätze WITH CHECK OPTION; # Prüfen ob Bed. bei Einf./Ändern erfüllt 17) Variablen ------------- MySQL kennt folgende Variablen-Arten: * @@SERVER: Einstellungen/Attribute MySQL-Server (fix) --> SHOW VARIABLES; --> SHOW GLOBAL VARIABLES; --> SHOW LOCAL VARIABLES; * @SESSION: Sitzungsbezogen (frei wählbar, read-write) * LOKAL: Stored Procedure/Event/Trigger (frei wählbar, read-write) * STATUS: Messwerte MySQL-Server (fix, read-only) --> SHOW/FLUSH STATUS; ist im Folgenden ein Variablen-Bezeichner (Identifier), d.h. er kann aus den Zeichen A-Z, a-z, 0-9, _ und $ bestehen, führende Ziffern sind nicht erlaubt --> 2f) Identifier (Bezeichner). +---------+------------------------------------------------------------------+ | Typ | Bedeutung | +---------+------------------------------------------------------------------+ | @@ | SERVER-Variable (klein geschrieben mit "_") | | | + Vom Server festgelegt | | | - Feste Anzahl (etwa 450 je nach MySQL-Version) | | | - FIXE Namen | | | - Enthalten Einstellungen/Attribute des MySQL-Servers | | | - Neue Version --> kennt neue zusätzlich / veraltete nicht mehr| | | - Server-Neustart --> Neu initialisiert | | | - Lesbar + sehr viele schreibbar (evtl. mit SUPER-Berechtigung)| | | + Startwert | | | - Fest eingebaut in "mysqld"-Server ODER | | | - Per Options-Parameter beim Server-Start übergeben ODER | | | - Aus Server-Konfig "my.cnf/ini" beim Start | | | + Viele doppelt vorhanden | | | - GLOBAL: Server-weiter (Default)wert | | | - SESSION: Für akt. Session (GLOBAL-Kopie bei Verb.-Aufbau) | | | + Gültigkeitsbereich: | | | - Einige GLOBAL STATISCH (nur beim Server-Start einstellbar) | | | - Einige GLOBAL DYNAMISCH (jederzeit mit SUPER-Recht änderbar) | | | - Viele SESSION-bezogen (jederzeit für akt. Sitzung änderbar) | | | + Session-Wert wird mit globalem Wert initialisiert | | | - Session-Wert geändert --> "überdeckt" Globalen Wert | | | - Verschwindet am Sitzungsende | | | + Auflisten per SHOW [GLOBAL|SESSION] VARIABLES [LIKE "..."] | +---------+------------------------------------------------------------------+ | @ | SESSION-Variable (Sitzungsbezogen): | | | + Benutzerdefiniert | | | - Name frei wählbar | | | - Beliebig viele | | | - NICHT ZU DEKLARIEREN (Datentyp beliebig, 1 Wert) | | | - Lesbar + schreibbar | | | + Gültigkeitsbereich: | | | - Auf Sitzung beschränkt | | | --> Gleichnamige versch. Sitzungen überschneiden sich NICHT | | | - Verschwinden am Ende der Sitzung (Verbindungsende) | | | + Nicht per SQL-Anweisung auflistbar | +---------+------------------------------------------------------------------+ | | LOKALE Variable + Aufruf-Parameter in Stored Proc./Event/Trigger | | | + Benutzerdefiniert | | | - Name frei wählbar | | | - Beliebig viele | | | - Vor Verwendung zu DEKLARIEREN (mit SQL-Datentyp) | | | (z.B. DECLARE betrag FLOAT DEFAULT 0.0;) | | | - Lesbar + schreibbar | | | + Gültigkeitsbereich: | | | - Auf Stored Procedure/Event/Trigger/... beschränkt | | | - Verschwinden am Ende von Stored Procedure/Event/Trigger/... | | | + Nicht per SQL-Anweisung auflistbar | +---------+------------------------------------------------------------------+ | | STATUS-Variable (großer Anfangsbuchstabe, Rest klein mit "_") | | | + Vom Server festgelegt | | | - Feste Anzahl (etwa 350 je nach MySQL-Version) | | | - FIXE Namen | | | - Enthalten Messwerte des MySQL-Servers | | | - Neue Version --> kennt neue zusätzlich / veraltete nicht mehr| | | - Server-Neustart --> Neu initialisiert | | | - Nur lesbar | | | + Gültigkeitsbereich: | | | - Serverweit gültig | | | - Mit FLUSH STATUS auf 0-Wert zurücksetzen (SUPER-Berechtigung)| | | + Auflisten per SHOW STATUS [LIKE "..."] | +---------+------------------------------------------------------------------+ Eigenschaften: * Variablenname darf kein Objektname und SQL-Schlüsselwort sein (außer er wird durch `...` eingerahmt --> 2f) Identifier (Bezeichner)) * Bis MY!4.1 wird GROSS/kleinschreibung unterschieden, ab MY!5.0 nicht mehr * Änderung von Globalen Server-Variablen --> SUPER-Recht notwendig Session-Variable @v einen Wert zuweisen (Variable dabei evtl. neu angelegt): SET @v = 3; # Schlecht, besser ":=" verwenden! SET @v := 3; # Besser als Verwendung von "="! @v = 3; # Fehler (SET davor notwendig) @v := 3; # Fehler (SET davor notwendig) SELECT @v := 3; # Zuweisung eines SELECT-Ergebnisses SELECT @v := COUNT(*) FROM pers; # (analog) SELECT COUNT(*) FROM pers INTO @v; # (analog) SELECT name, vorname FROM pers # Mehrere Variablen gleichzeitig WHERE nr = 1 INTO @v1, @v2; # MY!5.0 ACHTUNG: Folgende Anweisung ist KEINE Zuweisung sondern ein VERGLEICH der Session-Variablen @v mit dem Ergebnis von "SELECT COUNT(*) FROM pers". Gibt je nach Wert von @v und dem SELECT-Ergebnis TRUE oder FALSE zurück (Wert von @v bleibt unverändert): SELECT @v = COUNT(*) FROM pers; # Fehler: Vergleich (statt Zuweisung) SELECT @v := COUNT(*) FROM pers; # OK: Zuweisung Server-Variable (Session oder Global) auflisten: SHOW SESSION VARIABLES; # ALLE sessionbezogenen Server-Var. SHOW LOCAL VARIABLES; # ALLE sessionbezogenen Server-Var. SHOW GLOBAL VARIABLES; # ALLE globalen Server-Var. SHOW VARIABLES; # ALLE Server-Var. (Session, sonst Global) SHOW SESSION VARIABLES LIKE "b%"; # PASSENDE Server-Var. (Session) SHOW LOCAL VARIABLES LIKE "b%"; # PASSENDE Server-Var. (Session) SHOW GLOBAL VARIABLES LIKE "b%"; # PASSENDE Server-Var. (Global) SHOW VARIABLES LIKE "b%"; # PASSENDE Server-Var. (Sess., sonst Global) Status-Variablen auflisten: SHOW STATUS; # Alle Status-Var. (Server) SHOW STATUS LIKE "b%"; # Einige Status-Var. (Server) Variablen-Wert anzeigen: SELECT wait_timeout; # Lokale Var. in Stored Procedure/Event SELECT @wait_timeout; # Session SELECT @@SESSION.wait_timeout; # Server-Session SELECT @@GLOBAL.wait_timeout; # Server-Global SELECT @@wait_timeout; # Server-Session ODER -global # (Session "überdeckt" Global) Einige Server-Variable sind nur Global verfügbar: SELECT @@SESSION.binlog_cache_size; # Fehler da nur Global! SELECT @@GLOBAL.binlog_cache_size; # Global SELECT @@binlog_cache_size; # Automatisch Global Belegung von Variablen mit einem Wert: SET wait_timeout = 900; # 0) Lokal (nur in Stored Procedure) SET @wait_timeout = 901; # 1) Session SET @@SESSION.wait_timeout = 902; # 2a) Server-Session SET SESSION wait_timeout = 903; # 2b) Server-Session, überschreibt 2a) SET @@GLOBAL.wait_timeout = 904; # 3a) Server-Global SET GLOBAL wait_timeout = 905; # 3b) Server-Global, überschreibt 3a) Erneut Variablen-Wert anzeigen: SELECT wait_timeout; # 0) Stored Procedure --> 900 SELECT @wait_timeout; # 1) Session --> 901 SELECT @@SESSION.wait_timeout; # 2) Server-Session --> 903 SELECT @@GLOBAL.wait_timeout; # 3) Server-Global --> 905 SELECT @@wait_timeout; # 4) Server-Session ODER -global --> 903 # (Session "überdeckt" Global) 18) Prepared Statements (Vorbereitete Anweisungen) -------------------------------------------------- Prepared Statements sind vorbereitete SQL-Anweisungen ("Template/Skeleton") mit PLATZHALTERN der Form "?" (oder ":name", die erst beim Aufruf mit WERTEN (aus Variablen) gefüllt werden. Ein Prepared Statement ist entweder * ein String-Literal "..." oder * eine Text-Variable mit einer SQL-Anweisung als Inhalt Eigenschaften: * Nur lokal pro Sitzung existent (liegen nicht auf Platte!) --> Gleichnamige verschiedener Sitzungen überschneiden sich nicht! * Prepared Statement --> Statement-Handle * Hauptzweck + In Stored Procedures einsetzbar ("dynamic SQL") + Einschränkungen: - Datenbank-, Tabellen- und Spalten-Namen sind Identifier --> Nicht per PLATZHALTER parametrisierbar - LIMIT und OFFSET parametrisierbar seit MY!5.6.x * Geschwindigkeitsgewinn + SQL-Anweisung nur 1x zu kompilieren + Optimierung nur 1x durchzuführen + Daten binär statt ASCII-Text (DATE 10 --> 3, BLOB, TEXT, ..) + Bei jeder Ausführung nur Parameter für Platzhalter senden * Sicherheitsgewinn + Escape/Quote unnötig + "SQL-Injection" unmöglich (Benutzer-Eingabe kann SQL-Anweisung nicht manip) + REGEL: "Never trust incoming Data!" + Reguläre Ausdrücke zum Prüfen der Benutzer-Daten bieten sich an! * Erneut Prepared Statement mit gleichem Namen --> Altes wird überschrieben * Binäres Protokoll nur von API genutzt, nicht vom SQL-Interface Drei prinzipielle Typen: * Client-side: Client schickt Endergebnis der Ersetzung (Emulation) * Server-side: Statement Identifier, Binäres Protokoll, Parameter (API) * SQL-Interface: Trennung PREPARE + EXECUTE, Textprotokoll, SQL-Variablen Optimierung mehrstufig: * Bei Definition: + SQL-Text parsen + Negationen entfernen + Subqueries umschreiben * Erste Ausführung: + Nested Joins vereinfachen + OUTER JOIN --> INNER JOIN falls möglich * Jede Ausführung: + Partitionen weglassen + COUNT(), MIN(), MAX() weglassen wo möglich + Konstante Subexpression weglassen + Konstante Tabellen erkennen + Identitäten verteilen + Zugriffsmethoden "ref", "range" und "index_merge" analysieren + optimieren + Join Reihenfolge optimieren Beschränkungen: * Nur lokal pro Sitzung existent (liegen nicht auf Platte!) + Statement-Handle nur darin verwendbar * In Prozeduren verwendbar, aber NICHT in Funktionen, Triggern und Events * Nicht im Query-Cache (vor MY!5.1, später schon) * Platzhalter nur für Datenwerte, NICHT aber für SQL-Schlüsselworte und für Datenbank-, Tabellen- und Spalten-Namen einsetzbar + LIMIT/OFFSET-Werte möglich seit MY!5.6.X * Nur Variable in Platzhalter einsetzbar, keine Ausdrücke oder Literale + Anzahl Variable muss gleich Anzahl Platzhalter sein * Nur eine SQL-Anweisung pro Prepared Statement erlaubt (kein ";") * Nicht verschachtelbar * Einmaliger Einsatz langsamer als äquiv. normale SQL-Anweisung auszuführen * "Memory Leak" möglich, falls Freigabe vergessen wird * Maximal "max_prepared_stmt_count" pro Server möglich (Wert 0 --> keine) Prepared Statement mit Platzhaltern (ergibt Statement-Handle ): PREPARE # Beim Ausführen ausgefüllt, Anführungszeichen nötig, FROM ; # "?" als Wert-Platzhalter, Textvariable erlaubt Prepared Statement mit Variablenwerten füllen und ausführen: EXCECUTE ; # Ohne Platzhalter EXCECUTE # Falls Platzhalter "?" verwendet USING , ...; # MÜSSEN Variable sein, keiner Ausdr./Literale! Prepared-Statement anzeigen gibt es nicht! Prepared Statement löschen (Speicher freigeben, "Memory Leak" falls vergessen): DEALLOCATE PREPARE ; # OK DROP PREPARE ; # OK Beispiel (Platzhalter "?" im Statement NICHT in "..." oder '...' setzen, auch wenn der repräsentierte Wert dies eigentlich erfordern würde): # Beispiel 1 DROP TABLE IF EXISTS buecher; CREATE TABLE buecher ( titel CHAR(50), autor CHAR(30), isbn INT ); PREPARE neues_buch FROM "INSERT INTO buecher(titel, autor, isbn) VALUES (?, ?, ?)"; SET @titel = "MySQL für Anfänger"; # Variable setzen SET @autor = "Thomas Birnthaler"; # Variable setzen SET @isbn = "3-987654-321-0"; # Variable setzen EXECUTE neues_buch USING @titel, @autor, @isbn; # OK EXECUTE neues_buch USING "Carrie", "King", "9-876543-210-X"; # Falsch, nur # Variable OK! SELECT * FROM buecher; DEALLOCATE PREPARE neues_buch; # OK DROP PREPARE neues_buch; # Ebenfalls OK # Beispiel 2 PREPARE stmt1 FROM "SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse"; SET @a = 3; SET @b = 4; EXECUTE stmt1 USING @a, @b; DEALLOCATE PREPARE stmt1; # Beispiel 3 SET @s = "SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse"; PREPARE stmt2 FROM @s; SET @a = 6; SET @b = 8; EXECUTE stmt2 USING @a, @b; DEALLOCATE PREPARE stmt2; # Beispiel 4 DROP TABLE IF EXISTS t1; CREATE TABLE t1 (a INT NOT NULL); INSERT INTO t1 VALUES (4), (8), (11), (32), (80), (90), (100); SELECT * FROM t1; SET @table = "t1"; SET @limit = 2; SET @offset = 3; SET @s = CONCAT("SELECT * FROM ", @table, " LIMIT ", @limit, " OFFSET ", @offset); SELECT @s as "Statement1"; PREPARE stmt3 FROM @s; EXECUTE stmt3; DEALLOCATE PREPARE stmt3; /*!50700 SET @s = CONCAT("SELECT * FROM ", @table, " LIMIT ? OFFSET ?"); SELECT @s as "Statement2"; PREPARE stmt4 FROM @s; EXECUTE stmt4 USING 1, 3; EXECUTE stmt4 USING "1", "3"; DEALLOCATE PREPARE stmt4; */ # Beispiel 5 # 1x an den Server schicken PREPARE select_boot FROM "SELECT b.boot_id, bd.laenge FROM boot AS b JOIN boot_detail AS bd WHERE b.name = ? AND bd.typ = CONCAT("fest_", ?); # Nx ausfuehren mit unterschiedlichen Werten EXECUTE select_boot USING @name, @typ; 19) Stored Routines (Stored Procedures/Functions) ------------------------------------------------- Eigenschaften: * Dienen der Kapselung von Geschäftslogik in der Datenbank (nur 1x), viele verschiedene Anwendungen rufen sie nur auf (statt Nx programmiert) * Routine = Prozedur oder Funktion + Funktion hat Rückgabewert --> In Ausdrücken oder per SELECT aufzurufen (gerne vergessen!) + Prozedur hat KEINEN Rückgabewert (außer per INOUT/OUT-Parameter), --> Mit "CALL" aufzurufen (gerne vergessen!) * An eine Datenbank GEBUNDEN (werden mit dieser Datenbank gelöscht!), eigentliche Definition liegt in zentraler Datenbank "mysql" * Anweisungen in Funk./Proz. beziehen sich auf eine einzige Datenbank (auf beim Aufruf aktuell mit USE ausgewählte) * Körper (Body) besteht aus einer/mehreren SQL-Anweisungen (mehrere in BEGIN...END einschließen) * Erlaubt sind die meisten DDL-, DML- und DCL-Anweisungen (aber keine Transaktionssteuerung!), verboten sind: + LOCK/UNLOCK TABLES + ALTER VIEW + LOAD DATA/TABLE + PREPARE, EXECUTE, DEALLOCATE PREPARE # nicht in Functions/Trigger + Alle in Prepared Statements nicht erlaubten Anweisungen * Rechte zum Erstellen/Ändern (CREATE ROUTINE, ALTER ROUTINE) und zum Ausführen (EXECUTE) notwendig * Lokale Variablen möglich + Definition: DECLARE DEFAULT ; + Initialisierung: SET := ; + Wert in Anweisung verwenden: ... ... + Parameter sind ebenfalls automatisch lokale Variablen * Aufruf: CALL ; # In Default/Standard-Datenbank CALL . # In anderer Datenbank SELECT ; # Oder im Rahmen eines Ausdrucks SELECT .; # Analog in anderer Datenbank * Eigene Funktionen wie eingebaute MySQL-Funktionen verwendbar * Rekursiver Aufruf in Funktionen/Prozeduren erlaubt (Fakultät) Beschränkungen: * Anzahl und Typ der Parameter muss beim Aufruf eingehalten werden * Nur EIN Wert pro Variable übergebbar (keine Arrays/Objekte/Strukturen) * Variablen NICHT für Datenbank/Tabellen/Spaltennamen nutzbar (aber für LIMIT/OFFSET-Werte!) * Macht "Prelocking" aller darin benötigten Tabellen! * Replikation des Aufrufs oder der Änderungen durch den Aufruf? + MY!5.0: Binary Logging, alle DETERMINISTIC oder log_bin_trust_function_creators * Bei jedem Aufruf geparst und optimiert (auch wenn DETERMINISTIC) Hinweise: * SET vor Variablenzuweisung gerne vergessen * CALL vor Prozeduraufruf gerne vergessen * SELECT vor Funktionsaufruf notwendig * Das Abschlusszeichen von SQL-Anweisungen ";" (SQL-Delimiter) ist VOR der Definition einer Prozedur/Funktion temporär auf "//" oder ähnliches setzen und DANACH wieder auf ";" zurücksetzen, damit ";" in der Definition zur Trennung der SQL-Statements verwendbar ist (MY!). DELIMITER // ... # Prozedur/Funktions-Definition, die SQL-Anweisungen mit ";" enthält, ... # ohne die soforige Ausführung dieser SQL-Anweisungen auszulösen ... DELIMITER ; Vorteile/Nachteile: !!! * S283 HINWEIS: Beispiele für alle Kontrollstrukturen folgen am Ende des Abschnitts 19a) Lokale Variablen --------------------- Eigenschaften: * Nur innerhalb Prozeduren/Funktionen/Event/Trigger erlaubt * Nur am Anfang deklarierbar (direkt nach BEGIN) * Jeder SQL-Datentyp in Deklaration verwendbar (INT, FLOAT, CHAR(20), ...) * Initialisierbar per DEFAULT-Anweisung (sonst NULL als Startwert) * Kein "@" oder "@@" vor Variablenname (--> Session- oder Server-Variable) * Verschwinden beim Verlassen von Stored Procedure/Event DECLARE anz INT; # Mit NULL initialisiert DECLARE max FLOAT DEFAULT 0.0; # Mit 0.0 initialisiert DECLARE sum FLOAT DEFAULT 0.0; # Mit 0.0 initialisiert DECLARE str CHAR(30) DEFAULT ""; # Mit "" initialisiert DECLARE str2 CHAR(30) DEFAULT "Summe"; # Mit "Summe" initialisiert 19b) Wertzuweisung an Variable ------------------------------ Direkte Wertangabe oder Ergebnis einer Berechnung zuweisen (Berechnung geht in DECLARE wahrscheinlich nicht): SET max = 9999; # Mit Vergleich "=" verwechselbar SET str = "Hallo"; # Mit Vergleich "=" verwechselbar SET str = CONCAT(str, " welt!"); # Mit Vergleich "=" verwechselbar SET anz = anz + 1; # Mit Vergleich "=" verwechselbar SET max := 9999; # NICHT mit Vergleich "=" verwechselbar SET str := "Hallo"; # NICHT mit Vergleich "=" verwechselbar SET str := CONCAT(str, " welt!"); # NICHT mit Vergleich "=" verwechselbar SET anz := anz + 1; # NICHT mit Vergleich "=" verwechselbar HINWEISE: + Zugewiesener Wert muss zu Datentyp passen + In Ausdruck Aufruf von MySQL-Funktionen und eigener Funktionen erlaubt SELECT-Ergebnis zuweisen: SELECT COUNT(*) FROM pers INTO anz; # @anz und @@anz auch möglich SELECT MAX(preis) FROM artikel INTO max; # SELECT SUM(anz) FROM artikel INTO sum; # SELECT SUM(anz) INTO sum FROM artikel; # Auch möglich SELECT MIN(preis), MAX(preis), SUM(anz) # Mehrere Variablen auf einmal FROM artikel INTO min, max, sum; # SELECT SUM(anz) INTO sum FROM artikel; # Auch möglich SELECT func(1, 3.14, "text") INTO str; # Funktionsergebnis HINWEISE: * GENAU EIN Datensatz muss selektiert werden (Trick: LIMIT 1 anhängen), (mehrere Sätze müssen per CURSOR-Zugriff in einer Schleife behandelt werden) * Anzahl selektierter Spalten muss gleich Anzahl Variablen sein. 19c) Ausgabe von Variablen oder Daten ------------------------------------- SELECT @str AS "Titel"; # Einzelwert SELECT "Fester Text: ", @wert, " Euro"; # Werteliste SELECT @sum / @anz * 100; # Ausdruck SELECT CONCAT("Fester Text: ", 17.00, @str) AS Text; # Verketteter Text SELECT func(1, 3.14, @str2) AS "Funktionswert" # Funktionsergebnis 19d) Kontrollstrukturen (Block/Compound Statement) -------------------------------------------------- Eigenschaften: * Ein BEGIN...END-Block fasst mehrere SQL-Anweisungen zusammen (statt einzelner SQL-Anweisung verwendbar) * Benennung per Marke