Psql to tekstowy front

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;