SQL / SQL Server / Access - Mike Unckel

Ausgabe aller Duplikate (einer Spalte) und deren Anzahl per SQL ermitteln.

SELECT Spalte, (COUNT(*)-1) AS Anzahl
FROM Tabelle
GROUP BY Spalte
HAVING COUNT(*)>1
ORDER BY COUNT(*) DESC;

Zufälligen Datensatz (Zeile) aus einer Tabelle abrufen:

Access:

SELECT TOP 1 * 
FROM Table 
ORDER BY Rnd(ID);

-- oder --

SELECT TOP 1 * 
FROM Table 
ORDER BY Rnd(INT(NOW*ID)-NOW*ID);

SQL Server:

SELECT TOP (1) * 
FROM Table 
ORDER BY NEWID();

Gleiche Funktion mit andere Datenbanken: http://www.petefreitag.com/item/466.cfm

Ohne die Einschränkung TOP im SELECT, lassen sich die Daten zufällig sortieren.

random rows

Anhand von Code-Beispielen, ohne viel Erklärung, soll hier kurz der Unterschied der JOINs unter Access aufgezeigt werden.

Die Ausgangstabellen:

Alle Artikel mit dazugehöriger Kategorie, auch die Artikel ohne Kategorie:

SELECT Articles.Article, Categories.Category
FROM Articles LEFT JOIN Categories
ON Articles.CategoryID = Categories.ID

Nur Artikel mit dazugehöriger Kategorie, auch die Kategorien ohne Artikel:

SELECT Articles.Article, Categories.Category
FROM Articles RIGHT JOIN Categories
ON Articles.CategoryID = Categories.ID

Nur Artikel mit dazugehöriger Kategorie, (keine leeren Artikel und Kategorien):

SELECT Articles.Article, Categories.Category
FROM Articles INNER JOIN Categories
ON Articles.CategoryID = Categories.ID

- oder -

SELECT  Articles.Article, Categories.Category
FROM Articles, Categories
WHERE Articles.CategoryID = Categories.ID;

Telefonnummern in einer SQL-Server-Datenbank ganz einfach mit T-SQL nach DIN 5008 umformatieren. Daher die bestehende Formatierung anpassen.

Ist:
089/12345-67
089 / 12345-67
(089) 1 23 45-67

Soll (nach DIN 5008 ohne Ländercode):
089 12345-67

-- 1. alle Leerzeichen löschen
UPDATE Users
SET Phone = REPLACE(Phone,' ','');

-- 2. geöffnete Klammer löschen
UPDATE Users
SET Phone = REPLACE(Phone,'(','');

-- 3. geschlossene Klammer mit Leerzeichem ersetzen
UPDATE Users
SET Phone = REPLACE(Phone,')',' ');

-- 4. Schrägstrich mit Leerzeichem ersetzen
UPDATE Users
SET Phone = REPLACE(Phone,'/',' ');

Quelle: http://blog.schelian.de/2007/09/06/TSQLReplace.aspx

Eines vorweg - grundsätzlich sollte man SQL-Befehle nicht, wie hier beschrieben, im Code zusammenbauen. Ein Problem sind vor allem SQL-Injections welche z.B. so umgangen werden können.

Hier soll daher nur der Aufbau eines SQL-Statements, daß mehrere Suchbegriffe über mehrere Felder (Tabellen-Spalten) suchen kann, beschrieben werden. Einzelne Begriffe werden mit UND verknüpft.

// C#

// String mit mehreren Begriffen, von z.B. einer TextBox
string searchWords = TextBox1.Text;

// die einzelnen Suchbegriffe in ein Array packen
string[] searchWordsArray = searchWords.Trim().Split();

// beginn des SQL-Statements...
string sqlStatement = "SELECT * FROM Tabelle WHERE";

// Prüfung - gibt es überhaupt was zu suchen
if (!string.isEmptyOrNull(searchWords.Trim()))
{
  // einzelne Suchbegriffe extrahieren
  foreach (string searchWord in searchWordsArray)
  {
    // Begriff darf in irgendeinem Felder stehen > ODER,
    // zweiter Begriff aber auch in irgendeinem Feld > UND
    sqlStatement += "(Spalte1 LIKE '%" + searchWord + "%' OR Spalte2 LIKE '%" + searchWord + "%') AND ";
  }

  // letzte 4 Zeichen (AND) löschen
  sqlStatement = sqlStatement.Substring(0,sqlStatement.Length-5);
}

// ggf. noch eine Sortierung einfügen...
sqlStatement += " ORDER BY DateCreated DESC ";

SQL bei der Suche nachf: "A":

SELECT *
FROM Tabelle
WHERE (Spalte1 LIKE '%" + A + "%' OR Spalte2 LIKE '%" + A + "%')
ORDER BY DateCreated DESC

SQL bei der Suche nach: "A B C":

SELECT *
FROM Tabelle
WHERE (Spalte1 LIKE '%" + A + "%' OR Spalte2 LIKE '%" + A + "%') AND
     (Spalte1 LIKE '%" + B + "%' OR Spalte2 LIKE '%" + B + "%') AND
     (Spalte1 LIKE '%" + C + "%' OR Spalte2 LIKE '%" + C + "%')
ORDER BY DateCreated DESC

Vergleich von Zeitfenster A mit Zeitfenster B auf Überschneidung. Das ist z.B. wichtig bei Kalender-Anwendungen mit Terminen usw... Es gibt sechs Fälle dazu:

A            |--------|
B1 |----|                          OK
B2         |----|                  Überschneidung
B3                 |----|          Überschneidung
B4                         |----|  OK
B5              |--|               Überschneidung
B6      |------------------|       Überschneidung

A-Anfang wäre der 01.01.2008, Ende wäre der 05.05.2008
B-Anfang = DateStart und das Ende dann DateEnd

Bei dieser Definition, würe ein Vergeich so aussehen:

...
WHERE ((DateStart > '2008-01-01' AND DateStart > '2008-05-05')  OR
      (DateEnd   < '2008-01-01' AND DateEnd   < '2008-05-05')) OR
       DateStart IS NULL
...

Aufgepasst bei der Verwendung von NULL in Datum/Zeit-Feldern. Nicht NULL sondern IS NULL verwenden:

SELECT *
FROM Tab
WHERE Date IS NULL

bzw.

...WHERE Date IS NOT NULL

Mehr dazu: http://www.teialehrbuch.de/Kostenlose-Kurse/SQL/14731-Der-Vergleich-mit-dem-Wert-Null.html

So mal schnell zum nachschlagen...

SELECT column_name(s)
FROM table_name

INSERT INTO table_name (column1, column2, column3,...)
VALUES (value1, value2, value3,...)

UPDATE table_name
SET column1=value, column2=value2,...
WHERE some_column=some_value

DELETE FROM table_name
WHERE some_column=some_value

Mehr davon, mit Erklärungen und zum selbst testen unter: http://w3schools.com/sql

…immer so ein Thema…

… = '" + DateTime.Now.ToShortDateString() + "' …
… = #" + DateTime.Now.ToString("yyyy-MM-dd HH:mm") + "# …

DateTime.ToString-Methode (String):
http://msdn.microsoft.com/de-de/library/zdtaw1bw(VS.80).aspx

format formatierung datetime

Fehlenden Spalten einfach mit NULL oder einem vordefiniertem Wert hinzufügen:

SELECT SpalteA, SpalteB FROM Tabelle1
UNION
SELECT SpalteA, NULL AS SpalteB FROM Tabelle2

Unterschiedlichen Spaltennamen mit AS angleichen:

SELECT SpalteA FROM Tabelle1
UNION
SELECT SpalteB AS SpalteA FROM Tabelle2

Nur nach dem zweiten SELECT per ORDER sortieren. Nicht bei beiden. (Wirkt sich aber auf beide Tabellen aus):

SELECT Spalte1, Spalte2 FROM Tabelle1
UNION
SELECT Spalte1, Spalte2 FROM Tabelle2
ORDER BY Spalte1

Ab Access 97 können GUIDs automatisch erzeugt werden:

1. entsprechendes Feld auf "Autowert" setzen
2. unter Feldgröße statt "Long Integer", "Replikations-ID" auszuwählen
3. unter Indiziert "Ja, ohne Duplikate" auszuwählen

Der Feldtyp ist weder Text noch Zahl sondern GUID. SQL-Statements in ' setzen.

... WHERE GUID = '' ...

SQL Server:

SELECT name
FROM sysobjects
WHERE type='U'

Access:

SELECT MSysObjects.Name
FROM MSysObjects
WHERE ((MSysObjects.Flags=0) AND (MSysObjects.Type=1))

Mehr dazu:
http://www.aspheute.com/artikel/20010511.htm
http://www.xtremevbtalk.com/showthread.php?s=&threadid=18084

tabellennamen

Fehlermeldung: Microsoft OLE DB Provider for ODBC Drivers- Fehler '80004005'
Fehler ID: 80004005

- oder-

Fehlermeldung: "Operation muss eine aktualisierbare Abfrage verwenden."

Liegt an den mangelnden Schreibrechten für den IUSR_ in dem Verzeichnis, in der die Access-Datenbank liegt. D.h. die "Eigenschaften" im Verzeichnis aufrufen und unter dem Reiter "Sicherheit" den User "IUSR_" hinzufügen und entsprechnende Schreibrechte geben.

Quelle: http://www.aspextra.de/tipps/dbtipps/Tipp__Schreibrechte_fuer_Access.html

Wer mit dem SQL Server 2005 Express Edition (SQLExpress) arbeitet findet leider keine Möglichkeit vor, die Datenbank automatisch sichern zu lassen. Mit ein bisschen Handarbeit geht das aber trotzdem. Und zwar so:

• SQL Server Management Studio Express starten
• Rechtsklick auf die zu sichernde Datenbank » Tasks » Sichern
• Einstellungen wie Speicherort, Sicherungstyp usw. vornehmen
• Oben mittig auf 'Skript' » 'Skript als Datei speichern' klicken und z.B. als C:\Backup\Backup.sql auf die Festplatte speichern. In der Datei sollte dann in etwa dies drinstehen:

BACKUP DATABASE [DBNAME] TO  DISK = N'D:\Backup\DBNAME.bak' WITH NOFORMAT,
NOINIT,  NAME = N'DBNAME-Vollständig Datenbank Sichern', SKIP, NOREWIND,
NOUNLOAD, STATS = 10
GO

• Eine Batchdatei Namens C:\Backup\StartBackup.bat mit folgendem Inhalt erstellen:

@ECHO OFF

SQLCMD -E -S SERVERNAME\SQLEXPRESS -i C:\Backup\Backup.sql

Mit dem Dienstprogramms SQLCMD können T-SQL Skripttasks (wie in der Backup.sql) gestartet werden. SERVERNAME\SQLEXPRESS entsprechend anpassen.

Jetzt nur noch die StartBackup.bat über Geplante Tasks (unter Systemsteuerung) regelmäßig starten lassen und fertig ist die automatische Sicherung :-)

Links zum Thema:

• Verwendung des Dienstprogramms SQLCMD
• Batch-Dateien - Kommandos und Befehle
• SQL Server 2005 Express Edition
• SQL Server Management Studio Express

Gegeben sind zwei Tabellen, die mit einem Fremdschlüssel (CategoryID) verbunden sind. Typisch für die Auflistung bei Blogs.

Die Anzahl der Artikel erhalten wir per Unterabfrage (SELECT COUNT...). Da einfach die Schlüssel bei der Tabellen verknüpfen.

SELECT Category,
       (SELECT COUNT(*) 
        FROM Articles 
        WHERE CategoryID = Categories.ID) AS Num
FROM Categories
ORDER BY Category;

Angenommen man will die aktuellsten 3 Nachrichten darstellen. Dann sieht das, z.B. für Access/SQL Server, so aus:

SELECT TOP 3 *
FROM News
ORDER BY Date DESC

Soweit, so klar.

Alle älteren Nachrichten, außer den 3 Aktuellsten,  sollen nun auf einer Archivseiten dargestellt werden. Und dazu brauchen wir das Gegenteil von SELECT TOP.

1. Versuch:

SELECT TOP 3 *
FROM News
ORDER BY Date ASC

Es erscheinen die letzten 3 Nachrichten. Allerdings nur die letzten 3 und (natürlich) auch in verkehrter Reihenfolge.

2. Versuch:

SELECT *
FROM (SELECT TOP 3 *
  FROM News
  ORDER BY Date DESC)
ORDER BY Date ASC

Die Reihenfolge stimmt jetzt, aber die Anzahl der Nachrichten ist nicht variabel. Es werden nur 3 und nicht wie gewünscht der Rest selektiert.

3. Versuch:

SELECT *
FROM News
WHERE ID NOT IN (SELECT TOP 3 ID
               FROM News
               ORDER BY Date DESC)
ORDER BY Date DESC

Bingo!

Quelle: Rich Claussen