Transcript
Zaawansowane Bazy Danych Ćwiczenia 2 Wprowadzenie do systemu PostgreSQL, cz. I PostgreSQL jest systemem zarządzania relacyjnymi bazami danych rozprowadzanym na zasadach open source. Twórcą i właścicielem praw autorskich jest Uniwersytet Berkeley w Stanach Zjednoczonych. Ze wszystkich dostępnych darmowych systemów bazodanowych PostgreSQL jest najwszechstronniejszy i najbardziej skalowalny. Po zainstalowaniu PostgreSQL’a, oprócz oczywiście samego serwera, dostępnych jest kilka dodatkowych programów ułatwiających administrowanie systemem i zarządzanie bazami. System PostgreSQL jest oparty o architekturę klien-serwer. Sam serwer może zarządzać bardzo dużą liczbą baz danych, których właścicielami mogą być różni użytkownicy. Przykładowo w instalacji PostgreSQL’a znajdującej się na serwerze venus jest obecnie ok. 1600 różnych baz danych. Jeżeli przyjmiemy, że średni każda baza ma ok. 10 tabel, to widzimy, że serwer zarządza około 16 tyś. tabel! Podstawowym programem, który umożliwia bezpośredni dostęp do baz jest psql. Jest to tekstowy front-end do Postgres'a. Umożliwia on m. in. wprowadzanie interaktywnie zapytań oraz oglądanie ich wyników. Alternatywnie dane wejściowe mogą być zapisane w odpowiednim pliku i skierowane do przetwarzania przez monitor psql. Oprócz normalnych zapytań SQL, monitor psql przetwarza wiele pomocnych meta-poleceń (zaczynających się od znaku backslash: \) oraz umożliwia wykonywanie poleceń powłoki. Najważniejsze meta-polecenia programu psq: \d relacja(tabela) pokazuje wszystkie pola (kolumny) danej relacji (może to być tabela, widok, indeks lub tzw. sekwencja) \d
wywołane bez żadnego parametru pokazuje listę wszystkich tabel, widoków i sekwencji
\h [komenda] pomoc na temat składni polecenia SQL lub informacja o meta-poleceniach \i filename czyta komendy z pliku filename i wykonuje je tak jakby były wprowadzone z klawiatury \l \q \t \z \?
(litera ‘el’) wyświetla listę nazw baz danych zarządzanych przez serwer razem z ich właścicielami zakończenie pracy z psql’em włączanie/wyłączanie wyświetlania nazw kolumn i licznika wierszy. informacje o tabelach i aktualnych prawach dostępu do nich. pomoc na temat meta-poleceń.
Najważniejsze opcje wiersza wywołania monitora psql: -f filename użycie pliku filename jako źródła zapytań zamiast czytania interaktywnego. -l wylistuj wszystkie dostępne bazy danych, a potem zakończ pracę. -o filename wyniki zapytań umieszczaj w pliku filename
Tworzenie nowej bazy w Postgresie Zanim rozpoczniemy pracę z psql można utworzyć własną bazę. Do utworzenia nowej bazy w PostgreSQL’u służy polecenie createdb, uruchamiane z linii poleceń powłoki Unix’a: createdb moja_baza utworzy nową bazę o nazwie moja_baza. Można się teraz dołączyć do niej np. przy pomocy klienta psql. Polecenie createdb zadziała, gdy będziemy zarejestrowani jako użytkownicy Postgresa z prawami do tworzenia bazy danych. Do usunięcia bazy danych z poziomu wiersza poleceń możemy użyć polecenia dropdb. dropdb moja_baza Oczywiści można to również zrobić monitorem wydając odpowiednią komendę SQL w trakcie sesji z psql. Rozpoczynanie sesji Po wydaniu polecenia psql nazwa_bazy zostanie uruchomiony klient psql, które spróbuje nawiązać połączenie z bazą o nazwie nazwa_bazy. Teraz możemy prowadzić normalną interaktywną sesję. Rozpocznijmy od sprawdzenia, jaki użytkownik jest zalogowany i jaki jest czas
=>SELECT CURRENT_USER; =>SELECT CURRENT_TIMESTAMP; W psql polecenie SQL jest zakończone, gdy wprowadzimy średnik i naciśniemy Enter. Dalej będziemy rozważali prosty przykład bazy o następującym schemacie relacji (tabel): Pracownicy(Id_prac, Nazwisko, Stanowisko, Data_zatr, Zarobki, Premia, Id_dz) Działy(Id_dz, Nazwa, Miasto) Zaszeregowania(Grupa, Dolne, Górne) Zadanie 1. (Tworzenie tabeli) Do utworzenia tabeli Products powadzący ćwiczenia wykonał następujące polecenie: CREATE TABLE Products(Id_prod int, Name varchar(20), Price varchar(20), Quantity int); Zadanie 2. (Wstawianie danych) Do tabeli w bazie danych poszczególne wiersze są wstawiane za pomocą instrukcji INSERT o następującej składni: INSERT INTO tabela VALUES (wartość1, wartość2,...); Wykonać następujące polecenia: INSERT INSERT INSERT INSERT INSERT INSERT INSERT
INTO INTO INTO INTO INTO INTO INTO
Pracownicy VALUES (1, ‘Kowalski’, ‘Salesman’,’22-02-90’,300,100,10); Pracownicy VALUES (2, ‘Blake’, ‘Manager’,’1-05-81’,2850,100,30); Pracownicy VALUES (3, ‘Ford’, ‘Analyst’,’3-12-81’,3000,100,20); Działy VALUES (10,’Accounting’,’New York’); Działy VALUES (20,’Sales’,’Chicago’); Zaszeregowania VALUES (1, 700, 1200); Zaszeregowania VALUES (2, 201, 1400);
Uzupełnić tabele: Pracownicy (co najmniej 6 rekordów), Działy (co najmniej 3 rekordy), Zaszeregowania(co najmniej 3 rekordy) danymi takimi jak powyżej. Zadanie 3. (Modyfikowanie danych) Do modyfikowania danych w tabeli służy instrukcja SQL UPDATE o składni: UPDATE tabela SET kolumna = wyrażenie, ... [WHERE warunek]; Wykonać następujące polecenia: UPDATE Pracownicy SET Premia = Premia*1.1; UPDATE Pracownicy SET Zarobki = Zarobki + 200 WHERE Stanowisko = ‘Salesman’; Zadanie 4. (Usuwanie danych) Dane z tabel możemy można usuwać instrukcją DELETE, która ma składnię: DELETE FROM tabela [WHERE warunek]; (Dopisz do tabeli Pracownicy jednego pracownika podając w kolumnie Stanowisko wartość NULL) Wykonać następujące polecenie: DELETE FROM Pracownicy WHERE Stanowisko IS NULL;
Zapytania Centralną instrukcją języka SQL jest instrukcja służąca do wydobywania informacji z bazy danych. Jest nią instrukcja SELECT, określająca z jakich tabel w bazie danych mają być sprowadzone dane, jakie warunki mają one spełniać i w jakiej postaci mają się pojawić przed użytkownikiem. Najprostsza postać instrukcji SELECT jest następująca: SELECT [DISTINCT] kolomna1, kolumna2, ... FROM nazwa_tabeli [WHERE warunek]; Zadanie 5. Wypisz nazwiska, zarobki i stanowiska pracowników firmy: SELECT Nazwisko, Zarobki, Stanowisko FROM Pracownicy; Wypisanie całej zawartości tabeli Pracownicy: SELECT * FROM Pracownicy; Jest to skrócona postać: * oznacza wszystkie kolumny tabeli występującej w klauzuli FROM. Zadanie 6. Wykonać poniższe zapytania: SELECT Nazwisko, Zarobki, Stanowisko FROM Pracownicy WHERE Id_dz = 10; Zapytanie wypisujące działy, w których jest zatrudniony jakiś pracownik: SELECT Id_dz FROM Pracownicy; Zauważmy, że powtarzające się wiersze nie są automatycznie eliminowane z wyników zapytania. Słowo kluczowe DISTINCT oznacza eliminację powtarzających się wierszy: SELECT DISTINCT Id_dz FROM Pracownicy; Użycie klauzuli ORDER BY umożliwia sortowanie wyników: SELECT Nazwisko, Zarobki FROM Pracownicy ORDER BY Nazwisko; Domyślnie porządek sortowania jest rosnący. Jeżeli chcemy ustalić porządek sortowania na malejący, używamy słowa DESC: SELECT Nazwisko, Zarobki FROM Pracownicy ORDER BY Zarobki DESC; Zadanie 7. W zapytaniu może wystąpić pole wyliczeniowe. Używamy wtedy konstrukcji AS alias. Wykonać obliczania pełnego wynagrodzenia (Zarobki + Premia): SELECT Nazwisko, (Zarobki + Premia) AS Wynagrodzenie, Id_dz FROM Pracownicy; Zadanie 8. Wykonać zapytanie SELECT, które poda okres zatrudnienia każdego pracownika w latach. Należy znaleźć odpowiednie funkcje w helpie: \df. (przyjmij, że rok ma 365 dni, CURRENT_DATE – data systemowa).
Zadnie 9. (Funkcje agregujące i podsumowania) Dane z jednej lub więcej tabel mogą zostać podsumowane przy użyciu jednej z funkcji agregujących. Mówimy wtedy o zapytaniach z podsumowaniami. W każdym dialekcie SQL’a występują następujące funkcje agregujące: COUNT, AVG, SUM, MAX, MIN Wykonać poniższe polecenia: Maksymalne zarobki: SELECT max(Zarobki) FROM Pracownicy; SELECT max(Zarobki) AS "Max wynagrodzenie" FROM Pracownicy; Liczba pracowników firmy: SELECT COUNT(Id_prac) AS "Liczba pracowników firmy" FROM Pracownicy; Statystyka zarobków pracowników firmy: SELECT MIN(Zarobki) AS "Min zarobki", MAX(Zarobki) AS "Max zarobki", MAX(Zarobki) – MIN(Zarobki) AS "Rozp zarobków", AVG(Zarobki) AS "Średnie zarobki" FROM Pracownicy; Zadanie 10 (Skrypty przetwarzane przez psql) Można utworzyć plik tekstowy, który zawiera polecenia SQL i meta-polecenia psql’a. Następnie można nakazać monitorowi psql wykonanie wszystkich zawartych w tym pliku poleceń. Z wnętrza psql’a robimy to tak: =>\i filename.sql a) Utwórz skrypt, który utworzy w bieżącej bazie tabelę Klienci (pola: Id_Klienta, Nazwa, Adres, Telefon) i dopisze 4 klientów (odpowiednie instrukcje CREATE TABLE i INSERT). b) Utwórz skrypt, który dopisze do bazy klienci 4 nowych klientów, których dane znajdują się w pliku tekstowym o nazwie klienci.txt (format: pola rozdzielane tabulatorem). (Wsk. Kopiowanie z tabeli do pliku:\copy tabela to plik; Kopiowanie z pliku do tabeli: \copy tabela from plik) c) Przenieś dane z tabeli Pracownicy z bazy Kadry-cw6.mdb do tabeli Pracownicy w swojej bazie w Postgresie (pola: Id_prac – kolejny numer, Nazwisko, Imię, Stanowisko, Data zatrudnienia, Stawka=Zarobki, Premia – wyliczona kwota premii, Id_dz - NULL).
