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
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
\z
(Control-Z). Wprowadzony, aby ominąć problem znaku końca pliku
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
VARYING(M)
VARCHAR(M)
FLOAT4
FLOAT
FLOAT8
DOUBLE
INT1
TINYINT
INT2
SMALLINT
INT3
MEDIUMINT
INT4
INT
INT8
BIGINT
LONG VARBINARY MEDIUMBLOB
LONG VARCHAR
MEDIUMTEXT
LONG
MEDIUMTEXT (od
wersji MySQL
4.1.0)
MIDDLEINT
MEDIUMINT
VARBINARY(M)
VARCHAR(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), FAŁ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), FAŁ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 znaleźć 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 znaleźć 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), FAŁ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 FAŁ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 znaleźć '\n' za pomocą
funkcji LIKE należy użyć '\\n'. Aby znaleźć '\' 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'