S Q L

SQL

SQL

– Structured Query Language

Język komunikacji użytkownika z oprogramowaniem
zarządzającym relacyjnymi bazami danych
Ujęty w normie ANSI/ISO w roku 1986
Aktualizacje: SQL-89, SQL-92

Język SQL danej bazy, np. mySQL, zawiera:

• polecenia SQL ujęte w standardzie
• rozszerzenia standardu – polecenia specyficzne dla

konkretnego systemu baz danych

Rodzaje poleceń SQL

Polecenia SQL dotyczą:

• tworzenia i usuwania baz danych, tabel, kluczy
• wprowadzania, uaktualniania i usuwania danych
• wyszukiwania danych
• ustawiania praw dostępu do danych
• administracji bazą danych
• zarządzania transakcjami

SQL – liczby i napisy

Łańcuchy znaków:

'napis'

lub

"napis"

użycie backslasha (\):

'napis \'03'

Liczby całkowite:

1221 0 -32

Liczby zmiennoprzecinkowe:

294.42 -32032.6809e+10

Liczby szesnastkowe:

x'4D7953514C' 0x5061756c

Wartość pusta:

NULL

Wprowadzanie komend SQL

Sposób wprowadzania do bazy poleceń SQL:

• w programie działającym z linii poleceń

(np. mySQL monitor)

• w programie z graficznym interfejsem użytkownika

(np. mySQL Navigator)

• w skryptach i programach komunikujących się z bazą

danych (np. skrypty PHP)

• pośrednio, przy użyciu graficznego interfejsu

użytkownika (np. Access)

Tworzenie bazy danych – mySQL

Sposób utworzenia bazy danych w mySQL:

• uruchomienie programu:

mysql

• utworzenie bazy:

CREATE DATABASE nazwa;

• przełączenie się do bazy:

USE nazwa;

• teraz można utworzyć tabele – utworzenie tabeli

wymaga podania nazw pól (kolumn) oraz ich typów.

Typy danych

Typy danych mySQL –

liczby całkowite

:

• TINYINT

(1 bajt)

• SMALLINT

(2 bajty)

• MEDIUMINT

(3 bajty)

• INT

(4 bajty)

• BIGINT

(8 bajtów)

Dodatkowe atrybuty:

• UNSIGNED

– liczba bez znaku

• ZEROFILL

– dopełnienie zerami

• (M)

– wyświetlenie M cyfr

Typy danych

Typy danych mySQL –

liczby zmiennoprzecinkowe

:

• FLOAT (n)

– pojedyncza precyzja, n liczb

• DOUBLE (M,D)

– podwójna precyzja

• DECIMAL (M,D)

– liczba zapisywana jako tekst

Dodatkowe atrybuty:

• UNSIGNED

– liczba bez znaku

• ZEROFILL

– dopełnienie zerami

• (M)

– wyświetlenie M cyfr

• (M,D)

– wyświetlenie M cyfr, D cyfr po przecinku

Typy danych

Typy danych mySQL –

data i czas

:

• DATETIME

– data + czas (2003-10-15 15:00:12)

• DATE

– data (2003-10-15)

• TIME

– czas (15:00:12)

• YEAR

– rok (2003 lub 03)

• TIMESTAMP (n)

– znacznik czasu (n – liczba znaków)

Typy danych

Typy danych mySQL –

łańcuchy tekstowe

:

• CHAR (n)

– stała długość n (max. 255)

• VARCHAR (n)

– zmienna długość, max. n (do 255)

• TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT

– dane tekstowe ASCII

• TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB

– dane binarne

• ENUM

– typ wyliczeniowy

• SET

– zbiór wartości

Tworzenie tabel

Utworzenie tabeli wymaga podania nazw pól (kolumn) oraz
ich typów.

CREATE TABLE albumy (

id

INT NOT NULL,

wykonawca

VARCHAR(30),

tytuł

VARCHAR(30),

rok

YEAR,

liczba-utw SMALLINT,
opis

TEXT

);

Wstawianie danych do tabeli

Wstawianie danych z podaniem wszystkich kolumn tabeli
(należy zachować kolejność!)

Wstawianie danych do wybranych kolumn tabeli

INSERT INTO albumy
VALUES (1, ‘Pink Floyd’, ‘The Division Bell’,
1994, 11, ‘Ostatni studyjny album’);

INSERT INTO albumy (id, album, wykonawca)
VALUES (2, ‘The Wall’, ‘Pink Floyd’);

Wyszukiwanie danych

Wyszukiwanie danych w tabeli – instrukcja

SELECT

Ogólna postać instrukcji SELECT:

SELECT

które_kolumny

FROM

z_której_tabeli

WHERE

które_rekordy;

Wyszukiwanie danych

Najprostsza postać instrukcji

SELECT

Wyszukiwanie:

• w tabeli albumy
• wszystkich pól (kolumn) – „*”
• wszystkich rekordów (wierszy) – brak warunku

WHERE

SELECT * FROM albumy;

Wyszukiwanie – wybór kolumn

Wyszukiwanie danych – wyświetlenie wybranych kolumn

W ten sposób można uzyskać powtarzające się wyniki:

Eliminacja powtórzeń wyników:

SELECT rok, tytuł, wykonawca FROM albumy;

SELECT wykonawca FROM albumy;

SELECT DISTINCT wykonawca FROM albumy;

Wyszukiwanie – wybór wierszy

Wyszukiwanie rekordów spełniających zadany warunek
– instrukcja

WHERE

Przykłady:

SELECT tytuł FROM albumy
WHERE wykonawca = 'Pink Floyd';

SELECT wykonawca, rok FROM albumy
WHERE tytuł = 'The Best Of' AND rok < 1970;

Operatory

Operatory używane w instrukcji

SELECT ... WHERE

:

• porównania:

= <> < > <= >= <=>

• logiczne:

NOT ! AND && OR || XOR

• IS NULL, IS NOT NULL

• expr

BETWEEN

min

AND

max (

NOT BETWEEN

)

• expr

IN

(lista)

(

NOT IN

)

SELECT * FROM albumy
WHERE wykonawca IN ('Pink Floyd', ‘Dire
Straits')
AND (rok < 1975 OR rok BETWEEN 1979 AND 1983);

Symbole wieloznaczne

Symbole wieloznaczne używane w instrukcji

WHERE

:

• %

zastępuje dowolny ciąg znaków

• _

zastępuje jeden znak

Operator symboli wieloznacznych:

LIKE

,

NOT LIKE

Wyrażenia regularne – operator

REGEXP

(mySQL)

SELECT * FROM albumy
WHERE wykonawca LIKE 'The %s';

SELECT * FROM albumy
WHERE album NOT LIKE 'The Best in 197_';

Sortowanie wyników

Sortowanie wyników wg zadanej kolumny:

• ORDER BY

pole – w porządku rosnącym

• ORDER BY

pole

ASC

– jw.

• ORDER BY

pole

DESC

– w porządku malejącym

SELECT * FROM albumy
ORDER BY rok DESC, wykonawca;

Grupowanie wyników

Tworzenie zestawień przez grupowanie wyników:

• użycie funkcji, np.

COUNT

,

SUM

,

MAX

,

MIN

,

AVG

• nazwanie kolumny z wynikami (opcjonalnie) –

AS

• zgrupowanie wyników –

ORDER BY

Przykład – obliczenie ilości albumów wszystkich
wykonawców:

SELECT wykonawca, COUNT(*) FROM albumy
GROUP BY wykonawca;

SELECT wykonawca, COUNT(*) AS ilosc FROM albumy
GROUP BY wykonawca ORDER BY ilosc DESC;

Grupowanie wyników

Ograniczenie rekordów uzyskanych w wyniku grupowania
– operator

HAVING

Nie należy mylić instrukcji

WHERE

i

HAVING

!

Przykład – obliczenie ilości albumów wszystkich
wykonawców, wyświetlenie tylko tych, którzy mają więcej
niż 5 albumów:

SELECT wykonawca, COUNT(*) AS ilosc FROM albumy
GROUP BY wykonawca
HAVING ilosc > 5;

Ograniczenie liczby wyników

Ograniczenie liczby zwracanych wyników –

LIMIT

LIMIT n

– n pierwszych wyników

LIMIT m,n

– n wyników, pomijając m pierwszych

Przykład: 10 wykonawców o największej liczbie albumów:

20 następnych wyników (11-30):

SELECT wykonawca, COUNT(*) AS ilosc FROM albumy
GROUP BY wykonawca LIMIT 10;

SELECT wykonawca, COUNT(*) AS ilosc FROM albumy
GROUP BY wykonawca LIMIT 10,20;

Wyszukiwanie w wielu tabelach

Pobieranie danych w więcej niż jednej tabeli

Przykład bazy danych – dwie tabele:

• albumy
• utwory

Wybranie wszystkich możliwych kombinacji rekordów z obu
tabel (iloczyn kartezjański):

IDA

Wykonawca Album Rok

Gatunek

IDU

Utwór

Czas

IDA

SELECT * FROM albumy, utwory;

Wyszukiwanie w wielu tabelach

Uwzględnienie relacji między tabelami:

Łączy ze sobą rekordy obu tabel mające takie same dane
w polach, które są połączone relacją:

• albumy

• utwory

SELECT * FROM albumy, utwory
WHERE albumy.IDA = utwory.IDA;

IDA

Wykonawca Album Rok

Gatunek

IDU

Utwór

Czas

IDA

Wyszukiwanie w wielu tabelach

Wybór kolumn:

Krótsza wersja – aliasy nazw tabel:

SELECT albumy.wykonawca, albumy.album,
utwory.utwor, utwory.czas
FROM albumy, utwory
WHERE albumy.IDA = utwory.IDA;

SELECT a.wykonawca, a.album, u.utwor, u.czas
FROM albumy a, utwory u
WHERE a.IDA = u.IDA;

Wstawianie danych – inne metody

Wstawianie do tabeli danych uzyskanych w wyniku
zapytania:

Wstawianie danych z pliku na komputerze klienta (pola
rozdzielone tabulatorami, rekordy – znakiem nowej linii)
– komenda mySQL (nie standard):

INSERT INTO nowa (autor, dzielo)
SELECT DISTINCT wykonawca, album
FROM albumy;

LOAD DATA LOCAL INFILE ‘dane.txt’
INTO nowa_tabela;

Usuwanie rekordów

Usunięcie rekordów spełniających zadane kryteria
– instrukcja

DELETE

. Warunki takie same jak w

SELECT

.

Usuwane są zawsze całe rekordy.
Przykład:

UWAGA! Te komendy czyszczą całą tabelę:

DELETE FROM albumy
WHERE wykonawca = 'Pink Floyd';

DELETE FROM albumy;

TRUNCATE TABLE albumy;

Uaktualnianie rekordów

Zmiana danych rekordów już istniejących w tabeli
– komenda

UPDATE

.

Nowe wartości określane są komendą

SET

.

Przykład:

UWAGA! Ta komenda uaktualni WSZYSTKIE rekordy:

UPDATE albumy
SET wykonawca = 'Pink Floyd'
WHERE wykonawca = 'Fink Ployd';

UPDATE albumy SET wykonawca = 'Pink Floyd';

Operacje na bazach danych

Tworzenie bazy danych:

Usuwanie całej bazy:

Wyświetlenie istniejących baz danych:

Przełączenie się na inną bazę danych:

CREATE DATABASE baza;

DROP DATABASE baza;

SHOW DATABASES;

USE baza;

Operacje na tabelach

Tworzenie tabeli (przykład):

Usuwanie tabeli:

Wyświetlenie istniejących baz danych:

Wyświetlenie struktury tabeli:

CREATE TABLE tabela (id INT, nazwa VARCHAR(30));

DROP TABLE tabela;

SHOW TABLES;

DESCRIBE tabela;

Operacje na tabelach

Zmiana nazwy:

Zmiana struktury tabeli –

ALTER TABLE

Dodanie kolumny:

Usuwanie kolumny:

RENAME TABLE tabela TO nowa_tabela;

ALTER TABLE tabela ADD (opis TEXT);

ALTER TABLE tabela DROP opis;

Operacje na tabelach

Modyfikacja typu kolumny (ograniczenia typu!):

Zmiana nazwy kolumny:

Za pomocą

ALTER TABLE

możliwe jest również dodawanie i

usuwanie atrybutów pól.

ALTER TABLE tabela MODIFY opis VARCHAR(50);

ALTER TABLE tabela CHANGE opis info VARCHAR(50);

Atrybuty pól tabeli

Przy tworzeniu lub zmianie tabeli można podać opcjonalne
atrybuty pól (kolumn) tabeli:

CREATE TABLE (pole typ atrybuty, ...);

Dostępne atrybuty:

• NULL

– można nie podawać wartości (domyślnie)

• NOT NULL

– wartość musi być podana

• DEFAULT wartość

– gdy nie podamy wartości

• AUTO_INCREMENT

– automatycznie zwiększany licznik

• COMMENT 'opis'

– komentarz

• PRIMARY KEY, KEY

– indeksy główne

AUTO_INCREMENT i DEFAULT

AUTO_INCREMENT

– nie wpisujemy danych, baza wpisuje

aktualny stan licznika i zwiększa go o 1.

DEFAULT

– jeżeli nie wprowadzimy danych, zostanie

wpisana wartość domyślna

Wynik: (1, 'XYZ', 'brak opisu')

CREATE TABLE wykonawcy {

id

INT NOT NULL AUTO_INCREMENT,

wykonawca

VARCHAR(30),

opis

TEXT DEFAULT 'brak opisu'

};
INSERT INTO wykonawcy (wykonawca) VALUES ('XYZ');

TIMESTAMP

Wartością domyślną dla kolumny o typie

TIMESTAMP

jest aktualny znacznik czasu (data i czas).
Kolumna tego typu umożliwia zachowanie czasu
wprowadzenia lub ostatniej modyfikacji rekordu.
Jeżeli zostanie podana wartość – jest ona wpisywana.
Jeżeli nie zostanie podana wartość (NULL) – wpisywany jest
znacznik czasu.

Sprawdzanie poprawności danych

NOT NULL

– w tej kolumnie muszą być wpisane dane

CHECK (wyrażenie)

– dane muszą spełniać podany

warunek

CHECK nie jest obecnie zaimplementowane w MySQL.

CREATE TABLE dane {

nazwisko

VARCHAR(30) NOT NULL,

rok_ur

INT,

CHECK(rok_ur BETWEEN 1900 AND 2040)

};

Indeksowanie tabel

Na wybrane kolumny tabeli mogą być nakładane indeksy
(klucze) w celu:

• przyspieszenia wyszukiwania
• zdefiniowania relacji pomiędzy tabelami

Typy indeksów w MySQL:

• KEY, INDEX
• UNIQUE
• PRIMARY KEY
• FULLTEXT

Tworzenie indeksów

Tworzenie indeksu podczas definiowania tabeli – komenda

INDEX

lub

KEY

(obie są równoważne):

KEY nazwa (kolumny)

Indeks może obejmować wiele kolumn

CREATE TABLE dane {

nazwisko

VARCHAR(30) NOT NULL,

rok_ur

INT,

KEY indeks (nazwisko)

};

Tworzenie indeksów

Tworzenie indeksu w już istniejącej tabeli:

Indeks wielokolumnowy, indeksowanych 10 pierwszych
znaków pola nazwisko:

Usunięcie indeksu (nie usuwa danych!):

CREATE INDEX indeks ON dane(nazwisko);

CREATE INDEX indeks
ON dane (nazwisko(10), rok_ur);

DROP INDEX indeks ON dane;

Wartości niepowtarzalne

UNIQUE

– żadne dwa rekordy w tabeli nie mogą mieć

jednakowych danych w indeksowanej kolumnie. Jest to
jednocześnie INDEX.

Jeżeli indeksowana kolumna ma atrybut NOT NULL, dane w
kolumnie muszą być unikatowe i muszą być wprowadzone.

Jeżeli indeksowana kolumna ma atrybut NULL, dane w
kolumnie muszą być unikatowe, ale mogą nie być
wprowadzane (pole może pozostać puste).

Tworzenie indeksu UNIQUE

Tworzenie indeksu podczas definiowania tabeli:

Tworzenie indeksu w istniejącej tabeli:

CREATE TABLE dane {

nazwisko

VARCHAR(30) NOT NULL,

pesel

CHAR(11),

UNIQUE indeks (pesel)

};

CREATE UNIQUE INDEX indeks ON dane (pesel);

Indeks główny

Indeks główny –

PRIMARY KEY

• identyfikuje jednoznacznie każdy rekord w tabeli
• może istnieć tylko jeden w tabeli
• jest typu UNIQUE
• indeksowana kolumna otrzymuje automatycznie

atrybut NOT NULL

• ma nazwę PRIMARY (nie można podać własnej)
• bierze domyślnie udział w relacjach z innymi tabelami

Tworzenie indeksu głównego

Tworzenie indeksu głównego podczas definiowania tabeli
– w definicji kolumny:

To samo w definicji tabeli (może to być ind. wielokolumn.)

CREATE TABLE dane {

nazwisko

VARCHAR(30) NOT NULL,

pesel

CHAR(11) PRIMARY KEY,

};

CREATE TABLE dane {

nazwisko

VARCHAR(30) NOT NULL,

pesel

CHAR(11),

PRIMARY KEY (pesel)

};

Tworzenie indeksu głównego

Tworzenie indeksu głównego w już istniejącej tabeli:

Usuwanie indeksu głównego w tabeli (nie usuwa danych!):

ALTER TABLE dane ADD PRIMARY KEY (pesel);

ALTER TABLE dane DROP PRIMARY KEY;

Wyszukiwanie w całym tekście

MySQL posiada specjalny typ klucza

FULLTEXT

,

umożliwiający wyszukiwanie informacji w

polach

tekstowych

. Indeks ten nie wchodzi w skład standardu SQL.

Tworzenie indeksu FULLTEXT (dwie metody):

CREATE TABLE albumy {

tytul

VARCHAR(30) PRIMARY KEY,

wykonawca

VARCHAR(30),

recenzja

TEXT,

FULLTEXT indeks (tytul, recenzja)

};

ALTER TABLE albumy ADD FULLTEXT ind (recenzja);

Wyszukiwanie w całym tekście

Wyszukiwanie dosłowne:

MATCH (kolumny) AGAINST (napis)

Dla każdego zwróconego rekordu baza oblicza wskaźnik
podobieństwa (score) – im większa liczba, tym lepsze
dopasowanie szukanego ciągu.
Znalezione rekordy są sortowane w kolejności od
najwyższego wyniku.

SELECT * FROM albumy
WHERE MATCH (tytul, recenzja)

AGAINST ('best of');

Wyszukiwanie w całym tekście

Wyświetlenie kolumny z wynikiem dopasowania wymaga
dwukrotnego użycia tej samej komendy MATCH:

SELECT tytul, recenzja,

MATCH (tytul, recenzja)
AGAINST ('best of') AS score

FROM albumy
WHERE MATCH (tytul, recenzja)

AGAINST ('best of');

Wyszukiwanie w trybie logicznym

Wyszukiwanie w trybie logicznym
– operator

IN BOOLEAN MODE

Operatory wyszukiwania:

+słowo

– słowo musi wystąpić

–słowo

– słowo nie może wystąpić

"całe zdanie"

– musi wystąpić podana fraza

słow*

– słowo zaczynające się od podanych liter

()

– grupowanie operatorów

~

– zaprzeczenie

Wyszukiwanie w trybie logicznym

Przykłady:

rock jazz

słowa rock LUB jazz

+rock +jazz

słowa rock ORAZ jazz

+rock jazz

słowo rock musi wystąpić,
słowo jazz zwiększa score

+rock -jazz

słowo rock musi wystąpić,
słowo jazz nie może wystąpić

"rock music"

musi wystąpić fraza rock music

rock*

pasują m.in. rock, rocks, rocking

Wyszukiwanie w trybie logicznym

Przykład wyszukiwania w trybie logicznym:

Algorytm wyszukiwania pomija słowa:

• złożone z mniej niż 4 liter
• znajdujące się na liście "stopwords" (pospolite)
• występujące w więcej niż połowie rekordów tabeli

SELECT * FROM albumy
WHERE MATCH (tytul, recenzja)

AGAINST ('+rock +"debut album" -awful'
IN BOOLEAN MODE);

Wyszukiwanie w całym tekście

Indeksy FULLTEXT znacznie zwiększają możliwości
wyszukiwania danych w bazie.
Jednocześnie spowalniają one jednak operacje na
rekordach (wstawianie, zmiana) – konieczność
uaktualniania indeksu.
Wyszukiwanie w całym tekście w bazie MySQL jest w
trakcie opracowywania i optymalizacji. Operatory logiczne
działają od wersji 4.0.1.

Funkcje SQL

Język SQL udostępnia szereg funkcji umożliwiających
wykonywanie operacji na danych w zapytaniach.
Funkcje:

• matematyczne
• tekstowe
• daty i czasu

Funkcje te mogą być wykorzystywane w instrukcji SELECT,
w warunku wyboru kolumn lub w warunku wyboru wierszy.

Funkcje SQL

Przykład stosowania funkcji w instrukcji SELECT
Funkcja UPPER() zamienia litery na wielkie.

Użycie w warunku wyboru kolumn – zamienia litery na
wielkie w zwracanych danych:

Użycie w warunku wyboru rekordu – zawartość pola po
konwersji musi odpowiadać warunkowi:

SELECT UPPER(wykonawca) FROM albumy;

SELECT * FROM albumy
WHERE UPPER(wykonawca)='U2';

Funkcje matematyczne (1)

ABS(x) – wartość bezwzględna
SIGN(x) – znak liczby (-1, 0, 1)
MOD(m,n) – reszta z dzielenia M/N
FLOOR(x) – zaokrąglenie w dół
CEIL(x) – zaokrąglenie w górę
ROUND(x) – zaokrąglenie do najbliższej l. całkowitej
m DIV n – część całkowita z dzielenia m/n
EXP(x) – e

x

LN(x), LOG2(x), LOG10(x), LOG(b,x) – logarytmy
POWER(x,y) = x

y

Funkcje matematyczne (2)

SQRT(x) – pierwiastek kwadratowy
PI() – wartość 
SIN(x), COS(x), TAN(x), COT(x) – funkcje trygonometr.
ASIN(x), ACOS(x), ATAN(x) – odwrotne funkcje tryg.
CRC32('wyr') – kod CRC wyrażenia wyr
RAND() – liczba losowa od 0 do 1
LEAST(x,y,...) – najmniejsza wartość z listy
GREATEST(x,y,...) – największa wartość z listy
DEGREES(x), RADIANS(x) – konwersja stopnie/radiany
TRUNCATE(x,d) – skrócenie x do d miejsc po przecinku

Funkcje tekstowe (1)

ASCII(x) – kod ASCII znaku
ORD(x) – suma na podstawie kodów ASCII
CONV(x,m,n) – konwersja między systemami liczbowymi
BIN(x), OCT(x), HEX(x) – konwersja między systemami
CHAR(x) – ciąg złożony ze znaków o podanych kodach
CONCAT(s1,s2,...) – łączy podane napisy w jeden
CONCAT_WS(sep,s1,s2,...) – łączy napisy separatorem
LENGTH(s) – długość napisu
LOCATE(s1,s2,p) – pozycja napisu s1 w s2 (szuk. od p)
INSTR(s1,s2) – pozycja napisu s2 w s1

Funkcje tekstowe (2)

LPAD(s1,n,s2) – poprzedza s1 ciągiem s2 do długości n
RPAD(s1,n,s2) – dopisuje do s1 ciąg s2 do długości n
LEFT(s,n) – n pierwszych znaków z napisu s
RIGHT(s,n) – n ostatnich znaków z napisu s
SUBSTRING(s,m,n) – n znaków z napisu s od poz. m
SUBSTRING_INDEX(s,sep,n) – część napisu s przed

n-tym wystąpieniem separatora sep

LTRIM(s) – usuwa początkowe spacje
RTRIM(s) – usuwa końcowe spacje
TRIM(s) – usuwa początkowe i końcowe spacje
SPACE(n) – napis złożony z n spacji

Funkcje tekstowe (3)

REPLACE(s1,s2,s3) – zamień s2 na s3 w napisie s1
REPEAT(s,n) – napis z n powtórzeń s
REVERSE(s) – odwraca napis s
INSERT(s1,m,n,s2) – wstawia n znaków s2 do s1 na poz. m
ELT(n,s1,s2,...) – zwraca n-ty napis ze zbioru
FIELD(s,s1,s2,...) – zwraca indeks napisu s w zbiorze
LOWER(s) – zmienia litery na małe
UPPER(s) – zmienia litery na wielkie
LOAD_FILE(plik) – odczytuje zawartość pliku
QUOTE(s) – poprzedza znaki specjalne znakiem '\'
STRCMP(s1,s2) – porównanie dwóch napisów

Funkcje daty i czasu (1)

DATE(s) – pobiera datę z wyrażenia s
TIME(s) - pobiera czas z wyrażenia s
TIMESTAMP(s) – pobiera datę i czas z wyrażenia s
DAYOFWEEK(data) – podaje dzień tygodnia
DAYOFMONTH(data) – podaje dzień miesiąca
DAYOFYEAR(data) – podaje dzień w roku
MONTH(data) – podaje numer miesiąca
DAYNAME(data) – podaje nazwę dnia
MONTHNAME(data) – podaje nazwę miesiąca
WEEK(data) – podaje numer tygodnia (od 0)
WEEKOFYEAR(data) – podaje numer tygodnia (od 1)

Funkcje daty i czasu (2)

YEAR(data) – podaje rok
YEARWEEK(data) – podaje rok i numer tygodnia
HOUR(data) – podaje godzinę
MINUTE(data) – podaje minutę
SECOND(data) – podaje sekundę
MICROSECOND(data) – podaje ułamki sekundy
PERIOD_ADD(p,m) – dodaje m miesięcy do daty p
PERIOD_DIFF(p1,p2) – różnica dwóch dat
DATE_ADD(data, INTERVAL wyr typ)

– dodaje do daty podany czas

DATE_SUB(data, INTERVAL wyr typ) – odejmowanie daty

Funkcje daty i czasu (3)

ADDDATE(data,n) – dodaje n dni do daty
SUBDATE(data,n) – odejmuje n dni od daty
ADDTIME(s1,s2) – dodawanie czasu
SUBTIME(s1,s2) – odejmowanie czasu
EXTRACT(typ FROM data) – pobranie części daty
TO_DAYS(data) – zamienia datę na numer dnia
FROM_DAYS(n) – zamienia numer dnia na datę
DATE_FORMAT(data,format) – formatowanie daty
TIME_FORMAT(czas,format) – formatowanie czasu

Funkcje daty i czasu (4)

MAKEDATE(rok,dzień) – podaje datę
MAKETIME(godz,min,sek) – podaje czas
CURDATE() – bieżąca data
CURTIME() – bieżący czas
NOW() – bieżąca data i czas
UNIX_TIMESTAMP() – bieżąca data i czas w formacie UNIX
SEC_TO_TIME(sek) – konwersja sekund na czas
TIME_TO_SEC(czas) – konwersja czasu na sekundy

Przykład operacji na datach

Dodawanie i odejmowanie

DATE_ADD('1997-12-31 23:59:59', INTERVAL 1 DAY);
DATE_ADD('1997-12-31 23:59:59',

INTERVAL '1:1' MINUTE_SECOND);

DATE_SUB('1998-01-02', INTERVAL 31 DAY);

Pobranie części daty

EXTRACT(YEAR FROM "1999-07-02");

Formatowanie daty

DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y');

Funkcje konwersji

Konwersje typów danych
CAST(wyr AS typ) – zmiana wyr na typ
CONVERT(wyr,typ) – jw.
CONVERT(wyr USING kod) – zmiana strony kodowej

Typy: BINARY, CHAR, DATE, DATETIME,

SIGNED, TIME, UNSIGNED

Inne funkcje (1)

DATABASE() – nazwa bieżącej bazy danych
USER() – nazwa bieżącego użytkownika
PASSWORD(s) – koduje napis s jako hasło
ENCRYPT(s) – koduje napis s
ENCODE(s,pass) – koduje napis s przy użyciu hasła pass
DECODE(s,pass) – dekoduje napis s przy użyciu hasła pass
COMPRESS(s) – kompresja napisu s
UNCOMPRESS(s) – dekompresja napisu s
LAST_INSERT_ID() – ostatnio użyta

wartość AUTO_INCREMENT

Inne funkcje (2)

FORMAT(n,d) – formatuje liczbę n do d miejsc dziesiętnych
VERSION() – wersja bazy danych MySQL
CONNECTION_ID() – identyfikator połączenia
BENCHMARK(n,wyr) – oblicza czas wykonania wyr (n razy)
FOUND_ROWS() – liczba rekordów z ostatniego SELECT

Transakcje

Domyślnie wszystkie instrukcje są wykonywane od razu po
ich wprowadzeniu – zmiana danych w bazie.
W pewnych sytuacjach nie chcemy aby wykonywane
operacje modyfikowały fizyczny zbiór danych.

Tryb transakcji – wprowadzane operacje zostaną wykonane
dopiero po podaniu odpowiedniej komendy.

MySQL obsługuje kilka typów tabel, nie wszystkie
umożliwiają przeprowadzanie transakcji

Transakcje

START TRANSACTION

– rozpoczęcie transakcji

Kolejne operacje są zapamiętywane, ale nie są
wykonywane.

COMMIT

– wykonanie operacji z całej transakcji

ROLLBACK

– cofnięcie do początku transakcji

Niektóre komendy automatycznie wykonują COMMIT, np.
CREATE INDEX, DROP INDEX, DROP TABLE, DROP
DATABASE, ALTER TABLE, RENAME TABLE, TRUNCATE

Transakcje

Możliwe jest ustawienie w trakcie transakcji punktów
zapisu za pomocą komendy

SAVEPOINT nazwa

Wykonanie komendy

ROLLBACK TO SAVEPOINT nazwa

powoduje cofnięcie do punktu zapisu o podanej nazwie

COMMIT nadal wykonuje całą transakcję.

Blokowanie tabel

W pewnych sytuacjach potrzebne jest czasowe
zablokowanie tabeli, aby inny użytkownik nie zmodyfikował
danych.
Blokowanie:

LOCK TABLES tabela1 typ, tabela2 typ, ... ;

Typ blokady:

• READ

– blokada do odczytu

• WRITE

– blokada do zapisu

Odblokowanie tabel:

UNLOCK TABLES;

Typy tabel w MySQL

MySQL obsługuje różne standardy zapisywania tabel.

Typy nie udostępniające mechanizmu transakcji:

MyISAM

(domyślny),

ISAM

(stary),

HEAP

,

MERGE

Typy udostępniające mechanizm transakcji i blokowania:

InnoDB

,

BDB

Indeks pełnego tekstu (FULLTEXT) działa tylko w tabelach
MyISAM.

Typy tabel w MySQL

CREATE TABLE tworzy domyślnie tabelę MyISAM.
Aby utworzyć tabelę innego typu, należy podać żądany typ
na końcu instrukcji:

Zmiana typu tabeli (w praktyce utworzenie nowej tabeli,
przepisanie danych i usunięcie starej tabeli):

CREATE TABLE nazwa (definicja) TYPE=InnoDB;

ALTER TABLE nazwa TYPE=InnoDB;

Autoryzacja dostępu do bazy

System zarządzania bazą danych obsługuje tzw.

system

przywilejów

(privilege system):

• autoryzacja użytkownika łączącego się z bazą z

określonego komputera (login, hasło),

• określenie praw do wykonywania poszczególnych

operacji na bazie danych (przywileje)

Przywileje są ustalane na podstawie:

• nazwy użytkownika
• nazwy sieciowej komputera klienta
• operacji, którą chce wykonać użytkownik

Autoryzacja dostępu do bazy

Sprawdzanie przywilejów odbywa się na dwóch poziomach

Poziom 1 – połączenie z bazą

Sprawdzanie czy użytkownik o podanej nazwie ma prawo
połączyć się z bazą z danego komputera

Poziom 2 – wykonanie operacji

Sprawdzanie czy użytkownik ma prawo wykonać żądaną
operację na danych w określonej tabeli.

Użytkownicy i hasła

Użytkownik łącząc się z bazą podaje swój identyfikator
(login) oraz hasło (jeżeli jest ustawione).
Identyfikatory i hasła bazy MySQL są niezależne od
identyfikatorów i haseł systemu operacyjnego.
Połączenie z bazą z linii poleceń systemu:

Jeżeli nie podany zostanie identyfikator użytkownika, (-u)
przyjmowany jest identyfikator (login) systemowy
bieżącego użytkownika.
Opcja -p wymaga podania hasła

mysql -h serwer -u użytkownik -p baza_danych

Nadawanie praw dostępu

Nadanie praw wykonywania określonych operacji na
danych w bazie:

GRANT

przywilej (kolumny_tabeli)

ON

baza_danych.tabela

TO

użytkownik@host

IDENTIFIED BY

'hasło';

Odbieranie praw dostępu:

REVOKE

przywilej (kolumny_tabeli)

ON

baza_danych.tabela

FROM

użytkownik@host;

Rodzaje przywilejów

ALL – prawo do wykonywania wszystkich operacji
ALTER – zmiana def. tabeli (ALTER TABLE)
CREATE – tworzenie tabel (CREATE TABLE)
DELETE – kasowanie danych z tabeli (DELETE)
DROP – usuwanie tabel (DROP TABLE)
FILE – ładowanie danych z/do plików
INDEX – tworzenie i usuwanie indeksów
INSERT – wstawianie danych (INSERT)
SELECT – pobieranie danych (SELECT)
UPDATE – uaktualnianie danych (UPDATE)
USAGE – bez żadnych praw

Poziomy przywilejów

Przywileje dostępu do danych (ON) mogą dotyczyć:

• wszystkich baz danych na serwerze (poziom globalny):

GRANT ... ON *.*

• wszystkich tabel w określonej bazie:

GRANT ... ON baza_danych.*

• określonej tabeli:

GRANT ... ON baza_danych.tabela

• pojedynczych kolumn w określonej tabeli

Przywileje dla użytkowników

Przywileje dla użytkowników (TO)

• użytkownik user z dowolnego komputera:

user@'%'

• użytkownik user z komputera w domenie:

user@'%.eti.pg.gda.pl'

• użytkownik z określonego komputera:

[e-mail]
user@localhost
user@153.19.49.25

Ustawianie hasła

Domyślnie utworzony użytkownik nie posiada hasła, o ile
nie użyta zostanie opcja IDENTIFIED BY.

Ustalenie hasła przy nadawaniu praw:

Inna instrukcja do nadania hasła:

GRANT ALL ON *.* TO user IDENTIFIED BY 'hasło';

SET PASSWORD FOR user = PASSWORD('hasło');

Prawa dostępu – przykłady

Nadanie wszystkich praw do bazy forum dla użytkownika
www łączącego się z serwera

Nadanie wybranych praw do tabeli dane użytkownikowi joe
łączącemu się z podanej domeny

Uwaga – ta instrukcja jest niebezpieczna!

GRANT ALL ON forum.* TO www;

GRANT SELECT,INSERT,UPDATE ON forum.dane
TO joe@'%.eti.pg.gda.pl'
IDENTIFIED BY 'alamakota';

GRANT ALL ON *.* TO joe@'%';

Administrator bazy danych

Użytkownik root ma prawo dostępu do całej bazy – jest
administratorem bazy.
Po zainstalowaniu MySQL każdy może zalogować się jako
root bez hasła. Zatem należy ustawić hasło z linii poleceń:

Po połączeniu się z bazą systemową należy ustawić hasło:

Można również użyć programu mysqladmin:

mysql -u root mysql;

SET PASSWORD FOR root = PASSWORD('tajnehaslo');

mysqladmin -u root password tajnehaslo;

Dodawanie użytkowników

Dodanie użytkownika z jednoczesnym nadaniem praw
dostępu do danych:

Dodanie użytkownika bez nadania praw:

Dodanie na końcu opcji WITH GRANT OPTION pozwala
użytkownikowi na przekazywanie praw innym
użytkownikom.

GRANT ALL ON baza.* TO user IDENTIFIED BY 'haslo1';

GRANT USAGE ON baza.* TO user
IDENTIFIED BY 'haslo2';