MySQL - Comelio Medien

MySQL – Kurzreferenz Teil 2: Abfragen und Programmierung
Abfragen
Abfragen
Sichten
Sichten
Basis-Syntax
Verknüpfungen
Abfragen haben folgende Bestandteile:
Man unterscheidet verschiedene Arten der Verknüpfung (typischerweise entlang der Primärschlüssel-Fremdschlüssel-Beziehung) für
zwei Tabellen tab1 und tab2. Die meisten erfordern nach ON eine
Bedingung bed, welche zwei oder mehr Spalten in Beziehung setzt:
ON tab1.spalte = tab2.spalte. Zur Tabellennamen-Verkürzung
bei gleich lautenden Spaltennamen setzt man Tabellenaliasnamen
ein: tab1 AS t1.
• SELECT enthält die Spaltenliste inkl. optionaler Spaltenaliasnamen
• Hinweise für Abfrageverarbeitung zwischen SELECT und den
Spaltennamen für Caching, Priorität der Abfrage oder Ein-/
Ausblendung von Duplikaten im Ergebnis
• FROM enthält die Tabellen und ihre Verknüpfungen
• WHERE enthält die Filterbedingungen
• GROUP BY gibt die Gruppierung an und HAVING enthält die Filter
auf Gruppenebene
• ORDER BY enthält die Sortierung
• LIMIT enthält die maximale Zeilenzahl im Ergebnis
• PROCEDURE enthält den Namen einer Prozedur, aus der die
Ergebnismenge stammt
• INTO OUTFILE oder DUMPFILE legt fest, dass die Ergebnismenge
in eine Datei geschrieben werden soll
• FOR UPDATE oder LOCK sperrt die abgerufenen Ergebnisse
SELECT
[ALL | DISTINCT | DISTINCTROW ]
[HIGH_PRIORITY]
[STRAIGHT_JOIN]
[SQL_SMALL_RESULT] [SQL_BIG_RESULT]
[SQL_BUFFER_RESULT]
[SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
select_ausdruck, ...
[FROM tabelle_verweise
[WHERE filter]
[GROUP BY {spalte | ausdruck | position}
[ASC | DESC], ... [WITH ROLLUP]]
[HAVING filter]
[ORDER BY {spalte | ausdruck | position}
[ASC | DESC], ...]
[LIMIT {[beginn,] anzahl | anzahl OFFSET beginn}]
[PROCEDURE procedure_name(argument_list)]
[INTO OUTFILE ‘datei‘ optionen
| INTO DUMPFILE ‘datei‘]
[FOR UPDATE | LOCK IN SHARE MODE]]
Operatoren
Operatoren können für Ausdrücke in der SELECT-Liste für Spaltenausgaben, links/rechts von der WHERE-Klausel für Filterausdrücke
oder auch für die Sortierung genutzt werden .
• Allgemeine Operatoren: +, -, /, *
• Filter-Operatoren: <, >, <=, >=, !=, [NOT] BETWEEN untergrenze AND obergrenze, [NOT] LIKE für unscharfe Suche mit _
als Platzhalter für ein Zeichen und % als Platzhalter für mehrere
Zeichen, [NOT] IN (wert1, wert2,…)
• Boolesche Operatoren für Filter: AND, OR, NOT
• Innere Verknüpfung (Standardfall): tab1 INNER JOIN tab2 ON
bed / Datensätze mit Treffern aus tab1 und tab2 gelangen in die
Ergebnismenge.
• Äußere Verknüpfung: tab1 [LEFT |RIGHT] OUTER JOIN tab2
ON bed / Datensätze mit Treffern aus tab1 und tab2 sowie
diejenigen aus tab1 (LEFT) oder tab2 (RIGHT) gelangen in die
Ergebnismenge.
• Kreuzverknüpfung (selten): tab1 CROSS JOIN tab2 ON bed /
Datensätze werden kreuzweise miteinander kombiniert
• Natürliche / Spaltennamen-Verknüpfung: tab1 NATURAL [LEFT
[OUTER]] JOIN tab2 / innere oder äußere Verknüpfung anhand
gleicher Spaltennamen und daher ohne Bedingung
Vereinigung
Zwei Ergebnismengen mit gleicher Spaltenanzahl und gleichen Datentypen in den einzelnen Spalten können über UNION aneinander
gehängt werden und bilden dann eine gemeinsame Ergebnismenge.
Duplikate werden dabei eingeblendet (ALL) oder ausgeblendet
(DISTINCT). Die Spaltennamen für die Ergebnismenge stammen aus
der ersten Abfrage.
SELECT ...
UNION [ALL | DISTINCT]
SELECT ...
[UNION [ALL | DISTINCT] SELECT ...]
Gruppierung
Die Gruppierung steht zwischen WHERE und ORDER BY und wird
typischerweise mit Aggregatfunktionen kombiniert, welche für die
gruppierten Werte angewandt werden. Die Aggregatwerte können
dann mit HAVING noch gefiltert werden, während WHERE die zu
gruppierenden Daten vorher bereits filtern kann.
Über WITH ROLLUP lassen sich berichtsähnliche Untersummen
erzeugen.
SELECT select_ausdruck, ...
FROM tabelle_verweise
[GROUP BY {spalte | ausdruck | position}
[ASC | DESC], ... [WITH ROLLUP]]
[HAVING filter]
Aggregatfunktionen
Typische Aggregatfuntionen sind
• COUNT(*), COUNT(spalte) oder COUNT(DISTINCT spalte):
Anzahl von Datensätzen
• SUM(spalte): Summe von Spaltenwerten
• AVG(spalte): Durchschnitt von Spaltenwerten
• Weitere MySQL-Funktionen für Datumswerte/Zeichenketten oder
Zahlen
SQL-Programmierung
SQL-Programmierung
Allgemeine Syntax
Variablen
• Die Abfrage-/SELECT-Anweisung folgt dem ASSchlüsselwort
Deklarieren einer Variablen mit Datentyp und optionalem Standardwert:
• DEFINER legt fest, ob ein bestimmter Benutzer oder
der aktuelle Benutzer der offizielle Ersteller ist.
• SQL SEQURITY legt fest, ob die Sicht im Sicherheitskontext des Erstellers oder des Aufrufers ausgeführt
werden soll.
• Aktualisierbare Sichten können mit WITH CHECK
OPTION so definiert werden, dass eingefügte Daten
auch einer möglichen WHERE-Klausel entsprechen
und immer auch durch die Sicht sichtbar sind.
CREATE [OR REPLACE]
[DEFINER = { benutzer| CURRENT_USER }]
[SQL SECURITY { DEFINER | INVOKER }]
VIEW sicht [(spaltenliste)]
AS select_anweisung
[WITH [CASCADED | LOCAL] CHECK OPTION]
ALTER
[DEFINER = { benutzer| CURRENT_USER }]
[SQL SECURITY { DEFINER | INVOKER }]
VIEW sicht [(spalte_liste)]
AS select_anweisung
[WITH [CASCADED | LOCAL] CHECK OPTION]
DROP VIEW [IF EXISTS] sicht [, sicht ] ...
Import
Importund
undExport
Export
Allgemeine Syntax
LOAD DATA [LOW_PRIORITY | CONCURRENT]
[LOCAL] INFILE ‘datei‘
[REPLACE | IGNORE]
INTO TABLE tabelle
[FIELDS
[TERMINATED BY ‘string‘]
[[OPTIONALLY] ENCLOSED BY ‘char‘]
[ESCAPED BY ‘char‘]
]
[LINES
[STARTING BY ‘string‘]
[TERMINATED BY ‘string‘]
]
[IGNORE anzahl LINES]
[(spalte_oder_variable,...)]
[SET spalte = ausdruck,...)]
DECLARE var_name[,...] typ [DEFAULT wert]
Initialisierung einer/mehrerer Variablen mit jeweils
einem Wert:
SET var_name = ausdruck [, var_name =
ausdruck] ...
Übernahme von Spaltenwerten in jeweils eine Variable:
SELECT spalte[,...] INTO var_name[,...]
tabelle_ausdruck
Fallunterscheidungen
Einfache Fallunterscheidung mit einem WENN-Zweig
und optional mehreren ODER-WENN-Zweigen und
einem optionalen SONST-Zweig:
IF such_bedingung THEN anweisungen
[ELSEIF such_bedingung THEN
anweisungen] ...
[ELSE anweisungen]
END IF
Schleifen
Einfache Schleife ohne integrierte Bedingung (typischerweise Verwendung von LEAVE):
[label:] LOOP
anweisungen
END LOOP [label]
Schleife, die solange durchgeführt wird, wie die Bedingung wahr ist. Einmalige Durchführung in jedem Fall,
da die Prüfung den Anweisungen folgt.
[label:] REPEAT
anweisungen
UNTIL such_bedingung
END REPEAT [label]
Schleife ähnlich REPEAT, aber Prüfung direkt zu Beginn,
sodas auch keinmalige Durchführung möglich ist.
[label:] WHILE such_bedingung DO
anweisungen
END WHILE [label]
Fortsetzen mit der nächsten Iteration der Schleife über
Nennung des Labels:
ITERATE label
Verlassen der Schleife über Nennung des Labels:
LEAVE label
MySQL – Kurzreferenz Teil 2: Abfragen und Programmierung
Transaktionen
Transaktionenund
undSperren
Sperren
Prozeduren
Prozedurenund
undFunktionen
Funktionen
Allgemeine Syntax
Prozeduren und Funktionen sind DB-Mini-Programme mit (optionalen) Übergabeparametern, die in SQL-Skripten oder auch von
externen Programmiersprachen verwendet werden können.
Prozeduren erstellt man für DML-Operationen oder administrative
Aufgaben. Sie haben ein- und ausgehende Übergabeparameter.
CREATE PROCEDURE
[ [ IN | OUT |
param_name
[merkmale ...]
sp_name (
INOUT ]
typ [,...]])
koerper
Funktionen erstellt man für die Verwendung in SQL-Anweisungen.
Sie haben Übergabeparameter und einen Rückgabewert.
CREATE FUNCTION sp_name (
[param_name typ [,...]])
RETURNS typ
[merkmale ...] koerper
Prozeduren und Funktionen können in SQL oder einer Host-Sprache erstellt werden. Liefern sie bei gleichen Eingabewerten
gleiche Ausgabewerte, sind sie deterministisch. Die Ausführung kann im Sicherheitskontext des Erstellers (DEFINER)
oder des Aufrufers (INVOKER) stattfinden.
merkmale:
LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA |
MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT ‚zeichenkette‘
Einfügen
Standardbefehl für das Einfügen von Daten inkl. optionaler Behandlung bei Schlüsselkonflikt.
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY]
[IGNORE]
[INTO] tabelle [(spalte,...)]
VALUES ({ausdruck | DEFAULT},...),(...),...
[ ON DUPLICATE KEY UPDATE spalte=ausdruck, ... ]
Variante mit Einzelzuweisung von Werten zu Spalten.
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY]
[IGNORE]
[INTO] tabelle
SET spalte={ausdruck | DEFAULT}, ...
[ ON DUPLICATE KEY UPDATE spalte=ausdruck, ... ]
Einfügen von Daten aus einer Abfrage (häufig bei Import-Szenarien).
INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
[INTO] tabelle [(spalte,...)]
SELECT ...
[ ON DUPLICATE KEY UPDATE spalte=ausdruck, ... ]
Aktualisieren
UPDATE [LOW_PRIORITY] [IGNORE] tabelle
SET spalte1=ausdruck1 [, spalte2=ausdruck2 ...]
[WHERE filter]
[ORDER BY ...]
[LIMIT anzahl]
UPDATE [LOW_PRIORITY] [IGNORE] tabelle_verweise
SET spalte1=ausdruck1 [, spalte2=ausdruck2 ...]
[WHERE filter]
Zusammengestellt von Marco Skulschus
Layout und Satz: Nadine Kilian
© 2012 Comelio Medien
Setzen von globalen Transaktionseigenschaften:
• READ UNCOMMITTED: Lesen von unbestätigten Daten
Änderungen über eine Neu-Definition mit ALTER statt CREATE.
• READ COMMITTED: Nur Lesen von bestätigten Daten
Die Anweisungen stehen innerhalb von BEGIN/END.
• REPEATABLE READ: Wiederholbares Lesen bzw. weiterhin
Sperrung von gelesenen Daten
ALTER {PROCEDURE | FUNCTION} sp_name [merkmale ...]
[label:] BEGIN
[anweisungen]
END [label]
• SERIALIZABLE: Serialisierung von Operationen und damit
exklusive Durchführung
Prozeduren/Funktionen werden mit DROP gelöscht.
DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name
Der Aufruf einer Prozedur erfolgt über CALL.
CALL sp_name([parameter[,...]])
SET [GLOBAL | SESSION] TRANSACTION ISOLATION
LEVEL
{ READ UNCOMMITTED | READ COMMITTED | REPEATABLE
READ | SERIALIZABLE }
Beginn einer Transaktion:
START TRANSACTION | BEGIN [WORK]
Bestätigen und Zurücksetzen einer Transaktion:
Ersetzen
Mit REPLACE kann man Daten, die anhand des Primärschlüsselwerts
zugeordnet werden, ersetzen.
Datenmanipulation
Datenmanipulation
Transaktionen
Mit Transaktionen fasst man mehrere Einzel-Operationen
zu einer Operation zusammen, die entweder komplett
durchgeführt und bestätigt oder aufgrund eines Fehlerfalls
zurückgesetzt wird.
Standardbefehl für das Einfügen von Daten.
REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tabelle [(spalte,...)]
VALUES ({ausdruck | DEFAULT},...),(...),...
Variante mit Einzelzuweisung von Werten zu Spalten.
REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tabelle
SET spalte={ausdruck | DEFAULT}, ...
Ersetzen von Daten aus einer Abfrage.
REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tabelle [(spalte,...)]
SELECT ...
Löschen
Standard-Variante des Löschens mit optionalem Filter.
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tabelle
[WHERE filter]
[ORDER BY ...]
[LIMIT anzahl]
Löschen von Datensätzen aus mehreren Tabellen, die verbunden sind.
DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
tabelle[.*] [, tabelle[.*]] ...
FROM tabelle_verweise
[WHERE filter]
Alternative Formulierung mit USING zur Formulierung des Verbundes.
DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
FROM tabelle[.*] [, tabelle[.*]] ...
[WHERE filter]
Vollständig löschen
Vollständige, schnelle Leerung der Tabelle ohne Filtermöglichkeit.
TRUNCATE [TABLE] tabelle
Comelio GmbH
Goethestr. 34, 13086 Berlin
Web: www.comelio.com
COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
Auto-Bestätigungsmodus für einzelne Operationen ein-/
ausschalten:
SET AUTOCOMMIT = {0 | 1}
Sicherungspunkte
Mit Sicherungspunkten kann man Zwischenstationen einer
umfangreichen Transaktion setzen und zu ihnen wieder
zurückkehren, ohne sofort zum Anfang der gesamten Transaktion zurückspringen zu müssen.
Sicherungspunkt setzen:
SAVEPOINT bezeichner
Zurückkehren zu einem Sicherungspunkt (teilweises Rollback):
ROLLBACK [WORK] TO SAVEPOINT bezeichner
Löschen eines Sicherungspunktes:
RELEASE SAVEPOINT bezeichner
Tabellen sperren
Tabellen können vorab für Lese-/Schreib-Operationen gesperrt und nach der Durchführung wieder entsperrt werden.
MySQL-Funktionen
MySQL -Funktionen
MySQL besitzt viele nützliche Funktionen. Auswahl:
Zeichenketten
CONCAT_WS(separator, str1, str2,...) Zeichen-
ketten verbinden mit Trennzeichen
CONCAT(str1, str2,...) Zeichenketten verbinden
LOWER(str) Zeichenkette in Kleinbuchstaben
UPPER(str) Zeichenkette in Großbuchstaben
LPAD(str, len, padstr) Links mit einem Füllzeichen padstr len-mal füllen
LPAD(str, len, padstr) Rechts mit einem Füllzeichen padstr len-mal füllen
LTRIM(str) Links Leerzeichen abschneiden
RTRIM(str) Rechts Leerzeichen abschneiden
TRIM([{BOTH | LEADING | TRAILING} [remstr]
FROM] str) oder TRIM([remstr FROM] str) Leer-
zeichen von links und/oder rechts abschneiden
SUBSTR(str, pos), SUBSTR(str FROM pos),
SUBSTR(str,pos,len) oder SUBSTR(str FROM pos
FOR len) Den Teil einer Zeichenkette ab Beginn (pos)
für eine Länge (len) ausschneiden
Zeit
CURDATE() Aktuelles Datum
CURTIME([fsp]) Aktuelle Zeit im Format HH:MM:SS
oder HHMMSS.uuuuuu mit fsp als Sekundenbruchteile
0 bis 6
NOW() liefert den Zeitstempel
DATE(expr) Liefert den Datumsanteil eines Datetime-
Werts
EXTRACT(unit FROM date) Liefert die Einheit (unit)
eines Date-Wertes. Mögliche Werte für unit sind MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR u.v.m.
Datentypen
CAST(expr AS type) oder CONVERT(expr,type)
Wandelt einen Ausdruck in einen anderen Datentyp um
LOCK TABLES
tabelle [AS alias] {READ [LOCAL] | [LOW_
PRIORITY] WRITE}
[, tabelle [AS alias] {READ [LOCAL] | [LOW_
PRIORITY] WRITE}] ...
UNLOCK TABLES
Trigger
Trigger
Trigger sind durch ein Ereignis ausgelöste Mini-Programme,
und für die Datenkonsistenz und DB-Integrität zuständig. Ein
Trigger-Ereignis sind DML-Operationen wie INSERT, UPDATE,
DELETE oder auch REPLACE und LOAD DATA. Ein Trigger kann
vor oder nach der DML-Operation ausgeführt werden. Typische Anweisungen sind ähnlich wie bei Prozeduren.
Terrashop GmbH
Lise-Meitner-Str. 8, 53332 Bornheim
Web: www.terrashop.de
CREATE
[DEFINER = { benutzer | CURRENT_USER }]
TRIGGER name [BEFORE | AFTER] trigger_ereignis
ON tabelle FOR EACH ROW trigger_anweisungen
Löschen eines Triggers:
DROP TRIGGER [schema_name.]trigger_name
ISBN 978-3-939701-74-3
9 783939 701743