SQL

SQL – Strukturalny język
zapytań
Wprowadzenie do systemów baz danych
Historia
 Prace nad językiem rozpoczęto w 1982 roku
 1986 – standard ANSI (American National Standards




Committee) – SQL-86
1987 – standard ISO (International Standards Organization) –
SQL-87 (SQL1)
1992 – SQL-92 (SQL2)
1999 – SQL-99 (SQL3)
2003 – SQL2003
Funkcje języka
 Język definiowania danych (ang. Data Definition Language –
DDL)


Definiowanie struktury bazy danych
Kontrola integralności
 Język zarządzania (manipulowania) danymi (ang. Data
Manipulation Language – DML)


Wprowadzanie, modyfikacja i kasowanie danych
Przeszukiwanie danych
 Język kontroli danych (ang. Data Control Language – DCL)

Nadawanie i odbieranie uprawnień do korzystania z
bazy danych
Typy danych - napisowe
 CHARACTER(n) – tablica znaków o stałej długości n
Skrót CHAR
 CHARACTER VARYING(n) – tablica znaków o zmiennej
długości – liczba n określa długość maksymalną
 Skróty: VARCHAR, CHAR VARYING
 NATIONAL CHARACTER(n)
 Skróty: NATIONAL CHAR, NCHAR
 NATIONAL CHARACTER VARYING(n)
 Skróty: NATIONAL CHAR VARYING, NCHAR VARYING,
NVARCHAR

Typy danych - liczbowe
 EXACT NUMERIC (stałoprzecinkowe)
INTEGER – liczba całkowita
 NUMERIC(m,n) – liczba dziesiętna o długości m z n
miejscami po przecinku (m – precision, n – scale)
 DECIMAL(m,n) – liczba dziesiętna o długości m z n
miejscami po przecinku (rzeczywista długość liczby może
być większa od n)
 APPROXIMATE NUMERIC (zmiennoprzecinkowe)
 FLOAT[(m)] – liczba zmiennoprzecinkowa
 REAL – liczba zmiennoprzecinkowa (krótka)
 DOUBLE PRECISION – liczba zmiennoprzecinkowa
podwójnej precyzji

Typy danych określające czas

DATE – data


TIME – czas



TIMESTAMP '2001-01-01 12:20:10'
TIMESTAMP '2007-12-31 23:59:59' - TIMESTAMP '2007-01-01 00:00:00 ' = 364
days 23:59:59 = 364 23:59:59.0
INTERVAL – różnica czasu






TIME '12:00:00' - TIME '15:10:00' = -03:10:00
TIMESTAMP – data i czas (stempel czasowy)


DATE '1999-12-12' - DATE '1900-01-01' = 36504
DATE '1900-01-01' + 36504 = 1999-12-12
DATE '2007-01-01' + 1 YEAR = 07/01/02 (Oracle)
DATE '2007-01-01' + INTERVAL '1 YEAR' = 2008-01-01 00:00:00 (PostgreSQL)
DATE '0000-01-01' + INTERVAL '2001 YEAR 3 MONTH 10 DAY' = 2001-04-11
00:00:00 (PostgreSQL)
TIMESTAMP '2007-01-01 00:00:00' + INTERVAL '2 month 5 day 15:10:00' =
2007-03-06 15:10:00 (PostgreSQL)
Funkcje czasu i daty:
CURRENT_DATE, CURRENT_TIME[()], CURRENT_TIMESTAMP[()]
Tworzenie tabeli
 CREATE TABLE nazwa_tabeli
(nazwa_kolumny typ_kolumny [ograniczenia_kolumny],
nazwa_kolumny typ_kolumny [ograniczenia_kolumny],
..............,
[ograniczenia_tabeli])
Ograniczenia kolumny
 PRIMARY KEY
 NOT NULL
 UNIQUE
 CHECK (warunek)
 REFERENCES tabela_zewnętrzna(nazwa_kolumny)
[ON DELETE reakcja]
[ON UPDATE reakcja]
 DEFAULT wartość

Aby wpisać wartość domyślną, w instrukcji INSERT INTO,
należy wpisać DEFAULT
Ograniczenia tabeli




PRIMARY KEY (nazwa_kolumny [, ...])
UNIQUE (nazwa_kolumny [, ...])
CHECK (warunek)
FOREIGN KEY (nazwa_kolumny [, ...])
REFERENCES tabela(nazwa_kolumny [, ...])
[ON DELETE reakcja]
[ON UPDATE reakcja]
 Ograniczenia tabeli można poprzedzić nazwą:
CONSTRAINT Nazwa_ograniczenia Ograniczenie tabeli
Naruszenie integralności referencyjnej
 Można określić reakcję systemu na próbę naruszenia zasad
integralności referencyjnej poprzez usunięcie bądź zmianę
wartości „klucza rodzica” w powiązanej tabeli
 Reakcje to:





Odmowa wykonania modyfikacji (domyślna) – NO ACTION,
RESTRICT
Kaskadowa aktualizacja wierszy powiązanych – CASCADE
Kaskadowe usunięcie wierszy powiązanych – CASCADE
Wstawienie do wierszy powiązanych wartości NULL – SET NULL
Wstawienie do wierszy powiązanych wartości domyślnej – SET
DEFAULT
Klucz obcy – związek jeden do wiele
Diagram standardu Integration DEFinition (IDEF1X)
MS Access
Diagram MS SQL Server
Ograniczenie CHECK
 Ograniczenie CHECK powoduje sprawdzanie warunku podczas
wprowadzania lub uaktualniania danych.
 Operacja jest wykonywana, jeśli wartość logiczna warunku jest
TRUE
 Klauzula CHECK wymusza integralność dziedziny
 Przykłady




CHECK (wzrost BETWEEN 50 AND 250)
CHECK kierunek IN ('KSS', 'SiST', 'EO', 'EM')
Warunek powinien być stały, nie zależeć od zmiennych, danych w
innych tabelach ani od bieżącej daty (czasu)
Porównania wpisywanej daty z datą bieżącą można dokonać przez
procedurę wyzwalaną
Odraczanie sprawdzania więzów
 Więzy można zadeklarować jako możliwe do odroczenia
DEFERRABLE lub jako niemożliwe do odroczenia NOT
DEFERRABLE (wartość domyślna).
 Odroczenie sprawdzania więzów możliwe jest na czas trwania
transakcji i stosowane w przypadkach więzów zapętlonych.
 Więzy odraczane można dodatkowo zadeklarować jako
odraczane od początku INITIALLY DEFERRED lub
natychmiastowe od początku INITIALLY IMMEDIATE
 Określenie trybu sprawdzania ograniczeń
SET CONSTRAINS {nazwa_ograniczenia | ALL}
{DEFERRED | IMMEDIATE}
Przykład
 CREATE TABLE studenci
(pid CHAR(4) PRIMARY KEY,
imię VARCHAR(15),
nazwisko VARCHAR(15) NOT NULL,
imię_ojca VARCHAR(15),
semestr INTEGER CHECK (semestr<10))
 CREATE TABLE studenci1
(id_studenta CHAR(4),
nazwisko
VARCHAR(25) NOT NULL,
imie
VARCHAR(25),
imie_ojca
VARCHAR(25),
CONSTRAINT Klucz_glowny_studenci PRIMARY KEY(id_studenta));
Przykład
 CREATE TABLE oceny
(pid CHAR(4) REFERENCES studenci(pid),
przedmiot VARCHAR(15),
ocena VARCHAR(10),
data DATE,
PRIMARY KEY (pid, przedmiot))
Zmiana struktury bazy danych
 ALTER TABLE nazwa_tabeli




ADD [COLUMN] nazwa_kolumny typ_kolumny
ALTER TABLE nazwa_tabeli
ALTER | RENAME [COLUMN]
stara_nazwa TO nowa_nazwa
ALTER TABLE nazwa_tabeli
DROP [COLUMN] nazwa_kolumny [RESTRICT | CASCADE ]
ALTER TABLE nazwa_tabeli
ADD definicja ograniczenia tabeli
ALTER TABLE nazwa_tabeli
DROP CONSTRAINT nazwa_ograniczenia
Diagram (IDEF1X) prostej bazy danych
i skrypt SQL tworzący tą bazę
(PostgreSQL)
Skrypt SQL tworzący tab. Oceny
skrypt SQL tworzący bazę danych
Diagram (MS SQL Server) prostej bazy danych
i skrypt SQL tworzący tą bazę
(MS SQL Server)
Skrypt SQL tworzący tab. oceny
skrypt SQL tworzący bazę danych
Diagram prostej bazy danych
(Oracle)
skrypt SQL tworzący tabelę
EMPLOYEES
HR
Diagram prostej bazy danych
HR (kurze łapki)
Narzędzia graficzne do tworzenia tabel
baz danych
SQL Server
Enterprise Manager
Narzędzia graficzne do tworzenia tabel
Oracle
Indeksy
 Krotki są zapisywane w pliku w kolejności pojawiania się
 Przeszukiwanie dużego pliku w poszukiwaniu krotki może zająć
wiele czasu
 W celu przyspieszenia wyszukiwania krotek, na podstawie
wartości atrybutu, tworzy się indeks na tym atrybucie
 Stosowanie indeksów przyspiesza wyszukiwanie danych, ale
spowalnia ich zapis
Tworzenie indeksu
 CREATE [UNIQUE] INDEX nazwa_indeksu
ON nazwa_tabeli(nazwa_kolumny [, ...])
 Przykłady:
 CREATE UNIQUE INDEX indeks_studenci
ON studenci(nazwisko, imię)
 CREATE INDEX indeks_przedmioty
ON oceny(przedmiot)
Usuwanie tabel i indeksów
 DROP TABLE nazwa_tabeli
 DROP INDEX nazwa_indeksu
 Przykłady:


DROP TABLE studenci
DROP INDEX indeks_studenci
Wprowadzanie danych do tabel
 INSERT INTO nazwa_tabeli[(nazwa_kolumny [,..])]
VALUES (wartość [, ...])
 INSERT INTO nazwa_tabeli[(nazwa_kolumny [,..])]
zapytanie SELECT
 Przykłady:
 INSERT INTO studenci
VALUES (’0001’, ’Jan’, ’Papkin’, ’Jan’)
 INSERT INTO studenci(pid, nazwisko)
VALUES (’0002’, ’Ramzes’)
 INSERT INTO studenci
SELECT pid, nazwisko, imie, imie_ojca FROM kandydaci
WHERE …
 W miejsce wartości można wpisać: DEFAULT lub NULL
Wybieranie danych
 SELECT [DISTINCT]
wyrażenie [AS nazwa_kolumny] [,...]
[FROM źródło_elementów]
[WHERE warunek]
[GROUP BY wyrażenie [,...]]
[HAVING warunek]
[{UNION | INTERSECT | EXCEPT} instrukcja_SELECT]
[ORDER BY wyrażenie [ASC | DESC]
Proste przykłady SELECT
 SELECT * FROM studenci
 SELECT * FROM oceny
WHERE pid = ’0001’
 SELECT * FROM studenci NATURAL JOIN oceny
 SELECT * FROM studenci NATURAL JOIN oceny
WHERE pid='0017'
 SELECT DISTINCT studenci.nazwisko, studenci.imie, oceny.
przedmiot, oceny.ocena, oceny.data
FROM studenci, oceny
WHERE oceny. przedmiot = ‘OiSA'
AND studenci.pid = oceny.pid
ORDER BY studenci.nazwisko
Klauzula SELECT
 Klauzula SELECT określa wynikowe kolumny, i może zawierać





Nazwy kolumn wynikowych
Wyrażenia zawierające funkcje, nazwy kolumn i stałe
Stosując AS można nadać wynikowym kolumnom nazwy
inne od domyślnych
Symbol * oznacza wszystkie kolumny
Dyrektywę DISTINCT likwidującą powtórzenia wierszy
 Nazwy kolumn:


nazwa_tabeli.nazwa_kolumny
nazwa_kolumny – jeśli nazwa kolumny jednoznacznie
określa tabelę, z której pochodzi (brak w źródle
elementów kolumn o tych samych nazwach)
Wyrażenia arytmetyczne
 Operatory: +, -, *, /
 Funkcje matematyczne: ABS(x), POWER(x,y), SQRT(x),
RAND() – generuje liczbę losową z zakresu od 0 do 1
 Funkcje zaokrąglania liczb:
 CEILING(x) – funkcja zaokrągla swój argument w górę do
najbliższej liczy całkowitej (Oracle – CEIL(x))
 FLOOR(x) – funkcja zaokrągla swój argument w dół do
najbliższej liczy całkowitej
 ROUND(x,n) – zaokrągla liczbę x do n miejsc po przecinku
 Jeśli w wyrażeniu matematycznym choć jeden z
argumentów jest NULL, to wynik tego wyrażenia zawsze
jest NULL
Wyrażenia tekstowe (napisowe)
 Łączenie (konkatenacja) łańcuchów
 Operator konkatenacji || (Oracle, dB2, PostgreSQL)
 'tekst' || NULL = NULL
 SELECT nazwisko || ' ' || imie FROM
 Funkcja CONCAT(x,y) (Oracle, dB2, MySQL)
 Operator + w MS SQL Server
 Operatory: +, & w MS Access
 'tekst' + NULL = NULL
 'tekst' & NULL = 'tekst'
Funkcje tekstowe





LOWER(x), UPPER(x)
CHARACTER_LENGTH(x), LENGTH(x)
SUBSTRING(x FROM n FOR m)
POSITION(x1 IN x)
TRIM(ltb wzorzec FROM napis)

ltb – LEADING, TRAILING, BOTH
 COALESCE(x1, x2, …) – funkcja zwraca pierwszy
argument, który jest różny od NULL (używana przy
łączeniu łańcuchów, z których jeden lub więcej może
być NULL)
x, x1, x2 – wyrażenie łańcuchowe (napis)
Konwersje typów
 CAST(wyrażenie AS typ_danych)
 CAST(ocena AS NUMERIC(2, 1))
CASE (2 formy)
Forma 1
CASE
lista klauzul WHEN
ELSE wyrażenie skalarne
END
Klauzula WHEN ma postać:
WHEN wyrażenie_warunkowe THEN wyrażenie_skalarne
Forma 2
CASE wyrażenie_skalarne
lista klauzul WHEN
ELSE wyrażenie skalarne
END
Klauzula WHEN ma postać:
WHEN wyrażenie_skalarne THEN wyrażenie_skalarne
Klauzule WHEN są wykonywane kolejno, pierwszy spełniony warunek określa wynik końcowy
i sprawdzanie klauzul zostaje zakończone
Przykład użycia CASE (2 formy)
SELECT ocena,
CASE
WHEN ocena > 4 THEN 'gratuluję'
WHEN ocena > 3 THEN 'dobrze'
WHEN ocena > 2 THEN 'nienajlepiej'
WHEN ocena = 2 THEN 'może następnym razem'
END AS "komentarz 1",
CASE ocena
WHEN 5 THEN 'gratuluję'
WHEN 4.5 THEN 'też gratuluję'
WHEN 4 THEN 'dobrze'
WHEN 3.5 THEN 'no średnio'
WHEN 3 THEN 'nienajlepiej'
ELSE
'może następnym razem'
END AS "komentarz 2"
FROM oceny
Funkcje agregujące
 Funkcje agregujące wyznaczają jedną wartość z grupy wartości
w kolumnie – grupy wierszy określa klauzula GROUP BY
 COUNT – zlicz ilość wszystkich wartości (w kolumnie)
różnych od NULL





COUNT(*) – zlicza wszystkie wiersze
AVG – oblicza średnią ze wszystkich wartości
SUM – sumuje wartości
MAX – zwraca wartość największą
MIN – zwraca wartość najmniejszą
W przypadku użycia jednocześnie wyrażeń i funkcji agregujących
wszystkie wyrażenia muszą wystąpić w klauzuli GROUP BY
Klauzula FROM


Klauzula FROM zawiera „wyrażenie tabelowe” określające źródło danych dla
zapytania
Wyrażenie tabelowe klauzuli FROM może zawierać

Listę tabel i perspektyw źródłowych


A, B = A CROSS JOIN B
 Złączenia tabel






A, B – oznacza iloczyn kartezjański (złączenie krzyżowe) tabel A i B
A NATURAL [typ] JOIN B – złączenie naturalne (po kolumnach o tych samych nazwach w
obu tabelach)
A [typ] JOIN B ON warunek
A [typ] JOIN B USING (kolumna [,...])
Typy złączeń zewnętrznych:
{LEFT | RIGHT | FULL} [OUTER]
Podzapytania – umieszczone w nawiasach i z nadanymi aliasami
Tabelom można nadać zastępcze nazwy nazywane: aliasami, nazwami
skorelowanymi i zmiennymi zakresu
 format: nazwa_tabeli_źródłowej [AS] alias
 nadawanie aliasów tabelom źródłowym stosuje się przy „samozłączeniach”
tabel
 alias trzeba nadać wynikowi podzapytania, które może być użyte w zastępstwie
tabeli
Przykład samozłączenia
select
"PRACOWNICY"."FIRST_NAME" as "Imię",
"PRACOWNICY"."LAST_NAME" as "Nazwisko",
"SZEFOWIE"."FIRST_NAME" as "Imię(szefa)",
"SZEFOWIE"."LAST_NAME" as "Nazwisko(szefa)"
from
"EMPLOYEES" "PRACOWNICY" LEFT JOIN
"EMPLOYEES" "SZEFOWIE" ON
"PRACOWNICY"."MANAGER_ID"="SZEFOWIE"."EMPLOYEE_ID"
Klauzula WHERE
 Zawiera warunek selekcji wierszy tabeli wynikowej nazywany




często wyrażeniem ograniczającym
Warunek (predykat) jest wyrażeniem logicznym (funkcją
zdaniową) – w tabeli wynikowej znajdą się tylko wiersze
spełniające warunek
Predykat może zawierać: nazwy kolumn, wyrażenia logiczne,
operatory porównań, funkcje i zagnieżdżone instrukcje SELECT
(podzapytania)
Predykaty mogą mieć wartość logiczną TRUE, FALSE lub NULL
Na logicznym rachunku predykatów opiera się rachunek
relacyjny – w przypadku języka SQL jest to rachunek relacyjny
na krotkach
Predykaty - przykłady
 Porównania: =, <, >, <=, >=, <>

a >= b; a <b; itp.
 BETWEEN: x BETWEEN 23 AND 143  x >= 23 AND x <= 143
 IN: x IN (1, 2, 3); x IN (SELECT y FROM tabela_A)
 LIKE: nazwisko LIKE ’Kowal%’
’_’ – zastępuje dowolny pojedynczy znak
’%’ – zastępuje ciąg pusty lub składający się z dowolnej ilości
znaków
W programie MS Access znaki ’_’, ’%’ są zastąpione przez ’?’, ’*’
 NULL: x IS NULL; y IS NOT NULL
 EXISTS:
EXISTS (SELECT * FROM zakupy WHERE id_klienta=’0123’)
 OVERLAPS
Porównania z wynikami podzapytań
 ANY, SOME
 X > ANY (SELECT … )
 X > ANY (1,2,3) ≡ X >1
 X <= ANY (1,2,3) ≡ X <=3
 ALL
 X > ALL (SELECT … )
 X > ALL (1,2,3) ≡ X >3
 X <= ALL (1,2,3) ≡ X <=1
 UNIQUE (podzapytanie)
 MATCH
Klauzule GROUP BY i HAVING
 Klauzula GROUP BY jest używana do definiowania grup
wyjściowych wierszy
 Dla grup wierszy można używać funkcji agregujących (COUNT,
MIN, MAX,SUM, AVG)
 Klauzula HAVING (podobnie jak WHERE) odrzuca grupy
wierszy nie spełniające warunku (predykatu)
Przykład zapytania grupującego
SELECT studenci.nazwisko || studenci.imie AS student,
srednie."średnia"
FROM
(SELECT oceny.id_studenta, AVG(oceny.ocena) AS "średnia"
FROM oceny
GROUP BY oceny.id_studenta) srednie
NATURAL JOIN studenci
UNION, INTERSECT, EXCEPT, ORDER BY
 UNION – operator sumowania wyników dwóch instrukcji
SELECT
 INTERSECT – operator przecięcia wyników dwóch instrukcji
SELECT
 EXCEPT – operator różnicy wyników dwóch instrukcji SELECT
 ORDER BY – klauzula określająca kryterium sortowania
 ASC – oznacza kolejność sortowania rosnącą (domyślna)
 DESC – oznacza kolejność sortowania malejącą
Przykład UNION
SELECT 'student' AS funkcja, nazwisko AS "Nazwisko", imie AS "Imię"
FROM studenci
UNION
SELECT "Stopien_naukowy", "Nazwisko", "Imie"
FROM wykladowcy
ORDER BY 2
Tworzenie perspektyw
 CREATE VIEW nazwa[(lista kolumn)]




AS (instrukcja_SELECT)
Perspektywa (widok) jest tabelą wirtualną
Perspektywa jest traktowana jak tabela, ale jej definicja zawiera
instrukcję SELECT
Kiedy w instrukcji używa się perspektywy, wynik zapytania staje
się jej zawartością na czas trwania instrukcji
Przykład
Tworzenie schematu
 Obiekty bazy danych mogą być grupowane w schematy
 Nazwy obiektów muszą być unikalne w obrębie schematu,
ale nie muszą się różnić miedzy schematami
 CREATE SCHEMA nazwa_schematu AUTHORIZATION
identyfikator_uprawnień
 CREATE SCHEMA student1 AUTHORIZATION student1
Usuwanie danych
 DELETE FROM nazwa_tabeli
WHERE wyrażenie_ograniczające
 Przykłady:
 DELETE FROM studenci
WHERE pid = ’0001’
 DELETE FROM studenci
WHERE pid < ’0009’
Aktualizacja danych
 UPDATE nazwa_tabeli SET nazwa_kolumny = wyrażenie [,…]
WHERE wyrażenie_ograniczające
 Przykłady
 UPDATE płace SET płaca = płaca * 2
WHERE nazwisko = ’Kowalski’
 UPDATE płace SET płaca = płaca + 10
WHERE nazwisko LIKE ’Kow%’
Obsługa dużych obiektów
 Współczesne bazy danych często muszą przechowywać duże
obiekty takie jak
 Obiekty graficzne – fotografie osób, odcisków palców, wzory
podpisów itp.
 Pliki dźwiękowe
 Programy
 Duże obiekty tekstowe
 Do przechowywania dużych obiektów służą typy danych
 BLOB (Binary Large Object)
 CLOB (Chracter Large Object)
 NCLOB (National Chracter Large Object)
Nawiązanie połączenia z SZBD
 CONNECT TO {DEFAULT |
nazwa_serwera
[AS nazwa połączenia]
[USER nazwa_użytkownika] }
 Przykłady
 CONNECT student/hasło@localhost:1521/XE
(ORACLE)
 PSQL [opcje] [nazwa_bazy_danych [nazwa_użytkownika]]
(PostgreSQL)
\connect [nazwa_bazy_danych [nazwa_użytkownika]]