Kapitel 5 Datenbanken

MS Access / MySql

• Was ist das eigentlich, eine Datenbank?
• Warum nutzt man Datenbanken?
• Wie erstellt man sie?
• Wie greift man darauf zu?
• Welche (und wieviele) Grundoperationen gibt es?

CREATE TABLE IF NOT EXISTS p_person (
    pid_pers int(4) ,
    lastname varchar(50) ,
    firstname varchar(50) ,
    gender varchar(1) ,
    birth_dt date ,
    sport varchar(100) ,
    hobby varchar(100) ,
    remarks varchar(100) 
  ) ;

ALTER TABLE p_person
   ADD UNIQUE KEY pid_pers (pid_pers);

INSERT INTO p_person (pid_pers, lastname, firstname, gender, birth_dt, sport, hobby, remarks) VALUES
 (1001, 'Borsten', 'Ronja', 'f', '2002-04-29', 'Judo,Reiten' , 'Lesen', ''),
 (1002, 'Mauz', 'Luise', 'f', '2003-06-13', 'Zumba' , '' , ''),
 (1003, 'Wiegner', 'Wiegner', 'f', '2002-07-25', '' , 'Chillen' , ''),
 (1004, 'Majer', 'David', 'm', '2001-12-09', 'Fussball, Kickers' , '' , ''),
 (1005, 'Timmer', 'Yannik', 'm', '2002-06-20', 'Fussball' , '' , ''),
 (1006, 'Wagner', 'Marc', 'm', '2000-08-31', 'Feuerwehr' , '' , ''),
 (1007, 'Waldheim', 'Max', 'm', '2002-10-14', 'Fussball' , 'Gitarre' , '')
 ; 

CREATE OR REPLACE VIEW vw_female
  AS SELECT
 pid_pers,  lastname, firstname, gender, birth_dt, sport, hobby, remarks 
 FROM         p_person WHERE gender = 'f' 
 ORDER BY     
               lastname
  ,            firstname
  ; 

CREATE OR REPLACE VIEW vw_male
  AS SELECT
  firstname, lastname,  birth_dt, sport, hobby, remarks
  FROM p_person WHERE gender = 'm'
  ORDER BY     
                birth_dt
   ,            lastname
   ; 

CREATE OR REPLACE VIEW vw_geburtstagsliste AS
 SELECT 
 month(birth_dt)                           AS month
 ,dayofmonth(birth_dt)                     AS day
 ,year(birth_dt)                           AS year
 ,datediff( curdate() ,birth_dt ) /365.228 AS age
 ,lastname                                 AS lastname
 ,firstname                                AS firstname
 ,pid_pers                                 AS nummer
 FROM      p_person 
 WHERE     (month(birth_dt) > 0) 
 ORDER BY 
  month(birth_dt)
 ,dayofmonth(birth_dt)
 ,year(birth_dt)
 ;