Beispiele - Comelio Medien

MySQL – Kurzreferenz Teil 2: Abfragen und Programmierung – Beispiele
Abfragen:
Abfragen:Einfache
EinfacheAbfragen
Abfragen
Beispiel 1: Abruf aller Zeilen und Spalten
SELECT * FROM Person;
Beispiel 2: Filterung mit WHERE und Sortierung mit ORDER BY, Sortierung
erst nach P_Nachname (DESC, absteigend) und dann nach P_Vorname
(automatisch aufsteigend)
SELECT
FROM
WHERE
ORDER
P_Vorname, P_Nachname
Person
year(P_Geburtstag) >= 1978
BY P_Nachname DESC, P_Vorname;
Beispiel 3: Ausblendung von Duplikaten im Ergebnis
SELECT DISTINCT A_Stadt
FROM Adresse;
Beispiel 4: Sortierung durch Angabe der Spaltennummer in der Spaltenliste
SELECT P_Vorname, P_Nachname
FROM Person
ORDER BY 2 DESC, 1;
Beispiel 5: Anwenden von Funktionen auf Spalten in SELECT und in WHERE,
Erstellung von Spaltenaliasnamen und ihre Verwendung in der Sortierung
SELECT
FROM
WHERE
ORDER
concat(P_Vorname, ‘ ‘, P_Nachname) AS Name
Person
concat(P_Vorname, ‘ ‘, P_Nachname) LIKE ‘%Ebenhof’
BY Name;
Beispiel 6: Verschiedene Operatoren in der WHERE-Klausel
SELECT
FROM
WHERE
AND
AND
P_Vorname, P_Nachname
Person
P_Nr BETWEEN 1 AND 5
month(P_Geburtstag) IN (4, 5, 6)
(P_Nachname LIKE ‘_ben%’
OR P_Nachname LIKE ‘Hülze%’);
Import
Importund
undExport
Export
Beispiel 1: Laden einer CSV-Datei als Import
LOAD DATA LOCAL INFILE ‘C:\\person.csv’
REPLACE INTO TABLE ‘person’
FIELDS TERMINATED BY ‘;’
ENCLOSED BY ‘“‘
ESCAPED BY ‘\\’
LINES TERMINATED BY ‘\r\n’
Beispiel 2: Schreiben einer CSV-Datei als Export
SELECT
INTO
FIELDS
LINES
FROM
P_Vorname, P_Nachname
OUTFILE ‘C:\\xampp\\mysql\\ergebnis.csv’
TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘“‘
TERMINATED BY ‘\r\n’
Person;
Abfragen:
Abfragen:Verknüpfungen
Verknüpfungen
Beispiel 1: Standardfall von zwei verknüpften
Tabellen. Verknüpfung über mehrere Spalten gelingt
mit ON t1.spalte1 = t2.spalte1 AND t1.spalte2 =
t2.spalte2.
SELECT P_Nachname AS Nachname,
K_Wert
AS Email
FROM Kontakt k INNER JOIN Person p
ON k.P_Nr = p.P_Nr
WHERE P_Nachname = ‘Ebenhof’
AND K_Art = ‘Email’;
Beispiel 2: Standardfall von drei (und mehr) verknüpften Tabellen
SELECT P_Nachname AS Nachname,
A_Stadt
AS Stadt,
K_Wert
AS Email
FROM Kontakt k INNER JOIN Person p
ON k.P_Nr = p.P_Nr
INNER JOIN Adresse a
ON a.P_Nr = p.P_Nr
WHERE P_Nachname = ‘Ebenhof’
AND K_Art = ‘Email’;
Beispiel 3: Standardfall der äußeren Verknüpfung, die
sowohl alle Daten aus Person (linke äußere Verknüpfung) sowie ggf. gefundene Datensätze aus Adresse
und Kontakt (ansonsten Auffüllung mit NULL).
SELECT P_Nachname AS Nachname,
A_Stadt
AS Stadt,
K_Wert
AS Email
FROM Person p LEFT OUTER JOIN Adresse a
ON a.P_Nr = p.P_Nr
LEFT OUTER JOIN Kontakt k
ON k.P_Nr = p.P_Nr;
Beispiel 4: Natürliche Verknüpfung über gleich lautende Spaltennamen (empfehlenswerte Modellierung).
SELECT P_Nachname AS Nachname,
K_Art
AS KontaktArt,
K_Wert
AS Wert
FROM Kontakt k NATURAL JOIN Person p;
Beispiel 5: Alternative Formulierung mit USING für
gleich lautende Spaltennamen
SELECT P_Nachname AS Nachname,
K_Art
AS KontaktArt,
K_Wert
AS Wert
FROM Kontakt k INNER JOIN Person p
USING (P_Nr);
Abfragen:
Abfragen:Gruppierung
Gruppierung
Beispiel 1: Anzahl der Personen gruppiert
nach dem Geburtsjahr und gefiltert bei mehr
als seiner Person pro Jahr
SELECT year(P_Geburtstag) AS Jahr,
COUNT(*)
AS Anzahl
FROM Person
GROUP BY Jahr
HAVING Anzahl > 1;
Beispiel-Tabellen
Beispiel-Tabellen
PERSON (P_Nr, P_Vorname, P_Nachname, P_Geburtstag)
ADRESSE (A_Nr, P_Nr, A_Strasse, A_PLZ, A_Stadt)
KONTAKT (K_Nr, P_Nr, K_Art, K_Wert )
Beispiel 2: Kombinierte Gruppierung von Jahr
und Stadt
SELECT year(P_Geburtstag) AS Jahr,
A_Stadt
AS Stadt,
COUNT(*)
AS Anzahl
FROM Person NATURAL JOIN Adresse
GROUP BY Jahr, A_Stadt;
Sichten
Sichten
Beispiel 1: Erstellung einer Sicht mit den OriginalSpaltennamen
CREATE VIEW v_Adressbuch AS
SELECT P_Nr, P_Vorname, P_Nachname
FROM Person;
Beispiel 2: Sicht mit neuen Spaltennamen, die im
Sicherheitskontext des Erstellers ausgeführt wird
CREATE
SQL SECURITY DEFINER
VIEW v_Adressbuch (Nr, Vorname, Nachname)
AS
SELECT P_Nr, P_Vorname, P_Nachname
FROM Person;
Beispiel 3: Abfrage der Sicht (genauso wie bei einer
Tabelle)
SELECT Vorname, Nachname
FROM v_Adressbuch
WHERE Nachname = ‘Ebenhof’;
Beispiel 4: Verwendung einer aktualisierbaren Sicht als
Ziel von DML-Operationen
INSERT INTO v_Adressbuch
VALUES (4, ‘Heidi’, ‘Stern’);
SQL-Programmierung:
SQL-Programmierung:Fallunterscheidungen
Fallunterscheidungen
Beispiel 1: Fallunterscheidung mit CASE und Prüfung auf
Gleichheit
SELECT P_Nr AS Person,
CASE K_Art
WHEN ‘Email’ THEN ‘Internet’
WHEN ‘Fest’ THEN ‘Telefon’
ELSE ‘Handy’
END AS K_Art,
K_Wert
FROM Kontakt;
Beispiel 2: Prüfung von Werten und Bereichen ohne
einfache Gleichheit
SELECT A_Stadt AS Stadt,
CASE
WHEN COUNT(*) < 5
THEN ‘A’
WHEN COUNT(*) BETWEEN 5 AND 10
THEN ‘B’
ELSE ‘C’
END AS Typ
FROM Adresse
GROUP BY A_Stadt;
MySQL – Kurzreferenz Teil 2: Abfragen und Programmierung – Beispiele
Datenmanipulation:
Datenmanipulation:Einfügen
Einfügen
Datenmanipulation:
Datenmanipulation:Ersetzen
Ersetzen
Beispiel 1: Einfügen in alle Spalten (keine Nennung der Spalten)
Beispiel 1: Ersetzen von Daten mit VALUES-Klausel
INSERT INTO ‘Adressverwaltung’.’Person’
VALUES
( 1, ‘Anton’, ‘Ebenhof’, ‘1968-04-06’ );
Beispiel 2: Ersetzen mit SET-Klausel und einzelner Spaltenzuweisung
REPLACE INTO ‘Adressverwaltung’.’Person’
SET P_Nr = 1, P_Vorname = ‘Anton’, P_Nachname = ‘Ebenhof’,
P_Geburtstag = ‘1978-04-06’ ;
INSERT INTO Person
( P_Nr , P_Vorname , P_Nachname )
VALUES
( 2, ‘Elvira’, ‘Hülzemann’),
( 3, ‘Anna-Mae’, ‘Bullock’);
Beispiel 3: Ersetzen aus Abfrage
REPLACE INTO ‘Adressverwaltung’.’Person’
SELECT Nr+4, Vorname, Nachname, GebDatum
FROM Person_Import;
Beispiel 3: Einfügen aus Abfragen (auto_increment-Schlüssel in Tabelle
Person hier einfacher, +3 steht für höchsten Schlüsselwert in Person)
Beispiel 4: Einfügen oder Aktualisierung
INSERT INTO Person
SET P_Nr = 1, P_Vorname = ‘Anton’,
P_Nachname = ‘Ebenhof’, P_Geburtstag = ‘1978-04-06’
ON DUPLICATE KEY
UPDATE P_Vorname = ‘Anton’, P_Nachname = ‘Ebenhof’,
P_Geburtstag = ‘1978-04-06’;
Datenmanipulation:
Datenmanipulation:Löschen
Löschen
Beispiel 1: Leeren einer Tabelle
DELETE FROM ‘Adressverwaltung’.’Kontakt’;
Beispiel 2: Löschen von gefilterten Datensätzen
DELETE FROM ‘Adressverwaltung’.’Kontakt’
WHERE ‘K_Nr’ = 1;
Beispiel 3a: Löschen von Datensätzen aus einem Verbund
DELETE k, p
FROM ‘Adressverwaltung’.’Kontakt’ k
INNER JOIN ‘Adressverwaltung’.’Person’ p
ON k.P_Nr = p.P_Nr;
Beispiel 3b: Alternative Formulierung zu 3a
DELETE FROM k, p
USING ‘Adressverwaltung’.’Kontakt’ k
INNER JOIN ‘Adressverwaltung’.’Person’ p
ON k.P_Nr = p.P_Nr;
Zusammengestellt von Marco Skulschus
Layout und Satz: Nadine Kilian
© 2012 Comelio Medien
Beispiel 1 : Prozedur für eine parametrisierte Abfrage
REPLACE INTO ‘Adressverwaltung’.’Person’
VALUES ( ‘1’, ‘Anton’, ‘Ebenhof’, ‘1971-04-27’);
Beispiel 2: Einfügen in eine Unterauswahl der Spalten und Einfügen
mehrerer Zeilen
INSERT INTO Person
SELECT Nr+4, Vorname, Nachname, GebDatum
FROM Person_Import;
Prozeduren
Prozeduren
Datenmanipulation:
Datenmanipulation:Aktualisieren
Aktualisieren
INSERT INTO Kontakt
( P_Nr, K_Art, K_Wert )
VALUES ( ‘1’, ‘Email’, ‘[email protected]’ );
UPDATE Kontakt
SET K_Wert = ‘[email protected]’
WHERE P_Nr = 1 AND K_Art = ‘Email’;
Beispiel 2: Prozedur mit Ausgabeparameter und lokalen Variablen
SQL-Programmierung:
SQL-Programmierung:Schleifen
Schleifen
Beispiel 1: Einfacher LOOP mit Ausstiegsbedingung
DECLARE nr INT;
SET nr = 0;
zaehler: LOOP
SET nr = nr + 1;
SELECT nr;
IF nr = 10
THEN LEAVE zaehler;
END IF;
END LOOP zaehler;
Beispiel 2: Einfaches WHILE mit Fortsetzungsanweisung
Trigger
Trigger
Beispiel 1: Papierkorb-Tabelle für gelöschte Datensätze
mit Trigger für Lösch-Aktion und Zugriff auf die gerade
gelöschten Datensätze. Weiterer sinnvoller Trigger:
Aktualisierungsaktion.
CREATE TABLE ‘PersonPapierkorb’ (
‘P_Nr’
INT
NOT NULL,
‘P_Nachname’ VARCHAR(30)
NOT NULL,
‘P_Aktion’
SET (‘DEL’, ‘UPD’) NOT NULL,
‘P_Datum’
DATETIME DEFAULT now()
);
DELIMITER $$
CREATE TRIGGER trg_Person
AFTER DELETE ON Person
FOR EACH ROW BEGIN
INSERT INTO PersonPapierkorb
SET P_Nr = OLD.P_Nr,
P_Nachname = OLD.P_Nachname,
P_Aktion = ‘DEL’;
END;
$$
Comelio GmbH
Goethestr. 34, 13086 Berlin
Web: www.comelio.com
DELIMITER $$
CREATE PROCEDURE getKontaktDetails(IN pnr INT)
BEGIN
SELECT K_Art, K_Wert
FROM Kontakt
WHERE P_Nr = pnr;
END; $$
-- Aufruf
CALL getKontaktDetails(1);
DECLARE nr INT;
SET nr = 0;
zaehler: WHILE nr <= 5 DO
SET nr = nr + 1;
IF nr = 3
THEN ITERATE zaehler;
END IF;
SELECT nr;
END WHILE zaehler;
Beispiel 3: Einfacher REPEAT mit Ausstiegsbedingung
DECLARE nr INT;
SET nr = 0;
zaehler: REPEAT
SET nr = nr + 1;
IF nr = 5
THEN ITERATE zaehler;
END IF;
SELECT nr;
UNTIL nr <= 10 END REPEAT zaehler;
Terrashop GmbH
Lise-Meitner-Str. 8, 53332 Bornheim
Web: www.terrashop.de
DELIMITER $$
CREATE PROCEDURE getKontakt(IN nr INT,
OUT kontakt CHAR(100) )
BEGIN
-- Variablendeklaration
DECLARE vorname CHAR(30);
DECLARE nachname CHAR(30);
-- Abruf von Werten aus DB
SELECT P_Vorname, P_Nachname
INTO vorname, nachname
FROM Person
WHERE P_Nr = nr;
-- Zuweisung an Ausgabe-Parameter
SET kontakt = concat(vorname, ‘ ‘, nachname);
END; $$
-- Aufruf
CALL getKontakt(1, @text);
SELECT @text;
Beispiel 3: Funktion für Aufruf in SQL
DELIMITER $$
CREATE FUNCTION getAdresse (pnr INT)
RETURNS CHAR(100)
DETERMINISTIC READS SQL DATA
BEGIN
DECLARE adresszeile CHAR(100);
SELECT concat(A_PLZ, ‘ ‘, A_Stadt)
INTO adresszeile
FROM Adresse
WHERE P_Nr = pnr;
RETURN adresszeile;
END; $$
-- Aufruf
SELECT P_Nachname, getAdresse(P_Nr) AS Adresse
FROM Person;
ISBN 978-3-939701-78-1
9 783939 701781