1

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

;

2013-01-10

Bazy danych / Krzysztof Molenda / WSEI w Krakowie

143

2

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

3

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)

);

2013-01-10

Bazy danych / Krzysztof Molenda / WSEI w Krakowie

146

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

2013-01-10

Bazy danych / Krzysztof Molenda / WSEI w Krakowie

147

4

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,

2013-01-10

Bazy danych / Krzysztof Molenda / WSEI w Krakowie

148

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ń

2013-01-10

Bazy danych / Krzysztof Molenda / WSEI w Krakowie

150

5

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);

2013-01-10

Bazy danych / Krzysztof Molenda / WSEI w Krakowie

151

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)

);

2013-01-10

Bazy danych / Krzysztof Molenda / WSEI w Krakowie

152

6

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;

2013-01-10

Bazy danych / Krzysztof Molenda / WSEI w Krakowie

153

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

7

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;

2013-01-10

Bazy danych / Krzysztof Molenda / WSEI w Krakowie

155

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;

2013-01-10

Bazy danych / Krzysztof Molenda / WSEI w Krakowie

156

8

Wybieranie danych

– SELECT (1)

2013-01-10

Bazy danych / Krzysztof Molenda / WSEI w Krakowie

157

SELECT…

-

które pola

FROM

…

-

z której (których) tabeli (tabel)

WHERE...

-

jakie kryterium selekcji

GROUP BY... -

grupowanie

HAVING...

-

ograniczenie wybranych

rekordów w grupowaniu

ORDER BY... -

porządkowanie

klauzule – dodatkowe

określenie jak
wykonać zapytanie

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

2013-01-10

Bazy danych / Krzysztof Molenda / WSEI w Krakowie

158

9

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

2013-01-10

Bazy danych / Krzysztof Molenda / WSEI w Krakowie

160

10

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

2013-01-10

Bazy danych / Krzysztof Molenda / WSEI w Krakowie

161

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

2013-01-10

Bazy danych / Krzysztof Molenda / WSEI w Krakowie

162

11

SELECT Miesiąc, SUM(Dochód)

FROM Zestaw
GROUP BY Miesiąc;

Agregacja i grupowanie

Agregacja - wyznaczenie

jednej z następujących
wartości dla zbioru
rekordów: sumy, średniej,
maksimum, minimum

• Funkcje agregujące:

– SUM

- suma

– COUNT

- liczba elementów

w grupie

– AVG

– średnia

arytmetyczna

– MAX

- wartości maksymalna

– MIN

- wartość minimalnej

– FIRST

- pierwszy z grupy

– LAST

- ostatni z grupy

Miesiąc

Dochód

Razem

Styczeń

1000

7300

Styczeń

2300

Styczeń

4000

Luty

1000

2300

Luty

1300

Marzec

1450

3850

Marzec

2300

Marzec

100

Przykłady

2013-01-10

Bazy danych / Krzysztof Molenda / WSEI w Krakowie

164

SELECT nr_faktury, count(*)

FROM pozycje
GOUP BY nr_faktury

•Zliczanie

SELECT nr_czytelnika, count(data_zwr)

FROM wypozyczenia
GROUP BY nr_czytelnika

•Zliczanie liczby wierszy z wartością niepustą

SELECT avg(wzrost)

FROM dzieci

•Zliczanie średniej atrybutu z wartością niepustą

12

Klauzula HAVING

2013-01-10

Bazy danych / Krzysztof Molenda / WSEI w Krakowie

165

•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;

SELECT a, b

FROM tabela2
WHERE w IN(
SELECT MAX(w) FROM tabela
);

Wykorzystanie wyniku jednego zapytania jako parametru drugiego
zapytania

Tabela

Wynik1

Wynik

Zapytanie1

Zapytanie2

Tabela2

parametr

Zapytania zagnieżdżone

13

Perspektywy (ang. VIEW)

2013-01-10

Bazy danych / Krzysztof Molenda

167

Zapamiętanie zapytania pod określoną nazwą (synonim zapytania)

Ukrycie złożonego zapytania – ułatwienie dla użytkownika

Wspomagają realizację łańcucha zapytań

Tabela

Wynik pośredni

Wynik

Zapytanie1

Zapytanie2

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;

Podzapytania

jednowartościowe i równoległe

2013-01-10

Bazy danych / Krzysztof Molenda

168

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)

14

Zapytania z wielu tabel

2013-01-10

Bazy danych / Krzysztof Molenda

169

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

Złączenia opcjonalne

2013-01-10

Bazy danych / Krzysztof Molenda

170

•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

15

Dopełnienie zapytania

2013-01-10

Bazy danych / Krzysztof Molenda

171

Wybranie wpływów, które nie są maksymalne

SELECT * FROM wpływy

WHERE kwota NOT IN
( SELECT MAX(kwota) FROM wpływy );

SELECT atrybuty FROM tabela

WHERE atrybut

NOT IN

(

Zapytanie1

);

Zastosowanie w rekurencji, polega na utworzeniu
synonimu 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;

SELECT atrybuty

FROM tabela, tabela AS synonim_tabeli

...

Samozłączenie

16

Operacje teoriomnogościowe - SUMA

2013-01-10

Bazy danych / Krzysztof Molenda

173

SELECT nr_czytelnika, nr_ksiazki, data_wyp, null

FROM wypozyczenia

UNION

SELECT nr_czytelnika, nr_ksiazki, data_wyp, data_zwr
FROM zwroty

•UNION/ UNION ALL

•INTERSECT

•MINUS

Przykłady (różne)

2013-01-10

Bazy danych / Krzysztof Molenda

174

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’;