MySQL Befehle

Neuen Datensatz anlegen

INSERT INTO Tabelle (Jahr, Monat, Tag) VALUES (2010,10,20)

INSERT und UPDATE kombinieren

Mysql kann entscheiden, ob INSERT oder UPDATE ausgeführt wird.

INSERT INTO books
   (id, title, author, year_published)
VALUES
   (123, 'Stories', 'myAuthor', '2000')
ON DUPLICATE KEY UPDATE
   title = 'New Stories',
   year_published = '2010';

• Siehe auch folgendes Howto

Datensätze duplizieren

Wenn für den Primärschlüssel autoincrement definiert ist, muss man diese Spalte in der temporären Tabelle löschen.

CREATE TEMPORARY TABLE tmp SELECT * FROM Noten WHERE Sammelheft_Id = 75;
UPDATE tmp SET Sammelheft_Id = 77;
ALTER TABLE tmp DROP Id;
ALTER TABLE tmp ADD Id INT(5) FIRST;
INSERT INTO Noten SELECT * FROM tmp;

Spalten

Mehrere Spalten ändern mit Formel

Datum von Text in das Mysql Format umwandeln

UPDATE chor 
SET Eintritt = STR_TO_DATE(Eintritt,'%d.%m.%Y'), 
Austritt = STR_TO_DATE(Austritt,'%d.%m.%Y')

Anschließend den Datentyp der betreffenden Spalten auf DATE ändern.

UPDATE Beispiele

UPDATE tabellenname SET feldname = Wert WHERE ...

Update Noten
SET Sammelheft_Id = 51
WHERE `Anmerkung` LIKE '%German%'

UPDATE Noten AS n
LEFT JOIN Sammelhefte AS s 
ON n.Sammelheft_Id = s.Id
SET n.Komponist_Id = NULL
WHERE s.Komponist_Id > 0

UPDATE Werke 
INNER JOIN Hefte ON Hefte.Id = Werke.Heft_Id
SET Werke.Schlagw = "" 
WHERE LENGTH (Hefte.Schlagw) > 0

UPDATE Programme 
INNER JOIN Ensembles ON Ensemble_Id = Ensembles.Id
SET Ensemble = NameEnsemble
WHERE Ensemble_Id IS NOT NULL

Achtung: Die Reihenfolge ist wichtig: UPDATE - JOIN - SET - WHERE

Zum Schluss kommt die WHERE Klausel.

Update mit SubQuery

Mit einer Unterabfrage kann man berechnete Werte zum Update verwenden.

UPDATE Hefte 
SET AnzWerke = (
    SELECT COUNT(Id) FROM Werke 
    WHERE Hefte.Id = Heft_Id
)

Info zu Subqueries

Spalte kopieren

update tabellenname SET Spalte2 = Spalte1

Reihenfolge der Spalten ändern

ALTER TABLE gerichte MODIFY COLUMN Datum date AFTER Sort

AUTO_INCREMENT zurücksetzen

ALTER TABLE tablename AUTO_INCREMENT = 1

Doppelte Datensätze finden

SELECT Zuname, Vorname, count(*)
FROM Interpreten
GROUP BY Zuname, Vorname
HAVING count(*) >1

Datensätze mit unterschiedlichen Werten

SELECT Id, Werk, Besetzung, Anz FROM `Werke` 
GROUP BY Besetzung
ORDER BY Besetzung

GROUP BY statt SELECT DISTINCT ermöglicht eindeutige Werte in nur 1 Spalte

Joins

SELECT `Vorname`,`Zuname`, instrumente.Bezeichnung 
     FROM chor, instrumente
     WHERE Instr_Id = instrumente.Id

SELECT i.Vorname, i.Zuname, i.Id, i.Sortierung, a.Konzert_Id
     FROM Interpreten i
     LEFT JOIN Auftritte a
       ON i.Id = a.Interpret_Id
       AND a.Konzert_Id = 9
     ORDER BY Sortierung, Zuname

SELECT  Count(a.Interpret_Id) As Anzahl, 
             Vorname, Zuname, i.Id AS Id, Sortierung, Maedchenname, Namensaenderung, 
             YEAR(i.Namensaenderung) AS Jahr, MONTH(i.Namensaenderung) AS Monat 
     FROM Auftritte a
     RIGHT JOIN Interpreten i ON i.Id = a.Interpret_Id
     GROUP BY Id

SELECT Konzerte.Datum, Ensembles.NameEnsemble 
     FROM Programme
     JOIN Konzerte ON Konzert_Id = Konzerte.Id
     JOIN Ensembles ON Ensemble_Id = Ensembles.Id
     WHERE Ensemble_Id = 4 AND Datum <= NOW()
     GROUP BY Konzert_Id

Funktionen

Datum

• Jahr, Monat, Tag ausgeben

SELECT Datum, Thema, Bemerkungen, Id, 
      DAYOFMONTH(Datum) AS Tag, MONTH(Datum) AS Monat, YEAR(Datum) AS Jahr FROM Konzerte
      WHERE YEAR(Datum) >= 2000 AND YEAR(Datum) <= 1998
      ORDER BY Datum DESC;

• Deutsches Datum abfragen

SELECT Datum, DATE_FORMAT (Datum,'%d.%m.%y') AS dateGerman
FROM gerichte

• Datum ändern um eine Woche

UPDATE gerichte set Datum = date_add(Datum, INTERVAL 7 DAY)

• Datum vergleichen

Wenn die Spalte Datum keine vollständige Zeitangabe im Mysql Format enthält, sondern nur ein Datum:

SELECT Datum WHERE Datum < DATE(NOW())

• Alte Datensätze löschen

DELETE FROM `tbl_token_auth` WHERE expiry_date < SUBDATE(CURDATE(), INTERVAL 1 YEAR)

Rechte

• Einem Benutzer alle Rechte einer Datenbank verleihen

GRANT ALL PRIVILEGES ON databaseName . * TO 'frecsiba'@'localhost';

Sichern und wiederherstellen

• Sichern einer Datenbank

mysqldump -h localhost -u root -p --databases db1 --result-file db1.sql

• Kopieren einer Datenbank

mysqldump SOURCE_DB > dump_db.sql
create database TARGET_DB;
mysql TARGET_DB < dump_db.sql

Eventuell ist es notwendig, am Anfang des Mysqldump dump_db.sql einige Zeilen zu entfernen, die sich auf die Quelldatenbank beziehen.

Besonderheiten

• Ein Alias kann nicht in einer WHERE Klausel stehen

Folgende Abfrage funktioniert NICHT:

SELECT MONTH(Datum) AS Monat FROM Konzerte WHERE Monat = 08;

WHERE ersetzen durch HAVING:

SELECT MONTH(Datum) AS Monat FROM Konzerte
HAVING Monat = 08;

PHP

Datum umwandeln

Mysql Datum vergleichen mit aktuellem Datum

Die PHP Funktion strtotime() wandelt das Datumsformat von MySql in einen timestamp um

if ( strtotime($row["MySqlDatum"]) > time() ) ...