- Einführung
-
SELECT id, name /* Großschreibung ist üblich; Befehle untereinander schreiben macht diese lesbarer */
FROM tabelle
WHERE id = 1;
- Kommentar
- /* Inline Kommentar, auch innerhalb von Befehlen */
# Weiterer Kommentar
- Konstanten
-
SELECT 9/4 AS n; # Spalte n mit Wert 2.25
SELECT 9 DIV 4; # 2
SELECT 9 MOD 4; # Rest 1
- Rechnen
- SELECT wert * 2 as w FROM tab;
SELECT (wert + zahl) / zahl FROM tab;
- Variablen
-
SET @var = 6; # entweder mit SET oder mit INTO werden Variablen belegt
SELECT 3+3 INTO @var; # nur ein Wert darf enthalten sein
SELECT @var/3; # 2
- Aliasnamen
- SELECT name AS n FROM tab; # auf n kann man innerhalb des Ausdrucks Bezug nehmen
SELECT name n FROM tab; # oder ohne AS
- Zufall
-
SELECT RAND (); # 0.235435435
SELECT FLOOR (1+ RAND() * (10-1)); # 7
- WHERE Klausel
-
SELECT * FROM tab WHERE (id >= 3 AND id < = 5) OR id = 10;
SELECT * FROM tab WHERE id IN (3,4,5,10); # oder NOT IN
SELECT * FROM tab WHERE id BETWEEN 1 AND 6; # oder NOT BETWEEN; Zahlen werden mit eingeschlossen
SELECT * FROM tab WHERE id IS NULL # oder IS NOT NULL
- LIKE Muster
-
SELECT * FROM tab WHERE name LIKE '%Peter%'; # Vor und nach den Ausdruck kann etwas stehen
SELECT * FROM tab WHERE name LIKE 'P_ter'; # Peter wird gefunden
- Sortierung
- SELECT * FROM tab ORDER BY name DESC, datum ASC; # name absteigend, datum aufsteigend; Sortierung durch Indizes beschleunigt
- Limit
- SELECT name FROM tab LIMIT 0,1; # ab den ersten Datensatz, wird ein Datensatz ausgegeben; entspricht LIMIT 1
- Mehrfachausgabe unterbinden
- SELECT DISTINCT name, vorname FROM tab; # Eine Kombination aus gleichem name und vorname wird nur einmal ausgegeben.
- Muster mit regulären Ausdrücken
- SELECT * FROM tab WHERE name REGEXP "^b"; # Übereinstimmung am Anfang
SELECT * FROM tab WHERE name REGEXP "^[bB]"; # b oder B
SELECT * FROM tab WHERE name REGEXP "fy$"; # auf fy endend
SELECT * FROM tab WHERE name REGEXP "^.....$"; # genau 5 Zeichen
SELECT * FROM tab WHERE name REGEXP "[0-9]*"; # mehere Ziffern
MySQL Handbuch
- Zwei Tabellen verknüpfen
-
SELECT * FROM tab INNER JOIN tab2 ON tab.id = tab2.id; # Verbunden, wenn 2 ids übereinstimmen
SELECT * FROM tab, tab2 WHERE tab.id = tab2.id; # andere Schreibweise
SELECT * FROM tab INNER JOIN tab2 USING (id); # falls in beiden Tabellen die Verbindungsspalte gleich bezeichnet ist
SELECT * FROM tab INNER JOIN tab2 USING (id) WHERE id > 10; # zusätzicher Bedingung
SELECT x.name n1, y.name n2 FROM tab x INNER JOIN tab2 y USING (id); # Tabellenaliase und Spaltenaliase verwendet
- Drei Tabellen verknüpfen
-
SELECT * FROM (tab INNER JOIN tab2 USING (id)) INNER JOIN tab3 USING (id); # aus dem Ausdruck in Klammern entsteht neue Realtion; Schreibweise auch ohne Klammern
- Outer Joinn
-
SELECT * FROM tab LEFT OUTER JOIN tab2 USING (id); # Alle Datensätze von tab werden aufgeführt; exestiert der zugerhörige Datensatz in tab2 nicht, sind Feler NULL
SELECT * FROM tab RIGHT OUTER JOIN tab2 USING (id); # Alle Datensätze von tab2
- Self join
-
SELECT * FROM tab INNER JOIN tab ON id = bezug_id; # Fremdschlüssel und Primärschlüssel in einer Tabelle
- Mengenoperation UNION
- SELECT id,name FROM tab UNION SELECT id,name FROM tab2; # Vereinigung; doppelt vorkommende Werte werden nur einmal angezeigt
SELECT S1 FROM tab UNION ALL SELECT S1 FROM `tabelle2`; # doppelt vorkommende Werte, werden mehrmal angezeigt
- Durchschnitt
-
SELECT AVG(spalte) AS durchschnitt FROM tab; # 22.555685; zwischen Funktion und Klammer kein Leerzeichen
- Anzahl
- SELECT COUNT(*) FROM tab;
SELECT COUNT(spalte) FROM tab; # Alles wird gezählt, ausser NULL
SELECT COUNT(DISTINCT(spalte) FROM tab; # Alle unterschiedlichen gezählt
- Summe
-
SELECT SUM(spalte) FROM tab; # summiert
- Maximum, Minimum
-
SELECT MAX(spalte) FROM tab;
SELECT MIN(spalte) FROM tab;
- Absolute Werte
-
SELECT ABS(spalte) FROM tab; # -5 wird 5
- Standartabweichung, Varianz
-
SELECT STDDEV(spalte) FROM tab; # 2.255;
SELECT VARIANCE(spalte) FROM tab; # 5.5556;
- Runden
-
SELECT CEIL(spalte) FROM tab; # aufgerundet
SELECT FLOOR(spalte) FROM tab; # abgerundet
- Gruppierung
- SELECT land, COUNT(*) AS anzahl FROM tab GROUP BY land; # Menschen aus gleichem Land werden zusammengefasst und gezählt
SELECT land, COUNT(*) AS anzahl FROM tab GROUP BY land HAVING land != 'D'; # Alles außer Land D, wird gruppiert
String
SELECT CONTACT(splate1, spalte2) aliasname FROM tabelle; # verbindet
SELECT LOWER(spalte) FROM tabelle; # Kleinbuchstaben
SELECT UPPER(spalte) FROM tabelle; # Großbuchstaben
SELECT LENGTH(spalte) FROM tabelle; # Stringlänge
Replace
UPDATE table SET spalte = REPLACE(spalte, 'oe', 'ö'); # oe wird durch ö ersetzt
SELECT REPLACE(spalte, "oe", "ö") AS spale FROM tabelle # Alternative
- Skalare Unterabfrage
-
SELECT * FROM tab WHERE wert = (SELECT MAX(wert) FROM tab); # Unterabfrage enthält eine Spalte und eine Zeile, also ein Wert
- IN Listenunterabrage
-
SELECT * FROM tab WHERE id IN (SELECT id FROM tab2); # Datensätze, welche tab2 auch enthält; verneint durch NOT IN
- ALL Listenunterabrage
-
SELECT * FROM tab WHERE wert ALL > (SELECT wert FROM tab2); # wert muss größer sein, als alle Werte in tab2
- ANY Listenunterabrage
-
SELECT * FROM tab WHERE wert ANY > (SELECT wert FROM tab2); # wert muss größer sein, als ein Werte in tab2
- Datenbank erstellen
-
CREATE DATABASE db_1 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; # oder CREATE SCHEMATA; KOLLATION gibt Zeichensatz für Sortierung an
USE db1; # um die Tabelle zu verwenden
- Datenbank löschen
- DROP SCHEMA db_1;
- Tabellen anzeigen
- SHOW TABLES;
- Tabelle erzeugen
- CREATE TABLE tab (
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, /* nur positive Zahlen; es wird automatisch hochgezählt */
name VARCHAR (50) NOT NULL DEFAULT 'x', /* Werteberich +/- 2 Milliarden; erzwingt Wertangabe, wird keiner angegeben dann x */
text VARCHAR (255) NULL, /* 255 Zeichen sind bei varchar möglich; Werte dürfen leer bleiben */
text_2 TEXT , /* lange Texte */
text_3 VARCHAR (100) BINARY, /* durch BINARY wird zwischen Groß und Kleinschreibung unterschieden, ansonsten nicht */
zahl INT UNIQUE, /* Wertebereich: -2147483648 bis +2147483647; Schlüsseleigenschaft erzwungen */
zahl_2 SMALLINT, /* Wertebereich -32768 bis +32767 */
preis DOUBLE (4,2), /* Gesamtlänge, Nachkommastellen; hier bis 99.99 */
datum DATE, /* YYYY-MM-DD */
zeit TIME, /* HH:MM:SS */
kunden INT REFERENCES kunden(id), /* Fremdschlüssel: referentieller Integrität erzwingt Vorhandensein von ids in kunde; bei InnoDB z.B. kaskierende Auswirkungen */
auswahl ENUM ('1','2','3') DEFAULT 1, /* nur 1,2 oder 3 sind möglich */
deleted TINYINT /* Wertebereich: -128 bis 127 */
) ENGINE = MyISAM ; /* Storage Engines, InnoDB andere sehr gute Engine oder durch Memory im RAM */
- Tabelle temporär erzeugen
- CREATE TEMPORARY TABLE tab_temp ( id INT ); /* Existenz nur solange die Verbindung exestiert */
- Tabelle kopieren
- CREATE TABLE tab_neu LIKE tab; # nur Strukturdaten werden kopiert
- Spalten verändern
- ALTER TABLE tab ADD name char (40) AFTER datum; # Fügt eine weiteres Feld hinzu
ALTER TABLE tab MODIFY name int; # Ändert den Typ einer Spalte
ALTER TABLE tab RENAME TO tab_2; # Ändert den Namen einer Tabelle
- Tabelle löschen
- DROP TABLE tab;
- Indizes anzeigen
- SHOW INDEX FROM tab;
- Indizes erstellen
- CREATE INDEX in_1 on tab (id,name(30)); # name ist auf 30 Zeichen begrenzt; bei vielen Veränderung, könnte man den Index kurzzeitig abschalten
CREATE UNIQUE INDEX in_2 on tab (id,name); # erzwingt Eindeutigkeit
- Indizes löschen
- DROP INDEX in_1 ON tab;
- Datensätze hinzufügen
- INSERT INTO tab (id, name, alter) VALUES (NULL, 'Hans', 38), VALUES (NULL, 'Peter', 32); # 2 Datensätze werden eingetragen; bei den Values normale Anführungszeichen
INSERT INTO tab VALUES (NULL, 'Peter', 32); # wenn die Reihenfolge den der Spalten der Tabelle entspricht
- Datensätze verändern
- UPDATE tab SET name='Olga' WHERE id = 2; # Felder verändern
UPDATE tab SET zeit = ROUND (zeit,2), alter=alter*1.15; # mit Tabellenfeld rechnen
- Datensätze kopieren
- INSERT INTO tab_neu (id, name) SELECT id,name FROM tab; # Die Tabelle tab_neu muss exestieren
- Datensätze löschen
- DELETE FROM tab WHERE id = 2;
- Tabelle leeren
- TRUNCATE tab;
- Datensätze importieren
- LOAD DATA LOCAL INFILE 'daten.csv' INTO TABLE tab FIELDS TERMINATED BY ';' LINES TERMINATED BY '\n' IGNORE 1 LINES (id,name) SET ort='x';
# Fehlender Spalte ort wird x zugewiesen; durch REPLACE oder IGNORE können Werte mit gleichen Schlüssel ersetzt werden oder nicht
- Verbindung mit mySQL und einer Datenbank
- $myc = mysql_connect ("localhost", "name", "pass") or die ("keine Verbindung zu SQL");
mysql_SELECT_db ("dbname") or die ("Keine Verbindung zur Datenbank");
- Zugreifen auf Datensätze
- $mye = mysql_query ("SELECT * FROM tab" or die (mysql_error());
while ($d = mysql_fetch_array ($mye))
{ echo $d['name']; }
- Zugreifen auf einen Datensatz
- $mye = mysql_query ("SELECT * FROM tab WHERE id=5");
$x = mysql_result ($mye,0,'spalte');
- Anzahl Datensätze
- mysql_num_rows ($mye); # Anzahl zurückgegebener Datensätze
- Hochgezählte ID
- mysql_query ("INSERT INTO tab (spalte) values ('$wert')");
$id_insert = mysql_insert_id();
- Anzahl veränderter Datensätzen
- mysql_affected_rows; # Anzahl nach update veränderte Datensätze
- mySQL Verbindung schliessen
-
mysql_close ($myc); # Performance
- Verbindung
- $mysqli = new mysqli ("localhost", "name", "pass", "dbname"); # Verbindung zu mySQL
if ($mysqli->connect_error) { echo "Fehler: " . mysqli_connect_error(); exit(); } # pfüft auf Fehler
- Datensätze zugreifen
-
$mye = $mysqli->query ("SELECT name FROM pflanzen;"); # query Methode
while($zeile = $ergebnis->fetch_array())
{ echo "<tr><td>" . htmlspecialchars($zeile["name"]) . "</td> </tr>\n"; }
- Fehlermeldung und Fehlernummer
- $mye = $mysqli->query ("SELECT name FROM pflanzen;");
if (!$mye) echo "Fehler : " . $mysqli->error . " - Fehlernummer: " . $mysqli->errno;
- Einige Eingenschaften
- $spaltenzahl = $mye->field_count; # Anzahl Spalten
$zeilenzahl = $mye->num_rows; # Anzahl zurückgegebener Datensätze
$betroffen = $mysqli->affected_rows; # Anzahl veränderter Datensätze
- MySQLi Information zu Spalten
- $sinf = $ergebnis->fetch_fields();
foreach($sinf as $inf) {
echo "Name: " . $inf->name . "-"; # Name Spalte
echo "Tabelle: " . $inf->table . "-"; # Name Tabelle
echo "Längste Zeichenkette: " . $inf->max_length; } # Längste Zeichenkette
- Zeichensatz
-
if (!$mysqli->set_charset ("utf8")) { echo "Fehler". $mysqli->error; } # Ruft Methode für Zeichensatz auf
- Sonderzeichen umwandlen
-
$name = $mysqli->real_escape_string ("O'Brien"); // O/'Brien - Schützt vor SQL Injections
$insert = "INSERT INTO pfanzen (name) VALUES ('$name')";
- Verbindung schliessen
- $mye->close();
$mysqli->close();
Impressum: Gero Zickermann, Haasestr. 3, 10245 Berlin