Notatki z nauki SQL część 2 – typy danych, ograniczenia, sekwencje i indexy


Typy danych

/* Treść poniższego wpisu, choć w większości pewnie uniwersalna, odnosi się jednak głównie do serwera PostgreSQL. */

Typy liczbowe mogą zawierać możliwość podania precyzji, skali lub obydwu.

Precyzja (precision) – całkowita ilość cyfr w liczbie.
Skala (scale) – ilość cyfr po separatorze. W opcji max może równać się precyzji przy założeniu, że przed separatorem będzie zero.

Przykład:

NUMERIC(5) – nie może zawierać więcej niż pięć cyfr – 55555
NUMERIC(5,2) – nie może zawierać więcej niż pięć cyfr, w tym 2 po separatorze – 555.55

SMALLINT Całkowicie liczbowy, 16 – bitowy / 2 bajty.
INTEGER Całkowicie liczbowy, 32 – bitowy / 4 bajty.
BIGINT Całkowicie liczbowy, 64 – bitowy / 8 bajtów.
REAL Zmiennoprzecinkowy, 32 – bitowy / 4 bajty.
DOUBLE PRECISION Zmiennoprzecinkowy, 64 – bitowy / 8 bajtów.
FLOAT(p) Zmiennoprzecinkowy z możliwością podania precyzji (max 53), np. FLOAT(53).
NUMERIC(p, s) Daje możliwość zadeklarowania bardzo dużej precyzji (max 1000 cyfr) oraz skali. Jest rekomendowany w dokumentacji postgreSQL do przetrzymywania m. in. pieniędzy, gdzie uniwersalna precyzja i skala może mieć przykładową wartość – (19, 4). Wadą jest, że obliczenia na wartościach tego typu są o wiele wolniejsze niż na typach całkowitych lub FLOAT.
DECIMAL(p, s) To samo co numeric.
MONEY Ponoć lepiej nie używać.
DATE Data
TIME  Czas
TIMESTAMP  Data i czas
CHAR(liczba znaków)
character
Typ łańcuchowy, wymaga podania liczby określającej długość znaków – jeśli nie zostanie podana PostgreSql ustawi ją na 1 znak. Jeżeli wartości wierszy będą krótsze od zadeklarowanej długości, reszta zostanie uzupełniona spacjami.
VARCHAR(liczba znaków)
character varying
Typ łańcuchowy, od powyższego różni się tym, że wiersze kolumny nie zostaną wypełnione spacjami jeżeli wartości będą krótsze od zadeklarowanych. Daje również możliwość, ale nie wymaga określenia długości łańcucha.
TEXT Typ łańcuchowy, raczej zalecany do użycia gdy nie wiadomo jaka będzie maksymalna długość ciągu.

Constraints – Atrybuty kolumn/ograniczenia

Ustawia się je na kolumnach w trakcie tworzenia tabeli lub dodaje do już istniejących kolumn. W przypadku drugiej opcji część ograniczeń ustawia się przez SET jak DEFAULT, czy NOT NULL. Inne dodaje się przez ADD CONSTRAINT jak UNIQUE, czy PRIMARY KEY

 PRIMARY KEY Kolumna z tym atrybutem będzie kluczem głównym tabeli. Klucz podstawowy w tabeli może być tylko jeden, lecz może być złożony z większej ilości kolumn. Zawierają one domyślnie atrybuty NOT NULL oraz UNIQUE, są też automatycznie indeksowane.
 NOT NULL W kolumnie nie zapiszą się wartości puste. Nie można usunąć tego atrybutu z kolumny, która jest jednocześnie kluczem głównym.
 UNIQUE Wszystkie wartości danej kolumny muszą być unikatowe. Utworzony zostanie automatycznie index dla tej kolumny.
 DEFAULT Ustawia wartość domyślną w zależności od typu danych kolumny, np.  0 lub ′brak danych′.
 SERIAL Zapis tylko dla serwera PostgreSql. Kolumna będzie trzymała “samoinkrementującą” się wartość typu całkowitego – 32 bity. SERIAL w dokumentacji wymieniany jest w jednej tabelce razem z liczbowymi typami danych. Nie jest on jednak typem danych – jest pewnym skrótowym zapisem łączącym wykorzystanie sekwencji oraz atrybutów.
 BIGSERIAL Podobnie jak wyżej tyle będzie to “samoinkrementująca” się wartość typu całkowitego – 64 bity.

Przykłady:

Utworzenie tabeli z kolumnami posiadającymi ograniczenia.

CREATE TABLE nazwa_tabeli (
nazwa_kolumny1 INTEGER PRIMARY KEY,
nazwa_kolumny2 VARCHAR(30) NOT NULL,
nazwa_kolumny3 VARCHAR(50) NOT NULL,
nazwa_kolumny4 CHAR(11) NOT NULL UNIQUE
);

Ustawienie oraz usunięcie atrybutu NOT NULL z kolumny.

ALTER TABLE nazwa_tabeli ALTER COLUMN nazwa_kolumny SET NOT NULL;
ALTER TABLE nazwa_tabeli ALTER COLUMN nazwa_kolumny DROP NOT NULL;

Dodanie oraz usunięcie atrybutu UNIQUE z kolumny.

ALTER TABLE nazwa_tabeli ADD CONSTRAINT nazwa_ograniczenia UNIQUE(nazwa_kolumny);
ALTER TABLE nazwa_tabeli DROP CONSTRAINT nazwa_ograniczenia;

Sekwencje i indexy

Konfigurowanie tabel i ich kolumn łączy się z pojęciem indeksu oraz sekwencji. Daleko nie szukać, w powyższym przykładzie nazwa_ograniczenia będzie widoczna w tabeli jako jej index. Co to index?

  • Indeks (index) – struktura wewnątrz serwera, domyślnie dla PostgreSql jest to drzewiasty typ Btree, której zawartość po utworzeniu indeksu nie jest widoczna dla użytkownika serwera. Indeksy służą przyśpieszeniu wykonywania zapytań do bazy, poprzez zawężenie ilości wierszy do przeszukania, na danej kolumnie. Bez indeksowania efektem zapytania jest liniowe przeszukanie wszystkich wierszy. Index w uproszczeniu przechowuje posortowaną zawartość kolumny oraz wskaźnik na fizyczne umiejscowienie rekordu w bazie. Przeszukiwane są wpierw indeksy tabel, a następnie odczytywane konkretne rekordy. Wadą może być wolniejszy zapis danych, bo wraz z zapisem do bazy aktualizowane są indeksy. Zwiększa się również ogólna waga bazy, gdyż suma indeksów danej tabeli może zajmować podobnie lub więcej miejsca, niż zawartość wszystkich jej kolumn. Może też być tak, że indeksy nie powodują przyśpieszenia odczytów.

Przykładowe utworzenie  indeksu oraz usunięcie indeksu:

CREATE INDEX nazwa_indeksu ON nazwa_tabeli (kolumna1, kolumna2, ...);
DROP INDEX nazwa_indexu;

Przykładowe sposoby na podgląd istniejących indeksów dla nazw tabeli lub schematu:

\d nazwa_tabeli
SELECT * FROM pg_indexes WHERE schemaname = 'public';
SELECT * FROM pg_indexes WHERE tablename = 'nazwa_tabeli';
  • Sekwencja (sequence) – generuje sekwencję numerów w oparciu o utworzoną osobną jednorzędową tabelę oraz odpowiednią funkcję manipulującą. Sekwencje zwykle są wykorzystywane do generowania niepowtarzalnych identyfikatorów dla wierszy kolumn.

Super przykładem jest tabela z utworzoną kolumną typu SERIAL (PostgreSql):

CREATE TABLE nazwa_tablicy (
    nazwa_kolumny SERIAL
);

­Powyższy zapis tworzy sekwencję i podpina ją pod utworzoną tabelę oraz daną kolumnę. Jest równoważny z poniższym zapisem:

CREATE SEQUENCE nazwa_tabeli_nazwa_kolumny_seq;
CREATE TABLE nazwa_tabeli (
    nazwa_kolumny integer NOT NULL DEFAULT nextval('nazwa_tabeli_nazwa_kolumny_seq')
);
ALTER SEQUENCE nazwa_tabeli_nazwa_kolumny_seq OWNED BY nazwa_tabeli.nazwa_kolumny;

­Jak widać SERIAL jest uproszczeniem zapisu, prawdziwym typem danych jest INTEGER
z atrybutami NOT NULL i DEFAULT. Funkcja nextval przesuwa aktualną wartość sekwencji
­do następnej oraz ją zwraca. Domyślnymi wartościami dla wierszy kolumny z SERIAL
­będą właśnie wartości zwracane przez tę funkcję.

Dodaj komentarz

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *