SQL (ang. Structured Query Language, strukturalny język zapytań)
Uniwersalny język stosowany w większości systemów zarządzania bazami danych. Wszelkie
operacje dotyczące definicji danych, dostępu do danych i ich modyfikacji, jak również
zazwyczaj czynności administracyjne, odbywają się poprzez komendy i programy
zapisywane w SQL.
SQL powstał w latach siedemdziesiątych w laboratoriach firmy IBM w San Jose w Kalifornii.
Początkowo został wprowadzony w życie dla relacyjnego systemu zarządzania bazą danych
o nazwie DB2 (produktu firmy IBM, który nadal jest w sprzedaży na różne platformy
sprzętowe) ale bardzo szybko stał się standardem w innych systemach relacyjnych baz
danych takich jak np. Oracle, MySQL, Inter-Base, Paradox czy dBase. SQL tworzy podstawę
dla relacyjnych baz danych.
SQL jest językiem nieproceduralnym. Wskazuje które dane należy skasować, wstawić czy
wyświetlić a nie, tak jak języki proceduralne (C, FORTRAN, Cobol), które określają jak
należy wykonać daną procedurę. Oczywiście współcześnie niektóre systemy dostarczają
rozwinięcia SQL, przekształcając go w język proceduralny, którym jest PL/SQL.
Składnia języka SQL dzieli się na trzy typy:
" DML (Data Manipulation Language)
" DCL (Data Control Language)
" DDL (Data Definition Language)
DML jest językiem stosowanym przez wszystkich użytkowników bazy danych. Służy do
wybierania danych i manipulowania danymi znajdującymi się w bazie. Za jego pomocą,
można dodawać, usuwać, wybierać i uaktualniać dane. Wszystkie te czynności można
wykonać za pomocą następująych, podstawowych komend SQL:
" SELECT - wydobywanie danych z tabel,
" UPDATE - uaktualnianie danych w tabeli,
" DELETE - kasowanie danych w tabeli,
" INSERT - wprowadzanie danych do tabeli.
DCL jest częścią języka SQL stosowaną przez administratorów bazy danych. Służy do
zapewnienia bezpieczeństwa dostępu do danych znajdujących się w bazie. Za jego pomocą
można na przykład nadawać i odbierać uprawnienia poszczególnym użytkownikom lub całym
grupom.
DDL jest wykorzystywany do utrzymywania struktury bazy danych. Dotyczy więc obiektów i
poleceń jakie można na nich wykonywać. Najbardziej popularnymi poleceniami tego języka
są:
" CREATE TABLE - tworzy nową tabelę,
" ALTER TABLE - zmienia istniejącą definicję tabeli,
" DROP TABLE - kasuje istniejącą tabelę,
" CREATE INDEX - tworzy indeks w tabeli,
" DROP INDEX - usuwa indeks w tabeli.
Podstawowe zasady
Polecenie SQL składa się z dwóch rodzajów słów:
" zarezerwowanych,
" zdefiniowanych przez użytkowanika.
Słowa zarezerwowane są integralną częścią języka i nie mogą być zmieniane ani dzielone
pomiędzy wierszami. Słowa zdefiniowane przez użytkownika reprezentują nazwy różnych
obiektów bazy danych takich jak: indeksy, widoki, tabele, kolumny, relacje.
Każde polecenie powinno być zakończone średnikiem (w przypadku większości baz
danych), chociaż nie jest to wymagane przez standard ISO.
Przykład:
SELECT * FROM moja_tablica;
Oznaczenia wykorzystywane w składni poleceń
" Słowa pisane dużymi literami - słowa zarezerwowane, np. SELECT, FROM,
REVOKE, GROUP BY itd.
" Słowa pisane małymi literami - słowa niezarezerwowane, zdefiniowane przez
użytkownika. Są to więc np. nazwy kolumn tabel, nazwy tabel itp.
" [] - w nawiasach kwadratowych ujmuje się słowa, które mogą ale nie muszą być
wykorzystane przy pisaniu polecenia. Na przykład w poleceniu SELECT [
DISTINCT | ALL ] słowo SELECT musi być użyte natomiast dwa kolejne są
opcjonalne. Jest to typowy sposób oznaczania użycia opcji w poleceniu, tj. w
językach skryptowych.
" | - znak oznaczający "lub" np. DISTINCT | ALL oznacza, że w poleceniu może
być użyte DISTINCT lub ALL.
" {} - nawiasy klamrowe wyróżnaiają zawarty w nich ciąg poleceń.
Komentarze
MySQL toleruje następujące trzy sposoby wstawiania komentarzy:
" od znaczka `#' do końca linii
" od znaczka `--' do końca linii. Uwaga! Użycie `--' (ang. double-dash) wymaga, aby za
drugim myślnikiem stała co najmniej jedna spacja lub znak nowej linii.
" od sekwencji `/*' (otwierającej komentarz) do najbliższej sekwencji `*/'. (zamykającej
komentarz). Sekwencja otwierająca nie musi być w tej samej linii co zamykająca, co
pozwala na pisanie komentarzy wielolinijkowych.
Przykłady:
SELECT 1 + 1; #Ten komentarz jest aż do końca linii.
SELECT 1 + 1; -- Ten komentarz jest aż do końca linii.
SELECT 1 /* To jest komentarz jednolinijkowy */
+ 1;
SELECT 1 +
/*
To jest
komentarz wielolinijkowy
*/
1;
Podstawy polecenia SELECT
Polecenie SELECT jest używane do uzyskiwania odpowiednich danych z tabeli z danymi.
Jest to podstawowe polecenie języka SQL i może być bardzo złożone.
Składnia polecenia SELECT jest mniej więcej następująca:
SELECT [PARAMETRY] wyr [FROM tabela [OPCJE]]
" każde wyrażenie wyr oznacza nazwę kolumny, z której chcemy otrzymać dane,
" tabela oznacza tabelę, z której chcemy uzyskać wiersze.
" za pomocą opcji i parametrów możemy znacząco skomplikować polecenie SELECT, i
w ten sposób bardzo dokładnie określić, o które dane nam chodzi.
" wyniki zapytania SELECT możemy wyświetlić zarówno na terminalu, jak również
przekierować do pliku, dzięki czemu możemy wykonać backup wybranych danych,
chociaż są do tego odrębne narzędzia.
" istnieją PARAMETRY, dzięki którym możemy wydobyć jedynie dane niepowtarzające
się; dzięki odpowiednim opcjom możemy dane sortować i ograniczać ilość
wyświetlanych danych,
Przykład:
mysql> SELECT a, COUNT(b) FROM test_tab GROUP BY a DESC;
+------------+
| wyniki ... |
| .......... |
+------------+
Uzyskanie danych z kolumny a, oraz ilości odpowiednich danych z kolumny b, z tabeli
test_tab posortowanych w odwrotnej kolejności względem danych z kolumny a.
Polecenia SELECT możemy użyć do uzyskania wierszy bez odwołania się do jakichkolwiek
tablic, na przykład:
mysql> SELECT 1 + 1;
+---+
| 2 |
+---+
Więcej na temat tego podstawowego polecenia języka SQL na następnym wykładzie.
Typy danych
W języku SQL można wyóżnić następujące typy danych:
1. ciągi znaków (ang. stringi),
2. liczby,
3. wartości szesnastkowe,
4. wartości logiczne,
5. wartości NULL.
Stringi
String jest ciągiem znaków otoczonym albo pojedynczym apostrofem albo apostrofem
podwójnym. Na przykład:
'jakis string'
"następny string"
Wśród ciągów znaków specjalne sekwencje mają specjalne znaczenie. Każda taka
sekwencja ropoczyna się zawsze od znaku odwrotnego ukośnika (ang. backslash), tak jak w
języku C. MySQL ozpoznaje następujące sekwencje specjalne:
\0
Znak 0 w kodzie ASCII
\'
Znak dosłowny pojedynczego apostrofu
\"
Znak dosłowny podwójnego apostrofu
\b
Znak cofnięcia się (ang. backspace)
\n
Znak nowej linii
\r
Znak powrotu karetki (kursora)
\t
Znak poizomej tabulacji
(Control-Z). Wprowadzony, aby ominąć problem znaku końca pliku
\z
w systemie Windows.
\\
Znak dosłowny odwrotnego ukośnika
\%
Znak procent (dosłownie)
\_
Znak podkreślenia (dosłownie)
Sekwencje znaków specjalnych są wrażliwe na wielkość liter. Na przykład `\b' jest
interpretowany jak znak cofnięcia się (backspace), ale `\B' jest interpretowany jak B.
Sekwencje `%' oraz \_ są używane do szukania dosłownych znaków procent i
podkreślenia w kontekście dopasowywania wzorców, gdzie inaczej mogłyby być
zinterpretowane jako znaki specjalne (metaznaki), tak jak w języku Perl, czy sed, przy
wyszukiwaniu za pomocą wyrażeń regularnych.
We wszystkich pozostałych przypadkach znaki poprzedzone znakiem odwrotnego ukośnika
interpretowane są tak, jakby tego odwrotnego ukośnika nie było, na przykład `\B' jest
interpretowany jak ''B.
Istnieje kilka sposobów na używanie cudzysłowów w stringach.
" Znak `'' wewnątrz stringu cytowanego przy pomocy znaków pojedynczego
apostrofu można zapisać jako `''' .
" Znak `"' wewnątrz stringu cytowanego przy pomocy znaków pojedynczego
apostrofu można zapisać jako `""' .
" Można zapisać znaki aposrofów przy pomocy znaku odwrotnego ukośnika
" Znak `'' wewnątrz stringu cytowanego przy pomocy znaków podwójnego
apostrofu nie potrzebuje specjalnego traktowania i nie musi być poprzedzony
znakiem podwójnego apostrofu lub odwrotengo ukośnika. Podobnie znak `"'
cytowanego przy pomocy znaków pojedynczego apostrofu nie potrzebuje
specjalnego traktowania.
Przykłady polecenia SELECT na cytowanie i używanie odwrotnych ukośników:
Przykład 1
mysql> SELECT 'hello', '"hello"', '""hello""', 'hel''lo',
'\'hello';
+-------+---------+-----------+--------+--------+
| hello | "hello" | ""hello"" | hel'lo | 'hello |
+-------+---------+-----------+--------+--------+
Przykład 2
mysql> SELECT "hello", "'hello'", "''hello''", "hel""lo",
"\"hello";
+-------+---------+-----------+--------+--------+
| hello | 'hello' | ''hello'' | hel"lo | "hello |
+-------+---------+-----------+--------+--------+
Przykład 3
mysql> SELECT 'To\nsa\nCztery\nLinie';
+--------------------+
| To
sa
Cztery
Linie |
+--------------------+
Przykład 4
mysql> SELECT 'znikajacy\ ukosnik';
+-------------------+
| znikajacy ukosnik |
+-------------------+
Liczby
Liczby całkowite są reprezentowane jako ciągi cyfr, liczby zmiennoprzecinkowe są
reprezentowane przez cyfry i separator dziesiętny lub znak e w notacji naukowej.
Przykłady prawidłowo zapisywanych w MySQL liczb:
1221
0
-32
294.42
-32032.6809e+10
Liczba całkowita może być użyta w kontekście zmiennoprzecinkowym. Jest wtedy
interpretoana jako odpowiednia liczba zmiennoprzecinkowa.
Wartości szesnastkowe
W kontektście numerycznym działają jak 64 bitowe wartości liczb całkowitych, natomiast w
kontekście znakowym, działają jak "binarne" stringi, gdzie każda para liczby szesnaastkowej
jest zamieniana na jeden znak. Na przykład:
mysql> SELECT x'4D7953514C';
+-------+
| MySQL |
+-------+
mysql> SELECT 0xa+0;
+----+
| 10 |
+----+
mysql> SELECT 0x5061756c;
+------+
| Paul |
+------+
Poczynając od wersji 4.0.1 MySQL można zamienić liczbę lub znak do formatu
szesnatskowego przy pomocy funkcji HEX() Na przykład:
mysql> SELECT HEX('cat');
+--------+
| 636174 |
+--------+
mysql> SELECT 0x636174;
+-----+
| cat |
+-----+
Wartości logiczne
Poczynając od wersji 4.1 MySQL stała TRUE oznacza 1 a stała FALSE oznacza 0. Te stałe
można pisać małymi i dużymi literami.
mysql> SELECT TRUE, true, FALSE, false;
+---+---+---+---+
| 1 | 1 | 0 | 0 |
+---+---+---+---+
Wartość NULL
Wartość NULL oznacza brak danych. Uwaga! Wartość NULL jest czymś innym niż 0 w
przypadku liczb czy też pusty string '' w przypadku stringów.
Typy kolumn tabel
Dane przechowywane są w tabelach. Każda kolumna tabeli przechowuje dane tylko
określonego typu, który definiuje się podczas tworzenia tabeli przy pomocy polecenia
CREATE, o którym będzie mowa dalej.
MySQL umożliwia tworzenie kolumn z danymi typu:
" numerycznego,
" data i czas,
" znakowego.
Kolumny typu numerycznego
Przy definicji typu kolumny można użyć specjalnego parametru ZEROFILL, który
automatycznie powoduje dodanie arybutu UNSIGNED do definicji typu kolumny.
MySQL umożliwia tworzenie kolumn numerycznych o następujących typach (poniżej
parametr M oznacza największy możliwy rozmiar podczas wyświetlania liczby, natomiast D
oznacza liczbę cyfr po kropce dzisiętnej):
1. TINYINT[(M)] [UNSIGNED] [ZEROFILL]
Bardzo mały integer. Zakres dla liczb ze znakiem od -128 do 127,
natomiast dla liczb bez znaku od 0 do 255.
2. BIT - synonim TINYINT
3. BOOL - synonim TINYINT
4. BOOLEAN - synonim TINYINT
5. SMALLINT[(M)] [UNSIGNED] [ZEROFILL]
Mały integer. Zakres dla liczb ze znakiem od -32768 do 32767, natomiast dla liczb
bez znaku od 0 do 65535.
6. MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]
Średniej wielkości integer. Zakres dla liczb ze znakiem od -8388608 do 8388607,
dla liczb bez znaku od 0 do 16777215.
7. INT[(M)] [UNSIGNED] [ZEROFILL]
Normalnej wielkości integer. Zakres dla liczb ze znakiem od -2147483648 do
2147483647, natomiast dla liczb bez znaku od 0 do 4294967295.
8. INTEGER[(M)] [UNSIGNED] [ZEROFILL] - synonim INT.
9. BIGINT[(M)] [UNSIGNED] [ZEROFILL]
Duży integer. Zakres dla liczb ze znakiem od -9223372036854775808 do
9223372036854775807, natomiast dla liczb bez znaku od 0 do
18446744073709551615.
10. FLOAT(p) [UNSIGNED] [ZEROFILL]
Liczba zmiennoprzecinkowa. P oznacza precyzję i może przyjąć wartości od 0 do 24
dla liczb pojedynczej precyzji oraz od 25 do 53 dla liczb podwójnej precyzji. Te typy
odpowiadają tradycyjnie zapisywanym typom FLOAT i DOUBLE FLOAT(p)
11. FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]
Liczba zmiennoprzecinkowa pojedynczej precyzji. Możliwe wartości są następujące:
od -3.402823466E+38 do -1.175494351E-38, 0, oraz od 1.175494351E-38 do
3.402823466E+38. Jeśli podano parametr UNSIGNED wartości mniejsze od zera są
niedozwolonea. M jest "szerokością" wypisywanej liczby natomiast D jest liczbą cyfr
po przecinku.
12. DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]
Liczba zmiennoprzecinkowa podwójnej precyzji. Możliwe zakresy są następujące od -
1.7976931348623157E+308 do -2.2250738585072014E-308, dla liczb ze znakiem, 0,
oraz od 2.2250738585072014E-308 do 1.7976931348623157E+308 dla liczb bez
znaku.
13. DOUBLE PRECISION[(M,D)] [UNSIGNED] [ZEROFILL] - synonim
DOUBLE.
14. REAL[(M,D)] [UNSIGNED] [ZEROFILL] - synonim DOUBLE.
15. DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]
"Niespakowana" liczba stałoprzecinkowa. Zachowuje się tak, jak dana typu CHAR.
"Niespakowana" oznacza, że liczba jest przechowywana tak jak znaki, tzn. jeden
znak na każdą cyfrę. M jest maksymalną liczbą cyfr a D jest liczbą dziesiątek.
16. DEC[(M[,D])] [UNSIGNED] [ZEROFILL] - synonim DECIMAL
17. NUMERIC[(M[,D])] [UNSIGNED] [ZEROFILL] - synonim DECIMAL
18. FIXED[(M[,D])] [UNSIGNED] [ZEROFILL] - synonim DECIMAL
Kolumny typu: data i czas
1. DATE - data z zakresu od '1000-01-01' do '9999-12-31'. MySQL wyświetla datę w
formacie 'YYYY-MM-DD', ale możliwe jest wprowadzanie wartości do pól typu DATE
zarówno w postaci znakowej jak też numerycznej.
2. DATETIME - data i czas z zakresu od '1000-01-01 00:00:00' do '9999-12-31
23:59:59'. MySQL wyświetla DATETIME w formacie 'YYYY-MM-DD HH:MM:SS', ale
możliwe jest wprowadzanie wartości zarówno w postaci znakowej jak też
numerycznej.
3. TIMESTAMP[(M)] - data i czas z zakresu '1970-01-01 00:00:00' aż do roku 2037.
Pierwsza kolumna typu TIMESTAMP jest automatycznie ustawiana na datę i czas
ostatnio wykonywanej operacji, jeśli nie ustawimy wartości samodzielnie. To samo
można osiągnąć ustawiając explicite wartość NULL. Od wersji 4.1 MySQL-a
TIMESTAMP jest wyświetlany jako string w formacie 'YYYY-MM-DD HH:MM:SS', ale
jeśli chcemy otrzymać liczby powinniśmy dodać +0 do kolumny TIMESTAMP. W
wersji 4.0 MySQL (i niższych) wartości z pól typu TIMESTAMP są wyświetlane w
formacie 'YYYYMMDDHHMMSS', 'YYMMDDHHMMSS', 'YYYYMMDD', 'YYMMDD' w
zależności od tego, czy M było odpowiednio równe 14 (albo zostało opuszczone), 12,
8, lub 6. Argument M definiuje tylko sposób wyświetlania wartości kolumn typu
TIMESTAMP, nie działa natomiast na sposób przechowywania danych tego typu;
każda dana typu TIMESTAMP jest przechowywana w 4 bajtach.
4. TIME - czas z zakresu od '-838:59:59' do '838:59:59'. MySQL wyświetla czas w
formacie 'HH:MM:SS' ale możliwe jest wprowadzanie wartości zarówno w postaci
znakowej jak też numerycznej.
5. YEAR[(2|4)] - rok w formacie dwu- lub czterocyfrowym. Domyślnym formatem
jest format czterocyfrowy. W formacie czterocyfrowym można wprowadzać wartości z
zakresu od 1901 do 2155, oraz 0000. W formacie dwucyfrowym można wprowadzać
wartości z zakresu od 70 do 69, co oznacza lata od 1970 do 2069. MySQL wyświetla
rok w formacie 'YYYY', ale możliwe jest wprowadzanie wartości zarówno w postaci
znakowej jak też numerycznej.
Kolumny typu znakowego
1. [NATIONAL] CHAR(M) [BINARY | ASCII | UNICODE] - string o długości
M. Jeśli wartość jest krótsza niż zaspecyfikowana w definicji typu kolumny tablicy,
wtedy brakujące znaki są uzupełnione znakami spacji. M może przybierać wartości od
0 do 255. Kolumny typu M z M większym od 255 są zamieniane na kolumny typu
TEXT, które mogą przechowywać dane o długości takiej jak zaspecyfikowano, np.
kolumna typu CHAR(500) jest zamieniana do kolumny typu TEXT, a kolumna typu
CHAR(200000) jest zamieniana do kolumny typu MEDIUMTEXT. CHAR jest
skrótem od CHARACTER. NATIONAL CHAR (lub jeszcze krócej NCHAR) jest
skrótem od standardowego sposobu definiowania typu kolumn w języku SQL,
używając domyślnego sposobu kodowania (latin2, utf8, itp). Atrybut BINARY
powoduje, że takie funkcje jak sortowanie czy porównywanie będzie wrażliwe na
wielkość liter danych kolumn typu CHAR. Atrybut ASCII przypisuje automatycznie
kodowanie typu latin1 do typu kolumny. Atrybut UNICODE przypisuje
automatycznie kodowanie typu ucs2 do kolumny typu CHAR. Można utworzyć
kolumnę typu CHAR(0), co przydaje się czasem do starszych wersji definicji danych,
które wymagały istnienia kolumny ale tak napradę nigdy danych z tej kolumny nie
używały. Kolumny typu CHAR(0) nie zdefiniowane jako NOT NULL zajmują tylko 1
bit pamięci i mogą przyjmować tylko wartości NULL lub ''.
2. CHAR - synonim typu CHAR(M)
3. [NATIONAL] VARCHAR(M) [BINARY] - string zmiennej długości. Parametr M
reprezentuje maksymalną długość stringu, nie większą niż 255 znaków. VARCHAR
jest skrótem od CHARACTER VARYING.Atrybut BINARY powoduje, że takie funkcje
jak sortowanie czy porównywanie będzie wrażliwe na wielkość liter
4. TINYBLOB - typ danych kolumny typu BLOB o maksymalnej długości 255 (2^8 - 1)
znaków.
5. TINYTEXT typ danych kolumny typu TEXT o maksymalnej długości 255 (2^8 - 1)
znaków.
6. BLOB typ danych kolumny typu BLOB o maksymalnej długości 65,535 (2^16 -1)
znaków.
7. TEXT typ danych kolumny typu TEXT o maksymalnej długości 65,535 (2^16 -1)
znaków.
8. MEDIUMBLOB typ danych kolumny typu BLOB o maksymalnej długości 16,777,215
(2^24 - 1) znaków.
9. MEDIUMTEXT typ danych kolumny typu TEXT o maksymalnej długości 16,777,215
(2^24 - 1) znaków.
10. LONGBLOB typ danych kolumny typu BLOB o maksymalnej długości 4,294,967,295
lub 4GB (2^32 - 1) znaków.
11. LONGTEXT typ danych kolumny typu TEXT o maksymalnej długości 4,294,967,295
lub 4GB (2^32 - 1) znaków.
12. ENUM('wart1','wart2',...) - Typ wyliczeniowy. String, który może mieć
tylko jedną z wartości 'wart1','wart2',... przedstawionych na liście, lub
wartość pustą NULL, lub specjalną wartość błędu ''. Kolumna typu ENUM może
mieć tylko 65,535 różnych wartości. Wewnętrznie (w pamięci) dane kolumny typu
ENUM są reprezentowane jak liczby całkowite.
13. SET('wart1','wart2',...) Zestaw. String, który może mieć zero lub więcej
wartości, wybranych z listy: 'wart1','wart2',.... Kolumna typu SET może
mieć najwyżej 64 różnych wartości. Wewnętrznie (w pamięci) dane kolumny typu
SET są reprezentowane jak liczby całkowite.
Wybór optymalnych typów danych
Dla jak najbardziej wydajnego używania i przechowywania bazy danych, należy posługiwać
się typami danych najbardziej precyzyjnymi. Na przykłąd, jeśli będzimey używać kolumny z
liczbami całkowitymi z zakresu 1 do 99999, to najbardziej odpowiednim typem danych
będzie w tym wypadku MEDIUMINT UNSIGNED. Ze wszystkich typów danych, dane tego
typu zajmują najmniej pamięci.
W przypadku danych monetarnych, należy używać typów DECIMAL. Takie dane będą
przechowywane jako stringi i nie będzie mowy o stracie dokładności i precyzji obliczeń
(obliczenia bedą prowadzone przy pomocy arytmetyki podwójnej precyzji).
Jeśli potrzebujemy wysokiej precyzji obliczeń, zawsze możemy użyć typu BIGINT. Dzięki
temu możemy prowadzić obliczenia przy użyciu liczb całkowitych i wynik przekształcać do
liczby podwójnej precyzji jedynie, kiedy to będzie konieczne.
Używanie typów danych kolumn z innych baz danych
Aby umożliwić import danych z innych baz danych MySQL mapuje typy danych kolumn w
następujący sposób:
nie-MySQL MySQL
BINARY(M) CHAR(M) BINARY
CHAR
VARCHAR(M)
VARYING(M)
FLOAT4 FLOAT
FLOAT8 DOUBLE
INT1 TINYINT
INT2 SMALLINT
INT3 MEDIUMINT
INT4 INT
INT8 BIGINT
LONG VARBINARY MEDIUMBLOB
LONG VARCHAR MEDIUMTEXT
MEDIUMTEXT (od
LONG wersji MySQL
4.1.0)
MIDDLEINT MEDIUMINT
VARCHAR(M)
VARBINARY(M)
BINARY
Mapowanie typów danych odbywa się w momencie definicji tablicy. Kiedy nastąpi wstawienie
do takiej tablicy danych typu z lewej kolumny powyższej tablicy, MySQL automatycznie
przedefiniuje typy danych kolumn na odpowiednie typy z prawej kolumny powyższej tablicy.
Poleceniem DESCRIBE nazwa_tablicy powinno pokazać definicję tablicy po
zmapowaniu typów danych.
Operatory
Pierwszeństwo operatorów w MySQL jest następująca (od najmniejszego do największego.
W tej samej linii wymieniono operatory o takim smaym priorytecie (pierwszeństwie).):
:=
||, OR, XOR
&&, AND
BETWEEN, CASE, WHEN, THEN, ELSE
=, <=>, >=, >, <=, <, <>, !=, IS, LIKE, REGEXP, IN
|
&
<<, >>
-, +
*, /, DIV, %, MOD
^
- (unary minus), ~ (unary bit inversion)
NOT, !
BINARY, COLLATE
Funkcje
Uwaga. Pomiędzy nazwą funkcji a nawiasami okrągłymi z listą parametrów, nie może być
żadnych białych znaków. Jest tak dla odróżnienia wywołania funkcji i odniesienia do tablic
lub kolumn, które mają często nazwy identyczne jak nazwa funkcji. Odstępy pomiędzy
argumentami wywołania funkcji są jak najbardziej dozwolone.
Można jednak wymusić w MySQL akceptację odstępów między nazwą funkcji a nawiasami
okrągłymi przy wywołaniu funkcji. Należy wtedy rozpocząć pracę serwera MySQL z opcją: -
-sql-mode=IGNORE_SPACE (jako administrator). W takim wypadku nazwy funkcji będą
słowami zarezerwowanymi.
Funkcje i operatory porównujące
Operacje relacji (przy pomocy operatorów relacji) funkcji porównujących zwracają zawsze
PRAWD
(wartość 1), FAA
SZ (wartość 0) lub NULL Takie operatory pracują zarówno na
liczbach jak też na stringach. Jeśli podczas operacji porównania zachodzi konieczność
zamiany stringu do liczby lub odwrotnie, to taka zamiana jest automatycznie dokonywana.
Niektóre funkcje zwracają wartość inną niż PRAWDA (1), FAA
SZ (0), lub NULL, na przykład
LEAST(), GREATEST(). W takich wypadkach (a właściwie w każdym przypadku)
wartość, którą zwracają jest zawsze wynikiem działania następujących zasad operacji
porównania:
1. Jeśli po obydwu stronach operatora znajduje się wartość NULL to wynik operacji
porównania jest NULL, poza przypadkiem operatora <=>
2. Jeśli po obydwu stronach operatora są stringi, to są one porównywane jak stringi.
3. Jeśli po obydwu stronach operatora są liczby całkowite, to są one porównywane jak
liczby całkowite.
4. Wartości szesnastkowe są traktowane tak jak "binarne" stringi, poza przypadkiem,
kiedy z jednej strony operatora mamy wartość szesnastkową a z drugiej liczbę.
5. Jeśli jednym z argumentów jest zmienna typu TIMESTAMP lub DATETIME a drugim
jest stała, to podczas operacji porównania stała jest zamieniana do typu TIMESTAMP
i dopiero wtedy następuje porównanie.
6. We wszystkich innych przypadkach argumenty są porównywane jak liczby
zmiennoprzecinkowe (rzeczywiste).
Domyślnie operacje porównywania stringów nie uwzględniają wielkości liter. Dlatego
'string1' jest taki sam jak 'StRinG1'.
Za pomocą funkcji CAST() można zamienić argument do odpowiedniego typu danej.
Ponadto kodowanie stringów można zamienić na inne kodowanie przy pomocy funkcji
CONVERT().
Następujące przykłady ilustrują zamiany stringów do liczb podczas operacji porównania
stringów z liczbami:
mysql> SELECT 1 > '6x';
+---+
| 0 |
+---+
mysql> SELECT 7 > '6x';
+---+
| 1 |
+---+
mysql> SELECT 0 > 'x6';
+---+
| 0 |
+---+
mysql> SELECT 0 = 'x6';
+---+
| 1 |
+---+
Przykłady użycia operatorów relacji:
Operator równości =
mysql> SELECT 1 = 0;
+---+
| 0 |
+---+
mysql> SELECT '0' = 0;
+---+
| 1 |
+---+
mysql> SELECT '0.0' = 0;
+---+
| 1 |
+---+
mysql> SELECT '0.01' = 0;
+---+
| 0 |
+---+
mysql> SELECT '.01' = 0.01;
+---+
| 1 |
+---+
Operator równości odporny na NULL <=>
mysql> SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL;
+---+---+---+
| 1 | 1 | 0 |
+---+---+---+
mysql> SELECT 1 = 1, NULL = NULL, 1 = NULL;
+---+------+------+
| 1 | NULL | NULL |
+---+------+------+
Operatory nierówności: <> oraz !=
mysql> SELECT '.01' <> '0.01';
+---+
| 1 |
+---+
mysql> SELECT .01 <> '0.01';
+---+
| 0 |
+---+
mysql> SELECT 'www' <> 'wwww';
+---+
| 1 |
+---+
Operator mniejszości lub równości <=
mysql> SELECT 0.1 <= 2;
+---+
| 1 |
+---+
Operator mniejszości <
mysql> SELECT 2 < 2;
+---+
| 0 |
+---+
Operator większości lub równości >=
mysql> SELECT 2 >= 2;
+---+
| 1 |
+---+
Operator większości >
mysql> SELECT 2 > 2;
+---+
| 0 |
+---+
Wyrażenia IS NULL oraz IS NOT NULL
mysql> SELECT 1 IS NULL, 0 IS NULL, NULL IS NULL;
+---+---+---+
| 0 | 0 | 1 |
+---+---+---+
mysql> SELECT 1 IS NOT NULL, 0 IS NOT NULL, NULL IS NOT NULL;
+---+---+---+
| 1 | 1 | 0 |
+---+---+---+
Dla kompatybilności z ODBC MySQL umożliwia następujące dwa użycia wyrażenia IS
NULL
" Można znalez
ć wiersz, w którym występuje ostatnio wprowadzona wartość typu
AUTO_INCREMENT wydając następujące polecenie:
SELECT * FROM nazwa_tablicy WHERE auto_kol IS NULL
" Dla kolumn typu DATE oraz DATETIME, które są zadeklarowane jako NOT NULL
można znalez
ć specjalną datę '0000-00-00' używając następującego
polecenia:
SELECT * FROM nazwa_tabeli WHERE date_column IS NULL
Jest to czasem porzebne do aplikacji typu ODBC, gdyż ODBC nie toleruje daty o
wartości '0000-00-00'.
Wyrażenie wyr BETWEEN min AND max
Jeśli wyrażenie wyr jest większe lub równe min oraz mniejsze lub równe max to
BETWEEN zwraca 1, w przeciwnym wypadku zwraca 0. Takie wyrażenie jest
równoznaczne wyrażeniu (min <= wyr AND wyr <= max) jeśli wszystkie
argumenty są tego samego typu. Jeśli argumenty nie są tego samego typu, to następuje
konwersja na zasadach opisanych wcześniej i stosowana do wszystkich trzech
argumentów wyrażenia.
mysql> SELECT 1 BETWEEN 2 AND 3;
+---+
| 0 |
+---+
mysql> SELECT 'b' BETWEEN 'a' AND 'c';
+---+
| 1 |
+---+
mysql> SELECT 2 BETWEEN 2 AND '3';
+---+
| 1 |
+---+
mysql> SELECT 2 BETWEEN 2 AND 'x-3';
+---+
| 0 |
+---+
Wyrażenie wyr NOT BETWEEN min AND max
Jest to to samo co NOT (wyr BETWEEN min AND max)
Funkcja COALESCE(wart1,wart2,...)
Funkcja zwraca pierwszą wartość z listy nierówną NULL
mysql> SELECT COALESCE(NULL,1);
+---+
| 1 |
+---+
mysql> SELECT COALESCE(NULL,NULL,NULL);
+------+
| NULL |
+------+
Funkcja LEAST(wart1,wart2,...)
Funkcja zwraca najmniejszą wartość z listy. Argumenty są porównywane według
następujących zasad:
" jeśli wszystkie wartości są liczbami całkowitymi to sa porównywane jak liczby
całkowite
" jeśli wszystkie wartości są liczbami rzeczywistymi to sa porównywane jak liczby
rzeczywiste.
" jeśli jakiś argument jest stringiem ze znaczącą wielkością znaków, to argumenty są
porównywane tak, jak stringi z uwzględnieniem rozróżniania wielkości znaków.
" we wszystkich innych wypadkach, argumenty sa porównywane tak jak stringi, przy
czym nie rozróżnia się wielkości znaków.
mysql> SELECT LEAST(2,0);
+---+
| 0 |
+---+
mysql> SELECT LEAST(34.0,3.0,5.0,767.0);
+-----+
| 3.0 |
+-----+
mysql> SELECT LEAST('B','A','C');
+-----+
| 'A' |
+-----+
mysql> SELECT LEAST('B','a','C',NULL);
+------+
| NULL |
+------+
Funkcja GREATEST(wart1,wart2,...)
Funkcja zwraca największą wartość z listy. Argumenty są porównywane na takich samych
zasadach jak w przypadku funkcji LEAST().
mysql> SELECT GREATEST(2,0);
+---+
| 2 |
+---+
mysql> SELECT GREATEST(34.0,3.0,5.0,767.0);
+-------+
| 767.0 |
+-------+
mysql> SELECT GREATEST('B','A','C');
+-----+
| 'C' |
+-----+
Wyrażenie wyr IN (wart1,wart2,...)
Takie wyrażenie jest równe 1, jeśli wyrażenie wyr jest równe któremuś z argumentów listy
argumentów, w przeciwnym wypadku równe jest 0. Od wersji 4.1 IN zwraca NULL nie
tylko kiedy wyrażenie wyr jest równe NULL ale również wtedy, gdy nie ma na liście
argumentów wartości równej wyr i jednocześnie jednym z argumentów była wartość
NULL. Od wersji 4.1 MySQL-a IN jest wykorzystywane podczas pisania niektórych
rodzajów podzapytań.
mysql> SELECT 2 IN (0,3,5,'wefwf');
+---+
| 0 |
+---+
mysql> SELECT 'wefwf' IN (0,3,5,'wefwf');
+---+
| 1 |
+---+
Wyrażenie wyr NOT IN (wart1,wart2,...)
Jest to to samo co NOT (wyr IN (wart1,wart2...))
Funkcja ISNULL(wyr)
Funkcja zwraca 1 jeśli wyr jest równe NULL, w przeciwnym wypadku zwraca 0.
mysql> SELECT ISNULL(1+1);
+---+
| 0 |
+---+
mysql> SELECT ISNULL(1/0);
+---+
| 1 |
+---+
Funkcja INTERVAL(N,N1,N2,N3,...)
Funkcja zwraca 0 jeśli N < N1, 1 jeśli N < N2, i tak dalej, albo -1 jeśli N jest równe NULL.
mysql> SELECT INTERVAL(23, 1, 15, 17, 30, 44, 200);
+---+
| 3 |
+---+
mysql> SELECT INTERVAL(10, 1, 10, 100, 1000);
+---+
| 2 |
+---+
mysql> SELECT INTERVAL(22, 23, 30, 44, 200);
+---+
| 0 |
+---+
Operatory logiczne
Jak już zostało powiedziane wcześniej, w języku SQL wynikiem operacji logicznych jest albo
PRAWDA (liczbowo 1), FAA
SZ (liczbowo 0) lub NULL. Niktóre serwery bazodanowe mogą
zwracać wartości PRAWDY różne od 1, ale zawsze różne od 0.
Operator NOT (inaczej !).
zwraca 1 jeśli wynikiem jest PRAWDA, 0 jeśli wynikiem jest FAA
SZ, oraz NOT NULL
zwraca NULL.
mysql> SELECT NOT 10;
+---+
| 0 |
+---+
mysql> SELECT NOT 0;
+---+
| 1 |
+---+
mysql> SELECT NOT NULL;
+------+
| NULL |
+------+
mysql> SELECT ! (1+1);
+---+
| 0 |
+---+
mysql> SELECT ! 1+1;
+---+
| 1 |
+---+
w ostatnim przykładzie mamy wyrażenie (!1)+1.
Operator AND (inaczej &&).
Zwraca 1 jeśli argumenty są różne od zera i różne od NULL, 0 jeśli jeden lub więcej
argumentów jest równych zeru, a w każdych innych przypadkach zwraca NULL.
mysql> SELECT 1 && 1;
+---+
| 1 |
+---+
mysql> SELECT 1 && 0;
+---+
| 0 |
+---+
mysql> SELECT 1 && NULL;
+------+
| NULL |
+------+
mysql> SELECT 0 && NULL;
+---+
| 0 |
+---+
mysql> SELECT NULL && 0;
+---+
| 0 |
+---+
Operator OR (inaczej ||).
Zwraca 1 jeśli co najmniej jeden z argumentów jest różny od zera, NULL jeśli przynajmniej
jeden z argumentów jest równy NULL, w przeciwnym wypadku zwraca 0.
mysql> SELECT 1 || 1;
+---+
| 1 |
+---+
mysql> SELECT 1 || 0;
+---+
| 1 |
+---+
mysql> SELECT 0 || 0;
+---+
| 0 |
+---+
mysql> SELECT 0 || NULL;
+------+
| NULL |
+------+
mysql> SELECT 1 || NULL;
+---+
| 1 |
+---+
Operator XOR.
Zwraca NULL jeśli obydwa argumenty są równe NULL, 1 jeśli jeden z argumentów jest
różny od 0, w przeciwnym wypadku zwraca 0.
mysql> SELECT 1 XOR 1;
+---+
| 0 |
+---+
mysql> SELECT 1 XOR 0;
+---+
| 1 |
+---+
mysql> SELECT 1 XOR NULL;
+------+
| NULL |
+------+
mysql> SELECT 1 XOR 1 XOR 1;
+---+
| 1 |
+---+
XOR b jest matematycznie równe (a AND (NOT b)) OR ((NOT a) and b).
Funkcje porównujące stringi
MySQL automatycznie zamienia liczby do stringów i odwrotnie, jeśli uzna to za konieczne.
mysql> SELECT 1 + '1';
+---+
| 2 |
+---+
mysql> SELECT CONCAT(2,' test');
+----------+
| '2 test' |
+----------+
Jeśli chcemy zamienić jawnie liczbę do stringu to powinniśmy posłużyć się funkcjami
CAST() lub CONCAT().
mysql> SELECT 38.8, CAST(38.8 AS CHAR);
+--------------+
| 38.8, '38.8' |
+--------------+
mysql> SELECT 38.8, CONCAT(38.8);
+--------------+
| 38.8, '38.8' |
+--------------+
Powinniśmy raczej używać funkcji CAST() jednak przed wersją 4.02 MySQL-a nie była ona
dostępna.
Jeśli do funkcji operującej na stringach przekazujemy string binarny jako argument, wtedy
wynikowy string jest również stringiem binarnym. Ponadto, liczba zamieniana do stringu jest
traktowana jak string binarny. Takie zachowanie funkcji operujących na stringach wpływa na
porównywanie stringów.
Funkcje i wyrażenia porównujące
wyr LIKE wzor [ESCAPE 'escape-znak']
Typowe porównanie, które używa prostego mechanizmu wyrażeń regularnych.
Funkcja LIKE zwraca 1 (TRUE) lub 0 (FALSE). Jeśli zarówno wyr jak i wzór są
NULL wtedy funkcja zwraca NULL.
Przy pomocy funkcji LIKE możemy użyć dwóch następujących metaznaków:
% (procent) Pasuje do dowolnej ilości znaków,
nawet do zerowej ilości.
_ (podkreślenie) Pasuje do dokładnie jednego
znaku.
mysql> SELECT 'David!' LIKE 'David_';
+---+
| 1 |
+---+
mysql> SELECT 'David!' LIKE '%D%v%';
+---+
| 1 |
+---+
Aby porównać jawnie wystąpujące metaznaki należy poprzedzić je znakiem
odwrotnego ukośnika.
\% Pasuje do jednego literalnego znaku
procenta.
\_ Pasuje do jednego literalnego znaku
podkreślenia.
mysql> SELECT 'David!' LIKE 'David\_';
+---+
| 0 |
+---+
mysql> SELECT 'David_' LIKE 'David\_';
+---+
| 1 |
+---+
Jeśli nie chcemy używać znaku odwrotnego ukośnika, to możemy za pomocą opcji
[ESCAPE 'escape-znak'] użyć dowolnego znaku, który bedzie zastępować
znak odwotnego ukośnika.
mysql> SELECT 'David_' LIKE 'David|_' ESCAPE '|';
+---+
| 1 |
+---+
Dwa następne przykłady ilustrują zasadę porównywania stringów bez baczenia na
wielkość znaków, chyba że jeden z operandów jest stringiem binarnym:
mysql> SELECT 'abc' LIKE 'ABC';
+---+
| 1 |
+---+
mysql> SELECT 'abc' LIKE BINARY 'ABC';
+---+
| 0 |
+---+
W MySQL-u można używać funkcji LIKE w przypadku argumentów liczbowych. W
standardowym SQL funkcja LIKE operuje wyłącznie na znakach.
mysql> SELECT 10 LIKE '1%';
+---+
| 1 |
+---+
Uwaga. Ponieważ MySQL używa składni języka C dotyczącej stringów (na przykład
'\n' do reprezentacji znaku nowej linii, więc trzeba podwoić każde wystąpienie znaku
'\' w przypadku użycia funkcji LIKE. Na przykład, aby znalez
ć '\n' za pomocą
funkcji LIKE należy użyć '\\n'. Aby znalez
ć '\' należy użyć '\\\\' (odwrotne
ukośniki są dzielone raz przez parser i drugi raz kiedy, dopasowanie wzorca się
powiodło, zostawiając na końcu jeden odwrotny ukośnik do dopasowania).
wyr NOT LIKE wzor [ESCAPE 'escape-znak']
Jest to to samo, co
NOT (wyr LIKE wzor [ESCAPE 'escape-znak']).
wyr REGEXP wzor
wyr RLIKE wzor
Dopasowywanie wzorca wzor do wyrażenia wyr. wzor może być tzw.
rozszerzonym wyrażeniem regularnym (na temat wyrażeń regularnych nie będzie
mowy na tym wykładzie. Zaleca ię czytanie książek z Perla, gdyż są w tym zakresie
najlepsze, aczkolwiek trzeba pamiętać, że wyrażenia regularne Perla są często dość
osobliwe i właściwe tylko dla Perla). Wyrażeniezwraca wartość 1 jeśli dopasowanie
się powiodło i 0 jeśli się nie powiodło. Jeśli wzor lub wyr jest NULL, wtedy wynik
też jest NULL. RLIKE jest synonimem REGEXP wymyślonym dla kompatybilności z
mSQL.
mysql> SELECT 'Monty!' REGEXP 'm%y%%';
+---+
| 0 |
+---+
mysql> SELECT 'Monty!' REGEXP '.*';
+---+
| 1 |
+---+
mysql> SELECT 'new*\n*line' REGEXP 'new\\*.\\*line';
+---+
| 1 |
+---+
mysql> SELECT 'a' REGEXP 'A', 'a' REGEXP BINARY 'A';
+-----+
| 1 0 |
+-----+
mysql> SELECT 'a' REGEXP '^[a-d]';
+---+
| 1 |
+---+
REGEXP i RLIKE używają bierzącego sposobu kodowania zdefiniowanego w bazie
MySQL (domyślnie jest to ISO-8859-1 Latin1).
wyr NOT REGEXP wzor
wyr NOT RLIKE wzor
Te dwa wyrażenia znaczą to samo co odpowiednio
NOT (wyr REGEXP wzor)
oraz
NOT (wyr RLIKE wzor)
STRCMP(wyr1,wyr2)
STRCMP() zwraca 0 jeśli stringi (paramery) są takie same, -1 jeśli pierwszy argument
jest mniejszy od drugiego, oraz 1 w innych wypadkach.
mysql> SELECT STRCMP('text', 'text2');
+----+
| -1 |
+----+
mysql> SELECT STRCMP('text2', 'text');
+---+
| 1 |
+---+
mysql> SELECT STRCMP('text', 'text');
+---+
| 0 |
+---+
Od wersji 4.0 MySQL-a STRCMP() używa bierzącego systemu kodowania podczas
porównywania. Zatem domyślnie wielkość znaków (wielkość liter) nie ma znaczenia
chyba, że jeden lub dwa operandy są stringami binarnymi. Przed MySQL-em w wersji
4.0 funkcja STRCMP() jest czuła na wielkość znaków (wielkość liter) i rozróżni
'Ryba' od 'ryba'
Porównywanie z uwzględnieniem wielkości liter
Operator BINARY
Zamienia string występujący z prawej strony operatora do stringu "binarnego". Jest to
prosta i bezpośrednia metoda do spowodowania rozróżniania wielkości liter podczas
porównywania napisów, nawet jeśli typ kolumny nie jest zdefiniowany z parametrem
BINARY lub BLOB. BINARY string jest skrótem od CAST(string AS BINARY).
mysql> SELECT 'a' = 'A';
+---+
| 1 |
+---+
mysql> SELECT BINARY 'a' = 'A';
+---+
| 0 |
+---+
Jeśli nie chcemy, aby porównywanie napisów kolumn typu BLOB odbywało się z
rozróżnieniem wielkości liter możemy:
" użyć funkcji UPPER(): aby zamienić wszystkie litery do dużych liter, lub
LOWER() aby zamienić wszystkie litery do małych liter.
SELECT 'A' LIKE UPPER(blob_col) FROM tab;
" użyć funkcji CONVERT(). Wynik jest stringiem "nie-binarnym" i użycie LIKE nie
spowoduje rozróżniania wielkości liter.
SELECT 'A' LIKE CONVERT(blob_col USING latin1) FROM tab;
gdzie latin1 jest odpowiednią nazwą sposobu kodowania znaków i może być
zastąpiona przez dowolną nazwę kodowania znaków rozpoznawaną przez MySQL,
na przykład 'latin2'