ABSTRACT: fortsetzung der mysql einfuehurng. joins werden erklaert und
          grupierungen mit aggregatfunktionen. mysqldump zum backup von
          mysql wird erklaert.

AUDIENCE: junior admin
SYSTEM:   any unix
SECTION:  basic unix commands
AUTHOR:   mond <fs@mond.at>
COPYRIGHT: GNU Free Documentation Licence http://www.gnu.org/licenses/fdl.txt

  bei komplexeren anfragen verknuepft man 2 oder mehr tabellen in einer
  abfrage. anknuepfend an unsere "drinks" datenbank wollen wir nun die
  verkauften cocktails in einer tablle "verkauf" speichern.

create table verkauf (name char(30) not null default '', tag date not null, 
                      menge integer);  
  
alter table verkauf index(name);
alter table verkauf add unique(name,tag);

  hier verteilt sich der primaere schluessel auf 2 felder. d.h. es kann
  immer nur eine verkaufszahl zu einem getraenk an einem tag geben.
 
insert into verkauf (name,tag,menge) values('mojito','2002-02-01',5);
insert into verkauf (name,tag,menge) values('mojito','2002-02-02',7);
insert into verkauf (name,tag,menge) values('white russian','2002-02-02',2);
insert into verkauf (name,tag,menge) values('white russian','2002-02-01',11);
insert into verkauf (name,tag,menge) values('tequila sunrise','2002-02-02',1);
  
  jetzt koennen wir z.b. eine verknuepfte abfrage machen:

select * from verkauf join cocktails where verkauf.name=cocktails.name ;

  alternativ kann "LEFT JOIN" und "ON" verwenden. man schreibt:

select * from verkauf left join cocktails on verkauf.name=cocktails.name   ;
 
  oder wenn die felder die man fuer den join verwendet in beiden tabellen
  gleich heissen:

select * from verkauf left join cocktails using(name)  ;

  interessant ist wie sich das join verhaelt wenn auf einer seite kein
  datensatz existiert. wir fuegen daher zum testen einen drink in die
  "verkauf" tabelle die noch keinen datensatz in der cocktails tabelle hat:

insert into verkauf (name,tag,menge) values('b52',now(),7);
  
  (die funktion now() liefert aktuelles datum und zeit).  
  
select * from verkauf left join cocktails using(name)  ;

  liefert jetzt an den stellen an denen die werte aus cocktails sind den
  wert NULL.

  gibt man bei einem join keinerlei kriterian an so entsteht das "cross
  produkt" beider tabellen das fuer jeden datensatz auf der einen seite alle
  datensaetzte auf der anderen seite hat. (d.h. wenn die eine tabelle 200
  und die andere 300 datensaetzte hat sind das dann 60000 zeilen)


select * from cocktails left join verkauf using(name)  ;

  obiges join wuerde aber das neue feld ignorieren (die linke tabelle hat
  also hier vorrang)

select * from verkauf left join cocktails using(name) where isnull(cocktails.name) ;
 
  findet also genau die datensaetzte aus verkauf zu denen noch kein name in
  cocktails angelegt wurde.

select verkauf.name, tag, menge * preis as umsatz from cocktails left join
  verkauf using(name)  ;

  wuerde uns aus menge*preis immer den umsatz pro cocktail berechnen. das
  "AS" sagt uns wie die so berechnete spalten in der ausgabe heissen soll.

  oft will man gar nicht alle daten aus einer tabelle abfragen sondern nur
  die summer einer spalze oder die anzahl der datensaetzte wissen die eine
  abfrage liefert. solche abfragen macht man mithilfe von
  aggregatfunktionen. z.b: 

select count(*) from verkauf ;

  liefert die zahl der datensaetzte in verkauf.

select sum(menge) from verkauf ;

  berechnet die gesamtsumme aller verkaufter cocktails.  mit der GROUP BY
  klausel kann man die aggregatfunktionen anstatt ueber die ganze tabelle
  (oder einen mit WHERE bestimmten teil) jeweils ueber eine gruppe von
  datensaetzten laufen lassen die einen gemeinsamen wert in einem oder
  mehreren feldern haben. z.b.:

select tag,sum(menge) from verkauf group by tag ;

  berechnet jeweils die menge der verkauften cocktails gruppiert nach tagen.
  
select tag,sum(menge*preis) from verkauf left join cocktails 
                            using(name) group by tag ;
  
  wuerde den jeweiligen tagesumsatz berechnen.  zu beachten bei komplexeren
  selects ueber mehrere grosse tabellen ist dass die felder die zum
  verknuepfen von tabellen verwendet werden nach moeglichkeit einen index
  haben sollten damit nicht die ganze tabelle jedesmal durchgesucht werden
  muss.

 
select verkauf.name,sum(menge*preis) from verkauf left join cocktails
       using(name) group by name ;

  wuerde die umsaetzte nach cocktail gruppiert ausgeben. hat man mehrere
  felder nach denen man grupieren will so gibt man diese mit beistrich
  getrennt an.

  manchmal will man datensaetze mit einer eindeutigen nummer versehen. diese
  eigent sich im allgemeinen auch gut als primary key.

  z.b:

create table autotest (lfdnr integer not null auto_increment, 
          primary key(lfdnr), text varchar(80));

insert into autotest (text) values('bla');
insert into autotest (text) values('bli');   

  im lfdnr werden jetzt automatisch eindeutige nummern eingesetzt:

select * from autotest ;
+-------+------+
| lfdnr | text |
+-------+------+
|     1 | bla  |
|     2 | bli  |
+-------+------+

  wollte man versuchen dieses verhalten haendisch nachzuprogrammieren in dem
  man selbst die nummern vergiebt so waere das gar nicht so trival: es
  koennten ja mehrere clients gleichzeitig versuchen daten einzufuegen. der
  eine denkt 3 waere die naechste freie nummer. der andere auch. beide
  wuerden gleichzeitig versuchen die nummer 3 zu verwenden... das
  auto_increment feature ist in dieser hinsicht sehr nuetzlich.

  das problem konsistente daten zu haben auch wenn mehrere leute
  gleichzeitig an einer datenbank daten aendern ist nicht immer trival. 
  manche datenabanken (und zum teil auch mysql in neueren versionen) bieten
  die moeglichkeit hier mehrere operationen zusammenzufassen die entweder
  als ganzes ausgefuehrt werden oder gar nicht. man nennt dies
  "transactions". mit gut durchdachten sql befehlen kann man dies
  notwendigkeit solcher transactions aber in den allermeisten faellen
  vermeiden. (das mysql manual erklaert wie man hier am besten vorgeht)

  eines der nettesten features von mysql ist die praktische art wie man es
  backupen kann. am shell prompt:


mysqldump -uroot -p drinks > drinks.dump

  wuerde unsere datenbank "drinks" in ein textfile drinks.dump schreiben. 
  das textfile enthaelt dann alle CREATE befehle die zum erzeugen der
  tabellen und alle INSERT befehle die zum fuellen der datenbank mit daten
  notwendig sind.

  um die daten wieder herzusteleln (z.b. auch in einer anderen datenbank
  oder auf einem anderen host ) koennen wir das dump file direkt in den
  mysql client stopfen:

mysqladmin -uroot -p create drinksneu 
mysql -uroot -p drinksneu < drinks.dump

  drinksneu ist jetzt eine kopie unserere drinks datenbank.


EXERCISES:

  * lege dir 2 tabellen an und verknuepfe sie anhand eines feldes. verwende
    dabei verschiedene formen der join statements. was passiert wenn ein
    schluessel in der einen tabelle existiere und in der anderen nicht?

  * verwende aggregatfunkeionen wie SUM(), COUNT(), MIN(), MAX() an
    verschiedenen feldern der tabelle.

  * verwende GROUP BY in zusammenhang mit aggregat funktionen

  * wie legt man ein feld an dessen wert automatisch hochgezaehlt wird?

  * erzeuge ein backup deiner datetenbank mit mysqldump. schau dir das
    backup mit less an. spiele das backup unter einem anderen datenbanknamen
    wieder in mysql ein.


REFERENCES:

man mysql

/usr/share/doc/mysql-gpl-doc/manual_toc.html

 (oder an andere stelle je nach distribution)

file:///usr/share/doc/mysql-gpl-doc/manual.html

http://www.mysql.com/documentation/