SSQL Simple SQL

Befehls-Übersicht:

    SHOW
    SHOW TABLE
    SELECT
    INSERT
    UPDATE
    DELETE

Eingebaute Funktionen:

    EXTRACT
    POSITION
    STRLEN
    STRCAT
    SUBSTR
    UPPER
    LOWER
    ABS
    MOD
    CAST

Summenfunktion:

    AVG
    MAX
    MIN
    SUM
    COUNT

Befehls – Syntax:

Beispiel:

    SHOW TABLE EM200DEB     zeigt die Tabellenstruktur des Kundenstammes an.

Die einfache SELECT - Anweisung

SELECT feld1, feld2... FROM tabelle WHERE (bedingung1) AND/OR ( bedingung2)...

Mit diesem Befehl werden die einzelnen Feldinhalte für alle Datensätze aus der Tabelle angezeigt, auf die die angegebenen Bedingungen  zutreffen.

Die WHERE – Klausel kann auch entfallen, dann werden alle Daten der Tabelle gezeigt.

Werden Bedingungen angegeben, muss jede Bedingung für sich mit einem Klammerpaar () umschlossen werden.

Beispiele:

 SELECT konto, name, plzort FROM em200deb

Erstellt eine Liste aller Kunden, wobei die Kundennummer, der Name und die Postleitzahl mit Wohnort angedruckt werden.

SELECT konto, name, plzort FROM em200deb WHERE ( konto =  ''10141'')

Zeigt die o.g. Daten für den Kunden mit der Kontonummer 10141.

SELECT konto, name, plzort FROM em200deb   WHERE (SUBSTR (plzort, 1,5) = “10000”)

Zeigt die Daten aller Kunden, deren PLZ 10000 ist. Die Funktion SUBSTR  liefert hier ab der 1.Stelle die nächste 5 Zeichen des Feldes <plzort>, d.h. die Postleitzahl.

SELECT konto, name, plzort FROM em200deb    WHERE (plzort LIKE “*Hamburg*”)

Diese Abfrage liefert alle Datensätze, bei denen irgendwo im Feld <plzort> die Zeichenkette „Hamburg“ vorkommt. Die beiden Sternchen links und rechts dienen als Joker für beliebig viele andere Zeichen.

Der Ausdruck „Hamburg*“ würde somit nur Treffer liefern, wenn das Feld mit „Hamburg....“ beginnt; analog hierzu liefert „*Hamburg“ nur dann Treffer, wenn die Zeichenkette auf „...Hamburg“ endet.

Bitte Beachten Sie, dass Sie den LIKE-Vergleich nur mit den Feldern des Typs „S“ (Zeichenkette) verwenden können.

Die einfache SELECT-Anweisung mit Sortierung

 Um eine bestimmte Sortierung zu erreichen, muss die ORDER BY Anweisung verwendet werden:

SELECT feld1, feld2... FROM tabelle WHERE (bedingung1) AND/OR (bedingung2).. 
    ORDER BY sortfeld1, sortfeld2…

Beispiel: 

SELECT konto,name,plzort FROM em200deb ORDER BY plzort

Diese Anweisung erstellt eine Liste aus dem Kundenstamm, wobei die Ausgabe nach Postleitzahlen und Orten sortiert ist.

Die Sortierung erfolgt grundsätzlich in aufsteigender Reihenfolge. Soll die Reihenfolge absteigend sortiert werden, kann dies  mit dem Schlüsselwort DESC erreicht werden:

SELECT konto, name, plzort FROM em200deb ORDER BY plzort DESC

Die Funktion COUNT ( )

Mit der Funktion COUNT können Datensätze durchgezählt werden:

 SELECT COUNT (konto) FROM em200deb

Zählt, wie oft das Feld <konto> in der Tabelle vorkommt, d.h. also, wie viel Kundenstammdaten vorhanden sind.

SELECT mit Gruppenbildung (GROUP BY)

Mit Hilfe der Gruppenbildung können Sie ein Feld mit gleichen Inhalten zusammenfassen, gruppieren:

 SELECT plzort, COUNT (konto) FROM em200deb  GROUP BY plzort

 Liefert eine Liste in der Art:

Das Feld <plzort> wird zunächst sortiert, für jeden eindeutigen Inhalt wird dann die Anzahl der vorhandenen Fälle ermittelt und dargestellt.

Das Element GROUP BY sorgt für die Gruppierung. Es folgt immer nach WHERE, wenn ein solches vorhanden ist. Es kann selber noch durch HAVING eingeschränkt werden. HAVING folgt immer GROUP BY.

SELECT plzort,COUNT (konto) FROM em200deb  GROUP BY plzort  HAVING COUNT (konto) > 2

 Liefert eine Liste in der Art:

 PLZORT                                               COUNT (KONTO)

 03042                Cottbus                      3

Weitere Aggregatsfunktionen

Neben COUNT () gibt es noch die Aggregatsfunktionen SUM (), AVG () , MIN () und MAX ():

SUM (feld)                ermittelt die Summe des Feldes

AVG (feld)                ermittelt den Durchschnitt des Feldes

MIN  (feld)                ermittelt den kleinsten Feldwert

MAX (feld)                ermittelt den größten Feldwert

SELECT MAX (feendsu1) FROM as100res WHERE (vgkennz = „U“)

 Liefert den höchsten derzeitigen Einzel-Auftragswert.

SELECT 1_kundnr, 1_name, COUNT (1_kundnr), SUM (feendsu1) FROM as100res  
WHERE (vgkennz = “U”)  GROUP BY 1_kundnr

Liefert eine Aufstellung aller Kunden, die derzeit aktuelle Aufträge haben und ermittelt die jeweilige Anzahl und die Summe der Aufträge gruppiert nach Kunden.

Mathematische Funktionen

Sie können bereits innerhalb einer Abfrage Berechnungen durchführen. Wenn Sie zum Beispiel eine Liste aller Aufträge mit dem Brutto-Rechnungspreis einschl. MWSt. erstellen möchten, können Sie folgende Abfrage formulieren:

SELECT auftrnr, ruecknr, feendsu1 * 1.16 AS Bruttopreis FROM as100res

 Der Spaltenbezeichner AS definiert hierbei für die Spalte eine neue Überschrift, er muss nicht sein, dient aber –gerade in berechneten Spalten der besseren Übersicht der Ausgabe.

STRLEN

Mit STRLEN (Spaltenname) wird die Länge einer Zeichenkette ermittelt. Mit folgender Abfrage erfahren Sie die Länge der Kundennamen:

SELECT konto, STRLEN (name) FROM em200deb

EXTRACT

Um aus einem Datum den Tag, den Monat oder das Jahr zu ermitteln, steht die Funktion EXTRACT zur Verfügung:

 SELECT auftrnr, ruecknr, 1_kundnr, 
    EXTRACT (DAY FROM belegdat) AS “TAG“, 
    EXTRACT (MONTH FROM belegdat) AS “Monat”, 
    EXTRACT (YEAR FROM belegdat) AS “Jahr”
FROM as100res

MOD

Die Funktion  MOD (Spaltenname, Wert) gibt den Rest einer Division der Spalte durch Wert an.

SELECT konto, MOD (konto, 100) FROM em200deb

erzeugt eine Liste wie folgt: 

POSITION

Mit Hilfe von POSITION(zeichenkette IN spaltenname) können Sie feststellen, ob ein bestimmtes Zeichen bzw. eine Zeichenkette in der Spalte vorhanden ist.

SELECT konto, name, POSITION (“au“ IN name) FROM em200deb WHERE (POSITION(“au“) IN name) > 0)

Erstellt eine Liste aller Kunden, in deren Name irgendwo die Zeichenfolge „au“ enthalten ist.

Mit  dem nachfolgenden SQL können Sie eine Liste aller Sachbearbeiter erstellen, die eine ungültige Email-Adresse in Ihrem Stamm haben.  Ungültig meint hier, eine Email-Adresse ohne „@“ – Zeichen:

SELECT login, name, email FROM druckusr  WHERE (POSITION(“@” IN email) < 1) AND  (STRLEN (email) > 0)

STRCAT

Verschiedene Spalten oder Zeichenketten lassen ich in der Ausgabe zu einer neuen Spalte verbinden; die Funktion hierzu ist STRCAT (Spalte1, Spalte2….)

 Um eine Liste mit Kundennummer  und dem vollständigen Namen in einer Spalte zu erhalten, verwenden Sie

 SELECT konto, STRCAT (name, “, “, firma) AS “ Voller Name“ FROM em200deb

Durch geschickte Kombination kann man persönliche Daten auch anonymisieren. Um z.B. eine Kundenliste auszugeben, die nur die ersten beiden Stellen des Namens enthalten, könnte man schreiben:

SELECT konto, strcat(substr(name, 1, 2) , “*****“), plzort FROM em200deb

Jetzt werden die ersten beiden Buchstaben des Namens, gefolgt von 5 Sternchen ausgegeben: 

UPPER / LOWER

Um eine Spalte komplett in Grossbuchstaben auszugeben, verwenden Sie UPPER (spaltenname):

 SELECT konto, UPPER (name) FROM em200deb

Um alle Buchstaben in Kleinbuchstaben zu verwandeln, können Sie LOWER (spaltenname) verwenden. So liefert die Abfrage

SELECT konto, LOWER (matchcod) FROM em200deb

eine ungewöhnliche Matchcode-Liste, die Sie so sicher noch nicht gesehen haben.

Typumwandlung mit CAST( )

Manchmal ist es notwendig, den Datentyp einer Spalte in einen anderen Datentyp umzuwandeln, um beispielsweise Berechnungen durchzuführen oder ähnliches.

Hierzu wird die Funktion CAST( ) verwendet. Der allgemeine Aufbau lautet

CAST (spalte AS datentyp)

 Datentyp kann hierbei sein: INTEGER, DECIMAL( n,n), CHAR (n) oder DATE.

Wird DECIMAL verwendet, muss in Klammern hierbei die Anzahl der Stellen insgesamt und der Nachkommastellen angegeben werden. Soll ein Wert als 15-stellige Zahl mit drei Nachkommastellen Genauigkeit gewandelt werden, wird daher DECIMAL ( 15,3) geschrieben.

 Wird in eine Zeichenkette mittels CHAR umgewandelt, muss die Länge der Zeichenklette festgelegt werden:

CHAR (20) erzeugt also eine Zeichenkette mit 20 Zeichen.

Beispiele:

Das Feld „Konto“ aus dem Kundenstamm ist vom Typ String, Zeichenkette.

Mit Zeichenketten kann man im allgemeinen keine Rechenoperationen durchführen; Zeichenketten können alle möglichen  Zeichen enthalten. Wir wollen nun eine Liste erstellen, auf der das jeweilige Konto und daneben eine um 1000 höhere Zahl steht. Die einfache Abfrage

SELECT konto, konto+1000 FROM EM200DEB

führt hierbei ins Abseits, da sie nicht den gewünschten Erfolg zeigt.

Wir setzen also CAST zur Typumwandlung ein:

SELECT konto, CAST (konto AS INTEGER) +1000 FROM em200deb

 Als Ergebnis erhalten wir eine Liste in der Art:

Wir wollen eine Buchungsliste mit allen Buchungen, deren Betrag auf 11 Cent endet. Hierzu wandeln wir den Betrag in eine Zeichenkette um und verwenden dann LIKE:

 SELECT konto, datum, betrageu FROM as100bus WHERE (CAST(betrageu AS CHAR (15)) LIKE “*11”)

Die Liste sieht dann etwa so aus:

Wir wollen eine Aufstellung aller Aufträge, wobei wir als Betrag auch den auf vollen Euro gerundeten Wert zeigen möchten:

SELECT auftragsnr, ruecknr, fenesum1, CAST (fenesum1 AS DECIMAL (15,0) ) FROM as100res

Würden Sie in dieser Abfrage den CAST so formulieren:

      CAST (fenesum1 AS INTEGER) ,

wäre das Ergebnis nur der Ganzzahlenteil (Vorkommateil) ohne jede Rundung.

Verknüpfung mehrerer Tabellen

INNER JOIN

Der innere Verbund (INNER JOIN) gibt nur Datensätze aus, die in beiden Tabellen vorhanden sind.

 Der Aufbau des INNER JOIN entspricht folgende, allgemeine Regeln:

 SELECT spaltenliste FROM tabelle1

      [INNER] JOIN tabelle2 ON tabelle1.spalte = tabelle2.spalte

           [INNER] JOIN tabelle3 ON tabelle2.spalte = tabelle3.spalte.....

Beispiele:

Erstellen Sie eine Artikelliste, auf der der aktuelle Lieferant mit seinem Namen erscheint:

SELECT a.artnr, a.1_bezeic, a.liefnrvp, k.name FROM as100art a 
INNER JOIN em200krd k
ON a.liefnrvp =k.konto

Listen Sie alle Kunden, die zur Zeit aktuelle Aufträge haben und ermitteln Sie die Anzahl und den Wert dieser Aufträge:

SELECT a.konto, a.name, COUNT(b.auftrnr), SUM(b.feendsu1) FROM em200deb a
JOIN as100res b
ON a.konto = b.1_kundnr
WHERE (b.vgkennz = “U”)
GROUP BY a.konto

 Dies liefert eine Aufstellung in der folgenden Art:

Da der INNNER JOIN die meistgebrauchte Verbindung darstellt, kann das Schlüsselwort INNER auch weggelassen werden, wie im zweiten Beispiel gesehen.

OUTER JOIN

Angenommen, wir wollen eine Kundenliste haben und wollen hierbei, falls ein aktueller Auftrag für den Kunden besteht, das Auftragsdatum drucken. Es sollen aber alle Kunden gelistet werden, unabhängig davon, ob gerade ein Auftrag vorhanden ist oder nicht. Dies ist weder mit INNER JOIN noch WHERE möglich. Hierfür wird der OUTER JOIN verwendet. Hierbei können Sie angeben, welche Tabelle vollständig gedruckt werden soll.

Der Aufbau wird dabei so angepasst, wie es notwendig ist: 

SELECT spaltenliste FROM tabelle1   LEFT bzw. RIGHT [OUTER] JOIN tabelle2 ON….

Wenn Sie alle betreffenden Spalten der links vom JOIN stehenden Tabelle ausgeben wollen, verwenden Sie LEFT JOIN. Entsprechend verwenden Sie RIGHT JOIN, um alle Spalten der rechts stehenden Tabelle zu listen.

Unsere vorstehende Aufgabe würden Sie also folgendermaßen lösen: 

SELECT a.konto, a.name, b.belegdat FROM em200deb a 
LEFT JOIN as100res b ON a.kont = b.1_kundnr

Als Ergebnis erhalten Sie eine Liste etwa in der Art:

Unterabfrage (Sub-Selects)

 Das folgende Beispiel zeigt das Prinzip der Unterfragen. Es wird hierbei die Kundennummer und die Auftragsnummer desjenigen Auftrags mit dem höchsten Wert gesucht:

SELECT 1_kundnr, auftrnr, ruecknr, feendsu1 FROM as100res  WHERE 
        (
             feendsu1 =  SELECT MAX (feendsu1) FROM as100res
        )

Als Ergebnis erhalten Sie genau eine Zeile in der Art:

 1_KUNDNR                AUFTRNR                RUECKNR                          FEENDSU1
 10182                          96000564                00                                              24527,31

Ausgabe in EXCEL-Format

 Durch Angabe des Zusatzes EXCEL „Dateiname“ wird die Ausgabe nicht als Liste, sondern als Datei im EXCEL-Format ausgegeben. Hierbei muss ein gültiger Dateiname angegeben werden, sonst wird die Operation mit einer Fehlermeldung abgebrochen.

Die allgemeine Schreibweise für die SELECT Anweisung lautet dann:

SELECT spaltenliste FROM tabelle 
WHERE (bedingung)
GROUP BY feld
EXCEL “Ausgabe-Dateiname”

Beispiel:

SELECT a.kont, a.name, count (b.konto) FROM em200deb a
LEFT JOIN as200bus b ON a.konto = b.konto
GROUP BY konto
EXCEL “/u1/modem/sspl.xls”

Diese Anweisung erstellt eine EXCEL-Datei mit einer Aufstellung aller Kunden und deren Anzahl Buchungen, auch wenn keine Buchungen vorhanden sind.

Datensatz ändern mit UPDATE

Um Daten eines oder mehrere Datensätze zu ändern, verwenden Sie den UPDATE-Befehl. Dieser hat allgemein die Syntax

UPDATE tabelle SET spalte=Wert, spalte=Wert...
   WHERE Auswahlbedingung.

Die Auswahlbedingung ist hierbei von großer Bedeutung; fehlt sie, werden die Änderungen auf alle Datensätze in der Tabelle angewandt!

Beispiel:

Der Kunde mit Nummer 10141 teilt eine neue Telefon-Nummer mit:

 UPDATE em200deb  SET TELEFON = “09.....“ WHERE (konto = “10141“)

Datensätze löschen mit DELETE

Mit der allgemeinen Anweisung

DELETE FROM tabelle WHERE Auswahlbedingung

werden Daten unwiderruflich gelöscht. Wird die Auswahlbedingung weggelassen, sind anschließend keinerlei Daten mehr in der Tabelle vorhanden !

Senden Sie E-Mail mit Fragen oder Kommentaren zu dieser Website an:

 
Stand: 03. August 2005