1 Temat: Wczytywanie danych z pliku tekstowego.
LOAD DATA INFILE „nazwa_pliku” INTO TABLE nazwa_tabeli CHARACTER SET kodowanie_znaków
FIELDS TERMINATED BY ‘separator_pól ‘ LINES TERMINATED BY ‘separator_wierszy’
IGNORE numer LINES;
Ćwiczenie 5
Utwórz nową bazę danych o nazwie kino.
1. Utwórz nową bazę danych: CREATE DATABASE kino_nazwisko; {wpisz swoje nazwisko}
2. Wejdź do bazy danych: USE kino_nazwisko;
3. Pojawi się komunikat, że zmieniła się baza danych: Database Changed 4. W bazie danych zaprojektuj strukturę pierwszej tabeli: filmy
CREATE TABLE filmy (
id INT PRIMARY KEY,
tytul VARCHAR(20) NOT NULL, godzina TIME NOT NULL );
Zatwierdź klawiszem ENTER.
5. Wyświetl strukturę zaprojektowanej tabeli.
6. Wczytaj dane z pliku tekstowego filmy.txt do tabeli filmy. Najpierw umieść ten plik tekstowy na dysku C w folderze kino_nazwisko:
C\Program Files\WebServ\mysql\data\kino_nazwisko\filmy.txt.
Następnie skorzystaj z polecenia:
LOAD DATA INFILE „filmy.txt” INTO TABLE filmy
FIELDS TERMINATED BY ‘ ‘ LINES TERMINATED BY ‘\r\n’;
w przypadku, gdy spacja jest separatorem pól (\t – gdy tabulator), a Enter separatorem wierszy.
7. Wyświetl na ekranie zawartość tabeli filmy.
8. Zaprojektuj drugą tabelę: osoby CREATE TABLE osoby (
id INT PRIMARY KEY, imie VARCHAR(20) NOT NULL, nazwisko VARCHAR(20) NOT NULL, film_id INT NOT NULL
);
Zatwierdź klawiszem ENTER.
9. Wyświetl strukturę zaprojektowanej tabeli.
10. Wczytaj dane z pliku tekstowego osoby.txt do tabeli filmy. Najpierw umieść ten plik tekstowy na dysku C w folderze kino_nazwisko jak w punkcie 6. Następnie skorzystaj z polecenia:
LOAD DATA INFILE „osoby.txt” INTO TABLE osoby FIELDS TERMINATED BY ‘ ‘ LINES TERMINATED BY ‘\r\n’;
11. Wyświetl na ekranie zawartość tabeli osoby.
2 12. Dodaj klucz obcy do tabeli osoby (dla pola film_id).
ALTER TABLE osoby ALTER TABLE nazwa_tabeli ADD CONSTRAINT filmy_fk ADD CONSTRAINT nazwa FOREIGN KEY (film_id) FOREIGN KEY (nazwa_kolumny)
REFERENCES filmy(id); REFERENCES nazwa_tabeli(nazwa_kolumny);
13. Wyświetl uczniów, którzy nie mogą pójść do kina (33 rows in set).
14. Policz uczniów, którzy nie mogą pójść do kina.
15. Wyświetl uczniów, którzy mogą pójść do kina (467 rows in set).
16. Policz uczniów, którzy mogą pójść do kina (467).
17. Podaj ile jest dziewczynek wśród uczniów, którzy pójdą do kina (202).
18. Podaj ilu jest chłopców wśród uczniów, którzy pójdą do kina (265).
19. Podaj zestawienie, w którym umieścisz w kolejnych wierszach imiona i nazwiska uczniów, którzy pójdą na seanse z filmem „Film_o_pszczolach” (12).
20. Podaj zestawienie, w którym umieścisz w kolejnych wierszach imiona i nazwiska uczniów, którzy pójdą na seanse z filmem „Ten_obcy” (72).
21. Podaj zestawienie, w którym umieścisz w kolejnych wierszach godzinę rozpoczęcia seansu, tytuł filmu i liczbę uczniów, którzy pójdą na dany seans. Wiersze powinny być posortowane rosnąco według godzin seansu, a następnie według tytułu filmu.
SELECT godzina, tytul,
COUNT(nazwisko) AS „liczba_widzow”
FROM filmy, osoby WHERE film_id=filmy.id GROUP BY godzina, tytul ORDER BY godzina, tytul;
Ćwiczenie 6
Utwórz samodzielnie nową bazę danych o nazwie Ośrodki. Zaprojektuj dwie tabele. Zaimportuj do nich dane. Utwórz zapytania.
1. Utwórz nową bazę danych osrodki_nazwisko: CREATE DATABASE osrodki_nazwisko. Uaktywnij ją (otwórz).
2. W bazie danych zaprojektuj strukturę pierwszej tabeli: osrodki.
3. Wyświetl strukturę zaprojektowanej tabeli.
4. Umieść plik tekstowy osrodki.txt na dysku C w folderze osrodki_nazwisko.
5. Wczytaj dane z pliku tekstowego osrodki.txt do tabeli osrodki. C\\Program Files\\WebServ\\mysql\\data\\osrodki_nazwisko\\osrodki.txt.
6. Wyświetl na ekranie zawartość tabeli osrodki.
7. Powtórz czynności z punktów 2-6 dla drugiej tabeli.
8. Dodaj klucz obcy do tabeli ankiety.
3 9. Utwórz zapytanie wyświetlające listę z kodami oraz nazwami ośrodków znajdujących się na
terenie Trójmiasta. Wyniki uporządkuj rosnąco według nazwy miasta.
10. Utwórz zapytanie wyświetlające listę z numerem ankiety i datą przyjazdu do ośrodków po 14 sierpnia 2004 roku.
11. Utwórz zapytanie wyświetlające listę z kodami oraz nazwami ośrodków znajdujących się w miejscowościach, których nazwy zaczynają się na literę „K”
12. Utwórz zapytanie wyświetlające listę z kodami oraz nazwami ośrodków znajdujących się w miejscowościach, których nazwy zaczynają się na literę „K” lub „Z”.
13. Utwórz zapytanie podające liczbę ankietowanych wypoczywających w poszczególnych miejscowościach.
4 14. Utwórz zapytanie wyświetlające listę ośrodków z następującymi informacjami: kod ośrodka oraz
średnią arytmetyczną z odpowiedzi na piąte pytanie (dotyczyło ono ogólnej oceny całego wypoczynku) udzielonych przez wszystkich ankietowanych turystów danego ośrodka.
15. Utwórz zapytanie wyświetlające nr_ankiety i nazwy ośrodków, w których przebywający turyści pobyt w ośrodku ocenili najwyżej, czyli na wszystkie pytania w ankiecie podali 6.
16. Utwórz listę ośrodków (miejscowość, nazwę ośrodka i liczbę ankietowanych osób), w których wykonano badania ankietowe.
17. Utwórz zapytanie podające liczbę ośrodków wymienionych w poprzednim punkcie (wynik=21).
Trzeba skorzystać z zapytania z podzapytaniem w klauzuli FROM. Wynikiem zapytania typu SELECT jest tablica zawierająca określone kolumny i dane. Skoro jest to tablica, możliwe jest wykonanie na niej kolejnego zapytania typu SELECT. W ten sposób otrzymamy zapytanie złożone o ogólnej postaci:
SELECT kolumny_zapytania FROM
(SELECT kolumny_podzapytania FROM tablice
WHERE warunki_podzapytania ) ) AS nazwa
WHERE warunki_zapytania;
Należy zwrócić uwagę na argument nazwa, którym jest nazwa tablicy wynikowej, zwróconej przez podzapytanie. Może ona być dowolna, jednak w bazach MySQL, PostgreSQL i MS SQL musi być zawsze obecna.
