MySQL Kurzreferenz – Die wichtigsten Kommandos

MySQL Kurzreferenz
www.brielbeck.de
MySQL Kurzreferenz – Die wichtigsten Kommandos
Datenbank erstellen
CREATE DATABASE name
Tabellen erstellen
CREATE TABLE name (spalte1 datentyp [constraints1],spalte2 datentyp [constraints1],[constraints2])
Constraints [constraints1]
PRIMARY KEY, NOT NULL, UNIQUE, DEFAULT
Aktuelles Datum als Default: Spalte1 TIMESTAMP DEFAULT CURRENT_TIMESTAMP()
Constraints [constraints2]
FOREIGN KEY (spalte) REFERENCES tabelle(spalte) [ON DELETE/UPDATE/CASCADE/SET NULL]
PRIMARY KEY (spalte1,spalte2,spalte3)
Datentypen
TEXT, CHAR(Länge), INT, FLOAT [(Stellen gesamt, Nachkomma)], DATE,ENUM[‘wert1‘,‘wert2‘]
Datenbanken anzeigen
SHOW DATABASES
Datenbank auswählen
USE name
Tabellen anzeigen
SHOW TABLES
Tabelle löschen
DROP TABLE name
Tabellen-„Quelltext“ anzeigen
SHOW CREATE TABLE name
Tabellenfelder mit Infos anzeigen
DESCRIBE name
Spaltenname / Datentyp ändern
ALTER TABLE name CHANGE spalte_alt spalte_neu datentyp_neu
Fremdschlüssel entfernen
1. SHOW CREATE TABLE name Name des Fremdschlüssels suchen z.B. Mitarbeiter_ibfk_1
2. DROP FOREIGN KEY Mitarbeiter_ibfk_1
Tabelle Spalten hinzufügen
ALTER TABLE tab_name ADD spalte datentyp
ALTER TABLE tab_name ADD constraint
ALTER TABLE tab_name DROP spalte/constraint
Stand: 13.04.2010
Seite | 1
MySQL Kurzreferenz
www.brielbeck.de
Datensatz hinzufügen
INSERT INTO name VALUES (wert1,wert2,wert3)
INSERT INTO name (spalte1,spalte2,spalte3) VALUES (wert1,wert2,wert3)
INSERT INTO name VALUES (wert1a,wert2a,wert3a), (wert1b,wert2b,wert3b),…
Datensatz ändern
UPDATE name SET spalte1=wert1 [WHERE bedingung]
UPDATE name SET spalte1=wert1, spalte2=wert2,spalte3=wert3 [WHERE bedingung]
Bedingungen
=, !=, >, <, LIKE, BETWEEN
z.B.: WHERE name=‘Maier‘
Platzhalter bei LIKE
% für beliebig viele Zeichen, z.B. WHERE name LIKE ‘M%‘
_ für ein Zeichen, z.B WHERE name LIKE ‘M_ier‘
BETWEEN
z.B.: WHERE geb_dat BETWEEN ‘1980-01-01‘ AND ‘1990-01-01‘
Liefert das selbe Ergebnis wie:
WHERE geb_dat > ‘1980-01-01‘ AND geb_dat < ‘1990-01-01‘
Datensatz löschen
DELETE FROM name [WHERE bedingung]
Spezielle Bedingung
spalte is [not] null
SELECT-Abfragen (Grundgerüst)
SELECT spalte1,spalte2 FROM tab_name [WHERE bedingung]
SELECT * FROM tab_name [WHERE bedingung]
Kombinierte Bedingungen
SELECT * FROM tab_name WHERE spalte1=wert1 AND spalte2=wert2
SELECT * FROM tab_name WHERE spalte1=wert1 OR spalte1=wert2
SELECT * FROM tab_name WHERE spalte1=wert1 AND (spalte2=wert2 OR spalte2=wert3)
Ausgabe sortieren
SELECT * FROM tab_name ORDER BY spalte1 aufsteigend
SELECT * FROM tab_name ORDER BY spalte1 ASC aufsteigend
SELECT * FROM tab_name ORDER BY spalte1 DESC absteigend
Einfache Berechnungen
SELECT spalte1 +1000 AS spalte_neu,FROM tab_name
z.B. SELECT netto*1,19 AS brutto FROM preise
Operatoren: +, -, *, /
Stand: 13.04.2010
Seite | 2
MySQL Kurzreferenz
www.brielbeck.de
Mengenfunktionen
COUNT(spalte), MIN(spalte), MAX(spalte), AVG(spalte), SUM(spalte)
SELECT COUNT(*) FROM tab_name
SELECT COUNT(*) AS spalte_neu FROM tab_name
z.B.: SELECT COUNT(*) AS AnzahlMitarbeiter FROM mitarbeiter
Gruppierungen
SELECT spalte1,spalte2 FROM tab_name GROUP BY spalte1
SELECT * FROM tab_name [WHERE bedingung] GROUP BY spalte1 [HAVING gruppenbedingung]
z.B.: SELECT name FROM autos WHERE land=‘D‘ GROUP BY ort HAVING count(*)>10
Alle Autos aus Deutschland, gruppiert nach Ort. Aber nur mit mehr als 10 Autos.
CROSS JOIN
SELECT a.*,b.* FROM tab_name1 a, tab_name2 b
Jeder Datensatz aus Tabelle 1 wird mit jedem Datensatz aus Tabelle 2 verknüpft. Ohne Bedingung!
INNER JOIN
SELECT a.*,b.* FROM tab1 a INNER JOIN tab2 b ON a.spalte1=b.spalte3 (meist PS und FS!)
Nur Datensätze, welche die Bedingung erfüllen werden verknüpft.
z.B.: SELECT a.*, b.* FROM autos a INNER JOIN mitarbeiter b ON a.kennzeichen=b.kennzeichen
Gleichsetzungs-JOIN
SELECT a.*,b.* FROM tab1 a, tab2 b WHERE a.spalte1=b.spalte5
Nur Datensätze, die die WHERE-Bedinung erfüllen werden ausgegeben.
LEFT JOIN
SELECT a.*, b.* FROM tab1 a LEFT JOIN tab2 b ON a.spalte1 = b.spalte7
Alle Datensätze der tab1 werden ausgegeben, auch wenn sie nicht mit tab2 in Beziehung stehen.
RIGHT JOIN
Wie LeftJoin, nur andersherum.
FULL JOIN
SELECT … FROM tab1 a RIGHT JOIN tab2 b ON a.spalte1 = b.spalte7 UNION
SELECT … FROM tab1 a LEFT JOIN tab2 b ON a.spalte1 = b.spalte7
Wie Left- und Right-JOIN zusammen.
SELF JOIN
SELECT … FROM tab1 a INNER JOIN tab1 b ON a.spalte1 = b.spalte1
Inner-, Left-, Right-JOIN, der sich auf 2 mal die selbe Tabelle bezieht
MEHRFACH-JOINS
SELECT … FROM tabelle1 t1 INNER JOIN (tabelle t2,tabelle t3,…) ON (t1.ps = t2.ps AND t2.ps = t3.ps)
INDIZES
CREATE INDEX name ON tabelle (spalte [ASC,DESC][,spalten]);
DROP INDEX name ON tabelle;
SHOW INDEX FROM tabelle;
Stand: 13.04.2010
Seite | 3
MySQL Kurzreferenz
www.brielbeck.de
Datumsfunktionen
DATE_ADD(datum, INTERVAL zahl DAY/MONTH/YEAR) auch: DATE_SUB(…)
CURDATE() liefert aktuelles Datum
DAY(Spalte) / MONTH(Spalte) / YEAR(Spalte)
DAYNAME(Spalte) / MONTHNAME(Spalte)
DAYOFMONTH(Spalte) / DAYOFWEEK(Spalte) / DAYOFYEAR(Spalte)
DATE_FORMAT(datum/Spalte,GET_FORMAT(DATE,‘USA‘,‘EUR‘,‘ISO‘));
z.B.: SELECT * FROM Mitarbeiter WHERE DATE_ADD(Geb_Dat, INTERVAL 18 YEAR) > CURDATE();
Alle Mitarbeiter ausgaben, die noch nicht 18 Jahre alt sind.
STRING-Funktionen
CONCAT (Spalte/‘text‘,Spalte/‘text‘,…) Strings zusammenfügen
RIGHT(Spalte,Anzahl_Zeichen) genauso LEFT(…) Anzahl Zeichen von rechts/links ausgeben
SUBSTRING(Spalte,von_position[,bis_position]) Ausgabe der Zeichen von …bis
REPLACE(Spalte,‘alt‘,‘neu‘)
LOCATE(‚was‘,Spalte[,Suchbeginn]) liefert Anfangsposition zurück
INSERT(Spalte,Pos,wie_viel_ersetzen?,‘neu‘)
LENGTH(Spalte) liefert Anzahl Bytes!
z.B.: SELECT CONCAT(Nachname,‘_‘,RIGHT(Plz,3)) FROM Mitabreiter;
Ausgabe: Nachname_3letzteZiffernderPLZ (Bsp: Huber_123)
IF
IF(Bedingung,wahr_Wert,falsch_Wert)
z.B.:SELECT Name,IF(Gehalt<1000,‘wenig‘,‘viel‘) FROM Mitarbeiter;
VIEWS
CREATE VIEW name AS SELECT * FROM tabelle;
Kein ORDER BY, geschachteltes Select, Joins, temp. Tabellen
z.B.: CREATE VIEW v_nameohneadresse AS SELECT nachname,vorname FROM Mitarbeiter;
Benutzervariablen (existieren nur bis zum Abmelden)
SELECT @name:= …
Erstellen: SELECT @name:= SELECT MAX(spalte) FROM tabelle;
Benutzen: SELECT * FROM tabelle WHERE spalte=@name;
Benutzerverwaltung
CREATE USER ‘name‘@‘Anmelde-Rechner‘ [IDENTIFIED BY password]
GRANT rechte ON datenbank.tabelle TO ‘name‘@‘Anmelde-Rechner‘ [WITH GRANT OPTION option]
Rechte entfernen mit REVOKE
Rechte
SELECT, UPDATE, DELETE, INSERT, DROP, ALTER …
Anmelde-Rechner
alle Rechner: %; alle aus 192.168.0.0/16: 192.168.%; Nur lokal: localhost
Options
MAX_USER_CONNECTIONS zahl; MAX_QUERIES_PER_HOUR
z.B.: CREATE USER ‘hans‘@‘192.168.3.%‘ IDENTIFIED BY ‘geheim12345‘
GRANT SELECT,ALTER ON webseite.gästebuch TO ‘hans‘@‘192.168.3.%‘ IDENTIFIED BY
‘geheim12345‘ WITH GRANT OPTION MAX_USER_SONNECTIONS 2;
Stand: 13.04.2010
Seite | 4