Software Development : SQL Tutorial Part II

von Manuel Soriano

Über den Autor:

Manuel ist ein Software Consultant und seine Leidenschaft ist Linux. Er hat schon mit Computern gearbeitet als ein einziger Computer noch einen ganzen Raum ausfüllte und nur 32K Ram hatte.

Inhalt:

Einführung
Erzeugen einer Tabelle
Verändern einer Tabelle
Einfügen von Daten in eine Tabelle
Daten auswählen
Einzelheiten über WHERE
Der UPDATE Befehl
Der DELETE Befehl
Referenzen

SQL Tutorial Part II

Zusammenfassung:

In Teil II dieses Tutorials untersuchen wir einige Grundanweisungen der SQL-Sprache.

Einführung

Dies ist der zweite Teil des SQL Kurses. In diesem Teil konzentrieren wir uns auf verschiedene SQL Befehle für die Erzeugung von Tabellen, ihrer Veränderung und/oder ihrer Löschung.

Außerdem behandeln wir den SELECT Befehl, der, meiner Einschätzung nach, der wichtigste von allen ist.

Ich hoffe, daß dir das Lesen dieses zweiten Teils Spaß machen wird und er für dich lehrreich ist.

Erzeugen einer Tabelle

Wie wir im ersten Artikel gesehen haben, wird zum Erzeugen einer Tabelle der Befehl CREATE mit dem Argument TABELLEN benutzt. Der Befehl CREATE dient dazu, das folgende zu erzeugen:

Benutzer: CREATE USER
Tabellen: CREATE TABLE
Indexe: CREATE INDEX
Sichten: CREATE VIEW

Der CREATE Befehl gibt dem Manager an, daß etwas erzeugt werden soll. Später werden wir sehen, was und wie.

Was uns jetzt interessiert, ist die Erzeugung einer Tabelle:

Syntax

CREATE TABLE name ( column type [DEFAULT value] [NOT NULL], ...
      [INHERITS (inherits, ...)]
      [CONSTRAINT constraints CHECK (test), CHECK (test)] );

wobei:

`name:`	Ist der Name, der der Tabelle gegeben wurde und mit dem sie von jedem Befehl referenziert wird
`Column`:	Ist der Name der Spalte
`Type`:	Ist der Datentyp (`varchar, char, int, date, time, timestamp`), Postgres hat andere Datentypen, aber sie sind nicht mit ANSI SQL kompatibel
`Value`:	Der Wert, den er per default zugewiesen bekommt
`Inherits`:	Dies ist Postgres' eigen. Es definiert eine Vererbung von einer anderen Tabelle. Dies wird eine Entität erzeugen, die die Spalten der Tabelle besitzt, die wir erzeugen und die, die es geerbt hat
`Nom_cons`:	Dies definiert eine Integritätsregel, die jedes Mal erfüllt sein muß, wenn eine Reihe geändert wird
`Test`:	Bedingungen, die überprüft werden müssen

Beispiel:

CREATE TABLE countries (
         cod_country integer NOT NULL,
         name varchar(30))
   CONSTRAINT cod_raro CHECK (cod_country > 0 AND cod_country < 154);

Mit diesem Beispiel haben wir eine Tabelle mit Ländern erzeugt. Jedes Mal, wenn wir eine neue Reihe einfügen, müssen diese Bedingungen erfüllt sein:

Der Ländercode wird nicht NULL. Wenn versucht wird, einen Code zu Null zu machen, gibt der Manager diese Fehlermeldung aus:
```
ExecAppend: Fail to add null value in not null attribute cod_country
```
Der Ländercode muß größer als 0 und kleiner als 154 sein. Wenn man versucht, einen fehlerhaften Ländercode einzugeben, gibt der Manager die folgende Fehlermeldung aus:
```
ExecAppend: rejected due to CHECK constraint cod_raro
```

Bemerkung

Was bedeutet NULL? In SQL existieren zwei Zustände, Daten und keine Daten. Wir können daran interessiert sein, daß ein Feld keine Daten hat, wie NULL, oder auch daran, daß die Leerräume (0, '') Daten sind. SQL führt das Konzept der NULL ein und arbeitet damit. Ein praktisches Beispiel:

Ich habe eine Tabelle mit Rechnungen mit den folgenden Feldern: customer, value, date_issued, date_paid

Wenn ich eine Reihe erzeuge, dann füge ich die Daten ein: customer, value, date_issued

Ich lasse die Daten für paid (bezahlt) null; dann kann ich auf diese Weise mit dem folgenden Befehl feststellen, welche Rechnungen bezahlt werden müssen:

SELECT * FROM bills WHERE date_paid IS NULL;

Man kann einwänden, daß eine null (0) in dem Feld date_paid dasselbe erreichen würde. Das ist richtig, außer daß NULL nicht zu den Daten gehört und es verhindert, daß eine date_paid vom Datentyp erzeugt wird und die angemessenen Datenfunktionen darauf angewendet werden.

Beispiele für das Erzeugen mit NULL:

insert into countries values (15, NULL);

Oder:

insert into countries (cod_country) values (27);

Das Fehlen des Feldes "name" impliziert, daß es einen Wert erhalten hat.

Verändern einer Tabelle

In PostgreSQL können Änderungen nur neue Spalten hinzufügen.

ALTER TABLE table ADD name type;

Wobei:

Table	Name der Tabelle, die geändert werden soll
Name	Name der Spalte, die hinzugefügt werden soll
Type	Datentyp (siehe `CREATE TABLE`)

Einfügen von Daten in eine Tabelle

Jetzt wollen wir Daten in unsere Tabelle einfügen:

SYNTAX:

INSERT INTO table [(column, column, ...)] VALUES (value-1, value-2,
...)

oder sonst:

INSERT INTO table [(column, column, ...)] SELECT ....

Wie wir gesehen haben, gibt es zwei Arten, Daten in eine Tabelle einzufügen, entweder Zeile für Zeile oder als Ergebnis einer Unterauswahl, die eine oder mehrere Reihen liefern kann.

Wenn wir Zeilen in eine Tabelle einfügen, werden wir IMMER Daten in alle Spalten schreiben, einschließlich denen, die wir nicht erwähnen, diese werden mit NULL Werten belegt.

Wenn wir in dem Befehl nicht spezifizieren, welche Spalten wir füllen wollen, dann wird das so aufgefaßt, daß wir alle Spalten mit Daten füllen wollen, Beispiel:

INSERT INTO countries VALUES (34, 'Spain');

Dies wäre falsch:

INSERT INTO countries VALUES (34);

Aber dies wäre korrekt:

INSERT INTO countries (cod_country) VALUES (34);

Ich empfehle, daß ein Befehl, der in "C" Programme oder in Datenbankfunktionen eingebettet wird, IMMER die Spalten spezifiziert, die er berühren wird, sonst wird, wenn wir eine neue Spalte zu der Tabelle hinzufügen (ALTER TABLE), und als nächstes insert eingeben, ein Fehler auftreten, Beispiel:

INSERT INTO countries VALUES (34, 'Spain');

INSERT 18301 1

ALTER TABLE countries add population integer

INSERT INTO countries VALUES (34, 'Spain');

Angenommen, daß die Daten für die Bevölkerung fehlen, dann ergibt dies einen Syntaxerror.

Bemerkung

PostgreSQL generiert keine Fehlermeldung. Es erzeugt die Zeile mit dem "population" Feld NULL. Dies ist aber nur eine Besonderheit von PostgreSQL, jeder andere SQL Manager würde eine Fehlermeldung ausgeben.

Wir haben noch den anderen INSERT Typ, der durch eine Unterauswahl zustande kommt.

Dieser insert Typ wird oft verwendet, um vorläufige Tabellen zu erzeugen, um eine konkrete Aufgabe von spekulativen Kalkulationen durchzuführen.

Der Teil, der ersetzt wird, ist der, der die Daten selbst berührt, dies kommt von den SELECT Anweisungen, die vorher ausgeführt wurden und dem Einfügen von Daten. Die Anweisung SELECT kann eine oder mehrere Reihen liefern.

Daten auswählen

Ich wollte an diesem Punkt ankommen! :-))

Wir haben die erforderlichen SQL Befehle behandelt, die SQL Sprache ohne SELECT wäre wie Bohnen ohne Würstchen.

Der SELECT Befehl erlaubt uns, auf Daten zuzugreifen, aber mit der Einschränkung, daß Suchen und Vereinigen (unions) von Tabellen mit den Daten funktionieren müssen und mit den Suchregeln ausgeführt werden können.

Ein Beispiel:

select * from countries;

Ein weiteres Beispiel:

SELECT a.name, SUM(population)
    FROM countries a, states b, counties c
    WHERE b.cod_country = a.cod_country
        AND (c.cod_country = b.cod_country
        AND c.state_code = b.state_code)
        AND population IS NOT NULL
        GROUP BY a.name
        ORDER BY sum ASC;

Laßt es mich erklären, ich forderte die Bevölkerung von allen Ländern geordnet nach der Bevölkerung in aufsteigender Reihenfolge.

Hierfür habe ich eine neue Spalte (population) in die Ländertabelle hinzugefügt.

Das wäre dann folgendermaßen:

create table counties (cod_country int, 
                        state_code int, 
                        county_code int,
                        county_name varchar(60),
                        population int);
insert into counties values (1, 1, 1, 'Country 1, State 1, County 1',
5435);
insert into counties values (2, 1, 1, 'Country 2, State 1, County 1',
7832);
insert into counties values (3, 1, 1, 'Country 3, State 1, County 1',
4129);
insert into counties values (1, 2, 1, 'Country 1, State 2, County 1',
76529);
insert into counties values (2, 2, 1, 'Country 2, State 2, County 1',
9782);
insert into counties values (3, 2, 1, 'Country 3, State 2, County 1',
852);
insert into counties values (1, 3, 1, 'Country 1, State 3, County 1',
3433);
insert into counties values (2, 3, 1, 'Country 2, State 3, County 1',
7622);
insert into counties values (3, 3, 1, 'Country 3, State 3, County 1',
2798);
insert into counties values (1, 1, 2, 'Country 1, State 1, County 2',
7789);
insert into counties values (2, 1, 2, 'Country 2, State 1, County 2',
76511);
insert into counties values (3, 1, 2, 'Country 3, State 1, County 2',
98);
insert into counties values (1, 2, 2, 'Country 1, State 2, County 2',
123865);
insert into counties values (2, 2, 2, 'Country 2, State 2, County 2',
886633);
insert into counties values (3, 2, 2, 'Country 3, State 2, County 2',
982345);
insert into counties values (1, 3, 2, 'Country 1, State 3, County 2',
22344);
insert into counties values (2, 3, 2, 'Country 2, State 3, County 2',
179);
insert into counties values (3, 3, 2, 'Country 3, State 3, County 2',
196813);
insert into counties values (1, 1, 3, 'Country 1, State 1, County 3',
491301);
insert into counties values (2, 1, 3, 'Country 2, State 1, County 3',
166540);
insert into counties values (3, 1, 3, 'Country 3, State 1, County 3',
165132);
insert into counties values (1, 2, 3, 'Country 1, State 2, County 3',
0640);
insert into counties values (2, 2, 3, 'Country 2, State 2, County 3',
65120);
insert into counties values (3, 2, 3, 'Country 3, State 2, County 3',
1651462);
insert into counties values (1, 3, 3, 'Country 1, State 3, County 3',
60650);
insert into counties values (2, 3, 3, 'Country 2, State 3, County 3',
651986);
insert into counties values (3, 3, 3, 'Country 3, State 3, County 3',
NULL);
commit work;

Nun, wir können es nicht mit ALTER TABLE machen, sondern sollten den Befehl UPDATE benutzen, den ich noch nicht erklärt habe. So benutze "cut & paste" und alle sind glücklich :-))

Jetzt können wir die QUERY (Abfrage) ausführen und die Ergebnisse sollten wie folgt sein:

name     |    sum
- ---------+-------
country 1| 705559
country 2|1212418
country 3|2804018
(3 rows)

Jetzt wollen wir dies verifizieren:

select sum(population) from counties where cod_country = 1;

Ergebnisse:

   sum
- ------
791986
(1 row)

!!!!!! Ein Unterschied !!!!!!

Laßt uns die Staatentabelle betrachten, es fehlt Staat 3, wir tippen:

INSERT INTO states VALUES (3, 1, 'State 3, Country 1');
INSERT INTO states VALUES (3, 2, 'State 3, Country 2');
INSERT INTO states VALUES (3, 3, 'State 3, Country 3');

und wiederholen den Befehl mit dem Ergebnis:

name     |    sum
---------+-------
country 1| 791986
country 2|1872205
country 3|3003629

Uns fehlt von jedem Land Staat 3.

Nun für diejenigen, die nicht mehr mitgekommen sind, erinnere dich, daß joins (Vereinigungen) von Tabellen exakt sind, sie extrahieren nur Daten, wenn die Bedingung exakt erfüllt ist.

Laßt uns den ersten Teil von WHERE: b.cod_country = a.cod_country betrachten

Dies bedeutet, daß ich die Ländertabelle mit den Staaten vereinige, wo der Ländercode gleich ist, jetzt erinnere dich an die Länderdaten, die wir eingefügt haben:

Führ dies nicht aus, es ist nur zur Illustration.

create table countries (cod_country integer, name varchar(30));
insert into countries values (1, 'country 1');
insert into countries values (2, 'country 2');
insert into countries values (3, 'country 3');
commit work;

Jetzt die Staatendaten:

create table states (state_code int, 
                        cod_country int, 
                        state_name varchar(30));
insert into states values (1, 1, 'State 1, Country 1');
insert into states values (2, 1, 'State 2, Country 1');
insert into states values (1, 2, 'State 1, Country 2');
insert into states values (2, 2, 'State 2, Country 2');
insert into states values (1, 3, 'State 1, Country 3');
insert into states values (2, 3, 'State 2, Country 3');
commit work;

Alle Staaten 3 von jedem Land fehlen, außer in der Ländertabelle mit den korrespondierenden Daten zu Staat 3, deshalb ist es normal, daß wir nicht die Länderdaten zu den Staaten mit Code 3 hinzufügen, die im zweiten Teil abgelegt sind, wo:

        AND (c.cod_country = b.cod_country
        AND c.state_code = b.state_code)

Der Staat existiert in den Ländertabellen, aber NICHT in der Staatentabelle.

Für diejenigen, die das nicht verstanden haben, nehmt eine Aspirin, führt euren Hund aus (oder wenn ihr keinen Hund habt, geht ohne Hund spazieren), atmet ein bißchen frische Luft ein und wenn ihr zurückkommt, fangt noch einmal mit der ersten Übung an.

Es ist sehr wichtig zu verstehen, wie das Vereinigen von Daten ausgeführt wird, ohne dies kann die Entwicklung, die wir machen, unvorhergesehene Ergebnisse haben.

Wechseln wir den Gang und beginnen mit der Syntax des SELECT Befehls.

SELECT [DISTINCT] expression1 [AS nom-attribute] {, expression-i [as
nom-attribute-i]} 
       [INTO TABLE classname] 
       [FROM from-list] 
       [WHERE where-clause] 
       [GROUP BY attr_name1 {, attr_name-i....}] 
       [ORDER BY attr_name1 [ASC | DESC ] [USING op1 ] {,
nom-attribute-i...}] 
       [UNION {ALL} SELECT ...]

Schritt für Schritt:

`DISTINCT`:	dies wird benutzt, um doppelt auftretende Reihen zu entfernen
`expression1`:	was wir als Ergebnis geliefert haben wollen, normalerweise eine Spalte aus der Tabelle der Liste FROM
`AS nom-attribute`:	ein alias für den Namen einer Spalte, Beispiel: manu=> select cod_country from countries; cod_country - ----------- 1 2 3 (3 rows) Jetzt mit dem alias: manu=> select cod_country as countr from countries; countr - ------ 1 2 3 (3 rows)
`INTO TABLE`:	erlaubt das direkt Einfügen einer resultierenden Spalte in eine andere Tabelle (siehe INSERT ... SELECT...)
`FROM`:	Liste mit input Tabellen
`WHERE`:	Auswahlkriterium (Vereinigungs- und Selektionskriterien).
`GROUP BY`:	Gruppenkriterien, bestimmte Funktionen, die in Ausdrücken benutzt werden, brauchen vielleicht eine Gruppierung
`ORDER BY`:	Ordnungskriterium der gelieferten Reihen, ASC aufsteigende Reihenfolge, DESC absteigende Ordnung, USING wenn die Spalte, die die Ordnung definiert, nicht in der Liste ist (expression)
`UNION ALL SELECT`:	Dies sagt, daß zu den Ergebnissen des ersten SELECT die des zweiten SELECT, die verschiedene Tabellen sein können, hinzugefügt werden sollen, erzeugt aber dieselbe Anzahl von Spalten.

Wir haben gesehen, daß der SELECT Befehl nicht nur Posten von der DB liefert, sondern sie auch verändern kann:

SELECT SUM(salary * 1.1) - SUM(salary) AS increment FROM employees;

Dies liefert das Zahlungsinkrement von einer Zahlungssteigerung von mehr als 10%.

Laßt uns die verfügbaren Funktionen betrachten:

COUNT():	gibt die Anzahl der Reihen an ,die nicht NULL sind
SUM():	liefert die Gesamtsumme einer Spalte aus Zahlen
AVG():	berechnet den Durchschnitt einer Spalte aus Zahlen
MIN():	liefert den kleinsten Wert einer Spalte
MAX():	liefert den größten Wert einer Spalte
FLOAT(int):	liefert `FLOAT8, FLOAT(12345)`
FLOAT4(int):	liefert `FLOAT4, FLOAT4(12345)`
INT(float):	liefert ein `INT` von einem `FLOAT/4, INT(123.456)`
LOWER(text):	gibt den Text in Kleinbuchstaben aus
UPPER(text):	gibt den Text in Großbuchstaben aus
LPAD(text, long, char):	füllt linksbündig mit `char` mit der Länge von `long` die Spalte `text`
RPAD(text, long, char):	füllt rechtsbündig mit `char` mit der Länge von `long` die Spalte `text`
LTRIM(text, char):	löscht von links aus `text` alle Zeichen `char`
RTRIM(text, char):	löscht von rechts aus `text` alle Zeichen `char`
POSITION(string IN text):	zieht aus `text` die Positon von `string` heraus, aber ES FUNKTIONIERT NICHT
SUBSTR(text,from[,to]):	zieht die Unterzeichenkette von `text`, von der Positon `from` bis zu der Position `to` oder dem Ende der Zeichenkette, heraus
DATETIME(date, hour):	konvertiert ein Datumsformat zum Datum (YYYY-MM-DD) und eine Stunde zu (HH:MM)

Dies waren einige wenige der Funktionen, die in SQL existieren, dies sind diejenigen, die in ANSI SQL definiert sind und auch in Postgres95 vorhanden sind.

Einzelheiten über WHERE

Bis jetzt haben wir gesehen, daß wir in dem Abschnitt von WHERE im SELECT Befehl Dinge wie die folgenden schreiben:

AND column = value

Dies ist ein kleines Beispiel darüber, was wir machen oder verbinden:

AND, OR, NOT, IN, IN ALL, =, !=, >, <, (SELECT....), LIKE auch die Klammern sind relevant, Beispiel:

WHERE 
   column IN (SELECT DISTINCT column FROM table WHERE ....)
   column IN ('value1','value2','value3',...)
   (column = 'value' and column = 'other_value' OR column != 'value')

!= ist dasselbe wie NOT EQUAL

LIKE erlaubt das Suchen nach einer Zeichenkette innerhalb einer Spalte mit wildcards:

WHERE column LIKE '%Pepito%'

Das % ist eine wildcard, in dem Beispiel ist es wahr, wenn "Pepito" in der Zeichenkette ist

WHERE column LIKE 'Pepito%'

wird es wahr, wenn "Pepito" am Anfang der Zeichenkette steht

WHERE column LIKE '%Pepito'

wird es wahr, wenn "Pepito" am Ende der Zeichenkette steht

Es ist nicht genug Zeit, um alle möglichen Optionen von WHERE aufzuzählen, die Grenze liegt im Vorstellungsvermögen des Programmierers oder in den Begrenzungen des individuellen Prozeßparsers.

Jetzt können wir den SELECT Befehl verlassen und uns auf die letzten beiden konzentrieren.

Der UPDATE Befehl

Der UPDATE Befehl erlaubt die Veränderung von einer oder mehreren Zeilen, abhängig von der in WHERE definierten Bedingung

SYNTAX:

UPDATE table SET column-1 = expression-1 
                 [, column-i = expression-i] 
       [WHERE condition]

Wobei:

table:	die zu ändernde Tabelle, nur eine Tabelle kann auf einmal verändert werden
column:	Die Spalte, die verändert werden soll
expression:	der Wert, den die Spalte erhalten wird. Dieser Wert kann fest sein oder das Ergebnis einer Funktion
condition:	die Bedingung, die die Veränderungsgrenzen definiert, die hier definierten Regeln werden auf den `SELECT` Befehl angewandt

Der DELETE Befehl

Der DELETE Befehl erlaubt ebenfalls die Änderung von einer oder mehreren Zeilen einer Tabelle.

SYNTAX

DELETE FROM table
       [WHERE condition]

Wobei:

table:	die Tabelle, in der eine Reihe gelöscht werden soll, nur aus einer Tabelle kann zu einem Zeitpunkt gelöscht werden
condition:	die Bedingung, die die Veränderungsgrenzen definiert, die hier definierten Regeln werden auf den `SELECT` Befehl angewandt BEACHTE: ohne das `WHERE` werden ALLE Reihen aus der Tabelle gelöscht

Referenzen

SQL: Einführung zu SQL. Installation von PostgreSQL, der erste Artikel in dieser Reihe.

Authoren und Übersetzer:

fr	->	--	Manuel Soriano
fr	->	en	Dallas L. Gale
en	->	de	Katja Socher

1999-10-14, generated by lfparser version 0.7

mirror server hosted at Truenetwork, Russian Federation.