Nowe wiersze do tabeli wstawiamy poleceniem INSERT

INSERT INTO tabela
VALUES (wartość,...);

INSERT INTO Gatunki
VALUES ('krowa','Europa',FALSE,FALSE);

Wartości można podawać w innej kolejności niż w definicji tabeli, wtedy jednak trzeba po nazwie tabeli podać w nawiasach listę nazw kolumn w odpowiedniej kolejności. Dzięki temu można nie podawać wartości dla kolumn dopuszczających NULL.

INSERT INTO Gatunki(nazwa,chroniony,kontynent)
VALUES ('krowa',FALSE,'Europa');

Daty zapisujemy wyrażeniem

DATE '2008-03-11'

Czas podajemy wyrażeniem

TIME '15:00:07'

TIMESTAMP '2008-03-11 15:00:10'

Zawartość niektórych kolumn może ulegać zmianom. Takich zmian dokonuje się poleceniem UPDATE

UPDATE tabela
SET kolumna = wartość, ...
WHERE warunek;

Zmiana dotyczy wszystkich wierszy, dla których jest spełniony warunek. Jeśli warunek został pominięty, zmiana dotyczy wszystkich wierszy w tabeli!

Czasem zmiana słuzy do uzupełnienia lub korekty informacji

UPDATE Gatunki
SET grozny = FALSE
WHERE nazwa = 'krowa';

Często dokonanie zmian jest wymuszone przez zmiany w otaczającym świecie

UPDATE Zwierz
SET wiek = wiek + 1;

Usuwanie wierszy to najprostsza operacja modyfikacji

DELETE FROM tabela
WHERE warunek;

Usuwane są wszystkie wiersze, dla których jest spełniony warunek. Jeśli warunek został pominięty, usuwane są wszystkie wiersze w tabeli!

DELETE FROM Gatunki
WHERE nazwa = 'krowa';

Czasem poszukiwana informacja znajduje się w kilku tabelach. Aby zapytanie dotyczyło kilku tabel, należy je podać we frazie FROM

Jeśli w używanych tabelach powtarzają się nazwy atrybutów, należy użyć notacji tabela.atrybut

Zaczniemy od przykładu zwykłego złączenie dwóch relacji. Zapytajmy o imiona wszystkich zwierzaków pochodzących z Afryki.

SELECT imie
FROM Zwierz, Gatunki
WHERE Gatunki.nazwa = Zwierz.gatunek
  AND kontynent = 'Afryka';

Bardzo niepraktyczna semantyka formalna mogłaby wyglądać tak

1. Utwórz iloczyn kartezjański wszystkich tabel z frazy FROM.

2. Używając otrzymanego wyniku, postępuj jak dla zapytania opartego na pojedynczej tabeli.

Warto też obejrzeć uproszczoną semantykę operacyjną

• Z każdą tabelą z frazy FROM związujemy ukrytą zmienną krotkową (zwykle nazywającą się tak jak tabela i wskazującą na ,,bieżący” wiersz).

• Przy dwóch tabelach:

  • Dla każdej wartości zmiennej krotkowej z pierwszej tabeli znajdujemy wszystkie ,,pasujące” wiersze z drugiej tabeli, przechodząc po niej jej zmienną krotkową.

  • Dla każdego znalezionego wiersza łączymy go z wierszem z pierwszej tabeli i przetwarzamy jak dla pojedynczej tabeli (tzn. sprawdzamy warunek itd.).

• Analogicznie postępujemy przy większej liczbie tabel wprowadzając kolejne zagnieżdżone pętle iteracyjne.

Nawiasowane wyrażenie SELECT (podzapytanie) można umieszczać we frazach WHERE i FROM (a w pewnych sytuacjach także we frazie SELECT, ale nie próbujcie robić tego w domu).

We frazie FROM po podzapytaniu musi wystąpić zmienna krotkowa, a wynik podzapytania traktowany jest w zewnętrznym zapytaniu tak, jak dodatkowa tabela (a ściślej perspektywa). Przykład: podaj imiona najcięższych zwierzaków z każdego gatunku

SELECT imie
FROM Zwierz, (SELECT gatunek,MAX(waga) AS maks
              FROM Zwierz
              GROUP BY gatunek) mwg
WHERE Zwierz.gatunek = mwg.gatunek
  AND waga = maks;

Jeśli używamy podzapytania we frazie WHERE w zwykłym porównaniu, to powinno ono zwracać pojedynczą wartość. Przykład: podaj imiona zwierzaków, które ważą najwięcej

SELECT imie
FROM Zwierz
WHERE waga = (SELECT MAX(waga)
              FROM Zwierz);

Podzapytania bardzo często używa się w połączeniu z operatorem IN. Może ono wtedy zwracać wiele wartości, ale może mieć tylko jedną kolumnę w wyniku.

Podaj imiona wszystkich zwierzaków pochodzących z Afryki.

SELECT imie
FROM Zwierz
WHERE gatunek IN (SELECT nazwa
                  FROM Gatunki
                  WHERE kontynent = 'Afryka');

• Warunki łączące dla złączeń można zapisywać we frazie FROM

• Jeszcze raz imiona wszystkich zwierzaków pochodzących z Afryki.

SELECT imie
FROM Zwierz JOIN Gatunki
  ON (Gatunki.nazwa = Zwierz.gatunek)
WHERE kontynent = 'Afryka';

Standard SQL-92 podaje operatory złączeń do używania we frazie FROM:

Po takim wyrażeniu dodatkowo podajemy

W SQL relacja to tabela lub perspektywa.

Tworzenie perspektywy

CREATE VIEW nazwa [(atrybut ...)] AS zapytanie;

CREATE VIEW GatunkiAfryki AS
  SELECT *
  FROM Gatunki
  WHERE kontynent = 'Afryka';

Usuwanie perspektywy

DROP VIEW nazwa;

Uproszczona semantyka operacyjna dla zapytań z perspektywami:

• Nazwę perspektywy we frazie FROM w zapytaniu zastępuje się relacjami, na podstawie których ją utworzono.

• Warunki z definicji perspektywy dołącza się do warunków zapytania.

Modyfikacje są dozwolone tylko dla aktualizowalnych perspektyw:

• zbudowanych na podstawie pojedynczej tabeli, oraz

• obejmujących wszystkie atrybuty nie posiadające wartości domyślnych.

Warto zabronić operacji wstawiania i modyfikacji perspektywy dających wiersze, które nie bedą należeć do perspektywy, używając podczas jej tworzenia frazy WITH CHECK OPTION:

CREATE VIEW GatunkiAfryki AS
  SELECT *
  FROM Gatunki
  WHERE kontynent = 'Afryka'
  WITH CHECK OPTION;

• Kursory służą do krokowego przeglądania wyniku zapytania. Używa się ich przede wszystkim w procedurach składowanych.

• Kursor deklarujemy poleceniem DECLARE

  DECLARE kursor_gatkon CURSOR FOR
  SELECT gatunek, kontynent FROM Gatunki;
  

• Z kursora można pobierać kolejne wiersze używając polecenia

  FETCH [ kierunek ] [ ile ]  IN | FROM  cursor
  

• Parametr kierunek definiuje kierunek pobierania wierszy i może być równy

  FORWARD	pobiera następne wiersze (zachowanie domyślne)
  BACKWARD	pobiera poprzednie wiersze.

• Parametr ile określa, ile wierszy należy pobrać i może być równy

  n	Liczba ze znakiem podająca liczbę wierszy do pobrania. Podanie liczby ujemnej zamienia znaczenie FORWARD i BACKWARD.
  ALL	Wszystkie pozostałe wiersze.
  NEXT	Równoważny podaniu 1.
  PRIOR	Równoważny podaniu -1.

Aby pobrać dwa kolejne wiersze z kursora

=> FETCH 2 FROM kursor_gatkon;
gatunek      kontynent
----------------------
lew          Afryka
bóbr         Europa

Po kursorze można się cofać

FETCH -1 FROM kursor_gatkon;

-- Pobierz poprzedni wiersz
FETCH BACKWARD 1 FROM kursor_gatkon;

• Kursor można pozycjonować bez pobierania wierszy poleceniem

  MOVE [ kierunek ] [ ile ]  IN | FROM  kursor
  

  Znaczenie parametrów jest takie, jak dla FETCH.

• Aby przestawić kursor o 5 wierszy do przodu

  MOVE 5 FROM kursor_gatkon;
  

• Na zakończenie kursor należy zamknąć

  CLOSE kursor_gatkon;
  

• Uwaga: kursory działają tylko wewnątrz transakcji, czyli przed ich użyciem należy wykonać

  BEGIN WORK;
  

  (o ile nie jesteśmy wewnątrz otwartej transakcji), a potem (niekoniecznie natychmiast) zamknąć transakcję

  COMMIT WORK;
  

• Nie jest możliwa aktualizacja bieżącego wiersza kursora, trzeba używać niezależnego polecenia UPDATE.

• SQL92 nie zawiera polecenia MOVE, ale za to pozwala na absolutne pozycjonowanie kursora, co w PostgresSQL nie jest zrealizowane.

• Nie występują nigdzie poza standardem. Składnia:

  CREATE ASSERTION nazwa CHECK (warunek);
  

• Przykład użycia:

  CREATE ASSERTION DodatniaWaga
    CHECK (NOT EXISTS (SELECT * FROM Zwierz
                       WHERE waga < 0));
  

Służą do określania typów danych. Polecenie

CREATE DOMAIN AdresTyp AS
  VARCHAR(40) DEFAULT 'Nieznany';

CREATE TABLE Studenci (
  indeks CHAR(6) PRIMARY KEY,
  imie VARCHAR(15) NOT NULL,
  nazwisko VARCHAR(15) NOT NULL,
  adres AdresTyp
);

Dziedzinę usuwamy poleceniem

DROP DOMAIN Adres;

• Polecenie CREATE INDEX definiuje nowy indeks dla podanej tabeli. Pojawiło się dopiero w SQL-99.

  CREATE [ UNIQUE ] INDEX nazwa-indeksu ON tabela
    (kolumna [, ...])
    [ WHERE warunek ]
  

• Parametr UNIQUE powoduje sprawdzanie duplikatów w tabeli podczas tworzenia indeksu i przy każdej modyfikacji.

• Utworzony indeks będzie oparty na kluczu powstałym przez konkatenację podanych kolumn.

• Do usuwania indeksu służy polecenie DROP INDEX.

• Utworzymy indeks na kolumnie kontynent tabeli Gatunki

  CREATE INDEX IndGat ON Gatunki(kontynent);
  

• Indeks może obejmować kilka kolumn.

  CREATE INDEX IndKontChron
    ON Gatunki(kontynent,chroniony);
  

• Usuwanie indeksu:

  DROP INDEX IndGat;
  

• Istnieje też inna postać definicji indeksu:

  CREATE [ UNIQUE ] INDEX nazwa-indeksu ON tabela
    ( funkcja( kolumna [, ... ]) )
    [ WHERE warunek ]
  

• Służy ona do definiowania indeksów funkcyjnych, gdzie wartością klucza indeksowego jest wynik wywołania określonej przez użytkownika funkcji, której parametrami są podane kolumny indeksowanej tabeli.

• Przykładowo, użycie funkcji upper(kolumna) pozwoli podczas indeksowania ignorować rozróżnianie dużych i małych liter.

  CREATE INDEX test1_idx ON test1 (upper(kol1));
  

• Wartość funkcji używanej w indeksie musi zależeć jedynie od jej argumentów. Podczas jej tworzenia należy ją oznaczyć jako ustaloną (immutable).

• Jeśli w definicji indeksu występuje klauzula WHERE, to powstanie indeks częściowy, zawierający pozycje tylko dla wierszy tabeli spełniających podany warunek.

• Na przykład w tabeli zamówień można by zdefiniować indeks tylko dla wierszy zawierających 'tak' w kolumnie zapłacono.

• Wyrażenie w klauzuli WHERE może odwoływać się tylko do kolumn indeksowanej tabeli, nie wolno też używać podzapytań ani funkcji agregujących.

• Służą do otrzymania kolejnych wartości dla kolumn typu całkowitego.

• Tworzenie

  CREATE SEQUENCE sekw_kat
    INCREMENT BY 1 START WITH 1;
  

• Generowanie kolejnej wartości funkcją nextval (jej argumentem jest nazwa generatora):

  SELECT nextval('sekw_kat');
  

• Wywołanie nextval można też umieścić we frazie DEFAULT definicji kolumny w poleceniu CREATE TABLE.

• Do otrzymania bieżącej wartości generatora sekwencji służy funkcja curval.

  SELECT curval('sekw_kat');
  

  zaś do ustawienia na konkretną wartość funkcja setval

  SELECT setval('sekw_kat', 12);
  

• Zamiast umieszczać wywołanie nextval we frazie DEFAULT, można jako typ takiej kolumny podać SERIAL. Zostanie wtedy automatycznie utworzona sekwencja, a kolumna będzie w rzeczywistości typu INT4.

Zmiana hasła użytkownika

ALTER USER nazwa PASSWORD 'nowe-hasło';