Transcript
E-biznes Wprowadzenie do systemu PostgreSQL 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, jest dostępnych kilka dodatkowych programów ułatwiających administrowanie i testowanie baz. Jednym z nich 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 program psql. Oprócz normalnych zapytań SQL, psql przetwarza wiele pomocnych meta-poleceń (zaczynających się od znaku backslash: \) oraz umożliwia wykonywanie poleceń powłoki. Technicznie rzecz biorąc psql jest normalną aplikacją kliencką do sytemu PostgreSQL, który sam z kolei pracuje jako serwer. Aby się połączyć z bazą danych musi ona oczywiście istnieć i musimy znać jej nazwę. W ogólności musimy również znać numer portu TCP, na którym nasłuchuje serwer PostgreSQL. Standardowo jest to numer 5432. Najważniejsze meta-polecenia psql’a: \d 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 \g wysyła aktualne zapytanie do serwera (równoważne średnikowi) \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 (litera ‘el’) wyświetla listę nazw baz danych zarządzanych przez serwer razem z ich właścicielami \q zakończenie pracy z psq’el \t włączanie/wyłączanie wyświetlania nazw kolumn i licznika wierszy. \x włączanie/wyłączanie rozszerzonego formatu wypisywania wierszy. \z 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 -p port specyfikacja numeru portu TCP/IP, na którym nasłuchuje serwer postmaster; domyślnym numerem portu jest na ogół 5432
Tworzenie nowej bazy w Postgresie Zanim rozpoczniemy pracę z psql można utworzyć własną bazę. Do utworzenia nowej bazy służy polecenie np. createdb, uruchamiane z linii poleceń powłoki Unix’a: createdb nazwa_bazy utworzy nową bazę o nazwie nazwa_bazy. 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 test_nazwisko 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 i zostanie nawiązane 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) CREATE TABLE nazwa_tabeli (kol1 typ1 [parametry],...); Wykonać następujące polecenia: CREATE TABLE Działy(Id_dz int, Nazwa varchar(20), Miasto varchar(20)); CREATE TABLE Pracownicy(Id_prac int, Nazwisko Varchar(20), Stanowisko varchar(10), Data_zatr DATE, Zarobki float, Premia float, Id_dz int); CREATE TABLE Zaszeregowania( Grupa Int, Dolne float, Górne float); 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 systemie pomocy: \df. (przyjmij, że rok ma 365 dni, CURRENT_DATE – data systemowa). Zadnie 9. (Funkcje agregujące – 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 "Zakres zarobków", AVG(Zarobki) AS "Średnie zarobki" FROM Pracownicy;
