Typy danych SQL - Nauczycielka informatyki

TYPY DANYCH – WPROWADZENIE

Celem lekcji jest zapoznanie z typami danych. Dane w tabelach muszą być określonego typu i definiuje się je na etapie tworzenia tabeli. Standard języka SQL określa typy danych w kilku kategoriach. Ponadto w różnych środowiskach baz danych mogą być dołączone dodatkowe typy danych. Z tego właśnie względu przenoszenie baz danych pomiędzy poszczególnymi systemami może zakończyć się błędem. Rozróżnia się typy łańcuchowe (znakowe i bajtowe), liczbowe, daty i czasu, przestrzenne, JSON, walutowe oraz specjalne.

TYPY DANYCH SQL – TYPY ZNAKOWE

STANDARD SQL	MS SQL SERVER	MySQL
character	char(n)	char(n)
character varying	varchar(n)	varchar
national character	nchar(n)	national character
–	–	nvarchar2(n)
national character varying	nvarchar(n)	national charachter varying
–	ntext, text, image	–

CHAR – przechowuje do 255 znaków – pole o stałej liczbie znaków np. char(20). Jeśli ilość znaków jest mniejsza tzn. tekst jest krótszy, zostaje uzupełniony spacjami do wartości jaka została zadeklarowana, dłuższy tekst zostaje obcięty.

VARCHAR przechowuje do 255 znaków – pole o stałej liczbie znaków np. varchar(20). Jeśli ilość znaków jest mniejsza tzn. tekst jest krótszy, NIE zostaje uzupełniony spacjami do wartości jaka została zadeklarowana, dzięki czemu oszczędzane są zasoby pamięci. VARCHAR to skrót od CHARACTER VARYING.

Uwaga – MySQL jest zgodny ze standardową specyfikacją SQL i nie usuwa końcowych spacji z wartości VARCHAR

NATIONAL VARCHAR (NVARCHAR) – To standardowy sposób SQL definiujący, że kolumna VARCHAR powinna używać jakiegoś wstępnie zdefiniowanego zestawu znaków. MySQL używa standardu kodowania znaków narodowych UNICODE (utf8) jako predefiniowanego zestawu znaków, co oznacza, że na jeden znak potrzeba 2 bajtów pamięci.

Wskasówki do użycia nchar lub nvarchar , użyj:

nchar, gdy rozmiary wpisów danych kolumn są spójne.
nvarchar, gdy rozmiary wpisów danych kolumn znacznie się różnią.
nvarchar(max), gdy rozmiary wpisów danych kolumn znacznie się różnią, a długość ciągu może przekraczać 4000 par bajtów.

Zaleca się używanie typów o zmiennej ilości znaków.

Inne typy danych znakowych to:

NTEXT – stosowany do przechowywania dużej ilości znaków

Następujące typy danych: ntext, text i image zostaną usunięte w przyszłej wersji programu SQL Server. Unikaj używania tych typów danych w nowych pracach programistycznych i planuj modyfikowanie aplikacji, które obecnie z nich korzystają. Zamiast tego użyj nvarchar (max), varchar (max) i varbinary (max).

TYPY DANYCH SQL – TYPY BINARNE

Są one reprezentowane w postaci liczb hexadecymanlych (szesnastkowych) i występują zarówno w MS SQL jak i MySQL. Należą do nich: BINARY [(M)] i VARBINARY (M) są podobne do typu CHAR i VARCHAR ale przechowują binarne ciągi bajtów zamiast niebinarnych ciągów znaków. Opcjonalna długość M reprezentuje długość kolumny w bajtach. Jeśli pominięte, M przyjmuje wartość domyślną 1. Oba typy przechowują dane binarne do długości maksymalnie 8000 bajtów, lecz w typie binarny są to długości stałe , a w typie varbinary długości zmienne.

TYPY DANYCH SQL – TYPY DATY I CZASU

Datetime – zapisywany na 8 bajtach, określa datę i czas z dokładnością do 1 sekundy

YYYY-MM-DD HH:MM:SS

Date – zapisywany na 3 bajtach YYYY-MM-DD

Time – zapisywany na trzech bajtach w formacie HH-MM-SS

Year – Rok z przedziału od 1901 do 2155 oraz wartość 0000. Wartość typu YEAR jest wyświetlana w formacie YYYY

Timestamp – zapisywany na 4 bajtach w formacie YYYYMMDDHHMMSS; dopuszczalny zakres do 2037 roku

SMALLDATETIME – 4 bajty

MS SQL SERVER	MYSQL
date	date
time	time
datetime datetime2, datetime3	datetime –
–	timestamp
smalldatetime	Year

Przykładowe typy daty i czasu – porównanie

TYPY DANYCH SQL – TYPY LICZBOWE

Wśród typów liczbowych można wyróżnić typy liczb całkowitych i typy rzeczywistych. Wszędzie tam, gdzie dane na pewno będą miały wartość całkowitą, powinno się stosować właśnie te typy.

STANDARD SQL	MS SQL SERVER	MYSQL
integer	int	integer (n)
smallint	smallint	smallint
–	bigint	–
–	tinyint	–
float	float	float(n)
–	real	–
–	decimal(p,s)	decimal
–	numeric(p,s)	numer(p,s)

Przykładowe typy liczbowe

Rodzaj	Pamięć	Zakres ze znakiem	Zakres bez znaku
Tinyint	1 bajt	-128 do 127	0-255
Smallint	2 bajty	-32768 do 32767	0-65535
Int	4 bajty	-2 147 483 648 do 2 147 483 647	0- 4 294 967 295
Bigint	8 bajtów	-2^63 2^63-1	od 0 do 18 446 744 073 709 551 615

Typy liczb całkowitych

Typy liczb rzeczywistych to takie typy które mają wartości po przecinku. Dlatego dla określenia danych, których ilość miejsc po przecinku jest znana stosuje się: decimal[ (p[ ,s] )] oraz numeric[ (p[ ,s] )]

Numeric (precyzja, skala) -precyzja określa maksymalną liczbę cyfr, a skala określa, ile liczb znajduje się po przecinku numeric(3,2). Wartości domyślne to 10 dla precyzji i 0 dla skali. Gdy używana jest maksymalna precyzja, prawidłowe wartości należą do zakresu od – 10 ^ 38 +1 do 10 ^ 38 – 1.

float (n)– liczba zmiennoprzecinkowa zajmująca 4 lub 8 bajtów pamięci, przy czym n oznacza precyzję podaną w bitach. N z zakresu:

1-24 oznacza precyzję pojedynczą (dokładność do 7 miejsc po przecinku) 4 bajty
25-53 precyzja podwójna (do 15 miejsc po przecinku) 8 bajtów

real – liczba zmiennoprzecinkowa zajmująca 4 bajty będąca odpowiednikiem float(24)

TYPY DANYCH SQL – TYPY WALUTOWE

Można je spotkać TYLKO W MSSQL SERVER. Są to:

MONEY – 8 bajtów
SMALLMONEY – 4 bajty

Najczęściej wykorzystywane do przechowywanie wartości walutowych z dokładnością do 4 liczb po przecinku.

TYPY DANYCH SQL – specjalne

Typy wyodrębnione z powyższych, np., text, image pozwalające na przechowywanie dużej ilości danych. Mają zostać usunięte z kolejnej wersji MSQL.

Typ bit – określa wartości logiczne (0/1, włączony/wyłączony, prawda/fałsz)

SKŁADNIA ŁAŃCUCHOWYCH TYPÓW DANYCH W MySQL

Do łańcuchowych typów danych, zwanych inaczej typami danych ciągu należą: CHAR, VARCHAR, BINARY i VARBINARY , BLOB, ENUM, SET oraz TEXT.

W przypadku definicji kolumn ciągów znaków (typy CHAR, VARCHAR i TEXT) MySQL interpretuje specyfikacje długości w jednostkach znakowych. Natomiast przy kolumnach z ciągami binarnymi (typy BINARY, VARBINARY i BLOB) MySQL interpretuje specyfikacje długości w jednostkach bajtów. Definicje kolumn dla typów danych ciągów znaków CHAR, VARCHAR, typy TEXT, ENUM, SET mogą określać zestaw znaków kolumny i sortowanie. Pokażę to na ćwiczeniu:

Utwórzmy bazę danych typ, a w niej zdefiniujmy tabelę str, która utworzy kolumnę o nazwie k1, posiadającą zestaw znaków utf8 z domyślnym sortowaniem dla tego zestawu znaków oraz kolumnę o nazwie k2, posiadającą zestaw znaków latin1 i sortowanie z uwzględnieniem wielkości liter (_cs).

CREATE DATABASE typ;
USE typ;
CREATE TABLE str
(
    k1 VARCHAR(20) CHARACTER SET utf8,
    k2 VARCHAR(20) CHARACTER SET latin1 COLLATE latin1_general_cs
);

CHARACTER SET (synonim dla tej składni to CHARSET) określa zestaw znaków. W razie potrzeby sortowanie zestawu znaków można określić za pomocą atrybutu COLLATE, wraz z innymi atrybutami. Określenie atrybutu binarnego CHARACTER SET dla typu danych łańcucha znakowego powoduje utworzenie kolumny jako odpowiadającego typu danych łańcucha binarnego: CHAR staje się BINARY, VARCHAR staje się VARBINARY, a TEXT staje się BLOB. Nie występuje to w przypadku typów danych ENUM i SET; są tworzone zgodnie z deklaracją. Załóżmy, że określasz tabelę za pomocą tej definicji:

Wynikowa tabela ma następującą strukturę:

Atrybut BINARY jest niestandardowym rozszerzeniem MySQL, będącym skrótem do określania binarnego (_bin) sortowania zestawu znaków kolumny (lub domyślnego zestawu znaków tabeli, jeśli nie określono zestawu znaków kolumny). W tym przypadku porównanie i sortowanie są oparte na numerycznych wartościach kodów znaków. Załóżmy, że określasz tabelę za pomocą tej definicji:

Innymi słowy tabela wynikowa daje rezultat:

CREATE TABLE str2 (
  k1 VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_bin,
  k2 TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin
) CHARACTER SET utf8mb4

W MySQL 8.0 to niestandardowe użycie atrybutu BINARY jest niejednoznaczne, ponieważ zestaw znaków utf8mb4 ma wiele sortowań _bin. Od wersji MySQL 8.0.17 atrybut BINARY jest przestarzały i należy spodziewać się, że jego obsługa zostanie usunięta w przyszłej wersji MySQL. Aplikacje należy dostosować tak, aby zamiast tego używały jawnego sortowania _bin. Natomiast użycie funkcji BINARY do określenia typu danych lub zestawu znaków pozostaje niezmienione. Atrybuty ASCII oraz UNICODE są już przestarzałe. Od wersji 8.0.28 zamiast nich używa się odpowiednio CHARACTER SET latin1 oraz CHARACTER SET ucs2.

Porównanie i sortowanie kolumn znaków jest oparte na sortowaniu przypisanym do kolumny. W przypadku typów danych CHAR, VARCHAR, TEXT, ENUM i SET można zadeklarować kolumnę z sortowaniem binarnym (_bin) lub atrybutem BINARY, aby spowodować, że porównanie i sortowanie będą używać podstawowych wartości kodu znaków zamiast porządkowania leksykalnego. Funkcja zgodności mówi, że typ danych CHAR BYTE jest aliasem dla typu danych BINARY.

Ponieważ czasem musisz zachować zgodność ze starymi aplikacjiami zależącymi od istniania kolumny, której w rzeczywistości nie używają wartośći w MySQL można utworzyć kolumnę typu CHAR (0). Drugi przypadek użycia CHAR (0) to wtedy gdy potrzebujesz kolumny, która może przyjmować tylko dwie wartości: Kolumna zdefiniowana jako CHAR (0) NULL zajmuje tylko jeden bit i może przyjmować tylko wartości NULL i ” (pusty ciąg) ..

TINYBLOB

Kolumna BLOB 0 – 255 (2⁸ – 1) bajtów. Każda wartość TINYBLOB jest przechowywana przy użyciu 1-bajtowego prefiksu, który wskazuje liczbę bajtów wartości.

[CHARACTER SET nazwa_zestawu znaków] [COLLATE nazwa_zestawu]

Kolumna TEXT o maksymalnej długości 255 (2⁸ – 1) znaków. Efektywna maksymalna długość jest mniejsza, jeśli wartość zawiera znaki wielobajtowe. Każda wartość TINYTEXT jest przechowywana przy użyciu 1-bajtowego prefiksu, który wskazuje liczbę bajtów wartości.

BLOB [(M)]

Kolumna BLOB o maksymalnej długości 65 535 (2¹⁶ – 1) bajtów. Każda wartość BLOB jest przechowywana przy użyciu 2-bajtowego prefiksu, który wskazuje liczbę bajtów wartości. Dla tego typu można podać opcjonalną długość M. Jeśli tak się stanie, MySQL utworzy kolumnę jako najmniejszy typ BLOB wystarczająco duży, aby pomieścić wartości o długości M bajtów.

TEXT [(M)] [CHARACTER SET nazwa_zbioru znaków] [COLLATE nazwa_zestawu]

Kolumna TEXT o maksymalnej długości 65 535 (2¹⁶ – 1) znaków. Efektywna maksymalna długość jest mniejsza, jeśli wartość zawiera znaki wielobajtowe. Każda wartość TEXT jest przechowywana przy użyciu 2-bajtowego prefiksu, który wskazuje liczbę bajtów wartości. Dla tego typu można podać opcjonalną długość M. Jeśli tak się stanie, MySQL utworzy kolumnę jako najmniejszy typ TEKSTU, wystarczająco duży, aby pomieścić wartości M znaków.

MEDIUMBLOB

Kolumna BLOB o maksymalnej długości 16777215 (2²⁴ – 1) bajtów. Każda wartość MEDIUMBLOB jest przechowywana przy użyciu 3-bajtowego prefiksu, który wskazuje liczbę bajtów wartości

MEDIUMTEXT [CHARACTER SET nazwa_zestawu znaków] [COLLATE nazwa_zestawu]

Kolumna TEXT o maksymalnej długości 16 777 215 (2²⁴ – 1) znaków. Efektywna maksymalna długość jest mniejsza, jeśli wartość zawiera znaki wielobajtowe. Każda wartość MEDIUMTEXT jest przechowywana przy użyciu 3-bajtowego prefiksu, który wskazuje liczbę bajtów wartości.

LONGBLOB

Kolumna BLOB o maksymalnej długości 4 294 967 295 lub 4 GB (2³²– 1) bajtów. Efektywna maksymalna długość kolumn LONGBLOB zależy od skonfigurowanego maksymalnego rozmiaru pakietu w protokole klient / serwer i dostępnej pamięci. Każda wartość LONGBLOB jest przechowywana przy użyciu 4-bajtowego prefiksu, który wskazuje liczbę bajtów wartości.

LONGTEXT [CHARACTER SET nazwa_zestawu znaków] [COLLATE nazwa_zestaw]

Kolumna TEXT o maksymalnej długości 4 294 967 295 lub 4 GB (2³² – 1) znaków. Efektywna maksymalna długość jest mniejsza, jeśli wartość zawiera znaki wielobajtowe, natomiast długość kolumn LONGTEXT zależy również od skonfigurowanego maksymalnego rozmiaru pakietu w protokole klient / serwer i dostępnej pamięci. Każda wartość LONGTEXT jest przechowywana przy użyciu 4-bajtowego prefiksu, który wskazuje liczbę bajtów wartości.

ENUM („wartość1”, „wartość2”, …) [CHARACTER SET nazwa_zestawu znaków] [COLLATE nazwa_zestawu]

ENUM jest obiektem typu string, który może mieć tylko jedną wartość, wybraną z listy wartości „wartość1”, „wartość2”, …, NULL lub specjalną wartość błędu „”. Wartości ENUM są wewnętrznie reprezentowane jako liczby całkowite, zaś kolumna ENUM może mieć maksymalnie 65 535 różnych elementów.

Maksymalna obsługiwana długość pojedynczego elementu ENUM to M <= 255 i (M x w) <= 1020, gdzie M to długość literału elementu, aw to liczba bajtów wymaganych dla znaku maksymalnej długości w zestawie znaków.

SET („wartość1”, „wartość2”, …) [CHARACTER SET nazwa_zestawu znaków] [COLLATE nazwa_zestawu]

SET jest obiektem łańcuchowym, który może mieć zero lub więcej wartości, z których każda musi zostać wybrana z listy wartości „wartość1”, „wartość2”, … Wartości SET są wewnętrznie reprezentowane jako liczby całkowite, natomiast kolumna SET może mieć maksymalnie 64 różnych elementów. Maksymalna obsługiwana długość pojedynczego elementu SET to M <= 255 i (M x w) <= 1020, gdzie M to długość literału elementu, aw to liczba bajtów wymaganych dla znaku maksymalnej długości w zestawie znaków.

TYPY DANYCH SQL – AKTUALNE TYPY DANYCH

AKTUALNE TYPY DANYCH SQL DLA SYSTEMU MySQL można znaleźć pod adresem https://dev.mysql.com/doc/refman/8.0/en/data-types.html , natomiast DLA SYSTEMU MS SQL można znaleźć tutaj https://docs.microsoft.com/en-us/sql/t-sql/data-types/data-types-transact-sql?view=sql-server-ver15

PYTANIA KONTROLNE

Wymień typy danych liczb rzeczywistych
Wymień typy danych liczb całkowitych
Czym różni się varchar od char?
Czy można zdefiniować kolumnę CHAR(0)?
Jakich typy danych występują tylko w MS SQL?
Czym różni się binary od varbinary?
Które typy liczbowe są przedstawiane w postaci liczb szesnastkowych?
Określenie atrybutu binarnego CHARACTER SET dla typu danych łańcucha znakowego powoduje utworzenie kolumny jako odpowiadającego typu danych łańcucha binarnego. Które typy pozostają niezmienne i są zgodne z deklaracją?

Źródła

J. Pokorska Tworzenie i administrowanie stronami i bazami danych
https://dev.mysql.com/doc/refman/8.0/en/data-types.html
https://pomoc.home.pl/baza-wiedzy/typy-danych-w-mysql
https://docs.microsoft.com/en-us/sql/t-sql/data-types/data-types-transact-sql?view=sql-server-ver15