KK10_7 Einfache Datenabfragen

Datenbanken
7 Einfache Datenabfragen
Karl Meier
[email protected]
26.10.2010
Inhalt






Daten abfragen
Bedingungen definieren
Operatoren und logische Ausdrücke
Ergebnisse gruppieren
Ergebnisse sortieren
Übung
26.10.2010
7 Einfache Datenabfragen
2
1
Datenabfrage
Gezielte Abfrage gespeicherter Information
SELECT
Komplexeste SQL Anweisung
In der Praxis am häufigsten verwendet
Qualität der Anfrage bestimmt das Resultat
26.10.2010
7 Einfache Datenabfragen
3
Anzeige aller Attribute
SELECT * FROM
mysql> SELECT * FROM t_ma;
+-------+--------+----------------+------+------------+----+
| vname | name
| str
| plz | ort
| id |
+-------+--------+----------------+------+------------+----+
| Otto | Kaiser | Bahnstr. 3
| 5600 | Brugg
| 1 |
| Hans | Huber | Ahornweg 12
| 8765 | Engi
| 2 |
| Eva
| Lustig | Paradiesstr. 7 | 7000 | Chur
| 3 |
| Fritz | Moll
| Scherzweg 13
| 7000 | Chur
| 4 |
| Heidi | Huber | Auf der Alp
| 7304 | Maienfeld | 5 |
| Rolf | Bader | Moosstrasse 42 | 8105 | Regensdorf | 6 |
+-------+--------+----------------+------+------------+----+
6 rows in set (0.00 sec)
26.10.2010
7 Einfache Datenabfragen
4
2
Anzeige einzelner Attribute
SELECT … FROM
mysql> SELECT vname, name FROM t_ma;
+-------+--------+
| vname | name
|
+-------+--------+
| Otto | Kaiser |
| Hans | Huber |
| Eva
| Lustig |
| Fritz | Moll
|
| Heidi | Huber |
| Rolf | Bader |
+-------+--------+
6 rows in set (0.00 sec)
26.10.2010
7 Einfache Datenabfragen
5
Spalten umbenennen
SELECT … AS …
mysql> SELECT vname AS Vorname, name AS Name FROM t_ma;
+---------+--------+
| Vorname | Name
|
+---------+--------+
| Otto
| Kaiser |
| Hans
| Huber |
| Eva
| Lustig |
| Fritz
| Moll
|
| Heidi
| Huber |
| Rolf
| Bader |
+---------+--------+
6 rows in set (0.00 sec)
26.10.2010
7 Einfache Datenabfragen
6
3
Konstante Werte
SELECT ″…″ AS …
mysql> SELECT ″Mitarbeiter″ AS status, vname, name
-> FROM t_ma;
+-------------+--------+------------+
| status
| name
| vname
|
+-------------+--------+------------+
| Mitarbeiter | Kaiser | Otto
|
| Mitarbeiter | Huber | Hans
|
| Mitarbeiter | Lustig | Eva
|
| Mitarbeiter | Moll
| Fritz
|
| Mitarbeiter | Huber | Heidi
|
| Mitarbeiter | Bader | Rolf
|
+-------------+--------+------------+
6 rows in set (0.00 sec)
26.10.2010
7 Einfache Datenabfragen
7
Operatoren
Folgende Operatoren können für
Berechnungen in SQL verwendet werden:
+
*
/
26.10.2010
Subtraktion
Addition
Multiplikation
Division
7 Einfache Datenabfragen
8
4
Spezialfälle 1
SELECT kann auch losgelöst von Datenbanken
und Tabellen verwendet werden:
mysql> SELECT 2*4;
+-----+
| 2*4 |
+-----+
|
8 |
+-----+
1 row in set (0.27 sec)
mysql> SELECT now();
+---------------------+
| now()
|
+---------------------+
| 2002-11-25 21:26:53 |
+---------------------+
1 row in set (0.00 sec)
Berechnungen
Zeit
26.10.2010
7 Einfache Datenabfragen
9
Die DUAL Tabelle in Oracle
oracle> DESCRIBE dual;
Name
Null
Type
------------ ------ ----------DUMMY
VARCHAR2(1)
oracle> SELECT * FROM dual;
+---+
| D |
+---+
| X |
+---+
1 row in set (0.02 sec)
Auch wenn diese System-Tabelle geändert
werden kann, sollte dies tunlichst unterlassen
werden!!!
26.10.2010
7 Einfache Datenabfragen
10
5
Spezialfälle 2
mysql> SELECT COUNT(*) FROM t_ma;
+----------+
| count(*) |
+----------+
|
9 |
+----------+
1 row in set (0.00 sec)
Ausgabe der Anzahl Datensätze in einer Tabelle
( Wird im Kapitel 9 Funktionen ausführlich behandelt )
26.10.2010
7 Einfache Datenabfragen
11
Syntax
SELECT [DISTINCT] * | spalten FROM tabelle
[WHERE bedingung]
[GROUP BY spalten [HAVING bedingung]]
[ORDER BY spalten [ASC|DESC] ]
[LIMIT [start, ] anzahl ];
• Beginn der Abfrage mit SELECT
• Doppelte Datensätze vermeiden mit DISTINCT
• Nach FROM folgt die gewünschte Tabelle
26.10.2010
7 Einfache Datenabfragen
12
6
Optionale Parameter
WHERE
Bedingung, welche die Auswahl einschränkt
GROUP BY
Daten werden nach Feld(ern) gruppiert
HAVING
Gruppierte Abfrage wird durch Bedingung eingeschränkt
ORDER BY
Sortierung nach Datenfeld(ern)
LIMIT
Anzahl zurückgelieferter Datensätze wird beschränkt
26.10.2010
7 Einfache Datenabfragen
13
WHERE
Einschränkung der Resultate mit Bedingung(en)
mysql> SELECT name, vname FROM t_ma
-> WHERE name>="m";
> grösser als
+--------+------------+
| name
| vname
|
< kleiner als
+--------+------------+
>= grösser/gleich
| Moll
| Fritz
|
| Moll
| Fritz jun. |
<= kleiner/gleich
| Zubler | Heinz
|
<> / != ungleich
+--------+------------+
LIKE String Vergleich 3 rows in set (0.12 sec)
= gleich
26.10.2010
7 Einfache Datenabfragen
14
7
WHERE … LIKE
LIKE Vergleiche sind bei grossen Tabellen in der Regel sehr
aufwändig und langsam! Es müssen alle Datensätze gelesen
und analysiert werden.
mysql> SELECT name, vname FROM t_ma
-> WHERE name LIKE "%er";
+--------+---------+
| name
| vname
|
+--------+---------+
| Kaiser | Otto
|
| Huber | Hans
|
| Huber | Heidi
|
| Bader | Rolf
|
| Zubler | Heinz
|
| Huber | Andreas |
+--------+---------+
6 rows in set (0.00 sec)
26.10.2010
7 Einfache Datenabfragen
15
Bedingungen
Bedingung
Vergleich
Beispiel
Erklärung
Vergleich Datenfeld mit
vorgegebenen Wert
Bereich
preis < 100
name = „Huber“
preis BETWEEN 50 AND 100
Element
ort IN („Chur“, „Zuerich“)
Wert in einer Liste
Muster
name LIKE „%er“
name LIKE „____“
Wertübereinstimmung
mit Muster ( % , _ )
Nullwert
preis IS NULL
ISNULL(preis)
Feldinhalt NULL
Wert innerhalb eines
Bereiches
Verknüpfung mehrerer
preis < 100 AND preis > 50
Logische
Operatoren name = „Koch“ OR name = „Moll“ Bedingungen
NOT (name LIKE „M%“)
26.10.2010
7 Einfache Datenabfragen
16
8
GROUP BY
Gruppierte Abfragen
• Sinn dieser Abfrage?
• Abhängig vom Zufall, bzw.
• Abhängig von Sortierung
Aggregatsfunktionen
COUNT, SUM, MIN, MAX, AVG
machen Abfrage erst sinnvoll
(Kapitel 10: Funktionen)
26.10.2010
mysql> SELECT name, vname
-> FROM t_ma
-> GROUP by name;
+--------+-------+
| name
| vname |
+--------+-------+
| Bader | Rolf |
| Bond
| James |
| Eagle | Edi
|
| Huber | Hans |
| Kaiser | Otto |
| Lustig | Eva
|
| Moll
| Fritz |
| Zubler | Heinz |
+--------+-------+
8 rows in set (0.00 sec)
7 Einfache Datenabfragen
17
GROUP BY
mysql> SELECT name, COUNT(vname) AS anzahl
-> FROM t_ma
-> GROUP BY name;
+--------+--------+
| name
| anzahl |
+--------+--------+
| Bader |
1 |
| Bond
|
1 |
| Eagle |
1 |
| Huber |
3 |
| Kaiser |
1 |
| Lustig |
1 |
| Moll
|
2 |
| Zubler |
1 |
+--------+--------+
8 rows in set (0.00 sec)
26.10.2010
7 Einfache Datenabfragen
18
9
HAVING
HAVING erlaubt es, Bedingungen für die einzelnen Zeilen
des GROUP BY Statements festzulegen
mysql> SELECT name, count(vname) FROM t_ma
-> GROUP BY name
-> HAVING count(vname) > 1;
+-------+--------------+
| name | count(vname) |
+-------+--------------+
| Huber |
3 |
| Moll |
2 |
+-------+--------------+
2 rows in set (0.00 sec)
26.10.2010
7 Einfache Datenabfragen
19
ORDER BY
Ergebnisse sortieren
• ASC (ascending) Aufsteigend – Default
• DESC (descending) Absteigend
mysql> SELECT name, vname FROM t_ma ORDER BY name ;
+--------+------------+
| name
| vname
|
+--------+------------+
| Bader | Rolf
|
| Bond
| James
|
…
| Moll
| Fritz jun. |
| Zubler | Heinz
|
+--------+------------+
10 rows in set (0.00 sec)
26.10.2010
7 Einfache Datenabfragen
20
10
GROUP BY / ORDER BY
mysql> SELECT name, COUNT(vname) AS anzahl
-> FROM t_ma
-> GROUP BY name
-> ORDER BY anzahl DESC, name;
+--------+--------+
| name
| anzahl |
+--------+--------+
| Huber |
3 |
| Moll
|
2 |
| Bader |
1 |
| Bond
|
1 |
| Eagle |
1 |
| Kaiser |
1 |
| Lustig |
1 |
| Zubler |
1 |
+--------+--------+
8 rows in set (0.00 sec)
26.10.2010
7 Einfache Datenabfragen
21
ORDER BY RAND()
Ausgabe der Ergebnisse in zufälliger Reihenfolge
mysql> SELECT * FROM t_ma ORDER BY RAND() LIMIT 3;
+-------+--------+----------------+------+------------+----+
| vname | name
| str
| plz | ort
| id |
+-------+--------+----------------+------+------------+----+
| Otto | Kaiser | Bahnstr. 3
| 5600 | Brugg
| 1 |
| Rolf | Bader | Moosstrasse 42 | 8105 | Regensdorf | 6 |
| Hans | Huber | Ahornweg 12
| 8765 | Engi
| 2 |
+-------+--------+----------------+------+------------+----+
3 rows in set (0.00 sec)
Drei zufällige Datensätze aus der Tabelle t_ma
26.10.2010
7 Einfache Datenabfragen
22
11
LIMIT n
Limitierung der Anzahl Ergebnissätze
• Rechenzeit
• Speicher- und Netzwerkkapazität
mysql> SELECT * FROM t_ma LIMIT 3;
+-------+--------+----------------+------+-------+----+
| vname | name
| str
| plz | ort
| id |
+-------+--------+----------------+------+-------+----+
| Otto | Kaiser | Bahnstr. 3
| 5600 | Brugg | 1 |
| Hans | Huber | Ahoernweg 12
| 8765 | Engi | 2 |
| Eva
| Lustig | Paradiesstr. 7 | 7000 | Chur | 3 |
+-------+--------+----------------+------+-------+----+
3 rows in set (0.00 sec)
26.10.2010
7 Einfache Datenabfragen
23
LIMIT offset, n
Limitierung der Anzahl Ergebnissätze mit Offset
• Zählung der Datensätze beginnt mit 0
• Offset 3 bedeutet also, Beginn mit viertem Datensatz
mysql> SELECT * FROM t_ma LIMIT 3, 3;
+-------+-------+----------------+------+------------+----+
| vname | name | str
| plz | ort
| id |
+-------+-------+----------------+------+------------+----+
| Fritz | Moll | Scherzweg 13
| 7000 | Chur
| 4 |
| Heidi | Huber | Auf der Alp
| 7304 | Maienfeld | 5 |
| Rolf | Bader | Moosstrasse 42 | 8105 | Regensdorf | 6 |
+-------+-------+----------------+------+------------+----+
3 rows in set (0.01 sec)
26.10.2010
7 Einfache Datenabfragen
24
12
Übersicht Kapitel 7
Befehl
Aktion
SELECT * FROM tabelle;
Datenabfrage
SELECT * FROM tabelle WHERE bedingung;
Abfrage mit
Suchbedingung
SELECT … WHERE daten LIKE „muster“;
Musterabfrage
SELECT … WHERE daten BETWEEN lo AND hi;
Bereichsabfrage
SELECT … WHERE daten IN (werteliste);
Werteliste
SELECT … WHERE bedingung1 AND bedingung2 …;
Logische
Verknüpfung
SELECT … WHERE NOT bedingung;
Negierung
SELECT … ORDER BY spalte;
Sortierung
SELECT … GROUP BY spalte;
Gruppierung
SELECT … GROUP BY spalte HAVING bedingung;
Bedingte Gruppe
26.10.2010
7 Einfache Datenabfragen
25
Übungen (t_ma)
1.
Lassen Sie sich 12 Einträge Vorname Name anzeigen
Ohne Einschränkungen
Alphabetisch sortiert
Zufällig ausgewählt
a)
b)
c)
2.
3.
4.
5.
6.
7.
Nehmen Sie zusätzlich Postleitzahl und Ort hinzu
Benennen Sie die Spalten mit anderen Namen
Listen Sie nur die Mitarbeiter aus Zuerich und Chur auf
Gruppieren Sie die Mitarbeiter nach Ortschaften
Sortieren Sie die Ortschaften absteigend
In welchen Ortschaften wohnen mehrere Mitarbeiter
26.10.2010
7 Einfache Datenabfragen
26
13
26.10.2010
7 Einfache Datenabfragen
27
14