MySQL Cwiczenia

title

MySQL ćwiczenia

author

Leszek Albrzykowski (NobleProg Ltd)

Etap projektowanie bazy danych

• Jakie informacje chcemy przechowywać i jakie będą relacje między obiektami?
• Jakie tabele powinna zawierać baza?
• Diagram bazy
• Zatwierdzenie projektu

1.1. Jakie informacje chcemy przechowywać?

Baza będzie zawierać informacje o szkoleniach, instruktorach oraz planie oferowanych szkoleń. Harmonogram powninien zawierać datę rozpoczęcia i zakończenia szkolenia oraz referencję do tematu szkolenia oraz instruktora. Zakładamy, że jeden instruktor może prowadzić wiele szkoleń i jedno szkolenie może być prowadzone przez wielu instruktorów.

1.2. Jakie tabele powinna zawierać baza?

• course - Tabela zawierająca szkolenia
• trainer - Tabela zawierająca instruktorów
• schedule - Tabela zawierająca harmonogram szkoleń (tabela asocjacyjna/łącza)

1.3. Diagram bazy

1.3.1. Szkolenia

1.3.2. Instruktorzy

1.3.3. Harmonogram szkoleń

1.3. Zatwierdzenie projektu

• Czy projekt jest zgodny z wymaganiami?
• Czy schemat można zoptymalizować?
• Dodatkowe uwagi?

Implementacja projektu

• Połączenie z serwerem oraz wybór bazy
• Utworzenie tabel
• Wypełnienie tabeli danymi
• Pobieranie danych oraz łączenie tabel
• Zadania

2.1. Połączenie z serwerem oraz wybór bazy

 mysql -h localhost -u root -p

 CREATE DATABASE nobleprog;

 USE nobleprog;

2.2. Utworzenie tabel

 CREATE TABLE IF NOT EXISTS `course` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `course` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `course` (`course`)
 ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='Courses offer' AUTO_INCREMENT=5 ;

 CREATE TABLE IF NOT EXISTS `instructor` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(255) NOT NULL,
  `last_name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `first_name` (`first_name`,`last_name`)
 ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='Instructors' AUTO_INCREMENT=19 ;

 CREATE TABLE IF NOT EXISTS `schedule` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `begin_at` datetime NOT NULL,
  `finish_at` datetime NOT NULL,
  `course` int(10) unsigned NOT NULL,
  `instructor` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `instructor` (`instructor`),
  KEY `course` (`course`)
 ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='Schedule' AUTO_INCREMENT=12 ;

 ALTER TABLE `schedule`
  ADD CONSTRAINT `schedule_ibfk_1` FOREIGN KEY (`course`) REFERENCES `course` (`id`),
  ADD CONSTRAINT `schedule_ibfk_2` FOREIGN KEY (`instructor`) REFERENCES `instructor` (`id`);

2.3. Wypełnienie tabeli danymi

Dane dostępne jako pliki *.txt w dziale z wgranymi plikami

\N	MariaDB Database Administration
\N	SQL Fundamentals
\N	SQL Advanced in MySQL
\N	MySQL Database Administration

course

\N	Bernard	Szlachta
\N	Grzegorz	Pruszczynski
\N	Filip	Stachecki
\N	Lukasz	Sokolowski
\N	Leszek	Albrzykowski

instructor

\N	2012-10-10 09:00:00	2012-10-12 17:00:00	1	14
\N	2012-10-10 09:00:00	2012-10-12 17:00:00	1	15
\N	2012-10-12 09:00:00	2012-10-14 17:00:00	2	15
\N	2012-10-12 09:00:00	2012-10-14 17:00:00	1	14
\N	2012-10-10 09:00:00	2012-10-12 17:00:00	3	16
\N	2012-10-13 09:00:00	2012-10-15 17:00:00	3	17
\N	2012-10-13 09:00:00	2012-10-15 17:00:00	1	14
\N	2012-10-10 09:00:00	2012-10-12 17:00:00	4	18
\N	2012-10-10 09:00:00	2012-10-12 17:00:00	2	18
\N	2012-10-17 09:00:00	2012-10-19 17:00:00	2	14
\N	2012-10-17 09:00:00	2012-10-19 17:00:00	1	14

schedule

2.4. Pobieranie danych oraz łączenie tabel

 SELECT * FROM course;

Pobranie wszystkich danych z tabeli szkoleń

 SELECT * FROM course ORDER BY begin_at ASC ;

 SELECT * FROM course ORDER BY begin_at DESC ;

Pobranie wszystkich danych z tabeli harmonogramu według dat (DESC|ASC)

 SELECT * 
 FROM schedule AS s
 JOIN course AS c ON s.course = c.id
 JOIN instructor AS i ON s.instructor = i.id

Złączenie tabel

2.5. Zadania

• Pobrać informacje jakie kursy prowadzi instruktor X
• Pobrać instruktorów których nazwiska zaczynają się na literę S
• Pobrać informację ile szkoleń zawiera oferta
• Do tabeli instruktorów dodać pole email (jaki typ pola i jaka długość, czy jakieś dodatkowe opcje?)
• Do czego możemy wykorzystać klucze obce?
• Jak ograniczyć dostęp do utworzonej bazy dla wybranego użytkownika?