<-- Studium  

MySQL Kurzreferenz und Labortagebuch der Vorlesung "Datenbanken":

Kurzreferenz

# Zu mysql verbinden:
mysql -h host -u foo -p

# Informationen ueber mysql anzeigen:
status;

# Benutzerpasswort fuer aktuellen Benutzer vergeben:
set password = password('foo')

# Benutzerpasswort vergeben (host muss auch lokal genannt werden, z.b. localhost):
set password for 'user'@'host' = password('foo');

# Duemmere Moeglichkeit das Benutzerpasswort zu vergeben:
update mysql.user set Password=password('newpass') where User='bob' and Host='%.loc.gov'; flush privileges;

# Neuen Benutzer anlegen:
GRANT ALL PRIVILEGES ON *.* TO 'USER'@'%' IDENTIFIED BY 'PASSWORD' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'USER'@'localhost' IDENTIFIED BY 'PASSWORD' WITH GRANT OPTION;

# Mysql beenden:
exit



# Vorhandene Datenbanken anzeigen:
show databases;

# Mit Datenbank verbinden:
use foo;

# Datenbank erstellen:
create database foo;

# Datenbank loeschen:
drop database foo; 

# Tabellen in aktueller DB anzeigen:
show tables;

# Eigenschaften aller Spalten in Tabelle foo anzeigen:
desc foo;
# oder
show columns from foo;
# oder
explain foo;

# Eigenschaften der Spalete bar in Tabelle foo anzeigen:
desc foo bar;

# Tabelle loeschen:
drop table foo;

# Daten einer Tabelle loeschen
truncate table foo;

# Tabelle umbenennen:
alter table foo rename as foobar;



# Backup:
mysqldump -u foo -p fooDB fooTabelle > backup.sql

# Backup zurueckspielen:
mysql -u foo -p fooDB < backup.sql

Termin 01

Aufgaben:
Aufgabe 1)
Erstellen Sie eine Tabelle zur Verwaltung einer Filme Sammlung. Folgende Spalten werden benoetigt: ID, Titel, Regie, Dauer, Pfad, Erscheinungsdatum Ueberlegen Sie sich wie die Datentypen zu waehlen sind damit folgendes SQL- Skript die Tabelle befuellen kann.
 
Aufgabe 2)
Geben sie alle Filme aus, die von George Lucas gedreht wurden.
 
Aufgabe 3)
Geben sie Dateiname und Titel aus, von Filmen mit einer ID kleiner als 10.
 
Aufgabe 4)
Geben sie Dateiname und Titel aus, von Filmen mit einer ID kleiner als 15 und groesser oder gleich 10.
 
Aufgabe 5)
Geben sie alle Filme an die vor 1970 veroeffentlicht wurden.
 
Aufgabe 6)
Sie suchen die ID eines Films, haben aber nicht mehr genau den Titel im Kopf, wissen aber das im Titel etwas mit `Empire` vorkommt. Wie lautet die ID?
 
Aufgabe 7)
Geben Sie alle Film Titel aus, die zwischen 1981 und 1999 veroeffentlicht wurden, wobei die Filme von 1995 nicht mit aufgelistet werden sollen.
 
Aufgabe 8)
Wie viele Filme haben eine Laufzeit von ueber 110 Minuten ?
 
Aufgabe 9)
Fuegen sie eine Spalte `Land` hinzu und uehren sie folgendes SQL-Skript aus.
 
Aufgabe 10)
Entfernen Sie den Eintrag des Films mit dem Titel `Rear Window` aus der Tabelle.
 
Aufgabe 11)
Geben sie alle Filme und ihre Regisseure an, dessen Titel zwischen den Anfangsbuchstaben ,I` und ,T` liegen! Entspricht das Ergebnis Ihren Erwartungen? Wieso?
 
Aufgabe 12)
Erstellen Sie ein Backup der Tabelle filme und praeparieren Sie das Backup Skript so, dass die Tabelle beim Rueckspielen filme_backup heisst. Tragen Sie nach dem Rueckspielen des Backup Skriptes anschliessend alle Eintraege, mittels eines einzigen SELECT / INSERT Statements, aus dem filme_backup in filme ein (Ohne Ruecksichtnahme auf mehrfach Eintraege).

# Aufgabe 1:
create database DB1_CNB3;
create table filme(id int PRIMARY KEY AUTO_INCREMENT, Titel varchar(50) NOT NULL, Regie varchar(50) NOT NULL, Dauer int,  Pfad varchar(50) NOT NULL, Erscheinungsdatum datetime NOT NULL);
# ...Script ausfuehren...

# Aufgabe 2:
select * from filme where Regie="George Lucas";

# Aufgabe 3:
select Pfad, Titel from filme where id < 10;

# Aufgabe 4:
select Pfad, Titel from filme where id < 15 and id > 10;

# Aufgabe 5:
select Titel from filme where Erscheinungsdatum < "1970";

# Aufgabe 6:
select id from filme where Titel like "%Empire%";

# Aufgabe 7:
select Titel from filme where Erscheinungsdatum between '1981-01-01' and '1999-01-01' and Erscheinungsdatum not like '%1995%';

# Aufgabe 8:
select count(*) from filme where Dauer > 110;

# Aufgabe 9:
alter table filme add Land varchar(50) NOT NULL;
# ...Script ausfuehren...
# Anmerkung: Dr. Strangelove nicht geupdatet da Titel in DB abgeschnitten ist wegen nur 50 Zeichen grossem Feld.
# Loesung:
UPDATE filme SET land = 'UK' where Titel like '%Dr. Strangelove%';

# Aufgabe 10:
delete from filme where Titel = 'Rear Windows';
# Anmerkung:  'Rear Window' gibt es nicht, vermutlich Schreibfehler.

# Aufgabe 11:
select Titel,Regie from filme where Titel >'I' and Titel <'T' order by Titel asc;
select Titel,Regie from filme where Titel between 'I' and 'T' order by Titel asc;
# geht beides. Keine Ahnung was mir da auffallen soll.

# Aufgabe 12:
mysqldump -u root -p DB1_CNB3 filme > backup.sql
# ...
mysql -u root -p DB1_CNB3 < backup_n.sql

# mysql>  insert into filme select * from filme_backup;
# ERROR 1062 (23000): Duplicate entry '1' for key 1

# Daher muss man die Spalten explizit angeben:
insert into filme(Titel,Regie,Pfad,Erscheinungsdatum,Dauer,Land) select Titel,Regie,Pfad,Erscheinungsdatum,Dauer,Land from filme_backup;

Termin 02

Aufgaben:
Aufgabe 1)
Erstellen sie mit Hilfe des folgenden SQL-Skripts die Film Datenbank.
 
Aufgabe 2)
Geben sie die Namen der Regisseure aus, ohne doppelte Eintraege, die zwischen 1970 und 1990 Filme gedreht haben, die mehr als 110 Minuten Spieldauer haben.
 
Aufgabe 3)
Geben sie die Namen der Regisseure aus, sowie die Gesammtspielzeit der von ihnen gedrehten Filme, in absteigender Reihenfolge.
 
Aufgabe 4)
Geben sie die Anzahl der Titel, die von 'Francis Ford Coppola' oder 'Milos Forman' gedreht wurden, und in der Datenbank als .mpg Format vorliegen.
 
Aufgabe 5)
Geben sie die ID, den Titel und den zugehoerigen Pfad aus. In der Ergebnistabelle soll die Spalte 'Pfad' als 'Datei' angezeigt werden, die ID's sollen nicht zwischen 5 und 10 liegen und der Titel muss mit 'The' anfangen.
 
Aufgabe 6)
Summieren sie die IDs der Regisseure welche eine gesamtspielzeit von mehr als 300 Minuten haben, und geben sie diese in Absteigender Reihenfolge aus.
 
Aufgabe 7)
Geben sie die Regisseure und die Gesammtspielzeit ihrer Filme aus, wobei zwei Einschraenkungen zu beachten sind.
1. Die Gesammtlaufzeit soll nur zwischen 300 und 900 Minuten liegen.
2. Das Ergebnis soll auf folgende Regisseure beschraenkt werden : Steven Spielberg und Francis Ford Coppola.
 
Aufgabe 8)
Erstellen sie mit Hilfe des folgenden SQL-Skripts die World Datenbank.
 
Aufgabe 9)
Geben sie aus der Tabelle City den District und die Gesammtpopulation aus, der Laender Deutschland (DEU) und Brasilien (BRA) wobei die Districts mit 'Ba' anfangen sollen und nach Absteigender Ordnung sortiert sind.
 

# Aufgabe 1:
Entfaellt da DB von letztem mal schon vorhanden

# Aufgabe 2:
select distinct Regie from filme where Erscheinungsdatum > '1970' and Erscheinungsdatum < '1990' and Dauer >= 110;

# Aufgabe 3:
select distinct Regie, sum(Dauer) from filme group by Regie order by Regie desc;

# Aufgabe 4:
select count(*) from filme where (Regie = "Francis Ford Coppola" or Regie = "Milos Forman") and Pfad like "%mpg";

# Aufgabe5:
select id, Titel, Pfad as "Datei" from filme where (id <= 5 or id >= 10) and Titel like "The%";

# Aufgabe 6:
select distinct sum(id) from filme group by Regie having sum(Dauer) > 300 order by sum(id) desc;

# Aufgabe 7:
select distinct Regie, sum(Dauer) from filme group by Regie having (sum(Dauer) >= 300 and sum(Dauer) <= 900) and (Regie = "Steven Spielberg" or Regie = "Francis Ford Coppola");

# Aufgabe 8:
mysql -u root -p DB1_CNB3 < Aufgabe2_worlddb.sql

# Aufgabe 9:
select District, sum(Population) from city where (CountryCode = "DEU" or CountryCode = "BRA") and District like "Ba%" group by District order by Population asc;

Termin 03

Aufgaben:
Aufgabe 1)
Erstellen sie mit Hilfe des folgenden SQL-Skripts die Lieferanten Datenbank, sofern sie noch nicht in Ihrer Datenbank vorhanden ist.
 
Aufgabe 2)
Erstellen Sie einen View bestell_menge_tid, der die tid und die Summe der Bestellungen beinhaltet.
Anschliessend soll mit Hilfe dieses Views die Summe aller Bestellungen ausgegeben werden.
 
Aufgabe 3)
Erstellen Sie einen View, der aus der Tabelle bestellungen, diejenigen Zeilen ausgibt die eine Menge unter 200 haben.
Geben sie mittels JOIN genau die Namen der Teile aus, die unter 200 liegen.
Was geben die anderen JOIN Statements aus?
 
Aufgabe 4)
Geben sie die Namen der Teile aus, die in der Bestellungen Tabelle summenmaessig am wenigstens bestellt wurden.
 
Aufgabe 5)
Geben sie die Lieferanten aus London aus die Teile als Bestellungen offen haben die mit 'C' im Namen anfangen und die Farbe 'Red' haben.
 
Aufgabe 6)
Erstellen sie einen View der aus der Tabelle bestellungen nur die lid von Lieferanten ausgibt, die das Produkt P2 liefern.
Geben sie die Daten der Lieferanten aus (mittels JOIN) welche in London wohnen und im eben angelegten VIEW vorkommen.
Schreiben sie die gleiche Abfrage aber ohne die Hilfe von JOIN.
 
Aufgabe 7)
Erstellen sie mit Hilfe des folgenden SQL-Skripts die World Datenbank, sofern sie noch nicht in Ihrer Datenbank vorhanden ist.
 
Aufgabe 8)
Gesucht wird die Anzahl der Laender deren Bevoelkerung eine Gewisse Grenze ueberschreitet. Die Grenze ergibt sich aus der Summe der Staedte die in Laendern liegen deren Bevoelkerung zwischen 10.000.000 und 15.000.000, aus einer beliebigen Region aus Europa kommen und als Regierungsform eine Republik haben.
 

# Aufgabe 1:
mysql -u root -p < hp-kram/leese.info/stud/db/Aufgabe3_lieferanten.sql

# Aufgabe 2:
create view bestell_menge_tid as select tid, sum(menge) as mengensumme from bestellungen group by tid;
select sum(mengensumme) from bestell_menge_tid;

# Aufgabe 3:
create view menge_unter_200 as select * from bestellungen where menge < 200;
select * from menge_unter_200;
+-----+-----+-------+
| lid | tid | menge |
+-----+-----+-------+
| S1  | P5  |   100 | 
| S1  | P6  |   100 | 
+-----+-----+-------+

select tname from teile inner join menge_unter_200 on teile.tid = menge_unter_200.tid;
+-------+
| tname |
+-------+
| Cam   | 
| Cog   | 
+-------+

select tname from teile left join menge_unter_200 on teile.tid = menge_unter_200.tid;
+-------+
| tname |
+-------+
| Nut   | 
| Bolt  | 
| SCREW | 
| Screw | 
| Cam   | 
| Cog   | 
| Peg   | 
+-------+

select tname from teile right join menge_unter_200 on teile.tid = menge_unter_200.tid;
+-------+
| tname |
+-------+
| Cam   | 
| Cog   | 
+-------+

# Aufgabe 4:
select tname, sum(menge) from teile inner join bestellungen on teile.tid = bestellungen.tid group by bestellungen.tid order by sum(menge) asc limit 2;

# Aufgabe 5:
select lname from lieferanten inner join bestellungen on lieferanten.lid = bestellungen.lid inner join teile on bestellungen.tid = teile.tid where teile.farbe = "red" and teile.tname like "C%";

# Aufgabe 6:
create view lid_P2 as select lieferanten.lid from lieferanten inner join bestellungen on lieferanten.lid = bestellungen.lid inner join teile on bestellungen.tid = teile.tid where teile.tid = "P2";
select lieferanten.lid, lname, status from lieferanten inner join lid_P2 on lieferanten.lid = lid_P2.lid where stadt = "London";
select distinct lieferanten.lid, lname, status from lieferanten, lid_P2 where stadt = "London";

# Aufgabe 7:
# Schon vorhanden, Befehl waehre:
mysql -u root -p DB1_CNB3 < Aufgabe2_worlddb.sql 

# Aufgabe 8:
create view Gewisse_Grenze as select sum(city.Population) as Grenze from city inner join country on city.CountryCode = country.Code where (country.Population between '10000000' and '15000000') and (country.Continent = "Europe") and (country.GovernmentForm like "%Republic%");
select count(Name) from country, Gewisse_Grenze where Population < Grenze and (country.Population between '10000000' and '15000000') and (country.Continent = "Europe") and (country.GovernmentForm like "%Republic%");
+-------------+
| count(Name) |
+-------------+
|           5 | 
+-------------+

Termin 04

Aufgaben:

Tabelle in "0." Normalform
 
Aufgabe 1)
Normalisieren Sie die Oben gegebene Tabelle in dem Sie alle drei Normalisirungsstufen durchlaufen!
 
Aufgabe 2)
Schreiben Sie fuer jede Normalform ein SQL-Skript das die Zugehoerigen Tabellen erzeugt. (nur die CREATE's zum erstellen der jeweiligen Tabellen)
 
Hinweis
Zu 1NF: Spalte Kunde : Zahl ist Kundennummer.
Zu 2NF: In 2 Tabellen aufteilen. Passen PK / FK waehlen.
Zu 3NF: 4 Tabellen.

# Aufgabe 2:
create database Obst;
use Obst;
create table Auftraege(AuftrNr int, Datum date, ArtNr int, Menge int, VerpArt varchar(50), primary key(ArtNr));
create table Artikel(ArtNr int, Bezeichnung varchar(50), primary key(ArtNr));
create table Kunden(AuftrNr int, KdNr int, KdName varchar(50), primary key(KdNr), foreign key(AuftrNr) references Auftraege(AuftrNr));
create table Ort(KdNr int, Ort int(50), primary key(KdNr));

Termin 05

Aufgaben:
Aufgabe 1)
Schreiben sie eine Funktion welche aus einer uebergebenen URL die Domain heraus extrahiert. Bennen sie die Funktion url_domain. Als Uebergabeparameter bekommt die Funktion eine Variable vom Typ TEXT.
 
Aufgabe 2)
Schreiben sie eine Funktion welche aus einer Uebergebenen URL die SubDomain heraus extrahiert. Bennen sie die Funktion url_subdomain. Als Uebergabeparameter bekommt die Funktion eine Variable vom Typ TEXT.
 
Aufgabe 3) Erstellen sie folgende Tabelle in einer Datenbank ihrer Wahl.

CREATE TABLE member (
  id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(32),
  create_date TIMESTAMP DEFAULT '0000-00-00 00:00:00',
  update_date TIMESTAMP DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (id)
);

# Aufgabe1:
CREATE FUNCTION url_domain (url TEXT) RETURNS varchar(255) RETURN 
SUBSTRING_INDEX(
  SUBSTRING_INDEX(
    SUBSTRING_INDEX(TRIM(LEADING "http://" FROM TRIM(url)), "/", 1),
  ":", 1),
 ".",
  if(url LIKE "%.com.__%" OR 
     url LIKE "%.org.__%" OR
     url LIKE "%.net.__%" OR
     url LIKE "%.__.de%",
  -3, -2) );

SELECT url_domain(" http://www.gmx.de ") AS domain;

# Aufgabe2:
CREATE FUNCTION url_subdomain (url TEXT) RETURNS varchar(255) RETURN 
SUBSTRING_INDEX(
  SUBSTRING_INDEX(TRIM(LEADING "http://" FROM TRIM(url)), "/", 1), 
  ":", 1
);

SELECT url_subdomain(" http://mail.gmx.de ") AS subdomain;

# Aufgabe 3:
delimiter //

create trigger t1 before insert on member for each row 
begin 
set new.create_date = now(), new.update_date="0000-00-00 00:00:00"; 
end//

create trigger t2 before update on member for each row 
begin 
set new.update_date = now();  
end//

select * from information_schema.TRIGGERS;
show triggers;

# Aufgabe 4:
create table frust(id int PRIMARY KEY AUTO_INCREMENT, zeichenfolge varchar(50), hexadezimal varchar(50));
alter table frust add Laenge int; # Beim Erstellen vergesen

CREATE PROCEDURE getLaengeUndHex(s VARCHAR(10)) 
BEGIN 
  DECLARE laenge TINYINT; 
  DECLARE stringInHex VARCHAR(20); 
  SET laenge = LENGTH(s); 
  SET stringInHex = HEX(s); 
  INSERT INTO frust (zeichenfolge, Laenge, hexadezimal) Values (s, laenge, stringInHex); 
END;//

call getLaengeUndHex('TestStr')//

Termin 06

Aufgaben:
1) Erstellen sie mit Hilfe des folgenden SQL-Skripts die fuer die Aufgaben noetigen Tabellen.
 
2) Schreiben sie einen SQL Query der folgendes Ergebnis auf Basis der ipadressen Tabelle ausgibt, indem sie auf String Funktionen zurueckgreifen:

 
3) Schreiben sie einen SQL Query der aus der email Tabelle nur die Domaenen (asdf@hallo.com -> hallo.com) der einzelnen Emails ausgibt.
 
4) Schreiben sie einen SQL Query der eine Email Adresse validieren soll mittels REGEXP.
 
5) Schreiben sie einen SQL Query der die Tage, Stunden und Minuten ausgibt zwischen einem Datum aus der datum Tabelle und dem Heutigen Datum.

# Aufgabe 1:
mysql -u root -p < hp-kram/leese.info/stud/db/Aufgabe6.sql

# Aufgabe 2:
select ip, SUBSTRING(ip,1,3) as a, SUBSTRING(ip,5,3) as b, substring_INDEX(SUBSTRING(ip,9),'.',1) as c, substring_INDEX(SUBSTRING(ip,9), '.',-1) as d from ipadressen;

# Aufgabe 3:
select id, SUBSTRING(email,INSTR(email, "@") +1 ) as a from emails;

# Aufgabe 4:
select * from emails where email REGEXP '^[a-zA-Z0-9._-]+@[a-zA-Z0-9-]+\\.[a-zA-Z.]{2,5}$';
# oder
select * from emails where email REGEXP '^[a-zA-Z0-9._-]+@+[a-zA-Z0-9.-]+(\\.de|\\.com|\\.org|\\.int)$';

# Aufgabe 5:
select DATEDIFF(NOW(), datum) as Tage, DATEDIFF(NOW(), datum) * 24 as Stunden,  DATEDIFF(NOW(), datum) * 24 * 60 as Minuten from datum where id=1;

Abschlussprojekt

Aufgabe:
Schreiben sie ein Programm ihrer Wahl, was den Themenumfang des Praktikums weitestgehend abdeckt. Die Sprache in der Sie das Programm verfassen sollte dabei C# [Anm.: Sonst aber noch alles fit bei den Tutoren?!? Hat jedoch trotzdem keines der Teams gemacht] oder Java sein. Die Datenbank die sie als Grundlage verwenden ist von ihnen selber zu erstellen (inkl. Demo Datensaetzen), und sollte mind. 3 Tabellen haben und sich in 3NF befinden.
 
Technische Grundanforderungen :

• Einfuegen, Aendern und Loeschen der Daten in der Datenbank, mit Beachtung von Anomalien die Auftreten koennen.
• Verwenden von Stored Procedures, Stored Function und Triggern
• Es sollte moeglich sein mit dem Programm interaktiv zu arbeiten!
• Exception Handling
• GUI ist nicht zwingend noetig. Wenn sie sich fuer eine Konsole Anwendung entschliessen, sollte diese aber die gleichen Ein. Bzw. Ausgabe Moeglichkeiten bieten wie entsprechende GUI Elemente
• Multi-User Zugriffe sollen moeglich sein
• Grosse Datenmengen generieren zum testen
• Entweder Optimistic Concurrency Control oder Pessimistic Concurrency Control (Lock Oriented) implementieren
• Optimieren, Index indizieren und am Buffer drehen

1. umfangreiche Kundenverwaltung mit Rechnungen, Produkten, Kunden, offenen und Abgeschlossenen Lieferungen usw.
2. Ein Gruppenplanungstool, mit Mitarbeiter, Informationen zu Mitarbeitern, Aufteilung in Gruppen mit Zeitlichen Einteilungen usw.

• Datenbankstruktur (Visio [Anm.: Wohl zuviel Ballmer Praesentationen gesehen] oder aequivalent)
• Source Codes
• Datenbank Skripte (Sicherung der DB inkl. den Prozeduren etc.)
• Lauffaehiges Programm !!!

MKSO Admin GUI