Psql to tekstowy front

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).