SQL Replace

Muss man in einer Datenbank einen String ersetzen, kann dies einfach mit einem REPLACE Statement erledigt werden.

REPLACE sucht nach einer bestimmten Zeichenfolge eines Strings und ersetzt diesen mit einer anderen Zeichenfolge. Somit ergibt dieses Statement:

SELECT Replace('Camma Rocks!', 'Rocks', 'Rolls')
gibt folgendes zurück

Camma Rolls!

REPLACE sucht im ersten String nach einer Übereinstimmung mit dem zweiten String und ersetzt diese mit dem dritten String. Die Stringlänge des alten und des neuen Strings müssen nicht übereinstimmen. Beispiel:

SELECT Replace('Camma Rocks!', 'Rocks', 'is cool')
ergibt:

Camma is cool!

Hier wurde ein 5 Zeichen String mit einem 7 Zeichen String ersetzt. Wenn der String nicht gefunden wird, werden keine Änderungen vorgenommen:

SELECT Replace('Camma Rocks!', 'Milch', 'Zucker')
gibt genau das zurück was vorher schon da war:

Camma Rocks!

Replace kann auch in Update-Statements genutzt werden.

Update adressen
Set ortschaft = replace(ortschaft, 'Moritz, 'St Moritz');

Alle Orte welche „Moritz“ im Namen haben werden durch „St Moritz“ ersetzt. Bei allen anderen Orten wird nichts unternommen.

Natürlich kann dem Statement auch eine genauere Auswahl der Datensätze zur Verfügung gestellt werden.

UPDATE adressen
set ortschaft = replace(ortschaft, 'Moritz, 'St Moritz')
WHERE ortschaft LIKE 'Moritz%';

Dies verändert nur die Datensätze welche mit „Moritz“ beginnen.

Veröffentlicht unter SQL | Verschlagwortet mit

SQL: doppelte Datensätze finden

Will man auf einer Tabelle in nachhinein  einen Key über mehrere Felder anlegen, scheitert das machmal an doppelten Datensätzen. Mit folgendem Schnippsel kann man doppelte Datensätze finden:

Hier ein Beispiel für eine Zelle

SELECT domain, COUNT(domain) AS NumOccurrences
FROM domains
GROUP BY domain
HAVING ( COUNT(domain) > 1 )

Hier ein Beispiel für einen Schlüssel über mehrere Zellen:

SELECT domain, subdomain, email, COUNT(domain) AS NumOccurrences
FROM domains
GROUP BY domain, subdomain, email
HAVING ( COUNT(domain) > 1 and COUNT(subdomain) > 1 and COUNT(email) > 1)

Veröffentlicht unter SQL | Verschlagwortet mit ,

Bash Script zum erstellen eines File und MySQL Backup mit FTP Upload

Update 21.07.2009
– mysqldump angepasst, damit die dumps auch mit phpMyAdmin eingelesen werden können.
– durchgängige Benennung der Dateien (Zeitformat überall gleich)
– Problembehebung Zeitformat (19:22:20 hat zumindest Mac auf einem smb Share nicht gerne)
– DB information_schema wird nicht mehr mitgesichert
– Vor jeder File Erstellung wird die Zeit neu eingelesen. Dadurch hat man die genaue Übersicht des Backupzeitpunkts

Update 30.06.2009 – INCFILE Pfad angepasst. Nun wird das tar-inc-backup.dat File sicher im gleichen Ordner wie das Script abgelegt.

Gestern habe ich das für mich ultimative Script zum erstellen eines Backup via Bash Script entdeckt.


#!/bin/sh
# Website + MySQL backup script
# Full backup day - Sun (rest of the day do incremental backup)
# Copyright (c) 2005-2006 nixCraft
# This script is licensed under GNU GPL version 2.0 or above
# Modified June 2009 by BlatterTech Informatik www.blattertech.ch
#
# Restore Database Backup
# gunzip mybackup.sql.gzip
# mysql -u USER -p dbname < mybackup.sql # # Restore Files # You need to restore the last full backup first ($FULLBACKUP day) # followed by each of the subsequent incremental backups to the # present day in the correct order. # # --------------------------------------------------------------------- ### Backp Name ### BACKUPSET="Backup of Website xy" SHORTNAME="websitexy" ### System Setup ### DIRS="/home/path/to/files /home/path/to/other/files" BACKUP=/tmp/backup.$$ NOW=$(date +"%Y%m%d") SCRIPTPATH=`dirname $0` INCFILE="$SCRIPTPATH/$SHORTNAME-tar-inc-backup.dat" DAY=$(date +"%u") # 1=Mon, 2=Tue, 3=Wed, .. FULLBACKUP="2" ### MySQL Setup ### MUSER="mysqluser" MPASS="mysqlpassword" MHOST="localhost" MYSQL="$(which mysql)" MYSQLDUMP="$(which mysqldump)" GZIP="$(which gzip)" ### FTP server Setup ### FTPD="/path/on/ftpserver" FTPU="ftpuser" FTPP="ftppassword" FTPS="your.ftpserver.com" NCFTP="$(which ncftpput)" ### Other stuff ### EMAILID="your@email.com" ### Start Backup for file system ### [ ! -d $BACKUP ] && mkdir -p $BACKUP || : ### See if we want to make a full backup ### i=$(date +"%Hh%Mm%Ss") if [ ! -f $INCFILE ]; then FTPD="$FTPD/full" FILE="$SHORTNAME-files-full-$NOW-$i.tar.gz" tar -g $INCFILE -zcvf $BACKUP/$FILE $DIRS elif [ "$DAY" == "$FULLBACKUP" ]; then FTPD="$FTPD/full" FILE="$SHORTNAME-files-full-$NOW-$i.tar.gz" tar -zcvf $BACKUP/$FILE $DIRS else FTPD="$FTPD/incremental" FILE="$SHORTNAME-files-i-$NOW-$i.tar.gz" tar -g $INCFILE -zcvf $BACKUP/$FILE $DIRS fi ### Start MySQL Backup ### # Get all databases name DBS="$($MYSQL -u $MUSER -h $MHOST -p$MPASS -Bse 'show databases')" for db in $DBS do if [ "$db" == "information_schema" ]; then continue fi i=$(date +"%Hh%Mm%Ss") FILE=$BACKUP/$SHORTNAME-mysql-$db.$NOW-$i.sql.gz $MYSQLDUMP -Q --opt --compact -u $MUSER -h $MHOST -p$MPASS $db | $GZIP -9 > $FILE
# FILE=$BACKUP/$SHORTNAME-mysql-$db.$NOW-$(date +"%T").sql.bz2
# $MYSQLDUMP -Q --opt --compact -u $MUSER -h $MHOST -p$MPASS $db | bzip2 -cq9 > $FILE
done

i=$(date +"%Hh%Mm%Ss")
### Make md5 Sum ###
$(which md5sum) -b $BACKUP/* >$BACKUP/$SHORTNAME-backup$NOW-$i.md5

### Dump backup using FTP ###
#Start FTP backup using ncftp
#If ncftp not aviable, use ftp:
#ftp -inv $FTPS <$T
echo "Hostname: $(hostname)" >>$T
echo "Backup Set: $BACKUPSET"
echo "Backup failed" >>$T
mail -s "BACKUP FAILED" "$EMAILID" <$T rm -f $T fi

Das Script habe ich hier gefunden.

Auf der selben Seite gibt es einen Generator um das Script mit den eigenen Parametern abzufüllen.
http://bash.cyberciti.biz/backup/wizard-ftp-script.php

Ich habe das Script noch ein wenig aufgebohrt. So habe ich die Möglichkeit geschaffen beim Fehlermail mitzugeben welches Backup den Fehler verursacht hat. Zudem habe ich die Bezeichnung des Backuptages auf die Tagesnummer geändert. Somit ist das Script nicht von der Systemsprache abhängig.
Zu guter letzt, habe ich eine Prüfung eingebaut ob das tar.dat File vorhanden ist. Dieses wird für das incrementelle Backup verwendet. Falls dieses nicht existiert, wird ein Fullbackup gemacht.

Kunden von CeviNet können das Script einfach in Ihrem Webspace als Cronjob einbinden:

Cronjob einrichten

Cronjob einrichten

COALESCE – SQL mehrere Felder mit teilweisen Null-Werten zusammenfügen

In MS SQL Server fügt man verschiedene Felder in der Regel so zusammen:

select vorname + ' ' + nachname as name, strasse, plz + ' ' + ort as ortschaft from adresse

Wenn nun in einem der zusammengefügten Felder kein Wert steht, wird NULL als Rückgabewert zurückgegeben.

Hier hilft die Funktion COALESCE

coalesce(ausdruck_1, ausdruck_2[, _ audruck_n])

Die Funktion COALESCE kann eine beliebige Anzahl von Parametern verarbeiten. Die übergebenen Parameter sind typischerweise Ausdrücke, die der Reihe nach ausgewertet werden. Der erste Ausdruck, der einen Wert ungleich NULL zurückliefert, bestimmt das Ergebnis der Funktion. Geben alle Ausdrücke NULL zurück, so ist das Ergebnis der Funktion NULL. Es müssen mindestens 2 Parameter übergeben werden. Ausserdem sollten die darauf achten, dass alle Ausdrücke einen Wert vom selben Datentyp zurückliefern oder zumindest eine implizite Typenumwandlung unterstützen.

Die Funktion COALESCE ist äquivalent mit folgenden CASE-Funktionen


Case
When ausdruck_1 ist not null then ausdruck_1
When ausdruck_2 ist not null then ausdruck_2
-
When ausdruck_n ist not null then ausdruck_n
Else null
end

Somit kann die obere SQL Abfrage so aufgebaut werden:

select coalesce(vorname,'') + ' ' + coalesce(nachname,'') as name, strasse, coalesce(plz,'') + ' ' + coalesce(ort,'') as ortschaft from adresse

Veröffentlicht unter SQL | Verschlagwortet mit

Automatisiertes MySQL Backup

Im Moment beschäftige ich mich mit dem automatisierten Backup von MySQL sowie Webseiteninhalten.

Eine Möglichkeit zum Backupen von MySQL DBs ist der MySQL Administrator. Er kann auch automatisiert Backups erstellen. Nachteil dieser Lösung ist, dass ein PC/Mac laufen muss, damit das Backup automatisiert über den Cronjob ausgeführt wird.

Ein MySQL Backup kann jedoch auch über ein Shell Script oder ein PHP Script erstellt werden. Dieses wird dann vom Server mit Cronjob regelmässig aufgerufen:


%s/backup.sql.gz',
$dbhost,
$dbuser,
$dbpwd,
$dbname,
getenv('DOCUMENT_ROOT')
)
);
?>

Wer es ein wenig detaillierter haben will, findet im selfphp Kochbuch Hilfe.

Ein weiteres sehr gutes Script findet sich bei DragonDesign. Ich werde dieses Backupscript als Grundlage für meine Weiterentwicklung gebrauchen.

Folgende Punkte will ich zusätzlich integrieren:

  • Backup vordefinierter Ordner
  • Komprimieren des File und MySQL Backups in einem Archiv
  • Automatisiertes Hochladen des Backups auf einen entfernten FTP Server

Je nach dem wie gut mein Hack ist, werde ich das Script danach hier zum Download anbieten.

Datenbankfelder mit verschiedenen Formatierungen kombinieren

Will man in einem SQL Statement ein int und ein varchar Feld zusammen in einem virtuellen Feld ausgeben, muss man die Werte auf einen gleichen Nenner (Typ) bringen:


cast(Int_Feld as varchar)

Das Statement lautet also:


select cast(int_Feld as varchar) + ' ' + varcharFeld as kombiniertes_Feld from tabelle

Veröffentlicht unter SQL | Verschlagwortet mit

MS SQL Server Befehle

MS SQL und MySQL sind ja in vielem ähnlich. Leider sind jedoch die einen oder anderen Befehle dennoch anders geschrieben. So geht z.B. „limit“ nicht unter MS SQL.

2 Seiten welche die SQL Befehle für MS SQL auflisten, sind hier zu finden:

SQL Tutorial

Einfache Beispiele für SQL Kommandos

Umfangreiches Tutorial zu MS SQL Server Befehlen (Es gibt auch ein MySQL Tutorial auf dieser Seite)

Veröffentlicht unter SQL | Verschlagwortet mit