SQL / SQL Server / Access - Mike Unckel

SQL: Duplikate mit Anzahl ausgeben

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;
Spalte Anzahl
A. Müller 5
B. Schneider 2
W. Weber 1

SQL

Juni 2010 | Permalink | Feedback


SQL: Zufalls-Datensatz bzw. -Sortierung in Access, SQL Server

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

SQL

August 2009 | Permalink | Feedback


SQL: Unterschied zwischen LEFT JOIN, RIGHT JOIN und INNER JOIN unter Access

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

Die Ausgangstabellen:

Categories
ID Category
1 Funiture
2 Automobiles
3 Buildings
4 Computer
 
Articles
ID CategoryID Article
1 1 Table
2 1 Chair
3 2 Car
4 2 Truck
5 4 Keyboard
6 4 Mouse
7 5 Hand
8 5 Foot

 

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
Article Category
Table Funiture
Chair Funiture
Car Automobiles
Truck Automobiles
Keyboard Computer
Mouse Computer
Hand  
Foot  

 

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
Article Category
Table Funiture
Chair Funiture
Car Automobiles
Truck Automobiles
  Buildings
Keyboard Computer
Mouse Computer

 

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;
Article Category
Table Funiture
Chair Funiture
Car Automobiles
Truck Automobiles
Keyboard Computer
Mouse Computer

SQL

April 2009 | Permalink | Feedback


SQL: Telefonnummer umformatieren mit T-SQL Replace

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

SQL

Februar 2009 | Permalink | Feedback


SQL: Suche nach mehreren Begriffen über mehrere Felder

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

SQL

Februar 2009 | Permalink | Feedback


SQL: Zeitfenstervergleich

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
...

SQL

November 2008 | Permalink | Feedback


SQL: Datum vorhanden? - Vergleich mit 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

SQL

Oktober 2008 | Permalink | Feedback


SQL: Grundoperationen SELECT, INSERT, UPDATE und DELETE

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

SQL

Oktober 2008 | Permalink | Feedback


SQL: Datum und Datenbanken...

…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

SQL

Oktober 2008 | Permalink | Feedback


SQL: UNION Tipps - unterschiedlich viele Spalten / unterschiediche Spaltennamen / Sortierung

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

SQL

August 2008 | Permalink | Feedback


Access: GUID setzen

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

März 2008 | Permalink | Feedback


SQL: Alle SQL Server- / Access-Tabellen ermitteln

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

SQL

Januar 2008 | Permalink | Feedback


Access: Schreibrechte beim Einsatz von ASP.NET

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

SQL

Oktober 2007 | Permalink | Feedback


SQL Server 2005 Express Edition-Datenbank automatisch sichern

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:

SQL

Juli 2007 | Permalink | Feedback


SQL: Anzahl der Artikel zum Kategorie per Unterabfrage ermitteln

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

Categories
ID Category
1 Funiture
2 Automobiles
3 Buildings
4 Computer
 
Articles
ID CategoryID Article
1 1 Table
2 1 Chair
3 2 Car
4 2 Truck
5 3 Skycraper
6 4 Mouse
7 4 Honitor
8 4 Keyboard

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;
Category Num
Automobiles 2
Buildings 1
Computer 3
Funiture 2

SQL

Juni 2007 | Permalink | Feedback


SQL: Was ist das Gegenteil von SELECT TOP?

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

SQL

Juni 2007 | Permalink | Feedback