[MySQL] Einigen Code zu MySQL

[Joopi]

MySQL - wichtige Stringfunktionen

CHAR_LENGTH(str ): Gibt die Länge eines Strings zurück. Sehr interessant um zu wissen wie lange eine Zeichenkette ist. Diese Funktion zählt die Zeichen, nicht die Bytes.

Code:

SELECT CHAR_LENGTH( "Hallo" );

ergibt also:

5

LEFT(str,len): Gibt die ersten len Buchstaben eines Strings der linken Seite zurück.

Code:

SELECT LEFT( "das ist ein Test", 9 );

würde demzufolge "das ist e" zurückgeben.

RIGHT(str,len): Gibt dementsprechend die rechte Seite eines Strings zurück.

Code:

SELECT RIGHT("Das ist ein TEST",10);

ist dann "t ein TEST".

MySQL - verbinden zweiter Tabellen per JOIN

Sehr häufig müssen 2 Tabellen miteinander verbunden werden. Denn oft stehen Tabellen mit einander in Verbindung. Zum Beispiel beinhaltet eine Tabelle mit Kundendaten in einer Spalte das Bundesland in dem der Kunde wohnt. Natürlich könnte man jetzt immer dort "Hessen" eintragen. Geschickter ist es aber in dieser Spalte nur eine Nummer zu hinterlegen, die in der Tabelle "Bundesländer" dann mit der id von Hessen verknüpft ist.

Viele werden jetzt sagen: "Hessen ist doch schnell getrippt, warum Tabellen verbinden". Das schöne ist, man kann komplette Datensätze miteinander verbinden, ohne alle Angaben nochmal eintragen zu müssen. Zum Beispiel braucht man dann in einer Tabelle "Rechnungen" nicht mehr alle Kundeninformationen speichern wie z.B. Name, Strasse, plz, ort. Sondern man speichert nur noch die Zahl oder id des Kunden aus der "Kundentabelle". Man spricht hier von Entitäten.

Eingie Varianten sind LEFT JOIN, RIGHT JOIN, oder INNER JOIN. Hier mal 3 Beispiele:

Code:

SELECT * FROM kunde
INNER JOIN rechnung ON kunde.id=rechnung.rech_id;

Bei einem INNER JOIN werden zum Beispiel nur die Daten angezeigt, die in beiden Tabellen übereinstimmen. Also werden in diesem Fall nur Kunden mit einer Rechnung angezeigt.

Code:

SELECT * FROM kunde
LEFT JOIN rechnung ON kunde.id=rechnung.rech_id;

Bei einem LEFT JOIN werden zum Beispiel alle Daten der Linken Tabelle des Joins angezeigt, und wenn in der Rechten Tabelle keine übereinstimmenden Daten vorhanden sind, bleiben diese Spalten dann eben leer. Also werden in diesem Fall alle Kunden angezeigt, ob Sie eine rechnunge haben oder nicht.

Code:

SELECT * FROM kunde
RIGHT JOIN rechnung ON kunde.id=rechnung.rech_id;

Dieses Beispiel macht eigentlich keinen Sinn. Es besagt, daß alle Rechnungen angezeigt werden sollen, auch wenn es für diese Rechnung in der Tabelle Kunden keinen Kunden gibt. Und Rechnungen ohne Kunden machen nun wirklich keinen Sinn. Aber dennoch braucht man diese Funktion. Stellen Sie sich zum Beispiel den Fall vor, daß ein Kunde aus versehen aus der Kundentabelle gelöscht wurde. Mit dieser Funktion kann man jetzt nach Rechnungen suchen, die keinen Kunden haben.

MySQL - Bei einer MySQL die Standart-Kollation ändern

Da hat man eine neue MySQL Datenbank angelegt, und muss nach dem Einfügen der Tabellen mit entsetzen feststellen, dass alle Text-Spalten mit der Standart Kollation latin1_swedish_ci angelegt werden. Da kann es dann schon mal zu Problemen mit den Umlauten kommen. Wie kann man jetzt den Standart der Kollation auf einen Anderen setzen? Hier ist der SQL-Befehl:

Code:

ALTER DATABASE `db-name`
DEFAULT CHARACTER SET latin1
COLLATE  latin1_german1_ci;

Wenn man, wie es normalerweise der Fall ist, oft mit Umlauten und vielleicht sogar mit der russischen Schrift arbeitet, dann ist es besser folgende Collation zu benutzen.

Code:

ALTER DATABASE `db422430845`
DEFAULT CHARACTER SET utf8
DEFAULT COLLATE utf8_general_ci;

MySQL - aktuelle Zeit oder Datum in eine Spalte schreiben

Sehr oft kommt es for daß man das aktuelle Datum in eine Spalte einer Tabelle eintragen will. Sehr häufig benutzt man es für eine Spalte in der der letzte Log-In protokolliert wird. Oder man will festhalten wann ein Datensatz angelegt, oder das letzte Mal geändert wurde. Man kann das in php erledigen, und den wert dann gleich richtig formatiert mitschicken, man könnte das aber sofort und einfacher per MySQL erledigen. Hier wird jetzt das Datum samt Urzeit im richtigen Format für eine MySQL-Tabelle eingetragen: '2012-01-19 10:03:25'

Code:

UPDATE tabelle SET login=NOW()

Wenn Sie nur das Datum eintragen möchten, dann wäre das hier das richtige. Hier wird jetzt nur das Datum im richtigen Format für eine MySQL-Tabelle eingetragen: '2012-01-19'

Code:

UPDATE tabelle SET login=CURDATE()

Wegen der Vollständigkeit hier noch die Zeit. Mit den beiden letzten funktionen könnte man dann die Logindaten in zwei verschiedene Spalten schreiben. Eine für das Datum eine für die Zeit. Hier wird jetzt die Urzeit im richtigen Format für eine MySQL-Tabelle eingetragen: '10:03:25'

Code:

UPDATE tabelle SET login=CURTIME( );

MySQL - Ein Deutsches Datum per SQL in die Datenbank schreiben

Oft steht man vor dem Problem, daß mann ein Deutsches Datum in eine Zelle schreiben muss. Doch dummerweise versteht MySQL das deutsche Datumsformat nicht. Oft wird dann mit php oder anderen Scripten das Datum in kleine Teilstrings zerstückelt. Doch hier gibt es einen kleinen netten SQL-Befhel, der das alles wie von selbst macht. Hier ist der SQL-Befehl:

Code:

INSERT INTO `tabelle` (`id` ,`datum`) VALUES ('', STR_TO_DATE('14.04.2006', '%d.%m.%Y'));

MySQL - Ein Datum aus der MySQL-Datenbank im Deutschen Format anzeigen

Wir wissen jetzt, wie man ein Deutsches Datum in die Datenbank bekommt. Doch wie bekommt man es jetzt von dort wieder im Deutschen Format raus ??? Hierfür gibt es jetzt noch ein MySQL-Befehl, und der sieht so aus:

Code:

SELECT DATE_FORMAT(datum, '%d.%m.%Y') AS MeinDatum FROM tabelle;

MySQL - Mit IN Array eine MySQL-Datenbank durchsuchen

Manchmal kommt es vor, daß man eine Datenbank nach mehreren Begriffen durchsuchen muss. Man könnte jetzt natürlich die Begriffe aneinanderreihen nach dem Motto: "WHERE test LIKE 'A' OR test LIKE 'B' OR test LIKE 'C' ". Aber das macht den MySQL Code sehr unübersichtlich, wenn man mal gerne 20 oder mehr Begriffe abfragen möchte. Hier gitb es eine tolle Hilfe. Den Befehl IN ('A', 'B', 'C').

So würde das Ganze dann aussehen:

Code:

SELECT *
FROM tabelle
WHERE test IN ('A', 'B', 'C');

Wenn das Array schon in php in einer Variable vorliegt, könnte man das Ganze noch besser machen. Und zwar so:

Code:

$array = ('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I');
$SQLarray = "'" . implode("','", $array) . "'";

$MySQL = "SELECT * FROM tabelle WHERE test IN ($SQLarray);";
echo $MySQL;

MySQL - Datensätze aus zwei Spalten in einer Spalte zusammenfassen

Ab und zu kommt es auch vor, daß man die Werte zweier Spalten in einer Spalte zusammenfassen möchte. Auch hierfür gibt es einen bestimmten SQL-Befehl. In meinem Beispiel werden die beiden Werte mit einem Komma getrennt in die 3. Spalte geschrieben. Hier ist der SQL-Code:

Code:

UPDATE INTO tabelle1 
SET Spalte3 = CONCAT( Spalte1, ', ', Spalte2 )
WHERE bedingung = 1

MySQL - Mit IF eine MySQL-Datenbank abfragen

Heute ist es mir mal wieder passiert, dass ich in einer Datenbank eine Abfrage machen wollte mit der man auf einen bestimmten Umstand anders reagieren könnte. Und dabei ist mir mal wieder nicht die richtige Syntax eingefallen. Ich wollte den Inhalt einer Spalte abfragen, und wenn diese nicht vorhanden ist, soll der Wert einer anderen Spalte genommen werden.

Der Grund dafür könnte sein, dass eine Tabelle verschiedene Sprachversionen in den einzelnen Spalten ist, und wenn jetzt z.B. Spanisch noch nicht übersetzt ist, soll er eben die Übersetzung aus der Englischen Spalte nehmen.

So würde das Ganze dann aussehen:

Code:

SELECT IF(spanisch LIKE '',englisch,spanisch) AS MeinText
FROM tabelle
WHERE id = 1;

Das schöne daran ist, ich kann jetzt in meinem php Code einfach echo $row['MeinText']; einbauen und muss mich dann hier nicht mit einer php IF-Then-Else Geschichte rumplagen, da es in MySQL doch einfacher zu handhaben ist.

Vielleicht hilft Euch das ja weiter. Mir hat es zumindest geholfen. :-)

MySQL - Datensätze suchen, die nicht einer Anforderung entsprechen

Heute stand ich vor dem Problem Datensätze zu suchen, die einem Kriterium nicht entsprechne dürfen. Da ich dies schon öfter benutzt habe und heute danach gefragt wurde, möchte ich dies jetzt hier für die Nachwelt festhalten. Hier ist der relativ simple SQL-Code für My SQL

Code:

SELECT * FROM teballe
WHERE spalte NOT LIKE 'SuchBegriff';

MySQL - Bei einer WHERE Abfrage nach einem Teilstring suchen

Wie macht man es, wenn man nur nach einem Teilstring in MySQL suchen möchte? Das ist sehr ähnlich wie bei MS Access. Einfach mit left und right scuhen, wenn sich der Teilstring am Ende oder am Anfang befindet.

Code:

SELECT * FROM tabelle
WHERE left(spalte, 5) LIKE '12345';

Eine andere Möglichkeit wäre natürlich mit dem Platzhaltersymbol '%' zu suchen. Dann findet er diesen Teilstring egal wo sich dieser in der Spalte befindet.

Code:

SELECT * FROM tabelle
WHERE spalte LIKE '%12345%';

In diesem Zusammenhang sei noch erwänt, daß man auch nach einem String suchen kann, bei dem man einen oder mehrere Buchstaben durch einen Platzhalter oder Joker ersetzt. In dem unten gezeigten Beispiel findet er 12345 oder auch 10345. Das ist zum Beispiel interessant, wenn man nicht mehr weiß ob man in einem Spanischen Wort das 'ñ' mit einem normalen 'n' geschrieben hat oder nicht. So könnte man dann nach 'Espa_a' suchen und er würde 'España' und auch 'Espana' finden.

Code:

SELECT * FROM tabelle
WHERE spalte LIKE '%1_345%';

Letzte Änderung dieser Information:

19.01.2012 10:47 h

MySQL - Nach einem Strings einer bestimmten Länge suchen

Manchmal könnte es vorkommen, daß man Strings sucht, die eine bestimmte Länge aufweisen. So ist es mir zumindest heute ergangen. Dafür gibt es auch SQL Funktionen. Für meinen Fall half mir heute diese SQL.

Code:

SELECT *
FROM tabelle
WHERE CHAR_LENGTH( spalte ) >5;

MySQL - Eine Spalte in zwei Tabellen vergleichen und fehlende anzeigen

Soeben habe ich mich gefragt, wie man eine Spalte in zwei Tabellen vergleichen kann. Ich hatte zwei Tabellen, die eigentlich die gleiche Anzahl von datensätzen haben sollte. Hatten Sie aber nicht. Die eine Tabelle hatte einen Satz mehr. Damit ich nicht meine Jugendzeit damit verbringe, diesen einen Satz zu suchen, habe ich eine SQL-Abfrage geschrieben. Hier ist sie:

Code:

SELECT a.spalteA
FROM tabelle1 AS a
LEFT JOIN tabelle2 AS b ON a.spalteA = b.id
WHERE b.id IS NULL;

MySQL - Werte einer Spalte in eine andere Spalte schreiben

Heute hatte ich das Problem, daß ich Werte einer Spalte in eine andere Spalte schreiben mußte, und das über 24 Spalten hinweg bei einer Anzahl von 1200 Datensätzen. Da läßt sich logischerweise nichts mehr mit der Hand machen. Also kam ich zu folgender Lösung:

Code:

UPDATE `tabelle` SET
wert1 = wert4,
wert2 = wert5,
wert3 = wert6;

Dabei sollte man aber bedenken, daß man nicht Spalte 1 auf Spalte 2 und dann erste Spalte 2 auf Spalte 3 setzen kann. Denn dann hat man dreimal den Wert der Spalte 1. Man muß am hinteren Ende der Tabelle anfagnen, damit man sich nicht die Werte überschreibt. Bei solchen Angelegenheiten ist es sowieso ratsam man macht erst einmal einen Test mit einer Beispieltabelle, und dann erst an den richtigen Daten. Am Besten ist es auch vorher von der Datenbank ein Backup zu erstellen.

MySQL - Spalte mit einer bestimmten Anzahl von Buchstaben füllen

Wollten Sie schon mal eine Spalte mit einer bestimmten Anzahl von Buchstaben füllen? Das mußte ich heute. Meine Tabelle braucht in einer Spalte eine gewisse Anzahl von Buchstaben für einen Belegunskalender für Ferienhäuser. Also muß in der Spalte für den Januar 31 Buchstaben stehen, für Februar 2012 natürlich 29, da dies ein Schaltjahr ist. Hier folgender Vorschlag:

Code:

UPDATE `tabelle` SET
januar = REPEAT('F',31),
februar = REPEAT('F',29);