Archiv

Archiv für die Kategorie ‘SQL’

SQL Replace

17. November 2010 Keine Kommentare

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.

KategorienSQL Tags:

SQL: doppelte Datensätze finden

9. Februar 2010 Keine Kommentare

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)
KategorienSQL Tags: ,

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

21. Juli 2009 2 Kommentare

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 <http://www.cyberciti.biz/fb/>
# 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 <<END_SCRIPT
#quote USER $FTPU
#quote PASS $FTPP
ncftp -u"$FTPU" -p"$FTPP" $FTPS<<EOF

mkdir $FTPD
mkdir $FTPD/$NOW
cd $FTPD/$NOW
lcd $BACKUP
mput *
quit
EOF

### Find out if ftp backup failed or not ###
if [ "$?" == "0" ]; then
 rm -f $BACKUP/*
else
 T=/tmp/backup.fail
 echo "Date: $(date)">$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

KategorienCMS Made Simple, Linux, SQL, Typo3, Webservice Tags: , , , , ,

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

18. Mai 2009 Keine Kommentare

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
KategorienSQL Tags:

Automatisiertes MySQL Backup

24. April 2009 Keine Kommentare

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:

<?php
system(
  sprintf(
    'mysqldump --opt -h%s -u%s -p"%s" %s | gzip > %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.

KategorienPHP, SQL Tags: , ,

Datenbankfelder mit verschiedenen Formatierungen kombinieren

18. März 2009 Keine Kommentare

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
KategorienSQL Tags:

MS SQL Server Befehle

3. Januar 2009 Keine Kommentare

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)

KategorienSQL Tags:
Get Adobe Flash player