Beispiel

“select * from adresse” im Editor-Fenster markieren, dann Menü “Session – Bookmark – Add”, Name sei “adr*”, bei Beschreibung gebe ich ein “Alle Adressen”. Dann kann ich im Editor-Fenster Strg-j drücken und bekomme die Liste aller Bookmarks präsentiert. Mit jedem Tastendruck reduziert sich die Anzahl der Elemente in der Auswahl. Wenn ich “a” und “d” eingetippt habe, sehe ich nur noch “adr*” und kann auf “Return” hämmern, um den Befehl in den Editor einzufügen und Strg-Return, um den Befehl auszuführen. Sehr bequem!
Für “select * from adresse” wird man kein Bookmark anlegen, aber für das WeihnachtsgeschäftSQL schon.

Asymmetrie

Ich reite nicht gerne auf Dingen herum. Einiges ist eben omnipräsent ;) Das Bookmark-Plugin von SQuirreL weist eine Asymmetrie auf: Man kann Bookmarks nicht auf dem Weg löschen, auf dem man sie erstellt hat. Aber man ist dem nicht ausgeliegert: um ein Bookmark zu bearbeiten gehe ins Menü “File – Global Preferences”, dort gibt es einen Reiter “Bookmark” und darin entsprechende Buttons für das Erstellen, Bearbeiten und Löschen.

Alternative: Das Plugin speichert seine Einstellungen in ~/.squirrel-sql/plugins/sqlbookmark/bookmarks.xml. Darin enthalten sind <Bean>-Tags. Finde das passende Tag und editiere oder lösche es. Oder füge ein Tag hinzu, das neue Bookmark wird nach dem Neustart von SQuirreL zur Verfügung stehen.

Schmankerl: Parameter

Ein Bookmark ist erstmal nur ein kurzer Weg über die Tastatur zum Befehl. Im Alltag kommt es oft vor, dass ein Befehl einen Parameter braucht, um zum Ergebnis zu kommen.Beispiel: In “select * from adresse where id = 42″ ist “42” der Wert des Parameters. Und oft kommt mehr als ein Parameter vor. Beispiel:

select * from adresse where Name = 'Kopp' and Vorname='Matthias'

Die Werte unterscheiden sich natürlich bei jeder Abfrage, und es ist ziemlich nervig, immer wieder durch den SQL-Text zu laufen und die Werte zu verändern. Bookmarks in SQuirreL können Dir diese Lauferei abnehmen. Erstelle das Bookmark in der Form

select * from adresse where Name = '${Name-Eingabe}' and Vorname='${Vorname-Eingabe}'

Dann fragt SQuirreL die beiden Werte ab, wenn Du das Bookmark aufrufst, und setzt die beiden Werte ein, wenn es den Text in den Editor gibt. Wenn derselbe Variablenname mehrfach vorkommt, wird er nur einmal abgefragt und bei jedem Vorkommen passend ersetzt.

Wunderbar. Danke, SQuirreL.

Kompliziert wird es, wenn Tabellen sich ändern – beispielsweise wegen Kundenanpassungen. Für den bereits bestehenden Code ist das nicht gut, denn der “wettet” ja auf eine ganz bestimmte Struktur. Beim Ändern der Tabelle müßte man also darauf achten, bestehenden Code nicht zu zerbrechen – was für eine Qual! Ich müßte den gesamten Code abklappern … gar nicht gut, das dauert viel zu lange und Chef sitzt mir im Nacken. Was tun?

Eine Option ist, den Quelltext so zu organisieren, dass mir das Abklappern leicht fällt. Aber das ist ein anderes Thema. Hier und jetzt möchte ich die Seite des RDBMS beleuchten. Kann mir das helfen? Natürlich kann es das.

Die mir bekannten RDBMS erlauben es, Views zu erstellen. Ein View kann man sich als benannte und im RDBMS gespeicherte Abfrage vorstellen. Das ist eine hilfreiche Option, dem Kind einen Namen geben zu können – hilfreich für das Verständnis, das Debuggen. Und man kann die Daten so bekommen, wie man sie braucht: Man kann weglassen, Spalten umbenennen, mit join arbeiten usw. – eben all die Techniken nutzen, die ein select bietet. Der View “kennt” die benötigten Tabellennamen, aber der Nutzer des Views braucht die beteiligten Tabellennamen und -strukturen nicht zu kennen. Der Nutzer des Views kennt – nur den View. Wie der View zu seinem Ergebnis kommt, ist dem View-Abfrager gleichgültig. In der Programmierung nennen wir dieses Prinzip “Kapselung”.

Auf der Seite der Client-Programmierung macht es keinen Unterschied, ob ich eine Tabelle abfrage oder einen View. Deswegen bietet das Erstellen von Views die Möglichkeit, eine stabile Schnittstelle aufzubauen. Feine Sache, weil der zugreifende Code nicht zerbrechen wird, wenn ich eine Tabelle ändere, die jenseits der Schnittstelle liegt.

Eitel Sonnenschein? Fast. Woher weiss ich, dass eine Tabellenänderung nicht Views kaputt macht? Hier hilft mir eine Automatisierung: Ich habe ein Script, das alle Views löscht. Und ich habe ein Script, das alle Views aufbaut. Wenn die beiden Scripte erfolgreich abgearbeitet werden können, dann ist’s in Ordnung. Wenn nicht, dann kann ich anhand der Fehlermeldung beim Anlegen genau sehen, was und wo es schiefgeht.

Offene Frage: Ist einfügen in und löschen aus Views möglich? Mit Oracle’s RDBMS schwant mir, dass es möglich ist, wenn der View “key preserved” ist. Mssql löscht einfach – und unter Umständen ist man überrascht, was da gelöscht wird und was nicht :-/

Offene Frage: Gibt es Views mit Parametern? Das wäre praktisch …

Hier ist ein Ausschnitt der (My)Sql-Tabellen-Struktur, gefüllt mit Fantasie-Daten. Ich habe es für MySQL entwickelt, es sollte mit keinen oder wenigen Anpassungen auf andere RDBMS übertragbar sein.

CREATE TABLE adresse
(
   id int PRIMARY KEY NOT NULL,
   vorname varchar(45),
   name varchar(45),
   strasse varchar(80),
   plz varchar(10),
   ort varchar(20)
);

insert into adresse (id,vorname,name,strasse,plz,ort) values (1,'Peter','Mustermann','Musterstr.42','12345','Musterhausen');
insert into adresse (id,vorname,name,strasse,plz,ort) values (2,'Paul','Mustermann','Musterstr.43','12345','Musterhausen');
insert into adresse (id,vorname,name,strasse,plz,ort) values (3,'Erika','Musterfrau','Musterstr.44','12345','Musterhausen');
insert into adresse (id,vorname,name,strasse,plz,ort) values (4,'Erna','Normalverbraucher','Musterstr.45','12345','Musterhausen');
insert into adresse (id,vorname,name,strasse,plz,ort) values (5,'Erna','Normalverbraucher','Ottostr.45','12345','Musterhausen');

CREATE TABLE bestellung
(
   id int PRIMARY KEY NOT NULL,
   rechnungsadresse int DEFAULT 0 NOT NULL,
   lieferadresse int DEFAULT 0 NOT NULL,
   id_person int DEFAULT 0 NOT NULL,
   datum timestamp NOT NULL
);

insert into bestellung (id,rechnungsadresse,lieferadresse,id_person,datum) values (1,1,1,1,'2010.12.01');
insert into bestellung (id,rechnungsadresse,lieferadresse,id_person,datum) values (2,1,1,1,'2011.01.01');
insert into bestellung (id,rechnungsadresse,lieferadresse,id_person,datum) values (3,1,1,1,'2011.02.01');
insert into bestellung (id,rechnungsadresse,lieferadresse,id_person,datum) values (4,1,1,1,'2011.12.01');
insert into bestellung (id,rechnungsadresse,lieferadresse,id_person,datum) values (5,1,1,1,'2012.01.01');
insert into bestellung (id,rechnungsadresse,lieferadresse,id_person,datum) values (6,1,1,1,'2012.12.01');
insert into bestellung (id,rechnungsadresse,lieferadresse,id_person,datum) values (7,2,2,2,'2010.12.02');
insert into bestellung (id,rechnungsadresse,lieferadresse,id_person,datum) values (8,2,2,2,'2011.02.01');
insert into bestellung (id,rechnungsadresse,lieferadresse,id_person,datum) values (9,3,3,3,'2011.02.03');
insert into bestellung (id,rechnungsadresse,lieferadresse,id_person,datum) values (10,3,3,3,'2011.12.03');
insert into bestellung (id,rechnungsadresse,lieferadresse,id_person,datum) values (11,3,3,3,'2012.03.03');
insert into bestellung (id,rechnungsadresse,lieferadresse,id_person,datum) values (12,3,3,3,'2012.12.03');
insert into bestellung (id,rechnungsadresse,lieferadresse,id_person,datum) values (13,4,4,4,'2011.05.04');
insert into bestellung (id,rechnungsadresse,lieferadresse,id_person,datum) values (14,4,4,4,'2011.07.04');
insert into bestellung (id,rechnungsadresse,lieferadresse,id_person,datum) values (15,5,4,4,'2011.09.04');

Zuerst finde heraus, wer wieoft bestellt hat und wann die letzte Bestellung stattfand – unter Bachtung der oben gegebenen Einschränkungen:

-- INTERVAL 22 MONTH
-- weil es erst November ist und wir den Januar auch "mitnehmen" möchten.

SELECT id_person, max(datum) as letzteBestellung, count(*) as anzahlBestellungen
FROM (
	SELECT id_person, datum
	FROM bestellung
) t1
group by id_person
having count(*) >= 3 -- wenigstens 3 Bestellungen 
and max(datum) < DATE_SUB(now(), INTERVAL 22 MONTH) -- und seit 22 Monaten ohne Umsatz

Aufgrund dieser Daten suche ich die Rechnungsadresse – mit einem self-join:

SELECT distinct taba.id_person, tabb.letzteBestellung, tabb.anzahlBestellungen, taba.rechnungsadresse
FROM bestellung taba inner join
(
	SELECT id_person, max(datum) as letzteBestellung, count(*) as anzahlBestellungen
	FROM (
		SELECT id_person, datum
		FROM bestellung
	) t1
	group by id_person
	having count(*) >= 3
	and max(datum) < DATE_SUB(now(), INTERVAL 22 MONTH)
) tabb
on (taba.id_person = tabb.id_person
and tabb.letzteBestellung = taba.datum)

Und dann brauche ich das nur noch mit den Adressen zu verbinden und habe das Ergebnis:

SELECT *
FROM (
	SELECT distinct taba.id_person, tabb.letzteBestellung, tabb.anzahlBestellungen, taba.rechnungsadresse
	FROM bestellung taba inner join
	(
		SELECT id_person, max(datum) as letzteBestellung, count(*) as anzahlBestellungen
		FROM (
			SELECT id_person, datum
			FROM bestellung
		) t1
		group by id_person
		having count(*) >= 3
		and max(datum) < DATE_SUB(now(), INTERVAL 22 MONTH)
	) tabb
	on (taba.id_person = tabb.id_person
	and tabb.letzteBestellung = taba.datum)
) ago inner join adresse
on ago.rechnungsadresse = adresse.id

Die Bestelldaten sind auch für Menschen überschaubar. Peter bekommt keine Karte, weil er ein treuer Kunde ist und wir hoffen, dass er in diesem Jahr auch so wieder etwas bestellt. Paul bekommt keine Karte, weil er – nach unseren Masstäben – kein Interesse hat. Erika ergeht es wie Peter. Aber eine Karte in Erna zu investieren lohnt bestimmt!

Aufräumen nicht vergessen, weil das nur ein Bespiel ist.

drop table bestellung;
drop table adresse;

Und das SQL ist so, dass wir es im kommenden Jahr wieder verwenden können. Der Serienbrief-Druck kann beginnen.

Über den Sinn und Unsinn einer solchen Weihnachtskarten-Aktion kann man trefflich streiten. Erfolgskontrolle wird die Marketing-Abteilung freuen: Wer von den angeschriebenen hat wieder etwas bei uns gekauft? Und warum? War die Karte der Auslöser oder die Trennung vom Partner oder … was?

Frage: Welche Postleitzahlen gibt es sowohl in Deutschland als auch in den USA?

Stationen auf dem Weg zur Antwort:

-- join
--
SELECT count(*)
FROM (
    SELECT distinct orte.PLZ FROM 
    orte, orteus
    where orte.PLZ = orteus.PLZ
) t1;

-- subselect
--
SELECT count(*)
FROM (
    SELECT distinct orte.PLZ
    FROM orte
    where orte.PLZ in (
        select orteus.PLZ FROM orteus
    )
) t2;

mysql> select count(*) from orte;
 +----------+
 | count(*) |
 +----------+
 |    44188 |
 +----------+
 1 row in set (0.00 sec)

mysql> select count(*) from orteus;
+----------+
| count(*) |
+----------+
|    81831 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT count(*) FROM (SELECT distinct orte.PLZ FROM orte, orteus where orte.PLZ = orteus.PLZ) t1;
+----------+
| count(*) |
+----------+
|    13365 |
+----------+
1 row in set (0.16 sec)

mysql> SELECT count(*) FROM (SELECT distinct orte.PLZ FROM orte where orte.PLZ in (select orteus.PLZ FROM orteus)) t2;
+----------+
| count(*) |
+----------+
|    13365 |
+----------+
1 row in set (0.18 sec)

use test

/*
	Warenkorb-Analyse
	=================
	
	Bei youtube fand ich ein Science-Slam-Video zu Wirtschafts-Informatik,
	bei dem das Stichwort "Warenkorbanalyse" fiel. Interessante Sache 
	das, also habe ich das Beispiel aus dem Video in MySQL nachgebaut.
*/

create table einkauf
(
	e_id int,
	prod_id int
);

create table produkt
(
	prod_id int,
	name varchar (255)
);

insert into produkt ( prod_id, name ) values ( 1 , 'Cola');
insert into produkt ( prod_id, name ) values ( 2 , 'Milch');
insert into produkt ( prod_id, name ) values ( 3 , 'Bier');
insert into produkt ( prod_id, name ) values ( 4 , 'Pizza');
insert into produkt ( prod_id, name ) values ( 5 , 'Pampers');

insert into einkauf ( e_id, prod_id ) values ( 1, 1 );
insert into einkauf ( e_id, prod_id ) values ( 1, 2 );
insert into einkauf ( e_id, prod_id ) values ( 1, 4 );
insert into einkauf ( e_id, prod_id ) values ( 2, 2 );
insert into einkauf ( e_id, prod_id ) values ( 2, 5 );
insert into einkauf ( e_id, prod_id ) values ( 3, 1 );
insert into einkauf ( e_id, prod_id ) values ( 3, 4 );
insert into einkauf ( e_id, prod_id ) values ( 4, 2 );
insert into einkauf ( e_id, prod_id ) values ( 4, 3 );
insert into einkauf ( e_id, prod_id ) values ( 5, 1 );
insert into einkauf ( e_id, prod_id ) values ( 5, 3 );

SELECT * FROM produkt;
SELECT * FROM einkauf;

/*
	Alle Produkt-Paare aus den Einkäufen herausfinden, d.h.
	für jeden Einkauf jedes Produkt mit allen anderen
	Produkten dieses Einkaufs kombinieren.
	NB: Wenn ein Produkt innerhalb eines Einkaufs mehrfach
	vorkommt, dann muss das vorher gruppiert werden. Ich gehe
	hier oBdA davon aus, dass jedes Produkt nur einmal in jedem
	Einkauf vorkommt.
*/

SELECT ek1.e_id, ek1.prod_id, ek2.prod_id
FROM einkauf ek1, einkauf ek2
where ek1.e_id = ek2.e_id;

/*
	An dieser Stelle spielt die Einkaufsnummer keine Rolle.
	Hauptsache, ich kenne alle Paarungen.
	Das sind die Paarungen, die vorkommen.
	Was ist mit denen, die nicht vorkommen?
*/

SELECT ek1.prod_id, ek2.prod_id
FROM einkauf ek1, einkauf ek2
where ek1.e_id = ek2.e_id;

/*
	So gruppieren, dass ich sehe, welches Paar wie oft
	vorkommt.
	Die Zeilen, bei denen ek1.prod_id = ek2.prod_id ist,
	geben an, wie oft das Produkt mit der ek1.prod_id
	insgesamt gekauft wurde. 
*/

SELECT ekl.prod_id as prodA, ekr.prod_id as prodB, count(ekl.prod_id) as anzahl
FROM einkauf ekl, einkauf ekr
where ekl.e_id = ekr.e_id
group by prodA, prodB;

/*
	Welche Kombinationen fehlen für das jeweilige Produkt?
	Nun, vergleiche die obige Tabelle mit dem kartesischen Produkt
	der Waren.
*/

SELECT * 
FROM (
	SELECT ekl.prod_id as prodA, ekr.prod_id as prodB, count(ekl.prod_id) as anzahl
	FROM einkauf ekl, einkauf ekr
	where ekl.e_id = ekr.e_id
	group by prodA, prodB
) tupels 
right outer join 
(
	select prl.prod_id as prodR, prr.prod_id as prodL
	from produkt prl, produkt prr
) alleWarenTupel
on tupels.prodA = alleWarenTupel.prodR
and tupels.prodB = alleWarenTupel.prodL;

/*
	Von obiger Tabelle zeige ich prodA und prodB nicht an,
	denn prodR und prodL sind bedeutungsgleich. Wichtig ist,
	dass ich für die jeweilige Paarung die Anzahl kenne.
*/

SELECT alleWarenTupel.prodL, alleWarenTupel.prodR, tupels.anzahl
FROM (
	SELECT ekl.prod_id as prodA, ekr.prod_id as prodB, count(ekl.prod_id) as anzahl
	FROM einkauf ekl, einkauf ekr
	where ekl.e_id = ekr.e_id
	group by prodA, prodB
) tupels 
right outer join 
(
	select prl.prod_id as prodR, prr.prod_id as prodL
	from produkt prl, produkt prr
) alleWarenTupel
on tupels.prodA = alleWarenTupel.prodR
and tupels.prodB = alleWarenTupel.prodL;

/*
	Wie bekomme ich eine hübsche Tabelle?
	Ich hätte gerne die Spalte "anzahl" in Fünfer-Pakete aufgeteilt, so
	dass die ersten fünf Werte hintereinander stehen, in der zweiten
	Zeile die zweiten fünf Werte usw.
	Für MSSQL scheint es unkompliziert, siehe
	http://www.brighthub.com/internet/web-development/articles/91895.aspx
	Für MySQL ist es umständlich(er)
	http://www.onlamp.com/pub/a/onlamp/2003/12/04/crosstabs.html

	Schließlich ein Dank an
	http://en.wikibooks.org/wiki/MySQL/Pivot_table

	Wermutstropfen: Die Spaltennamen sind "hardcodiert". Bei großem
	Produktumfang wird das eine Qual :/, also doch besser in eine 
	Tabellenkalkulation bringen.
*/

select prodL, ProdName,
sum(anzahl*(1-abs(sign(prodR-1)))) as 'Cola',
sum(anzahl*(1-abs(sign(prodR-2)))) as 'Milch',
sum(anzahl*(1-abs(sign(prodR-3)))) as 'Bier',
sum(anzahl*(1-abs(sign(prodR-4)))) as 'Pizza',
sum(anzahl*(1-abs(sign(prodR-5)))) as 'Pampers'
from (
	SELECT alleWarenTupel.prodL, alleWarenTupel.ProdName, alleWarenTupel.prodR, tupels.anzahl
		FROM (
		SELECT ekl.prod_id as prodA, ekr.prod_id as prodB, count(ekl.prod_id) as anzahl
		FROM einkauf ekl, einkauf ekr
		where ekl.e_id = ekr.e_id
		group by prodA, prodB
	) tupels 
	right outer join 
	(
		select prl.prod_id as prodR, prr.prod_id as prodL, prr.name as ProdName
		from produkt prl, produkt prr
	) alleWarenTupel
	on tupels.prodA = alleWarenTupel.prodR
	and tupels.prodB = alleWarenTupel.prodL
) pivot
group by prodL, ProdName;

select ProdName, Cola, Milch, Bier, Pizza, Pampers
from 
(
	select prodL, ProdName,
	sum(anzahl*(1-abs(sign(prodR-1)))) as 'Cola',
	sum(anzahl*(1-abs(sign(prodR-2)))) as 'Milch',
	sum(anzahl*(1-abs(sign(prodR-3)))) as 'Bier',
	sum(anzahl*(1-abs(sign(prodR-4)))) as 'Pizza',
	sum(anzahl*(1-abs(sign(prodR-5)))) as 'Pampers'
	from (
		SELECT alleWarenTupel.prodL, alleWarenTupel.ProdName, alleWarenTupel.prodR, tupels.anzahl
			FROM (
			SELECT ekl.prod_id as prodA, ekr.prod_id as prodB, count(ekl.prod_id) as anzahl
			FROM einkauf ekl, einkauf ekr
			where ekl.e_id = ekr.e_id
			group by prodA, prodB
		) tupels 
		right outer join 
		(
			select prl.prod_id as prodR, prr.prod_id as prodL, prr.name as ProdName
			from produkt prl, produkt prr
		) alleWarenTupel
		on tupels.prodA = alleWarenTupel.prodR
		and tupels.prodB = alleWarenTupel.prodL
	) pivot
	group by prodL, ProdName
) nice;

-- Aufräumen;
--
drop table einkauf;
drop table produkt;

-- Ende.

/*
	Nummeriere die Datensätze
	*************************

	Gegeben sei eine Tabelle aysx mit einem Primary Key. 
        Die Werte für den PK müssen nicht fortlaufend sein. 

        Aufgabe: Nummeriere die Datensätze 
	vom kleinsten bis zum größsten PK-Wert.

	Das folgende MySQL-Script zeigt exemplarisch, wie 
        das machbar ist. Bedenke, dass die Bestimmung des 
        Rangs (gidf) nicht auf eine "echte" Tabelle beschränkt 
        ist, sondern auch mit einem View und dem Ergebnis
	einer Abfrage durchgeführt werden kann.

	Beispieltext: aysx Matthias Kopp 2013
*/

create table aysx ( 
	id int PRIMARY KEY, 
	remark varchar ( 64 ));

/*
	Tabelle mit Beispieldaten füllen.
*/

insert into aysx ( id, remark ) values ( 1,	'Eins' );
insert into aysx ( id, remark ) values ( 512,	'FiveOneTwo' );
insert into aysx ( id, remark ) values ( 21,	'Antv' );
insert into aysx ( id, remark ) values ( 42,	'Antwort' );
insert into aysx ( id, remark ) values ( 23,	'whatever' );

/*
	Wesentlich ist der self-join. Der PK-Constraint garantiert,
	dass jede aysx.id nur einmal vorkommt. Eine id ist immer
	gleich ihrer selbst und alle anderen sind entweder größer
	oder kleiner. Die where-Bedingung sucht diejenigen Datensätze
	zusammen, die kleiner-gleich einer id sind - und das für
	alle ids. Schließlich wird für jede id eine Gruppe gebildet
	und die Anzahl der Elemente in der Gruppe ist eben der Rang.
*/

select count(*) as rang, t2.id
from aysx t1, aysx t2
where t1.id <= t2.id
group by t2.id;

/*
	Ohne Gruppierung und Zählen - die "expandierte" Form.
	Hier kann man nachvollziehen, welche Elemente vorhanden sind.
	Zähle und gruppiere selbst!
*/

select t1.id, t2.id
from aysx t1, aysx t2
where t1.id <= t2.id
order by t2.id

/* Auräumen. */
drop table aysx;