Spinnendatenbank

Anleitungen

• In bestehende Tabelle Fangdaten nachladen

SQL-Sammlung für Spinnen-Datenbank

Habitatbindung einer Art

SELECT Art, btyp, Bindung
FROM info_Habitatbindung, info_rl_habitat
WHERE Art="XXXXXXXX"
AND Habitattyp=mnemo

Liste nach Datum Gruppieren

Problem: Datum1 und Datum liegen als VChar vor und eine Sortierung würde nach den Tagen vorgenommen. Für korrekte Sortierung muss der Datumsstring in ein Datum umgewandelt werden: STR_TO_DATE(datestring, Format)

Die in MariaDB eingebaute Prozedur STR_TO_DATE() erwartet als ersten Parameter den Datumsstring und als zweiten einen Formatstring, welcher definiert, welches Format der Datumsstring hat.

SELECT * FROM `data_raw_idx` 
WHERE datum LIKE "%2024"
AND OrtID=314 -- = NSG Höltigbaum
GROUP BY STR_TO_DATE(datum, '%d.%m.%Y');

Welche Orte gibt es in Projekt mit ProjektID=1?

Mit Angabe der OrtID

SELECT OrtID, Ortsname 
FROM meta_Orte, ref_Projekte_Orte
WHERE ProjektID=1
AND OrtID=IDOrt
GROUP BY Ortsname;

Ortsliste mit zugehörigen Biotopen:

SELECT o.ortsname, d.biotop
FROM data_raw_idx AS d, ref_Projekte_Orte AS r, meta_Orte AS o
WHERE r.ProjektId=1 -- <== Variable hier!
AND d.OrtId=r.OrtId
AND r.OrtId=o.idOrt
GROUP BY o.ortsname, d.biotop;

Artenliste nach Standorten eines Projektes

Hier: ProjektID=1 (Green roof, Hamburg)

SELECT Data.Artname, O.Ortsname AS Ort
FROM data_raw_idx AS Data, meta_Orte AS O, ref_Projekte_Orte AS R
WHERE R.ProjektID=1 
AND R.OrtID=O.IdOrt
AND Data.OrtID=R.OrtID
GROUP BY Ort, Artname;

Das selbe mit Ausgabe der Individuenzahl:

SELECT Data.Artname, SUM(Data.mm+Data.ww+Data.juv+Data.ind) AS Individuen, O.Ortsname AS Ort
FROM data_raw_idx AS Data, meta_Orte AS O, ref_Projekte_Orte AS R
WHERE R.ProjektID=1 
AND R.OrtID=O.IdOrt
AND Data.OrtID=R.OrtID
GROUP BY Ort, Artname;

Gesamtartenliste des Projektes ohne Angabe von Orten oder Individuen:

SELECT Artname
FROM data_raw_idx AS d, ref_Projekte_Orte AS r
WHERE r.ProjektID=1 
AND r.OrtID=d.OrtID
GROUP BY Artname;

Matrix-Abfrage: Gesamtartenzahlen nach Standorten und Jahren

SELECT SUM(mm+ww+juv+ind) AS Anzahl, Ortsname, SUBSTR(datum, 7, 4) AS Jahr
FROM data_raw_idx AS d, meta_Orte AS o, ref_Projekte_Orte AS p
WHERE p.ProjektID =1
AND p.OrtID=o.idOrt
AND d.OrtID=o.idOrt
GROUP BY Ortsname, Jahr;

In alternativer Sortierung nach Orten:

SELECT O.Ortsname AS Ort, Data.Artname, SUM(Data.mm+Data.ww+Data.juv+Data.ind) AS Individuen
FROM data_raw_idx AS Data, meta_Orte AS O, ref_Projekte_Orte AS R
WHERE R.ProjektID=1 
AND R.OrtID=O.IdOrt
AND Data.OrtID=R.OrtID
GROUP BY Ort, Artname;

Ortsname, Artname, Anzahl, Jahr:

SELECT Ortsname, Artname, SUM(mm+ww+juv+ind) AS Anzahl, SUBSTR(datum, 7, 4) AS Jahr
FROM data_raw_idx AS d, meta_Orte AS o, ref_Projekte_Orte AS p
WHERE p.ProjektID =1
AND p.OrtID=o.idOrt
AND d.OrtID=o.idOrt
GROUP BY Ortsname, Jahr, Artname;

UPDATE

Tabelle mit Daten einer andere Tabelle füllen

Hier: In Tabelle info_Habitatbindung soll das Feld ArtID mit den Ids aus tax_Arten gesetzt werden:

UPDATE info_Habitatbindung 
INNER JOIN tax_Arten ON Art=name
SET ArtId=IdArt;

INNER JOIN im Prinzip:

UPDATE tabelle1 
INNER JOIN tabelle2 ON tabelle1.id = tabelle2.id 
SET tabelle1.feld1 = tabelle2.wert1, 
    tabelle1.feld2 = tabelle2.wert2 
WHERE tabelle2.status = 'aktuell';

Kontroll-Abfragen

Fallenwechsel-Daten für Standort n; hier n= 66 (Feldstr. Bunker)

SELECT datum1, datum FROM `data_raw_idx` 
WHERE OrtID=66
GROUP BY STR_TO_DATE(datum1, '%d.%m.%Y');

Das selbe mit Angabe der Differenz in Tagen

SELECT datum1, datum, DATEDIFF(STR_TO_DATE(datum, '%d.%m.%Y'),STR_TO_DATE(datum1, '%d.%m.%Y')) AS Tage
FROM `data_raw_idx` 
WHERE OrtID=66
GROUP BY STR_TO_DATE(datum1, '%d.%m.%Y');

Info

DATEDIFF(expr1, expr2)

https://mariadb.com/docs/server/reference/sql-functions/date-time-functions/datediff