Doszliśmy już do dziesiątej części Darmowego Kursu SQL! Tym razem poznasz typy danych SQL. Dowiesz się jak przechowywana jest informacja w bazie danych. Oczywiście omówienie tego zagadnienia wymaga poruszenia tematu konwersji danych i funkcji konwertujących. Poniżej znajdziesz komplet informacji potrzebny do zrozumienia tego zagadnienia.
Dopiero zaczynasz Darmowy Kurs SQL? Witaj! Najlepiej jak zaczniesz tutaj: Kurs SQL #01: Wprowadzenie i środowisko
Czym są typy danych SQL?
Wszelkie dane w relacyjnych bazach danych przechowywane są w postaci wierszy. Jak zapewne pamiętasz, każdy wiersz składa się z wielu kolumn. W obrębie których mamy zawsze zestaw danych o identycznej charakterystyce. Przykładowo CUST_FIRST_NAME z wcześniejszych lekcji przechowuje imiona klientów sklepu. Imię z założenia składać się będzie z ciągu znaków o zmiennej długości. CUST_FIRST_NAME nie będzie przechowywać w sobie dat, liczb, czy danych binarnych. W uproszczeniu znajdować się w niej będzie porostu tekst.
Myśle, że już rozumiesz czym jest typ danych SQL. Jest to porostu charakterystyka określająca jak silnik bazodanowy ma obchodzić się z daną informacją. Przekłada się to, także na zestaw operacji które są dopuszczalne na danym typie danych. Podejście takie zabezpiecza przed np. wykonywaniem operacji matematycznego dodawania na tekście i dacie. Podczas tworzenia tabeli przy pomocy CREATE TABLE musimy zadeklarować typy danych poszczególnych kolumn. Daje nam to dodatkowe zabezpieczenie – próba zapisu danych nie pasujących nie powiedzie się.
Typy danych SQL będą się różnic w zależności od tego z jakiego silnika bazodanowego będziesz korzystać. Musisz natomiast wiedzieć, że po poznaniu nazewnictwa typów danych w jednym RDBMS bez problemu odnajdziesz się w innych środowiskach. Jako że Darmowy Kurs SQL tworzę w oparciu o środowisko Oracle Live SQL, dlatego też przyjrzymy się bliżej typom danych stosowanych w Oracle Database.
Instrukcja DESC
Baza danych Oracle Database dostarcza nam polecenie DESC, pozwalające wyświetlić definicje tabeli. Wystarczy po instrukcji DESC podać nazwę tabeli, którą chcemy sprawdzić.
desc sh.customers;| Column | Null? | Type |
|---|---|---|
| CUST_ID | NOT NULL | NUMBER |
| CUST_FIRST_NAME | NOT NULL | VARCHAR2(20) |
| CUST_LAST_NAME | NOT NULL | VARCHAR2(40) |
| CUST_GENDER | NOT NULL | CHAR(1) |
| CUST_YEAR_OF_BIRTH | NOT NULL | NUMBER(4,0) |
| CUST_MARITAL_STATUS | – | VARCHAR2(20) |
| CUST_STREET_ADDRESS | NOT NULL | VARCHAR2(40) |
| CUST_POSTAL_CODE | NOT NULL | VARCHAR2(10) |
| CUST_CITY | NOT NULL | VARCHAR2(30) |
| CUST_CITY_ID | NOT NULL | NUMBER |
W wyniku komendy widać spis wszystkich kolumn danej tabeli. Ostatnia kolumna wyniku to TYPE. Tutaj właśnie znajdziesz informacje na temat typu danych w danej kolumnie. Opis takiego typu składa się z jego nazwy, a także opcjonalnie długości maksymalnej ciągu znaków i precyzji w przypadku liczb. Dwie ostatnie wspomnianych informacji znajdują się w nawiasach 😉
Typy danych dostępne w Oracle Database
| Code | Data Type |
|---|---|
| 1 | VARCHAR2(size [BYTE | CHAR]) |
| 1 | NVARCHAR2(size) |
| 2 | NUMBER[(precision [, scale]]) |
| 8 | LONG |
| 12 | DATE |
| 21 | BINARY_FLOAT |
| 22 | BINARY_DOUBLE |
| 23 | RAW(size) |
| 24 | LONG RAW |
| 69 | ROWID |
| 96 | CHAR [(size [BYTE | CHAR])] |
| 96 | NCHAR[(size)] |
| 112 | CLOB |
| 112 | NCLOB |
| 113 | BLOB |
| 114 | BFILE |
| 180 | TIMESTAMP [(fractional_seconds)] |
| 181 | TIMESTAMP [(fractional_seconds)] WITH TIME ZONE |
| 182 | INTERVAL YEAR [(year_precision)] TO MONTH |
| 183 | INTERVAL DAY [(day_precision)] TO SECOND[(fractional_seconds)] |
| 208 | UROWID [(size)] |
| 231 | TIMESTAMP [(fractional_seconds)] WITH LOCAL TIMEZONE |
Typy znakowe
Tekstowe typy danych obejmują CHAR, NCHAR, VARCHAR2, NVARCHAR2 i VARCHAR. Typy N* służą do przechowywania znaków Unicode. Najczęściej jednak używa się typów VARCHAR2 i CHAR. CHAR służy do przechowywania ciągu znakowego o określonej wartości. Jeżeli masz pewność że wszelki tekst wpisywany do kolumny będzie miał, powiedzmy zawsze po 10 znaków, wtedy użycie CHAR(10) będzie miało sens. W przypadku wspomnianych wcześniej imion masz pewność, że ciągi znakowe będą miały różne wielkości. Przykładowo imię Jacek i imię Ewa mają różną długość, powinny jednak one trafiać do tej samej kolumny. W tym przypadku właściwym będzie zastosowanie typu danych VARCHAR(20). Podejście takie zakłada, że imiona mają zmienna długość i najdłuższe z nich nie będzie potrzebowało więcej niż 20 znaków/bajtów miejsca.
Typy liczbowe
NUMBER jest typem danych do przechowywania liczb. W nawiasach możemy podać dodatkowe dwa parametry, mówiące o precyzji i skali.
Kolumna w definicji mająca typ danych NUMBER(7, 5) będzie w stanie przechowywać np. liczbę 72,17654. Krótko mówiąc maksymalnie siedem cyfr, w tym 5 miejsc po przecinku.
Datowe typy danych – data i czas
Wyróżnia się następujące typy datowe: DATE, TIMESTAMP, TIMESTAMP WITH WIME ZONE i TIMESTAMP WITH LOCAL TIME ZONE. Dodatkowo mamy też typy danych przechowujące interwały czasowe: INTERVAL YEAR TO MONTH i INTERVAL DAY TO SECOND.
Na tą chwile istotnym jest, aby zapamiętać że typ DATE przechowuje daty zawierające rok, miesiąc, dzień, godzinę, minutę i sekundę. Typy danych TIMESTAMP przechowują również ten sam zestaw danych, dodatkowo jednak umieją przechowywać cząstki sekund, w tym z strefą czasową.
Typy danych RAW i LONG RAW służą do przechowywania danych binarnych takich jak dokumenty, pliki z dźwiękiem, czy materiały video. Typ RAW potrafi przechować do 2000 bajtów danych, LONG RAW do 2GB.
BFILE
BFILE to specjalny typ danych przechowujący wskaźnik wskazujący na plik leżący zewnętrznie poza plikami bazy danych.
BLOB i CLOB
BLOB inaczej Binary Large Object. Typ danych służący do przechowywania dużych ilości danych binarnych. Jego odpowiednikiem do przechowywania dużej ilości danych znakowych jest CLOB – Character Large Object.
Jeżeli chcesz kompleksowo poznać administrację bazą danych PostgreSQL lub planujesz ścieżkę zawodową DBA, wejdź tutaj: Praktyczna Administracja PostgreSQL
Konwersja typów danych
Podczas pracy z bazą danych często może zdarzyć się tak, że będziesz mieć potrzebę zamiany jednego typu danych do innego. Przykładowo daty w bazie zapisane będą jako TIMESTAMP, tobie jednak wystarczyć będzie na potrzeby raportu prosty zapis daty w formie tekstowej. Przy tego typu problemach pomocne okażę się funkcje konwertujące. Pozwalają one na zmianę jednego typu danych w inny. W dalszej części artykułu omówię najczęściej używane z nich.
Funkcja CAST()
Funkcja CAST() wyróżnia się na tle innych. Pozwala zdefiniować jaki typ danych chcemy otrzymać na wyjściu, przyjmując jednocześnie dowolny na wejściu. Większość funkcji konwertujących ma jasno określone typy wyjściowe. Poniżej znajdziesz tabelę reprezentującą zestawienie dopuszczalnych przez CAST() przejść.
Czas przejść do przykładów praktycznych!
select '30/June/2015',
cast('30/June/2015' AS DATE) as output_value
from dual;| ’30/JUNE/2015′ | OUTPUT_VALUE |
|---|---|
| 30/June/2015 | 30-JUN-15 |
Powyżej widzisz przykład działania funkcji CAST(). Pomimo podania daty w postaci zwykłego ciągu tekstowego, na wyjściu otrzymujemy typ DATE sformatowany zgodnie z ustawieniami silnika bazodanowego.
select date_of_birth,
cast(date_of_birth as timestamp) as output_value
from oe.customers;| DATE_OF_BIRTH | OUTPUT_VALUE |
|---|---|
| 09-FEB-70 | 09-FEB-70 12.00.00.000000 AM |
| 19-FEB-46 | 19-FEB-46 12.00.00.000000 AM |
| 12-MAR-63 | 12-MAR-63 12.00.00.000000 AM |
| 21-MAR-44 | 21-MAR-44 12.00.00.000000 AM |
Tym razem skorzystamy z konkretnej tabeli. DATE_OF_BIRTH do przechowywania wykorzystuje typ DATE. Użycie CAST() pozwoliło uzyskać nam przekształcenie DATE do TIMESTAMP.
Funkcja TO_CHAR()
Funkcja TO_CHAR() pozwala zamieniać inne typy danych na napisy. Dopuszcza ona przekazanie opcjonalnego parametru, pozwalającego na zdefiniowanie formatu wyjściowego.
select to_char(12345.67) from dual;| TO_CHAR(12345.67) |
|---|
| 12345.67 |
select to_char(12345.67, '99G999D99') from dual;| TO_CHAR(12345.67,’99G999D99′) |
|---|
| 12,345.67 |
Kolejne zapytanie zmienia liczbę 12345,67 na napis zgodnie z formatem wskazanym jako parametr. W formacie użyto symboli G i D oznaczających stosowne separatory.
select to_char(1234511111.67, '99G999D99') from dual;| TO_CHAR(1234511111.67,’99G999D99′) |
|---|
| ########## |
Warto wiedzieć o tym, że długość liczby będzie za duża dla podanego formatu otrzymamy jako wynik ciąg znaków #.
Funkcja TO_NUMBER()
Kolejna ciekawa funkcja to TO_NUMBER(). Pozwala ona na konwersję ciągu znakowego w NUMBER. Poniżej znajdziesz przykład tego typu konwersji.
select to_number('970.13') from dual;| TO_NUMBER(’970.13′) |
|---|
| 970.13 |
select to_number('970.13') + 20 from dual;| TO_NUMBER(’970.13′) |
|---|
| 990.13 |
Aby udowodnić Ci że wynik to naprawdę NUMBER, a nie dalej CHAR czy VARCHAR postanowiłem wykonać operacje arytmetyczną… jak widać udało się 😉
Kolejna porcja wiedzy za Tobą 🙂 Jeżeli podobały Ci się dotychczasowe materiały udostępnij je dalej przy pomocy guzików mediów społecznościowych!
W kolejnej części Darmowego Kursu SQL nauczę Cię grupować wyniki i obrabiać je przy pomocy funkcji agregujących.
Część jedenasta już dostępna! Kurs SQL #11: Klauzule HAVING i GROUP BY