cSDMVcUL

1/********************************************************************************
2
3Benutzerverwaltung
4
5*********************************************************************************/
6
7--benutzer umeyer mit passwort geheim anlegen
8
9CREATE USER 'umeyer'@'localhost' IDENTIFIED BY 'geheim';
10
11
12--rechte gewähren
13
14GRANT SELECT, UPDATE ON kunde to 'umeyer'@'localhost';
15
16GRANT SELECT, UPDATE(name) ON account to 'umeyer'@'localhost';
17
18
19--rechte entziehen
20
21REVOKE DELETE ON kunde FROM 'umeyer'@'localhost';
22
23REVOKE ALL ON kunde FROM 'umeyer'@'localhost';
24
25
26--datensatz löschen
27
28DELETE FROM bank WHERE bankname = Bundesbank;
29
30
31
32
33/********************************************************************************
34
35Allgemein
36
37*********************************************************************************/
38
39-- INNER JOIN n zu m allgemein
40
41SELECT
42
43ht.merkmal_1, ..., ht.merkmal_n
44
45zt.merkmal_1, ..., zt.merkmal_n
46
47nt.merkmal_1, ..., nt.merkmal_n
48
49FROM
50
51(haupttabelle ht LEFT JOIN zwischentabelle zt ON joinbedingung ht-zt)
52
53LEFT JOIN nebentabelle nt ON joinbedingung zt-nt
54
55
56--INNERJOIN n yu m Prototyp
57
58SELECT
59
60ht.merkmal_1, zt.merkmal_1, nt.merkmal_1
61
62FROM
63
64(haupttabelle ht LEFT JOIN zwischentabelle zt ON ht.id = zt.id)
65
66LEFT JOIN nebentabelle nt ON zt.id = nt.id
67
68
69--Left join
70
71SELECT
72
73ht.merkmal_1, nt.merkmal_1
74
75FROM haupttabelle ht LEFT JOIN nebentabelle nt
76
77ON ht.id=nt.id
78
79
80--jüngsten schüler auswählen
81
82SELECT Vorname, Nachname
83
84FROM schüler
85
86WHERE Geburtsdatum =
87
88(
89
90  SELECT MIN(Geburtsdatum)
91
92  FROM `schüler`
93
94  WHERE YEAR(Geburtsdatum) != 0
95
96)
97
98
99--order by
100
101SELECT `Name`,`Vorname`,`Strasse`,`PLZ`,`Ort`
102
103FROM `mitarbeiter`
104
105ORDER BY `PLZ`
106
107
108--limit
109
110SELECT ProjNr, Bezeichnung, Auftragswert
111
112FROM projekt
113
114ORDER BY Auftragswert DESC LIMIT 5
115
116
117--distinct
118
119SELECT DISTINCT MNr FROM kind
120
121
122--time
123
124SELECT MNr, Name, Vorname, eingestellt
125
126FROM mitarbeiter
127
128WHERE YEAR(eingestellt) < 2014
129
130
131--bool
132
133SELECT MNr, Name, Vorname, Ort
134
135FROM Mitarbeiter
136
137WHERE ORT!='Köln'
138
139
140--between
141
142SELECT ProjNr, Bezeichnung, Auftragswert
143
144FROM projekt
145
146WHERE Auftragswert between 25000 AND 60000
147
148
149--LIKE
150
151SELECT MNr, Name, Vorname, PLZ
152
153FROM mitarbeiter
154
155WHERE PLZ NOT LIKE '5%'
156
157
158--bool2
159
160SELECT MNr, Name, Vorname, Ort
161
162FROM mitarbeiter
163
164WHERE (MNR < 0200 OR MNR > 0700) AND Ort = 'Hamburg';
165
166
167--alias
168
169SELECT
170
171Bezeichnung, auftragswert, auftragswert*0.19 as MWSt,
172
173auftragswert + auftragswert*0.19 AS Auftragswert2
174
175FROM projekt;
176
177
178--order by
179
180SELECT ProjNr, count(MNr) AS 'Anzahl der Mitarbeiter'
181
182FROM projektmitarbeiter
183
184GROUP BY ProjNr;
185
186
187--sum
188
189SELECT Leiter, sum(Auftragswert) as 'Summe Auftragswerte'
190
191FROM projekt
192
193WHERE Storno = 0
194
195GROUP BY Auftragswert;
196
197
198--min max average
199
200SELECT
201
202MIN(auftragswert) AS MIN,
203
204AVG(auftragswert) AS AVG,
205
206MAX(auftragswert) AS MAX
207
208FROM projekt;
209
210
211--durchschnitt subtrahieren
212
213SELECT Bezeichnung, Auftragswert -
214
215(
216
217  SELECT AVG(Auftragswert)
218
219  FROM Projekt
220
221)
222
223FROM projekt;
224
225
226--truncate
227
228SELECT
229
230TRUNCATE(Auftragswert, -4) AS 'Auftragswert ab ... Euro',
231
232count(TRUNCATE(Auftragswert, -4)) AS Anzahl
233
234FROM projekt
235
236GROUP BY TRUNCATE(Auftragswert, -4);
237
238
239--ceiling
240
241SELECT Bezeichnung, Etat * 0.025, CEILING(Etat * 0.025)
242
243FROM abteilung;
244
245
246/********************************************************************************
247
248Prozeduren Aufgabe 1
249
250*********************************************************************************/
251
252DROP PROCEDURE IF EXISTS INSERT_ARTIKEL_IN_WARENGRUPPE;
253
254DELIMITER //
255
256
257CREATE PROCEDURE INSERT_ARTIKEL_IN_WARENGRUPPE
258
259(
260
261   IN pArtikelname VARCHAR(255),
262
263   IN pWarengruppe VARCHAR(255)
264
265)
266
267BEGIN
268
269
270DECLARE var_artikel_id INT DEFAULT 0;
271
272DECLARE var_warengruppe_id INT DEFAULT 0;
273
274DECLARE var_artikel_anzahl_zugeordnet INT  DEFAULT 0;
275
276
277SELECT artikel_id
278
279FROM artikel
280
281WHERE bezeichnung = pArtikelname
282
283INTO var_artikel_id;
284
285
286SELECT warengruppe_id
287
288FROM warengruppe
289
290WHERE bezeichnung = pWarengruppe
291
292INTO var_warengruppe_id;
293
294
295IF var_artikel_id = 0
296
297THEN
298
299  INSERT INTO artikel (bezeichnung)
300
301  VALUES (pArtikelname);
302
303END IF;
304
305
306IF var_warengruppe_id = 0
307
308  THEN
309
310  INSERT INTO warengruppe (bezeichnung)
311
312  VALUES (pWarengruppe);
313
314END IF;
315
316
317SELECT artikel_id
318
319FROM artikel
320
321WHERE bezeichnung = pArtikelname
322
323INTO var_artikel_id;
324
325
326SELECT warengruppe_id
327
328FROM warengruppe
329
330WHERE bezeichnung = pWarengruppe
331
332INTO var_warengruppe_id;
333
334
335IF ((var_artikel_id != 0) AND (var_warengruppe_id != 0))
336
337THEN
338
339  SELECT COUNT(artikel_id)
340
341  FROM artikel_nm_warengruppe
342
343  WHERE artikel_id = var_artikel_id
344
345  INTO var_artikel_anzahl_zugeordnet;
346
347  IF ( var_artikel_anzahl_zugeordnet = 0)
348
349  THEN
350
351      INSERT INTO artikel_nm_warengruppe (artikel_id, warengruppe_id)
352
353      VALUES (var_artikel_id, var_warengruppe_id);
354
355  END IF;
356
357END IF;
358
359
360END //
361
362DELIMITER ;
363
364
365--Aufruf
366
367CALL INSERT_ARTIKEL_IN_WARENGRUPPE('Koks', 'Pflanzen');
368
369
370/********************************************************************************
371
372Prozeduren Aufgabe 2
373
374
375*********************************************************************************/
376
377DELIMITER //
378
379
380CREATE PROCEDURE umsatzreport
381
382(
383
384  IN iZeitraum INT
385
386)
387
388BEGIN
389
390DROP TABLE IF EXISTS umsatzzahlen;
391
392
393CASE iZeitraum
394
395WHEN 1 THEN -- Total
396
397  CREATE TABLE umsatzzahlen
398
399  SELECT 'Total', SUM(einzelpreis * menge) ‘Umsatz‘
400
401  FROM
402
403  rechnung_position INNER JOIN artikel USING (artikel_id)
404
405  INNER JOIN rechnung USING (rechnung_id);
406
407
408WHEN 2 THEN -- Pro Jahr
409
410  CREATE TABLE umsatzzahlen
411
412  SELECT YEAR(datum) ‘Jahr‘, SUM(einzelpreis * menge) ‘Umsatz‘
413
414  FROM
415
416  rechnung_position INNER JOIN artikel USING (artikel_id)
417
418  INNER JOIN rechnung USING (rechnung_id)
419
420  GROUP BY 'Jahr'
421
422  ORDER BY 'Jahr' DESC;
423
424
425WHEN 3 THEN -- Pro Monat
426
427  CREATE TABLE umsatzzahlen
428
429  SELECT YEAR(datum) ‘Jahr‘, MONTH(datum) ‘Monat‘, SUM(einzelpreis * menge)'Umsatz'
430
431  FROM
432
433  rechnung_position INNER JOIN artikel USING (artikel_id)
434
435  INNER JOIN rechnung USING (rechnung_id)
436
437  GROUP BY ‘Jahr‘, ‘Monat‘
438
439  ORDER BY ‘Jahr‘ DESC, ‘Monat‘ DESC;
440
441
442ELSE -- Sowas sollte es immer geben
443
444  CREATE TABLE umsatzzahlen
445
446  SELECT ’Unbekannte Berichtsart’, iZeitraum;
447
448END CASE;
449
450
451END//
452
453
454DELIMITER ;
455
456
457/********************************************************************************
458
459Prozeduren Aufgabe Zusatz 1
460
461Bestellung neue Position hinzufügen
462
463*********************************************************************************/
464
465
466DROP PROCEDURE IF EXISTS position_bestellung_hinzufuegen;
467
468DELIMITER //
469
470
471CREATE PROCEDURE position_bestellung_hinzufuegen
472
473(
474
475   IN pBestellung_id INT,
476
477   IN pArtikel_id INT,
478
479   IN pMenge DECIMAL(14,6)
480
481)
482
483
484prozedur:
485
486BEGIN
487
488
489DECLARE var_position_nr INT DEFAULT 0;
490
491
492IF(SELECT bestellung_id FROM bestellung WHERE bestellung_id = pBestellung_id) IS NULL
493
494THEN   
495
496  SELECT 'Diese Bestellung existiert nicht';
497
498  leave prozedur;
499
500END IF;
501
502
503IF(SELECT artikel_id FROM artikel WHERE artikel_id=pArtikel_id) IS NULL
504
505THEN
506
507  SELECT 'Dieser Artikel existiert nicht';
508
509  leave prozedur;
510
511END IF;
512
513
514SELECT count(bestellung_id)+1
515
516FROM bestellung_position
517
518WHERE bestellung_id=pBestellung_id
519
520INTO var_position_nr;
521
522
523INSERT INTO bestellung_position (bestellung_id, position_nr, artikel_id, menge, deleted)
524
525VALUES (pBestellung_id, var_position_nr, pArtikel_id, pMenge, 0);
526
527
528END //
529
530DELIMITER ;
531
532
533CALL position_bestellung_hinzufuegen(1, 3001, 300);
534
535
536/********************************************************************************
537
538Prozeduren Aufgabe Zusatz 2
539
540Position aus bestellung löschen, positionsnummern neu nummerieren,
541
542übergabeparameter plausibilisieren
543
544*********************************************************************************/
545
546
547DROP PROCEDURE IF EXISTS positionLoeschen;
548
549DELIMITER //
550
551
552CREATE PROCEDURE positionLoeschen
553
554(
555
556   IN pBestellung_id INT,
557
558   IN pPosition INT
559
560)
561
562
563prozedur:
564
565BEGIN
566
567
568DECLARE var_position_nr INT DEFAULT 0;
569
570
571IF(SELECT bestellung_id FROM bestellung WHERE bestellung_id = pBestellung_id) IS NULL
572
573THEN   
574
575  SELECT 'Diese Bestellung existiert nicht';
576
577  leave prozedur;
578
579END IF;
580
581
582IF
583
584(   SELECT position_nr
585
586  FROM bestellung_position
587
588  WHERE bestellung_id = pBestellung_id
589
590  AND position_nr = pPosition) IS NULL
591
592THEN
593
594  SELECT 'Bestellung enthält die Posi nicht';
595
596  leave prozedur;
597
598END IF;
599
600
601DELETE FROM bestellung_position
602
603WHERE bestellung_id = pBestellung_id AND position_nr = pPosition;
604
605
606UPDATE bestellung_position
607
608SET position_nr=position_nr-1
609
610WHERE bestellung_id=pBestellung_id AND position_nr>pPosition;
611
612
613END //
614
615DELIMITER ;
616
617
618/********************************************************************************
619
620Trigger Insert
621
622*********************************************************************************/
623
624drop trigger if exists before_mitarbeiter_insert;
625
626DELIMITER $$
627
628CREATE TRIGGER before_mitarbeiter_insert
629
630  BEFORE insert ON mitarbeiter
631
632  FOR EACH ROW
633
634BEGIN
635
636  DECLARE vUser varchar(45);
637
638  SELECT USER() INTO vUser;
639
640  INSERT INTO sql_trigger_insert
641
642  SET Timestamp = CURRENT_TIMESTAMP,
643
644  User = vUser,
645
646  inserted_mnr = new.mnr;
647
648END$$
649
650DELIMITER ;
651
652
653--normaler insert
654
655INSERT INTO `eurodata_example`.`mitarbeiter`
656
657(`MNr`,
658
659`Name`,
660
661`Vorname`,
662
663`Strasse`,
664
665`PLZ`,
666
667`Ort`,
668
669`Telefon`,
670
671`Geschlecht`,
672
673`eingestellt`,
674
675`KFZ1`,
676
677`KFZ2`,
678
679`Abteilung`)
680
681VALUES
682
683('9999',
684
685'Müller',
686
687'John',
688
689'WegStraße 22',
690
691'12345',
692
693'Wuppertal',
694
695'0202 12345',
696
697'w',
698
699'2001-12-12',
700
701'AB AB12',
702
703'CD CD34',
704
705'01');
706
707
708/********************************************************************************
709
710Trigger Delete
711
712*********************************************************************************/
713
714drop trigger if exists before_mitarbeiter_delete;
715
716DELIMITER $$
717
718CREATE TRIGGER before_mitarbeiter_delete
719
720  BEFORE DELETE ON mitarbeiter
721
722  FOR EACH ROW
723
724BEGIN
725
726  DECLARE vUser varchar(45);
727
728  SELECT USER() INTO vUser;
729
730  INSERT INTO sql_trigger_delete
731
732  SET Timestamp = CURRENT_TIMESTAMP,
733
734  User = vUser,
735
736  deleted_mnr = old.mnr;
737
738END$$
739
740DELIMITER ;
741
742
743--normaler delete
744
745DELETE FROM `eurodata_example`.`mitarbeiter`
746
747WHERE
748
749mnr = '9999';