Bazy danych
Wykład 5.
Język SQL - przegląd
Podstawy SQL
" Standardy SQL (ISO/ANSI)
 SQL1 (SQL 86)
 SQL2 (SQL92, SQL99)
 SQL3
" SQL  język deklaratywny, zorientowany na przetwarzanie
zbiorów
" Nie rozróżnia DUŻYCH i małych liter
" Nie są ważne białe znaki (spacje, tabulatory, koniec
wiersza, & )
" Instrukcja kończy się średnikiem ;
143
2013-01-10 Bazy danych / Krzysztof Molenda / WSEI w Krakowie
1
Podział instrukcji języka SQL
" DDL [ang. Data Definition Language] 
 instrukcje definiowania struktury danych:
" CREATE, DROP, ALTER
" DML [ang. Data Manipulation Language] -
 instrukcje manipulacji na danych:
" SELECT, INSERT, DELETE, MERGE, UPDATE
" DCL [ang. Data Control Language] -
 instrukcje kontroli dostępu do danych:
" GRANT, REVOKE, DENY, COMMIT, ROLLBACK
Instrukcje DDL
" CREATE TABLE - utworzenie nowej tabeli
" CREATE INDEX - utworzenie indeksu
" CREATE VIEW - utworzenie perspektywy
(nazwanego zapytania)
" ALTER TABLE - zmiana formatu tabeli
(dodanie, usunięcie, zmiana pól
tabeli)
" DROP TABLE - usuniecie tabeli z bazy danych
" DROP INDEX - usuniecie indeksu z bazy danych
" DROP VIEW - usuniecie perspektywy
2
Tworzenie tabel
CREATE TABLE nazwa_tabeli struktura_tabeli
 nazwa_tabeli = unikalna nazwa (identyfikator języka)
 struktura_tabeli = lista definicji atrybutów
" lista = pozycje w nawiasach okrągłych, oddzielone przecinkami
" definicja atrybutu =
 nazwa atrybutu
 typ pola, dziedzina
 ograniczenie integralnoÅ›ciowe (opcjonalnie Ä…ð jÄ™zyk integralnoÅ›ci danych)
 opcjonalnie wartość domyślna (DEFAULT)
Przykład:
CREATE TABLE studenci (
nr_indeksu INT NOT NULL PRIMARY KEY,
imie VARCHAR(50),
nazwisko VARCHAR(50)
);
146
Bazy danych / Krzysztof Molenda / WSEI w Krakowie
2013-01-10
Typy danych
Typy danych (nazwy, znaczenie, liczba) zależą od implementacji DBMS.
Konieczne zapoznanie siÄ™ z dokumentacjÄ… DBMS.
" Napisy
 CHAR(n), CHARACTER(n)  teksty o stałej długości n
 VARCHAR(n)  teksty o zmiennej długości nie przekraczającej n
" Liczby
 Całkowite: INT, INTEGER, SMALLINT, &
 Zmiennoprzecinkowe: REAL, DOUBLE PRECISION, NUMERIC(m, n),
DECIMAL(m, n)
" Data i czas
 DATE, TIME, TIMESTAMP, INTERVAL
" CiÄ…gi binarne
 BIT(n), BIT VARYING(n), &
" Inne
 BLOB, TEXT, IMAGE, MONEY, &
" Można definiować własne typy danych
" Autoinkrementacja  typ całkowity z wymuszeniem/rejestrowaniem
jednoznacznej wartości dla wiersza tabeli
147
2013-01-10 Bazy danych / Krzysztof Molenda / WSEI w Krakowie
3
Ograniczenia integralnościowe
" Integralność (spójność) danych  baza danych zawsze opisuje
rzeczywistość. Więzy integralności = warunki poprawności danych
 Więzy kluczy
 Więzy referencji
 Więzy dziedziny
" Więzy klucza
 określają który atrybut (które atrybuty  w przypadku klucza
złożonego) są kluczem głównym,
 wymuszają unikalność wartości klucza,
 wymuszają brak wartości NULL w kluczu,
 pozwalają utworzyć indeks dla atrybutów klucza,
148
Bazy danych / Krzysztof Molenda / WSEI w Krakowie
2013-01-10
Ograniczenia integralnościowe (1)
" Ograniczenia integralnościowe (użycie zależne od DBMS)
 PRIMARY KEY  klucz podstawowy
 FOREIGN KEY  klucz obcy
 NULL, NOT NULL  wartość pusta/niepusta
 CHECK  sprawdzenie wartości atrybutu
 UNIQUE  wartość unikalna
Przykład:
CREATE TABLE studenci (
nr_indeksu INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
imie VARCHAR(50) CHECK (imie LIKE '[A-Z]%'),
nazwisko VARCHAR(50) NOT NULL CHECK (nazwisko LIKE '[A-Z]%'),
rodzaj VARCHAR(7) CHECK(rodzaj IN ('dzienny', 'zaoczny')) DEFAULT 'dzienny',
rok_studiow INT CHECK(rok_studiow>=1 AND rok_studiow<=5 ) DEFAULT 1
);
" Sprawdzenie, czy imie oraz nazwisko zaczynają się od dużej litery; czy rok_studiow
jest liczbÄ… z zakresu 1..5, &
" Do tak utworzonej tabeli nie można wstawić żadnej wartości, która nie spełnia
nałożonych przez CHECK ograniczeń
150
2013-01-10 Bazy danych / Krzysztof Molenda / WSEI w Krakowie
4
Ograniczenia integralnościowe (2)
" Ograniczenia możemy nazywać i nimi możemy zarządzać
" Czasami musimy wprowadzać ograniczenia dopiero po utworzeniu tabeli
Przykład:
CREATE TABLE studenci (
nr_indeksu INT NOT,
imie VARCHAR(50) CHECK (imie LIKE '[A-Z]%'),
nazwisko VARCHAR(50) CHECK (nazwisko LIKE '[A-Z]%') NOT NULL,
rodzaj VARCHAR(7) CHECK(rodzaj IN ('dzienny', 'zaoczny')) DEFAULT 'dzienny',
rok_studiow INT CHECK(rok_studiow>=1 AND rok_studiow<=5 ) DEFAULT 1,
CONSTRAINT nr_indeksuPK PRIMARY KEY (nr_indeksu)
);
CREATE TABLE promotorzy (
id_promotora INT NOT NULL PRIMARY KEY AUTO_INCREMENT
nazwisko VARCHAR(50) NOT NULL
);
ALTER TABLE studenci
ADD CONSTRAINT opiekuje_sie FOREIGN KEY (id_promotora)
REFERENCES promotorzy(id_promotora);
151
Bazy danych / Krzysztof Molenda / WSEI w Krakowie
2013-01-10
Ograniczenia integralnościowe (3)
" Ważna kolejność wykonywania instrukcji
" CHECK może dotyczyć całej relacji
Przykład:
CREATE TABLE promotorzy (
id_promotora INT NOT NULL PRIMARY KEY AUTO_INCREMENT
nazwisko VARCHAR(50) NOT NULL
);
CREATE TABLE studenci (
nr_indeksu INT NOT PRIMARY KEY,
imie VARCHAR(50) CHECK (imie LIKE '[A-Z]%'),
nazwisko VARCHAR(50) CHECK (nazwisko LIKE '[A-Z]%') NOT NULL,
rodzaj VARCHAR(7) CHECK(rodzaj IN ('dzienny', 'zaoczny')) DEFAULT 'dzienny',
rok_studiow INT CHECK(rok_studiow>=1 AND rok_studiow<=5 ) DEFAULT 1,
id_promotora INT REFERENCES promotorzy(id_promotora),
data_przyjecia DATE NOT NULL,
data_odejscia DATE,
CHECK (data_przyjecia < data_odejscia)
);
152
2013-01-10 Bazy danych / Krzysztof Molenda / WSEI w Krakowie
5
Modyfikacje tabeli
DROP TABLE nazwa_tabeli;  usuwanie relacji
 DROP TABLE studenci;
Uwaga: Należy uprzednio upewnić się, czy do tabeli nie ma referencji z
jakichś innych tabel  wówczas tabeli nie da się usunąć bez uprzedniego
usunięcia tamtych.
ALTER TABLE nazwa_tabeli specyfikacja_struktury; - modyfikacja
struktury
 ALTER TABLE promotorzy ADD COLUMN imie VARCHAR(50);
 ALTER TABLE promotorzy ALTER COLUMN imie VARCHAR(30);
 ALTER TABLE promotorzy DROP COLUMN imie;
 ALTER TABLE studenci ADD CONSTRAINT zapisany_na & ;
 ALTER TABLE studenci DROP CONSTRAINT zapisany_na;
153
Bazy danych / Krzysztof Molenda / WSEI w Krakowie
2013-01-10
Instrukcje DML
" SELECT - wybieranie danych (zapytanie)
" SELECT INTO - zapisanie wyników zapytania do tablicy
" INSERT INTO - dodawanie rekordów do tablicy
" UPDATE - modyfikowanie danych
" DELETE - usunięcie danych
" UNION - suma tabel
" INTERSECT 1) - część wspólna tabel (iloczyn, przecięcie)
" MINUS 1) - różnica tabel
1)
nie wszędzie zaimplementowane
6
Wprowadzanie danych do tabel
INSERT INTO nazwa_tabeli [lista_atrybutów] VALUES lista_wartości ;
 lista = pozycje w nawiasach okrągłych, oddzielone przecinkami
 lista atrybutów  opcjonalnie
 lista wartości  jeśli brak listy atrybutów kolejność zamieszczenia wartości istotna
 Jeśli brak wartości dla atrybutu wymaganego, to przyjmowana wartość DEFAULT
 Jeśli nie spełnione reguły integralnościowe, to dane nie są wprowadzone
Przykład:
INSERT INTO studenci (imie, nazwisko, rok_studiow) VALUES ( Krzysztof ,
 Molenda , 2);
 wynik: dodano (21,  Krzysztof ,  Molenda ,  dzienny , 2)
INSERT INTO studenci (imie, nazwisko) VALUES ( Krzysztof ,  Molenda );
 wynik: dodano (21,  Krzysztof ,  Molenda ,  dzienny , 1)
INSERT INTO studenci VALUES ( Krzysztof ,  Molenda );
 wynik: dodano (21,  Krzysztof ,  Molenda ,  dzienny , 1)
INSERT INTO studenci (imie, rok_studiow) VALUES ( Krzysztof , 2);
 wynik: nie dodano
INSERT INTO absolwenci
SELECT imie, nazwisko FROM studenci WHERE data_odejscia IS NOT NULL;
155
Bazy danych / Krzysztof Molenda / WSEI w Krakowie
2013-01-10
Modyfikowanie i usuwanie danych
UPDATE nazwa_tabeli
SET nazwa_atrybutu = nowa_wartość,
[ nazwa_atrybutu = nowa_wartość, ...]
[WHERE warunek]
DELETE FROM nazwa_tabeli
WHERE kryteria
Przykład:
UPDATE studenci SET rok_studiow = rok_studiow + 1;
UPDATE studenci SET rok_studiow = -1 WHERE data_odejscia IS NOT NULL;
DELETE FROM studenci WHERE rok_studiow =-1;
156
2013-01-10 Bazy danych / Krzysztof Molenda / WSEI w Krakowie
7
Wybieranie danych  SELECT (1)
SELECT& - które pola
FROM & - z której (których) tabeli (tabel)
WHERE... - jakie kryterium selekcji
GROUP BY... - grupowanie
klauzule  dodatkowe
HAVING... - ograniczenie wybranych
określenie jak
rekordów w grupowaniu
wykonać zapytanie
ORDER BY... - porzÄ…dkowanie
157
Bazy danych / Krzysztof Molenda / WSEI w Krakowie
2013-01-10
Wybieranie danych  SELECT (2)
SELECT {predykat}
[ * | tabela.* | {tabela.} pole1 {AS alias1}{,{tabela.}pole2 {AS alias2} {,...}}]
FROM wyrażenie_tabelowe {, ...} {IN zewnętrzna_baza_danych}
{WHERE... }
{GROUP BY... }
{HAVING... }
{ORDER BY... }
" Istotna jest kolejność słów kluczowych
Przykład:
SELECT * FROM studenci WHERE rok = 3 AND rodzaj=  zaoczny ;
SELECT * FROM studenci WHERE rok = 1 ORDER BY srednia DESC, nazwisko ASC;
SELECT DISTINCT rok_studiow AS rocznik FROM studenci;
" Gwiazdka * oznacza wszystko
158
2013-01-10 Bazy danych / Krzysztof Molenda / WSEI w Krakowie
8
Nazwa zastępcza - ALIAS
ALIAS (synonim)
" ZastÄ…pienie nazwy pola
" ZastÄ…pienie nazwy tabeli
" Skrót upraszczający opis wyrażeń
SELECT nazwisko, imie, wynagrodzenie AS pobory
FROM pracownicy
WHERE pobory > 3000;
SELECT nazwisko, imie
FROM pracownicy AS pr
WHERE pr.pensja > 3000 AND pr.data<2-02-2000;
SELECT - przykłady
" Stałe w zapytaniu:
 SELECT nazwisko, imie, 100,  lat FROM osoby
" Wyrażenia w zapytaniu
 SELECT towar, ilosc, cena AS netto, cena*1.22 AS brutto, ilosc*cena FROM
pozycje_faktury
" Konkatenacja (inne funkcje):
 SELECT nazwisko ||   || substr( imie, 1, 1 ) ||  . FROM studenci
160
2013-01-10 Bazy danych / Krzysztof Molenda / WSEI w Krakowie
9
FUNKCJE SQL
Funkcje SQL  zależne od implementacji DBMS
" Znakowe:
 lower, upper, lpad, rpad, substr, replace, length, instr, &
" Liczbowe:
 round, trunc, ceil, floor, sign, mod, abs
" Działające na datach:
 sysdate, trunc, next_day, last_day, months_between, &
" Konwersji:
 to_char(liczba|data[, format ]), to_date(ciąg_znaków[, format ]),
to_number
" Inne
161
Bazy danych / Krzysztof Molenda / WSEI w Krakowie
2013-01-10
Operatory SQL
" Operatory arytmetyczne:
 * / + - (priorytety)
" Operator konkatenacji: ||
" Operatory relacyjne
 <, <=, >, >=, <>
" Operatory logiczne
 AND, OR, NOT
" Operatory SQL
 IS NULL, IS NOT NULL
 BEETWEN & AND &
 IN (zbiór)
 LIKE
" % - wiele znaków
" _ - jeden znak
162
2013-01-10 Bazy danych / Krzysztof Molenda / WSEI w Krakowie
10
Agregacja i grupowanie
Agregacja - wyznaczenie
jednej z następujących
wartości dla zbioru
Miesiąc Dochód Razem
rekordów: sumy, średniej,
Styczeń 1000
maksimum, minimum
Styczeń 2300
" Funkcje agregujÄ…ce:
Styczeń 4000 7300
 SUM - suma
Luty 1000
 COUNT - liczba elementów
Luty 1300 2300
w grupie
Marzec 1450
 AVG  średnia
arytmetyczna Marzec 2300
 MAX - wartości maksymalna Marzec 100 3850
 MIN - wartość minimalnej
SELECT Miesiąc, SUM(Dochód)
 FIRST - pierwszy z grupy
FROM Zestaw
 LAST - ostatni z grupy
GROUP BY MiesiÄ…c;
Przykłady
" Zliczanie
SELECT nr_faktury, count(*)
FROM pozycje
GOUP BY nr_faktury
" Zliczanie liczby wierszy z wartością niepustą
SELECT nr_czytelnika, count(data_zwr)
FROM wypozyczenia
GROUP BY nr_czytelnika
" Zliczanie średniej atrybutu z wartością niepustą
SELECT avg(wzrost)
FROM dzieci
164
2013-01-10 Bazy danych / Krzysztof Molenda / WSEI w Krakowie
11
Klauzula HAVING
" Klauzula WHERE  dotyczy wierszy
" Klauzula HAVING  dotyczy grup
SELECT cust_name, sum(total-nvl(vat,0)) AS netto
FROM customers AS c, orders AS o
WHERE order_date < sysdate-14
AND o.cust_id = c.cust_id
GROUP BY cust_name
HAVING sum(total)>100
ORDER BY 2;
165
Bazy danych / Krzysztof Molenda / WSEI w Krakowie
2013-01-10
Zapytania zagnieżdżone
Wykorzystanie wyniku jednego zapytania jako parametru drugiego
zapytania
Zapytanie2
Tabela2
Wynik
Zapytanie1
parametr
Wynik1
Tabela
SELECT a, b
FROM tabela2
WHERE w IN(
SELECT MAX(w) FROM tabela
);
12
Perspektywy (ang. VIEW)
Zapamiętanie zapytania pod określoną nazwą (synonim zapytania)
Ukrycie złożonego zapytania  ułatwienie dla użytkownika
Wspomagają realizację łańcucha zapytań
Zapytanie1 Zapytanie2
Tabela Wynik pośredni Wynik
Zdefiniowanie widoku (perspektywy):
CREATE VIEW Wpływy_mies AS
SELECT miesiąc, SUM(wpływ) AS sumaW
FROM operacje
GROUP BY MiesiÄ…c;
Użycie widoku:
SELECT * FROM Wpływy_mies WHERE miesiąc<12;
167
Bazy danych / Krzysztof Molenda
2013-01-10
Podzapytania jednowartościowe i równoległe
SELECT *
FROM dzieci
WHERE wiek > (SELECT avg(wiek) FROM dzieci)
SELECT *
FROM dzieci
WHERE
wiek > (SELECT avg(wiek) FROM dzieci)
AND
waga > (SELECT avg(waga) FROM dzieci)
168
2013-01-10 Bazy danych / Krzysztof Molenda
13
Zapytania z wielu tabel
Zapytania z wielu tabel dają możliwość realizacji:
- iloczynu kartezjańskiego
- złączeń (naturalnych, theta)
SELECT * FROM Pracownik, Prezent; - iloczyn kartezjański
SELECT * FROM Pracownik, Instytut
WHERE Pracownik.Inst=Instytut.ID; - złączenie naturalne
SELECT * FROM Pracownik
INNER JOIN Instytut ON
Pracownik.Inst=Instytut.ID; - złączenie naturalne
169
Bazy danych / Krzysztof Molenda
2013-01-10
ZÅ‚Ä…czenia opcjonalne
" Wartość NULL  oznaczenie że rekord nie jest związany
" Wykorzystanie słów kluczowych RIGHT, LEFT JOIN
SELECT * FROM Pracownik, Instytut
WHERE
(Pracownik.Inst = Instytut.ID)
OR
(Pracownik.Inst IS NULL); - złączenie opcjonalne
SELECT * FROM Pracownik
LEFT JOIN Instytut ON
Instytut.ID = Pracownik.Inst; - złączenie opcjonalne
170
2013-01-10 Bazy danych / Krzysztof Molenda
14
Dopełnienie zapytania
SELECT atrybuty FROM tabela
WHERE atrybut NOT IN (Zapytanie1);
Wybranie wpływów, które nie są maksymalne
SELECT * FROM wpływy
WHERE kwota NOT IN
( SELECT MAX(kwota) FROM wpływy );
171
Bazy danych / Krzysztof Molenda
2013-01-10
Samozłączenie
Zastosowanie w rekurencji, polega na utworzeniu
synonimu tabeli:
SELECT atrybuty
FROM tabela, tabela AS synonim_tabeli
...
Tabela:
Pracownik ( id, nazwisko, imie, id_kierownika )
 zwiÄ…zek rekurencyjny przez pola id = id_kierownika
SELECT nazwisko, kierownik.nazwisko
FROM pracownik, pracownik AS kierownik
WHERE pracownik.id_kierownika = kierownik.id;
15
Operacje teoriomnogościowe - SUMA
" UNION/ UNION ALL
" INTERSECT
" MINUS
SELECT nr_czytelnika, nr_ksiazki, data_wyp, null
FROM wypozyczenia
UNION
SELECT nr_czytelnika, nr_ksiazki, data_wyp, data_zwr
FROM zwroty
173
Bazy danych / Krzysztof Molenda
2013-01-10
Przykłady (różne)
UPDATE pracownicy
SET premia = premia+100
WHERE placa = (SELECT MAX(placa) FROM pracownicy);
UPDATE pracownicy
SET (premia, urlop) =
(SELECT AVG(premia), AVG(urlop) FROM pracownicy)
WHERE dzial = 2;
DELETE FROM dzieci
WHERE wiek > (SELECT AVG(wiek) FROM dzieci);
INSERT INTO stare_wypozyczenia
SELECT * FROM wypozyczenia
WHERE data_zwr >  10.03.1988 ;
174
2013-01-10 Bazy danych / Krzysztof Molenda
16